Search Results ic_item_hierarchy




Overview
Explore in AI Chat

Overview

GMI.IC_ITEM_HIERARCHY is a Process Manufacturing Inventory table that stores hierarchical information used by the Item Activation Workflow. In Oracle EBS 12.1.1 and 12.2.2, the Item Activation Workflow governs the controlled creation and release of new item definitions. Rather than allowing any user to create items freely, the workflow routes item creation to designated approvers. IC_ITEM_HIERARCHY is the configuration table that defines the approval hierarchy: it records which user is the creator (originator) of items and which user is that creator's approver (supervisor). This one-record-per-creator mapping allows the workflow engine to determine the correct approval path whenever an item is submitted for activation.

The table resides in the GMI (Process Manufacturing) schema and is classified as VALID in the ETRM metadata. Its documented Data Vault classification is standalone, meaning it sits independently in the mined foreign-key structure with no references to other GMI tables. As a modeling suggestion, this would position it closer to a reference or lookup satellite than a hub or link: the natural business key is the creator user, and the descriptive payload is the supervisor assignment. The heuristic classification should be treated as an observation about FK topology, not a formal constraint on how the table is designed.

Key Information Stored

The table is documented with seven physical columns and a single-column primary key. The most significant are:

  • CREATOR_USER_NAME — Identifies the user who creates items. This column is both the primary key (IC_ITEM_HIERARCHY_PK) and the only documented unique-index candidate, making it the singular business-key surrogate in practice. Each creator can appear at most once.
  • SUPERVISOR_USER_NAME — Identifies the user designated as the approver for the corresponding creator. At runtime, the Item Activation Workflow consults this value to determine where the approval notification should be routed.
  • CREATION_DATE — Audit timestamp recording when the hierarchy assignment row was inserted.
  • CREATED_BY — The application user who originally created the hierarchy record.
  • LAST_UPDATE_DATE — Audit timestamp of the most recent modification to the supervisor assignment.
  • LAST_UPDATED_BY — The user who last changed the record.
  • LAST_UPDATE_LOGIN — The login/session identifier associated with the last update, useful for tracking the originating responsibility or EBS session.

Because the primary key is a natural business identifier (the user name) rather than a generated surrogate, the table is compact and not historically versioned; changes to a creator's supervisor are captured only through the standard WHO audit columns.

Common Use Cases and Queries

Typical uses center on resolving, maintaining, and auditing the item-approval chain.

  1. Resolving the approver for a creator: 
    SELECT supervisor_user_name
FROM   gmi.ic_item_hierarchy
WHERE  creator_user_name = :p_creator;
  2. Listing all approval assignments: 
    SELECT creator_user_name, supervisor_user_name
FROM   gmi.ic_item_hierarchy
ORDER  BY creator_user_name;
  3. Identifying orphaned or self-referencing assignments where a creator is also their own supervisor, which can stall the workflow: 
    SELECT creator_user_name, supervisor_user_name
FROM   gmi.ic_item_hierarchy
WHERE  creator_user_name = supervisor_user_name;
  4. Audit reporting on who changed the approval hierarchy over a period, using LAST_UPDATE_DATE and LAST_UPDATED_BY.

Reporting tends to focus on workflow configuration health rather than transactional volume, since the table is a setup table with low row counts. Verifying that every active item creator appears in this table is a common pre-go-live checklist item.

Related Objects

The metadata documents no foreign-key relationships, so IC_ITEM_HIERARCHY is effectively self-contained. Nevertheless, it interacts with the following objects in the broader Item Activation Workflow:

  • IC_ITEM_HIERARCHY_PK — the primary key constraint on CREATOR_USER_NAME; also the only unique-index candidate.
  • FND_USER — referenced implicitly via CREATOR_USER_NAME and SUPERVISOR_USER_NAME to resolve user details.
  • IC_ITEM_CATALOG / IC_ITEM_MASTER (GMI) — the item definitions that flow through the Item Activation Workflow this table configures.
  • IC_ITEM_ACTIVATION — the process that reads this hierarchy to determine the approval path.
  • WF_ITEM_ACTIVITY_STATUSES — the Oracle Workflow runtime table where the resulting approval notifications are recorded.

Because no FK constraints are documented, joins to user tables should be validated against actual data rather than assumed referential integrity.