1. Administering Account Reconciliation
  2. Understanding Data Integration for Oracle Account Reconciliation
  3. Importing Pre-mapped Balances or Transactions
  4. Importing Pre-mapped Transactions for Transaction Matching

Importing Pre-mapped Transactions for Transaction Matching

The first step to reconcile accounts using Transaction Matching involves importing data. You can import two types of data: transactions and balances.

Importing Transactions

Note the following about importing transactions:

  • Transactions are always imported as new transactions because there is no method to update previously imported transactions.
  • You can run only one import transactions job for a Match Type at a time. However, parallel imports can be run into different match types. This also applies to importing using EPM Automate.
  • You can specify that auto match runs at the end of a transaction import. The auto match runs on the imported data based on the rules that have been set up by an administrator.
  • To help preserve data integrity, any error during import will cause the entire import to be rolled back and the job status will be 'failed'. Instead, errors are logged so that users can fix the errors and resubmit.
  • The import file is saved as a compressed zip file in the database and you can download from the Job History screen.

  • A locking mechanism prevents further changes to transactions during a data load. During the data load, the entire match type is locked and a message is displayed to the user alerting them to the temporary lock.

    Another data load on the same match type can still be processed

  • Importing transactions is not allowed if the Accounting Date of one or more transactions in the match set is lower than the Purge Through Date. The import log contains an error message indicating that the import failed. This is applicable to Transaction Matching profiles that are integrated with Reconciliation Compliance.
  • Import of Transaction Matching transactions automatically reopens the reconciliation with no warning confirmation message. See Closing and Locking Periods in Setting Up and Configuring Account Reconciliation.
  • Parallel processing is not supported when loading files into a single match type. This is applicable when loading Transaction Matching transactions using Transaction Matching, Data Management, and EPM Automate.
  • Number attributes for a data source used for amount fields can be up to 15 digits in total and up to 12 digits after precision. Numbers are rounded to 2 decimal places for variance calculations. Numbers with up to total 15 digits are supported without loss of precision. For example, all of these examples are valid:
    • 1234567890123.45
    • 12345678901234.5
    • 123456789012.345
    • 1234.56789012345

File Format

The import file should meet the following requirements:

  • File must be in comma separated value (.csv) format.
  • File should include a header row containing column headings that match the attribute ID in the data source definition.
  • File should include an Account ID column to determine which reconciliation the transaction is assigned to.
  • Transactions for more than one reconciliation may be imported within the same file, so long as they use the same Match Type.
  • An example of a load file:

    Account ID,Acctg Date,Amount,Invoice Number

    100-1003,20-APR-2019,1100.00,145292

    100-1003,19-APR-2019,461.68,145293

    100-1003,20-APR-2019,1550.00,145294

  • File should include the required Date column that represents the Accounting Date that determines the accounting period that the transaction is reflected in. This date assigned to each transaction is used to perform all period-end calculations.
  • File should include the required Amount column that represents the transaction amount from the source or sub-system.This "Balancing Amount" assigned to each transaction is used to perform all period-end calculations.
  • For more details on format for date attributes in a data source, see Define Data Sources
  • The backslash "\" is a special character in file imports. The system reads the next character after a backslash "as is". In order for data containing a backslash to load properly from the .csv file, you must use a double backslash \\.

Importing Transactions

To import transactions:

  1. From Home, click Application, then select Jobs.
  2. Select Transaction Matching.
    screenshot of Transaction Matching Jobs tab
  3. On Jobs , select Actions, then Import Transactions.
    screenshot of import transactions dialog

  4. Select the Match Type for which transactions should be imported.

    The system uses the selected match type as the default selection for subsequent Import Transactions or Auto Match jobs for the duration of the current session. To run Auto Match for a different match type, change the Match Type selection in the Run Auto Match dialog.

  5. Specify whether or not to run Auto Match after the import is completed. There is a locking mechanism on the reconciliation that prevents further changes during auto match. A message displays to the user about the temporary lock.

    Note:

    If you need to run Auto Match independent of the import, use Application, then Jobs , then Transaction Matching, and select Run Auto Match from the Actions menu.
  6. In Date Format, select the format of the date fields in the transactions import file. The default is DD/MM/YYYY. Other supported date formats are MM/dd/yyyy, dd/MM/yyyy, MM-dd-yyyy, d-M-yyyy, and MMM d.yyyy.
  7. For each data source, browse to the file containing the transactions to import and click Submit.

  8. After the job is submitted, you can see the status on the Job History tab.

    Click Refresh to check that the job succeeded.

If an import job is in progress, any scheduled maintenance jobs will begin after the import job completes.

Note:

To understand the impact of loading transactions into locked or closed periods, see Closing and Locking Periods.

Watch Importing Data Video

Click this link to watch the video:

Importing Data video link