User:Eswarkanumuru/sandbox

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

2.Concurrent manager 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.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';

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

4.To find sid,serial# for a given concurrent request id? - set lines 200 SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process, c.SPID ,d.inst_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.request_id = &Request_ID AND a.phase_code = 'R';

SELECT sid, SERIAL# FROM gv$session WHERE paddr LIKE (SELECT addr FROM gv$process WHERE spid = (SELECT oracle_process_id FROM apps.fnd_concurrent_requests WHERE request_id = TO_NUMBER(30122498) ) );

Select a.inst_id, sid, a.serial#, b.spid ,a.event,a.p1,a.sql_id from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='36693839' AND a.inst_id = b.inst_id and c.os_process_id = a.process;

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

6.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' /

7. To find child requests - 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';

8. Cancelling Concurrent request : -- update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id=&req_id;

Putting all concurrent jobs on hold:

Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');

Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them.

ALTER SESSION SET NLS_LANGUAGE='American' /

--- -- To Cancel the standalone concurrent request --- update fnd_concurrent_requests fcr set status_code='D', phase_code='C' where phase_code <> 'C' and status_code in ( 'I' --Inactive ,'Q' --Pending Standby ,'H' --On hold ,'F' --Scheduled ,'M' -- 'No Managers ) and exists (	select 1 	from fnd_concurrent_programs_vl fcp	   ,fnd_lookup_values flv	where fcp.concurrent_program_id = fcr.concurrent_program_id	and   fcp.user_concurrent_program_name = flv.meaning	and   flv.lookup_type='FDX_DELETE_JOBS_POST_CLONE'        and   flv.lookup_code like 'B%'	and   flv.enabled_flag ='Y' ) /-- 1,557 rows updated. Commit / --- -- To Cancel the request submitted for concurrent programs attached to that request set. --- update fnd_concurrent_requests fcr set status_code='D', phase_code='C' where phase_code <> 'C' and status_code in ( 'I' --Inactive ,'Q' --Pending Standby ,'H' --On hold ,'F' --Scheduled ,'M' -- 'No Managers ) and exists (	select 1        from fnd_request_set_programs frsp        where frsp.concurrent_program_id = fcr.concurrent_program_id        and exists		( select 1 from fnd_request_sets_vl frsv ,fnd_lookup_values flv where frsv.request_set_id = frsp.request_set_id and  frsv.user_request_set_name = flv.meaning and  flv.lookup_type='FDX_DELETE_JOBS_POST_CLONE' and  flv.lookup_code like 'A%' and  flv.enabled_flag ='Y' 		) ) / Commit / --- -- To Cancel the request set submission. --- update fnd_concurrent_requests fcr set status_code='D', phase_code='C' where phase_code <> 'C' and status_code in ( 'I' --Inactive ,'Q' --Pending Standby ,'H' --On hold ,'F' --Scheduled ,'M' -- 'No Managers ) and exists (	select 1 	from fnd_lookup_values flv	where fcr.description = flv.meaning	and   flv.lookup_type='FDX_DELETE_JOBS_POST_CLONE' 	and   flv.lookup_code like 'A%'	and   flv.enabled_flag ='Y' ) / Commit /

--- update fnd_concurrent_requests set phase_code='C', status_code='D' where phase_code = 'P' and concurrent_program_id not in ( select concurrent_program_id from fnd_concurrent_programs_tl where user_concurrent_program_name like '%Synchronize%tables%' or user_concurrent_program_name like '%Workflow%Back%' or user_concurrent_program_name like '%Sync%responsibility%role%' or user_concurrent_program_name like '%Workflow%Directory%') and (status_code = 'I' OR status_code = 'Q');

Use the SQL below to only cancel the running requests connecting as sys UPDATE applsys.fnd_concurrent_requests SET phase_code = 'C', status_code = 'X' WHERE phase_code = 'R' and status_code ='R' / commit

9. Kill sessions program wise

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

10 .Concurrent Request running by SID - SELECT a.request_id, 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 d.sid = &SID;

select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status from gv$process v, gv$session s, applsys.fnd_concurrent_requests f where 1=1 and s.paddr=v.addr and f.oracle_process_id=v.spid and sid=580 and f.status_code='R';

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

12. Oracle Concurrent Request Error Script (requests which were error ed 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;

SELECT fcr.request_id, fu.user_name, fcp.user_concurrent_program_name "Concurrent_Program_Name", TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "actual_start_date", TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "actual_completion_date" FROM apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs_tl fcp, apps.fnd_user fu  WHERE     fcr.program_application_id = fcp.application_id AND fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.requested_by = fu.user_id AND fcr.phase_code = 'C'        AND fcr.status_code = 'E'         ORDER BY 1; Concurrent Request Error Completed with error or Warning

SELECT f.user_name, r.request_id, prg.concurrent_program_name Module, p.USER_CONCURRENT_PROGRAM_NAME, decode(r.phase_code, 'C', 'Completed', 'I' , 'Inactive', 'P' , 'Pending', 'R' , 'Running',r.phase_code) Phase_Desc, 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',r.status_code) Status_Desc, r.actual_start_date, r.actual_completion_date FROM fnd_concurrent_requests r,fnd_concurrent_programs_tl p,fnd_concurrent_programs prg, fnd_user f WHERE r.program_APPLICATION_ID=p.APPLICATION_ID and r.CONCURRENT_PROGRAM_ID =p.CONCURRENT_PROGRAM_ID and r.requested_by = f.user_id and r.program_APPLICATION_ID=prg.APPLICATION_ID and r.CONCURRENT_PROGRAM_ID =prg.CONCURRENT_PROGRAM_ID and r.status_code in ('E','G') and r.actual_completion_date > (sysdate-1)

13. Request submitted by User - SELECT user_concurrent_program_name, request_date, request_id, phase_code, status_code FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp, fnd_responsibility_tl fr, fnd_user fu WHERE fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id and fcr.responsibility_id = fr.responsibility_id and fcr.requested_by = fu.user_id and user_name = '&user' AND actual_start_date > sysdate - 1 ORDER BY REQUEST_DATE Asc;

14.Concurrent Program 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 Programs Running long. ex: 30 mins --

col program form A35 trunc head "Program Full Name" col intprog format a20 trunc head "Internal Name" col time form 9999.99 col "Req Id" form 9999999 col qname head "Concurrent Manager Queue" format a25 trunc rem select q.concurrent_queue_name qname select q.concurrent_queue_name || ' - ' || target_node qname ,a.request_id "Req Id" ,a.phase_code,a.status_code ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time" ,c.concurrent_program_name || ' (' || to_char(c.concurrent_program_id) || ')' intprog, 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.phase_code in ('I','P','R','T') 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' and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 2 and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > ( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 from APPLSYS.fnd_Concurrent_requests a2, APPLSYS.fnd_concurrent_programs c2 where c2.concurrent_program_id = c.concurrent_program_id and a2.concurrent_program_id = c2.concurrent_program_id and a2.program_application_id = c2.application_id and a2.phase_code || '' = 'C' ) order by 5 desc;

Scripts for CM -- Concurrent Processing - CP Analyzer Sample SQL Statements Including Date Range Parameters from Doc 1411723.1- Concurrent Processing - CP Analyzer for E-Business Suite [ID 1499538.1]

Identify the request that are waiting for CM process - SELECT REQUEST_ID, TO_CHAR (REQUESTED_START_DATE, 'DD-Mon-YY HH24:MI:SS') || '-' || TO_CHAR (ACTUAL_START_DATE, 'DD-Mon-YY HH24:MI:SS') "Requested - Actual", (ACTUAL_START_DATE - REQUESTED_START_DATE) * 24 * 60 delayed_start_min, HOLD_FLAG, IS_SUB_REQUEST, PHASE_CODE, STATUS_CODE FROM apps.fnd_concurrent_requests WHERE ACTUAL_COMPLETION_DATE > sysdate -1 and (ACTUAL_START_DATE - REQUESTED_START_DATE) * 24 * 60 > 1 ORDER BY 1;

Trace file location of the concurrent request based on request ID -

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;

Query to find out concurrent program details: - select prog.user_concurrent_program_name "program name", prog.concurrent_program_name "program short name", appl.application_name "program application name", prog.description "program description", exe.executable_name "executable name", exe.execution_file_name "executable file name", decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader', 'E' ,'Perl Concurrent Program', 'A' ,'Spawned', 'B' ,'Request Set Stage Function', 'K' ,'Java Concurrent Program', 'J' ,'Java Stored Procedure', 'S' ,'Immediate', 'H' ,'Host', 'Q','SQL*Plus', exe.execution_method_code) "execution method" from apps.fnd_executables exe, apps.fnd_application_tl appl, apps.fnd_concurrent_programs_vl prog where exe.application_id = appl.application_id AND exe.executable_id = prog.executable_id AND appl.language='US' AND prog.user_concurrent_program_name =:p_conc_prog_name;

Concurrent Manager Administer backend Querry

SELECT 'Instance : ' FROM v$database; Prompt =========================== Prompt concurrent manager status Prompt =========================== SELECT q.concurrent_queue_id, q.concurrent_queue_name, q.user_concurrent_queue_name, q.target_node, q.max_processes, q.running_processes, running.run running, pending.pend, Decode(q.control_code, 'D', 'Deactivating', 'E', 'Deactivated', 'N', 'Node unavai', 'A', 'Activating', 'X', 'Terminated', 'T', 'Terminating', 'V', 'Verifying', 'O', 'Suspending', 'P', 'Suspended', 'Q', 'Resuming', 'R', 'Restarting') status FROM (SELECT concurrent_queue_name, COUNT(phase_code) run FROM fnd_concurrent_worker_requests WHERE phase_code = 'R' AND hold_flag != 'Y' AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) running, (SELECT concurrent_queue_name, COUNT(phase_code) pend FROM fnd_concurrent_worker_requests WHERE phase_code = 'P' AND hold_flag != 'Y' AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) pending, apps.fnd_concurrent_queues_vl q WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) AND q.concurrent_queue_name = pending.concurrent_queue_name(+) AND q.enabled_flag = 'Y' ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)), Sign(q.max_processes) DESC, q.concurrent_queue_name, q.application_id;
 * NAME instance_name

