SQL view for inventory quantities on hand


I haven’t had much time to blog lately as we have added functionality to our GP Reports Viewer product that allows seamless replacement of SOP reports in Dynamics GP and the amount of interest has been overwhelming. 

However, I just needed to create a new SmartList to show current inventory quantities on hand with their costs and I wanted to share the view I used. Please bear in mind, this was only tested with our data and only FIFO Perpetual inventory items, so this may not work for all situations. And the average unit cost is simply the total cost divided by the quantity on hand.

For other GP SQL views, please take a look at my GP Reports page.

~~~~~

CREATE VIEW view_On_Hand_Inventory
AS

/********************************************************************
view_On_Hand_Inventory
Created on June 18, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Inventory on hand quantities by site
Only tested with FIFO Perpetual inventory valuation method
I - IV00101 - Item Master
T - IV10200 - Purchase Receipts
Updated on June 24, 2009 to add Item Class
********************************************************************/


SELECT T.ITEMNMBR Item,
       I.ITEMDESC Description,
I.ITMCLSCD Item_Class,
       T.TRXLOCTN Site_ID,
       sum(T.QTYRECVD-T.QTYSOLD) Quantity,
       sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD)) Total_Cost,
       sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))
          /sum(T.QTYRECVD-T.QTYSOLD) Avg_Unit_Cost


FROM IV10200 T
INNER JOIN
     IV00101 I
     ON I.ITEMNMBR = T.ITEMNMBR


WHERE T.QTYRECVD <> T.QTYSOLD
GROUP BY T.ITEMNMBR, T.TRXLOCTN, I.ITEMDESC, I.ITMCLSCD


