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 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
Victoria Yudin 
Hi Victoria, Your code is so helpful
I am trying to get Inventory not Sold in Last 12 months. But I can’t get that exactly
SELECT I.ITEMNMBR Item_Number,
I.ITEMDESC Item_Description,
CAST(Q.QTYONHND AS INT) AS 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
AND S.LastSale < DATEADD(m,-12,SYSDATETIME());
GO
LikeLike
This code looks good to me – what is the problem you are seeing?
-Victoria
LikeLike
Hi, Victoria. Firstly, it is quite a site you’re running here. I’ve found lots of helpful info here to be very useful.
I am, however, stuck on what is seemingly an easy question. But, I can ‘t seem to find reference to an answer anywhere…..
I am just looking to update item quantities as our numbers are off. I am aware of mis-matches on on hand/available qtys. I am looking to place hard counts in the table(s) where applicable. I can do this to IV00102, but when I reconcile it places the delta of QTYRCVD and QTYSOLD from IV10200. I’ve tried to force QTYSOLD = QTYRECVD in IV10200, then back to IV00102 to update numbers. Cards and numbers are fine until you reconcile again. Then it places “Reconcile” RCPTNMBR data fields for “Orphaned” entitities, Taking it back to obscure numbers.
Anyway – I just wondered if you knew of a SQL table method of updating inventory item quantities? Any help is appreciated!
LikeLike
Hi Tray,
Thank you for your kind words.
For your situation – I would not recommend changing this kind of data directly in the tables. You’ve seen why. In general, you want to be very careful about changing data directly in the SQL tables, as you can very easily throw the integrity of your data completely out the window. Some things can be fixed back by reconcile and check links, however, others cannot. Unless you are 100% confident what you’re doing will not have any negative impact on the rest of the data, don’t do it.
The best way to adjust inventory quantities if they are incorrect is to enter an inventory adjustment transaction. If there are a lot of these and you have access to something like Integration Manager or another import tool, you could import instead of having to manually enter the transaction.
Decreases will automatically use appropriate cost based on your inventory setup. For any increase adjustments you may need to determine the correct cost to use for the items. You may also decide to use different GL accounts for the adjustments. This will need to be discussed with someone in accounting that can make that determination.
Hope that helps,
-Victoria
LikeLike
Hi Victoria I was trying to use the Inventory with Dates by Site view but I couldn’t find the site ID on this script. Can you please help me.
Thanks,
Babu
LikeLike
Hi Babu,
Thanks for noticing this – looks like the links were wrong. Here is the one you’re looking for: https://victoriayudin.com/2011/01/29/dynamics-gp-inventory-by-location-with-dates-and-sites/.
-Victoria
LikeLike
Thanks for your kind reply Victoria.
Regards,
Babu
LikeLike
Victoria,
I had tried this script back in Jan and it worked fine…..now I actually want to formally use it and I am getting the below error…I am sure it is something small but I cant seem to get it to work…any suggestions?:
Msg 208, Level 16, State 1, Procedure view_Inventory_with_Dates, Line 65
Invalid object name ‘IV00101’.
Msg 15151, Level 16, State 1, Line 2
Cannot find the object ‘view_Inventory_with_Dates’, because it does not exist or you do not have permission.
Thanks,
Wayne
LikeLike
Wayne,
Can you make sure you’re running this against your Dynamics GP company database? It’s saying it can’t find IV00101 which is the inventory master table, so I am thinking maybe you’re running this against the wrong database…
-Victoria
LikeLike
Victoria,
I knew it was something small…….did not think it would be something so obvious and dumb !!….My bad..sorry…..it works fine now.
Thanks again….your scripts are a great resource.
Wayne
LikeLike
LOL – it’s all good! 🙂
-Victoria
LikeLike
Hi Victoria As always these views are invaluable. The above is is exactly what I need but I would need to add in the T.TRXLOCTN from the other query you have for SQL view for inventory quantities on hand. Is this possible as this view seems to be using different tables? Many thanks Trish
LikeLike
Hi Trish,
Try this:
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, Q.LOCNCODE Site_ID 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 = 2 INNER JOIN IV40201 U ON U.UOMSCHDL = I.UOMSCHDL WHERE Q.QTYONHND <> 0-Victoria
LikeLike
Does this query account for drop-ship sales in the “last sale date”?
LikeLike
Mike,
It’s looking at all sales transactions in the inventory tables for that. I don’t believe it’s distinguishing between regular vs. drop-ship sales.
-Victoria
LikeLike
So if it is pulling from inventory tables it would not show a drop-ship sale since it did not come out of inventory. Right? Since we use Service Call, which “uses” drop-ship functionality in SOP, it is not showing sale date for items sold via Service Call. That is what I am seeing with my data. Not sure if it is Service Calls only or all drop-ships.
LikeLike
Ah! That makes sense. What you can do instead is add a little code to pull the latest date from SOP. Maybe something like my SQL view for last sale date of item.
-Victoria
LikeLike
Victoria, your sql views are beyond my knowledge level. Can you please combine the two pieces of code and post it? I need the one with Location too. If so, Thanks!
LikeLike
Mike,
I have been swamped this week, sorry. I will try to do this for you soon.
-Victoria
LikeLike
Mike,
Try this:
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, q.* from IV00101 I inner join IV00102 Q on I.ITEMNMBR = Q.ITEMNMBR and Q.RCRDTYPE = 2 left outer join (select ITEMNMBR, d.LOCNCODE, max(DOCDATE) LastSale from SOP30300 d inner join SOP30200 h on d.SOPTYPE = h.SOPTYPE and d.SOPNUMBE = h.SOPNUMBE where d.SOPTYPE = 3 group by ITEMNMBR, d.LOCNCODE) S on I.ITEMNMBR = S.ITEMNMBR and s.LOCNCODE = q.LOCNCODE inner join IV40201 U on U.UOMSCHDL = I.UOMSCHDL where Q.QTYONHND <> 0 or coalesce(S.LastSale,'1/1/1900') <> '1/1/1900'-Victoria
LikeLike
Victoria, This appears to be correct. I will let you know if I find any more problems. Thanks!!!
LikeLike
Victoria,
We have a similar report already built, not using a SQL view but rather a SmartList Builder report. We are looking to display only one rows of Items as they have been purchased from multiple vendors. We only want to show the most recent / last received date per item. How can we do this within SmartList Builder?
Example:
Item# 8004
Vendor# ABC123
Last Receipt Date: 8/1/2014
Item# 8004
Vendor# EFG456
Last Receipt Date: 8/15/2014
The only row we want to see is under from Vendor# EFG456, as the Last Receipt Date: 8/15/2014 is greater than the Vendor# ABC123, Last Receipt Date: 8/1/2014.
Thank you for your assistance.
Kerri Reinholtz
LikeLike
Hi Kerri,
I do not know the SmartList Builder syntax to accomplish that, sorry. You could try reaching out to eOne on their support forum to ask them. I personally would create a SQL view that does exactly what I want, then use it in SmartList Builder.
-Victoria
LikeLike
Hi Victoria,
Do you know if it is possible to create a SQL view similar to the standard GP report – Historical IV trial balance? I’d like to get to all the Inventory transactions – Receipts, Sales, Adjustments so i Can create an Opening to closing by Site, by Item – for quantities over a specified date range?
I’m guessing there is something complex about this, as I cannot find a solution anywhere!
LikeLike
Hi Rob,
I am sure it’s possible, but I suspect it would take a huge amount of work.
-Victoria
LikeLike
Hi Victoria,
I have found your blog to be the most useful online resource for GP. Thanks much for your help!
I have a fairly detail question for which I haven’t found a related answer online yet. In our customized application of GP, we have some scenarios where there are multiple transactions for an inventory item – same Inventory Item ID and Site ID – and has reversed inventory transactions or inventory variance doc type transactions to cancel a mistaken inventory transaction. I have now worked fairly extensively on the GP database side, but in this multiple transaction scenario I don’t see a way to link the specific reverse or variance inv transactions to their original inv transaction within IV30300, IV00102, IV10200-10201, IV30500, etc., as the Doc Numbers can’t be linked to the specific transaction within the multiple original transactions for that item & site.
Any help will be greatly appreciated.
Thanks,
Andy Kumar
LikeLike
Hi Andy,
It does not sound like you are entering this data, so until you do, I am not sure how you could report on it. For future transactions, you could possible start a new numbering convention. Something like, if the original transaction was 1234567, then all reversing or related transactions would be 1234567-001, 1234567-002, etc. That would you give you the ability to ‘group’ these pretty easily, but may change the way you enter (or import?) transactions. Another option would be something more complicated, like adding Extender data or a customization where you can track the ‘original’ transactions for each line item.
Hope this helps to give you some ideas.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
OK now can it be modified to show items on hand and never sold.
Thanks in advance.
LikeLike
Rick,
Just search for any items with a Last_Sale_Date of 1/1/1900.
-Victoria
LikeLike
Can this be modified to just show a single site?
LikeLike
Hi Rick,
I just posted a new view for this: https://victoriayudin.com/2011/01/29/dynamics-gp-inventory-by-location-with-dates/.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Thanks Victoria. Im getting blank data columns Last Sale Date and Last Receipt Date, is this because I have multiple sites?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike