Batch Genealogy SQL Query Generator (Forward and Backward Trace)

-- Component batch B240915A of material 000000000010000123 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 SAPSR3.MSEG s
JOIN SAPSR3.MKPF k
  ON k.MANDT = s.MANDT AND k.MBLNR = s.MBLNR AND k.MJAHR = s.MJAHR
WHERE s.MANDT = '100'
  AND s.WERKS = '1000'
  AND s.MATNR = '000000000010000123'
  AND s.CHARG = 'B240915A'
  AND s.BWART = '261'
  AND k.BUDAT >= '2025-01-01'
ORDER BY k.BUDAT, s.MBLNR;

-- Finished batch B240915A 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 SAPSR3.MSEG r
JOIN SAPSR3.MSEG c
  ON c.MANDT = r.MANDT AND c.AUFNR = r.AUFNR AND c.BWART = '261'
WHERE r.MANDT = '100'
  AND r.WERKS = '1000'
  AND r.MATNR = '000000000010000123'
  AND r.CHARG = 'B240915A'
  AND r.BWART = '101'
  AND r.AUFNR <> ''
ORDER BY r.AUFNR, c.MATNR;

-- 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 SAPSR3.MSEG s
WHERE s.MANDT = '100'
  AND s.WERKS = '1000'
  AND s.MATNR = '000000000010000123'
  AND s.CHARG = 'B240915A'
  AND s.BWART = '262';