A plsql script that adds a specified role to a specified user in OracleEBS

In some EBS projects that focus on permissions, common users usually do not have the SystemAdministrator permission. Instead, DBAs can only add responsibilities to one user and add responsibilities to each user.

In some EBS projects that focus on permissions, common users usually do not have the System Administrator permission. Instead, the DBA can only add one role to one user.

A plsql script that adds a specified role to a specified user in Oracle EBS

[Date: 2012-11-23] Source: Linux community Author: t0nsha [Font:]

In some EBS projects that focus on permissions, common users usually do not have the System Administrator permission. Instead, the DBA can only add one role to one user. It is very troublesome to manually write a PLSQL script, keep the backup. since Oracle 10g does not support the continue statement, the 10g and 11G are written separately.

1. Based on EBS R12.0.6/Oracle DB 10gR2

DECLARE
-- Script to add user responsibility for R12.0/10gR2

TYPE tab_user_list is table of VARCHAR2 (100 );
TYPE arr_resp_list is varray (100) OF VARCHAR2 (100 );

-- User to be changed
L_tab_user_list tab_user_list
: = Tab_user_list ('liao'
, 'Karl'
, 'Xx ');

-- Responsibility to be added
Rochelle r_resp_list arr_resp_list
: = Arr_resp_list ('System Administrator'
, 'Purchasing Super user'
, 'Application Administrator ');

CURSOR cur_user (
Pc_username IN VARCHAR2)
IS
SELECT fu. user_id
, Fu. user_name
FROM fnd_user fu
WHERE fu. user_name = pc_username
And trunc (SYSDATE) between trunc (NVL (fu. start_date, SYSDATE ))
And trunc (NVL (fu. end_date, SYSDATE ));
TYPE tab_user is table of cur_user % ROWTYPE;
Rochelle tab_user;

CURSOR cur_resp (
Pc_resp IN VARCHAR2)
IS
SELECT fa. application_id
, Fa. application_short_name
, Fr. responsibility_id
, Fr. responsibility_name
, Fr. responsibility_key
, Fsg. security_group_key
FROM fnd_application fa
, Fnd_responsibility_vl fr
, Fnd_security_groups fsg
Where lower (fr. responsibility_name) = LOWER (pc_resp)
AND fa. application_id = fr. application_id
AND fr. data_group_id = fsg. security_group_id;
TYPE tab_resp is table of cur_resp % ROWTYPE;
Rochelle tab_resp;

Expt_no_user EXCEPTION;
Expt_no_resp EXCEPTION;
Rochelle expt_msg VARCHAR2 (2000 );
BEGIN
-- L_tab_user_list: = tab_user_list ();
IF (l_tab_user_list.COUNT = 0)
THEN
Rochelle expt_msg: = 'no user to change ';
RAISE expt_no_user;
End if;

-- L_arr_resp_list: = arr_resp_list ();
IF (l_arr_resp_list.COUNT = 0)
THEN
Rochelle expt_msg: = 'no resp to add ';
RAISE expt_no_resp;
End if;

-- Loop user
< >
FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST
LOOP
DBMS_OUTPUT.put_line (
'>>>'
| Idx_tab_user_list
| ', Working for user ('
| L_tab_user_list (idx_tab_user_list)
| ') <');

-- Check if user exist or active
OPEN cur_user (l_tab_user_list (idx_tab_user_list ));
FETCH cur_user
Bulk collect into l_tab_user;
CLOSE cur_user;
IF (l_tab_user.COUNT = 0)
THEN
DBMS_OUTPUT.put_line (
'User ('
| L_tab_user_list (idx_tab_user_list)
| ') Is not exist or disabled ');
-- CONTINUE loop_tab_user_list;
Goto goto_tab_user_list;
End if;

-- Loop responsibility
< >
FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST
LOOP
-- Check if responsibility active
OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list ));
FETCH cur_resp
Bulk collect into l_tab_resp;
CLOSE cur_resp;
IF (l_tab_resp.COUNT = 0)
THEN
DBMS_OUTPUT.put_line (
'Resp ('
| L_arr_resp_list (idx_arr_resp_list)
| ') Is not exist or disabled ');
-- CONTINUE loop_arr_resp_list;
Goto goto_arr_resp_list;
End if;

-- Add resp for user
DBMS_OUTPUT.put_line (
'Adding resp ('
| L_arr_resp_list (idx_arr_resp_list)
| ') For user ('
| L_tab_user_list (idx_tab_user_list)
| ')');
Fnd_user_pkg.addresp (
Username => l_tab_user_list (idx_tab_user_list)
, Resp_app => l_tab_resp (1). application_short_name
, Resp_key => l_tab_resp (1). responsibility_key
, Security_group => l_tab_resp (1). security_group_key
, Description => NULL
, Start_date => TRUNC (SYSDATE)
, End_date => NULL );
< > Null;
End loop loop_arr_resp_list;
< > Null;
End loop loop_tab_user_list;
COMMIT;
EXCEPTION
WHEN expt_no_user
THEN
DBMS_OUTPUT.put_line (l_expt_msg );
ROLLBACK;
WHEN expt_no_resp
THEN
DBMS_OUTPUT.put_line (l_expt_msg );
ROLLBACK;
END;

/*
>>> 1. working for user (LIAO) <
Adding resp (system Administrator) for user (LIAO)
Adding resp (Purchasing Super User) for user (LIAO)
Resp (Application Administrator) is not exist or disabled
>>> 2, working for user (KARL) <
Adding resp (system Administrator) for user (KARL)
Adding resp (Purchasing Super User) for user (KARL)
Resp (Application Administrator) is not exist or disabled
>>> 3. working for user (xx) <
User (xx) is not exist or disabled
*/

/*
1. The Oracle PL/SQL GOTO Statement

*/

,