はじめに

このページでは、CData Cloud でのSage 200 への接続の確立のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Sage 200 への接続

接続の確立は、CData Cloud にデータベースを作成するためのSage 200 への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してSage 200 からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメントで詳しく説明します。

接続の確立

Database タブで対応するアイコンを選択して、Sage 200 に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

Sage 200 への接続

接続の前に、Sage API 接続にアカウントが登録されていることを確認してください。これを行うには、

https://developer.columbus.sage.com/docs/services/api/uk/subscribe に移動します。
ご利用のSage 200 エディション（StandardUK またはProfessionalUK）のAPI を含むサブスクリプションを選択します。Sage 200 Unlimited サブスクリプションは、両方のエディションのAPI を含む必要があります。
契約しているサービスにログインし、自身のプロファイルを確認します。そこに表示されているSubscriptionKey をメモしてください。

Sage 200 に接続するには、次の接続プロパティを指定します。

Schema：使用するSage 200 のエディションを指定します。StandardUK またはProfessionalUK のいずれかです。
SubscriptionKey：上記のサブスクリプションプロファイルから取得した値。

Sage 200 への認証

Sage 200 は埋め込みOAuth クレデンシャルを提供しており、デスクトップアプリケーションからの接続を簡単にします。Web アプリケーションから接続するには、カスタムOAuth アプリケーションの作成で説明するようにカスタムOAuth アプリケーションを作成する必要があります。

すべての認証フローで、OAuth 経由で接続するにはAuthScheme をOAuth に設定する必要があります。

以下のサブセクションでは、利用可能なOAuth フローでのSage 200 への認証について詳しく説明します。カスタムOAuth アプリケーションの作成についての情報と、すでに埋め込みOAuth 認証情報を持つ認証フローでもカスタムOAuth アプリケーションを作成したほうがよい場合の説明については、カスタムOAuth アプリケーションの作成を参照してください。 Sage 200 で利用可能な接続文字列プロパティの全リストは、Connection を参照してください。

アクセストークンの期限が切れたときは、Cloud は自動でアクセストークンをリフレッシュします。

カスタムOAuth アプリケーションの作成

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:

Go to Sage 200 API Credentials Request Form.
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.
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.

SSL の設定

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
接続をトンネルするには、FirewallType をTUNNEL に設定します。
認証するには、FirewallUser とFirewallPassword を設定します。
SOCKS プロキシへの認証には、さらにFirewallType を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 データモデル 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 データモデル 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.

Sage200 StandardUK データモデル

Views

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

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.

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - Sage 200 ビュー


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.

BankPostedTransactions

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

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.

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

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.

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

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.

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

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.

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

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.

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

CustomerDeliveryAddresses

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

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

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

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.

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.

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.

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.

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.

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

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

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

NominalTransactionAnalysisCodes

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

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.

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

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.

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

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.

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

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.

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

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

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.

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

PurchaseOrderLines

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.

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.

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

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.

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

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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

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)

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

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.

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.

StockSettings

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

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.

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

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

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.

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

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.

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

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.

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

ストアドプロシージャ

ストアドプロシージャはファンクションライクなインターフェースで、Sage 200 の単純なSELECT 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにSage 200 から関連するレスポンスデータを返します。

CData Cloud - Sage 200 ストアドプロシージャ


Name	Description

Sage200 ProfessionalUK データモデル

Views

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

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.

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - Sage 200 ビュー


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.

BankPostedTransactions

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

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.

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

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.

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

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.

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

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.

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

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.

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

CustomerDeliveryAddresses

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

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.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
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

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

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.

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 FinancialReportLayoutId = 46151
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportLayoutAccountReportType = 'ProfitLoss'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportLayoutName = 'Sample Profit and Loss Layout'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowId = 47221
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowLineType = 'Text'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowLayoutPositionType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowLayoutDebitOrCreditType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowReportRow = 1
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowTitle = 'Sales'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowSubtotalGroup = 1
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak1 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak2 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak3 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak4 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak5 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak6 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak7 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak8 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak9 = 0
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryId = 123
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryCode = 'abc'
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryDescription = 'abc'

Columns


