Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Query - Sales Orders linked to Deliveries

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

Tags:
Former Member
Former Member replied

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

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question