Search Results amw_rl_hierarchy_pkg




Overview
Explore in AI Chat

Overview

AMW_RL_HIERARCHY_PKG is a PL/SQL package owned by the APPS schema within the Oracle E-Business Suite environment, classified under the module prefix AMW. It is part of the Application Management Workspace / Enterprise Risk and Control Management (formerly ETRM) family of objects and, as the suffix "RL_HIERARCHY" indicates, it governs the maintenance and navigation of the risk-and-control library hierarchy — the parent/child relationships that link processes, risks, and controls into a structured tree. The package encapsulates the business rules by which hierarchy nodes are inserted, reordered, revised, and removed, and it maintains the denormalized and "latest" hierarchy snapshots that support query performance and user-facing navigation.

The header timestamp (amwrlhrs.pls 120.1, 2005/11/29) reflects a long-stable code line inherited from the original ETRM product and carried forward into EBS 12.1.1 and 12.2.2 with only minor technical recompilation. Because it declares exception types such as amw_process_locked_exception, amw_circularity_exception, wf_cycle_present_exception, and amw_process_deleted_exception, the package enforces integrity constraints that protect the hierarchy from illegal restructuring.

Key Procedures and Functions

The package exposes forty-one documented procedures and functions. The dominant group concerns hierarchy mutation and child linkage. ADD_EXISTING_PROCESS_AS_CHILD inserts an existing process node beneath a parent, DELETE_CHILD removes a child link, and UPDATE_DENORM_ADD_CHILD and UPDATE_DENORM refresh the denormalized hierarchy tables after such changes. UPDATE_APPROVED_DENORM performs the equivalent refresh for the approved hierarchy snapshot. REVISE_PROCESS_IF_NECESSARY conditionally creates a revision of a process when edits require versioning, while IS_ANCESTOR_IN_HIERARCHY answers ancestor/descendant queries used during drag-and-drop moves.

Validity and lifecycle guards include IS_LOCKED_PROCESS (checks the process lock table before permitting edits), IS_DELETED_PROCESS (detects end-dated processes that must not be reintroduced), and IMPORT_WF_PROCESS (brings in processes originating from Oracle Workflow definitions, raising the cycle-present exception where recursion is detected).

A third group recalculates roll-up counts so that parents display accurate aggregates: UPDATE_ORG_COUNT, UPDATE_LATEST_RISK_COUNTS, UPDATE_LATEST_CONTROL_COUNTS, UPDATE_APPROVED_RISK_COUNTS, UPDATE_APPROVED_CONTROL_COUNTS, UPDATE_RC_LATEST_COUNTS, UPDATE_APPR_RISK_COUNTS, UPDATE_APPR_CONTROL_COUNTS, UPDATE_ALL_ORG_COUNTS, and UPDATE_ALL_LATEST_RC_COUNTS. These maintain counters across organizational and approval scopes.

Tables Accessed

The package reads and writes AMW_LATEST_HIERARCHIES as the authoritative current link table, and AMW_APPROVED_HIERARCHIES for the frozen approved view. Denormalized structures AMW_PROC_HIERARCHY_DENORM and AMW_ORG_HIERARCHY_DENORM are refreshed to accelerate reporting. Process master data resides in AMW_PROCESS, AMW_PROCESS_NAMES_TL, AMW_PROCESS_NAME_S, and AMW_PROCESS_CODE_S. Concurrency and sequencing are handled through AMW_PROCESS_LOCKS, AMW_CHILD_ORDER_S, AMW_PROCESS_ORGANIZATION, and AMW_PARAMETERS. Association tables AMW_ACCT_ASSOCIATIONS, AMW_CONTROL_ASSOCIATIONS, and AMW_OBJECTIVE_ASSOCIATIONS are consulted when validating node dependencies before deletion.

Usage Notes

AMW_RL_HIERARCHY_PKG is invoked from the ETRM hierarchy maintenance forms (for example the process/risk library tree UI) whenever a user adds, moves, or deletes a hierarchy node — operations commonly typed as delete_draft or similar UI actions. It is also called by concurrent programs that rebuild denormalized counts and approved snapshots in batch. Six other packages call into it, so customizations should treat it as an internal, version-sensitive API. Because it raises named exceptions and enforces lock and circularity checks, callers should always include an exception handler; direct DML against the underlying hierarchy tables should be avoided in favour of the documented procedures.