SQL view for inventory items and dates

This view is a little bit of a twist on my previous SQL view for inventory quantities on hand. The results of this view will give you a list of your Microsoft Dynamics GP inventory items, current quantities and the last sales and purchase dates along with the vendor.

For other SQL views on Dynamics GP data, please visit my GP Reports page.

CREATE VIEW view_Inventory_with_Dates
AS
/********************************************************************
view_Inventory_with_Dates
Created on Dec 4, 2009 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 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
Updated Dec 22, 2009 to add WHERE clause at end
Updated Jan 29, 2011 to change join type for IV30300 ********************************************************************/

SELECT I.ITEMNMBR Item_Number,
       I.ITEMDESC Item_Description,
       Q.QTYONHND Quantity_on_Hand,
       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

LEFT OUTER JOIN
     (SELECT ITEMNMBR, MAX(DOCDATE) LastSale
      FROM IV30300
      WHERE DOCTYPE = 6
      GROUP BY ITEMNMBR) S
     ON I.ITEMNMBR = S.ITEMNMBR

INNER JOIN
     IV00102 Q
     ON I.ITEMNMBR = Q.ITEMNMBR
     AND RCRDTYPE = 1

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_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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s