...
Generate PNM accounting file
Schedule fields:
| Name | Possible value or definition (if apply) | Default value | Mandatory |
|---|---|---|---|
| 01/01/2023 00:00 | - | No | |
| 30/06/2023 00:00 | - | No | |
| Line types | Sales, VAT, Payments, Analytic, Sales Compensation, Payments Compensation | Sales, VAT and Payments | Yes |
| Email recipient(s) | name@elca.ch | - | No |
| Ordering | Fields by which to order accounting lines in file created, separated by , | No | |
| Amount formatting | 0.00 0,00 | 0.00 | Yes |
. , | . | Yes | |
| 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, specified below) | - | No |
| Yes | |||
| Only order ids | Generate a file with only the specified orders | - | No |
| The file will be created with the especific header | Yes, only for PNM | ||
| With headers | The file will be created with headers | Marked | Yes, only for CSV |
| Encoding | UTF-8 | UTF-8 | No, only for CSV |
| End of line | Unix / Windows | Unix | No, only for CSV |
| Field separator | Separator of the values, example: |
| ' ' space, ' ' tabulation | tabulation |
| No, only for CSV | |||
| File to upload | Ignore | ||
How to write Formulas for "Computed fields":
| Name | Syntax | Explanation |
|---|---|---|
| Constant | "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 |
| Variable | _field_value | returns value of field_value |
| Mandatory | MANDATORY(_field_value) | field_value is mandatory and if no value given in raw data, file is not created and error is logged |
| Format | 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 | 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) |
| If error | IFERROR(value;value_if_error) | returns value if possible, and if not, value_if_error |
| Or | 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 |
| Concatenate | value_1&value_2&value_n | concatenates value_1 with value_2, etc |
| Equal | value_a=value_b | returns true if value_a is equal to value_b, else returns false |
...
Default CSV formula:
| Code Block |
|---|
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 Block |
|---|
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" |
...