Search Results amw_full_entity_hier_pk

Overview

AMW.AMW_FULL_ENTITY_HIER is a table in the Oracle E-Business Suite Internal Controls Manager (AMW) module. Its documented purpose is to store the full organization hierarchy structure, making it the authoritative repository for the flattened relationship between organizational entities across the enterprise. In the context of Oracle EBS 12.1.1 and 12.2.2, this table supports governance, risk, and compliance workflows by enabling controls, procedures, and assessments to be scoped to precise nodes within the organizational tree.

The object resides in the AMW schema and is marked VALID in the ETRM repository. Its primary key is AMW_FULL_ENTITY_HIER_PK, defined on the ENTITY_HIERARCHY_ID column, and it consists of 15 documented columns in the 12.1.1 physical schema. The heuristic Data Vault classification mined from the foreign-key structure is standalone, suggesting it functions as an independent structure rather than a classic hub, link, or satellite. In modeling terms, it most closely behaves as a hierarchical relationship table whose grain is one row per entity-to-parent association.

Key Information Stored

The table captures both the identity of each organizational node and its position in the hierarchy. The most significant columns are:

• ENTITY_HIERARCHY_ID — The surrogate primary key, uniquely identifying each hierarchy row.
• ENTITY_TYPE and ENTITY_ID — Identify the child entity, with the type discriminating the source object that the identifier references.
• OBJECT_TYPE and OBJECT_ID — Describe the generic object being represented at this hierarchy node.
• PARENT_OBJECT_TYPE and PARENT_OBJECT_ID — Define the parent node, establishing the edge of the hierarchy and enabling upward traversal.
• LEVEL_ID — A foreign key to MSD_LEVELS that classifies the depth or level of the node within the hierarchy.
• DELETE_FLAG — Supports logical deletion, allowing historical rows to be retained while excluding them from active reporting.
• OBJECT_VERSION_NUMBER — Enforces optimistic locking for concurrent updates.
• The standard WHO audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — provide change tracking and accountability.

While the metadata documents only the surrogate primary key, the combination of entity, object, and parent identifiers effectively serves as the business-key candidate for uniquely locating a node's position in the tree.

Common Use Cases and Queries

Typical usage centers on resolving organizational scope for controls, reporting rollups, and hierarchy traversal. A common query joins the hierarchy to its level definition to produce a labeled organizational tree:

• Direct reports of a parent: SELECT * FROM AMW.AMW_FULL_ENTITY_HIER WHERE PARENT_OBJECT_ID = :id AND DELETE_FLAG = 'N';
• Level enrichment: SELECT h.*, l.level_name FROM AMW.AMW_FULL_ENTITY_HIER h, MSD_LEVELS l WHERE h.LEVEL_ID = l.LEVEL_ID AND h.DELETE_FLAG = 'N';
• Scoping controls to an entity: filter assessment and control records by the ENTITY_ID values returned from this table.
• Audit trail review: use the creation and update columns to identify when hierarchy relationships were established or modified.

Related Objects

The documented foreign-key relationship ties this table directly to the level definitions used to classify hierarchy depth:

• MSD_LEVELS — Referenced through AMW_FULL_ENTITY_HIER.LEVEL_ID, providing the level name and classification for each node.
• AMW_FULL_ENTITY_HIER_PK — The primary key constraint on ENTITY_HIERARCHY_ID guaranteeing row uniqueness.
• AMW control, procedure, and assessment tables that consume ENTITY_ID to scope compliance activity to specific organizational units.
• Organization and entity definition tables referenced indirectly by ENTITY_TYPE, ENTITY_ID, OBJECT_TYPE, and OBJECT_ID.

Together these objects allow the flattened hierarchy to be interpreted, maintained, and applied consistently across Internal Controls Manager reporting and governance processes.