Monthly Archives: August 2014

SQL Script to check if the Accounting Code Combination exists in Oracle

DECLARE
p_application_short_name VARCHAR2(10) := ‘SQLGL’;
p_key_flex_code VARCHAR2(10) := ‘GL#’;
p_structure_number NUMBER := 101;
p_date DATE := TRUNC(sysdate);

l_cc_id NUMBER;

cursor cur_code_combination is
select ’99-211750-620170-000000-999999′ code_combination from dual
union all
select ’99-211750-620170-000000-999998′ code_combination from dual;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

FND_GLOBAL.SET_NLS_CONTEXT (p_nls_language => ‘AMERICAN’
,p_nls_date_format => null
,p_nls_date_language => null
,p_nls_numeric_characters => ‘.,’
,p_nls_sort => null
,p_nls_territory => null
);

fnd_global.apps_initialize(0,20420,1);

for c_segment_cur in cur_code_combination
loop
l_cc_id := fnd_flex_ext.get_ccid(p_application_short_name
,p_key_flex_code
,p_structure_number
,to_char(p_date,’YYYY/MM/DD HH24:MI:SS’)
,c_segment_cur.code_combination);

IF l_cc_id = 0 –if the segments are not valid then it returns O.
THEN
dbms_output.put_line(c_segment_cur.code_combination || ‘ : Error-’ || fnd_flex_ext.GET_MESSAGE);
end if;

ROLLBACK;
end loop;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;