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;