当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

ORACLE ERP导数据


  方法:把数据导入BOM清单的方法是,把数据导入接口表中,让其自动运行既可。上传文件的时候,要注意使   用ASCII字符模式。
  
  1、自己建立一中转表
  
  drop table cux_bill_temp;
  create table cux_bill_temp(
  bill_sequence_id number,
  assembly_item_id number,
  organization_id number,
  assembly_item varchar2(50),  --BOM
  component_sequence_id  number,
  component_quantity  number, --组件数量
  item_num  number, --项目序列
  operation_seq_num  number, --工序序列
  component_item_id  number,
  component_item  varchar2(50), --组件
  PLANNING_FACTOR  number, --计划%d100
  component_yield_factor number, --产出率d1
  wip_supply_type  number, --供应类型
  supply_type  varchar2(50),
  supply_subinventory  varchar2(50), --供应子库存
  OPTIONAL  number, --可选的
  OPTIONAL_disp  varchar2(10), --可选的
  MUTUALLY_EXCLUSIVE_OPTIONS  number, --互不相容
  MUTUALLY_EXCLUSIVE_O_disp varchar2(10), --互不相容
  attribute1  varchar2(50),  --排序号
  row_num  number)
  ;
  
  2、删除中转表中的数据
  

  delete cux_bill_temp;
  
  3、把要导入的数据放在扩展名为*.csv的文件中,且要相对应于中转表的字段,本例中的文件名为bill.csv。
  

  另外的脚本文件为bill.ctl,其内容如下:
  
  options (skip=1) //跳过第一行,一般第一行为其字段说明
  LOAD DATA
  INFILE bill.csv //bill.csv为数据文件
  APPEND
  INTO TABLE cux_bill_temp
  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  (与中转表相对应的字段列表)
  
  登录进入ORACLE数据库服务器,利用命令:(sqlload 用户名/密码@数据库名)载入文件bill.csv的数据入中转表。
  
  4、查看中转表中的记录数(以备导入数据后进行对比)
  

  select count(*) from cux_bill_temp;
  
  5、去除导入时在表bill.csv中的关键字段的空格字符,以免影响导入。
  

  update cux_bill_temp
  set ASSEMBLY_ITEM=replace(ASSEMBLY_ITEM,' ',''),
  COMPONENT_ITEM=replace(COMPONENT_ITEM,' ','');
  
  6、查看是否有重复的选项(既是否重复了Item)
  

  select assembly_item,component_item,min(row_num),count(*)
  from cux_bill_temp
  group by assembly_item,component_item
  having count(*)>1;
  如果有重复的Item,则要删除(或是重新合并)
  delete cux_bill_temp
  where row_num in (select min(row_num) from cux_bill_temp
  group by assembly_item,component_item
  having count(*)>1);
  
  以下步骤为选做(如有重复才做,没有重复不做7-10)
  
  7、再重新建立一个临时表(对于有重复数据,则只取一条数据,现取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,
  attribute1,
  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,
  attribute1;
  
  8、删除cux_bill_temp表
  

  delete cux_bill_temp;
  
  9、再重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,
  attribute1,
  row_num)
  select assembly_item,
  component_item,
  component_quantity,
  PLANNING_FACTOR,
  component_yield_factor,
  supply_type,
  supply_subinventory,
  OPTIONAL_disp,
  MUTUALLY_EXCLUSIVE_O_disp,
  attribute1,
  row_num
  from cux_bill_a;
  
  10、删除表cux_bill_a
  

  drop table cux_bill_a;
  
  11、再检查一次表,是否有重复的数据
  

  select assembly_item,component_item,min(row_num),count(*)
  from cux_bill_temp
  group by assembly_item,component_item
  having count(*)>1;
  
  12、查看在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 segment1=b.assembly_item and organization_id=2)
  union
  select distinct component_item item
  from cux_bill_temp b
  where not exists (select null from mtl_system_items where segment1=b.component_item and organization_id=2)
  )
  order by item;
  
  13、如果在mtl_system_items中,有不存在的物品ITEM时,要把其删除(或是把这些物品Item导入到系统中)
  

  删除:delete cux_bill_temp b
  where not exists (select null from mtl_system_items where segment1=b.component_item and organization_id=2);
  delete cux_bill_temp a
  where not exists (select null from mtl_system_items where segment1=a.assembly_item and organization_id=2);
  
  14、对没有物品Item的进行处理,把其放入另一临时表cux_item_temp中(以备查询及导入mtl_system_items表中)
  

  delete cux_item_temp;
  insert into cux_item_temp(
  segment1,description)
  select distinct item,item
  from (
  select distinct assembly_item item
  from cux_bill_temp b
  where not exists (select null from mtl_system_items where segment1=b.assembly_item and organization_id=2)
  union
  select distinct component_item item
  from cux_bill_temp b
  where not exists (select null from mtl_system_items where segment1=b.component_item and organization_id=2)
  )
  ;
  
  将找到没有ITEM的BOM数据放到另一个表中,以备下次ITEM导入后在导BOM
  
  create table cux_bom_temp1
  select distinct item
  from (
  select distinct assembly_item item
  from cux_bill_temp b
  where not exists (select null from mtl_system_items where segment1=b.assembly_item and organization_id=2)
  union
  select distinct component_item item
  from cux_bill_temp b
  where not exists (select null from mtl_system_items where segment1=b.component_item and organization_id=2)
  )
  -----------------------------------------------------------------------------------------------------------
  
  15、从表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 segment1=b.assembly_item and organization_id=2),
  component_item_id=(select inventory_item_id from mtl_system_items
  where segment1=b.component_item and organization_id=2);
  
  16、查看是否有没有物品ID的编码存在(既没有物品的ID被导入临时表cux_bill_temp中)
  

  select row_num
  from cux_bill_temp
  where assembly_item_id is null or component_item_id is null;
  
  17、对其中导入的数据进行处理
  

  update cux_bill_temp
  set OPTIONAL=1
  where upper(OPTIONAL_disp) like 'Y%';
  
  update cux_bill_temp
  set OPTIONAL=2
  where OPTIONAL is null;
  
  update cux_bill_temp
  set MUTUALLY_EXCLUSIVE_OPTIONS=1
  where upper(MUTUALLY_EXCLUSIVE_O_DISP) like 'Y%';
  
  update cux_bill_temp
  set MUTUALLY_EXCLUSIVE_OPTIONS=2
  where MUTUALLY_EXCLUSIVE_O_DISP is null;
  
  18、查看cux_bill_temp中的数据处理是否有漏
  

  select count(*)
  from cux_bill_temp
  where OPTIONAL is null
  or MUTUALLY_EXCLUSIVE_OPTIONS is null
  or assembly_item_id is null
  or component_item_id is null;
  
  19、更新其内的WIP_SUPPLY_TYPE;
  

  update cux_bill_temp
  set WIP_SUPPLY_TYPE=6
  where component_item like 'B%';
  
  20、删除表中的包(cux_bill_temp中),其相对应于表bom_bill_of_materials(既在表中已经存在了些选项包,不必导入包头,只需导入包内容既可)
  

  delete cux_bill_temp t
  where exists (select null from bom_bill_of_materials where assembly_item_id=t.assembly_item_id and organization_id=2);
  
  21、利用已经写好的包写入数据(既写入接口表bom_bill_of_mtls_interface)
  

  exec cux_bom_temp.insert_bill_15(1);
  select count(*) from cux_bill_temp temp
  where exits (select null from bom_inventory_components b
  where temp.bill_sequence_id=b.bill_sequence_id
  and temp.component_item_id=b.component_item_id);
  
  delete cux_bill_temp temp
  where exists (select null from bom_inventory_components b
  where b.bill_sequence_id=temp.bill_sequence_id
  and b.component_item_id=temp.component_item_id);
  
  exec cux_bom_temp.insert_bill_10(1);
  
  22、对写入的数据在接口表中的情况进行查看
  
  select count(*) from bom_bill_of_mtls_interface;
  
  23、接着更新
  
  exec cux_bom_temp.insert_bill_15(1);
  
  select count(*) from cux_bill_temp where bill_sequence_id is null;
  
  exec cux_bom_temp.insert_bill_20(1);
  去提交请求
  
  select count(*) from bom_inventory_comps_interface;
  (导入成功后)对组件进行排序
  exec cux_bom_temp.update_bill_item_num4;
  
  select count(*) from bom_inventory_comps_interface;
  
  24、对于接口表中的数据进行导入
  
  delete bom_bill_of_mtls_interface;
  insert into bom_bill_of_mtls_interface(
  assembly_type,assembly_item_id,
  organization_id,
  process_flag,transaction_type)
  select distinct 1,assembly_item_id,
  1,
  1,'CREATE'
  from cux_bill_temp;
相关内容
赞助商链接