Script to End Date Responsibility for a User

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;