Automatic Bank Statement Reconciliation Setup

Reconciliation types in (ABS960)

In 'Reconciliation Type. Open' (ABS960), a bank statement reconciliation type is defined for each bank account for which automatic bank statement reconciliation should be performed.

The bank statement reconciliation type is linked to a bank account ID defined in (CRS692), to which an account, from the chart of accounts, is linked.

The bank statement reconciliation type can be linked to a responsible person, defined as a user in 'User. Open' (MNS150). This person will be automatically set as responsible for new bank statements entered for this bank statement reconciliation type in (ABS180).

The parameter 'Reverse sign' is not used by the functionality in M3 BE, but could be used by a Financial Business Message (FBM) to reverse signs for amounts uploaded to (ABS180) and (ABS181).

If the parameter 'Start automatic reconciliation' is activated, automatic reconciliation will be performed during the validation run, if the validation ends without any errors.

The parameter 'Set debit/credit code automatically' can be used in the situation where the division has been defined as using debit/credit codes in 'Settings – General Ledger' (CRS750), but the bank statements received from the bank do not contain debit/credit codes. To be able to perform automatic reconciliation for such bank statements, when activated, this parameter adds debit/credit codes to the bank statement lines in (ABS181), by translating positive amounts to debit transactions and negative amounts to credit transactions.

Reconciliation search sequences in (ABS965)

To each bank statement reconciliation type, a reconciliation search sequence is linked. The reconciliation search sequences are defined in 'Reconciliation Search Sequence. Open' (ABS965) and are used to define how the automatic reconciliation is to be performed for a certain bank statement reconciliation type. The automatic reconciliation can be handled differently for different bank accounts.

For example, it could be based on check number for one bank account, to which check payments are received, whereas another bank account could be reconciled based on accounting dimension 2, as the bank transactions concern payments posted on different cost centers defined in accounting dimension 2 in (CRS630).

The fields available for setup in the reconciliation search sequences are defined in field group 'ABSSO' in 'Field Group. Display Permitted Fields' (CRS109):

• &002 – Check number
• &AIT2 – Accounting dimension 2
• &AIT3 – Accounting dimension 3
• &AIT4 – Accounting dimension 4
• &AIT5 – Accounting dimension 5
• &AIT6 – Accounting dimension 6
• &AIT7 – Accounting dimension 7
• &OPC – Bank operation code
• &CUAM – Foreign currency amount
• &CURD – Value date
• &VTXT – Voucher text

For each reconciliation search sequence, five levels of search sequences can be defined. For each level, five different search sequence objects can be selected from the available fields listed above. The five different search sequence objects defined for one level constitutes the 'search string' that is used during automatic reconciliation. All selected objects for level 1 must match between the bank statement line and the general ledger transaction, to have those transactions automatically reconciled.

If not matched, the program tries to perform automatic reconciliation based on the selected objects for level 2 and if still not matched, it continues with level 3, 4, and 5. If still not matched after level 5, no automatic reconciliation can be performed, based on the current setup of the reconciliation search sequence for the bank statement reconciliation type.

When transactions in the general ledger are identified to match a bank statement line, based on the 'search string' built up by the search sequence objects, the amounts of those transactions must also be the same as the amount of the bank statement line, otherwise they cannot be reconciled against each other. If currency amount (&CUAM) is one of the search sequence objects, then the amount of one single transaction in the general ledger must be the same as the amount of one single bank statement line.

If currency amount is not among the search sequence objects, the total amount of all transactions in the general ledger having the correct 'search string' can be matched against one single, or several, bank statement lines, if the total of those lines is equal to the total of the general ledger transactions. Similarly, if the total amount of several bank statement lines matches the amount of one single general ledger transaction, they can also be matched.

Example of setup of the search sequence levels and objects:

Search sequence level	Search sequence object
1	&002 and &CUAM
2	&AIT2 and &CUAM
3	&AIT2
4	No setup
5	No setup

With this setup of the reconciliation search sequence, the program will start by searching for a transaction in the general ledger that has the same check number and amount as stated on the bank statement line. If not found, it will continue by trying to find a transaction in the general ledger with the same value in accounting dimension 2 and the same amount as stated on the bank statement line.

If such a transaction is still not found, it will continue by searching for all transactions in the general ledger with the same value in accounting dimension 2 as stated on the bank statement line. If the total amount of all those transactions in the general ledger is equal to the amount of the bank statement line, an automatic reconciliation will be made. If the total amount does not match the bank statement line, no further search will be performed, as no search sequence objects have been defined for levels 4 and 5.

If accounting date (&ACDT) is one of the search sequence objects, then the accounting date of the transactions in the general ledger must be the same as the accounting date of the bank statement line to automatically reconcile those transactions. The same applies to the situation where value date (&CURD) is among the search sequence objects.

If the search sequence objects have been defined in a way that more than one transaction in the general ledger can be matched against one, or more, bank statement lines, then the parameter 'Group reconciliation by date' can be used to further refine the automatic reconciliation.

These are the alternatives:

• 0 = Not used
• 1 = Grouped by accounting date
• 2 = Grouped by value date

If this parameter has been set to 1 - 'Grouped by accounting date' or 2 - 'Grouped by value date', the transactions identified to have the correct search string will be grouped by date, either by accounting date or by value date. This means that the total amount of the selected transactions in the general ledger, per date, must match the total amount of the bank statement lines, per date. So, if the total amount of all selected transactions match, but the amounts per date do not match, no automatic reconciliation will be made.

Example of grouping per date where all transactions have the same search string:

Transactions in the general ledger:

• January 1: 1000
• January 1: 2000
• January 1: 500
• January 2: 3000

Bank statement lines:

• January 5: 3000
• January 6: 3500

The total amount of the transactions in the general ledger is 6500. The total amount of the corresponding bank statement lines is also 6500. If 'Group reconciliation by date' has been set to 0, all these transactions in the general ledger and the two bank statement lines will be automatically reconciled against each other, all in the same connection number.

If 'Group reconciliation by date' has been set to 1 or 2, the transactions in the general ledger posted on date January 1, with a total of 3500, will be reconciled against the bank statement line for January 6 with amount 3500. Similarly, the transaction in the general ledger posted on date January 2 with amount 3000, will be reconciled against the bank statement line for January 5 with amount 3000. In this situation, two different connection numbers will be used for the reconciliation of this bank statement.