Search Results controls_changed

Overview

AMW.AMW_CERT_DASHBOARD_SUM is a denormalized summary table within the Oracle E-Business Suite 12.1.1 / 12.2.2 environment, owned by the AMW schema. It stores consolidated, certification-level aggregate information used by the certification dashboard functionality within Oracle's governance, risk, and compliance (GRC) framework. Rather than computing dashboard metrics on demand from the underlying transactional certification, process, risk, and control tables, the dashboard reads pre-aggregated counts directly from this summary table, which improves dashboard response time and reduces query complexity on high-volume certification data.

The table resides in the APPS_TS_SUMMARY tablespace, consistent with its role as a summary or materialized-aggregate structure rather than an operational transaction table. From a Data Vault modeling perspective, the mined relationship data suggests a satellite-leaning classification: the table is keyed by CERTIFICATION_ID and carries a large set of descriptive, dated, and metric attributes that describe a certification hub. Practitioners modeling this data should treat AMW_CERT_DASHBOARD_SUM as a descriptive satellite keyed to the CERTIFICATION_ID hub, with CREATION_DATE and LAST_UPDATE_DATE providing the load/change timestamp context typical of satellites.

Key Information Stored

The primary key is AMW_CERT_DASHBOARD_SUM_PK on CERTIFICATION_ID, which is the surrogate identifier for the certification record. CERTIFICATION_ID is also the single non-unique index column (AMW_CERT_DASHBOARD_SUM_U1) and serves as the join key back to the certification base table. The most significant columns fall into four metric categories:

• Process metrics: NEW_PROCESSES_ADDED, PROCESSES_REMOVED, PROCESSES_CHANGED, NEW_ORG_PROCESSES_ADDED, ORG_PROCESSES_REMOVED, and ORG_PROCESSES_CHANGED capture change counts at both global and organization levels.
• Risk metrics: NEW_RISKS_ADDED, RISKS_REMOVED, RISKS_CHANGED, RISKS_WITH_NO_CONTROLS, and UNMITIGATED_RISKS aggregate risk exposure.
• Control metrics: NEW_CONTROLS_ADDED, CONTROLS_REMOVED, CONTROLS_CHANGED, and INEFFECTIVE_CONTROLS summarize control population and effectiveness.
• Certification status metrics: PROCESSES_NOT_CERT, PROCESSES_CERT_ISSUES, ORG_PROCESS_NOT_CERT, ORG_PROCESS_CERT_ISSUES, PROC_INEFF_CONTROL, and ORG_PROC_INEFF_CONTROL quantify certification completeness and exceptions.
• Scope and period attributes: ORGS_IN_SCOPE, ORGS_PENDING_IN_SCOPE, ORGS_PENDING_CERTIFICATION, PERIOD_START_DATE, and ORIG_SYSTEM_REFERENCE define the certification scope window.
• Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, and SECURITY_GROUP_ID support auditability and multi-tenant security.

Common Use Cases and Queries

The table is the primary data source for the certification dashboard. Typical queries retrieve aggregate counts for a given certification to render dashboard tiles.

To display the certification status summary for a specific certification:

• SELECT certification_id, processes_not_cert, processes_cert_issues, org_process_not_cert, org_process_cert_issues FROM amw.amw_cert_dashboard_sum WHERE certification_id = :cert_id;

To identify certifications with the greatest number of certification exceptions:

• SELECT certification_id, processes_not_cert + org_process_not_cert AS total_not_cert FROM amw.amw_cert_dashboard_sum ORDER BY total_not_cert DESC;

To measure change velocity during a period, filtering on the audit column:

• SELECT certification_id, new_processes_added, processes_changed, new_risks_added, new_controls_added FROM amw.amw_cert_dashboard_sum WHERE last_update_date >= :period_start;

Because the table is denormalized, reconciliation against source detail tables is a recommended validation step before treating dashboard totals as authoritative. Reports should also restrict by SECURITY_GROUP_ID where multi-org security applies.

Related Objects

The documented foreign key relationships and primary key structure establish the following significant dependencies:

• AMW.AMW_CERTIFICATION_B — referenced by AMW_CERT_DASHBOARD_SUM.CERTIFICATION_ID. This is the base certification table and the principal business parent; joins on CERTIFICATION_ID link summary metrics to the certification header.
• FND_SECURITY_GROUPS — referenced by AMW_CERT_DASHBOARD_SUM.SECURITY_GROUP_ID, enforcing the multi-org security model.
• The AMW certification, process, risk, and control detail tables that feed the aggregation logic are logical parents of the metric columns, though they are not documented here as direct foreign keys.
• The certification dashboard concurrent programs and OAF pages that populate and consume AMW_CERT_DASHBOARD_SUM are the primary application-level dependents.

All joins should be performed on CERTIFICATION_ID, which is the documented and indexed access path.