CCID Creation Code Thru API

Call get_code_combination_id_f function by passing required segment values and ledger name and hold error message returned in variable. This function will check for existing CCID and return the value. If CCID does'nt exists, it calls create_a_combination function and generate new CCID using API

FUNCTION create_a_combination
(
p_segment1 IN VARCHAR2,
p_segment2 IN VARCHAR2,
p_segment3 IN VARCHAR2,
p_segment4 IN VARCHAR2,
p_segment5 IN VARCHAR2,
p_segment6 IN VARCHAR2,
p_segment7 IN VARCHAR2,
p_segment8 IN VARCHAR2,
p_flex_delimiter IN VARCHAR2,
p_chart_of_accounts_id IN NUMBER,
p_ccid OUT NUMBER
)
RETURN VARCHAR2
IS
ccid NUMBER := 0;
allsegments VARCHAR2 (256) := NULL;
flexerror VARCHAR2 (2560) := NULL;
BEGIN
allsegments :=
p_segment1
|| p_flex_delimiter
|| p_segment2
|| p_flex_delimiter
|| p_segment3
|| p_flex_delimiter
|| p_segment4
|| p_flex_delimiter
|| p_segment5
|| p_flex_delimiter
|| p_segment6
|| p_flex_delimiter
|| p_segment7
|| p_flex_delimiter
|| p_segment8;

DBMS_OUTPUT.put_line ('Inside create_a_combination-' || allsegments);

ccid :=
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
validation_date => TO_CHAR (SYSDATE, fnd_flex_ext.DATE_FORMAT), concatenated_segments => allsegments);

p_ccid := ccid;

IF ccid <= 0
THEN
flexerror := fnd_message.get;
END IF;

RETURN flexerror;

EXCEPTION
WHEN OTHERS THEN
p_ccid := 0;
flexerror := SQLERRM || ' ' || fnd_message.get;
RETURN flexerror;

END create_a_combination;

-------------------------------------------------------------------------------------

FUNCTION get_code_combination_id_f
(
p_segment1 IN gl_code_combinations.segment1%TYPE,
p_segment2 IN gl_code_combinations.segment2%TYPE,
p_segment3 IN gl_code_combinations.segment3%TYPE,
p_segment4 IN gl_code_combinations.segment4%TYPE,
p_segment5 IN gl_code_combinations.segment5%TYPE,
p_segment6 IN gl_code_combinations.segment6%TYPE,
p_segment7 IN gl_code_combinations.segment7%TYPE,
p_segment8 IN gl_code_combinations.segment8%TYPE,
p_set_of_books_name IN gl_ledgers.NAME%TYPE,
p_error_msg OUT VARCHAR2
)
RETURN gl_code_combinations.code_combination_id%TYPE
IS
CURSOR c_ccid_validate (
p_segment1 IN gl_code_combinations.segment1%TYPE,
p_segment2 IN gl_code_combinations.segment2%TYPE,
p_segment3 IN gl_code_combinations.segment3%TYPE,
p_segment4 IN gl_code_combinations.segment4%TYPE,
p_segment5 IN gl_code_combinations.segment5%TYPE,
p_segment6 IN gl_code_combinations.segment6%TYPE,
p_segment7 IN gl_code_combinations.segment7%TYPE,
p_segment8 IN gl_code_combinations.segment8%TYPE
)
IS
SELECT code_combination_id
FROM gl_code_combinations
WHERE segment1 = p_segment1
AND segment2 = p_segment2
AND segment3 = p_segment3
AND segment4 = p_segment4
AND segment5 = p_segment5
AND segment6 = p_segment6
AND segment7 = p_segment7
AND segment8 = p_segment8;

CURSOR c_flex_details
IS
SELECT id_flex_num
FROM fnd_id_flex_segments
WHERE id_flex_num =
(SELECT chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = (SELECT ledger_id
FROM gl_ledgers
WHERE NAME = p_set_of_books_name))
AND application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL')
AND enabled_flag = 'Y'
AND ROWNUM = 1;

l_flex_details c_flex_details%ROWTYPE;
l_flexerror VARCHAR2 (2560) := NULL;
l_acc_segment1 gl_code_combinations.segment1%TYPE;
l_acc_segment2 gl_code_combinations.segment2%TYPE;
l_acc_segment3 gl_code_combinations.segment3%TYPE;
l_acc_segment4 gl_code_combinations.segment4%TYPE;
l_acc_segment5 gl_code_combinations.segment5%TYPE;
l_acc_segment6 gl_code_combinations.segment6%TYPE;
l_acc_segment7 gl_code_combinations.segment7%TYPE;
l_acc_segment8 gl_code_combinations.segment8%TYPE;

l_ccid gl_code_combinations.code_combination_id%TYPE := NULL;
l_user_name fnd_user.user_name%TYPE := fnd_profile.VALUE ('USERNAME');
x_delimiter VARCHAR2 (10);

BEGIN
FOR c_ccid_validate_rec IN c_ccid_validate
(p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8
)

LOOP
l_ccid := c_ccid_validate_rec.code_combination_id;
RETURN (l_ccid);
END LOOP;

IF l_ccid IS NULL
THEN
fnd_file.put_line (fnd_file.LOG, 'CCID Not Found in gl_code_combinations');
fnd_file.put_line (fnd_file.LOG, 'CCID Not Found..Creating New CCID...');

-------- Cursor for getting the SOB details---------------------------

OPEN c_flex_details;
FETCH c_flex_details INTO l_flex_details;
CLOSE c_flex_details;

fnd_file.put_line (fnd_file.LOG, 'chart of account :-' || l_flex_details.id_flex_num);

x_delimiter :=
fnd_flex_ext.get_delimiter
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_flex_details.id_flex_num
);

--Create CCID

l_flexerror :=
create_a_combination
(p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8,
x_delimiter,
l_flex_details.id_flex_num,
l_ccid
);

fnd_file.put_line (fnd_file.LOG, 'Newly Created CCID-' || l_ccid);

IF l_ccid <= 0
THEN
fnd_file.put_line (fnd_file.LOG, 'l_flexerror' || l_flexerror);
p_error_msg := l_flexerror;
RETURN (0);
ELSE
p_error_msg := NULL;
RETURN (l_ccid);
END IF;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_flexerror := SQLERRM;
p_error_msg := l_flexerror;
RETURN 0;

WHEN OTHERS
THEN
l_flexerror := SQLERRM;
p_error_msg := l_flexerror;
RETURN 0;
END get_code_combination_id_f;