MTL_ITEM_CATEGORIES

-- ============================================================
-- 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;