COHV

-- ============================================================
-- T-Code : COHV Production Orders: Mass Processing
-- Purpose: Mass-processing view of production orders (same tables as COOIS, typically used for bulk release / TECO)
-- Grain  : One row per production-order operation
-- Tables : AFKO, AFPO, AUFK, AFVC, CRHD, MAKT, MARA, T001W
-- Notes  : COHV and COOIS read the same tables — COHV is the execution view (mass release, TECO, rescheduling). The JEST status filter is the primary differentiator in practice.
-- ============================================================
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)
  l.PSMNG   AS "Order 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",
  h.GLTRP   AS "Basic Finish",
  op.FSAVD  AS "Operation Start",
  op.FSEDD  AS "Operation End",

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

  -- 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"
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
LEFT 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>.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.GLTRP BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
ORDER BY h.GLTRP, h.AUFNR, op.VORNR;