cancel
Showing results for 
Search instead for 
Did you mean: 

Help With Query To Check Monthly Build

RahF
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi Rahul,

Please remove condition T1.[ItemCode] = 'LABOUR'  and then run again.


Thanks

RahF
Participant
0 Kudos

Hi Nagarajan

I can't remove the condition T1.[ItemCode] - 'LABOUR'

I am identifying the condition T1.[PlannedQty] as 'Time' using it

In a production order in the WOR1 table, we have all the components and one of those components is Labour.

If I remove it, how can I get the query result to display the quantity as time?

Please see attached

Thanks and Regards

Rahul

gaurav_bali
Active Participant
0 Kudos

Hi Rahul

Try bellow query

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

(select S1.[PlannedQty] from WOR1 S1 where S1.DocEntry = T0.DocEntry and S1.ItemCode = 'LABOUR') as 'Time',

T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Canceled' end 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

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]

kothandaraman_nagarajan
Active Contributor
0 Kudos

Based on your query condition, it will display those production order which is having labour item will be displayed.

If you want to get all productions with or without labor item, you should remove Itemcode = 'labour' from your query.

For planned time, Isnull(T1.[PlannedQty]),0) as time --> Meaning those production order without labor displayed as 0 time

RahF
Participant
0 Kudos

Hi Gaurav

When I click on Execute nothing happens

Thanks

Rahul

RahF
Participant
0 Kudos

Hi Nagarajan

Yes I think I have the condition wrong

When I remove ItemCode = Labour, the query result displays all the components in the production orders

Thanks and Regards

Rahul

kothandaraman_nagarajan
Active Contributor
0 Kudos

Yes correct. Issue solved?

Former Member
0 Kudos

Hello Nagarajan.

How do you write this Query.

RahF
Participant
0 Kudos

Hi Nagarajan

No that isn't the result I am after

I think Gaurav is on the right track, but it's not working

There is something wrong in the below he wrote

(select S1.[PlannedQty] from WOR1 S1 where S1.DocEntry = T0.DocEntry and S1.ItemCode = 'LABOUR') as 'Time',

I think if the above works that would be the result I am after

I have attached screen caps of my results

One with the Labour condition and one without

The one without the labour condition, shows multiple results if there is an labour Item code in the production order

Thanks and Regards

Rahul

gaurav_bali
Active Participant
0 Kudos

here is the query I didn't tested it first time. I had to remove some UDFs though hope you can convert rest to your needs.

SELECT

T0.[DueDate] as 'Build Date',T0.[DocNum] as 'Prod Ord',T0.[PlannedQty] as 'Qty',T0.[ItemCode] as 'Product No',T2.[ItemName] as 'Product Description',

(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry) as 'Time',T0.[Comments] as 'Remarks',T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Canceled' end 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 '20140101' and '20150401' and

T0.[Status] not in ( 'C')

GROUP BY

T0.[DocNum], T0.[DueDate], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T1.[PlannedQty], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],

T5.[LineTotal],T1.DocEntry

ORDER BY

T0.[DueDate], T0.[DocNum]

RahF
Participant
0 Kudos

Hi Gaurav

Tried it, it will not execute the query

I don't know if i am doing something wrong

Thanks and Regards

Rahul

gaurav_bali
Active Participant
0 Kudos

Hi Rahul

Replace T0.[DueDate] between '20140101' and '20150401' with T0.[DueDate] between [%0] and [%1]


and it should work.

RahF
Participant
0 Kudos

Hi Gaurav

I tried it but i am getting an (SWEI) error?

The below is what I have done...

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

(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry) as 'Time',

T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Cancelled' end 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

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],T1.DocEntry

ORDER BY

T0.[DueDate], T0.[DocNum]


Thanks and Regards

Rahul

gaurav_bali
Active Participant
0 Kudos

HI Rahul

Here is the complete Query Please check it should work with out any error

Declare @FDate datetime

Declare @TDate Datetime

Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]

Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]

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

(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'manpower' GROUP BY S1.DocEntry) as 'Time',T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',

T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'

FROM OWOR T0

INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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

