JDBC Driver for Sage 50 UK

Build 22.0.8462

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-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.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462