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, AFPO, AFVC (optional), AUFK, CRHD, 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). AUFK has no MATNR field — material comes from AFPO (order item table, keyed AUFNR+POSNR); joining on AUFNR alone fans out one row per AFPO item per confirmation for multi-item (co-product) orders. AFRU has no ARBPL field — the readable work-center code is reached via CRHD (ARBID = OBJID).
-- ============================================================
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",
  wc.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 <catalog>.<schema>.afru a
LEFT JOIN <catalog>.<schema>.aufk ord
  ON  ord.MANDT = a.MANDT
  AND ord.AUFNR = a.AUFNR
LEFT JOIN <catalog>.<schema>.afpo po
  ON  po.MANDT = a.MANDT
  AND po.AUFNR = a.AUFNR
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = a.MANDT
  AND mt.MATNR = po.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.crhd wc
  ON  wc.MANDT = a.MANDT
  AND wc.OBJTY = 'A'
  AND wc.OBJID = a.ARBID
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = a.MANDT
  AND pt.WERKS = a.WERKS
WHERE
  a.MANDT = '<MANDT>'
  AND a.AUFNR = '<AUFNR>'
  AND a.STOKZ = ''
ORDER BY a.BUDAT DESC, a.RUECK, a.RMZHL;