Overpayments and refunds

Here’s the scenario: Your client owns Rock Castle Construction. Renee Barley hired your client to do $900 of work (installing drywall: a tedious job that she was happy to hire out ).

Here is the invoice for that work:
Renee pays $1000. Whoops, that’s a $100 overpayment:

Notice that QuickBooks points out the overpayment. We don’t know what Renee had in mind, so we’ll leave the overpayment as a credit. Notice that QuickBooks will offer to print a credit memo. It would be the right thing to do, but notice that even if you do choose to print a credit memo for Renee, you won’t be able to find a credit memo transaction for Renee. The printed memo is just something that we can give Renee to ensure that she can remind us that she overpaid us by $100.

That’s because QuickBooks credit memo transactions are for situations in which a customer returns items for which you have already recorded an invoice, customer payment, or sales receipt and either you or the customer wish to retain the value of the returned goods as a credit for use in future transactions (as opposed to refunding immediately). That’s not the situation we have here, because nothing is being returned.
So regardless of how we respond to the following dialog:

Renee winds up with a $100 credit:

The manual solution in the QuickBooks UI

Rock Castle Construction now must refund this overpayment manually. In our example the payment has already been deposited, so we can’t delete the overpayment and try to correct the problem.

You might guess that you could just create a credit memo now, after all it was the failure to do that which resulted in this problem. But, that’s not the right answer. Doing so would give Renee a $200 credit! That would be double the problem.
So instead, we go to the Write Checks window. Create a check to Renee Barley for $100.00. Use an expense line with Account = Accounts Receivable and Customer:Job = Renee Barley.
This will do the right thing with the balance on Renee’s account (it will return to $0.00). However that this doesn’t update the credit on the original payment transaction. Let’s look at that payment:
What we really want to do is to apply the payment to the check transaction we see there by adding it to the payment receipt we have. We can just add a check mark to the refund check line in this payment receipt, but if the payment has already been deposited some accountants may take issue with the modification of this payment receipt.
So there’s another way to proceed here by clearing out the discounts and credits. We start by creating a new Payment Receipt for Renee:

Notice that QuickBooks correctly sees the refund check we wrote earlier for the Accounts Receivable expense.
We want to apply the overpayment credit to this refund check so we click Discount & Credits:
The credit is already selected, so we just click Done.

We now have a payment for $0 that applies to nothing, but notice that the Customer has credits available message is no longer present. Click Save & Close.
Because this is a transaction for $0.00 nothing actually posts, just like no credit memo was created earlier, no new payment receipt is created, but if we check the original transaction again, we can see that the credit that we refunded is now cleaned up:

The overpayment portion is gone. Even more importantly, that extra receive payment we just did, which wasn’t really a payment receipt, won’t show up on any transaction lists, it really just cleaned up a quirk of the QuickBooks business logic.

The same workflow in the SDK

Based on the manual solution described above, you begin to code an application that will solve this for your client. But the details of doing this programmatically are a little more exposed. And the functionality in this area has improved which means you need different solutions if you need to support older versions of QuickBooks.

Lets start with QuickBooks 2005 since a good number of clients would have access to this functionality by now.. In QB2005 we have ARRefundCreditCardAdd (for credit card refund) and CheckAdd (for check refund). Let’s see what happens when we use those.

<qbxml>
    <qbxmlmsgsrq onerror="stopOnError">
        <checkaddrq>
            <checkadd>
                <accountref>
                    <fullname>Checking</fullname>
                </accountref>
                <payeeentityref>
                    <fullname>Barley, Renee</fullname>
                </payeeentityref>
                <txndate>2007-12-15</txndate>
                <memo>Refund for overpayment</memo>
                <istobeprinted>1</istobeprinted>
                <expenselineadd>
                    <accountref>
                        <fullname>Accounts Receivable</fullname>
                    </accountref>
                    <amount>100.00</amount>
                    <customerref>
                        <fullname>Barley, Renee</fullname>
                    </customerref>
                </expenselineadd>
            </checkadd>
        </checkaddrq>
    </qbxmlmsgsrq>