Name	Type	References	Description
FinancialReportLayoutId [KEY]	Long		Financial report layout record Id.
FinancialReportLayoutAccountReportType	String		The type of the financial layout. See financial_report_layout_types
FinancialReportLayoutName	String		financial report layout name.
FinancialReportRowId	Long		An asscoiated financial report row Id.
FinancialReportRowLineType	String		The type of the financial layout. See financial_report_row_line_types
FinancialReportRowLayoutPositionType	String		The type of the financial report row layout position. See financial_report_row_layout_position_types
FinancialReportRowLayoutDebitOrCreditType	String		The type of the financial layout. See financial_report_row_layout_debit_or_credit_types
FinancialReportRowReportRow	Int		Value to show the line number of the report row in the layout.
FinancialReportRowTitle	String		The row title.
FinancialReportRowSubtotalGroup	Int		Value to represent the sub total group.
FinancialReportRowGroupBreak1	Int		Value to represent the report row group break 1.
FinancialReportRowGroupBreak2	Int		Value to represent the report row group break 2.
FinancialReportRowGroupBreak3	Int		Value to represent the report row group break 3.
FinancialReportRowGroupBreak4	Int		Value to represent the report row group break 4.
FinancialReportRowGroupBreak5	Int		Value to represent the report row group break 5.
FinancialReportRowGroupBreak6	Int		Value to represent the report row group break 6.
FinancialReportRowGroupBreak7	Int		Value to represent the report row group break 7.
FinancialReportRowGroupBreak8	Int		Value to represent the report row group break 8.
FinancialReportRowGroupBreak9	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.

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.

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.

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.

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

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

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

NominalTransactionAnalysisCodes

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

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.

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

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.

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

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.

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

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.

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	Long		Product price record Id.
ProductId	Long		Product record Id. See products
ProductCode [KEY]	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

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

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.

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.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PurchaseOrderLines

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.
LineAnalysisCode6	String		Analysis code 6.
LineAnalysisCode7	String		Analysis code 7.
LineAnalysisCode8	String		Analysis code 8.
LineAnalysisCode9	String		Analysis code 9.
LineAnalysisCode10	String		Analysis code 10.
LineAnalysisCode11	String		Analysis code 11.
LineAnalysisCode12	String		Analysis code 12.
LineAnalysisCode13	String		Analysis code 13.
LineAnalysisCode14	String		Analysis code 14.
LineAnalysisCode15	String		Analysis code 15.
LineAnalysisCode16	String		Analysis code 16.
LineAnalysisCode17	String		Analysis code 17.
LineAnalysisCode18	String		Analysis code 18.
LineAnalysisCode19	String		Analysis code 19.
LineAnalysisCode20	String		Analysis code 20.
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.

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.

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.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
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).

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.

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

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.

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

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.

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

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.

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
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.
LineId	Long		Associated sales order line Id. See sop_order_lines
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.
LineAnalysisCode6	String		Analysis code 6.
LineAnalysisCode7	String		Analysis code 7.
LineAnalysisCode8	String		Analysis code 8.
LineAnalysisCode9	String		Analysis code 9.
LineAnalysisCode10	String		Analysis code 10.
LineAnalysisCode11	String		Analysis code 11.
LineAnalysisCode12	String		Analysis code 12.
LineAnalysisCode13	String		Analysis code 13.
LineAnalysisCode14	String		Analysis code 14.
LineAnalysisCode15	String		Analysis code 15.
LineAnalysisCode16	String		Analysis code 16.
LineAnalysisCode17	String		Analysis code 17.
LineAnalysisCode18	String		Analysis code 18.
LineAnalysisCode19	String		Analysis code 19.
LineAnalysisCode20	String		Analysis code 20.
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.

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.

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
SalesOrderProformaId [KEY]	Long		Unique Id of the sales pro forma
SalesOrderProformaDocumentNo	String		Sales pro forma document number.
SalesOrderProformaDocumentDate	Date		Sales pro forma date.
SalesOrderProformaDocumentStatus	String		Sales pro forma status. See sop_document_status_types
SalesOrderProformaCustomerDocumentNo	String		Customer sales pro forma document number.
SalesOrderProformaCurrencyId	Long		The Id for the currency used for the sales pro forma. See currencies
SalesOrderProformaCurrencyName	String		The name for the currency used for the sales pro forma.
SalesOrderProformaCurrencySymbol	String		The symbol of the currency used for the sales pro forma.
SalesOrderProformaExchangeRate	Decimal		Exchange rate for the sales pro forma.
SalesOrderProformaRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderProformaPromisedDeliveryDate	Date		Promised delivery date.
SalesOrderProformaAnalysisCode1	String		Analysis code 1.
SalesOrderProformaAnalysisCode2	String		Analysis code 2.
SalesOrderProformaAnalysisCode3	String		Analysis code 3.
SalesOrderProformaAnalysisCode4	String		Analysis code 4.
SalesOrderProformaAnalysisCode5	String		Analysis code 5.
SalesOrderProformaDateTimeUpdated	Datetime		The date and time the sales pro forma was last updated (UTC).
SalesOrderProformaLineId	Long		Associated sales pro forma line Id
SalesOrderProformaLineNumber	Short		Sales pro forma line number.
SalesOrderProformaLineType	String		Sales pro forma line type. See order_return_line_types
SalesOrderProformaLineProductCode	String		Sales pro forma line product code.
SalesOrderProformaLineProductDescription	String		Sales pro forma line product description. This could be stock item description, free text, additional charge or comment.
SalesOrderProformaLineQuantity	Decimal		The line quantity.
SalesOrderProformaLineSellingUnitDescription	String		The selling unit description.
SalesOrderProformaLineSellingUnitPrice	Decimal		The selling price per item.
SalesOrderProformaLineUnitDiscountPercent	Decimal		The percentage discount per item.
SalesOrderProformaLineUnitDiscountValue	Decimal		The discount value per item.
SalesOrderProformaLineCostPrice	Decimal		The cost price.
SalesOrderProformaLineTotalValue	Decimal		The total value of the line.
SalesOrderProformaLineLineTaxValue	Decimal		The tax value of the line.
SalesOrderProformaLineDiscountedUnitPrice	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.
SalesOrderProformaLineRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderProformaLinePromisedDeliveryDate	Date		Promised delivery date.
SalesOrderProformaLineAnalysisCode1	String		Analysis code 1.
SalesOrderProformaLineAnalysisCode2	String		Analysis code 2.
SalesOrderProformaLineAnalysisCode3	String		Analysis code 3.
SalesOrderProformaLineAnalysisCode4	String		Analysis code 4.
SalesOrderProformaLineAnalysisCode5	String		Analysis code 5.
SalesOrderProformaLineAnalysisCode6	String		Analysis code 6.
SalesOrderProformaLineAnalysisCode7	String		Analysis code 7.
SalesOrderProformaLineAnalysisCode8	String		Analysis code 8.
SalesOrderProformaLineAnalysisCode9	String		Analysis code 9.
SalesOrderProformaLineAnalysisCode10	String		Analysis code 10.
SalesOrderProformaLineAnalysisCode11	String		Analysis code 11.
SalesOrderProformaLineAnalysisCode12	String		Analysis code 12.
SalesOrderProformaLineAnalysisCode13	String		Analysis code 13.
SalesOrderProformaLineAnalysisCode14	String		Analysis code 14.
SalesOrderProformaLineAnalysisCode15	String		Analysis code 15.
SalesOrderProformaLineAnalysisCode16	String		Analysis code 16.
SalesOrderProformaLineAnalysisCode17	String		Analysis code 17.
SalesOrderProformaLineAnalysisCode18	String		Analysis code 18.
SalesOrderProformaLineAnalysisCode19	String		Analysis code 19.
SalesOrderProformaLineAnalysisCode20	String		Analysis code 20.
SalesOrderProformaLineDateTimeUpdated	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.

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.

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
SalesOrderQuoteId [KEY]	Long		Unique Id of the sales quote
SalesOrderQuoteDocumentNo	String		Sales quote document number.
SalesOrderQuoteDocumentDate	Date		Sales quote date.
SalesOrderQuoteDocumentStatus	String		Sales quote status. See sop_document_status_types
SalesOrderQuoteCustomerDocumentNo	String		Customer sales quote document number.
SalesOrderQuoteCurrencyId	Long		The Id for the currency used for the sales quote. See currencies
SalesOrderQuoteCurrencyName	String		The name for the currency used for the sales quote.
SalesOrderQuoteCurrencySymbol	String		The symbol of the currency used for the sales quote.
SalesOrderQuoteExchangeRate	Decimal		Exchange rate for the sales quote.
SalesOrderQuoteRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderQuotePromisedDeliveryDate	Date		Promised delivery date.
SalesOrderQuoteAnalysisCode1	String		Analysis code 1.
SalesOrderQuoteAnalysisCode2	String		Analysis code 2.
SalesOrderQuoteAnalysisCode3	String		Analysis code 3.
SalesOrderQuoteAnalysisCode4	String		Analysis code 4.
SalesOrderQuoteAnalysisCode5	String		Analysis code 5.
SalesOrderQuoteAnalysisCode6	String		Analysis code 6.
SalesOrderQuoteAnalysisCode7	String		Analysis code 7.
SalesOrderQuoteAnalysisCode8	String		Analysis code 8.
SalesOrderQuoteAnalysisCode9	String		Analysis code 9.
SalesOrderQuoteAnalysisCode10	String		Analysis code 10.
SalesOrderQuoteAnalysisCode11	String		Analysis code 11.
SalesOrderQuoteAnalysisCode12	String		Analysis code 12.
SalesOrderQuoteAnalysisCode13	String		Analysis code 13.
SalesOrderQuoteAnalysisCode14	String		Analysis code 14.
SalesOrderQuoteAnalysisCode15	String		Analysis code 15.
SalesOrderQuoteAnalysisCode16	String		Analysis code 16.
SalesOrderQuoteAnalysisCode17	String		Analysis code 17.
SalesOrderQuoteAnalysisCode18	String		Analysis code 18.
SalesOrderQuoteAnalysisCode19	String		Analysis code 19.
SalesOrderQuoteAnalysisCode20	String		Analysis code 20.
SalesOrderQuoteDateTimeUpdated	Datetime		The date and time the sales quote was last updated (UTC).
SalesOrderQuoteLineId	Long		Associated sales quote line Id
SalesOrderQuoteLineNumber	Short		Sales quote line number.
SalesOrderQuoteLineType	String		Sales quote line type. See order_return_line_types
SalesOrderQuoteLineProductCode	String		Sales quote line product code.
SalesOrderQuoteLineProductDescription	String		Sales quote line product description. This could be stock item description, free text, additional charge or comment.
SalesOrderQuoteLineQuantity	Decimal		The line quantity.
SalesOrderQuoteLineSellingUnitDescription	String		The selling unit description.
SalesOrderQuoteLineSellingUnitPrice	Decimal		The selling price per item.
SalesOrderQuoteLineUnitDiscountPercent	Decimal		The percentage discount per item.
SalesOrderQuoteLineUnitDiscountValue	Decimal		The discount value per item.
SalesOrderQuoteLineCostPrice	Decimal		The cost price.
SalesOrderQuoteLineTotalValue	Decimal		The total value of the line.
SalesOrderQuoteLineLineTaxValue	Decimal		The tax value of the line.
SalesOrderQuoteLineDiscountedUnitPrice	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.
SalesOrderQuoteLineRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderQuoteLinePromisedDeliveryDate	Date		Promised delivery date.
SalesOrderQuoteLineAnalysisCode1	String		Analysis code 1.
SalesOrderQuoteLineAnalysisCode2	String		Analysis code 2.
SalesOrderQuoteLineAnalysisCode3	String		Analysis code 3.
SalesOrderQuoteLineAnalysisCode4	String		Analysis code 4.
SalesOrderQuoteLineAnalysisCode5	String		Analysis code 5.
SalesOrderQuoteLineAnalysisCode6	String		Analysis code 6.
SalesOrderQuoteLineAnalysisCode7	String		Analysis code 7.
SalesOrderQuoteLineAnalysisCode8	String		Analysis code 8.
SalesOrderQuoteLineAnalysisCode9	String		Analysis code 9.
SalesOrderQuoteLineAnalysisCode10	String		Analysis code 10.
SalesOrderQuoteLineAnalysisCode11	String		Analysis code 11.
SalesOrderQuoteLineAnalysisCode12	String		Analysis code 12.
SalesOrderQuoteLineAnalysisCode13	String		Analysis code 13.
SalesOrderQuoteLineAnalysisCode14	String		Analysis code 14.
SalesOrderQuoteLineAnalysisCode15	String		Analysis code 15.
SalesOrderQuoteLineAnalysisCode16	String		Analysis code 16.
SalesOrderQuoteLineAnalysisCode17	String		Analysis code 17.
SalesOrderQuoteLineAnalysisCode18	String		Analysis code 18.
SalesOrderQuoteLineAnalysisCode19	String		Analysis code 19.
SalesOrderQuoteLineAnalysisCode20	String		Analysis code 20.
SalesOrderQuoteLineDateTimeUpdated	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.

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.

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.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
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).

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.

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

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)

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

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.

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.

