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