MTL_MATERIAL_TRANSACTIONS

-- ============================================================
-- Table  : MTL_MATERIAL_TRANSACTIONS — The material transaction ledger — one row per inventory movement or cost update, classified by transaction type, action, and source type, with quantity, date, and the document reference that caused it; the reconciliation backbone for every stock question
-- Purpose: Column-selected read of MTL_MATERIAL_TRANSACTIONS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + TRANSACTION_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.TRANSACTION_ID AS "Transaction id — the ledger's key; distributions, lot, and serial detail join on it",
  t.INVENTORY_ITEM_ID AS "Item id",
  t.ORGANIZATION_ID AS "Inventory organization the movement happened in",
  t.SUBINVENTORY_CODE AS "The subinventory the movement touched",
  t.LOCATOR_ID AS "The locator the movement touched",
  t.TRANSACTION_TYPE_ID AS "The transaction type — the user-facing classification; decodes via MTL_TRANSACTION_TYPES",
  t.TRANSACTION_ACTION_ID AS "The physical action (issue, receipt, transfer…) behind the type",  -- decode t.TRANSACTION_ACTION_ID via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION', LANGUAGE-filtered) — see quirks guide #lookups
  t.TRANSACTION_SOURCE_TYPE_ID AS "What kind of document drove the movement (purchase order, sales order, job…)",
  t.TRANSACTION_SOURCE_ID AS "The driving document's id — what it points at depends on the source type",
  t.TRANSACTION_QUANTITY AS "Signed quantity in the transaction UOM",
  t.PRIMARY_QUANTITY AS "Signed quantity in the item's primary UOM — the column to aggregate",
  t.TRANSACTION_UOM AS "The UOM the transaction was entered in (the on-hand table spells its equivalent TRANSACTION_UOM_CODE)",
  t.TRANSACTION_DATE AS "When the movement happened — the primary analysis date",
  t.TRANSFER_ORGANIZATION_ID AS "Counterpart org on inter-org transfers",
  t.ACTUAL_COST AS "Unit cost the movement was valued at",
  t.REVISION AS "Item revision the movement applied to",
  t.TRANSACTION_REFERENCE AS "Free-form reference entered with the transaction",
  t.RCV_TRANSACTION_ID AS "The receiving transaction that drove this movement, for receipt-sourced rows — the cross-ledger link to receiving"
FROM <catalog>.<schema>.MTL_MATERIAL_TRANSACTIONS 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.TRANSACTION_DATE >= DATE '<DATE_FROM>'
  -- AND t.TRANSACTION_DATE <= 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.TRANSACTION_ID;