Concurrent Manager Queries

Table Locks:

 

col OBJECT_NAME for a40
col MACHINE for a20
set lines 200
col OSUSER for a10
set num 10
col OBJECT_TYPE for a10

 

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from
v$locked_object a ,v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id and c.object_name='&table_name';

 

Session :

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where status='INACTIVE' and PROGRAM='JDBC Thin Client'

 select count(*) from v$session where status=’INACTIVE’;

 

SQL Statment from server process id :

 select sql_text from gv$sql where sql_id=(select sql_id from gv$session where paddr = (select addr from gv$process where spid=&server_process_id));

 select sql_text from gv$sql where sql_id=(select sql_id from gv$session where sid=&a and inst_id=&inst_id);

 col MODULE for a30

col PROGRAM for a30

col event for a40

col action for a30

set lines 200

• select inst_id,sid,serial#,module,program,action,status,event from gv$session where sid='&a';

• select sid,serial#,sql_id from v$session where paddr = (select addr from v$process where        spid=&server_process_id)

•  select inst_id,spid from gv$process where addr=(select paddr from gv$session where sid='&a' and    inst_id='&inst_id');

• select inst_id,username,event,count(*),sum(SECONDS_IN_WAIT) from gv$session group by inst_id,username,event  order by COUNT(*) desc;

--CONCURRENT REQUEST MONITORING QUERIES
--SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
s
et lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';
 
--SQL TO FIND CONCURRENT REQUEST SID,OS PROCESS DETAILS BY REQUEST ID
select a.argument_text, a.phase_code, a.status_code, a.oracle_process_id "DB_PROCESS", a.OS_PROCESS_ID "MT_PROCESS", f.user_name
from apps.fnd_concurrent_requests a, apps.fnd_user f where request_id='&REQUEST_ID' and a.requested_by=f.user_id;
OR
==
SELECT C.SID, C.SERIAL#, A.PID
FROM   GV$PROCESS A, FND_CONCURRENT_REQUESTS B, GV$SESSION C
WHERE  B.ORACLE_PROCESS_ID = A.SPID
-- AND    B.REQUEST_ID like '%'
AND    B.REQUEST_ID ='&x'
AND    A.ADDR=C.PADDR;

--SQL TO FIND THE CONCURRENT REQUEST TRACE FILE DETAILS (Input Request ID)
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id|
|'.trc',
--'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id ;


CC_request-SID & Serial# find script

SELECT ses.sid, ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM FND_CONCURRENT_REQUESTS
WHERE request_id ='&request_id');
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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';
***************OSPID********************
select os_process_id from fnd_concurrent_requests where request_id ='&request_id';
select request_id, phase_code, status_code, oracle_process_id from fnd_concurrent_requests where request_id ='&request_id';

*******************************
SQL> ALTER SYSTEM KILL SESSION ' 1114, 8017' IMMEDIATE;
System altered.

Issue: Request is in pending from long time,
Action:
#First Terminate the Request as follows
update fnd_concurrent_requests
   set status_code='X', phase_code='C'
   where request_id=31783706;
commit;
#Then change the status with Completed-Error as follows.
update fnd_concurrent_requests
   set status_code='E', phase_code='C'
   where request_id=31783706;
commit;
#This will change the status of any request.
#Status Code
E -  Error
X -  Terminate
G -  Warning

update applsys.fnd_concurrent_requests
set phase_code = 'C',
STATUS_CODE = 'X'
where request_id = '&Req_id';
Commit;

--Query to find concurrent program   
    select frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    From fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    where frt.responsibility_id = fr.responsibility_id
    and frg.request_group_id = fr.request_group_id
    and frgu.request_group_id = frg.request_group_id
    and fcpt.concurrent_program_id = frgu.request_unit_id
    and frt.language = USERENV('LANG')
    and fcpt.language = USERENV('LANG')
    and fcpt.user_concurrent_program_name = :conc_prg_name
    order by 1,2,3,4

--Query to find Request Set

    select frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_request_set_name
    From apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt,
    apps.fnd_request_groups frg, apps.fnd_request_group_units frgu,
    apps.fnd_request_Sets_tl fcpt
    where frt.responsibility_id = fr.responsibility_id
    and frg.request_group_id = fr.request_group_id
    and frgu.request_group_id = frg.request_group_id
    and fcpt.request_set_id = frgu.request_unit_id
    and frt.language = USERENV('LANG')
    and fcpt.language = USERENV('LANG')
    and fcpt.user_request_set_name = :request_set_name
    order by 1,2,3,4


Profile Value find script:
select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',NVL(E.USER_NAME,TO_CHAR(C.LEVEL_VALUE))
,'Unknown');

Find request set Details:
select * from fnd_conc_req_summary_v where program like 'PJS2%';


