网上收集标准API展开BOM代码

来源：互联网发布：中国的战争潜力知乎编辑：程序博客网时间：2024/06/05 05:16

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;