This page provides a guide to Establishing a Connection to Sage 200 in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Sage 200

Establishing a Connection shows how to authenticate to Sage 200 and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Sage 200 through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Sage 200 by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Sage 200

Before connecting, you must ensure that your account is registered for Sage API access. To do this:

1. Navigate to https://developer.columbus.sage.com/docs/services/api/uk/subscribe.
2. Select a subscription that contains the API of your Sage 200 edition (StandardUK or ProfessionalUK). The Sage 200 Unlimited subscription should contain the APIs of both editions.
3. Log in to the service to which you subscribed, and look at your profile. Make a note of the SubscriptionKey you find there.

To connect to Sage 200, specify the following connection properties:

• Schema: Specify the edition of Sage 200 in use; either StandardUK or ProfessionalUK.
• SubscriptionKey: The value you just retrieved from your subscription profile, above.

Authenticating to Sage 200

Sage 200 provides embedded OAuth credentials that simplify connection from a Desktop application . To connect from a Web application, you must create a custom OAuth application, as described in Creating a Custom OAuth Application.

To connect via OAuth from all authentication flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to Sage 200 from the available oauth flows. For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that already have embedded OAuth credentials, see Creating a Custom OAuth Application. For a complete list of connection string properties available in Sage 200, see Connection.

When the access token expires, the Cloud refreshes it automatically.

Creating a Custom OAuth Application

CData embeds OAuth Application Credentials with CData branding that can be used when connecting to Sage 200 via a desktop application or a headless machine.

However, you must create a custom OAuth application to connect to Sage 200 via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway. Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure

To obtain the OAuth client credentials, you must contact Sage directly. To read more about how to do this, see Sage 200 API - Guide to Sage ID Changes.

When you are ready to begin the process:

1. Go to Sage 200 API Credentials Request Form.
2. Fill out the form to configure your custom OAuth application:
   • Specify the desired Refresh token expiry time in days. We suggest you set this to the maximum number of days since you will need to initiate OAuth again once the Refresh token expires.
   • If your custom OAuth application is intended for use with a Web Application, specify a Redirect URL(s). Set this to a trusted URL where users will return after they authorize your application. It is required by Sage to include URLs that start with https.

   Sage creates the desired custom OAuth application and responds with values for the OAuthClientId (Client Id), the OAuthClientSecret (Client Secret), and the Scope.

3. To ensure that the account is registered for API access, follow this link: https://developer.columbus.sage.com/docs/services/api/uk/subscribe.

If you need further assistance from Sage, see https://developer.columbus.sage.com/contactus.

Other Properties

• CompanyId: The specific company to connect to. It should only be set if you have multiple companies. The CompanyId should be sent to you by Sage when each company is created.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

This section shows the available API objects and provides more information on executing SQL to Sage 200 APIs.

Key Features

• The Cloud models Sage 200 entities like banks, customers, and products as relational views, allowing you to write SQL to query Sage 200 data.
• Stored procedures allow you to execute operations to Sage 200, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your Sage 200 account are immediately reflected when using the Cloud.

Sage 200 StandardUK Data Model

Sage200 StandardUK Data Model describes the schemas available to connect to Sage 200 Standard UK accounts. You can use tables to work with live Sage 200 data. You can use stored procedures provided by CData Cloud to automate working with Sage 200 data.

Sage 200 ProfessionalUK Data Model

Sage200 ProfessionalUK Data Model describes the schemas available to connect to Sage 200 Professional UK accounts. You can use tables to work with live Sage 200 data. You can use stored procedures provided by CData Cloud to automate working with Sage 200 data.

Views

Views describes the available views. Views are statically defined to model Banks, Customers, Products, and more.

Stored Procedures

Stored Procedures are function-like interfaces to Sage 200. Stored procedures allow you to execute operations to Sage 200, such as retrieving the OAuth Access Token.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Sage 200 Views

