Skip to Content
0
Jul 28, 2016 at 01:26 AM

Help With Open Production Order Query

292 Views

Hello Experts

I need a query that will display a list of production orders that are still open and scheduled to go out on a date

In a sales order we have rows that are scheduled to go out on different dates

Each row has a production order for the product to be built

What i need is a say if i put in the ship date as 01.08.2016

It should display production orders that are scheduled to be shipped on the 01.08.2016 (not build date as they are built few days earlier) but haven't been built yet and the production orders are still open

I have written a query, but i am getting an error. I think i made a mistake in the joins

SELECT

T0.[DocNum] as 'S/O #',

T1.[ShipDate] as 'Ship Date',

T0.[CardName] as 'Sales Partner',

T1.[U_Prod_Order_No] as 'Prod.Ord#',

T1.[OpenQty] as 'Qty',

T1.[ItemCode] as 'Item Code',

T1.[Dscription] as 'Item Description'

FROM

ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry],

OWOR T2 INNER JOIN RDR1 T1 ON T2.DocNum = T1.U_Prod_Order_No

WHERE

T1.[ShipDate] = [%0] and

T2.[Status] = 'O'

ORDER BY

T0.[CardName],

T0.[DocNum]

Can someone please check where i am going wrong and advise

Thanks and Regards

Rahul