轉自:

利用Open Interface表對BOM、Substitute、Reference Designater 進行Create、Update、Delete操作

[@more@]

1、首先介紹涉及到的幾個Interface表
BOM:
bom_bill_of_mtls_interface
bom_inventory_comps_interface
bom_interface_delete_groups
Substitute:
bom_sub_comps_interface
Reference Designator:
bom_ref_desgs_interface

。表bom_interface_delete_groups的大致介紹：
在我看來，沒有太大的作用，就是做BOM刪除的時候，有一個欄位叫 delete_group_name 是必填的欄位，可以填寫系統中有，或者填寫你自己的，放在bom_interface_delete_groups表裡面，會在執行Request Bill and Routing Interface的時候把bom_interface_delete_groups的資料也匯入到系統中.

有一個Form，叫 Delete Groups就是維護這個資料的。

在這些Interface表裡面，有一些欄位是必須的或者二者至少選一
assembly_item_id/item_number(assembly_item_number)
organization_id/organization_code
component_item_id/component_item_number
process_flag
transaction_type (Update/Create/Delete)
effectivity_date

1、BOM Create
這個相對而言比較簡單

INSERT into bom_bill_of_mtls_interface
(process_flag,
organization_code,
item_number,
assembly_type,
last_updated_by,
created_by,
transaction_type)
VALUES
(1,
'V1',
'MARK_BOM_T',
1,
-1,
-1,
'CREATE')
/
commit;

-- Load inventory components (children) of BOM
-- ISV_ITEM2C

INSERT into bom_inventory_comps_interface
(component_item_number,
last_updated_by,
created_by,
operation_seq_num,
effectivity_date,
transaction_type,
process_flag,
component_quantity,
assembly_item_number,
organization_code)
VALUES
('MARK_ONE',
-1,
-1 ,
'1',
sysdate,
'CREATE',
1,
3,
'MARK_BOM_T',
'V1')

2、BOM Update
在這個裡面，欄位 bill_sequence_id、COMPONENT_SEQUENCE_ID就必要要有值了

INSERT into bom_bill_of_mtls_interface
(assembly_item_id,
organization_id,
bill_sequence_id,
process_flag,
organization_code,
item_number,
assembly_type,
delete_group_name,
last_updated_by,
created_by,
transaction_type)
VALUES
(
11816,
204,
45185, --bill_sequence_iD
1,
'V1',
'MARK_BOM_T',
1,
'BILLS',
-1,
-1,
'UPDATE')
/

INSERT into bom_inventory_comps_interface
(
component_item_id,
assembly_item_id,
bill_sequence_id,

COMPONENT_SEQUENCE_ID,
--component_item_number,
last_updated_by,
created_by,
operation_seq_num,
effectivity_date,
transaction_type,
delete_group_name,
process_flag,
component_quantity,
-- assembly_item_number,
organization_code)
VALUES
(
11817,
11816,
45185, --bill_sequence_iD
45186, --COMPONENT_SEQUENCE_ID
--'MARK_ONE',
-1,
-1 ,
'1',
TRUNC(SYSDATE),
'UPDATE',
'Components',
1,
1, --component_quantity
--'MARK_BOM_T',
'V1')
/

3、BOM Delete
比起前面的Create和Update，這個就要複雜一些了。
欄位 delete_group_name是必須的

INSERT into bom_bill_of_mtls_interface
(assembly_item_id,
organization_id,
bill_sequence_id,
process_flag,
--organization_code,
-- item_number,
assembly_type,
delete_group_name,
last_updated_by,
created_by,
transaction_type)
VALUES
(
11816,
204,
45611, --bill_sequence_id
1,
--'V1',
--'MARK_BOM_T',
1,
'BILLS',
-1,
-1,
'DELETE')
/

INSERT into bom_inventory_comps_interface
(
component_item_id,
assembly_item_id,
bill_sequence_id,

COMPONENT_SEQUENCE_ID,
-- component_item_number,
last_updated_by,
operation_seq_num,
effectivity_date,
transaction_type,
delete_group_name,
process_flag,
component_quantity,
assembly_type,
created_by
--assembly_item_number
--,organization_code
)
VALUES
(
11817, --component_item_id
11816,
45611, --bill_sequence_id
45612, --COMPONENT_SEQUENCE_ID
--'MARK_ONE',
-1 ,
1,
TRUNC(SYSDATE),
'DELETE',
'Components',
1,
1,
1,
-1
--'MARK_BOM_T'
--,'V1'
)
/

3、BOM Delete
Insert了上面的記錄之後，跑Request Bill and Routing Interface，之後你發現你的BOM或者Component並沒有被刪除掉。這個時候，你到Form Delete Groups裡面輸入你上面的delete_group_name並查詢後，你會發現你的BOM的Item會出現在該Form的下方，狀態為Pending，這個時候你需要定位游標到你的BOM所在行，點選下面的按鈕 delete Group，這個時候會啟動一個Request Delete Item Information,等這個Concurrent結束之後，你會發現，你的BOM或者Component才真正的刪除了。

