Item Cost of Items on a particular date

[Former Member]

HI All,

We need to get the item cost of items on a particular date.

I have tested the following but it gives multiple results:

SELECT T0.ItemCode, Max(T0.Dscription) 'Item Name',T0.CalcPrice

FROM OINM T0

WHERE T0.DocDate = [%0]

GROUP BY T0.ItemCode, T0.Calcprice

I have checked the audit report too but it shows the current item cost.

is there a way to get the desired data?

Thanks,

Joseph

[Former Member]

Hi Joseph,

What is your inventory revaluation method? It will be different to get the item cost by query depending on the method.

Thanks,

Gordon

[Former Member]

Hi,

I think you cannot get the item cost of an item on a particular date on the OITM table. it only shows the cost as of the current date. it also challenged me when i was doing the query. have you tried my query though? cause its working fine with me.

Regards,

[Former Member]

Hi Joseph,

Try this. I've been using this and giving me correct details.

SELECT

T3.WhsCode 'Whse Code', T3.WhsName 'Whse Name', T0.[ItemCode], T1.[ItemName],

sum(T0.[InQty] - T0.[OutQty]) 'OnHand', O2.CalcPrice/sum(T0.[InQty] - T0.[OutQty]) 'Cost'

FROM OINM T0 

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

INNER JOIN OWHS T3 ON T0.[Warehouse] = T3.WhsCode

INNER JOIN

(

SELECT DISTINCT o.Warehouse, o.ItemCode, o.Balance CalcPrice

FROM OINM O  INNER JOIN

(

  SELECT T0.[Warehouse] 'Whse', T0.[ItemCode] 'Code', MAX(T0.[CreateDate]) 'cDate' , MAX(T0.TransNum) '#'

  FROM OINM T0 WHERE T0.[CreateDate] <= '[%1]'

  GROUP BY T0.[Warehouse], T0.[ItemCode]

) O1 ON O.Warehouse = O1.Whse and O.ItemCode = O1.Code and O.CreateDate = O1.cDate and O.TransNum=O1.#

) O2 ON T0.Warehouse = O2.Warehouse and T0.[ItemCode] = O2.ItemCode

WHERE

T0.[CreateDate] <= '[%1]'

and T1.[ItemName] = '[%2]'

GROUP BY T3.WhsCode, T3.WhsName, T0.[ItemCode], T1.[ItemName], O2.CalcPrice

HAVING (sum(T0.[InQty] - T0.[OutQty]) <> 0)

ORDER BY T3.WhsCode, T0.[ItemCode]

you can also modify it to fit your needs.

Regards,

[kothandaraman_nagarajan]

Hi,

How are you? Hope doing well.

Please clarify:

Are you looking for item cost or calculated price? If I run above query getting  two rows of result with different items for particular date.

Thanks & Regards,

Nagarajn

[Former Member]

Hi Nagarajan,

i'm doing just fine

Hope the same for you!

I'm looking for Item Cost on a particular date (31-03-2014).

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi,

If you want item cost, then you have query OITM table not OINM table.

Thanks & Regards,

Nagarajan

[Former Member]

With OITM, how can I get the item cost on a particular date??

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi,

Please refer thread:

http://scn.sap.com/thread/3429755

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagaraj,

I got the data from the inventory audit report... Used cumulative qty and value as on 31-03-14.. Based on these, I was able to derive Item cost as on 31-03-14..

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi,

Thanks for feedback.

Thanks & Regards,

Nagarajan