Oracle EBS Inventory Tables Reference (MTL and CST)
When you build an Oracle EBS inventory report, write an interface, or debug a stuck transaction, the first question is which table holds the data. This reference lists the core MTL and CST tables with their purpose and the columns you normally join on.
| Table | Area | Purpose | Key columns |
|---|---|---|---|
MTL_SYSTEM_ITEMS_B | Item master | Item definitions, one row per item per inventory organization | INVENTORY_ITEM_ID, ORGANIZATION_ID, SEGMENT1 |
MTL_SYSTEM_ITEMS_TL | Item master | Translated item description and long description | INVENTORY_ITEM_ID, ORGANIZATION_ID, LANGUAGE |
MTL_PARAMETERS | Organization | Inventory organization parameters and master organization link | ORGANIZATION_ID, ORGANIZATION_CODE, MASTER_ORGANIZATION_ID |
MTL_ITEM_REVISIONS_B | Item master | Item revisions with effectivity dates | INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION |
MTL_CATEGORY_SETS_B | Categories | Category set definitions | CATEGORY_SET_ID, STRUCTURE_ID |
MTL_CATEGORIES_B | Categories | Category definitions | CATEGORY_ID, STRUCTURE_ID |
MTL_ITEM_CATEGORIES | Categories | Assignment of items to categories within a category set | INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_SET_ID, CATEGORY_ID |
MTL_SECONDARY_INVENTORIES | Storage | Subinventory definitions per organization | SECONDARY_INVENTORY_NAME, ORGANIZATION_ID |
MTL_ITEM_LOCATIONS | Storage | Stock locators | INVENTORY_LOCATION_ID, ORGANIZATION_ID, SUBINVENTORY_CODE |
MTL_ONHAND_QUANTITIES_DETAIL | Onhand | Onhand quantity by item, subinventory, locator, lot and revision | INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID, LOT_NUMBER, TRANSACTION_QUANTITY |
MTL_MATERIAL_TRANSACTIONS | Transactions | Processed material transactions (history) | TRANSACTION_ID, TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_DATE |
MTL_MATERIAL_TRANSACTIONS_TEMP | Transactions | Pending transactions not yet processed by the transaction manager | TRANSACTION_TEMP_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID |
MTL_TRANSACTIONS_INTERFACE | Interface | Open interface for inventory transactions | TRANSACTION_INTERFACE_ID, PROCESS_FLAG, TRANSACTION_MODE, ERROR_CODE |
MTL_TRANSACTION_TYPES | Transactions | Transaction type definitions | TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME |
MTL_TRANSACTION_ACCOUNTS | Accounting | Accounting lines generated for material transactions | TRANSACTION_ID, REFERENCE_ACCOUNT, ACCOUNTING_LINE_TYPE, BASE_TRANSACTION_VALUE |
MTL_TRANSACTION_LOT_NUMBERS | Lots | Lot detail for processed transactions | TRANSACTION_ID, LOT_NUMBER |
MTL_UNIT_TRANSACTIONS | Serials | Serial number detail for processed transactions | TRANSACTION_ID, SERIAL_NUMBER |
MTL_LOT_NUMBERS | Lots | Lot master with expiration and origination dates | INVENTORY_ITEM_ID, ORGANIZATION_ID, LOT_NUMBER, EXPIRATION_DATE |
MTL_SERIAL_NUMBERS | Serials | Serial number master and current status | SERIAL_NUMBER, INVENTORY_ITEM_ID, CURRENT_STATUS |
MTL_RESERVATIONS | Availability | Reservations of supply against demand | RESERVATION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, PRIMARY_RESERVATION_QUANTITY |
MTL_DEMAND | Availability | Demand records used for availability and planning | DEMAND_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID |
MTL_SUPPLY | Availability | Expected supply (PO, requisition, shipment) used for availability | SUPPLY_TYPE_CODE, INVENTORY_ITEM_ID (item id column varies by supply type), TO_ORGANIZATION_ID |
MTL_TXN_REQUEST_HEADERS | Move orders | Move order headers | HEADER_ID, ORGANIZATION_ID, REQUEST_NUMBER |
MTL_TXN_REQUEST_LINES | Move orders | Move order lines | LINE_ID, HEADER_ID, INVENTORY_ITEM_ID |
MTL_SYSTEM_ITEMS_INTERFACE | Interface | Open interface for importing items | PROCESS_FLAG, SET_PROCESS_ID, TRANSACTION_TYPE, ORGANIZATION_ID |
MTL_INTERFACE_ERRORS | Interface | Error messages for rows rejected by item and category imports | TRANSACTION_ID, ERROR_MESSAGE, TABLE_NAME |
MTL_CYCLE_COUNT_HEADERS | Cycle count | Cycle count definitions | CYCLE_COUNT_HEADER_ID, ORGANIZATION_ID, CYCLE_COUNT_HEADER_NAME |
MTL_CYCLE_COUNT_ENTRIES | Cycle count | Individual count requests and results | CYCLE_COUNT_ENTRY_ID, CYCLE_COUNT_HEADER_ID, INVENTORY_ITEM_ID |
MTL_PHYSICAL_INVENTORIES | Physical inventory | Physical inventory definitions | PHYSICAL_INVENTORY_ID, ORGANIZATION_ID |
MTL_ABC_CLASSES | ABC | ABC class definitions | ABC_CLASS_ID, ORGANIZATION_ID, ABC_CLASS_NAME |
MTL_INTERORG_PARAMETERS | Organization | Inter-organization transfer parameters and accounts | FROM_ORGANIZATION_ID, TO_ORGANIZATION_ID |
ORG_ACCT_PERIODS | Periods | Inventory accounting periods per organization | ORGANIZATION_ID, ACCT_PERIOD_ID, OPEN_FLAG, PERIOD_CLOSE_DATE |
CST_COST_TYPES | Costing | Cost type definitions | COST_TYPE_ID, COST_TYPE |
CST_ITEM_COSTS | Costing | Item cost summary per organization and cost type | INVENTORY_ITEM_ID, ORGANIZATION_ID, COST_TYPE_ID, ITEM_COST |
CST_ITEM_COST_DETAILS | Costing | Item cost by cost element and level | INVENTORY_ITEM_ID, ORGANIZATION_ID, COST_TYPE_ID, COST_ELEMENT_ID |
Names reflect Release 12 era schemas. Confirm column lists in your instance with the Oracle eTRM or DESCRIBE, as columns differ between 11i, 12.1 and 12.2.
Ask your EBS data in plain English
Optional: see how read-only natural-language queries over MTL and WIP tables could work on your instance.
Optional. The tool and every export work without this. No spam.
How to use the table list
Type part of a table name, an area such as Lots, or a column name into the search box. The key columns field shows the joins you will use most often, so you can move from the item master to onhand, transactions and accounting without opening the eTRM each time. Export the filtered list to Excel to paste into a technical design document or a report specification.
- Search by area to see every table for lots, serials, cycle count or interface
- Join to the item master on INVENTORY_ITEM_ID and ORGANIZATION_ID together
- Treat the ORGANIZATION_ID as part of the key almost everywhere
How the inventory tables fit together
MTL_SYSTEM_ITEMS_B is the item master, stored once per organization, with the master organization holding the global attributes. Quantities live in MTL_ONHAND_QUANTITIES_DETAIL, which is updated by the transaction manager as MTL_MATERIAL_TRANSACTIONS_TEMP or MTL_TRANSACTIONS_INTERFACE rows are validated and processed into MTL_MATERIAL_TRANSACTIONS. Accounting lines for each processed transaction go to MTL_TRANSACTION_ACCOUNTS, and item cost is held in CST_ITEM_COSTS by cost type.
Reading transaction data correctly
MTL_MATERIAL_TRANSACTIONS joins to MTL_TRANSACTION_TYPES on TRANSACTION_TYPE_ID, and the source is identified by TRANSACTION_SOURCE_TYPE_ID together with TRANSACTION_SOURCE_ID. Quantities are signed, so issues are negative. Lot and serial detail sit in separate tables keyed by TRANSACTION_ID. For a transaction that appears missing, check the temp and interface tables before assuming it was lost.
Common mistakes
Developers often join the item master on item id alone and get duplicate rows across organizations. Another frequent error is reporting from base tables without respecting multi-org security, or using quantities from a stale view. Do not update these tables directly: supported APIs and open interfaces exist, and direct updates break onhand and accounting integrity.
Frequently Asked Questions
Where is onhand quantity stored in Oracle EBS?
The base table is MTL_ONHAND_QUANTITIES_DETAIL, with one row per item, organization, subinventory, locator, lot and revision combination. Sum TRANSACTION_QUANTITY to get onhand. Availability also considers reservations and pending transactions.
What is the difference between MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL?
The _B table holds the language independent attributes such as segments, UoM and planning settings. The _TL table holds translated description text by language. Join both when you need a description in a particular language.
Can I insert into MTL_MATERIAL_TRANSACTIONS directly?
No. Use the MTL_TRANSACTIONS_INTERFACE open interface or the inventory transaction APIs so validation, onhand updates and accounting run. Direct inserts leave onhand and the general ledger out of sync.
Are these tables the same in 11i and R12?
Most core tables exist in both, but columns and some tables differ, especially around costing, multi-org and the move to _B and _TL splits. Always confirm against the eTRM for your exact release.
If you want natural-language questions answered from your EBS tables instead of hand-written joins, we can set that up read-only on your own servers.
Related Tools
Oracle EBS Onhand Quantity SQL Generator
Fill in organization, item pattern and subinventory to get ready-to-run SQL for onhand by lot, onhand versus reservations, and recent transaction history.
OracleOracle EBS WIP Tables Reference
Lookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.
Developer ToolsOracle EBS Pending Transactions SQL Generator for Period Close
Builds SQL that counts and lists pending, errored and uncosted transactions in inventory and WIP before you close an Oracle inventory accounting period.
OracleOracle EBS Cycle Count Workload Calculator
Enter item counts and count frequency per ABC class to see yearly counts, daily counts and the number of counters needed for an Oracle Inventory cycle count.
More Oracle tools
See all- Oracle EBS Discrete Manufacturing Go-Live Checklist
- Oracle EBS Inventory Accuracy Readiness Assessment
- Oracle EBS Concurrent Request Phase and Status Reference
- Oracle EBS Responsibility Design Worksheet
- Oracle EBS GL Period Close Checklist
- Oracle EBS AP Invoice Interface Template
- Oracle EBS 12.2 Upgrade Readiness Checklist
- Oracle EBS Patching Checklist for adop
- Oracle EBS SQL for Concurrent Requests Generator
New to Oracle E-Business Suite? Start with the Oracle E-Business Suite guide and toolkit, or plan with Oracle EBS Roadmap Planner, Training Path Builder, ERP Certification Study Planner.
Keep exploring
Free tools
- Oracle EBS Cost Management Tables ReferenceA lookup of the Oracle EBS tables behind item cost, WIP jobs, BOM, routing and inventory transactions used in manufacturing costing.
- Oracle EBS WIP Tables ReferenceLookup of Oracle EBS Work in Process tables with purpose and key columns, covering discrete jobs, operations, requirements, resources and interfaces.
- Oracle EBS Customer and Supplier Tables ReferenceSearchable list of the Trading Community Architecture customer tables and the supplier tables in Oracle EBS, with keys, join columns and where to confirm in the eTRM.
ERP fixes and how-tos
- How to Close an Inventory Accounting Period in Oracle EBSClose an Oracle EBS Inventory accounting period: clear pending transactions, run the pending transactions report, close via Inventory Accounting Periods, transfer to GL.
- How to Create an Item in Oracle EBSCreate an inventory item in Oracle EBS: Master Items form, master organization, templates, attributes, organization assignment, categories and common errors.
- Data Conversion in Oracle EBS: Interface Tables, Open Interfaces and ReconciliationHow data conversion works in Oracle EBS: extract, stage, validate, load through interface tables and APIs, then reconcile. Practical steps and common failures.
Runs with your ERP
- Esker Order Management and Oracle E-Business SuiteHow Esker captures customer POs from email, fax and PDF and creates sales orders in Oracle EBS: integration, data flows, setup, pitfalls and a private AI layer.
- Cloud Inventory (DSI) with Oracle E-Business Suite: Mobile Barcode InventoryCloud Inventory, formerly DSI, adds offline mobile barcode inventory to Oracle E-Business Suite and JD Edwards. Data flows, setup steps, pitfalls and AI notes.
- Oracle Mobile Supply Chain Applications (MSCA) for Oracle EBSOracle Mobile Supply Chain Applications (MSCA) for EBS: native RF transactions for inventory, WMS and manufacturing, setup, pitfalls and where private AI helps.
AI on your ERP
- AI for Oracle E-Business Suite, Without Leaving On-PremHosting Oracle EBS 12.2 on-prem or in a private cloud? Add a private LLM over concurrent programs, interface tables and AP/PO data without moving to Fusion.
- AI for Oracle EBS in Industrial Manufacturing: WIP, MRP, and BOM Questions Answered DirectlyAI for Oracle E-Business Suite in industrial manufacturing: WIP jobs, BOMs, MRP, inventory and purchasing answered in plain English, private and read-only.
Integrations
- How to use ChatGPT with Oracle E-Business SuiteUsing ChatGPT with Oracle E-Business Suite: export vs live access, custom GPT actions over ISG REST, enterprise data controls and when to keep data on-prem.
- How to connect Claude to Oracle E-Business SuiteConnect Claude to Oracle E-Business Suite via MCP, the Anthropic API, AWS Bedrock or Vertex, and know when an on-prem open-weight model is the better fit.