Identify Long running Concurrent programs

-- Identify Long running Concurrent programs with child details and run time Parameters

select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
       fcr.request_id    "Request ID",
       fcr.requested_by "User",
       substr(DECODE (FCR.DESCRIPTION,  NULL,
       FCPTL.USER_CONCURRENT_PROGRAM_NAME,
       FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
       (fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed Time",
       oracle_process_id "Trace File ID" ,
       fcr.phase_code "Phase",
       fcr.status_code "Status",
       to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS')   "Submitted",
       (fcr.actual_start_date - fcr.request_date)*1440 "Delay",
       to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS')  "Start Time",
       to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
       fcr.argument_text "Parameters"
  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
               fcr1.request_id
          from fnd_concurrent_requests fcr1
         where 1=1
         start with fcr1.request_id = :RequestID
       connect by prior fcr1.request_id = fcr1.parent_request_id) x,
       fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcptl
 where fcr.request_id = x.request_id
   and fcr.concurrent_program_id = fcp.concurrent_program_id
   and fcr.program_application_id = fcp.application_id
   and fcp.application_id = fcptl.application_id
   and fcp.concurrent_program_id = fcptl.concurrent_program_id
   and fcptl.language = 'US'
 order by 1


 -- To find current running SQL text for a request with performance/hanging issues - refer to Doc ID 186472.1
-- prints all requests currently running for a request set or can be used for any single running request
-- note that if no SQL is returned, then the process may be working in RAM Memory - examples are MRP or ASCP Memory Based Planner process
-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated

select
request_id,
to_char(sid) sid
, to_char(serial#) serial#
, vq.sql_id SQLID
, vs.osuser
, vs.machine
, sql_text
, vs.process
from
apps.fnd_concurrent_requests fcr,
v$session vs,
v$sqltext vq
where
vs.process = fcr.os_process_id
and vs.sql_address = vq.address
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
   and request_id in
  (select fcr.request_id
  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
          fcr1.request_id
       from fnd_concurrent_requests fcr1
         where 1=1
         start with fcr1.request_id = &request_id
          connect by prior fcr1.request_id = fcr1.parent_request_id) x,
          fnd_concurrent_requests fcr,
          fnd_concurrent_programs fcp,
          fnd_concurrent_programs_tl fcptl
          where fcr.request_id = x.request_id
          and fcr.concurrent_program_id = fcp.concurrent_program_id
          and fcr.program_application_id = fcp.application_id
          and fcp.application_id = fcptl.application_id
          and fcp.concurrent_program_id = fcptl.concurrent_program_id
          and fcptl.language = 'US')
order by request_id, serial#, piece;