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:
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
*This post is locked for comments