Nolem的部落格

由於我方料號編碼整個調整,所以直接複製舊料號的型錄給新料號使用

mtl_system_items.attribute29  存的是舊料號ID ,讓新料號直接對串資料~

declare
 cursor cur is select msi.INVENTORY_ITEM_ID ,msi.segment1,msi.attribute28,msi.attribute29,
                      clog.INVENTORY_ITEM_ID old_INVENTORY_ITEM_ID,clog.ELEMENT_NAME,clog.ELEMENT_VALUE,clog.ELEMENT_SEQUENCE
               from  (select INVENTORY_ITEM_ID,segment1,attribute28,attribute29  
                        from mtl_system_items  
                       where ORGANIZATION_ID=101 and attribute30='Y'  and attribute29 is not null) msi ,
                      MTL_DESCR_ELEMENT_VALUES clog
               where msi.attribute29=clog.INVENTORY_ITEM_ID   ;
               
  rec_init mtl_desc_elem_val_interface%rowtype;
  rec_imp  mtl_desc_elem_val_interface%rowtype;                 
  vset_process_id number :=1694;
  vprocess_flag number :=1;
  vupd_by number :=3314;
  x_errbuf varchar2(200):=null;         
  x_retcode  number :=null;  
begin

  for rec in cur loop
    rec_imp := rec_init;
    rec_imp.INVENTORY_ITEM_ID := rec.INVENTORY_ITEM_ID;
    rec_imp.ELEMENT_NAME := rec.ELEMENT_NAME;
    rec_imp.ELEMENT_VALUE := rec.ELEMENT_VALUE;
    rec_imp.ELEMENT_SEQUENCE := rec.ELEMENT_SEQUENCE;
    rec_imp.PROCESS_FLAG := vprocess_flag;
    rec_imp.SET_PROCESS_ID := vset_process_id;
    rec_imp.LAST_UPDATED_BY := vupd_by;
    insert into  mtl_desc_elem_val_interface values rec_imp;
  end loop;
  commit;
        
  inv_item_catalog_elem_pub.process_item_catalog_grp_recs
              (
              errbuf                    => x_errbuf,
              retcode                   => x_retcode,
              p_rec_set_id              => vset_process_id,
              p_upload_rec_flag         => 1,
              p_delete_rec_flag         => 1,
              p_commit_flag             => 1,
              p_prog_appid              => NULL,
              p_prog_id                 => NULL,
              p_request_id              => NULL,
              p_user_id                 => NULL,
              p_login_id                => NULL
              );
              dbms_output.put_line(x_errbuf);
             dbms_output.put_line(to_char(x_retcode));
             
     commit;
end;