Long Running Oracle Applications Jobs

Long Running Oracle Applications Jobs

SELECT a.request_id "Req Id"

,a.phase_code,a.status_code

, actual_start_date

, actual_completion_date

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C') avg_mins

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

--and a.phase_code in ('I','P','R','T')

AND a.phase_code = 'C'

AND actual_start_date > sysdate - 20

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND ctl.LANGUAGE = 'US'

AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10

AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >

( SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C'

)

ORDER BY 5 DESC;

 

You are welcome to leave a comment....