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 at minimum, plus corrections as new rows) — filter TRANSACTION_TYPE or received quantities double-count
-- 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 receiving event",
  t.PARENT_TRANSACTION_ID AS "The parent event in the chain — walk it to net a receipt line",
  t.SHIPMENT_HEADER_ID AS "Shipment header the event belongs to",
  t.SHIPMENT_LINE_ID AS "Shipment line the event acts on — the item lives there, not here",
  t.TRANSACTION_TYPE AS "Event type — receive, inspect, transfer, deliver, correct, return",  -- decode t.TRANSACTION_TYPE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'RCV_TRANSACTION_TYPE', LANGUAGE-filtered)
  t.TRANSACTION_DATE AS "When the receiving event occurred",
  t.QUANTITY AS "Event quantity in the transaction UOM",
  t.UOM_CODE AS "Transaction UOM code",
  t.PRIMARY_QUANTITY AS "Event quantity in the item's primary UOM",
  t.ORGANIZATION_ID AS "Receiving inventory org where the event was created",
  t.SOURCE_DOCUMENT_CODE AS "Source document type behind the event",  -- decode t.SOURCE_DOCUMENT_CODE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'RCV_SOURCE_DOCUMENT_TYPE', LANGUAGE-filtered)
  t.PO_HEADER_ID AS "PO header pointer",
  t.PO_LINE_ID AS "PO line pointer",
  t.PO_LINE_LOCATION_ID AS "PO schedule pointer — the schedule's cumulative buckets roll up from these events",
  t.DESTINATION_TYPE_CODE AS "Destination type at event time",
  t.SUBINVENTORY AS "Destination subinventory of the event",
  t.INSPECTION_STATUS_CODE AS "Not inspected, accepted, or rejected",
  t.VENDOR_ID AS "Supplier on the event"
FROM <catalog>.<schema>.RCV_TRANSACTIONS 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;