Tuesday, 24 June 2014

API script to Disable the enabled Responsibilities for a User

/* Formatted on 6/24/2014 12:34:59 PM (QP5 v5.115.810.9015) */
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 NOT NULL
            AND fu.user_id = :p_user_id;
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 => NULL,
                                                     description => NULL
         );
         COMMIT;
      END IF;
   END LOOP;
END;

No comments:

Post a Comment