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