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-1a53666e67c8</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.