1 PACKAGE BODY irc_grants_util AS
2 /* $Header: irgntutl.pkb 120.0.12000000.2 2007/05/07 14:39:22 vkaduban noship $ */
3 -- ----------------------------------------------------------------------------
4 -- |--------------------------< create_grants>--------------------------------|
5 -- ----------------------------------------------------------------------------
6 --
7 procedure create_grants(
8 errbuf OUT NOCOPY VARCHAR2
9 ,retcode OUT NOCOPY NUMBER
10 ,p_resp_key IN VARCHAR2
11 ,p_resp_appl_name IN VARCHAR2
12 ,p_permission_set IN VARCHAR2)
13 IS
14 cursor csr_get_candidates(p_resp_id number, p_menu_id number) is
15 select fu.user_id, fu.user_name, furg.security_group_id
16 from
17 fnd_user fu, fnd_user_resp_groups furg
18 where furg.responsibility_id = p_resp_id
19 and furg.user_id=fu.user_id
20 and fu.user_id > 1000
21 and trunc(sysdate) between nvl(fu.start_date,trunc(sysdate)) and nvl(fu.end_date,trunc(sysdate))
22 and not exists (select /*+ NO_UNNEST */1 from fnd_grants where grantee_type='USER' and grantee_key=fu.user_name
23 and ctx_resp_id=furg.responsibility_id and menu_id=p_menu_id);
24
25 cursor get_menu_id is
26 select menu_id from fnd_menus where menu_name=upper(p_permission_set);
27
28 cursor get_responsibility_id is
29 select responsibility_id, application_id
30 from fnd_responsibility where responsibility_key=upper(p_resp_key);
31
32 l_user_id number;
33
34 TYPE l_user_id_typ IS TABLE OF FND_USER.USER_ID%TYPE index by binary_integer;
35 TYPE l_user_name_typ IS TABLE OF FND_USER.USER_NAME%TYPE index by binary_integer;
36 TYPE l_resp_id_typ IS TABLE OF FND_USER_RESP_GROUPS.RESPONSIBILITY_ID%TYPE index by binary_integer;
37 TYPE l_resp_appl_id_typ IS TABLE OF FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID%TYPE index by binary_integer;
38 TYPE l_sec_group_id_typ IS TABLE OF FND_USER_RESP_GROUPS.SECURITY_GROUP_ID%TYPE index by binary_integer;
39
40 l_user_id_tbl l_user_id_typ;
41 l_user_name_tbl l_user_name_typ;
42 l_sec_group_id_tbl l_sec_group_id_typ;
43
44 l_menu_id number;
45 l_responsibility_id number;
46 l_resp_appl_id number;
47 l_grant_name varchar2(80);
48 l_user_name varchar2(100);
49 l_o varchar2(30);
50 l_plsql_max_array_size number := 1000;
51
52 l_proc VARCHAR2(30) default 'create_grants';
53 BEGIN
54 hr_utility.set_location('Entering'||l_proc, 10);
55 -- get menu id for the permission set
56 open get_menu_id;
57 fetch get_menu_id into l_menu_id;
58 close get_menu_id;
59 -- get Resp info
60 open get_responsibility_id;
61 fetch get_responsibility_id into l_responsibility_id,l_resp_appl_id;
62 close get_responsibility_id;
63
64 hr_utility.set_location('before csr_get_candidates', 20);
65
66 open csr_get_candidates(l_responsibility_id, l_menu_id);
67 loop
68 l_user_id_tbl.delete;
69 l_user_name_tbl.delete;
70 l_sec_group_id_tbl.delete;
71
72 FETCH csr_get_candidates BULK COLLECT INTO
73 l_user_id_tbl
74 ,l_user_name_tbl
75 ,l_sec_group_id_tbl
76 limit l_plsql_max_array_size;
77
78 IF (l_user_id_tbl.count = 0) THEN
79 EXIT;
80 END IF;
81
82 FOR i in l_user_id_tbl.first..l_user_id_tbl.last LOOP
83 --
84 fnd_global.apps_initialize(user_id=>l_user_id_tbl(i),
85 resp_id=> l_responsibility_id,
86 resp_appl_id=> l_resp_appl_id,
87 security_group_id=>l_sec_group_id_tbl(i),
88 server_id=>null);
89 --
90 l_user_name := l_user_name_tbl(i);
91 if length(l_user_name) > 65 then
92 l_grant_name := 'IRC_'||substr(l_user_name,1,65)||'_CAND_GRANT';
93 else
94 l_grant_name := 'IRC_'||upper(l_user_name)||'_CAND_GRANT';
95 end if;
96 --
97 irc_party_api.grant_access(p_user_name => l_user_name,
98 p_user_id => l_user_id_tbl(i),
99 p_menu_id => l_menu_id,
100 p_resp_id => l_responsibility_id,
101 p_resp_appl_id => l_resp_appl_id,
102 p_sec_group_id => l_sec_group_id_tbl(i),
103 p_grant_name => l_grant_name,
104 p_description => ' ');
105 --
106 END LOOP ;
107 -- commit the changes so far
108 commit;
109 end loop;
110
111 IF csr_get_candidates%ISOPEN THEN
112 CLOSE csr_get_candidates;
113 END IF;
114
115 hr_utility.set_location('Leaving'||l_proc, 80);
116 end create_grants;
117 --
118 end irc_grants_util;
[Home] [Help]