How do I apply a journal entry to an invoice?

I'm not trying to invoice using the GL accounts directly.  I'm trying to apply a journal entry to an existing invoice to reduce balance remaining on the invoice.

Quickbooks allows you to apply credits from journal entries to existing invoices.  I need to know how to do this.  Since, through QODBC, we can't receive a payment that isn't applied to an invoice, and apply the payment later, when the invoice has been created, I'm trying to use Journal Entries for this.

My accountant says that I can debit Undeposited Funds, and credit A/R.  Then, when the invoice is created, apply the credit to the invoice.

If you do a Journal entry that debits Undeposited Funds, and credits A/R for the customer,  then the journal ends up appearing in the ReceivePaymentToDeposit table the same way as doing a Receive a Payment to Deposit using QuickBooks in the first place. So I wouldn't do the journals and I would do the following instead:

First , 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 form ReceivePaymentToDeposit Table)