Search Results serial_control

Overview

MTL_MATERIAL_STATUSES_VL is a multilingual (VL) view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is documented under the Inventory (INV) product family and is described in the ETRM metadata as a "Multilingual view for material status tables. - Used by WMS only." Material statuses define the inventory control attributes applied to material — such as whether it is reservable, whether it affects available-to-promise (ATP), and whether locator, lot, serial, LPN, or on-hand controls are enforced. The view presents a language-resolved, joined representation of the material status definition and its translatable descriptive text, which is the form most convenient for reporting, integration, and Warehouse Management System (WMS) processing.

Underlying Base Objects

The view is a join over two base synonyms: MTL_MATERIAL_STATUSES_B (the base table holding language-independent status attributes) and MTL_MATERIAL_STATUSES_TL (the translation table holding language-dependent text such as STATUS_CODE and DESCRIPTION). The view text confirms this structure with an inner join on STATUS_ID and a language filter:

SELECT ... FROM MTL_MATERIAL_STATUSES_TL T, MTL_MATERIAL_STATUSES_B B
WHERE B.STATUS_ID = T.STATUS_ID
  AND T.LANGUAGE = USERENV('LANG')

Because the join condition includes USERENV('LANG'), the view returns exactly one translated row per status for the session's current language. It exposes all B columns, the ROW_ID pseudocolumn (B.ROWID aliased as ROW_ID), and the translated STATUS_CODE and DESCRIPTION from T.

Key Columns

• STATUS_ID — Primary identifier linking the base and translation tables; the join key for status lookups.
• STATUS_CODE and DESCRIPTION — Translated, user-facing name and description of the status, drawn from the TL table.
• ENABLED_FLAG — Indicates whether the status is active and selectable for material assignment.
• RESERVABLE_TYPE — Controls the degree to which on-hand material in this status can be reserved.
• AVAILABILITY_TYPE — Determines nettable/available quantity treatment for planning and ATP.
• INVENTORY_ATP_CODE — Governs whether the status is included in ATP calculations.
• Control columns — LOCATOR_CONTROL, LOT_CONTROL, SERIAL_CONTROL, LPN_CONTROL, ZONE_CONTROL, and ONHAND_CONTROL specify which subinventory-level and item attributes are enforced when material carries the status.
• ATTRIBUTE1–ATTRIBUTE15 / ATTRIBUTE_CATEGORY — Descriptive flexfield segments for extensibility.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE identify the creating and updating user and concurrent program context.

Common Use Cases and Queries

The view is primarily consumed by WMS functionality and by reports or interfaces that need the current-language status code and description alongside the status control attributes. A typical lookup returning all enabled statuses is:

SELECT status_id, status_code, description,
       reservable_type, availability_type, enabled_flag
FROM   apps.mtl_material_statuses_vl
WHERE  enabled_flag = 'Y'
ORDER  BY status_code;

To resolve a specific status for a known material condition, filter by STATUS_ID:

SELECT status_code, description, locator_control,
       lot_control, serial_control, lpn_control
FROM   apps.mtl_material_statuses_vl
WHERE  status_id = :p_status_id;

For integration extracts into warehouse or planning systems, querying the control columns and ATP code together provides the full behavior profile of each status. Because the view enforces the session language, applications should ensure the invoking session's language environment is set correctly; otherwise the translation join may return no rows for statuses lacking text in that language. Substituting the base tables directly is sometimes preferred when a language-independent extract is required.