Search Results rci_org_cert_risks_f

Overview

RCI_ORG_CERT_RISKS_F is a fact table owned by the AMW schema within the Oracle E-Business Suite Internal Controls Manager (ICM) module. Its documented purpose is to store the summary data that drives the Risk Detail report, one of the core deliverables of the certification and risk assessment workflow. In Oracle EBS 12.1.1 and 12.2.2, this table functions as a denormalized reporting store: transactional risk evaluation activity is consolidated into this structure so that the Risk Detail report can be rendered efficiently without repeatedly aggregating underlying detail records.

The table carries a mined Data Vault classification of standalone, meaning the heuristic FK analysis did not identify it as a strict hub, link, or satellite. From a modeling perspective, this object is best treated as an aggregate fact structure — a pre-computed reporting surface rather than a normalized operational entity. The single documented foreign key (RISK_REV_ID referencing AMW_RISKS_B) nonetheless anchors each row to a specific risk revision, giving the fact records a degree of referential integrity to the risk master data. With 30 documented columns in the 12.1.1 physical schema, the table is wide by EBS standards and clearly oriented toward reporting rather than transactional processing.

Key Information Stored

Each row represents a summarized risk assessment outcome for a given certification, organization, and period combination. The most significant columns include:

• PROJECT_ID — identifies the ICM project (certification campaign) context for the summary row.
• CERTIFICATION_ID / FIN_CERTIFICATION_ID — the certification and its final (signed-off) instance to which the risk summary relates.
• CERTIFICATION_STATUS / CERTIFICATION_TYPE / CERTIFICATION_PERIOD_NAME / CERTIFICATION_PERIOD_SET_NAME — descriptive attributes of the certification, including its lifecycle state, category, and accounting period context.
• ORGANIZATION_ID / PROCESS_ID / NATURAL_ACCOUNT_ID — the organizational, business process, and account dimensions summarized by the fact row.
• RISK_ID / RISK_REV_ID — the risk and its specific revision; RISK_REV_ID is the documented foreign key to AMW_RISKS_B.
• MATERIAL / RISK_IMPACT / LIKELIHOOD — the assessed materiality and the impact/likelihood scoring that feed risk ranking.
• AUDIT_RESULT_CODE — the outcome classification recorded against the risk.
• LAST_EVALUATOR_ID / LAST_EVALUATED_ON — the evaluator and timestamp of the most recent assessment.
• PERIOD_YEAR / PERIOD_NUM / QUARTER_NUM / ENT_PERIOD_ID / ENT_QTR_ID / ENT_YEAR_ID / REPORT_DATE_JULIAN — the full temporal dimension set, including Julian report dates supporting period-over-period reporting.
• CREATION_DATE / CREATED_BY / LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — the standard EBS audit columns.

The surrogate primary key is represented by the internal ID columns (notably CERTIFICATION_ID and RISK_REV_ID in combination, subject to the documented index structure), while business-key candidates are the composite of PROJECT_ID, CERTIFICATION_ID, ORGANIZATION_ID, PROCESS_ID, and RISK_REV_ID. The metadata does not document a unique index explicitly, so uniqueness should be verified against the actual DBA_INDEXES output in each release.

Common Use Cases and Queries

The primary use case is the Risk Detail report. A typical query joins the fact table to the risk master to enrich risk descriptions:

• Risk Detail extraction: SELECT r.RISK_ID, r.RISK_IMPACT, r.LIKELIHOOD, r.MATERIAL, r.AUDIT_RESULT_CODE FROM AMW.RCI_ORG_CERT_RISKS_F r WHERE r.CERTIFICATION_ID = :p_cert_id.
• Join to risk definitions: SELECT f.*, b.RISK_NAME FROM AMW.RCI_ORG_CERT_RISKS_F f, AMW.AMW_RISKS_B b WHERE f.RISK_REV_ID = b.RISK_REV_ID.
• Period trending: aggregate COUNT(*) of material risks by PERIOD_YEAR and QUARTER_NUM or ENT_QTR_ID to produce heat-map or trend dashboards.
• Organizational roll-up: group by ORGANIZATION_ID and PROCESS_ID to surface risk concentration for management review.
• Evaluator workload and aging: filter on LAST_EVALUATED_ON and LAST_EVALUATOR_ID to identify stale assessments.

Related Objects

• AMW_RISKS_B — joined via RISK_REV_ID; the parent risk definition table supplying risk names and attributes.
• AMW_RISKS_TL — translated risk descriptions, typically joined via RISK_ID.
• RCI_ORG_CERTIFICATIONS / certification base tables — supply CERTIFICATION_ID, status, and period metadata.
• Certification final/sign-off tables — referenced through FIN_CERTIFICATION_ID.
• HR organization and process tables — resolve ORGANIZATION_ID and PROCESS_ID to business-friendly names.
• Risk Detail report concurrent program — the primary consumer that reads this fact table for output generation.
• Risk Detail report views — presentation-layer views built over this fact surface for BIP/XML Publisher output.