Automatic Bank Statement Reconciliation Processing

This document describes the process for automatic bank statement reconciliation performed in 'Bank Statement Reconciliation. Open' (ABS180), through which general ledger transactions can be reconciled against transactions on a bank statement.

If a bank statement is received electronically as a file from the bank, the bank statement can be transferred to M3 Business Engine (M3 BE) through APIs for reconciliation against accounting entries in the general ledger. Through user-defined matching rules, the transactions in the general ledger can be automatically reconciled against the transactions on the bank statement. If all transactions have not been automatically reconciled, manual reconciliation can be made for the remaining transactions.

Limitations

If there are more than one possible match for the transaction on the bank statement, none of the transactions will be selected for automatic reconciliation. For example, if there is a bank statement line with amount 100 and there are five transactions in the general ledger with amount 100 that has the correct search string, none of those transactions will be selected for automatic reconciliation against the bank statement line. Similarly, if there is one transaction in the general ledger with amount 100 and two bank statement lines with amount 100, no automatic reconciliation will be performed. The program cannot know which are the correct transactions to match, but manual reconciliation can be performed afterwards to select the correct transactions for reconciliation.

Solution overview

The flowchart shows an overview of the programs and tables used in the functionality for automatic bank statement reconciliation. For each of the programs described below, there is an MI program with corresponding functionality.

Description

In 'Reconciliation Type. Open' (ABS960) (table FBSRET) 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 'Bank Account. Open' (CRS692), to which an account, from the chart of accounts, is linked.

To each bank statement reconciliation type, a reconciliation search sequence is linked. The reconciliation search sequences are defined in 'Reconciliation Search Sequence. Open' (ABS965) (table FBSREO) 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 'Accounting Identity. Open' (CRS630).

When a bank statement is received electronically, as a file from the bank, the bank statement can be transferred to 'Bank Statement Reconciliation. Open' (ABS180) through APIs available for this functionality. The bank statement can also be entered manually in (ABS180) (table FBSREH).

Each bank statement that is created in (ABS180) is linked to a bank statement reconciliation type defined in (ABS960) and if a responsible person has been linked to the bank statement reconciliation type, that person will automatically be set as responsible for the processing of the new bank statement created in (ABS180). The bank statement header in (ABS180) also contains information about opening and closing balance as well as bank statement date and from and to date for the bank statement. It is optional to enter information about opening and closing balance for the bank statement, but all other fields must be specified when a bank statement is created in (ABS180).

For each bank statement, the bank statement lines are found in 'Bank Statement Reconciliation. Open Lines' (ABS181) (table FBSREL), reached through related option 11 in (ABS180). Each bank statement line contains information about date and amount for the bank transaction, as well as other information that is important for the automatic reconciliation, such as check number, bank operation code, voucher text and accounting dimensions 2 to 7. Two dates can be updated for each bank statement line: accounting date and value date. Amount and at least one of these dates must be entered for each bank statement line.

When automatic reconciliation has been processed for a bank statement in (ABS180), the reconciled transactions can be displayed in 'Bank Stmnt Reconc. Reconciled Trans' (ABS182) (table FBSREC), reached through related option 12 in (ABS180). Bank statement lines are displayed as line type 1, whereas general ledger transactions are displayed as line type 2. Records that have been matched against each other are marked with the same connection number. If needed, a reconciliation can be reversed in (ABS182) through related option 23='Reverse Reconciliation'.

Bank statement lines and general ledger transactions that have not been automatically reconciled, can be displayed in 'Bank Stmnt Reconc. Not Reconciled Trans' (ABS185) (table FBSREM), reached through related option 13 in (ABS180). Bank statement lines are displayed as line type 1, whereas general ledger transactions are displayed as line type 2. Manual reconciliation of the remaining bank statement lines can be performed by selecting the records that should be matched with related option 8='Select line' and then using F20-'Reconcile selected lines'. If a line is selected by mistake, it can be released by using related option 9='Release line' or F21-'Release selected lines'.

If a transaction exists on the bank statement, but not in the general ledger, that transaction can be entered in 'Journal Voucher. Enter' (GLS100) by using F19-'Journal Voucher. Enter' in (ABS185). The new transaction will not automatically appear in (ABS185) after entry in (GLS100), but it will be retrieved when automatic reconciliation is restarted for the bank statement in (ABS180).

