Knowledgebase: QODBC
[QODBC-Desktop] Troubleshooting - How to Apply payment to multiple Invoice
Posted by Jack - QODBC Support on 07 March 2018 12:18 PM

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 2 invoices

I would like to apply the payment to the 2 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 received payment to the desired Invoice.

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

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

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

First of all please create new MS Access database & link Invoice, InvoiceLine, ReceivePayment, ReceivePaymentLine table.

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

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

MS Access >> Create tab >> Query Design >> It should popup for choosing table, please click "close" button & click on "SQL" from left top corner below File menu.

Here, We first get TxnID of the ReceivePayment using 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 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 Payment to the 2 Invoice# 1108 & 1109 using below sample query.

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

The payment applied to Invoice# 1108.

Now, We will apply Payment to the Invoice# 1109 using below sample query.

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

The payment applied to Invoice# 1109.

Related Data Location:

TxnID = TxnID from the ReceivePayment table for the Payment which you have received.

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

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

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

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).