Long Running Programs – SQL Query to Find Current Execution Stage of a Concurrent Request

SELECT a.request_id,
       d.sid,
       d.serial#,
       d.osuser,
       d.process,
       c.SPID,
       d.inst_id
  FROM apps.fnd_concurrent_requests   a,
       apps.fnd_concurrent_processes  b,
       gv$process                     c,
       gv$session                     d
 WHERE     a.controlling_manager = b.concurrent_process_id
       AND c.pid = b.oracle_process_id
       AND b.session_id = d.audsid
       AND a.request_id = :request_id
       --AND a.phase_code = 'R'
;

SELECT a.request_id, d.sid, d.serial#, d.osuser, d.process, c.SPID, d.inst_id FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id = d.audsid AND a.request_id = :request_id --AND a.phase_code = 'R' ;

  SELECT S.USERNAME,
         s.sid,
         s.osuser,
         t.sql_id,
         sql_text
    FROM gv$sqltext_with_newlines t, gV$SESSION s
   WHERE     t.address = s.sql_address
         AND t.hash_value = s.sql_hash_value
         AND s.status = 'ACTIVE'
         AND s.username <> 'SYSTEM'
         AND s.sid = :Session_ID
ORDER BY s.sid, t.piece;

SELECT S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text FROM gv$sqltext_with_newlines t, gV$SESSION s WHERE t.address = s.sql_address AND t.hash_value = s.sql_hash_value AND s.status = 'ACTIVE' AND s.username <> 'SYSTEM' AND s.sid = :Session_ID ORDER BY s.sid, t.piece;