[QODBC-Desktop] How to Receive a Payment not be Applied to an Invoice ( How to handle Customer credits on account )
Posted by brad waddell on 12 March 2009 05:21 PM

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

(112 vote(s))
Helpful
Not helpful

Comments (4)
Eric Phillips
15 December 2016 06:49 AM
I am trying to apply a pre-paid deposit for a customer to one of their job invoices, with the following statement:

INSERT INTO ReceivePaymentLine ( CustomerRefListID, ARAccountRefListID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount )
SELECT '80001447-1314282533', '620000-1084332528', '1B2B8C-1481781578', 300, '1B10DD-1481065253', 300;

It works when the invoice and payment is for the parent customer, but it doesn't when the payment is applied to the parent customer but the invoice is listed for the customer job. Quickbooks lets you apply payments from parent customers to their child jobs, but I cannot get it to work with the insert statement.

I get the following error

[QODBC] Error: 3120 - OBJECT "1B10DD-1481065253" specified in the request cannot be found. (#10053)

I verified 1B10DD-1481065253 is a valid TxnID in the RecievePayment table.

Jack
15 December 2016 09:42 AM
Hi Eric,

I would like to share that AppliedToTxnSetCreditCreditTxnID should be TxnID from ReceivePaymentToDeposit Table not TxnID in the RecievePayment table.

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Aftab
28 January 2018 10:48 AM
Dear Sirs,
How can we view today's received amounts from Customers ,
Thanks
Jack
29 January 2018 09:52 AM
Hi Aftab,

You can refer ReceivePayment table for that details. You can filter it by TxnDate & CustomerRefFullName.

For Example:
Select * from ReceivePayment where CustomerRefFullName = ‘John’ & TxnDate = {d’2018-01-29’}

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).