[QODBC-Desktop] Refunds with QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Refunds with QODBC

Applying a refund check to an existing credit memo

Apply Refund Check in QuickBooks

You cannot use QODBC to create refund checks applied to credit memos. To do this, open the Create Credit Memos/Refunds dialog box using the QuickBooks UI and find the credit memo for that customer. In the dialog box icon bar, click on the dollar bill with a hand above it and choose "Give Refund."


For existing Credit Memos, we can use the Credit Memo TxnID and apply it in the AppliedToTxnSetCreditCreditTxnID column in the ReceivePaymentLine insert for payments, but not for refunds. The CreditMemoLinkedTxn table is updated automatically by QuickBooks and cannot be used by QODBC to create linked refund transactions at this stage.


Overpayments and Refunds Support

QODBC v9.00.00.259 (or later) now supports OverPayments and Refunds. See below as an example:

Create an Invoice for a Customer
INSERT INTO InvoiceLine (CustomerRefFullName, ARAccountRefFullName, InvoiceLineItemRefFullName, InvoiceLineAmount) VALUES ('Pretell Real Estate:155 Wilks Blvd.', 'Accounts Receivable', 'Framing', 1000.00)

Get the TxnID of the Invoice
sp_lastinsertID Invoice (returns '48FB-1197746739')

Receive the check that includes overpayment to just the invoice
INSERT INTO ReceivePaymentLine (CustomerRefFullName, ARAccountRefFullName, TotalAmount, PaymentMethodRefFullName, DepositToAccountRefFullName, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('Pretell Real Estate:155 Wilks Blvd.', 'Accounts Receivable', 1100.00, 'Check', 'Checking', '48FB-1197746739', 1000.00)

Get the TxnID from the ReceivePayment
sp_lastinsertID ReceivePayment (returns '48FF-1197747551')

To see what it looks like
SELECT * FROM ReceivePaymentLine where TxnID = '48FF-1197747551'

Print a refund check (It can be an ARRefundCreditCard, you need the TxnID from it)
INSERT INTO CheckExpenseLine (AccountRefFullName, PayeeEntityRefFullName, ExpenseLineAccountRefFullName, ExpenseLineAmount, ExpenseLineCustomerRefFullName) VALUES ('Checking', 'Pretell Real Estate:155 Wilks Blvd.', 'Accounts Receivable', 100.00, 'Pretell Real Estate:155 Wilks Blvd.')

Get the TxnID from the Check
sp_lastinsertID Check (returns '4904-1197749043')

Now attach the overpayment to the original payment that was overpaid
Note: This is the guts of it, being able to edit the payment and connect it to the correct pieces.
INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('48FF-1197747551', '4904-1197749043', 100.00)

To see what it looks like
SELECT * FROM ReceivePaymentLine where TxnID = '48FF-1197747551'

(135 vote(s))
Not helpful

Comments (2)
23 November 2013 05:55 AM
When try to insert Check details into ReceivePaymentLine 'xxxxxx specified TxnID not found error ' showing.How to handle this?
Layla Alexia
07 September 2016 08:39 AM
My setup links CC payments to an other current asset account.

I discovered it was not possible to write a check from an other current asset account.

I found I can insert refunds directly into the table:
in a single step and pull from the other current asset account without issue.
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).