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 represent 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:
GPEssentials_InventoryAging_Historical @AsOfDateAS DATE
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.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
WHEN
) THEN
WHEN
) THEN
WHEN
) THEN
WHEN
) THEN
WHEN
) THEN
1 year
WHEN
) THEN
WHEN
) THEN
WHEN
) THEN
WHEN
) THEN
ELSE
END AS InventoryAgingBucket
LEFT OUTER JOIN
SELECT X.ITEMNMBR,
FROM
dbo.IV10201
QTYTYPE= 1
) AS X
GROUP BY X.ITEMNMBR,
) 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
AND A.QTYTYPE = 1
AND A.QTYRECVD – ISNULL(B.QuantitySold, 0) <> 0;
Best Regards,
Mahmoud M. AlSaadi