Production order – Issued and Received quantity with batch number

Dear all,

The below query is useful for production planner who want know the issued and/or received quantity for particular project  from production order.

Query:

SELECT

T0.[PostDate], T0.[DocNum], T0.[PlannedQty],case when (T3.[BaseType] = ’59’ and  T3.[Direction] = ‘0’ and T0.[ItemCode] = T3.[ItemCode] ) then  ‘Received’ end as Direction,T0.[ItemCode], T3.[Quantity], T3.[BatchNum], case when (T2.[BaseType] = ’60’ and T2.[Direction] = ‘1’ and T1.[ItemCode] = T2.[ItemCode] ) then ‘issued’ end Direction, T2.[Quantity], T2.[ItemCode], T2.[BatchNum]

FROM

OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left  join IBT1 T2 on T2.[BsDocEntry]  = t0.docentry and T2.[BsDocLine]  =  T1.[LineNum] left  join IBT1 T3 on T3.[BsDocEntry]  = t0.docentry and T0.[ItemCode] = T3.[ItemCode]

WHERE

T2.[BsDocType]  = ‘202’ and  (T0.[Status] = ‘R’ OR T0.[Status] = ‘L’ )  and  T0.[Project]  = [%0]

Hope it will be helpful.

Thanks & Regards,

Nagarajan