December 4, 2009
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 http://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
This is great, thanks.
Is there a way to add the sales (IA’s) to Service Calls as well? I see they’re all DOCTYPE 1 in IV30300, so maybe not so easy to separate from regular inventory transactions?
Hi David,
None of my test data has this that I can see, so I am not sure I can answer your question without looking at the actual data. But theoretically, you would just need to find something unique to only the records you want to include that would separate them from the inventory adjustments – maybe the transaction source or the module?
-Victoria
OK now can it be modified to show items on hand and never sold.
Thanks in advance.
Rick,
Just search for any items with a Last_Sale_Date of 1/1/1900.
-Victoria
Can this be modified to just show a single site?
Hi Rick,
I just posted a new view for this: http://victoriayudin.com/2011/01/29/dynamics-gp-inventory-by-location-with-dates/.
-Victoria
Victoria,
Thank you for creating so many views, I use several of them all the time. However I must be doing something wrong. When I open them in Excel I can only open as SA and not a GP user can you help me with this?
Thanks
Rick
Hi Rick,
You cannot use any GP logins outside of GP because GP encrypts the passwords when creating the SQL users. You can keep using sa, or create another user directly in SQL. If you’re doing looking at data in Excel, it might be best to add your domain user in SQL so that you don’t have to have yet another user ID and password to remember.
-Victoria
Thanks Victoria. Im getting blank data columns Last Sale Date and Last Receipt Date, is this because I have multiple sites?
Martin,
This is a little trickier to answer without seeing your data and understanding your setup and how transactions are entered. Multiple locations should not matter, this particular view only shows one line per item, not caring about locations. First thing I would check is whether the items you’re seeing blank dates for are set up to track history. If not, the tables this view uses may not have the required data.
To troubleshoot further I would pick an item that has those dates blank and that I know has been purchased (using POP) and/or sold (using SOP) and take a look in the tables the view uses for those items. The two main tables to check are:
IV30300 – Transaction Amounts History with DOCTYPE = 6 (sales)
IV00102 – Item Quantity Master
-Victoria
The previous SQL view for inventory quantities on hand worked a treat for me, but this one seems to show all items, not just ones with Qty on hand.
Martin,
Thanks for catching that! I updated the code with a WHERE clause at the end to filter out all items with Quantity on Hand not equal to zero.
-Victoria
Something I have noticed a lot of partners forgetting to do – is deliver a great view like this to end users via Excel Report Builder. This means that whenever they open the spreadsheet the results of the view are displayed.