Hello Experts
We have items that sometimes need to be worked on in the production area and we manage it by using Labour in the production order
Then we have items which come in complete and don't need to be worked on and since it won't be going into production we don't use Labour in the production order
I have written a query which result should tell me the date the production orders are scheduled time allocated and so on
The query works but I am not getting all the info I need
The results of the query only display's the production orders with Labour in it, it doesn't display the production order without labour
Can someone please check my query and tell me what am I doing wrong
I need the results to display all production orders in the date range, with and without labour
SELECT
T0.[DueDate] as 'Build Date',
T0.[DocNum] as 'Prod Ord',
T0.[U_CustName] as 'Sales Partner',
T0.[PlannedQty] as 'Qty',
T0.[ItemCode] as 'Product No',
T2.[ItemName] as 'Product Description',
T1.[PlannedQty] as 'Time',
T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
T0.[Status] as 'Prod Status',
T4.[ReqDate] as 'Required Date',
T5.[ShipDate] as 'Despatch Date',
T5.[LineTotal] as 'Row Value'
FROM
OWOR T0 INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]
LEFT JOIN ORDR T4 ON T4.[DocNum] = T0.[OriginNum]
LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]
WHERE
T1.[ItemCode] <> 'SUNDRY LABOUR' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'SUNDRY LABOUR') and
T1.[ItemCode] <> 'OUTSOURCED' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'OUTSOURCED') and
T0.[DueDate] between [%0] and [%1] and
T1.[ItemCode] = 'LABOUR' and
T0.[Status] not in ( 'C')
GROUP BY
T0.[DocNum], T0.[DueDate], T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T1.[PlannedQty], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate], T5.[LineTotal]
ORDER BY
T0.[DueDate], T0.[DocNum]
Have another question, I have a column that shows me the status. This status only shows the letter L for closed, C for cancelled and so on
Is it possible to show the whole word in the result Closed instead of just L?
Thanks and Regards
Rahul