How to obtain total amount of raw materials from BOM

Helllo,

I am trying to sum all the raw(purchased) material (MATERIAL1, MATERIAL2) for my product. (According to qty_per_assembly) I just generated simple structure of L1 product. 

It looks like this:

What i need to do is to sum the MATERIAL1 and MATERIAL2:

• • MATERIAL1 (8,25kg) goes to part_no L4.1 (1 pcs) and component L4.1 (1 pcs) goes to part_no L3 (16 pcs) that means: 8,25 * 1 * 16 * 1 = 132 kg (level4 * level3 * level2 * level1)

  • MATERIAL2 (0,75kg) goes to part_no L4.2 (2 pcs) and component L4.1 (1 pcs) goes to part_no L3 (16 pcs) that means: 0,75 * 2 * 16 * 1 = 24 kg (level4 * level3 * level2 * level1)

How to do it in oracle? 

Thank in advance.

MANUF_STRUCTURE view - basic

with data as (

select 'L1' as part_no, 0 as eng_chg_level, '' as alternative_no, 'L2' as component_part, 1 as qty_per_assembly, 'PCS' as print_unit from dual union all

select 'L2' as part_no, 0 as eng_chg_level, '' as alternative_no, 'L3' as component_part, 16 as qty_per_assembly, 'PCS' as print_unit from dual union all

select 'L3' as part_no, 0 as eng_chg_level, '' as alternative_no, 'L4.1' as component_part, 1 as qty_per_assembly, 'PCS' as print_unit from dual union all

select 'L3' as part_no, 0 as eng_chg_level, '' as alternative_no, 'L4.2' as component_part, 2 as qty_per_assembly, 'PCS' as print_unit from dual union all

select 'L4.1' as part_no, 0 as eng_chg_level, '' as alternative_no, 'MATERIAL1' as component_part, 8.25552 as qty_per_assembly, 'kg' as print_unit from dual union all

select 'L4.2' as part_no, 0 as eng_chg_level, '' as alternative_no, 'MATERIAL2' as component_part, 0.7414686 as qty_per_assembly, 'kg' as print_unit from dual

)

select part_no, component_part, qty_per_assembly, print_unit from data

;

HIERARCHY STRUCTURE view

with aa as (select 'L1' as part_no, 0 as eng_chg_level, '*' as alternative_no, 'L2' as component_part, 1 as qty_per_assembly, 'PCS' as print_unit from dual union all
        select 'L2' as part_no, 0 as eng_chg_level, '*' as alternative_no, 'L3' as component_part, 16 as qty_per_assembly, 'PCS' as print_unit from dual union all
        select 'L3' as part_no, 0 as eng_chg_level, '*' as alternative_no, 'L4.1' as component_part, 1 as qty_per_assembly, 'PCS' as print_unit from dual union all
        select 'L3' as part_no, 0 as eng_chg_level, '*' as alternative_no, 'L4.2' as component_part, 2 as qty_per_assembly, 'PCS' as print_unit from dual union all
        select 'L4.1' as part_no, 0 as eng_chg_level, '*' as alternative_no, 'MATERIAL1' as component_part, 8.25 as qty_per_assembly, 'kg' as print_unit from dual union all
        select 'L4.2' as part_no, 0 as eng_chg_level, '*' as alternative_no, 'MATERIAL2' as component_part, 0.75 as qty_per_assembly, 'kg' as print_unit from dual
        )
select lpad(level, (level *2)-1,'.') level_graphic, LEVEL, PART_NO
               ,COMPONENT_PART, qty_per_assembly
from aa
CONNECT BY PRIOR COMPONENT_PART = PART_NO 
START WITH PART_NO = 'L1' 
        ;

SQL code for creating testing data:

create table bom_test (part_no varchar2(20), component_part varchar2(20), qty_per_assembly number, um varchar2(10));
insert into bom_test values ('L1','L2',1,'pcs');
insert into bom_test values ('L2','L3',16,'pcs');
insert into bom_test values ('L3','L4.1',1,'pcs');
insert into bom_test values ('L3','L4.2',2,'pcs');
insert into bom_test values ('L4.1','MATERIAL1',8.25,'kg');
insert into bom_test values ('L4.2','MATERIAL2',0.75,'kg');

select lpad(level, (level *2)-1,'.') level_graphic, LEVEL, PART_NO,COMPONENT_PART, qty_per_assembly,um
from bom_test
CONNECT BY PRIOR COMPONENT_PART = PART_NO 
START WITH PART_NO = 'L1' 
        ;