How to gather an Invoice, related payments, and the "applied_to" subrecords for those payments in h SuiteQL

Question

I'm having a heck of a time with this. Because SuiteQL uses Analytics Browser schema, it doesn't match up to a lot of NetSuite information out there, which is often in context of the direct ODBC schema.

Via REST I am trying to collect Sales Orders, Purchase Orders, and Invoices to display in Salesforce for a given Opportunity in Salesforce. I am able to retrieve the IDs via a SuiteQL query and can then use their respective end points to get the details (e.g. /services/rest/record/v1/salesorder/2374577 where 2374577 is the Sales Order ID).

My problem is with payments - I can get the ones related to an invoice just fine, but the "total amount" on the payment may not have fully been applied to that one invoice, it may have been spread across invoices.. So I need to get the "amount applied to this invoice".

I am currently using this SuiteQL query but it doesn't give me the breakdown per invoice I need:

SELECT *, NT.TranDate, NT.TranID, REPLACE( BUILTIN.DF( NT.Status ), BUILTIN.DF( NT.Type ) || \' : \' ) AS Status, NT.ForeignTotal, NT.exchangerate FROM NextTransactionLineLink AS NTLL INNER JOIN Transaction AS NT ON ( NT.ID = NTLL.NextDoc ) WHERE ( NTLL.PreviousDoc = \'2373356\' ) AND NT.Type = \'CustPymt\' ORDER BY NT.TranDate ASC

It seems like this is an ODBC approach (below) to getting that information but the "transaction_links" table/view doesn't seem to be available in SuiteQL.. So I'm not sure where to turn to at this point.

"t1".TRANSACTION_ID , t1."TRANSACTION_NUMBER", t1."TRANSACTION_TYPE"
,ln1.amount Invoice_Amount
,xref."AMOUNT_LINKED" Applied_Amount, xref."APPLIED_TRANSACTION_ID", xref."ORIGINAL_TRANSACTION_ID", xref."APPLIED_DATE_POSTED"
, t2."TRANSACTION_ID" t2_tranid, t2."TRANSACTION_NUMBER" t2_trannbr, t2."TRANSACTION_TYPE" t2_trantype
,ln2.amount Payment_Amount

from
"ns_transactions_raw" t1
join "ns_transaction_lines_raw" ln1
on t1."TRANSACTION_ID" = ln1."TRANSACTION_ID"
join
"ns_transaction_links_raw" xref
on t1."TRANSACTION_ID" = xref."ORIGINAL_TRANSACTION_ID"
and ln1."TRANSACTION_LINE_ID" = xref."ORIGINAL_TRANSACTION_LINE_ID"
join "ns_transactions_raw" "t2"
on "t2"."TRANSACTION_ID" = "xref"."APPLIED_TRANSACTION_ID"
join "ns_transaction_lines_raw" ln2
on ln2."TRANSACTION_ID" = "xref"."APPLIED_TRANSACTION_ID"
and ln2."TRANSACTION_LINE_ID" = "xref"."APPLIED_TRANSACTION_LINE_ID"
where t1."TRANSACTION_TYPE" = 'Invoice'
and t2."TRANSACTION_NUMBER" = 'PMT028551'

Answer 1

I was able to get this working via the NextTransactionLineLink table. This is my SuiteQL query, where objectId is the id of the invoice I want to get payments for. This works for all kinds of cash transactions, too, such as deposit applications, credit memos, etc:

SELECT NT.ID, NT.TranDate, NT.TranID, REPLACE( BUILTIN.DF( NT.Status ), BUILTIN.DF( NT.Type ) || \' : \' ) AS Status, NT.ForeignTotal, NT.exchangerate, NTLL.ForeignAmount FROM NextTransactionLineLink AS NTLL INNER JOIN Transaction AS NT ON ( NT.ID = NTLL.NextDoc ) WHERE ( NTLL.PreviousDoc = objectId ) ORDER BY NT.TranDate ASC

The ForeignAmount field on the NextTransactionLineLink record is the value of the part of the payment that applies to this invoice.