QM02

-- ============================================================
-- T-Code : QM02 Change Quality Notification
-- Purpose: Change an existing quality notification
-- Grain  : One row per notification item (QMEL header + QMFE item)
-- Tables : QMEL, QMFE, MAKT, MARA, LFA1, KNA1, T001W
-- Notes  : QMEL.QMART distinguishes notification types: 'Q1' customer complaint, 'Q2' vendor complaint, 'Q3' internal. Filter STTXT for status (OPEN, INPR, CMPL, etc.).
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.QMNUM   AS "Notification Number",
  l.FENUM   AS "Defect Item Number",

  -- Descriptive text
  h.QMTXT   AS "Notification Short Text",
  l.FETXT   AS "Defect Item Text",
  mt.MAKTX  AS "Material Description",

  -- Dates
  h.ERDAT   AS "Created On",
  h.QMDAT   AS "Notification Date",
  h.STRMN   AS "Required Start",
  h.LTRMN   AS "Required End",
  h.ASTRTDAT AS "Actual Start",
  h.AENDDT  AS "Actual End",

  -- Status / indicators
  h.QMART   AS "Notification Type",
  h.STTXT   AS "System Status Text",
  h.PRIOK   AS "Priority",
  h.AUSVN   AS "Breakdown Start",
  h.AUSBS   AS "Breakdown End",
  l.FEKAT   AS "Defect Code Group",
  l.FECOD   AS "Defect Code",

  -- Org fields
  h.MAWERK  AS "Plant (Material)",
  pt.NAME1  AS "Plant Name",

  -- Master data enrichment
  h.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  h.CHARG   AS "Batch",
  h.LIEFNR  AS "Vendor",
  v.NAME1   AS "Vendor Name",
  h.KUNUM   AS "Customer",
  c.NAME1   AS "Customer Name",
  h.AUFNR   AS "Order Number",
  h.ERNAM   AS "Created By"
FROM qmel h
LEFT JOIN qmfe l
  ON  l.MANDT = h.MANDT
  AND l.QMNUM = h.QMNUM
LEFT JOIN makt mt
  ON  mt.MANDT = h.MANDT
  AND mt.MATNR = h.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN mara m
  ON  m.MANDT = h.MANDT
  AND m.MATNR = h.MATNR
LEFT JOIN lfa1 v
  ON  v.MANDT = h.MANDT
  AND v.LIFNR = h.LIEFNR
LEFT JOIN kna1 c
  ON  c.MANDT = h.MANDT
  AND c.KUNNR = h.KUNUM
LEFT JOIN t001w pt
  ON  pt.MANDT = h.MANDT
  AND pt.WERKS = h.MAWERK
WHERE
  h.MANDT = '100'
  AND h.QMDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND h.QMART = '<QMART>'
  -- AND h.MAWERK = '<WERKS>'
ORDER BY h.QMDAT DESC, h.QMNUM, l.FENUM;