ERP BOM标准功能，能导入BOM数据吗？

zhli

BOM维护，手工输入，工作量大，效率低，正确性较难保证，为了提高输入效率与准确性，能否按一定的格式，将BOM数据直接导入ERP呢？

纵横四海

oracle bom interface相关操作利用Open Interface表对BOM、Substitute、Reference Designater 进行Create、Update、Delete操作
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_date1、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

Recycled

直接导入BOM用WebADI是最方法的，只是写package的时候一定要对导入interface的数据进行验证

wilson1966

有BOM用WebADI?

wxywxy1020

BOM和工艺路线，都是可以用WEB ADI导入的，很多实施的都有现成的代码。