How to upload flex values in value set in Oracle Applications



CREATE OR REPLACE PROCEDURE APPS.CUST_LOAD_FLEX_VALUES
IS
   CURSOR C_COMP
   IS
    SELECT FLEX_VALUE_SET_NAME, FLEX_VALUE, FLEX_VALUE_MEANING, DESCRIPTION,
           ENABLED_FLAG, SUMMARY_FLAG, QUALIFIER1, QUALIFIER2, QUALIFIER3,
           HIERARCHY_LEVEL
      FROM DEV_SNGPL_ENTITY;
     
  /*
      CREATE TABLE DEV_SNGPL_ENTITY
(
  FLEX_VALUE_SET_NAME  VARCHAR2(60 BYTE),
  FLEX_VALUE           VARCHAR2(150 BYTE),
  FLEX_VALUE_MEANING   VARCHAR2(150 BYTE),
  DESCRIPTION          VARCHAR2(240 BYTE),
  ENABLED_FLAG         VARCHAR2(1 BYTE),
  SUMMARY_FLAG         VARCHAR2(1 BYTE),
  QUALIFIER1           VARCHAR2(1 BYTE),
  QUALIFIER2           VARCHAR2(1 BYTE),
  QUALIFIER3           VARCHAR2(1 BYTE),
  FINAL_QUALIFIER      VARCHAR2(2000 BYTE),
  FLEX_VALUE_SET_ID    NUMBER(10),
  FLEX_VALUE_ID        NUMBER(15),
  HIERARCHY_LEVEL      VARCHAR2(30 BYTE)
)*/

   --V_ENABLED_FLAG           VARCHAR2 (2)              := 'Y';
   --V_SUMMARY_FLAG          VARCHAR2 (2)              := 'Y';
   V_START_DATE_ACTIVE       DATE                              := SYSDATE;
   V_ERROR_MSG               VARCHAR2 (1000)       := NULL;
   V_WHO_TYPE                FND_FLEX_LOADER_APIS.WHO_TYPE;
   V_REQUEST_ID              NUMBER;
   V_REC_SUCCESS             NUMBER;
   V_REC_ERROR               NUMBER;
   V_REC_CNT                 NUMBER                        := 0;
   V_USER_ID                 NUMBER                        := FND_GLOBAL.USER_ID;
   V_LOGIN_ID                NUMBER                        := FND_GLOBAL.LOGIN_ID;
   V_REQ_ID                  NUMBER                        := FND_GLOBAL.CONC_REQUEST_ID;
  
   lc_c_comp                c_comp%ROWTYPE;
 
          


   BEGIN
       OPEN C_COMP;

       LOOP
          FETCH C_COMP
           INTO LC_C_COMP;

          EXIT WHEN C_COMP%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE('Before Begin');  
  
               BEGIN
               
                  
                   V_WHO_TYPE.CREATED_BY := V_USER_ID;
                   V_WHO_TYPE.CREATION_DATE := SYSDATE;
                   V_WHO_TYPE.LAST_UPDATED_BY := V_USER_ID;
                   V_WHO_TYPE.LAST_UPDATE_DATE := SYSDATE;
                   V_WHO_TYPE.LAST_UPDATE_LOGIN := V_LOGIN_ID;
                   
                  DBMS_OUTPUT.PUT_LINE('After Begin');
                                       
                  FND_FLEX_VALUES_PKG.LOAD_ROW
                           (X_FLEX_VALUE_SET_NAME             => LC_C_COMP.FLEX_VALUE_SET_NAME,
                            X_PARENT_FLEX_VALUE_LOW           => NULL,
                            X_FLEX_VALUE                      => LC_C_COMP.FLEX_VALUE,
                            X_WHO                             => V_WHO_TYPE,
                            X_ENABLED_FLAG                    => LC_C_COMP.ENABLED_FLAG ,
                            X_SUMMARY_FLAG                    => LC_C_COMP.SUMMARY_FLAG,
                            X_START_DATE_ACTIVE               => V_START_DATE_ACTIVE,
                            X_END_DATE_ACTIVE                 => NULL,
                            X_PARENT_FLEX_VALUE_HIGH          => NULL,
                            X_STRUCTURED_HIERARCHY_LEVEL      => NULL,
                            X_HIERARCHY_LEVEL                 => LC_C_COMP.HIERARCHY_LEVEL,
                            X_COMPILED_VALUE_ATTRIBUTES       => NULL,
                            X_VALUE_CATEGORY                  => NULL,
                            X_ATTRIBUTE1                      => NULL,
                            X_ATTRIBUTE2                      => NULL,
                            X_ATTRIBUTE3                      => NULL,
                            X_ATTRIBUTE4                      => NULL,
                            X_ATTRIBUTE5                      => NULL,
                            X_ATTRIBUTE6                      => NULL,
                            X_ATTRIBUTE7                      => NULL,
                            X_ATTRIBUTE8                      => NULL,
                            X_ATTRIBUTE9                      => NULL,
                            X_ATTRIBUTE10                     => NULL,
                            X_ATTRIBUTE11                     => NULL,
                            X_ATTRIBUTE12                     => NULL,
                            X_ATTRIBUTE13                     => NULL,
                            X_ATTRIBUTE14                     => NULL,
                            X_ATTRIBUTE15                     => NULL,
                            X_ATTRIBUTE16                     => NULL,
                            X_ATTRIBUTE17                     => NULL,
                            X_ATTRIBUTE18                     => NULL,
                            X_ATTRIBUTE19                     => NULL,
                            X_ATTRIBUTE20                     => NULL,
                            X_ATTRIBUTE21                     => NULL,
                            X_ATTRIBUTE22                     => NULL,
                            X_ATTRIBUTE23                     => NULL,
                            X_ATTRIBUTE24                     => NULL,
                            X_ATTRIBUTE25                     => NULL,
                            X_ATTRIBUTE26                     => NULL,
                            X_ATTRIBUTE27                     => NULL,
                            X_ATTRIBUTE28                     => NULL,
                            X_ATTRIBUTE29                     => NULL,
                            X_ATTRIBUTE30                     => NULL,
                            X_ATTRIBUTE31                     => NULL,
                            X_ATTRIBUTE32                     => NULL,
                            X_ATTRIBUTE33                     => NULL,
                            X_ATTRIBUTE34                     => NULL,
                            X_ATTRIBUTE35                     => NULL,
                            X_ATTRIBUTE36                     => NULL,
                            X_ATTRIBUTE37                     => NULL,
                            X_ATTRIBUTE38                     => NULL,
                            X_ATTRIBUTE39                     => NULL,
                            X_ATTRIBUTE40                     => NULL,
                            X_ATTRIBUTE41                     => NULL,
                            X_ATTRIBUTE42                     => NULL,
                            X_ATTRIBUTE43                     => NULL,
                            X_ATTRIBUTE44                     => NULL,
                            X_ATTRIBUTE45                     => NULL,
                            X_ATTRIBUTE46                     => NULL,
                            X_ATTRIBUTE47                     => NULL,
                            X_ATTRIBUTE48                     => NULL,
                            X_ATTRIBUTE49                     => NULL,
                            X_ATTRIBUTE50                     => NULL,
                            X_ATTRIBUTE_SORT_ORDER            => NULL,
                            X_FLEX_VALUE_MEANING              => LC_C_COMP.FLEX_VALUE_MEANING,
                            X_DESCRIPTION                     => LC_C_COMP.DESCRIPTION
                           );
                         
               END;
                DELETE FROM DEV_SNGPL_ENTITY
                WHERE FLEX_VALUE = LC_C_COMP.FLEX_VALUE;
               
                COMMIT;              
           END LOOP;
          
EXCEPTION
 WHEN OTHERS
    THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR IS ' || SUBSTR (SQLERRM, 1, 1000));
    
END;
/


0 comments: