Nolem的部落格

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

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

declare
  cursor cur is
   select msi.INVENTORY_ITEM_ID ,msi.segment1,msi.attribute28,msi.attribute29,
                      cov.INVENTORY_ITEM_ID old_INVENTORY_ITEM_ID,cov.UNIT_OF_MEASURE,cov.UOM_CODE,
                      cov.UOM_CLASS,cov.CONVERSION_RATE , cov.DEFAULT_CONVERSION_FLAG ,cov.DISABLE_DATE,
                      sysdate LAST_UPDATE_DATE , 3314 LAST_UPDATED_BY , sysdate CREATION_DATE,  3314 CREATED_BY
               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_UOM_CONVERSIONS cov  
               where msi.attribute29=cov.INVENTORY_ITEM_ID;
               
  rec_imp MTL_UOM_CONVERSIONS%rowtype;
  rec_init MTL_UOM_CONVERSIONS%rowtype;
begin
   for rec in cur loop
     rec_imp :=rec_init;
     rec_imp.INVENTORY_ITEM_ID := rec.INVENTORY_ITEM_ID;
     rec_imp.UNIT_OF_MEASURE := rec.UNIT_OF_MEASURE;
     rec_imp.UOM_CODE := rec.UOM_CODE;
     rec_imp.UOM_CLASS := rec.UOM_CLASS;
     rec_imp.LAST_UPDATE_DATE := rec.LAST_UPDATE_DATE;
     rec_imp.LAST_UPDATED_BY := rec.LAST_UPDATED_BY;
     rec_imp.CREATION_DATE := rec.CREATION_DATE;
     rec_imp.CREATED_BY := rec.CREATED_BY;
     rec_imp.CONVERSION_RATE := rec.CONVERSION_RATE;
     rec_imp.DEFAULT_CONVERSION_FLAG := rec.DEFAULT_CONVERSION_FLAG;
     rec_imp.DISABLE_DATE := rec.DISABLE_DATE;
     
     insert into MTL_UOM_CONVERSIONS values rec_imp;
     
   end loop;
   commit;
end;