Search Results mtl_system_items_b




Overview
Explore in AI Chat

Overview

MTL_SYSTEM_ITEMS_B is the master definition table for inventory items in Oracle E-Business Suite, owned by the INV (Inventory) schema. It stores the core attributes that describe every item an organization transacts, plans, purchases, manufactures, sells, or services. Because Oracle EBS operates with a multi-organization architecture, an item is not globally unique on its own: the table is keyed by the composite primary key MTL_SYSTEM_ITEMS_B_PK on (INVENTORY_ITEM_ID, ORGANIZATION_ID), meaning the same inventory item is represented by a distinct row in each organization where it is defined and enabled. The table carries 401 documented columns in 12.2.2 and serves as the central hub from which nearly every supply chain, manufacturing, costing, order management, and service module draws item validation and defaulting logic.

From a dimensional modeling perspective, the mined foreign-key structure suggests a hub-leaning classification. MTL_SYSTEM_ITEMS_B functions as a core entity hub in a Data Vault sense, with INVENTORY_ITEM_ID acting as the durable business key and ORGANIZATION_ID participating as part of the composite identity. Its 401 columns are largely descriptive and would typically be split into one or more satellites (planning, purchasing, costing, service, and process attributes) rather than modeled as a single wide hub. Tables that reference it via INVENTORY_ITEM_ID behave as links or dependent satellites.

Key Information Stored

The table's identity is established by the composite primary key (INVENTORY_ITEM_ID, ORGANIZATION_ID), which is also exposed as unique index MTL_SYSTEM_ITEMS_B_U1 and serves as the business-key candidate. The most functionally significant columns include:

Common Use Cases and Queries

MTL_SYSTEM_ITEMS_B is queried in virtually every inventory, order, purchasing, and manufacturing report. The most common pattern joins it to MTL_PARAMETERS for organization context and MTL_SYSTEM_ITEMS_TL for translated descriptions.

  • Item master inquiry: retrieve all enabled items for a given organization ordered by SEGMENT1, filtering on ORGANIZATION_ID and ENABLED_FLAG.
  • Item status and defaulting: join to MTL_ITEM_STATUS via INVENTORY_ITEM_STATUS_CODE to surface active or restricted items.
  • Planning and purchasing extraction: select PLANNER_CODE, PLANNING_MAKE_BUY_CODE, and purchasing flags for MRP and procurement interfaces.
  • Costing and accounting: resolve COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, and related GL_CODE_COMBINATIONS references for transaction accounting.
  • Model and structure reporting: use BASE_ITEM_ID and PRODUCT_FAMILY_ITEM_ID self-joins to reconstruct model, family, and configuration relationships.

A typical query joins MTL_SYSTEM_ITEMS_B to MTL_SYSTEM_ITEMS_TL on INVENTORY_ITEM_ID and ORGANIZATION_ID to return translated descriptions alongside item attributes.

Related Objects

MTL_SYSTEM_ITEMS_B is one of the most heavily referenced tables in EBS. The following are among the most significant related objects and foreign-key relationships documented in the metadata:

Beyond these, self-referencing foreign keys (ENGINEERING_ITEM_ID, BASE_ITEM_ID, BASE_WARRANTY_SERVICE_ID, PRODUCT_FAMILY_ITEM_ID) allow the table to model hierarchy, warranty, and family relationships within itself.