WIP_OPERATIONS

-- ============================================================
-- Table  : WIP_OPERATIONS — Operations on a job or repetitive schedule — the shop-floor step sequence with department, live quantity counters per intraoperation step (queue, run, to-move, reject, scrap), and first/last-unit schedule dates
-- Purpose: Column-selected read of WIP_OPERATIONS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + WIP_ENTITY_ID + OPERATION_SEQ_NUM + REPETITIVE_SCHEDULE_ID
-- Caution: The quantity columns are live position counters, not history — summing them across extracts double-counts; the event history is the move transaction ledger
-- 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.WIP_ENTITY_ID AS "The job or schedule",
  t.OPERATION_SEQ_NUM AS "Operation sequence within the job's routing",
  t.ORGANIZATION_ID AS "Inventory organization — part of the key",
  t.REPETITIVE_SCHEDULE_ID AS "Repetitive schedule — NULL for discrete jobs, but part of the unique key",
  t.OPERATION_SEQUENCE_ID AS "The BOM routing operation this one came from",
  t.STANDARD_OPERATION_ID AS "The standard operation reference",
  t.DEPARTMENT_ID AS "The department (work center) running the operation",
  t.QUANTITY_IN_QUEUE AS "Units in the Queue step right now — a live counter, not history",
  t.QUANTITY_RUNNING AS "Units in the Run step",
  t.QUANTITY_WAITING_TO_MOVE AS "Units in the To Move step",
  t.QUANTITY_REJECTED AS "Units in the Reject step",
  t.QUANTITY_SCRAPPED AS "Units scrapped from the operation",
  t.QUANTITY_COMPLETED AS "Units completed through the operation",
  t.FIRST_UNIT_START_DATE AS "First unit's scheduled start — the schedule-side analysis date",
  t.LAST_UNIT_START_DATE AS "Last unit's scheduled start",
  t.LAST_UNIT_COMPLETION_DATE AS "Last unit's scheduled completion",
  t.DATE_LAST_MOVED AS "When a move last touched the operation — the activity-recency signal",
  t.COUNT_POINT_TYPE AS "Count-point/autocharge behavior of the operation"
FROM <catalog>.<schema>.WIP_OPERATIONS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.WIP_ENTITY_ID = <WIP_ENTITY_ID>
  -- AND t.OPERATION_SEQ_NUM = <OPERATION_SEQ_NUM>
  -- AND t.FIRST_UNIT_START_DATE >= DATE '<DATE_FROM>'
  -- AND t.FIRST_UNIT_START_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.WIP_ENTITY_ID;