I am trying to join the SalesLine table and the CustInvoiceTrans. I currently have:
FROM SalesLine sl
LEFT JOIN CustInvoiceTrans cit ON sl.SalesId = cit.SalesId
and cit.itemid = sl.itemid
The issue with this is if there were multiple shipments then this isn't a unique join and results in duplication.
The issue is I have two sales lines with different amounts:
line 1: SalesId:23 | itemid 1234 | Qty 12 | Amt 880
line 5: SalesId:23 | itemid 1234 | Qty 6 | Amt 440
now in the invoice line transaction table there is multiple invoices for the shipments
Invoice:1 | SalesId:23 | itemid 1234 | Qty 8 | Amt 550 --Now this is related to line 1
Invoice:15 | SalesId:23 | itemid 1234 | Qty 6 | Amt 440 --Now this is related to line 5
but there is no unique join from invoice to sales line
How do I get a one to one between line 5 and invoice transaction 15
This is what I have currently
SELECT sl.SALESID
,sl.LINENUM
, CONVERT(INT, sl.QTYORDERED) AS 'SalesQty'
, sl.INVENTTRANSID
, cpst.PACKINGSLIPID
, CONVERT(INT, cpst.QTY) AS 'PackingSlipQty'
, cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE
, cipsqm.INVOICESOURCEDOCUMENTLINE
, cit.INVOICEID
,cit.LINENUM
FROM SALESTABLE AS st
JOIN SALESLINE AS sl
ON st.SALESID = sl.SALESID
AND st.PARTITION = sl.PARTITION
AND st.DATAAREAID = sl.DATAAREAID
LEFT JOIN CUSTPACKINGSLIPTRANS AS cpst
ON sl.INVENTTRANSID = cpst.INVENTTRANSID
AND sl.PARTITION = cpst.PARTITION
AND sl.DATAAREAID = cpst.DATAAREAID
LEFT JOIN CustInvoicePackingSlipQuantityMatch AS cipsqm
ON cpst.SOURCEDOCUMENTLINE = cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE
LEFT JOIN CUSTINVOICETRANS AS cit
ON cipsqm.INVOICESOURCEDOCUMENTLINE = cit.SOURCEDOCUMENTLINE
where sl.SALESID = 'SO-034460'
order by sl.SALESID
,sl.LINENUM
Try joining it by InventTransId. That should give you a single line, unless you have a partial invoice let's say the salesLine qty is 30 and you only invoice 10 and at a later time you invoice the remaining 20, in this case you will see 2 lines, both related to the same sales line.