Skip to main content

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

 

​@westjan I gave this a shot and could only come up with a solution walking up the structure instead of starting from the top.

SELECT matl_part
     , lvl
     , part_no
     , qty_per_assembly
     , component_part
     , comp_uom
     , round(exp(SUM(ln(qty_per_assembly)) OVER(PARTITION BY matl_part ORDER BY - lvl)),2) total
  FROM ( SELECT CONNECT_BY_ROOT component_part matl_part
              , level lvl
              , part_no
              , qty_per_assembly
              , component_part
              , component_unit_meas comp_uom
          FROM manuf_structure
       CONNECT BY PRIOR part_no = component_part
       START WITH component_part IN ( 'MATERIAL.1', 'MATERIAL.2' )
       )

 

Hello,

thank you Tracy Norwillo, nice try but not the right way.

 

I am just thinking, is there any way in IFS Cloud to get the total of all purchased material from product structure?? There must be a way, this is quite common question, isn't it?

Hi,

If you are using Costing, then you might want to have a look in Part Cost page - Structure Multilevel. If you query for a top part (after you have calculated your part cost) you can see accumulated qty per assembly.

Does it help you?

-Mats

Hello again :-)

Or you can also try out Simulate Multilevel Material Availability. Please note the yellow highlighted fields in the header portion. You should test with Qty 1 and enable ALL Levels.

 

We had a problem when the same component existed multiple times in the same BOM, but that has been corrected in latest Core and Apps10 (recently).

Cheers,

Mats

Thanks a lot, that might be it. :)

I recall in Apps2004 and maybe into 7 and 7.5 on the Engineering/PDM side of the application there was a standard report called Summarized Bill of Material (or something similar) that did exactly what you need for the Engineering bill of material.

Hi,

I couldn’t find this type of report neither in APPS10 or in IFScloud.

Thanks anyway.

​@westjan I forgot to include that this report was removed, possibly when Apps8 was released.

Reply


Terms & ConditionsCookie settings