Checking the locks in concurrent jobs --

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;

Checking the concurrent programs currently with Details of Processed time-- and Start Date --- SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end, (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority FROM  apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d WHERE  a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND a.requested_by=d.user_id AND status_code='R' order by Process_time desc; For checking last run of a Concurrent Program along with Processed time Useful to find the Details of Concurrent programs which run daily and comparison purpose - SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME, round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time, a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'), To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end, (a.actual_start_date-a.request_date)*24*60*60 AS lag_time, d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority FROM  apps.fnd_concurrent_requests a,            apps.fnd_concurrent_programs b , apps.FND_CONCURRENT_PROGRAMS_TL c,           apps.fnd_user d WHERE       a.concurrent_program_id= b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND a.requested_by =d.user_id AND --         trunc(a.actual_completion_date) = '24-AUG-2005' c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like  '%,, , , ,%'; --         and status_code!='C'

Checking the last run of concurrent Program. - Use below query to check all the concurrent request running which may refer given package -- This is very useful check before compiling any package on given instance. -- The query can be modified as per requirement. -- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package. -- -- -- SELECT FCR.REQUEST_ID ,FCPV.USER_CONCURRENT_PROGRAM_NAME ,FCPV.CONCURRENT_PROGRAM_NAME ,FCPV.CONCURRENT_PROGRAM_ID ,FCR.STATUS_CODE ,FCR.PHASE_CODE FROM FND_CONCURRENT_PROGRAMS_VL FCPV ,FND_EXECUTABLES FE ,SYS.DBA_DEPENDENCIES DD ,FND_CONCURRENT_REQUESTS FCR WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID AND FE.EXECUTION_METHOD_CODE = 'I' AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME) AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required --AND referenced_owner = 'XXCUS' AND DD.REFERENCED_NAME = UPPER('&Package_name') AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID AND fcr.phase_code NOT IN ( 'C','P');

