Overview

Modern bank reconciliation includes major changes to Reconciliation matching rules.
Mark new transaction rules have been replaced by new actions as described in more detail here .

Currently the standard Reconciliation matching rules data entity doesn’t support Modern bank reconciliation new actions.
Interim option for migration is to either manually create/change the Mark new transaction rules to the new actions, or use Electronic reporting to create these new Reconciliation matching rules.
The following sections will describe the Electronic reporting import option that includes Finance Utilities fields and intercompany offset support.

Note: After enabling the Modern bank reconciliation feature, running the standard Cash and bank management > Setup > Advanced bank reconciliation setup > Advanced bank reconciliation data upgrade will DELETE all remaining Mark new transaction reconciliation matching rules. It is thus important to ensure all required rules have been migrated before running this upgrade.

Note: The Electronic reporting import option is only provided as interim solution and clients would need to map any additional fields including financial dimensions.
Validation hasn’t been built into the import, please ensure the data is correct.

Tables and Configurations

Tables

Modern bank reconciliation rules are stored in the following tables:

1. BankReconciliationMatchRule - Shared across legal entities
2. BankReconciliationMatchRuleLine - Shared across legal entities
3. BankReconciliationMatchRule_PostingInfo - Legal entity specific offset details

Electronic reporting configurations

Electronic reporting consists out of the following configurations:

1. Bank reconciliation match rule model - related to BankReconciliationMatchRule table
2. Bank reconciliation match rule line model - related to BankReconciliationMatchRuleLine table
3. Bank reconciliation match rule posting Info model - related to BankReconciliationMatchRule_PostingInfo table

Fields per table / configuration

File 1 - Table : Bank Reconciliation match rule (BankReconciliationMatchRule)

1. Rule Id
2. Name
3. IsActive
4. MatchActionType
5. Search Matching Entry
6. Matching Type
7. Require Manual Matching

File 2 - Table : Bank reconciliation match rule line (BankReconciliationMatchRuleLine)
Create a line for each line in the applicable step/s (i.e. could be multiple lines for the rule)

1. Rule Id
2. Compare Field String
3. Compare Field ID - see table systablefieldidview for specific Table id
4. Field ID - see table systablefieldidview for specific Table id
5. Line Num
6. Line Type
7. Operator
8. Source Table ID - see table systableidview
9. Value
10. Offset Account Pattern

File 2 Field reference:

File 3 - Table : Bank reconciliation match rule posting Info (BankReconciliationMatchRule_PostingInfo)

1. Rule ID
2. Legal Entity
3. Bank Transaction Type
4. Transaction Text
5. Offset Account Type
6. Offset Company
7. Offset Ledger Dimension
8. Customer Account
9. Vendor Account
10. Automatic Customer Account Matching
11. Account date type
12. Method of payment
13. Sales Tax Group
14. Item Sales Tax Group
15. Prepayment Journal Voucher
16. Posting Profile
17. Post

Reconcilition matching rule Actions

Generate voucher

Action Generate voucher is used for offsets to ledger and bank.

• File 1 - Bank Reconciliation match rule
• File 2 - Bank reconciliation match rule line (Step 1)
• File 3 - Bank reconciliation match rule posting Info (Step 2)

Generate vendor payment

Action Generate vendor payment is used for offsets to vendors.

• File 1 - Bank Reconciliation match rule
• File 2 - Bank reconciliation match rule line (Step 1)
• File 3 - Bank reconciliation match rule posting Info (Step 2)

Generate customer payment

Action Generate customer payment is used for offsets to customers.

• File 1 - Bank Reconciliation match rule
• File 2 - Bank reconciliation match rule line (Step 1 and 2)
• File 3 - Bank reconciliation match rule posting Info (Step 3)

Settle customer invoice

Action Settle customer invoice is used for offsets to customers.

• File 1 - Bank Reconciliation match rule
• File 2 - Bank reconciliation match rule line (Step 1 and 2)
• File 3 - Bank reconciliation match rule posting Info (Step 3)

Import

Import process

After creating the three csv files (without headers), the files can be imported via the Run option on the mapping configuration.

Examples

Example import files

• File 1 - Bank Reconciliation match rule
• File 2 - Bank reconciliation match rule line
• File 3 - Bank reconciliation match rule posting Info

Examples with table headings

Excel file with headings and examples for each action: Example file