How to find concurrent request id from Sid or spid

We often come across a situation in EBS Database, where we want to know the Concurrent request id from the given SID or SPID. Here are some queries which can be helpful in this situation

How to find the concurrent request id from Sid

Non-RAC

set lines 2000
select a.request_id ,a.os_process_id,b.sid from apps.fnd_concurrent_requests a,v$session b where
b.audsid(+)= a.oracle_session_id and b.sid=&1;
or
set lines 2000
select a.request_id,b.sid,b.serial#,c.spid from apps.fnd_concurrent_requests a,v$session b , v$process c where
a.oracle_process_id= c.spid and b.paddr=c.addr  and b.sid=&1;

RAC

set lines 2000 
select a.request_id ,a.os_process_id,b.sid,b.inst_id,b.serial# from apps.fnd_concurrent_requests a,gv$session b where b.audsid(+)= a.oracle_session_id and b.sid=&1; 
or 
set lines 2000 
select a.request_id,b.sid,b.serial#,b.inst_id,c.spid from apps.fnd_concurrent_requests a,gv$session b , gv$process c where a.oracle_process_id= c.spid and b.paddr=c.addr  and b.inst_id=a.inst_id and b.sid=&1;

How to find the concurrent request id from SPID

Non-RAC

set lines 2000 
select a.request_id,b.sid,b.serial#,c.spid from apps.fnd_concurrent_requests a,v$session b , v$process c where a.oracle_process_id= c.spid and b.paddr=c.addr  and c.spid=&1;

RAC

set lines 2000  
select a.request_id,b.sid,b.serial#,b.inst_id,c.spid from apps.fnd_concurrent_requests a,gv$session b , gv$process c where a.oracle_process_id= c.spid and b.paddr=c.addr  and b.inst_id=a.inst_id and c.spid=&1;

Related Articles 

Oracle Concurrent Manager : How an E-Business Suite Concurrent Manager Process Works,Oracle Concurrent Manager,What is internal monitor, What is service manager and troubleshooting
Concurrent Request Phase and status : All information about Concurrent Request Phase and Status .The meaning derived for each combination.
Core files in Oracle Concurrent manager : This page contains description about core file for oracle concurrent manager.Core file can be used to debug various issues in CM
Priority for concurrent Program : This post has detailed description about changing Priority for Concurrent Program or user or request to solve user critical report running issues
Concurrent Manager Interview questions : Check out 24 Concurrent Manager Interview questions to help you in EBS interview. This consists of all sort of question on standard manager,service manager
Parallel Concurrent Processing: What is PCP, How to setup it, how to define internal monitor
ORA-01427 : Check out this for the solution on ORA-01427: single-row subquery returns more than one row error ,how to resolve it when it happens with Concurrent Manager