Inventory Tables in Oracle Apps R12

Managing inventory in Oracle EBS R12 relies on several foundational tables. It’s the central hub that tracks every raw material, finished good, and spare part. For developers, consultants, and analysts, a deep understanding of its underlying data structure isn’t just helpful—it’s essential for building robust integrations, accurate reports, and effective support solutions. These inventory tables power transactions, stock tracking, costing, and inventory API processing in Oracle Apps R12. Here’s a consultant-friendly guide to each core table—along with the key columns, relationships, and usage.

Table of Contents

MTL_SYSTEM_ITEMS_B: Item Master Table

MTL_CATEGORY_SETS_B: Category Sets

MTL_ITEM_CATEGORIES: Item-Category Mapping

MTL_CATEGORIES_B: Category Master

MTL_DEMAND: Demand Entries

MTL_TXN_REQUEST_HEADERS: Move Order Header Table

MTL_TXN_REQUEST_LINES: Move Order Line Table

MTL_CYCLE_COUNTS: Cycle Count Definition Table

MTL_MATERIAL_TRANSACTIONS: Transaction History

MTL_ONHAND_QUANTITIES_DETAIL: Onhand Quantity Table

MTL_TRANSACTION_TYPES: Transaction Types Tables

MTL_SECONDARY_INVENTORIES: Subinventory Table

MTL_ITEM_LOCATIONS: Stock Locators

MTL_LOT_NUMBERS: Lot Number Tracking

MTL_SERIAL_NUMBERS: Serialized Tracking

MTL_RESERVATIONS: Reservations Table

MTL_PARAMETERS: Org-Level Inventory Control

MTL_TRANSACTIONS_INTERFACE: Inventory Transaction Interface

MTL_MATERIAL_TRANSACTIONS_TEMP: Temporary Transaction Processing

MTL_INTERFACE_ERRORS: Interface Error Table

MTL_TRANSACTION_LOTS_INTERFACE: Lot Transaction Interface

MTL_SERIAL_NUMBERS_INTERFACE: Serial Number Interface

📦 MTL_SYSTEM_ITEMS_B – Oracle Item Master Table

MTL_SYSTEM_ITEMS_B stores item definitions across inventory organizations. This table acts as the basic inventory tables in Oracle Apps R12.

Why it matters: Every transaction, reservation, or planning action uses this table.

Important columns:

INVENTORY_ITEM_ID (PK) – Unique ID per item; foreign key in most inventory tables.
ORGANIZATION_ID – Refers to inventory org (HR_ORGANIZATION_UNITS).
SEGMENT1 – Item code used by users; varchar2(40).
DESCRIPTION – Short description of the item.
ITEM_TYPE – Defines item behavior (e.g., finished good, asset).
SERIAL_NUMBER_CONTROL_CODE & LOT_CONTROL_CODE: These flags determine if an item requires serial or lot tracking, fundamentally changing how it’s transacted.

Used in: When a user enters an item on a sales order or purchase order, the system validates it against this table. All APIs including INV_TXN_MANAGER_PUB. PROCESS_TRANSACTIONS.

🧷MTL_CATEGORY_SETS_B – Category Sets

MTL_CATEGORY_SETS_B table contains the definition for a category sets. A category set is a categorization for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.

Defines named category sets like “Inventory”, “Purchasing”, or “Order Management”. Only categories with the same flexfield structure may be grouped into a category set.

Why it matters:
Each functional area can use a different category set for item grouping.

Key columns:

CATEGORY_SET_ID (PK)
CATEGORY_SET_NAME
CONTROL_LEVEL – Indicates if it’s item-level or org-level control.

Joins with:
MTL_ITEM_CATEGORIES for item-to-category mapping.

🧩 MTL_ITEM_CATEGORIES – Item-Category Mapping

This table maintains the assignment of inventory items to categories within defined category sets in Oracle Inventory. Links items to one or more categories within category sets.

Why it matters:
This is where Oracle stores the actual item-to-category assignment used in UI and reports.

Key columns:

INVENTORY_ITEM_ID – FK to MTL_SYSTEM_ITEMS_B
CATEGORY_ID – FK to MTL_CATEGORIES_B
CATEGORY_SET_ID – FK to MTL_CATEGORY_SETS_B
ORGANIZATION_ID

Use case:
Query this table to find all categories assigned to an item or filter items by category.

🗂 MTL_CATEGORIES_B – Category Master

This table holds the code combinations for item categories in Oracle Inventory. Each category is part of a flexible grouping scheme defined by a category set. Defines individual item categories (e.g., “Electronics”, “Consumables”, “Cameras”).

Why it matters:
Categories help organize and classify items for reporting, rules, and planning.

Key columns:

CATEGORY_ID (PK) – Unique identifier for the category.
STRUCTURE_ID – Links to the flexfield structure.
CATEGORY_NAME, DESCRIPTION

Related to:
MTL_ITEM_CATEGORIES, MTL_CATEGORY_SETS_B

📦 MTL_DEMAND – Demand Entries

Holds detailed demand information generated from sales orders, WIP jobs, etc.

Why it matters:
Used to calculate available-to-promise (ATP) and net demand for planning.

Key columns:

DEMAND_ID (PK)
INVENTORY_ITEM_ID, ORGANIZATION_ID
DEMAND_SOURCE_TYPE_ID – Sales order, job, forecast
QUANTITY, DEMAND_DATE

Related to:
MTL_RESERVATIONS, MTL_ONHAND_QUANTITIES_DETAIL

Used by:
Planning engines, ATP logic, and some reservation flows.

📝MTL_TXN_REQUEST_HEADERS – Inventory Move Order Header Table

This table holds the header-level data for Move Orders in Oracle Inventory. Move Orders are formal requests for material movement within an organization.

Why it matters:
Move Orders are used for subinventory transfers, replenishments, and sales order picks. This header defines the transaction type, source, requestor, and status.

Key columns:

HEADER_ID (PK) – Unique ID for each move order
REQUEST_NUMBER – Auto-generated request number
REQUEST_TYPE – Requisition, replenishment, pick wave, etc.
STATUS – Pending, Approved, or Completed
ORGANIZATION_ID – Owning inventory organization

Linked to:
_{MTL_TXN_REQUEST_LINES}for line details, and to subinventory and locator tables.

📦 MTL_TXN_REQUEST_LINES – Move Order Line Table

Contains line-level details for each Move Order created in Oracle Inventory.

Why it matters:
Defines item, quantity, source, and destination details for each requested movement. Tracks approval and allocation status line-by-line.

Key columns:

LINE_ID (PK) – Unique identifier for the line
HEADER_ID – FK to MTL_TXN_REQUEST_HEADERS
INVENTORY_ITEM_ID – FK to MTL_SYSTEM_ITEMS_B
REQUESTED_QUANTITY, ALLOCATED_QUANTITY
SUBINVENTORY_CODE, LOCATOR_ID

Used in:
Material picking, WIP supply, inter-subinventory movement automation.

🔁 MTL_CYCLE_COUNTS – Cycle Count Definition Table

Holds cycle count header definitions for physical inventory control. Cycle counts allow periodic counting without a full physical inventory.

Why it matters:
Essential for tracking and validating inventory accuracy. Enables compliance with accounting and quality standards by frequently auditing specific item classes.

Key columns:

CYCLE_COUNT_HEADER_ID (PK) – Unique ID per cycle count
CYCLE_COUNT_NAME – User-defined name
CLASS_ID – Grouping of items for counting
ABC_CLASS – Optional for ABC-based count classification
COUNT_TYPE – Manual or automatic

Linked to:
Cycle count entries and results (e.g., _{MTL_CYCLE_COUNT_ENTRIES}, _{MTL_CYCLE_COUNT_ADJUSTMENTS})

🔄 MTL_MATERIAL_TRANSACTIONS – Oracle Transaction History Table

Logs all inventory transactions (issues, receipts, adjustments, transfers). This is the main inventory tables for storing transactions in Oracle Apps R12.

Why it matters: Main audit and reporting table for material movements.

Important columns:

TRANSACTION_ID (PK) – Unique identifier per movement.
INVENTORY_ITEM_ID – FK to MTL_SYSTEM_ITEMS_B.
TRANSACTION_TYPE_ID – FK to MTL_TRANSACTION_TYPES.
TRANSACTION_DATE – Date the transaction occurred.
QUANTITY – Movement quantity; number.

Populated by: INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS.

📊 MTL_ONHAND_QUANTITIES_DETAIL – Stock Inventory Tables in Oracle Apps R12

Tracks on-hand quantities at subinventory and locator levels.

Why it matters: Validates available stock before transaction is processed.

Important columns:

INVENTORY_ITEM_ID, ORGANIZATION_ID – Item and org references.
SUBINVENTORY_CODE – FK to MTL_SECONDARY_INVENTORIES.
LOCATOR_ID – FK to MTL_ITEM_LOCATIONS.
TRANSACTION_QUANTITY – Total on-hand stock in the locator.

Used by API: INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES.

🔁 MTL_TRANSACTION_TYPES – Oracle Transaction Types Table

Defines inventory transaction types like PO Receipt, WIP Issue, Subinventory Transfer.

Why it matters: Categorizes each transaction for processing and reporting.

Important columns:

TRANSACTION_TYPE_ID (PK) – Links to material transactions.
TRANSACTION_TYPE_NAME – Descriptive name like “Sales Order Issue”.
TRANSACTION_ACTION_ID – Points to action (issue, receive, adjust).

Joined with: MTL_MATERIAL_TRANSACTIONS, MTL_TRANSACTIONS_INTERFACE.

🏬MTL_SECONDARY_INVENTORIES – Oracle Subinventory Table

Defines subinventories such as “Stores”, “Production”, “Finished Goods”.

Why it matters: Used to classify stock locations within organizations.

Important columns:

SECONDARY_INVENTORY_NAME (PK) – User-defined subinventory code.
ORGANIZATION_ID – Links to inventory org.
ENABLE_LOCATOR_CONTROL – Flags if locators are mandatory.

Used in: On-hand quantity tracking and transaction entry.

🧭 MTL_ITEM_LOCATIONS – Oracle Stock Locators Table

Stores definitions for physical locator structures (e.g., aisle/bin).

Why it matters: Helps track exact storage positions in subinventories.

Important columns:

INVENTORY_LOCATION_ID (PK) – Locator ID.
SUBINVENTORY_CODE – FK to MTL_SECONDARY_INVENTORIES.
SEGMENT1-3 – Custom locator segments (e.g., Row-Rack-Bin).

Referenced in: MTL_ONHAND_QUANTITIES_DETAIL, transaction APIs.

🔢 MTL_LOT_NUMBERS – Oracle Lot Tracking Table

Contains batch-wise lot information for lot-controlled items.

Why it matters: Enables expiry tracking, recalls, and batch-level traceability.

Important columns:

LOT_NUMBER – Unique lot identifier.
INVENTORY_ITEM_ID – FK to MTL_SYSTEM_ITEMS_B.
EXPIRATION_DATE – Used in FEFO/expiry-based planning.

Referenced by: Transactions, reservations, INV_TXN_MANAGER_PUB.

🔐 MTL_SERIAL_NUMBERS – Oracle Serialized Tracking Table

Maintains individual serial number data for serial-controlled items.

Why it matters: Supports high-value item tracking and service history.

Important columns:

SERIAL_NUMBER – Unique value for the item unit.
CURRENT_STATUS – Availability (issued, received, reserved).
INVENTORY_ITEM_ID – FK to item master.

Populated by: Serial control routines during issue/receipt.

📌MTL_RESERVATIONS – Oracle Demand Reservations Table

Stores quantity reservations against sales orders, jobs, or internal demands.

Why it matters: Affects available-to-transact (ATT) and ATP calculations.

Important columns:

RESERVATION_ID (PK) – Unique per reservation.
DEMAND_SOURCE_TYPE_ID – Type of demand (SO, WIP).
SUPPLY_SOURCE_TYPE_ID – Type of supply (PO, subinventory).
QUANTITY, RESERVATION_UOM

Queried by: INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES API.

⚙️ MTL_PARAMETERS – Org-Level Inventory Control

Defines inventory setup parameters for each organization.

Why it matters: Controls costing method, calendar, serial/lot usage, and defaults.

Important columns:

ORGANIZATION_ID (PK) – Links to all other inventory data.
COSTING_METHOD – Determines standard, FIFO, average.
ENABLE_SERIAL_NUMBERS_FLAG – Enables serial tracking.

Usage: Audit configuration, troubleshoot setup issues.

🔌 Interface Tables in Oracle Inventory

Interface tables act as a bridge between Oracle Inventory and external systems or other Oracle modules. Transactions and data are first staged here before being validated and moved to base tables. They’re vital for custom integration, bulk uploads, and API troubleshooting.

