SAP B1 Query - Inventory Aging

[larryenet]

Hello SAP Gurus,

Can someone please help update the following query so that it selects all warehouses by default? Right now when you run the query, the Query - Selection Criteria window pops open asking which Warehouse Code to select. I just want it to query all warehouses by default.

Here is the query:

SELECT distinct T0.ITEMCODE , T0.ONHAND as 'Total Qty', T1.AvgPrice 'Average Price', T1.[ManSerNum],


CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)',


CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T1.AVGPRICE END '<45 Days(Value)',


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*T1.AVGPRICE END '45 to 90 Days(Value)',


CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 90 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 180 THEN T0.ONHAND END '90 to 180 Days(Qty)' ,


CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 90 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 180 THEN T0.ONHAND*T1.AVGPRICE END '90 to 180 Days(Value)',


CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND END '>180 Days(Qty)',


CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND*T1.AVGPRICE END '>180 Days(Value)'


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


INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join ibt1 t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode


WHERE


T0.WhsCode ='[%0]'

[kothandaraman_nagarajan]

Hi,

try this,

SELECT distinct T0.ITEMCODE , T0.ONHAND as 'Total Qty', T1.AvgPrice 'Average Price', T1.[ManSerNum], CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T1.AVGPRICE END '<45 Days(Value)', 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*T1.AVGPRICE END '45 to 90 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 90 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 180 THEN T0.ONHAND END '90 to 180 Days(Qty)' , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 90 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 180 THEN T0.ONHAND*T1.AVGPRICE END '90 to 180 Days(Value)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND END '>180 Days(Qty)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND*T1.AVGPRICE END '>180 Days(Value)' FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join ibt1 t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode WHERE (T0.WhsCode ='[%0]' or '[%0]' = ' ')

Regards,

Nagarajan

[larryenet]

I figured it out. Too bad the old website is gone. This would have been answered within 5 minutes.