I am trying to write the SQL to find Sales Orders, and all the sales line items, with the Purchase Order information (also by line item). I can retrieve the Purchase Orders when they are linked together using the Procurement Wizard, but for Purchase Orders that are created without the wizard and are "referenced" to the Sales Order using the RefDocNum field in the POR21 table, I cannot get those lines to be retrieved and display properly.
This query displays the line, but duplicates them and only for the properly 'linked' PO's. What am I doing wrong in the table joins?
Here's my Query so far: (there are some UDF's in there as well)
SELECT T0.[DocNum] as 'Sales Order', r.linetotal as 'SO Line Amount', t1.linetotal as 'PO Line Amount', t0.docstatus as 'SO Status', t2.canceled, t2.docnum as 'PO #', t2.cardname as 'Vendor', r.u_TrackingNum, r.u_freightconame, T0.[CardCode] as 'Customer', T0.[CardName] as 'Customer Name', T0.[DocDate] as 'SO Date', T0.[DocDueDate] as 'SO Due Date', r.linenum + 1 as 'Line', r.itemcode as 'SO Item', t1.itemcode as 'PO Item', r.dscription, r.u_polineDel, r.ocrcode as 'Contract'
FROM ORDR T0
inner join rdr1 r on r.docentry = t0.docentry
inner join por1 t1 on t1.baseref = T0.[DocNum] and t1.baseline = r.linenum
inner join POR21 t3 on t3.refdocnum = t0.docnum
inner join opor t2 on t2.docentry = t1.docentry
WHERE t2.docnum is not null
order by t0.docnum, r.linenum + 1
