create or replace procedure bom_expand(p_org_id number,p_item_id varchar2) is
v_cnt number;
l_grp_id number ;
l_session_id number ;
l_org_id number ;
l_levels_to_explode number;
l_module number default 2 ; -- bom = 2
l_cst_type_id number default -1 ; -- all cost = -1 else 0
l_item_id number ;
l_bom_or_eng number:=1 ;--1 bom 2 eng
l_rev_date varchar2(30):= to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'); --Must be this
l_err_msg varchar2(250) ;
l_error_code number ;
begin
-- 首先要有一个group id
select bom_explosion_temp_s.nextval
into l_grp_id
from dual ;

-- 其次要得到一个session id
select bom_explosion_temp_session_s.nextval
into l_session_id
from dual ;

-- Find item_id
l_item_id := p_item_id;
/*select inventory_item_id
into l_item_id
from mtl_system_items_b -- mtl_item_flexfields
where organization_id = p_org_id and segment1 = p_item_no;
*/
-- determine maximum levels to explode from bom_explosions
select maximum_bom_level
into l_levels_to_explode
from bom_parameters where organization_id = p_org_id;

delete bom.bom_explosion_temp where top_item_id =l_item_id;

--delete bom_explosion_temp;
commit;

bompexpl.exploder_userexit(
verify_flag =>0, -- DEFAULT 0
org_id =>p_org_id,
order_by =>1, -- DEFAULT 1
grp_id =>l_grp_id,
session_id =>l_session_id,
levels_to_explode =>2,--l_levels_to_explode,
bom_or_eng =>l_bom_or_eng,
impl_flag =>1, -- DEFAULT 1
plan_factor_flag =>2, -- DEFAULT 2
explode_option =>2, -- DEFAULT 2
module =>l_module, -- DEFAULT 2
cst_type_id =>l_cst_type_id,
std_comp_flag =>0,
expl_qty =>1, -- DEFAULT 1
item_id =>l_item_id,
alt_desg =>'',
comp_code =>'',
rev_date =>l_rev_date,
err_msg =>l_err_msg,
error_code =>l_error_code
);


if ( l_error_code <> 0 ) then
rollback;
dbms_output.put_line('ERROR: ' || l_err_msg);
else
insert into xxbom
select * from bom_explosion_temp;
select count(*) into v_cnt from bom_explosion_temp where group_id=l_grp_id;
dbms_output.put_line('Count=' || v_cnt);
commit;
end if;
end;