CO11N

-- ============================================================
-- T-Code : CO11N Enter Production Order Confirmation
-- Purpose: Record operation confirmations (yield, scrap, rework, activity quantities)
-- Grain  : One row per confirmation record (AFRU)
-- Tables : AFRU, AFVC (optional), AUFK, MAKT, T001W
-- Notes  : AFRU.STOKZ = 'X' marks a reversed confirmation — filter it out for production reports. AFRU and AFVC join via AUFPL + APLZL + PLNFL (not AUFNR).
-- ============================================================
SELECT
  -- Keys
  a.MANDT   AS "Client",
  a.RUECK   AS "Confirmation Number",
  a.RMZHL   AS "Confirmation Counter",
  a.AUFNR   AS "Order Number",
  a.VORNR   AS "Operation Number",

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

  -- Quantities + UOM (paired)
  a.LMNGA   AS "Yield (Confirmed)",
  a.XMNGA   AS "Scrap (Confirmed)",
  a.RMNGA   AS "Rework (Confirmed)",
  a.MEINH   AS "Unit of Measure",
  a.ISM01   AS "Actual Activity 1",
  a.ISM02   AS "Actual Activity 2",

  -- Dates
  a.BUDAT   AS "Posting Date",
  a.ISDD    AS "Confirmed Start Date",
  a.IEDD    AS "Confirmed Finish Date",
  a.ERSDA   AS "Created On",

  -- Status / indicators
  a.STOKZ   AS "Reversal Indicator",
  a.AUERU   AS "Automatic Goods Receipt",
  ord.AUART AS "Order Type",

  -- Org fields
  a.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  a.ARBPL   AS "Work Center",

  -- Master data enrichment
  a.AUFPL   AS "Routing Number",
  a.APLZL   AS "Operation General Counter",
  a.PLNFL   AS "Sequence",
  a.ERNAM   AS "Entered By"
FROM afru a
LEFT JOIN aufk ord
  ON  ord.MANDT = a.MANDT
  AND ord.AUFNR = a.AUFNR
LEFT JOIN makt mt
  ON  mt.MANDT = a.MANDT
  AND mt.MATNR = ord.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN t001w pt
  ON  pt.MANDT = a.MANDT
  AND pt.WERKS = a.WERKS
WHERE
  a.MANDT = '100'
  AND a.AUFNR = '<AUFNR>'
  AND a.STOKZ = ''
ORDER BY a.BUDAT DESC, a.RUECK, a.RMZHL;