Details w.r.t. SID from Oracle backend

--> the below sql gives the details w.r.t. a SID
--> SQL currently running for the request
--> whether the SQL is stuck or NOT

SELECT vs.inst_id,
         fcr.request_id,
         vs.process,
         vs.sid,
         vsql.sql_text current_running_sql,
         vsw.event,
         vsw.wait_time,
         vsw.seconds_in_wait,
         vsw.state,
         vsw.p2text,
         vsw.p2,
         vsw.p3text,
         vsw.p3,
         vs.sid || ',' || vs.serial# sid_serial,
         p.spid,
         vs.process,
         vs.username || '/' || vs.osuser username,
         vs.status,
         TO_CHAR (vs.logon_time, 'DD-MON-YY HH24:MI:SS') logon_time
    FROM fnd_concurrent_requests fcr,
         gv$session vs,
         gv$sqltext_with_newlines vsql,
         gv$session_wait vsw,
         gv$process p
   WHERE     1 = 1
         AND vsql.address = vs.sql_address
         AND vsql.inst_id = vs.inst_id
         AND hash_value = sql_hash_value
         AND vs.sid = vsw.sid
         AND fcr.OS_PROCESS_ID = vs.process
         AND vs.paddr = p.addr
         AND vs.inst_id = p.inst_id
         AND vs.inst_id = vsw.inst_id
         AND fcr.request_id = <>
ORDER BY vsql.piece

Get executable details with program Name

1) The below SQL gives the details about a concurrent program

select CONCURRENT_PROGRAM_NAME,fe.execution_method_code,output_file_type,executable_name,execution_file_name,fcpt.*,fcp.*,fe.*
from FND_CONCURRENT_PROGRAMS_TL fcpt, fnd_concurrent_programs fcp, fnd_executables fe
where 1=1
and fcpt.user_concurrent_program_name = <>
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fcpt.language = 'US'  -- if the instance has multiple languages


execution_method_code --
  P :  Report (RDF/XML)
 H  :  Host script (unix script)
Q :   sql file
  I :  pl/sql pkg


2) The below SQL tells the details about request running (or already completed)  in an instance


SELECT
(select application_name from fnd_application_tl where application_id = b.application_id and language = 'US') "Application",
        DECODE (phase_code,'C', ROUND (((actual_completion_date - actual_start_date) * 1440) , 2),
    'R', ROUND (((SYSDATE - actual_start_date) * 1440) , 2) ,
    'I', 'SCHEDULED',              
    'Q', 'SCHEDULED',              
    'W', 'PAUSED',              
    'G', 'WARNING',              
    'E', ROUND (((actual_completion_date - actual_start_date) * 1440) , 2),
    'D', 'CANCELLED'                ) No_of_mins,
    a.concurrent_program_id,
    USER_CONCURRENT_PROGRAM_NAME,
    REQUEST_ID,USER_NAME, completion_text,
    SYSDATE, REQUESTED_START_DATE,
     REQUEST_DATE START_DATE, actual_start_date,
    logfile_name,
   decode(PHASE_CODE ,'C','COMPLETED','R','RUNNING','PENDING') PHASE_CODE,
    DECODE(STATUS_CODE,'C','COMPLETED',
                                                  'I','SCHEDULED',
                                                  'Q','SCHEDULED',                    
                                                   'W','PAUSED',
                                                    'G','WARNING',
                                                    'E','ERROR',
                                                    'R','NORMAL',
                                                     'D','CANCELLED',STATUS_CODE) STATUS_CODE,
    ACTUAL_COMPLETION_DATE END_DATE,
     argument_text,status_code,outfile_name,logfile_name
FROM apps.FND_CONCURRENT_REQUESTS A,
           apps.FND_CONCURRENT_PROGRAMS_TL B,
             apps.FND_USER C
WHERE 1=1
and REQUESTED_BY = c.user_id
and a.program_application_id = b.application_id
AND A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
--and user_name =
--and status_code <> 'C'
and b.language = 'US'
order by requested_start_date desc