# SAP QM Inspection Lot ABAP SELECT Generator (QALS and QAVE)

> Source: https://netray.co/tools/sap-qm-inspection-lot-abap-select-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in plant, inspection type and date range and get a ready ABAP SELECT joining the inspection lot table QALS to the usage decision table QAVE.

## Category

developer

## Erp

SAP S/4HANA

## Industry

Discrete Manufacturing

## Audience

- ABAP developer
- SAP QM consultant

## Tool

**Kind:** template

**Output:** code

**Language:** abap

**Fields:**

#### Plant (WERK)

**Key:** plant

**Type:** text

**Default:** 1000

#### Inspection type (ART)

**Key:** insp_type

**Type:** text

**Default:** 01

**Help:** Two character inspection type, for example 01 for goods receipt.

#### Lot created from (YYYYMMDD)

**Key:** date_from

**Type:** text

**Default:** 20260101

#### Usage decision code (VCODE)

**Key:** ud_code

**Type:** text

**Default:** A

**Help:** Your own code from the UD catalog. Not a fixed SAP value.

#### Maximum rows

**Key:** max_rows

**Type:** number

**Default:** 500

**Blocks:**

#### Open SQL: lots with usage decision

**Body:** REPORT z_qm_lots_with_ud.

TYPES: BEGIN OF ty_lot,
         prueflos TYPE qals-prueflos,
         werk     TYPE qals-werk,
         matnr    TYPE qals-matnr,
         art      TYPE qals-art,
         herkunft TYPE qals-herkunft,
         enstehdat TYPE qals-enstehdat,
         losmenge TYPE qals-losmenge,
         vcode    TYPE qave-vcode,
         vcodegrp TYPE qave-vcodegrp,
         vdatum   TYPE qave-vdatum,
       END OF ty_lot.

DATA lt_lots TYPE STANDARD TABLE OF ty_lot WITH EMPTY KEY.

SELECT a~prueflos, a~werk, a~matnr, a~art, a~herkunft,
       a~enstehdat, a~losmenge, b~vcode, b~vcodegrp, b~vdatum
  FROM qals AS a
  INNER JOIN qave AS b ON b~prueflos = a~prueflos
  WHERE a~werk = '{{plant}}'
    AND a~art = '{{insp_type}}'
    AND a~enstehdat >= '{{date_from}}'
    AND b~vcode = '{{ud_code}}'
  ORDER BY a~prueflos
  INTO TABLE @lt_lots
  UP TO {{max_rows}} ROWS.

IF sy-subrc <> 0.
  WRITE / 'No inspection lots found.'.
  RETURN.
ENDIF.

LOOP AT lt_lots ASSIGNING FIELD-SYMBOL(<ls_lot>).
  WRITE: / <ls_lot>-prueflos, <ls_lot>-matnr, <ls_lot>-vcode, <ls_lot>-vdatum.
ENDLOOP.

#### Variant: lots still without a usage decision

**Body:** SELECT a~prueflos, a~werk, a~matnr, a~art, a~enstehdat
  FROM qals AS a
  LEFT OUTER JOIN qave AS b ON b~prueflos = a~prueflos
  WHERE a~werk = '{{plant}}'
    AND a~art = '{{insp_type}}'
    AND a~enstehdat >= '{{date_from}}'
    AND b~prueflos IS NULL
  INTO TABLE @DATA(lt_open)
  UP TO {{max_rows}} ROWS.

**Notes:** Uses the newer Open SQL syntax with @ host variables, which needs ABAP 7.40 SP05 or later (all S/4HANA releases). Verify field names in SE11 for your release. A rejected lot is not identified by a fixed code: the codes come from your own usage decision catalog. Lots flagged for deletion should be excluded by reading status in JEST. Run in a sandbox first.

## Content

**Intro:** Custom QM reports usually start with the same join: the inspection lot in QALS and its usage decision in QAVE. This generator writes that query with your plant, inspection type and date filters so you can paste it into a report and extend it.

**Sections:**

#### How to use the generator

**Body:** Enter the plant, the inspection type, the earliest creation date in YYYYMMDD form and the usage decision code you want. The generator fills a complete report skeleton with typed fields, an inner join and a loop. Copy it into SE38 or ADT, activate and run. The second block flips the join to a left outer join to find lots that have no usage decision yet, which is the common backlog report for quality managers.

#### Why a join on PRUEFLOS

**Body:** QALS holds one row per inspection lot and QAVE holds the usage decision for that lot, so the inspection lot number PRUEFLOS is the join key. Using an inner join returns only decided lots; a left outer join with an IS NULL test on the QAVE side returns only undecided ones. Filtering on QALS date fields keeps the join small, which matters because QALS grows quickly in plants that inspect every goods receipt.

**Bullets:**

- Inner join: lots with a usage decision
- Left outer join with IS NULL: lots awaiting a decision
- Filter on WERK, ART and ENSTEHDAT first for speed
- Use UP TO n ROWS while testing

#### Extending the query

**Body:** Typical next steps are adding the material description from MAKT, the vendor from the lot header, or the lot status from JEST using the object number OBJNR. Add an authority check on plant using the standard QM authorization object before you release a report to users, and consider a CDS view if the data will feed analytics. If the lot quantity is needed in another unit, convert it with the standard unit conversion function rather than dividing by hand.

#### Common mistakes

**Body:** Hard coding a rejection code, forgetting that material numbers can be longer in S/4HANA, selecting without a date filter on a large QALS and skipping the authority check are the main risks. Also remember that a lot can be reset after the decision is changed, so the report shows the state at run time.

**Cta:** If you would rather let quality managers ask for open lots or rejected lots in plain language, we can build a read-only layer over your SAP data that shows the query it ran.

**Faq:**

#### How do I join inspection lots to usage decisions in ABAP?

**Answer:** Join QALS to QAVE on the inspection lot number PRUEFLOS. An inner join returns lots that have a usage decision. A left outer join with a test for NULL on the QAVE key returns lots that do not yet have one.

#### Which date field should I filter on in QALS?

**Answer:** ENSTEHDAT holds the creation date of the inspection lot and is a good general filter. Other date fields exist for the start and end of inspection, so pick the one that matches the report question and check names in SE11 for your release.

#### Can I run this query on ECC as well as S/4HANA?

**Answer:** The tables exist in both, but the @ host variable syntax needs ABAP 7.40 SP05 or higher. On older systems, remove the @ signs, declare the target explicitly and move the INTO clause as the older syntax requires.

#### How do I exclude deleted inspection lots?

**Answer:** Deletion flags are statuses, not columns in QALS. Join JEST on OBJNR and exclude lots that carry the deletion status, or use a standard function to read the status text. Check the exact status code in your system before filtering.

## Search terms

ABAP select QALS, SAP inspection lot ABAP query, QALS QAVE join, SAP QM ABAP report, inspection lots with usage decision ABAP, SAP QM custom report, ABAP open SQL inspection lot, QALS select statement

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json