Search Results mtl_category_sets_v




Overview
Explore in AI Chat

Overview

MTL_CATEGORY_SETS_V is an Oracle E-Business Suite application view owned by the APPS schema within the Inventory (INV) product. The ETRM metadata classifies this object as 10SC ONLY, indicating that its exposure is restricted to that specific context rather than being a general-purpose public interface. Functionally, the view presents item category set definitions in a denormalized, user-facing form. A category set is the container that groups related item categories according to a chosen flexfield structure, such as commodity, product line, or buyer grouping. Rather than requiring report authors and integration developers to join the category set base tables to the flexfield structure view and the lookup view, MTL_CATEGORY_SETS_V resolves those relationships internally and exposes descriptive, translated values alongside the primary keys.

The view is read-only from a consumer standpoint and is intended for reporting and inquiry. It surfaces both the technical identifiers needed for joins and the display values needed for presentation, including the flexfield structure name, the translated control level meaning, and the several updateable-flag indicators used by the item category assignment user interface.

Underlying Base Objects

The view is defined over four documented base objects. MTL_CATEGORY_SETS_B is the base table holding the category set definition itself, including the structure identifier, control level, validation flag, and the updateable flags. MTL_CATEGORY_SETS_TL is its translation table, supplying the language-dependent category set name and description. Both are referenced through APPS synonyms in the documented metadata. FND_ID_FLEX_STRUCTURES_VL is the key flexfield structures view; the join restricts to APPLICATION_ID = 401 and ID_FLEX_CODE = 'MCAT', which is the item categories key flexfield, and returns ID_FLEX_STRUCTURE_NAME as STRUCTURE_NAME. MFG_LOOKUPS supplies the display meaning for the control level, joined on LOOKUP_TYPE = 'ITEM_CONTROL_LEVEL_GUI'. The translation join enforces LANGUAGE = USERENV('LANG') so that each session receives names and descriptions in its own language. Because MTL_CATEGORY_SETS_B is a non-translated base table, the view joins it to the translation table on CATEGORY_SET_ID.

Key Columns

Common Use Cases and Queries

Typical uses include listing the category sets associated with a flexfield structure, identifying which sets are hierarchy-enabled, and joining set definitions to item category assignments for reporting. The following query lists category sets and their structures:

  • SELECT category_set_id, category_set_name, structure_name, control_level_disp, hierarchy_enabled FROM apps.mtl_category_sets_v ORDER BY structure_name, category_set_name;
  • SELECT category_set_id, category_set_name FROM apps.mtl_category_sets_v WHERE structure_name = :structure_name;
  • SELECT v.category_set_id, v.category_set_name, m.category_id, m.item_id FROM apps.mtl_category_sets_v v, apps.mtl_item_categories m WHERE v.category_set_id = m.category_set_id AND v.structure_id = m.structure_id;

Because the view resolves the flexfield structure join internally, it is the preferred source whenever structure_name must appear in output without writing the FND_ID_FLEX_STRUCTURES_VL join explicitly.