Create Bulk GL Code Combinations Using API

DECLARE

CURSOR c1
   IS
   SELECT  a.rowid row_id,a.*
FROM    temp_gl_code a
WHERE   process_flag= 'N';
   
     
  l_segment1                 GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2                 GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3                 GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4                 GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5                 GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6                 GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination        BOOLEAN;
  l_cr_combination           BOOLEAN;
  l_ccid                     GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num            FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs                GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1               VARCHAR2(240);
  p_error_msg2               VARCHAR2(240);
  v_error_flag               BOOLEAN;
  v_error_msg                VARCHAR2(3000) :=NULL;
  v_count1                   NUMBER;
 v_count2                    NUMBER;
 v_row_count                 NUMBER := 0;
 v_req_message               VARCHAR2(3000);
 v_req_id                    NUMBER;
 v_req_status                BOOLEAN;
 v_rphase                    VARCHAR2(20);
 v_rstatus                   VARCHAR2(20);
 v_dphase                    VARCHAR2(20);
 v_dstatus                   VARCHAR2(20);
  -- ----------------------
--START OF VALIDATION
-- ----------------------
BEGIN

 FOR i in c1

   LOOP
     v_error_flag := FALSE;
     v_error_msg := NULL;
 
  l_segment1  := i.segment1;
  l_segment2  := i.segment2;
  l_segment3  := i.segment3;
  l_segment4  := i.segment4;
  l_segment5  := i.segment5;
  l_segment6  := i.segment6;
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;



  BEGIN
    SELECT
    id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='COVAD_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;

  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid         :=NULL;
  END;
 
    -- -------------------------------------------------------------------------------
-- UPDATING THE PROCESS FLAG FOR ERRORS IN temp_gl_code TABLE
-- -------------------------------------------------------------------------------
    IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    --DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
    v_error_msg := v_error_msg ||'Combination already Exists '|| '; ' ;
 
        UPDATE  temp_gl_code
        SET process_flag                = 'E'
        ,errror_message                 = 'Validation Error:'||v_error_msg
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'N' ;
COMMIT;
    ELSE
 
DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;
 
    BEGIN
        UPDATE  temp_gl_code
        SET process_flag                = 'E'
        ,errror_message                  = FND_FLEX_KEYVAL.ERROR_MESSAGE
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'N' ;
COMMIT;
    END;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
       
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;
       
       
   IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
     
        UPDATE  temp_gl_code
        SET process_flag                = 'S'
        ,errror_message                  = 'Import Success='|| l_ccid
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'E' ;
    COMMIT;
    ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;