Sage 200 Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 23.0.8843 of the connector.
Get Started¶
Sage 200 Version Support
The connector leverages the Sage200 API to enable bidirectional access to Sage200.
Establish a Connection¶
Connect to Sage 200¶
Before connecting, you must ensure that your account is registered for Sage API access. You can easily do this by following this link:
<https://developer.columbus.sage.com/docs/services/api/uk/subscribe
>.
You will need to select a subscription that contains the API of your Sage 200 edition (StandardUK or ProfessionalUK). The Sage 200 Unlimited subscription should contain the APIs of both editions.
To connect to Sage 200, specify the following connection properties:
Schema
: Determines which Sage 200 edition you are connecting to. Specify eitherStandardUK
orProfessionalUK
.SubscriptionKey
: After subscribing to the API, you can find the subscription key in your profile after logging into Sage 200.
Authenticate to Sage 200¶
Sage 200 uses the OAuth authentication standard.
You can connect after setting required connection properties for your user credentials and obtaining required access tokens using a tool such as Postman.
See Using OAuth Authentication for an authentication guide and details of the OAuth authentication flow.
Create a Custom OAuth Application¶
Create a Custom OAuth Application¶
embeds OAuth Application Credentials with 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:
-
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), theOAuthClientSecret
(Client Secret), and theScope
. -
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
.
Fine-Tuning Data Access¶
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.
Important Notes¶
Configuration Files and Their Paths¶
- All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.
Advanced Features¶
This section details a selection of advanced features of the Sage 200 connector.
User Defined Views
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
SSL Configuration
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert
property under "Connection String Options" for more information.
Proxy
To configure the connector using private agent proxy settings, select the Use Proxy Settings
checkbox on the connection configuration screen.
Query Processing
The connector offloads as much of the SELECT statement processing as possible to Sage 200 and then processes the rest of the query in memory (client-side).
See Query Processing for more information.
User Defined Views¶
The Sage 200 connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:
- Create a JSON-formatted configuration file defining the views you want.
- DDL statements.
Define Views Using a Configuration File¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Banks WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
Define Views Using DDL Statements¶
The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.
Create a View¶
To create a new view using DDL statements, provide the view name and query as follows:
CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;
If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews
connection property.
Alter a View¶
To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:
ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';
The view is then updated in the JSON configuration file.
Drop a View¶
To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.
DROP LOCAL VIEW [MyViewName]
This removes the view from the JSON configuration file. It can no longer be queried.
Schema for User Defined Views¶
User Defined Views are exposed in the UserViews
schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName
property.
Work with User Defined Views¶
For example, a SQL statement with a User Defined View called UserViews.RCustomers
only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
SSL Configuration¶
Customize the SSL Configuration¶
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert
property for the available formats to do so.
Data Model¶
Overview
This section shows the available API objects and provides more information on executing SQL to Sage 200 APIs.
Key Features
- The connector 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 connector.
Sage 200 StandardUK Data Model
Sage200 StandardUK Data Model describes the schemas available to connect to Sage 200 Standard UK accounts. You can use tables to work with live Sage 200 data. You can use stored procedures provided by Sage 200 connector to automate working with Sage 200 data.
Sage 200 ProfessionalUK Data Model
Sage200 ProfessionalUK Data Model describes the schemas available to connect to Sage 200 Professional UK accounts. You can use tables to work with live Sage 200 data. You can use stored procedures provided by Sage 200 connector to automate working with Sage 200 data.
Sage200 StandardUK Data Model¶
Views¶
Views describes the available views. Views are statically defined to model Banks, Customers, Products, and more.
Stored Procedures¶
Stored Procedures are function-like interfaces to Sage 200. Stored procedures allow you to execute operations to Sage 200, such as retrieving the OAuth Access Token.
Views¶
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
Sage 200 Connector Views¶
Name | Description |
---|---|
BankPostedTransactions | Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book. |
Banks | Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account. |
CompanyDetails | Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details. |
CostCentres | Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production. |
Currencies | Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions. |
CustomerContacts | Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website. |
CustomerDeliveryAddresses | A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order. |
Customers | Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource. |
Departments | Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training. |
FinancialReportLayouts | Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending. |
FinancialYearPeriods | This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending. |
NominalCodes | Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department. |
NominalReportCategories | Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense). |
NominalTransactionAnalysisCodes | Nominal transaction analysis codes are used to group nominal analysis items on various transaction types |
PendingEmails | When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200. |
PriceBands | A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to. |
ProductGroups | Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description. |
ProductPrices | This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending. |
Products | Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices. |
ProductTransactions | A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending. |
PurchaseOrderLines | When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource. |
PurchaseOrders | Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received. |
PurchasePostedTransactions | Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions. |
SalesHistoricalTransactions | Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions. |
SalesOrderAdditionalCharges | Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for. |
SalesOrderLines | When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending. |
SalesOrderProformaLines | This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending. |
SalesOrderQuoteLines | This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending. |
SalesOrders | Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API. |
SalesPostedTransactions | Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource. |
SalesTradingPeriods | This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods) |
SalesTransactions | When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions. |
StockSettings | These settings to define how to process and manage your stock. |
SupplierContacts | Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website. |
Suppliers | Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc). |
TaxCodes | Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions. |
WarehouseHoldings | All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product. |
Warehouses | All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses. |
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 = 'sales@stationerycomputermartuk.co.uk'
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 = 'newbusinessadvice@sage.com'
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). |
Stored Procedures¶
Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with Sage 200.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Sage 200, along with an indication of whether the procedure succeeded or failed.
Sage 200 Connector Stored Procedures¶
Name | Description |
---|---|
GetOAuthAccessToken | Gets an authentication token from Sage200. |
GetOAuthAuthorizationURL | Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with sagebca. |
GetOAuthAccessToken¶
Gets an authentication token from Sage200.
Input¶
Name | Type | Required | Description |
---|---|---|---|
AuthMode | String | False | The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is WEB. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Sage200 app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | False | The verifier returned from Sage200 after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Sage200 authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Scope | String | False | A whitespace-separated list of permissions to request from the user. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The access token used for communication with Sage200. |
OAuthRefreshToken | String | The OAuth refresh token. This is the same as the access token in the case of Sage200. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
GetOAuthAuthorizationURL¶
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Input¶
Name | Type | Required | Description |
---|---|---|---|
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Sage200 app settings. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Sage200 authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Scope | String | False | A whitespace-separated list of permissions to request from the user. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with sagebca.
Input¶
Name | Type | Required | Description |
---|---|---|---|
OAuthRefreshToken | String | True | The refresh token returned with the previous access token. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from sagebca. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | This is the same as the access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Sage200 ProfessionalUK Data Model¶
Views¶
Views describes the available views. Views are statically defined to model Banks, Customers, Products, and more.
Stored Procedures¶
Stored Procedures are function-like interfaces to Sage 200. Stored procedures allow you to execute operations to Sage 200, such as retrieving the OAuth Access Token.
Views¶
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
Sage 200 Connector Views¶
Name | Description |
---|---|
BankPostedTransactions | Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book. |
Banks | Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account. |
CompanyDetails | Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details. |
CostCentres | Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production. |
Currencies | Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions. |
CustomerContacts | Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website. |
CustomerDeliveryAddresses | A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order. |
Customers | Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource. |
Departments | Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training. |
FinancialReportLayouts | Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending. |
FinancialYearPeriods | This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending. |
NominalCodes | Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department. |
NominalReportCategories | Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense). |
NominalTransactionAnalysisCodes | Nominal transaction analysis codes are used to group nominal analysis items on various transaction types |
PendingEmails | When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200. |
PriceBands | A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to. |
ProductGroups | Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description. |
ProductPrices | This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending. |
Products | Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices. |
ProductTransactions | A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending. |
PurchaseOrderLines | When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource. |
PurchaseOrders | Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received. |
PurchasePostedTransactions | Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions. |
SalesHistoricalTransactions | Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions. |
SalesOrderAdditionalCharges | Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for. |
SalesOrderLines | When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending. |
SalesOrderProformaLines | This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending. |
SalesOrderQuoteLines | This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending. |
SalesOrders | Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API. |
SalesPostedTransactions | Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource. |
SalesTradingPeriods | This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods) |
SalesTransactions | When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions. |
StockSettings | These settings to define how to process and manage your stock. |
SupplierContacts | Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website. |
Suppliers | Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc). |
TaxCodes | Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions. |
WarehouseHoldings | All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product. |
Warehouses | All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses. |
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 = 'sales@stationerycomputermartuk.co.uk'
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 = 'newbusinessadvice@sage.com'
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). |
Stored Procedures¶
Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with Sage 200.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Sage 200, along with an indication of whether the procedure succeeded or failed.
Sage 200 Connector Stored Procedures¶
Name | Description |
---|---|
GetOAuthAccessToken | Gets an authentication token from Sage200. |
GetOAuthAuthorizationURL | Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with sagebca. |
GetOAuthAccessToken¶
Gets an authentication token from Sage200.
Input¶
Name | Type | Required | Description |
---|---|---|---|
AuthMode | String | False | The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is WEB. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Sage200 app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | False | The verifier returned from Sage200 after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Sage200 authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Scope | String | False | A whitespace-separated list of permissions to request from the user. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The access token used for communication with Sage200. |
OAuthRefreshToken | String | The OAuth refresh token. This is the same as the access token in the case of Sage200. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
GetOAuthAuthorizationURL¶
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Input¶
Name | Type | Required | Description |
---|---|---|---|
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Sage200 app settings. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Sage200 authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Scope | String | False | A whitespace-separated list of permissions to request from the user. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with sagebca.
Input¶
Name | Type | Required | Description |
---|---|---|---|
OAuthRefreshToken | String | True | The refresh token returned with the previous access token. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from sagebca. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | This is the same as the access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
System Tables¶
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
Schema Tables¶
The following tables return database metadata for Sage 200:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Banks table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Banks'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the GetOAuthAuthorizationURL stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAuthorizationURL' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the Banks table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Banks'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:sage200:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discovering the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported 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 | Supported 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 | Supported date/time 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 | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | 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 | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
SubscriptionKey | Your subscription key. |
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
Scope | This determines the scopes that are requested from Sage. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | Used to specify what Sage200 Api to use. The default one is the Standard UK API. |
Property | Description |
---|---|
CompanyId | The ID of your company. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
MaxThreads | Specifies the number of concurrent requests. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
SubscriptionKey | Your subscription key. |
SubscriptionKey¶
Your subscription key.
Data Type¶
string
Default Value¶
""
Remarks¶
Your subscription key. Obtained by subscribing to the appropriate edition API here: https://developer.columbus.sage.com/docs/services/api/uk
.
OAuth¶
This section provides a complete list of OAuth properties you can configure.
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
Scope | This determines the scopes that are requested from Sage. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
InitiateOAuth¶
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Possible Values¶
OFF
, GETANDREFRESH
, REFRESH
Data Type¶
string
Default Value¶
OFF
Remarks¶
The following options are available:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.GETANDREFRESH
: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.REFRESH
: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.
OAuthClientId¶
The client ID assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId
value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
OAuthClientSecret¶
The client secret assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret
property.
OAuthAccessToken¶
The access token for connecting using OAuth.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthAccessToken
property is used to connect using OAuth. The OAuthAccessToken
is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
OAuthSettingsLocation¶
The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'
.
Data Type¶
string
Default Value¶
%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt
Remarks¶
When InitiateOAuth is set to GETANDREFRESH
or REFRESH
, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.
Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://'
followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props
system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.
The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA%
set to the user's configuration directory. The default values are
- Windows: "
register://%DSN
" - Unix: "%AppData%..."
where DSN is the name of the current DSN used in the open connection.
The following table lists the value of %APPDATA%
by OS:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Linux | ~/.config |
CallbackURL¶
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
Data Type¶
string
Default Value¶
""
Remarks¶
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
Scope¶
This determines the scopes that are requested from Sage.
Data Type¶
string
Default Value¶
""
Remarks¶
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.
OAuthVerifier¶
The verifier code returned from the OAuth authorization URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
Authentication on Headless Machines¶
See to obtain the OAuthVerifier
value.
Set OAuthSettingsLocation along with OAuthVerifier
. When you connect, the connector exchanges the OAuthVerifier
for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier
from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
OAuthRefreshToken¶
The OAuth refresh token for the corresponding OAuth access token.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthRefreshToken
property is used to refresh the OAuthAccessToken when using OAuth authentication.
OAuthExpiresIn¶
The lifetime in seconds of the OAuth AccessToken.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
OAuthTokenTimestamp¶
The Unix epoch timestamp in milliseconds when the current Access Token was created.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | Used to specify what Sage200 Api to use. The default one is the Standard UK API. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\Sage200 Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note
Given that this connector supports multiple schemas, the structure for Sage 200 custom schema files is as follows:
- Each schema is given a folder corresponding to that schema name.
- These schema folders are contained in a parent folder.
- The
parent folder
should be set as theLocation
, not an individual schema's folder.
If left unspecified, the default location is "%APPDATA%\Sage200 Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Schema¶
Used to specify what Sage200 Api to use. The default one is the Standard UK API.
Possible Values¶
StandardUK
, ProfessionalUK
Data Type¶
string
Default Value¶
StandardUK
Remarks¶
Used to specify what Sage200 Api to use. The default one is the Standard UK API.
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
CompanyId | The ID of your company. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
MaxThreads | Specifies the number of concurrent requests. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
CompanyId¶
The ID of your company.
Data Type¶
string
Default Value¶
""
Remarks¶
The ID of your company. If you have multiple companies connected to your Sage200 Account, by default we use the first one.
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
MaxThreads¶
Specifies the number of concurrent requests.
Data Type¶
string
Default Value¶
20
Remarks¶
This property allows you to issue multiple requests simultaneously, thereby improving performance.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Banks WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json
Note that the specified path is not embedded in quotation marks.