cancel
Showing results for 
Search instead for 
Did you mean: 

Production Order Batch Query

kedalenechong
Participant
0 Kudos

Hi All

Please help to improve this Production Batch Query as it is not showing Supplier Purchase Order Number and Supplier Name?

SELECT T1."DocNum", T1."ItemCode", T1."Project", T1."StartDate", T1."DueDate", T2."ItemCode", T0."ItemName", T0."BatchNum", TRIM(SUM((CASE WHEN T0."Direction" = 1 THEN -T0."Quantity" ELSE T0."Quantity" END))) as "CONSUMED_QTY", T5."DocNum" as PO, T0."BsDocLine" as "Job_Row", T2."CardCode", T2."CardName", T3."BsDocType", T3."BsDocEntry", T3."BsDocLine" FROM IBT1 T0 INNER JOIN OWOR T1 ON T0."BsDocType" = T1."ObjType" AND T0."BsDocEntry" = T1."DocEntry" LEFT JOIN OIBT T2 ON T0."ItemCode" = T2."ItemCode" AND T0."BatchNum" = T2."BatchNum" INNER JOIN IBT1 T3 ON T2."BaseType" = T3."BaseType" and T2."BaseEntry" = T3."BaseEntry" and T2."BaseNum" = T3."BaseNum" and T2."BaseLinNum" = T3."BaseLinNum" LEFT JOIN POR1 T4 ON T3."BsDocType" = T4."ObjType" and T3."BsDocEntry" = T4."DocEntry" and T3."BsDocLine" = T4."LineNum" LEFT JOIN OPOR T5 ON T4."DocEntry" = T5."DocEntry"

GROUP BY T1."DocNum", T1."ItemCode", T1."Project", T1."StartDate", T1."DueDate", T2."ItemCode", T0."ItemName", T0."BatchNum", T0."BsDocLine", T2."CardCode", T2."CardName", T3."BsDocType", T3."BsDocEntry", T3."BsDocLine", T5."DocNum" HAVING TRIM(SUM((CASE WHEN T0."Direction" = 1 THEN -T0."Quantity" ELSE T0."Quantity" END))) <> '0.000000'

ORDER BY T1."Project", T0."BsDocLine", T0."BatchNum"

Kedalene Chong

Accepted Solutions (0)

Answers (0)