Material Consumption Report

Query for Material Consumption Reports

SELECT T1.[ItemCode], T1.[Dscription],T2.[CardCode],T5.[CardName],Sum(T1.[Quantity])[Required For Month Order],Sum(T3.[Quantity])[Consumption],Sum(T1.[Quantity])-Sum(T3.[Quantity])[Net   Requirement],T2.[MinOrdrQty],T2.[MinLevel],T2.[OrdrIntrvl],T6.[Name],T2.[OrdrMulti],

T2.[OnHand],T2.U_MinInvDays, ( Sum(T1.[Quantity])-Sum(T3.[Quantity]))/24 [Coverage Days]

FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

  Inner join oitm T2 on T1.ItemCode=T2.ItemCode

Inner join IGE1 T3 On T1.ItemCode=T3.ItemCode

Inner Join OIGE T4 ON T0.DocDate=T4.DocDate

inner join OCRD T5 On T2.CardCode=T5.CardCode

inner join OCYC T6 ON T2.[OrdrIntrvl]=T6.Code

WHERE (

datepart(month,T0.[DocDate] ) >= [%0] Or

datepart(month,T4=.[DocDate] )

>= [%0]) and  (

datepart(month,T0.[DocDate] )

<= [%1] OR

datepart(month,T4.[DocDate] )

<= [%1])

Group By T1.[ItemCode], T1.[Dscription],T2.[OnHand], T2.[CardCode],T5.CardName, T2.[MinLevel], T2.[OrdrIntrvl],T6.Name, T2.[OrdrMulti], T2.[MinOrdrQty],T2.U_MinInvDays

———————————–

/* 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