Introduction
This Post illustrates the steps required to through API(Application program interface) end date the user and their responsibilities in Oracle EBS R12.
Script to through API end date the user and their responsibilities
DECLARE
CURSOR User_end is
select user_name from fnd_user where user_name in (SELECT user_name FROM irm_empl_issue
WHERE 1 =1
AND attribute1 IS NULL
AND attribute2 IS NULL
AND attribute3 IS NULL
AND attribute4 IS NULL
AND color =’R’);
CURSOR c1(C_USER_NAME VARCHAR2)
IS
SELECT fu.user_name,
fa.application_short_name,
frt.responsibility_name,
fr.responsibility_key,
fsg.security_group_key
FROM fnd_user_resp_groups_all ful,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_responsibility fr,
fnd_security_groups fsg,
fnd_application fa
WHERE fu.user_id = ful.user_id
AND frt.responsibility_id = ful.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fsg.security_group_id = ful.security_group_id
AND fa.application_id = ful.responsibility_application_id
AND frt.language = ‘US’
AND fu.user_name = C_USER_NAME;
BEGIN
For i in user_end loop
For j in c1 (i.USER_NAME)
loop
BEGIN
fnd_user_pkg.delresp (username => j.user_name,
resp_app => j.application_short_name,
resp_key => j.responsibility_key,
security_group => j.security_group_key);
COMMIT;
DBMS_OUTPUT.
put_line (
j.responsibility_name || ‘ has been End Dated Successfully !!!’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
‘Inner Exception: ‘
|| ‘ – ‘
|| j.responsibility_key
|| ‘ – ‘
|| SQLERRM);
END;
END LOOP;
begin
fnd_user_pkg.updateuser
( x_user_name => i.user_name,
x_owner => NULL,
x_unencrypted_password => NULL,
x_start_date => NULL,
x_end_date => SYSDATE,
x_description =>’Duplicate User or User No Longer log on’,
x_password_date => NULL,
x_password_lifespan_days => NULL,
x_employee_id => NULL,
x_email_address => NULL
);
DBMS_OUTPUT.put_line(‘User’||i.user_name||’End Dated’);
UPDATE irm_empl_issue SET ATTRIBUTE5=’User End dated’ where user_name=i.user_name;
commit;
exception when others then
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UPDATE irm_empl_issue SET ATTRIBUTE5=’ERROR’ where user_name=i.user_name;
commit;
end ;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
commit;
END;
/
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
