SAP quality data in the lakehouse: QALS, QAVE, and the notification fan-out

Table set. QALS is the lot: one row per inspection lot (key PRUEFLOS), created by goods receipt, production, or manually — the origin field HERKUNFT says which. It carries the vendor (LIFNR) and purchasing document (EBELN/EBELP) directly for GR-sourced lots, so vendor acceptance needs no purchasing join. QAVE is the usage decision. Its key is not just the lot: PRUEFLOS + decision type (KZART) + a counter (ZAEHLER) — the structure allows multiple decision rows per lot, so "the" usage decision is the latest counter, chosen deliberately. The decision's accept/reject verdict is VBEWERTUNG: A accepted, R rejected, blank non-valuated.

The trap. Lots without a usage decision yet (QALS.STAT35 not set, no QAVE row) are open, not rejected. A LEFT JOIN that treats NULL as fail understates every vendor with recent receipts. Count acceptance over decided lots and report open lots separately.

Grain. One row per inspection lot, with the chosen QAVE row attached. Vendor acceptance is an aggregate over decided lots.

-- Vendor acceptance rate over decided inspection lots
-- QAVE can hold multiple decision rows per lot (key: PRUEFLOS, KZART, ZAEHLER)
-- — take the latest counter per lot before judging accept/reject.
-- QALS and QAVE name the client field MANDANT, not MANDT.
WITH latest_ud AS (
  SELECT
    q.mandant,
    q.prueflos,
    q.vbewertung,
    row_number() OVER (
      PARTITION BY q.mandant, q.prueflos
      ORDER BY q.zaehler DESC, q.kzart
    ) AS rn
  FROM bronze_sap.qave AS q
  WHERE q.mandant = '100'
)
SELECT
  l.lifnr                                            AS vendor,
  count(*)                                           AS decided_lots,
  count_if(u.vbewertung = 'A')                       AS accepted_lots,
  count_if(u.vbewertung = 'R')                       AS rejected_lots,
  -- decided lots include non-valuated (blank) decisions; accepted + rejected can be < decided
  round(100.0 * count_if(u.vbewertung = 'A') / count(*), 1)
                                                     AS acceptance_pct
FROM bronze_sap.qals AS l
JOIN latest_ud AS u
  ON  u.mandant  = l.mandant
  AND u.prueflos = l.prueflos
  AND u.rn       = 1
WHERE l.mandant = '100'
  AND l.lifnr  <> ''
GROUP BY l.lifnr
ORDER BY decided_lots DESC;

Production-origin lots come out of the order flow — order status, confirmations, and the movements behind them are in the production orders guide →

Table pages: QALS, QAVE→

Table set.The lot's quantity story is on QALS itself: LOSMENGE (lot quantity), then the stock-posting split — LMENGE01 (to unrestricted use), LMENGE02 (to scrap), LMENGE04 (to blocked stock), LMENGEZUB (still to be posted) — with STAT34 flagging that stock postings are complete. These are physical destinations, not verdicts: an accepted lot can still send part of its quantity to scrap, and a rejected lot can be returned to the vendor rather than scrapped. Acceptance (the section above) and disposition (this one) are different measures — model both, label them honestly.

Grain. Still one row per lot; the split fields are additive within the lot. Lots with LMENGEZUB > 0 are in flight — exclude or flag them in disposition reporting, or your percentages move every day for reasons nobody can explain.

Table pages: QALS→

Table set. QMEL is the notification header (key QMNUM, type QMART, vendor as LIFNUM — note the different field name from QALS.LIFNR). Beneath it fan out QMFE (defect items), QMUR (causes), QMSM (tasks), QMMA (activities) — all keyed QMNUM plus their own counters. A lot-origin notification carries PRUEFLOS, tying the complaint back to the inspection lot. Defect codes on QMFE (FEKAT catalog / FEGRP code group / FECOD code) decode through QPCD, with texts in QPCT.

The trap. Three counting grains: notifications (customer complaints received), defects (QMFE rows — one notification can carry many), causes (QMUR— many per defect). "Defect count by vendor" triples when someone joins all three levels and counts rows. Pick the grain per metric, aggregate children before joining upward.

