To start off, let's create a invoice with three lines in it.
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCNeg1', '250000-933272656', 'Building permit Additional Room',
100.00000, 100.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCNeg1', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCNeg1', '250000-933272656', 'Less Council Rebate',
-50.00000, -50.00, '20000-999022286', 0)
Once these three SQL INSERT commands have been run in sequence (without breaking the QODBC connection), QODBC will generate the following Invoice in the Sample Rock Castle Construction company file in the USA edition of QuickBooks 2006.
Note: See Line 3 of the invoice below for line item discount of $50.00 :-
To locate the TxnID and InvoiceLineTxnLineID for the new invoice lines we run the following query using VB Demo:-
SELECT TxnID, InvoiceLineTxnLineID, InvoiceLineDesc, InvoiceLineAmount,
RefNumber FROM InvoiceLine unoptimized where RefNumber = 'QODBCNeg1'
To delete the last line we run the following query using the TxnID and InvoiceLIneTxnLineID values for the last invoice line in VB Demo:-
delete from InvoiceLine where TxnID='5BAB-1197758658'
and InvoiceLineTxnLineID='5BAF-1197758658'
The last invoice line is now deleted and it can be verfied by re-running:-
SELECT TxnID, InvoiceLineTxnLineID, InvoiceLineDesc, InvoiceLineAmount,
RefNumber FROM InvoiceLine unoptimized where RefNumber = 'QODBCNeg1'
Please Note: All regions outside the United States should use TaxCode instead of SalesTaxCode. |
