利用Open Interface表对BOM、Substitute、Reference Designater 进行Create、Updat、D

hphubei · 发表于 2006-8-5 20:58

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

hphubei · 发表于 2006-8-5 21:09

。[B]表bom_interface_delete_groups的大致介绍：[/B]
在我看来，没有太大的作用，就是做BOM删除的时候，有一个栏位叫 delete_group_name 是必填的栏位，可以填写系统中有，或者填写你自己的，放在bom_interface_delete_groups表里面，会在执行Request Bill and Routing Interface的时候把bom_interface_delete_groups的资料也导入到系统中.

有一个Form，叫 Delete Groups就是维护这个资料的。

hphubei · 发表于 2006-8-5 21:13

在这些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

hphubei · 发表于 2006-8-5 21:15

[B]1、BOM Create[/B]
这个相对而言比较简单

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')

hphubei · 发表于 2006-8-5 21:17

[B]2、BOM Update[/B]
在这个里面，栏位 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')
/

hphubei · 发表于 2006-8-5 21:18

[B]3、BOM Delete[/B]
比起前面的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'
)
/

hphubei · 发表于 2006-8-5 21:21

[B]3、BOM Delete[/B]
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是要不一样的。

hphubei · 发表于 2006-8-5 21:28

[B]4、Substitute Create[/B]

在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')
/

hphubei · 发表于 2006-8-5 21:29

[B]5、Substitute Update[/B]
这里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')
/

hphubei · 发表于 2006-8-5 21:30

[B]6、Substitute Delete[/B]
比较简单，和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')
/

[精华] 利用Open Interface表对BOM、Substitute、Reference Designater 进行Create、Updat、D