First off I'm going to say that what you want to do is unsound and after trying to build what you wanted to do, QuickBooks doesn't allow it anyway. The good news is that I now have a very good example on creating bills, billcredits, payments and how they are applied against each other. Let's start the journey to enlightenment.....
Step 1: Let's create a new Accounts Payable Bill No: 1000 for ABC Company
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefFullName", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('Insurance', 100.00, 'Insurance Expense to ABC Company', 1)
INSERT INTO "Bill" ("VendorRefFullName", "APAccountRefFullName", "TxnDate", "RefNumber", "TermsRefFullName", "DueDate", "Memo") VALUES ('ABC Company', 'Accounts Payable', {d'2007-12-01'}, '1000', 'Net 30', {d'2007-12-31'}, 'Insurance Bill to ABC Company')
The two insert statements created the following "Accounts Payable" Bill No: 1000 in QuickBooks:
To locate the Transaction ID (TxnID) of this Bill we run:
sp_lastinsertID bill
The TxnID Value returned: 5CB4-1197764010 for use as the AppliedToTxnTxnID for Bill No: 1000
Step 2: Let's create the A/P Holding Account BillCredit for ABC Company
INSERT INTO "VendorCreditExpenseLine" ("VendorRefFullName", "APAccountRefFullName", "TxnDate", "ExpenseLineAccountRefFullName", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('ABC Company', 'A/P Holding Account', {d'2007-12-01'}, 'Insurance', 100.00, 'Credit ABC Company', 0)
The insert statement created the following "A/P Holding Account" BillCredit in QuickBooks:
To locate the Transaction ID (TxnID) of this BillCredit we run:
sp_lastinsertID VendorCreditExpenseLine
The TxnID Value returned: 5CB7-1197767333 is the AppliedToTxnSetCreditCreditTxnID when applying the credit.
Step 3: Let's create a new A/P Holding Account Bill No: 1000 for John Smith
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefFullName", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('Insurance', 100.00, 'Insurance Expense to John Smith', 1)
INSERT INTO "Bill" ("VendorRefFullName", "APAccountRefFullName", "TxnDate", "RefNumber", "TermsRefFullName", "DueDate", "Memo") VALUES ('John Smith', 'A/P Holding Account', {d'2007-12-01'}, '1000', 'Net 30', {d'2007-12-31'}, 'Insurance Bill to John Smith')
The two insert statements created the following "A/P Holding Account" Bill No: 1000 in QuickBooks:
To locate the Transaction ID (TxnID) of this Bill we run:
sp_lastinsertID bill
TxnID Value returned: 5CBA-1197768466 for use as the AppliedToTxnTxnID for check payment to John Smith
Step 4: To pay the Bill to John Smith via the Checking Account
QuickBooks shows the bill for John Smith under the A/P Account: "A/P Holding Account".
To apply the payment we use the TxnID for the Bill to John Smith
INSERT INTO BillPaymentCheckLine (PayeeEntityRefFullName, BankAccountRefFullName, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, FQSaveToCache) Values ('John Smith', 'Checking', 1, '5CBA-1197768466', 100.00, {d'2007-12-31'}, 0)
John Smith's bill is now marked "PAID":
Step 5: To apply the ABC Company CREDIT to the ABC Company Bill No: 1000
To apply the credit we use the TxnID for the Bill to ABC Company against the TxnID for the BillCredit
INSERT INTO BillPaymentCheckLine (PayeeEntityRefFullName, BankAccountRefFullName, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate, FQSaveToCache) Values ('ABC Company', 'Barter Account', 1, '5CB4-1197764010', 100.00, '5CB7-1197767333', 100.00, {d'2007-12-31'}, 0)
However, QuickBooks tells us that the TxnID can't be found. The QODBC message log says:
2007-08-29 20:59:44 QODBC Ver: 7.10.00.231 ********************************************************************************************************************* IsAService: False SQL Statement: INSERT INTO BillPaymentCheckLine (PayeeEntityRefFullName, BankAccountRefFullName, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate, FQSaveToCache) Values ('ABC Company', 'Barter Account', 1, '5CB4-1197764010', 100.00, '5CB7-1197767333', 100.00, {d'2007-12-31'}, 0)
3120 - Object "5CB4-1197764010" specified in the request cannot be found. Input XML: <?xml version="1.0" encoding="ISO-8859-1"?> <?qbxml version="6.0"?> <QBXML> <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll"> <BillPaymentCheckAddRq requestID = "1"> <BillPaymentCheckAdd defMacro = "TxnID:0E78840A-488B-43F9-985A-2BEB425D3A2C"> <PayeeEntityRef> <FullName>ABC Company</FullName> </PayeeEntityRef> <TxnDate>2007-12-31</TxnDate> <BankAccountRef> <FullName>Barter Account</FullName> </BankAccountRef> <IsToBePrinted>1</IsToBePrinted> <AppliedToTxnAdd> <TxnID>5CB4-1197764010</TxnID> <PaymentAmount>100.00</PaymentAmount> <SetCredit> <CreditTxnID>5CB7-1197767333</CreditTxnID> <AppliedAmount>100.00</AppliedAmount> </SetCredit> </AppliedToTxnAdd> </BillPaymentCheckAdd> </BillPaymentCheckAddRq> </QBXMLMsgsRq> </QBXML> Output XML: <?xml version="1.0" ?> <QBXML> <QBXMLMsgsRs> <BillPaymentCheckAddRs requestID="1" statusCode="3120" statusSeverity="Error" statusMessage="Object "5CB4-1197764010" specified in the request cannot be found. " /> </QBXMLMsgsRs> </QBXML>
This is because the credit is against the A/P Holding Account while the Bill is against Accounts Payable even though they both are clealy seen in QuickBooks against ABC Company:
To fix this I created a new Bill No: 1001 against the A/P Holding Account:
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefFullName", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('Insurance', 100.00, 'Insurance Expense to ABC Company', 1)
INSERT INTO "Bill" ("VendorRefFullName", "APAccountRefFullName", "TxnDate", "RefNumber", "TermsRefFullName", "DueDate", "Memo") VALUES ('ABC Company', 'A/P Holding Account', {d'2007-12-01'}, '1001', 'Net 30', {d'2007-12-31'}, 'Insurance Bill to ABC Company')
To locate the Transaction ID (TxnID) of this Bill we again run:
sp_lastinsertID bill
The TxnID Value returned: 5CC2-1197777808 for use as the AppliedToTxnTxnID for Bill No: 1001
QuickBooks now shows a bill for ABC Company under the A/P Account: "A/P Holding Account".
We can now apply the ABC Company CREDIT to the ABC Company Bill No: 1001 without any errors:
INSERT INTO BillPaymentCheckLine (PayeeEntityRefFullName, BankAccountRefFullName, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate, FQSaveToCache) Values ('ABC Company', 'Barter Account', 1, '5CC2-1197777808', 100.00, '5CB7-1197767333', 100.00, {d'2007-12-31'}, 0)
And Bill No: 1001 now appears paid in QuickBooks because the billcredit has been applied !!!!:
And you guessed it, I still need to credit the original Accounts Payable Bill No: 1000 for ABC Company |