SQL to keep Concurrent requests on Hold

 

1)Create table apps.conc_req_on_hold_DDMMYY as select fcr.*, fcrv.user_concurrent_program_name from fnd_Concurrent_requests fcr, fnd_concurrent_programs_VL fcrv where fcr.PHASE_CODE='P' and fcr.hold_flag='N' and fcr.requested_start_date > sysdate and fcr.CONCURRENT_PROGRAM_ID=fcrv.CONCURRENT_PROGRAM_ID;

 

2) select count(*) from apps.conc_req_on_hold_DDMMYY

 

3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and requested_start_date > sysdate and request_id in (select request_id from  apps.conc_req_on_hold_DDMMYY);

 

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

 

4) Commit;

 

To Release hold on Concurrent Requests after the maintenance window, 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_DDMMYY);

 

6)Commit the changes

 

commit;