Automatic Bank Statement Reconciliation Processing

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 API 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:

• 00 - Not reconciled
• 90 - Reconciled

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

• 10 - New
• 20 - Validation in progress
• 25 - Validated with errors
• 30 - Validated
• 40 - Automatic reconciliation in progress
• 50 - Automatic reconciliation completed
• 70 - Reversal of reconciliation in progress
• 80 - Manual reconciliation in progress
• 90 - Reconciled

Deletion of a bank statement is allowed, if it is in any of these statuses:

• 10 - New
• 25 - Validated with errors
• 30 - Validated

During processing of a job, you cannot delete a bank statement if it is in any of these statuses:

• 20 - Validation in progress
• 40 - Automatic reconciliation in progress
• 70 - Reversal of reconciliation in progress
• 80 - Manual reconciliation in progress

When a bank statement has been reconciled, you cannot delete it unless the reconciliation is reversed first. This applies to these statuses:

• 50 - Automatic reconciliation completed
• 90 - Reconciled

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 below:

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:

• If there are no transactions in table FBSREC, nor in table FBSREM, the status in table FBSREH is reset to 10.
• If there are no transactions in table FBSREC, but in table FBSREM, the transactions in table FBSREM are deleted and the status in table FBSREH is reset to 30.
• If there are transactions both in table FBSREC and in table FBSREM, the reconciled transactions are reversed (moved from table FBSREC to table FBSREM and the status in table FBSREL is reset from 90 to 00). Then the transactions in table FBSREM are deleted and the status in table FBSREH is reset to 30.

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 specifying 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

• 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.
• 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.
• When a line has been selected in (ABS185), a 'Selection ID' is 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). This number series is automatically created, so it cannot be defined, or updated, manually in (CRS165).
• If bank operation codes are to be used for the automatic reconciliation, those codes must be defined in 'Bank Operation. Open' (CRS079). If a transaction is posted with a bank operation code, that code will be stored in table FGLEDB.
• If bank operation codes are used, it is possible to define how the value dates will be set. This setup is made in 'Bank Operation. Define Value Date Method' (GLS010). The value dates are stored in table FGLEDB.
• The bank account for which the automatic reconciliation is performed must be defined in 'Accounting Identity. Open' (CRS630) as a 'Bank account' with the parameter 'Reconciliation' activated. If bank operation codes are to be used for the bank account, the parameter 'Enter bank operation' must also be activated.
• The bank account must be defined in 'Bank Account. Open' (CRS692) as bank account type 01-'Company bank account'. Use the account defined earlier in (CRS630) in the accounting string that will be used for postings created for this bank account ID.
• If accounting dimensions 2 to 7 are used in the automatic reconciliation, accounting identities for those dimensions must be defined in (CRS630) and used when posting transactions on the bank account. If a value always must be entered in a certain accounting dimension when posting transactions on the bank account, that accounting dimension can be set to be mandatory in the rules for account cross reference on (CRS630/G).
• If check numbers are used for automatic reconciliation, payment methods for check payments must be used when entering payment transactions from customers or to suppliers.
• If voucher text is used for automatic reconciliation, make sure that a relevant voucher text is entered when posting bank transactions. The voucher text used for the transaction in the general ledger must be identical to the voucher text stated for the bank statement line to have the transaction automatically reconciled.
  Note: The setup in (ABS960) and (ABS965) are further described in Automatic Bank Statement Reconciliation 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

• The reconciled bank statement lines have status 90 in (ABS181) and all reconciled transactions are displayed in (ABS182).
• The 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 bank statement line concerns a cashed check, the check book in 'Check. Open Checkbook' (APS300) is updated upon reconciliation either from the bank statement in (ABS180), or from the bank statement line in (ABS185). The status of the check in (APS300) is set to 2='Cashed', and the check is also updated with cashed date, and bank statement number. The cashed date is retrieved from the bank statement date in (ABS180).