Query - Sales Orders linked to Deliveries

[Former Member]

Hello all,

I have come up with the below query that displays details of sales orders due for delivery on a selected future date (user input). I would like to add an extra column that displays delivery numbers (ODLN.DocNum) in case the sales order has been copied to an A/R Reserve invoice and then to a Delivery. NOTE: we use AR Reserve invoices for future deliveries and AR Invoices for same day deliveries.

SELECT T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName], T1.[Dscription], T3.[FrgnName],T3.[U_Item_Colour],  T1.[Quantity], T2.[SlpName],T5.[descript] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode INNER JOIN OTER T5 ON T4.Territory = T5.territryID WHERE T0.[DocDueDate] = "%0"

My attempt to improve the above query by adding OINV and linking RDR1.TrgetEntry = OINV.DocNum has not been very successful so far as the results are pulling out wrong data - Old Deliveries. Kindly assist improve the below query to pull out correct data.

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName], T1.[Dscription], T3.[FrgnName],T3.[U_Item_Colour],  T1.[Quantity], T2.[SlpName],T5.[descript],t6.docnum,t8.docnum FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode full outer JOIN OITM T3 ON T1.ItemCode = T3.ItemCode left JOIN OCRD T4 ON T0.CardCode = T4.CardCode left JOIN OTER T5 ON T4.Territory = T5.territryID right JOIN OINV T6 ON T6.docnum = T1.trgetentry left JOIN INV1 T7 ON T7.baseentry = t0.docnum left JOIN ODLN T8 ON T4.CardCode = T8.CardCode left join dln1 t9 on  t9.baseentry=t6.docnum WHERE T0.[DocDueDate] =[%0] and t9.trgetentry is null

Thank you in advance.

Henry

[Former Member · Accepted solution]

Hi Henry,

Try:

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName],

T1.[Dscription], T3.[FrgnName], T1.[Quantity],

T2.[SlpName],T5.[descript],T7.docnum 'Invoice #',T9.docnum 'Delivery#', T0.DocNum 'SO#'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode

LEFT JOIN OTER T5 ON T4.Territory = T5.territryID

LEFT join dln1 T8 on T8.baseentry = t0.docentry and t1.linenum = T8.baseline

LEFT join odln T9 on T9.docentry = T8.docentry

LEFT JOIN INV1 T6 ON T6.BaseEntry=T9.DocEntry

LEFT JOIN OINV T7 ON T7.DocEntry=T6.DocEntry

WHERE  T0.[DocDueDate] =[%0] and t8.trgetentry is null

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon and kennedy,

thanks for your prompt responses. However, just like I was experiencing before, your queries are displays correct and wrong results in some instances i.e. when I follow up linked documents using base/target documents, at times am getting different documents from what the report displays. The report in some instances is also displaying A/R Invoices instead of A/R Reserve Invoices.

How about we eliminate OSLP, OCRD, OITM and OTER tables and just try and link the remaining tables?

Thanks and kind regards,

Henry

[KennedyT21 · Accepted solution]

Hi Henry Njagi

Try This

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

T1.[Dscription], a5.docnum,a3.docnum

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

inner join dln1 a2 on a2.baseentry = t0.docentry and t1.linenum = a2.baseline

inner join odln a3 on a3.docentry = a2.docentry

LEFT JOIN INV1 a4 ON a4.BaseEntry=a3.DocEntry

LEFT JOIN OINV a5 ON a5.DocEntry=a4.DocEntry

WHERE T0.[DocDueDate] =[%0]

Regards

Kennedy

[Former Member]

Try:

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

T1.[Dscription], T3.docnum,T5.docnum

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN INV1 T2 ON T2.BaseEntry=t0.DocEntry and t2.BaseType = '17'

INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry AND T3.IsIns = 'Y'

LEFT join dln1 T4 on T4.baseentry = t0.docentry and t1.linenum = T4.baseline AND BaseType = '13'

LEFT join odln T5 on T5.docentry = T4.docentry

WHERE T0.[DocDueDate] =[%0]

[Former Member]

Hi Gordon,

Thank you very much. OINV.IsIns='Y' (Reserve Invoices only) did the trick. I modified your query slightly in the bold fonts area as below

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

T1.[Dscription], T3.docnum,T5.docnum,t3.docstatus

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN INV1 T2 ON T2.BaseEntry=t0.DocEntry and t2.BaseType = '17'

INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry AND T3.IsIns = 'Y'

full outer join dln1 T4 on T4.baseentry = t3.docentry

full outer join odln T5 on T5.docentry = T4.docentry

WHERE T0.[DocDueDate] ='%0'

I then further added the remaining tables as below to pull out all the fields I wanted.

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName],

T1.[Dscription], T3.[FrgnName], T1.[Quantity],

T2.[SlpName],T5.[descript],T7.docnum 'Invoice #',T9.docnum 'Delivery#', T0.DocNum 'SO#'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

full JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

full JOIN OCRD T4 ON T0.CardCode = T4.CardCode

LEFT JOIN OTER T5 ON T4.Territory = T5.territryID

full join inv1 t6 on t6.baseentry=t0.docentry and t6.basetype='17'

full join oinv t7 on t7.docentry=t6.docentry and t7.isins='y'

full outer join dln1 t8 on t8.baseentry = t7.docentry

full outer join odln t9 on t9.docentry=t8.docentry

WHERE  T0.[DocDueDate] =[%0]

Thanks and kind regards,

Henry

[former_member204969]

Try to start with this:

Select

  OO.[DocDueDate], OO.[CardName],OO.[DocNum],

  T3.[SuppCatNum], O.[Dscription], T3.[FrgnName], T3.[U_Item_Colour], O.[Quantity],

  T2.[SlpName],T5.[descript],DD.docnum Delivery,II.docnum Invoice

From RDR1 O inner join ORDR OO on O.DocEntry=OO.DocEntry

   INNER JOIN OSLP T2 ON OO.SlpCode = T2.SlpCode

   full outer JOIN OITM T3 ON O.ItemCode = T3.ItemCode

   left JOIN OCRD T4 ON OO.CardCode = T4.CardCode

   left JOIN OTER T5 ON T4.Territory = T5.territryID

   left outer join DLN1 D on D.Basetype=17 and D.BaseEntry=O.DocEntry and D.BaseLine=O.LineNum

   left outer join ODLN DD on D.DocEntry=DD.DocEntry

   left outer join Inv1 I on (I.Basetype=15 and I.BaseEntry=D.DocEntry and I.BaseLine=D.LineNum)

                          or (I.Basetype=17 and I.BaseEntry=O.DocEntry and I.BaseLine=O.LineNum)

   left outer join OINV II on I.DocEntry=II.DocEntry

[Former Member]

Thanks guys for your efforts so far. However, none of the queries are yielding perfect results. I want to restrict my report to documents linked as below:

Sales Order --> A/R Reserve Invoice --> Delivery

i.e. Sales order details with the last column of the report indicating a Delivery number if the delivery has already been raised, if not, the delivery number field should be empty. The queries so far are displaying results for the above documents flow and for the normal flow as below:

Sales Order --> Delivery --> A/R Invoice

Thanks and regards,

Henry

[KennedyT21]

Try This

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

T1.[Dscription], a5.docnum,a3.docnum

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN INV1 a4 ON a4.BaseEntry=t0.DocEntry

LEFT JOIN OINV a5 ON a5.DocEntry=a4.DocEntry

inner join dln1 a2 on a2.baseentry = t0.docentry and t1.linenum = a2.baseline

inner join odln a3 on a3.docentry = a2.docentry

WHERE T0.[DocDueDate] =[%0]

Regards

Kennedy

[KennedyT21]

Hi  Henry Njagi

Try This

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName],

T1.[Dscription], T3.[FrgnName],T3.[U_Item_Colour],  T1.[Quantity],

T2.[SlpName],T5.[descript],a5.docnum,a3.docnum

FROM ORDR T0 

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

full outer JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

left JOIN OCRD T4 ON T0.CardCode = T4.CardCode

left JOIN OTER T5 ON T4.Territory = T5.territryID

inner join dln1 a2 on a2.baseentry = t0.docentry and t1.linenum = a2.baseline

inner join odln a3 on a3.docentry = a2.docentry

LEFT JOIN INV1 a4 ON a4.BaseEntry=a3.DocEntry

LEFT JOIN OINV a5 ON a5.DocEntry=a4.DocEntry

WHERE T0.[DocDueDate] =[%0]

Hope helpful

Regards

Kennedy