Various scripts used with Concurrent Manager/Requests
a. To list all running programs
SELECT FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') PHASE
,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
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV('Lang')
AND FCR.PHASE_CODE = 'R'
ORDER BY FCR.ACTUAL_START_DATE DESC
b. To list all pending programs
SELECT FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') PHASE
,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
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV('Lang')
AND FCR.PHASE_CODE = 'P'
ORDER BY FCR.ACTUAL_START_DATE DESC
c. To list all programs that are scheduled and yet to run
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date, cr.PHASE_CODE, cr.STATUS_CODE
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
--AND trunc(cr.requested_start_date) >= trunc(SYSDATE)
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND cr.release_class_id is not null
order by name, requested_start_date;
d. To list avg / min / max Execution time / Wait time and number of executions for a given program for a given date range
select c.CONCURRENT_PROGRAM_ID,
q.concurrent_queue_name qname,
c.concurrent_program_name||' - '||
ctl.user_concurrent_program_name "Program"
,to_char(min(actual_start_date),'mm/dd/rr') earlieststart
,sum(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(tot)"
,avg(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(avg)"
,min(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(min)"
,max(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(max)"
,count(*) "Jobs"
,ls.meaning lsm
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_concurrent_programs_tl ctl,
APPLSYS.fnd_lookup_values ls,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q
where c.concurrent_program_id = &cmprogid
and a.concurrent_program_id = c.concurrent_program_id
and a.controlling_manager = b.concurrent_process_id
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 a.program_application_id = c.application_id
and ctl.application_id = c.application_id
and ls.lookup_type = 'CP_STATUS_CODE'
and ls.language = 'US'
and ls.enabled_flag = 'Y'
and (ls.start_date_active <= sysdate and ls.start_date_active is not null)
and (ls.end_date_active > sysdate or ls.end_date_active is null)
and a.status_code || '' = ls.lookup_code
and a.phase_code || '' = 'C'
group by c.CONCURRENT_PROGRAM_ID ,q.concurrent_queue_name, c.concurrent_program_name
,ctl.user_concurrent_program_name
,ls.meaning
order by 4 desc
e. To get the input the Concurrent request number and if it is running then give the Oracle Sid, serial# , unix process id, along with program or module it is linked with
SELECT d.sid, d.serial# ,d.process , c.SPID, fcpv.user_concurrent_program_name, a.REQUEST_ID, fa.APPLICATION_NAME,
d.osuser, replace(d.machine,'HEADLANDS\',null) user_srvr, a.REQUESTED_START_DATE
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d,
fnd_concurrent_programs_vl fcpv,
fnd_application_tl fa
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'
AND fcpv.concurrent_program_id = a.CONCURRENT_PROGRAM_ID
AND a.RESPONSIBILITY_APPLICATION_ID = fa.APPLICATION_ID
f. List all programs that can be run by a given userSELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application,
FCPT.USER_CONCURRENT_PROGRAM_NAME
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE g.user_id(+) = u.user_id
AND u.USER_NAME = '&USER_NAME'
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND FCP.APPLICATION_ID = A.APPLICATION_ID
AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
ORDER BY responsiblity, application, FCPT.USER_CONCURRENT_PROGRAM_NAME
g. List all programs that are defined as incompatible for a given programSELECT FAL.APPLICATION_NAME INCOMPATIBLE_APPLICATION , FCPT.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_PROGRAM
FROM FND_CONCURRENT_PROGRAM_SERIAL FCPS,
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION_TL FAL,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPS.TO_RUN_APPLICATION_ID = FCP.APPLICATION_ID
AND FCPS.TO_RUN_CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCPS.RUNNING_CONCURRENT_PROGRAM_ID IN (SELECT FCPT.CONCURRENT_PROGRAM_ID FROM FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPT.USER_CONCURRENT_PROGRAM_NAME = :USER_CONCURRENT_PROGRAM_NAME)
AND FAL.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FCPS.TO_RUN_CONCURRENT_PROGRAM_ID
h. Given a program name in which manager it will runselect ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
from Fnd_Concurrent_Requests t,
FND_CONCURRENT_PROCESSES k,
Fnd_Concurrent_Queues_TL QTL,
Fnd_Concurrent_Programs_TL PTL
where k.concurrent_process_id = t.controlling_manager
and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
and ptl.concurrent_program_id=t.concurrent_program_id
and qtl.language='US'
and PTL.USER_CONCURRENT_PROGRAM_NAME = :CONCURRENT_PROGRAM_NAME
ORDER BY ptl.user_concurrent_program_name DESC
To Identify the manager for a given concurrent program The script given below identifies which manager is going to execute a given Concurrent program:
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND
NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND
NVL (cqc.queue_application_id, 0) = cq.application_id AND
cq.LANGUAGE = 'US'
To identify long running concurrent requests for more than 1 hour.
set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
fu.user_name "Username",
round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs",
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
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,
fnd_concurrent_programs_vl fcp,
fnd_user fu
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 req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
AND fu.user_id = req.requested_by
AND round((sysdate - actual_start_date) * 24) > 1;
