CS02

-- ============================================================
-- T-Code : CS02 Change Material BOM
-- Purpose: Change an existing material BOM
-- Grain  : One row per BOM item (components of a material)
-- Tables : MAST, STKO, STPO, MAKT, MARA, T001W
-- Notes  : MAST is the plant-level assignment: material → BOM number (STLNR) + usage (STLAN). STKO is the BOM header; STPO lists the components. STAS (not joined) carries the valid-from/to per item if alternative BOMs are used.
-- ============================================================
SELECT
  -- Keys
  a.MANDT   AS "Client",
  a.MATNR   AS "Header Material",
  a.WERKS   AS "Plant",
  a.STLAN   AS "BOM Usage",
  a.STLNR   AS "BOM Number",
  a.STLAL   AS "Alternative BOM",
  l.POSNR   AS "Item Number",

  -- Descriptive text
  mth.MAKTX AS "Header Material Description",
  mti.MAKTX AS "Component Description",
  l.POTX1   AS "Item Text Line 1",

  -- Quantities + UOM (paired)
  l.MENGE   AS "Component Quantity",
  l.MEINS   AS "Component UoM",
  h.BMENG   AS "Base Quantity (Header)",
  h.BMEIN   AS "Base UoM (Header)",
  l.AUSCH   AS "Component Scrap (%)",

  -- Dates
  l.DATUV   AS "Valid From",
  h.ANDAT   AS "Created On",
  h.AEDAT   AS "Last Changed On",

  -- Status / indicators
  l.LOEKZ   AS "Deletion Indicator",
  h.LOEKZ   AS "Header Deletion Indicator",
  l.POSTP   AS "Item Category",
  h.BMART   AS "BOM Status",

  -- Org fields
  pt.NAME1  AS "Plant Name",

  -- Master data enrichment
  l.IDNRK   AS "Component Material",
  mi.MTART  AS "Component Material Type",
  mi.MATKL  AS "Component Material Group"
FROM mast a
INNER JOIN stko h
  ON  h.MANDT = a.MANDT
  AND h.STLNR = a.STLNR
  AND h.STLAL = a.STLAL
INNER JOIN stpo l
  ON  l.MANDT = h.MANDT
  AND l.STLNR = h.STLNR
  AND l.STLKN = h.STLKN
LEFT JOIN makt mth
  ON  mth.MANDT = a.MANDT
  AND mth.MATNR = a.MATNR
  AND mth.SPRAS = 'E'
LEFT JOIN makt mti
  ON  mti.MANDT = l.MANDT
  AND mti.MATNR = l.IDNRK
  AND mti.SPRAS = 'E'
LEFT JOIN mara mi
  ON  mi.MANDT = l.MANDT
  AND mi.MATNR = l.IDNRK
LEFT JOIN t001w pt
  ON  pt.MANDT = a.MANDT
  AND pt.WERKS = a.WERKS
WHERE
  a.MANDT = '100'
  AND a.MATNR = '<MATNR>'
  AND a.WERKS = '<WERKS>'
  AND l.LOEKZ = ''
ORDER BY a.MATNR, a.STLAL, l.POSNR;