Search Results amw_cert_dashboard_sum_pk

Overview

The AMW_CERT_DASHBOARD_SUM table is a denormalized summary table within the Oracle E-Business Suite Internal Controls Manager (AMW) module. It stores overall, pre-aggregated certification information used to render the certification dashboard, allowing users to review the state of a certification initiative without traversing the detailed transactional tables that underlie it. Because the dashboard is a high-visibility, frequently accessed screen, denormalization trades storage redundancy for query performance, collapsing process, risk, and control metrics into a single row per certification.

The object is owned by the AMW schema and is marked VALID in the documented ETRM 12.1.1 metadata. Its primary key is AMW_CERT_DASHBOARD_SUM_PK on CERTIFICATION_ID. Applying a heuristic Data Vault classification, the table is satellite-leaning: it is keyed by a single surrogate primary key and carries descriptive, aggregated attributes rather than participating in many-to-many relationships. In a Data Vault model this would most naturally be modeled as a satellite attached to the AMW_CERTIFICATION_B hub.

Key Information Stored

The table contains 36 documented columns. The most functionally significant are the aggregate counts that drive the dashboard, organized into categories of change, completeness, and effectiveness:

• CERTIFICATION_ID — surrogate primary key (AMW_CERT_DASHBOARD_SUM_PK) and foreign key to AMW_CERTIFICATION_B; the business-key anchor for each certification.
• NEW_PROCESSES_ADDED, PROCESSES_REMOVED, PROCESSES_CHANGED — process-level change metrics for the certification period.
• NEW_RISKS_ADDED, RISKS_REMOVED, RISKS_CHANGED, RISKS_WITH_NO_CONTROLS — risk inventory movement and exposure gaps.
• NEW_CONTROLS_ADDED, CONTROLS_REMOVED, CONTROLS_CHANGED, INEFFECTIVE_CONTROLS — control-level change and effectiveness counts.
• PROCESSES_NOT_CERT, PROCESSES_CERT_ISSUES, PROC_INEFF_CONTROL, UNMITIGATED_RISKS — outstanding certification issues at the process level.
• ORGS_IN_SCOPE, ORGS_PENDING_IN_SCOPE, ORGS_PENDING_CERTIFICATION, ORG_CERT_ISSUES, ORG_INEFFECTIVE — organization-scope and pending-completion metrics.
• PERIOD_START_DATE — the reporting period the summary covers.
• Standard EBS audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) plus SECURITY_GROUP_ID (FK to FND_SECURITY_GROUPS) and ORIG_SYSTEM_REFERENCE for multi-tenant and integration control.

Common Use Cases and Queries

The primary use case is dashboard and status reporting for in-flight certifications. A typical query joins the summary to the certification base table to retrieve header context while exposing the aggregate counts:

SELECT s.certification_id,
       s.processes_not_cert,
       s.processes_cert_issues,
       s.unmitigated_risks,
       s.ineffective_controls,
       s.orgs_pending_certification
FROM   amw_cert_dashboard_sum s
WHERE  s.certification_id = :p_certification_id;

For exception-driven reporting, analysts filter on non-zero exception counters (for example, UNMITIGATED_RISKS or INEFFECTIVE_CONTROLS) to identify certifications requiring remediation. Because the table is security-group aware, queries should respect SECURITY_GROUP_ID to enforce multi-org data access. The table is denormalized, so refreshes are typically performed by the certification process rather than by ad-hoc DML; reports should treat it as a read-only snapshot.

Related Objects

• AMW_CERTIFICATION_B — the parent hub/base table; joined on CERTIFICATION_ID (documented foreign key).
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for access control.
• AMW_CERT_DASHBOARD_SUM_PK — the primary key index on CERTIFICATION_ID.
• Underlying detail tables for processes, risks, and controls (for example the AMW process, risk, and control certification entities) supply the source data that populates these aggregates.
• Certification dashboard and reporting views/APIs in the AMW module that read this summary to render dashboards.