How to activate Accounting Export Plugin

The Accounting Export Interface allow to account manager to have more control and capacity to solve issues immediately.

The accounting export has basic initialization and custom parameterization available for every SecuTix customer.

Prerequisites

Check with Platform team that the generic query is installed for the institution.

Set up

Create an export interface of type "Accounting"

New Interface →  Exports →  Accounting

The field "Accounting code required" is not taken into account (will disappear in the future, for the moment set the "No" value).

Fill the values on the previous image, which should all be provided by the SecuTix customer.

Schedules

It is possible to generate two different reports, PNM or CSV formats.

New schedule: 

Generate CSV accounting file

Mandatory fields:

Line types, by default: Sales, VAT and Payments

Amount formatting, by default: 0.00

Decimal separator, by default: .

Filename prefix, by default: 'ExportComptable_'yyMMdd_HHmm'.csv' it is possible to change the text 'ExportComptable'. Example: Filename prefix: 'AccountingExport_'yyMMdd_HHmm'.csv' → Result name: AccountingExport_230619_1126.csv

With headers, by default: marked

Generate PNM accounting file

Mandatory fields:

Line types, by default: Sales, VAT and Payments

Amount formatting, by default: 0.00

Decimal separator, by default: .

Filename prefix, by default: 'ExportComptable_'yyMMdd_HHmm'.pnm' it is possible to change the text 'ExportComptable'. Example: Filename prefix: 'AccountingExport_'yyMMdd_HHmm'.pnm' → Result name: AccountingExport_230619_1126.pnm

Header line, by default: COMPTABILITE

Rest of fields for CSV and PNM:

Cash desk closes from (dd/MM/yy hh:mm)

Cash desk closes to (dd/MM/yy hh:mm)

Email recipient(s: Who are the receivers of the reports, separated by ;

Ordering: Fields by which to order accounting lines in file created, separated by ,

Computed fields: Formulas to calculate values for each file column pasted one after another. Max capacity of the box 4.000 characters (Following custom parameter box format). See details below. (Default CSV formula or PNM formula, also specified below)

Encoding, by default UTF-8

End of line, by default Unix

Only order ids, generate a file with only the specified orders

File to upload, to ignore

How to write Formulas for "Computed fields":

Formulas

"Constant text" : returns any text contained within " "

                     **to include brackets () as part of constant text, each bracket must be preceeded by - , e.g. " -(part á "&FORMAT(_vat_rate;0.00)&"% -)"

                     **& symbol should be avoided as literal text wherever possible

_field_value : returns value of field_value 

MANDATORY(_field_value) :  field_value is mandatory and if no value given in raw data, file is not created and error is logged

FORMAT(field;format) : value of field will be formatted according to format

Available Formats (for FORMAT() formula)

0.00

az09 → abcdefghijklmnopqrstuvwxyz1234567890 

dd.mm.yyyy

ddmmyyyy

mm.aaaa

IF(logical_test;value_if_true;else) : if logical_test returns true, value_if_true will be returned (and analysed if it is a formula), otherwise else will be returned (and analysed if it is a formula).

IFERROR(value;value_if_error) : returns value if possible, and if not, value_if_error

OR(value_1;value_2;…;value_n);true_value;false_value : if any of the conditions (value_1, value_2, etc.) return true, returns true_value; if all of the conditions return false, returns false_value 

value_1&value_2&value_n : concatenates value_1 with value_2, etc. 

value_a=value_b : returns true if value_a is equal to value_b, else returns false

Examples:

Default CSV formula:

Code journal="ID"
N de compte=IF(_line_type=SALE;_accounting_code_1;_line_type=VAT;_vat_accounting_code_1;_payment_method_internal_name)
Piece="Ventes-"&FORMAT(_order_date;dd-MM-yyyy)
Libelle=IF(_line_type=SALE;_name&" -(part à "&FORMAT(_vat_rate;0.00)&"%-)";IF(_line_type=VAT;"TVA collectée à "&FORMAT(_vat_rate;0.00)&"%";"Paiement "&_payment_method_internal_name))
Debit=IF(_credit_debit=D;_amount;0.00)
Credit=IF(_credit_debit=C;_amount;0.00)
Analytique=IF(_line_type=SALE;_accounting_code_2;"")

Default PNM formula:

code_journal="VTE"
date_de_piece=FORMAT(_order_date;yyMMdd)
compte_general=IF(OR(_line_type=SALE;_line_type=ANALYTIC);_accounting_code_1;_line_type=VAT;_vat_accounting_code_1;_accounting_code_1)
type_de_compte=IF(_line_type=ANALYTIC;"A";"G")
compte_auxiliaire_ou_analytique=IF(_line_type=ANALYTIC;_accounting_code_2;"")
reference_ecriture=_invoice_number
libelle=IF(OR(_line_type=SALE;_line_type=ANALYTIC);IF(_delayed_payment=Yes;"Vente "&IFERROR(_purchaser_structure_name;_purchaser_last_name&", "&_purchaser_first_name);_name);IF(_line_type=VAT;"TVA collectée à "&FORMAT(_vat_rate;0.00)&"%";_payment_method_internal_name))
mode_de_paiement="S"
date_d_echeance=IFERROR(_invoice_date;IFERROR(_order_date;""))
sens=_credit_debit
montant_en_monnaie_de_tenue_de_compte=_amount
type_d_ecriture="N"
zone_reservee=_sales_channel_code
code_iso_de_la_monnaie="EUR"

Data examples, can be used in formulas: