PurchaseOrders
Create, Update, Delete, and Query PurchaseOrders within Sage UK 50 Accounts.
Table Specific Information
The PurchaseOrders table allows you to select, insert, update, and delete purchase orders within a Sage 50 UK company dataset.
Select
The PurchaseOrders table returns the purchase orders for a Sage 50 UK company dataset.
SELECT * FROM PurchaseOrders WHERE Date >= '1/1/2015' AND Date < '2/1/2015'
Insert
To create a new PurchaseOrder record, the TradingAccountUUID field is required.
Purchase Order Line Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PurchaseOrderLineItems table.
The following example demonstrates how to insert a new PurchaseOrder with two line items:
INSERT INTO PurchaseOrders (TradingAccountUUID, DeliveryDate, PostalAddressName, PostalAddressType, PostalAddressAddress1, PostalAddressTownCity, PostalAddressStateRegion, ItemsAggregate) VALUES ('b83c940f-9d2b-4a35-bf42-28bbe07ef994', '05/05/2015', 'Purchase Order Billing Address', 'Billing', '123 Main St', 'Chapel Hill', 'NC', '<PurchaseOrderLineItems> <Row><CommodityUUID>0a47b43d-7115-4863-9689-1a53123e67c8</CommodityUUID><Quantity>1</Quantity><ActualPrice>1.99</ActualPrice></Row> <Row><CommodityUUID>586df2bb-7702-4228-b464-e8d509148547</CommodityUUID><Quantity>4</Quantity><ActualPrice>4.99</ActualPrice></Row> </PurchaseOrderLineItems>')
Update
Any field that is not read-only can be updated.
When updating a PurchaseOrder record, PostalAddressUUID is required when updating a postal address. Note that purchase order line items cannot be updated nor deleted.
UPDATE PurchaseOrders SET PostalAddressZipPostCode = '12345' WHERE PurchaseOrderUUID = '1b47b43a-7115-4863-9679-1a78636e67c1' AND PostalAddressUUID = '28915e09-b2a1-47e1-b193-741b32ba8ba0'
Delete
Deleting a PurchaseOrder record will also delete all line items and postal addresses associated with the PurchaseOrder.
DELETE FROM PurchaseOrders WHERE PurchaseOrderUUID = '1b47b43a-7115-4863-9679-1a78636e67c1'
Columns
Name | Type | ReadOnly | Description |
PurchaseOrderUUID [KEY] | String | True |
PurchaseOrder UUID |
TradingAccountUUID | String | False |
Trading Account UUID associated with the PurchaseOrder |
ContactUUID | String | True |
PurchaseOrder Seller Contact UUID |
PurchaseOrderNumber | String | False |
PurchaseOrder Reference |
Reference2 | String | False |
PurchaseOrder Reference2 |
Status | String | False |
PurchaseOrder Status |
StatusFlagText | String | False |
PurchaseOrder Status Flag Text |
SupplierReference | String | False |
PurchaseOrder Supplier Reference |
CopyFlag | Boolean | False |
PurchaseOrder Copy Flag |
DeliveryDate | Date | False |
PurchaseOrder Delivery Date |
CarrierNetPrice | Decimal | False |
PurchaseOrder Carrier Net Price |
CarrierTaxPrice | Decimal | False |
PurchaseOrder Carrier Tax Price |
CarrierTotalPrice | Decimal | False |
PurchaseOrder Carrier Gross Price |
CarrierReference | String | False |
PurchaseOrder Carrier reference |
Currency | String | False |
PurchaseOrder Currency |
OCCurrencyExchangeRate | Decimal | False |
PurchaseOrder Operating Company Currency Exchange Rate |
OCCurrencyExchangeRateOperator | String | False |
PurchaseOrder Operating Company Currency Exchange Rate Operator |
Date | Date | False |
PurchaseOrder Date |
User | String | False |
PurchaseOrder User |
LineCount | Decimal | True |
PurchaseOrder Line Count |
Text1 | String | False |
PurchaseOrder Text1 |
Text2 | String | False |
PurchaseOrder Text2 |
NetTotal | Decimal | True |
PurchaseOrder Net Total |
TaxTotal | Decimal | True |
PurchaseOrder Tax Total |
GrossTotal | Decimal | True |
PurchaseOrder Gross Total |
OnOrder | String | False |
PurchaseOrder On Order Status |
ItemsAggregate | String | False |
An aggregate of the line item data, which is used for adding a purchase order and its line items. |
PostalAddressUUID | String | True |
PurchaseOrder PostalAddress UUID |
PostalAddressActive | Boolean | False |
PurchaseOrder PostalAddress Active Flag |
PostalAddressReference | String | False |
PurchaseOrder PostalAddress Reference |
PostalAddressName | String | False |
PurchaseOrder PostalAddress Name |
PostalAddressDescription | String | False |
PurchaseOrder PostalAddress Description |
PostalAddressAddress1 | String | False |
PurchaseOrder PostalAddress Address 1 |
PostalAddressAddress2 | String | False |
PurchaseOrder PostalAddress Address 2 |
PostalAddressAddress3 | String | False |
PurchaseOrder PostalAddress Address 3 |
PostalAddressAddress4 | String | False |
PurchaseOrder PostalAddress Address 4 |
PostalAddressTownCity | String | False |
PurchaseOrder PostalAddress TownCity |
PostalAddressCounty | String | False |
PurchaseOrder PostalAddress County |
PostalAddressStateRegion | String | False |
PurchaseOrder PostalAddress StateRegion |
PostalAddressZipPostCode | String | False |
PurchaseOrder PostalAddress ZipPost Code |
PostalAddressCountry | String | False |
PurchaseOrder PostalAddress Country |
PostalAddressPrimacyIndicator | Boolean | False |
PurchaseOrder PostalAddress Primacy Indicator |
PostalAddressType | String | False |
PurchaseOrder PostalAddress Type. Sample values: Billing, Shipping. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Updated | Datetime |
An input used internally to filter records updated at a specific datetime. |