During the work with manual reconciliation in (ABS185), the 'Selection ID' is central. When a line has been selected in (ABS185), a 'Selection ID' is automatically assigned to the record. The 'Selection ID' consists of the current date (YYYYMMDD) and the next available number from number series 'S2-K' in 'Number Series. Open' (CRS165). All records having the same 'Selection ID' will get the same 'Connection number' in (ABS182) when reconciled. If a selected line is released with related option 9 or F21 in (ABS185), the 'Selection ID' is removed.

In the header of panel (ABS185/B) the field 'Selected amount from bank statement' displays the amount of the selected bank statement lines and the field 'Selected amount from general ledger' displays the amount of the selected general ledger transactions. While those two amounts are not equal, the remaining amount to be selected is displayed in field 'Difference'. When those two amounts are equal, the field 'Difference' will be blank and then it will be possible to reconcile the selected records with F20 in (ABS185). Reconciled transactions are then moved from (ABS185) to (ABS182).

Reconciled transactions in the general ledger are updated with external reconciliation code 9 and external reconciliation date, which can be displayed on 'General Ledger. Display Transactions' (GLS211/F) (table FGLEDG). Additional information 039-'Bank statement number' is added to the reconciled transactions, which can be displayed on 'General Ledger. Display Additional Info' (GLS212) (table FGLEDX).

Each general ledger transaction also exists in table FGLEDB, where information about value date and bank operation code is stored. When a general ledger transaction has been reconciled in (ABS180), the corresponding record in table FGLEDB is also updated with information about reconciliation date and bank statement number.

When all bank statement lines have been reconciled, the status of the bank statement header is set to 90 and the external reconciliation date is set on the bank statement header in (ABS180). The bank statement date is used as external reconciliation date, both for the bank statement header, the transactions in the general ledger and the transactions in table FGLEDB.

If a report is required when the bank statement is fully reconciled, a list of reconciled transactions can be downloaded to Excel from (ABS182) using Tools > Export to Excel.

Bank statement statuses

The bank statement lines in (ABS181) can have these statuses:

The bank statement header in (ABS180) can have these statuses:

The status of the lines and the header are automatically updated during the processing of the bank statement. The statuses set during the process flow are further described here:

Validation

When a new bank statement is received, or entered, in (ABS180), the status is set to 10. Then validation of the bank statement is started with related option 21='Validate' in (ABS180). During validation, the status is set to 20. The validation checks that the bank statement number is unique for the year and the reconciliation type, that there are lines for the bank statement and that the total of the lines reconciles with the difference between the opening and closing balance on the header. If no errors are found during the validation, the status is set to 30.

If errors are found during the validation, the status is set to 25 and then an application message of type 070 is created in 'Application message. Open' (CRS420) telling that errors have been found during validation of a certain bank statement number and job number. The application message is sent to the user who started the validation job. A more detailed message, describing the actual error, is also created in 'Detailed Mail Message. Open' (CMS421), reached through related option 14='Display Error Log' in (ABS180).

Automatic reconciliation

When the bank statement is in status 30, automatic reconciliation can be started with related option 22='Automatic Reconciliation' in (ABS180). During this run, the status is set to 40.

When the automatic reconciliation has finished, the status is either set to 50 or 90. If all bank statement lines have been reconciled, the status is set to 90 (both for the lines and the header) and the external reconciliation date is set on the bank statement header. If some bank statement lines have not been automatically reconciled, the status of those lines will remain to be 00 and the status on the bank statement header is set to 50.

Manual reconciliation

If manual reconciliation is performed in (ABS185), the status is set to 80, as soon as a line has been selected with related option 8. Once the selected lines have been reconciled with F20, or released with related option 9 or F21, the status is set back to 50 on the bank statement header. Status 80 is set as an indication that someone is working with manual reconciliation of the bank statement, to avoid having someone else working with the same bank statement simultaneously.

Reversal of bank statement reconciliation

If needed, a reconciliation of a bank statement can be reversed with related option 23='Reverse Reconciliation' in (ABS180). During the reversal, the status is set to 70. When the reversal has finished, the status is set back to 50 on the header and 00 on the lines, the external reconciliation date is removed from the bank statement header and for all involved general ledger transactions the external reconciliation code and date are reset to 0, additional information 039 is removed, and external reconciliation date and bank statement number are removed from the corresponding records in table FGLEDB as well.

