June 18, 2009 
I haven’t had much time to blog lately as we have added functionality to our GP Reports Viewer product that allows seamless replacement of SOP reports in Dynamics GP and the amount of interest has been overwhelming.
However, I just needed to create a new SmartList to show current inventory quantities on hand with their costs and I wanted to share the view I used. Please bear in mind, this was only tested with our data and only FIFO Perpetual inventory items, so this may not work for all situations. And the average unit cost is simply the total cost divided by the quantity on hand.
For other GP SQL views, please take a look at my GP Reports page.
~~~~~
CREATE VIEW view_On_Hand_Inventory
AS
/********************************************************************
view_On_Hand_Inventory
Created on June 18, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Inventory on hand quantities by site
Only tested with FIFO Perpetual inventory valuation method
I - IV00101 - Item Master
T - IV10200 - Purchase Receipts
Updated on June 24, 2009 to add Item Class
********************************************************************/
SELECT T.ITEMNMBR Item,
I.ITEMDESC Description,
I.ITMCLSCD Item_Class,
T.TRXLOCTN Site_ID,
sum(T.QTYRECVD-T.QTYSOLD) Quantity,
sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD)) Total_Cost,
sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))
/sum(T.QTYRECVD-T.QTYSOLD) Avg_Unit_Cost
FROM IV10200 T
INNER JOIN
IV00101 I
ON I.ITEMNMBR = T.ITEMNMBR
WHERE T.QTYRECVD <> T.QTYSOLD
GROUP BY T.ITEMNMBR, T.TRXLOCTN, I.ITEMDESC, I.ITMCLSCD
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_On_Hand_Inventory TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Dear Victoria, first of all thank you this page helps me a lot, how can I get this same report but with Series and compartment?
Thank you
LikeLike
Hi Vitors,
Series and compartment are not “standard” Dynamics GP fields. Do you know where these are being entered for items?
-Victoria
LikeLiked by 1 person
Victoria, I’m trying to get an Inventory report built that shows Quantity, Lot, Bin and U of M .. one would think this should be easy, however it’s proving rather difficult, we tried to just add IV00300 to an existing Inventory Smart List (Item Quantites) to pull the Bin with a Left Outer Join, so that records without Lot’s would also pull.. but it seems to be joining to the wrong Bin’s at times .. So I tried using your sql above, and the results look promising except that as soon as I add IV00300 to the Query and Join on Item Number, once again I’m getting incorrect Bin’s to the item – in other words the bins that show under a site are not actually bins we have associated with that site.
Is there any way to fix this?
LikeLike
Hi Gail,
Are you using multiple bins? Or do you just have a bin assigned per site?
Unfortunately, I don’t have any good sample data that uses both bins and lots, so it’s difficult to help without seeing your data. Are you able to get help on this from your GP partner?
-Victoria
LikeLike
What command could be added to this query that would combine the same item from multiple locations into one average cost regardless of inventory locations?
LikeLike
Philip,
You’d actually need to remove, not add some code. 🙂 Try the query below:
-Victoria
LikeLike
Thanks for the quick reply. I had actually tried removing it from the group by I didn’t take it out of the select statement because I thought it might have been needed to get the initial calculations.
LikeLike
I do get results with the new query but it also gives me this message:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
LikeLike
Philip,
Add the following line at the bottom to get rid of the divide by zero error:
-Victoria
LikeLike
HAVING sum(T.QTYRECVD-T.QTYSOLD) 0 fixed the problem. 🙂
Thanks!
LikeLike
thanks victoria
LikeLike
Hi Victoria,
I am trying to reconcile the SQL view you have here, with the stock status report from GP, and they are slightly different. Any ideas as to why this may be the case, or do you have any smartlists/sql views that give me exactly what the Standard GP Stock Status Report would give, as i really need to replicate this report, in an excel type format (so either with SQL or smartlists).
Thanks
Matt
LikeLike
Okay, I wrote a SQL script that matches exactly to the Inventory Stock Status Report (to the cent). The details are below.
SELECT itemnmbr, trxloctn, sum(qtyrecvd-qtysold) as qty, sum((qtyrecvd-qtysold)*unitcost) as amount FROM IV10200 where qtytype=1
group by itemnmbr, trxloctn order by itemnmbr, trxloctn
It is useful to have this as you can make it into a view in SQL and then a smartlist within GP, so you can now have access to the Stock Status Report via a smartlist.
Note, this will only ever give you the LIVE stock status report, nothing historical. As Victoria mentioned earlier in this thread, this is very difficult to do.
Thanks
Matt
LikeLike
Matt,
The one thing I have found using this view vs. the Stock Status report in GP is that this will only show you On Hand quantities. The GP report will also show quantities that are Returned, Damaged, etc. Also, if you are not using LIFO or FIFO Perpetual, all bets are off. 🙂
-Victoria
LikeLike
Thank you so much for sharing this!
You probably saved me a good few hours trying to work out how to calculate FIFO costs.
Thanks again,
Jon
LikeLike
Victoria,
I have a doubt. Can I get the On Hand qty to the historical dates.(i.e I want to know the on hand qty of previous month).
Regards,
Jeganee
LikeLike
Jeganeedhi,
This query will not do it for you. Historical data is much more difficult to get, you would basically have to recreate the work that GP does when the Historical Stock Status report is run.
-Victoria
LikeLike
Victoria,
Is there any published listing of the work that GP does to create the Historical Stock Status Report ? Any assistance would be much appreciated.
Regards,
Robert
LikeLike
Robert,
I am not aware of documentation for the Historical Stock Status Report, only the HITB. You could try going to the GP KnowledgeBase and searching for KB articles on the Historical Stock Status Report to see if any of them give you any insight.
-Victoria
LikeLike
I tested the query and found no issues with it.
a) you will never get a division by zero because the first condition is QTYRECVD QTYSOLD and since both fields are defined as NUMERIC(19,5) SQL Server will cast this operation as a decimal.
b) you will never get a null value, as Dynamics GP cannot store nulls — it’s not supported by Dexterity. Hence the default GPS_MONEY.
Best regards,
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
LikeLike
Divide by zero error for me.
Change last select line to:
NULLIF(sum(T.QTYRECVD-T.QTYSOLD), 0) Avg_Unit_Cost
Alternately you could do
ISNULL(sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))
/NULLIF(sum(T.QTYRECVD-T.QTYSOLD), 0), 0) Avg_Unit_Cost
If you’d rather get a zero instead of NULL.
LikeLike
Phil,
I appreciate your comment, but I must respectfully disagree. Did you by any chance take the WHERE clause out of my view? One of the reasons that is there is to prevent a situation where there would be a division by zero. Since the results should only be bringing in data where the quantity received does not equal the quantity sold, you should never get a zero when you subtract one from the other. If you take the WHERE clause out, yes, you will most likely receive a divide by zero data and adding your code will address that. Leaving the WHERE clause as is, since all the fields I am bringing in must be not NULL by definition, I believe it is fine to not check for NULLS.
-Victoria
LikeLike
I think the problem is the WHERE clause pulls T.QTYRECVD T.QTYSOLD. If I throw SUM(T.QTYRECVD) and SUM(T.QTYSOLD) into the query as two more columns there are definitely instances where they’re the same value (causing the divide by zero error).
If I use a HAVING clause instead of a where clause it fixes the divide by zero error and I get the same number as my NULLIF query if I removed the NULL values.
SELECT T.ITEMNMBR Item,
I.ITEMDESC Description,
T.TRXLOCTN Site_ID,
SUM(T.QTYRECVD – T.QTYSOLD) Quantity,
SUM(T.UNITCOST * ( T.QTYRECVD – T.QTYSOLD )) Total_Cost,
SUM(T.UNITCOST * ( T.QTYRECVD – T.QTYSOLD )) / SUM(T.QTYRECVD – T.QTYSOLD) Avg_Unit_Cost,
SUM(T.QTYRECVD),
SUM(T.QTYSOLD)
FROM IV10200 T
INNER JOIN IV00101 I ON I.ITEMNMBR = T.ITEMNMBR
GROUP BY T.ITEMNMBR,
T.TRXLOCTN,
I.ITEMDESC
HAVING SUM(T.QTYRECVD) SUM(T.QTYSOLD)
LikeLike
Phil,
I am not clear on why the original view needs to be changed? Why do you have to take the WHERE clause out? I tried running the code you posted and got an error on the last line. Should there be a ” between the two sums? Adding that gives me exactly the same results as I have with my original code, however I believe using WHERE will result in more optimized code, since the WHERE operation will significantly reduce the amount of data in the result set before the GROUP BY clause is executed. If you are saying running the code exactly how I have it is causing the divide by zero error, I would have to think that there is something unexpected in your data beyond what should be in the two GP tables that are being used.
-Victoria
LikeLike
Whoops. The comment system stripped the angle bracket characters (makes sense, they look like HTML).
make the last line (or put the angle brackets back in):
HAVING SUM(T.QTYRECVD) != SUM(T.QTYSOLD)
I think you’re correct on the WHERE clause being easier from SQL Server’s perspective and it wouldn’t be the first time my system’s data was weird 🙂
LikeLike
Victoria,
Great post as usual. My first thought was how would you separate In Use, In Service, Returned, and Damaged Quantities to get to the actual On Hand? I know a lot of GP customers don’t use those quantities and there aren’t any in the sample data but this is important to some.
To get that level of detail, you’d need to query the IV00102. You could also query the IV00112 if you tracked Bins. The problem with querying that data is then you couldn’t get the actual cost using the cost layers in the Purchase Receipts table. Well, you probably could but that wouldn’t be quite as straight forward. You could rely on the Current or Standard Cost of the item but that’s not actual cost. This might be an interesting problem to work.
Anyway, I thought I would share that thought with you. Keep the info coming!
Thanks!
LikeLiked by 1 person