Views
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.
Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.
Sage 200 Adapter 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. |