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
Thank you! Great report. Here is the HANA equivalent. Cheers
SELECT
T0."PostDate",
T0."DocNum",
T0."PlannedQty",
CASE
WHEN (T3."BaseType" = '59' AND T3."Direction" = '0' AND T0."ItemCode" = T3."ItemCode") THEN 'Received'
END "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 OUTER JOIN IBT1 T2 ON T2."BsDocEntry" = T0."DocEntry" AND T2."BsDocLine" = T1."LineNum"
LEFT OUTER 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]