Business Need:
Reduce the number of EBS Responsibilities (hats) for a given user.
With Minimum Tweaking, you may achieve additional features:
- Stop a single responsibility
from entire business use
- Stop people with a language from
accessing one more responsibilities using EBS Standard Multi Language feature
- User has 100 responsibilities
in Production. Cloning this to a development environment requires only one
Operating Unit and its responsibilities
Standard End Dating of FND User Responsibilities used to work in
older versions (Eg: at least 12.1.3). Now responsibility is moved to
Role/Permission tables and need API calls to control this.
Benefits:
Ease of use and less memory usage
Program / PL/SQL Script:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c1
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 IN ('<user-name>') -- change user name appropriately
-- remove user name, if this is applicable to all users
AND frt.responsibility_name like 'XX%'; -- change resp name appropriately
BEGIN
FOR i IN c1
LOOP
BEGIN
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
COMMIT;
DBMS_OUTPUT.put_line ('User: '||i.user_name||'Resp: '||i.responsibility_name || ' has been End Dated Successfully !!!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (
'Inner Exception: '
|| ' - '
|| i.responsibility_key
|| ' - '
|| SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/
COMMIT;
CURSOR c1
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 IN ('<user-name>') -- change user name appropriately
-- remove user name, if this is applicable to all users
AND frt.responsibility_name like 'XX%'; -- change resp name appropriately
FOR i IN c1
LOOP
BEGIN
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
COMMIT;
DBMS_OUTPUT.put_line ('User: '||i.user_name||'Resp: '||i.responsibility_name || ' has been End Dated Successfully !!!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (
'Inner Exception: '
|| ' - '
|| i.responsibility_key
|| ' - '
|| SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/
Keywords:
Oracle EBS, R12, R12.2.8, Oracle Applications
FND, AOL, Request Group, Responsibility, Security, Access Control, Hat
