Search Results rci_org_cert_ctrls_f

Overview

RCI_ORG_CERT_CTRLS_F is a fact table in the AMW (Internal Controls Manager) product of Oracle E-Business Suite, owned by the AMW schema. As documented, it stores summary data supporting the Control Detail report, aggregating certification and control-evaluation results at the organization, process, account, and period level. It is a denormalized reporting fact rather than a transactional base table. Based on the FK structure mined from the metadata, the table is heuristically classified as standalone; this is a modeling suggestion only — the table does not participate in a classic hub/link/satellite dependency pattern and appears to be loaded as a materialized reporting artifact rather than a normalized Data Vault construct. In both EBS 12.1.1 and 12.2.2 the object retains 35 documented columns and is marked VALID.

Key Information Stored

Because this is a fact table, its most significant columns are those that define the reporting grain and carry the derived measures and status codes:

• CERTIFICATION_ID / FIN_CERTIFICATION_ID — surrogate identifiers for the certification event driving the report row; a likely component of the primary key.
• AUDIT_RESULT_CODE — the summarized evaluation result, the semantic measure most often reported.
• CERTIFICATION_STATUS / CERTIFICATION_TYPE — the state and classification of the certification.
• CERTIFICATION_PERIOD_NAME / CERTIFICATION_PERIOD_SET_NAME / PERIOD_YEAR / PERIOD_NUM / QUARTER_NUM — the accounting period and calendar context, establishing the temporal grain.
• ORGANIZATION_ID / PROCESS_ID / NATURAL_ACCOUNT_ID — the organizational, process, and chart-of-accounts dimensions used to slice results.
• CONTROL_ID / CONTROL_REV_ID / LATEST_REV_NUM — the control and its revision; CONTROL_ID references CST_REVENUE_COGS_CONTROL and CONTROL_REV_ID references AMW_CONTROLS_B.
• CONTROL_TYPE / CONTROL_LOCATION / AUTOMATION_TYPE / CONTROL_FREQUENCY / KEY_CONTROL / DISCLOSURE_CONTROL — control attributes carried into the fact to avoid runtime joins.
• LAST_EVALUATED_BY_ID / LAST_EVALUATED_ON — evaluation provenance.
• ENT_PERIOD_ID / ENT_QTR_ID / ENT_YEAR_ID / REPORT_DATE_JULIAN — enterprise calendar surrogate keys and a Julian date used for efficient range filtering.

The metadata does not document a declared unique index, so the surrogate primary key cannot be asserted with certainty. CERTIFICATION_ID combined with CONTROL_ID and the period identifiers is the most plausible business-key candidate. Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) support audit lineage.

Common Use Cases and Queries

Primary usage is Control Detail reporting. A representative query ties the fact to its control master:

• Count of audits by result code and period: SELECT CERTIFICATION_PERIOD_NAME, AUDIT_RESULT_CODE, COUNT(*) FROM AMW.RCI_ORG_CERT_CTRLS_F GROUP BY CERTIFICATION_PERIOD_NAME, AUDIT_RESULT_CODE;
• Key-control focus: ... WHERE KEY_CONTROL = 'Y' AND ORG_ID = :org_id;
• Control attribute enrichment: join AMW_CONTROLS_B on CONTROL_REV_ID and CST_REVENUE_COGS_CONTROL on CONTROL_ID.
• Trend analysis by quarter using ENT_QTR_ID or QUARTER_NUM.
• Dashboard extracts filtered by REPORT_DATE_JULIAN for fast date ranges.

Related Objects

• AMW_CONTROLS_B — joined via CONTROL_REV_ID; source of control definitions.
• CST_REVENUE_COGS_CONTROL — joined via CONTROL_ID; documented FK target.
• Certification master tables referenced by CERTIFICATION_ID and FIN_CERTIFICATION_ID.
• Organization, process, and natural-account dimension tables via ORGANIZATION_ID, PROCESS_ID, and NATURAL_ACCOUNT_ID.
• Enterprise calendar tables via ENT_PERIOD_ID, ENT_QTR_ID, and ENT_YEAR_ID.
• Control Detail report definitions that consume this fact as their primary source.