標準API展開BOM程式碼

--BOM_EXPLOSION_temp是一個臨時表，在展BOM的時候，是一個很有用的表，存放了組成料件層次。預設時是沒有記錄的，加入下列的語句：
DECLARE
l_group_id NUMBER;
l_error_message VARCHAR2(1000);
l_error_code NUMBER;
BEGIN
SELECT bom.bom_explosion_temp_s.NEXTVAL --取下一個序列
INTO l_group_id
FROM DUAL;

BEGIN
apps.bompexpl.exploder_userexit(verify_flag => NULL,
org_id => 122,
order_by => 1,
grp_id => l_group_id,
session_id => NULL,
levels_to_explode => 10, ----展開十層
bom_or_eng => 1,
impl_flag => 1,
plan_factor_flag => NULL,
explode_option => 2,
MODULE => 2,
cst_type_id => NULL,
std_comp_flag => 2,
expl_qty => 1,
item_id => 32523, --:inventory_item_id_parent, --- (select inventory_item_id from mtl_system_items_b where organization_id=89 and segment1='FWP14(A)-PLN'),
alt_desg => NULL,
comp_code => NULL,
rev_date => to_char(sysdate,
'yyyy/mm/dd hh24:mi:ss'), --版本日期
err_msg => l_error_message,
ERROR_CODE => l_error_code);
COMMIT;
END;

dbms_output.put_line(l_error_code || l_error_message);
END;
---查詢
select * from BOM_EXPLOSION_temp;

-- 還有一種更加準確
DECLARE err_meg VARCHAR2(100);
ERROR_CODE VARCHAR2(100);
BEGIN
bompxinq.exploder_userexit(verify_flag => 0,
org_id => 122,
order_by => 1,
grp_id => 0,
session_id => 0,
levels_to_explode => 10,
bom_or_eng => 1, -- 1 bom 2 eng
impl_flag => 2,

plan_factor_flag => 1,

explode_option => 1,

module => 2,

cst_type_id => 2, --average
std_comp_flag => 2,

expl_qty => 1,
item_id => 35463,
unit_number_from => NULL,
unit_number_to => NULL,
alt_desg => '',
comp_code => '',
rev_date => '',

err_msg => err_meg,
ERROR_CODE => ERROR_CODE);

END;

SELECT * FROM bom_small_expl_temp;

--程式碼三:
procedure expand_bom(p_org_id number, p_item_no 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
select inventory_item_id
into l_item_id
from mtl_item_flexfields
where organization_id = p_org_id
and item_number = 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_explosion_tmp where top_item_id = l_item_id;

--delete bom_explosion_temp;
commit;

cuxbompexpl.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 => 5, --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
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;

/*
insert into bom_explosion_tmp
select * from bom_explosion_temp;
--where top_item_id =l_item_id
commit; */
end;