Results for “edw_items_category_fkv”

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

Overview

The EDW_ITEMS_CATEGORY_FKV view is an Oracle E-Business Suite database object owned by the APPS schema and classified under the ENG (Engineering) product module. It is a reporting- and integration-oriented construct that presents item-to-category assignment information in a flattened, denormalized form suitable for data warehouse extraction, interface programs, and ad hoc reporting. Rather than requiring consumers to join the three underlying category tables manually, the view pre-joins MTL_CATEGORIES, MTL_ITEM_CATEGORIES, and MTL_CATEGORY_SETS and exposes a consolidated result set.

The name suffix _FKV follows the Oracle EBS convention for a "foreign key view," indicating that the object is designed to resolve and expose descriptive attributes associated with foreign key relationships—here, the linkage between an inventory item and the category sets and categories to which it is assigned. The view holds item category assignment information, making it a convenient source for reporting on how items are classified across category structures. Given its ENG module association, it is particularly relevant where engineering items and their categorization must be surfaced for downstream analytical or integration purposes. The object carries a VALID status in the documented environment spanning Oracle EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over three base tables, as reflected in its view text:

• MTL_CATEGORIES (aliased CAT) — supplies the category definition, joined on CATEGORY_ID.
• MTL_ITEM_CATEGORIES (aliased MIC) — the central assignment table linking inventory items to categories and category sets.
• MTL_CATEGORY_SETS (aliased SETS) — provides the category set definition, including its name and structural properties, joined on CATEGORY_SET_ID.

The join logic is: CAT.CATEGORY_ID = MIC.CATEGORY_ID and MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID. This correlates each item-category assignment to its owning category set and category record. Although the ETRM metadata notes no explicitly documented referenced base objects, the embedded view text authoritatively establishes these three tables as the sources of the view.

Key Columns

• INVENTORY_ITEM_ID — the unique identifier of the inventory item, sourced from MTL_ITEM_CATEGORIES. This is the primary item key.
• ORGANIZATION_ID — the inventory organization context in which the assignment applies.
• CATEGORY_SET_NAME — the descriptive name of the category set, retrieved from MTL_CATEGORY_SETS, identifying which classification structure the category belongs to.
• CONTROL_LEVEL — indicates the control level of the category set (for example, whether it is a master or organization-specific structure), retrieved from MTL_CATEGORY_SETS.
• CATEGORY_ID — the identifier of the assigned category.
• FUNCTIONAL_AREA_ID — documented as an exposed column, used to denote the functional area to which the category assignment relates.

Together, these columns allow a consumer to determine which items are assigned to which categories, within which category sets, and under what organizational and control-level context—without reconstructing the joins independently.

Common Use Cases and Queries

This view is typically used to extract item classification data for warehouse loads, cross-reference reports, and validation of category assignments. A representative query retrieving all assignments for a given organization is:

• SELECT inventory_item_id, organization_id, category_set_name, control_level, category_id FROM apps.edw_items_category_fkv WHERE organization_id = :org_id;
• Filtering by category set: SELECT inventory_item_id, category_id FROM apps.edw_items_category_fkv WHERE category_set_name = :set_name;
• Joining to MTL_SYSTEM_ITEMS_B on INVENTORY_ITEM_ID and ORGANIZATION_ID to report item segment descriptions alongside their categories.

Because the view resolves the category-set name and control level directly, it is well suited to integration scenarios where a target system must receive both the item and its human-readable classification context. Analysts should confirm the organization context and category set when querying, as the same item may hold assignments across multiple category sets and organizations.