January 29, 2011 
In response to a request from a reader, I have created a new version of the Inventory with Dates script to show the same information by Site ID. I also added a Quantity on Order column.
You can see more Dynamics GP Inventory scripts here. Or visit my GP Reports page for links to additional GP scripts and other reporting information and tips.
~~~~~
CREATE VIEW view_Inventory_by_Site_with_Dates_and_Sites AS /******************************************************************** view_Inventory_by_Site_with_Dates Created on Jan 29, 2011 by Victoria Yudin - Flexible Solutions, Inc. For updates see https://victoriayudin.com/gp-reports/ All inventory items with quantity on hand and last sale and receipt dates, by site ID Functional amounts only Tables used: I - IV00101 - Item Master S - IV30300 - Transaction Amounts History with DOCTYPE = 6 (sales) Q - IV00102 - Item Quantity Master U - IV40201 - U of M Schedule Header ********************************************************************/ SELECT I.ITEMNMBR Item_Number, Q.LOCNCODE Site_ID, I.ITEMDESC Item_Description, Q.QTYONHND Quantity_on_Hand, Q.QTYONORD Quantity_on_Order, U.BASEUOFM U_of_M, CASE I.ITEMTYPE WHEN 1 THEN 'Sales Inventory' WHEN 2 THEN 'Discontinued' WHEN 3 THEN 'Kit' WHEN 4 THEN 'Misc Charges' WHEN 5 THEN 'Services' WHEN 6 THEN 'Flat Fee' END Item_Type, I.CURRCOST Current_Cost, I.ITMCLSCD Item_Class, coalesce(S.LastSale,'1/1/1900') Last_Sale_Date, coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date, coalesce(Q.LSORDVND,'') Last_Vendor FROM IV00101 I INNER JOIN IV00102 Q ON I.ITEMNMBR = Q.ITEMNMBR AND RCRDTYPE = 2 LEFT OUTER JOIN (SELECT ITEMNMBR, MAX(DOCDATE) LastSale, TRXLOCTN FROM IV30300 WHERE DOCTYPE = 6 GROUP BY ITEMNMBR, TRXLOCTN) S ON I.ITEMNMBR = S.ITEMNMBR AND Q.LOCNCODE = S.TRXLOCTN INNER JOIN IV40201 U ON U.UOMSCHDL = I.UOMSCHDL WHERE Q.QTYONHND <> 0 /** 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_Inventory_by_Site_with_Dates_and_Sites 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
Dear Victoria
Thanks a lot for your scripts and they are fantastic and I use many of them saving uncountable hours.
But now I tried the Inventory Views “Inventory with Dates” and “Inventory with Dates by Site”. Here in the View “Inventory with Dates” the Last Sales date is coming good, but in the view “Inventory with Dates by Site” many of the Last Sales date is coming 1/1/1900. I checked and found the same item is coming with the correct Last Sale Date in the View Inventory with Dates.
Thanks in advance
Abdul Rahman
LikeLike
Hi Abdul,
Remember that this view will return a row for each item/site combination where the on hand quantity is not 0. 1/1/1900 in the Last Sale date means that item has a quantity other than 0 for the site, but has not been sold from this site.
-Victoria
LikeLike
Hi Victoria,
Do you have a SQL view that will show inventory movement, both outgoing and incoming, showing the cost and sales amount per item?
Thanks in advance.
Ernie
LikeLike
Hi Ernie,
Sorry, I don’t have anything like that. I’ve looked around at other blogs and sites, as well, but have not found any scripts that do what you’re asking.
-Victoria
LikeLike
Thanks, This was a great time saver in identifying slows moving and ‘stuck’ stock lines.
LikeLike
OK. Thanks, anyway. I’ll try to create one and share the script here when I’m done.
Ernie
LikeLike
Thank you another great Report!!!
LikeLike