Script to End Date Responsibility for a User

This script below will get the list of all inactive users and end date all the responsibilities assigned to them.

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;