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;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

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

Related posts: