MTL_TRANSACTION_LOT_NUMBERS

-- ============================================================
-- Table  : MTL_TRANSACTION_LOT_NUMBERS — Lot-level detail for material transactions — one row per lot consumed or received per material transaction, with the lot's share of the quantity
-- Purpose: Column-selected read of MTL_TRANSACTION_LOT_NUMBERS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + TRANSACTION_ID + LOT_NUMBER
-- 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.TRANSACTION_ID AS "The material transaction the lot detail belongs to",
  t.LOT_NUMBER AS "The lot consumed or received",
  t.ORGANIZATION_ID AS "Inventory organization",
  t.INVENTORY_ITEM_ID AS "Item id",
  t.TRANSACTION_QUANTITY AS "The lot's share of the movement, in the transaction UOM",
  t.PRIMARY_QUANTITY AS "The lot's share in the primary UOM",
  t.SERIAL_TRANSACTION_ID AS "Bridge to serial detail for lot-and-serial-controlled items"
FROM <catalog>.<schema>.MTL_TRANSACTION_LOT_NUMBERS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.TRANSACTION_ID = <TRANSACTION_ID>
  -- AND t.LOT_NUMBER = '<LOT_NUMBER>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.TRANSACTION_ID;