Script Find Concurrent request

Very Important Concurrent Request Scripts | Oracle Apps R12

****HISTORY OF CONCURRENT REQUEST - SCRIPT (PROGRAM WISE) *****

set pagesize 200
set linesize 200
col "Who submitted" for a25
col "Status" for a10
col "Parameters" for a20
col USER_CONCURRENT_PROGRAM_NAME for a42
SELECT distinct t.user_concurrent_program_name,
r.REQUEST_ID,
to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.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",r.argument_text "Parameters",substr(u.description,1,25) "Who submitted",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate-30)
--AND r.requested_by=22378
AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '%%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');

 *** Requests completion date details ***

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');

*** Reqid_from sid **

SELECT a.request_id, a.PHASE_CODE, a.STATUS_CODE,

d.sid as Oracle_SID,

d.serial#,

d.osuser,

d.process,

c.SPID as OS_Process_ID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$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.PHASE_CODE='R' AND a.STATUS_CODE='R'

AND d.sid = &SID;

How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,

fnd_concurrent_queues_vl b, fnd_concurrent_requests c

where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and a.CONCURRENT_PROCESS_ID = c.controlling_manager

and c.request_id = '&conc_reqid';

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 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;

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' ;

Cancelling Concurrent request :

--By request id 

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where request_id=&req_id;

--by program_id

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where CONCURRENT_PROGRAM_ID=&prg_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';

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; 

***** 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;

***Concurrent Program count under QUEUE ***

col  "program name" format a55;

col "name" format  a17;

col "queue name" format a15

col "statuscode" format a3

select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",concurrent_queue_name "QUEUE NAME", priority,decode(phase_code,'P','Pending') "PHASE", 

decode(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') " 

NAME", status_code,count(*) from 

fnd_concurrent_worker_requests 

where  phase_code='P' and hold_flag!='Y' 

and requested_start_date<=sysdate

and concurrent_queue_name<> 'FNDCRM'

and concurrent_queue_name<> 'GEMSPS'

group by 

user_CONCURRENT_PROGRAM_NAME,

concurrent_queue_name,priority,phase_code,status_code

order by count(*) desc

/

***Lists the Manager Names with the No. of Requests in Pending/Running ***

col "USER_CONCURRENT_QUEUE_NAME" format a40;

SELECT a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES,

sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,

sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,

sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal

FROM FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b

where a.concurrent_queue_id = b.concurrent_queue_id

AND b.Requested_Start_Date<=SYSDATE

GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;

*** 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