Help With Open Production Order Query

[RahF]

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

[gaurav_bali · Accepted solution]

Hi Rahul

Try below query

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]

  INNER JOIN OWOR T2 ON ISNULL(T1.U_Prod_Order_No, -1) = T2.DocNum

WHERE

T1.[ShipDate] = [%0] and

T2.[Status] in ('P','R')

ORDER BY

T0.[CardName],

T0.[DocNum]

Please Note that Status for production order is as below hence Query was returning Blank records.

P	Planned
R	Released
L	Closed
C	Canceled

[RahF · Accepted solution]

Hi Gaurav

It works - Thanks a lot

I can't believe i got the status wrong

Thanks and Regards

Rahul

[former_member320372]

Hi,

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],

  Left JOIN OWOR T2 ON T1.U_Prod_Order_No = T2.DocNum

WHERE

T1.[ShipDate] = [%0] and

T2.[Status] = 'O'

ORDER BY

T0.[CardName],

T0.[DocNum]

Cheers!

[RahF]

Hi Prabakaran

Sorry it doesn't work. I am getting an error

Thanks

Rahul

[Johan_H]

Hi Rahul,

Your have guessed correctly, you are using RDR1 twice, and you have joined each intance separately.

Please give this a try:

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],

  INNER JOIN OWOR T2 ON ISNULL(T1.U_Prod_Order_No, -1) = T2.DocNum

WHERE

T1.[ShipDate] = [%0] and

T2.[Status] = 'O'

ORDER BY

T0.[CardName],

T0.[DocNum]

Regards,

Johan

[RahF]

Hi Johan

Thanks for the reply

I tried it, now i am not getting an error, but i am also getting no result

The query returns a blank screen

Thanks

Rahul

[Johan_H]

Hi Rahul,

As I do not have the U_Prod_Order_No field (or similar) I cannot test the query.

Please try the following:

1. find an example Sales Order, where the Production Order exists. In other words, find a case that you would expect to show up in the query result.
2. to the WHERE clause add: AND T0.DocNum = TheNumberOfYourTestCaseSalesOrder
3. The query will probably return an empty result set. Check all parameters / requirements as stated in the WHERE clause. Does the test case comply with all requirements ?
4. Check that the values in the fields that you used in the joins match up. Especially does T1.U_Prod_Order_No precisely match up with T2.DocNum ?

Regards,

Johan