Search Results child_org_flag




Overview
Explore in AI Chat

Overview

The APPS.MTL_ITEM_ORG_CATEG_V view is a denormalized reporting object that joins inventory item-to-category assignments against organization parameters, category set definitions, and Oracle's flexible lookup framework. In Oracle EBS 12.1.1 and 12.2.2, it exposes each row of MTL_ITEM_CATEGORIES enriched with the organization code, the master organization identifier, a decoded master-organization flag, the category set name and structure, and the human-readable meaning of the category set's control level. The view derives the control level by resolving MCS.CONTROL_LEVEL against the MFG_LOOKUPS lookup type ITEM_CONTROL_LEVEL_GUI, which is the same lookup used by the Item Categories form (the Item Control Level field). Because the object is owned by APPS and joins only synonymed or view-based base objects, it is safe for read-only reporting and integration use and is a natural source for extracts that must correlate item categorization with the organization's master-organization model.

Underlying Base Objects

The documented metadata lists five referenced base objects. MTL_ITEM_CATEGORIES (SYNONYM) supplies the item-to-category assignment rows (inventory_item_id, organization_id, category_set_id, category_id). MTL_PARAMETERS (SYNONYM) supplies the organization context and is used to project ORGANIZATION_CODE and MASTER_ORGANIZATION_ID. MTL_CATEGORY_SETS_B (SYNONYM) supplies STRUCTURE_ID and CONTROL_LEVEL for each category set, while MTL_CATEGORY_SETS_TL (SYNONYM) supplies the translated CATEGORY_SET_NAME, filtered to the session language via MCST.LANGUAGE = USERENV('LANG'). MFG_LOOKUPS (VIEW) is outer-joined on LOOKUP_TYPE = 'ITEM_CONTROL_LEVEL_GUI' and LOOKUP_CODE = MCS.CONTROL_LEVEL to translate the internal control-level code into its displayed meaning. The joins are conformed on CATEGORY_SET_ID, ORGANIZATION_ID, and the lookup key respectively, producing one row per item/category/organization combination.

Key Columns

  • ROW_ID — the ROWID of the underlying MTL_ITEM_CATEGORIES row; useful for uniquely identifying the assignment.
  • INVENTORY_ITEM_ID, ORGANIZATION_ID — the item and the organization in which the category assignment applies.
  • ORGANIZATION_CODE — the organization's short code from MTL_PARAMETERS.
  • MASTER_ORGANIZATION_ID — the master organization that governs the item.
  • DECODE(MIC.ORGANIZATION_ID, MP.MASTER_ORGANIZATION_ID, 0, 1) — an unnamed flag returning 0 when the row belongs to the master organization and 1 otherwise.
  • CONTROL_LEVEL — the translated MEANING of the category set's control level (for example, the level governing whether the category is set at master or organization level).
  • 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 assigned category within the set.

Common Use Cases and Queries

The view is typically used for item category extracts, master-versus-organization reconciliation, and integration feeds that need readable control-level and category-set names rather than raw IDs. A representative query joins it to MTL_SYSTEM_ITEMS_B for descriptive item attributes:

  • Listing all categories assigned to an item in a given organization, including the control level: SELECT inventory_item_id, organization_code, category_set, control_level, category_id FROM apps.mtl_item_org_categ_v WHERE organization_id = :org_id AND inventory_item_id = :item_id;
  • Confirming which assignments belong to the master organization versus a child organization using the decoded flag.
  • Feeding downstream systems with the category set name and control level for a specific structure via STRUCTURE_ID.
  • Auditing category set usage across organizations by grouping on CATEGORY_SET and CONTROL_LEVEL.

Because the lookup join is outer (+), items whose control level has no matching lookup row still appear, with a null CONTROL_LEVEL, which is important when validating lookup configuration.