Upon reversal, the earlier reconciled transactions are moved from (ABS182) to (ABS185). After the reversal, a new automatic reconciliation can be started from (ABS180), or manual reconciliation can be performed in (ABS185).

Interruption of bank statement reconciliation

During processing of a bank statement, when it is in status 20, 40, 70, or 80, other jobs cannot be started for the same bank statement. If a job is interrupted, for example due to a power failure, and the bank statement is still in status 20, 40, 70, or 80, even if a job is no longer in progress, it is possible to reset the status of the bank statement in 'Program Problem. Solve' (CRS418) using correction ID AB02-'Reset status on bank statement reconciliation – (ABS180)'.

When correction ID AB02 is used in CRS418, these updates will be made:

Reconciliation APIs

In addition to the interactive programs, there are MI transactions that can be used to handle the functionality for automatic bank statement reconciliation. The functionality of the MI transactions corresponds to the functionality of the interactive programs, but there are a few things that should be noted.

If a bank statement is manually entered or updated in (ABS180), a control is made to check that the bank statement number is unique for the year and the reconciliation type. The same control is made for a bank statement that is uploaded through ABS180MI (Bank statement reconciliation header interface), transaction Add, or updated through ABS180MI, transaction Upd. If the parameter 'Partial validation' is set to 1 for these two MI transactions, a new bank statement with the same bank statement number as an existing one for the same year and the same reconciliation type in (ABS180) will be allowed for upload through ABS180MI. In that situation, the bank statement number will still be checked during the validation of the bank statement.

When ABS182MI (Bank statement reconciliation, reconciled transaction interface), transaction Get, is used to display a specific transaction in table FBSREC, either a bank statement line or a general ledger transaction must be selected for display. If a bank statement line is to be displayed, the sequence number must be defined in field BLSN. If a general ledger transaction is to be displayed, the transaction must be defined by entering values in fields YEA4, JRNO, and JSNO. The same applies to ABS185MI (Bank statement reconciliation, not reconciled transaction interface), transactions Get, SelectLine and ReleaseLine.

When a line is manually selected, or released, in (ABS185), the amounts in fields 'Selected amount from bank statement', 'Selected amount from general ledger' and 'Difference' are automatically updated. However, the amounts in these fields are not updated if a line is selected or released through ABS185MI, transactions SelectLine, ReleaseLine or Release, as these MI transactions are intended for use by an external application and not together with the interactive program (ABS185).

Setup

Follow these steps

  1. Upload or create a bank statement in (ABS180) and the bank statement lines in (ABS181). The bank statement header will have status 10 and the bank statement lines will have status 00.

  2. Validate the bank statement with related option 21='Validate' in (ABS180).

  3. If errors are found during the validation, the status of the bank statement header is set to 25. Use related option 14='Display Error Log' in (ABS180) to display the error description in (CMS421). Then correct the errors and restart the validation with related option 21='Validate' in (ABS180).

  4. If no errors are found during the validation, the status of the bank statement header is set to 30. Then automatic reconciliation can be started with related option 22='Automatic Reconciliation' in (ABS180).

  5. If some bank statement lines are not automatically reconciled, the status of the bank statement header is set to 50. In (ABS180), use related option 11 to display the bank statement lines in (ABS181), where the reconciled lines have status 90, while the not reconciled lines have status 00. Use related option 12 in (ABS180) to display the reconciled transactions in (ABS182) and use related option 13 in (ABS180) to display the not reconciled transactions in (ABS185).

  6. Perform manual reconciliation of the not yet reconciled transactions in (ABS185), by selecting the lines that should be reconciled with related option 8='Select line' and then press F20-'Reconcile selected lines'. If a line is selected by mistake, it can be released by using related option 9='Release line' or F21='Release selected lines'.

  7. If needed, a reconciliation of a bank statement can be reversed with related option 23='Reverse Reconciliation' in (ABS180). If only the reconciliation of certain transactions needs to be reversed, then use related option 23='Reverse Reconciliation' in (ABS182) instead.

Outcome

Related topics