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, STAS, STPO, MAKT, MARA, T001W
-- Notes  : MAST is the plant-level assignment: material → BOM number (STLNR) + usage (STLAN). STKO is the BOM header; STKO has no STLKN column, so items are reached via the STAS bridge (STKO → STAS on STLTY/STLNR/STLAL, then STAS → STPO on STLTY/STLNR/STLKN).
-- ============================================================
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.LKENZ   AS "Deletion Indicator",
  h.LOEKZ   AS "Header Deletion Indicator",
  l.POSTP   AS "Item Category",
  h.STLST   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 <catalog>.<schema>.mast a
INNER JOIN <catalog>.<schema>.stko h
  ON  h.MANDT = a.MANDT
  AND h.STLNR = a.STLNR
  AND h.STLAL = a.STLAL
INNER JOIN <catalog>.<schema>.stas br
  ON  br.MANDT = h.MANDT
  AND br.STLTY = h.STLTY
  AND br.STLNR = h.STLNR
  AND br.STLAL = h.STLAL
INNER JOIN <catalog>.<schema>.stpo l
  ON  l.MANDT = br.MANDT
  AND l.STLTY = br.STLTY
  AND l.STLNR = br.STLNR
  AND l.STLKN = br.STLKN
LEFT JOIN <catalog>.<schema>.makt mth
  ON  mth.MANDT = a.MANDT
  AND mth.MATNR = a.MATNR
  AND mth.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.makt mti
  ON  mti.MANDT = l.MANDT
  AND mti.MATNR = l.IDNRK
  AND mti.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara mi
  ON  mi.MANDT = l.MANDT
  AND mi.MATNR = l.IDNRK
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = a.MANDT
  AND pt.WERKS = a.WERKS
WHERE
  a.MANDT = '<MANDT>'
  AND a.MATNR = '<MATNR>'
  AND a.WERKS = '<WERKS>'
  AND l.LKENZ = ''
ORDER BY a.MATNR, a.STLAL, l.POSNR;