API to create GL Code Combination in Oracle APPS

DECLARE

   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;

   l_error_msg1          VARCHAR2 (240);

   l_error_msg2          VARCHAR2 (240);

BEGIN

   l_conc_segs := '01-01-012-012345-012-012345-0123-0123-0000-0000';

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 = 'XX_ACCOUNTING_FLEXFIELD';

EXCEPTION

WHEN OTHERS THEN

        l_structure_num := NULL;

END;

   --Check if Code Combination 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;

IF l_ccid IS NOT NULL

THEN

--The Code Combination is Available

DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID= ' || l_ccid);

ELSE

--Code Combination does not exists. So creating 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

            );

l_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

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);

l_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;

DBMS_OUTPUT.PUT_LINE ('NEW COMBINATION_ID = ' || l_ccid);

ELSE

--Error in creating a new combination

DBMS_OUTPUT.PUT_LINE (

'Error in creating the combination: ' || l_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: ' || l_error_msg1);

END IF;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('Main Error: '||SQLCODE || ' ' || SQLERRM);

END;