Query to find current running SQL for a given concurrent request id

Query to find current running SQL for a given concurrent request id

SELECT FCR.REQUEST_ID,
       D.SID,
       D.SERIAL#,
       D.OSUSER,
       D.PROCESS,
       C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS   FCR,
       APPS.FND_CONCURRENT_PROCESSES  FCP,
       V$PROCESS                      C, 
       V$SESSION                      D,
       V$SQL                          E
 WHERE     FCR.CONTROLLING_MANAGER = FCP.CONCURRENT_PROCESS_ID
       AND C.PID = FCP.ORACLE_PROCESS_ID
       AND FCP.SESSION_ID = D.AUDSID
       AND D.SQL_ADDRESS = E.ADDRESS
       AND FCR.REQUEST_ID = :P_REQUEST_ID;

(In multi node -- use GV$)

Long Running Concurrent Requests

  SELECT ROUND (
               (NVL (ACTUAL_COMPLETION_DATE, SYSDATE) - actual_start_date)
             * 24
             * 60,
             2)
             run_time_mins,
         a.*
    FROM apps.fnd_amp_requests_v a
   WHERE     1 = 1
         AND actual_start_date > TRUNC (SYSDATE) - 1
         AND phase_code = 'R'
         AND ROUND (
                   (NVL (ACTUAL_COMPLETION_DATE, SYSDATE) - actual_start_date)
                 * 24
                 * 60,
                 2) >
             60

ORDER BY 1 DESC;