SAP BOM Where-Used SQL Generator: STPO, MAST and STKO Queries for Components and Dependencies

SELECT m.matnr AS parent_material,
       m.werks,
       m.stlan AS bom_usage,
       m.stlnr AS bom_number,
       m.stlal AS alternative,
       p.posnr AS item,
       p.idnrk AS component,
       p.menge AS quantity,
       p.meins AS uom,
       p.datuv AS valid_from
FROM stpo p
JOIN mast m
  ON m.mandt = p.mandt
 AND m.stlnr = p.stlnr
JOIN stko k
  ON k.mandt = p.mandt
 AND k.stlty = p.stlty
 AND k.stlnr = p.stlnr
 AND k.stlal = m.stlal
WHERE p.mandt = '100'
  AND p.stlty = 'M'
  AND p.idnrk = 'COMP-4410'
  AND p.lkenz = ' '
  AND m.werks = '1000'
  AND m.stlan = '1'
ORDER BY m.matnr, m.stlal, p.posnr;

SELECT m.matnr AS parent_material,
       p.posnr AS item,
       p.idnrk AS component,
       p.knobj AS dependency_link,
       p.datuv AS valid_from
FROM mast m
JOIN stpo p
  ON p.mandt = m.mandt
 AND p.stlnr = m.stlnr
WHERE m.mandt = '100'
  AND m.matnr = 'CONF-PUMP-100'
  AND m.werks = '1000'
  AND m.stlan = '1'
  AND p.stlty = 'M'
  AND p.lkenz = ' '
  AND p.knobj <> '0000000000'
ORDER BY p.posnr;

SELECT m.matnr AS parent_material,
       COUNT(*) AS item_count
FROM mast m
JOIN stpo p
  ON p.mandt = m.mandt
 AND p.stlnr = m.stlnr
WHERE m.mandt = '100'
  AND m.werks = '1000'
  AND m.stlan = '1'
  AND p.stlty = 'M'
  AND p.lkenz = ' '
GROUP BY m.matnr
ORDER BY item_count DESC;