How do I 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.  I also need to know how to apply that credit on account to the invoice at that time.

I tried inserting a record into ReceivePaymentLine, without applying it to a transaction, and I get the error:  Error parsing complete XML return string

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

the Payment Credit isn't a Credit Memo:

but a ReceivePaymentToDeposit line within QODBC found by doing:

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

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 on.

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'

For the "Data Access Worldwide" customer the ListID is: AE0000-1197765289.

TO 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)

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

SP_LASTINSERTID ReceivePayment

It returned a TxnID of: 5C29-1197772163

The insert also created a ReceivePaymentToDeposit line found by doing:

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

NOW YOU CAN CREATE THE INVOICE

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) 

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 INVOIVCE

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

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

The Invoice now displays as PAID !

 

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)

Try rechecking by running:

SELECT TxnID, RefNumber FROM ReceivePaymentToDeposit UNOPTIMIZED WHERE CustomerRefListID = '21D0000-1160000365'

You need to using either QODBC v6.00.00.176 or v7.00.00.194. Anything earier has problems with the ReceivePayment and ReceivePaymentLine tables due to a Intuit SDK bug.

Trying doing a:

sp_optimizefullsync ReceivePaymentLine

and

sp_optimizefullsync ReceivePaymentToDeposit

to resync your optimized ReceivedPaymentLine and ReceivePaymentToDeposit  tables. Once that's done, do:

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

and

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

and try finding the transactions you are looking for.

I have Ver:  6.00.00.176. I have no luck after resynching. Do I need to download and try it? Can I upgrade to Ver 7.0 without any problem? Where can I find an upgrade check list?   I am using USA edition of Quick Books. Could it be related to my  problem?

Thank you. Piyush

The Intuit Quickbooks USA qbXML SDK bug that causes the incorrect number of ReceivePaymentLine lines (used by the Optimizer), while other lines are missing altogether was fixed by Intuit in the R7 update for QuickBooks 2006 and Enterprise 6.0.

Intuit currently shows updates up to R8 as at October 10, 2006 at: http://www.quickbooks.com/support/index/ndxw_15_update.html. You need to ensure your QuickBooks is up to date. QODBC v7 contains fixes to v6 and you can install v7 over the top of your QODBC v6 installation whenever you're ready to do so.

I am receiving error when i write
SP_LASTINSERTID ReceivePayment

Txnid which i will get from here will insert for the query

INSERT INTO ReceivePaymentLine (CustomerRefListID, ARAccountRefListID,
AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('B6E0000-1171387426',
'3C0000-1164634404','insert the txnid from sp_lastinsertid didnot work', 84.59)"

I am using Quickbook Premier Version Retail Edition 2006.
I am using QODBC Read,Write version 7.00. ( Updated version )
Please Tom reply me soon with thanks.

The error is saying the AppliedToTxnTxnID 'D35D-1171387434' was not found as a TxnID in the Invoice Table. Please read from the top down as you are not following the procedure shown.

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.

Hello,

We have QB Enterprise Version 8.0 and QODBC.  Here is our situation:

1. We have a payment that comes in from a customer of $200 entered manually into QB

2.  We have two invoices for that customer ALREADY in QB's for say $100 each

We need to apply the $200 to each $100 invoice to pay them off.  This is an example of our situation.  In reality we have a large payment that needs applied to hundreds of invoices that are already in the system.  I can seem to pay off both of my example invoices at the same time using an insert to the RecievePaymentLine table.  Here is the SQL I am using:

INSERT INTO ReceivePaymentLine (CustomerRefListID, ARAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount,TxnDate, FQSaveToCache, TxnID)
VALUES ('80000001-1208796717', '80000025-1208796747', 200.00, '19-1208805375', 100.00, '2008-04-15', 0, 'CE-1209418219')

This will pay off ONE of the invoices, after changing the AppliedToTxnTxnID to the next $100 invoice the insert will go through but QB will swap the $100 from one invoice to the other, leaving one paid and one unpaid.  I need to figure out how to pay off BOTH of these invoices from this $200 payment.  Advice is greatly appreciated.  Thanks

Do not use TxnID in your SQL Statement. The use of a TxnID in an SQL Statement is reserved and associated with adding a new line to an existing transaction. You cannot currently add lines to an existing ReceivePaymentLine transaction and there's a bug in QODBC that will actually update the ReceivePaymentLine transaction if you use the TxnID.

In your case 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 can I create multiple lines in the Invoice, Purchase, Journal and other tables? for examples.

Tom,

Thanks for the response, I got my test data to work but I had to use TxnID to get it to reference an existing payment, otherwise it will create a new payment to apply to the invoices.

I do this first:

INSERT INTO ReceivePaymentLine (TxnID, CustomerRefListID, ARAccountRefListID, TotalAmount, AppliedToTxnTxnID,   AppliedToTxnPaymentAmount, FQSaveToCache)"
VALUES ('CE-1209418219', '80000001-1208796717', '80000025-1208796747', 200.00, '1-1208796835', 100.00, 1)"
   
Then this: 

INSERT INTO ReceivePaymentLine (TxnID, CustomerRefListID, ARAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, FQSaveToCache)"
Values ('CE-1209418219', '80000001-1208796717', '80000025-1208796747', 200.00, '19-1208805375', 100.00, 0)"
   
These statements will pay off both EXISTING invoices with the EXISTING payment.  Do you see any issue with using TxnID in this case? Is there any other way to use an existing payment to pay off existing invoices without using this ID?  This method seems to work fine.  Thanks again.