Search Results prod_cat_hrchy_id

Overview

CSS_DEF_PROD_CAT_HRCHY is a table within the CSS – Support product module of Oracle E-Business Suite. Its documented description is "Product Category Hierarchy." The table is flagged as part of CSS – Support, a module that Oracle classifies as obsolete, and the ETRM metadata explicitly notes that the object is "Not implemented in this database." Consequently, in both Oracle EBS 12.1.1 and 12.2.2 environments, the table is generally absent from a standard installation, and references to it typically surface only in legacy documentation, migration analysis, or customizations inherited from much earlier releases.

Functionally, the object was intended to model a parent-child relationship among product categories, effectively a tree or hierarchy of category nodes. Under the heuristic Data Vault classification derived from the mined foreign key structure, the table is best understood as a link entity: it connects category records to one another rather than storing descriptive attributes of a category itself. Modeling this as a link is a suggestion only, consistent with the observation that each row associates a category to a parent category.

Key Information Stored

The documented metadata identifies a small and specific column set. The most significant elements are:

• PROD_CAT_HRCHY_ID – The surrogate primary key of the table, defined through the constraint CSS_DEF_PROD_CAT_HRCHY_PK. It uniquely identifies each hierarchy edge or node record.
• CATEGORY_ID – A foreign key referencing MTL_CATEGORIES_B, representing the child or subject category in the hierarchy relationship.
• PARENT_CATEGORY_ID – A foreign key referencing MTL_CATEGORIES_B, representing the parent category in the same hierarchy.

No unique business-key index beyond the primary key is documented in the provided metadata. Because CATEGORY_ID and PARENT_CATEGORY_ID both resolve to the same foreign table, the table encodes a self-referencing structure at the business level, with PROD_CAT_HRCHY_ID serving solely as the technical row identifier.

Common Use Cases and Queries

Where such a structure exists, the primary use case is traversing or reporting on category roll-ups: identifying the parent of a given category, listing all children of a category, or building full-path descriptions for reporting on inventory and item categorization. A representative pattern joins to MTL_CATEGORIES_B twice to resolve both ends of the relationship:

• SELECT h.prod_cat_hrchy_id, c.category_name AS child_category, p.category_name AS parent_category FROM css_def_prod_cat_hrchy h, mtl_categories_b c, mtl_categories_b p WHERE h.category_id = c.category_id AND h.parent_category_id = p.category_id;
• Filtering by a specific PROD_CAT_HRCHY_ID to inspect a single hierarchy edge.
• Recursive traversal (CONNECT BY PRIOR) rooted at PARENT_CATEGORY_ID to reconstruct the full tree.

Given the obsolete status and the "not implemented" note, any development effort should confirm the table's physical existence in the target instance before relying on these queries; equivalent hierarchy logic in current releases is typically served through MTL_CATEGORY_SETS, MTL_CATEGORIES_B, and related category-set assignment structures.

Related Objects

The most significant related objects are those tied to the documented foreign keys and the supporting category model:

• MTL_CATEGORIES_B – The base category table; referenced twice via CATEGORY_ID and PARENT_CATEGORY_ID.
• MTL_CATEGORIES_TL – Provides translated category names and descriptions for reporting joins.
• MTL_CATEGORY_SETS and MTL_CATEGORY_SET_VALID_CATS – Define and validate category membership within sets.
• MTL_ITEM_CATEGORIES – Associates inventory items to categories, enabling roll-up reporting.
• CSS_DEF_PROD_CAT_HRCHY_PK – The primary key constraint governing PROD_CAT_HRCHY_ID.

Because the table is documented as obsolete and not implemented, joins to these objects should be treated as design-level references rather than verified runtime dependencies in 12.1.1 or 12.2.2.