{"id":21024,"date":"2022-04-11T13:55:46","date_gmt":"2022-04-11T11:55:46","guid":{"rendered":"https:\/\/help.openbee.com\/open-bee-scan-capture\/administrators-guide\/accounting\/setting-up-the-accounting-import\/accounting-import-bdd\/formatting-sql-queries\/"},"modified":"2026-05-13T09:40:00","modified_gmt":"2026-05-13T07:40:00","slug":"formatting-sql-queries","status":"publish","type":"page","link":"https:\/\/help.openbee.com\/en\/open-bee-scan-capture\/administrators-guide\/accounting\/setting-up-the-accounting-import\/accounting-import-bdd\/formatting-sql-queries\/","title":{"rendered":"Formatting SQL queries"},"content":{"rendered":"\n

For each of the accounting data in Open Bee\u2122 Scan Capture, here is the order, number, and attributes expected for each SQL query result.<\/p>\n\n

EXPENSE ACCOUNT<\/strong><\/p>\n\n

Account Number<\/th>Entitled<\/th>Analytics allowed (0 or false, 1 or true)<\/th>Additional Information… (see notes)<\/em><\/th><\/tr><\/tbody><\/table><\/figure>\n\n

ACCOUNTS PAYABLE<\/strong><\/p>\n\n

Account Number<\/th>Entitled<\/th>Third-party number<\/th>Discount Rate (Optional \/ Required if using additional information)<\/em><\/th>Additional Information… (see notes)<\/em><\/th><\/tr><\/tbody><\/table><\/figure>\n\n

CUSTOMER ACCOUNT<\/strong><\/p>\n\n

Account Number<\/th>Entitled<\/th>Third-party number<\/th>Discount Rate (Optional \/ Required if using additional information)<\/em><\/th>Additional Information… (see notes)<\/em><\/th><\/tr><\/tbody><\/table><\/figure>\n\n

VAT ACCOUNT (Up to 4.8.1)<\/strong><\/p>\n\n

Account Number<\/th>Entitled<\/th>VAT Rate (Optional \/ Mandatory if using additional information)<\/em><\/th>Additional Information… (see notes)<\/em><\/th><\/tr><\/tbody><\/table><\/figure>\n\n

VAT ACCOUNT (Since 4.8.2)<\/strong><\/p>\n\n

Account Number<\/th>Entitled<\/th>VAT Rate (Optional \/ Mandatory if using additional information)<\/em><\/th>(Optional) Analytics allowed (0 or false, 1 or true)<\/em><\/th>Additional Information… (see notes)<\/em><\/th><\/tr><\/tbody><\/table><\/figure>\n\n

EXERCISE<\/strong><\/p>\n\n

Start Date<\/th>End Date<\/th><\/tr><\/tbody><\/table><\/figure>\n\n

NEWSPAPER<\/strong><\/p>\n\n

Type (0 for buy, 1 for sell)<\/th>Name<\/th>Code<\/th><\/tr><\/tbody><\/table><\/figure>\n\n

ANALYTICAL PLAN<\/strong> (IN THE CASE OF ANALYTICAL BREAKDOWN BY PLAN)<\/strong><\/p>\n\n

Analytical Plan Number<\/th>Title of the plan<\/th>Section Number<\/th>Section Title<\/th>Mandatory plan (Optional – Available in 4.1.0 – possible values 1,true, 0, false)<\/em><\/th><\/tr><\/tbody><\/table><\/figure>\n\n

ANALYTICAL BREAKDOWN GRID (IN THE CASE OF ANALYTICAL BREAKDOWN BY PLAN)<\/strong><\/p>\n\n

Title of the grid<\/th>Analytical Plan Number<\/th>Section Number<\/th>Type of distribution (0 = Percentage, 1 = Balancing; 2 = Amount)<\/th>Distribution Value<\/th><\/tr><\/tbody><\/table><\/figure>\n\n

GRID ASSOCIATED WITH AN ACCOUNT (IN THE CASE OF ANALYTICAL BREAKDOWN BY PLAN)<\/strong><\/p>\n\n

Associated Account Number<\/th>Analytical Plan Number<\/th>Title of the plan<\/th>Section Number<\/th>Section Title<\/th>Type of distribution (0 = Percentage, 1 = Balancing; 2 = Amount)<\/th>Distribution Value<\/th><\/tr><\/tbody><\/table><\/figure>\n\n

ANALYTICAL PLAN<\/strong> (IN THE CASE OF ANALYTICAL BREAKDOWN BY SECTIONS)<\/strong><\/p>\n\n

Group<\/th>Section Number<\/th>Section Title<\/th><\/tr><\/tbody><\/table><\/figure>\n\n

ANALYTICAL BREAKDOWN GRID (IN THE CASE OF SECTIONAL ANALYTICAL BREAKDOWN)<\/strong><\/p>\n\n

Title of the grid<\/th>Type of distribution (0 = Percentage, 1 = Balancing; 2 = Amount)<\/th>Distribution Value<\/th>Code Sections …<\/th><\/tr><\/tbody><\/table><\/figure>\n\n

In the case of a section breakdown grid, the “Section Code”<\/strong> 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”<\/strong> columns as groups.<\/p>\n\n

Remarks<\/h2>\n\n

Any information in italics after the mandatory columns is considered to be additional informationIn 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\u2122 Scan Capture software.<\/p>\n\n

In SQL, naming a column in a SELECT is done by adding the AS<\/strong> keyword behind the column’s value. Example for Expense Account:<\/em><\/p>\n\n

SELECT NumCompte, libelleCompte, 1, profilTVA AS PROFIL_TVA, 'Autre infos' AS SUP_INFO\nFROM PlanCp<\/code><\/pre>\n\n
Their information, except in special cases (see paragraph below), is stored on the account and can be found on the account management<\/a> page.<\/p>\n\n
Populate the third-party record via a SQL query<\/u><\/strong><\/p>\n\n
Since version 3.6.0 of Open Bee\u2122 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\u2122 Scan Capture.<\/p>\n\n
ex. “testcapture@gmail.com” AS MAIL_INDEX<\/p>\n\n
By default, the value of an additional piece of information corresponding to a third-party index will only populate the third-party record if:<\/p>\n\n