</qbxml>

When we send this to the request processor with QB 2005 or greater we get:

<qbxml>
    <qbxmlmsgsrs>
        <checkaddrs statuscode="0" statusmessage="Status OK" statusseverity="Info">
            <checkret>
                <txnid>5BBE-1197738754</txnid>
                <timecreated>2007-12-15T09:12:34-08:00</timecreated>
                <timemodified>2007-12-15T09:12:34-08:00</timemodified>
                <editsequence>1197738754</editsequence>
                <txnnumber>1261</txnnumber>
                <accountref>
                    <listid>20000-933270541</listid>
                    <fullname>Checking</fullname>
                </accountref>
                <payeeentityref>
                    <listid>920000-1071506140</listid>
                    <fullname>Barley, Renee</fullname>
                </payeeentityref>
                <txndate>2007-12-15</txndate>
                <amount>100.00</amount>
                <memo>Refund for overpayment</memo>
                <address>
                    <addr1>Renee Barley</addr1>
                    <addr2>4417 N. Sun Valley Rd</addr2>
                    <city>Middlefield</city>
                    <state>CA</state>
                    <postalcode>94471</postalcode>
                </address>
                <istobeprinted>true</istobeprinted>
                <expenselineret>
                    <txnlineid>5BC0-1197738754</txnlineid>
                    <accountref>
                        <listid>40000-933270541</listid>
                        <fullname>Accounts Receivable</fullname>
                    </accountref>
                    <amount>100.00</amount>
                    <customerref>
                        <listid>920000-1071506140</listid>
                        <fullname>Barley, Renee</fullname>
                    </customerref>
                </expenselineret>
            </checkret>
        </checkaddrs>
    </qbxmlmsgsrs>
</qbxml>

But look at the Customer Payment:

Whoa! Look at that! Someone could still come along and issue a refund! The problem is the same as when we did it manually. It turns up we need that Apply Credits magic.

Those of you who remember our discussion of this situation in the QuickBooks UI will see that the problem for the SDK is that there’s actually no transaction for that credit, even if we printed a credit memo, that means there’s no TxnID we can use in the ReceivePaymentAdd request’s SetCredit aggregate.

Before QuickBooks 2007, we really have only two options.

Option 1: Prevent the situation from happening in the first place by recognizing an overpayment before we recording it from our application and generate a refund check first, then record the payment receipt, applying the payment to the original invoice and the refund check all at once (note that in the example below we’re also recording the invoice, that’s just to provide a functional request that doesn’t require the AlphaGeek to magically anticipate the TxnID of the invoice ):

<qbxml>
    <qbxmlmsgsrq onerror="stopOnError">
        <invoiceaddrq requestid="0">
            <invoiceadd defmacro="TxnID:INV1">
                <customerref>
                    <fullname>Barley, Renee</fullname>
                </customerref>
                <invoicelineadd>
                    <itemref>
                        <fullname>Install Drywall</fullname>
                    </itemref>
                    <quantity>1</quantity>
                    <rate>900.00</rate>
                    <salestaxcoderef>
                        <fullname>Non</fullname>
                    </salestaxcoderef>
                </invoicelineadd>
            </invoiceadd>
        </invoiceaddrq>
        <checkaddrq>
            <checkadd defmacro="TxnID:Check1">
                <accountref>
                    <fullname>Checking</fullname>
                </accountref>
                <payeeentityref>
                    <fullname>Barley, Renee</fullname>
                </payeeentityref>
                <memo>Refund</memo>
                <expenselineadd>
                    <accountref>
                        <fullname>Accounts Receivable</fullname>
                    </accountref>
                    <amount>100.00</amount>
                    <customerref>
                        <fullname>Barley, Renee</fullname>
                    </customerref>
                </expenselineadd>
            </checkadd>
        </checkaddrq>
        <receivepaymentaddrq>
            <receivepaymentadd>
                <customerref>
                    <fullname>Barley, Renee</fullname>
                </customerref>
                <totalamount>1000.00</totalamount>
                <appliedtotxnadd>
                    <txnid usemacro="TxnID:INV1"></txnid>
                    <paymentamount>900.00</paymentamount>
                </appliedtotxnadd>
                <appliedtotxnadd>
                    <txnid usemacro="TxnID:Check1"></txnid>
                    <paymentamount>100.00</paymentamount>
                </appliedtotxnadd>
            </receivepaymentadd>
        </receivepaymentaddrq>
    </qbxmlmsgsrq>