StockSettings

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

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.

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

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.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
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).

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.

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

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.

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

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.

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

ストアドプロシージャ

ストアドプロシージャはファンクションライクなインターフェースで、Sage 200 の単純なSELECT 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにSage 200 から関連するレスポンスデータを返します。

CData Cloud - Sage 200 ストアドプロシージャ


Name	Description

システムテーブル

このセクションで説明されているシステムテーブルをクエリして、スキーマ情報、データソース機能に関する情報、およびバッチ操作の統計にアクセスできます。

スキーマテーブル

以下のテーブルは、Sage 200 のデータベースメタデータを返します。

sys_catalogs：利用可能なデータベースをリスト。
sys_schemas：利用可能なスキーマをリスト。
sys_tables：利用可能なテーブルおよびビューをリスト。
sys_tablecolumns：利用可能なテーブルおよびビューのカラムについて説明。
sys_procedures：利用可能なストアドプロシージャについて説明。
sys_procedureparameters：ストアドプロシージャパラメータについて説明。
sys_keycolumns：主キーおよび外部キーについて説明。
sys_indexes：利用可能なインデックスについて説明。

データソーステーブル

以下のテーブルは、データソースへの接続方法およびクエリ方法についての情報を返します。

sys_connection_props：利用可能な接続プロパティについての情報を返す。
sys_sqlinfo：Cloud がデータソースにオフロードできるSELECT クエリについて説明。

