MTL_TRANSACTION_ACCOUNTS

-- ============================================================
-- Table  : MTL_TRANSACTION_ACCOUNTS — Accounting distributions for material transactions — the debit and credit lines behind each material transaction, valued and pointed at a GL account combination
-- Purpose: Column-selected read of MTL_TRANSACTION_ACCOUNTS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) — see the Fields section for the full key
-- Caution: Many rows per TRANSACTION_ID (debits and credits that sum to zero) — never treat the transaction id as unique here
-- 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 these lines account for — many rows per id, summing to zero",
  t.ORGANIZATION_ID AS "Inventory organization",
  t.INVENTORY_ITEM_ID AS "Item id",
  t.REFERENCE_ACCOUNT AS "The GL account combination (CCID) the line posts to",
  t.TRANSACTION_DATE AS "Accounting date of the line",
  t.ACCOUNTING_LINE_TYPE AS "What the line represents (inventory valuation, offset, variance…)",
  t.BASE_TRANSACTION_VALUE AS "Signed value of the line in the ledger currency",
  t.PRIMARY_QUANTITY AS "Quantity behind the line in the primary UOM",
  t.GL_BATCH_ID AS "The GL transfer batch that swept the line to the ledger",
  t.COST_ELEMENT_ID AS "Cost element (material, overhead, resource…) the value belongs to",
  t.INV_SUB_LEDGER_ID AS "Subledger accounting link (R12 SLA)"
FROM <catalog>.<schema>.MTL_TRANSACTION_ACCOUNTS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- 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
;