Importing BAI Balances to Account Reconciliation
Maximizing the Value of the Unified Oracle Cloud EPM Platform
Part 3: Streamlining Your Financial Close
Managing cash reconciliations efficiently requires accurate and timely data from bank statements. One of the most effective ways to bring in bank balance data is by using BAI format files, which provide standardized banking information. This document outlines the process of integrating a BAI balances file with Oracle Account Reconciliation through Data Integration, ensuring a seamless reconciliation workflow.
Note: The most recent format of the file is the BAI2 format. Whenever BAI is referenced in this document, it is referencing the BAI2 format.
The steps in this post can be used to also set up Swift MT940 and CAMT.053 bank file balances. See the Create the BAI Balances Application section for details.
Benefits
Automated Cash Balance Updates
Ensures scheduled updates of bank balances in Account Reconciliation, minimizing manual interventions.
Flexible Data Processing
Handles high-volume data efficiently, allowing reconciliation teams to work with large sets of banking information without delays.
Bank-Agnostic Compatibility
Works across multiple banks without requiring file format modifications, making it easy to standardize reconciliation processes across different institutions.
Improved Fraud Detection
Enables timely identification of balance discrepancies by ensuring that bank balances are always in sync with the accounting system.
Seamless Integration
Connects through a pre-defined adapter to Account Reconciliation, ensuring a structured flow of bank data.
Privacy and Security
Avoids exposing sensitive bank statement details directly in Account Reconciliation, reducing the risk of unauthorized access or visibility of confidential information.
Key Considerations
Cost
Some banks charge fees for generating and sending BAI files. These costs can vary based on factors such as file frequency, account volume, and delivery method.
Frequency of File Generation
The availability of BAI files depends on the bank’s processing schedule. Some banks provide daily files, while others may generate them multiple times per day or only at the end of the month.
File Delivery Methods
BAI files can be delivered through various methods, including SFTP, direct bank portals, or third-party treasury management systems. Organizations must ensure secure and reliable file transmission.
Data Consistency
While BAI is a standardized format, different banks may have slight variations in implementation. It’s important to validate the structure and content of each bank’s BAI file to ensure compatibility with reconciliation processes.
About BAI Balance Files
BAI (Bank Administration Institute) files follow a standardized structure that allows companies to receive account balance and detailed transaction data from banks in a machine-readable format. BAI files are used to automate bank reconciliations, track cash flow, and integrate financial data into various accounting and treasury management systems.
A BAI file consists of a series of records, each identified by specific codes that indicate the type of information being reported. These records include opening and closing balances, summary transactions, and detailed transaction descriptions. Because BAI files follow a structured format, they can be used to develop automated processes to import and analyze banking data within various tools like Account Reconciliation. By streamlining cash reporting and reconciliation, BAI files help businesses maintain better visibility into their financial transactions and ensure accurate accounting.
BAI File Structure
The BAI file format consists of a series of records, each identified by a unique record code that defines its purpose. The main components of a BAI file format include a file header, group header(s), account records, transaction details, and a file trailer. This structure ensures that BAI file formats are bank-agnostic and consistent across all banking platforms.
The BAI file contains the following codes:
Information Required from the BIP Report
To configure the integration, you need the following details:
- BIP Report Name with Full Path – e.g., /Shared Folders/Custom Reports/AP_Trial_Balance.xdo
- ESS Job Path – e.g., /oracle/apps/ess/custom/AP_TB_ESS_JOB
- ESS Job Name – e.g., AP_TB_ESS_JOB
- Parameter Listing and Data Table Names – e.g., LEDGER_ID, GL_ACCOUNT, BOOK
This information can be found in the Report Properties and by reviewing the report in the Job History in Oracle ERP Cloud.
BAI files are typically used to report both detailed transactions and account balances, but it is possible to generate a BAI file for closing balances only. The Transaction Detail (16) records would be omitted, reducing file size and complexity while still ensuring that financial teams receive accurate end-of-day balance data.
An example of a BAI file is shown below:
Source: HSBC BAI2 Statement
Account Reconciliation Application Setup
Create the BAI Balances Bank File Data Source Application
Create the application by navigating to Application > Data Exchange > Actions > Applications. Click the + icon to add a new application.
The application category is Data Source, the Type is Bank File, and the Application is BAI Format Bank File Balances.
Note: The application name will default to BAI Format Bank File Balances.
The application can be used for multiple banks or files because the format is the same across all banks. If it is necessary to distinguish the applications, enter a Prefix.
Click OK to create the application.
Note: The Application selection also contains options to set up Swift MT940 Format Bank File Balances and CAMT.053 Format Bank File Balances. The following steps are the same when setting up these files.
Viewing the Application Options
Application Options are automatically configured with default values that do not require modifications. The Options screen includes three tabs: Dimensions, Options, and Set Defaults.
The Dimensions tab is automatically configured based on the standard BAI balances format. No changes are required.
The Options tab defines the Source File definition. The file source should not be defined at the application level but rather at the integration level to allow for flexibility in integration configuration.
The Set Defaults tab is only available for update at the integration level, meaning changes cannot be made at the application level.
Create a Data Integration
The next step is to create the data integration to load data to Account Reconciliation. To create the integration, navigate to Application > Data Exchange and click the + icon to add a new integration.
Enter a Name and enter or select a Location. If a new location is created, the source and target must be defined. When the integration is saved, the system creates the location automatically. If an existing location is selected, the system populates the source and target automatically, and the details cannot be changed.
Next, choose the appropriate Category (currency bucket) for the integration. The selected category determines which currency bucket the data will be loaded into for the reconciliations.
After entering or selecting all required options, click Save and Continue to proceed.
Map Dimensions
In the next screen, the file type, delimiter, and source system (BAI file) columns are mapped or translated to their corresponding values in the target system (Account Reconciliation). Proper mapping ensures that data is correctly categorized and available for reconciliation purposes.
Map Dimensions
In the next screen, the file type, delimiter, and source system (BIP Report) columns are mapped or translated to their corresponding values in the target system (Account Reconciliation). Proper mapping ensures that data is correctly categorized and available for reconciliation purposes.
The Import Format name defaults to the name of the integration but can be modified to align with company naming conventions or specific requirements. Import Formats can also be reused across multiple integrations when the same definition is needed for different data loads.
Type determines what kind of data will be in the file. The options most relevant to this integration are:
- Delimited – Numeric Data – If the file contains only account balances, this option is preferable to prevent potential issues with non-numeric characters.
- Delimited – All Data Type – if the file includes descriptive fields, choose this type to ensure text is accurately imported and not misinterpreted or truncated.
Delimiter defines the character used for delimiting columns in the output file. Multiple options exist, but the BIP report output file is set to CSV as the default so Comma should be used.
For each Account Reconciliation dimension, use the Source Dimension dropdown to select the corresponding report column. This step aligns data between the sources, ensuring accurate transformation and processing.
Additionally, source or target expressions can be applied to manipulate data as needed – such as adding a constant for Source Type. To add a source or target expression, click the gear icon next to the dimension row and select the desired expression.
When the integration is initially created, the mapping table includes only the profile segments, source system, and a single profile row. Reconciliations based on BAI reports normally map a bank account number to a profile so additional profile rows are not needed. However, an additional row is required for currency. To add the additional currency row, click the gear icon and select Add Currency.
View or Update Options
The Options screen in the data integration setup consists of two tabs: Filters and Options.
- Filters contain the Source File definition. Click the folder icon to add a file to the File Browser for load.
Note: If a file is not selected during the integration setup, it can be selected during the load process.
- Options define additional parameters that control how the data is processed and transferred. In the example integration, the Category (currency bucket) and Period Mapping Type can be changed.
Map Members
Member mappings were covered in detail in Part 2 of this blog series. For a comprehensive explanation of the different mapping types and guidance on when to use them, please refer to that post.
BAI file data is mapped into Account Reconciliation using the bank account numbers to map to Account, Company, and Profile. In the following examples, bank account number 5580024780 is mapped to the profile 203-11103.
Account Map
Company Map
Profile Map
Source Type Map
In addition to mapping the bank account number to the Account, Company, and Profile, an additional * to * Like mapping is needed for the Source Type. The Source Type is defined in the Import Format as sub-system, and the value should be passed as-is to Account Reconciliation.
Format Setup
Reconciliations involving subsystems are typically set up using the Balance Comparison method, which enables a direct comparison of the balances in the Bank against the GL.
It is best practice to create a specific format tailored to bank reconciliations using the BAI file to help end users understand the source of the balances.
Key Customizations to Include in the Format:
- Update Source System and Subsystem balance labels to clearly indicate the source of the balances on the Summary page of the reconciliations.
- Create auto reconciliation rules that automatically close the reconciliation process when the General Ledger and Bank balances match.
- Adjust labels for Adjustments to Source System and Adjustments to Subsystem to reflect the specific application or subsystem the adjustments pertain to.
If auto-reconciliation rules are applied and the reconciliation closes but the balances change afterward, the reconciliation will automatically reopen. Notifications will be sent to the end users assigned to the reconciliation, alerting them of the changes and prompting them to take appropriate action.
Summary
Integrating a BAI balances file with Account Reconciliation helps automate cash reconciliations and improve financial accuracy. By following these steps, organizations can streamline their reconciliation process and reduce manual intervention.
The steps in this guide can be followed to set up integrations with files using Swift MT940 and CAMT.053 bank file balances.
FAQs
1. What is the benefit of using BAI files for Account Reconciliation?
BAI files provide a standardized, bank-agnostic format for importing balance and transaction data, enabling automated cash reconciliations, improving accuracy, and reducing manual data handling.
2. Can BAI integrations support multiple banks and file formats?
Yes, BAI files are designed to be bank-agnostic, allowing organizations to use a single integration setup across multiple banks. Similar processes can also support formats like MT940 and CAMT.053
3. How does data mapping impact the success of BAI integrations?
Accurate dimension and member mapping ensures that bank data is correctly aligned with Account Reconciliation profiles, accounts, and entities. Proper mapping is critical for maintaining data integrity and enabling automated reconciliation processes.
4. What are the key considerations when implementing BAI file integrations?
Organizations should consider factors such as bank file delivery methods, file generation frequency, data consistency across banks, and potential costs associated with generating BAI files to ensure a reliable and secure integration process.
Watch the AI World Series on YouTube!