How to Cancel, Terminate and Hold FND Concurrent Requests

Before cancelling any concurrent requests, first have a look on Status_Code and Phase_Code Columns from FND_CONCURRENT_REQUESTS table.

Table Name : FND_CONCURRENT_REQUESTS

STATUS_CODE Column:

A Waiting

B Resuming

C Normal

D Cancelled

E Error

F Scheduled

G Warning

H On Hold

I Normal

M No Manager

Q Standby

R Normal

S Suspended

T Terminating

U Disabled

W Paused

X Terminated

Z Waiting.

PHASE_CODE column.

C Completed

I Inactive

P Pending

R Running    

# Scenarios to Cancel Concurrent Request :

IF Status_Code of Request in 'R':

UPDATE fnd_concurrent_requests

       set phase_code =  'C',

           status_code = 'T',

           completion_text = 'Reason for Cancel',

           last_update_date = sysdate,

           last_updated_by = fnd_global.user_id

WHERE request_id = req_id;

 

Commit;

If the Status_code of Request in ('W', 'B') :

UPDATE fnd_concurrent_requests

       set phase_code =  'C',

           status_code = 'X',

           completion_text = 'Reason for Cancel',

           last_update_date = sysdate,

           last_updated_by = fnd_global.user_id

WHERE request_id = req_id;

 

Commit;

If the Status_code of Request something else  : 

UPDATE fnd_concurrent_requests

       set phase_code =  'C',

           status_code = 'D',

           completion_text = 'Reason for Cancel',

           last_update_date = sysdate,

           last_updated_by = fnd_global.user_id

WHERE request_id = req_id;

 

Commit;

Cancel Child Concurrent Request ID:

UPDATE fnd_concurrent_requests

     set phase_code = decode (status_code, 'R', 'R', 'C'),

status_code = decode (phase_code, 'R', decode (status_code, 'R', 'T', 'X'),'D'),

last_update_date = sysdate,

completion_text = 'Reason for Cancel',

last_updated_by = fnd_global.user_id

WHERE request_id in (select request_id

  from fnd_concurrent_requests

                  where phase_code <> 'C' and status_code <> 'T'

  connect by prior request_id = parent_request_id

  start with request_id =  & rid)

 

Commit;

# Hold Concurrent Request :

UPDATE fnd_concurrent_requests

       set hold_flag = 'Y',

           last_update_date = sysdate,

           last_updated_by = fnd_global.user_id

WHERE request_id = req_id;

 

Commit;

# Hold Scheduled Concurrent Requests:

UPDATE fnd_concurrent_requests

SET hold_flag = 'Y'

WHERE phase_code = 'P'

AND status_code in ('Q','I');

 

Commit;

Also Oracle is suggesting to explore the usage of private API FND_AMP_PRIVATE.TOGGLE_REQUEST_HOLD which does direct updates as stated above.

For e.g. to cancel the concurrent request :

fnd_amp_private.cancel_request(your_request_id, lv_message);