SalesOrders
Create, Update, Delete, and Query SalesOrders within Sage UK 50 Accounts.
Table Specific Information
The SalesOrders table allows you to select, insert, update, and delete sales orders within a Sage 50 UK company dataset.
Select
The SalesOrders table returns the sales orders for a Sage 50 UK company dataset.
SELECT * FROM SalesOrders WHERE Date >= '1/1/2015' AND Date < '2/1/2015'
Insert
To create a new SalesOrder record, the TradingAccountUUID field is required.
Sales 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 SalesOrderLineItems table. Note: a CommodityUUID must be set when adding a SalesOrderLineItem, as only existing Commodities can be referenced (to create a new Commodity, the Commodities table can be used).
The following example demonstrates how to insert a new SalesOrder with two line items:
INSERT INTO SalesOrders (TradingAccountUUID, DueDate, PostalAddress1Name, PostalAddress1Type, PostalAddress1Address1, PostalAddress1TownCity, PostalAddress1StateRegion, ItemsAggregate) VALUES ('b83c940f-9d2b-4a35-bf42-28bbe07ef994', True, '05/05/2015', 'Sales Order Delivery Address', 'Shipping', '123 Main St', 'Chapel Hill', 'NC', '<SalesOrderLineItems> <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> </SalesOrderLineItems>')
Update
Any field that is not read-only can be updated.
When updating a SalesOrder record, PostalAddress1UUID and PostalAddress2UUID are required when updating the respective postal address. Note that sales order line items cannot be updated nor deleted.
UPDATE SalesOrders SET PostalAddress1ZipPostCode = '12345' WHERE SalesOrderUUID = '957af245-4678-4ca9-904a-162dcce87e5b' AND PostalAddress1UUID = '28915e09-b2a1-47e1-b193-741b32ba8ba0'
Delete
Deleting a SalesOrder record will also delete all line items and postal addresses associated with the SalesOrder.
DELETE FROM SalesOrders WHERE SalesOrderUUID = '957af245-4678-4ca9-904a-162dcce87e5b'
Columns
Name | Type | ReadOnly | Description |
SalesOrderUUID [KEY] | String | True |
SalesOrder UUID |
TradingAccountUUID | String | False |
SalesOrder Trading Account UUID |
ContactUUID | String | True |
SalesOrder Buyer Contact UUID |
SalesOrderNumber | String | False |
SalesOrder Reference |
InvoiceNumber | String | False |
SalesOrder Invoice Reference |
AllocationStatus | String | False |
SalesOrder Allocation Status |
DeliveryStatus | String | False |
SalesOrder Delivery Status |
InvoiceStatus | String | False |
SalesOrder Invoice Status |
CustomerReference | String | False |
SalesOrder Customer reference |
Type | String | False |
SalesOrder Type |
CopyFlag | Boolean | False |
SalesOrder Copy Flag |
DueDate | Date | False |
SalesOrder Due Date |
CarrierNetPrice | Decimal | False |
SalesOrder Carrier Net Price |
CarrierTaxPrice | Decimal | False |
SalesOrder Carrier Tax Price |
CarrierTotalPrice | Decimal | False |
SalesOrder Carrier Gross Price |
CarrierReference | String | False |
SalesOrder Carrier Reference |
Currency | String | False |
SalesOrder Currency |
OCCurrency | String | False |
SalesOrder Operating Company Currency |
OCCurrencyExchangeRate | Decimal | False |
SalesOrder Operating Company Currency Exchange Rate |
OCCurrencyExchangeRateOperator | String | False |
SalesOrder Operating Company Currency Exchange Rate Operator |
Date | Date | False |
SalesOrder Date |
User | String | False |
SalesOrder User |
LineCount | Decimal | False |
SalesOrder Line Count |
OrderDiscountType | String | False |
SalesOrder Order Discount Type |
OrderDiscountAmount | Decimal | False |
SalesOrder Order Discount Amount |
OrderDiscountPercent | Decimal | False |
SalesOrder Order Discount Percent |
Text1 | String | False |
SalesOrder Text1 |
Text2 | String | False |
SalesOrder Text2 |
NetTotal | Decimal | False |
SalesOrder Net Total |
TaxTotal | Decimal | False |
SalesOrder Tax Total |
GrossTotal | Decimal | False |
SalesOrder Gross Total |
ItemsAggregate | String | False |
An aggregate of the line item data, which can be used for adding a purchase order and its line items. |
PostalAddress1UUID | String | True |
SalesOrder PostalAddress UUID |
PostalAddress1Active | Boolean | False |
SalesOrder PostalAddress Active Flag |
PostalAddress1Reference | String | False |
SalesOrder PostalAddress Reference |
PostalAddress1Name | String | False |
SalesOrder PostalAddress Name |
PostalAddress1Description | String | False |
SalesOrder PostalAddress Description |
PostalAddress1Address1 | String | False |
SalesOrder PostalAddress Address 1 |
PostalAddress1Address2 | String | False |
SalesOrder PostalAddress Address 2 |
PostalAddress1Address3 | String | False |
SalesOrder PostalAddress Address 3 |
PostalAddress1Address4 | String | False |
SalesOrder PostalAddress Address 4 |
PostalAddress1TownCity | String | False |
SalesOrder PostalAddress TownCity |
PostalAddress1County | String | False |
SalesOrder PostalAddress County |
PostalAddress1StateRegion | String | False |
SalesOrder PostalAddress StateRegion |
PostalAddress1ZipPostCode | String | False |
SalesOrder PostalAddress ZipPost Code |
PostalAddress1Country | String | False |
SalesOrder PostalAddress Country |
PostalAddress1PrimacyIndicator | Boolean | False |
SalesOrder PostalAddress Primacy Indicator |
PostalAddress1Type | String | False |
SalesOrder PostalAddress Type. Sample values: Billing, Shipping. |
PostalAddress2UUID | String | True |
SalesOrder PostalAddress UUID |
PostalAddress2Active | Boolean | False |
SalesOrder PostalAddress Active Flag |
PostalAddress2Reference | String | False |
SalesOrder PostalAddress Reference |
PostalAddress2Name | String | False |
SalesOrder PostalAddress Name |
PostalAddress2Description | String | False |
SalesOrder PostalAddress Description |
PostalAddress2Address1 | String | False |
SalesOrder PostalAddress Address 1 |
PostalAddress2Address2 | String | False |
SalesOrder PostalAddress Address 2 |
PostalAddress2Address3 | String | False |
SalesOrder PostalAddress Address 3 |
PostalAddress2Address4 | String | False |
SalesOrder PostalAddress Address 4 |
PostalAddress2TownCity | String | False |
SalesOrder PostalAddress TownCity |
PostalAddress2County | String | False |
SalesOrder PostalAddress County |
PostalAddress2StateRegion | String | False |
SalesOrder PostalAddress StateRegion |
PostalAddress2ZipPostCode | String | False |
SalesOrder PostalAddress ZipPost Code |
PostalAddress2Country | String | False |
SalesOrder PostalAddress Country |
PostalAddress2PrimacyIndicator | Boolean | False |
SalesOrder PostalAddress Primacy Indicator |
PostalAddress2Type | String | False |
SalesOrder 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. |