PurchaseOrders

Query, insert and update purchase orders for a Xero organisation.

Table Specific Information

The PurchaseOrders table allows you to SELECT, INSERT, and UPDATE PurchaseOrder lines for a Xero organization. The Id column is generated by the add-in; it combines the index of the line item with the unique, Xero-generated PurchaseOrderId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new Purchase Order, the ContactId and Date fields are required in addition to at least one line item. A PurchaseOrder line item must at minimum contain a description (LineItem_Description).

You can insert a new Purchase Order with multiple line items using an XML aggregate. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix. Please refer to the Xero documentation of the Purchase Order API for more details.
```
INSERT INTO PurchaseOrders (Contact_ContactId, LineItemAggregate) VALUES (
  '883628bc-7f22-4234-8a4c-81571816a8a1',
  '<LineItem>
		<Description>Example Description</Description>
   </LineItem>
   <LineItem>
		...
   </LineItem>'
)
```
You can also create multiple Purchase Orders in a single query by using a bulk insert. To do this, you should not include a PurchaseOrderId in your query, then specify the values for the first line item of each PurchaseOrder to add.
```
INSERT INTO PurchaseOrders (Contact_ContactId, LineItem_Description)
  VALUES
('37ec4d02-dc3f-4a4c-a943-e8ae8c7db93a', 'Example1'),
('abcd1234-1234-1b2c-a123-abcde12345ab', 'Example2')
```
You can also insert new line items on an existing object by specifying the Xero-generated value of the existing record. For example, to add 2 new line items to an existing Purchase Order:
```
INSERT INTO PurchaseOrders (PurchaseOrderId, LineItem_Description)
  VALUES
('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'Example1'),
('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'Example2')
```

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

UPDATE

You can update any field that is not read-only.

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the Id of the purchase order.
PurchaseOrderId	String	False	The Id of the purchase order.
PurchaseOrderNumber	String	False	A unique identifier for the purchase order that identifies it to the user.
Reference	String	False	An optional field to store a reference.
Type	String	False	The purchase order type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.
Contact_ContactId	String	False	The Id of the contact.
Contact_Name	String	False	The name of the contact.
DeliveryDate	Date	False	The date the purchase order is to be delivered.
ExpectedArrivalDate	Date	False	The date the purchase is expected to arrive.
DeliveryAddress	String	False	The address the goods are to be delivered to.
AttentionTo	String	False	The person the delivery is going to.
Telephone	String	False	The phone number of the person accepting the delivery.
DeliveryInstructions	String	False	Free-form field for information needed to complete the delivery.
Date	Date	False	The date the purchase order was created.
Status	String	False	The status of the purchase order.
LineAmountTypes	String	False	The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Purchase orders cannot be approved without this field being defined.
LineItem_LineItemId	String	False	The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description	String	False	The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_Quantity	Double	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_UnitAmount	Decimal	False	The unit amount of the subject of the line item. Invoices cannot be approved without these fields.
LineItem_ItemCode	String	False	The user-defined item code.
LineItem_AccountCode	String	False	The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.
LineItem_TaxType	String	False	The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_TaxAmount	Decimal	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_LineAmount	Decimal	False	The total of the Quantity field multiplied by the UnitAmount field with discounts applied.
LineItem_DiscountRate	Double	False	The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type purchase orders.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
SubTotal	Decimal	False	The subtotal of the purchase order, excluding taxes.
TotalTax	Decimal	False	The total tax on the purchase order.
Total	Decimal	False	The total of the purchase order (the subtotal plus the total tax).
LineItem_TrackingCategory1_CategoryId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
UpdatedDateUTC	Datetime	True	The date when the purchase order was last updated.
HasAttachments	Boolean	True	Indicates if the PurchaseOrder has a file attached.
CurrencyCode	String	False	The currency used for the purchase order.
BrandingThemeId	String	False	The Id of the branding theme.
CurrencyRate	Decimal	False	Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Copyright (c) 2024 CData Software, Inc. - All rights reserved.

Build 24.0.9060