Production Schedule by Date with Price

[RahF]

Hello All

I need two queries and I am getting an error with it.

• Query to bring up all production orders open and closed scheduled for a given date range.

I have done the below, but I am getting an error

Can someone please have a look and advise where I am going wrong?

SELECT T0.[DueDate],

T0.[DocNum],

T0.[U_CustName],

T0.[PlannedQty],

T2.[Dscription],

T1.[PlannedQty],

T2.[LineTotal]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry, RDR1 T2

WHERE T0.[DueDate] = >[%0] T0.[DueDate] <[%1]

ORDER BY T0.[DocNum]

• I need another similar query but which shouldn’t display any production orders that don’t have the Item Code “labour “in the BOM

SELECT T0.[DueDate],

T0.[DocNum],

T0.[U_CustName],

T0.[PlannedQty],

T2.[Dscription],

T1.[PlannedQty],

T2.[LineTotal]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry, RDR1 T2

WHERE T0.[DueDate] = >[%0] T0.[DueDate] <[%1] T1.[ItemCode] IS NOT NULL[%2]

ORDER BY T0.[DocNum]

Thanks and regards

Rahul

[Former Member · Accepted solution]

Hi Rahul,

Try this:

SELECT T0.[DueDate], T0.[DocNum], T0.[U_CustName], T0.[PlannedQty], T3.[Dscription], T1.[PlannedQty], T3.[LineTotal] FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left join  ORDR T2 on T2.[DocNum]  = T0.[OriginNum]  left JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry and T3.ItemCode=T0.ItemCode WHERE T0.[DueDate] >= [%0] and  T0.[DueDate] <= [%1] ORDER BY T0.[DocNum]

Make sure you enter a longer duration at first.

Thanks,

Gordon

[RahF · Accepted solution]

Hi Gordon

I tried your query

But it is showing muliple entries and quantites in the results

The actual quantity is only 12 and the Total value 13831.20$

See attached

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[DueDate], T0.[DocNum], T0.[PlannedQty], T3.[Dscription], sum(T1.[PlannedQty]), T3.[LineTotal] FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left join  ORDR T2 on T2.[DocNum]  = T0.[OriginNum]  left JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry and T3.ItemCode=T0.ItemCode WHERE T0.[DueDate] >= [%0] and  T0.[DueDate] <= [%1] group by T0.[DueDate], T0.[DocNum], T0.[PlannedQty], T3.[Dscription],T3.[LineTotal]   ORDER BY T0.[DocNum]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Try:

SELECT T0.[DueDate], T0.[DocNum], T0.[U_CustName], T0.[PlannedQty], T3.[Dscription], T3.[LineTotal]

FROM OWOR T0

left join  ORDR T2 on T2.[DocNum]  = T0.[OriginNum]  left JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry and T3.ItemCode=T0.ItemCode WHERE T0.[DueDate] >= [%0] and  T0.[DueDate] <= [%1] ORDER BY T0.[DocNum]

[RahF · Accepted solution]

Hi Nagarajan

It displays the correct results if the production orders have been completed

But for production orders that haven't been completed

It is showing multiple results and incorrect row totals

Is there a way if displays the correct row total even if the production order isnt completed?

Thanks and regards

Rahul

[RahF · Accepted solution]

Hi Gordon

It displays the correct results if the production orders have been completed

But for production orders that haven't been completed

It is showing multiple results and incorrect row totals

Is there a way if displays the correct row total even if the production order isnt completed?

Thanks and regards

Rahul

[Former Member · Accepted solution]

Hi Rahul Fernandez,

Try to put production status in selection criteria in query...

[former_member325312]

Hi Rahul

Find the Query Below

SELECT T0.[DueDate],T0.[DocNum],T0.[U_CustName],T0.[PlannedQty],T3.[Dscription],

T1.[PlannedQty],T3.[LineTotal]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry,

ORDR T2  INNER JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.[DueDate] >='[%0]' AND  T0.[DueDate]<='[%1]'

AND T0.OriginNum = T2.DocNum

ORDER BY T0.[DocNum]

Regards

Jenny

[RahF]

HI Jennifer

Tried it

It displays the results for each production order 53 times

Thanks and regards

Rahul

[kothandaraman_nagarajan]

Hi,

1. There is no proper link between RDR1 and OWOR tables. If you run without correct join, system performance will be affected.

2. Try this query:

SELECT T0.[DueDate], T0.[DocNum], T0.[U_CustName], T0.[PlannedQty], T3.[Dscription], T1.[PlannedQty], T3.[LineTotal] FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left join  ORDR T2 on T2.[DocNum]  = T0.[OriginNum]  and T2.[CardCode]  = T0.[CardCode] INNER JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry WHERE T0.[DueDate] >= [%0] and  T0.[DueDate] < [%1] ORDER BY T0.[DocNum]

3. As per forum rule, one question per thread allowed. For second query,please post as new discussion.

Thanks & Regards,

Nagarajan

[RahF]

Hi Nagarajan

I ran the query you have written, but i get an error "No data was found as a result of the selection criteria

Thanx

[kothandaraman_nagarajan]

Hi,

Tested and getting query result in my database.

Input correct due date in above query.

Thanks & Regards,

Nagarajan

[RahF]

Hi Nagarajan

It's strange. I tried it again and it comes up with the same message.

I am inputting today's date for both inputs

Please see attached...

Thanx

[kothandaraman_nagarajan]

Hi,

Do you have link between production order and sales order? Please post screen shot production order window here.

Thanks & Regards,

Nagarajan

[RahF]

Hi

Yes the production orders are linked to the sales orders

See attached

Thanx

[kothandaraman_nagarajan]

Hi,

Just remove doc due date from above and then try.

Thank & Regards,

Nagarajan

[RahF]

Hi

Removed doc due date.

I still can't get any data on screen

Thnx