Knowledgebase
[QODBC-Desktop] How to run an Open Purchase Orders by Job Report in QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Problem Description:

Can you please tell me which tables and fields link the purchase order payments to purchase orders?

I can't seem to track that. Basically, I need to see how much has been paid against a purchase order and that the outstanding balance is.

 

Solution:

You can refer below sp_report or can query PurchaseOrderLine table as described below & can get required details.

Typical Open Purchase Orders by Job Report in QuickBooks

Report Displayed in QODBC

Stored Procedure Command

      With QODBC the same report can be generated using stored procedure reports like this:

      sp_report OpenPOsByJob show Text as Type, Blank as Total, Date,
      DeliveryDate as "Deliv Date", RefNumber as Num, SourceName, Item, Quantity as Qty,
      ReceivedQuantity as Rcvd, UnitPrice as "Cost Price", Amount
      parameters DateMacro = 'All'

Display in QODBC

 

Access the Same Data from PurchaseOrderLine Table

SELECT Command

      SELECT PurchaseOrderLineCustomerRefFullName as Type, TxnDate as Date, DueDate as "Deliv Date",
      RefNumber as "Num", VendorRefFullName as SourceName,PurchaseOrderLineItemRefFullName as Item,
      PurchaseOrderLineQuantity as Qty, PurchaseOrderLineReceivedQuantity as Rcvd,
      PurchaseOrderLineRate as Cost, TotalAmount as Amount FROM PurchaseOrderLine where IsFullyReceived = 0
      and PurchaseOrderLineCustomerRefFullName is not Null order by PurchaseOrderLineCustomerRefFullName

(231 vote(s))
Helpful
Not helpful

Comments (4)
Nook
20 September 2012 08:01 AM
That's the pfercet insight in a thread like this.
Jeff Mayfield
11 August 2015 09:01 PM
i have checked both Purchaseorder and purchaseorderline and both tables have isfullyreceived as null. that field doesnt have any data in it. yes i know for a fact i have multiple POs that are still outstanding. i can also run the report in QBs and it returns accurate data. I also dont see PurchaseOrderLineReceivedQuantity having any data as well.

on a separate note i am trying to get a list of all inventory items to show qty on hand, reorder point and other fields but i cant find which table contains that info. i have tried item and iteminventory and those fields are null...
Jack
12 August 2015 02:17 PM
Hi Jeff,

I would like to suggest you to please change the following value on the Optimizer tab and try again.

QODBC Setup screen --> Optimizer --> Under "For balances and stock quantities" select Either "Dirty Reads"/"Real Time"

Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2457/45/balancecurrentbalancequantityonhand-is-missing-value-or-showing-null-value
Jeff Mayfield
12 August 2015 03:05 PM
BINGO! that worked. thank so much!
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).