Search Results certification_type

Overview

AMW.RCI_ORG_CERT_RISKS_F is a fact table in the Oracle E-Business Suite governance, risk, and compliance (GRC) schema, owned by the AMW application (Oracle Financial Governance, Risk, and Compliance Manager / ETRM). The table stores summarized data that supports the Risk Detail report, which reports on risks evaluated in the context of financial statement certifications and process certifications. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and it holds a single documented non-unique index, RCI_ORG_CERT_RISKS_F_N1, on REPORT_DATE_JULIAN in tablespace APPS_TS_TX_IDX.

Because the table aggregates certification and risk attributes across organizational and process dimensions, it functions as a reporting fact rather than a transactional entity. Its grain is effectively one row per risk occurrence tied to a certification context, captured at a point in time identified by REPORT_DATE_JULIAN. The documented physical schema exposes 30 columns, and the table is registered as FND Design Data under AMW.RCI_ORG_CERT_RISKS_F with a VALID status in EBS 12.1.1 and 12.2.2.

The heuristic Data Vault classification mined from the foreign key structure is standalone. Only one foreign key is documented — RISK_REV_ID referencing AMW_RISKS_B. As a modeling suggestion, this pattern is closer to a fact or satellite-style structure anchored to the risk revision than to a hub or link, because the table carries measurable and descriptive attributes (impacts, likelihood, audit results, period identifiers) rather than acting as a pure business-key registry or an association between two hubs.

Key Information Stored

The table does not expose a documented single-column surrogate primary key; instead, identity is composite, formed by the combination of certification context (FIN_CERTIFICATION_ID, CERTIFICATION_ID, ORGANIZATION_ID, PROCESS_ID) and the risk reference (RISK_ID, RISK_REV_ID). The most significant columns are:

• CERTIFICATION_TYPE (VARCHAR2 30) — the financial statement certification type, the attribute most frequently used to filter and group the Risk Detail report. It pairs with CERTIFICATION_STATUS and CERTIFICATION_PERIOD_NAME.
• CERTIFICATION_STATUS — the financial statement certification status.
• FIN_CERTIFICATION_ID — the financial statement certification identifier, the primary linkage to the certification header.
• CERTIFICATION_ID — the process certification identifier, allowing the same risk view to be segmented by process.
• RISK_ID and RISK_REV_ID — the risk identifier and its revision; RISK_REV_ID is the documented foreign key to AMW_RISKS_B.
• PROJECT_ID — the audit engagement identifier for the risk.
• ORGANIZATION_ID and PROCESS_ID — the organizational and process dimensions.
• MATERIAL, RISK_IMPACT, LIKELIHOOD, AUDIT_RESULT_CODE — the assessed risk characteristics driving the report.
• LAST_EVALUATOR_ID and LAST_EVALUATED_ON — who last evaluated the risk and when.
• REPORT_DATE_JULIAN — the indexed reporting date supporting time-based slicing.
• Period identifiers such as CERTIFICATION_PERIOD_NAME, CERTIFICATION_PERIOD_SET_NAME, PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, ENT_PERIOD_ID, ENT_QTR_ID, and ENT_YEAR_ID — used for calendaring and FII-based enterprise period alignment.
• Standard Who columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical use cases include producing the Risk Detail report, trending risk likelihood and impact by certification type and period, and reconciling certifications to the risk revisions that were evaluated. A representative query filters by certification type and period:

• SELECT FIN_CERTIFICATION_ID, CERTIFICATION_TYPE, ORGANIZATION_ID, RISK_ID, RISK_IMPACT, LIKELIHOOD, AUDIT_RESULT_CODE FROM AMW.RCI_ORG_CERT_RISKS_F WHERE CERTIFICATION_TYPE = :type AND PERIOD_YEAR = :year AND PERIOD_NUM = :period;
• Aggregate risk counts and material risks: SELECT CERTIFICATION_TYPE, ORGANIZATION_ID, SUM(CASE WHEN MATERIAL = 'Y' THEN 1 ELSE 0 END) FROM AMW.RCI_ORG_CERT_RISKS_F GROUP BY CERTIFICATION_TYPE, ORGANIZATION_ID;
• Time-based extract using the indexed column: SELECT * FROM AMW.RCI_ORG_CERT_RISKS_F WHERE REPORT_DATE_JULIAN BETWEEN :from_julian AND :to_julian;
• Join to the risk revision base: SELECT f.RISK_ID, f.CERTIFICATION_TYPE, b.* FROM AMW.RCI_ORG_CERT_RISKS_F f, AMW.AMW_RISKS_B b WHERE f.RISK_REV_ID = b.RISK_REV_ID;

Related Objects

The table is anchored by its documented FK relationship and by the certification and risk subject areas it reports on:

• AMW.AMW_RISKS_B — the risk base table; joined via RISK_REV_ID, the sole documented foreign key.
• AMW.RCI_ORG_CERT_RISKS_F_N1 — the supporting non-unique index on REPORT_DATE_JULIAN, used for date-range reporting.
• Financial statement certification headers referenced through FIN_CERTIFICATION_ID.
• Process certification headers referenced through CERTIFICATION_ID.
• Organization and process dimension objects referenced through ORGANIZATION_ID and PROCESS_ID.
• Natural account dimension referenced through NATURAL_ACCOUNT_ID.
• Evaluator person records referenced through LAST_EVALUATOR_ID.
• Enterprise period (FII) objects referenced through ENT_PERIOD_ID, ENT_QTR_ID, and ENT_YEAR_ID.

Because the table is a summary fact, dependent reports and views query it directly rather than writing to it; refreshes are driven by the underlying certification and risk processes rather than by direct DML.