クエリ情報テーブル

次のテーブルは、データ変更クエリのクエリ統計を返します。

sys_identity：バッチ処理または単一の更新に関する情報を返す。

sys_catalogs

利用可能なデータベースをリストします。

次のクエリは、接続文字列で決定されるすべてのデータベースを取得します。

SELECT * FROM sys_catalogs

Columns


Name	Type	Description
CatalogName	String	データベース名。

sys_schemas

利用可能なスキーマをリストします。

次のクエリは、すべての利用可能なスキーマを取得します。

          SELECT * FROM sys_schemas

Columns


Name	Type	Description
CatalogName	String	データベース名。
SchemaName	String	スキーマ名。

sys_tables

利用可能なテーブルをリストします。

次のクエリは、利用可能なテーブルおよびビューを取得します。

          SELECT * FROM sys_tables

Columns


Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベース。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	テーブル名またはビュー名。
TableType	String	テーブルの種類（テーブルまたはビュー）。
Description	String	テーブルまたはビューの説明。
IsUpdateable	Boolean	テーブルが更新可能かどうか。

sys_tablecolumns

利用可能なテーブルおよびビューのカラムについて説明します。

次のクエリは、Banks テーブルのカラムとデータ型を返します。

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Banks'

Columns


Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベースの名前。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	カラムを含むテーブルまたはビューの名前。
ColumnName	String	カラム名。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	カラムのストレージサイズ。
DisplaySize	Int32	指定されたカラムの通常の最大幅（文字数）。
NumericPrecision	Int32	数値データの最大桁数。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	カラムのスケール（小数点以下の桁数）。
IsNullable	Boolean	カラムがNull を含められるかどうか。
Description	String	カラムの簡単な説明。
Ordinal	Int32	カラムのシーケンスナンバー。
IsAutoIncrement	String	カラムに固定増分値が割り当てられるかどうか。
IsGeneratedColumn	String	生成されたカラムであるかどうか。
IsHidden	Boolean	カラムが非表示かどうか。
IsArray	Boolean	カラムが配列かどうか。
IsReadOnly	Boolean	カラムが読み取り専用かどうか。
IsKey	Boolean	sys_tablecolumns から返されたフィールドがテーブルの主キーであるかどうか。

sys_procedures

利用可能なストアドプロシージャをリストします。

次のクエリは、利用可能なストアドプロシージャを取得します。

          SELECT * FROM sys_procedures

Columns


Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベース。
SchemaName	String	ストアドプロシージャを含むスキーマ。
ProcedureName	String	ストアドプロシージャの名前。
Description	String	ストアドプロシージャの説明。
ProcedureType	String	PROCEDURE やFUNCTION などのプロシージャのタイプ。

sys_procedureparameters

ストアドプロシージャパラメータについて説明します。

次のクエリは、GetOAuthAuthorizationURL ストアドプロシージャのすべての入力パラメータについての情報を返します。

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAuthorizationURL' AND Direction=1 OR Direction=2

