WIP_REQUIREMENT_OPERATIONS

-- ============================================================
-- Table  : WIP_REQUIREMENT_OPERATIONS — Material requirements per job and operation — the job's exploded BOM with required vs issued quantities, supply type, and the supply subinventory issues pull from
-- Purpose: Column-selected read of WIP_REQUIREMENT_OPERATIONS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + WIP_ENTITY_ID + INVENTORY_ITEM_ID + OPERATION_SEQ_NUM + REPETITIVE_SCHEDULE_ID
-- Caution: Grain is component × operation × job — joining to the job multiplies rows, and open issue quantity is REQUIRED_QUANTITY − QUANTITY_ISSUED (arithmetic, no stored column)
-- 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.INVENTORY_ITEM_ID AS "The component item — part of the key; the same component can appear at several operations",
  t.OPERATION_SEQ_NUM AS "The operation the component is required at",
  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.DEPARTMENT_ID AS "The using department",
  t.WIP_SUPPLY_TYPE AS "Supply method — push, pull, phantom, bulk, supplier; numeric, undecoded",
  t.DATE_REQUIRED AS "When the component is required — the analysis date",
  t.REQUIRED_QUANTITY AS "Total quantity required",
  t.QUANTITY_ISSUED AS "Quantity issued to date — open balance is required minus issued",
  t.QUANTITY_PER_ASSEMBLY AS "Usage per assembly",
  t.COMPONENT_YIELD_FACTOR AS "Yield factor applied to the requirement",
  t.QUANTITY_ALLOCATED AS "Quantity allocated by picking",
  t.QUANTITY_BACKORDERED AS "Quantity backordered",
  t.SUPPLY_SUBINVENTORY AS "The subinventory issues pull from",
  t.SUPPLY_LOCATOR_ID AS "The locator issues pull from",
  t.MRP_NET_FLAG AS "Whether planning nets the requirement"
FROM <catalog>.<schema>.WIP_REQUIREMENT_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.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.OPERATION_SEQ_NUM = <OPERATION_SEQ_NUM>
  -- AND t.DATE_REQUIRED >= DATE '<DATE_FROM>'
  -- AND t.DATE_REQUIRED <= 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;