Oracle Apps Knowledge Sharing

Query to Find Scheduled Concurrent Progams

select /*+ FIRST_ROWS */
  req.request_id
, req.has_sub_request
, req.is_sub_request
, ROUND((req.actual_completion_date - req.actual_start_date)*100000,0) tot_time_sec
, prog.concurrent_program_name
, req.phase_code
, req.status_code
, req.hold_flag
, req.requested_start_date
, req.actual_start_date
, req.actual_completion_date
, requestor.user_name
, requestor.description long_name
, resp.responsibility_name
, req.parent_request_id
, req.priority_request_id
, req.oracle_process_id ORA_PROCESS_ID
, req.os_process_id FND_PROCESS_ID
, req.oracle_session_id
, conque.concurrent_queue_name
, DECODE(NVL(req.resubmit_interval,0)
                            , 0, 'None'
                            , 'RUN EVERY ' || req.resubmit_interval||'  '
                               || req.resubmit_interval_unit_code ||
                              ' FROM THE '  || req.resubmit_interval_type_code ||
                                ' OF PRIOR RUN' ||
                               DECODE(
                                NVL(req.resubmit_end_date,TRUNC(SYSDATE-1000))
                              , TRUNC(SYSDATE-1000), NULL
                              , ' UNTIL ' || req.resubmit_end_date)
                              ) RESUB_SCHEDULE
, sched.release_class_name saved_sched_name
, sched.date1 saved_sched_date1
, sched.date2 saved_sched_date2
, sched.class_type saved_sched_type
, sched.class_info saved_sched_info
, req.completion_text
, req.argument_text
, req.logfile_name
, req.outfile_name
, req.output_file_type
from applsys.fnd_concurrent_requests req
, applsys.fnd_user requestor
, applsys.fnd_concurrent_programs prog
, applsys.fnd_concurrent_programs_tl prog_desc
, applsys.fnd_concurrent_processes conproc
, applsys.fnd_concurrent_queues conque
, applsys.fnd_conc_release_classes sched
, applsys.fnd_responsibility_tl resp
where req.requested_by = requestor.user_id
and req.program_application_id = prog.application_id(+)
and req.concurrent_program_id = prog.concurrent_program_id(+)
and prog.application_id = prog_desc.application_id(+)
and prog.concurrent_program_id = prog_desc.concurrent_program_id(+)
and prog_desc.language(+) = 'US'
and req.controlling_manager = conproc.concurrent_process_id(+)
and conproc.queue_application_id = conque.application_id(+)
and conproc.concurrent_queue_id = conque.concurrent_queue_id(+)
and req.release_class_app_id = sched.application_id(+)
and req.release_class_id = sched.release_class_id(+)
and req.responsibility_application_id = resp.application_id(+)
and req.responsibility_id = resp.responsibility_id(+)
and resp.language(+) = 'US'
and req.request_id = '&request_id'
order by req.requested_start_date desc, req.request_id desc