I know I've seen this before but I can't find it.
I need to show the Sales Order Number on AR Invoices. We create AR Invoices from both Sales Orders and Delivery Notes. Only one document gets copied in per invoice. If the source was a SO, I can get it. If the souce was a DN, I need to track it back to the SO. Here's my code start:
select oinv.DocNum, oinv.DocEntry,
DLN1.DocEntry AS DNDocEntry, odln.DocNum as DN,
rdr1.DocEntry AS SODocEntry, ordr.DocNum as SO
from OINV join inv1 on oinv.DocEntry = inv1.docentry
left outer join dln1 on inv1.BaseEntry = dln1.DocEntry and inv1.BaseLine = dln1.LineNum and inv1.BaseType= 15
join ODLN on dln1.docentry = odln.docentry
left outer join rdr1 on inv1.BaseEntry = rdr1.DocEntry and inv1.BaseLine = rdr1.LineNum and inv1.BaseType= 17
join ORDR on rdr1.DocEntry = ordr.docentry
where
inv1.ItemCode is not null
and inv1.BaseType <> -1
but it isn't returning any rows.