Hi , I am trying to prepare a report that would retrieve all the Sales Invoices along with its Payment Schedule linking those installments to the Payment details (Apply Section). The business team wants an export for all the collected amounts to identify patterns in the past as of a certain date. Our schedule per customer can span more than a decade, so tracking collection history is critical.
I found that the Sales Invoice schedule is stored in the VendorBillInstallment ! (but I couldn't figure why) , Anyway I couldn't find any way to link the TransactionLine (for Payment Screen Apply ) to the VendorBillInstallment
Tried using the catalog but I was only able to find VendorBillInstallment table but nothing else and I am not sure how does netsuite display the installment information on the Payment screen along with installment # and due date.
Here is the Query I have been using
SELECT invoice.tranId, invoice.duedate, installment.seqnum, installment.duedate, installment.amount, paymentline.*, linkToLine.*
FROM Transaction invoice
INNER JOIN VendorBillInstallment installment ON invoice.id = installment.vendorbill
INNER JOIN NextTransactionLineLink linkToPayment ON linkToPayment.previousdoc = invoice.id
INNER JOIN Transaction payment ON payment.id = linkToPayment.nextdoc AND linkToPayment.nexttype = 'CustPymt'
INNER JOIN TransactionLine paymentLine ON payment.ID = paymentLine.Transaction /* Missing AND here to join VendorBillInstallment to TransactionLine */
WHERE invoice.Type = 'CustInvc'
AND invoice.tranid = 'INV-S1-B24-11'