Columns


Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベースの名前。
SchemaName	String	ストアドプロシージャを含むスキーマの名前。
ProcedureName	String	パラメータを含むストアドプロシージャの名前。
ColumnName	String	ストアドプロシージャパラメータの名前。
Direction	Int32	パラメータのタイプに対応する整数値：input (1)。input/output (2)、またはoutput(4)。input/output タイプパラメータは、入力パラメータと出力パラメータの両方になれます。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	文字データの場合は、許可される文字数。数値データの場合は、許可される桁数。
NumericPrecision	Int32	数値データの場合は最大精度。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	数値データの小数点以下の桁数。
IsNullable	Boolean	パラメータがNull を含められるかどうか。
IsRequired	Boolean	プロシージャの実行にパラメータが必要かどうか。
IsArray	Boolean	パラメータが配列かどうか。
Description	String	パラメータの説明。
Ordinal	Int32	パラメータのインデックス。

sys_keycolumns

主キーおよび外部キーについて説明します。

次のクエリは、Banks テーブルの主キーを取得します。

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Banks'

Columns


Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
IsKey	Boolean	カラムがTableName フィールドで参照されるテーブル内の主キーかどうか。
IsForeignKey	Boolean	カラムがTableName フィールドで参照される外部キーかどうか。
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。

sys_foreignkeys

外部キーについて説明します。

次のクエリは、他のテーブルを参照するすべての外部キーを取得します。

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

カラム


名前	タイプ	説明
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。
ForeignKeyType	String	外部キーがインポート（他のテーブルを指す）キーかエクスポート（他のテーブルから参照される）キーかを指定します。

sys_primarykeys

主キーについて説明します。

次のクエリは、すべてのテーブルとビューから主キーを取得します。

         SELECT * FROM sys_primarykeys

Columns


Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前。
KeySeq	String	主キーのシーケンス番号。
KeyName	String	主キーの名前。

sys_indexes

利用可能なインデックスについて説明します。インデックスをフィルタリングすることで、より高速なクエリ応答時間でセレクティブクエリを記述できます。

次のクエリは、主キーでないすべてのインデックスを取得します。

          SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


Name	Type	Description
CatalogName	String	インデックスを含むデータベースの名前。
SchemaName	String	インデックスを含むスキーマの名前。
TableName	String	インデックスを含むテーブルの名前。
IndexName	String	インデックス名。
ColumnName	String	インデックスに関連付けられたカラムの名前。
IsUnique	Boolean	インデックスが固有の場合はTrue。そうでない場合はFalse。
IsPrimary	Boolean	インデックスが主キーの場合はTrue。そうでない場合はFalse。
Type	Int16	インデックスタイプに対応する整数値：statistic (0)、clustered (1)、hashed (2)、またはother (3)。
SortOrder	String	並べ替え順序：A が昇順、D が降順。
OrdinalPosition	Int16	インデックスのカラムのシーケンスナンバー。

sys_connection_props

利用可能な接続プロパティと、接続文字列に設定されている接続プロパティに関する情報を返します。

このテーブルをクエリする際は、config 接続文字列を使用する必要があります。

jdbc:cdata:sage200:config:

この接続文字列を使用すると、有効な接続がなくてもこのテーブルをクエリできます。

次のクエリは、接続文字列に設定されている、あるいはデフォルト値で設定されているすべての接続プロパティを取得します。

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


Name	Type	Description
Name	String	接続プロパティ名。
ShortDescription	String	簡単な説明。
Type	String	接続プロパティのデータ型。
Default	String	明示的に設定されていない場合のデフォルト値。
Values	String	可能な値のカンマ区切りリスト。別な値が指定されていると、検証エラーがスローされます。
Value	String	設定した値またはあらかじめ設定されたデフォルト。
Required	Boolean	プロパティが接続に必要かどうか。
Category	String	接続プロパティのカテゴリ。
IsSessionProperty	String	プロパティが、現在の接続に関する情報を保存するために使用されるセッションプロパティかどうか。
Sensitivity	String	プロパティの機密度。これは、プロパティがロギングおよび認証フォームで難読化されているかどうかを通知します。
PropertyName	String	キャメルケースの短縮形の接続プロパティ名。
Ordinal	Int32	パラメータのインデックス。
CatOrdinal	Int32	パラメータカテゴリのインデックス。
Hierarchy	String	このプロパティと一緒に設定する必要がある、関連のある依存プロパティを表示します。
Visible	Boolean	プロパティが接続UI に表示されるかどうかを通知します。
ETC	String	プロパティに関するその他のさまざまな情報。

sys_sqlinfo

Cloud がデータソースにオフロードできるSELECT クエリ処理について説明します。

SQL 構文の詳細については、SQL 準拠を参照してください。

データソースのSELECT 機能

以下はSQL 機能のサンプルデータセットです。 SELECT 機能のいくつかの側面がサポートされている場合には、カンマ区切りのリストで返されます。サポートされていない場合、カラムにはNO が入ります。


