This FAQ is Checks 101:
You can only insert into the CheckItemLine or CheckExpenseLine table. The Check table is a header table and cannot be inserted into.
This creates one Check with three Item Lines and one Expense Line. Note the FQSaveToCache field, set to True except on the last line.
INSERT INTO "CheckItemLine" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Memo 1', 0, '250000-933272656', 'Building permit', 100.00, 100.00, '250000-933272658', 'Billable', 1)
INSERT INTO "CheckItemLine" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Memo 2', 0, '250000-933272656', 'Building permit', 200.00, 200.00, '250000-933272658', 'Billable', 1)
INSERT INTO "CheckItemLine" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Memo 3', 0, '250000-933272656', 'Building permit', 300.00, 300.00, '250000-933272658', 'Billable', 1)
INSERT INTO "CheckExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "ExpenseLineBillableStatus", "FQSaveToCache") VALUES ('320000-933270542', 600.00, 'Memo 1', '250000-933272658', 'Billable', 0)
This creates one Check with three Item Lines and one Expense Line. Note the FQSaveToCache field, set to True, uses Check to save Check info and save.
INSERT INTO "CheckItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit', 100.00, 100.00, '250000-933272658', 'Billable', 1)
INSERT INTO "CheckItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit', 200.00, 200.00, '250000-933272658', 'Billable', 1)
INSERT INTO "CheckItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit', 300.00, 300.00, '250000-933272658', 'Billable', 1)
INSERT INTO "CheckExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "ExpenseLineBillableStatus", "FQSaveToCache") VALUES ('320000-933270542', 600.00, 'Expense Memo 1', '250000-933272658', 'Billable', 1)
INSERT INTO "Check" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Check Memo 1', 0)
But how can I apply check payments or credits to bills?
Yes, here is some example code and the setup data required:
PayeeEntityRefListID = 'C0000-933272656' (Required)
BankAccountRefListID = '20000-933270541' (Required)
IsToBePrinted = 1 (Either this one or RefNumber is required)
AppliedToTxnTxnID = '2F78-1071505657','976-933373192','A2D-933373874', (Find from Bill Table)
AppliedToTxnPaymentAmount = 200.75 (Necessary)
AppliedToTxnSetCreditCreditTxnID = '5522-1197768491' (Find from VendorCredit Table)
AppliedToTxnSetCreditAppliedAmount = 10.00
TxnDate = 2007-12-15
INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, BankAccountRefListID, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, FQSaveToCache) Values ('C0000-933272656', '20000-933270541', 1, '2F78-1071505657', 200.75, {d'2007-12-15'}, 0)
INSERT INTO BillPaymentCheckLine PayeeEntityRefListID, BankAccountRefListID, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate,FQSaveToCache) Values ('C0000-933272656', '20000-933270541', 1, '2F78-1071505657', 200.75, '5522-1197768491', 10.00, {d'2007-12-15'}, 0)
Note: When Adding Checks
If you are adding a check the payee must use the same currency as the source bank account. If you don't, you get an error about the currency not being the same as the base currency but it isn't clear that the message is about the payee and not the account. This message is coming back from QuickBooks qbXML SDK which doesn't handle this very well (in the user interface it will let you do it and just change the bank account to one that is in the correct currency - without telling you).
|
