Historical Inventory Valuation

[candidochris]

Hello, I'm looking to find the value of an item on a specific date and cannot seem to find a built in SAP report to get this information.

For instance, I need to see the quantity in stock for all items that are in stock for a specific date and their avg price.

So, I'd like to see what my inventory valuation on 12-31-2012 was on an item level, i.e

ItemCode	AvgPrice	QtyOnHand
12345	33.33	17
45678	39.22	5
8910	25.00	1

Is there an easy way to do this through the UI? If not, any ideas on a query? -- OINM?

[Former Member · Accepted solution]

Hi

Run Inventory Audit Report by System date.

GLU

[candidochris · Accepted solution]

Thank you - what should I set my date parameters to?

[Former Member]

Hi Chris.,

Select  AA.ItemCode,ISNULL( AA.InQty,0)InQty , isnull(BB.OutQty,0) OutQty , (isnull(AA.InQty,0) - 

isnull(BB.OutQty,0) ) OpenQty from (

select ItemCode,isnull(SUM(isnull(Quantity,0) ),0) InQty  from IBT1_LINK B   

where   B.Direction = 0 and B.DocDate<='12/31/2012'

Group by  ItemCode) AA 

LEFT OUTER Join (

select ItemCode,isnull(SUM(isnull(Quantity,0) ),0) OutQty  from IBT1_LINK B   

where   B.Direction = 1 and B.DocDate<='12/31/2012'

Group by  ItemCode)  BB on AA.ItemCode =BB.ItemCode

You will use the above Query

Hope helpful

Thanks & Regards

Madeswaran P

[KennedyT21]

Hi Chris Candido ...

Try This Query

The only thing I can recommend is to run a query on OITW to get the cost price for each Item in each warehouse.

SELECT T0.ItemCode, T0.WhsCode, T0.AvgPrice

FROM OITW T0

ORDER BY T0.ItemCode, T0.WhsCode

Please note that this will not work for Items that are managed by FIFO as their cost price is not kept in OITW.

To get teh next open layer for each FIFO item per warehouse and the cost price that is going to be used, run trhe query below, the query will only contain FIFO items:

SELECT

MIN(T0.transnum), itemcode, warehouse, openqty, calcprice

FROM OINM T0

WHERE

openqty <> '0'

Group by itemcode, warehouse, openqty, calcprice

Last Evaluated Price is also updated after running the Inventory Audit Report.

Hope Helpful

Regards

Kennedy