{"id":21615,"date":"2022-04-11T13:59:46","date_gmt":"2022-04-11T11:59:46","guid":{"rendered":"https:\/\/help.openbee.com\/open-bee-smart-capture\/administrator-guide\/accounting\/gateway\/accounting-import-bdd-sql\/formatting-sql-queries\/"},"modified":"2024-05-20T14:43:35","modified_gmt":"2024-05-20T12:43:35","slug":"formatting-sql-queries","status":"publish","type":"page","link":"https:\/\/help.openbee.com\/en\/open-bee-smart-capture\/administrator-guide\/accounting\/gateway\/accounting-import-bdd-sql\/formatting-sql-queries\/","title":{"rendered":"Formatting SQL queries"},"content":{"rendered":"
\n

For each of the accounting data in Smart Capture, the order, number, and attributes expected for each SQL query result are as follows.<\/p>\n

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

\n\n\n\n
Account Number<\/th>\nEntitled<\/th>\nAnalytics allowed (0 or false, 1 or true)<\/th>\nAdditional Information… (see notes) <\/em><\/span><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Account Number<\/th>\nEntitled<\/th>\nThird-party number<\/th>\nDiscount Rate (Optional \/ Required if using additional information)<\/em><\/span><\/th>\nAdditional Information… (see notes)<\/em><\/span><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


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

\n\n\n\n
Account Number<\/th>\nEntitled<\/th>\nThird-party number<\/th>\nDiscount Rate (Optional \/ Required if using additional information)<\/span><\/em><\/th>\nAdditional Information… (see notes)<\/em><\/span><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Account Number<\/th>\nEntitled<\/th>\nVAT Rate (Optional \/ Mandatory if using additional information)<\/em><\/span><\/th>\nAdditional Information… (see notes)<\/em><\/span><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/em><\/p>\n

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

\n\n\n\n
Start Date<\/th>\nEnd Date<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Type (0 for buy, 1 for sell)<\/th>\nName<\/th>\nCode<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Analytical Plan Number<\/th>\nTitle of the plan<\/th>\nSection Number<\/th>\nSection Title<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Title of the grid<\/th>\nAnalytical Plan Number<\/th>\nSection Number<\/th>\nType of distribution (0 = Percentage, 1 = Balancing; 2 = Amount)<\/th>\nDistribution Value<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Associated Account Number<\/th>\nAnalytical Plan Number<\/th>\nTitle of the plan<\/th>\nSection Number<\/th>\nSection Title<\/th>\nType of distribution (0 = Percentage, 1 = Balancing; 2 = Amount)<\/th>\nDistribution Value<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

\n\n\n\n
Group<\/th>\nSection Number<\/th>\nSection Title<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


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

\n\n\n\n
Title of the grid<\/th>\nType of distribution (0 = Percentage, 1 = Balancing; 2 = Amount)<\/th>\nDistribution Value<\/th>\nCode Sections …<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\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<\/div>\n<\/div>\n

\n
\n

Remarks<\/p>\n

\n

Additional information is available from version 3.4.0<\/p>\n

Any information that follows the columns indicated as mandatory (Black column) is considered as additional information
\nIn 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.<\/p>\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
\n
SELECT NumCompte, libelleCompte, 1, profilTVA AS PROFIL_TVA, 'Autre infos' AS SUP_INFO\nFROM PlanCp\n\n<\/pre>\n<\/div>\n<\/div>\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
Populate the third-party record via a SQL query<\/u><\/strong><\/p>\n
It is possible to populate the third-party sheet when importing the third-party plan.
\nTo 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
ex. “testcapture@gmail.com<\/a>” AS MAIL_INDEX<\/p>\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