SQL View for Inventory Aging

Guys

I have come across many requests from various friends and clients of mine, who have a need to print a stock aging report in GP.

There is no out-of-the-box report for stock aging report in GP. So I decided to write out a simple view to achieve the same.

 
CREATE VIEW vw_IVStockAgeing
AS  SELECT  IV00101.ITEMNMBR AS ITEMNUMBER,
            IV00101.ITEMDESC AS ITEMNAME,
            IV10200.DATERECD AS DATERECEIVED,
            IV10200.QTYRECVD AS QTYRECEIVED,
            IV10200.QTYSOLD AS QTYSOLD,
            IV00101.ITMCLSCD AS ITEMCLASS,
            IV10200.UNITCOST AS UNITCOST,
            IV10200.PCHSRCTY AS RECEIPTTYPE,
            IV10200.RCPTNMBR AS RECEIPTNO,
            IV10200.TRXLOCTN AS LOCATION,
            ( IV10200.QTYRECVD - IV10200.QTYSOLD ) AS QTYAVAILABLE,
            ( ( IV10200.QTYRECVD - IV10200.QTYSOLD ) * IV10200.UNITCOST ) AS CURRENTVALUE,
            DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) AS AGEDDAYS,
            CASE WHEN DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) < 0
                 THEN 'Current'
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 0
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 30
                      ) THEN '0-30 Days'     -- 1 month
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 30
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 60
                      ) THEN '31-60 Days'     -- 2 months
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 60
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 90
                      ) THEN '61-90 Days'     -- 3 months
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 90
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 180
                      ) THEN '91-180 Days'    -- 6 months
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 180
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 360
                      ) THEN '181-360 Days'     -- 1 year
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 360
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 720
                      ) THEN '> 1 year'    -- 2 years
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 720
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 1440
                      ) THEN '> 2 years'    -- 3 years
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 1440
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 2880
                      ) THEN '> 3 years'-- 4 years
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) > 2880
                        AND DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) <= 5760
                      ) THEN '> 4 years'-- 5 years
                 ELSE '> 5 years'    -- Above 5 years
            END AS BUCKET
    FROM    ( IV00101 IV00101
              INNER JOIN IV10200 IV10200
                ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
            )
            INNER JOIN IV00102 IV00102
                ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
    WHERE   IV00102.RCRDTYPE = 1
            AND IV10200.PCHSRCTY <> 0

This view can be further extended with the following options:

1. The aging buckets in this view are hardcoded. They can be defined as a setup in GP, using a simple Dex customization and using this setup the aging buckets can be made dynamic.
2. This view ages the stock as of the current date. This can be changed by modifying the view into a stored procedure which takes in a date as a parameter and the aging can be done as of a specific date as well.

Until next post, happy scripting…

Siva

VN:F [1.9.13_1145]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.13_1145]
Rating: +1 (from 1 vote)

May 2, 2009 В· veeyeskay В· 6 Comments
Posted in: Dynamics, Great Plains, Inventory Total Views: 1,422

  • Pingback: Inventory aging | Wrestlingbeaut

  • http://twitter.com/vaidymohan Vaidy Mohan

    IV10200 table has got a drawback. It is Purchase Receipts table and has got a QTYSOLD field using which we derive at this. But it’s not clear on what basis GP considers QTYSOLD on a particular Purchase Receipt. It would work perfectly for Serialized / Lot Items. But regular items, I doubt. Aging overall would then be approximation. Having said that, we have only this method to derive an Aging report. No other option.

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
    • Sivakumar Venkataraman

      Vaidy, You are right, but unfortunately, thats the only method to derive the inventory aging… :) В 

      VA:F [1.9.13_1145]
      Rating: 0.0/5 (0 votes cast)
      VA:F [1.9.13_1145]
      Rating: 0 (from 0 votes)
  • veeyeskay

    Andy, This query works when you generate the aging as of the current date. It does not support historical aging, since the system picks up the current cost from the Item Master.

    VN:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VN:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
  • Andy

    OK to verify the accuracy of this report you should be able to print a stock status report and have it agree to the totals from this correct? Where would you look if it didn't agree and the Stock Status and the GL Account number agree?

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
  • Andrew

    I got tasked to make an agin report with buckets and found this. I now have a good start! Thanks!

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)