Here is a usage of standard API “fnd_flex_ext.get_combination_id” to find code_combination_id for given segment values. Returns TRUE if combination is valid, or FALSE and sets error message on server using FND_MESSAGE if invalid.
Note: This API return Code Combination ID but it will NOT create a Code Combination ID if it is not existing.
/*********************************************************
*PURPOSE: Sample API To Find Code Combination ID *
*AUTHOR: Shailender Thallam *
**********************************************************/
--
DECLARE
---------------------------
--Declaring Local Variables
---------------------------
l_1_company gl_code_combinations.segment1%TYPE;
l_2_account gl_code_combinations.segment2%TYPE;
l_3_cost_center gl_code_combinations.segment3%TYPE;
l_4_site gl_code_combinations.segment4%TYPE;
l_5_program gl_code_combinations.segment5%TYPE;
l_6_activity gl_code_combinations.segment6%TYPE;
l_7_intercompany gl_code_combinations.segment7%TYPE;
l_8_future gl_code_combinations.segment8%TYPE;
--
l_segments apps.fnd_flex_ext.segmentarray;
l_structure_num fnd_id_flex_structures.id_flex_num%TYPE;
l_ccid gl_code_combinations.code_combination_id%TYPE;
l_chart_of_accounts_id gl_code_combinations.chart_of_accounts_id%TYPE;
l_application_short_name fnd_application.application_short_name%TYPE;
l_key_flex_code fnd_id_flex_structures.id_flex_code%TYPE;
l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
l_return BOOLEAN;
l_err_message VARCHAR2 (2000);
BEGIN
-------------------------------------------------------------
--Setting Values for Application Short Name and Key Flex Code
-------------------------------------------------------------
l_application_short_name := 'SQLGL';
l_key_flex_code := 'GL#';
-----------------------------
--Finding Char of Accounts ID
-----------------------------
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books
WHERE 1 = 1 AND NAME = 'XX Cust Company Name';
--------------------------------
--Finding Accounting Flex Number
--------------------------------
SELECT id_flex_num
INTO l_structure_num
FROM apps.fnd_id_flex_structures
WHERE id_flex_code = l_key_flex_code
AND id_flex_structure_code = 'XXCUST_ACCOUNTING_FLEXFIELD';
-------------------
--Finding Delimiter
-------------------
l_delimiter :=
fnd_flex_ext.get_delimiter (l_application_short_name,
l_key_flex_code,
l_chart_of_accounts_id
);
-------------------------------------------------------------------------
--Setting Segment Values (Here in this context there are only 8 segments)
-------------------------------------------------------------------------
l_1_company := '9000';
l_2_account := '700701';
l_3_cost_center := '0534';
l_4_site := '0000';
l_5_program := '65301';
l_6_activity := '000000';
l_7_intercompany := '0000';
l_8_future := '0000';
-----------------------------------------------
--Assigning segments to a segmentarray variable
-----------------------------------------------
l_segments (1) := l_1_company;
l_segments (2) := l_2_account;
l_segments (3) := l_3_cost_center;
l_segments (4) := l_4_site;
l_segments (5) := l_5_program;
l_segments (6) := l_6_activity;
l_segments (7) := l_7_intercompany;
l_segments (8) := l_8_future;
---------------
--Fetching CCID
---------------
BEGIN
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 => SYSDATE,
n_segments => 8,
segments => l_segments,
combination_id => l_ccid
);
--
l_err_message := fnd_flex_ext.GET_MESSAGE;
--
IF l_return
THEN
DBMS_OUTPUT.put_line ('Generated CCID: ' || l_ccid);
ELSE
DBMS_OUTPUT.put_line ('Failed to Fetch CCID: ' || l_err_message);
END IF;
--
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception while fetching CCID: ' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception When Others: ' || SQLERRM);
--
--
END;
/