The tables behind ARCS, and why this module is different
Every other EPM module keeps its data in cubes you cannot query. Account Reconciliation runs on relational tables Oracle documents column by column, and lets you run your own SQL against them through report queries. This page is the map.
Note: ARCS, Oracle's cloud module for account reconciliations, runs on an ordinary relational database. The rest of EPM stores data in cubes you cannot query directly, which is why this page can list tables at all, and why that matters.
One question this page answers: when the application shows a number the reports will not give you, which table is it sitting in, and how do you get it out? The whole module is mapped on the Account Reconciliation index.
◆ The map, tables to answer, left to right; every node opens its page.
Everything in the left frame lives inside Oracle EPM, the upstream source: the ARCS tables, the query that reads them, the FCCS cube. One nightly crossing lands it in the right frame, yours: the star keeps every period, the console answers from it.
◆ The four table families, FCM_ common, ARM_ compliance, TM_ matching, and the dynamic tables per match type.
| Family | What lives there, and the page that uses it |
|---|---|
| FCM_ | Shared plumbing: users, teams, attributes. Every workflow query joins FCM_USERS for preparer and reviewer names. |
| ARM_ | Reconciliation Compliance. ARM_RECONCILIATIONS: one row per account per period, where status lives. ARM_BALANCE_TOTALS: the loaded balances. ARM_COMMENTS: the commentary auditors ask for. ARM_FORMATS: the snapshot proof. |
| TM_ | Transaction Matching, the engine that pairs transactions between systems by rule: its match types, data sources, rules, and jobs live here. |
| TM_TRANS_ | Created at match-type approval, one table per data source, columns defined by your attributes. The Match Type view unions them, one stable thing to query. The pattern, in full. |
The four families and every column in them are in Oracle's own reference, Tables and Views for Account Reconciliation, and the Match Type view behavior is documented here. This is the reference the cube modules of EPM never get.
◆ Custom queries, the two rules, the security clause and the derived-column trap, both verified against Oracle docs and live threads.
Three routes to a working query, per Oracle's report-configuration guide: duplicate a predefined report's query and edit its SQL, use Generate Query and pick attributes, or write the SQL yourself against the documented tables. Two rules keep the result safe and portable:
| Rule | Why |
|---|---|
| $ARM_SECURITY_CLAUSE$ | Append it to the WHERE clause and alias ARM_RECONCILIATIONS as ReconciliationEO. Without it, the report shows every reader every reconciliation, and your role security ends at the report boundary. The predefined queries ship with it applied; copy their pattern. |
| Derive, do not hunt | Period Activity, Unexplained Difference, and the adjusted balances are not stored columns waiting in ARM_BALANCE_TOTALS. They are computed on screen, and a live Customer Connect thread confirms they must be derived in your query. Write each derivation once, in one place. |
The query gets you the data; the output template is a second fight. Report layouts run through BI Publisher RTF templates, where Oracle's own template guide says plainly that conditional formatting cannot be used inside pivot tables and must be hand-coded into form fields, a limit users still hit in ARCS reports today. That ceiling is the practical case for the owned model: same query, run in your warehouse, formatted by whatever tool your team already uses.
The pattern for anything the column list does not offer is a correlated subquery, and the classic worked example is pulling reconciliation comments from ARM_COMMENTS by OBJECT_ID, documented by a practitioner here. If a number or a sentence is visible in the application, a query can return it.
◆ One Tuesday in close week, the open-items grid the close manager actually wants, in one query.
The sixty-entity group from our FCCS pages, June close, Tuesday evening. The close manager wants one row per open reconciliation with its preparer, its age, and the last comment. The query is ARM_RECONCILIATIONS joined to FCM_USERS, the security clause on the end, the comment as a correlated subquery. Sixty entities, 412 reconciliations in the period, and the 144 still open come back in one grid instead of 144 clicks. The same query, run through the report framework, is what the status export page automates nightly. And it is the reconciliation leg of the close-readiness answer on our homepage console: consolidation status comes from the FCCS cube by export, reconciliation status comes from these tables by query.
answer Wednesday 07:00 · closed 267 of 412 · 97 with preparers · 45 in review · 3 pending · reopened overnight: 1010-CL, the Chilean subsidiary, balance changed by a late journal
The problem: The application's data sits in documented tables nobody can query for reporting.
What we build: The tables that matter land nightly in your warehouse, named and keyed as this page documents them.
What you get: Any close question becomes SQL against tables you own.
- ARM_RECONCILIATIONS
- One row per reconciliation per period. Where every status query starts.
- Match Type view
- Auto-created per approved match type. One stable thing to query across data sources.
- security clause
- $ARM_SECURITY_CLAUSE$ in the WHERE. Role security inside your own SQL.
- derived column
- On-screen measures like Unexplained Difference. Computed, not stored. Derive them yourself.