SAP Business One Training Tips #6

Finally, an Inventory Aging report by warehouse

SELECT T0.ITEMCODE , T0.ONHAND as ‘Total Qty’, CASE WHEN Datediff(day,

T1.LASTPURDAT,GETDATE())= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN

T0.ONHAND END ’45 to 90 Days(Qty)’ , CASE WHEN DateDiff(day,

T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90

THEN T0.ONHAND*T0.AVGPRICE END ’45 to 90 Days(Value)’, CASE WHEN Datediff(day,

T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END ‘>90 Days(Qty)’, CASE WHEN

Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND*T0.AVGPRICE END ‘>90

Days(Value)’

FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE INNER JOIN OITB T2

ON T1.ITMSGRPCOD=T2.ITMSGRPCOD WHERE T0.ONHAND>0 AND T0.WhsCode ='[%0]’
AND T2.ITMSGRPNAM = ‘[%2]’