JDBC Driver for QuickBooks POS

Build 22.0.8462

PurchaseOrders

Create, update, delete, and query QuickBooks POS Purchase Orders.

Table Specific Information

The PurchaseOrders table allows you to SELECT, INSERT, UPDATE, and DELETE Purchase Orders within QuickBooks POS.

Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator.

To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available.

To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.


SELECT * FROM PurchaseOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert

To create a new Purchase Order record, the PurchaseOrderNumber and VendorListId fields are required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PurchaseOrderItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Purchase Order with two items:

INSERT INTO PurchaseOrders (PurchaseOrderNumber, VendorListId, ItemsAggregate) 
VALUES ('PO12345', '-9876543210987654321', '<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemCost>10.00</ItemCost><ItemQty>25</ItemQty></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemCost>20.00</ItemCost><ItemQty>25</ItemQty></Row>
</PurchaseOrderItems>')

Update

Any field that is not read-only can be updated.

When updating a Purchase Order record, items can be added or modified via the ItemsAggregate column.

To modify an existing item in a Purchase Order record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.

To add a new item, the ItemListId that corresponds to the item to be added must be specified, just as in an insert.

Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of only the existing items.

Modify Items Example: Total three items with ItemTxnLineId values of 1, 2, and 3. Item 1 is updated, Item 2 is left alone, and Item 3 will be deleted.

UPDATE PurchaseOrders SET ItemsAggregate='<PurchaseOrderItems>
<Row><ItemTxnLineId>1</ItemTxnLineId><ItemSize>L</ItemSize></Row>
<Row><ItemTxnLineId>2</ItemTxnLineId></Row>
</PurchaseOrderItems>'
WHERE TxnId='-1234567890123456789'

Add New Item Example: Existing items remain intact when adding new items.

UPDATE PurchaseOrders SET ItemsAggregate='<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>30.00</ItemCost><ItemQty>10</ItemQty></Row>
</PurchaseOrderItems>' 
WHERE TxnId='-1234567890123456789'

Columns

Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single

The unique identifier, generated by QuickBooks POS.

Associate String False Range

The employee making the adjustment.

CancelDate Datetime False Range

The date after which delivery may be refused. If the company preferences for receiving are set to check for cancel dates, the end user is alerted when receiving items against a purchase order past this cancel date. (The user can still accept shipments, at their discretion.) If the company preferences for receiving are not set to check the cancel date, the cancel date is ignored.

CompanyName String True Range

The name of the business.

Discount Double False Range

The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.

DiscountPercent Double False Range

Enter a percentage discount applicable to the purchase order. Discounts are applied to the purchase order subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.

Fee Decimal False Range

You can enter a fee using this field. Note that fees entered on a purchase order (such as a special handling fee) are not spread over the cost of individual items. When a receiving voucher references a purchase order with a fee entered, the fee can be transferred to the voucher at that point.

Instructions String False Range

You can use this optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on purchase orders but is not displayed on-screen.

ItemsCount String True Range

The number of line items in the insert that added the transaction.

ItemsAggregate String False

An aggregate of the line item data, which can be used for adding a transaction and its line items.

PurchaseOrderNumber String False Range

A user-defined number identifying the purchase order. This value should be unique. This number is automatically assigned to the purchase order by QBPOS if left empty during the creation process.

PurchaseOrderStatusDesc String False Single

All purchase orders have the status of either Open, Closed, or Custom. The Custom status is defined by the QBPOS user in the company preferences for purchasing.

QtyDue Double True

The total document quantity remaining to be received. Updated by receiving vouchers.

QtyOrdered Double True Range

The total quantity ordered, combined for all items.

QtyReceived Double True Range

The total quantity received to date.

SalesOrderNumber String True Range

This is a unique number automatically assigned to the sales order at creation by QBPOS. If a sales order was used to create the purchase order, the SalesOrderNumber for the purchase order is returned.

ShipToStoreNumber Double False Range

The store to which the item is to be shipped.

StartShipDate Datetime False Range

The estimated shipping date for the order. Included on purchase journal and merchandise on-order reports.

StoreExchangeStatus String True Single

In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.

StoreNumber Double False Range

This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.

Subtotal Decimal True Range

The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.

Terms String True

While not visible on the purchase order, any payment terms that are defined for the vendor file are carried to the purchase order.

TermsDiscount Decimal False Range

The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.

TermsDiscountDays Double False Range

The number of days after the invoice date when payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.

TermsNetDays Double False Range

Payment must be made within this number of days, counting from the invoice date.

Total Decimal True Range

The total amount, after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.

TxnDate Datetime False Range

The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.

UnfilledPercent String True Range

The percentage of the total purchase order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.

VendorCode String True Range

The code assigned to the vendor.

VendorListID String False Single

A reference to the vendor.

CustomFieldsOwnerID String False Multi

Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).

CustomFields String False

Custom fields returned from QuickBooks POS and formatted into XML.

TimeCreated Datetime True Range

When the transaction was created.

TimeModified Datetime True Range

When the transaction was last modified.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462