Search Results mtl_category_sets_vl




Overview
Explore in AI Chat

Overview

MTL_CATEGORY_SETS_VL is a multilingual (ML) view owned by the APPS schema in Oracle E-Business Suite, validated across both 12.1.1 and 12.2.2. It belongs to the Oracle Inventory (INV) product family and is documented as the "Category Sets multilingual view." Its purpose is to present category set definitions from the Inventory category set tables in the language of the current session, joining the base transactional table to its translation table so that the human-readable name and description are surfaced alongside the operational attributes. In reporting and integration contexts, MTL_CATEGORY_SETS_VL is the conventional entry point for queries that require the translated CATEGORY_SET_NAME rather than the untranslated base row, and it avoids the need for callers to construct the join between the _B and _TL tables manually.

Underlying Base Objects

The view text exposes a straightforward two-table join. The B alias resolves to the synonym MTL_CATEGORY_SETS_B, which holds the language-independent, single-row-per-category-set definition. The T alias resolves to the synonym MTL_CATEGORY_SETS_TL, the translation table holding one row per installed language. Both are documented as referenced base objects for this view, and both are exposed under the APPS schema.

The join condition is B.CATEGORY_SET_ID = T.CATEGORY_SET_ID AND T.LANGUAGE = USERENV('LANG'). The USERENV('LANG') predicate restricts the translation rows to the language of the current database session, which is the mechanism that makes this a multilingual view. Because MTL_CATEGORY_SETS_TL stores the translatable CATEGORY_SET_NAME and DESCRIPTION, the view effectively returns one row per category set with the session-appropriate translated text attached. The view retains B.ROWID as ROW_ID, a legacy construct preserved from the original definition that is not generally used as a stable identifier for application logic.

Key Columns

The view exposes all operational columns from the base table plus the translated text. The primary key is CATEGORY_SET_ID. STRUCTURE_ID identifies the category structure (for example, item categories) to which the set applies and therefore determines which category values are eligible. VALIDATE_FLAG and CONTROL_LEVEL govern validation behavior and the level at which the category assignment is controlled (for example, item versus item master). DEFAULT_CATEGORY_ID points to the default category for the set. HIERARCHY_ENABLED indicates whether the underlying structure supports hierarchical category queries.

The MULT_ITEM_CAT_ASSIGN_FLAG, CONTROL_LEVEL_UPDATEABLE_FLAG, MULT_ITEM_CAT_UPDATEABLE_FLAG, and VALIDATE_FLAG_UPDATEABLE_FLAG columns indicate the mutability and assignment behavior of the corresponding attributes. Standard who-columns are present: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN, along with the concurrent program columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE. The translated columns CATEGORY_SET_NAME and DESCRIPTION are the ones most commonly consumed by reports and interfaces.

Common Use Cases and Queries

Typical uses include LOV/report population, inventory setup validation, and interface extraction where the display name of a category set is required in the user's language.

  • Listing all category sets with translated names:
    SELECT category_set_id, category_set_name, structure_id, control_level FROM mtl_category_sets_vl ORDER BY category_set_name;
  • Finding the default category set for a structure or locating a set by name:
    SELECT category_set_id, default_category_id FROM mtl_category_sets_vl WHERE category_set_name = :name;
  • Checking hierarchy-capable sets: SELECT category_set_id, category_set_name FROM mtl_category_sets_vl WHERE hierarchy_enabled = 'Y';

Because the join is driven by USERENV('LANG'), callers in multilingual environments should confirm their session language resolves to an installed translation; otherwise the view returns no rows for that category set. Teams requiring all languages should query the underlying _B and _TL tables separately.