方法:把数据导入BOM清单的方法是把数据导入接口表中让其自动运行既可上传文件的时候要注意使 用ASCII字符模式
自己建立一中转表
drop table cux_bill_temp;
create table cux_bill_temp(
bill_sequence_id number
assembly_item_id number
organization_id number
assembly_item varchar() BOM
component_sequence_id number
component_quantity number 组件数量
item_num number 项目序列
operation_seq_num number 工序序列
component_item_id number
component_item varchar() 组件
PLANNING_FACTOR number 计划%d
component_yield_factor number 产出率d
wip_supply_type number 供应类型
supply_type varchar()
supply_subinventory varchar() 供应子库存
OPTIONAL number 可选的
OPTIONAL_disp varchar() 可选的
MUTUALLY_EXCLUSIVE_OPTIONS number 互不相容
MUTUALLY_EXCLUSIVE_O_disp varchar() 互不相容
attribute varchar() 排序号
row_num number)
;
删除中转表中的数据
delete cux_bill_temp;
把要导入的数据放在扩展名为*csv的文件中且要相对应于中转表的字段本例中的文件名为billcsv
另外的脚本文件为billctl其内容如下:
options (skip=) //跳过第一行一般第一行为其字段说明
LOAD DATA
INFILE billcsv //billcsv为数据文件
APPEND
INTO TABLE cux_bill_temp
FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
(与中转表相对应的字段列表)
登录进入ORACLE数据库服务器利用命令:(sqlload 用户名/密码@数据库名)载入文件billcsv的数据入中转表
查看中转表中的记录数(以备导入数据后进行对比)
select count(*) from cux_bill_temp;
去除导入时在表billcsv中的关键字段的空格字符以免影响导入
update cux_bill_temp
set ASSEMBLY_ITEM=replace(ASSEMBLY_ITEM )
COMPONENT_ITEM=replace(COMPONENT_ITEM );
查看是否有重复的选项(既是否重复了Item)
select assembly_itemcomponent_itemmin(row_num)count(*)
from cux_bill_temp
group by assembly_itemcomponent_item
having count(*)>;
如果有重复的Item则要删除(或是重新合并)
delete cux_bill_temp
where row_num in (select min(row_num) from cux_bill_temp
group by assembly_itemcomponent_item
having count(*)>);
以下步骤为选做(如有重复才做没有重复不做)
再重新建立一个临时表(对于有重复数据则只取一条数据现取row_num最小的一条)
drop table cux_bill_a;
create table cux_bill_a
as
select assembly_item
component_item
component_quantity
PLANNING_FACTOR
component_yield_factor
supply_type
supply_subinventory
OPTIONAL_disp
MUTUALLY_EXCLUSIVE_O_disp
attribute
min(row_num) row_num
from cux_bill_temp
group by assembly_item
component_item
component_quantity
PLANNING_FACTOR
component_yield_factor
supply_type
supply_subinventory
OPTIONAL_disp
MUTUALLY_EXCLUSIVE_O_disp
attribute;
删除cux_bill_temp表
delete cux_bill_temp;
再重cux_bill_a表中把数据导入给cux_bill_temp表完成把重复数据剔除的功能
insert into cux_bill_temp(
assembly_item
component_item
component_quantity
PLANNING_FACTOR
component_yield_factor
supply_type
supply_subinventory
OPTIONAL_disp
MUTUALLY_EXCLUSIVE_O_disp
attribute
row_num)
select assembly_item
component_item
component_quantity
PLANNING_FACTOR
component_yield_factor
supply_type
supply_subinventory
OPTIONAL_disp
MUTUALLY_EXCLUSIVE_O_disp
attribute
row_num
from cux_bill_a;
删除表cux_bill_a
drop table cux_bill_a;
再检查一次表是否有重复的数据
select assembly_itemcomponent_itemmin(row_num)count(*)
from cux_bill_temp
group by assembly_itemcomponent_item
having count(*)>;
查看在mtl_system_items表中既是在库存表中有没有不存在的Item
select distinct item
from (
select distinct assembly_item item
from cux_bill_temp b
where not exists (select null from mtl_system_items where segment=bassembly_item and organization_id=)
union
select distinct component_item item
from cux_bill_temp b
where not exists (select null from mtl_system_items where segment=ponent_item and organization_id=)
)
order by item;
如果在mtl_system_items中有不存在的物品ITEM时要把其删除(或是把这些物品Item导入到系统中)
删除:delete cux_bill_temp b
where not exists (select null from mtl_system_items where segment=ponent_item and organization_id=);
delete cux_bill_temp a
where not exists (select null from mtl_system_items where segment=aassembly_item and organization_id=);
对没有物品Item的进行处理把其放入另一临时表cux_item_temp中(以备查询及导入mtl_system_items表中)
delete cux_item_temp;
insert into cux_item_temp(
segmentdescription)
select distinct itemitem
from (
select distinct assembly_item item
from cux_bill_temp b
where not exists (select null from mtl_system_items where segment=bassembly_item and organization_id=)
union
select distinct component_item item
from cux_bill_temp b
where not exists (select null from mtl_system_items where segment=ponent_item and organization_id=)
)
;
将找到没有ITEM的BOM数据放到另一个表中以备下次ITEM导入后在导BOM
create table cux_bom_temp
select distinct item
from (
select distinct assembly_item item
from cux_bill_temp b
where not exists (select null from mtl_system_items where segment=bassembly_item and organization_id=)
union
select distinct component_item item
from cux_bill_temp b
where not exists (select null from mtl_system_items where segment=ponent_item and organization_id=)
)
从表mtl_system_items中把物品的编码ID加入中转表cux_bill_temp表(从项目主组织)中
update cux_bill_temp b
set assembly_item_id=(select inventory_item_id from mtl_system_items
where segmen
