Troubleshooting - How to Apply payment to multiple Invoice

Problem Description:

How can a payment be applied to multiple invoices?

Scenario 1

- 3 Invoices are created.

- A payment is received that covers 2 of the invoices, but not necessarily in the order in which they were created.

How can I apply the payment to the invoices of my choosing?

Scenario 2

- 2 invoices are created

- payment received in the amount higher than the two invoices

I want to apply the payment to the two existing invoices and then use the remaining unused amount for the next Invoice that will be created later. How can I achieve this?

Solution:

You have already received Payments & Invoices created. Now, You want to apply the received payment to the desired Invoice.

As you want to link the Payment with the Invoice, You need to insert records in the ReceivePaymentLine table.

As per Scenario 1, You have 3 Invoices created & A payment is received that covers 2 of the Invoices. Now, You want to apply the amount to the desired Invoice.

Please refer below steps for applying the payment to multiple Invoices using QODBC. I am using the MS Access application for this example. You can use your desired application.

First, please create a new MS Access database & link the Invoice, InvoiceLine, receive payment, and ReceivePaymentLine tables.

Please refer to How to Use QODBC with Microsoft Access for more details.

After linking the table, we need to execute the query in the MS Access query editor:

MS Access >> Create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

Here, We first get the TxnID of the ReceivePayment using the below sample query.

SELECT TxnID, CustomerRefListID, RefNumber, TotalAmount FROM ReceivePayment where CustomerRefFullName = 'Easley, Paula.'

We have details for Customer payments.

Now, We will get the Invoice details from the InvoiceLine table using the below sample query.

SELECT TxnID, CustomerRefListID, RefNumber, SubTotal FROM InvoiceLine where CustomerRefFullName='Easley, Paula'

We have details for Customer Invoices.

We will use the above details & apply a payment to the 2 Invoice# 1108 & 1109 using the below sample query.

INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('2437F-1639567954', '24373-1639567874', 140.00 )

The payment was applied to Invoice# 1108.

We will apply a payment to Invoice# 1109 using the sample query below.

INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('2437F-1639567954', '24379-1639567907', 90.00 )

The payment was applied to Invoice# 1109.

Related Data Location:

TxnID = TxnID from the ReceivePayment table for the payment you received.

AppliedToTxnTxnID = TxnID from the Invoice table for which you want to apply for the payment.

AppliedToTxnPaymentAmount = Payment Amount which you want to apply for the Invoice.

Also, When the payment amount is higher than the Invoice amount & you have applied it to the Invoice. The remaining payment will be shown in the UnusedPayment or UnusedCredits fields of the ReceivePayment & ReceivePaymentLine table.