Retrieving Code Combination ID for any key flex fields

It may required to validate the segments of key flex fields and retrieve the code combination id so that such ids may be used in APIs. Typical example includes validating soft coding key flex fields(in update_emp_asg), job,position, cost allocation key flex etc in element links API.

During conversion/interfacing type of projects we may be required to validate the kff descriptions and get back the code combination id. In such cases the following procedure may be useful. It internally uses hr_kflex_utility.ins_or_sel_keyflex_comb API

---
--- Name : validate_keyflex
--- Description : takes the kff desciptions concatenated by ~
--- and returns the code combination id. Other
--- mandatory parameters to be supplied are
--- flex code and flex num
--- Note : p_error_msg_io is just varchar2 type to take back
--- generated error messages.
---

PROCEDURE validate_keyflex(
p_concat_des_i IN VARCHAR2
,p_flex_code_i IN fnd_id_flex_segments.id_flex_code%TYPE
,p_flex_num_i IN fnd_id_flex_segments.id_flex_num%TYPE
,p_error_msg_io IN OUT VARCHAR2
,p_cost_allocation_keyflex_id_o OUT pay_element_links_f.cost_allocation_keyflex_id%TYPE
,p_concat_segments_o OUT VARCHAR2
)
IS
l_id_concat_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE;
l_segment_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
l_application_shortname fnd_application.application_short_name%TYPE;

TYPE flex_des_table_type IS TABLE OF fnd_flex_values_vl.description%TYPE
INDEX BY PLS_INTEGER;

TYPE flex_value_table_type IS TABLE OF fnd_flex_values.flex_value%TYPE
INDEX BY PLS_INTEGER;

TYPE flex_value_set_table_type IS TABLE OF fnd_flex_values_vl.flex_value_set_id%TYPE
INDEX BY PLS_INTEGER;

CURSOR value_set_cur IS
SELECT flex_value_set_id
FROM fnd_id_flex_segments
WHERE id_flex_code=p_flex_code_i
AND id_flex_num = p_flex_num_i
ORDER BY segment_num;

flex_des_table flex_des_table_type;
flex_value_table flex_value_table_type;
flex_value_set_table flex_value_set_table_type;


l_index_des PLS_INTEGER;
l_index_val PLS_INTEGER;

l_count NUMBER := 1;
l_loc NUMBER := 1;
l_position NUMBER;
l_des VARCHAR2(40);

BEGIN
----------- Get the segment delimiter for the structure ----------------
SELECT concatenated_segment_delimiter
INTO l_segment_delimiter
FROM fnd_id_flex_structures
WHERE id_flex_code = p_flex_code_i
AND id_flex_num = p_flex_num_i;
--DBMS_OUTPUT.PUT_LINE('Find Delimiter: Passed');
------------ Get all value set id for the flex structure used ----------
OPEN value_set_cur;
FETCH value_set_cur BULK COLLECT INTO flex_value_set_table;
CLOSE value_set_cur;

--DBMS_OUTPUT.PUT_LINE('Get value sets: Passed');
------------ Populate Description table ----------------------------------
l_index_des := 1;
LOOP
l_position := instr(p_concat_des_i, '~', 1, l_loc);
EXIT WHEN l_position = 0;
l_des := substr(p_concat_des_i, l_count, l_position-l_count);
flex_des_table(l_index_des) := l_des;
l_count := l_position+1;
l_loc := l_loc +1;
l_index_des := l_index_des + 1;
END LOOP;
l_des := substr(p_concat_des_i, l_count, length(p_concat_des_i)-l_count+1);
flex_des_table(l_index_des) := l_des;
DBMS_OUTPUT.PUT_LINE('Get flex des table: Passed');

---------- Populate value table for corresponding description ---------
---------- and create value concat segments ----------------------------
l_index_des := NULL;
l_index_val := flex_des_table.FIRST;
l_index_des := flex_value_set_table.FIRST;

WHILE l_index_val IS NOT NULL AND l_index_des IS NOT NULL
LOOP
SELECT flex_value
INTO flex_value_table(l_index_val)
FROM fnd_flex_values_vl
WHERE flex_value_set_id = flex_value_set_table(l_index_des)
AND description = flex_des_table(l_index_val);

---Create concat segments with delimiter at the begining
l_id_concat_segments := l_id_concat_segments || l_segment_delimiter||flex_value_table(l_index_val);
l_index_val := flex_des_table.NEXT(l_index_val);
l_index_des := flex_value_set_table.NEXT(l_index_des);
END LOOP;

-- Remove the preceeding delimiter
l_id_concat_segments := substr(l_id_concat_segments, 2, length(l_id_concat_segments));

--DBMS_OUTPUT.PUT_LINE('Get concatenated flex ids: Passed');
----------- Get the Application Short Name ---------------------------
SELECT application_short_name
INTO l_application_shortname
FROM fnd_application
WHERE application_id = (
SELECT application_id
FROM fnd_id_flex_structures
WHERE id_flex_code = p_flex_code_i
AND id_flex_num = p_flex_num_i
);

--DBMS_OUTPUT.PUT_LINE('Get Application Short Name: Passed');
--DBMS_OUTPUT.PUT_LINE('Des Segments: '|| p_concat_des_i );
--DBMS_OUTPUT.PUT_LINE('ID Segments: '|| l_id_concat_segments);

----------- Call Kflex utility to get the code combination id ---------
apps.hr_kflex_utility.ins_or_sel_keyflex_comb(
p_appl_short_name => l_application_shortname
,p_flex_code => p_flex_code_i
,p_flex_num => p_flex_num_i
,p_concat_segments_in => l_id_concat_segments
,p_ccid => p_cost_allocation_keyflex_id_o
,p_concat_segments_out => p_concat_segments_o
);

DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Passed');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
p_error_msg_io := p_error_msg_io ||'KeyFlex:Ambiguous Values~';
DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed');
p_cost_allocation_keyflex_id_o := NULL;
p_concat_segments_o := NULL;
WHEN NO_DATA_FOUND THEN
p_error_msg_io := p_error_msg_io ||'KeyFlex:Invalid parameter passed~';
DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed');
p_cost_allocation_keyflex_id_o := NULL;
p_concat_segments_o := NULL;
WHEN others THEN
p_error_msg_io := p_error_msg_io ||' '|| SQLERRM|| '~';
DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed');
p_cost_allocation_keyflex_id_o := NULL;
p_concat_segments_o := NULL;
END validate_keyflex;

The above code simply assumes that the validation sets used by the kffs are of either independent/dependent types. And the code is open for scrutiny.