Search Results des_eff_id

Overview

The AMW.RCI_ORG_CERT_CTRLS_F table is a fact table in the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 environment, residing in the AMW schema within the APPS_TS_TX_DATA tablespace. It is a certified VALID object registered under FND Design Data as AMW.RCI_ORG_CERT_CTRLS_F. According to the ETRM repository, its stated purpose is to store summary data related to the Control Detail report, specifically the relationship between financial statement certifications and the individual controls evaluated against them.

This object is a critical component of the Oracle EBS governance, risk, and compliance (GRC) reporting layer, providing pre-aggregated control-level results that feed certification and control-reporting dashboards. Based on the provided heuristic Data Vault classification (mined from the FK structure), this object is best modeled as a standalone object. In Data Vault terms it exhibits satellite-like behavior because it stores descriptive, time-stamped control evaluation attributes keyed to certification and control identifiers, rather than acting as a pure hub or link. The classification should be treated as a modeling suggestion only.

Key Information Stored

The table carries 35 documented columns. The following are the most significant for reporting and joins:

• FIN_CERTIFICATION_ID — the financial statement certification identifier; a principal business-key candidate relating the fact record back to the certification header.
• CERTIFICATION_ID — the process certification identifier, a second business-key candidate that links to the process certification entity.
• CONTROL_ID and CONTROL_REV_ID — foreign keys to the control definition (CST_REVENUE_COGS_CONTROL) and control revision (AMW_CONTROLS_B) respectively.
• ORGANIZATION_ID — the organization (operating unit / legal entity) under which the control was evaluated.
• LATEST_REV_NUM — indicates the latest revision number for the control; this is the column the user searched and is central to isolating the current control revision in a report.
• CERTIFICATION_STATUS and CERTIFICATION_TYPE — describe the state and category of the financial statement certification.
• CERTIFICATION_PERIOD_NAME and CERTIFICATION_PERIOD_SET_NAME — identify the accounting period and period set to which the certification belongs.
• CONTROL_TYPE, CONTROL_LOCATION, AUTOMATION_TYPE, and CONTROL_FREQUENCY — control classification attributes.
• KEY_CONTROL and DISCLOSURE_CONTROL — flags indicating whether the control is a key control or a disclosure control.
• AUDIT_RESULT_CODE, LAST_EVALUATED_BY_ID, and LAST_EVALUATED_ON — the audit outcome and the person/date of the most recent evaluation.
• OP_EFF_ID and DES_EFF_ID — operating effectiveness and design effectiveness identifiers.

No unique index is documented; the only index is RCI_ORG_CERT_CTRLS_F_N1, a NONUNIQUE normal index on REPORT_DATE_JULIAN in the APPS_TS_TX_IDX tablespace. Consequently there is no documented surrogate primary key — the fact row is naturally identified by the composite of certification, control, revision, and organizational keys.

Common Use Cases and Queries

Typical uses include the Control Detail report, certification status dashboards, key-control coverage analysis, and effectiveness roll-ups. A common pattern isolates the latest revision of each control by pairing CONTROL_ID with LATEST_REV_NUM:

• Reporting current controls only: SELECT c.control_id, c.control_rev_id, c.latest_rev_num, c.certification_status FROM amw.rci_org_cert_ctrls_f c WHERE c.latest_rev_num = c.control_rev_id;
• Filtering by reporting period using the indexed date column: SELECT * FROM amw.rci_org_cert_ctrls_f WHERE report_date_julian BETWEEN :start_jul AND :end_jul;
• Key-control variance reporting by organization: SELECT organization_id, COUNT(*) FROM amw.rci_org_cert_ctrls_f WHERE key_control = 'Y' GROUP BY organization_id;
• Effectiveness analysis joining design and operating effectiveness IDs to their lookup tables.

Related Objects

The following documented relationships are most significant:

• CST_REVENUE_COGS_CONTROL — referenced via CONTROL_ID, supplying the revenue/COGS control definition.
• AMW_CONTROLS_B — referenced via CONTROL_REV_ID, providing control revision detail.
• Certification header entities — accessed via FIN_CERTIFICATION_ID and CERTIFICATION_ID.
• Organization / HR tables — joined through ORGANIZATION_ID and LAST_EVALUATED_BY_ID.
• FND period and accounting calendar objects — resolved through CERTIFICATION_PERIOD_NAME and CERTIFICATION_PERIOD_SET_NAME.

Because the object is classified as standalone, referential integrity is enforced primarily through application logic and the two documented foreign keys rather than a single surrogate parent.