T0.[DueDate] between @Fdate and @Tdate and

T0.[Status] not in ( 'C')

GROUP BY

T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],

T5.[LineTotal],T1.DocEntry

ORDER BY

T0.[DueDate], T0.[DocNum]

RahF
Participant
0 Kudos

Hi Gaurav

It just does not want to work

This time has come up with a different error

Conversation Failed when converting the nvarchar value MANUALLY to data type int. (WLS1)

Thanks

gaurav_bali
Active Participant
0 Kudos

The error comes due to error in conversion most probably in [U_Prod_Order_No]. the field needs to contain numbers only for linking To DocNum

run the query

select * from RDR1 where [U_Prod_Order_No] = 'Manually'

If Query returns any result you can add condition to remove the docentry from query or update SalesOrder Rows

Also Please update S1.ItemCode = 'manpower' To 'Labour' I forgot to update it.

RahF
Participant
0 Kudos

Hi Gaurav

I updated S1.ItemCode ='manpower' to LABOUR and ran the query again

Got the same error

So i ran the select * from RDR1 where [U_Prod_Order_No] = 'Manually'

The query returned a lot of results. What do I need to modify in the query?

gaurav_bali
Active Participant
0 Kudos

Replace the line

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]

with

LEFT JOIN (select * from RDR1 S0 where S0.[U_Prod_Order_No] not in ('Manually')) T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]

The line will remove the lines from RDR1 containing Manually. Hope it solves the problem.

Also prefer to keep one datatype per field for consistency it will help to avoid lots of errors.

RahF
Participant
0 Kudos

Hi Gaurav

When you say the line will remove the lines from RDR1 containing Manually

Will it delete something in a Sales Order???

gaurav_bali
Active Participant
0 Kudos

NO Business one doesn't allows queries for Update via query manager. When I say "the line will remove the lines from RDR1 containing Manually", it means from the Out put report.

RahF
Participant
0 Kudos

Ok I made the changes

But I still get an error, see attached

gaurav_bali
Active Participant
0 Kudos

Use following Line also update all the values in Not in  if others are present. This can be long and tedious if there are more such values. In such case It would be advisable not to use RDR1 on line leve at all

LEFT JOIN (select * from RDR1 S0 where S0.[U_Prod_Order_No] not in ('Manually','185-0744') T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]

alternatively if you are using 'Procurement confirmation Wizard' or sales order number field in production order is updated you can use following line for mapping RDR1 to OWOR

LEFT JOIN ORDR T4 ON T4.[DocEntry] = T0.[OriginAbs]

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode

RahF
Participant
0 Kudos

HI Gaurav

I don't get it, the 185-0744 is actually the product code not a production order number

production order numbers are 6 digit numbers eg 651897

may be my initial left join is wrong

gaurav_bali
Active Participant
0 Kudos

The field U_Prod_Order_No contains values other than number which is why error is coming try

LEFT JOIN ORDR T4 ON T4.[DocEntry] = T0.[OriginAbs]

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode

Instead This should work Fine.

gaurav_bali
Active Participant
0 Kudos

Try this

Declare @FDate datetime

Declare @TDate Datetime

Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]

Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]

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

(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry) as 'Time',T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',

T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'

FROM OWOR T0

INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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.[DocEntry] = T0.[OriginAbs]

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode

WHERE

T0.[DueDate] between @Fdate and @Tdate and

T0.[Status] not in ( 'C')

GROUP BY

T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],

T5.[LineTotal],T1.DocEntry

ORDER BY

T0.[DueDate], T0.[DocNum]

RahF
Participant
0 Kudos

Hi Gaurav

It works, I need to run it and check the data

The production row that don't have labour show as blank

Is it possible to show them as zeros?

Thanks and Regards

Rahul

kothandaraman_nagarajan
Active Contributor
0 Kudos

Use ISNULL function to show blank as zero. For example,

Isnull(Field,0)

RahF
Participant
0 Kudos

Hi Gaurav

I checked the result data from the query

For some reason some of the rows are repeating and show in as multiple rows in the result

See attached

RahF
Participant
0 Kudos

