WIE_OPERATION_TRANSACTIONS

-- ============================================================
-- Table  : WIE_OPERATION_TRANSACTIONS — The shop-floor product ledger — one row per movement of primary-product quantity through operation dispatch states: completions, moves, scrap, reject, and their reversals
-- Purpose: Column-selected read of WIE_OPERATION_TRANSACTIONS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + WO_OPERATION_TRANSACTION_ID
-- Caution: The transaction type is really a from-state → to-state pair, and reversals write new rows moving quantity back — net the rows, don't count events
-- 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.WO_OPERATION_TRANSACTION_ID AS "Surrogate key of the shop-floor event",
  t.TRANSACTION_TYPE_CODE AS "Event type, derived from the from/to dispatch states",  -- decode t.TRANSACTION_TYPE_CODE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'ORA_WIE_OP_TRANSACTION_TYPE', LANGUAGE-filtered)
  t.TRANSACTION_DATE AS "When the event was reported",
  t.TRANSACTION_QUANTITY AS "Quantity moved by this event",
  t.TRANSACTION_UOM_CODE AS "UOM of the transacted quantity",
  t.PRIMARY_QUANTITY AS "Quantity converted to the primary UOM",
  t.ORGANIZATION_ID AS "Plant",
  t.WORK_ORDER_ID AS "Work order context",
  t.WO_OPERATION_ID AS "Operation the product is transacted from",
  t.FROM_DISPATCH_STATE AS "Source dispatch state (ready, complete, scrap, reject…)",  -- decode t.FROM_DISPATCH_STATE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'ORA_WIE_DISPATCH_STATE', LANGUAGE-filtered)
  t.TO_DISPATCH_STATE AS "Destination dispatch state",  -- decode t.TO_DISPATCH_STATE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'ORA_WIE_DISPATCH_STATE', LANGUAGE-filtered)
  t.REASON_CODE AS "Scrap/reject reason",
  t.INVENTORY_ITEM_ID AS "Primary product on the work order",
  t.INV_TRANSACTION_ID AS "The inventory ledger row a completion wrote — the documented link into stock"
FROM <catalog>.<schema>.WIE_OPERATION_TRANSACTIONS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the business unit — see quirks guide #item-org-striping
  -- AND t.WO_OPERATION_TRANSACTION_ID = <WO_OPERATION_TRANSACTION_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.WO_OPERATION_TRANSACTION_ID;