MTL_ITEM_CATEGORIES
Schema: INVmasterPer inventory orgThe item-to-category assignment — one row per item, organization, category set, and category; the bridge every category rollup crosses
The grain includes CATEGORY_ID: category sets that allow multiple assignments produce several rows per item per set, so aggregate or filter to one set before joining to quantities or a category rollup double-counts.
What the badges mean
- master
- Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or an APPS-schema view.
Fields
4 fields · 4 key
| # | Field | Description | Type | Flags |
|---|---|---|---|---|
| 1 | INVENTORY_ITEM_ID | Item id | NUMBER | Key |
| 2 | ORGANIZATION_ID | Inventory organization | NUMBER | Key |
| 3 | CATEGORY_SET_ID | The category set the assignment belongs to — filter to one set before aggregating | NUMBER | Key |
| 4 | CATEGORY_ID | The assigned category — part of the key, so multi-assignment sets carry several rows per item | NUMBER | Key |
Field provenance: hand-curated. 4 key fields.
Boilerplate SQL
Starting point for reading MTL_ITEM_CATEGORIESon Databricks — real DATE columns need no conversion, and the org anchor and the LAST_UPDATE_DATE watermark are already in place. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.
-- ============================================================
-- Table : MTL_ITEM_CATEGORIES — The item-to-category assignment — one row per item, organization, category set, and category; the bridge every category rollup crosses
-- Purpose: Column-selected read of MTL_ITEM_CATEGORIES — auto-generated from field metadata
-- Grain : One row per inventory org (ORGANIZATION_ID) + INVENTORY_ITEM_ID + CATEGORY_SET_ID + CATEGORY_ID
-- 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.CATEGORY_SET_ID AS "The category set the assignment belongs to — filter to one set before aggregating",
t.CATEGORY_ID AS "The assigned category — part of the key, so multi-assignment sets carry several rows per item"
FROM <catalog>.<schema>.MTL_ITEM_CATEGORIES 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.CATEGORY_SET_ID = <CATEGORY_SET_ID>
-- AND t.CATEGORY_ID = <CATEGORY_ID>
-- 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;7 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <INVENTORY_ITEM_ID>, <CATEGORY_SET_ID>, <CATEGORY_ID>, <watermark>
Relationships
1-hop neighbors — click a table to navigate there. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.
Join details
ON MTL_ITEM_CATEGORIES.INVENTORY_ITEM_ID = MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID AND MTL_ITEM_CATEGORIES.ORGANIZATION_ID = MTL_SYSTEM_ITEMS_B.ORGANIZATION_IDON MTL_ITEM_CATEGORIES.CATEGORY_ID = MTL_CATEGORIES_B.CATEGORY_IDON MTL_ITEM_CATEGORIES.CATEGORY_SET_ID = MTL_CATEGORY_SETS_B.CATEGORY_SET_ID