Nolem的部落格

以下在WSDB 執行
 
1. 開table進行檢查資轉入
CREATE TABLE R12_ITEM_REVISIONS_IMPORT
(
  ITEM_NUMBER            VARCHAR2(700 BYTE),
  R11_INVENTORY_ITEM_ID  NUMBER,
  R11_ORGANIZATION_ID    NUMBER,
  R12_INVENTORY_ITEM_ID  NUMBER,
  R12_ORGANIZATION_ID    NUMBER,
  REVISION               VARCHAR2(3 BYTE),
  R12_REVISION           VARCHAR2(3 BYTE),
  IMPLEMENTATION_DATE    DATE,
  EFFECTIVITY_DATE       DATE,
  REVISION_LABEL         VARCHAR2(80 BYTE),
  IMP                    VARCHAR2(3 BYTE),
  R12_ITEM               VARCHAR2(1 BYTE)       DEFAULT 'N',
  CATEGORY_NAME          VARCHAR2(25 BYTE)
)
 
2. 塞入R12所有料號
insert into R12_ITEM_REVISIONS_IMPORT (ITEM_NUMBER ,R12_ORGANIZATION_ID,R12_INVENTORY_ITEM_ID)
  (select segment1 ,ORGANIZATION_ID,INVENTORY_ITEM_ID from mtl_system_items@e4900prod);
 
-- mapping R11 /R12 IO id
update R12_ITEM_REVISIONS_IMPORT set R11_ORGANIZATION_ID = decode(R12_ORGANIZATION_ID,103,3,104,4,164,76,184,1216,210,1158,122,76,121,118)
 
3. 抓取R12版本
declare  
  cursor cur is select * from R12_ITEM_REVISIONS_IMPORT;
  cursor cur_r12(pORGANIZATION_ID in number ,pINVENTORY_ITEM_ID in number) is  
    select *  from MTL_ITEM_REVISIONS_ALL_V@e4900prod
    where ORGANIZATION_ID = pORGANIZATION_ID and INVENTORY_ITEM_ID =pINVENTORY_ITEM_ID
     order by IMPLEMENTATION_DATE desc ;
     i number :=0;
begin
   for rec in cur loop
     for rec_r12 in cur_r12(rec.R12_ORGANIZATION_ID,rec.R12_INVENTORY_ITEM_ID) loop
       update R12_ITEM_REVISIONS_IMPORT
          set R12_REVISION=rec_r12.REVISION
       where R12_ORGANIZATION_ID = rec.R12_ORGANIZATION_ID and R12_INVENTORY_ITEM_ID =rec.R12_INVENTORY_ITEM_ID;          
      exit;
     end loop;
     i :=i+1;
     if i >=100 then
      commit;
      i :=0;
     end if;
   end loop;
   commit;
end;
 
 
4. 抓取R11 版本
declare  
  cursor cur is select * from R12_ITEM_REVISIONS_IMPORT;
  cursor cur_r11(pORGANIZATION_ID in number ,pITEM_NUMBER in varchar2) is  
    select  a.ORGANIZATION_ID , a.INVENTORY_ITEM_ID ,a.segment1 ,b.REVISION, b.IMPLEMENTATION_DATE ,b.EFFECTIVITY_DATE
      from mtl_system_items@prod a , MTL_ITEM_REVISIONS_ALL_V@prod b
    where
      a.ORGANIZATION_ID = b.ORGANIZATION_ID and a.INVENTORY_ITEM_ID =b.INVENTORY_ITEM_ID
     and a.ORGANIZATION_ID = pORGANIZATION_ID and a.segment1 =pITEM_NUMBER
     order by b.EFFECTIVITY_DATE desc ;     
     i number :=0;
