Search Results sys_c00248510

Overview

AMW_ENTITY_HIERARCHIES is a transactional configuration table that belongs to the Oracle Internal Controls Manager (ICM) module, internally identified by the application short name AMW. The object is flagged as obsolete in Oracle E-Business Suite 12.1.1 and 12.2.2, meaning it is retained only for backward compatibility and is not implemented in current databases. Its documented purpose is to store organization hierarchies that drive three distinct certification contexts: process certification, financial certification, and the engagement lifecycle. Each row therefore defines a node within an organizational tree used to scope who certifies what, and to which parent the node rolls up.

Under the heuristic Data Vault classification supplied in the ETRM metadata, the table is modelled as a standalone entity, meaning it carries no outbound foreign keys other than the documented reference to MSD_LEVELS, and no other table references it through a declared FK. In practice this suggests treating AMW_ENTITY_HIERARCHIES as a self-contained hub-like structure rather than as a dependent satellite of a larger certification fact table.

Key Information Stored

The documented physical schema contains 15 columns owned by the AMW schema. The primary key constraint SYS_C00248510 is enforced on ENTITY_HIERARCHY_ID, which is the surrogate identifier for each hierarchy node. Business-key candidates are not separately documented as unique indexes, so ENTITY_HIERARCHY_ID remains the only guaranteed unique access path.

• ENTITY_HIERARCHY_ID — surrogate primary key, uniquely identifying each hierarchy row.
• ENTITY_TYPE and ENTITY_ID — describe the certified entity participating in the hierarchy and its identifier.
• OBJECT_TYPE and OBJECT_ID — describe the object (often the engagement or certification object) to which the entity node applies.
• PARENT_OBJECT_TYPE and PARENT_OBJECT_ID — the polymorphic self-referencing parent pointer that reconstructs the hierarchy tree; the pairing of type and ID avoids a hard FK.
• LEVEL_ID — foreign key into MSD_LEVELS, giving the node its position or level in the org structure.
• DELETE_FLAG — soft-delete indicator used instead of physical row removal.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Historically, this table was queried to render the certification org chart and to resolve the roll-up path from a leaf entity to its top-level parent. A typical pattern retrieves all children of a given parent:

• SELECT ENTITY_HIERARCHY_ID, ENTITY_ID, LEVEL_ID FROM AMW_ENTITY_HIERARCHIES WHERE PARENT_OBJECT_ID = :p AND DELETE_FLAG = 'N';
• Reporting queries joined AMW_ENTITY_HIERARCHIES to MSD_LEVELS on LEVEL_ID to label hierarchy depth.
• Concatenated-row filtering on ENTITY_TYPE / OBJECT_TYPE segmented process versus financial certification hierarchies.

Because the module is obsolete, these queries are relevant only for historical data extraction or migration.

Related Objects

• MSD_LEVELS — referenced via AMW_ENTITY_HIERARCHIES.LEVEL_ID; supplies the level definition for each node.
• AMW_ENTITY_HIERARCHIES (self) — logical parent/child linkage through PARENT_OBJECT_TYPE and PARENT_OBJECT_ID.
• Other AMW certification and engagement tables that consume ENTITY_ID and OBJECT_ID in the obsolete ICM data model.