COOIS

-- ============================================================
-- T-Code : COOIS Production Order Information System
-- Purpose: Multi-level reporting on production orders with operations and status
-- Grain  : One row per production-order operation
-- Tables : AFKO, AFPO, AUFK, AFVC, AFVV, CRHD, MAKT, MARA, T001W
-- Notes  : AFVC MUST join via AUFPL (routing number), not AUFNR — otherwise you get a Cartesian product on operations. AUFK.OBJNR is the key into JEST for status lookups (REL, CNF, TECO, DLV). Operation dates (FSAVD/FSEDD) live on AFVV (quantities/dates), not AFVC (operation master data) — joined via AUFPL + APLZL.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.AUFNR   AS "Order Number",
  l.POSNR   AS "Order Item",
  op.VORNR  AS "Operation Number",

  -- Descriptive text
  ord.KTEXT AS "Order Short Text",
  mt.MAKTX  AS "Material Description",
  op.LTXA1  AS "Operation Short Text",

  -- Quantities + UOM (paired)
  h.GAMNG   AS "Target Quantity",
  l.PSMNG   AS "Item Quantity",
  l.WEMNG   AS "Delivered Quantity",
  (l.PSMNG - l.WEMNG) AS "Open Quantity",
  l.AMEIN   AS "Unit of Measure",

  -- Dates
  h.GSTRP   AS "Basic Start Date",
  h.GLTRP   AS "Basic Finish Date",
  h.FTRMI   AS "Actual Release Date",
  opd.FSAVD AS "Operation Earliest Start Date",
  opd.FSEDD AS "Operation Earliest End Date",

  -- Status / indicators
  ord.OBJNR AS "Object Number (JEST)",
  op.STEUS  AS "Control Key",
  ord.AUART AS "Order Type",

  -- Org fields
  l.DWERK   AS "Plant",
  pt.NAME1  AS "Plant Name",
  wc.ARBPL  AS "Work Center",

  -- Master data enrichment
  h.PLNBEZ  AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  h.DISPO   AS "MRP Controller",
  h.FEVOR   AS "Production Scheduler"
FROM <catalog>.<schema>.afko h
INNER JOIN <catalog>.<schema>.afpo l
  ON  l.MANDT = h.MANDT
  AND l.AUFNR = h.AUFNR
INNER JOIN <catalog>.<schema>.aufk ord
  ON  ord.MANDT = h.MANDT
  AND ord.AUFNR = h.AUFNR
INNER JOIN <catalog>.<schema>.afvc op
  ON  op.MANDT = h.MANDT
  AND op.AUFPL = h.AUFPL
LEFT JOIN <catalog>.<schema>.crhd wc
  ON  wc.MANDT = op.MANDT
  AND wc.OBJID = op.ARBID
LEFT JOIN <catalog>.<schema>.afvv opd
  ON  opd.MANDT = op.MANDT
  AND opd.AUFPL = op.AUFPL
  AND opd.APLZL = op.APLZL
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.DWERK
WHERE
  h.MANDT = '<MANDT>'
  AND l.DWERK = '<WERKS>'
  -- AND h.AUFNR = '<AUFNR>'
  -- AND h.GLTRP BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
ORDER BY h.GLTRP DESC, h.AUFNR, op.VORNR;