with P_Parts_CTE (Company, PartNum)
as
(
    select Company, PartNum
    from dbo.Part
    where Company = 'Comp' and TypeCode = 'P'
),
P_Part_Inv_CTE (Company, PartNum, OnHandQty)
as
(
    select a.Company, a.PartNum, Sum(OnHandQty)
    from P_Parts_CTE as a
    left outer join dbo.PartWhse as b
    on a.Company = b.Company and a.PartNum = b.PartNum
    group by a.Company, a.PartNum
),
PartDtl_Sum_CTE (Company, PartNum, Supply, Demand)
as
(
    select c.Company, c.PartNum, Sum(case when d.RequirementFlag = 0 then d.Quantity else 0 end) as Supply, Sum(case when d.RequirementFlag = 1 then d.Quantity else 0 end) as Demand
    from P_Parts_CTE as c
    left outer join dbo.PartDtl as d
    on c.Company = d.Company and c.PartNum = d.PartNum
    group by c.Company, c.PartNum
),
PartDtl_CTE (Company, PartNum, Balance)
as
(
    select e.Company, e.PartNum, (e.OnHandQty + f.Supply - f.Demand) as Balance
    from P_Part_Inv_CTE as e
    left outer join PartDtl_Sum_CTE as f
    on e.Company = f.Company and e.PartNum = f.PartNum
    group by e.Company, e.PartNum, e.OnHandQty, f.Supply, f.Demand
),
Parts_Neg_CTE (Company, PartNum)
as
(
    select Company, PartNum
    from PartDtl_CTE
    where Balance < 0
),
Reverse_Recursive_BOM_CTE (Company, PartNum, [Level], MtlPartNUm)
as
(
    select h.Company, h.PartNum, 0 as [Level], h.MtlPartNum
    from Parts_Neg_CTE as g
    inner join dbo.PartMtl as h
    on g.Company = h.Company and g.PartNum = h.PartNum
    union all
    select i.Company, i.PartNum, [Level] - 1, i.MtlPartNum
    from dbo.PartMtl as i
    inner join Reverse_Recursive_BOM_CTE as j
    on i.MtlPartNum = j.PartNum
)
select *
from Reverse_Recursive_BOM_CTE