本文介绍了一个用于BOM展开的标准程序实现方法，并展示了如何通过PL/SQL脚本进行多级BOM的展开及数据保存。此外，还提供了一个多组织BOM查询的SQL示例。
1.标准的BOM API展开程序是采用插入数据到:BOM_EXPLOSION_TEMP 表,但由于此表是全局临时表,只对当前的会话有效,只要会话退出,数据马上被删除,因此需要建立表来保存BOM物料数据;
脚本如下:
create table BOM_EXPLOSION_TEMP_SAVE as select * from BOM_EXPLOSION_TEMP;
2.写BOM展开程序如下:
(多级清单BOM的展开)
declare
v_item varchar2(240) := '251200200168'; -- item to explode
v_org varchar2(3) := 'W09'; -- org in which item is exploded
v_cnt NUMBER := 0;
v_err_msg varchar2(240);
v_err_code NUMBER := 0;
v_verify_flag NUMBER := 0; -- DEFAULT 0
v_online_flag NUMBER := 2; -- DEFAULT 0
v_item_id NUMBER := 0; -- set to inventory_item_id of item to explode
v_org_id NUMBER := 0; -- set to organization_id of item to explode
v_alternate VARCHAR2(240) := NULL; -- DEFAULT null
v_list_id NUMBER := 0; -- for reports (default 0)
v_order_by NUMBER := 1; -- DEFAULT 1
v_grp_id NUMBER := 0; --
v_session_id NUMBER := 0; -- DEFAULT 0
v_req_id NUMBER := 0; -- DEFAULT 0
v_prgm_appl_id NUMBER := -1; -- DEFAULT -1
v_prgm_id NUMBER := -1; -- DEFAULT -1
v_levels_to_explode NUMBER := 1; -- DEFAULT 1
v_bom_or_eng NUMBER := 1; -- DEFAULT 1
v_impl_flag NUMBER := 1; -- DEFAULT 1
v_plan_factor_flag NUMBER := 2; -- DEFAULT 2
v_incl_lt_flag NUMBER := 2; -- DEFAULT 2
v_explode_option NUMBER := 2; -- DEFAULT 2
v_module NUMBER := 2; -- DEFAULT 2
v_cst_type_id NUMBER := 0; -- DEFAULT 0
v_std_comp_flag NUMBER := 0; -- DEFAULT 0
v_rev_date VARCHAR2(240); --
v_comp_code VARCHAR2(240) := NULL; --
v_expl_qty NUMBER := 1; -- DEFAULT 1
begin
-- item revision will be based on this explode date.
-- In this example, we use current date/time
v_rev_date := to_char(SYSDATE);
-- Find org_id
select mp.organization_id into v_org_id
from MTL_PARAMETERS mp
where mp.organization_code = v_org;
-- Find item_id
select inventory_item_id into v_item_id
from MTL_ITEM_FLEXFIELDS
where organization_id = v_org_id and item_number = v_item;
-- v_grp_id is a unique identifier for this run of the exploder
select bom_explosion_temp_s.nextval into v_grp_id from dual;
-- determine maximum levels to explode from bom_explosions
--这里是多级BOM展开,包括BOM头的信息也存在
select maximum_bom_level into v_levels_to_explode
from bom_parameters where organization_id = v_org_id;
--可以设置 v_levels_to_explode=1;
apps.bompexpl.exploder_userexit (
v_verify_flag,
v_org_id,
v_order_by,
v_grp_id,
v_session_id,
v_levels_to_explode,
v_bom_or_eng,
v_impl_flag,
v_plan_factor_flag,
v_explode_option,
v_module,
v_cst_type_id,
v_std_comp_flag,
v_expl_qty,
v_item_id,
v_alternate,
v_comp_code,
v_rev_date,
v_err_msg,
v_err_code);
if ( v_err_code <> 0 ) then
rollback;
dbms_output.put_line('ERROR: ' || v_err_msg);
else
select count(*) into v_cnt from bom_explosion_temp where group_id=v_grp_id;
dbms_output.put_line('Count=' || v_cnt);
commit;
dbms_output.put_line('.');
dbms_output.put_line('Group Id=' || v_grp_id);
dbms_output.put_line('Org =' || v_org);
dbms_output.put_line('Item =' || v_item);
dbms_output.put_line('Ord Id =' || v_org_id);
dbms_output.put_line('Item Id=' || v_item_id);
dbms_output.put_line('Levels =' || v_levels_to_explode);
end if;
end;
/
3.插入到自己创建的临时表中:
insert into BOM_EXPLOSION_TEMP_SAVE select * from BOM_EXPLOSION_TEMP;
commit;
4.推出会话,查看临时表;
//////////////////////////////////////////////////////////////////
SELECT distinct
bb.BOM层次,
bb.装配件,
bb.装配件说明,
bb.组件序号,
bb.组件,
bb.组件说明,
bb.组件用量,
bb.单位,
bb.利用率,
bb.冲减库房,
bb.冲减货位,
bb.属性,
bb.BOM创建时间,
BOR.COMPLETION_SUBINVENTORY 入库,
MSI.WIP_SUPPLY_SUBINVENTORY 冲减,
MSI.MAXIMUM_ORDER_QUANTITY 最大批量,
MSI.LEAD_TIME_LOT_SIZE 提前期,
MSI.FULL_LEAD_TIME 制造周期,
MSI.FIXED_LOT_MULTIPLIER 固定增加,
MSI.MINIMUM_ORDER_QUANTITY 最小批量,
MSI.FIXED_ORDER_QUANTITY 固定定货量,
MSI.FIXED_DAYS_SUPPLY 固定天数,
MSI.PLANNER_CODE 计划员
FROM MTL_SYSTEM_ITEMS MSI,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
(select distinct
aa.lvl BOM层次,
msi.segment1 装配件,
msi.description 装配件说明,
aa.item_num 组件序号,
msi1.segment1 组件,
msi1.description 组件说明,
aa.component_quantity 组件用量,
msi1.primary_unit_of_measure 单位,
aa.COMPONENT_YIELD_FACTOR 利用率,
msi1.wip_supply_subinventory 冲减库房,
mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
mil.segment4 冲减货位,
decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,
to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss') BOM创建时间,
msi1.inventory_item_id
from mtl_system_items_b msi,
mtl_system_items_b msi1,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_item_locations mil,
(
select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.OPERATION_SEQ_NUM,
bic.COMPONENT_YIELD_FACTOR,
bic.COMPONENT_SEQUENCE_ID,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date
FROM bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
(select bill_sequence_id
from bom_bill_of_materials bom2,
inv.mtl_system_items_b msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
---------------------------------------输入要查询的项目---------------------------------------------
and msi.segment1 =&ITEM_NUMBER
---------------------------------------输入要查询的项目---------------------------------------------
and msi.organization_id = &组织ID
and bom2.alternate_bom_designator is null)
CONNECT BY bic.bill_sequence_id in prior
(SELECT distinct bill_sequence_id
FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = &组织ID
and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bo.alternate_bom_designator is null
and disable_date IS NULL)) aa
where msi.organization_id=&组织ID
and msi1.organization_id=&组织ID
and bom.organization_id=&组织ID
and msi.inventory_item_id=bom.assembly_item_id
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi1.inventory_item_id
and bic.disable_date is null
and aa.bill_sequence_id=bic.bill_sequence_id
and aa.component_item_id=msi1.inventory_item_id
and mil.inventory_location_id(+)=msi1.wip_supply_locator_id
and mil.organization_id(+)=&组织ID
order by
aa.lvl,
aa.item_num) bb
WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID
AND MSI.ORGANIZATION_ID(+) = &组织ID
and bor.alternate_routing_designator is null
and bb.inventory_item_id=msi.inventory_item_id
order by
bb.BOM层次,
bb.装配件,
bb.装配件说明,
bb.组件序号,
bb.组件
//////////////////////////////////////////////////////////////
多组织的展开
select rownum seq_num
,top_item
,top_org
,lpad(to_char(level),decode(level,1,1,level+1),'.') bom_level
,bbm.ASSEMBLY_ITEM_ID
,msi.segment1 assembly_item
,msi.description assembly_description
,bbm.COMMON_ASSEMBLY_ITEM_ID
,bic.item_NUM
,bbm.COMMON_BILL_SEQUENCE_ID
,bbm.BILL_SEQUENCE_ID
,msic.segment1 component_item
,msic.description c_item_description
,bic.COMPONENT_ITEM_ID
,bic.COMPONENT_QUANTITY
,msic.primary_unit_of_measure
,bic.COMPONENT_YIELD_FACTOR
,bic.EFFECTIVITY_DATE
,bic.ATTRIBUTE1
,bic.ATTRIBUTE2
,bic.CHANGE_NOTICE
,ood.ORGANIZATION_CODE
,ood.ORGANIZATION_NAME
,bbm.ORGANIZATION_ID
,bbm.COMMON_ORGANIZATION_ID
from bom_bill_of_materials bbm
,bom_inventory_components bic
,mtl_system_items_b msi
,mtl_system_items_b msic
,org_organization_definitions ood
where
bbm.ASSEMBLY_ITEM_ID = msi.inventory_item_id
and bbm.ORGANIZATION_ID = msi.organization_id
and bic.COMPONENT_ITEM_ID =msic.inventory_item_id
--and bic.PK2_VALUE =msic.organization_id
and bbm.ORGANIZATION_ID =msic.organization_id
--and bbm.BILL_SEQUENCE_ID=bic.BILL_SEQUENCE_ID
and decode(bbm.COMMON_ASSEMBLY_ITEM_ID,null,bbm.BILL_SEQUENCE_ID,bbm.COMMON_BILL_SEQUENCE_ID)=bic.BILL_SEQUENCE_ID
and (bic.DISABLE_DATE is null or bic.DISABLE_DATE >= sysdate)
and bic.EFFECTIVITY_DATE <= sysdate
and ood.ORGANIZATION_ID=msi.organization_id
connect by bbm.ASSEMBLY_ITEM_ID = prior bic.COMPONENT_ITEM_ID
and bbm.ORGANIZATION_ID = decode (prior bbm.COMMON_ORGANIZATION_ID, null, prior bbm.ORGANIZATION_ID, prior bbm.COMMON_ORGANIZATION_ID)
start with msi.segment1=:P_ITEM
and ood.ORGANIZATION_CODE =:P_ORG
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
