Oracle GL Account Code Combination ID’s (CCID’s) through APIs

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed. 

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package. 

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.
 
The Combination_id output may be NULL if combination is invalid.

/* Formatted on 3/27/2017 4:30:27 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_application_short_name   VARCHAR2 (240);
   l_key_flex_code            VARCHAR2 (240);
   l_structure_num            NUMBER;
   l_validation_date          DATE;
   n_segments                 NUMBER;
   SEGMENTS                   APPS.FND_FLEX_EXT.SEGMENTARRAY;
   l_combination_id           NUMBER;
   l_data_set                 NUMBER;
   l_return                   BOOLEAN;
   l_message                  VARCHAR2 (240);
BEGIN
   l_application_short_name := 'SQLGL';
   l_key_flex_code := 'GL#';

   SELECT   id_flex_num
     INTO   l_structure_num
     FROM   apps.fnd_id_flex_structures
    WHERE   ID_FLEX_CODE = 'GL#'
            AND ID_FLEX_STRUCTURE_CODE = :ACCOUNTING_FLEXFIELD;

   l_validation_date := SYSDATE;
   n_segments := 6;
   segments (1) := '00101';
   segments (2) := '28506';
   segments (3) := '00000';
   segments (4) := '09063';
   segments (5) := '00000';
   segments (6) := '00000';
   l_data_set := NULL;

   l_return :=
      FND_FLEX_EXT.GET_COMBINATION_ID (
         application_short_name   => l_application_short_name,
         key_flex_code            => l_key_flex_code,
         structure_number         => l_structure_num,
         validation_date          => l_validation_date,
         n_segments               => n_segments,
         segments                 => segments,
         combination_id           => l_combination_id,
         data_set                 => l_data_set
      );
   l_message := FND_FLEX_EXT.GET_MESSAGE;

   IF l_return
   THEN
      DBMS_OUTPUT.PUT_LINE ('l_Return = TRUE');
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID = ' || l_combination_id);
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Error: ' || l_message);
   END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.
This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).
Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
• ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

/* Formatted on 3/27/2017 4:31:51 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   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);
BEGIN
   l_segment1 := '00101';
   l_segment2 := '28506';
   l_segment3 := '00000';
   l_segment4 := '14302';
   l_segment5 := '00455';
   l_segment6 := '00000';
   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 = 'EPC_GL_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;

   IF l_ccid IS NOT NULL
   THEN
      ------------------------The CCID is Available----------------------
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID= ' || l_ccid);
   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;

      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;

            DBMS_OUTPUT.PUT_LINE ('NEW COMBINATION_ID = ' || l_ccid);
         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;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLCODE || ' ' || SQLERRM);
END;