Inventory Aging Code for Dynamics AX February 06, 2018

SELECT ITEMID, DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) AS AGEDDAYS,
(CASE WHEN QTY>0 THEN QTY ELSE 0 END) AS PURQTY, (CASE WHEN QTY<0 THEN qty ELSE 0 END) AS SALQTY,

(CASE WHEN DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) < 0
THEN '0-Current' WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 0
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 30
) THEN '1-0-30 Days' -- 1 month
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 30
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 60
) THEN '2-31-60 Days' -- 2 months
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 60
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 90
) THEN '3-61-90 Days' -- 3 months
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 90
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 180
) THEN '4-91-180 Days' -- 6 months
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 180
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 360
) THEN '5-181-360 Days' -- 1 year
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 360
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 720
) THEN '6-> 1 year'  --2 years
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 720
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 1440
) THEN '7-> 2 years' -- 3 years
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 1440
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 2880
) THEN '8-> 3 years' -- 4 years
WHEN ( DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) > 2880
AND DATEDIFF(day, INVENTTRANS.DATEPHYSICAL, @TODATE) <= 5760
) THEN '9-> 4 years' -- 5 years
ELSE '9-> 5 years' --  — Above 5 years
END) AS BUCKET,left(INVENTDIM.INVENTBATCHID,4) as Crop
INTO #TEMPTB
FROM INVENTTRANS INNER JOIN INVENTDIM ON INVENTDIM.INVENTDIMID=INVENTTRANS.INVENTDIMID AND INVENTDIM.DATAAREAID=INVENTTRANS.DATAAREAID WHERE INVENTTRANS.DATAAREAID=@COMPANYID
--AND ITEMID='ITM-000245'
and INVENTTRANS.TRANSTYPE NOT IN (7,21,22)
AND INVENTTRANS.DATEPHYSICAL<>'01/01/1900 00:00:00'
AND INVENTTRANS.DATEPHYSICAL<=@TODATE
--AND INVENTBATCHID='1314-000225-263'
SELECT ITEMID,CROP,SUM(PURQTY) AS PURQTY,SUM(SALQTY) AS SALQTY,SUM(PURQTY+SALQTY) AS ONHAND,BUCKET AS BUCKET INTO #TEMPTB2 FROM #TEMPTB GROUP BY ITEMID,BUCKET,CROP ORDER BY BUCKET
SELECT TMP2.ITEMID,CROP,PURQTY,SALQTY,ONHAND,RIGHT(BUCKET,LEN(BUCKET)-2) AS BUCKET,LEFT(BUCKET,1) AS SORT,ITEMNAME,INVENTITEMBRAND.NAME AS BRANDNAME,INVENTITEMCATEGORY.NAME AS CATEGORYNAME,INVENTITEMGROUP.ITEMGROUPID,INVENTTABLEMODULE.UNITID
,(CASE WHEN INVENTTABLEMODULE.PRICE=0 THEN
(SELECT AVG(INVENTTRANS.COSTAMOUNTPHYSICAL / (CASE WHEN INVENTTRANS.QTY=0 THEN 1 ELSE INVENTTRANS.QTY END))
--(CASE WHEN INVENTTRANS.QTY=0 THEN 1 ELSE INVENTTRANS.QTY END))) END)
FROM INVENTTRANS INNER JOIN INVENTDIM ON INVENTTRANS.INVENTDIMID = INVENTDIM.INVENTDIMID AND INVENTTRANS.DATAAREAID = INVENTDIM.DATAAREAID WHERE (INVENTTRANS.ITEMID = TMP2.ITEMID) AND (INVENTTRANS.QTY > 0)) ELSE  INVENTTABLEMODULE.PRICE END) AS PRICE

