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 used later once the services have been rendered and the Invoice is generated. Also, how to apply for that credit to account for the Invoice at that time?

Instruction of a Received Payment has not to 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 was found by doing the following:

SELECT TxnID, CustomerRefFullName, RefNumber, Amount FROM ReceivePaymentToDeposit
where CustomerRefFullName='Joe Mama Inc 2', and txnid='8BC1-1481803687'

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Table Schema of ReceivePaymentToDeposit

     Examination of the ReceivePaymentToDeposit table using:

sp_columns ReceivePaymentToDeposit

It shows the table to be read-only, meaning we can't insert or update the table directly. To get around this, we need first to 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 need to locate the ListID for the customer by doing the following:

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

     Here for the "Data Access Worldwide," customer ListID is 8000007D-1481804760.

Receive the payment for the customer without any invoices

INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID,
PaymentMethodRefListID, DepositToAccountRefListID, TotalAmount,
IsAutoApply) VALUES ('8000007D-1481804760', '50001-896816252',
'30000-898307887', '20000-896814722', 500.00, TRUE)

Locate the Transaction ID for this payment

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

SP_LASTINSERTID ReceivePayment

It returned a TxnID of 8BD5-1481805044

Locate ReceivePaymentToDeposit Line

The insert also created a ReceivePaymentToDeposit line found by doing the following:

Select * from ReceivePaymentToDeposit where TxnID='8BD5-1481805044'

CREATE THE INVOICE

Create an Invoice using QODBC

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('8000007D-1481804760', 'PRE-PAID1', '8A0003-1044570142',
'Bin Permit Renovations', 500.00000, 500.00, '20000-1011136881', 0)

Locate the Transaction ID for the Invoice

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

select TxnID, RefNumber from InvoiceLine where CustomerRefListID = '8000007D-1481804760'

The TxnID of the Invoice is: 8BD8-1481806252

TO APPLY THE PAYMENT TO THE INVOICE OR APPLY A CREDIT MEMO 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 amount!

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 ('8000007D-1481804760',
'50001-896816252','8BD8-1481806252', 500.00, '596B-1046217641', 500.00)

Display in QuickBooks

     The Invoice now displays as PAID in QuickBooks.

Data Location Information

Where:
CustomerRefListID = '8000007D-1481804760' (Customer ListID - Required)
DepositToAccountRefListID = '20000-896814722' (Undeposited Funds Account ListID)
ARAccountRefListID = '50001-896816252' (Accounts Receivable Account ListID)
AppliedToTxnTxnID = '8BD8-1481806252' (Find TxnID in Invoice Table)
AppliedToTxnSetCreditCreditTxnID = '5967-1046217547' (Find TxnID in ReceivePaymentToDeposit Table) OR (Find TxnID in CreditMemo Table)

Troubleshooting

Cannot Locate Transaction ID

     When you cannot 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 the following:

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

     It would help if you wrote 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 usual method of producing an existing invoice.

How to Apply Credit to an Invoice

I require customers to submit a deposit when they open their account, and some customers pre-pay for service or submit frequent deposits. I want 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 it does not work if the invoiced amount is > than the new payment amount. If the invoiced amount > new 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 then 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 receive payment. 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 applied when the invoiced amount > the new payment amount?

Solution:

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

You can see the same in 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: Receive, how to make payment through Invoice using qodbc