QA01

-- ============================================================
-- T-Code : QA01 Create Inspection Lot
-- Purpose: Create an inspection lot for a material
-- Grain  : One row per inspection lot (QALS), optionally with usage decision
-- Tables : QALS, QAVE, MAKT, MARA, T001W, T001L, LFA1
-- Notes  : QAVE is the usage decision — LEFT JOIN so lots without a UD still appear (deduplicated to the latest decision per lot when more than one exists). STAT = '1' means new lot; '2' = released; '5' = posted; '7' = UD made. Filter HERKUNFT = '01' for GR inspections, '04' for production. QALS/QAVE's client field is MANDANT, not MANDT.
-- ============================================================
SELECT
  -- Keys
  q.MANDANT    AS "Client",
  q.PRUEFLOS   AS "Inspection Lot",

  -- Descriptive text
  mt.MAKTX     AS "Material Description",
  q.KTEXTLOS   AS "Short Text",

  -- Quantities + UOM (paired)
  q.LOSMENGE   AS "Lot Size",
  q.MENGENEINH AS "Unit of Measure",
  q.GESSTICHPR AS "Sample Size",
  q.LMENGEPR   AS "Quantity Inspected",
  q.LMENGESCH  AS "Defective Quantity",

  -- Dates
  q.ENSTEHDAT  AS "Created On",
  q.PASTRTERM  AS "Inspection Start",
  q.PAENDTERM  AS "Inspection End",
  ud.VDATUM    AS "Usage Decision Date",

  -- Status / indicators
  q.STAT35     AS "UD Made (Status)",
  q.STAT34     AS "Stock-Posted Status",
  q.ART        AS "Inspection Type",
  q.HERKUNFT   AS "Lot Origin",
  ud.VCODEGRP  AS "UD Code Group",
  ud.VCODE     AS "UD Code",

  -- Org fields
  q.WERK       AS "Plant",
  pt.NAME1     AS "Plant Name",
  q.LAGORTCHRG AS "Storage Location",
  sl.LGOBE     AS "Storage Location Name",

  -- Master data enrichment
  q.MATNR      AS "Material Number",
  m.MTART      AS "Material Type",
  m.MATKL      AS "Material Group",
  q.CHARG      AS "Batch",
  q.LIFNR      AS "Vendor",
  v.NAME1      AS "Vendor Name",
  q.MBLNR      AS "Material Document (GR)",
  q.AUFNR      AS "Order Number"
FROM <catalog>.<schema>.qals q
LEFT JOIN (
  SELECT * FROM <catalog>.<schema>.qave
  QUALIFY ROW_NUMBER() OVER (PARTITION BY MANDANT, PRUEFLOS ORDER BY ZAEHLER DESC) = 1
) ud
  ON  ud.MANDANT = q.MANDANT
  AND ud.PRUEFLOS = q.PRUEFLOS
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = q.MANDANT
  AND mt.MATNR = q.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = q.MANDANT
  AND m.MATNR = q.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = q.MANDANT
  AND pt.WERKS = q.WERK
LEFT JOIN <catalog>.<schema>.t001l sl
  ON  sl.MANDT = q.MANDANT
  AND sl.WERKS = q.WERK
  AND sl.LGORT = q.LAGORTCHRG
LEFT JOIN <catalog>.<schema>.lfa1 v
  ON  v.MANDT = q.MANDANT
  AND v.LIFNR = q.LIFNR
WHERE
  q.MANDANT = '<MANDT>'
  AND q.ENSTEHDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND q.WERK = '<WERKS>'
  -- AND q.MATNR = '<MATNR>'
  -- AND q.STAT35 = 'X'  -- UD made
ORDER BY q.ENSTEHDAT DESC, q.PRUEFLOS;