1 PACKAGE BODY CSI_ASSIGN_ROLE_PUB AS
2 -- $Header: csipuplb.pls 120.1 2006/01/11 14:17 epajaril noship $
3
4 PROCEDURE ROLE_ASSIGNMENT(
5 x_errbuf OUT nocopy varchar2,
6 x_retcode OUT nocopy number) AS
7
8 l_freeze_flag VARCHAR2(1);
9
10 CURSOR get_user IS
11 SELECT us.user_name, (
12 select decode(count(*), 0, 'CSI_END_USER', 'CSI_NORMAL_USER')
13 from fnd_user_resp_groups fg, fnd_responsibility fr
14 where fg.user_id = us.user_id
15 and (fg.end_date is null or fg.end_date > sysdate)
16 and fr.responsibility_id = fg.responsibility_id
17 and fr.responsibility_key = 'ORACLE_SUPPORT'
18 and rownum = 1 ) user_type
19 FROM fnd_user us
20 WHERE (us.end_date is null or us.end_date > sysdate)
21 AND EXISTS (select /*+ no_unnest */ null
22 from jtf_auth_principals_b p,
23 jtf_auth_principal_maps pr
24 where p.principal_name = us.user_name
25 and p.is_user_flag = 1
26 and p.jtf_auth_principal_id = pr.jtf_auth_principal_id
27 and exists (select /*+ no_unnest */ null
28 from jtf_auth_principals_b r
29 where pr.jtf_auth_parent_principal_id = r.jtf_auth_principal_id
30 and r.is_user_flag = 0
31 and r.principal_name like 'IBU%')
32 and not exists (select /*+ no_unnest */ null
33 from jtf_auth_principals_b r
34 where pr.jtf_auth_parent_principal_id = r.jtf_auth_principal_id
35 and r.is_user_flag = 0
36 and r.principal_name like 'CSI%'));
37 BEGIN
38 /* Check if the script will run or not */
39 BEGIN
40 SELECT nvl(freeze_flag,'N')
41 INTO l_freeze_flag
42 FROM csi_install_parameters;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 l_freeze_flag := 'N';
46 WHEN TOO_MANY_ROWS THEN
47 l_freeze_flag := 'Y';
48 END;
49
50 IF l_freeze_flag = 'Y' THEN
51 RETURN;
52 END IF;
53
54 FOR get_user_rec in get_user LOOP
55 IF get_user_rec.user_type = 'CSI_END_USER' THEN
56 jtf_auth_bulkload_pkg.assign_role(get_user_rec.user_name,'CSI_END_USER');
57 ELSIF get_user_rec.user_type = 'CSI_NORMAL_USER' THEN
58 jtf_auth_bulkload_pkg.assign_role(get_user_rec.user_name,'CSI_NORMAL_USER');
59 END IF;
60 END LOOP;
61 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
62 COMMIT;
63 EXCEPTION
64 WHEN OTHERS THEN
65 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
66 raise;
67 END;
68
69 END CSI_ASSIGN_ROLE_PUB;
[Home] [Help]