Historical Inventory Aging Report ... Finally !

By Mahmood M. AlSaadi posted 10 days ago

  

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,
Mahmood M. AlSaadi - MVP
Principal Dynamics ERP Consultant
SIROCo LLC - (844) 708-0008
McKinney TX
m.saadi@sirocollc.com
http://www.sirocotech.com

0 comments
9 views

Permalink