An Example of a Change in the Income Account for a Stock item that has been Invoiced

Create an Invoice for a Stock

     Here we create an invoice for a stock part called 'Door Frame':

     INSERT INTO "InvoiceLine" ("CustomerRefFullName", "RefNumber",
     "InvoiceLineItemRefFullName", "InvoiceLineDesc", "InvoiceLineQuantity",
     "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefFullName",
     "FQSaveToCache") VALUES ('Richman, Karen-2273526', '1001',
     'Door Frame', 'standard interior door frame', 3.00000,
     150, 450.00, 'Non', 0)

Retrieve the TxnID for the transaction just Created

      To retrieve the TxnID for the transaction I just created, I use the QODBC stored procedure:

      SP_LASTINSERTID InvoiceLine

Retrieve the TxnLineID from the Invoice Line

     I can then retrieve the TxnLineID for the invoice line using the TnxID: '5DE5-1197763322':

     select TxnID, InvoiceLineTxnLineID, InvoiceLineSeqNo from InvoiceLine
     where TxnID='5DE5-1197763322'

     The TnxID and TxnLineID for the Invoice line I just created are: 5DE5-1197763322 and 5DE7-1197763322

The result is Displayed in QuickBooks.

     In QuickBooks, a brief report for the invoice I just created shows the income account to be: Construction: Materials.

Change the Income Account

      Using the TnxID and TxnLineID for the Invoice line I just created, I can change the income account to a new Income Account by using the following UPDATE statement:

      UPDATE InvoiceLine
      SET InvoiceLineOverrideItemAccountRefFullName = 'New Income Account'
      WHERE TxnID = '5DE5-1197763322' AND InvoiceLineTxnLineID = '5DE7-1197763322'

The result is Displayed in QuickBooks.

     When I refresh the Quick Report in QuickBooks, it now shows the Income Account as New Income Account