QM01

-- ============================================================
-- T-Code : QM01 Create Quality Notification
-- Purpose: Create a quality notification with defect items
-- Grain  : One row per notification item (QMEL header + QMFE item)
-- Tables : QMEL, QMFE, QMIH, MAKT, MARA, LFA1, KNA1, T001W
-- Notes  : QMEL.QMART distinguishes notification types: 'Q1' customer complaint, 'Q2' vendor complaint, 'Q3' internal. System status is derived via QMEL.OBJNR -> JEST -> TJ30T/TJ02T, not stored on QMEL.
-- ============================================================
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",

  -- Status / indicators
  h.QMART   AS "Notification Type",
  h.PRIOK   AS "Priority",
  mi.AUSVN  AS "Breakdown Start",
  mi.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.LIFNUM  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 <catalog>.<schema>.qmel h
LEFT JOIN <catalog>.<schema>.qmfe l
  ON  l.MANDT = h.MANDT
  AND l.QMNUM = h.QMNUM
LEFT JOIN <catalog>.<schema>.qmih mi
  ON  mi.MANDT = h.MANDT
  AND mi.QMNUM = h.QMNUM
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = h.MANDT
  AND mt.MATNR = h.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = h.MANDT
  AND m.MATNR = h.MATNR
LEFT JOIN <catalog>.<schema>.lfa1 v
  ON  v.MANDT = h.MANDT
  AND v.LIFNR = h.LIFNUM
LEFT JOIN <catalog>.<schema>.kna1 c
  ON  c.MANDT = h.MANDT
  AND c.KUNNR = h.KUNUM
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = h.MANDT
  AND pt.WERKS = h.MAWERK
WHERE
  h.MANDT = '<MANDT>'
  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;