Multi-Level BOM Cost Rollup SQL Query Generator

WITH RECURSIVE bom_tree (parent_item, component_item, ext_qty, lvl) AS (
  SELECT b.parent_item, b.component_item,
         b.qty_per * (1 + b.scrap_pct / 100.0), 1
  FROM bom_line b
  WHERE b.parent_item = 'ASSY-1000'
  UNION ALL
  SELECT b.parent_item, b.component_item,
         t.ext_qty * b.qty_per * (1 + b.scrap_pct / 100.0),
         t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 15
)
SELECT t.component_item,
       SUM(t.ext_qty) AS total_qty,
       c.unit_cost AS unit_cost,
       SUM(t.ext_qty) * c.unit_cost AS ext_cost
FROM bom_tree t
JOIN item_cost c ON c.item = t.component_item
WHERE NOT EXISTS (
  SELECT 1 FROM bom_line x
  WHERE x.parent_item = t.component_item
)
GROUP BY t.component_item, c.unit_cost
ORDER BY ext_cost DESC;

-- Wrap the query above as a subquery named leaf_costs, then:
SELECT SUM(ext_cost) AS rolled_material_cost
FROM leaf_costs;