</qbxml>

Option 2. Recognize the situation has occurred and guide the user through fixing it by adding the refund check to QuickBooks, then use TxnDisplayMod to bring up the payment receipt in the QuickBooks user interface and ask the user to add a check mark next to the refund check in the apply to transaction list of the payment form.

QuickBooks 2007 to the rescue!

While the first solution proposed is reasonable if it is feasible for your application to detect the overpayment situation when its happening, the reality is that many times your application won’t have the omniscient view of QuickBooks data and the user’s intent that is required to apply it reliably.

Fortunately, the situation gets considerably better with QuickBooks 2007, we can record the payment as usual, applying it to the invoice and getting stuck with a $100 credit balance for Renee:

<qbxml>
    <qbxmlmsgsrq onerror="stopOnError">
        <receivepaymentaddrq>
            <receivepaymentadd>
                <customerref>
                    <fullname>Barley, Renee</fullname>
                </customerref>
                <totalamount>1000.00</totalamount>
                <appliedtotxnadd>
                    <txnid usemacro="TxnID:INV1"></txnid>
                    <paymentamount>900.00</paymentamount>
                </appliedtotxnadd>
            </receivepaymentadd>
        </receivepaymentaddrq>
    </qbxmlmsgsrq>
</qbxml>

Then at some later time we can record a check to refund Renee:

<qbxml>
<qbxmlmsgsrq onerror="stopOnError">
<checkaddrq>
<checkadd defmacro="TxnID:Check1">
<accountref>
<fullname>Checking</fullname>
</accountref>
<payeeentityref>
<fullname>Barley, Renee</fullname>
</payeeentityref>
<memo>Refund</memo>
<expenselineadd>
<accountref>
<fullname>Accounts Receivable</fullname>
</accountref>
<amount>100.00</amount>
<customerref>
<fullname>Barley, Renee</fullname>
</customerref>
</expenselineadd>
</checkadd>
</checkaddrq>
</qbxmlmsgsrq>
</qbxml>

A quick TransactionQuery request shows us the situation as it stands with Renee at this point:

<qbxml>
    <qbxmlmsgsrs>
        <transactionqueryrs statuscode="0" statusmessage="Status OK" statusseverity="Info">
            <transactionret>
                <txntype>Invoice</txntype>
                <txnid>5C29-1197706889</txnid>
                <timecreated>2007-12-15T00:21:29-08:00</timecreated>
                <timemodified>2007-12-15T00:21:29-08:00</timemodified>
                <entityref>
                    <listid>920000-1071506140</listid>
                    <fullname>Barley, Renee</fullname>
                </entityref>
                <accountref>
                    <listid>40000-933270541</listid>
                    <fullname>Accounts Receivable</fullname>
                </accountref>
                <txndate>2007-12-15</txndate>
                <refnumber>94</refnumber>
                <amount>900.00</amount>
            </transactionret>
            <transactionret>
                <txntype>ReceivePayment</txntype>
                <txnid>5C2D-1197706923</txnid>
                <timecreated>2007-12-15T00:22:03-08:00</timecreated>
                <timemodified>2007-12-15T00:22:03-08:00</timemodified>
                <entityref>
                    <listid>920000-1071506140</listid>
                    <fullname>Barley, Renee</fullname>
                </entityref>
                <accountref>
                    <listid>80000-933270541</listid>
                    <fullname>Undeposited Funds</fullname>
                </accountref>
                <txndate>2007-12-15</txndate>
                <amount>1000.00</amount>
            </transactionret>
            <transactionret>
                <txntype>Check</txntype>
                <txnid>5C32-1197706965</txnid>
                <timecreated>2007-12-15T00:22:45-08:00</timecreated>
                <timemodified>2007-12-15T00:22:45-08:00</timemodified>
                <entityref>
                    <listid>920000-1071506140</listid>
                    <fullname>Barley, Renee</fullname>
                </entityref>
                <accountref>
                    <listid>20000-933270541</listid>
                    <fullname>Checking</fullname>
                </accountref>
                <txndate>2007-12-15<txndate>
                <refnumber>304</refnumber>
                <amount>-100.00</amount>
            </transactionret>
        </transactionqueryrs>
    </qbxmlmsgsrs>
