Declare
v_user_cp1 varchar2(200);
v_cp1 varchar2(200);
v_user_cp varchar2(200);
v_cp varchar2(200);
v_cp_method varchar2(200);
Cursor Cur_name is
select distinct cc.concurrent_program_name,tl.user_concurrent_program_name
from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
where tl.application_id = cc.application_id
and tl.concurrent_program_id = cc.concurrent_program_id
and cc.execution_method_code in ('P','I')
and cc.concurrent_program_name not like 'XX%'
And tl.LANGUAGE ='US';
cursor cur1 (cpname varchar2) is
select distinct tl.user_concurrent_program_name, cc.concurrent_program_name,
decode(cc.execution_method_code,
'P', 'Oracle Reports',
'I' ,'PL/SQL Stored Procedure'
) Execution_Method
into v_user_cp,v_cp ,v_cp_method
from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
where tl.application_id = cc.application_id
and tl.concurrent_program_id = cc.concurrent_program_id
and cc.execution_method_code in ('P','I')
And cc.concurrent_program_name like 'XX%'
And tl.LANGUAGE ='US'
And cc.concurrent_program_name <> cpname
And REGEXP_LIKE (cc.concurrent_program_name, cpname);
begin
Open Cur_name;
LOOP
Fetch Cur_name into v_user_cp1,v_cp1;
exit when Cur_name%notfound;
open cur1(v_user_cp1);
loop
fetch cur1 into v_user_cp,v_cp,v_cp_method;
exit when cur1%notfound;
dbms_output.put_line(v_cp||' | '||v_user_cp||' | '||v_user_cp1||' | '||v_cp1||' | '||v_cp_method);
end loop;
close cur1;
END LOOP;
Close Cur_name;
end;
It will tack to time to execute query 15 to 20 mints.
v_user_cp1 varchar2(200);
v_cp1 varchar2(200);
v_user_cp varchar2(200);
v_cp varchar2(200);
v_cp_method varchar2(200);
Cursor Cur_name is
select distinct cc.concurrent_program_name,tl.user_concurrent_program_name
from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
where tl.application_id = cc.application_id
and tl.concurrent_program_id = cc.concurrent_program_id
and cc.execution_method_code in ('P','I')
and cc.concurrent_program_name not like 'XX%'
And tl.LANGUAGE ='US';
cursor cur1 (cpname varchar2) is
select distinct tl.user_concurrent_program_name, cc.concurrent_program_name,
decode(cc.execution_method_code,
'P', 'Oracle Reports',
'I' ,'PL/SQL Stored Procedure'
) Execution_Method
into v_user_cp,v_cp ,v_cp_method
from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
where tl.application_id = cc.application_id
and tl.concurrent_program_id = cc.concurrent_program_id
and cc.execution_method_code in ('P','I')
And cc.concurrent_program_name like 'XX%'
And tl.LANGUAGE ='US'
And cc.concurrent_program_name <> cpname
And REGEXP_LIKE (cc.concurrent_program_name, cpname);
begin
Open Cur_name;
LOOP
Fetch Cur_name into v_user_cp1,v_cp1;
exit when Cur_name%notfound;
open cur1(v_user_cp1);
loop
fetch cur1 into v_user_cp,v_cp,v_cp_method;
exit when cur1%notfound;
dbms_output.put_line(v_cp||' | '||v_user_cp||' | '||v_user_cp1||' | '||v_cp1||' | '||v_cp_method);
end loop;
close cur1;
END LOOP;
Close Cur_name;
end;
It will tack to time to execute query 15 to 20 mints.
thanks...very informative
ReplyDelete