The following query will display the time taken to execute the concurrent Programs --for a particular user with the latest concurrent programs sorted in least time taken -- to complete the request. SELECT f.request_id , pt.user_concurrent_program_name user_conc_program_name, f.actual_start_date start_on, f.actual_completion_date end_on, floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)       || ' HOURS ' || floor((((f.actual_completion_date-f.actual_start_date)       *24*60*60) - floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)*3600)/60)       || ' MINUTES ' || round((((f.actual_completion_date-f.actual_start_date)       *24*60*60) - floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)*3600 -       (floor((((f.actual_completion_date-f.actual_start_date) *24*60*60) -       floor(((f.actual_completion_date-f.actual_start_date)        *24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference, p.concurrent_program_name concurrent_program_name, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase, f.status_code from apps.fnd_concurrent_programs p,      apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null order by     f.actual_start_date desc;

--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request.. **************************************************************   SELECT a.request_id, d.sid, d.serial#, 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';

--Query 7: By using below Concurrent Manager and Program rules... --Gives Detail of the Concurrent_queue_name and User_concurrent_program_name

SELECT b.concurrent_queue_name, c.user_concurrent_program_name FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c WHERE a.queue_application_id = 283 and a.concurrent_queue_id = b.concurrent_queue_id and a.type_id = c.concurrent_program_id order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code; --Query 8: Gives Details of Running Concurrent jobs SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME, round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time, a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date, (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end, (a.actual_start_date-a.request_date)*24*60*60 AS lag_time, d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority FROM    apps.fnd_concurrent_requests a,    apps.fnd_concurrent_programs b , apps.FND_CONCURRENT_PROGRAMS_TL c,   apps.fnd_user d WHERE   a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND a.requested_by=d.user_id AND status_code='R' order by Process_time desc; -- Query 9: Gives detail of Concurrent job completed and pending SELECT FCR.REQUEST_ID ,FCPV.USER_CONCURRENT_PROGRAM_NAME ,FCPV.CONCURRENT_PROGRAM_NAME ,FCPV.CONCURRENT_PROGRAM_ID ,FCR.STATUS_CODE ,FCR.PHASE_CODE FROM FND_CONCURRENT_PROGRAMS_VL FCPV ,FND_EXECUTABLES FE ,SYS.DBA_DEPENDENCIES DD ,FND_CONCURRENT_REQUESTS FCR WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID AND FE.EXECUTION_METHOD_CODE = 'I' AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME) AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required --AND referenced_owner = 'XXCUS' AND DD.REFERENCED_NAME = UPPER('&Package_name') AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID AND fcr.phase_code NOT IN ( 'C','P');

-- Query 10:Gives Detail of Running and Completed Concurrent jobs with Start date and end date -- Latest one at the top

select f.request_id , pt.user_concurrent_program_name user_conc_program_name, f.actual_start_date start_on, f.actual_completion_date end_on, floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)       || ' HOURS ' || floor((((f.actual_completion_date-f.actual_start_date)       *24*60*60) - floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)*3600)/60)       || ' MINUTES ' || round((((f.actual_completion_date-f.actual_start_date)       *24*60*60) - floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)*3600 -       (floor((((f.actual_completion_date-f.actual_start_date) *24*60*60) -       floor(((f.actual_completion_date-f.actual_start_date)        *24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference, p.concurrent_program_name concurrent_program_name, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase, f.status_code from apps.fnd_concurrent_programs p,      apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null order by     f.actual_start_date desc;

--- Query 11 wait events details related with Concurrent programs SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username, s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server, s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal, UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id, s.prev_child_number, s.prev_exec_start, s.prev_exec_id, s.plsql_entry_object_id, s.plsql_entry_subprogram_id, s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash, s.action, s.action_hash, s.client_info, s.fixed_table_sequence, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over, s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status, s.current_queue_duration, s.client_identifier, s.blocking_session_status, s.blocking_instance, s.blocking_session, s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#, s.wait_class, s.wait_time, s.seconds_in_wait, s.state, s.wait_time_micro, s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name, s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr, s.creator_serial# FROM gv$session s WHERE ( (s.username IS NOT NULL) AND (NVL (s.osuser, 'x') <> 'SYSTEM') AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE' ) ORDER BY "PROGRAM";

-- Query 12 To find the pid of the Concurrent job and kill it. Select a.inst_id, sid, b.spid from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665' AND a.inst_id = b.inst_id and c.os_process_id = a.process;

-- Query 13:To find the Database SID of the Concurrent job -- We need our concurrent request ID as an input. -- c.SPID= is the operating system process id -- d.sid= is the Oracle process id

SQL> column process heading "FNDLIBR PID" 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';

Qurry to find out scheduled concurrent requests:

SELECT A.REQUEST_ID, B.USER_NAME, C.USER_CONCURRENT_PROGRAM_NAME, C.ARGUMENT_TEXT FROM FND_CONCURRENT_REQUESTS A, FND_USER B, FND_CONC_REQ_SUMMARY_V C WHERE B.USER_ID = A.REQUESTED_BY AND A.REQUEST_ID = C.REQUEST_ID AND A.REQUESTED_START_DATE > SYSDATE AND A.HOLD_FLAG = 'N' AND A.STATUS_CODE IN ('Q','I') AND B.USER_NAME LIKE '%' AND A.DESCRIPTION LIKE '%' ORDER BY A.REQUEST_ID;

Concurrent Program History: ---

SELECT f.request_id , pt.user_concurrent_program_name user_conc_program_name, f.actual_start_date start_on, f.actual_completion_date end_on, f.argument_text Parameters, floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)       || ' HOURS ' || floor((((f.actual_completion_date-f.actual_start_date)       *24*60*60) - floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)*3600)/60)       || ' MINUTES ' || round((((f.actual_completion_date-f.actual_start_date)       *24*60*60) - floor(((f.actual_completion_date-f.actual_start_date) *24*60*60)/3600)*3600 -       (floor((((f.actual_completion_date-f.actual_start_date) *24*60*60) -       floor(((f.actual_completion_date-f.actual_start_date)        *24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference, p.concurrent_program_name concurrent_program_name, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase, f.status_code from apps.fnd_concurrent_programs p,      apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null and pt.user_concurrent_program_name like '&PROGRAM_NAME' order by     f.actual_start_date desc; select fcr.request_id, fcp.user_concurrent_program_name, ou.organization_id, ou.Name OU_NAME, fcr.actual_start_date, fcr.actual_completion_date, (fcr.actual_completion_date-fcr.actual_start_date)*24*60 TOTAL_RUN_TIME, fcr.argument_text Parameters from apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs_tl fcp, apps.hr_operating_units ou where fcr.concurrent_program_id = fcp.concurrent_program_id and fcp.user_concurrent_program_name = 'FedEx Create Accounting Extract' and ou.organization_id(+) = fcr.argument3 and fcr.completion_text = 'Normal completion' order by ou.organization_id, fcr.actual_start_date desc;

History of the Concurrent Program: -- select * from ( SELECT fcr.parent_request_id parent_request_id   , fcr.REQUEST_ID request_id     , case when fcpt.user_concurrent_program_name = 'Report Set'                        then fcr.description                                                else fcpt.user_concurrent_program_name                   end program_name     , TO_CHAR(fcr.ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') start_time     , TO_CHAR(fcr.ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') end_time                , TO_CHAR(fcr.REQUESTED_START_DATE,'MM/DD/YY HH:MI:SS') requested_start_time                , ROUND((fcr.ACTUAL_START_DATE - fcr.REQUESTED_START_DATE)*(60*24),2) wait_time     , ROUND((fcr.ACTUAL_COMPLETION_DATE - fcr.ACTUAL_START_DATE)*(60*24),2) run_time     , SUBSTR(fcr.completion_text,1,100) completion_text	 , fcr.argument_text Parameters  FROM apps.fnd_concurrent_requests fcr     , apps.fnd_concurrent_programs_tl fcpt WHERE fcr.program_application_id = fcpt.application_id AND fcr.concurrent_program_id = fcpt.concurrent_program_id AND fcr.actual_start_date > sysdate - 7 AND (fcpt.user_concurrent_program_name like '%&v_conc_program_name%' or fcr.description like '%&&v_conc_program_name%') ) ORDER by wait_time desc;

Currently Running Concurrent Requests: -- SET PAGESIZE 9999; SET VERIFY off; SET FEEDBACK off; SET LINESIZE 132; COLUMN concreq HEADING 'Conc Req ID' FORMAT A12; COLUMN clproc HEADING 'Client|Process' FORMAT A11; COLUMN opid HEADING 'ORACLE PID' FORMAT A10; COLUMN reqph HEADING 'Req Phase' FORMAT A10; COLUMN reqst HEADING 'Req Status' FORMAT A10; COLUMN dbuser HEADING 'DB User' FORMAT A10; COLUMN svrproc HEADING 'Srvr|Process' FORMAT A10; COLUMN sid HEADING 'SID' FORMAT 99999; COLUMN serial# HEADING 'Serial#' FORMAT 99999; 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(+); /

Concurrent requests in a Request Set -- SELECT USER_CONCURRENT_PROGRAM_NAME FROM fnd_concurrent_programs_tl WHERE CONCURRENT_PROGRAM_ID IN         (SELECT concurrent_program_id             FROM FND_REQUEST_SET_PROGRAMS            WHERE request_set_id =                     (SELECT request_set_id FROM FND_REQUEST_SETS_TL WHERE upper(user_request_set_name) = upper('&Request Set'))); Request Set of the given Request -- SELECT DISTINCT user_request_set_name FROM FND_REQUEST_SETS_TL WHERE request_set_id IN         (SELECT request_set_id             FROM FND_REQUEST_SET_PROGRAMS            WHERE concurrent_program_id =                     (SELECT CONCURRENT_PROGRAM_ID FROM fnd_concurrent_programs_tl WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Request Name')));

Requests on Hold by CRM --                  select request_id Reqst, Oracle_Username Orcl, Priority, Concurrent_Program_Name Program, To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date, To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At, Run_Alone_Flag, Single_Thread_Flag, Fcp.Enabled_Flag from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs Fcp, fnd_oracle_userid O where Status_Code = 'Q' And ( Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And Program_Application_ID = Application_ID ) And Hold_Flag = 'N' And Fcr.Oracle_ID = O.Oracle_ID And Requested_Start_Date <= Sysdate Order By Requested_Start_Date Asc, Decode(Priority, Null, 9999999, Priority) Asc, Request_ID Asc ;

Concurrent requests Completed with error:

select p.user_concurrent_program_name Program_name, to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec, count(r.request_id)    Executions, avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time, min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time, max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time from apps.fnd_concurrent_requests r,   apps.fnd_concurrent_processes c,    apps.fnd_concurrent_queues q,    apps.fnd_concurrent_programs_vl p where p.concurrent_program_id = r.concurrent_program_id and p.application_id = r.program_application_id and c.concurrent_process_id = r.controlling_manager and q.concurrent_queue_id = c.concurrent_queue_id --and p.application_id = '&appl_id' and r.status_code = 'E'   and r.phase_code = 'C'     and to_char(r.actual_start_date,'DD-MON-YYYY')='20-NOV-2013' group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY') order by to_date(r.actual_start_date,'DD-MON-YYYY') asc;

Codes: --

PHASE CODE: I = Inactive P = Pending R = Running C = Completed

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

Child requests based on the parent request id: -- SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/ fcr.request_id "Request ID", fcptl.user_concurrent_program_name "Program Name", fcr.phase_code, fcr.status_code, --    to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted", --    (fcr.actual_start_date - fcr.request_date)*1440 "Delay", To_char(fcr.actual_start_date,                                'DD-MON-YYYY HH24:MI:SS')      "Start Time", To_char(fcr.actual_completion_date,                                'DD-MON-YYYY HH24:MI:SS') "End Time", ( fcr.actual_completion_date -                                  fcr.actual_start_date ) * 1440 "Elapsed", fcr.oracle_process_id "Trace ID" FROM  (SELECT /*+ index (fcr1 fnd_concurrent_requests_n3) */ fcr1.request_id         FROM   apps.fnd_concurrent_requests fcr1         WHERE  1 = 1         START WITH fcr1.request_id = &parent_request_id         CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,        apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs fcp, apps.fnd_concurrent_programs_tl fcptl WHERE fcr.request_id = x.request_id AND fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.program_application_id = fcp.application_id AND fcp.application_id = fcptl.application_id AND fcp.concurrent_program_id = fcptl.concurrent_program_id AND fcptl.LANGUAGE = 'US' ORDER BY 1;