Search Results mtl_item_org_categ_v




Overview
Explore in AI Chat

Overview

The APPS.MTL_ITEM_ORG_CATEG_V view exposes the relationship between inventory items and the category sets to which they are assigned at a specific organization level. It is a reporting and integration-oriented construct within the Oracle Inventory (INV) module, joining category assignment data held in MTL_ITEM_CATEGORIES with category set definitions, organization parameters, and control-level lookups. The documented ETRM metadata describes the object as "10SC ONLY," indicating that the view is restricted to a specific internal or vertical context (10SC) rather than a general-purpose public interface. Its status is VALID, and it resides in the APPS schema, which is consistent with the standard EBS convention of exposing reporting views through the APPS account rather than the base INV schema.

Underlying Base Objects

The view is defined over five documented objects, all referenced directly in its text. MTL_ITEM_CATEGORIES (SYNONYM) supplies the core item-to-category assignment rows, including INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_SET_ID, and CATEGORY_ID. MTL_CATEGORY_SETS_B (SYNONYM) provides the category set structure, notably STRUCTURE_ID and CONTROL_LEVEL. MTL_CATEGORY_SETS_TL (SYNONYM) supplies the translated CATEGORY_SET_NAME, filtered by USERENV('LANG') so that the name matches the session language. MTL_PARAMETERS (SYNONYM) contributes the ORGANIZATION_CODE and MASTER_ORGANIZATION_ID. Finally, MFG_LOOKUPS (VIEW) is joined with an outer-join to resolve the CONTROL_LEVEL code into a user-facing MEANING. The join conditions require that the category set identifier matches across MTL_ITEM_CATEGORIES, MTL_CATEGORY_SETS_B, and MTL_CATEGORY_SETS_TL, and that MTL_PARAMETERS.ORGANIZATION_ID equals MTL_ITEM_CATEGORIES.ORGANIZATION_ID.

Key Columns

  • ROW_ID — the ROWID of the underlying MTL_ITEM_CATEGORIES row.
  • INVENTORY_ITEM_ID — the item being categorized.
  • ORGANIZATION_ID — the organization in which the category assignment applies.
  • ORG_CODE — the organization code from MTL_PARAMETERS.
  • MASTER_ORGANIZATION_ID — the master organization for the item's organization.
  • CHILD_ORG_FLAG — a derived flag (0 or 1) indicating whether the row's organization differs from the master organization.
  • CONTROL_LEVEL — the decoded meaning of the category set control level, resolved through MFG_LOOKUPS for lookup type ITEM_CONTROL_LEVEL_GUI.
  • CATEGORY_SET_ID / CATEGORY_SET — the category set identifier and its translated name.
  • STRUCTURE_ID — the category structure to which the set belongs.
  • CATEGORY_ID — the specific category value assigned to the item.

Common Use Cases and Queries

Typical usage includes item categorization reporting, validation of category assignments against organization parameters, and integration extracts that require category context alongside organization and master organization identifiers. Because the view already resolves organization codes, translated category set names, control levels, and the child-organization flag, it removes the need for consumers to perform these joins independently.

Example query returning all category assignments for a given organization:

SELECT inventory_item_id,
       organization_id,
       category_set,
       category_id,
       control_level,
       child_org_flag
FROM   apps.mtl_item_org_categ_v
WHERE  organization_id = :org_id;

Example query listing master-organization assignments only:

SELECT inventory_item_id, org_code, category_set, category_id
FROM   apps.mtl_item_org_categ_v
WHERE  child_org_flag = 0;

Consumers should treat the view as read-only and avoid relying on it for anything other than its documented 10SC scope, since changes to the underlying INV base objects may affect its output in future patch levels.