SELECT 'REQUEST SET' TYPE
,fr.responsibility_name
,frg.request_group_name
,frs.user_request_set_name
FROM
fnd_request_group_units frgus
,fnd_request_sets_vl frs
,fnd_request_groups frg
,fnd_responsibility_vl fr
WHERE 1=1
AND fr.request_group_id = frg.request_group_id
AND frg.request_group_id = frgus.request_group_id
AND frgus.request_unit_type = 'S'
AND frgus.request_unit_id = frs.request_set_id
AND frs.user_request_set_name LIKE &user_request_set_name
AND frs.request_set_name LIKE &request_set_name
UNION
SELECT 'CONC PROG' TYPE
,fr.responsibility_name
,frg.request_group_name
,fcp.user_concurrent_program_name
FROM
fnd_request_group_units frgup
,fnd_concurrent_programs_vl fcp
,fnd_request_groups frg
,fnd_responsibility_vl fr
WHERE 1=1
AND fr.request_group_id = frg.request_group_id
AND frg.request_group_id = frgup.request_group_id
AND frgup.request_unit_type = 'P'
AND frgup.request_unit_id = fcp.concurrent_program_id
AND fcp.user_concurrent_program_name LIKE &user_concurrent_program_name
AND FCP.CONCURRENT_PROGRAM_NAME LIKE &CONCURRENT_PROGRAM_NAME
/
select frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_request_set_name
    From apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt,
    apps.fnd_request_groups frg, apps.fnd_request_group_units frgu,
    apps.fnd_request_Sets_tl fcpt
    where frt.responsibility_id = fr.responsibility_id
    and frg.request_group_id = fr.request_group_id
    and frgu.request_group_id = frg.request_group_id
    and fcpt.request_set_id = frgu.request_unit_id
    and frt.language = USERENV('LANG')
    AND FCPT.LANGUAGE = USERENV('LANG')
    AND FCPT.Program = '&program'
    order by 1,2,3,4;

select * from fnd_conc_req_summary_v where program like 'PJS2%';

--Concurrent Request Error Script
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 7
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND CTL.LANGUAGE = 'US'
ORDER BY 5 DESC;

--History of concurrent requests which are error out

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;

SQL to find out the Raw trace name and location for the concurrent program
SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND PROG.APPLICATION_ID = EXECNAME.APPLICATION_ID
AND prog.executable_id=execname.executable_id;

--Trace file Including SID

SELECT 'Request id: '||request_id ,  'Trace id: '||oracle_Process_id,  'Trace Flag: '||req.enable_trace, 
'Trace Name:  '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',  'Prog. Name: '
||prog.user_concurrent_program_name,  'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')  ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,  'Module : '||ses.module  from fnd_concurrent_requests req,
v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, 
fnd_executables execname  where req.request_id = &request 
and req.oracle_process_id=proc.spid(+) 
and proc.addr = ses.paddr(+)  and dest.name='user_dump_dest'  and dbnm.name='db_name' 
and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id 
and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id;

To check the timeline of the request

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = TO_NUMBER('&p_request_id');

Find out Concurrent Program which enable with trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

CCM Long Running jobs

SELECT   fcr.oracle_session_id
        ,fcr.request_id rqst_id
        ,fcr.requested_by rqst_by
        ,fu.user_name
        ,fr.responsibility_name
        ,fcr.concurrent_program_id cp_id
        ,fcp.user_concurrent_program_name cp_name
        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')
                                                             act_start_datetime
        ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min
        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)
                                                                    runtime_sec
        ,fcr.oracle_process_id "oracle_pid/SPID"
        ,fcr.os_process_id os_pid
        ,fcr.argument_text
        ,fcr.outfile_name
        ,fcr.logfile_name
        ,fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_user fu
        ,apps.fnd_responsibility_tl fr
        ,apps.fnd_concurrent_programs_tl fcp
   WHERE fcr.status_code ='R'
     AND fu.user_id = fcr.requested_by
     AND fr.responsibility_id = fcr.responsibility_id
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 60
ORDER BY RUNTIME_MIN desc
 

Scheduled Concurrent Requests SQL query

SELECT fl.meaning
     , fu.user_name
     , fu.description requestor
     , fu.end_date
     , NVL(fu.email_address, 'n/a') email_address
     , fcr.request_id
     , fcr.number_of_copies
     , fcr.printer
     , fcr.request_date
     , fcr.requested_start_date
     , fcp.description
     , fcr.argument_text
     , frt.responsibility_name
  FROM apps.fnd_concurrent_requests fcr
     , apps.fnd_user fu
     , apps.fnd_lookups fl
     , apps.fnd_concurrent_programs_vl fcp
     , apps.fnd_responsibility_tl frt
 WHERE fcr.requested_by = fu.user_id
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.status_code = fl.lookup_code
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.phase_code = 'P'

--Status Codeselect lookup_code,meaning from fnd_lookups where lookup_type = 'CP_STATUS_CODE'order by lookup_code;
A Waiting
B Resuming
C Normal
D Cancelled
E Error
G Warning
H On Hold
I  Normal
M No Manager
P Scheduled
Q Standby
R   Normal
S Suspended
T Terminating
U Disabled
W Paused

--Phase Code
Select lookup_code,meaning from fnd_lookups where lookup_type = 'CP_PHASE_CODE';

C Completed
I Inactive
P Pending
R Running

--To find concurrent program name,phase code,status code for a given request id
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated',  'C', 'Normal', status_code) status_code,
 to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text
 FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

Concurrent Manager Actual Target Process finding script

SELECT DECODE(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN',
 'Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager',
 'FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager',
 'FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager',
 'IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session',
 'INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager',
 'PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager',
 'STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service',
 'WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service',
 'XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service',
 'XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service',
 'XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service',
 'XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service')
 AS "Concurrent Manager's Name", MAX_PROCESSES AS "TARGET Processes", RUNNING_PROCESSES AS "ACTUAL Processes"
 FROM APPS.FND_CONCURRENT_QUEUES WHERE CONCURRENT_QUEUE_NAME IN ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH',
 'FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD',
 'WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC',
 'XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');

 

