For each of the accounting data in Open Bee™ Scan Capture, here is the order, number, and attributes expected for each SQL query result.
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 (Up to 4.8.1)
| Account Number | Entitled | VAT Rate (Optional / Mandatory if using additional information) | Additional Information… (see notes) |
|---|
VAT ACCOUNT (Since 4.8.2)
| Account Number | Entitled | VAT Rate (Optional / Mandatory if using additional information) | (Optional) Analytics allowed (0 or false, 1 or true) | 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 | Mandatory plan (Optional – Available in 4.1.0 – possible values 1,true, 0, false) |
|---|
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
Any information in italics after the mandatory columns is considered to be additional information
In order for this information to be recorded correctly, the column of the Bee Scan Capture must have a specific name that will be used as a key to find it in the Open Bee™ Scan 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 PlanCpTheir 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
Since version 3.6.0 of Open Bee™ Scan Capture, it is possible to feed 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