How to Receive a Payment not be Applied to an Invoice ( How to handle Customer credits on account )

Note: Below is how to receive a payment from a customer that will not be applied to an invoice. In other words, it will be a credit on account, or a deposit received. This will be applied later, once the services have been rendered and the invoice is generated. Also, about how to apply for that credit to account to the invoice at that time.

Instruction of a Received Payment has not Invoice Applied

In QuickBooks

     In QuickBooks, when you receive a payment against a customer that has no outstanding invoices as follows:

the Payment Credit isn't a Credit Memo:

In QODBC

     But a ReceivePaymentToDeposit line within QODBC found by doing:

SELECT TxnID, CustomerRefFullName, RefNumber, Amount FROM ReceivePaymentToDeposit
where CustomerRefFullName='Zuniga, Daphne'

Note:  VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

Table Schema of ReceivePaymentToDeposit

     Examination of the ReceivePaymentToDeposit table using:

sp_columns ReceivePaymentToDeposit

shows the table to be read-only, meaning we can't insert or update the table directly. To get around this we need to first receive the payment and then apply it to the invoice later.

TO RECEIVE THE PAYMENT FOR THE CUSTOMER WITHOUT ANY INVOICES

Locate Customer ListID

     To do this using QODBC you first you need to locate the ListID for the customer by doing:

Select * from Customer where FullName='Data Access Worldwide'

     Here for the "Data Access Worldwide" customer ListID is AE0000-1197765289.

Receive the payment for the customer without any invoices

INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID,
PaymentMethodRefListID, DepositToAccountRefListID, TotalAmount,
IsAutoApply) VALUES ('AE0000-1197765289', '40000-933270541',
'20000-933270334', '80000-933270541', 500.00, TRUE)

Locate Transaction ID for this payment

     The transaction ID for the payment we just did is found by doing:

SP_LASTINSERTID ReceivePayment

It returned a TxnID of: 5C29-1197772163

Locate ReceivePaymentToDeposit Line

The insert also created a ReceivePaymentToDeposit line found by doing:

Select * from ReceivePaymentToDeposit where TxnID='5C29-1197772163'

CREATE THE INVOICE

Create an Invoice using QODBC

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AE0000-1197765289', 'PRE-PAID1', '250000-933272656',
'Bin Permit Renovations', 500.00000, 500.00, '20000-999022286', 0)

Locate Transaction ID for the Invoice

     To locate the Transaction ID for the Invoice you can do:

select TxnID, RefNumber from InvoiceLine where CustomerRefListID = 'AE0000-1197765289'

The TxnID of the Invoice is: 5C2C-1197773093

TO APPLY THE PAYMENT TO THE INVOICE OR APPLY CREDITMEMO TO THE INVOICE

Apply the Payment to the Invoice using QODBC

     So now there's an invoice you can do a ReceivePaymentLine to apply that payment to the Invoice and credit the unapplied payment!

For existing Credit Memos, you can use the CreditMemo TxnID and apply it in the AppliedToTxnSetCreditCreditTxnID column in the ReceivePaymentLine insert for payments:

INSERT INTO ReceivePaymentLine (CustomerRefListID, ARAccountRefListID,
AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID,
AppliedToTxnSetCreditAppliedAmount) VALUES ('AE0000-1197765289',
'40000-933270541','5C2C-1197773093', 500.00, '5C29-1197772163', 500.00)

Display in QuickBooks

     The Invoice now displays as PAID in QuickBooks.

Data Location Information

Where:
CustomerRefListID = 'AE0000-1197765289' (Customer ListID - Required)
DepositToAccountRefListID = '80000-933270541' (Undeposited Funds Account ListID)
ARAccountRefListID = '40000-933270541' (Accounts Receivable Account ListID)
AppliedToTxnTxnID = '5C2C-1197773093' (Find TxnID in Invoice Table)
AppliedToTxnSetCreditCreditTxnID = '5C29-1197772163' (Find TxnID in ReceivePaymentToDeposit Table) OR (Find TxnID in CreditMemo Table)

Troubleshooting

