For each of the accounting data in Smart Capture, the order, number, and attributes expected for each SQL query result are as follows.
EXPENSE ACCOUNT
| Account Number | Entitled | Analytics allowed (0 or false, 1 or true) | Additional Information… (see notes) |
|---|
ACCOUNTS PAYABLE
| Account Number | Entitled | Third-party number | Discount Rate (Optional / Required if using additional information) | Additional Information… (see notes) |
|---|
CUSTOMER ACCOUNT
| Account Number | Entitled | Third-party number | Discount Rate (Optional / Required if using additional information) | Additional Information… (see notes) |
|---|
VAT ACCOUNT
| Account Number | Entitled | VAT Rate (Optional / Mandatory if using additional information) | Additional Information… (see notes) |
|---|
EXERCISE
| Start Date | End Date |
|---|
NEWSPAPER
| Type (0 for buy, 1 for sell) | Name | Code |
|---|
ANALYTICAL PLAN (IN THE CASE OF ANALYTICAL BREAKDOWN BY PLAN)
| Analytical Plan Number | Title of the plan | Section Number | Section Title |
|---|
ANALYTICAL BREAKDOWN GRID (IN THE CASE OF ANALYTICAL BREAKDOWN BY PLAN)
| Title of the grid | Analytical Plan Number | Section Number | Type of distribution (0 = Percentage, 1 = Balancing; 2 = Amount) | Distribution Value |
|---|
GRID ASSOCIATED WITH AN ACCOUNT (IN THE CASE OF ANALYTICAL BREAKDOWN BY PLAN)
| Associated Account Number | Analytical Plan Number | Title of the plan | Section Number | Section Title | Type of distribution (0 = Percentage, 1 = Balancing; 2 = Amount) | Distribution Value |
|---|
ANALYTICAL PLAN (IN THE CASE OF ANALYTICAL BREAKDOWN BY SECTIONS)
| Group | Section Number | Section Title |
|---|
ANALYTICAL BREAKDOWN GRID (IN THE CASE OF SECTIONAL ANALYTICAL BREAKDOWN)
| Title of the grid | Type of distribution (0 = Percentage, 1 = Balancing; 2 = Amount) | Distribution Value | Code Sections … |
|---|
In the case of a section breakdown grid, the “Section Code” column must be named with the name of the group, defined in the analytic plan query. It is possible to use as many “Code Sections” columns as groups.
Remarks
Additional information is available from version 3.4.0
Any information that follows the columns indicated as mandatory (Black column) is considered as additional information
In order for this information to be recorded correctly, the column of the Bee’s Smart Capture must have a specific name that will be used as a key to find it in the Open Bee Smart Capture software.
In SQL, naming a column in a SELECT is done by adding the AS keyword behind the column’s value. Example for Expense Account:
SELECT NumCompte, libelleCompte, 1, profilTVA AS PROFIL_TVA, 'Autre infos' AS SUP_INFO FROM PlanCp
Their information, except in special cases (see paragraph below), is stored on the account and can be found on the account management page.
Populate the third-party record via a SQL query
It is possible to populate the third-party sheet when importing the third-party plan.
To do this, simply use the additional information provided in the request. Indeed, an additional piece of information named with the ID of an index of the third-party record will have the effect of populating it when the third party is created or modified in Open Bee™ Scan Capture.
ex. “testcapture@gmail.com” AS MAIL_INDEX
By default, the value of an additional piece of information corresponding to a third-party index will only populate the third-party record if:
- The imported value is not empty
- The value already present in the business partner card is empty
If you want to force the addition or modification of a value, simply name your additional information: [ID_INDEX]_FORCE
e.g. “testcapture@gmail.com” AS MAIL_INDEX_FORCE