1. Administering Data Management for Oracle Enterprise Performance Management Cloud
  2. Integrating Data
  3. Integrating Account Reconciliation Data
  4. Integrating BAI, SWIFT MT940 or CAMT.053 Format Bank File Transactions and Balances
  5. Integrating BAI Format Bank File, SWIFT MT940, or CAMT.053 Balances

Integrating BAI Format Bank File, SWIFT MT940, or CAMT.053 Balances

When loading Bank file data, you create a data source associated with a Bank file source system. Data Management converts the BAI, SWIFT MT940, or CAMT.053 file formats to a CSV format.

The source application for a BAI Format Bank File Balance has the following pre-defined constant columns and headers:

  • Closing Balance
  • Currency
  • Transaction Type
  • Currency
  • Statement Date
  • Account

The source application for a Swift MT940 Format Bank File Balances has the following pre-defined constant columns and headers:

  • Closing Balance
  • Currency
  • Transaction Type
  • Currency
  • Statement Date
  • Account

The source application for a CAMT.053 bank statement file has the following pre-defined constant columns and headers:

  • Account
  • Closing Balance
  • Currency
  • As Of Date

Note:

BAI codes 100-399 are for bank credits (positive numbers) and 400-699 are for bank debits (negative numbers).

For Bank specific BAI codes which are greater than 699, Data Management will treat them as bank credits (positive numbers) by default. If you need any specific code in this range to be treated as bank debit (negative number), then you can use SQL Mapping (see Creating Mapping Scripts) to update the Amount as a negative number as in the following example. 


AMOUNTX=
CASE
    WHEN UD7 = '868' THEN AMOUNT*-1
     ELSE AMOUNT
END

To add a BAI Format Bank File, SWIFT MT940, or CAMT.053 Format Bank File Balances source system:

  1. Save the BAI Format Bank File, SWIFT MT940, or CAMT.053 Format Bank File Balances files as CSV format file.
  2. Upload the files using the file browser when registering the target application.

    The following shows a BAI Format Bank File Balances file:

    BAI Format Bank File Balances file:

    The following shows a SWIFT MT940 Format Bank File Balances file:

    Image shows SWIFT MT940 Format Bank File Balances file.

  3. From the Home age, click Navigator icon (Navigator icon) and then from the Integration category, select Data Management.
  4. Select the Setup tab, and then under Register, select Target Application.
  5. In Target Application, in the summary grid, click Add, and then select Data Source.
  6. From Source System page, select Bank file.
  7. From Application Name, select an application name from the list of values.

    Available types of applications include:

    • BAI Format Bank File Balances
    • SWIFT MT940 Format Bank File Balances
    • CAMT.053

    Image shows the Source System page.

    For a BAI Format Bank File Balances file, the available application names are a combination of match types and a data source name on that match type in Transaction Matching. For example, in Transaction Matching, the match type INTERCO has two data sources AP and AR. This results in two target application names in the available list; INTERCO:AP and INTERCO:AR.

    Note:

    The Data Management connection to the BAI source file fails under the following circumstances:

    • The match type is changed in Transaction Matching.
    • The data source ID changes.
    • The data source attribute ID changes, or is added and removed.

    In this case, you need to recreate the application (including the entire target application, import format, location, mapping and data load rule in Data Management.

    For a SWIFT MT940 file, select SWIFT MT940 Format Bank File Balances.

    For a CAMT.053 file, select CAMT.053 Format Bank File Balances.

  8. In Prefix, specify a prefix to make the source system name unique.

    Use a prefix when the source system name you want to add is based on an existing source system name. The prefix is joined to the existing name. For example, if you want to name a Bank file application the same name as the existing one, you might assign your initials as the prefix.

  9. Click OK.
  10. To add or modify dimensions in the BAI Format Bank File Balances file source system, select the Dimension Details tab.

    The dimension details for a BAI Format Bank File Balances file application are shown below:

    Image shows the Target Application page.

    The dimension details for a SWIFT MT940 Format Bank File Balances application are shown below:

    Image shows the Target Application screen.

    The dimension details for a CAMT.053 Format Bank File Balances application are shown below:

    Image show dimension details for CAMT.053 format bank file balances.

  11. Select the Target Dimension Class or click Image shows Search button to select the Target Dimension Class for each dimension that is not defined in the application.

    The dimension class is a property that is defined by the dimension type.

  12. Select the Setup tab, and then under Integration Setup, select Import Format.
  13. Set up the integration mapping between BAI Format Bank File Balances source system and the Account Reconciliation target application by building an import format.

    See Working with Import Formats.

    An example of the dimension detail for a BAI Format Bank File Balances application is shown below:

    Image shows import format for BAI Format Bank Balances

    An example of the import format for a SWIFT MT940 Format Bank File Balances application is shown below:

    Image shows Import Format for SWIFT MT940 Format Bank Balances

    An example of the import format for a CAMT.053 Format Bank File Balances application is shown below:

    Image shows the import format for a CAMT.053 format bank balances.

  14. Select the Setup tab, and then under Integration Setup, select Location.
  15. Define the location used to associate the import format.
  16. Select the Workflow tab, and then under Data Load, select Data Load Mapping.
  17. Map the account numbers in the file to the appropriate Reconciliation names.
    Image shows the Data Load Mapping page.

    Note:

    All transaction matching files require the Reconciliation Id dimension to be mapped to the corresponding Transaction Matching Profile.

  18. Map the source type dimension Source type * to the hard coded "source system" or "source sub-ledger" Target value.
    Image shows the Data Load Rule page.
  19. Select the Workflow tab, and then under Data Load, select Data Load Rule.
  20. Create a data load rule for the location and specify the period and category.

    the data rule "BAIRule" is created and the BAI Format Bank Balances file format is imported to the location "Loc_BAIFormat." The period is specified as "feb-20" and category is specified as "Functional."

    Image shows the Data Load Rule page.

  21. Go to Account Reconciliation home page.
  22. Click Application, then Configuration, and then Data Load.
  23. Create two rules for both import format executions as shown below.
    Image shows the Data Load page.
  24. Execute the data load rule by selecting Application and then Period.
  25. Click Image shows an ellipses. to go to the action menu and then click Import Data.
    Image shows the Periods page.
  26. Click +, then from New Data Load Execution, select Use saved data load, and select the data load created in the previous step.
    Image shows the New Data Load Execution page.
  27. Click OK to execute the data load rule.

    Balances are loaded to the reconciliation defined in the Data Load Mapping for the Profile dimension as shown below. Bank balances are typically loaded to the Subsystem, but can also be loaded to the Source System if needed.

    Image shows the Balance Summary.