begin
   for rec in cur loop
     for rec_r11 in cur_r11(rec.R11_ORGANIZATION_ID,rec.ITEM_NUMBER) loop
       update R12_ITEM_REVISIONS_IMPORT
          set R11_INVENTORY_ITEM_ID=rec_r11.INVENTORY_ITEM_ID,
              REVISION=rec_r11.REVISION ,
              IMPLEMENTATION_DATE=rec_r11.IMPLEMENTATION_DATE,
              EFFECTIVITY_DATE=rec_r11.EFFECTIVITY_DATE
       where R12_ORGANIZATION_ID = rec.R12_ORGANIZATION_ID and R12_INVENTORY_ITEM_ID =rec.R12_INVENTORY_ITEM_ID;          
      exit;
     end loop;
     i :=i+1;
     if i >=10 then
      commit;
      i :=0;
     end if;
   end loop;
   commit;
end;  
 
 

5.抓取R12 成本分類
declare  
  cursor cur is select * from R12_ITEM_REVISIONS_IMPORT;
  cursor cur_r12(pORGANIZATION_ID in number ,pINVENTORY_ITEM_ID in number) is  
   select b.CATEGORY_CONCAT_SEGS category_name
   from  mtl_item_categories@e4900prod a, MTL_CATEGORIES_V@e4900prod b
  where a.CATEGORY_SET_ID = 1100000041
    and a.CATEGORY_ID=b.CATEGORY_ID
    and a.ORGANIZATION_ID = pORGANIZATION_ID and a.INVENTORY_ITEM_ID =pINVENTORY_ITEM_ID;
     i number :=0;
begin
   for rec in cur loop
     for rec_r12 in cur_r12(rec.R12_ORGANIZATION_ID,rec.R12_INVENTORY_ITEM_ID) loop
       update R12_ITEM_REVISIONS_IMPORT
          set category_name=rec_r12.category_name
       where R12_ORGANIZATION_ID = rec.R12_ORGANIZATION_ID and R12_INVENTORY_ITEM_ID =rec.R12_INVENTORY_ITEM_ID;          
      exit;
     end loop;
     i :=i+1;
     if i >=20 then
      commit;
      i :=0;
     end if;
   end loop;
   commit;
end;
 
 
 
6. 更新不須轉入版本號
a. R12 新item
update R12_ITEM_REVISIONS_IMPORT
  set  IMP='N' , R12_ITEM='Y'
 where R11_INVENTORY_ITEM_ID is null;
 
b.R12 已變更
update R12_ITEM_REVISIONS_IMPORT
  set  IMP='N'
 where R12_REVISION<>'0';
 
c.沒版更過的料號
update R12_ITEM_REVISIONS_IMPORT
  set  IMP='N'
 where decode(R12_REVISION,'0','000')=REVISION;
 
 
7. 執行轉入interface
declare  
  vorg            NUMBER := 103;
  cursor cur is select * from R12_ITEM_REVISIONS_IMPORT where  nvl(IMP,'Y')='Y' ;
  rec_imp MTL_ITEM_REVISIONS_INTERFACE%rowtype;
  rec_imp_init MTL_ITEM_REVISIONS_INTERFACE%rowtype;
   vPROCESS_FLAG       NUMBER := 1;
   vTRANSACTION_TYPE   VARCHAR2 (10) := 'CREATE';
   vSET_PROCESS_ID     NUMBER := 1694;   
   vCREATED_BY         NUMBER := 1157;
   cnt number:=0;  
begin
  for rec in cur loop
    rec_imp := rec_imp_init;
    rec_imp.INVENTORY_ITEM_ID := rec.R12_INVENTORY_ITEM_ID ;
    rec_imp.ORGANIZATION_ID :=rec.R12_ORGANIZATION_ID;
    rec_imp.REVISION :=rec.REVISION;
    rec_imp.revision_label:=rec.REVISION;
    rec_imp.IMPLEMENTATION_DATE := rec.IMPLEMENTATION_DATE;
    rec_imp.EFFECTIVITY_DATE := sysdate;
    rec_imp.process_flag :=vPROCESS_FLAG;
    rec_imp.set_process_id :=vSET_PROCESS_ID;
    rec_imp.transaction_type := vTRANSACTION_TYPE;
    cnt :=cnt+1;
    if cnt >=1000 then
     vSET_PROCESS_ID := vSET_PROCESS_ID +1 ;
     cnt:=0;
    end if;  
    insert into MTL_ITEM_REVISIONS_INTERFACE@e4900prod values rec_imp;        
    commit;    
  end loop;
 
end;