MTL_ONHAND_QUANTITIES_DETAIL

-- ============================================================
-- Table  : MTL_ONHAND_QUANTITIES_DETAIL — The on-hand balance detail — receipt-level slices of stock per item, org, subinventory, locator, lot, and revision, consumed in FIFO order as material issues
-- Purpose: Column-selected read of MTL_ONHAND_QUANTITIES_DETAIL — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + ONHAND_QUANTITIES_ID
-- Caution: Rows are FIFO receipt slices — SUM PRIMARY_TRANSACTION_QUANTITY at your chosen grain; a single row is not a balance
-- 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.ONHAND_QUANTITIES_ID AS "Surrogate key of the receipt slice — not a business identifier",
  t.INVENTORY_ITEM_ID AS "Item id",
  t.ORGANIZATION_ID AS "Inventory organization",
  t.SUBINVENTORY_CODE AS "The subinventory holding the stock",
  t.LOCATOR_ID AS "The locator holding the stock — NULL in non-locator-controlled subinventories",
  t.LOT_NUMBER AS "Lot — NULL for non-lot-controlled items",
  t.REVISION AS "Item revision — populated for revision-controlled items",
  t.PRIMARY_TRANSACTION_QUANTITY AS "The slice's quantity in the item's primary UOM — SUM this at your grain for on-hand",
  t.TRANSACTION_QUANTITY AS "The slice's quantity in the transaction UOM it arrived in",
  t.TRANSACTION_UOM_CODE AS "The UOM TRANSACTION_QUANTITY is expressed in (the transaction ledger spells its equivalent TRANSACTION_UOM)",
  t.DATE_RECEIVED AS "When the slice was received — orders the FIFO stack",
  t.ORIG_DATE_RECEIVED AS "The original receipt date, preserved across moves",
  t.COST_GROUP_ID AS "Cost group of the slice (WMS/average-cost orgs)",
  t.LPN_ID AS "License plate the stock sits on (WMS orgs) — extends the on-hand grain",
  t.PROJECT_ID AS "Project the stock belongs to (project manufacturing orgs)",
  t.TASK_ID AS "Task within the project"
FROM <catalog>.<schema>.MTL_ONHAND_QUANTITIES_DETAIL t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.DATE_RECEIVED >= DATE '<DATE_FROM>'
  -- AND t.DATE_RECEIVED <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.ONHAND_QUANTITIES_ID;