Search Results mtl_categories_kfv

Overview

CSS_DEF_PROD_CAT_HRCHY_V is a view belonging to the CSS – Support product family, which Oracle has documented as obsolete in Oracle EBS 12.1.1 and 12.2.2. The view presents the product category hierarchy retained in the CSS_DEF_PROD_CAT_HRCHY table, resolving both the category and its parent category into human-readable concatenated segment names by joining against the key flexfield view MTL_CATEGORIES_KFV. It therefore serves as the reporting and integration surface for hierarchical product category relationships defined within the CSS (Customer Support / Service) schema.

Functionally, the view flattens a parent-child relationship between categories into a single row containing both sides of the link, which simplifies hierarchy traversal for reporting layers, concurrent programs, and interfaces that need category roll-up without embedding self-joins against the key flexfield views themselves. Because the object is obsolete and the ETRM notes state it is "Not implemented in this database," the view should be regarded as a legacy artifact and not relied upon for new development.

Underlying Base Objects

The view text defines two referenced objects:

• CSS_DEF_PROD_CAT_HRCHY HRCHY — the primary table supplying the hierarchy records (the driving table in the FROM clause).
• MTL_CATEGORIES_KFV SUB and MTL_CATEGORIES_KFV PARENT — the key flexfield view, used twice (aliased SUB and PARENT) to translate numeric CATEGORY_ID and PARENT_CATEGORY_ID values into CONCATENATED_SEGMENTS strings. The PARENT join is an outer join (+), so rows whose parent category is null or unresolved are still returned.

The join predicates are HRCHY.CATEGORY_ID = SUB.CATEGORY_ID and HRCHY.PARENT_CATEGORY_ID = PARENT.CATEGORY_ID (+). No additional base objects are documented in the ETRM metadata; the two tables above constitute the complete definition.

The linkage to MTL_CATEGORIES_KFV is significant because that view resolves the category key flexfield (the "Category" KFF) into concatenated descriptive segment strings — the same KFF surface referenced by the user's search term, mtl_categories_kfv.

Key Columns

• PROD_CAT_HRCHY_ID — primary identifier of the hierarchy row.
• CATEGORY_ID — the child (sub) category identifier.
• CATEGORY_NAME — concatenated flexfield segments for the child category (SUB.CONCATENATED_SEGMENTS).
• PARENT_CATEGORY_ID — identifier of the parent category, if any.
• PARENT_CATEGORY_NAME — concatenated segments for the parent category (PARENT.CONCATENATED_SEGMENTS).
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective dating of the hierarchy relationship.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE1 … ATTRIBUTE15 and CONTEXT — descriptive flexfield columns carried from the base hierarchy table.

Common Use Cases and Queries

Typical usage is to enumerate the category hierarchy with names rather than IDs, or to report parent-child relationships for CSS product categories. A representative query:

SELECT CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID, PARENT_CATEGORY_NAME FROM CSS_DEF_PROD_CAT_HRCHY_V WHERE START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE;

Because PARENT is outer-joined, root categories return a null PARENT_CATEGORY_NAME. Any use should be validated against the site's actual schema, since the metadata records the object as obsolete and not implemented.