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.
1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests
where PHASE_CODE='P' and hold_flag='N';
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and
hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);
Or
3)update apps.fnd_concurrent_requests set hold_flag =
'Y', last_update_date = sysdate, last_updated_by = -1 where phase_code = 'P';
NOTE: You have to commit if select & update are same number of records.
Otherwise rollback and try gain till the numbers are same
4) Commit;
To Release hold on Concurrent Requests patching, run the below sql :
5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select
request_id from apps.conc_req_on_hold);
Or
5)update apps.fnd_concurrent_requests a set
a.hold_flag = 'N' , last_update_date = sysdate, last_updated_by = -1 where
phase_code = 'P' and a.hold_flag = 'Y' and last_updated_by = -1and
last_update_date > sysdate-1;
6)Commit the changes
commit;