Query to list concurrent program details with its parameter

1. query to list all the responsibilities attached to a user

select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')


2.to find from which responsibility a concurrent program can be run

select distinct
  a.user_concurrent_program_name,
  a.description,
  request_group_name,
  e.responsibility_name
from
  fnd_concurrent_programs_tl a,
  fnd_request_groups b,
  fnd_request_group_units c,
  fnd_responsibility d,
  fnd_responsibility_tl e
where
  a.concurrent_program_id = c.request_unit_id   
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id

3.provide concurrent program name to the following query.

it lists all the request sets which are created with the concurrent program given.

select distinct user_request_set_name
  from fnd_request_sets_tl
 where request_set_id in
          (select request_set_id
             from fnd_request_set_programs
            where concurrent_program_id =
                     (select concurrent_program_id
                        from fnd_concurrent_programs_tl
                       where upper(user_concurrent_program_name) = upper( '&enter_prog_name')));

4. provide the request set name to the following query.
it lists all concurrent programs of this request set.

select user_concurrent_program_name
  from fnd_concurrent_programs_tl
 where concurrent_program_id in
          (select concurrent_program_id
             from fnd_request_set_programs
            where request_set_id =
                     (select request_set_id
                        from fnd_request_sets_tl
                       where upper(user_request_set_name) = upper('&request_set_name')));

5. query to list concurrent program details with its parameter, values set and default value/type:

  select fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.application_short_name
      , fav.application_name
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.default_value  
 from   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 where  fcp.concurrent_program_id = fcpl.concurrent_program_id
 and    fcpl.user_concurrent_program_name = :conc_prg_name
 and    fcpl.language = 'us'
 and    fav.application_id=fcp.application_id
 and    fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name
 and    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 and    flv.lookup_type(+) = 'flex_default_type'
 and    flv.lookup_code(+) = fdfcuv.default_type
 and    flv.language(+) = userenv ('lang');


 6. query to find out concurrent program details and its parameters

select fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.application_short_name
     , fav.application_name
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
from   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
where  fcp.concurrent_program_id = fcpl.concurrent_program_id
and    fcpl.user_concurrent_program_name = :conc_prg_name
and    fav.application_id=fcp.application_id
and    fcpl.language = 'US'
and    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


7 :for checking the locks in concurrent jobs

select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;

8 :for checking the concurrent programs running currently with details of processed time-- and start date


 select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where  a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
  
9 :for checking last run of a concurrent program along with processed time

-- useful to find the details of concurrent programs which run daily and comparison purpose

select distinct c.user_concurrent_program_name,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
            a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
  to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
            (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.fnd_concurrent_programs_tl c,
            apps.fnd_user d
where       a.concurrent_program_id= b.concurrent_program_id and
            b.concurrent_program_id=c.concurrent_program_id and
            a.requested_by =d.user_id and
--          trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' --  and argument_text like  '%, , , , ,%';
--          and status_code!='c'

10 :for checking the last run of concurrent program.

- use below query to check all the concurrent request running which may refer given package

-- this is very useful check before compiling any package on given instance.

-- the query can be modified as per requirement.

-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

11 :the following query will display the time taken to execute the concurrent programs

--for a particular user with the latest concurrent programs sorted in least time taken

-- to complete the request.


 select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      and pt.language = userenv('lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;


12 : by using the below query we can get sid,serial#,spid of the concurrent request..

    select a.request_id, d.sid, d.serial# , c.spid
    from apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    where a.controlling_manager = b.concurrent_process_id
    and c.pid = b.oracle_process_id
    and b.session_id=d.audsid
    and a.request_id = &request_id
    and a.phase_code = 'r';


13 : by using below concurrent manager and program rules...

--gives detail of the concurrent_queue_name and user_concurrent_program_name

select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
  

14 : gives details of running concurrent jobs


select distinct c.user_concurrent_program_name,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.fnd_concurrent_programs_tl c,
    apps.fnd_user d
where   a.concurrent_program_id=b.concurrent_program_id and
    b.concurrent_program_id=c.concurrent_program_id and
    a.requested_by=d.user_id and
    status_code='r' order by process_time desc;
  

15 : gives detail of concurrent job completed and pending


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

16 :gives detail of running and completed concurrent jobs with start date and end date

-- latest one at the top

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      and pt.language = userenv('lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;

17 : wait events details related with concurrent programs


select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";

18 : to find the pid of the concurrent job and kill it.


select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;

19 :to find the database sid of the concurrent job

-- we need our concurrent request id as an input.

-- c.spid= is the operating system process id

-- d.sid= is the oracle process id


sql> column process heading "fndlibr pid"

select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';