last leaf in SQL query

I have a requirement to explode a BOM down to its most basic level
components, meaning, convert all finished goods and subassemblies to raw
onhand qty.


Am I choosing the most efficient way to get this? Its taking quite a
while to run. I'm doing a BOM explosion with a RCTE, and then doing a
"WHERE Child NOT IN" the parent list kind of query to make sure that no FG
or subs are included in the results. I've removed some of the complication
in this example, such as processing the onhand value through the levels to
highlight the point of this question. I started out with "connect by"
logic, but, this doesn't allow multiplication of the qty required down
through the levels, so was unsuitable for my needs here.

If there was a way to tell the query engine what I wanted more directly, I
figured it would run more efficiently, but I don't know if there is a
trick, to again, getting just the last level components and the
accumulated requirement qty.

with Exploded(Parent, Child, Qty, Fac, TopParent) as
(select BPROD, BCHLD, BQREQ, BMWHS, BPROD
from MBML01 MBM
where BMWHS=:DftFac
and :Today8 between BDEFF and BDDIS
union all
select BPROD, BCHLD, BQREQ*Exploded.Qty, Fac, TopParent
from MBML01 mbm
join Exploded
on BPROD=Child
and BMWHS=Fac
where :Today8 between BDEFF and BDDIS

select
L1.part, sum(L2.OnHand)

from IPIM
left outer join
(select TopParent, Child, sum(Qty) as Qty
from Exploded
where Child not in (select Parent from Exploded)
group by TopParent, Child) E1
on E1.TopParent=IPIM.PARTNUM
cross join lateral
(values(case when E1.Child is null then IPIM.PARTNUM
else E1.Child end)) L1(PART)
cross join lateral
(values(case when E1.Child is null then IWI.OnHand
else IWI.OnHand*E1.Qty end)) L2(OnHand)
goup by L1.Part