Search Results product_family_item_id




Overview
Explore in AI Chat

Overview

RHX_DP_R_PRODFAM_ITEMORG_V is a custom reporting view delivered within the Oracle E-Business Suite environment, catalogued under the MRP - Master Scheduling/MRP product family. It is a denormalized, reporting-oriented construct that flattens the relationship between Oracle product families, their member items, and the organizations in which those items are defined. The view is designed to supply downstream extraction processes with a single, join-ready projection of product family membership at the item-organization level, which is a common requirement for demand planning, forecasting, and ASCP data-pull routines where product family hierarchies must be resolved against inventory items.

The view is a custom (RHX-prefixed) object rather than a seeded Oracle view, and it depends on RHX_DP_L_PRODFAM_V, a sibling custom view that resolves the product family definition, as well as on the standard key flexfield view MTL_SYSTEM_ITEMS_KFV. The "DP" naming segment indicates a data-pull or demand-planning context, and the "R" prefix denotes a reporting view. The view is designed to be consumed by external or staging processes rather than by interactive forms.

Underlying Base Objects

The view is defined over two documented source objects. The first is RHX_DP_L_PRODFAM_V, aliased PFM, which supplies the product family identifier and the family name. The second is MTL_SYSTEM_ITEMS_KFV, aliased MSI, the concatenated-segment key flexfield view over MTL_SYSTEM_ITEMS, which supplies organization, inventory item, segment concatenation, and the product family item reference. No ETRM reference lists base tables directly; all joins are performed against these view-level objects.

Three predicates control membership. The product family linkage is resolved with DECODE(MSI.PRODUCT_FAMILY_ITEM_ID, NULL, -999, MSI.PRODUCT_FAMILY_ITEM_ID) = PFM.PRODUCT_FAMILY_ITEM_ID, so items with a null family reference are compared against the sentinel value -999 rather than being silently excluded. Items with BOM_ITEM_TYPE = 5 are filtered out, removing product family parent items themselves from the member list. Only enabled items (ENABLED_FLAG = 'Y') are retained. A NOT EXISTS subquery against a second instance of MTL_SYSTEM_ITEMS_KFV suppresses rows where an item carries no family on the outer row but a non-null family on another row with the same inventory item, preventing duplicate or stale family associations. The DISTINCT keyword collapses the resulting row set.

Key Columns

Common Use Cases and Queries

The view is typically used to populate staging tables for demand planning or forecasting, to validate product family membership coverage before an ASCP or MRP data collection run, and to reconcile custom product family hierarchies against standard MTL_SYSTEM_ITEMS data. Because ITEM_ORG embeds the organization identifier, the view is convenient for building composite keys without additional joins.

A representative query listing all members of a given family is:

SELECT product_family,
       item_org,
       item_org_item_id,
       item_org_organization_id
FROM   rhx_dp_r_prodfam_itemorg_v
WHERE  product_family = :family_name
ORDER  BY item_org;

To count family membership by organization:

SELECT item_org_organization_id,
       product_family,
       COUNT(*) member_count
FROM   rhx_dp_r_prodfam_itemorg_v
GROUP  BY item_org_organization_id, product_family;

To locate items that are not assigned to any family, the custom view is queried in the opposite direction against MTL_SYSTEM_ITEMS_KFV, since the NOT EXISTS filter already excludes conflicting assignments from its own output. In all cases the view should be treated as read-only reporting infrastructure.