0

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
Jed
  • 929
  • 2
  • 19
  • 32
  • 2
    I don't understand? That's correct behavior. If you have 1 sales line with a qty of 100, you might pack/ship/invoice 50 pieces, then later pack/ship/invoice the remaining 50. Is there are reason you're doing this directly in SQL? And what version of AX are you running? Have you looked at `CustInvoiceJour`? More importantly...*what are you trying to do exactly*? – Alex Kwitny Nov 13 '20 at 01:44
  • When translating AX queries to SQL queries do not forget the `Partition` and `DataAreaId` fields! They are useful for index usage _and_ you avoid combining data from different companies. – Sander Nov 13 '20 at 08:57
  • @AlexKwitny I have updated with an example – Jed Nov 13 '20 at 13:53
  • @Sander its one company so this will not make it more unique – Jed Nov 13 '20 at 13:57

1 Answers1

2

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.

Luis Piedra
  • 36
  • 1
  • 2
  • I do have partial invoices for each shipment so this would not work for me – Jed Nov 13 '20 at 19:23
  • @Jed Then you will have to aggregate the data in the invoice lines. But Luis Piedra is right, `InventTransId` is used to determine which invoice lines correspond to which sales lines, similar to what you do for `CustPackingSlipTrans` in the SQL statement in the question. – FH-Inway Nov 14 '20 at 12:46
  • Ended up linking on (COALESCE(CAST(cpst.packingslipid AS VARCHAR(200)), '') + '-' + COALESCE(CAST(sl.inventtransid AS VARCHAR(200)), '')) and on the invoice I used CustInvoicePackingSlipQuantityMatch table to get the packing slip id – Jed Jul 07 '22 at 19:01