</qbxml>

The set of transactions above get us into this situation:

QuickBooks 2007 and SDK 6.0 add a number of transaction mod requests, including, fortunately, ReceivePaymentMod, so we can use the SDK to modify that payment receipt (TxnID 5C2D-1197706923) to apply it to the check as well:l:

<qbxml>
<qbxmlmsgsrq onerror="continueOnError">
<receivepaymentmodrq requestid="1">
<receivepaymentmod>
<txnid>5C2D-1197706923</txnid>
<editsequence>1197706923</editsequence>
<appliedtotxnmod>
<txnid>5C29-1197706889</txnid>
<paymentamount>900.00</paymentamount>
</appliedtotxnmod>
<appliedtotxnmod>
<txnid>5C32-1197706965</txnid>
<paymentamount>100.00</paymentamount>
</appliedtotxnmod>
</receivepaymentmod>
</receivepaymentmodrq>
</qbxmlmsgsrq>
</qbxml>

Checking the QuickBooks UI we see that we got exactly what we were after:

Which we can also see in the response from the ReceivePaymentMod request:

<qbxml>
    <qbxmlmsgsrs>
        <receivepaymentmodrs requestid="1" statuscode="0" statusmessage="Status OK" statusseverity="Info">
            <receivepaymentret>
                <txnid>5C2D-1197706923
                <timecreated>2007-12-15T00:22:03-08:00</timecreated>
                <timemodified>2007-12-15T01:01:10-08:00</timemodified>
                <editsequence>1197709270</editsequence>
                <txnnumber>1266</txnnumber>
                <customerref>
                    <listid>920000-1071506140</listid>
                    <fullname>Barley, Renee</fullname>
                </customerref>
                <araccountref>
                    <listid>40000-933270541</listid>
                    <fullname>Accounts Receivable</fullname>
                </araccountref>
                <txndate>2007-12-15</txndate>
                <totalamount>1000.00</totalamount>
                <deposittoaccountref>
                    <listid>80000-933270541</listid>
                    <fullname>Undeposited Funds</fullname>
                </deposittoaccountref>
                <unusedpayment>0.00</unusedpayment>
                <unusedcredits>0.00</unusedcredits>
                <appliedtotxnret>
                    <txnid>5C29-1197706889</txnid>
                    <TxnType>Invoice</TxnType>
                <txndate>2007-12-15</txndate>
                <refnumber>94</refnumber>
                <balanceremaining>0.00</balanceremaining>
                <amount>900.00</amount>
            </appliedtotxnret>
            <appliedtotxnret>
                <txnid>5C32-1197706965</txnid>
                <txntype>Check</txntype>
                <txndate>2007-12-15</txndate>
                <refnumber>304</refnumber>
                <balanceremaining>0.00</balanceremaining>
                <amount>100.00</amount>
            </appliedtotxnret>
        </receivepaymentret>
    </receivepaymentmodrs>
</qbxmlmsgsrs>
</qbxml>

Conclusion

The handling of overpayments has long been a topic of confusion for QuickBooks users and developers alike (not to mention the AlphaGeek). We hope that this in-depth exploration of the issues associated with overpayments in QuickBooks and how to handle them in the SDK will be helpful to everyone. ReceivePaymentMod is but one of many new requests from SDK 6.0, the AlphaGeek says Check it Out!