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'
;
