# SAP Batch Expiry Stock SQL Query Generator

> Source: https://netray.co/tools/sap-batch-expiry-stock-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a SQL query that lists batches whose shelf life expiry date falls in a chosen window, with stock by status, for chemical and specialty products.

## Category

sap

## Erp

SAP S/4HANA

## Industry

Chemical Manufacturing

## Audience

- SAP consultant
- Warehouse planner
- Quality manager

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Client (MANDT)

**Key:** client

**Type:** text

**Default:** 100

#### Plant (WERKS)

**Key:** plant

**Type:** text

**Default:** 1000

#### Expiry window (days from today)

**Key:** days_ahead

**Type:** number

**Default:** 90

#### Schema name

**Key:** schema

**Type:** text

**Default:** SAPHANADB

**Help:** Use your database schema, often SAPHANADB or SAPABAP1.

**Blocks:**

#### Batches expiring within the window

**Body:** SELECT b.MATNR,
       b.WERKS,
       b.LGORT,
       b.CHARG,
       m.LICHA AS VENDOR_BATCH,
       m.HSDAT AS MANUFACTURE_DATE,
       m.VFDAT AS EXPIRY_DATE,
       b.CLABS AS UNRESTRICTED,
       b.CINSM AS QUALITY_INSP,
       b.CSPEM AS BLOCKED
FROM {{schema}}.MCHB AS b
INNER JOIN {{schema}}.MCH1 AS m
        ON m.MANDT = b.MANDT
       AND m.MATNR = b.MATNR
       AND m.CHARG = b.CHARG
WHERE b.MANDT = '{{client}}'
  AND b.WERKS = '{{plant}}'
  AND m.VFDAT <> '00000000'
  AND m.VFDAT <= TO_VARCHAR(ADD_DAYS(CURRENT_DATE, {{days_ahead}}), 'YYYYMMDD')
  AND (b.CLABS + b.CINSM + b.CSPEM) > 0
ORDER BY m.VFDAT, b.MATNR, b.CHARG;

#### Total quantity at risk by material

**Body:** SELECT b.MATNR,
       COUNT(DISTINCT b.CHARG) AS BATCH_COUNT,
       SUM(b.CLABS) AS UNRESTRICTED_QTY
FROM {{schema}}.MCHB AS b
INNER JOIN {{schema}}.MCH1 AS m
        ON m.MANDT = b.MANDT
       AND m.MATNR = b.MATNR
       AND m.CHARG = b.CHARG
WHERE b.MANDT = '{{client}}'
  AND b.WERKS = '{{plant}}'
  AND m.VFDAT <> '00000000'
  AND m.VFDAT <= TO_VARCHAR(ADD_DAYS(CURRENT_DATE, {{days_ahead}}), 'YYYYMMDD')
GROUP BY b.MATNR
ORDER BY UNRESTRICTED_QTY DESC;

**Notes:** Read-only SQL for a replica or reporting database, not for production dialog use. Confirm table and column names on your release, check whether batches are managed at plant level (MCHA) in your configuration, and note that quantities are in base unit of measure. Date columns are character fields in YYYYMMDD format.

## Content

**Intro:** Chemical and specialty materials carry shelf lives, and expired batches turn into write-offs or quality incidents. This generator builds a HANA SQL query that joins the batch stock table MCHB to the batch master MCH1 and lists what is about to expire, with the stock split by status.

**Sections:**

#### How to use the generator

**Body:** Enter the client, plant, schema and the number of days you want to look ahead, then copy the SQL into your SQL console or reporting tool against a replica or reporting schema. The first query lists each batch with its manufacture date, expiry date and stock in unrestricted, quality inspection and blocked status. The second summarizes the unrestricted quantity per material so planners can prioritize what to consume, sell or retest first.

**Bullets:**

- Run against a read-only copy or replicated schema
- Change the window to 30, 60 or 180 days for different purposes
- Join MAKT afterward if you want material descriptions

#### How the tables fit together

**Body:** MCHB holds batch stock per material, plant, storage location and batch, split into unrestricted use (CLABS), quality inspection (CINSM) and blocked (CSPEM) quantities. MCH1 is the cross-plant batch master that holds the shelf life expiration date VFDAT, the date of manufacture HSDAT and the vendor batch LICHA. The join uses client, material and batch. A zero date in VFDAT means no expiry is maintained, which is why the query excludes it.

#### Common mistakes

**Body:** Frequent problems are querying without the client, comparing dates with the wrong format, and forgetting that expiry can be extended by a retest or recalculation. Stock in quality inspection or blocked status may still be relevant for write-off provisioning, so decide which quantities your report should count. For special stock such as consignment or project stock, check the separate stock tables and add them if required.

**Bullets:**

- Always filter by client
- Decide whether blocked and quality stock count as at risk
- Check special stock tables if you use consignment or project stock

**Cta:** For a conversational view of expiring stock without building SQL for each question, Netray can set up a read-only assistant over your SAP data that shows the query it ran.

**Faq:**

#### Where is the SAP batch expiry date stored?

**Answer:** In the batch master table MCH1 as VFDAT, the shelf life expiration date, with the manufacture date in HSDAT. In plant-level batch setups the data may also be held in MCHA. The stock itself sits in MCHB.

#### What do CLABS, CINSM and CSPEM mean?

**Answer:** They are the unrestricted-use, quality inspection and blocked stock quantities of a batch in MCHB. Together with restricted-use and in-transfer fields, they make up the total batch stock for the location.

#### Will this work outside HANA?

**Answer:** The functions ADD_DAYS and CURRENT_DATE are HANA syntax. For another database, replace the date arithmetic with its equivalent and keep the YYYYMMDD string comparison.

#### Why is a batch missing from the result?

**Answer:** Likely causes are no expiry date maintained, zero stock in all status fields, a different plant, or a batch managed on a different level. Check the batch master and the stock quantity for the material first.

## Search terms

sap batch expiry report, mchb mch1 join, sap shelf life expiration date table vfdat, sap expiring batches sql, sap batch stock query hana, mch1 vfdat hsdat, chemical batch expiry monitoring

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