Material consumption Report

[Former Member]

Dear experts,

I need a raw material consumption report for monthly wise and also item wise i need to run the report is it possible. Pls suggest me a query how can

Thanks in advance

[KennedyT21 · Accepted solution]

Hi Arthi Raj...

There are lot of thread with the same discussion check the links..

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

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

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

Regards

Kennedy

[kothandaraman_nagarajan]

Hi.

Please refer below threads:

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

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

Thanks & Regards,

Nagarajan

[Former Member]

Hi Arthi,

try this query

SELECT datename(mm,T0.[DocDate])+'/'+datename(yy,T0.[DocDate])as 'Month/Year', T0.[ItemCode], T0.[Dscription],

          sum(T0.[InQty])-sum(T0.[OutQty])as 'Consumption Qty',Avg(T0.[CalcPrice])as 'Avg Rate',sum(T0.[TransValue])as'Amount',

          convert(character(2),(max(datepart(mm,T0.[DocDate]))))+'/'+convert(character(4),(max(datepart(yy,T0.[DocDate]))))

          FROM OINM T0 WHERE T0.[TransType] in (60,59)

          GROUP BY T0.[ItemCode], T0.[Dscription],datename(mm,T0.[DocDate])+'/'+datename(yy,T0.[DocDate]),convert(character(2),(datepart(mm,T0.[DocDate])))+'/'+convert(character(4),(datepart(yy,T0.[DocDate])))

          order by 7,2

regards,

Raviraj

[KennedyT21]

Hi  Aarthi Raj...

Try This

Try This

/* select * from oinm  t1  */

DECLARE @D1 DATETIME

DECLARE @D2 DATETIME

DECLARE @D3 NVARCHAR(100)

SET @D1=/* t1.DocDATE */ '[%1]'

SET @D2=/* t1.DocDATE */ '[%2]'

SET @D3=/* T1.itemcode */ '[%3]'

SELECT  x.Month As Month, X.CODE , sum(X.Grpo)As Grpo , Sum(x.Prodissue)As Production,Diff=(sum(X.Grpo) - Sum(x.Prodissue)) FROM (

SELECT month(t1.DocDate) As Month , T1.ITEMCODE aS coDE,

CASE WHEN TransType='20' THEN SUM(T1.INQTY) ELSE '0' END AS grpo,'0' aS Prodissue

FROM OINM T1

WHERE TransType='20'  and t1.DocDate between @D1 and @D2 and t1.ItemCode=@d3

GROUP BY T1.ITEMCODE,TransType,month(t1.DocDate)

UNION ALL

SELECT month(t1.DocDate) As Month ,  T1.ITEMCODE,'0',

CASE WHEN TransType='60' THEN SUM(T1.OUTQTY) ELSE '0' END AS Prodissue

FROM OINM T1

WHERE TransType='60'  and t1.applobj='202'  and t1.DocDate between @D1 and @D2  and t1.ItemCode=@d3

GROUP BY T1.ITEMCODE,TransType,month(t1.DocDate) 

) AS X

group by X.CODE,x.Month

Regards

Kennedy