Dynamics GP – Inventory Aging Report

 

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:

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

Dynamics GP – Manufacturing Order Cost Distribution

The following reports retrieve data from the manufacturing order closing file, it illustrates the following details for every single MO per item number:

  • Material Cost
  • Material Fixed Overhead
  • Material Variable Overhead
  • Labor Cost
  • Labor Fixed Overhead
  • Labor Variable Overhead
  • Machine
  • Machine Fixed Overhead
  • Machine Variable Overhead
  • Total MO cost

 

SELECT ITEMNMBR,

DATEPART(yy, COMPLETECLOSEDATE) AS RecYear,

DATEPART(mm, COMPLETECLOSEDATE) AS RecMonth,

SUM(ENDQTY_I) AS RecQTY,

BASEUOFM
,

SUM(ITEM_COSTS_ARRAY_I_1) AS [Material_Costs],

SUM(ITEM_COSTS_ARRAY_I_2) AS [Material_Fixed_Overhead],

SUM(ITEM_COSTS_ARRAY_I_3) AS [Material_Variable_Overhead],

SUM(ITEM_COSTS_ARRAY_I_4) AS [Labor],

SUM(ITEM_COSTS_ARRAY_I_5) AS [Labor_Fixed_Overhead],

SUM(ITEM_COSTS_ARRAY_I_6) AS [Labor_Variable_Overhead],

SUM(ITEM_COSTS_ARRAY_I_7) AS [Machine],

SUM(ITEM_COSTS_ARRAY_I_8) AS [Machine_Fixed_Overhead],

SUM(ITEM_COSTS_ARRAY_I_9) AS [Machine_Variable_Overhead],

SUM(ITEM_COSTS_ARRAY_I_10) AS [Total_Cost]
FROM
(

SELECT A.*,

B
.ITEMNMBR,

B
.COMPLETECLOSEDATE,

B
.ENDQTY_I,

D
.[BASEUOFM]

FROM WO010701 AS A

INNER JOIN WO010032 AS B
            ON
A
.MANUFACTUREORDER_I = B.MANUFACTUREORDER_I

INNER JOIN IV00101 AS C
            ON
B
.[ITEMNMBR] = C.[ITEMNMBR]

INNER JOIN IV40201 AS D
            ON
C
.[UOMSCHDL] = D.[UOMSCHDL]

WHERE MANUFACTUREORDERST_I = 8
) AS E
GROUP BY ITEMNMBR,

DATEPART(yy, COMPLETECLOSEDATE),

DATEPART(mm, COMPLETECLOSEDATE),
         BASEUOFM
Best Regards, 
Mahmoud M. AlSaadi