Skip to Content
0
Nov 09, 2018 at 08:31 AM

SAPB1 Query for Inventory Trends by Week

159 Views Last edit Nov 09, 2018 at 07:04 AM 2 rev

Hello Experts,

I'm looking for a query which will give me closing inventory weekly for the past 8 weeks. We are trying to see inventory trends and compare to back-order demand. Since we are unable to fulfill orders for product that is not in stock we typically will not process the SO and we lose visibility on the demand. This will help us improve visibility on our inventory trends.

I wrote the following query but its not returning any results when trying to bin the ending inventory 7 days ago.

Please help

SELECT

OITW.WhsCode

,OITM.ItemCode

,OITM.ItemName AS 'Description'

,OITM.QryGroup1 AS 'AFM'

,OITM.QryGroup7 AS 'CurrentMY'

,SUM(OITW.OnHand) AS 'On Hand'

,SUM(OITW.OnOrder) AS 'On Order'

,SUM(OITW.IsCommited) AS 'Committed',

(SELECT SUM(OINM.InQty-OINM.OutQty) 'On Hand' FROM OINM WHERE OINM.DocDate = DATEADD(day,-7, GETDATE()) AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode, OINM.DocDate) AS '7 Days ago'

FROM OITM, OITW

WHERE OITM.ItemCode=OITW.ItemCode

and OITW.WhsCode = '01'

and (OITW.OnHand > 0 or OITW.OnOrder > 0 or OITW.IsCommited > 0)

and OITM.QryGroup1 = 'Y'

and OITM.QryGroup7 = 'Y'

GROUP BY

OITW.WhsCode

, OITM.ItemCode

, OITM.ItemName

, OITM.QryGroup1

, OITM.QryGroup7

, OITM.CardCode

, OITM.AvgPrice

ORDER BY OITM.ItemCode