# Batch Genealogy SQL Query Generator (Forward and Backward Trace)

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

## Short answer

Fill in plant, material and batch to produce SQL for forward and backward batch traces on SAP material documents.

## Category

developer

## Erp

SAP

## Industry

Pharma and Life Sciences

## Audience

- SAP developer
- Quality assurance
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Plant (WERKS)

**Key:** plant

**Type:** text

**Default:** 1000

#### Material number (MATNR)

**Key:** material

**Type:** text

**Default:** 000000000010000123

#### Batch (CHARG)

**Key:** batch

**Type:** text

**Default:** B240915A

#### Posting date from

**Key:** from_date

**Type:** date

**Default:** 2025-01-01

#### Schema name

**Key:** schema

**Type:** text

**Default:** SAPSR3

#### Client (MANDT)

**Key:** client

**Type:** text

**Default:** 100

**Blocks:**

#### Forward trace: orders that consumed this batch

**Body:** -- Component batch {{batch}} of material {{material}} issued to orders (movement 261, not reversed)
SELECT s.AUFNR AS order_no,
       s.MBLNR AS mat_doc,
       k.BUDAT AS posting_date,
       s.WERKS AS plant,
       s.MENGE AS qty,
       s.MEINS AS uom
FROM {{schema}}.MSEG s
JOIN {{schema}}.MKPF k
  ON k.MANDT = s.MANDT AND k.MBLNR = s.MBLNR AND k.MJAHR = s.MJAHR
WHERE s.MANDT = '{{client}}'
  AND s.WERKS = '{{plant}}'
  AND s.MATNR = '{{material}}'
  AND s.CHARG = '{{batch}}'
  AND s.BWART = '261'
  AND k.BUDAT >= '{{from_date}}'
ORDER BY k.BUDAT, s.MBLNR;

#### Backward trace: components consumed to make this batch

**Body:** -- Finished batch {{batch}} received from an order (movement 101), then the components that order consumed
SELECT r.AUFNR AS order_no,
       c.MATNR AS component,
       c.CHARG AS component_batch,
       c.MENGE AS qty,
       c.MEINS AS uom
FROM {{schema}}.MSEG r
JOIN {{schema}}.MSEG c
  ON c.MANDT = r.MANDT AND c.AUFNR = r.AUFNR AND c.BWART = '261'
WHERE r.MANDT = '{{client}}'
  AND r.WERKS = '{{plant}}'
  AND r.MATNR = '{{material}}'
  AND r.CHARG = '{{batch}}'
  AND r.BWART = '101'
  AND r.AUFNR <> ''
ORDER BY r.AUFNR, c.MATNR;

#### Reversals to review

**Body:** -- Reversals (262) for the same batch, to net against issues above
SELECT s.AUFNR AS order_no, s.MBLNR AS mat_doc, s.MENGE AS qty
FROM {{schema}}.MSEG s
WHERE s.MANDT = '{{client}}'
  AND s.WERKS = '{{plant}}'
  AND s.MATNR = '{{material}}'
  AND s.CHARG = '{{batch}}'
  AND s.BWART = '262';

**Notes:** Run on a read-only replica or extract, never on a production database during peak load. SAP stores dates as YYYYMMDD character fields on many systems, so adjust the date literal to your database. In S/4HANA, material documents may be served by MATDOC, so verify table names. Reversal handling and multi-level trace through intermediates need extra joins.

## Content

**Intro:** When a supplier raises a quality alert or a lot fails stability, quality has to know which finished batches used the affected material and where they went. This generator writes SQL for the forward and backward trace over SAP material documents.

**Sections:**

#### How to use the generator

**Body:** Enter the plant, material number, batch, a start date and the schema that holds your SAP tables. The page fills three queries: a forward trace, a backward trace and a reversal check. Copy them into your SQL client against a read-only copy of the data. Remember that SAP material numbers are padded to 18 characters in many systems, so copy the value from MM03 or MB51 rather than typing it.

#### How the trace works

**Body:** SAP records every goods movement in MSEG with the material, batch, movement type and, for order related movements, the order number in AUFNR. Movement 261 is a goods issue of a component into an order, and movement 101 is the goods receipt of the finished batch from that order. Joining the two movements through the order number links components to finished goods without any custom table.

#### Reading the output

**Body:** The forward query returns each order that consumed the batch, with the date and quantity. Take the order numbers into the backward logic to find the finished batches, then follow those batches to customers through movement 601 deliveries. If an order shows both 261 and 262 for the same batch, net the quantities before concluding how much was used.

**Bullets:**

- Check every plant that could have received the batch.
- Include reversals and scrap postings in the quantity balance.
- Compare the result with MB56 as a sanity check.

#### Common mistakes

**Body:** The most common error is skipping reversals, which overstates consumption. Another is tracing only one level, when a batch of an intermediate was made from the affected raw material. Finally, be careful with client filters and date formats, and never run a heavy trace against a live production database in the middle of a shift.

**Cta:** If you need multi-level traces across plants, Netray can build a private on-prem tracing assistant that runs read-only queries over SAP, shows the SQL it used and respects ERP authorisations.

**Faq:**

#### Does this replace MB56?

**Answer:** No. MB56 is the standard transaction and is a good first check. The SQL is useful for bulk traces, exports to Excel and recall exercises where you need many batches at once or want to join the result to delivery and customer data.

#### Will it work on S/4HANA?

**Answer:** The logic is the same, but S/4HANA uses MATDOC for material documents and keeps MSEG and MKPF as compatibility views. Table names, field lengths and date formats can differ, so test the query in a non-production system first.

#### How do I trace to customers?

**Answer:** Take the finished batches from the backward step and look for movement 601 goods issues or delivery items with the same batch. Delivery tables such as LIPS hold batch and delivery number, and the ship-to comes from the delivery header.

#### Is this safe to run on production?

**Answer:** Run it against a read-only replica or extract if possible. The queries are SELECT only, but unindexed scans of MSEG can be heavy on large systems. Add a plant and date filter, and ask your Basis or DBA team about indexes.

## Search terms

batch genealogy sql, sap batch where used sql, mseg charg aufnr query, forward trace backward trace batch, recall batch trace query, sap movement type 261 101 query, batch traceability sql generator

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