Search Results validate_flag_updateable_flag

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

• CATEGORY_SET_ID — Primary key of the category set; the join column for MTL_CATEGORIES_B, MTL_ITEM_CATEGORIES, and related objects.
• CATEGORY_SET_NAME / DESCRIPTION — Translated name and description from MTL_CATEGORY_SETS_TL.
• STRUCTURE_ID — Identifier of the underlying key flexfield structure, matching FND_ID_FLEX_STRUCTURES_VL.ID_FLEX_NUM.
• STRUCTURE_NAME — The flexfield structure name (for example, the commodity or product line structure) sourced from FIFS.ID_FLEX_STRUCTURE_NAME; this is the column users typically search for.
• CONTROL_LEVEL — Code controlling how many categories a single item may hold within the set; the value is typically 1 or 2.
• CONTROL_LEVEL_DISP — The translated meaning of the control level from MFG_LOOKUPS.
• VALIDATE_FLAG — Indicates whether category values assigned to items must be validated against the defined category list.
• DEFAULT_CATEGORY_ID — The default category used when an item is assigned to the set.
• MULT_ITEM_CAT_ASSIGN_FLAG, CONTROL_LEVEL_UPDATEABLE_FLAG, MULT_ITEM_CAT_UPDATEABLE_FLAG, VALIDATE_FLAG_UPDATEABLE_FLAG — Flags governing whether multiple item category assignments are permitted and whether each corresponding setting may be changed after creation.
• HIERARCHY_ENABLED — Indicates whether category hierarchy is enabled for the set.
• ROW_ID and audit columns — ROW_ID from MTL_CATEGORY_SETS_B plus CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and the concurrent program columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

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.