Hi Nagarajan

Thanks for that

I added isnull to the query and the result now shows blanks and zeros

isnull((select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry),0)

Thanks & Regards

Rahul

kothandaraman_nagarajan
Active Contributor
0 Kudos

But i think still you are not getting required result from the query?

RahF
Participant
0 Kudos

Hi Gaurav

Nagrajan has helped me fix the blanks to show as zero

but the result shows some rows multiple times

See attached

RahF
Participant
0 Kudos

The query is working, but for some reason some rows are showing in multiples

gaurav_bali
Active Participant
0 Kudos

Hi Rahul

The query is working fine for me can you post full screen shot showing full row data where duplicate values are coming.

RahF
Participant
0 Kudos

Hi Gaurav

I don't know why it won't work for me

I have attached screen shots

Below is the query. The only modification is the isnull that i added to it

Declare @FDate datetime

Declare @TDate Datetime

Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]

Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]

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

Isnull((select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry),0) as 'Time',T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',

T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'

FROM OWOR T0

INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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.[DocEntry] = T0.[OriginAbs]

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode

WHERE

T0.[DueDate] between @Fdate and @Tdate and

T0.[Status] not in ( 'C')

GROUP BY

T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],

T5.[LineTotal],T1.DocEntry

ORDER BY

T0.[DueDate], T0.[DocNum]

gaurav_bali
Active Participant
0 Kudos

Hi Rahul

Please note that the rows with duplicate values contains different values for Dispatch Date and Row Value. The group by will work only for rows with same values.

If you want to display sales data as per Row then the query is showing correctly.

Incase you want data in single row then I would suggest either you remove the Dispatch Date and add Row Values or remove both fields.

RahF
Participant
0 Kudos

Hi Gaurav

I removed the Dispatch date but I am getting the same result

So i checked the duplicate results against the sales order

Row 45 & 46

The sales order has two rows, different production orders,quantities and despatch dates but same product code

In the query result the despatch dates and the row value are the only things correct, everything else it shows what is in the last despatch date in that sales order

Same think with row 50 & 51 and all the other duplicates

If the sales order has the same product code in different rows, the query result is shows all the rows but the only thing correct is the despatch dates and row values

See attached

gaurav_bali
Active Participant
0 Kudos

That explains a lot. the sales order contains 2 or more rows per document which links all the rows to same production order. Try the query I converted docnum to nvarchar hope it works.

Declare @FDate datetime

Declare @TDate Datetime

Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]

Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]

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

Isnull((select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry),0) as 'Time',T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

case when T0.[Status] = 'P' then 'Planned'

when T0.[Status] = 'R' then 'Released'

when T0.[Status] = 'L' then 'Closed'

when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',

T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'

FROM OWOR T0

INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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.[DocEntry] = T0.[OriginAbs]

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = convert(nvarchar(50),T0.[DocNum])

WHERE

T0.[DueDate] between @Fdate and @Tdate and

T0.[Status] not in ( 'C')

GROUP BY

T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],

T5.[LineTotal],T1.DocEntry

ORDER BY

T0.[DueDate], T0.[DocNum]

RahF
Participant
0 Kudos

Hi Gaurav

Seems to work. I can't see any duplicates

I will check the data and let you know

In the query results there are no arrows to link the production order

Is it possible to get the arrows back?

gaurav_bali
Active Participant
0 Kudos

To add link Button add T0.Docentry in select as well as group by statement

RahF
Participant
0 Kudos

Hi Gaurav

Sorry had gone off the radar for a bit

I added the T0.DocEntry and when I run the query it shows the Internal Number

Is it possible to have an arrow on the T0.DocNum column?

Thanks

gaurav_bali
Active Participant
0 Kudos

Hi Rahul

The link button appears automatically, mostly on primary keys,

Personally I think the link button points to records hence they cannot be on the column which can contain duplicate values.

Mostly theses are Key fields and predefined. There is no way to create or add them to query manager.

Golden Arrow In Query | SCN

RahF
Participant
0 Kudos

Hi Gaurav

Thanks for that

I think I can work with the result without the arrow

Thanks

Rahul