INV_MATERIAL_TXNS

-- ============================================================
-- Table  : INV_MATERIAL_TXNS — 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 INV_MATERIAL_TXNS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + TRANSACTION_ID
-- Notes  : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. 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 "Surrogate key of the movement",
  t.INVENTORY_ITEM_ID AS "Item transacted",
  t.ORGANIZATION_ID AS "Inventory org where the transaction happened",
  t.SUBINVENTORY_CODE AS "Subinventory transacted against",
  t.LOCATOR_ID AS "Stock locator",
  t.TRANSACTION_TYPE_ID AS "Transaction type (miscellaneous issue, PO receipt…)",
  t.TRANSACTION_ACTION_ID AS "Underlying action (issue, receipt, transfer) — a VARCHAR2 code in the Fusion dictionary, unlike EBS",
  t.TRANSACTION_SOURCE_TYPE_ID AS "Class of source document driving the transaction",
  t.TRANSACTION_SOURCE_ID AS "Id of the specific source record",
  t.TRX_SOURCE_LINE_ID AS "Source document line id",
  t.TRANSACTION_QUANTITY AS "Signed quantity in the entered UOM",
  t.TRANSACTION_UOM AS "Entered unit of measure",
  t.PRIMARY_QUANTITY AS "Signed quantity converted to the item's primary UOM — the column to SUM",
  t.TRANSACTION_DATE AS "When the transaction was processed",
  t.TRANSFER_ORGANIZATION_ID AS "Counterpart org on transfers",
  t.TRANSFER_TRANSACTION_ID AS "Links the paired row of a two-row transfer",
  t.ACTUAL_COST AS "Costed actual cost of the transaction",
  t.REASON_ID AS "Transaction reason code id"
FROM <catalog>.<schema>.INV_MATERIAL_TXNS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the business unit — see quirks guide #item-org-striping
  -- 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>'  -- the column incremental BICC extracts key on
ORDER BY t.TRANSACTION_ID;