Historical Inventory Aging Report - Finally !!

We have always received requests from clients about historical inventory aging report, unfortunately, this report is not available in the system as a standard report, and thus requires customizations. We've illustrated in a previous post on GPUG how to calculate the inventory turn over analysis as part of the inventory reporting. On this article, we will dig deeper into the aging report.

Such as receivables and payables, aging buckets should be predefined for the inventory aging report, for this one, we will consider the following aging buckets:
 

• Date Difference < 0  Then (Current)
• Date Difference > 0 and < 30   Then   (0- 30) 
• Date Difference > 31 and < 60   Then (31-60) 
• Date Difference > 61 and < 90   Then   (61- 90) 
• Date Difference > 91 and < 180   Then   (91- 180) 
• Date Difference > 181 and < 360   Then (181-360) 
• Date Difference > 360 and < 720   Then (1-2 Years) 
• Date Difference > 720 and < 1440   Then (1-2 Years) 
• Date Difference > 1440 and < 2880  Then (3-4 Years) 
• Date Difference > 2880 and < 5760 Then (4-5 Years) 
• Date Difference > 5760 Then (Above 5 Years) 

The first step is to retrieve all open cost layers as of a specific date, for that purpose, we do need to consider two primary tables ( IV101200 and IV10201) which are purchase receipt and purchase receipt details. These two tables represents the cost layers of the inventory module, and there is a direct link represented with the (Receipt Sequence Number) and the (Source Receipt Sequence Number).


Here is the SQL Script for this report:

CREATE PROCEDURE GPEssentials_InventoryAging_Historical @AsOfDate AS DATE

AS

SET @AsOfDate = '2017-12-31';

SELECT A.ITEMNMBR AS ItemNumber,

       RTRIM(LTRIM(C.ITEMDESC)) AS ItemDescription,

       RTRIM(LTRIM(C.ITMCLSCD)) AS ItemClass,

       A.TRXLOCTN AS Site,

       A.DATERECD AS DateReceived,

       A.RCTSEQNM AS ReceiptSequenceNumber,

       A.QTYRECVD AS QuantityReceived,

       ISNULL(B.QuantitySold, 0) AS QuantitySold,

       A.QTYRECVD - ISNULL(B.QuantitySold, 0) AS RemainingQuanity,

       (A.QTYRECVD - ISNULL(B.QuantitySold, 0)) * A.UNITCOST AS ExtendedCot,

       A.RCPTNMBR AS ReceiptNumber,

       A.UNITCOST,

       A.QTYTYPE AS QuantityType,

       A.Landed_Cost,

       A.NEGQTYSOPINV,

       A.VCTNMTHD,

       A.ADJUNITCOST,

       ISNULL(B.SRCRCTSEQNM, '') AS SourceReceiptSequence,

       @AsOfDate AS AsOfDate,

       DATEDIFF(DAY, A.DATERECD, @AsOfDate) AS AgeDays,

       CASE

           WHEN DATEDIFF(DAY, A.DATERECD, @AsOfDate) < 0 THEN

               'Current'

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 0

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 30

           ) THEN

               '0-30 Days'    -- 1 month

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 30

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 60

           ) THEN

               '31-60 Days'   -- 2 months

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 60

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 90

           ) THEN

               '61-90 Days'   -- 3 months

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 90

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 180

           ) THEN

               '91-180 Days'  -- 6 months

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 180

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 360

           ) THEN

               '181-360 Days' -- 1 year

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 360

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 720

           ) THEN

               '> 1 year — 2 years'

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 720

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 1440

           ) THEN

               '> 2 years — 3 years'

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 1440

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 2880

           ) THEN

               '> 3 years – 4 years'

           WHEN

           (

               DATEDIFF(DAY, A.DATERECD, @AsOfDate) > 2880

               AND DATEDIFF(DAY, A.DATERECD, @AsOfDate) <= 5760

           ) THEN

               '> 4 years– 5 years'

           ELSE

               '> 5 years — Above 5 years'

       END AS InventoryAgingBucket

FROM dbo.IV10200 AS A

    LEFT OUTER JOIN

    (

        SELECT X.ITEMNMBR,

               X.TRXLOCTN,

               SUM(X.QTYSOLD) AS QuantitySold,

               SUM(X.ExtendedCost) AS ExtendedCost,

               X.SRCRCTSEQNM,

               X.QTYTYPE

        FROM

        (

            SELECT ITEMNMBR,

                   TRXLOCTN,

                   QTYSOLD,

                   UNITCOST,

                   QTYSOLD * UNITCOST AS ExtendedCost,

                   RCTSEQNM,

                   SRCRCTSEQNM,

                   QTYTYPE

            FROM dbo.IV10201

            WHERE QTYTYPE = 1

                  AND DOCDATE <= @AsOfDate

        ) AS X

        GROUP BY X.ITEMNMBR,

                 X.TRXLOCTN,

                 X.SRCRCTSEQNM,

                 X.QTYTYPE

    ) AS B

        ON B.ITEMNMBR = A.ITEMNMBR

           AND B.TRXLOCTN = A.TRXLOCTN

           AND B.SRCRCTSEQNM = A.RCTSEQNM

    LEFT OUTER JOIN IV00101 AS C

        ON A.ITEMNMBR = C.ITEMNMBR

WHERE A.DATERECD <= @AsOfDate

      AND A.QTYTYPE = 1

      AND A.QTYRECVD - ISNULL(B.QuantitySold, 0) <> 0;

GO

Best Regards, 
Mahmoud M. AlSaadi