Search Results amw_org_hierarchy_denorm

Overview

The AMW_ORG_HIERARCHY_DENORM table is a denormalized structure within the AMW - Internal Controls Manager product of Oracle E-Business Suite (available in 12.1.1 and 12.2.2). Its purpose is to flatten and pre-join the process hierarchy across organizations, eliminating the need for recursive traversal of parent-child relationships at query time. This design supports performance-sensitive reporting and validation operations central to Internal Controls Manager, where users assess control coverage and process relationships across organizational units.

From a heuristic Data Vault modeling perspective, the table exhibits characteristics of a link table: it joins PROCESS_ID and ORGANIZATION_ID across hierarchical parent-child dimensions and carries descriptive context columns rather than serving as a pure hub or satellite. This classification is a modeling suggestion based solely on the unique index and FK structure; the physical table itself is a standard Oracle denormalized relational object stored under the AMW schema.

Key Information Stored

The table contains 12 documented columns. The most significant include:

• PROCESS_ID — Identifies the process node participating in the hierarchy.
• ORGANIZATION_ID — Identifies the organization to which the process relationship applies.
• PARENT_CHILD_ID — References the parent (or child) process node, enabling flattened traversal without recursion.
• UP_DOWN_IND — Direction indicator that specifies whether the record represents an upward or downward path in the process tree.
• HIERARCHY_TYPE — Distinguishes between hierarchy variants (for example, different process or control hierarchies maintained by the application).
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant data segregation.
• OBJECT_VERSION_NUMBER — Supports optimistic locking for concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS WHO columns providing audit lineage.

The table does not expose a single-column surrogate primary key in the documented metadata. Instead, the unique index AMW_ORG_HIERARCHY_DENORM_U1 on (PROCESS_ID, ORGANIZATION_ID, PARENT_CHILD_ID, UP_DOWN_IND, HIERARCHY_TYPE) serves as the composite business-key candidate, guaranteeing uniqueness of each flattened hierarchy path.

Common Use Cases and Queries

Denormalized hierarchies of this type are typically queried to enumerate ancestor or descendant relationships for a given process without recursive CONNECT BY operations. A representative pattern retrieves all descendant processes for an organization:

• Filter by ORGANIZATION_ID and HIERARCHY_TYPE, then filter UP_DOWN_IND to isolate direction (e.g., 'D' for down or 'U' for up).
• Join PARENT_CHILD_ID back to PROCESS_ID to expand the next level of nodes.
• Join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to restrict results to the user's authorized security group.

Typical reporting scenarios include generating control-coverage reports that aggregate processes by organization, validating that every process node has an assigned parent within the hierarchy, and producing audit documentation that lists complete upward or downward path chains. Because the table is denormalized, reports avoid the performance cost normally associated with recursive SQL over large process trees.

Related Objects

The most significant objects related to AMW_ORG_HIERARCHY_DENORM include:

• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for security filtering.
• AMW_PROCESSES (or the equivalent process definition entity) — joins on PROCESS_ID and PARENT_CHILD_ID to resolve process names and attributes.
• HR_ALL_ORGANIZATION_UNITS — resolves ORGANIZATION_ID to organization names.
• FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to user identities for audit reporting.
• FND_LOGINS — resolves LAST_UPDATE_LOGIN to session context.
• Internal Controls Manager concurrent programs and PL/SQL packages under the AMW schema that populate and refresh this denormalized structure whenever the underlying process hierarchy is modified.