Search Results mtl_system_items_b_pk

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:

• INVENTORY_ITEM_ID — surrogate identifier for the item, consistent across organizations; pairs with ORGANIZATION_ID.
• ORGANIZATION_ID — the inventory organization in which this item row is defined; foreign key to MTL_PARAMETERS.
• SEGMENT1 — the item number concatenated segment, the human-readable identifier for the item.
• DESCRIPTION — the item's primary description text.
• PRIMARY_UOM_CODE / PRIMARY_UNIT_OF_MEASURE — the stocking and transaction unit of measure.
• INVENTORY_ITEM_STATUS_CODE — controls whether the item is active, inactive, or otherwise restricted; foreign key to MTL_ITEM_STATUS.
• INVENTORY_ITEM_FLAG, PURCHASING_ITEM_FLAG, CUSTOMER_ORDER_FLAG, BOM_ITEM_TYPE — functional flags that classify the item as inventory, purchased, order-entry, and bill-of-material type.
• INVENTORY_ASSET_FLAG — determines whether the item is asset-tracked for costing and fixed assets.
• PLANNER_CODE, PLANNING_MAKE_BUY_CODE, MRP_PLANNING_CODE — planning and MRP attributes, with PLANNER_CODE referencing MTL_PLANNERS.
• COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, ENCUMBRANCE_ACCOUNT, SALES_ACCOUNT — accounting flexfield references to GL_CODE_COMBINATIONS for transaction accounting.
• ENGINEERING_ITEM_ID, BASE_ITEM_ID, PRODUCT_FAMILY_ITEM_ID — self-referencing relationships defining engineering, base model, and product family structures.
• LOT_CONTROL_CODE, SERIAL_NUMBER_CONTROL_CODE, SHELF_LIFE_CODE — inventory control and lot/serial tracking configuration.

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:

• MTL_SYSTEM_ITEMS_TL — translation table joined on INVENTORY_ITEM_ID and ORGANIZATION_ID for multilingual descriptions.
• MTL_PARAMETERS — referenced by ORGANIZATION_ID, SOURCE_ORGANIZATION_ID, and DEFAULT_SHIPPING_ORG; defines the inventory organization.
• MTL_ITEM_STATUS — referenced by INVENTORY_ITEM_STATUS_CODE, controlling item availability.
• MTL_SECONDARY_INVENTORIES — referenced by SOURCE_SUBINVENTORY and WIP_SUPPLY_SUBINVENTORY.
• GL_CODE_COMBINATIONS — referenced by COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, ENCUMBRANCE_ACCOUNT, and SALES_ACCOUNT.
• MTL_MATERIAL_TRANSACTIONS and MTL_ONHAND_QUANTITIES — reference INVENTORY_ITEM_ID for balances and transactional movement.
• BOM_STRUCTURES_B and BOM_BILL_OF_MATERIALS — reference ASSEMBLY_ITEM_ID and COMPONENT_ITEM_ID for bills of material.
• MTL_ITEM_CATEGORIES — references INVENTORY_ITEM_ID to assign categories.
• CST_ITEM_COSTS and CST_ITEM_COST_DETAILS — reference INVENTORY_ITEM_ID for cost rollups and item costs.
• WIP_DISCRETE_JOBS, WIP_REPETITIVE_ITEMS, and WIP_ENTITIES — reference PRIMARY_ITEM_ID for manufacturing execution.
• MTL_RESERVATIONS, MTL_LOT_NUMBERS, and MTL_SERIAL_NUMBERS — reference INVENTORY_ITEM_ID for reservation and lot/serial tracking.

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.