RCV_TRANSACTIONS

-- ============================================================
-- Table  : RCV_TRANSACTIONS — The receiving event ledger — one immutable row per receiving action (receive, inspect, transfer, deliver, correct, return), chained to its prior step by parent transaction id; the event-grain truth behind every receipt quantity
-- Purpose: Column-selected read of RCV_TRANSACTIONS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + TRANSACTION_ID
-- Caution: One receipt writes multiple rows (RECEIVE then DELIVER, plus corrections and returns as separate signed rows) — filter TRANSACTION_TYPE or received quantities double-count
-- 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 "Surrogate key of the receiving event",
  t.TRANSACTION_TYPE AS "The receiving action — receive, accept, reject, transfer, deliver, correct, return; the code list is not Tier-1 enumerable, so it ships undecoded",
  t.TRANSACTION_DATE AS "When the event happened — the primary analysis date",
  t.ORGANIZATION_ID AS "The inventory org the event happened in",
  t.PARENT_TRANSACTION_ID AS "The prior step in the receive→inspect→deliver chain — the self-join that nets corrections",
  t.SHIPMENT_HEADER_ID AS "The receipt/shipment header",
  t.SHIPMENT_LINE_ID AS "The shipment line — where the item lives; this ledger has no item column",
  t.QUANTITY AS "Event quantity in the transaction UOM — sign semantics come from the transaction type",
  t.UNIT_OF_MEASURE AS "Transaction UOM, name form",
  t.PRIMARY_QUANTITY AS "Event quantity in the item's primary UOM — the column to aggregate",
  t.SOURCE_DOCUMENT_CODE AS "The document family being received against",
  t.DESTINATION_TYPE_CODE AS "Where this step puts the goods — receiving, inventory, expense, shop floor",
  t.PO_HEADER_ID AS "The PO, for PO receipts",
  t.PO_LINE_ID AS "The PO line",
  t.PO_LINE_LOCATION_ID AS "The PO shipment schedule the event executes against",
  t.PO_DISTRIBUTION_ID AS "The PO distribution, once delivery is distributed",
  t.SUBINVENTORY AS "Putaway subinventory on deliver rows",
  t.INSPECTION_STATUS_CODE AS "Inspection outcome — not inspected, accepted, or rejected"
FROM <catalog>.<schema>.RCV_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;