Written By: Ivan Casarrubias - Sr Principal Consultant
Publication Date: 08/11/2020
Oracle's EPM Cloud Account Reconciliation (also known as 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.
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.
|Reconciliation ID||Source Type||SourceSystem|
|Reconciliation ID||Source Type|
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
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
Import Error Example - Unformatted Numbers
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.
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.
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.
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.
Get Free Demo Schedule your Free Automation Demo, and see it in action!
Witness 12 systems reconcile almost 1,000,000 transactions in minutes from 12 different files. Process Completion Notifications and Reconciliation Reports will be generated as part of the demo. No human interaction is used in the entire process!
We offer Autonomous Systems for EPM Cloud Applications such as Oracle Planning & Budgeting Cloud Service (PBCS)/EPBCS, Financial Close & Consolidations Cloud Service (FCCS), Hyperion Financial Management (HFM), Account Reconciliation Cloud Service (ARCS), Profitability & Cost Management (PCM) and more.
Ivan Casarrubias is a Sr. Principal Consultant for Paradigm SES.
Ivan creates Autonomous Systems for Enterprise applications and has experience with Machine Learning and Predictive Analytics.
Paradigm SES provides EPM and BI solutions to companies across the globe. Our team of experts have over 30 years of hands-on functional experience. We deliver process improvements, data management, data visualization and reports, and greater insights for a variety of Fortune 500 companies.We are passionate about creating better work environments through efficiency and imaginative solutions.