The production manager in my company requires a report which should have the fields exactly as described in the sql I have written so far -
SELECT Distinct T0.[DocNum] as 'Production Order No.', T0.[Type] as 'Production Order Type', T0.[ItemCode], T0.[U_WOHCONS], T0.[PlannedQty], T0.[CmpltQty], T0.[CreateDate] as 'Creation Date - Production order', T0.DueDate as 'Due Date - Production', T2.[DocDate] as 'Date Produced', T0.[Status], T0.[OriginNum], T4.DocNum, T4.[CardCode] as 'Customer No.', T4.[CreateDate] as 'Creation Date - Sales Order', T4.[DocDueDate] as 'Due Date - Sales', T6.[DocDate] as 'Delivery Date' FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry, OIGN T2 INNER JOIN IGN1 T3 ON T2.DocEntry = T3.DocEntry, ORDR T4 INNER JOIN RDR1 T5 ON T4.DocEntry = T5.DocEntry, ODLN T6 INNER JOIN DLN1 T7 ON T6.DocEntry = T7.DocEntry WHERE T3.[BaseEntry] = T1.[DocEntry] and T3.[ItemCode] = T0.[ItemCode] and T3.[BaseType] = 202 and T7.[BaseEntry] = T5.[DocEntry] and T7.[BaseLine] = T5.[LineNum] and T7.[ItemCode] = T5.[ItemCode] and T7.[BaseType] = 17 and T0.[OriginNum] = T4.[DocNum] and T0.[ItemCode] = T5.[ItemCode]
The problem I am encountering is that the report is not giving the full analysis - for example: Not all the production orders has Sales Order on it - so only those which have, can be shown on report. Also,the prod order in planned status with no delivery note can not be displayed.
So what I have developed is a very basic report. I think it can be modified by using OUTER JOIN s?
Thanks & Regards,