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 PROCEDUREGPEssentials_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
            FROMdbo.IV10201
            WHEREQTYTYPE = 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