Search Results mtl_system_items_er4_v




Overview
Explore in AI Chat

Overview

MTL_SYSTEM_ITEMS_ER4_V is a view owned by the APPS schema in Oracle E-Business Suite, classified under the INV (Inventory) product family. The object is documented in the E-Business Suite Technical Reference Manual (ETRM) for releases 12.1.1 and 12.2.2 with a status of VALID. Its stated purpose is to support the ERES (Electronic Records and Electronic Signatures) framework for the Items Organization Assignment business function.

In practice, the view presents a denormalized, presentation-ready projection of item-to-organization assignments. Rather than exposing raw foreign keys and unlabelled codes, it resolves organization identifiers to their translated names and codes and converts several coded UOM attributes into their descriptive display values. This design is typical of ERES-supporting views, which are consumed by the EBS electronic signature infrastructure to render the record contents that a user is approving or signing. The view therefore functions less as a general-purpose reporting object and more as a controlled interface layer between the underlying item definition tables and the ERES UI, although it can be queried directly for reporting and integration purposes.

Underlying Base Objects

The view is defined over four documented objects, joined to produce one row per item-organization combination within the master organization context:

The join predicates link MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID to both HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID and MTL_PARAMETERS.ORGANIZATION_ID. The language qualifier on the HR table is the only filter in the view text, meaning no implicit item-status or enabled-flag restriction is applied.

Key Columns

The view exposes twelve columns. ORG_ANIZATION_CODE and ORGANIZATION_NAME identify the inventory organization in code and translated name form. INVENTORY_ITEM_ID and ORGANIZATION_ID form the composite key of the underlying item assignment, while MASTER_ORGANIZATION_ID identifies the item's master organization.

The remaining columns are derived display values. PRIMARY_UNIT_OF_MEASURE and SECONDARY_UOM return the decoded unit of measure descriptions via INV_MEANING_SEL.C_UNITMEASURE. TRACKING_QUANTITY resolves the TRACKING_QUANTITY_IND flag against the INV_TRACKING_UOM_TYPE lookup. ONT_PRICING_QTY_SOURCE resolves ONT_PRICING_QTY_SOURCE against INV_PRICING_UOM_TYPE. SECONDARY_DEFAULT_IND resolves the secondary defaulting flag against INV_DEFAULTING_UOM_TYPE. DUAL_UOM_DEVIATION_HIGH and DUAL_UOM_DEVIATION_LOW expose the tolerance thresholds used in dual-unit-of-measure transactions.

Common Use Cases and Queries

Typical uses include auditing item organization assignments, validating dual UOM configuration, and building ERES approval extracts. The following query lists items assigned to a specific organization with decoded UOM values:

  • SELECT organization_code, organization_name, inventory_item_id, primary_unit_of_measure, secondary_uom FROM mtl_system_items_er4_v WHERE organization_code = 'M1';
  • SELECT inventory_item_id, dual_uom_deviation_high, dual_uom_deviation_low FROM mtl_system_items_er4_v WHERE secondary_default_ind IS NOT NULL;
  • SELECT organization_code, COUNT(*) FROM mtl_system_items_er4_v GROUP BY organization_code;

Because INV_MEANING_SEL calls are executed per row, large extracts can incur performance overhead; filtering on ORGANIZATION_ID or ORGANIZATION_CODE is recommended over full-table scans.