OracleFree referencePDF, Excel, Word exportOracle E-Business Suite

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.

Download:

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.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Oracle tools

See all

Back to All Tools