Hold, Un hold and cancel Pending Concurrent Requests

 Here is an easy way to put all the Pending Concurrent requests in hold, if we are going through any situation
(Production Cutover/upgrade and go live scenarios).

1. First create a backup table 

SQL> CREATE TABLE apps.conc_bkp AS SELECT * FROM fnd_concurrent_requests WHERE phase_code = 'P' AND NVL (hold_flag, 'N') <> ‘Y’;

2. Then put the reqs on hold using the following query,

SQL> UPDATE apps.fnd_concurrent_requests SET hold_flag = 'Y' WHERE request_id IN ( SELECT request_id FROM apps.conc_bkp);

SQL> Commit;

3. Then to release the hold use the following query,

SQL> UPDATE fnd_concurrent_requests SET hold_flag = 'N' WHERE request_id IN ( SELECT request_id FROM apps.conc_bkp);

SQL> Commit;

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';

In case of bulk cancellation of concurrent request below query can be used.

update fnd_concurrent_requests set status_code='X', phase_code='C' where request_id in (select request_id from fnd_concurrent_requests where PHASE_CODE='P' and concurrent_program_id='&i' and requested_by = '&j';