exact online data model for invantive sql · pdf filerdtr documenttypecategories rdty, #id...
Post on 06-Feb-2018
226 Views
Preview:
TRANSCRIPT
Finance
Invantive SQL supports over 700 tables on Exact Online, through REST, XML, Domains and Views.
Transactions and TransactionLines include Cash Entries, General Journal Entries and Bank Entries.
For Transactions, Documents and DocumentAttachments there are also Bulk variants that offer 3 times better throughput at the cost of reduced server-side filtering.
-
-
-
Notes
Countries
Each Exact Online country is separated from other Exact Online countries. A separate Exact Online country as a whole can be in the same data center as other countries, or geographically located somewhere else. The list of user interface languages available varies per country; e.g. in Belgium there are four: French, Flemish, English and German.
Data Dictionary
Metadata and execution data on Exact Online API access can be retrieved from the Invantive SQL data dictionary, such as:
- systempartitions: list of all divisions,- systemtables: list of all tables,- sessionios: actual API calls made, including URL.
Cross-Division Access
Invantive SQL statements operate on one or more divisions in a country. The syntax 'use NUMBER, NUMBER, ...' selects some specific divisions to work with. The syntax 'use all' selects all divisions and 'use select code from systemdivi-sions limit 10' selects 10 randomly chosen divisions.
Colofon
When you find any errors or have remarks on this poster, please send them to support@invantive.com. Data model copyright by holder of intellectual rights Exact Online. Design and editing by Invantive BV.
Request a free copy through support@invantive.com. Digital versions available through https://download.invantive.com/exact-online.
Released November 2017.
And through OEM and white label products such as Auxil Excel Add-in voor Exact Online.
Divisions
Each country is partitioned into smaller pieces, called "divisions". Each division has a country-wide unique number. The division number never changes and is in general different from the number assigned by the customer. Division 1 is for Exact Online itself and contains for instance the customers and the custom applications (apps) metadata. All other divisions are used for accounting and are known as "Administratie" (Dutch), "Dossier" (Flemish), "Mandant" (German), "Company" (UK), etc.
Each division is assigned to a customer identified by a number. A real-life organization can have multiple customer numbers in one or multiple countries. A customer number can have multiple divisions. A customer number can have one or more users that can access some or all divisions of that customer.
Accountancy customers can have divisions of their own, so-called internal divisions. Accountancy customers can also have access to the divisions of other Exact Online customers, so-called external divisions.
A user can see all (internal and external) accessible divisions in SystemDivisions. HRMDivisions shows only the divisions of the customer of the users last selected division in Exact Online. Use HRMDivisions only in very specific use cases. In AllAdministrationClassifications a user can find the classifications of all accessible divisions.
Global Cross-Platform Database
Invantive SQL allows global access for Exact Online and other cloud platforms by each SQL statement using a distributed database connection. Such a distributed database requires changes in the settings.xml file and allows the use of aliases per Exact Online country or other cloud platforms. For instance, this query operates on data in France, the Netherlands and Salesforce:
select *from exactonlinerest..items@eolnljoin exactonlinerest..items@eolfron ...join products@salesforceorg1on ...
Pointers
The full Invantive SQL grammar is available at:www.invantive.com/products/invantive-sql/grammarBesides Exact Online, over 50 other platforms are supported. The list is avalable at:www.invantive.com/products/invantive-sql/providerlist
$
www.invantive.cominfo@invantive.com+31 88 00 26 500
Exact Online Data Model for Invantive SQL
Also made possible by:
Legend
Only very specific aggregation APIs have been included which are known to be useful in many occasions such as ReportingBalance. All other tables contain detail data.
Only the REST API table has been included when a data set is available both as XML as well as REST table. The XML table might however contain columns not available in the REST table. The XML tables have similar or identical names to the REST API tables; try first to prefix the REST API table name with XML..
Whenever possible, relationships are based on GUIDs. Historically, Exact Online also has used relationships based upon numbers and codes. These relationships are used here when a relationship is not available through the APIs using a GUID.
A label is shown on a relationship only when required for correct interpretation.
Transactional Data
Setup Data
Technical Data
Defined in other topic
Countrywide Setup
Hard-coded Values
Product Support
Invantive SQL runs on: Windows , iOS , Android , macOS and Linux
invantive controlfor excel
invantive compositionfor word
invantivebusiness drive
invantive businessfor outlook
onlinesql editor
invantive businessfor windows
invantivedata replicator
invantivedata access point
invantive bridgefor power bi
invantive bridgefor developers
invantivedata hub
invantivequery tool
PaymentMethodsdompmd, #code
DirectDebitMandateTypesdomdma, #code
DirectDebitMandatePaymentTypesdomdmy, #code
VATCalculationsdomvcn, #code
PaymentStatusesdompss, #code
ReceivableStatusesdomrss, #code
PaymentSourcesdompse, #code
ReceivableSourcesdomrse, #code
PaymentDiscountTypesdompdc, #code
PaymentConditionsrcpn, #ID
Receivablesrcrve
Paymentsrcpyt
DirectDebitMandatesrdde, #ID
ReceivablesListrrlt
PayablesListrplt
CashflowBalancercbe
SalesInvoicesSales: rsie, #InvoiceID
JournalsFinancial: rjnl, #ID
AccountsAccounts: ract, #ID
PurchaseInvoicesPurchase: pie, #ID
BankAccountsAccounts: rbat, #ID
TransactionLinesTransaction: rtle, #ID
TransactionsTransaction: rtxn, #EntryNr
SystemUsersSystem: rsur, #ID
TransactionTypesTransaction: domtte, #code
TransactionStatusesTransaction: domtss, #code
DocumentsDocuments: rdct, #ID
GLAccountsFinancial: rgat, #ID
ContactsAccounts: rctt, #ID
PaymentDiscountType
VATCalculation
AccountBankAccountID
AccountBankAccountID
Sales
Purchase
BankAccount
ReceivableSelector
PaymentSelector
AccountContact
Cashflow
VatTypesdomvte, #code
TransactionTypesdomtte, #code
TransactionStatusesdomtss, #code
Transactionsrtxn, #EntryNumber
TransactionLinesrtle, #ID
DocumentsDocuments: rdct, #ID
AssetsAssets: rast, #ID
SubscriptionsSubscription: rscn, #EntryID
ProjectsProject: rpjt, #ID
ItemsLogistics: ritm, #ID
GLAccountsFinancial: rgat, #ID
AccountsAccounts: ract, #ID
CostunitsPayroll: rcut, #Code
CostcentersPayroll: rccr, #Code
OffsetID
Financial Transactions
PurchaseVATCode
GLAccount
VatTransactionTypesdomvxe, #code
VatDocTypesdomvde, #code
TaxReturnTypesdomtre, #code
IntrastatTypesdomite, #code
EuSalesListingsdomesg, #code
VatPercentagesrvpe
VATCodesrvce, #ID
TaxSchedulesrtse, #IDTaxScheduleComponents
rtst
AccountsAccounts: ract, #ID
GLAccountsFinancial: rgat, #ID
VATCodeID
VatDocType
VATTransactionType
TaxReturnType
IntrastatType
EUSalesListing
Account
Account
SalesVATCode
GLToPay
GLToClaim
GLDiscountSales
GLDiscountPurchase
TaxSchedule
VAT
PaymentConditionSales
PaymentConditionPurchase
BankAccountTypesdombae, #code
AddressTypesdomade, #code
AccountTypesdomate, #code
InvoicingMethodsdomimd, #code
AccountConsolidationScenariosdomaco, #code
AddressStatesrase, #State
AccountClassificationNamesrace, #ID
Contactsrctt, #ID
BankAccountsrbat, #ID
Addressesrads, #ID
Accountsract, #ID AccountClassifications
racn, #ID
SystemUsersSystem: rsur, #ID
DocumentsDocuments: rdct, #ID
BanksMailbox: rcbk, #ID
WarehousesLogistics: rwhe, #ID
ShippingMethodsSales: rsmd, #ID
VATCodesVAT: rvce, #ID
PaymentConditionsCashflow: rcpn, #ID
GLAccountsFinancial: rgat, #ID
PriceListsSales: rspl, #ID
CostcentersPayroll: rccr, #Code
AccountMainContactPerson
ResellerParent
Accountant
InvoiceAccount
Type
MainContact
IdentificationUser
AccountManager
Person
IdentificationDocument
SalesVATCode
PurchaseVATCode
GLAP
GLAccountSales
GLAccountPurchase
GLAR
Accounts
SolutionTypesdomstp, #code
SolutionLinkStatusesdomsls, #code
CustomerCooperationsrwcn
InvolvedUsersriur
InvolvedUserRolesriue, #ID
AccountOwnersraor
AccountInvolvedAccountsrait
SolutionLinksrslk
ExcludedDivisionsraen
OpportunitiesCRM: ropy, #ID
ContactsAccounts: rctt, #ID
EmployeesPayroll: reye, #ID
DocumentsDocuments: rdct, #ID
ProjectsProject: rpjt, #ID
AccountsAccounts: ract, #ID
SystemUsersSystem: rsur, #ID
ItemsLogistics: ritm, #ID
SystemDivisionsSystem: rsdn, #Code
Customer
Item_2
Item_1
InternalSolutionDivision
Accountancy
Document
top related