Search Results get_cert_sub_process

Overview

The APPS.AMW_ORG_PROC_CERT_DATED_SUMM package body is a reporting and summarization utility within the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 environment. It belongs to the Application Management Workspace (AMW) module, which underpins the Enterprise Risk and Compliance Management (ERM) / Audit Management functionality in EBS. Specifically, this package aggregates risk, control, and certification data at the organizational and process level, filtered by a date range — a design reflected in the "_DATED_SUMM" suffix. The functions return counts of evaluations, total risks, total controls, ineffective controls, unmitigated risks, and process highlights scoped to a certification or organization. The package is classified as OTHER (not a public API), indicating it is intended for internal use by AMW forms, concurrent programs, or dependent PL/SQL logic rather than as a published integration interface.

Key Procedures and Functions

The package exposes 25 documented functions grouped by analytical purpose:

• Certification/Organization risk rollups: GET_UNMIT_RISK_FOR_ORG, GET_EVAL_RISK_FOR_ORG, and GET_TOTAL_RISKS_FOR_ORG return counts of unmitigated, evaluated, and total risks for a given certification and organization, optionally constrained by a date range and a material-risks flag.
• Control rollups: GET_EVAL_CTRLS_FOR_ORG, GET_TOTAL_CTRLS_FOR_ORG, and GET_INEFF_CTRLS_FOR_ORG provide evaluated, total, and ineffective control counts at the organization level.
• Process-level rollups: GET_INEFF_CTRLS_FOR_ORG_PROC, GET_EVAL_CTRLS_FOR_ORG_PROC, GET_TOTAL_CTRLS_FOR_ORG_PROC, GET_TOTAL_RISKS_FOR_ORG_PROC, GET_EVAL_RISK_FOR_ORG_PROC, and GET_UNMIT_RISK_FOR_ORG_PROC drill the same metrics down to a specific process within an organization.
• Process enumeration: GET_ALL_PROCESS_IN_ORG, GET_CERT_PROCESS_IN_ORG, and GET_CERT_SUB_PROCESS return the set of processes or sub-processes in scope for an organization or certification.
• Issue and hierarchy rollups: GET_PROCESS_CERT_ISSUES_IN_ORG, GET_TOTAL_ORG, GET_TOTAL_ORG_CERT_ISSUES, GET_TOTAL_ORG_CERT, and GET_TOTAL_SUB_PROCESS summarize issues, certifications, organizations, and sub-processes across the entity hierarchy.

The most recent evaluation per risk or control is determined through a correlated subquery against AMW_OPINIONS_LOG, using MAX(authored_date) — the same column the user searched on, indicating date-bounded evaluation history is central to the package's logic.

Tables Accessed

The package reads from the following APPS synonyms:

• AMW_RISKS_B and AMW_RISK_ASSOCIATIONS — risk definitions and their linkage to certifications, organizations, and processes.
• AMW_CONTROLS_B and AMW_CONTROL_ASSOCIATIONS — control definitions and their associations, used for control counts and effectiveness analysis.
• AMW_OPINIONS_LOG — the evaluation/opinion audit trail; the authored_date column drives date-range filtering and determination of the latest evaluation per object.
• AMW_PROCESS and AMW_PROCESS_ORGANIZATION — process master and process-to-organization mappings.
• AMW_ENTITY_HIERARCHIES — organizational hierarchy used for parent/child rollups.
• AMW_EXECUTION_SCOPE — determines which objects fall within a certification's scope.

Usage Notes

This package is typically invoked from AMW certification and risk-assessment forms and from concurrent programs that produce summary dashboards. Because it is classified as OTHER rather than a public API and is referenced by zero other packages in the documented metadata, it is not a supported integration point. Custom code that calls these functions should treat the interface as unsupported and subject to change across patch levels. When invoking the date-aware functions, callers should supply from_date and to_date bounds to restrict evaluation history; when omitted, the metadata indicates the logic defaults to a wide historical window. The authored_date column in AMW_OPINIONS_LOG is the authoritative timestamp for evaluation sequencing and should be indexed for acceptable performance.