独立值集导入脚本

/*   
--1、创建临时表
  create table cux_flex_independent_temp(
    flex_value_set_name varchar2(200),
    flex_value varchar2(200),
    description  varchar2(200),
    end_date_active varchar2(200),
    hierarchy_level varchar2(200));
    
    delete cux_flex_independent_temp;
    
 --2、插入数据
    select * from cux_flex_independent_temp  where  1<>1 for update;
*/

--3.导入到独立值集
declare
  Cursor cur_import Is
    Select t.*
      From cux_flex_independent_temp t
      where not exists(select 1 from fnd_flex_values_vl ffv 
      where ffv.FLEX_VALUE_SET_ID=(select vs.flex_value_set_id 
       from fnd_flex_value_sets vs where
        vs.flex_value_set_name=t.flex_value_set_name )
      and ffv.FLEX_VALUE=t.flex_value);
      
      a VARCHAR2(200);
Begin
  For lr_import In cur_import Loop
      fnd_flex_val_api.create_independent_vset_value(p_flex_value_set_name        => lr_import.flex_value_set_name
                                                    ,p_flex_value                 => lr_import.flex_value
                                                    ,p_description                => lr_import.description
                                                    ,p_enabled_flag               => 'Y'
                                                    ,p_end_date_active            => lr_import.end_date_active
                                                    ,p_hierarchy_level            => lr_import.hierarchy_level 
                                                    ,x_storage_value              => a); 
    Commit;
    end loop;
   end;