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

44 Responses to “SQL view for inventory items and dates”

  1. 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

    Like

  2. 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!

    Like

    • 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

      Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

    • 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

      Like

      • Does this query account for drop-ship sales in the “last sale date”?

        Like

        • 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

          Like

          • 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.

            Like

            • 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

              Like

              • 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!

                Like

                • Mike,

                  I have been swamped this week, sorry. I will try to do this for you soon.

                  -Victoria

                  Like

                  • 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

                    Like

  6. 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

    Like

    • 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

      Like

  7. 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!

    Like

  8. 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

    Like

    • 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

      Like

  9. 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?

    Like

    • 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

      Like

  10. OK now can it be modified to show items on hand and never sold.

    Thanks in advance.

    Like

  11. Can this be modified to just show a single site?

    Like

  12. Thanks Victoria. Im getting blank data columns Last Sale Date and Last Receipt Date, is this because I have multiple sites?

    Like

    • 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

      Like

  13. 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.

    Like

  14. 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.

    Like

Trackbacks/Pingbacks

  1. Dynamics GP inventory by location with dates - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 29, 2011

    […] 0 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 […]

    Like

  2. Victoria Yudin - January 29, 2011

    Dynamics GP inventory by location with dates…

    In response to a request from a reader, I have created a new version of the Inventory with Dates script…

    Like

  3. Dynamics GP inventory by location with dates « Victoria Yudin - January 29, 2011

    […] 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 […]

    Like

  4. SQL View for inventory Quantities on Hand - DynamicAccounting.net - December 7, 2009

    […] View for inventory Quantities on Hand Victoria Yudin has posted a new SQL view to show inventory items, current quantities, last sales and purchase date along with vendor. Make sure you check it out. Published: Monday, December 07, 2009, 05:30 […]

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: