5 ARCS Hacks for Transaction Matching Reconciliations
By: Ivan Casarrubias - Sr. Principal Consultant
Published: August 11, 2020
Oracle's EPM Cloud Account Reconciliation (ARCS) is purpose-built to efficiently manage and improve global account reconciliation by automating and addressing security and risk typically associated with the reconciliation process.
ARCS features Transaction Matching as part of the Reconciliation Compliance feature set. With Transaction Matching, companies can automate preparation of high volume, labor intensive reconciliations and seamlessly integrate those results into the tracking features within Reconciliation Compliance.
1. Data Sources
The Transaction Matching design begins with creating Match Types. Match Types determine how transaction data is paired between a source system and a sub system
There are many Point-of-Sale (POS) systems out there with their own file formatting specifications. Companies that use a Datawarehouse, can leverage SQL Queries to extract data into transaction files for Pre-Mapped Transactions Matching. A properly designed SQL Query can extract transaction data from a system to be formatted for direct import into ARCS.
Example Sub System csv file
|Reconciliation ID||Source Type||Source System|
Example Source System csv file
|Reconciliation ID||Source Type|
Modifying the SQL query to include the above three (3) additional columns for sub system, and two (2) additional columns for source system, is all you need to import pre-mapped transactions into ARCS from any database.
2. Importing Transactions
In ARCS, you can import up to 1 million transactions from a flat
file, from your source system, or database.
Assure that the load files are formatted correctly to avoid import errors. There should be no null values on Required Attributes such as Balancing Attributes and attributes used in Match Set Rules
Use Text as a Data Type
A good rule to remember is to make all non-essential attributes Text data types. If the attributes are not used in Match Rules, declaring those attributes as Text data types will minimize the need to troubleshoot import errors.
Import Error Example - Null Dates
Error at line no 5 : Cannot be converted to number NULL. Error processing value for attribute Date_Approved.
You only need the Accounting Date as a Date data type for Recon. If the Accounting Date attribute is Sales_Date, then any extra date attributes can be set to text, otherwise null dates in your import file will cause import failure.
Import Error Example - Unformatted Numbers
Error at line no 2 : Error processing value for attribute Amount_Calc. Number has to be a maximum 18 digit long and cannot contain any non numeric characters including separators such as decimal or thousands.
Only one Amount column is needed as a Number data type, and that is the Balancing Attribute (example Sales_Amount). If your import file contains another amount such as Amount_Calc that contains the same amount value, but has 14 decimal places, Oracle will reject the import. Instead, make the Amount_Calc attribute a text data type, since Sales_Amount is already the balancing attribute used for the reconciliation.
3. Match Set Rules
How Rules Work
Match Set Rules are used to reconcile source and sub transactions by leveraging Automatch. Match rules determine how matches are made. Rules can be configured for tolerance ranges on dates and amounts, and adjustments can be automatic when variances exist.
By naming the Match Set Rules with a standard Naming Convention, we can determine which rules are matching the most transactions in Matching.
In this example, we named the Match Set Rules using the following naming convention: Rule Number, Match Status Code (A = Confirmed), Cardinality (Rule Type), Rule Attributes.
Therefore, we know that 2.A1_1-Date-Invoice-Amount can be understood to be: Rule #2, Confirmed (Automatic) Match, 1-to-1 cardinality, and matched using Sales_Date, InvoiceNumber and Sales_Amount.
4. Easier Auditing
Make visual audits and reviews easier by creating lists in the Matching section of ARCS
By default, Oracle displays columns for Matches and Unmatched Transactions in a random fashion. Click View -> Select Columns and arrange the columns for Source and Sub Systems using a standard order.
Good practice would be to order the columns by accounting date, balancing attribute, and match set rule attributes.
You can Remove unnecessary fields and save the List. Visual Auditing is now much easier to do without all the non-essential attributes in the way, and Amounts are easier to find when a user does not need to scroll across columns to find the Amount column.
5. Complete Automation
A properly designed Transaction Matching workflow can be optimized with Automation. Transaction files can be scheduled to be extracted and sent to a shared SFTP folder. From the folder, EPM Automate can identify which files to import into the appropriate Source and Sub system. After Importing transaction files into ARCS, EPM Automate can execute Automatch, create a Reconciliation Status Report, and notify the ARCS Administrator of process completion. Top Fortune 500 companies are Automating their EPM Cloud designs for a variety of benefits.
Schedule your Free Consultation, to see it in action!
We offer Autonomous Systems for EPM Cloud Applications such as Oracle Planning & Budgeting Cloud Service (PBCS/EPBCS), Financial Close & Consolidations Cloud Service (FCCS), Enterprise Data Management Cloud Services (EDMCS), Account Reconciliation Cloud Service (ARCS), Profitability , Account Reconciliation Cloud Service (ARCS), Profitability & Cost Management (PCM) and more.