Published on November 04, 2024.

This SuiteQL query returns the invoices to which a customer payment was applied. The corresponding sales order number is returned, along with the customer's PO number, and the amount applied.

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 = 7518243 )