Search Results rhx_dp_p_category_set




Overview
Explore in AI Chat

Overview

RHX_DP_L_PRODFAM_V is a database view shipped within the Oracle E-Business Suite Master Scheduling/MRP (MRP) module. It belongs to the ETRM (Enterprise Territory and Resource Management) family of database objects, which are commonly associated with Oracle's Demantra demand planning integration and related supply chain planning extensions. The view presents a governed list of "product family" items — a classification used across the planning applications to group finished goods and other items into higher-level aggregates for forecasting, demand planning, and constraint-based scheduling.

The view's stated description in the ETRM metadata indicates that it is not implemented by default in the base database; it is typically created only when the corresponding ETRM/planning patch or configuration is applied. Its purpose is to return valid, currently active product family items that satisfy a configured category-set restriction, so that downstream planning logic and reports can consistently resolve which items qualify as planning product families.

Underlying Base Objects

The documented view definition references two base objects from the Oracle inventory schema: MTL_SYSTEM_ITEMS_KFV and MTL_ITEM_CATEGORIES.

  • MTL_SYSTEM_ITEMS_KFV — the key-flexfield version of the master item table, exposing CONCATENATED_SEGMENTS (the descriptive flexfield-concatenated item identifier) alongside descriptive, status, and lifecycle-column attributes.
  • MTL_ITEM_CATEGORIES — the item-to-category assignment table, which links items to categories within organization-specific category sets.

The view also invokes a helper function, RHX_DP.GET_PARAMETER, to resolve runtime configuration values — specifically 'RHX_DP_P_CATEGORY_SET' (the category set that identifies product families) and 'RHX_DP_P_DUMMY' (the label for an injected placeholder row). Because MTL_SYSTEM_ITEMS_KFV is a key flexfield view, the underlying physical structures include the master item base table and its flexfield segment columns.

Key Columns

The metadata documents three exposed columns:

  • PRODUCT_FAMILY_ITEM_ID — the inventory item identifier of the product family. In the union branch used for actual families this maps to MSI.INVENTORY_ITEM_ID; the "DUMMY" row returns the static value -999 to serve as a null/placeholder selection.
  • PRODUCT_FAMILY — the concatenated key flexfield segment value of the product family item, drawn from MSI.CONCATENATED_SEGMENTS.
  • DESCRIPTION — the item's descriptive text (MSI.DESCRIPTION); for the dummy row this is the literal 'DUMMY PRODUCT FAMILY'.

Common Use Cases and Queries

The view is typically consumed by planning and reporting logic that needs a clean, configuration-driven list of product family items. Filtering rules embedded in the view enforce that only items whose category assignment matches the configured category set, whose BOM_ITEM_TYPE equals 5 (a product family BOM item type), that are enabled, and that fall within their active item date range are returned.

A representative query lists all selectable product families:

  • SELECT PRODUCT_FAMILY_ITEM_ID, PRODUCT_FAMILY, DESCRIPTION FROM RHX_DP_L_PRODFAM_V ORDER BY PRODUCT_FAMILY;

A second common pattern excludes the injected dummy placeholder when only genuine families are required:

  • SELECT PRODUCT_FAMILY_ITEM_ID, PRODUCT_FAMILY FROM RHX_DP_L_PRODFAM_V WHERE PRODUCT_FAMILY_ITEM_ID <> -999;

Because the view relies on RHX_DP.GET_PARAMETER to resolve the active category set, results depend on the current ETRM configuration; if that parameter is unset or the category set does not exist, the query returns only the dummy row. This behavior should be validated whenever the view is used as a lookup source for demand planning or master scheduling reports.