Check Completed Long Running Jobs In Oracle ApplicationsCheck Completed Long Running Jobs In Oracle Applications
You may use the script as below to detect the completed long running jobs in Oracle Applications: -
SQL> 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;
Leave a Comment- #2
zenux
November 30, 2008 8:20 pmAdded request log file and output location
select a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.oracle_process_id “SPID”
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(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
,round((actual_completion_date – requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 = ‘C’
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 a.requested_by = fu.user_id
and a.responsibility_id = frt.responsibility_id
and a.request_id = ’10830321′
order by a.actual_start_date desc
November 5, 2008 9:20 pm
Well, you might need to have the responsibility information that runs in your Oracle Applications
. It was tuned based on the original script
select a.request_id “Request Id”
,frt.responsibility_name
,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name “program”
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(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
,round((actual_completion_date – requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.status_code = ‘E’
and actual_start_date > sysdate – 30
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 > 0
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’
)
and a.requested_by = fu.user_id
and a.responsibility_id = frt.responsibility_id
order by a.request_id