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.
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
Systems Design - Consider internal and external factors and systems when designing your Match Types
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
SourceSystem
1111-DoorDash-Bank-Partners
sub system
DoorDash
Example Source System csv file
Reconciliation ID
Source Type
1111-DoorDash-Bank-Partners
source system
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
Minimize Errors - Data Normalization is an important concept for Database professionals, but in OLAP and Multidimensional Databases, declaring certain attributes as Int or Date can cause import errors.
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.
Naming Conventions
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.
Save Time - Reconcile millions of transactions in under 5 minutes, while increasing frequency at no additional cost
Increase Profits - Multiple Match Types can be automated simultanously, reducing operating expenses dramatically.
Less Maintenance - Import data files, automate reconciliations, and maintain audit trails with minimal human intervention.
FREE DEMO
Ready to Try Automation?
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.
About the Author
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.
About Paradigm SES
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.
(1).jpg){kind=spacer}
Ivan Casarrubias is a Sr. Principal Consultant for Paradigm SES.