名前	説明	有効な値
AGGREGATE_FUNCTIONS	サポートされている集計関数。	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	COUNT 関数がサポートされているかどうか。	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	識別子をエスケープするための開始文字。	[
IDENTIFIER_QUOTE_CLOSE_CHAR	識別子をエスケープするための終了文字。	]
SUPPORTED_OPERATORS	サポートされているSQL 演算子。	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	GROUP BY がサポートされているかどうか。サポートされている場合、どのレベルでサポートされているか。	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	サポートされている外部結合の種類。	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	外部結合がサポートされているかどうか。	YES, NO
SUBQUERIES	サブクエリがサポートされているかどうか。サポートされていれば、どのレベルでサポートされているか。	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	サポートされている文字列関数。	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	サポートされている数値関数。	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	サポートされている日付および時刻関数。	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	レプリケーション中にスキップされたテーブルを示します。
REPLICATION_TIMECHECK_COLUMNS	レプリケーション中に更新判断のカラムとして使用するかどうかを、（指定された順に）チェックするカラムのリストを含む文字列の配列。
IDENTIFIER_PATTERN	識別子としてどの文字列が有効かを示す文字列値。
SUPPORT_TRANSACTION	プロバイダーが、コミットやロールバックなどのトランザクションをサポートしているかどうかを示します。	YES, NO
DIALECT	使用するSQL ダイアレクトを示します。
KEY_PROPERTIES	Uniform データベースを特定するプロパティを示します。
SUPPORTS_MULTIPLE_SCHEMAS	プロバイダー用に複数のスキームが存在するかどうかを示します。	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	プロバイダー用に複数のカタログが存在するかどうかを示します。	YES, NO
DATASYNCVERSION	このドライバーにアクセスするために必要な、CData Sync のバージョン。	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	このドライバーのCData Sync カテゴリ。	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	API で提供されている以上の、追加SQL 機能がサポートされているかどうか。	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	バッチ操作がサポートされているかどうか。	YES, NO
SQL_CAP	このドライバーでサポートされているすべてのSQL 機能。	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	使用したいcacheOptions を指定する文字列値。
ENABLE_EF_ADVANCED_QUERY	ドライバーがEntity Framework の高度なクエリをサポートしているかどうかを示します。サポートしていなければ、クエリはクライアントサイドで処理されます。	YES, NO
PSEUDO_COLUMNS	利用可能な疑似カラムを示す文字列の配列。
MERGE_ALWAYS	値がtrue であれば、CData Sync 内でMerge Model が強制的に実行されます。	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	レプリケート開始日時を返すSELECT クエリ。
REPLICATION_MIN_FUNCTION	サーバーサイドでmin を実行するために使用する式名を、プロバイダーが指定できるようになります。
REPLICATION_START_DATE	レプリケート開始日を、プロバイダーが指定できるようになります。
REPLICATION_MAX_DATE_QUERY	レプリケート終了日時を返すSELECT クエリ。
REPLICATION_MAX_FUNCTION	サーバーサイドでmax を実行するために使用する式名を、プロバイダーが指定できるようになります。
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	初回のレプリケートで、レプリケートをチャンクに分割しないテーブルのリスト。
CHECKCACHE_USE_PARENTID	CheckCache 構文を親キーカラムに対して実行するかどうかを示します。	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	スキーマファイルの生成に使用できる、ストアドプロシージャを示します。

次のクエリは、WHERE 句で使用できる演算子を取得します。

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

WHERE 句では、個々のテーブルの制限や要件が異なる場合がありますので注意してください。詳しくは、データモデルセクションを参照してください。

Columns


Name	Type	Description
NAME	String	SQL 構文のコンポーネント、またはサーバー上で処理できる機能。
VALUE	String	サポートされるSQL またはSQL 構文の詳細。

sys_identity

試行された変更に関する情報を返します。

次のクエリは、バッチ処理で変更された行のId を取得します。

         SELECT * FROM sys_identity

Columns


Name	Type	Description
Id	String	データ変更処理から返された、データベース生成Id。
Batch	String	バッチの識別子。1 は単一処理。
Operation	String	バッチ内の処理の結果：INSERTED、UPDATED、またはDELETED。
Message	String	SUCCESS、またはバッチ内の更新が失敗した場合のエラーメッセージ。

接続文字列オプション

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

Authentication

プロパティ	説明
SubscriptionKey	Your subscription key.

OAuth

