SQL view for inventory quantities on hand

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 http://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
********************************************************************/

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

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

/** 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.