ARM_RECONCILIATIONS, the table underneath everything

One row per reconciliation per period, and one row per profile, in the same table. Status as a number, the compliance flags beside it, and an index that makes the close-day question cheap.

Note: ARCS, Oracle's account reconciliation module, stores each account's per-period reconciliation and its standing profile in one table: this one. Every status query in the module starts here, and this page is its working reference.

All ARCS table references hang off Tables & views; the object this table stores is defined on Profiles & formats.

RuleFilter PERIOD_ID <> -2, read status by the documented codes, and keep the security clause on anything a report runs.

Neverdecode STATUS_ID from guesswork. The codes are documented: 32, 6, 10, 1, and null means you forgot the profile filter.

◆ The columns that matter, nine of the fifty, chosen by what queries actually use.

Column	What it holds
RECONCILIATION_ID	Primary key. One row per reconciliation, and one per profile.
PERIOD_ID	The period of the instance. -2 for profiles. The single most important filter in the module.
STATUS_ID	Null = profile, 32 = Pending, 6 = Open with Preparer, 10 = Open with Reviewer, 1 = Closed. The four resting statuses, as numbers.
RECONCILIATION_ACCOUNT_ID	The concatenated account segments. The business key readers recognize.
AUTO_RECONCILED	Whether auto-reconciliation closed it. AUTO_RECONCILIATION_METHOD names the rule.
AGING_VIOLATION	The flag behind the Compliance Dashboard's aging condition; source, subsystem, and expected-balance variants exist as their own columns.
EVER_BEEN_LATE / REJECTS	Late, ever, with anyone; and a count of rejects. The compliance story at row level.
ACTUAL_END_DATE	When it actually finished. On-time is this against END_DATE.
FORMAT_ID	FK to ARM_FORMATS, which decides method and layout.

◆ The trap, the gift, and the query, the -2 filter, the duality in the columns, and the footed status count.

The trap and the gift are the same design choice: profiles and their period instances live in one table. Query without PERIOD_ID <> -2 and every profile counts as a reconciliation, which is how a status total stops footing. The gift: the profile-versus-instance duality from the dictionary page is literally visible in the columns, START_DATE on instances against START_OFFSET on profiles, per Oracle's reference. And the status query is cheap by design: an index sits on PERIOD_ID plus STATUS_ID.

-- the close-day status count, footed
SELECT r.STATUS_ID, COUNT(*)
FROM ARM_RECONCILIATIONS r
WHERE r.PERIOD_ID = :period_id
  AND r.PERIOD_ID <> -2
  AND $ARM_SECURITY_CLAUSE$        -- alias the table ReconciliationEO in report queries
GROUP BY r.STATUS_ID

THE USE CASE, SIMPLIFIED

The problem: Status counts refuse to foot because profiles are being counted as reconciliations.

What we build: The table lands in your warehouse with the profile filter built into every view.

What you get: Counts that foot to the population, every time, by construction.

Building on this table?

Our 10-day engagement lands it in your warehouse with the status history the app never keeps, gated nightly.

Talk to us

Terms on this page

PERIOD_ID = -2: A profile, not a reconciliation. Filter it or nothing foots.
STATUS_ID: 32 Pending · 6 with Preparer · 10 with Reviewer · 1 Closed.
ReconciliationEO: The required alias when the security clause is used.
who columns: CREATED_BY, LAST_UPDATED_BY and friends. Free audit trail.