INTO #TEMPTB3
FROM #TEMPTB2 TMP2 INNER JOIN INVENTTABLE ON TMP2.ITEMID=INVENTTABLE.ITEMID AND INVENTTABLE.DATAAREAID=@COMPANYID
INNER JOIN INVENTITEMBRAND ON INVENTITEMBRAND.ITEMBRANDID=INVENTTABLE.ITEMBRANDID AND  INVENTITEMBRAND.DATAAREAID=INVENTTABLE.DATAAREAID
INNER JOIN INVENTITEMCATEGORY ON INVENTITEMCATEGORY.ITEMCATEGORYID=INVENTTABLE.ITEMCATEGORYID AND INVENTITEMCATEGORY.DATAAREAID=INVENTTABLE.DATAAREAID
INNER JOIN INVENTITEMGROUP ON INVENTITEMGROUP.ITEMGROUPID=INVENTTABLE.ITEMGROUPID AND INVENTITEMGROUP.DATAAREAID=INVENTTABLE.DATAAREAID
INNER JOIN INVENTTABLEMODULE ON INVENTTABLE.ITEMID=INVENTTABLEMODULE.ITEMID AND INVENTTABLE.DATAAREAID=INVENTTABLEMODULE.DATAAREAID AND INVENTTABLEMODULE.MODULETYPE=0
WHERE ONHAND<>0 AND INVENTTABLE.ITEMGROUPID = @ITEMGROUPID

--SELECT * FROM #TEMPTB3


--SELECT * FROM INVENTTABLEMODULE WHERE DATAAREAID='MRP' AND ITEMID='ITM-001445' AND MODULETYPE=0


DECLARE @st TABLE
(
Sort INT,
PurQty NUMERIC(20,2),
SalQty NUMERIC(20,2),
RunningTotal NUMERIC(20,2),
ITEMID VARCHAR(20),
BUCKET VARCHAR(50),
AGING NUMERIC(20,2),
ITEMGROUPID VARCHAR(10),
STAT VARCHAR(1),
ITEMNAME VARCHAR(200),
UNITID VARCHAR(10),
PRICE NUMERIC(20,2),
CROP VARCHAR(4)
);

DECLARE @RunningTotal NUMERIC(20,2) = 0;
declare @Issuedstk NUMERIC(20,2) = 0;
--SET @Issuedstk =(Select SUM(Salqty)*-1 from #TEMPTB3)
INSERT @st(Sort, PurQty,SalQty, RunningTotal,ITEMID,BUCKET,AGING,ITEMGROUPID,STAT,ITEMNAME,UNITID,PRICE,CROP)
SELECT SORT, PURQTY,SALQTY , RunningTotal = 0,ITEMID,BUCKET,0,ITEMGROUPID,'',ITEMNAME,UNITID,PRICE,CROP
FROM #TEMPTB3
ORDER BY SORT desc

DECLARE @ITEMID VARCHAR(20)
DECLARE @CROP VARCHAR(4)
DECLARE STK CURSOR FOR
SELECT DISTINCT ITEMID,CROP FROM @ST;
OPEN STK;
FETCH NEXT FROM STK INTO @ITEMID,@CROP;


WHILE @@FETCH_STATUS = 0

BEGIN
SET @Issuedstk =(Select SUM(Salqty)*-1 from #TEMPTB3 WHERE ITEMID=@ITEMID AND CROP=@CROP GROUP BY ITEMID,CROP)
UPDATE @st SET @RunningTotal = RunningTotal = @RunningTotal + PurQty FROM @st where ITEMID = @ITEMID AND CROP=@CROP
UPDATE @st SET AGING =ISNULL((case when RunningTotal<@Issuedstk then 0 when RunningTotal>@Issuedstk then RunningTotal-@Issuedstk end),0) where ITEMID = @ITEMID AND CROP=@CROP

--SET @Issuedstk =(Select SUM(Salqty)*-1 from #TEMPTB3 WHERE ITEMID=@ITEMID GROUP BY ITEMID)
UPDATE @st SET STAT = 'X' WHERE AGING<>0 AND ITEMID = @ITEMID AND CROP=@CROP and SORT IN (SELECT MAX((Sort)) FROM @ST WHERE AGING<>0 AND ITEMID = @ITEMID AND CROP=@CROP)

--SELECT MAX((Sort)) FROM @ST WHERE AGING<>0 AND ITEMID = @ITEMID
SET @Issuedstk=0

FETCH NEXT FROM STK INTO @ITEMID,@CROP;
SET @RunningTotal=0

END


CLOSE STK;
DEALLOCATE STK;
SELECT Sort, PurQty,SalQty, RunningTotal,itemid,bucket,AGING,ITEMGROUPID,STAT,ITEMNAME,UNITID,PRICE,CROP
FROM @st WHERE ITEMGROUPID = @ITEMGROUPID
ORDER BY Sort
 
DROP TABLE #TEMPTB
DROP TABLE #TEMPTB2
DROP TABLE #TEMPTB3