Customer Payments: Application Details

Returns the invoices to which a customer payment was applied, including sales order numbers, customer PO numbers, and the amounts applied.

-- Contributor: Tim Dietrich (timdietrich@me.com)
-- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customer-payment-application/
SELECT     
     Invoice.TranID AS InvoiceID,
     REPLACE( BUILTIN.DF( InvoiceMainLine.CreatedFrom ), 'Sales Order #', '' ) AS SONumber,
     Invoice.OtherRefNum AS CustomerPO,
     PTLL.ForeignAmount AS AmountPaid
FROM
     Transaction AS Payment
     INNER JOIN TransactionLine AS PaymentLine ON
          ( PaymentLine.Transaction = Payment.ID )
          AND ( PaymentLine.MainLine = 'F' )
     INNER JOIN PreviousTransactionLineLink AS PTLL ON
          ( PTLL.NextDoc = PaymentLine.Transaction )
          AND ( PTLL.NextLine = PaymentLine.ID )
          AND ( PTLL.LinkType = 'Payment' )     
     INNER JOIN TransactionLine AS InvoiceLine ON
          ( InvoiceLine.Transaction = PTLL.PreviousDoc )
          AND ( InvoiceLine.ID = PTLL.PreviousLine )
     INNER JOIN Transaction AS Invoice ON
          ( Invoice.ID = InvoiceLine.Transaction )
     INNER JOIN TransactionLine AS InvoiceMainLine ON
          ( InvoiceMainLine.Transaction = Invoice.ID )
          AND ( InvoiceMainLine.MainLine = 'T' )
WHERE
     ( Payment.ID = 999999 )

Click here to return to the list of available queries.