-- Defect Pareto by code group and code, at defect grain (QMFE)
-- QPCT text join: language- and version-dependent — pin both. Defects are
-- counted BEFORE the text join, which is why the counts cannot fan out on it.
WITH defects AS (
  SELECT
    f.fekat,
    f.fegrp,
    f.fecod,
    count(*)                                 AS defect_count,
    count(DISTINCT f.qmnum)                  AS notifications_affected
  FROM bronze_sap.qmfe AS f
  WHERE f.mandt = '100'
  GROUP BY f.fekat, f.fegrp, f.fecod
)
SELECT
  d.fekat                                    AS catalog,
  d.fegrp                                    AS code_group,
  d.fecod                                    AS defect_code,
  max(t.kurztext)                            AS defect_text,
  d.defect_count,
  d.notifications_affected
FROM defects AS d
LEFT JOIN bronze_sap.qpct AS t
  ON  t.mandt      = '100'
  AND t.katalogart = d.fekat
  AND t.codegruppe = d.fegrp
  AND t.code       = d.fecod
  AND t.sprache    = 'E'
GROUP BY d.fekat, d.fegrp, d.fecod, d.defect_count, d.notifications_affected
ORDER BY d.defect_count DESC;

Table pages: QMEL, QMFE, QMUR, QMSM, QMMA, QPCD→

Where controlling enters. Every PRCTR column in this reference points at CEPC — among them the delivery item (LIPS.PRCTR), the production order (AUFK.PRCTR), the account line (ACDOCA.PRCTR), and the purchasing account assignment (EKKN.PRCTR). The moment a quality or logistics question becomes "…by profit center," this join appears.

The trap. CEPC is time-dependent master data: its key is profit center + controlling area + valid-to date (PRCTR, KOKRS, DATBI), one row per validity period. Joining on PRCTR alone multiplies your fact rows by the number of validity periods the profit center ever had. Join as-of: the transaction date between valid-from and valid-to, plus the controlling area.

-- As-of profit-center join: delivery lines to the profit center valid
-- on the goods-issue date. Grain-preserving: at most one CEPC row matches.
SELECT
  i.vbeln                                   AS delivery,
  i.posnr                                   AS delivery_item,
  i.prctr                                   AS profit_center,
  c.datbi                                   AS valid_to_raw
FROM bronze_sap.lips AS i
JOIN bronze_sap.likp AS h
  ON  h.mandt = i.mandt
  AND h.vbeln = i.vbeln
LEFT JOIN bronze_sap.cepc AS c
  ON  c.mandt = i.mandt
  AND c.prctr = i.prctr
  AND c.kokrs = '1000'                      -- your controlling area
  -- DATS zeros mean "unset", not a date — nullif before casting, same as WADAT_IST below
  AND to_date(h.wadat_ist, 'yyyyMMdd')
        BETWEEN to_date(nullif(c.datab, '00000000'), 'yyyyMMdd')
            AND to_date(nullif(c.datbi, '00000000'), 'yyyyMMdd')
WHERE i.mandt = '100'
  AND i.prctr <> ''
  AND h.wadat_ist <> '00000000';

The WADAT_IST zero-date guard in that query is the same trap documented in the deliveries guide →

Table pages: CEPC, LIPS, LIKP, AUFK, ACDOCA, EKKN→

• QALS, QAVE, and the QMEL family are transparent tables; direct replication works. No simplification-list entry restructuring these tables shows up in published material — an absence, not an SAP statement; verify against the simplification list for your release.
• Volumes are modest next to material movements: lots grow with goods receipts and production orders, notifications with complaints. The tall table in this domain is results data (QAMR/QASE, characteristic-level), which this guide deliberately leaves out of gold until a question actually needs it.
• Usage decisions arrive later than lots (days to weeks) — a lot extracted today gets its QAVE row and STAT35 flip later. Late-arriving decisions change already-reported acceptance rates; recompute a trailing window, same pattern as movement reversals.
• CEPC is small, slowly changing, and ships its own validity intervals — extract full, every run.

• Bronze: QALS, QAVE, QMEL + children, QPCD/QPCT, CEPC — raw, all clients.
• Silver: one client. inspection_lot (QALS conformed, one row per lot), usage_decision (latest-counter QAVE row per lot — the dedupe lives here, once), notification / notification_defect (QMEL / QMFE conformed, defect codes decoded), profit_center (CEPC validity periods intact).
• Gold: fct_inspection_lot at lot grain (decided flag, verdict, disposition split, vendor key, material key, date roles for lot creation and decision) and fct_notification_defect at defect grain. dim_profit_center comes straight from CEPC's validity periods — it is already a type-2 dimension; keep the intervals and join as-of. Serves vendor acceptance, defect Pareto, complaint rates, and any "…by profit center" cut.