This script below will get the list of all inactive users and end date all the responsibilities assigned to them.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
Declare –cursor to get all inactive users CURSOR cur_inactive_user IS select fu.user_id, fd.responsibility_id, fd.responsibility_application_id, fd.security_group_id, fd.start_date, fd.end_date from fnd_user fu, fnd_user_resp_groups_direct fd where fu.user_id = fd.user_id and (fu.end_date <= sysdate or fu.end_date is NOT NULL) and fd.end_date is null; BEGIN FOR rec_inactive_user IN cur_inactive_user LOOP –checking if the responsibility is assigned to the user IF (fnd_user_resp_groups_api.assignment_exists (rec_inactive_user.user_id, rec_inactive_user.responsibility_id, rec_inactive_user.responsibility_application_id, rec_inactive_user.security_group_id)) then — Call API to End date the responsibility fnd_user_resp_groups_api.update_assignment (user_id => rec_inactive_user.user_id, responsibility_id => rec_inactive_user.responsibility_id, responsibility_application_id => rec_inactive_user.responsibility_application_id, security_group_id => rec_inactive_user.security_group_id , start_date => rec_inactive_user.start_date , end_date => rec_inactive_user.end_date, description => NULL); COMMIT; END IF; END LOOP; END; |