Search Results mtl_system_items_vl

Overview

MTL_SYSTEM_ITEMS_VL is a multilingual (ML) view owned by the APPS schema in Oracle E-Business Suite. It is part of the INV – Inventory product and exposes item master definition data for reporting, integration, and inquiry purposes. As indicated by the "VL" suffix convention used throughout EBS, this is a "_VL" view that combines the base (non-translated) columns of the item master with translated description columns sourced from a translation table. Its distinguishing feature is a concatenated segment column, reflecting the concatenated key flexfield (KFF) representation of the item, commonly known as the "Item" flexfield segment string.

The view presents one row per inventory item per organization, drawing on the enabled item master organization contexts. Because it is a standard, supported Oracle view rather than a custom object, the columns exposed are aligned with the underlying base objects and remain a stable reference point across the 12.1.1 and 12.2.2 releases.

Underlying Base Objects

The documented metadata identifies two referenced base objects for MTL_SYSTEM_ITEMS_VL:

• MTL_SYSTEM_ITEMS_B_KFV (VIEW) – the base item master key flexfield view. This object supplies the item attributes (inventory_item_id, organization_id, flags, controls, cost and purchasing attributes, etc.) along with the concatenated flexfield segment column that gives MTL_SYSTEM_ITEMS_VL its concatenated segment column characteristic.
• MTL_SYSTEM_ITEMS_TL (SYNONYM) – the item master translation table (synonym). This supplies translated columns such as the item description and long description, which are dependent on the session's language setting (MLS / language context).

The view text shows the base attribute columns aliased with the prefix "B." (for example, B.ROW_ID, B.INVENTORY_ITEM_ID, B.ORGANIZATION_ID, B.PRIMARY_UOM_CODE, B.ITEM_TYPE, B.INVENTORY_ITEM_STATUS_CODE) and joins the translated description information for the current language. In practice, MTL_SYSTEM_ITEMS_B_KFV is itself defined over the base table MTL_SYSTEM_ITEMS_B, so the VL view ultimately presents a multilingual projection of the same item data used by the base table.

Key Columns

The view exposes a broad set of item definition columns. Selected important columns include:

• INVENTORY_ITEM_ID – the unique internal identifier of the item.
• ORGANIZATION_ID – the organization context in which the item is defined.
• SEGMENT1 … SEGMENTn / concatenated segment column – the item key flexfield segments and their concatenated representation, enabling users to display or search by item number without separately querying the base KFF view.
• DESCRIPTION / LONG_DESCRIPTION – translated item descriptions subject to the language setting.
• PRIMARY_UOM_CODE, PRIMARY_UNIT_OF_MEASURE – the item's primary unit of measure.
• ITEM_TYPE – standard versus other item type classification.
• INVENTORY_ITEM_STATUS_CODE – active/inactive status.
• INVENTORY_ITEM_FLAG, STOCK_ENABLED_FLAG, MTL_TRANSACTIONS_ENABLED_FLAG – inventory enablement indicators.
• BOM_ENABLED_FLAG, BOM_ITEM_TYPE, ENG_ITEM_FLAG – bills of material and engineering attributes.
• PURCHASING_ITEM_FLAG, PURCHASING_ENABLED_FLAG, PURCHASING_TAX_CODE, BUYER_ID, UNIT_OF_ISSUE – purchasing-related attributes.
• COSTING_ENABLED_FLAG, INVENTORY_ASSET_FLAG, COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, ASSET_CATEGORY_ID – costing attributes.
• SHFEL_LIFE_CODE, SHELF_LIFE_DAYS, LOT_CONTROL_CODE, SERIAL_NUMBER_CONTROL_CODE, LOCATION_CONTROL_CODE – lot, serial, and locator controls.
• ALLOWED_UNITS_LOOKUP_CODE, ITEM_CATALOG_GROUP_ID, CATALOG_STATUS_FLAG – catalog and unit-of-measure lookup attributes.

Common Use Cases and Queries

MTL_SYSTEM_ITEMS_VL is frequently used when a report or integration must display the item number (concatenated segments) together with the translated description, without manually joining the KFF view and the translation table. Typical scenarios include item inquiry reports, purchasing and receiving interfaces, BOM and routing reports, and multilingual implementations where descriptions must respect each user's language.

Sample SQL:

• List items with segment and translated description:
  SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, SEGMENT1, DESCRIPTION FROM MTL_SYSTEM_ITEMS_VL WHERE ORGANIZATION_ID = :org_id;
• Find active stock-enabled items:
  SELECT INVENTORY_ITEM_ID, SEGMENT1, DESCRIPTION FROM MTL_SYSTEM_ITEMS_VL WHERE ORGANIZATION_ID = :org_id AND INVENTORY_ITEM_STATUS_CODE = 'Active' AND STOCK_ENABLED_FLAG = 'Y';
• Join to item transactions:
  SELECT t.TRANSACTION_ID, v.SEGMENT1, v.DESCRIPTION FROM MTL_MATERIAL_TRANSACTIONS t, MTL_SYSTEM_ITEMS_VL v WHERE v.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID AND v.ORGANIZATION_ID = t.ORGANIZATION_ID;

Because translation columns depend on the session language, queries executed in different language environments may return different description values while the base item attributes remain identical. This behavior should be considered when comparing query output across environments.