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
l_arr_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;
l_tab_user 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;
l_tab_resp tab_resp;
expt_no_user EXCEPTION;
expt_no_resp EXCEPTION;
l_expt_msg VARCHAR2 (2000);
BEGIN
--l_tab_user_list := tab_user_list ();
IF (l_tab_user_list.COUNT = 0)
THEN
l_expt_msg := 'no user to change';
RAISE expt_no_user;
END IF;
IF (l_arr_resp_list.COUNT = 0)
THEN
l_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)
|| ')')