1. Using Payables Invoice to Pay
  2. How You Process Electronic Bank Statements

How You Process Electronic Bank Statements

The electronic bank statement process transfers bank statements and imports them into Oracle Fusion Cash Management.

The following statement file formats are supported:

  • BAI2

  • SWIFT MT940

  • EDIFACT FINSTA

  • ISO20022 CAMT052 V1 - camt.052.001.01

  • ISO20022 CAMT053 V1 - camt.053.001.01

  • ISO20022 CAMT053 V2 - camt.053.001.02

  • ISO20022 CAMT053 V3 - camt.053.001.03

Note:

The import process supports bank files with the following file extensions: *.txt, *.dat, *.csv, *.xml, and *.ack.

The electronic bank statement process consists of the following three phases:

  1. Retrieve phase: Retrieves the electronic bank statement file or stream from external sources and stores it in the database. The external sources can be a file stored on a remote computer or a file stored on the local computer.

  2. Load phase: Processes the retrieved electronic bank statement and populates the bank statement interface tables, also known as the staging area.

  3. Import phase: Processes the loaded bank statement data from the staging area against functional validations before populating the data into the bank statements tables. The parsing rules are executed during this phase.

If the process fails with import errors, correct the reported errors. Rerun just the import phase from the Processing Warnings and Errors table of the Bank Statements and Reconciliation Overview page. However, if there are any errors during the load phase, purge the error data and resubmit the program.

The following prerequisites for Oracle Fusion Cash Management and Oracle Fusion Payments are required to process electronic bank statements.

Cash Management

Set up the following entities in Cash Management:

  • Bank Account

  • Balance Codes: The ISO 20022 balance codes for the opening and closing booked and available balances are provided and additional codes can be defined using the Balance Code lookup (CE_INTERNAL_BALANCE_CODES).

  • Transaction Codes

  • Parsing Rules: Parsing Rules setup is optional but relevant to Bank Statement Reconciliation.

Payments

Set up the following entities in Payments:

  • Code Map Group: Uses code map groups for mapping the balance codes and transaction codes that are reported on the external data file to the ones that are defined internally in the application. Each code map group is assigned to a format. Two code map groups mapping the BAI and EDIFACT opening and closing booked balance codes to the internal balance codes are provided. You can create transaction code mapping (CE_TRX_CODE ) to determine the flow indicator for configurable BAI2 transaction codes .SWIFT940 doesn't require a balance code mapping because it's position-based but you can create a code map group to map the transaction codes to the internally-defined transaction codes. The delivered code map groups provide only basic mappings. You can either modify or create code map groups according to your requirements.

  • Format: Cash Management delivers a single format for each of the bank statement formats supported. You can add additional formats.

You can load and import bank statements in Oracle Fusion Cash Management by any of the following methods:

  • Automatic Bank Statement File Import

  • Manual Bank Statement File Import through Oracle Web Center

Automatic Bank Statement File Import

Use the payment transmission configuration feature to automate the bank statement file import process. The process uses the payments transmission feature to connect to bank and download the bank statement file for subsequent processing and loading into the application.

Perform the following steps to set up automatic bank statement file import:

  1. Set up Payment System: Represents the financial institute or bank in Oracle Fusion Payments. For customers who access Oracle Fusion Applications through Oracle Public Cloud and other implementations, navigate to the Manage Payments System page from Setup and Maintenance to create a payment system.

  2. Set up Transmission Configuration: Contains the connectivity details for the financial institute or bank. This connectivity information is provided by the bank. The following protocols are supported for connecting and retrieving a file from bank server:

    • Secure File Transfer Retrieval Protocol for Static File Names (SFTP_GET): The bank provides the SFTP server details including SFTP server host/ip, port, credential details. The SFTP_GET protocol currently supports both username/password and ssh key file based authentication.

    • Http(s) GET Request: The bank provides the SSL enabled destination URL for connecting and retrieving the statement files. This URL can have authentication enabled. The HTTPS_GET protocol supports https connection but doesn't support the exchange of any dynamic tokens.

  3. Schedule Process Electronic Bank Statement Request: The process performs the following tasks:

    • Connects to the bank and retrieves the banks statement file

    • Processes and loads the bank statement file into the application

Manual Bank Statement File Import through Oracle Web Center

Use the document repository of Oracle Web Center Content Management to manually load and import bank statement files. The Web Center document repository is also available for customers who access Oracle Fusion Applications through other implementations. The account, fin/cashManagement/import, is created on the Web Center for the users of Oracle Fusion Cash Management.

Use the document repository of Oracle Web Center Content Management to manually load and import bank statement files. The Web Center document repository is also available for customers who access Oracle Fusion Applications through other implementations. The account, fin/cashManagement/import, is created on the Web Center for the users of Oracle Fusion Cash Management.

Example

The following example describes how to manually load and import an electronic bank statement.

  1. Obtain a bank statement file: bai2.txt, from the bank.

  2. Compress the file into a .zip file called bai2.zip

  3. Using the File Import and Export functionality, transfer the .zip file to the Web Center document repository and place it in the account: fin/cashManagement/import.

    Note:

    For detailed information on the import and export process, see the hyperlink at the end of this topic for: Files for Import and Export: Explained.

  4. Run the Load and Import Bank Statement process. This process has the following parameters:

    • Format: Select the bank statement file format. For example, BAI2 format.

    • Data File: Select the .zip file from the WebCenter document repository. For example, bai2.zip from fin$/cashManagement$/import$ account.

    • Intraday: Select this parameter if you're importing an intraday statement. Intraday statement type can either be Cumulative or Incremental.

    • Submit Autoreconciliation: Use this parameter to submit the Autoreconciliation process immediately after the Load and Import Bank Statement process completes successfully. This option isn't available if Intraday is selected.

  5. Check for any processing errors from the Bank Statements and Reconciliation Overview page. If the file is successfully imported, you can review it from the Manage Bank Statements page.

Bank Statement Validation

Prior Day Bank Statements:

  1. The Statement ID should be unique in a bank account within a calendar year.

  2. Multiple statements are allowed within the date range.

Intraday Bank Statements:

  1. The Statement ID should be unique in a bank account within a calendar year.

  2. Only one statement is allowed per day.

Intraday Bank Statements: Cumulative

  • The application deletes the existing intraday bank statement and imports the cumulative statement.

Intraday Bank Statements: Incremental

  • The application checks if there is a manual intraday statement within the same date range. If yes, the incremental statement isn't imported and the process is completed with a status of :Import Error.

  • The application checks if there is an imported intraday statement within the same date range. If found, the statement lines from the incremental intraday bank statement is appended to the existing intraday bank statement. If not found, a new intraday statement is created.

Related Topics