HOW TO fetch combination_id for given set of key flexfield segment values

API to fetch combination_id for given set of key flexfield segment values in Oracle Apps R12 

 

API: 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 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.

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