Skip to Content
0
Former Member
Jun 05, 2011 at 02:20 AM

Show sales order number on AR Invoice

278 Views

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.