cancel
Showing results for 
Search instead for 
Did you mean: 

open production order report query

Former Member
0 Kudos

Dear Experts,

I want Open Production order report query which give following information.

Production Order No., Product No. Product Description, order date, planned quantity, completed quantity.

Thanks & Regards,

Akshata

View Entire Topic
Mahesh6050
Newcomer
0 Kudos

Here is Error Free Query 


SELECT
T0."DocEntry",
T0."DocNum",
T0."PostDate",
T0."DueDate",
(CASE
WHEN T0."Status" = 'L' THEN 'Closed'
WHEN T0."Status" = 'R' THEN 'Released'
WHEN T0."Status" = 'C' THEN 'Cancelled'
END) AS "Status",
T0."ItemCode",
T2."ItemName",
T0."PlannedQty",
T0."CmpltQty",
T0."RjctQty",
T1."ItemCode",
T3."ItemName",
T1."PlannedQty",
T1."IssuedQty",
T1."VisOrder" AS "Row"
FROM OWOR T0
LEFT OUTER JOIN WOR1 T1 ON T1."DocEntry" = T0."DocEntry"
LEFT OUTER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"
LEFT OUTER JOIN OITM T3 ON T3."ItemCode" = T1."ItemCode"
WHERE T0."PostDate" >= '[%0]'
AND T0."PostDate" <= '[%1]'
AND T1."VisOrder" = 0

UNION ALL

SELECT
T0."DocEntry",
NULL AS "DocNum",
NULL AS "PostDate",
NULL AS "DueDate",
NULL AS "Status",
NULL AS "ItemCode",
NULL AS "ItemName",
NULL AS "PlannedQty",
NULL AS "CmpltQty",
NULL AS "RjctQty",
T1."ItemCode",
T3."ItemName",
T1."PlannedQty",
T1."IssuedQty",
T1."VisOrder" AS "Row"
FROM OWOR T0
LEFT OUTER JOIN WOR1 T1 ON T1."DocEntry" = T0."DocEntry"
LEFT OUTER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"
LEFT OUTER JOIN OITM T3 ON T3."ItemCode" = T1."ItemCode"
WHERE T0."PostDate" >= '[%0]'
AND T0."PostDate" <= '[%1]'
AND T1."VisOrder" > 0
ORDER BY 1, 14; -- DocEntry is the 1st column and VisOrder is the 14th column