EBS Reference
MTL_ITEM_REVISIONS_B
Schema: INVmasterPer inventory orgItem revisions — one row per revision level of an item in an organization, with effectivity and implementation dates and the change notice that introduced it
Identity
Module: Item & Product MasterInventory-org partitioned (ORGANIZATION_ID)
Notes
REVISION_ID is the surrogate key; the business key is item + org + revision. Split into _B/_TL in R12 like the item master (the _TL side is not yet cataloged).
What the badges mean
- Schema: INV
- Schema: the Oracle product schema that owns the table (INV, ONT, WSH, PO, BOM, WIP, MRP, MSC, AR, AP, GL, HR, APPLSYS) — tells you which product family the object belongs to, not who can query it.
- master
- Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or an APPS-schema view.
- OU-striped (ORG_ID)
- Rows are scoped to an operating unit. A landed extract carries every operating unit’s rows — filter or join on
ORG_ID, and don’t confuse it withORGANIZATION_ID(see the quirks guide). - Per inventory org
- Rows are scoped to an inventory organization (plant or warehouse) via
ORGANIZATION_ID— a different partition from OU-striped tables (see the quirks guide). - Language-striped
- The table carries a
LANGUAGEcolumn (a _TL translation table or FND_LOOKUP_VALUES) — one row per language. Filter to oneLANGUAGEor a join multiplies rows (see the quirks guide). - View
- This is an APPS-schema convenience view, not a physical table. Extract the base tables it joins instead — views can be slow at scale and aren't guaranteed stable across patches.
Structural facts — how the table is partitioned, not a trap by itself
Join & extract hazards — verify before you rely on this
In field listings, the Key chip marks a primary-key field.
Fields
8 fields · 3 key
8 fields.
| # | Field | Description | Type | Flags |
|---|---|---|---|---|
| 1 | INVENTORY_ITEM_ID | Item id | NUMBER | Primary-key field |
| 2 | ORGANIZATION_ID | Inventory organization | NUMBER | Primary-key field |
| 3 | REVISION | The revision level (3 characters) | VARCHAR2 | Primary-key field |
| 4 | REVISION_ID | Surrogate key of the revision row | NUMBER | |
| 5 | EFFECTIVITY_DATE | When the revision becomes effective | DATE | |
| 6 | IMPLEMENTATION_DATE | When the revision was implemented — NULL for pending ECO revisions | DATE | |
| 7 | CHANGE_NOTICE | The engineering change order that introduced the revision | VARCHAR2 | |
| 8 | REVISION_LABEL | Display label for the revision | VARCHAR2 |
Field provenance: hand-curated. 3 key fields.
Boilerplate SQL
Starting point for reading MTL_ITEM_REVISIONS_B on Databricks — real DATE columns need no conversion, and the org anchor is already in place. The optional LAST_UPDATE_DATE watermark is included. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.
Query parameters
6 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <INVENTORY_ITEM_ID>, <REVISION>, <watermark>
-- ============================================================
-- Table : MTL_ITEM_REVISIONS_B — Item revisions — one row per revision level of an item in an organization, with effectivity and implementation dates and the change notice that introduced it
-- Purpose: Column-selected read of MTL_ITEM_REVISIONS_B — auto-generated from field metadata
-- Grain : One row per inventory org (ORGANIZATION_ID) + INVENTORY_ITEM_ID + REVISION
-- Notes : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
t.INVENTORY_ITEM_ID AS "Item id",
t.ORGANIZATION_ID AS "Inventory organization",
t.REVISION AS "The revision level (3 characters)",
t.REVISION_ID AS "Surrogate key of the revision row",
t.EFFECTIVITY_DATE AS "When the revision becomes effective",
t.IMPLEMENTATION_DATE AS "When the revision was implemented — NULL for pending ECO revisions",
t.CHANGE_NOTICE AS "The engineering change order that introduced the revision",
t.REVISION_LABEL AS "Display label for the revision"
FROM <catalog>.<schema>.MTL_ITEM_REVISIONS_B t
WHERE
t.ORGANIZATION_ID = <inventory_org_id> -- inventory org, NOT the operating unit — see quirks guide #two-orgs
-- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
-- AND t.REVISION = '<REVISION>'
-- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>' -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.INVENTORY_ITEM_ID;Verified September 2026
Relationships
Diagram of 1-hop neighbors — join details below. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.
Join details
ON MTL_ITEM_REVISIONS_B.INVENTORY_ITEM_ID = MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID AND MTL_ITEM_REVISIONS_B.ORGANIZATION_ID = MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID
More Item & Product Master tables
- MTL_SYSTEM_ITEMS_BThe item master — one row per item per inventory organization, carrying identity (the SEGMENTn flexfield the user-visible item number concatenates from), status, unit of measure, and the control flags for every functional area; every balance, transaction, and order line joins back to it on INVENTORY_ITEM_ID + ORGANIZATION_ID
- MTL_SYSTEM_ITEMS_TLThe item master's translation companion — item description and long description in every installed language, one row per item per organization per language
- MTL_UNITS_OF_MEASURE_TLThe unit-of-measure master — both the 3-char UOM code and the 25-char unit name, the UOM class, and the base-unit flag, one row per unit per language
- MTL_UOM_CONVERSIONSIntra-class UOM conversion rates to each class's base unit — both standard conversions and item-specific overrides, distinguished by the item id
- MTL_CATEGORIES_BThe category master — one row per category code combination, with the SEGMENTn flexfield columns the category name concatenates from, keyed by the flexfield structure
- MTL_CATEGORY_SETS_BCategory set definitions — validation rules, control level (master-org vs org-level), the multiple-assignments flag, and the default category new items inherit