Deactivate responsibility of single or multiple users?

In this forum Oracle Applications DBA's, System Administrators & Developers can share their knowledge/issues.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Deactivate responsibility of single or multiple users?

Post by admin »

DECLARE
-- ERPstuff sample code to deactivate responsibility of single or multiple users
-- www.erpstuff.com/forums

v_user_name VARCHAR2 (100) := 'SIKANDAR';
v_responsibility_name VARCHAR2 (100) := 'General Ledger';
v_application_name VARCHAR2 (100) := NULL;
v_responsibility_key VARCHAR2 (100) := NULL;
v_security_group VARCHAR2 (100) := NULL;
BEGIN
SELECT fa.application_short_name,
fr.responsibility_key,
frg.security_group_key
INTO v_application_name, v_responsibility_key, v_security_group
FROM fnd_responsibility fr,
fnd_application fa,
fnd_security_groups frg,
fnd_responsibility_tl frt
WHERE fr.application_id = fa.application_id
AND fr.data_group_id = frg.security_group_id
AND fr.responsibility_id = frt.responsibility_id
AND frt.LANGUAGE = USERENV ('LANG')
AND frt.responsibility_name = v_responsibility_name;

FOR i
IN ( SELECT f.user_name,
APPLICATION_ID,
rn.responsibility_name,
r.responsibility_id,
r.start_date resp_start_date,
r.end_date resp_end_date
FROM apps.fnd_user_resp_groups_direct r,
apps.fnd_responsibility_vl rn,
fnd_user f
WHERE r.user_id = f.user_id
AND r.responsibility_id = rn.responsibility_id
AND rn.responsibility_name = v_responsibility_name
AND f.end_date IS NULL
AND f.start_date > '01-JAN-2010'
AND r.end_date IS NULL
AND f.user_name = v_user_name
ORDER BY 1)
LOOP
fnd_user_pkg.delresp (username => i.user_name,
resp_app => v_application_name,
resp_key => v_responsibility_key,
security_group => v_security_group);
END LOOP;

COMMIT;

DBMS_OUTPUT.put_line (
'Responsiblity '
|| v_responsibility_name
|| ' is revoked from the user '
|| v_user_name);

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'While processing user ' || v_user_name || ' error: ' || SQLERRM);
END;
Post Reply

Who is online

Users browsing this forum: Google Adsense [Bot] and 3 guests