Oracle使用包bompexpl.explode展开BOM下所有组件

如果想要展开一个BOM物料下所有组件，包含虚拟件，可以使用以下方法:

SELECT bom_explosion_temp_s.nextval INTO x_group_id FROM dual;

-- determine maximum levels to explode from bom_explosions
select maximum_bom_level
into l_levels_to_explode
from bom_parameters
where organization_id = p_organization_id;
 

FOR rec_bom IN cur_bom(p_organization_id => p_organization_id,
                           p_item_id         => p_item_id) LOOP
      
      bompexpl.explode(org_id            => rec_bom.organization_id, --库存组织ID
                       grp_id            => x_group_id,--rec_bom.group_id,
                       levels_to_explode => 15,--l_levels_to_explode
                       explode_option    => 3,--1 All,2 Current,3 Current and future
                       std_comp_flag     => 1,
                       item_id           => rec_bom.assembly_item_id, --要张开的模型物料ID
                       rev_date          => to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                       alt_desg          => NULL,
                       err_msg           => l_err_msg,
                       ERROR_CODE        => l_err_code);
      
      IF l_err_code = '0' THEN
        log_msg('[Success] 组织''' || rec_bom.organization_code || '''制造件''' || rec_bom.assembly_item_number || '''已成功展开所有组件;');
      ELSE
        log_msg('[Error] 组织''' || rec_bom.organization_code || '''制造件''' || rec_bom.assembly_item_number || '''展开组件发生错误,' || l_err_msg);
        RAISE fnd_api.g_exc_error;
      END IF;
    
    END LOOP;

或者用bom_lists来展开BOM

SELECT bom_lists_s.nextval INTO l_seq_id FROM DUAL;
    SELECT bom_explosion_temp_s.nextval INTO l_group_id FROM DUAL;
    INSERT INTO bom_lists(sequence_id,assembly_item_id,organization_id) VALUES(V_L_SEQ_ID,rec_bom.assembly_item_id,rec_bom.organization_id);
    DBMS_OUTPUT.PUT_LINE('GROUP ID:' || TO_CHAR(l_group_id));

FOR rec_bom IN cur_bom LOOP

Bompexpl.explosion_report
    (verify_flag       => 0,
     org_id            => rec_bom.organization_id,--organization_id
     order_by          => 1,--1 Op seq, item seq,2 Item seq, op seq
     list_id           => l_seq_id,
     grp_id            => l_group_id,--unique value to identify current explosion,use value from sequence bom_explosion_temp_s
     session_id        => 0,--unique value to identify current session,use value from bom_explosion_temp_session_s
     levels_to_explode => 15,
     bom_or_eng        => 1,--1 BOM,2 ENG
     impl_flag         => 1,--1 implemented only,2 both impl and unimpl
     plan_factor_flag  => 2,--1 Yes,2 No
     incl_lt_flag      => 2, --1 Yes,2 No
     explode_option    => 3,--1 All,2 Current,3 Current and future
     module            => 2,--1 Costing,2 Bom,3 Order entry,4 ATO,5 WSM
     cst_type_id       => 0,--cost type id for costed explosion
     std_comp_flag     => 0,--1 explode only standard components,2 all components
     expl_qty          => 1,--explosion quantity
     report_option     => 0,--1 cost rollup with report,2 cost rollup no report,3 temp cost rollup with report
     req_id            => 0,--request id
     cst_rlp_id        => 0,--rollup_id
         lock_flag         => 2,--1 do not lock the table,2 lock the table
     rollup_option     => 2,--1 single level rollup,2 full rollup
     alt_rtg_desg      => '',--alternate routing designator
     alt_desg          => '',   --alternate bom designator
     rev_date          => to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),   --explosion date YYYY/MM/DD HH24:MI:SS
     err_msg           => l_err_msg,--error message out buffer
     error_code        => l_err_code--error code out.  returns sql error code
         );

END LOOP;

然后用表bom_explosion_temp查询出展开的BOM

select * from BOM_EXPLOSION_VIEW--bom_explosion_temp

表中的top_item_id就是需要展开的BOM物料，top_bill_sequence_id是对应BOM的ID，plan_level表示此次展开BOM的层级。

注：top_bill_sequence_id和bill_sequence_id会不一致，因为bill_sequence_id包含展开二级以上BOM对应的ID，

       top_bill_sequence_id始终是最上层那个BOM的ID

 

存储过程的参数解释如下：

Parameters:

org_id    organization_id
order_by  1 - Op seq, item seq; 2 - Item seq, op seq
grp_id    unique value to identify current explosion,use value from sequence bom_explosion_temp_s
session_id  unique value to identify current session,use value from bom_explosion_temp_session_s
levels_to_explode
bom_or_eng  1 - BOM 2 - ENG
impl_flag 1 - implemented only; 2 - both impl and unimpl
explode_option  1 - All; 2 - Current; 3 - Current and future;
module    1 - Costing; 2 - Bom; 3 - Order entry; 4 - ATO; 5 - WSM
cst_type_id cost type id for costed explosion
std_comp_flag 1 - explode only standard components; 2 - all components
expl_qty  explosion quantity
item_id   item id of asembly to explode
list_id   unique id for lists in bom_lists for range
report_option 1 - cost rollup with report; 2 - cost rollup no report; 3 - temp cost rollup with report
cst_rlp_id  rollup_id
req_id    request id
prgm_appl_id  program application id
prg_id    program id
user_id   user id
lock_flag 1 - do not lock the table; 2 - lock the table
alt_rtg_desg  alternate routing designator
rollup_option 1 - single level rollup; 2 - full rollup
plan_factor_flag1 - Yes; 2 - No
incl_lt_flag    1 - Yes; 2 - No
alt_desg  alternate bom designator
rev_date  explosion date YYYY/MM/DD HH24:MI:SS
comp_code concatenated component code lpad 16
err_msg   error message out buffer
error_code  error code out.  returns sql error code