Knowledgebase
[QODBC-Desktop] How to convert a Sales Order into an Invoice
Posted by brad waddell on 12 March 2009 05:21 PM

 

An Example of Converting a Sales Order into an Invoice

Note: To understand the process for everyone, I will do a simple one-line estimate example, make it a Sales Order and then make it into an Invoice.

TO CREATE THE ESTIMATE

Query in QODBC

The following SQL statement will create a new estimate:

     INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID",
     "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('470001-1071525403', '201', '250000-933272656',
     'Building permit No 201', 100.00000, 100.00, '20000-999022286', 0)


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

Result in QuickBooks

The above query results in the following estimate in the QuickBooks 2006 Premier USA Edition - Sample Rock Castle Construction company file:

CREATE A SALES ORDER FROM AN ESTIMATE

Query in QODBC

When we're ready to create the Sales Order we can read the EstimateLine table and insert it into the SalesOrderLine table like this:

      INSERT INTO "SalesOrderLine" ("CustomerRefListID", "RefNumber", "Memo", "SalesOrderLineItemRefListID",
      "SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
      "FQSaveToCache")
      Select "CustomerRefListID", "RefNumber", {fn CONCAT('Estimate ', "RefNumber")} as "Memo","EstimateLineItemRefListID",
      "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
      "FQSaveToCache" from EstimateLine where "RefNumber"='201' and "EstimateLineSeqNo"=1

Note: This is one complete SQL statement, for multiple estimate lines you would set FQSaveToCache to1 instead (using:1 as "FQSaveToCache") and loop the EstimateLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

The Estimate now appears as a Sales Order in QuickBooks with "Estimate 201" in the memo field the same way QuickBooks creates a Sales Order from an Estimate.

CREATE AN INVOICE FROM THE SALES ORDER

Query in QODBC

When we're ready to create the Invoice, we can read the SalesOrderLine table and insert it into the InvoiceLine table like this:

      INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", "Memo", "InvoiceLineItemRefListID",
      "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
      "FQSaveToCache")
      Select "CustomerRefListID", {fn CONCAT('I', "RefNumber")} as "RefNumber",
      {fn CONCAT('Sales Order ', "RefNumber")} as "Memo","SalesOrderLineItemRefListID",
      "SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
      "FQSaveToCache" from SalesOrderLine where "CustomerRefFullName" ='Lamb, Brad:Room Addition' and "RefNumber"='201'
      and "SalesOrderLineSeqNo"=1

Note: This is one complete SQL statement, for multiple estimate lines you would set FQSaveToCache to 1 instead (using:1 as "FQSaveToCache") and loop the SalesOrderLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

The Sales Order now appears as an Invoice in QuickBooks but is unlinked to the Sales Order line at this stage.

CREATE A LINKED INVOICE FROM THE SALES ORDER

Query in QODBC

When we're ready to create the Invoice we can read the SalesOrderLine table and insert it into the InvoiceLine table as a linked Invoice instead like this:

      INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
      "InvoiceLineLinkToTxnTxnID", "InvoiceLineLinkToTxnTxnLineID",
      "FQSaveToCache")
      Select "CustomerRefListID", {fn CONCAT('SOLink', "RefNumber")},
      "TxnID", "SalesOrderLineTxnLineID",
      0 as "FQSaveToCache" from SalesOrderLine
      where "CustomerRefFullName" ='Lamb, Brad:Room Addition' and "RefNumber"='201'

      and "SalesOrderLineSeqNo"=1

Note: This is one complete SQL statement, for multiple estimate lines you would set FQSaveToCache to 1 instead (using:1 as "FQSaveToCache") and loop the SalesOrderLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

The query above results in the following linked Invoice in the QuickBooks 2007 Premier USA Edition - Sample Rock Castle Construction company file:

and because there was only one line in the example Sales Order, the Sales Order has also been marked, "INVOICED IN FULL".

(164 vote(s))
Helpful
Not helpful

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