-- This script will list running concurrent requests

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
       SUBSTR(proc.os_process_id,1,15) clproc,
       SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
       SUBSTR(look.meaning,1,10) reqph,
       SUBSTR(look1.meaning,1,10) reqst,
       SUBSTR(vsess.username,1,10) dbuser,
       SUBSTR(vproc.spid,1,10) svrproc,
       vsess.sid sid,
       vsess.serial# serial#
FROM   fnd_concurrent_requests req,
       fnd_concurrent_processes proc,
       fnd_lookups look,
       fnd_lookups look1,
       v$process vproc,
       v$session vsess
WHERE  req.controlling_manager = proc.concurrent_process_id(+)
AND    req.status_code = look.lookup_code
AND    look.lookup_type = 'CP_STATUS_CODE'
AND    req.phase_code = look1.lookup_code
AND    look1.lookup_type = 'CP_PHASE_CODE'
AND    look1.meaning = 'Running'
AND    PROC.ORACLE_PROCESS_ID = VPROC.PID(+)
AND    vproc.addr = vsess.paddr(+);

--This script will map concurrent request information about a specific request id.

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
       SUBSTR(proc.os_process_id,1,15) clproc,
       SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
       SUBSTR(look.meaning,1,10) reqph,
       SUBSTR(look1.meaning,1,10) reqst,
       SUBSTR(vsess.username,1,10) dbuser,
       SUBSTR(vproc.spid,1,10) svrproc
  FROM fnd_concurrent_requests req,
       fnd_concurrent_processes proc,
       fnd_lookups look,
       fnd_lookups look1,
       v$process vproc,
       v$session vsess
 WHERE req.controlling_manager = proc.concurrent_process_id(+)
   AND req.status_code = look.lookup_code
   AND look.lookup_type = 'CP_STATUS_CODE'
   AND req.phase_code = look1.lookup_code
   AND look1.lookup_type = 'CP_PHASE_CODE'
   AND req.request_id = &&reqid
   AND proc.oracle_process_id = vproc.pid(+)
   AND vproc.addr = vsess.paddr(+);
  
  
--Query to display session information
 SELECT a.username usr,
       a.sid sid,
       a.status status,
       a.server server,
       a.schemaname schema,
       b.username sosusr,
       b.spid spid,
       a.osuser cosusr,
       a.process process,
       a.machine machine,
       a.terminal terminal,
       a.program program
  FROM v$session a,
       v$process b
 WHERE a.type != 'BACKGROUND'
   AND a.paddr = b.addr
 ORDER BY a.status DESC
/

To Get Long Running Concurrent Programs
SELECT   fcr.oracle_session_id
        ,fcr.request_id rqst_id
        ,fcr.requested_by rqst_by
        ,fu.user_name
        ,fr.responsibility_name
        ,fcr.concurrent_program_id cp_id
        ,fcp.user_concurrent_program_name cp_name
        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')act_start_datetime
        ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min
        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_sec
        ,fcr.oracle_process_id "oracle_pid/SPID"
        ,fcr.os_process_id os_pid
        ,fcr.argument_text
        ,fcr.outfile_name
        ,fcr.logfile_name
        ,fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_user fu
        ,apps.fnd_responsibility_tl fr
        ,apps.fnd_concurrent_programs_tl fcp
   WHERE fcr.status_code LIKE 'R'
     AND fu.user_id = fcr.requested_by
     AND fr.responsibility_id = fcr.responsibility_id
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 60
ORDER BY fcr.concurrent_program_id
        ,request_id DESC;

To Get SID, Serial# & SPID
SELECT s.sid , s.serial# ,p.spid FROM apps.fnd_concurrent_requests f,v$session s , v$process p
WHERE f.request_id = 150332400
AND f.oracle_process_id = p.spid
AND p.addr = s.paddr;
--EXEC DBMS_SYSTEM.SET_EV(1033 , 19376 ,10046, 12 ,'');
/*To Get Current Running SQL*/
SELECT t.sql_text FROM v$sqltext t , v$session s , v$process p
WHERE p.spid = 8072
AND p.addr = s.paddr
AND t.hash_value = s.sql_hash_value
ORDER BY piece;

To Know The Current Wait Event
SELECT * FROM v$session_wait WHERE sid=1322;
/*To Get The Overall Wait Event Statistics*/
SELECT event ,ROUND(time_waited/6000,2) Time_Wait ,total_waits
FROM v$session_event
WHERE sid =678
ORDER BY time_waited/6000 DESC;

