Search Results padded_item_number




Overview
Explore in AI Chat

Overview

The view MTL_ITEM_FLEXFIELDS_OLD is an Inventory (INV) module database object that exposes a simplified, reporting-oriented projection of item master data. Its stated purpose within the ETRM documentation is to provide "concatenated item segments" alongside the INVENTORY_ITEM_ID. In practice, the view selects a small subset of the far larger column set held by the item master base table, presenting the item identifier, its description, its primary unit of measure, and two representations of the item number.

It is important to note a critical documented fact: this view is not implemented in the database as recorded in the ETRM metadata. Its inclusion in documentation is largely historical and referential. Reports or integrations that reference MTL_ITEM_FLEXFIELDS_OLD will fail against a standard Oracle EBS 12.1.1 or 12.2.2 installation unless the object has been manually created in the customer's schema. The name suffix "_OLD" further signals that it is a legacy or superseded construct, likely retained in documentation for backward-traceability rather than active runtime use.

Underlying Base Objects

The ETRM metadata documents no referenced base objects, but the view text makes the dependency explicit. The view is defined over a single base table:

  • MTL_SYSTEM_ITEMS_B — the core item master base table, holding organization-specific item definitions.

The view performs a column alias mapping rather than a join or aggregation. Specifically, it renames SEGMENT1 twice: once as ITEM_NUMBER and once as PADDED_ITEM_NUMBER. It also renames INVENTORY_ITEM_ID to ITEM_ID. Because no joins are present, the view returns one row per item per organization, consistent with the base table's granularity. There is no filter predicate in the documented view text, meaning it exposes all organization rows visible through the base table; organization-level security must be applied by the calling query or by Oracle's Multi-Org views layered above it.

Key Columns

  • ORGANIZATION_ID — the inventory organization to which the item row belongs.
  • ITEM_ID — aliased from INVENTORY_ITEM_ID; the unique surrogate identifier for the item within the organization. This is the column most integration logic keys on.
  • DESCRIPTION — the item's descriptive text from the item master.
  • PRIMARY_UOM_CODE — the primary stocking unit of measure for the item.
  • ITEM_NUMBER — aliased from SEGMENT1; the concatenated item number as stored in the first (and, in single-segment configurations, the only) key flexfield segment.
  • PADDED_ITEM_NUMBER — also aliased from SEGMENT1. Notably, in this documented view text the "padded" column is not actually padded; it is a direct duplicate of ITEM_NUMBER. A user searching for "padded_item_number" should understand that, per the ETRM view text, no padding transformation is applied. Genuine padded or zero-filled item numbers typically originate from a different source or a later, corrected view.

Common Use Cases and Queries

The original intent of the view was to give reporting tools a lightweight item lookup that avoided the wide column set of MTL_SYSTEM_ITEMS_B. A representative query, if the view existed, would be:

  • SELECT ORGANIZATION_ID, ITEM_ID, ITEM_NUMBER, PADDED_ITEM_NUMBER, DESCRIPTION, PRIMARY_UOM_CODE FROM MTL_ITEM_FLEXFIELDS_OLD WHERE ORGANIZATION_ID = :org_id;

Because the object is not implemented, the recommended practice in a live 12.1.1 or 12.2.2 environment is to query the base table directly, for example:

  • SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, SEGMENT1, DESCRIPTION, PRIMARY_UOM_CODE FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = :org_id AND SEGMENT1 = :item_number;

Typical consumers include custom item validation routines, interface staging programs where INVENTORY_ITEM_ID and SEGMENT1 must be resolved together, and legacy reports mentioning "padded" item numbers. Where zero-padding is genuinely required, it must be applied in the SQL layer, for instance via LPAD(SEGMENT1, n, '0'), since the documented view supplies no such formatting. Analysts should treat MTL_ITEM_FLEXFIELDS_OLD as a historical reference object and confirm its existence before relying on it.