Results for “edw_item_prdfam_lcv”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The EDW_ITEM_PRDFAM_LCV view is an Enterprise Data Warehouse (EDW) reporting object delivered within the Oracle E-Business Suite Engineering (ENG) module. Its purpose is to expose product family information drawn from the inventory category hierarchy, presenting a flattened, denormalized representation suitable for extract, transform, and load (ETL) processing and analytical reporting. The suffix LCV denotes a "Local Canonical View," indicating that the object conforms to the EDW staging layer standard, which unifies source-system data into a consistent format for downstream consumption.

In the Oracle EBS 12.1.1 and 12.2.2 releases, this view serves as the canonical source for product family classification associated with items. Product families are a fundamental grouping construct used across manufacturing, planning, and order management. The view exists primarily to support the EDW rather than transactional processing and, per the documented ETRM metadata, is not implemented in the base database — it is deployed only in environments where the EDW schema has been installed.

Underlying Base Objects

Although the ETRM metadata records no documented base objects, the view text reveals its definition. It is constructed from a three-table equijoin:

• MTL_CATEGORIES_KFV — the key flexfield view over inventory categories, aliased CATS. This supplies the category identifier, concatenated segments, and description.
• MTL_CATEGORY_SETS — aliased SETS. This table links a structure ID to a category set name and provides the filter that restricts output to the PRODUCT FAMILY category set only.
• EDW_LOCAL_INSTANCE — aliased INST. This EDW-local table supplies the instance code used to qualify each row.

The join condition is CATS.STRUCTURE_ID = SETS.STRUCTURE_ID, combined with the literal predicate SETS.CATEGORY_SET_NAME = 'PRODUCT FAMILY'. The view is defined WITH READ ONLY, preventing DML through the view. Because MTL_CATEGORIES_KFV is itself a flexfield view, the category segments are resolved dynamically against the category key flexfield definition, meaning the concatenated segments reflect the descriptive flexfield configuration of the operating environment.

Key Columns

The view exposes seventeen columns, several of which are derived rather than stored:

• PROD_FAMILY_PK — a synthetic primary key formed by concatenating CATS.CATEGORY_ID with a hyphen and INST.INSTANCE_CODE, uniquely identifying a product family within an instance.
• INSTANCE — the instance code from EDW_LOCAL_INSTANCE, identifying the source EBS environment.
• ALL_FK — a constant literal 'ALL', used as a fixed foreign key for EDW dimension joins.
• PROD_FAMILY_DP — the first 240 characters of the concatenated category segments; a display value used for hierarchical presentation.
• NAME — the first 80 characters of the concatenated segments, serving as the short descriptive name.
• PRODUCT_FAMILY — the first 240 characters of the concatenated segments, providing the full product family label.
• DESCRIPTION, CREATION_DATE, LAST_UPDATE_DATE — carried directly from MTL_CATEGORIES_KFV.
• OPERATION_CODE and USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — exposed as NULL placeholders to maintain column parity with the EDW canonical schema.

Common Use Cases and Queries

The view is typically consumed by EDW extraction jobs and by ad hoc reporting requiring a clean product family dimension. A representative query returns all product families for a given source instance:

• SELECT PROD_FAMILY_PK, PRODUCT_FAMILY, DESCRIPTION FROM EDW_ITEM_PRDFAM_LCV WHERE INSTANCE = 'PROD';
• Joining to item-level EDW views via PROD_FAMILY_PK to attribute items to their product family for planning extracts.
• Filtering on LAST_UPDATE_DATE to identify product families created or modified since the previous ETL run.

Because the view is read-only and depends on the PRODUCT FAMILY category set existing in MTL_CATEGORY_SETS, environments that lack that category set will return no rows. Implementers should confirm the category set name and flexfield structure before relying on the view for reporting.