Slow moving Inventory report JD edwards

I create Slow moving Inventory report from F4111 - Cardex as following

CREATE proc pinvrep as
DROP TABLE JDE_RK.DBO.TEMPSALES4111
select ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO,max(ILUKID)ILUKID INTO JDE_RK.DBO.TEMPSALES4111
from JDE_PRODUCTION.proddta.f4111
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4101 on ILitm=imitm
--LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F41021 on ILitm=liitm and ilmcu=limcu and ILGLPT=liglpt
where ildct='RI' and ildcto in ('SO','SA','SF','SR','SI')
GROUP BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO
order BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO

DROP TABLE JDE_RK.DBO.TEMPRECVS4111
select ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO,max(ILUKID)ILUKID INTO JDE_RK.DBO.TEMPRECVS4111
from JDE_PRODUCTION.proddta.f4111
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4101 on ILitm=imitm
--LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F41021 on ILitm=liitm and ilmcu=limcu and ILGLPT=liglpt
where ildct='OV' and ildcto in ('O6','OW','OP','OH')
GROUP BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO
order BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO

--select count(*) from JDE_RK.DBO.TEMPSALES4111
drop table JDE_RK.DBO.TEMPSALES4111_1
select a.*, iltrqt,iltrdj into JDE_RK.DBO.TEMPSALES4111_1 from JDE_RK.DBO.TEMPSALES4111  a
left outer join  JDE_PRODUCTION.proddta.f4111 b on a.ilukid=b.ilukid
drop table JDE_RK.DBO.TEMPRECVS4111_1
select a.*,iltrqt,iltrdj  into JDE_RK.DBO.TEMPRECVS4111_1 from JDE_RK.DBO.TEMPRECVS4111  a
left outer join  JDE_PRODUCTION.proddta.f4111 b on a.ilukid=b.ilukid
--SELECT TOP 10 * FROM  JDE_RK.DBO.TEMPRECVS4111_1
       declare @y as varchar(10)
       declare @dy as varchar(10)       
       declare @date1 as datetime       
       declare @szupmj as varchar(10)
       declare @szupmjINT as int
       set @date1= getdate()
 set @y =(datepart(yy,@date1) - 1900) * 1000;
 set @dy = datepart(dy,@date1);
 set @szupmj = @y + cast(@dy as int);
        set @szupmjINT= cast(@szupmj as int)

DROP TABLE ZDEV.DBO.INV_REPORT

SELECT S.MCCO,LIITM,IMDSC1,LIMCU,LIGLPT,SUM(LIPQOH)LIPQOH,ISNULL(SUM(LIPQOH*B.COUNCS/10000),0)TOTCOST21, 
ISNULL(SUM(LIPQOH*C.COUNCS/10000),0)TOTCOST25,ISNULL(SUM(LIPQOH*D.COUNCS/10000),0)TOTCOST02,
R.IMSRP1,R.IMSRP2,R.IMSRP3,ISNULL(AVG(B.COUNCS)/10000,0) COST21,ISNULL(AVG(C.COUNCS)/10000,0) COST25,
ISNULL(AVG(D.COUNCS),0) COST02,
ISNULL(SUM(-E.ILTRQT),0) SALQTY,
--ISNULL(MAX(E.ILTRDJ),0) SALDAT,
DATEADD(dy, cast(MAX(E.ILTRDJ) as varchar(10)) % 1000, DATEADD(yy, cast(MAX(E.ILTRDJ) as varchar(10)) / 1000,-1)) SALDAT,
--@szupmjINT-ISNULL(MAX(E.ILTRDJ),0) SALDATAGA,
ISNULL(SUM(F.ILTRQT),0) RECQTY,
--ISNULL(MAX(F.ILTRDJ),0) RECDAT,
DATEADD(dy, cast(MAX(F.ILTRDJ) as varchar(10)) % 1000, DATEADD(yy, cast(MAX(F.ILTRDJ) as varchar(10)) / 1000,-1)) RECDAT
--@szupmjINT-ISNULL(MAX(F.ILTRDJ),0) RECDATAGA
into ZDEV.DBO.INV_REPORT
FROM JDE_PRODUCTION.PRODDTA.F41021 A
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4105 B ON LIITM=B.COITM AND B.COMCU=LIMCU AND B.COLEDG='21'
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4105 C ON LIITM=C.COITM AND C.COMCU=LIMCU AND C.COLEDG='25'
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4105 D ON LIITM=D.COITM AND D.COMCU=LIMCU AND D.COLEDG='02'
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4101 R on liitm=imitm
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F0006 S on limcu=S.mcmcu
LEFT OUTER JOIN JDE_RK.DBO.TEMPSALES4111_1 E on A.LIITM=E.ILITM AND A.LIMCU=E.ILMCU AND A.LIGLPT=E.ILGLPT  AND E.ILKCO=S.MCCO
LEFT OUTER JOIN JDE_RK.DBO.TEMPRECVS4111_1 F on A.LIITM=F.ILITM AND A.LIMCU=F.ILMCU AND A.LIGLPT=F.ILGLPT  AND F.ILKCO=S.MCCO
WHERE LIPQOH>0 GROUP BY LIITM,LIMCU,LIGLPT,R.IMSRP1,R.IMSRP2,R.IMSRP3,S.MCCO,IMDSC1

--select * from  ZDEV.INV_REPORT where saldat=0
--update ZDEV.INV_REPORT  set SALDATAGA=0 where saldat is null
--update ZDEV.INV_REPORT  set RECDATAGA=0 where RECDAT  is null
--SELECT *, ISNULL(DATEDIFF(dd,RECDAT,getdate()),0) RECDATAGA,ISNULL(DATEDIFF(dd,SALDAT,getdate()),0) SALDATAGA FROM ZDEV.INV_REPORT

 

GO