Knowledgebase: Sp_commands & Reports
[QODBC-Desktop] How to access the QuickBooks Open Sales Order by Item report in QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Note: Actually we don't have a sp_report for the QuickBooks "Open Sales Order by Item" report which is not available via Intuit SDK, however, I've managed to extract similar data using QODBC.

Typical Open Sales Order by Item Report in QuickBooks

     For example, when using the Rock Castle Construction QuickBooks 2006 sample company file: sample_product-based business.qbw the report looks like this:

Report Displayed in QODBC

Normal Query

     By using the following select statement:

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date,
DueDate, RefNumber as Num, CustomerRefFullName as Name,
SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as Invoiced,
SalesOrderLineAmount as Amount FROM SalesOrderLine
where IsFullyInvoiced = FALSE
and SalesOrderLineInvoiced < SalesOrderLineQuantity

Display in QODBC

     what is shown in the above report appears using QODBC:

Query with Date Range

     This can also be made must faster by limiting the date range like this:

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date,
DueDate, RefNumber as Num, CustomerRefFullName as Name,
SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as Invoiced,
SalesOrderLineAmount as Amount FROM SalesOrderLine
where TxnDate >= {d'2008-01-01'} and IsFullyInvoiced = FALSE
and SalesOrderLineInvoiced < SalesOrderLineQuantity

 

Normal Issues and Solutions

Resulting Rows are Short of the QuickBooks Report's Rows and Total Amounts Don't Match

    Make sure you are running QODBC v6.00.00.155 or later. And try the below instead:

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date,
DueDate, RefNumber as Num, CustomerRefFullName as Name,
SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as Invoiced,
SalesOrderLineAmount as Amount FROM SalesOrderLine unoptimized
where IsFullyInvoiced = FALSE
and SalesOrderLineInvoiced < SalesOrderLineQuantity

How to add the aging field, the class field, and the memo field to the report

     Well, this will work based on the actual current system date (not the QuickBooks date):

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date, DueDate, RefNumber as Num, CustomerRefFullName as Name, SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as
Invoiced, SalesOrderLineAmount as Amount, ClassRefFullName as Class, Memo,
({fn CURDATE()}-TxnDate) as Aging FROM SalesOrderLine unoptimized
where IsFullyInvoiced = FALSE and SalesOrderLineInvoiced < SalesOrderLineQuantity

When the sp_report command for "Open Sales Orders by Item" report could be added

     I've not heard or seen anything about adding sp_report for Open Sales Orders by Customer or Open Sales Orders by Item. This is a limitation of Intuit SDK. But could see also: Open sales orders by Item as a reference.

(182 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).