Introduction
This Post illustrate the steps required to Create Bulk GL Code Combinations Using API in Oracle EBS R12.
Script to Create Bulk GL Code Combinations Using API
DECLARE
CURSOR c1
IS
SELECT a.ROWID row_id, a.*
FROM temp_gl_code a
WHERE process_flag = ‘N’;
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);
v_error_flag BOOLEAN;
v_error_msg VARCHAR2 (3000) := NULL;
v_count1 NUMBER;
v_count2 NUMBER;
v_row_count NUMBER := 0;
v_req_message VARCHAR2 (3000);
v_req_id NUMBER;
v_req_status BOOLEAN;
v_rphase VARCHAR2 (20);
v_rstatus VARCHAR2 (20);
v_dphase VARCHAR2 (20);
v_dstatus VARCHAR2 (20);
— ———————-
–START OF VALIDATION
— ———————-
BEGIN
FOR i IN c1
LOOP
v_error_flag := FALSE;
v_error_msg := NULL;
l_segment1 := i.segment1;
l_segment2 := i.segment2;
l_segment3 := i.segment3;
l_segment4 := i.segment4;
l_segment5 := i.segment5;
l_segment6 := i.segment6;
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 = ‘COVAD_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;
— ——————————————————————————-
— UPDATING THE PROCESS FLAG FOR ERRORS IN temp_gl_code TABLE
— ——————————————————————————-
IF l_ccid IS NOT NULL
THEN
————————The CCID is Available———————-
–DBMS_OUTPUT.PUT_LINE(‘COMBINATION_ID= ‘ ||l_ccid);
v_error_msg := v_error_msg || ‘Combination already Exists ‘ || ‘; ‘;
UPDATE temp_gl_code
SET process_flag = ‘E’,
errror_message = ‘Validation Error:’ || v_error_msg,
last_update_date = SYSDATE
WHERE ROWID = i.row_id AND process_flag = ‘N’;
COMMIT;
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;
BEGIN
UPDATE temp_gl_code
SET process_flag = ‘E’,
errror_message = fnd_flex_keyval.error_message,
last_update_date = SYSDATE
WHERE ROWID = i.row_id AND process_flag = ‘N’;
COMMIT;
END;
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;
UPDATE temp_gl_code
SET process_flag = ‘S’,
errror_message = ‘Import Success=’ || l_ccid,
last_update_date = SYSDATE
WHERE ROWID = i.row_id AND process_flag = ‘E’;
COMMIT;
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;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ‘ ‘ || SQLERRM);
END;
What we expect in the script.
This script helps us to comprehend how to Create Bulk GL Code Combinations Using API.
Summary
This Post described the script Create Bulk GL Code Combinations Using API in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
