Skip to content

Oracle EBS Query to End date the responsibilities of all end dated users

Sometimes users are end-dated but the responsibilities assigned to specific users are not end-dated. Below SQL code can be used to identify all such users and end date the responsibilities of all end dated users

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;
Brijesh Gogia
Leave a Reply