這個問題讓我頭大了好久。

delete_group_name在刪除BOM或者Component時是必須的，如果你不想使用系統中已有的，那麼可以自己建立，insert記錄到表bom_interface_delete_groups中即可。

INSERT into bom_interface_delete_groups(entity_name, delete_group_name, description)
values ('BOM_BILL_OF_MTLS_INTERFACE', 'BILLS', 'DELETE GROUP FOR BILLS OF MATERIAL')
/

INSERT into bom_interface_delete_groups(entity_name, delete_group_name, description)
values ('BOM_INVENTORY_COMPS_INTERFACE', 'Components', 'Component Delete Group')

這些記錄也會在匯入BOM的時候一起匯入到系統中。注意，對BOM和Component，entity_name是要不一樣的。

4、Substitute Create

在BOM的Form上面，有一個按鈕叫Substitute 的，就是替換料件的。

INSERT into bom_sub_comps_interface
(process_flag,
substitute_comp_number,
substitute_item_quantity,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
operation_seq_num,
effectivity_date,
--substitute_component_id,
transaction_type)
VALUES
(1,
'MARK_THREE',
1,
45187, --component_sequence_id
'V1',
'MARK_BOM_T',
'MARK_TWO',
1,
TRUNC(SYSDATE),
--25536,
'CREATE')
/

5、Substitute Update
這裡substitute_component_id,欄位是要有值的。

INSERT into bom_sub_comps_interface
(process_flag,
substitute_comp_number,
substitute_item_quantity,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
operation_seq_num,
effectivity_date,
substitute_component_id,
transaction_type)
VALUES
(1,
'MARK_THREE',
2, --substitute_item_quantity
45187, --component_sequence_id
'V1',
'MARK_BOM_T',
'MARK_TWO',
1,
TRUNC(SYSDATE),
11819,
'UPDATE')
/

6、Substitute Delete
比較簡單，和Update差不多，能在Request之後直接看到結果
INSERT into bom_sub_comps_interface
(process_flag,
substitute_comp_number,
substitute_item_quantity,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
operation_seq_num,
effectivity_date,
substitute_component_id,
transaction_type)
VALUES
(1,
'MARK_THREE',
2, --substitute_item_quantity
45187, --component_sequence_id
'V1',
'MARK_BOM_T',
'MARK_TWO',
1,
TRUNC(SYSDATE),
11819,
'DELETE')
/

7、Reference Designator Create
BOM的Form上面，有一個按鈕叫Designator的

INSERT into bom_ref_desgs_interface
(process_flag,
component_reference_designator,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
ref_designator_comment,
transaction_type)
VALUES (1,
'r1',
45186,
'V1',
'MARK_BOM_T',
'MARK_ONE',
'Reference Designator R1!',
'CREATE')
/

-- Load reference designators
-- r2

INSERT into bom_ref_desgs_interface
(process_flag,
component_reference_designator,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
ref_designator_comment,
transaction_type)
VALUES (1,
'r2',
45186,
'V1',
'MARK_BOM_T',
'MARK_ONE',
'LOOK: Reference Designator R2!',
'CREATE')
/

8、Reference Designator Update

INSERT into bom_ref_desgs_interface
(process_flag,
component_reference_designator,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
ref_designator_comment,
transaction_type)
VALUES (1,
'r2',
45186,
'V1',
'MARK_BOM_T',
'MARK_ONE',
'UPDATED:Reference Designator R2!',
'UPDATE')
/

9、Reference Designator Delete
和Update差不多，能在Request之後直接看到結果

INSERT into bom_ref_desgs_interface
(process_flag,
component_reference_designator,
component_sequence_id,
organization_code,
assembly_item_number,
component_item_number,
ref_designator_comment,
transaction_type)
VALUES (1,
'r1',
45186,
'V1',
'MARK_BOM_T',
'MARK_ONE',
'Reference Designator R1!',
'DELETE')
/

這些Script，都是我經過測試，確實可行的。

如果出現錯誤，在表 mtl_interface_errors 裡面可以看到錯誤資訊

process_flag
初始插入記錄到Interface表的時候都是 1，表示Pending
7表示Import Successfully
3表示Error
應該還有其他的幾個數字及代表意義，以後有時候再找找相關資料然後共享。

如果哪位知道的比較詳細，也可以分享出來，謝謝

BOM介面表匯入到正式表裡面要透過一個程式跑, 這個程式的名字?SHORT NAME 叫 Import Bills and Routings

利用Open Interface表對BOM、Substitute、Reference Designater 進行Create、Update、Delete操作

相關文章