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
AS
/********************************************************************
view_Inventory_by_Site_with_Dates
Created on Jan 29, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://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 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


Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code Tagged: Dynamics GP, GP Reports code, Inventory, SQL code