プロパティ	説明
Scope	This determines the scopes that are requested from Sage.

SSL

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

Logging

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

Schema

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Schema	Used to specify what Sage200 Api to use. The default one is the Standard UK API.

Miscellaneous

プロパティ	説明
CompanyId	The ID of your company.
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
MaxThreads	Specifies the number of concurrent requests.
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

Authentication

このセクションでは、本プロバイダーの接続文字列で設定可能なAuthentication プロパティの全リストを提供します。

プロパティ	説明
SubscriptionKey	Your subscription key.

SubscriptionKey

Your subscription key.

データ型

string

デフォルト値

""

解説

Your subscription key. Obtained by subscribing to the appropriate edition API here: https://developer.columbus.sage.com/docs/services/api/uk.

OAuth

このセクションでは、本プロバイダーの接続文字列で設定可能なOAuth プロパティの全リストを提供します。

プロパティ	説明
Scope	This determines the scopes that are requested from Sage.

Scope

This determines the scopes that are requested from Sage.

データ型

string

デフォルト値

""

解説

By default, the following scopes will be requested: 'openid profile email offline_access'. If you want to override this, you can set this property to a space-separated list of scopes.

SSL

このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

SSLServerCert

TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

データ型

string

デフォルト値

""

解説

TLS/SSL 接続を使用する場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。コンピュータによって信頼されていない他の証明書はすべて拒否されます。

このプロパティは、次のフォームを取ります：


説明	例
フルPEM 証明書（例では省略されています）	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
証明書を保有するローカルファイルへのパス。	C:\cert.cer
公開鍵（例では省略されています）	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
MD5 Thumbprint （hex 値はスペースおよびコロン区切り）	ecadbdda5a1529c58a1e9e09828d70e4
SHA1 Thumbprint （hex 値はスペースおよびコロン区切り）	34a929226ae0819f2ec14b4a3d904f801cbb150d

これを指定しない場合は、マシンが信用するすべての証明書が受け入れられます。

すべての証明書の受け入れを示すには、'*'を使用します。セキュリティ上の理由から、これはお勧めできません。

Logging

このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

Verbosity

ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

データ型

string

デフォルト値

"1"

解説

Verbosity レベルは、Cloud がLogfile にどの程度の詳細さで記載するかを決定します。1 から5 までのVerbosity レベルがサポートされています。より詳しい説明は、ログページに記載されています。

Schema

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Schema	Used to specify what Sage200 Api to use. The default one is the Standard UK API.

BrowsableSchemas

このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

データ型

string

デフォルト値

""

解説

スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、パフォーマンスが向上します。

Schema

Used to specify what Sage200 Api to use. The default one is the Standard UK API.

Possible Values

StandardUK, ProfessionalUK

データ型

string

デフォルト値

"StandardUK"

解説

Used to specify what Sage200 Api to use. The default one is the Standard UK API.

Miscellaneous

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

プロパティ	説明
CompanyId	The ID of your company.
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
MaxThreads	Specifies the number of concurrent requests.
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

Timeout の有効期限が切れても処理が完了していない場合は、Cloud は例外をスローします。

Cloud

Sage 200 - CData Cloud

概要

主要機能

はじめに

Sage 200 への接続

CData Cloud サービスからデータにアクセス

接続の確立

Sage 200 への接続

Sage 200 への認証

カスタムOAuth アプリケーションの作成

Creating a Custom OAuth Application

Procedure

データアクセスのファインチューニング

Other Properties

SSL の設定

SSL 設定のカスタマイズ

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

その他のプロキシ

データモデル

Overview

Key Features

Sage 200 StandardUK Data Model

Sage 200 ProfessionalUK Data Model

Sage200 StandardUK データモデル

Views

Stored Procedures

ビュー

CData Cloud - Sage 200 ビュー

BankPostedTransactions

Table Specific Information

Select

Columns

Banks

Table Specific Information

Select

Columns

CompanyDetails

Table Specific Information

Select

Columns

CostCentres

Table Specific Information

Select

Columns

Currencies

Table Specific Information

Select

Columns

CustomerContacts

Table Specific Information

Select

Columns

CustomerDeliveryAddresses

Table Specific Information

Select

Columns

Customers

Table Specific Information

Select

Columns

Departments

Table Specific Information

Select

Columns

FinancialReportLayouts

Table Specific Information

Select

Columns

FinancialYearPeriods

Table Specific Information

Select

Columns

NominalCodes

Table Specific Information

Select

Columns

NominalReportCategories