Long Running Program and SQL Query

LONG RUNNING PROGRAM

SELECT a.request_id

,a.oracle_process_id "SPID"

,frt.responsibility_name

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

,a.description

,a.ARGUMENT_TEXT

,b.node_name

,b.db_instance

,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 = 'R'

AND a.status_code = 'R'

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 a.requested_by = fu.user_id

AND a.responsibility_id = frt.responsibility_id

ORDER BY a.actual_start_date DESC

GET THE SESSION ID FROM PREVIOUS QUERY

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time, ROUND((SYSDATE - b.logon_time) * 1440, 2)   "Runtime (in Minutes)"  

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND a.spid = 'SESSION ID PUT'

ORDER BY a.spid, c.piece

PROGRAM CONSUMING TIME

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date - rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"   ,  

       ROUND((sysdate - rq.actual_start_date) * 1440, 2)   "Runing (in Minutes)" ,

       ROUND((sysdate - rq.actual_start_date) * 1440/60, 2)   "Runing (in Hours)"

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

--   AND rq.actual_start_date IS NOT NULL

--   AND rq.actual_completion_date IS NOT NULL

  and  request_id=202319707