Sometimes users are end-dated but the responsibilities assigned to specific users are not end-dated. Below SQL code can be used to identify all such users and end date the responsibilities of all end dated users
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;
Latest posts by Brijesh Gogia (see all)
- Building a Scalable and Secure AI Infrastructure in the Cloud - May 12, 2024
- Harnessing the Power of AI in the Cloud for Business Transformation - March 2, 2024
- Building a Resilient Cybersecurity Framework in the Oracle Cloud - January 16, 2024