Name	Description
BankPostedTransactions	Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.
Banks	Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.
CompanyDetails	Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.
CostCentres	Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.
Currencies	Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.
CustomerContacts	Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
CustomerDeliveryAddresses	A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.
Customers	Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.
Departments	Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.
FinancialReportLayouts	Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.
FinancialYearPeriods	This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.
NominalCodes	Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.
NominalReportCategories	Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).
NominalTransactionAnalysisCodes	Nominal transaction analysis codes are used to group nominal analysis items on various transaction types
PendingEmails	When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.
PriceBands	A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.
ProductGroups	Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.
ProductPrices	This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.
Products	Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.
ProductTransactions	A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.
PurchaseOrderLines	When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.
PurchaseOrders	Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.
PurchasePostedTransactions	Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.
SalesHistoricalTransactions	Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions.
SalesOrderAdditionalCharges	Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.
SalesOrderLines	When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.
SalesOrderProformaLines	This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.
SalesOrderQuoteLines	This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.
SalesOrders	Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.
SalesPostedTransactions	Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.
SalesTradingPeriods	This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)
SalesTransactions	When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.
StockSettings	These settings to define how to process and manage your stock.
SupplierContacts	Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
Suppliers	Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).
TaxCodes	Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.
WarehouseHoldings	All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.
Warehouses	All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: NominalAnalysisItems,TaxAnalysisItems,TradeItems columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM BankPostedTransactions WHERE Id = 29448
SELECT * FROM BankPostedTransactions WHERE BankId = 29401
SELECT * FROM BankPostedTransactions WHERE BankTransactionType = 'Payment'
SELECT * FROM BankPostedTransactions WHERE EntryDescription = 'Opening Balance'
SELECT * FROM BankPostedTransactions WHERE Reference = 'O/Bal'
SELECT * FROM BankPostedTransactions WHERE SecondReference = 'abc'
SELECT * FROM BankPostedTransactions WHERE TransactionDate = '2017-12-31T01:00:00.000+01:00'
SELECT * FROM BankPostedTransactions WHERE PostedDate = '2017-09-13T02:00:00.000+02:00'
SELECT * FROM BankPostedTransactions WHERE ChequeValue = -6511.96
SELECT * FROM BankPostedTransactions WHERE ChequeDiscountValue = 0
SELECT * FROM BankPostedTransactions WHERE ChequeToAccountCurrencyRate = 1
SELECT * FROM BankPostedTransactions WHERE BankChequeToBaseCurrencyRate = 1
SELECT * FROM BankPostedTransactions WHERE ChequeCurrencyId = 2103
SELECT * FROM BankPostedTransactions WHERE Urn = 24
SELECT * FROM BankPostedTransactions WHERE UserName = 'Migration'
SELECT * FROM BankPostedTransactions WHERE DateTimeUpdated = '2018-02-28T15:23:46.467+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Id.
BankId	Long	Banks.Id	Bank Id. See banks
BankTransactionType	String		Transaction type. See bank_transaction_types
EntryDescription	String		Transaction description.
Reference	String		Transaction reference.
SecondReference	String		Second reference.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
ChequeValue	Decimal		Value of the entry.
ChequeDiscountValue	Decimal		Entry discount value.
ChequeToAccountCurrencyRate	Decimal		Exchange rate between the bank account currency and the cheque currency.
BankChequeToBaseCurrencyRate	Decimal		Exchange rate between the bank account and the company base currency.
ChequeCurrencyId	Long		Currency Id. See currencies
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See bank_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See bank_posted_transactions_tax_analysis_items
TradeItems	String		Trade items. See bank_posted_transactions_trade_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: BankAddressId,BankAddressContact,BankAddressCountry,BankAddressCity,BankAddressPostcode,BankAddress_1,BankAddress_2,BankAddress_3,BankAddress_4,BankContacts columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Banks WHERE Id = 29401
SELECT * FROM Banks WHERE Code = '1200'
SELECT * FROM Banks WHERE Description = 'Bank Current Account'
SELECT * FROM Banks WHERE SortCode = '233434'
SELECT * FROM Banks WHERE AccountNo = '003234234'
SELECT * FROM Banks WHERE BankName = 'Lloyds Bank PLC'
SELECT * FROM Banks WHERE AccountType = 'BankAccountTypeCurrent'
SELECT * FROM Banks WHERE AccountStatusType = 'AccountStatusActive'
SELECT * FROM Banks WHERE Balance = -9941.57
SELECT * FROM Banks WHERE BalanceInBase = -9941.57
SELECT * FROM Banks WHERE OverdraftLimit = 0
SELECT * FROM Banks WHERE AccountName = 'Stationery & Computer Mart UK Ltd'
SELECT * FROM Banks WHERE Iban = 'abc'
SELECT * FROM Banks WHERE Bic = 'abc'
SELECT * FROM Banks WHERE StatementBalance = 0
SELECT * FROM Banks WHERE StatementDate = '2019-10-02T11:46:53Z'
SELECT * FROM Banks WHERE AccountCurrencyId = 2103
SELECT * FROM Banks WHERE DateTimeUpdated = '2018-02-28T15:23:46.070+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Bank account record Id.
Code	String		Bank account code.
Description	String		Bank description.
SortCode	String		Bank sort code.
AccountNo	String		Bank account number.
BankName	String		Bank name.
AccountType	String		Bank account type. See bank_account_types
AccountStatusType	String		The status of the bank account (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
Balance	Decimal		Bank balance.
BalanceInBase	Decimal		Bank balance in base currency.
OverdraftLimit	Decimal		Bank overdraft limit.
AccountName	String		Account name.
Iban	String		International bank account number.
Bic	String		Bank identifier code.
StatementBalance	Decimal		Last statement balance.
StatementDate	Date		Last statement date.
AccountCurrencyId	Long	Currencies.Id	Currency record Id. See currencies
BankAddressId	Long		Bank address. Bank address record id.
BankAddressContact	String		Bank address. Address Contact name.
BankAddressCountry	String		Bank address. Country.
BankAddressCity	String		Bank address. City.
BankAddressPostcode	String		Bank address. Postcode.
BankAddress_1	String		Bank address. Address line 1.
BankAddress_2	String		Bank address. Address line 2.
BankAddress_3	String		Bank address. Address line 3.
BankAddress_4	String		Bank address. Address line 4.
BankContacts	String		Collection of bank contact values.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEUMember,AddressCountry,Address_1,Address_2,Address_3,Address_4,AddressCity,AddressCounty,AddressPostcode columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM CompanyDetails WHERE Name = 'Stationery & Computer Mart UK'
SELECT * FROM CompanyDetails WHERE Telephone = '0191 955 3000'
SELECT * FROM CompanyDetails WHERE Fax = '0191 955 3001'
SELECT * FROM CompanyDetails WHERE Email = '[email protected]'
SELECT * FROM CompanyDetails WHERE Website = 'http://www.stationerycomputermartuk.co.uk'
SELECT * FROM CompanyDetails WHERE TaxRegistrationNumber = '376 6823 94'
SELECT * FROM CompanyDetails WHERE CountryCodeId = 13
SELECT * FROM CompanyDetails WHERE YearsAccountsHeldFor = 2
SELECT * FROM CompanyDetails WHERE EoriNumber = 'abc'
SELECT * FROM CompanyDetails WHERE DateTimeUpdated = '2018-02-28T15:23:46.040+01:00'

Columns

Name	Type	References	Description
Name [KEY]	String		Company name.
Telephone	String		Telephone number.
Fax	String		Fax number.
Email	String		Email address.
Website	String		Company website address.
TaxRegistrationNumber	String		Tax registration number.
CountryCodeId	Long		Country code record Id. See country_codes
CountryCode	String		Country code record Id. See country_codes
CountryCodeName	String		Country code record Id. See country_codes
CountryCodeEUMember	Boolean		Country code record Id. See country_codes
YearsAccountsHeldFor	Int		Number of years that periods with nominal data exist for up to a maximum of six years.
AddressCountry	String		The company's address Id.
Address_1	String		The company's address. Address line 1.
Address_2	String		The company's address. Address line 2.
Address_3	String		The company's address. Address line 3.
Address_4	String		The company's address. Address line 4.
AddressCity	String		The company's address. City
AddressCounty	String		The company's address. County
AddressPostcode	String		The company's address. Postcode
EoriNumber	String		The company's EORI number.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM CostCentres WHERE Id = 1015
SELECT * FROM CostCentres WHERE Code = 'abc'
SELECT * FROM CostCentres WHERE Name = 'abc'
SELECT * FROM CostCentres WHERE DateTimeUpdated = '2018-02-28T15:23:46.230+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Cost centre record Id.
Code	String		Cost centre code.
Name	String		Cost centre name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CurrencyIsoCode,CurrencyIsoCodeName columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Currencies WHERE Id = 2103
SELECT * FROM Currencies WHERE Symbol = '£'
SELECT * FROM Currencies WHERE Name = 'Pound Sterling'
SELECT * FROM Currencies WHERE CoreCurrencyRate = 1
SELECT * FROM Currencies WHERE EuroCurrencyRate = 0.714285
SELECT * FROM Currencies WHERE CurrencyIsoCodeId = 49
SELECT * FROM Currencies WHERE IsBaseCurrency = true
SELECT * FROM Currencies WHERE IsEuroCurrency = false
SELECT * FROM Currencies WHERE DateTimeUpdated = '2018-02-28T15:23:46.243+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Currency record Id.
Symbol	String		Currency symbol.
Name	String		Currency name.
CoreCurrencyRate	Decimal		Currency rate in use against base.
EuroCurrencyRate	Decimal		Euro rate.
CurrencyIsoCodeId	Long		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCode	String		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCodeName	String		ISO Currency Code Record Id. See currency_iso_codes
IsBaseCurrency	Bool		Flag to indicate if the currency is the base currency.
IsEuroCurrency	Bool		Flag to indicate if the currency is Euro.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM CustomerContacts WHERE Id = 42101
SELECT * FROM CustomerContacts WHERE CustomerId = '0000000001'
SELECT * FROM CustomerContacts WHERE Name = 'Sample'

Columns

Name	Type	References	Description
Id [KEY]	Long		Customer contact record Id.
CustomerId	Long		Customer record Id.
Name	String		Contact Name - Concatenated first_name, middle_name and last_name.
FirstName	String		Contact first name.
MiddleName	String		Contact middle name.
LastName	String		Contact surname.
IsDefault	Bool		Is this the default contact for the parent customer. See customer
DefaultTelephone	String		Default telephone number.
DefaultEmail	String		Default email address.
SalutationId	Long		Contact salutation id.
SalutationIsDefault	Boolean		Contact salutation.
SalutationCode	String		Contact salutation code.
SalutationDescription	String		Contact salutation description.
Emails	String		Customer emails. See customer_emails
Telephones	String		Customer telephones. See customer_telephones
Mobiles	String		Customer mobiles. See customer_mobiles
Faxes	String		Customer faxes. See customer_faxes
Websites	String		Customer websites. See customer_websites
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM CustomerDeliveryAddresses WHERE Id = 27927
SELECT * FROM CustomerDeliveryAddresses WHERE Contact = 'Lee Dalkin'
SELECT * FROM CustomerDeliveryAddresses WHERE Country = 'abc'
SELECT * FROM CustomerDeliveryAddresses WHERE CustomerId = 27825
SELECT * FROM CustomerDeliveryAddresses WHERE Description = 'Registered address'
SELECT * FROM CustomerDeliveryAddresses WHERE Email = '[email protected]'
SELECT * FROM CustomerDeliveryAddresses WHERE Fax = '01742 876 236'
SELECT * FROM CustomerDeliveryAddresses WHERE IsDefault = false
SELECT * FROM CustomerDeliveryAddresses WHERE PostalName = 'A1 Design Services'
SELECT * FROM CustomerDeliveryAddresses WHERE TaxCodeId = 1729
SELECT * FROM CustomerDeliveryAddresses WHERE TaxNumber = 'GB238 3839 38'
SELECT * FROM CustomerDeliveryAddresses WHERE Telephone = '01742 876 234'
SELECT * FROM CustomerDeliveryAddresses WHERE Address1 = '67a Station Road'
SELECT * FROM CustomerDeliveryAddresses WHERE Address2 = 'abc'
SELECT * FROM CustomerDeliveryAddresses WHERE Address3 = 'Blackpool'
SELECT * FROM CustomerDeliveryAddresses WHERE Address4 = 'Lancashire'
SELECT * FROM CustomerDeliveryAddresses WHERE City = 'abc'
SELECT * FROM CustomerDeliveryAddresses WHERE County = 'abc'
SELECT * FROM CustomerDeliveryAddresses WHERE Postcode = 'BP12 7HT'
SELECT * FROM CustomerDeliveryAddresses WHERE AddressCountryCodeId = 0
SELECT * FROM CustomerDeliveryAddresses WHERE DateTimeUpdated = '2018-02-28T15:23:46.867+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique id of the customer delivery address.
Contact	String		The contact associated with the customer delivery address.
Country	String		Country (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
CustomerId	Long		Unique Id of the customer account the customer delivery address is associated with. See customers
Description	String		The description of the customer delivery address.
Email	String		The email address associated with the customer delivery address.
Fax	String		The fax number associated with the customer delivery address.
IsDefault	Bool		Flag to indicate if this is the default customer delivery address for the parent customer. See customer
PostalName	String		Postal name is the name of the person or company who the invoice or sales order is addressed to.
TaxCodeId	Long		The tax code record Id. See tax_codes
TaxNumber	String		The tax number.
Telephone	String		The telephone number associated with the customer delivery address.
Address1	String		Address line 1.
Address2	String		Address line 2.
Address3	String		Address line 3.
Address4	String		Address line 4.
City	String		City (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
County	String		County (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
Postcode	String		Postcode.
AddressCountryCodeId	Long		Country code Id. See country_codes
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Customers WHERE Id = 27825
SELECT * FROM Customers WHERE Reference = 'A1D001'
SELECT * FROM Customers WHERE Name = 'A1 Design Services'
SELECT * FROM Customers WHERE ShortName = 'A1 Desig'
SELECT * FROM Customers WHERE Balance = 1120.92
SELECT * FROM Customers WHERE OnHold = false
SELECT * FROM Customers WHERE AccountStatusType = 'AccountStatusActive'
SELECT * FROM Customers WHERE CurrencyId = 2103
SELECT * FROM Customers WHERE ExchangeRateType = 'ExchangeRateSingle'
SELECT * FROM Customers WHERE TelephoneCountryCode = '44'
SELECT * FROM Customers WHERE TelephoneAreaCode = '01742'
SELECT * FROM Customers WHERE TelephoneSubscriberNumber = '876234'
SELECT * FROM Customers WHERE FaxCountryCode = '44'
SELECT * FROM Customers WHERE FaxAreaCode = '01742'
SELECT * FROM Customers WHERE FaxSubscriberNumber = '876236'
SELECT * FROM Customers WHERE Website = 'www.sage.co.uk'
SELECT * FROM Customers WHERE CreditLimit = 0
SELECT * FROM Customers WHERE DefaultTaxCodeId = 1729
SELECT * FROM Customers WHERE VatNumber = 'GB238 3839 38'
SELECT * FROM Customers WHERE DunsCode = 'abc'
SELECT * FROM Customers WHERE AnalysisCode1 = 'Trade'
SELECT * FROM Customers WHERE AnalysisCode2 = 'George'
SELECT * FROM Customers WHERE AnalysisCode3 = 'Lancashire'
SELECT * FROM Customers WHERE AnalysisCode4 = 'abc'
SELECT * FROM Customers WHERE AnalysisCode5 = 'abc'
SELECT * FROM Customers WHERE AverageTimeToPay = 0
SELECT * FROM Customers WHERE ValueOfCurrentOrdersInSop = 340.38
SELECT * FROM Customers WHERE CountryCodeId = 13
SELECT * FROM Customers WHERE DateTimeUpdated = '2018-03-05T11:19:18.317+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Customer unique Id.
Reference	String		Customer account reference. NOTE: Not required if customer reference is set to
Name	String		Customer name.
ShortName	String		Customer short name.
Balance	Decimal		Customer account balance.
OnHold	Bool		True if customer account is on hold, else False.
AccountStatusType	String		The status of the customer account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId	Long	Currencies.Id	Currency record Id. See currencies
ExchangeRateType	String		The type of exchange rate used on the customer account. See exchange_rate_types
TelephoneCountryCode	String		Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode	String		Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber	String		Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode	String		Fax country code.
FaxAreaCode	String		Fax area code.
FaxSubscriberNumber	String		Fax subscriber number.
Website	String		Website address.
CreditLimit	Decimal		Credit limit for the customer.
DefaultTaxCodeId	Long	TaxCodes.Id	Default tax code record Id. See tax_codes
VatNumber	String		VAT registration number.
DunsCode	String		DUNS number.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
AverageTimeToPay	Int		The customer average time to pay.
ValueOfCurrentOrdersInSop	Decimal		Value of current sales orders for this customer.
CountryCodeId	Long		Country code record Id.
CountryCode	String		The customers country code.
CountryCodeName	String		The customers country code name.
CountryCodeEuMember	Boolean		Shows if the customer is Eu Member.
MainAddress_1	String		The customers main address line 1.
MainAddress_2	String		The customers main address line 2.
MainAddress_3	String		The customers main address line 3.
MainAddress_4	String		The customers main address line 4.
MainAddressCity	String		The customers main address city.
MainAddressCounty	String		The customers main address county.
MainAddressPostcode	String		The customers main address postcode.
Contacts	String		Customer contacts. See customer_contacts
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Departments WHERE Id = 1016
SELECT * FROM Departments WHERE Code = 'abc'
SELECT * FROM Departments WHERE Name = 'abc'
SELECT * FROM Departments WHERE DateTimeUpdated = '2018-02-28T15:23:46.277+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Department record Id.
Code	String		Department code.
Name	String		Department name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM FinancialReportLayoutViews WHERE LayoutId = 46151
SELECT * FROM FinancialReportLayoutViews WHERE LayoutAccountReportType = 'ProfitLoss'
SELECT * FROM FinancialReportLayoutViews WHERE LayoutName = 'Sample Profit and Loss Layout'
SELECT * FROM FinancialReportLayoutViews WHERE RowId = 47221
SELECT * FROM FinancialReportLayoutViews WHERE RowLineType = 'Text'
SELECT * FROM FinancialReportLayoutViews WHERE RowLayoutPositionType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE RowLayoutDebitOrCreditType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE RowReportRow = 1
SELECT * FROM FinancialReportLayoutViews WHERE RowTitle = 'Sales'
SELECT * FROM FinancialReportLayoutViews WHERE RowSubtotalGroup = 1
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak1 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak2 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak3 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak4 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak5 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak6 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak7 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak8 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak9 = 0
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryId = 123
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryCode = 'abc'
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryDescription = 'abc'

Columns

Name	Type	References	Description
LayoutId [KEY]	Long		Financial report layout record Id.
LayoutAccountReportType	String		The type of the financial layout. See financial_report_layout_types
LayoutName	String		financial report layout name.
RowId	Long		An asscoiated financial report row Id.
RowLineType	String		The type of the financial layout. See financial_report_row_line_types
RowLayoutPositionType	String		The type of the financial report row layout position. See financial_report_row_layout_position_types
RowLayoutDebitOrCreditType	String		The type of the financial layout. See financial_report_row_layout_debit_or_credit_types
RowReportRow	Int		Value to show the line number of the report row in the layout.
RowTitle	String		The row title.
RowSubtotalGroup	Int		Value to represent the sub total group.
RowGroupBreak1	Int		Value to represent the report row group break 1.
RowGroupBreak2	Int		Value to represent the report row group break 2.
RowGroupBreak3	Int		Value to represent the report row group break 3.
RowGroupBreak4	Int		Value to represent the report row group break 4.
RowGroupBreak5	Int		Value to represent the report row group break 5.
RowGroupBreak6	Int		Value to represent the report row group break 6.
RowGroupBreak7	Int		Value to represent the report row group break 7.
RowGroupBreak8	Int		Value to represent the report row group break 8.
RowGroupBreak9	Int		Value to represent the report row group break 9.
NominalAccountReportCategoryId	Long		An Id specific to each nominal account report category. See nominal_report_category_types
NominalAccountReportCategoryCode	String		The nominal account report category code.
NominalAccountReportCategoryDescription	String		The nominal account report category description.

This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearId = 1
SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearStartDate = '2018-01-01T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearEndDate = '2018-12-31T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE YearRelativeToCurrentYear = 0
SELECT * FROM FinancialYearPeriodViews WHERE NumberOfPeriodsInYear = 12
SELECT * FROM FinancialYearPeriodViews WHERE AccountingPeriodId = 1
SELECT * FROM FinancialYearPeriodViews WHERE PeriodNumber = 1
SELECT * FROM FinancialYearPeriodViews WHERE PeriodStartDate = '2018-01-01T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE PeriodEndDate = '2018-01-31T01:00:00.000+01:00'

Columns

Name	Type	References	Description
FinancialYearId [KEY]	Long		An Id specific to each financial year.
FinancialYearStartDate	Date		Date the financial year starts. The returned string includes the time but this will always be 00:00:00.
FinancialYearEndDate	Date		Date the financial year ends. The returned string includes the time but this will always be 00:00:00.
YearRelativeToCurrentYear	Int		Value to represent the year relative to the current financial year. The current financial year is 0. The first previous year is -1. The first future year is 1.
NumberOfPeriodsInYear	Int		The number of periods in the financial year.
AccountingPeriodId	Long		An Id specific to each accounting period.
PeriodNumber	Int		Value to show the period in relation to the associated year. For example, for a year that starts in January, January will be period 1, February period 2 etc.
PeriodStartDate	Date		Date the period starts. The returned string includes the time but this will always be 00:00:00.
PeriodEndDate	Date		Date the period finished. The returned string includes the time but this will always be 00:00:00.

Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM NominalCodes WHERE Id = 2317
SELECT * FROM NominalCodes WHERE Name = 'Freehold Property'
SELECT * FROM NominalCodes WHERE Reference = '0010'
SELECT * FROM NominalCodes WHERE CostCentreId = 1015
SELECT * FROM NominalCodes WHERE CostCentreCode = 'abc'
SELECT * FROM NominalCodes WHERE DepartmentId = 1016
SELECT * FROM NominalCodes WHERE DepartmentCode = 'abc'
SELECT * FROM NominalCodes WHERE NominalAccountType = 'NominalAccountTypePosting'
SELECT * FROM NominalCodes WHERE AccountStatusType = 'AccountStatusActive'
SELECT * FROM NominalCodes WHERE ReportCategoryId = 2289
SELECT * FROM NominalCodes WHERE DisplayBalancesInSelectionList = false
SELECT * FROM NominalCodes WHERE BalanceYearToDate = 123.3
SELECT * FROM NominalCodes WHERE DateTimeUpdated = '2018-02-28T15:23:46.717+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Nominal code record Id.
Name	String		Nominal code name.
Reference	String		Nominal code reference.
CostCentreId	Long	CostCentres.Id	Nominal code cost centre record Id. See cost_centres
CostCentreCode	String		Cost centre code.
DepartmentId	Long	Departments.Id	Nominal code department record Id. See departments
DepartmentCode	String		Department code.
NominalAccountType	String		The type of the nominal code. See nominal_account_types
AccountStatusType	String		The status of the nominal code (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
ReportCategoryId	Long		Financial report category record Id. See nominal_report_categories
DisplayBalancesInSelectionList	Bool		Whether balances for the nominal code can be shown in selection lists.
BalanceYearToDate	Decimal		The nominal account balance, if balances can be shown for the nominal account.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM NominalReportCategories WHERE Id = 2258
SELECT * FROM NominalReportCategories WHERE AccountReportType = 'ProfitLoss'
SELECT * FROM NominalReportCategories WHERE Code = '1'
SELECT * FROM NominalReportCategories WHERE Description = 'Sales'
SELECT * FROM NominalReportCategories WHERE AccountReportCategoryType = 'NominalReportingCategoryTypeIncome'
SELECT * FROM NominalReportCategories WHERE DateTimeUpdated = '2018-02-28T15:23:45.960+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Nominal report category record Id.
AccountReportType	String		The type of the report.. See financial_report_layout_types
Code	String		Nominal report category code.
Description	String		Nominal report category description.
AccountReportCategoryType	String		The type of the report category. See nominal_report_category_types
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Nominal transaction analysis codes are used to group nominal analysis items on various transaction types

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM NominalTransactionAnalysisCodes WHERE Code = 'Promo0715'
SELECT * FROM NominalTransactionAnalysisCodes WHERE Description = 'Promotion July 2015'
SELECT * FROM NominalTransactionAnalysisCodes WHERE Active = true
SELECT * FROM NominalTransactionAnalysisCodes WHERE DateTimeUpdated = '2018-02-28T15:23:46.413+01:00'

Columns

Name	Type	References	Description
Code [KEY]	String		Nominal transaction analysis code.
Description	String		Nominal transaction analysis code description.
Active	Bool		Boolean indicating whether the transaction analysis code is active.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM PendingEmails WHERE Id = 123
SELECT * FROM PendingEmails WHERE EmailType = 'abc'
SELECT * FROM PendingEmails WHERE EmailAddress = 'abc'
SELECT * FROM PendingEmails WHERE EmailSubject = 'abc'
SELECT * FROM PendingEmails WHERE EmailBody = 'abc'
SELECT * FROM PendingEmails WHERE UserNumber = 123
SELECT * FROM PendingEmails WHERE UserName = 'abc'
SELECT * FROM PendingEmails WHERE DateTimeUpdated = '2019-10-02T11:46:53Z'

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique id of the Sage 200 pending email identifier.
EmailType	String		The type of pending email (which provides the intended purpose).
EmailAddress	String		The email address of the email recipient.
EmailSubject	String		The text to use for the subject of the email.
EmailBody	String		The text to use for the body of the email.
UserNumber	Long		The user id of the email recipient.
UserName	String		The user name of the email recipient.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM PriceBands WHERE Id = 1064
SELECT * FROM PriceBands WHERE Name = 'Standard'
SELECT * FROM PriceBands WHERE Description = 'Standard'
SELECT * FROM PriceBands WHERE IsStandardBand = true
SELECT * FROM PriceBands WHERE CurrencyId = 2103
SELECT * FROM PriceBands WHERE PriceBandTypeId = 0
SELECT * FROM PriceBands WHERE IsActive = true
SELECT * FROM PriceBands WHERE IsTimeBased = false
SELECT * FROM PriceBands WHERE ActiveDateTimeFrom = '2019-10-02T11:46:53Z'
SELECT * FROM PriceBands WHERE ActiveDateTimeTo = '2019-10-02T11:46:53Z'
SELECT * FROM PriceBands WHERE DateTimeUpdated = '2018-02-28T15:23:46.183+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Price band record Id.
Name	String		Price band name.
Description	String		Price band description.
IsStandardBand	Bool		Is this the Standard price band.
CurrencyId	Long		Currency record Id. See currencies
PriceBandTypeId	Long		Price band type record Id; this will be 0 for Universal price bands and 1 for Limited price bands.
IsActive	Bool		Is this price band active.
IsTimeBased	Bool		Is this price band time based.
ActiveDateTimeFrom	Datetime		The date and time this price band is active from.
ActiveDateTimeTo	Datetime		The date and time this price band is active to.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM ProductGroups WHERE Id = 34632
SELECT * FROM ProductGroups WHERE Code = 'NONS'
SELECT * FROM ProductGroups WHERE Description = 'Non Stock Item'
SELECT * FROM ProductGroups WHERE ProductType = 'EnumStockItemTypeMisc'
SELECT * FROM ProductGroups WHERE CanLevelsGoNegative = false
SELECT * FROM ProductGroups WHERE DateTimeUpdated = '2018-02-28T15:23:46.310+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Product group Id.
Code	String		Product group code.
Description	String		Product group description.
ProductType	String		Product group type. See product_types
CanLevelsGoNegative	Bool		Can stock levels go negative.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

 SELECT * FROM ProductPriceViews WHERE ProductPriceId = 34760
SELECT * FROM ProductPriceViews WHERE ProductId = 34751
SELECT * FROM ProductPriceViews WHERE ProductCode = 'BOARD001'
SELECT * FROM ProductPriceViews WHERE ProductName = 'Whiteboard - Drywipe (900 x 1200)'
SELECT * FROM ProductPriceViews WHERE ProductStockUnitName = 'Each'
SELECT * FROM ProductPriceViews WHERE PriceBandId = 1064
SELECT * FROM ProductPriceViews WHERE PriceBandName = 'Standard'
SELECT * FROM ProductPriceViews WHERE ProductPriceUseStandard = true
SELECT * FROM ProductPriceViews WHERE ProductPricePrice = 20
SELECT * FROM ProductPriceViews WHERE CurrencyId = 2103
SELECT * FROM ProductPriceViews WHERE CurrencyName = 'Pound Sterling'
SELECT * FROM ProductPriceViews WHERE CurrencySymbol = '£'
SELECT * FROM ProductPriceViews WHERE DateTimeUpdated = '2018-02-28T15:23:46.787+01:00'

Columns

Name	Type	References	Description
ProductPriceId [KEY]	Long		Product price record Id.
ProductId [KEY]	Long		Product record Id. See products
ProductCode	String		Product code.
ProductName	String		Product name.
ProductStockUnitName	String		The name of the unit the product is sold in. For example box, crate, bottle. The default unit is each.
PriceBandId	Long		Price band record Id.
PriceBandName	String		The name of the price band.
ProductPriceUseStandard	Bool		'True' if uses the price from the standard price band is used otherwise 'False' The price of a product can be linked to the price set in the standard price band. This is used when only some product prices are different to the standard price.
ProductPricePrice	Decimal		The selling price for the shown product and price band.
CurrencyId	Long		Currency record Id. See currencies
CurrencyName	String		The name of the currency used for the price.
CurrencySymbol	String		The symbol of the currency used for the price.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: WarehouseHoldings columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Products WHERE Id = 34737
SELECT * FROM Products WHERE Code = 'M'
SELECT * FROM Products WHERE Name = 'M'
SELECT * FROM Products WHERE Description = 'abc'
SELECT * FROM Products WHERE Barcode = 'abc'
SELECT * FROM Products WHERE AllowSalesOrder = true
SELECT * FROM Products WHERE ProductGroupId = 34633
SELECT * FROM Products WHERE TaxCodeId = 1731
SELECT * FROM Products WHERE DateTimeUpdated = '2019-08-01T18:00:33.140+02:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Product record Id.
Code	String		Product code.
Name	String		Product name.
Description	String		Product description.
Barcode	String		Product barcode.
AllowSalesOrder	Bool		Allowed on a sales order.
ProductGroupId	Long		Product group record Id. See product_groups
TaxCodeId	Long		Tax code record Id. See tax_codes
WarehouseHoldings	String		If stock setting UseMultipleLocations is true, you must specify warehouse holding(s) when creating products. If stock setting UseMultipleLocations is false, then new product will be automatically linked to the default (home) warehouse. See warehouse_holdings
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM ProductTransactionViews WHERE Id = 36511
SELECT * FROM ProductTransactionViews WHERE ProductId = 34751
SELECT * FROM ProductTransactionViews WHERE ProductCode = 'BOARD001'
SELECT * FROM ProductTransactionViews WHERE ProductGroupId = 34640
SELECT * FROM ProductTransactionViews WHERE ProductGroupCode = '0006'
SELECT * FROM ProductTransactionViews WHERE TransactionType = 'EnumSecondaryActivityTypeStockTakeAdjustmentIn'
SELECT * FROM ProductTransactionViews WHERE TransactionDate = '2015-12-31T01:00:00.000+01:00'
SELECT * FROM ProductTransactionViews WHERE SourceAreaReference = 'abc'
SELECT * FROM ProductTransactionViews WHERE SourceAreaName = 'Internal'
SELECT * FROM ProductTransactionViews WHERE Reference = 'abc'
SELECT * FROM ProductTransactionViews WHERE SecondReference = 'STK TAKE'
SELECT * FROM ProductTransactionViews WHERE WarehouseName = 'Warehouse'
SELECT * FROM ProductTransactionViews WHERE BinName = 'Unspecified'
SELECT * FROM ProductTransactionViews WHERE Quantity = 2
SELECT * FROM ProductTransactionViews WHERE UnitCostPrice = 15
SELECT * FROM ProductTransactionViews WHERE UnitIssuePrice = 0
SELECT * FROM ProductTransactionViews WHERE UnitDiscountValue = 0
SELECT * FROM ProductTransactionViews WHERE TotalOrderDiscount = 0
SELECT * FROM ProductTransactionViews WHERE CostValue = 30
SELECT * FROM ProductTransactionViews WHERE IssueValue = 0
SELECT * FROM ProductTransactionViews WHERE Memo = 'abc'
SELECT * FROM ProductTransactionViews WHERE UserName = 'Migration'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode1 = 'abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode2 = 'abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode3 = 'abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode4 = 'abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode5 = 'abc'
SELECT * FROM ProductTransactionViews WHERE DateTimeUpdated = '2018-02-28T15:23:46.123+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Product transaction Id.
ProductId	Long		Product record Id. See products
ProductCode	String		Product code.
ProductGroupId	Long		Product group record Id. See product_groups
ProductGroupCode	String		Product group code. See product_groups
TransactionType	String		Enum value that describes the transaction that created the product movement. See product_transaction_types
TransactionDate	Date		The date of the transaction.
SourceAreaReference	String		The code of the source of the transaction.
SourceAreaName	String		The name of the source of the transaction.
Reference	String		This can be an optional reference added to the transaction by the user, or an automatically generated reference such as the order number.
SecondReference	String		This can be an additional optional reference added to the transaction by the user, or an automatically generated reference such as the despatch note number.
WarehouseName	String		The warehouse the product was moved from or to. See warehouses
BinName	String		The name of the bin to which the transaction relates to.
Quantity	Decimal		The quantity on the transaction.
UnitCostPrice	Decimal		The cost price per unit for this transaction.
UnitIssuePrice	Decimal		The price used when products are moved out of stock.
UnitDiscountValue	Decimal		The discount amount per unit used for this transaction.
TotalOrderDiscount	Decimal		The total discount value of the transaction.
CostValue	Decimal		The total cost value.
IssueValue	Decimal		The total issue or sales price of the transaction.
Memo	String		Any memo note that was applied to the product transaction.
UserName	String		The name of the Sage 200 user that created the transaction.
AnalysisCode1	String		Analysis Code 1.
AnalysisCode2	String		Analysis Code 2.
AnalysisCode3	String		Analysis Code 3.
AnalysisCode4	String		Analysis Code 4.
AnalysisCode5	String		Analysis Code 5.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM PurchaseOrderLines WHERE PurchaseOrderId = 42101
SELECT * FROM PurchaseOrderLines WHERE CurrencyId = '0000000001'

Columns

Name	Type	References	Description
PurchaseOrderId [KEY]	Long	PurchaseOrders.Id	Unique ID of the purchase order. See _orders
SupplierDocumentNo	String		Supplier purchase order document number.
CurrencyId	Long		The Id for the currency used for the purchase order. See currencies
CurrencyName	String		The name for the currency used for the purchase order.
CurrencySymbol	String		The symbol of the currency used for the purchase order.
SubtotalValue	Decimal		The subtotal value for the order.
SubtotalDiscountValue	Decimal		The subtotal discount value for the order.
SubtotalLandedCostsValue	Decimal		The subtotal landed costs value for the order.
LineId	Long		Associated purchase order line Id. See _order_lines
LineNumber	Short		Purchase order line number.
LineType	String		Purchase order line type. See order_return_line_types
LineProductCode	String		Purchase order line product code.
LineProductDescription	String		Purchase order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity	Decimal		The quantity ordered.
LineUnitPrice	Decimal		The buying price per item.
LineUnitDiscountPercent	Decimal		The percentage discount per item.
LineUnitDiscountValue	Decimal		The discount value per item.
LineTotalValue	Decimal		The total value of the line.
LineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the _order_line_unit_discount_value from the _order_line_unit_price.
LineLandedCostsValue	Decimal		The value of landed costs for the order.
LineAnalysisCode1	String		Analysis code 1.
LineAnalysisCode2	String		Analysis code 2.
LineAnalysisCode3	String		Analysis code 3.
LineAnalysisCode4	String		Analysis code 4.
LineAnalysisCode5	String		Analysis code 5.
LineDateTimeUpdated	Datetime		The date and time the order line was last updated (UTC).
LineForDirectDelivery	Bool		Indicates if line is marked for direct delivery
LineShowOnSupplierDocs	Bool		Indicates if line will show on supplier documents.
DeliveryAddrPostalName	String		delivery address name.
DeliveryAddrDescription	String		delivery address description.
DeliveryAddrAddress1	String		delivery address line 1.
DeliveryAddrAddress2	String		delivery address line 2.
DeliveryAddrAddress3	String		delivery address line 3.
DeliveryAddrAddress4	String		delivery address line 4.
DeliveryAddrCity	String		delivery address city.
DeliveryAddrCounty	String		delivery address county.
DeliveryAddrPostCode	String		delivery address post code
DeliveryAddrCountry	String		delivery address country.
DeliveryAddrContact	String		delivery address contact
DeliveryAddrTelephone	String		delivery address telephone number
DeliveryAddrFax	String		delivery address line fax number...
DeliveryAddrEmail	String		delivery address line email.
SupplierId	Long		The associated supplier record Id. See suppliers
SupplierReference	String		The associated supplier account reference.
SupplierName	String		The associated supplier name.
ProductId	Long		The associated product record Id. Only returned where _order_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where _order_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where _order_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where _order_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where _order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where _order_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM PurchaseOrders WHERE Id = 42101
SELECT * FROM PurchaseOrders WHERE DocumentNo = '0000000001'
SELECT * FROM PurchaseOrders WHERE ExchangeRate = 'Sample'

Columns

Name	Type	References	Description
Id [KEY]	Long		Purchase order record Id.
DocumentNo	String		Purchase order document number. Note: If the POP setting in Sage 200c Professional, then this property MUST be set.
DocumentDate	Date		Purchase order document date.
DocumentStatus	String		Document status. See pop_document_status_types
AuthorisationStatus	String		Authorisation status. See pop_authorisation_status_types
SupplierId	Long	Suppliers.Id	Supplier record Id. See suppliers
ExchangeRate	Decimal		Exchange rate.
SubtotalGoodsValue	Decimal		Goods value (sum of stock items and free text).
SubtotalChargesNetValue	Decimal		Charges net value.
SubtotalDiscountValue	Decimal		Discount value.
SubtotalLandedCostsValue	Decimal		Landed costs value.
TotalNetValue	Decimal		Net value (sum of all line types).
TotalTaxValue	Decimal		Tax value.
TotalGrossValue	Decimal		Gross value.
SupplierDocumentNo	String		Supplier document number.
SettlementDiscountDays	Short		Settlement discount days.
SettlementDiscountPercent	Decimal		Settlement discount percent.
DocumentDiscountPercent	Decimal		Document discount percent.
DocumentOriginatorId	Long		Document originator id. See users
DocumentOriginatorName	String		Document originator name.
DocumentCreatedById	Long		Document created by id. See users
DocumentCreatedBy	String		Document created by.
RequestedDeliveryDate	Date		Requested delivery date.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
Lines	String		The purchase order lines collection. See pop_order_lines
DeliveryAddress_1	String		The delivery address line 1.
DeliveryAddress_2	String		The delivery address line 2.
DeliveryAddress_3	String		The delivery address line 3.
DeliveryAddress_4	String		The delivery address line 4.
DeliveryAddressCity	String		The delivery address city.
DeliveryAddressCounty	String		The delivery address county.
DeliveryAddressPostcode	String		The delivery address postcode.
DeliveryAddressContact	String		The delivery address contact.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: NominalAnalysisItems,TaxAnalysisItems,BankItems,AllocationSessionItems,AllocationHistoryItems columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM PurchasePostedTransactions WHERE Id = 33018
SELECT * FROM PurchasePostedTransactions WHERE SupplierId = 28936
SELECT * FROM PurchasePostedTransactions WHERE TraderTransactionType = 'TradingAccountEntryTypeInvoice'
SELECT * FROM PurchasePostedTransactions WHERE Reference = 'O/BAL'
SELECT * FROM PurchasePostedTransactions WHERE SecondReference = 'Opening Balance'
SELECT * FROM PurchasePostedTransactions WHERE Queried = 'abc'
SELECT * FROM PurchasePostedTransactions WHERE TransactionDate = '2017-12-31T01:00:00.000+01:00'
SELECT * FROM PurchasePostedTransactions WHERE PostedDate = '2018-09-13T02:00:00.000+02:00'
SELECT * FROM PurchasePostedTransactions WHERE DueDate = '2024-01-30T01:00:00.000+01:00'
SELECT * FROM PurchasePostedTransactions WHERE DocumentGoodsValue = 55
SELECT * FROM PurchasePostedTransactions WHERE DocumentGrossValue = 55
SELECT * FROM PurchasePostedTransactions WHERE DocumentTaxValue = 0
SELECT * FROM PurchasePostedTransactions WHERE DocumentDiscountValue = 0
SELECT * FROM PurchasePostedTransactions WHERE DiscountPercent = 0
SELECT * FROM PurchasePostedTransactions WHERE DocumentTaxDiscountValue = 0
SELECT * FROM PurchasePostedTransactions WHERE DocumentAllocatedValue = 55
SELECT * FROM PurchasePostedTransactions WHERE DocumentOutstandingValue = 0
SELECT * FROM PurchasePostedTransactions WHERE BaseGoodsValue = 55
SELECT * FROM PurchasePostedTransactions WHERE BaseGrossValue = 55
SELECT * FROM PurchasePostedTransactions WHERE BaseTaxValue = 0
SELECT * FROM PurchasePostedTransactions WHERE BaseDiscountValue = 0
SELECT * FROM PurchasePostedTransactions WHERE BaseTaxDiscountValue = 0
SELECT * FROM PurchasePostedTransactions WHERE BaseAllocatedValue = 55
SELECT * FROM PurchasePostedTransactions WHERE ControlValueInBaseCurrency = 0
SELECT * FROM PurchasePostedTransactions WHERE ExchangeRate = 1
SELECT * FROM PurchasePostedTransactions WHERE SettledImmediately = false
SELECT * FROM PurchasePostedTransactions WHERE VatAdjustmentDocExpected = false
SELECT * FROM PurchasePostedTransactions WHERE DiscountDays = 0
SELECT * FROM PurchasePostedTransactions WHERE Urn = 11
SELECT * FROM PurchasePostedTransactions WHERE UserName = 'Migration'
SELECT * FROM PurchasePostedTransactions WHERE DateTimeUpdated = '2018-02-28T15:23:46.137+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Id.
SupplierId	Long		Supplier Id. See customers
TraderTransactionType	String		Transaction type. See trader_transaction_types
Reference	String		Transaction reference.
SecondReference	String		Second reference.
Queried	String		Query flag.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
DueDate	Date		Due date.
DocumentGoodsValue	Decimal		Value of the goods.
DocumentGrossValue	Decimal		Gross value.
DocumentTaxValue	Decimal		Tax value.
DocumentDiscountValue	Decimal		Tax discount value.
DiscountPercent	Decimal		Percentage discount.
DocumentTaxDiscountValue	Decimal		Tax discount value.
DocumentAllocatedValue	Decimal		Allocated value.
DocumentOutstandingValue	Decimal		Outstanding value.
BaseGoodsValue	Decimal		Value of the goods in base currency.
BaseGrossValue	Decimal		Gross value in base currency.
BaseTaxValue	Decimal		Tax value in base currency.
BaseDiscountValue	Decimal		Discount value in base currency.
BaseTaxDiscountValue	Decimal		Tax discount value in base currency.
BaseAllocatedValue	Decimal		Allocated value in base currency.
ControlValueInBaseCurrency	Decimal		Control value in base currency.
ExchangeRate	Decimal		Exchange rate used for transaction.
SettledImmediately	Bool		Immediately settled
VatAdjustmentDocExpected	Bool		Whether a VAT adjustment is expected.
DiscountDays	Short		Number of days discount valid.
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See purchase_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See purchase_posted_transactions_tax_analysis_items
BankItems	String		Bank items. See purchase_posted_transactions_bank_items
AllocationSessionItems	String		Allocation session items. See purchase_posted_transactions_allocation_session_items
AllocationHistoryItems	String		Allocation history items. See purchase_posted_transactions_allocation_history_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: NominalAnalysisItems,TaxAnalysisItems,BankItems columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesHistoricalTransactions WHERE Id = 123
SELECT * FROM SalesHistoricalTransactions WHERE CustomerId = 123
SELECT * FROM SalesHistoricalTransactions WHERE TraderTransactionType = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE Reference = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE SecondReference = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE Queried = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE TransactionDate = '2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE PostedDate = '2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE DueDate = '2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE FullSettlementDate = '2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE DocumentGoodsValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentGrossValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentTaxValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentDiscountValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DiscountPercent = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentTaxDiscountValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentAllocatedValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentOutstandingValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseGoodsValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseGrossValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseTaxValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseDiscountValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseTaxDiscountValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseAllocatedValue = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE ControlValueInBaseCurrency = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE ExchangeRate = 123.3
SELECT * FROM SalesHistoricalTransactions WHERE SettledImmediately = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE DiscountDays = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE Urn = 123
SELECT * FROM SalesHistoricalTransactions WHERE UserName = 'abc'
SELECT * FROM SalesHistoricalTransactions WHERE DateTimeUpdated = '2019-10-02T11:46:53Z'

Columns

Name	Type	References	Description
Id [KEY]	Long		Id.
CustomerId	Long		Customer Id. See customers
TraderTransactionType	String		Transaction type. See trader_transaction_types
Reference	String		Transaction reference.
SecondReference	String		Second reference.
Queried	String		Query flag.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
DueDate	Date		Due date.
FullSettlementDate	Date		Full settlement date.
DocumentGoodsValue	Decimal		Value of the goods.
DocumentGrossValue	Decimal		Gross value.
DocumentTaxValue	Decimal		Tax value.
DocumentDiscountValue	Decimal		Document discount value.
DiscountPercent	Decimal		Percentage discount.
DocumentTaxDiscountValue	Decimal		Tax discount value.
DocumentAllocatedValue	Decimal		Allocated value.
DocumentOutstandingValue	Decimal		Outstanding value.
BaseGoodsValue	Decimal		Value of the goods in base currency.
BaseGrossValue	Decimal		Gross value in base currency.
BaseTaxValue	Decimal		Tax value in base currency.
BaseDiscountValue	Decimal		Discount value in base currency.
BaseTaxDiscountValue	Decimal		Tax discount value in base currency.
BaseAllocatedValue	Decimal		Allocated value in base currency.
ControlValueInBaseCurrency	Decimal		Control value in base currency.
ExchangeRate	Decimal		Exchange rate used for transaction.
SettledImmediately	Bool		Immediately settled.
DiscountDays	Short		Number of days discount valid.
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See sales_historical_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See sales_historical_transactions_tax_analysis_items
BankItems	String		Bank items. See sales_historical_transactions_bank_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesOrderAdditionalCharges WHERE Id = 42101
SELECT * FROM SalesOrderAdditionalCharges WHERE Code = '0000000001'
SELECT * FROM SalesOrderAdditionalCharges WHERE Name = 'Sample'

Columns

Name	Type	References	Description
Id [KEY]	Long		Sop additional charge record Id.
Code	String		Sop additional charge code.
Name	String		Sop additional charge name.
ChargeValue	Decimal		Sop additional charge value.
NotionalCostValue	Decimal		Sop additional charge notional cost value.
NominalCodeId	Long	NominalCodes.Id	Nominal code record Id. See nominal_codes
TaxCodeId	Long	TaxCodes.Id	Tax code record Id. See tax_codes
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesOrderLines WHERE SalesOrderId = 42101
SELECT * FROM SalesOrderLines WHERE CurrencyId = 2343234543
SELECT * FROM SalesOrderLines WHERE CurrencyName = 'Sample'

Columns

Name	Type	References	Description
SalesOrderId [KEY]	Long		Unique Id of the sales order. See sop_orders
LineId [KEY]	Long		Associated sales order line Id. See sop_order_lines
CustomerDocumentNo	String		Customer sales order document number.
CurrencyId	Long		The Id for the currency used for the sales order. See currencies
CurrencyName	String		The name for the currency used for the sales order.
CurrencySymbol	String		The symbol of the currency used for the sales order.
DocumentDiscountPercent	Decimal		Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy	String		The person who created the SOP Order
RequestedDeliveryDate	Date		Requested delivery date.
PromisedDeliveryDate	Date		Promised delivery date.
LineNumber	Short		Sales order line number.
LineType	String		Sales order line type. See order_return_line_types
LineProductCode	String		Sales order line product code.
LineProductDescription	String		Sales order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity	Decimal		The quantity ordered.
LineAllocatedQuantity	Decimal		The quantity allocated.
LineDespatchReceiptQuantity	Decimal		The quantity despatched.
LineInvoiceCreditQuantity	Decimal		The quantity invoiced.
LineSellingUnitDescription	String		The selling unit description.
LineSellingUnitPrice	Decimal		The selling price per item.
LineUnitDiscountPercent	Decimal		The percentage discount per item.
LineUnitDiscountValue	Decimal		The discount value per item.
LineCostPrice	Decimal		The cost price.
LineTotalValue	Decimal		The total value of the line.
LineLineTaxValue	Decimal		The tax value of the line.
LineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_order_line_unit_discount_value from the sop_order_line_selling_unit_price.
LineRequestedDeliveryDate	Date		Requested delivery date.
LinePromisedDeliveryDate	Date		Promised delivery date.
LineAnalysisCode1	String		Analysis code 1.
LineAnalysisCode2	String		Analysis code 2.
LineAnalysisCode3	String		Analysis code 3.
LineAnalysisCode4	String		Analysis code 4.
LineAnalysisCode5	String		Analysis code 5.
LineDateTimeUpdated	Datetime		The date and time the sop order line was last updated (UTC).
DeliveryAddrAddress1	String		SOP delivery address line 1.
DeliveryAddrAddress2	String		SOP delivery address line 2.
DeliveryAddrAddress3	String		SOP delivery address line 3.
DeliveryAddrAddress4	String		SOP delivery address line 4.
DeliveryAddrCity	String		SOP delivery address city.
DeliveryAddrCounty	String		SOP delivery address county.
DeliveryAddrPostCode	String		SOP delivery address post code
DeliveryAddrCountry	String		SOP delivery address country.
DeliveryAddrContact	String		SOP delivery address contact
DeliveryAddrTelephone	String		SOP delivery address telephone number
DeliveryAddrFax	String		SOP delivery address line fax number...
DeliveryAddrEmail	String		SOP delivery address line email.
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_order_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_order_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_order_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesOrderProformaLines WHERE SalesOrderProformaId = 346345654564565
SELECT * FROM SalesOrderProformaLines WHERE SalesOrderProformaDocumentStatus = 'Sample'

Columns

Name	Type	References	Description
ProformaId [KEY]	Long		Unique Id of the sales pro forma
ProformaDocumentNo	String		Sales pro forma document number.
ProformaDocumentDate	Date		Sales pro forma date.
ProformaDocumentStatus	String		Sales pro forma status. See sop_document_status_types
ProformaCustomerDocumentNo	String		Customer sales pro forma document number.
ProformaCurrencyId	Long		The Id for the currency used for the sales pro forma. See currencies
ProformaCurrencyName	String		The name for the currency used for the sales pro forma.
ProformaCurrencySymbol	String		The symbol of the currency used for the sales pro forma.
ProformaExchangeRate	Decimal		Exchange rate for the sales pro forma.
ProformaRequestedDeliveryDate	Date		Requested delivery date.
ProformaPromisedDeliveryDate	Date		Promised delivery date.
ProformaAnalysisCode1	String		Analysis code 1.
ProformaAnalysisCode2	String		Analysis code 2.
ProformaAnalysisCode3	String		Analysis code 3.
ProformaAnalysisCode4	String		Analysis code 4.
ProformaAnalysisCode5	String		Analysis code 5.
ProformaDateTimeUpdated	Datetime		The date and time the sales pro forma was last updated (UTC).
ProformaLineId	Long		Associated sales pro forma line Id
ProformaLineNumber	Short		Sales pro forma line number.
ProformaLineType	String		Sales pro forma line type. See order_return_line_types
ProformaLineProductCode	String		Sales pro forma line product code.
ProformaLineProductDescription	String		Sales pro forma line product description. This could be stock item description, free text, additional charge or comment.
ProformaLineQuantity	Decimal		The line quantity.
ProformaLineSellingUnitDescription	String		The selling unit description.
ProformaLineSellingUnitPrice	Decimal		The selling price per item.
ProformaLineUnitDiscountPercent	Decimal		The percentage discount per item.
ProformaLineUnitDiscountValue	Decimal		The discount value per item.
ProformaLineCostPrice	Decimal		The cost price.
ProformaLineTotalValue	Decimal		The total value of the line.
ProformaLineLineTaxValue	Decimal		The tax value of the line.
ProformaLineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_proforma_line_unit_discount_value from the sop_proforma_line_selling_unit_price.
ProformaLineRequestedDeliveryDate	Date		Requested delivery date.
ProformaLinePromisedDeliveryDate	Date		Promised delivery date.
ProformaLineAnalysisCode1	String		Analysis code 1.
ProformaLineAnalysisCode2	String		Analysis code 2.
ProformaLineAnalysisCode3	String		Analysis code 3.
ProformaLineAnalysisCode4	String		Analysis code 4.
ProformaLineAnalysisCode5	String		Analysis code 5.
ProformaLineDateTimeUpdated	Datetime		The date and time the sop pro forma line was last updated (UTC).
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_proforma_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_proforma_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_proforma_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_proforma_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_proforma_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_proforma_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteId = 3424324324324
SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteDocumentNo = 'Sample'
SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteCustomerDocumentNo = 'Sample'

Columns

Name	Type	References	Description
QuoteId [KEY]	Long		Unique Id of the sales quote
QuoteDocumentNo	String		Sales quote document number.
QuoteDocumentDate	Date		Sales quote date.
QuoteDocumentStatus	String		Sales quote status. See sop_document_status_types
QuoteCustomerDocumentNo	String		Customer sales quote document number.
QuoteCurrencyId	Long		The Id for the currency used for the sales quote. See currencies
QuoteCurrencyName	String		The name for the currency used for the sales quote.
QuoteCurrencySymbol	String		The symbol of the currency used for the sales quote.
QuoteExchangeRate	Decimal		Exchange rate for the sales quote.
QuoteRequestedDeliveryDate	Date		Requested delivery date.
QuotePromisedDeliveryDate	Date		Promised delivery date.
QuoteAnalysisCode1	String		Analysis code 1.
QuoteAnalysisCode2	String		Analysis code 2.
QuoteAnalysisCode3	String		Analysis code 3.
QuoteAnalysisCode4	String		Analysis code 4.
QuoteAnalysisCode5	String		Analysis code 5.
QuoteDateTimeUpdated	Datetime		The date and time the sales quote was last updated (UTC).
QuoteLineId	Long		Associated sales quote line Id
QuoteLineNumber	Short		Sales quote line number.
QuoteLineType	String		Sales quote line type. See order_return_line_types
QuoteLineProductCode	String		Sales quote line product code.
QuoteLineProductDescription	String		Sales quote line product description. This could be stock item description, free text, additional charge or comment.
QuoteLineQuantity	Decimal		The line quantity.
QuoteLineSellingUnitDescription	String		The selling unit description.
QuoteLineSellingUnitPrice	Decimal		The selling price per item.
QuoteLineUnitDiscountPercent	Decimal		The percentage discount per item.
QuoteLineUnitDiscountValue	Decimal		The discount value per item.
QuoteLineCostPrice	Decimal		The cost price.
QuoteLineTotalValue	Decimal		The total value of the line.
QuoteLineLineTaxValue	Decimal		The tax value of the line.
QuoteLineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_quote_line_unit_discount_value from the sop_quote_line_selling_unit_price.
QuoteLineRequestedDeliveryDate	Date		Requested delivery date.
QuoteLinePromisedDeliveryDate	Date		Promised delivery date.
QuoteLineAnalysisCode1	String		Analysis code 1.
QuoteLineAnalysisCode2	String		Analysis code 2.
QuoteLineAnalysisCode3	String		Analysis code 3.
QuoteLineAnalysisCode4	String		Analysis code 4.
QuoteLineAnalysisCode5	String		Analysis code 5.
QuoteLineDateTimeUpdated	Datetime		The date and time the sop quote line was last updated (UTC).
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_quote_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_quote_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_quote_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_quote_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_quote_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_quote_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesOrders WHERE Id = 42101
SELECT * FROM SalesOrders WHERE DocumentNo = '0000000001'
SELECT * FROM SalesOrders WHERE CustomerDocumentNo = 'Sample'

Columns

Name	Type	References	Description
Id [KEY]	Long		Sales order record Id.
DocumentNo	String		Sales order document number. Note: If the SOP setting in Sage 200c Professional or Sage 200 Extra Online is to NOT automatically generate numbers, then this property MUST be set. If the SOP setting in Sage 200 Extra is to automatically generate numbers, or you are using Sage 200c Standard (which doesn't allow you to set this option), then setting this on POST will be ignored.
DocumentDate	Date		Sales order document date.
DocumentStatus	String		Document status. See sop_document_status_types
CustomerId	Long	Customers.Id	Customer record Id. See customers
ExchangeRate	Decimal		Exchange rate.
SubtotalGoodsValue	Decimal		Goods value (sum of stock items and free text).
SubtotalChargeNetValue	Decimal		Charge net value.
SubtotalChargeTaxValue	Decimal		Charge tax value.
SubtotalDiscountValue	Decimal		Discount value
TotalNetValue	Decimal		Net value (sum of all line types).
TotalTaxValue	Decimal		Tax value.
TotalGrossValue	Decimal		Gross value.
CustomerDocumentNo	String		Customer document number.
UseInvoiceAddress	Bool		True if this order uses the customer invoice address, else False.
SettlementDiscountDays	Short		Settlement discount days.
SettlementDiscountPercent	Decimal		Settlement discount percent.
DocumentDiscountPercent	Decimal		Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy	String		The person who created the SOP Order
RequestedDeliveryDate	Date		Requested delivery date.
PromisedDeliveryDate	Date		Promised delivery date.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
Lines	String		The sales order lines collection. See sop_order_lines
DeliveryAddressId	Long		The delivery address contact.
DeliveryAddress_1	String		The delivery address line 1.
DeliveryAddress_2	String		The delivery address line 2.
DeliveryAddress_3	String		The delivery address line 3.
DeliveryAddress_4	String		The delivery address line 4.
DeliveryAddressCity	String		The delivery address city.
DeliveryAddressCounty	String		The delivery address county.
DeliveryAddressPostcode	String		The delivery address postcode.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: NominalAnalysisItems,TaxAnalysisItems,BankItems,AllocationSessionItems,AllocationHistoryItems columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesPostedTransactions WHERE Id = 29674
SELECT * FROM SalesPostedTransactions WHERE CustomerId = 27849
SELECT * FROM SalesPostedTransactions WHERE TraderTransactionType = 'TradingAccountEntryTypeInvoice'
SELECT * FROM SalesPostedTransactions WHERE Reference = 'O/BAL'
SELECT * FROM SalesPostedTransactions WHERE SecondReference = 'Opening Balance'
SELECT * FROM SalesPostedTransactions WHERE Queried = 'abc'
SELECT * FROM SalesPostedTransactions WHERE TransactionDate = '2017-12-31T01:00:00.000+01:00'
SELECT * FROM SalesPostedTransactions WHERE PostedDate = '2018-09-13T02:00:00.000+02:00'
SELECT * FROM SalesPostedTransactions WHERE DueDate = '2018-02-14T01:00:00.000+01:00'
SELECT * FROM SalesPostedTransactions WHERE FullSettlementDate = '2019-04-15T02:00:00.000+02:00'
SELECT * FROM SalesPostedTransactions WHERE DocumentGoodsValue = 150
SELECT * FROM SalesPostedTransactions WHERE DocumentGrossValue = 150
SELECT * FROM SalesPostedTransactions WHERE DocumentTaxValue = 0
SELECT * FROM SalesPostedTransactions WHERE DocumentDiscountValue = 0
SELECT * FROM SalesPostedTransactions WHERE DiscountPercent = 0
SELECT * FROM SalesPostedTransactions WHERE DocumentTaxDiscountValue = 0
SELECT * FROM SalesPostedTransactions WHERE DocumentAllocatedValue = 150
SELECT * FROM SalesPostedTransactions WHERE DocumentOutstandingValue = 0
SELECT * FROM SalesPostedTransactions WHERE BaseGoodsValue = 150
SELECT * FROM SalesPostedTransactions WHERE BaseGrossValue = 150
SELECT * FROM SalesPostedTransactions WHERE BaseTaxValue = 0
SELECT * FROM SalesPostedTransactions WHERE BaseDiscountValue = 0
SELECT * FROM SalesPostedTransactions WHERE BaseTaxDiscountValue = 0
SELECT * FROM SalesPostedTransactions WHERE BaseAllocatedValue = 150
SELECT * FROM SalesPostedTransactions WHERE ControlValueInBaseCurrency = 0
SELECT * FROM SalesPostedTransactions WHERE ExchangeRate = 1
SELECT * FROM SalesPostedTransactions WHERE SettledImmediately = false
SELECT * FROM SalesPostedTransactions WHERE DiscountDays = 0
SELECT * FROM SalesPostedTransactions WHERE Urn = 1
SELECT * FROM SalesPostedTransactions WHERE UserName = 'Migration'
SELECT * FROM SalesPostedTransactions WHERE DateTimeUpdated = '2018-02-28T15:23:46.330+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Id.
CustomerId	Long		Customer Id. See customers
TraderTransactionType	String		Transaction type. See trader_transaction_types
Reference	String		Transaction reference.
SecondReference	String		Second reference.
Queried	String		Query flag.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
DueDate	Date		Due date.
FullSettlementDate	Date		Full settlement date.
DocumentGoodsValue	Decimal		Value of the goods.
DocumentGrossValue	Decimal		Gross value.
DocumentTaxValue	Decimal		Tax value.
DocumentDiscountValue	Decimal		Document discount value.
DiscountPercent	Decimal		Percentage discount.
DocumentTaxDiscountValue	Decimal		Tax discount value.
DocumentAllocatedValue	Decimal		Allocated value.
DocumentOutstandingValue	Decimal		Outstanding value.
BaseGoodsValue	Decimal		Value of the goods in base currency.
BaseGrossValue	Decimal		Gross value in base currency.
BaseTaxValue	Decimal		Tax value in base currency.
BaseDiscountValue	Decimal		Discount value in base currency.
BaseTaxDiscountValue	Decimal		Tax discount value in base currency.
BaseAllocatedValue	Decimal		Allocated value in base currency.
ControlValueInBaseCurrency	Decimal		Control value in base currency.
ExchangeRate	Decimal		Exchange rate used for transaction.
SettledImmediately	Bool		Immediately settled.
DiscountDays	Short		Number of days discount valid.
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See sales_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See sales_posted_transactions_tax_analysis_items
BankItems	String		Bank items. See sales_posted_transactions_bank_items
AllocationSessionItems	String		Allocation session items. See sales_posted_transactions_allocation_session_items
AllocationHistoryItems	String		Allocation history items. See sales_posted_transactions_allocation_history_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesTradingPeriods WHERE Id = 1
SELECT * FROM SalesTradingPeriods WHERE PeriodNumber = '1'
SELECT * FROM SalesTradingPeriods WHERE StartDate = '2018-01-01T01:00:00.000+01:00'
SELECT * FROM SalesTradingPeriods WHERE EndDate = '2018-01-31T01:00:00.000+01:00'
SELECT * FROM SalesTradingPeriods WHERE DateTimeUpdated = '2018-02-28T15:23:45.667+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		An unique Id specific to each sales trading period.
PeriodNumber	String		The period number.
StartDate	Date		Date the trading period starts. The returned string includes the time but this will always be 00:00:00.
EndDate	Date		Date the trading period ends. The returned string includes the time but this will always be 00:00:00.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesTransactionViews WHERE PostedCustomerTransactionId = 29674
SELECT * FROM SalesTransactionViews WHERE TransactionType = 'TradingAccountEntryTypeInvoice'
SELECT * FROM SalesTransactionViews WHERE TransactionDate = '2017-12-31T01:00:00.000+01:00'
SELECT * FROM SalesTransactionViews WHERE PostedDate = '2018-09-13T02:00:00.000+02:00'
SELECT * FROM SalesTransactionViews WHERE DueDate = '2018-02-14T01:00:00.000+01:00'
SELECT * FROM SalesTransactionViews WHERE DiscountDays = 0
SELECT * FROM SalesTransactionViews WHERE Reference = 'O/BAL'
SELECT * FROM SalesTransactionViews WHERE SecondReference = 'Opening Balance'
SELECT * FROM SalesTransactionViews WHERE Query = ' '
SELECT * FROM SalesTransactionViews WHERE Status = 'Full'
SELECT * FROM SalesTransactionViews WHERE UserName = 'Migration'
SELECT * FROM SalesTransactionViews WHERE LedgerType = 'LedgerSales'
SELECT * FROM SalesTransactionViews WHERE Urn = 1
SELECT * FROM SalesTransactionViews WHERE ExchangeRate = 1
SELECT * FROM SalesTransactionViews WHERE SettledImmediately = false
SELECT * FROM SalesTransactionViews WHERE BaseGoodsValue = 150
SELECT * FROM SalesTransactionViews WHERE BaseGrossValue = 150
SELECT * FROM SalesTransactionViews WHERE BaseDiscountValue = 0
SELECT * FROM SalesTransactionViews WHERE BaseTaxValue = 0
SELECT * FROM SalesTransactionViews WHERE BaseTaxDiscountValue = 0
SELECT * FROM SalesTransactionViews WHERE BaseAllocatedValue = 150
SELECT * FROM SalesTransactionViews WHERE BaseOutstandingValue = 0
SELECT * FROM SalesTransactionViews WHERE DocumentGoodsValue = 150
SELECT * FROM SalesTransactionViews WHERE DocumentGrossValue = 150
SELECT * FROM SalesTransactionViews WHERE DocumentDiscountValue = 0
SELECT * FROM SalesTransactionViews WHERE DocumentTaxValue = 0
SELECT * FROM SalesTransactionViews WHERE DocumentTaxDiscountValue = 0
SELECT * FROM SalesTransactionViews WHERE DocumentAllocatedValue = 150
SELECT * FROM SalesTransactionViews WHERE DocumentOutstandingValue = 0
SELECT * FROM SalesTransactionViews WHERE CustomerId = 27849
SELECT * FROM SalesTransactionViews WHERE CustomerReference = 'COM001'
SELECT * FROM SalesTransactionViews WHERE CustomerName = 'Compton Packaging'
SELECT * FROM SalesTransactionViews WHERE CurrencyName = 'Pound Sterling'
SELECT * FROM SalesTransactionViews WHERE CurrencySymbol = '£'

Columns

Name	Type	References	Description
PostedCustomerTransactionId [KEY]	Long		Id of the transaction.
TransactionType	String		Transaction type. See trader_transaction_types
TransactionDate	Date		The date of the transaction.
PostedDate	Date		The date the transaction was posted.
DueDate	Date		The due date of the transaction.
DiscountDays	Short		Days discount valid.
Reference	String		The reference of the transaction.
SecondReference	String		The second reference of the transaction.
Query	String		Query.
Status	String		Status.
UserName	String		User that posted the transaction.
LedgerType	String		Ledger type. See ledger_types
Urn	Long		Unique reference number.
ExchangeRate	Decimal		Exchange Rate.
SettledImmediately	Bool		Immediately settled.
BaseGoodsValue	Decimal		Goods value in base currency.
BaseGrossValue	Decimal		Gross value in base currency.
BaseDiscountValue	Decimal		Discount value in base currency.
BaseTaxValue	Decimal		Tax value in base currency.
BaseTaxDiscountValue	Decimal		Tax discount value in base currency.
BaseAllocatedValue	Decimal		Allocated value in base currency.
BaseOutstandingValue	Decimal		Outstanding value in base currency.
DocumentGoodsValue	Decimal		Goods value.
DocumentGrossValue	Decimal		Gross value.
DocumentDiscountValue	Decimal		Discount value.
DocumentTaxValue	Decimal		Tax value.
DocumentTaxDiscountValue	Decimal		Tax discount value.
DocumentAllocatedValue	Decimal		Allocated value.
DocumentOutstandingValue	Decimal		Outstanding value.
CustomerId	Long		Customer Id.
CustomerReference	String		Customer account code.
CustomerName	String		Customer account name.
CurrencyName	String		Currency.
CurrencySymbol	String		Currency symbol.

These settings to define how to process and manage your stock.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM StockSettings WHERE Id = 1
SELECT * FROM StockSettings WHERE UseMultipleLocations = true
SELECT * FROM StockSettings WHERE ProfitPercentOfRevenue = true
SELECT * FROM StockSettings WHERE DateTimeUpdated = '2018-02-28T15:23:45.543+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Stock setting unique Id.
UseMultipleLocations	Bool		When true this setting allows Sage 200 to track stock at different locations. This can be across several warehouses, or a single warehouse split into separate areas (bins). (Sage 200c Standard value is true and cannot be changed).
ProfitPercentOfRevenue	Bool		Profit can be calculated as a percentage of revenue (true), or as a percentage of cost (false).
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SupplierContacts WHERE Id = 42101
SELECT * FROM SupplierContacts WHERE SupplierId = 325434364565
SELECT * FROM SupplierContacts WHERE FirstName = 'Sample'

Columns

Name	Type	References	Description
Id [KEY]	Long		Supplier contact record Id.
SupplierId	Long		Supplier record Id.
Name	String		Contact Name - Concatenated first_name, middle_name and last_name.
FirstName	String		Contact first name.
MiddleName	String		Contact middle name.
LastName	String		Contact surname.
IsDefault	Bool		Is this the default contact for the parent Supplier. See Supplier
DefaultTelephone	String		Default telephone number.
DefaultEmail	String		Default email address.
SalutationId	Long		Contact salutation id.
SalutationIsDefault	Boolean		Contact salutation.
SalutationCode	String		Contact salutation code.
SalutationDescription	String		Contact salutation description.
Emails	String		Supplier emails. See Supplier_emails
Telephones	String		Supplier telephones. See Supplier_telephones
Mobiles	String		Supplier mobiles. See Supplier_mobiles
Faxes	String		Supplier faxes. See Supplier_faxes
Websites	String		Supplier websites. See Supplier_websites
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,BankId,BankAccountNumber,BankAccountName,BankIBAN,BankBIC,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Suppliers WHERE Id = 28846
SELECT * FROM Suppliers WHERE Reference = 'CON001'
SELECT * FROM Suppliers WHERE Name = 'Concept Stationery Supplies'
SELECT * FROM Suppliers WHERE ShortName = 'Concept'
SELECT * FROM Suppliers WHERE Balance = 4346.16
SELECT * FROM Suppliers WHERE OnHold = false
SELECT * FROM Suppliers WHERE AccountStatusType = 'AccountStatusActive'
SELECT * FROM Suppliers WHERE CurrencyId = 2103
SELECT * FROM Suppliers WHERE ExchangeRateType = 'ExchangeRateSingle'
SELECT * FROM Suppliers WHERE TelephoneCountryCode = '44'
SELECT * FROM Suppliers WHERE TelephoneAreaCode = '0191'
SELECT * FROM Suppliers WHERE TelephoneSubscriberNumber = '643 4343'
SELECT * FROM Suppliers WHERE FaxCountryCode = '44'
SELECT * FROM Suppliers WHERE FaxAreaCode = '0191'
SELECT * FROM Suppliers WHERE FaxSubscriberNumber = '643 4345'
SELECT * FROM Suppliers WHERE Website = 'www.sage.co.uk'
SELECT * FROM Suppliers WHERE CreditLimit = 17000
SELECT * FROM Suppliers WHERE CountryCodeId = 13
SELECT * FROM Suppliers WHERE DefaultTaxCodeId = 1729
SELECT * FROM Suppliers WHERE VatNumber = 'GB988 3453 23'
SELECT * FROM Suppliers WHERE AnalysisCode1 = 'North East'
SELECT * FROM Suppliers WHERE AnalysisCode2 = 'Stationery'
SELECT * FROM Suppliers WHERE AnalysisCode3 = 'abc'
SELECT * FROM Suppliers WHERE AnalysisCode4 = 'abc'
SELECT * FROM Suppliers WHERE AnalysisCode5 = 'abc'
SELECT * FROM Suppliers WHERE DateTimeUpdated = '2018-02-28T15:23:46.253+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Supplier unique Id.
Reference	String		Supplier account reference. NOTE: Not required if supplier reference is set to
Name	String		Supplier name.
ShortName	String		Supplier short name.
Balance	Decimal		Supplier account balance.
OnHold	Bool		True if supplier account is on hold, else False.
AccountStatusType	String		The status of the supplier account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId	Long	Currencies.Id	Currency record Id. See currencies
ExchangeRateType	String		The type of exchange rate used on the supplier account. See exchange_rate_types
TelephoneCountryCode	String		Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode	String		Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber	String		Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode	String		Fax country code.
FaxAreaCode	String		Fax area code.
FaxSubscriberNumber	String		Fax subscriber number.
Website	String		Website address.
CreditLimit	Decimal		Credit limit for the supplier.
CountryCodeId	Long		Country code record Id.
CountryCode	String		The customers country code.
CountryCodeName	String		The customers country code name.
CountryCodeEuMember	Boolean		Shows if the customer is Eu Member.
DefaultTaxCodeId	Long	TaxCodes.Id	Default tax code record Id. See tax_codes
VatNumber	String		VAT registration number.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
BankId	String		Supplier bank account id.
BankAccountNumber	String		Supplier bank account number.
BankAccountName	String		Supplier bank account name.
BankIBAN	String		Supplier bank account iban number.
BankBIC	String		Supplier bank account BIC number.
MainAddress_1	String		The customers main address line 1.
MainAddress_2	String		The customers main address line 2.
MainAddress_3	String		The customers main address line 3.
MainAddress_4	String		The customers main address line 4.
MainAddressCity	String		The customers main address city.
MainAddressCounty	String		The customers main address county.
MainAddressPostcode	String		The customers main address postcode.
Contacts	String		Supplier contacts. See supplier_contacts
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM TaxCodes WHERE Id = 1728
SELECT * FROM TaxCodes WHERE Code = 0
SELECT * FROM TaxCodes WHERE Name = 'Zero rated'
SELECT * FROM TaxCodes WHERE TaxRate = 0
SELECT * FROM TaxCodes WHERE Terms = 'EcTermsNotApplicable'
SELECT * FROM TaxCodes WHERE TermsDescription = 'Not Applicable'
SELECT * FROM TaxCodes WHERE DateTimeUpdated = '2018-02-28T15:23:46.133+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Tax code record Id.
Code	Short		Tax code.
Name	String		Name of the tax code.
TaxRate	Decimal		Tax rate.
Terms	String		Name of the terms.
TermsDescription	String		Description of the terms.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM WarehouseHoldings WHERE Id = 34738
SELECT * FROM WarehouseHoldings WHERE WarehouseId = 34629
SELECT * FROM WarehouseHoldings WHERE ProductId = 34737
SELECT * FROM WarehouseHoldings WHERE ReorderLevel = 0
SELECT * FROM WarehouseHoldings WHERE MinimumLevel = 0
SELECT * FROM WarehouseHoldings WHERE MaximumLevel = 0
SELECT * FROM WarehouseHoldings WHERE ConfirmedQtyInStock = 0
SELECT * FROM WarehouseHoldings WHERE UnconfirmedQtyInStock = 0
SELECT * FROM WarehouseHoldings WHERE QuantityInStock = 0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedStock = 0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedSop = 0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedBom = 0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocated = 0
SELECT * FROM WarehouseHoldings WHERE DateTimeUpdated = '2019-08-01T18:00:33.140+02:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Warehouse holding record Id.
WarehouseId	Long	Warehouses.Id	Warehouse record Id. See warehouses
ProductId	Long	Products.Id	Product record Id. See products
ReorderLevel	Decimal		Reorder stock level.
MinimumLevel	Decimal		Minimum stock quantity level.
MaximumLevel	Decimal		Maximum stock quantity level.
ConfirmedQtyInStock	Decimal		Confirmed stock quantity.
UnconfirmedQtyInStock	Decimal		Unconfirmed stock quantity.
QuantityInStock	Decimal		Total confirmed and unconfirmed stock quantity.
QuantityAllocatedStock	Decimal		Allocated stock quantity.
QuantityAllocatedSop	Decimal		Allocated sales order quantity.
QuantityAllocatedBom	Decimal		Allocated bill of materials quantity.
QuantityAllocated	Decimal		Total Allocated stock, sop and bom quantity.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Warehouses WHERE Id = 34626
SELECT * FROM Warehouses WHERE Name = 'Supplied Direct'
SELECT * FROM Warehouses WHERE DateTimeUpdated = '2018-02-28T15:23:46.190+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Warehouse record Id.
Name	String		Warehouse name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT operations with Sage 200.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Sage 200, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Sage 200 Stored Procedures

Name	Description

Views

Views describes the available views. Views are statically defined to model Banks, Customers, Products, and more.

Stored Procedures

Stored Procedures are function-like interfaces to Sage 200. Stored procedures allow you to execute operations to Sage 200, such as retrieving the OAuth Access Token.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Sage 200 Views

Name	Description
BankPostedTransactions	Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.
Banks	Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.
CompanyDetails	Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.
CostCentres	Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.
Currencies	Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.
CustomerContacts	Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
CustomerDeliveryAddresses	A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.
Customers	Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.
Departments	Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.
FinancialReportLayouts	Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.
FinancialYearPeriods	This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.
NominalCodes	Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.
NominalReportCategories	Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).
NominalTransactionAnalysisCodes	Nominal transaction analysis codes are used to group nominal analysis items on various transaction types
PendingEmails	When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.
PriceBands	A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.
ProductGroups	Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.
ProductPrices	This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.
Products	Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.
ProductTransactions	A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.
PurchaseOrderLines	When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.
PurchaseOrders	Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.
PurchasePostedTransactions	Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.
SalesHistoricalTransactions	Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions.
SalesOrderAdditionalCharges	Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.
SalesOrderLines	When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.
SalesOrderProformaLines	This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.
SalesOrderQuoteLines	This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.
SalesOrders	Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.
SalesPostedTransactions	Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.
SalesTradingPeriods	This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)
SalesTransactions	When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.
StockSettings	These settings to define how to process and manage your stock.
SupplierContacts	Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
Suppliers	Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).
TaxCodes	Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.
WarehouseHoldings	All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.
Warehouses	All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: NominalAnalysisItems,TaxAnalysisItems,TradeItems columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM BankPostedTransactions WHERE Id = 29448
SELECT * FROM BankPostedTransactions WHERE BankId = 29401
SELECT * FROM BankPostedTransactions WHERE BankTransactionType = 'Payment'
SELECT * FROM BankPostedTransactions WHERE EntryDescription = 'Opening Balance'
SELECT * FROM BankPostedTransactions WHERE Reference = 'O/Bal'
SELECT * FROM BankPostedTransactions WHERE SecondReference = 'abc'
SELECT * FROM BankPostedTransactions WHERE TransactionDate = '2017-12-31T01:00:00.000+01:00'
SELECT * FROM BankPostedTransactions WHERE PostedDate = '2017-09-13T02:00:00.000+02:00'
SELECT * FROM BankPostedTransactions WHERE ChequeValue = -6511.96
SELECT * FROM BankPostedTransactions WHERE ChequeDiscountValue = 0
SELECT * FROM BankPostedTransactions WHERE ChequeToAccountCurrencyRate = 1
SELECT * FROM BankPostedTransactions WHERE BankChequeToBaseCurrencyRate = 1
SELECT * FROM BankPostedTransactions WHERE ChequeCurrencyId = 2103
SELECT * FROM BankPostedTransactions WHERE Urn = 24
SELECT * FROM BankPostedTransactions WHERE UserName = 'Migration'
SELECT * FROM BankPostedTransactions WHERE DateTimeUpdated = '2018-02-28T15:23:46.467+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Id.
BankId	Long	Banks.Id	Bank Id. See banks
BankTransactionType	String		Transaction type. See bank_transaction_types
EntryDescription	String		Transaction description.
Reference	String		Transaction reference.
SecondReference	String		Second reference.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
ChequeValue	Decimal		Value of the entry.
ChequeDiscountValue	Decimal		Entry discount value.
ChequeToAccountCurrencyRate	Decimal		Exchange rate between the bank account currency and the cheque currency.
BankChequeToBaseCurrencyRate	Decimal		Exchange rate between the bank account and the company base currency.
ChequeCurrencyId	Long		Currency Id. See currencies
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See bank_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See bank_posted_transactions_tax_analysis_items
TradeItems	String		Trade items. See bank_posted_transactions_trade_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: BankAddressId,BankAddressContact,BankAddressCountry,BankAddressCity,BankAddressPostcode,BankAddress_1,BankAddress_2,BankAddress_3,BankAddress_4,BankContacts columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Banks WHERE Id = 29401
SELECT * FROM Banks WHERE Code = '1200'
SELECT * FROM Banks WHERE Description = 'Bank Current Account'
SELECT * FROM Banks WHERE SortCode = '233434'
SELECT * FROM Banks WHERE AccountNo = '003234234'
SELECT * FROM Banks WHERE BankName = 'Lloyds Bank PLC'
SELECT * FROM Banks WHERE AccountType = 'BankAccountTypeCurrent'
SELECT * FROM Banks WHERE AccountStatusType = 'AccountStatusActive'
SELECT * FROM Banks WHERE Balance = -9941.57
SELECT * FROM Banks WHERE BalanceInBase = -9941.57
SELECT * FROM Banks WHERE OverdraftLimit = 0
SELECT * FROM Banks WHERE AccountName = 'Stationery & Computer Mart UK Ltd'
SELECT * FROM Banks WHERE Iban = 'abc'
SELECT * FROM Banks WHERE Bic = 'abc'
SELECT * FROM Banks WHERE StatementBalance = 0
SELECT * FROM Banks WHERE StatementDate = '2019-10-02T11:46:53Z'
SELECT * FROM Banks WHERE AccountCurrencyId = 2103
SELECT * FROM Banks WHERE DateTimeUpdated = '2018-02-28T15:23:46.070+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Bank account record Id.
Code	String		Bank account code.
Description	String		Bank description.
SortCode	String		Bank sort code.
AccountNo	String		Bank account number.
BankName	String		Bank name.
AccountType	String		Bank account type. See bank_account_types
AccountStatusType	String		The status of the bank account (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
Balance	Decimal		Bank balance.
BalanceInBase	Decimal		Bank balance in base currency.
OverdraftLimit	Decimal		Bank overdraft limit.
AccountName	String		Account name.
Iban	String		International bank account number.
Bic	String		Bank identifier code.
StatementBalance	Decimal		Last statement balance.
StatementDate	Date		Last statement date.
AccountCurrencyId	Long	Currencies.Id	Currency record Id. See currencies
BankAddressId	Long		Bank address. Bank address record id.
BankAddressContact	String		Bank address. Address Contact name.
BankAddressCountry	String		Bank address. Country.
BankAddressCity	String		Bank address. City.
BankAddressPostcode	String		Bank address. Postcode.
BankAddress_1	String		Bank address. Address line 1.
BankAddress_2	String		Bank address. Address line 2.
BankAddress_3	String		Bank address. Address line 3.
BankAddress_4	String		Bank address. Address line 4.
BankContacts	String		Collection of bank contact values.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEUMember,AddressCountry,Address_1,Address_2,Address_3,Address_4,AddressCity,AddressCounty,AddressPostcode columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM CompanyDetails WHERE Name = 'Stationery & Computer Mart UK'
SELECT * FROM CompanyDetails WHERE Telephone = '0191 955 3000'
SELECT * FROM CompanyDetails WHERE Fax = '0191 955 3001'
SELECT * FROM CompanyDetails WHERE Email = '[email protected]'
SELECT * FROM CompanyDetails WHERE Website = 'http://www.stationerycomputermartuk.co.uk'
SELECT * FROM CompanyDetails WHERE TaxRegistrationNumber = '376 6823 94'
SELECT * FROM CompanyDetails WHERE CountryCodeId = 13
SELECT * FROM CompanyDetails WHERE YearsAccountsHeldFor = 2
SELECT * FROM CompanyDetails WHERE EoriNumber = 'abc'
SELECT * FROM CompanyDetails WHERE DateTimeUpdated = '2018-02-28T15:23:46.040+01:00'

Columns

Name	Type	References	Description
Name [KEY]	String		Company name.
Telephone	String		Telephone number.
Fax	String		Fax number.
Email	String		Email address.
Website	String		Company website address.
TaxRegistrationNumber	String		Tax registration number.
CountryCodeId	Long		Country code record Id. See country_codes
CountryCode	String		Country code record Id. See country_codes
CountryCodeName	String		Country code record Id. See country_codes
CountryCodeEUMember	Boolean		Country code record Id. See country_codes
YearsAccountsHeldFor	Int		Number of years that periods with nominal data exist for up to a maximum of six years.
AddressCountry	String		The company's address Id.
Address_1	String		The company's address. Address line 1.
Address_2	String		The company's address. Address line 2.
Address_3	String		The company's address. Address line 3.
Address_4	String		The company's address. Address line 4.
AddressCity	String		The company's address. City
AddressCounty	String		The company's address. County
AddressPostcode	String		The company's address. Postcode
EoriNumber	String		The company's EORI number.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM CostCentres WHERE Id = 1015
SELECT * FROM CostCentres WHERE Code = 'abc'
SELECT * FROM CostCentres WHERE Name = 'abc'
SELECT * FROM CostCentres WHERE DateTimeUpdated = '2018-02-28T15:23:46.230+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Cost centre record Id.
Code	String		Cost centre code.
Name	String		Cost centre name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CurrencyIsoCode,CurrencyIsoCodeName columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Currencies WHERE Id = 2103
SELECT * FROM Currencies WHERE Symbol = '£'
SELECT * FROM Currencies WHERE Name = 'Pound Sterling'
SELECT * FROM Currencies WHERE CoreCurrencyRate = 1
SELECT * FROM Currencies WHERE EuroCurrencyRate = 0.714285
SELECT * FROM Currencies WHERE CurrencyIsoCodeId = 49
SELECT * FROM Currencies WHERE IsBaseCurrency = true
SELECT * FROM Currencies WHERE IsEuroCurrency = false
SELECT * FROM Currencies WHERE DateTimeUpdated = '2018-02-28T15:23:46.243+01:00'

Columns

Name	Type	References	Description
Id [KEY]	Long		Currency record Id.
Symbol	String		Currency symbol.
Name	String		Currency name.
CoreCurrencyRate	Decimal		Currency rate in use against base.
EuroCurrencyRate	Decimal		Euro rate.
CurrencyIsoCodeId	Long		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCode	String		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCodeName	String		ISO Currency Code Record Id. See currency_iso_codes
IsBaseCurrency	Bool		Flag to indicate if the currency is the base currency.
IsEuroCurrency	Bool		Flag to indicate if the currency is Euro.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).