Search Results eni_denorm_hrchy




Overview
Explore in AI Chat

Overview

APPS.ENI_DENORM_HRCHY is a denormalization utility package within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema and classified as an OTHER API. Its primary business function is to flatten and persist the hierarchical relationships inherent to Oracle Product Information Management (PIM) and Inventory item category structures into purpose-built denormalized staging and reporting tables. This transformation is essential because native EBS category hierarchies reside in normalized tables (such as MTL_CATEGORY_SETS_B and MTL_CATEGORIES_B) that are inefficient for high-volume reporting, Oracle Business Intelligence Enterprise Edition (OBIEE) extraction, and downstream star-schema analytics.

The package supports the Oracle Product Hub / Item Catalog denormalization architecture, populating tables such as ENI_DENORM_HIERARCHIES and ENI_DENORM_HRCHY_PARENTS so that full parent-child category paths can be queried without recursive SQL. It also synchronizes category assignments from item-to-category intersection data and maintains staging records used for bulk loading and incremental refresh. Because the object is VALID and referenced by four other packages (ENI_ITEMS_STAR_PKG, ENI_PROD_VALUESET, ENI_UPD_ASSGN, and ENI_UPGRADE_VSET), it functions as a shared dependency for item star-schema maintenance and value-set upgrade routines.

Key Procedures and Functions

The ETRM metadata documents eight procedures and functions:

  • GET_CATEGORY_SET_ID — Retrieves the identifier of the relevant category set, typically used as a lookup helper before hierarchy processing.
  • INSERT_INTO_STAGING — Loads denormalized hierarchy rows into the staging table prior to final merge or validation.
  • LOAD_PRODUCT_HIERARCHY — Orchestrates the extraction and flattening of the product category hierarchy into denormalized form.
  • GET_LAST_CATALOG_UPDATE_DATE — Returns the most recent catalog update timestamp, supporting incremental or delta-based refresh logic.
  • SYNC_CATEGORY_ASSIGNMENTS — Reconciles item-to-category assignments between source EBS tables and the denormalized structures.
  • SYNC_STAR_ITEMS_FROM_IOI — Populates the item star-schema from the Item Organizations Integration (IOI) source, ensuring analytical consistency.
  • SPLIT_CATEGORY_CODES — Parses concatenated or delimited category code strings into discrete components for hierarchical decomposition.
  • LOAD_OBIEE_HIERARCHY — Produces the hierarchy output specifically consumed by OBIEE reporting layers.

Tables Accessed

The package reads and writes across several functional groups. Denormalization targets include ENI_DENORM_HIERARCHIES, ENI_DENORM_HRCHY_PARENTS, ENI_DENORM_HRCHY_STG, and ENI_ICAT_CDENORM_HIERARCHIES. Master category sources include MTL_CATEGORIES_B, MTL_CATEGORIES_TL, MTL_CATEGORY_SETS_B, MTL_CATEGORY_SETS_TL, MTL_CATEGORY_SET_VALID_CATS, MTL_DEFAULT_CATEGORY_SETS, and MTL_ITEM_CATEGORIES. Item data is sourced from MTL_SYSTEM_ITEMS_B, while FND_LANGUAGES supports multilingual category descriptions. Administration tables AD_CTX_DDL and EGO_FND_DSC_FLX_CTX_EXT are used for context and descriptive flexfield definition handling. All access occurs through APPS synonyms, consistent with EBS coding standards.

Usage Notes

ENI_DENORM_HRCHY is an internal, non-API-classified package and should not be invoked directly by end users. It is typically executed by concurrent programs, scheduled data-refresh jobs, or higher-level packages such as ENI_ITEMS_STAR_PKG during item star-schema and OBIEE hierarchy rebuilds. Customizations should call only the documented entry points and avoid direct DML against the denormalized tables, since the package assumes controlled sequencing. Because it depends on STANDARD and SYS and is referenced by upgrade packages, it plays a role in patching and post-upgrade synchronization; administrators should validate denormalized results after upgrades or large item-catalog loads.