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. STAT = '1' means new lot; '2' = released; '5' = posted; '7' = UD made. Filter HERKUNFT = '01' for GR inspections, '04' for production.
-- ============================================================
SELECT
  -- Keys
  q.MANDT      AS "Client",
  q.PRUEFLOS   AS "Inspection Lot",

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

  -- Quantities + UOM (paired)
  q.LOSMENGE   AS "Lot Size",
  q.MEINS      AS "Unit of Measure",
  q.STICHPRMGE AS "Sample Size",
  q.APRUEFMGE  AS "Accepted Quantity",
  q.RPRUEFMGE  AS "Rejected 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.KZART      AS "Lot Type",
  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 qals q
LEFT JOIN qave ud
  ON  ud.MANDT = q.MANDT
  AND ud.PRUEFLOS = q.PRUEFLOS
LEFT JOIN makt mt
  ON  mt.MANDT = q.MANDT
  AND mt.MATNR = q.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN mara m
  ON  m.MANDT = q.MANDT
  AND m.MATNR = q.MATNR
LEFT JOIN t001w pt
  ON  pt.MANDT = q.MANDT
  AND pt.WERKS = q.WERK
LEFT JOIN t001l sl
  ON  sl.MANDT = q.MANDT
  AND sl.WERKS = q.WERK
  AND sl.LGORT = q.LAGORTCHRG
LEFT JOIN lfa1 v
  ON  v.MANDT = q.MANDT
  AND v.LIFNR = q.LIFNR
WHERE
  q.MANDT = '100'
  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;