熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

在Oracle ERP中導數據(BOM清單)

2013-11-13 22:15:10  來源: Oracle 

  方法:把數據導入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
From:http://tw.wingwit.com/Article/program/Oracle/201311/18605.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.