/** 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_On_Hand_Inventory 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.

24 Responses to “SQL view for inventory quantities on hand”

  1. Victoria, I’m trying to get an Inventory report built that shows Quantity, Lot, Bin and U of M .. one would think this should be easy, however it’s proving rather difficult, we tried to just add IV00300 to an existing Inventory Smart List (Item Quantites) to pull the Bin with a Left Outer Join, so that records without Lot’s would also pull.. but it seems to be joining to the wrong Bin’s at times .. So I tried using your sql above, and the results look promising except that as soon as I add IV00300 to the Query and Join on Item Number, once again I’m getting incorrect Bin’s to the item – in other words the bins that show under a site are not actually bins we have associated with that site.
    Is there any way to fix this?

    Like

    • Hi Gail,

      Are you using multiple bins? Or do you just have a bin assigned per site?

      Unfortunately, I don’t have any good sample data that uses both bins and lots, so it’s difficult to help without seeing your data. Are you able to get help on this from your GP partner?

      -Victoria

      Like

  2. What command could be added to this query that would combine the same item from multiple locations into one average cost regardless of inventory locations?

    Like

    • Philip,

      You’d actually need to remove, not add some code. 🙂 Try the query below:

      SELECT T.ITEMNMBR Item,
      I.ITEMDESC Description,
      I.ITMCLSCD Item_Class,
      sum(T.QTYRECVD-T.QTYSOLD) Quantity,
      sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD)) Total_Cost,
      sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))
       /sum(T.QTYRECVD-T.QTYSOLD) Avg_Unit_Cost
      FROM IV10200 T
      INNER JOIN
       IV00101 I
       ON I.ITEMNMBR = T.ITEMNMBR
      WHERE T.QTYRECVD <> T.QTYSOLD
      GROUP BY T.ITEMNMBR, I.ITEMDESC, I.ITMCLSCD
      

      -Victoria

      Like

  3. thanks victoria

    Like

  4. Hi Victoria,

    I am trying to reconcile the SQL view you have here, with the stock status report from GP, and they are slightly different. Any ideas as to why this may be the case, or do you have any smartlists/sql views that give me exactly what the Standard GP Stock Status Report would give, as i really need to replicate this report, in an excel type format (so either with SQL or smartlists).

    Thanks

    Matt

    Like

    • Okay, I wrote a SQL script that matches exactly to the Inventory Stock Status Report (to the cent). The details are below.

      SELECT itemnmbr, trxloctn, sum(qtyrecvd-qtysold) as qty, sum((qtyrecvd-qtysold)*unitcost) as amount FROM IV10200 where qtytype=1
      group by itemnmbr, trxloctn order by itemnmbr, trxloctn

      It is useful to have this as you can make it into a view in SQL and then a smartlist within GP, so you can now have access to the Stock Status Report via a smartlist.

      Note, this will only ever give you the LIVE stock status report, nothing historical. As Victoria mentioned earlier in this thread, this is very difficult to do.

      Thanks

      Matt

      Like

    • Matt,

      The one thing I have found using this view vs. the Stock Status report in GP is that this will only show you On Hand quantities. The GP report will also show quantities that are Returned, Damaged, etc. Also, if you are not using LIFO or FIFO Perpetual, all bets are off. 🙂

      -Victoria

      Like

  5. Thank you so much for sharing this!

    You probably saved me a good few hours trying to work out how to calculate FIFO costs.

    Thanks again,

    Jon

    Like

  6. Victoria,

    I have a doubt. Can I get the On Hand qty to the historical dates.(i.e I want to know the on hand qty of previous month).

    Regards,
    Jeganee

    Like

    • Jeganeedhi,

      This query will not do it for you. Historical data is much more difficult to get, you would basically have to recreate the work that GP does when the Historical Stock Status report is run.

      -Victoria

      Like

      • Victoria,
        Is there any published listing of the work that GP does to create the Historical Stock Status Report ? Any assistance would be much appreciated.

        Regards,
        Robert

        Like

  7. I tested the query and found no issues with it.

    a) you will never get a division by zero because the first condition is QTYRECVD QTYSOLD and since both fields are defined as NUMERIC(19,5) SQL Server will cast this operation as a decimal.

    b) you will never get a null value, as Dynamics GP cannot store nulls — it’s not supported by Dexterity. Hence the default GPS_MONEY.

    Best regards,

    MG.-
    Mariano Gomez, MVP
    Maximum Global Business, LLC
    http://www.maximumglobalbusiness.com

    Like

  8. Divide by zero error for me.

    Change last select line to:
    NULLIF(sum(T.QTYRECVD-T.QTYSOLD), 0) Avg_Unit_Cost

    Alternately you could do
    ISNULL(sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))
    /NULLIF(sum(T.QTYRECVD-T.QTYSOLD), 0), 0) Avg_Unit_Cost

    If you’d rather get a zero instead of NULL.

    Like

    • Phil,

      I appreciate your comment, but I must respectfully disagree. Did you by any chance take the WHERE clause out of my view? One of the reasons that is there is to prevent a situation where there would be a division by zero. Since the results should only be bringing in data where the quantity received does not equal the quantity sold, you should never get a zero when you subtract one from the other. If you take the WHERE clause out, yes, you will most likely receive a divide by zero data and adding your code will address that. Leaving the WHERE clause as is, since all the fields I am bringing in must be not NULL by definition, I believe it is fine to not check for NULLS.

      -Victoria

      Like

      • I think the problem is the WHERE clause pulls T.QTYRECVD T.QTYSOLD. If I throw SUM(T.QTYRECVD) and SUM(T.QTYSOLD) into the query as two more columns there are definitely instances where they’re the same value (causing the divide by zero error).

        If I use a HAVING clause instead of a where clause it fixes the divide by zero error and I get the same number as my NULLIF query if I removed the NULL values.

        SELECT T.ITEMNMBR Item,
        I.ITEMDESC Description,
        T.TRXLOCTN Site_ID,
        SUM(T.QTYRECVD – T.QTYSOLD) Quantity,
        SUM(T.UNITCOST * ( T.QTYRECVD – T.QTYSOLD )) Total_Cost,
        SUM(T.UNITCOST * ( T.QTYRECVD – T.QTYSOLD )) / SUM(T.QTYRECVD – T.QTYSOLD) Avg_Unit_Cost,
        SUM(T.QTYRECVD),
        SUM(T.QTYSOLD)
        FROM IV10200 T
        INNER JOIN IV00101 I ON I.ITEMNMBR = T.ITEMNMBR
        GROUP BY T.ITEMNMBR,
        T.TRXLOCTN,
        I.ITEMDESC
        HAVING SUM(T.QTYRECVD) SUM(T.QTYSOLD)

        Like

        • Phil,

          I am not clear on why the original view needs to be changed? Why do you have to take the WHERE clause out? I tried running the code you posted and got an error on the last line. Should there be a ” between the two sums? Adding that gives me exactly the same results as I have with my original code, however I believe using WHERE will result in more optimized code, since the WHERE operation will significantly reduce the amount of data in the result set before the GROUP BY clause is executed. If you are saying running the code exactly how I have it is causing the divide by zero error, I would have to think that there is something unexpected in your data beyond what should be in the two GP tables that are being used.

          -Victoria

          Like

          • Whoops. The comment system stripped the angle bracket characters (makes sense, they look like HTML).

            make the last line (or put the angle brackets back in):
            HAVING SUM(T.QTYRECVD) != SUM(T.QTYSOLD)

            I think you’re correct on the WHERE clause being easier from SQL Server’s perspective and it wouldn’t be the first time my system’s data was weird 🙂

            Like

  9. Victoria,

    Great post as usual. My first thought was how would you separate In Use, In Service, Returned, and Damaged Quantities to get to the actual On Hand? I know a lot of GP customers don’t use those quantities and there aren’t any in the sample data but this is important to some.

    To get that level of detail, you’d need to query the IV00102. You could also query the IV00112 if you tracked Bins. The problem with querying that data is then you couldn’t get the actual cost using the cost layers in the Purchase Receipts table. Well, you probably could but that wouldn’t be quite as straight forward. You could rely on the Current or Standard Cost of the item but that’s not actual cost. This might be an interesting problem to work.

    Anyway, I thought I would share that thought with you. Keep the info coming!

    Thanks!

    Liked by 1 person

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: