SQL id from Request id

set lines 1000
col USER_CONCURRENT_PROGRAM_NAME for a30
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
fcr.request_id,
fcp.user_concurrent_program_name
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.SQL_ID
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    request_id='&req_id';
 

SQL ID Information

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select * from
(select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'SQL ID..................................................: '||s.sql_id,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||''||s.process||'' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'CLIENT_IDENTIFIER...................................: '||s.client_identifier,
'LOGON_TIME..........................................: '||s.logon_time,
 'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600,
'OS USER ...............................................'||s.OSUSER,
'P.USERNAME .............................................'||p.username,
'SCHEMANAME......................................'||s.SCHEMANAME
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid))
,(select /*+ CHOOSE*/
'SQL Text...: '||sql_fulltext
from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&sid');