Script to create user accounts and assign the responsibilities from back end.
DECLARE
l_record NUMBER := 0;
l_user_id NUMBER := fnd_global.user_id;
x_user_id NUMBER := NULL;
l_password VARCHAR2 (30) := 'welcome1';
l_appl_name VARCHAR2 (20);
l_resp_name VARCHAR2 (200);
l_resp_key VARCHAR2 (200);
l_resp_desc VARCHAR2 (2000);
CURSOR cur_user_rec
IS
SELECT *
FROM (SELECT DECODE (col,
1, "1",
2, "2",
3, "3",
4, "4",
5, "5",
6, "6",
7, "7" /* ,
8, "8",
9, "9",
10, "10",
11, "11",
12, "12",
13, "13",
14, "14" */
)
username
FROM (SELECT 'JBAVISETTI' AS "1" ,
'USERNAME2' AS "2", -- replace with required usernames
'USERNAME3' AS "3",
'USERNAME4' AS "4",
'USERNAME5' AS "5",
'USERNAME6' AS "6",
'USERNAME7' AS "7" /*,
'USERNAME8' AS "8",
'USERNAME9' AS "9",
'USERNAME10' AS "10" ,
'USERNAME11' AS "11",
'USERNAME12' AS "12",
'USERNAME13' AS "13",
'USERNAME14' AS "14" */
FROM DUAL),
(SELECT ROWNUM AS col
FROM all_objects
WHERE ROWNUM <= 20))
WHERE username IS NOT NULL;
CURSOR cur_resp_rec
IS
SELECT *
FROM (SELECT DECODE (col,
1, "1",
2, "2",
3, "3",
4, "4",
5, "5",
6, "6",
7, "7",
8, "8",
9, "9",
10, "10",
11, "11",
12, "12",
13, "13",
14, "14" /*,
15, "15",
16, "16",
17, "17",
18, "18",
19, "19",
20, "20",
21, "21",
22, "22",
23, "23",
24, "24",
25, "25",
26, "26",
27, "27",
28, "28",
29, "29",
30, "30",
31, "31",
32, "32",
33, "33" */
)
respname
FROM (SELECT 'Application Developer' AS "1",
'Application Diagnostics' AS "2",
'Cash Management Superuser' AS "3",
'Cost Management - SLA' AS "4",
'Fixed Assets Manager' AS "5",
'Fixed Assets Administrator' AS "6",
'General Ledger Super User' AS "7",
'Payables Manager' AS "8",
'Receivables Inquiry' AS "9",
'Receivables Manager' AS "10",
'Tax Managers' AS "11",
'Workflow Administrator' AS "12",
'XML Publisher Administrator' AS "13",
'System Administrator' AS "14"
FROM DUAL),
(SELECT ROWNUM AS col
FROM all_objects
WHERE ROWNUM <= 100))
WHERE respname IS NOT NULL;
CURSOR cur_resp_detail_rec (
p_resp_name VARCHAR2)
IS
SELECT frt.responsibility_name,
fa.application_short_name,
fr.RESPONSIBILITY_KEY,
frt.description,
frt.responsibility_id,
frt.application_id
FROM fnd_responsibility_tl frt,
fnd_responsibility fr,
fnd_application fa
WHERE frt.responsibility_id = fr.responsibility_id
AND frt.application_id = fa.application_id
AND UPPER (responsibility_name) = UPPER (p_resp_name);
BEGIN
FOR rec_user_cur IN cur_user_rec
LOOP
SELECT COUNT (user_id)
INTO l_record
FROM fnd_user
WHERE user_name = UPPER (rec_user_cur.username);
IF l_record = 0
THEN
hr_user_acct_internal.create_fnd_user (
p_user_name => rec_user_cur.username,
p_password => l_password,
p_employee_id => NULL,
p_user_id => x_user_id,
p_user_start_date => SYSDATE,
p_email_address => NULL,
p_description => 'Apps Associates Consultant',
p_password_date => NULL);
DBMS_OUTPUT.put_line (x_user_id);
IF x_user_id IS NOT NULL
THEN
UPDATE fnd_user
SET password_lifespan_days = 90
WHERE user_id = x_user_id;
COMMIT;
END IF;
ELSE
SELECT user_id
INTO x_user_id
FROM fnd_user
WHERE user_name = UPPER (rec_user_cur.username);
DBMS_OUTPUT.put_line (
'User '
|| rec_user_cur.username
|| ' is exists with user id '
|| x_user_id);
END IF;
FOR rec_resp_cur IN cur_resp_rec
LOOP
FOR rec_resp_detail_cur
IN cur_resp_detail_rec (rec_resp_cur.respname)
LOOP
SELECT COUNT (RESPONSIBILITY_ID)
INTO l_record
FROM FND_USER_RESP_GROUPS_ALL
WHERE USER_ID = x_user_id
AND RESPONSIBILITY_ID =
rec_resp_detail_cur.responsibility_id
AND RESPONSIBILITY_APPLICATION_ID =
rec_resp_detail_cur.application_id;
IF l_record = 0
THEN
BEGIN
fnd_user_pkg.addresp (
username => UPPER (rec_user_cur.username),
resp_app => rec_resp_detail_cur.application_short_name,
resp_key => rec_resp_detail_cur.RESPONSIBILITY_KEY,
security_group => 'STANDARD',
description => rec_resp_detail_cur.description,
start_date => SYSDATE,
end_date => NULL);
COMMIT;
DBMS_OUTPUT.put_line (
rec_resp_detail_cur.responsibility_name
|| ' Responsibility Added Successfully for the user '
|| rec_user_cur.username);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
rec_resp_detail_cur.responsibility_name
|| ' Responsibility is not added for the user '
|| rec_user_cur.username
|| ' due to'
|| SQLCODE
|| SUBSTR (SQLERRM, 1, 100));
END;
ELSE
DBMS_OUTPUT.put_line (
rec_resp_detail_cur.responsibility_name
|| ' Responsibility already assigned to the user '
|| rec_user_cur.username);
END IF;
END LOOP;
END LOOP;
END LOOP;
COMMIT;
END;
/
No comments:
Post a Comment