[QODBC-Desktop] How to change the Income Account for a Stock Item that has already been invoiced
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Change 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

Result Displayed in QuickBooks

     In QuickBooks a quick 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'

Result Displayed in QuickBooks

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

(243 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).