Processing CODA files
CODA, or geCOdeert DAgafschrift is a bank statement or ‘Coded statement of account’ filetype used in Belgium.
Reconciling CODA files
The reconciliation process allows you to update and confirm your payment data in Salesforce. FinDock turns CODA files into a Transaction Set with Transaction records that are processed using Guided Matching.
Requirements
Like other Bank statements, CODA files are uploaded to a specific Salesforce Chatter group to be parsed by FinDock. FinDock requires a SEPA target with the bank account for which you want to reconcile a CODA file.
For more information on the general Processing bank statement processing flow, please read our Processing bank statements article.
CODA file structure
FinDock parses CODA files following the structure set out by Febelfin, the Belgian Federation for the Financial sector, as documented in the Coded statement of account (CODA) description (PDF). This document contains every detail on CODA, so it’s worth checking out if you wish to understand CODA reconciliation further.
Fixed Width files
CODA files are structure Fixed Width data files. Fixed Width files are arranged in columns, but instead of the columns being separated by a character like a comma or semicolon every row is the same length. Every column has their own fixed width.
ID(10) Name(15) Address(25) Amount(7)
This is complicated by the fact that, in the case of CODA, what data is available can be determined by the content of other columns. So unlike with a CSV or Excel file, each row in the file can have a different structure, and not every row is a separate transaction.
Transaction types
A CODA file consists of transactions identified by a transaction ID. A transaction can consist of several cash movements, for instance when fees are deducted or exchange rates are applied, captured by movement records.
When the transaction type code starts with 0 or 1, FinDock creates Transaction records for the transaction entries. However, if subtype 5 is present, FinDock skips the type 1 entry and instead creates a Transaction record with the details from the subtype 5 entry.
When the transaction type code starts with 2 or 3, the transaction is treated as a summary transaction and ignored. Only the underlying transactions subtypes 6 or 7 lead to Transaction records in Salesforce.
Coded Statement of Account 3.1 Type
| Type | Sub-Type | Sub-Type | Description |
|---|---|---|---|
| 0 | Simple amount without detailed data; e.g. : an individual credit transfer (free of charges) | ||
| 1 | Amount as totalised by the customer; e.g. a file regrouping payments of wages or payments made to suppliers or a file regrouping collections for whic the customer is debited or credited with one single amount. As a matter of principle, this type is also used when no detailed data is followi (type 5). | ||
| 5 | detail of 1 Standard procedure is no detailing. However, the customer may ask for detailed data to be included into his file after the overall record (type 1). | ||
| 2 | Amount as totalised by the bank; e.g. : the total amount of a series of credit transfers with a structured communication As a matter of principle, this type will also be used when no detailed data (type 6 or 7) is following. | ||
| 6 | detail of 2, simple amount without detailed data Normally, data of this kind comes after type 2. The customer may ask for a separate file containing the detailed data. In that case, one will speak of a ‘separate application’. The records in a separate application keep type 6. | ||
| 7 | detail of 2, simple account with detailed data The records in a separate application keep type 7. | ||
| 9 | detail of 7 The records in a separate application keep type 9. | ||
| 3 | Simple amount with detailed data; e.g. in case of charges for cross-border credit transfers | ||
| 8 | detail of 3 |
Movement and Information records
A transaction contains detailed data in the form of movement records, which can in turn have information records with additional information. A transaction’s movement and information data stored in the Raw XML field on the Transaction record could look like follows:
21000100009876546498751245 00000001976111801912180015000007013X000001mAZbQAM/abcdefg 19121823801 0
2200010000 0000123456 KREDBEBB 1 0
2300010000BE12345687952177 EURJAN JANSEN 0 1
31000100019876546498751245 001500001001JAN JANSEN 1 0
3200010001JAARGETIJDENLAAN 150 B16 1050 BRUSSEL 0 0
The rows starting with a 2 are movement records, where 21 is the main movement record and the rows starting with 22 and 23 store additional data about the movement.
The rows starting with a 3 are information records, where 31 is the main information record and the rows start with 32, and possibly even 33 store additional information.
Each type of record (21, 22, 23, 31, 32, 33) has its own structure. A movement record starting with 21 for instance has the following structure.
2|1|0001|0000|2007211901244519 |0000000000506000|210720|00150000|0 |210720|198|0|1| |0|
The detailed structure of all records can be found in Annexe I of the Coded statement of account (CODA) starting at page 14.
Example: Data record 2.1 - "movement record"
| Positions | Number | Type | Content | Tag |
|---|---|---|---|---|
| 1 | 1 | N | Record identification = 2 | 0 |
| 2 | 1 | N | Article code = 1 | 2.00721E+15 |
| 3-6 | 4 | N | Continuous sequence number Starts at 0001 and is increased by 1 for each movement record referring to another movement on the daily statement of account. If there are more than 9,999 transactions, the number goes up to 0000 and then 0001. | 90400 |
| 7-10 | 4 | N | Detail number starts at 0000 and is increased by 1 for each movement record for the same continuous sequence number. If there are more than 9,999 details relating to one single transaction, the number goes up to 0000 and then 0001. | 210720 |
| 11-31 | 21 | AN | Reference number of the bank This information is purely informative. | 61/8 |
| 32 | 1 | N | Movement sign: 0 = credit 1 = debit | 61/3 |
| 33-47 | 15 | N | Amount: 12 pos. + 3 decimals | 61/5 |
| 48-53 | 6 | N | Value date or 000000 if not known (DDMMYY) | 61/1 |
| 54-61 | 8 | N | Transaction code (see enclosure II) | 61/6 |
| 62 | 1 | N | Communication type: 0 = none or unstructured 1 = structured | ABCDE123345 |
| 63-115 | 53 | AN | Communication zone: - if pos. 62 = 0 free communication in pos. 63 up to 115 - if pos. 62 = 1 type of structured communication in pos. 63 up to 65, and communication as of pos. 66 (see enclosure III) | 61/9 |
| 116-121 | 6 | N | Entry date DDMMYY | 61/2 |
| 122-124 | 3 | N | Sequence number statement of account on paper or Julian date or zeros. As for a non-Belgian account number: last 3 positions of the first part of the statement of account number. | 28/c |
| 125 | 1 | N | Globalisation code Marks the beginning and end of a globalisation for each hierarchy level. | |
| 126 | 1 | N | Next code: 0 = no record 2 or 3 with record identification 2 is following 1 = a record 2 or 3 with record identification 2 is following | |
| 127 | 1 | AN | Blank | |
| 128 | 1 | N | Link code with next data record: 0 = no information record is following (data record 3) 1 = an information record is following |
Guided matching for CODA
FinDock provides managed and suggested Guided Matching rules for CODA. You can add your own Guide Matching rules to the predefined rule sets.
Using Guided Matching for CODA
To start reconciliation of CODA, upload your file to Chatter. The file is picked up by FinDock and transformed into Transaction records. FinDock creates a Transaction record in Salesforce for each transaction record in the CODA file, with the movement records as data in the Raw XML field.
Transactions can also be grouped or summarized in a transaction record in the file. In those cases FinDock only creates Transaction records from the detailed records and the summary transaction is left out. For more information about summarized transactions, please read section 3. Coding of the transaction, specifically section 3.1 type of the Coded statement of account (CODA) description linked above.
Matching rule behavior of managed rules
The Guided Matching rules defined for CODA apply to all CODA files, regardless of the target used.
Once Transaction records have been created, Guided Matching will take over. With a set of pre-configured Guided Matching rules, FinDock:
- Maps fields from the CSV (as stored in the ‘Raw Message’ field) to the Transaction
- Extracts relevant data (see below for mapping)
- Searches for existing Installments based on the extracted data
- If found, sets the Contact based on the existing Installment
- If not found:
- Ask the user for Contact information by providing a search bar that allows you to both search and select an existing Contact, as well as create a new Contact.
- Creates a new Installment with the selected contact
- Processes the Installment by updating the amount and changing the status if applicable.
The managed rules create mapping for Transaction as outlined in the table below.
| Field | Record identification (pos. 1-2) | Additional logic | Values in pos. | Example |
|---|---|---|---|---|
| Type (debit / credit) | 21 | - | 32 (0=credit, 1=debit) | 0 |
| Account Servicer Reference | 21 | - | 11-31 | 2.00721E+15 |
| Actual Amount | 21 | - | 33-47 (leading 0s, 3 decimals) | 90400 |
| Value Date | 21 | - | 48-53 (DDMMYY, 000000 if unknown) | 210720 |
| Reported Payment Method Code | 21 | - | 55-56 (or do we need the full transaction code?) | 5 |
| Reported Payment Method | - | Based on Transaction code. See table below | - | SEPA credit transfer |
| Description / Communication / Additional Entry information | 21 | Structured or unstructured based on 62 = 0 or 1 | 63-115 | unstructured: 7013X000001mAZbQAM/Donation towards campaign X |
| Entry Date | 21 | 116-121 | 210720 | |
| Payment Reference | 22 | 22 exists | 64-98 | ABCDE123345 |
| End to end id | 22 | 22 exists | 64-98 (identical to payment reference?) | ABCDE123345 |
| Bank Account Number (IBAN) & Currency code | 23 | 23 exists | 11-47 | BE68539007547034 EUR |
| Account Holder Name | 23 | 23 exists | 48-82 | Jan Jansen |
| Reason Code | 22 | 22 exists | 114-117 | AC03 |
Extending FinDock Guided Matching rules for CODA
FinDock does not parse bank specific rules for unstructured communication out of the box. You can however parse this data by extending the out-of-the-box provided Guided Matching rules yourself or executing business logic on the Additional Entry Information field where the communication is stored.
For instance, if you know that the first 18 characters of the (unstructured) communication contains the CampaignId present in your Salesforce environment, you could either:
- extract the CampaignId from the Additional Entry Information with a Regex rule on Campaign into a free field on the Transaction record and then Query the campaign based on the extracted Id.
- directly parse the CampaignId with a Fixed Width rule of your own on the RAW XML field where the original CODA movement and information records with regards to this Transaction are stored.
Or if you want to parse the address of a detailed information record (starting with 31 and 32) with structured communication, you could use the Fixed Width rule to get the Street based on its position in the record. You can limit the extraction of these characters to when the record itself indicates the communication is structured and contains a specific set of data to prevent extracting data from records that do not provide the right data.
For instance, in the example below we extract the Street from positions 11-45 on line 32 in the Raw XML field, but only when position 40-43 on line 31 is ‘1001’: a structured communication (1) with data concerning the counter party (001) as per the Febelfin CODA rules.
