Script to Put Concurrent Requests on Hold

   During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.

This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.

Below are the scripts to put Scheduled concurrent requests on hold and to monitor the running requests .

1. List out the Jobs which were already on HOLD.

select request_id, phase_code, status_code from fnd_concurrent_requests where hold_flag = 'Y';
 
Connect as apps user, Drop old table (jobs_already_on_hold) if required .

drop table jobs_already_on_hold;

2. Create backup of the table with the jobs which were already ON HOLD.
​
create table jobs_already_on_hold as (select request_id, phase_code, status_code from fnd_concurrent_requests where hold_flag = 'Y');

select count(*) from jobs_already_on_hold;

select * from jobs_already_on_hold;

3.Now, Place the pending jobs on HOLD using the below update command. 

update fnd_concurrent_requests set hold_flag = 'Y'  where phase_code ='P' and hold_flag ='N';

commit;

 After Putting the Jobs ON HOLD, now verify if any Running Requests with PAUSED Status.
 If there are any Running requests with PAUSED status,
 please verify the child requests status of this Program. 
 If there were any put on HOLD as part of the putting Jobs on Hold process.
 Release them from HOLD and process the requests.

4.Please ensure to check the below query for every 5 minutes until it returns 0 rows.
 List the pending ,running and passed requests

SELECT REQUEST_ID,
      PHASE_CODE,
      STATUS_CODE,
      HAS_SUB_REQUEST,
      IS_SUB_REQUEST,
      hold_flag,
      REQ_INFORMATION
 FROM apps.fnd_concurrent_requests
WHERE    (phase_code = 'P' AND hold_flag = 'N')
      OR phase_code = 'R'
      OR status_code = 'W';

set echo off pages 100 lines 202
column REQUEST heading 'Request' format a9
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(e)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,9)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'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',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,
substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,
s.sid,s.serial#
--substr(fcr.os_process_id,1,7)OS_PIDa
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,
v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;
 
If above queries return 0 then we can proceed with our activity i.e we can bring down the concurrent managers .
And once activity is done ,Perform complete sanity then release the Jobs from Hold by using below commands .

Release jobs script:

Connect as apps user and perform the below

update fnd_concurrent_requests set hold_flag = 'N' where hold_flag = 'Y' and request_id not in (select request_id from jobs_already_on_hold);

commit;