BOM模組的介面表使用總結
利用BOM介面完成BOM表、Routing的新增還是比較順利的，對於刪除物料、修改物料、刪除資源、新增資源、修改資源這樣的操作，按常規思維利用介面去做還是比較糾結的，每次都要花時間從頭測過。現將相關資料整理總結一下(不一定非得用遊標，主要為增強可讀性)：
1. 通過介面批量插入BOM物料
declare
x_operation_seq_num number;
cursor xxx_ass is
select msi.inventory_item_id,
msi.segment1,
bbom.BILL_SEQUENCE_ID BILL_SEQUENCE_ID1,
bc.component_quantity,
msi1.segment1 com_segment1
from bom_bill_of_materials bbom,
bom_bill_of_materials bbom1, --tJ2
bom_components_b bc,
bom.bom_operational_routings bor,
bom.bom_operation_sequences bos,
bom.bom_standard_operations bso,
mtl_system_items_b msi, --成品料號
mtl_system_items_b msi1 --採購料號
where bbom.common_bill_sequence_id=bc.bill_sequence_id
and bbom1.ASSEMBLY_ITEM_ID=bbom.ASSEMBLY_ITEM_ID
and bbom1.ORGANIZATION_ID=117
and to_char(bc.disable_date,'yyyy/mm/dd')='2012/01/11'
and bc.implementation_date is not null
and bbom.ORGANIZATION_ID=116--&ORGANIZATION_ID --組織
and bbom.ASSEMBLY_ITEM_ID=bor.assembly_item_id
and bbom.ORGANIZATION_ID=bor.organization_id
and bor.routing_sequence_id=bos.routing_sequence_id
and bos.operation_seq_num=bc.operation_seq_num
and bbom.ORGANIZATION_ID=bso.organization_id
and bos.standard_operation_id=bso.standard_operation_id
and bos.department_id=bso.department_id
and bos.disable_date is null
and bso.operation_code='A700' --表示 鑽孔
and bc.component_item_id=msi1.inventory_item_id
and msi1.organization_id=115
and bbom.ASSEMBLY_ITEM_ID=msi.inventory_item_id
and bbom.ORGANIZATION_ID=msi.organization_id
and msi.inventory_item_status_code='Active'
-- and msi.segment1='A681AC6037B11'
;
begin
for p_ass in xxx_ass loop
if substr(p_ass.segment1,7,1)<='2' then
x_operation_seq_num := 20;
else
x_operation_seq_num := 70;
end if;
INSERT into bom_bill_of_mtls_interface
(assembly_item_id,
organization_id,
bill_sequence_id,
process_flag,
organization_code,
item_number,
assembly_type,
last_updated_by,
created_by,
transaction_type
)
VALUES
(
p_ass.inventory_item_id,
117,
p_ass.BILL_SEQUENCE_ID1, --bill_sequence_iD
1,
'TJ2',
p_ass.segment1,
1,
-1, --更新時用系統使用者
-1,
'UPDATE'
);
INSERT into bom_inventory_comps_interface
(component_item_number,
last_updated_by,
created_by,
operation_seq_num,
effectivity_date,
supply_subinventory,
location_name,
transaction_type,
process_flag,
component_quantity,
assembly_item_number,
organization_code,
wip_supply_type
)
VALUES(
p_ass.com_segment1,
-1,
-1,
x_operation_seq_num,
sysdate,
'11911',
'A502.000.000',--正式環境是D702.000.000
'CREATE',
1,
p_ass.component_quantity,
p_ass.segment1,
'TJ2',
3
);
commit;
end loop;
exception when others then
rollback;
raise_application_error (-20001,
'Disable item is failure！'
);
end;
2.通過介面，批量插入工序
DECLARE
l_iface_rec bom.bom_op_routings_interface%ROWTYPE;
l_iface_seq_rec bom.bom_op_sequences_interface%ROWTYPE;
l_iface_res_rec bom.bom_op_resources_interface%ROWTYPE;
l_cur_mfg_org_id NUMBER := 117; --Current Inv Organization
l_user_id NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
l_transaction_type varchar2(20);
cursor xxx_r is
select distinct
bor.organization_id,
bor.assembly_item_id,
msi.segment1,
bor.routing_sequence_id,
msi.unit_volume
from BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
mtl_system_items_b msi
where bor.routing_sequence_id=bos.routing_sequence_id
and bor.organization_id=117
and bos.standard_operation_id in(9,4)
and length(msi.segment1)=13
and bor.assembly_item_id=msi.inventory_item_id
and msi.organization_id=115
--and msi.segment1='A681AC6037B11'
and not exists
( select 1
from BOM_OPERATION_SEQUENCES bos1
where bos.routing_sequence_id=bos1.routing_sequence_id
and bos1.standard_operation_id=10
)
;
BEGIN
for i in xxx_r loop
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := l_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := l_user_id;
l_iface_rec.last_update_login := -1;
l_iface_seq_rec.last_update_date := SYSDATE;
l_iface_seq_rec.last_updated_by := l_user_id;
l_iface_seq_rec.creation_date := SYSDATE;
l_iface_seq_rec.created_by := l_user_id;
l_iface_seq_rec.last_update_login := -1;
l_iface_res_rec.last_update_date := SYSDATE;
l_iface_res_rec.last_updated_by := l_user_id;
l_iface_res_rec.creation_date := SYSDATE;
l_iface_res_rec.created_by := l_user_id;
l_iface_res_rec.last_update_login := -1;
l_iface_rec.process_flag := 1;
l_iface_rec.transaction_type := 'UPDATE';
l_iface_rec.organization_id := l_cur_mfg_org_id;
l_iface_rec.assembly_item_id := i.assembly_item_id;
l_iface_rec.assembly_item_number := i.segment1;
INSERT INTO bom.bom_op_routings_interface VALUES l_iface_rec;
l_iface_seq_rec.process_flag := 1;
l_iface_seq_rec.transaction_type := 'CREATE';
l_iface_seq_rec.organization_id := l_iface_rec.organization_id;
l_iface_seq_rec.assembly_item_number := l_iface_rec.assembly_item_number;
l_iface_seq_rec.alternate_routing_designator := l_iface_rec.alternate_routing_designator;
if substr(i.segment1,7,1)<='2' then
l_iface_seq_rec.operation_seq_num := 20;
else
l_iface_seq_rec.operation_seq_num := 70;
end if;
l_iface_seq_rec.department_code := 'A70';
l_iface_seq_rec.effectivity_date := SYSDATE;
l_iface_seq_rec.reference_flag :=2;
l_iface_seq_rec.standard_operation_id :=10;
INSERT INTO bom.bom_op_sequences_interface VALUES l_iface_seq_rec;
l_iface_res_rec.process_flag := 1;
l_iface_res_rec.organization_id := l_iface_seq_rec.organization_id;
l_iface_res_rec.assembly_item_number := l_iface_seq_rec.assembly_item_number;
l_iface_res_rec.alternate_routing_designator := l_iface_seq_rec.alternate_routing_designator;
l_iface_res_rec.operation_seq_num := l_iface_seq_rec.operation_seq_num;
l_iface_res_rec.effectivity_date := l_iface_seq_rec.effectivity_date;
l_iface_res_rec.transaction_type := 'UPDATE';
l_iface_res_rec.resource_seq_num := 1;
l_iface_res_rec.resource_code := 'A70E001';
l_iface_res_rec.usage_rate_or_amount := i.unit_volume;
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
l_iface_res_rec.resource_seq_num := 2;
l_iface_res_rec.resource_code := 'A70M001';
l_iface_res_rec.usage_rate_or_amount := i.unit_volume;
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
l_iface_res_rec.resource_seq_num := 3;
l_iface_res_rec.resource_code := 'A70P001';
l_iface_res_rec.usage_rate_or_amount := i.unit_volume;
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
l_iface_res_rec.resource_seq_num := 4;
l_iface_res_rec.resource_code := 'AN0T001';
l_iface_res_rec.usage_rate_or_amount := round(i.unit_volume*0.47361,6);
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
end loop;
commit;
END;
3. 單獨通過製程資源介面表，插入資源
DECLARE
l_iface_res_rec bom.bom_op_resources_interface%ROWTYPE;
l_cur_mfg_org_id NUMBER := 116; --Current Inv Organization
l_user_id NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
cursor xxx is
select distinct
bor.routing_sequence_id,
bos.operation_sequence_id,
bos.operation_seq_num,
bore.resource_seq_num,
SPNLtoPCS.element_value,
msi.unit_volume
from BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
bom_operation_resources bore,
mtl_system_items_b msi,
(select md.inventory_item_id,md.element_value
from apps.MTL_DESCR_ELEMENT_VALUES md
where md.element_sequence=140)SPNLtoPCS --出貨片排版
where bor.routing_sequence_id=bos.routing_sequence_id
and bor.organization_id=116
and bos.standard_operation_id=45
and bos.operation_sequence_id=bore.operation_sequence_id
and bore.resource_id =63
and bor.assembly_item_id=SPNLtoPCS.inventory_item_id(+)
and bor.assembly_item_id=msi.inventory_item_id
and msi.organization_id=115
--and msi.segment1='C226AA4049B11'
;
BEGIN
for i in xxx loop
l_iface_res_rec.last_update_date := SYSDATE;
l_iface_res_rec.last_updated_by := l_user_id;
l_iface_res_rec.creation_date := SYSDATE;
l_iface_res_rec.created_by := l_user_id;
l_iface_res_rec.last_update_login := -1;
l_iface_res_rec.process_flag := 1;
l_iface_res_rec.routing_sequence_id := i.routing_sequence_id;
l_iface_res_rec.operation_sequence_id :=i.operation_sequence_id;
l_iface_res_rec.transaction_type :='CREATE';
l_iface_res_rec.effectivity_date :=to_date('12-JUL-2006 10:14:03',
'DD-MON-YYYY HH24:MI:SS');
l_iface_res_rec.resource_seq_num := 5;
l_iface_res_rec.resource_id :=202;
l_iface_res_rec.usage_rate_or_amount :=i.element_value;
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
l_iface_res_rec.resource_seq_num := 6;
l_iface_res_rec.resource_id :=7008;
l_iface_res_rec.usage_rate_or_amount :=i.unit_volume;
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
end loop ;
commit;
END;
4.通過介面批量刪除特定站別的所有BOM物料
declare
cursor xxx_comp is
select msi.segment1,
bbom.ASSEMBLY_ITEM_ID,
bbom.BILL_SEQUENCE_ID,
bc.operation_seq_num,
bc.item_num,
bc.component_item_id,
bc.component_sequence_id,
msi1.segment1 com_segment1
from bom_bill_of_materials bbom,
bom_components_b bc,
bom.bom_operational_routings bor,
bom.bom_operation_sequences bos,
bom.bom_standard_operations bso,
mtl_system_items_b msi, --成品料號
mtl_system_items_b msi1 --採購料號
where bbom.common_bill_sequence_id=bc.bill_sequence_id
--and bc.disable_date is null
and bc.implementation_date is not null
and bbom.ORGANIZATION_ID=116--&ORGANIZATION_ID --組織
and bbom.ASSEMBLY_ITEM_ID=bor.assembly_item_id
and bbom.ORGANIZATION_ID=bor.organization_id
and bor.routing_sequence_id=bos.routing_sequence_id
and bos.operation_seq_num=bc.operation_seq_num
and bbom.ORGANIZATION_ID=bso.organization_id
and bos.standard_operation_id=bso.standard_operation_id
and bos.department_id=bso.department_id
and bos.disable_date is null
and bso.operation_code='A700' --D700表示 OSP
and bc.component_item_id=msi1.inventory_item_id
and msi1.organization_id=115
and bbom.ASSEMBLY_ITEM_ID=msi.inventory_item_id
and bbom.ORGANIZATION_ID=msi.organization_id
and msi.inventory_item_status_code='Active'
and msi.segment1='A681AC8033C10'
;
p_assem mtl_system_items_b.segment1 %type;
begin
p_assem:='AAAA'; --初值
for p_comp in xxx_comp
loop
if p_assem<>p_comp.segment1 then
p_assem:=p_comp.segment1;
INSERT into bom_bill_of_mtls_interface
(assembly_item_id,
organization_id,
bill_sequence_id,
process_flag,
organization_code,
item_number,
assembly_type,
last_updated_by,
created_by,
transaction_type
)
VALUES
(
p_comp.ASSEMBLY_ITEM_ID,
116,
p_comp.BILL_SEQUENCE_ID, --bill_sequence_iD
1,
'TJ1',
p_comp.segment1,
1,
-1, --更新時用系統使用者
-1,
'UPDATE'
);
end if;
INSERT into bom_inventory_comps_interface
(
component_item_id,
assembly_item_id,
bill_sequence_id,
COMPONENT_SEQUENCE_ID,
last_updated_by,
created_by,
operation_seq_num,
--effectivity_date,
transaction_type,
process_flag,
disable_date,
organization_code,
delete_group_name
)
VALUES
(
p_comp.component_item_id,
p_comp.ASSEMBLY_ITEM_ID,
p_comp.BILL_SEQUENCE_ID, --bill_sequence_iD
p_comp.component_sequence_id, --COMPONENT_SEQUENCE_ID
-1,
-1 ,
p_comp.operation_seq_num,
--TRUNC(SYSDATE),
'DELETE',
1,
TRUNC(SYSDATE),
'TJ1',
'C1213'
);
commit;
end loop;
exception when others then
rollback;
raise_application_error (-20001,
'Disable item is failure！'
);
end;
5. 單獨通過製程資源介面表，刪除資源
DECLARE
l_iface_res_rec bom.bom_op_resources_interface%ROWTYPE;
l_cur_mfg_org_id NUMBER := 116; --Current Inv Organization
l_user_id NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
cursor xxx is
select distinct
bor.routing_sequence_id,
bos.operation_sequence_id,
bos.operation_seq_num,
bore.resource_seq_num
from BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
bom_operation_resources bore
where bor.routing_sequence_id=bos.routing_sequence_id
and bor.organization_id=116
and bos.standard_operation_id=45
and bos.operation_sequence_id=bore.operation_sequence_id
and bore.resource_id <>63
;
BEGIN
for i in xxx loop
l_iface_res_rec.last_update_date := SYSDATE;
l_iface_res_rec.last_updated_by := l_user_id;
l_iface_res_rec.creation_date := SYSDATE;
l_iface_res_rec.created_by := l_user_id;
l_iface_res_rec.last_update_login := -1;
l_iface_res_rec.process_flag := 1;
l_iface_res_rec.transaction_type :='DELETE';
l_iface_res_rec.operation_sequence_id := i.operation_sequence_id;
l_iface_res_rec.resource_seq_num := i.resource_seq_num;
l_iface_res_rec.routing_sequence_id := i.routing_sequence_id;
l_iface_res_rec.operation_seq_num := i.operation_seq_num;
l_iface_res_rec.effectivity_date :=to_date('12-JUL-2006 10:14:03',
'DD-MON-YYYY HH24:MI:SS');
INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
end loop;
END;
6. 刪除特定製程
DECLARE
l_iface_rec bom.bom_op_routings_interface%ROWTYPE;
l_iface_seq_rec bom.bom_op_sequences_interface%ROWTYPE;
l_iface_res_rec bom.bom_op_resources_interface%ROWTYPE;
l_cur_mfg_org_id NUMBER := 116; --Current Inv Organization
l_user_id NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
l_transaction_type varchar2(20);
cursor xxx is
select distinct
bor.organization_id,
bor.assembly_item_id,
msi.segment1,
bor.routing_sequence_id,
msi.unit_volume,
bos.operation_seq_num,
bos.department_id,
bos.operation_sequence_id
from BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
mtl_system_items_b msi
where bor.routing_sequence_id=bos.routing_sequence_id
and bor.organization_id=116
and bos.standard_operation_id=45
and length(msi.segment1)=13
and bor.assembly_item_id=msi.inventory_item_id
and msi.organization_id=115
and msi.segment1='A681AC8033C10'
;
BEGIN
for i in xxx loop
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := l_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := l_user_id;
l_iface_rec.last_update_login := -1;
l_iface_seq_rec.last_update_date := SYSDATE;
l_iface_seq_rec.last_updated_by := l_user_id;
l_iface_seq_rec.creation_date := SYSDATE;
l_iface_seq_rec.created_by := l_user_id;
l_iface_seq_rec.last_update_login := -1;
l_iface_res_rec.last_update_date := SYSDATE;
l_iface_res_rec.last_updated_by := l_user_id;
l_iface_res_rec.creation_date := SYSDATE;
l_iface_res_rec.created_by := l_user_id;
l_iface_res_rec.last_update_login := -1;
l_iface_rec.process_flag := 1;
l_iface_rec.transaction_type := 'UPDATE';
l_iface_rec.organization_id := l_cur_mfg_org_id;
l_iface_rec.assembly_item_number := i.segment1;
INSERT INTO bom.bom_op_routings_interface VALUES l_iface_rec;
l_iface_seq_rec.process_flag := 1;
l_iface_seq_rec.delete_group_name := 'yang1213';
l_iface_seq_rec.transaction_type := 'DELETE';
l_iface_seq_rec.operation_sequence_id := i.operation_sequence_id;
INSERT INTO bom.bom_op_sequences_interface VALUES l_iface_seq_rec;
end loop;
commit;
END;
刪除製程、工序或BOM是需要指定刪除群組。有事還會經常出現“You cannot update or delete the operation resources when there is no operation sequence num 70”異常。此時，建議不要通過組裝料號或id進行刪除，直接用 routing id刪除。
附上從Metalink網站上下載的技術文件，很有用的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16860121/viewspace-714924/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- BOM學習總結
- SAP MM模組常用表總結
- JS知識總結之BOM物件JS物件
- web前端知識總結-BOM和DOMWeb前端
- co模組總結
- JavaScript學習總結（三）BOM和DOM詳解JavaScript
- python logging模組使用總結Python
- node模組總結（初步）
- JavaScript 模組化總結JavaScript
- 免費介面總結
- 介面測試總結
- 關於介面返回BOM頭處理的問題
- 評論模組開發總結
- 上傳模組開發總結
- 前端模組化簡單總結前端
- ListenalbeFuture的使用總結
- git的使用總結Git
- WebView的使用總結WebView
- JXCategoryView的使用總結GoView
- cmake的使用總結
- SOLIDWORKS如何一鍵生成BOM表Solid
- 分割槽表總結
- 表分割槽總結
- 分割槽表 總結
- FastReport報表控制元件使用技巧總結AST控制元件
- 總帳介面表詳細說明
- 介面模組的定義
- maven 學習總結(八)——使用Maven構建多模組專案Maven
- Nginx 學習總結（4）—— Rewrite 模組Nginx
- Android 開發功能模組總結Android
- Java總結-抽象類與介面Java抽象
- [總結] Oracle表的分析統計Oracle
- oracle臨時表的用法總結Oracle
- 前端模組化，AMD和CMD的區別總結前端
- 關於 JS 模組化的最佳實踐總結JS
- git 子模組使用小結Git
- 最近使用 gin 的總結
- iconfonts使用的總結