Load flex values for value set using API in oracle Apps R12

CREATE OR REPLACE PACKAGE BODY APPS.XXRAN_FLEX_VALUE_UPLOAD_PKG
AS
------------------------- MAIN Procedure --------------------------------           
PROCEDURE FLEX_field_value_load (p_errbuf         OUT VARCHAR2
                                ,p_retcode        OUT NUMBER )  IS
                CURSOR lcu_seg_values IS
                        SELECT TRIM(ls_value_set_name) value_set_name,
                               TRIM(ls_flex_value) flex_value,
                               TRIM(ls_enable_flag) enable_flag,
                               TRIM(ls_compiled_attr) compiled_attr,
                               TRIM(start_date_active) start_date_active,
                               TRIM(end_date_active) end_date_active,
                               TRIM(ls_meaning) meaning,
                               TRIM(ls_description) description,
                               TRIM(value_category) value_category                           
                          FROM XXRAN_FND_SEG_VALUE_STG
 -- STAGING TABLE HAVING RECORDS TO BE LOADED INTO FLEX VALUE
                         WHERE PROCESS_FLAG = 'P';
                    lc_error_flag           VARCHAR2(1);
                    lc_flex_value_set_name  VARCHAR2(100);
                    ln_flex_value_set_id    NUMBER;
                    lc_flex_value           VARCHAR2(100);
                    lc_flex_value_id        VARCHAR2(100);
                    lc_flex_value_meaning   varchar2(100);
                    lc_error_msg            varchar2(1000);
                    error                   VARCHAR2(400);
                    ln_length_flex_val      number;
                    ln_display_size         number;
                    ln_flex_value_count     number;
                    lc_error_loc             varchar2(1000);
                    ln_flex_length_validate  varchar2(20);
         BEGIN ---Validate Procedure
              MO_GLOBAL.set_policy_context('S',TO_NUMBER(FND_PROFILE.VALUE('ORG_ID')));
              fnd_file.put_line (fnd_file.LOG, 'Start of Log Messages: ');
              fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------------------------- '|| CHR (10));
                 p_errbuf := NULL;
                 p_retcode := 0;
                 lc_error_flag    :='N';
                 ln_length_flex_val :=0;
                 ln_display_size    := 0;
                 ln_flex_value_count :=0;
                 ln_flex_length_validate :=0;
         
            FOR lr_seg_values IN lcu_seg_values
            LOOP    
                 lc_error_flag    :='';
                 lc_flex_value_set_name :=NULL;
                 ln_flex_value_set_id :=NULL;
                 fnd_file.put_line(fnd_file.LOG,'Segment Value Upload is executing for Flex Value Set = '||
                                    lr_seg_values.value_set_name ||'Flex Value = '||lr_seg_values.flex_value);
           -- Validate Flex value set Name and get flex value set ID
           BEGIN
                SELECT flex_value_set_id
                  INTO ln_flex_value_set_id
                  FROM FND_FLEX_VALUE_SETS
                 WHERE UPPER(flex_value_set_name) = UPPER(lr_seg_values.value_set_name) ;
           EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                 lc_error_msg := 'Error : value set not found : '||lr_seg_values.value_set_name;
                 ln_flex_value_set_id := NULL;
                 WHEN OTHERS THEN
                 ln_flex_value_set_id := NULL;
           END;
        
          IF ln_flex_value_set_id  is not null then
                 
                BEGIN  -- CHECK FOR FLEX VALUE EXISTANCE
                        SELECT flex_value_id
                        INTO ln_flex_value_id
                        FROM FND_FLEX_VALUES
                        WHERE upper(flex_value)= upper(lr_seg_values.flex_value)
                        AND flex_value_set_id = ln_flex_value_set_id;
               EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                     lc_flex_value_id := 'S';
                     when too_many_rows then
                     lc_error_msg := 'Error : Too many Flex values found : '||ln_flex_value_set_id;
                     FND_FILE.PUT_LINE(FND_FILE.LOG,lc_error_msg);
                     lc_flex_value_id :=  'E';             
                     WHEN OTHERS THEN             
                     lc_error_msg := 'Error : Invlaid Flex value : '||ln_flex_value_set_id;
                     FND_FILE.PUT_LINE(FND_FILE.LOG,lc_error_msg);
                     lc_flex_value_id := 'E';
               END;
                
            
               IF  lc_flex_value_id = 'S' then
                  -----------------------Validate length of  flex value ------------------------------------
                    BEGIN
                          SELECT length(p_flex_value)
                            INTO ln_length_flex_val
                            FROM DUAL ;
                    EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                         lc_error_msg := 'Error : flex value length : '||p_flex_value;
                         FND_FILE.PUT_LINE(FND_FILE.LOG,lc_error_msg);
                         ln_flex_length_validate := 'E';
                    END;
                     IF ln_length_flex_val > ln_display_size /* TAKE DISPLAY SIZE FROM SEGMENT LIKE 50*/ THEN
                     ln_flex_length_validate :=  'E';
                     else
                     ln_flex_length_validate :=  'S';
                     END IF;
               END IF;
          
           end if;
          END LOOP;
       
     EXCEPTION
       WHEN OTHERS THEN
           FND_FILE.PUT_LINE(FND_FILE.LOG,'Error executing the program : '||SUBSTR(SQLERRM,1,255));
           p_errbuf := SUBSTR(SQLERRM,1,255);
           p_retcode := -1;
     END FLEX_field_value_load;
          PROCEDURE upload (p_VALUE_SET_NAME in varchar2,p_FLEX_VALUE varchar2,
                           p_MEANING in varchar2,p_DESCRIPTION in varchar2 )  IS
           BEGIN  
               fnd_flex_loader_apis.up_value_set_value(  p_upload_phase               =>'BEGIN',
                                               p_upload_mode                =>'',
                                               p_custom_mode                =>'',
                                               p_flex_value_set_name        => p_VALUE_SET_NAME,
                                               p_parent_flex_value_low      =>'',
                                               p_flex_value                 =>p_FLEX_VALUE,
                                               p_owner                      =>'',
                                               p_last_update_date           => to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
                                               p_enabled_flag               =>'Y',
                                               p_summary_flag               =>'N',
                                               p_start_date_active          =>'',
                                               p_end_date_active            =>'',
                                               p_parent_flex_value_high     =>'',
                                               p_rollup_flex_value_set_name =>'',
                                               p_rollup_hierarchy_code      =>'',
                                               p_hierarchy_level            =>'',
                                               p_compiled_value_attributes  =>'',
                                               p_flex_value_meaning         =>p_MEANING,
                                               p_description                =>p_DESCRIPTION
                                               );                               
            END upload ;                
end XXRAN_FLEX_VALUE_UPLOAD_PKG;