5 ARCS Hacks for Transaction Matching Reconciliations

ARCS Overview

Oracle's EPM Cloud Account Reconciliation (ARCS) is purpose-built to efficiently manage and improve global account reconciliation by automating the process and addressing the security and risk typically associated with it.

Account Reconciliation offers two complementary features: Reconciliation Compliance, which manages the reconciliation process and tracks balance explanations, and Transaction Matching, which automates the preparation of high volume, labor intensive reconciliations. Transaction Matching is a perfect complement to the Reconciliation Compliance feature set: its period-end results integrate seamlessly with the tracking features in Reconciliation Compliance. (Transaction Matching is available with the Oracle Enterprise Performance Management Enterprise Cloud Service.)

1. Data Sources

The Transaction Matching design begins with creating Match Types. A Match Type defines the structure of the data to be matched and the rules used to match it. Each Match Type contains one or more data sources, for example a source system and a sub system, along with the match rules applied between them.

There are many Point-of-Sale (POS) systems out there, each with its own file formatting specifications. Companies that use a data warehouse can leverage SQL queries to extract data into transaction files for pre-mapped transaction matching. A properly designed SQL query can pull transaction data from a system and format it for direct import into ARCS, with no manual mapping step.

When you import pre-mapped transactions for Transaction Matching, the file needs a header row whose column names match the attribute IDs in your data source definition, plus three required pieces of information: an Account ID (which reconciliation the transaction belongs to), an Accounting Date (the date that drives the accounting period), and an Amount (the Balancing Attribute used for all period-end calculations).

Example pre-mapped transaction file (sub system: DoorDash)

Example pre-mapped transaction file (source system: Bank)

Note that whether a file loads into the source system or the sub system is decided by the data source you import it into when you run the load; it is not a column inside the file. So the hack is simply to shape your SQL output to these columns for each data source. Once the query returns the Account ID, Accounting Date, Balancing Amount, and your matching attributes, the extract imports straight into ARCS as pre-mapped transactions from any database.

2. Importing Transactions

Transaction Matching is built for high volume, so you can load large transaction files from a flat file, directly from your source system, or from a database (for example, through Data Integration).

Make sure the load files are formatted correctly to avoid import errors. There should be no null values on required attributes such as Balancing Attributes and any attributes used in match rules.

Use Text as a Data Type

A good rule to remember is to make all non-essential attributes Text data types. If an attribute is not used in match rules, declaring it as Text will minimize the need to troubleshoot import errors.

Import Error Example: Null Dates

Error at line no 5: Error processing value for attribute Date_Approved. Value cannot be converted to a date: NULL.

You only need the Accounting Date as a Date data type for the reconciliation. If your Accounting Date attribute is Sales_Date, then any extra date attributes can be set to Text; otherwise null values in those date columns will cause the import to fail.

Import Error Example: Over-precise or Unformatted Numbers

Error at line no 2: Error processing value for attribute Amount_Calc. The value exceeds the allowed length or precision, or contains characters that cannot be converted to a number.

Oracle supports amount (Number) attributes of up to 15 digits in total, with up to 12 digits after the decimal. Only one amount needs to be a Number data type, and that is the Balancing Attribute (for example, Sales_Amount). If your file also carries a second amount such as Amount_Calc that holds the same value but with 14 decimal places, it exceeds the 12-digit precision limit and the import is rejected. Instead, make Amount_Calc a Text data type, since Sales_Amount is already the Balancing Attribute used for the reconciliation.

3. Match Rules

How Rules Work

Match rules determine how matches are made. During Auto Match, these rules reconcile source and sub system transactions automatically. Rules can be configured with tolerance ranges on dates and amounts, and Adjustment rules can post one-sided adjustments when a variance exists.

Match rules also carry a cardinality, or rule type: 1 to 1, 1 to Many, Many to 1, or Many to Many. Each rule has a match status as well: a Confirmed rule makes the match automatically, while a Suggested rule flags the match for a user to review and confirm.

Naming Conventions

By naming your match rules with a consistent convention, you can quickly see which rules are matching the most transactions in the Matching workspace.

In this example, we named the match rules using the following convention: Rule Number, Match Status Code (A = Confirmed), Cardinality (Rule Type), and Rule Attributes.

So 2.A1_1-Date-Invoice-Amount can be read as: Rule #2, a Confirmed (automatic) match, 1-to-1 cardinality, 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, the Matched and Unmatched transaction views display columns in a standard order that may not match how you actually review. Click View → Select Columns and arrange the columns for the source and sub systems in an order that works for you.

Good practice is to order the columns by accounting date, balancing attribute, and match rule attributes.

You can remove unnecessary fields and save the list. Visual auditing is now much easier without the non-essential attributes in the way, and amounts are easier to find because a user does not have to scroll across columns to reach the Amount column.

5. Complete Automation

A well-designed Transaction Matching workflow can be fully optimized with automation. Transaction files can be scheduled for extraction and dropped into a shared SFTP folder. From there, EPM Automate can pick up the files and import them into the appropriate source and sub system data sources (importTmPremappedTransactions). After the transactions are loaded, EPM Automate can run Auto Match (runAutomatch), generate a matching or reconciliation report (for example, with runDMReport), and a script can notify the ARCS administrator when the process completes. Many large enterprises automate their EPM Cloud designs this way for a variety of operational benefits.