Search Results total_sub_org

Overview

The APPS.AMW_ORG_CERT_AGGR_PKG package body is a component of the Oracle E-Business Suite Enterprise Talent and Resource Management (ETRM) module, specifically within the certification and competency aggregation subsystem. Its principal responsibility is to maintain the aggregate certification hierarchy data that underpins organizational certification roll-up reporting and analysis. The package operates on the AMW_FULL_ENTITY_HIER hierarchy model, populating and refreshing the denormalized certification aggregation summaries used by ETRM dashboards, evaluation queries, and certification compliance reporting. In the context of the user's search term "evaluated_controls," this package participates in the broader evaluation aggregation chain: certification evaluations and their associated controls are summarized through the organization certification aggregation rows maintained here, indirectly supporting the evaluated controls reporting that consumes certification evaluation summary data.

Key Procedures and Functions

• POPULATE_FULL_HIERARCHIES — Builds the complete entity hierarchy records for business process certification, traversing organizational (subsidiary) structures and writing rows into AMW_FULL_ENTITY_HIER. Internally it relies on the private recursive routine recursive_populate_hier to walk parent-child relationships derived from HR organization flex values.
• POPULATE_ORG_CERT_AGGR_ROWS — Inserts organization-level certification aggregation summary rows. It establishes the base aggregation records consumed by downstream reporting against AMW_ORG_CERT_AGGR_SUM and the associated evaluation summary table.
• UPDATE_ORG_CERT_AGGR_ROWS — Refreshes existing aggregation summary rows, recomputing certification roll-up values as hierarchy or evaluation data changes. This procedure supports incremental maintenance of the aggregation tables without a full rebuild.

Tables Accessed

• AMW_FULL_ENTITY_HIER / AMW_FULL_ENTITY_HIER_S — The full entity hierarchy and its sequence; populated and queried to maintain parent-child certification hierarchy rows.
• AMW_ORG_CERT_AGGR_SUM / AMW_ORG_CERT_AGGR_SUM_S — Organization certification aggregation summary and its sequence; the primary target of insert and update operations.
• AMW_ORG_CERT_EVAL_SUM — Organization certification evaluation summary; read to source evaluation counts and results used in aggregation. This is the linkage point for evaluated-controls reporting.
• AMW_EXECUTION_SCOPE — Defines the scope of entities processed during a given aggregation run.
• FND_FLEX_VALUES and the FND_FLEX_VALUE_CHILDREN_V view — Supply the HR/flex hierarchy parent-child relationships used in recursive traversal.
• HR_ORGANIZATION_INFORMATION — Provides organization classification attributes required to identify subsidiaries and scoping metadata.

Usage Notes

This package is classified as OTHER and is typically invoked indirectly rather than from a user-facing form. It is called by concurrent programs and by higher-level aggregation packages (one other package references it) that orchestrate certification data refreshes. The recursive_populate_hier private routine handles hierarchy insertion with duplicate suppression via a row-existence cursor, and it short-circuits on the sentinel child id of -1. Administrators running organization certification aggregation jobs should expect this package to be exercised during full and incremental rebuilds; custom extensions should not call the private routines directly but may invoke the three public procedures in the documented sequence to rebuild and refresh aggregation data.