Adding
Responsibilities to User and setting the profile options from PL/SQL Script.
In this
article we shall see how to:-
1. add multiple responsibilities to a user in EBS
2. Set profile option value at user
level.
DECLARE
l_needed_resp varchar2(4000):= 'Alert Manager#
Application Administrator#
Functional Administrator#
System
Administration#
System
Administrator#
Workflow Administrator Web (New)#
XML
Publisher Administrator#';
CURSOR cur IS
WITH qry AS (
SELECT l_needed_resp resp_string
FROM dual
)
SELECT trim(replace(regexp_substr (resp_string, '[^#]+', 1, ROWNUM),chr(10),'')) resp
FROM qry
CONNECT BY LEVEL <= LENGTH(regexp_replace (resp_string, '[^#]+')) + 1
;
lc_user_name VARCHAR2(100) :=
'XXTEST';
lc_resp_appl_short_name VARCHAR2(100) ;
lc_responsibility_key VARCHAR2(100) ;
lc_security_group_key VARCHAR2(100) ;
ld_resp_start_date DATE := TO_DATE('11-SEP-2016');
ld_resp_end_date DATE := NULL;
l_success BOOLEAN;
l_user_id NUMBER;
BEGIN
--
-- This will set value of
profile option 'ONT_DEBUG_LEVEL' at User level to 1
--
select user_id
into l_user_id
from fnd_user
where user_name = lc_user_name;
--FND: Diagnostics
l_success := FND_PROFILE.save
( x_name => 'FND_DIAGNOSTICS'
, x_value => 'Y'
, x_level_name => 'USER'
, x_level_value => l_user_id -- Given User Id
, x_level_value_app_id => NULL
) ;
COMMIT;
IF l_success
THEN
DBMS_OUTPUT.put_line('FND: Diagnostics Profile
Updated successfully at User Level');
ELSE
DBMS_OUTPUT.put_line('FND: Diagnostics Profile
Update Failed at User Level. Error:'||sqlerrm);
END IF;
-- Utilities:Diagnostics
l_success := FND_PROFILE.save
( x_name => 'DIAGNOSTICS'
, x_value => 'Y'
, x_level_name => 'USER'
, x_level_value => l_user_id -- Given User Id
, x_level_value_app_id => NULL
) ;
COMMIT;
IF l_success
THEN
DBMS_OUTPUT.put_line('Utilities:Diagnostics Profile
Updated successfully at User Level');
ELSE
DBMS_OUTPUT.put_line('Utilities:Diagnostics Profile
Update Failed at User Level. Error:'||sqlerrm);
END IF;
-- Personalize Self-Service
Defn
l_success := FND_PROFILE.save
( x_name => 'FND_CUSTOM_OA_DEFINTION'
, x_value => 'Y'
, x_level_name => 'USER'
, x_level_value => l_user_id -- Given User Id
, x_level_value_app_id => NULL
) ;
COMMIT;
IF l_success
THEN
DBMS_OUTPUT.put_line('Personalize Self-Service Defn
Profile Updated successfully at User Level');
ELSE
DBMS_OUTPUT.put_line('Personalize Self-Service Defn
Profile Update Failed at User Level. Error:'||sqlerrm);
END IF;
-- FND: Personalization
Region Link Enabled
l_success := FND_PROFILE.save
( x_name => 'FND_PERSONALIZATION_REGION_LINK_ENABLED'
, x_value => 'Y'
, x_level_name => 'USER'
, x_level_value => l_user_id -- Given User Id
, x_level_value_app_id => NULL
) ;
COMMIT;
IF l_success
THEN
DBMS_OUTPUT.put_line('FND: Personalization Region
Link Enabled Profile Updated successfully at User Level');
ELSE
DBMS_OUTPUT.put_line('FND: Personalization Region
Link Enabled Profile Update Failed at User Level. Error:'||sqlerrm);
END IF;
FOR rec IN cur LOOP
BEGIN
dbms_output.put_line('Resp:' || rec.resp);
SELECT frv.responsibility_key, fa.application_short_name,
(SELECT security_group_key
FROM fnd_security_groups
WHERE security_group_id IN (
SELECT data_group_id
FROM fnd_responsibility_vl frv,
fnd_application fa
WHERE responsibility_key = frv.responsibility_key
AND frv.application_id = fa.application_id
AND fa.application_short_name = fa.application_short_name))
security_group
INTO lc_responsibility_key,lc_resp_appl_short_name,lc_security_group_key
FROM fnd_responsibility_vl frv, fnd_application fa
WHERE responsibility_name = rec.resp
AND frv.application_id = fa.application_id;
DBMS_OUTPUT.PUT_LINE
( 'lc_responsibility_key = ' || lc_responsibility_key );
DBMS_OUTPUT.PUT_LINE
( 'lc_resp_appl_short_name = ' || lc_resp_appl_short_name );
DBMS_OUTPUT.PUT_LINE
( 'lc_security_group_key = ' || lc_security_group_key );
BEGIN
fnd_user_pkg.addresp
( username => lc_user_name,
resp_app => lc_resp_appl_short_name,
resp_key => lc_responsibility_key,
security_group => lc_security_group_key,
description => NULL,
start_date => ld_resp_start_date,
end_date => ld_resp_end_date
);
DBMS_OUTPUT.PUT_LINE('************ADDED RESP');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END;
/
As you can
see that you can specify multiple responsibilities to be attached using “#” and
new line delimiter as defined by “l_needed_resp” PL/SQL variable.
This script
will speedup the process of setting up the user responsibility and needed profile
option values.
