Search Results amw_proc_hierarchy_denorm




Overview
Explore in AI Chat

Overview

AMW_PROC_HIERARCHY_DENORM is a denormalized table owned by the AMW schema within Oracle E-Business Suite, belonging to the AMW - Internal Controls Manager product module. As its name implies, the table stores a flattened, pre-computed representation of the process hierarchy used by Internal Controls Manager. Rather than resolving parent-child relationships recursively at query time, the denormalized structure materializes the full upward and downward paths between process nodes, allowing applications and reports to traverse the hierarchy with simple, indexed lookups. This design is characteristic of ETRM (Enterprise Transaction and Reference Model) objects that support hierarchical governance, risk, and compliance reporting, where performance of read-heavy hierarchy navigation is critical.

From a heuristic Data Vault modeling perspective, the table is classified as standalone. This suggests it behaves less like a pure hub, link, or satellite and more like a self-contained denormalized structure whose primary role is to serve as a derived or materialized view of underlying hierarchy relationships. The classification should be treated as a modeling suggestion rather than a normative declaration.

Key Information Stored

The table contains eleven documented columns. Its most significant columns include:

  • PROCESS_ID — The identifier of the process node whose position in the hierarchy is being represented. This column participates in the unique business key.
  • PARENT_CHILD_ID — The related process node connected to PROCESS_ID within the flattened path. Together with PROCESS_ID it defines a hierarchy edge or relationship.
  • UP_DOWN_IND — An indicator denoting whether the relationship is traversed upward (ancestor) or downward (descendant) in the hierarchy.
  • HIERARCHY_TYPE — Identifies the type of hierarchy being stored, allowing multiple hierarchy definitions to coexist within the same table.
  • SECURITY_GROUP_ID — Foreign key referencing FND_SECURITY_GROUPS, providing row-level security partitioning consistent with other EBS internal controls data.
  • OBJECT_VERSION_NUMBER — Supports optimistic locking and change tracking for concurrent access.
  • Standard Who Columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN provide auditability and change history.

The documented surrogate primary key is not explicitly separated from the business-key candidates. The unique index AMW_PROC_HIERARCHY_DENORM_U1 spans PROCESS_ID, PARENT_CHILD_ID, UP_DOWN_IND, and HIERARCHY_TYPE, establishing the natural business key for each hierarchy relationship.

Common Use Cases and Queries

Typical reporting scenarios include retrieving all ancestors or descendants of a given process for risk roll-up, compliance scoping, and segregation-of-duties analysis. A representative query to find all descendants of a process node follows:

  • SELECT PARENT_CHILD_ID FROM AMW_PROC_HIERARCHY_DENORM WHERE PROCESS_ID = :p_process_id AND UP_DOWN_IND = 'D' AND HIERARCHY_TYPE = :p_type AND SECURITY_GROUP_ID = :p_sg;
  • SELECT PROCESS_ID, PARENT_CHILD_ID FROM AMW_PROC_HIERARCHY_DENORM WHERE UP_DOWN_IND = 'U' AND HIERARCHY_TYPE = :p_type ORDER BY PROCESS_ID;

These queries bypass recursive CONNECT BY logic, making them suitable for high-volume GRC dashboards and audit reporting.

Related Objects

The most directly related object is FND_SECURITY_GROUPS, joined via SECURITY_GROUP_ID, which enforces data access partitioning. Additional significant objects include the underlying non-denormalized process hierarchy source tables in the AMW schema that populate this structure, the process definition tables referenced by PROCESS_ID and PARENT_CHILD_ID, and the AMW Internal Controls Manager application APIs that regenerate the denormalized content when hierarchy definitions change. Because the metadata documents only one foreign key, related objects beyond FND_SECURITY_GROUPS are inferred from functional context rather than explicit constraint data.