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 fully update
previously imported transactions.
However, the REST API can be used to update an unmatched transaction, if required. See Update Unmatched Transactions in REST APIs.
-
To maintain data integrity during the transaction load, the match type is locked while the Import Transactions job is running. A message is displayed to the user alerting them to the temporary lock.
Additional import transaction jobs can also be run in parallel for the same match type, if no other jobs (such as Auto Match) are running for the same match type.
- You can specify that Auto Match runs immediately at the end of a transaction import. However, if multiple Import Transactions jobs are intended to run in parallel for the same match type, it's recommended that the Auto Match is run separately, after all Import Transaction jobs for that match type are completed, since Auto Match requires a lock on the match type while running.
- Any error during import will cause the entire import to be rolled back and the job status will be 'failed'. Errors are logged so that users can fix the errors and resubmit.
- Power users can only import transactions into profiles which fall under their security filter.
- The import file is saved as a compressed zip file in the database and you can download it from the Job History screen.
- Importing transactions is not allowed if the Accounting Date of one or more transactions is earlier 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, if the period is not locked and there are one or more Accounting Dates in the file that are less than or equal to the Closed Through Date. See Closing and Locking Periods in Setting Up and Configuring Account Reconciliation.
- 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:
- From Home, click Application, then select Jobs.
- Select Transaction Matching.
- Click Actions and then select Import
Transactions.
-
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.
- 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. - 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 areMM/dd/yyyy, dd/MM/yyyy, MM-dd-yyyy, d-M-yyyy
, andMMM d.yyyy
. - For each data source, browse to the file containing the transactions to import and click Submit.
-
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:
Database sequences are used to assign transaction IDs. In an Oracle Real Application Clusters environment, sequences are cached on each node to improve performance. Therefore, transaction IDs may not always be in the same chronological order as the import jobs. So, a transaction that is imported later could have a lower transaction ID than one that was imported earlier.Note:
To understand the impact of loading transactions into locked or closed periods, see Closing and Locking Periods.