SSIS Components for Sage 200

Build 20.0.7587

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.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587