📥 MTL_TRANSACTIONS_INTERFACE – Inventory Transaction Interface

Purpose: Staging table for inventory transactions from external or internal sources.

Key columns:

TRANSACTION_INTERFACE_ID (PK) – Unique record ID.
SOURCE_CODE, SOURCE_LINE_ID – Integration source identifiers.
TRANSACTION_TYPE_ID, INVENTORY_ITEM_ID – Movement details.
PROCESS_FLAG – Status (1 = Pending, 2 = Running, 3 = Error, 4 = Success).
ERROR_CODE, ERROR_EXPLANATION – For debugging failed transactions.

Processed by: INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS.

Populates: MTL_MATERIAL_TRANSACTIONS.

🧪 MTL_MATERIAL_TRANSACTIONS_TEMP – Temporary Processing Table

Purpose: Temporary table used during the inventory transaction process.

Key columns:

TRANSACTION_TEMP_ID (PK) – Temporary transaction ID.
Mirrors many columns of MTL_MATERIAL_TRANSACTIONS.

Behavior: If processing fails or gets stuck, rows remain here.

Tips: Always clear stuck records here before reprocessing.

❌MTL_INTERFACE_ERRORS – Generic Error Table

Purpose: Logs errors from open interface tables (items, transactions, etc.).

Key columns:

INTERFACE_TYPE – Type of interface (INVENTORY, ITEMS, BOM).
REFERENCE_ID – FK to source interface table (e.g., MTL_TRANSACTIONS_INTERFACE.TRANSACTION_INTERFACE_ID).
ERROR_MESSAGE, ERROR_CODE – Diagnostic details.

Used for: Post-processing debugging.

🔁Lot and Serial Interface Tables in Oracle Inventory

Lot- and serial-controlled items require additional staging tables during transactions like receipts, issues, or transfers. These interface tables work in tandem with the main inventory transaction interfaces and ensure proper tracking and compliance.

🧬MTL_TRANSACTION_LOTS_INTERFACE – Lot Number Interface

Purpose: Stages lot details associated with each inventory transaction.

Key columns:

TRANSACTION_INTERFACE_ID – FK to MTL_TRANSACTIONS_INTERFACE.
LOT_NUMBER – The lot being issued or received.
LOT_EXPIRATION_DATE – Expiry control for perishable or regulated items.
TRANSACTION_QUANTITY – Quantity tied to the lot.

Populated by: Integrations or APIs when processing lot-controlled items.

Used by: INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS to populate MTL_TRANSACTION_LOTS.

🔐 MTL_SERIAL_NUMBERS_INTERFACE – Serial Number Interface

Purpose: Stages serial number data during transactions for serial-controlled items.

Key columns:

TRANSACTION_INTERFACE_ID – FK to MTL_TRANSACTIONS_INTERFACE.
FM_SERIAL_NUMBER, TO_SERIAL_NUMBER – Starting and ending serials.
CURRENT_STATUS – Defines whether serial is in stock, issued, etc.
TRANSACTION_QUANTITY – Must be 1 per serial if strict serial control is enabled.

Used by: Serial-controlled transactions in receiving, shipping, and WIP.

Populates: MTL_SERIAL_NUMBERS and MTL_UNIT_TRANSACTIONS.

Validated against: MTL_SYSTEM_ITEMS_B.SERIAL_CONTROL_CODE.

📘 Conclusion: Oracle Inventory Tables in Oracle Apps R12

Understanding the core and interface tables in Oracle Inventory is not just beneficial—it’s essential for Oracle EBS consultants, developers, and support analysts. These tables form the backbone of every inventory transaction, from simple stock issues to complex inter-org transfers, lot/serial control, and external system integrations.

MTL_SYSTEM_ITEMS_B defines the “what.”
MTL_ONHAND_QUANTITIES_DETAIL tracks the “how many and where.”
MTL_TRANSACTIONS_INTERFACE is the “how it gets in.”
MTL_MATERIAL_TRANSACTIONS provides the “what happened” historical proof.

📌 Whether you’re writing custom code, performing data cleanups, or auditing movement logs, this foundational knowledge equips you to deliver robust and reliable inventory solutions in Oracle R12. You can also explore PO Tables and TCA Tables as well.