Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
How do I access the QuickBooks' "Open Sales Order by Item" report please? |
Author |
Message |
|
Posted : 2006-04-06 03:13:08 |
How do I access the QuickBooks' "Open Sales Order by Item" report please? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-04-06 11:37:15 |
Actually we don't have a sp_report for the QuickBooks "Open Sales Order by Item" report, however I've managed to extract similiar data using QODBC.
For example, when using the Rock Castle Construction QuickBooks 2006 sample company file: sample_product-based business.qbw the report looks like this:
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
what is shown in the above report appears using QODBC:
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 |
|
|
|
|
Posted : 2006-04-13 09:54:06 |
Thank you for the query.
I ran the query on my Quick Books 2006 Premier company file and the resulting rows are short of the Quick Books report's rows. The Total amounts don't match. Any hints please? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-04-13 10:27:59 |
Make sure you are running QODBC v6.00.00.155 or later.
Try this 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 |
|
|
|
|
Posted : 2006-05-10 03:39:18 |
How do I add the aging field, the class field, and the memo field to this "Open Sales Order by Item" please? Thank you. Piyush |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-05-10 09:04:54 |
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 |
|
|
|
|
Posted : 2006-08-17 07:08:38 |
How likely it is to get a direct equivalent sp_report stored procedure (similar to APAgingDetail or TrialBalance) for "Open Sales Orders by Item" Quick Books 2006 Premier report? I don't want to use SQL as the results don't always match with the Quick Books report. Thank you. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-17 09:28:43 |
I've not heard or seen anything about adding sp_reports for Open Sales Orders by Customer or Open Sales Orders by Item.
See also: Open sales orders by Item |
|
|
|
|
Posted : 2008-08-27 08:25:57 |
Hi,
Occasionally I receive following error:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteReader() at QB_DotNetDaily_EOM.frmQBDataCollector.RunReport(String& DatabaseName, Int16& ConsortiumID, String& ReportCommandText, String& ReportDestinationTable) System.Data.Odbc.OdbcException: ERROR [42S00] [QODBC] Error at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteReader() at QB_DotNetDaily_EOM.frmQBDataCollector.RunReport(String& DatabaseName, Int16& ConsortiumID, String& ReportCommandText, String& ReportDestinationTable)
It occurs when I open the SqlReader to read a row. It is used in a .Net Windows program.
The SQL statement is:
SELECT "SalesOrderLine"."SalesOrderLineItemRefFullName" "Type", DueDate, "SalesOrderLine"."SalesOrderLineQuantity" "Qty","SalesOrderLine"."SalesOrderLineInvoiced" "Invoiced", "SalesOrderLine"."SalesOrderLineRate" "Amount", "SalesOrderLine"."TxnDate", "SalesOrderLine"."RefNumber" "Num", "SalesOrderLine"."CustomerRefFullName" "Name", "SalesOrderLine"."SalesOrderLineDesc", "SalesOrderLine"."IsManuallyClosed", "SalesOrderLine"."IsFullyInvoiced", "SalesOrderLine"."SalesOrderLineIsManuallyClosed", "SalesOrderLine"."PONumber" ,Class.FullName, Memo FROM "SalesOrderLine" "SalesOrderLine" , Class, Customer WHERE SalesOrderLine.ClassRefListID = Class.ListID AND Customer.ListID = SalesOrderLine.CustomerRefListID AND ("SalesOrderLine"."SalesOrderLineInvoiced" IS NULL OR ("SalesOrderLine"."TxnDate">={d '2005-01-01'} AND "SalesOrderLine"."TxnDate"<={d '2008-12-31'}) AND "SalesOrderLine"."IsManuallyClosed"=0 AND "SalesOrderLine"."IsFullyInvoiced"=0 AND "SalesOrderLine"."SalesOrderLineIsManuallyClosed"=0 AND Customer.IsActive = 1) AND SalesOrderLine.CustomerRefFullName = Customer.FullName Company = QB_Parlay06_Live. 0, .
Here is a part of the QODBC Message that occurred around that time.
2008-08-18 08:38:51 QODBC Ver: 7.10.00.231 ********************************************************************************************************************* IsAService: False Error Code: 26 SQLOptimize_OpenOptimizeDBHandle prepare: SELECT "type", "tbl_name" FROM "SQLite_MASTER" file is encrypted or is not a database
2008-08-18 08:39:14 QODBC Ver: 7.10.00.231 ********************************************************************************************************************* IsAService: False SQL Statement: SELECT "SalesOrderLine"."SalesOrderLineItemRefFullName" "Type", DueDate, "SalesOrderLine"."SalesOrderLineQuantity" "Qty","SalesOrderLine"."SalesOrderLineInvoiced" "Invoiced", "SalesOrderLine"."SalesOrderLineRate" "Amount", "SalesOrderLine"."TxnDate", "SalesOrderLine"."RefNumber" "Num", "SalesOrderLine"."CustomerRefFullName" "Name", "SalesOrderLine"."SalesOrderLineDesc", "SalesOrderLine"."IsManuallyClosed", "SalesOrderLine"."IsFullyInvoiced", "SalesOrderLine"."SalesOrderLineIsManuallyClosed", "Sa lesOrderLine"."PONumber" ,Class.FullName, Memo FROM "SalesOrderLine" "SalesOrderLine" , Class, Customer WHERE SalesOrderLine.ClassRefListID = Class.ListID AND Customer.ListID = SalesOrderLine.CustomerRefListID AND ("SalesOrderLine"."SalesOrderLineInv oiced" IS NULL OR ("SalesOrderLine"."TxnDate">={d '2005-01-01'} AND "SalesOrderLine"."TxnDate"<={d '2008-12-31'}) AND "SalesOrderLine"."IsManuallyClosed"=0 AND "SalesOrderLine"."IsFullyInvoiced"=0 AND "SalesOrderLine"."SalesOrderLineIsManuallyClose d"=0 AND Customer.IsActive = 1) AND SalesOrderLine.CustomerRefFullName = Customer.FullName Error Getting XML in BuildAndLoadXMLForNextRecord Input XML: <?xml version="1.0" encoding="ISO-8859-1"?> <?qbxml version="5.0"?> <QBXML> <QBXMLMsgsRq onError="continueOnError"> <ClassQueryRq requestID="1"> <ListID>À>Vó^</ListID> <IncludeRetElement>ListID</IncludeRetElement> <IncludeRetElement>EditSequence</IncludeRetElement> <IncludeRetElement>FullName</IncludeRetElement> </ClassQueryRq> </QBXMLMsgsRq> </QBXML>
This is run in an executable program.
However, the error doesn't occur when I run the program inside Visual Studio .Net 2005 in the debug mode.
How can I stop this error please?
Thank you,
Piyush |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-08-27 08:39:54 |
" Company = QB_Parlay06_Live. 0, " shouldn't be in your SQL Statement. The QODBC optimizer is single user only, you can't update the opt file by two applications at once. I often create muliple DSNs with different optimizer folder locations to get around this. |
|
|
|
|
Posted : 2008-08-27 08:48:56 |
I apologize. The statement does not have Parlay..
The statement is:
SELECT "SalesOrderLine"."SalesOrderLineItemRefFullName" "Type", DueDate, "SalesOrderLine"."SalesOrderLineQuantity" "Qty","SalesOrderLine"."SalesOrderLineInvoiced" "Invoiced", "SalesOrderLine"."SalesOrderLineRate" "Amount", "SalesOrderLine"."TxnDate", "SalesOrderLine"."RefNumber" "Num", "SalesOrderLine"."CustomerRefFullName" "Name", "SalesOrderLine"."SalesOrderLineDesc", "SalesOrderLine"."IsManuallyClosed", "SalesOrderLine"."IsFullyInvoiced", "SalesOrderLine"."SalesOrderLineIsManuallyClosed", "SalesOrderLine"."PONumber" ,Class.FullName, Memo FROM "SalesOrderLine" "SalesOrderLine" , Class, Customer WHERE SalesOrderLine.ClassRefListID = Class.ListID AND Customer.ListID = SalesOrderLine.CustomerRefListID AND ("SalesOrderLine"."SalesOrderLineInvoiced" IS NULL OR ("SalesOrderLine"."TxnDate">={d '2005-01-01'} AND "SalesOrderLine"."TxnDate"<={d '2008-12-31'}) AND "SalesOrderLine"."IsManuallyClosed"=0 AND "SalesOrderLine"."IsFullyInvoiced"=0 AND "SalesOrderLine"."SalesOrderLineIsManuallyClosed"=0 AND Customer.IsActive = 1) AND SalesOrderLine.CustomerRefFullName = Customer.FullName |
|
|
|
|