Monday, 21 March 2016

Query for to get Standard Customization objects list in Oracle apps R12

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.

1 comment: