Place all pending concurrent requests on hold

During a long maintenance of multiple tasks, apps services may be brought down and up for two or three times. You may want to hold all scheduled jobs from execution until all tasks are completed.

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)