1. Create a temp table to hold all request IDs
SQL> create table apps.FND_CONC_REQ_S_V_onhold as
select decode(phase_code,
'P','Pending',
'Running') Phase_code,
decode(status_code,
'R', 'Normal',
'W', 'Paused',
'Q', 'Standby',
'I', 'Scheduled') Status_code,
decode(hold_flag,'Y','Yes','No') Hold_flag,
request_id,
substr(requestor,1,10) usern_name,
to_char(requested_start_date,'MM/DD/YYYY HH24:MI:SS') req_start_date,
substr(program,1,50) program
from FND_CONC_REQ_SUMMARY_V
where phase_code in ('P') -- in Pending
and status_code != 'W' -- not in Paused
and hold_flag != 'Y' -- not on hold
and to_date(requested_start_date,'DD-MON-YY')
between to_date(sysdate,'DD-MON-YY')
and to_date(sysdate+2,'DD-MON-YY')
order by phase_code desc, requested_start_date, request_id
;
2. Update table fnd_concurrent_requests to hold them
SQL> UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
and status_code in ('Q','I') -- Standby or Scheduled
and hold_flag = 'N'
and request_id in (
select request_id from apps.FND_CONC_REQ_S_V_onhold);
3. Check/confirm any running requests before shutting down concurrent managers, if you like.
SQL> SELECT decode(phase_code,
'P','Pending',
'Running') Phase_code,
decode(status_code,
'R', 'Normal',
'W', 'Paused',
'Q', 'Standby',
'I', 'Scheduled') Status_code,
decode(hold_flag,'Y','Yes','No') Hold_flag,
request_id,
substr(requestor,1,10) usern_name,
to_char(requested_start_date,'MM/DD/YYYY HH24:MI:SS') req_start_date,
to_char(actual_start_date,'MM/DD/YYYY HH24:MI:SS') actual_start_date,
substr(program,1,50) program
FROM fnd_conc_req_summary_v
WHERE phase_code in ('R', 'P') order by program;
4. After maintenance, put on-hold requests back to scheduler
SQL> UPDATE fnd_concurrent_requests
SET hold_flag = 'N'
WHERE phase_code = 'P'
and status_code in ('Q','I') -- Standby or Scheduled
and hold_flag = 'Y'
and request_id in (
select request_id from apps.FND_CONC_REQ_S_V_onhold);
SQL> drop table apps.FND_CONC_REQ_S_V_onhold; -- optional
UPDATE in 2022: In R12.2, when used below line to hold concurrent jobs for a period of time for database upgrade, and after upgrade logged onto GUI forms to cancel the HOLD, some concurrent jobs got "No Manager" status.
SQL> update apps.fnd_concurrent_requests
set hold_flag = 'Y'
where status_code in ('Q','I') and hold_flag = 'N';
The problem is column EDITION_NAME of table fnd_concurrent_requests was populated with invalid data. The fix is to find the current edition and run an update to the column.
SQL> select edition_name from all_editions;
EDITION_NAME
-----------------------
ORA$BASE
SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SQL> update fnd_concurrent_requests
set edition_name='ORA$BASE'
where request_id in (228633x, 228633x, 228632x, 228632x, 228624x, 228624x)
;
Concurrent Processing: What to Do When Concurrent Requests Are Not Processing and Have Inactive No Manager Status? (Doc ID 1311526.1)
