1) To hold the specific concurrent program
Hold
update fnd_concurrent_requests set HOLD_FLAG='Y' where PHASE_CODE='P' and CONCURRENT_PROGRAM_ID =
(select CONCURRENT_PROGRAM_ID from FND_CONCURRENT_PROGRAMS_VL where CONCURRENT_PROGRAM_NAME like '%FNDSCURS%');
FNDSCURS ----> Short Name of the concurrent Program
Release
update FND_CONCURRENT_REQUESTS set HOLD_FLAG='N' where PHASE_CODE='P' and CONCURRENT_PROGRAM_ID =
(select CONCURRENT_PROGRAM_ID from FND_CONCURRENT_PROGRAMS_VL where CONCURRENT_PROGRAM_NAME like '%FNDSCURS%');
2) To hold the program for a user
update APPS.FND_CONCURRENT_REQUESTS
set HOLD_FLAG='Y'
where PHASE_CODE='P'
and REQUEST_ID in
(SELECT req.request_id
FROM
apps.fnd_concurrent_programs_tl prg
, apps.fnd_concurrent_requests req
, apps.fnd_user fus
, apps.fnd_conc_release_classes crc
WHERE 1=1
AND prg.application_id = req.program_application_id
AND prg.concurrent_program_id = req.concurrent_program_id
AND req.requested_by = fus.user_id
AND req.phase_code = 'P'
AND req.requested_start_date > sysdate
AND prg.language = 'US'
AND crc.release_class_id(+) = req.release_class_id
and CRC.APPLICATION_ID(+) = REQ.RELEASE_CLASS_APP_ID
and FUS.USER_NAME in ('USER1','USER1'));
To release the jobs
update APPS.FND_CONCURRENT_REQUESTS
set HOLD_FLAG='N'
where PHASE_CODE='P'
and REQUEST_ID in
(SELECT req.request_id
FROM
apps.fnd_concurrent_programs_tl prg
, apps.fnd_concurrent_requests req
, apps.fnd_user fus
, apps.fnd_conc_release_classes crc
WHERE 1=1
AND prg.application_id = req.program_application_id
AND prg.concurrent_program_id = req.concurrent_program_id
AND req.requested_by = fus.user_id
AND req.phase_code = 'P'
AND req.requested_start_date > sysdate
AND prg.language = 'US'
AND crc.release_class_id(+) = req.release_class_id
and CRC.APPLICATION_ID(+) = REQ.RELEASE_CLASS_APP_ID
and FUS.USER_NAME in ('YYY0M29'));
3) To get the all the child request id from parent request ID -- pass parent request id
select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
fcr.requested_by "User",
substr(DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
(fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed Time",
oracle_process_id "Trace File ID" ,
fcr.phase_code "Phase",
fcr.status_code "Status",
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.argument_text "Parameters"
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from 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,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
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;
Terminate the Request as follows
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where request_id='request_id';
