Search Results org_process_cert

Overview

AMW.AMW_PROC_CERT_EVAL_SUM is a denormalized summary table within the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 GRC (Governance, Risk, and Compliance) module, owned by the AMW schema. It stores certification and evaluation results for each process within a certification scope, consolidating aggregate counts, percentages, and opinion identifiers that would otherwise require expensive joins across detailed transaction tables. The name and structure indicate it acts as a pre-computed snapshot for reporting on certification progress and outcome metrics.

Physically, the table resides in the APPS_TS_SUMMARY tablespace with a 10 percent PCTFREE setting, consistent with summary tables that are loaded in batch and read frequently. The primary key, AMW_PROC_CERT_EVAL_SUM_PK, spans CERTIFICATION_ID, ORGANIZATION_ID, and PROCESS_ID, enforcing one row per process within an organization for a given certification. A unique index (AMW_PROC_CERT_EVAL_SUM_U1) mirrors this composite key as the business-key candidate, while a non-unique index (AMW_PROC_CERT_EVAL_SUM_N1) supports queries filtered on ORGANIZATION_ID and PROCESS_ID alone.

A heuristic Data Vault classification of this object is satellite-leaning. It carries descriptive, certification-scoped attributes rather than standing alone as a hub or a pure junction link, so in a Data Vault model it would typically be modeled as a satellite attached to a certification hub and process/org link.

Key Information Stored

The most significant columns fall into three groups: identity, aggregate counts, and derived ratios.

• Identity columns: CERTIFICATION_ID, ORGANIZATION_ID, and PROCESS_ID form the composite primary key and identify the certification instance, legal/organizational unit, and business process, respectively.
• Sub-process certification: SUB_PROCESS_CERT records the number of sub-processes certified, while the TOTAL_SUB_PROCESS_CERT column (the field that prompted the user's search) holds the total number of sub-processes. Together they yield the certification ratio SUB_PROCS_CERTIFIED_PRCNT.
• Organization certification: ORG_PROCESS_CERT counts organizations where the global process is certified, and TOTAL_ORG_PROCESS_CERT counts organizations where the global process exists. The derived percentage is ORG_PROCS_CERTIFIED_PRCNT.
• Risk and control tallies: UNMITIGATED_RISKS, INEFFECTIVE_CONTROLS, EVALUATED_RISKS, TOTAL_RISKS, EVALUATED_CONTROLS, and TOTAL_CONTROLS are stored alongside ratio columns UNMITIGATED_RISKS_PRCNT and INEFFECTIVE_CONTROLS_PRCNT.
• Issues and findings: OPEN_FINDINGS, OPEN_ISSUES, SUB_PROCESS_CERT_ISSUES, and ORG_PROCESS_CERT_ISSUES track outstanding remediation items.
• Opinion and process context: CERTIFICATION_OPINION_ID, EVALUATION_OPINION_ID, and EVALUATION_OPINION_LOG_ID link to opinion records, while GLOBAL_PROCESS flags whether the process is global and PROCESS_ORG_REV_ID ties to the process/organization revision.
• Standard WHO and locking columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, SECURITY_GROUP_ID, and ORIG_SYSTEM_REFERENCE.

The surrogate primary key is the three-column composite; AMW_PROC_CERT_EVAL_SUM_U1 is documented as the unique business-key candidate, though it currently defines the same column set as the primary key.

Common Use Cases and Queries

The principal use case is certification status reporting. Auditors and compliance managers query this table to see how many sub-processes remain uncertified and to identify processes with high unmitigated risk counts. A typical query aggregates outstanding work per certification:

• Certification completion tracking: SELECT CERTIFICATION_ID, SUM(SUB_PROCESS_CERT) certified, SUM(TOTAL_SUB_PROCESS_CERT) total FROM AMW_PROC_CERT_EVAL_SUM GROUP BY CERTIFICATION_ID;
• Risk exposure by process: filter on UNMITIGATED_RISKS > 0 and order by UNMITIGATED_RISKS_PRCNT descending to surface the highest-risk processes.
• Global process coverage: select rows where GLOBAL_PROCESS = 'Y' and compare ORG_PROCESS_CERT against TOTAL_ORG_PROCESS_CERT to gauge adoption.
• Findings and issue dashboards: aggregate OPEN_FINDINGS and OPEN_ISSUES by ORGANIZATION_ID for management reporting.

Because the table is denormalized, joins back to AMW_CERTIFICATION_B are limited to header attributes such as certification name and date; the numeric metrics are available directly.

Related Objects

The documented foreign keys establish the primary relationships, and the AMW certification schema provides supporting context.

• AMW.AMW_CERTIFICATION_B — joined via CERTIFICATION_ID, providing certification header details; the FK is documented on this table.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID, used for multi-tenant hosting separation.
• Process and organization master tables referenced through PROCESS_ID, ORGANIZATION_ID, and PROCESS_ORG_REV_ID, which supply the process and organization definitions behind each summary row.
• Opinion tables reached through CERTIFICATION_OPINION_ID, EVALUATION_OPINION_ID, and EVALUATION_OPINION_LOG_ID, which hold the narrative result of each evaluation.

This object should be treated as a read-optimized summary rather than a source of truth; any reconciliation should trace back to the underlying certification, risk, and control detail tables in the AMW schema.