Cannot Locate Transaction ID

     When you could not get the transaction ID information, please try doing as below:

sp_optimizefullsync ReceivePaymentLine

sp_optimizefullsync ReceivePaymentToDeposit

to resync your optimized ReceivePaymentLine and ReceivePaymentToDeposit tables.

    Once that's done, do:

Select * From ReceivePaymentLine WHERE CustomerRefListID = '21D0000-1160000365'

Select * From ReceivePaymentToDeposit WHERE CustomerRefListID = '21D0000-1160000365'

and try finding the transactions you are looking for.

How to apply multiple Invoices to a Payment

     You need to write multiple inserts using the "FQSaveToCache". Multi-line transactions require a series of SQL statements to complete a single transaction. The key to this process is the field named "FQSaveToCache". This field is not part of the table but is used as a flag to the QODBC driver. The value of "FQSaveToCache" is set to 1 or TRUE for the insert statements and then it is set to 0 or FALSE for the final insert statement for the transaction.

See: How to create multiple lines in the Invoice, Purchase and Journal table for examples.

And try looking at: How do I create a new Customer, invoice them and mark the invoice as paid? for the normal method of paying an existing invoice.

How to Apply Credit to an Invoice

I require customers to submit a deposit of when they open their account and some customers pre-pay for service or submit frequent deposits. I would like these credits to be applied to their invoices when the invoice is created.

My insert statement seems to work if the invoiced amount is <= to the unused payment amount for the payment. But does not work if the invoiced amount is > then the unused payment amount. In the case where the invoiced amount > unused payment amount, I need to apply for the remaining credit and send the customer the invoice for the remaining amount.

Here is my insert statement:

INSERT INTO ReceivePaymentLine(CustomerRefListID, ARAccountRefListID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount) 
VALUES (?, '80000020-1236740661', ?, ?, ?, ?) 

As an example:

invoice number=20097106
invoice transaction id=543CF-1409233024
invoice amount = 90
payment id=543B2-1409169998
unused payment amount=87.12
customer id=800000A7-1298568764

The insert statement would them be:

INSERT INTO ReceivePaymentLine (CustomerRefListID,ARAccountRefListID,AppliedToTxnTxnID,AppliedToTxnPaymentAmount,AppliedToTxnSetCreditCreditTxnID,AppliedToTxnSetCreditAppliedAmount) 
VALUES ('800000A7-1298568764', '80000020-1236740661', '543CF-1409233024', 87.12, '543B2-1409169998', 87.12) 

This statement fails with the following error: "Error 3180 - There was an error when saving a ReceivePayment. QuickBooks error message: Transaction not in balance. 

This particular customer had another payment of $2999, so when I try the insert with the remaining payment > invoiced amount it works:

 
invoice number=20097106
invoice transaction id=543CF-1409233024
invoice amount = 90
payment id=543BD-1409177019
unused payment amount=2999
customer id=800000A7-1298568764 

INSERT INTO ReceivePaymentLine (CustomerRefListID,ARAccountRefListID,AppliedToTxnTxnID,AppliedToTxnPaymentAmount,AppliedToTxnSetCreditCreditTxnID,AppliedToTxnSetCreditAppliedAmount)
VALUES ('800000A7-1298568764','80000020-1236740661','543CF-1409233024',90.0,'543BD-1409177019',90.0)

What do I need to do to have the credit successfully apply when invoiced amount > the unused payment amount?

Solution:

I would like to inform you that when credit amount is less than Invoice amount & you want to apply for the credit, then you need to put zero value in "AppliedToTxnPaymentAmount" column and put the credit value "AppliedToTxnSetCreditAppliedAmount" column. So you can resolve Transaction not in Balance error.

You can see the same from QuickBooks UI when you apply for credit, the Payment textbox will remain zero.

Please refer below query:

INSERT INTO ReceivePaymentLine (CustomerRefListID, ARAccountRefListID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount) 
VALUES ('800000A7-1298568764', '80000020-1236740661', '543CF-1409233024', 0, '543B2-1409169998', 87.12)

Keywords : Recive, how to make payment through invoice using qodbc