To Get The Running History Of A Concurrent Program
SELECT FCR.Oracle_Session_Id , FCR.request_id RQST_ID, FCR.requested_by RQST_BY, FU.user_name, FR.responsibility_name,
       FCR.concurrent_program_id CP_ID, FCP.user_concurrent_program_name CP_NAME,
       TO_CHAR(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') Act_Start_DateTime,
    SYSDATE,
       DECODE(FCR.status_code,'C','C:Completed',
                              'G','G:Warning',
                              'E','E:Error',FCR.status_code) Status,
       TO_CHAR(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') Act_End_DateTime,
       ROUND(((FCR.actual_completion_date - FCR.actual_start_date)*60*24),2) Runtime_Min,
       ROUND(((FCR.actual_completion_date - FCR.actual_start_date)*60*60*24),2) Runtime_Sec,
       FCR.oracle_process_id "oracle_pid/SPID", FCR.os_process_id os_pid, FCR.argument_text,
       FCR.OUTFILE_NAME,FCR.LOGFILE_NAME,FCR.ENABLE_TRACE   
 FROM apps.fnd_concurrent_requests FCR, apps.fnd_user FU,
     apps.fnd_responsibility_tl FR, apps.fnd_concurrent_programs_tl FCP
  WHERE --fcr.request_id = 150336946
        fcp.user_concurrent_program_name LIKE 'Unisys A/P Standard VAT Audit Trail Report'
  AND FU.user_id = FCR.requested_by
  AND FR.responsibility_id = FCR.responsibility_id
  AND FCR.concurrent_program_id = FCP.concurrent_program_id
  AND FCR.program_application_id = FCP.application_id
ORDER BY FCR.concurrent_program_id,request_id DESC;
SELECT * FROM fnd_concurrent_requests WHERE request_id = ********;

To Get The Running History Of A Concurrent Program--modified
SELECT   fcr.oracle_session_id o_sid
        ,ROUND ((  (  NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 24),2) runtime_min
        ,fcr.request_id rqst_id
--        ,fcr.requested_by rqst_by
        ,fu.user_name usern
        ,fr.responsibility_name
--        ,fcr.concurrent_program_id cp_id
        ,fcp.user_concurrent_program_name cp_name
        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') start_time
        ,DECODE (fcr.status_code ,'C', 'C:Completed','G', 'G:Warning','E', 'E:Error','Q','Q:Queued',fcr.status_code) status
        ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')end_time
        ,ROUND ((  (  NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 60* 24),2) runtime_sec
        ,fcr.oracle_process_id "oracle_pid/SPID"
        ,fcr.os_process_id os_pid
        ,fcr.argument_text
        ,fcr.outfile_name
        ,fcr.logfile_name
        ,fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_user fu
        ,apps.fnd_responsibility_tl fr
        ,apps.fnd_concurrent_programs_tl fcp
   WHERE --fcr.request_id = 150336946
        fcp.user_concurrent_program_name LIKE 'Unisys Customer Re-Tiering Open Orders Report'
       --AND TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY') IN ('04-MAR-2008','05-MAR-2008','06-MAR-2008')
     --AND ROUND (((fcr.actual_completion_date - fcr.actual_start_date) * 60 * 24),2) > 60
     AND fr.responsibility_id = fcr.responsibility_id
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fu.user_id = fcr.requested_by
     --AND (fcr.status_code IN ('R','Q'))
ORDER BY fcr.concurrent_program_id
        ,request_id DESC
--ORDER BY runtime_min DESC,fcr.actual_start_date DESC
--ORDER BY runtime_min DESC;

SELECT TO_CHAR(SYSDATE,'dd-MON-RR HH:MM:SS') FROM dual;

 To Get Number Of Times A Concurrent Program  Is Run
SELECT TRUNC(apps.fnd_concurrent_requests.actual_start_date) DATE_run , COUNT(*)COUNT
FROM apps.fnd_concurrent_requests
 WHERE concurrent_program_id =40856
GROUP BY TRUNC(apps.fnd_concurrent_requests.actual_start_date);
/* TO GET THE FAMILY PATCH LEVEL*/
SELECT patch_level FROM FND_PRODUCT_INSTALLATIONS WHERE patch_level LIKE '%CSI%';
/* To Get Number Of Times A Concurrent Program  Is Run*/
SELECT p.user_concurrent_program_name
      ,e.execution_file_name
      , (SELECT meaning
           FROM apps.fnd_lookups l
          WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
            AND l.lookup_code = e.execution_method_code) TYPE
      ,e.executable_name
  FROM apps.fnd_concurrent_programs_vl p
      ,apps.fnd_executables e
 WHERE p.user_concurrent_program_name =:User_Control_Program_Name
   AND p.executable_id = e.executable_id
   AND p.executable_application_id = e.application_id;
  
To Get The Details About Concurrent Program, Executable , Execution Method Etc
SELECT *
  FROM apps.fnd_concurrent_programs_tl
 WHERE user_concurrent_program_name LIKE 'Trans%';
SELECT *
  FROM apps.fnd_executables_tl
 WHERE application_id = 20006
   AND description LIKE 'Unisys AP Invoice Import Interface';
SELECT *
  FROM apps.fnd_executables
 WHERE executable_id = 7157;

To Get details On A Table
SELECT * FROM apps.fnd_tables ORDER BY table_name;
/*To Get Details On An Object*/
SELECT * FROM all_objects WHERE object_name LIKE 'V$%';
/*To Enable Trace From Back End*/
EXEC DBMS_SYSTEM.SET_EV(818 , 22295 ,10046, 12 ,'');
/*No of sec elapsed since the last call made to the database*/
SELECT s.last_call_et 
      --,q.sql_text
      ,q.users_opening
      ,q.executions
      ,q.fetches
      ,q.rows_processed
      ,TO_CHAR (s.logon_time, 'DD-MON-RR HH:MI:SS AM') session_logon
      ,TO_CHAR (TO_DATE (q.first_load_time, 'RRRR-MM-DD/HH24:MI:SS'),'DD-MON-RR HH:MI:SS AM') sql_load
  FROM v$session s
      ,v$sql q
 WHERE s.process = (SELECT os_process_id FROM fnd_concurrent_requests WHERE request_id = 18855640)                   
   AND s.sql_hash_value = q.hash_value;

No of physical GETS
SELECT sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
  FROM v$sess_io sess_io, v$session sesion
 WHERE sesion.sid = sess_io.sid
   AND sesion.username IS NOT NULL
   AND sesion.sid = 678
  
TOP_SQL by different parameters
SELECT v$session.sid,
   v$session.serial#,
   (cpu_time / 1000000) "CPU_Seconds",
   disk_reads "Disk_Reads",
   buffer_gets "Buffer_Gets",
   executions "Executions",
   CASE
WHEN rows_processed = 0 THEN
   NULL
ELSE
   (buffer_gets / NVL(REPLACE(rows_processed,    0,    1),    1))
END "Buffer_gets/rows_proc",
   (buffer_gets / NVL(REPLACE(executions,    0,    1),    1)) "Buffer_gets/executions",
   (elapsed_time / 1000000) "Elapsed_Seconds",
   v$sql.MODULE "Module",
   SUBSTR(sql_text,    1,    500) "SQL"
FROM v$sql,
   v$session
WHERE v$sql.hash_value = v$session.sql_hash_value
ORDER BY cpu_time DESC ,
(buffer_gets/NVL(REPLACE(rows_processed,0,1),1)) DESC ,
buffer_gets DESC ,
disk_reads DESC ,
executions DESC nulls last;

SELECT * FROM fnd_concurrent_programs WHERE CONCURRENT_PROGRAM_ID = 36034;

Script to get the trace file name from request_id
SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
       'Trace Flag: ' || req.enable_trace,
          'Trace Name:
'
       || dest.VALUE
       || '/'
       || LOWER (dbnm.VALUE)
       || '_ora_'
       || oracle_process_id
       || '.trc
',
       'Prog. Name: ' || prog.user_concurrent_program_name,
       'File Name: ' || execname.execution_file_name
       || execname.subroutine_name,
          'Status : '
       || DECODE (phase_code, 'R', 'Running')
       || '-'
       || DECODE (status_code, 'R', 'Normal'),
       'SID Serial: ' || ses.SID || ',' || ses.serial#,
       'Module : ' || ses.module
  FROM fnd_concurrent_requests req,
       v$session ses,
       v$process proc,
       v$parameter dest,
       v$parameter dbnm,
       fnd_concurrent_programs_vl prog,
       fnd_executables execname
 WHERE req.request_id = '&request'
   AND req.oracle_process_id = proc.spid(+)
   AND proc.addr = ses.paddr(+)
   AND dest.NAME = 'user_dump_dest'
   AND dbnm.NAME = 'db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id
   AND prog.executable_id = execname.executable_id;
                                 
SELECT * FROM apps.hr_organization_information hoi2
WHERE hoi2.org_information3 = '29';

SELECT * FROM apps.hr_organization WHERE organization_id = 29;

SELECT * FROM all_tables WHERE table_name LIKE '%HR%ORG%';

SELECT organization_id , attribute4 FROM hr_all_organization_units WHERE organization_id = 29;

SELECT * FROM fnd_user WHERE user_name LIKE 'SADASIN'

SELECT * FROM v$lock WHERE sid = 1314

SELECT * FROM dba_waiters WHERE holding_session = 1314

SELECT sql_hash_value FROM v$session WHERE sid = 1010

SELECT * FROM V$SQL_PLAN WHERE hash_value = 4118758020

SELECT * FROM all_tables WHERE table_name LIKE 'AD%PATCH%'

SELECT * FROM ad_bugs WHERE creation_date > '16-AUG-2008';

SELECT * FROM fnd_executables WHERE execution_file_name LIKE 'FAS480%'

select lookup_code,meaning from fnd_lookups where lookup_type = 'CP_STATUS_CODE' order by lookup_code;

A Waiting
B Resuming
C Normal
D Cancelled
E Error
G Warning
H On Hold
I  Normal
M No Manager
P Scheduled
Q Standby
R   Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z  Waiting

Select lookup_code,meaning from fnd_lookups where lookup_type = 'CP_PHASE_CODE' order by lookup_code;
C Completed
I Inactive
P Pending
R Running

--SID for completed request
SELECT DISTINCT b.request_id request,
               DECODE (b.parent_request_id,
                       -1, '-------',
                       b.parent_request_id
                      ) PARENT,
               b.requestor, b.program, d.meaning phase,
               TRIM (c.meaning) status,
               TO_CHAR (a.request_date, 'DD-MM-YY-HH24:mi') rdate,
               decode(os_process_id,null,'-----',os_process_id) fndpid, e.SID SID, f.spid spid,
               e.inst_id  server
          FROM fnd_conc_req_summary_v b,
               fnd_lookups c,
               (SELECT lookup_code, meaning, lookup_type
                  FROM fnd_lookups
                 WHERE lookup_type = 'CP_PHASE_CODE') d,
               fnd_concurrent_requests a,
               gv$session e,
               gv$process f
         WHERE
       b.request_id='&REQUEST_ID'
   AND c.lookup_type = 'CP_STATUS_CODE'
           AND b.status_code = c.lookup_code
           AND d.lookup_type = 'CP_PHASE_CODE'
           AND b.phase_code = d.lookup_code
           AND b.request_id = a.request_id
           AND f.spid(+) = a.oracle_process_id
           AND e.paddr(+) = f.addr
           ORDER BY b.request_id DESC;

CCM-completed-With-Error   

SELECT   fcr.oracle_session_id
        ,fcr.request_id rqst_id
        ,fcr.phase_code
        ,fcr.status_code
        ,fcr.requested_by rqst_by
        ,fu.user_name
        ,fr.responsibility_name
        ,fcr.concurrent_program_id cp_id
        ,fcp.user_concurrent_program_name cp_name
        ,fcr.description request_set_name
        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')
                                                             act_start_datetime
        ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')
                                                             act_completion_datetime
        ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min
        ,fcr.oracle_process_id "oracle_pid/SPID"
        ,fcr.os_process_id os_pid
        ,fcr.argument_text
        ,fcr.outfile_name
        ,fcr.logfile_name
        ,fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_user fu
        ,apps.fnd_responsibility_tl fr
        ,apps.fnd_concurrent_programs_tl fcp
   WHERE fcr.phase_code ='C'
     AND fcr.status_code not in ('C','G')
     AND fu.user_id = fcr.requested_by
     AND fr.responsibility_id = fcr.responsibility_id
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND to_char (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')  > '27-FEB-2012 03:25:42'
ORDER BY fcr.actual_completion_date DESC;

CCM-completed-With-Warning

SELECT   fcr.oracle_session_id
        ,fcr.request_id rqst_id
        ,fcr.phase_code
        ,fcr.status_code
        ,fcr.requested_by rqst_by
        ,fu.user_name
        ,fr.responsibility_name
        ,fcr.concurrent_program_id cp_id
        ,fcp.user_concurrent_program_name cp_name
        ,fcr.description request_set_name
        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')
                                                             act_start_datetime
        ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')
                                                             act_completion_datetime
        ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min
        ,fcr.oracle_process_id "oracle_pid/SPID"
        ,fcr.os_process_id os_pid
        ,fcr.argument_text
        ,fcr.outfile_name
        ,fcr.logfile_name
        ,fcr.enable_trace
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_user fu
        ,apps.fnd_responsibility_tl fr
        ,apps.fnd_concurrent_programs_tl fcp
   WHERE fcr.phase_code ='C'
     AND fcr.status_code in ('G')
     AND fu.user_id = fcr.requested_by
     AND fr.responsibility_id = fcr.responsibility_id
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND to_char (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')  > '27-FEB-2012 03:25:42'
ORDER BY fcr.actual_completion_date DESC;

 Concurrent request status for a given sid

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.INST_ID,s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';

--Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

--To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'
/
To find child requests for Parent request id.
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code)
phase_code, DECODE(sum.status_code,'D', 'Cancelled' , 'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' ,
'X', 'Terminated',  'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text
FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where  req.request_id=sum.request_id
and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';

set col os_process_id for 99
select HAS_SUB_REQUEST, is_SUB_REQUEST, parent_request_id, ORACLE_PROCESS_ID, ORACLE_SESSION_ID, OS_PROCESS_ID
from fnd_concurrent_requests where request_id= '&Req_ID' ;


To terminate the all concurrent requests using by Module wise.

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from gv$session where MODULE like 'GLPREV';



 Concurrent QUEUE Details
set echo off
set linesize 130
set serveroutput on size 50000
set feed off
set veri off
DECLARE
running_count NUMBER := 0;
pending_count NUMBER := 0;
crm_pend_count NUMBER := 0;
--get the list of all conc managers and max worker and running workers
CURSOR conc_que IS
SELECT concurrent_queue_id,
concurrent_queue_name,
user_concurrent_queue_name,
max_processes,
running_processes
FROM apps.fnd_concurrent_queues_vl
WHERE enabled_flag='Y' and
concurrent_queue_name not like 'XDP%' and
concurrent_queue_name not like 'IEU%' and
concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue          '||
'Concurrent Queue Name               '||' '||'MAX '||' '||'RUN '||' '||
'Running '||' '||'Pending   '||' '||'In CRM');
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
FOR i IN conc_que
LOOP

For each manager get the number of pending and running requests in each queue
SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0),
nvl(sum(decode(phase_code, 'P', 1, 0)), 0)
INTO running_count, pending_count
FROM fnd_concurrent_worker_requests
WHERE
requested_start_date <= sysdate
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y';
--for each manager get the list of requests pending due to conflicts in each manager
SELECT /*+ RULE */ count(1)
INTO crm_pend_count
FROM apps.fnd_concurrent_worker_requests a
WHERE concurrent_queue_id = 4
AND hold_flag != 'Y'
AND requested_start_date <= sysdate
AND exists (
SELECT 'x'
FROM apps.fnd_concurrent_worker_requests b
WHERE a.request_id=b.request_id
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y'
AND requested_start_date <= sysdate);
--print the output by joining the outputs of manager counts, 
DBMS_OUTPUT.PUT_LINE(
rpad(i.concurrent_queue_id,8,'_')||
rpad(i.concurrent_queue_name,15, ' ')||
rpad(i.user_concurrent_queue_name,40,' ')||
rpad(i.max_processes,6,' ')||
rpad(i.running_processes,6,' ')||
rpad(running_count,10,' ')||
rpad(pending_count,10,' ')||
rpad(crm_pend_count,10,' '));
--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
END;
/
set verify on
set echo on

CCM Failed Jobs

select
  fcr.request_id,
  fcr.parent_request_id,
  fu.user_name requestor,
  to_char(fcr.last_update_date, 'MON-DD-YYYY HH24:MM:SS') LAST_UPDATE,
  fr.responsibility_key responsibility,
  fcp.concurrent_program_name,
  fcpt.user_concurrent_program_name,
  fcr.argument_text,
  decode(fcr.status_code,
    'A', 'Waiting',
    'B', 'Resuming',
    'C', 'Normal',
    'D', 'Cancelled',
    'E', 'Error',
    'F', 'Scheduled',
    'G', 'Warning',
    'H', 'On Hold',
    'I', 'Normal',
    'M', 'No Manager',
    'Q', 'Standby',
    'R', 'Normal',
    'S', 'Suspended',
    'T', 'Terminating',
    'U', 'Disabled',
    'W', 'Paused',
    'X', 'Terminated',
    'Z', 'Waiting') status,
  decode(fcr.phase_code,
    'C', 'Completed',
    'I', 'Inactive',
    'P', 'Pending',
    'R', 'Running') phase,
  fcr.completion_text
from
  fnd_concurrent_requests fcr,
  fnd_concurrent_programs fcp,
  fnd_concurrent_programs_tl fcpt,
  fnd_user fu,
  fnd_responsibility fr
where
  fcr.status_code in ('D', 'E', 'S', 'T', 'X') and
  fcr.phase_code = 'C' and
  fcr.last_update_date > sysdate - 1 and
  fu.user_id = fcr.requested_by and
  fcr.concurrent_program_id = fcp.concurrent_program_id and
  fcr.concurrent_program_id = fcpt.concurrent_program_id and
  fcr.responsibility_id = fr.responsibility_id
order by
  fcr.last_update_date,
  fcr.request_id;

show the concurrent manager job status

set linesize 178
set verify off
SET VERIFY   OFF
COLUMN request_id        FORMAT  999999999 HEADING 'Request|ID';
COLUMN Req_Date          FORMAT        a20 HEADING 'Req|Date';
COLUMN oracle_process_id FORMAT    9999999 HEADING 'PID';
COLUMN session_id        FORMAT    9999999 HEADING 'Session ID';
COLUMN oracle_id         FORMAT    9999999 HEADING 'Oracle ID';
COLUMN os_process_id     FORMAT        a10 HEADING 'OS PID';
COLUMN requested_by      FORMAT    9999999 HEADING 'Requested By';
COLUMN phase_code        FORMAT         a6 HEADING "Phase|Code"
COLUMN status_code       FORMAT         a6 HEADING "Status|Code"
COLUMN completion_text   FORMAT        a35 HEADING 'Text';
COLUMN user_id           FORMAT    9999999 HEADING 'User ID';
COLUMN user_name         FORMAT        a10 HEADING 'User|Name';
COLUMN Req_Date          FORMAT        a20 HEADING 'Req|Date';
SELECT
    a.request_id
  , to_char(a.REQUEST_DATE,'DD-MON-YYYY HH24:MI:SS') Req_Date
  , b.user_name
  , a.phase_code
  , a.status_code
  , c.os_process_id
  , a.oracle_id
  , a.requested_by
  , a.completion_text
FROM
    applsys.fnd_concurrent_requests a
  , applsys.fnd_user b
  , applsys.fnd_concurrent_processes c
WHERE
      a.requested_by = b.user_id
  AND c.concurrent_process_id = a.controlling_manager
  AND a.phase_code in ('R', 'T')
ORDER BY
  a.request_id, c.os_process_id
/
set linesize 178
set verify off
COLUMN start_time FORMAT a18
COLUMN program_name FORMAT a50
COLUMN reqid FORMAT 999999999 HEADING 'Request|ID'
COLUMN tot_mins FORMAT 9999999 HeaDING "Total|Run-Time|in Mins"
COLUMN hrs FORMAT 99999 HEADING "Running|Hrs"
COLUMN mins FORMAT 99999 HEADING "Running|Mins"
COLUMN secs FORMAT 99999 HEADING "Running|Secs"
COLUMN user_name FORMAT a12
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN phase FORMAT a5 HEADING "Phase|Code"
COLUMN status FORMAT a6 HEADING "Status|Code"
select r.request_id reqid,
to_char(r.actual_start_date, 'DD-MON-YY HH24:MI:SS') start_time,
u.user_name,
r.phase_code phase,
r.status_code status,
floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600) hrs,
floor((((SYSDATE - r.actual_start_date)*24*60*60) - floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600)*3600)/60) mins,
round((((SYSDATE - r.actual_start_date)*24*60*60) - floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600)*3600 - (floor((((SYSDATE - r.actual_start_date)*24*60*60) - floor(((SYSDATE - r.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) secs,
(SYSDATE - r.actual_start_date)*24*60 Tot_Mins,
/* p.concurrent_program_id progid,*/
decode(p.user_concurrent_program_name,
       'Request Set Stage', 'RSS - '||r.description,
       'Report Set', 'RS - '||r.description,
       p.user_concurrent_program_name ) program_name,
       s.sid, s.serial#
from   v$session s,
       apps.fnd_user u,
       apps.fnd_concurrent_processes pr,
       apps.fnd_concurrent_programs_vl p,
       apps.fnd_concurrent_requests r
where s.process = pr.os_process_id
and pr.concurrent_process_id = r.controlling_manager
and r.phase_code = 'R' -- and r.status_code = 'R'
and r.requested_by = u.user_id
and p.concurrent_program_id = r.concurrent_program_id
order by 1
/
column start_time clear
column program_name clear
column reqid clear
column user_name clear
column sid clear
column serial# clear
! cat ~/scripts/conc_manager_user_query.lst
exit;
echo "Running conc_manager_user_query.sql"
echo
echo "" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst
echo "The phase_code and its meaning for fnd_concurrent_requests" >> ~/scripts/conc_manager_user_query.lst
echo "LOOKUP_CODE                    MEANING" >> ~/scripts/conc_manager_user_query.lst
echo "---------------------------------------------------------------------------" >> ~/scripts/conc_manager_user_query.lst
echo "C                              Completed" >> ~/scripts/conc_manager_user_query.lst
echo "I                              Inactive" >> ~/scripts/conc_manager_user_query.lst
echo "P                              Pending" >> ~/scripts/conc_manager_user_query.lst
echo "R                              Running" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst
echo "The status_code and its meaning for fnd_concurrent_requests" >> ~/scripts/conc_manager_user_query.lst
echo "LOOKUP_CODE                    MEANING" >> ~/scripts/conc_manager_user_query.lst
echo "---------------------------------------------------------------------------" >> ~/scripts/conc_manager_user_query.lst
echo "A                              Waiting" >> ~/scripts/conc_manager_user_query.lst
echo "B                              Resuming" >> ~/scripts/conc_manager_user_query.lst
echo "C                              Normal" >> ~/scripts/conc_manager_user_query.lst
echo "D                              Cancelled ">> ~/scripts/conc_manager_user_query.lst
echo "E                              Error" >> ~/scripts/conc_manager_user_query.lst
echo "F                              Scheduled" >> ~/scripts/conc_manager_user_query.lst
echo "G                              Warning" >> ~/scripts/conc_manager_user_query.lst
echo "H                              On Hold" >> ~/scripts/conc_manager_user_query.lst
echo "I                              Normal" >> ~/scripts/conc_manager_user_query.lst
echo "M                              No Manager">> ~/scripts/conc_manager_user_query.lst
echo "Q                              Standby" >> ~/scripts/conc_manager_user_query.lst
echo "R                              Normal" >> ~/scripts/conc_manager_user_query.lst
echo "S                              Suspended" >> ~/scripts/conc_manager_user_query.lst
echo "T                              Terminating">> ~/scripts/conc_manager_user_query.lst
echo "U                              Disabled" >> ~/scripts/conc_manager_user_query.lst
echo "W                              Paused" >> ~/scripts/conc_manager_user_query.lst
echo "X                              Terminated" >> ~/scripts/conc_manager_user_query.lst
echo "Z                              Waiting" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst
echo "The status_code and its meaning for fnd_concurrent_processes" >> ~/scripts/conc_manager_user_query.lst
echo "LOOKUP_CODE                    MEANING" >> ~/scripts/conc_manager_user_query.lst
echo "---------------------------------------------------------------------------" >> ~/scripts/conc_manager_user_query.lst
echo "A                              Active" >> ~/scripts/conc_manager_user_query.lst
echo "G                              Awaiting Discovery" >> ~/scripts/conc_manager_user_query.lst
echo "C                              Connecting ">> ~/scripts/conc_manager_user_query.lst
echo "S                              Deactivated " >> ~/scripts/conc_manager_user_query.lst
echo "D                              Deactiviating" >> ~/scripts/conc_manager_user_query.lst
echo "Z                              Initializing" >> ~/scripts/conc_manager_user_query.lst
echo "M                              Migrating" >> ~/scripts/conc_manager_user_query.lst
echo "R                              Running" >> ~/scripts/conc_manager_user_query.lst
echo "P                              Suspended" >> ~/scripts/conc_manager_user_query.lst
echo "K                              Terminated" >> ~/scripts/conc_manager_user_query.lst
echo "T                              Terminating" >> ~/scripts/conc_manager_user_query.lst
echo "" >> ~/scripts/conc_manager_user_query.lst
cat ~/scripts/conc_manager_user_query.lst
exit;

Find CCM Output file

SELECT OUTFILE_NODE_NAME,OUTFILE_NAME
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID in ('247321000','247320592','247319626','247319556','247319378');

SELECT OUTFILE_NODE_NAME,OUTFILE_NAME, logfile_node_name, logfile_name
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID in ('41261937','41234458','41234160');

ORACLE SPID find from Conc Request_ID

SELECT a.request_id, c.spid
FROM   apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       v$process c
WHERE  c.spid IN ( SELECT c.spid
                   FROM   apps.fnd_concurrent_requests a,
                          apps.fnd_concurrent_processes b,
                          v$process c
                   WHERE  a.controlling_manager = b.concurrent_process_id
                   AND    c.pid = b.oracle_process_id
                   AND    a.request_id = &req_id
                 )
AND   a.controlling_manager = b.concurrent_process_id
AND   c.pid = b.oracle_process_id
AND   A.PHASE_CODE = UPPER('&phase');

Change The PHASE CODE..To Find oracle SID,SPID

SELECT a.request_id, d.sid, d.serial#
FROM   apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       v$process c,
       v$session d
WHERE  a.controlling_manager = b.concurrent_process_id
AND    c.pid = b.oracle_process_id
AND    c.serial# = d.serial#
AND    a.request_id = &req_id
AND    A.PHASE_CODE = 'C';

CCM PLSQL FIND
SELECT request_id,
       requested_by,
       phase_code,
       status_code,
       program_application_id,
       concurrent_program_id,
       controlling_manager,
       oracle_process_id,
       oracle_session_id,
       os_process_id,
       enable_trace
  FROM FND_CONCURRENT_REQUESTS
 WHERE REQUEST_ID = TO_NUMBER('&p_request_id');

 Find PLSQL from request_id
 select cp.plsql_dir, cp.plsql_out, cp.plsql_log
 FROM FND_CONCURRENT_REQUESTS CR, FND_CONCURRENT_PROCESSES CP
 WHERE CR.REQUEST_ID = '&REQUEST_ID'
 and cp.concurrent_process_id = cr.controlling_manager;


 USER-ID find Script
 I/P Requestor Name
 SELECT user_id,
        user_name,
        description
   FROM FND_USER
 WHERE USER_name = '&P_REQUESTED_BY';

 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') ENDEDCR FROM SYS.DUAL;