PurchaseOrderItems

Create, update, delete, and query QuickBooks POS Purchase Order Items.

Table Specific Information

Select

QuickBooks Point of Sale 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 PurchaseOrderItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert

Insert can be used to create a new Purchase Order record or to add an item to an existing Purchase Order record.

To create a new Purchase Order record, the PurchaseOrderNumber and VendorListId fields are required.

INSERT INTO PurchaseOrderItems (PurchaseOrderNumber, VendorListId, ItemListId, ItemCost, ItemQty) 
VALUES ('PO12345', '-9876543210987654321', '-1000000000000000001', 15.00, 10)

To add an item to an existing record, the TxnId column of the Purchase Order record that the item is to be added to and the ItemListId are required.

INSERT INTO PurchaseOrderItems (TxnId, ItemListId, ItemCost, ItemQty) 
VALUES ('-1234567890123456789', '-1000000000000000001', 15.00, 10)

Update

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

Note: Update operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record, and another to update the record with new data.

To perform an update, TxnId and ItemTxnLineId are required fields.

UPDATE PurchaseOrderItems SET ItemSize='L' WHERE TxnId='-1234567890123456789' AND ItemTxnLineId='1'

Delete

Delete is used to remove an item from the specified record (it will not delete the entire record itself).

Note: Deletion of an item requires that all the wanted items that currently exist in the record be sent in the request, thus deleting those that are not specified. Therefore Delete operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record to obtain all the existing ItemTxnLineIds and another to delete the specified item.

To perform a delete, TxnId and ItemTxnLineId are required fields.

DELETE FROM PurchaseOrderItems WHERE TxnId='-1234567890123456789' AND ItemTxnLineId='3'

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	Enter 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. (That user can still accept the shipment, 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	Decimal	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 transaction. 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 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.
PurchaseOrderNumber	String	False	Range	The user-defined number identifying the purchase order. This value should be unique. This number is assigned to the purchase order by QBPOS if left empty during the creation process.
PurchaseOrderStatusDesc	String	False	Single	All purchase orders have a 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 purchase order quantity ordered, combined for all items.
QtyReceived	Double	True	Range	The total document quantity received to date.
SalesOrderNumber	String	True	Range	A unique number 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. 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		Although 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 in which 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.
ItemTxnLineID# [KEY]	String	True		Identification number of the transaction line. (TxnLineId is supported as of version 2.0 of the SDK. With QBXML 1.0 and 1.1, TxnLineId is always returned as zero.) If you need to add a new transaction line in an update, you can do so by setting the TxnLineId to -1.
ItemListID#	String	False		The unique identifier for the item, generated by QuickBooks POS.
ItemALU#	String	False		Alternate lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
ItemAttribute#	String	False		A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters.
ItemCost#	Decimal	False		The average cost of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos.
ItemDesc1#	String	False		The principal item description field. This description is printed on receipts.
ItemDesc2#	String	False		Secondary description field, usually used for any additional information to describe this particular item, such as a vendor's catalog number. Also can be used for additional information at the point of sale.
ItemExtendedCost#	Decimal	False		The quantity ordered multiplied by the item cost.
ItemItemNumber#	Double	True		Unique number assigned to the item when it is added to QBPOS.
ItemNumberOfBaseUnits#	Double	True		The base unit, used to track and report the inventory quantity.
ItemQty#	Double	False		The quantity of the line item being ordered or transferred.
ItemQtyReceived#	Double	True		The item quantity received to date.
ItemSize#	String	False		The size of an item. This field can also be used to describe a second item attribute other than size if desired. This field must be 1-8 characters.
ItemUnitOfMeasure#	String	False		If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.
ItemUPC#	String	False		The UPC, EAN, or ISBN of the item. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field.