Search Results MTL_SYSTEM_ITEMS_B

Overview

The MTL_SYSTEM_ITEMS_B table is the core master data repository for inventory item definitions within Oracle E-Business Suite (EBS) Inventory (INV) module. It serves as the fundamental building block for all item-centric transactions and processes across the supply chain, manufacturing, order management, and financial applications. Every tangible or intangible product, component, or service tracked within the system must be defined as a record in this table. Its primary key, a composite of INVENTORY_ITEM_ID and ORGANIZATION_ID, enforces the definition of items uniquely within each inventory organization, enabling complex multi-org structures.

Key Information Stored

The table stores a comprehensive set of attributes that define an item's behavior and control its processing throughout EBS. Key columns include the primary key components (INVENTORY_ITEM_ID, ORGANIZATION_ID), descriptive fields (ITEM_NUMBER, DESCRIPTION), and critical control flags governing inventory, costing, purchasing, and order management (e.g., INVENTORY_ITEM_FLAG, PURCHASING_ITEM_FLAG, SOURCE_TYPE). The table also holds numerous foreign key references that link the item to other master data, such as INVENTORY_ITEM_STATUS_CODE (to MTL_ITEM_STATUS), COST_OF_SALES_ACCOUNT (to GL_CODE_COMBINATIONS), ATP_RULE_ID (to MTL_ATP_RULES), and RECEIVING_ROUTING_ID (to RCV_ROUTING_HEADERS). This structure centralizes item definition while enforcing integration with related functional areas.

Common Use Cases and Queries

This table is central to reporting, data validation, and integration tasks. Common queries involve retrieving item details for a specific organization, listing items by their enabled functionality, or tracing item relationships. For example, to find all inventory-enabled items in organization 204 with their status, a typical query is: SELECT msi.segment1 ITEM_NUMBER, msi.DESCRIPTION, mis.STATUS_CODE FROM INV.MTL_SYSTEM_ITEMS_B msi JOIN INV.MTL_ITEM_STATUS_TL mis ON msi.INVENTORY_ITEM_STATUS_CODE = mis.STATUS_CODE WHERE msi.ORGANIZATION_ID = 204 AND msi.INVENTORY_ITEM_FLAG = 'Y' AND mis.LANGUAGE = USERENV('LANG');. Another critical use case is validating item-organization assignments during data migrations or when creating new items via the Item Open Interface (INV_ITEM_OPEN_PKG).

Related Objects

MTL_SYSTEM_ITEMS_B is the parent table for a vast network of related objects. The primary transactional child tables, such as MTL_ONHAND_QUANTITIES and MTL_MATERIAL_TRANSACTIONS, depend on its definitions. It has a language-specific translation table, MTL_SYSTEM_ITEMS_TL. Key foreign key relationships, as documented, link it to control tables like MTL_PARAMETERS (organization setup), MTL_SECONDARY_INVENTORIES (subinventories), and RA_RULES (accounting/invoicing rules). Furthermore, it is referenced by critical APIs, most notably the Item Import and Item Open Interface (INV_ITEM_OPEN_PKG), which is the standard programmatic method for creating and updating item master records.