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;