1 PACKAGE BODY UMX_W3H_UTL AS
2 /* $Header: UMXW3HUTLB.pls 120.6 2008/05/23 07:55:36 kkasibha noship $ */
3
4 /*type to store the excluded function list*/
5 TYPE TYPE_W3H_FUNCTIONS_TAB is table of VARCHAR2(1000) index by VARCHAR2(1000);
6 l_function_list TYPE_W3H_FUNCTIONS_TAB; --stores the excluded functions list for a given resp
7
8 -- Function
9 -- getObjectDetails
10 --
11 -- Description
12 -- This method takes in the name of permission set for the corresponding
13 -- database security object and returns the list for the
14 -- permission set
15 -- IN
16 -- p_menu_name - takes in FND_MENUS.MENU_NAME%TYPE object
17 -- RETURNS
18 -- List for the permission set
19
20 function getObjectDetails(p_menu_name in FND_MENUS.MENU_NAME%TYPE)
21 return varchar2
22
23 IS
24
25 l_menu_name FND_MENUS.MENU_NAME%TYPE;
26 l_function_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
27 l_list varchar2(3200);
28
29 cursor function_list IS
30 select frm.function_name
31 from fnd_form_functions frm,fnd_compiled_menu_functions fcm,fnd_menus fm
32 where frm.function_id=fcm.function_id
33 and fcm.menu_id=fm.menu_id
34 and fm.menu_name=l_menu_name;
35
36 begin
37 l_menu_name := p_menu_name;
38 l_list := null;
39 if function_list%ISOPEN then
40 close function_list;
41 end if;
42 open function_list;
43 loop
44 fetch function_list into l_function_name;
45 EXIT WHEN function_list%NOTFOUND;
46 l_list := l_list || l_function_name || ',';
47 if length(l_list) > 3000 then
48 exit;
49 end if;
50 end loop;
51 close function_list;
52 if length(l_list) > 0 then
53 l_list := substr(l_list,1,length(l_list)-1);
54 end if;
55 if length(l_list) > 3000 then
56 l_list := substr(l_list,1,2996);
57 l_list := substr(l_list,1,instr(l_list,',',-1,1)) || '...';
58 end if;
59 -- dbms_output.put_line('Menu List:' || l_list);
60 return l_list;
61 end getObjectDetails;
62 /***************************************************************************************/
63
64
65 -- Function
66 -- isFunctionAccessible
67 --
68 -- Description
69 -- This method takes in user name and role name for the a list of functions
70 -- for a user and returns true or false for accessibility
71 -- IN
72 -- p_user_name - varchar2 (takes the user_name)
73 -- p_role_name - varchar2 (takes the role_name)
74 -- RETURNS
75 -- result as true or false
76
77 function isFunctionAccessible(
78 p_user_name in varchar2,p_role_name in varchar2) return varchar2
79
80 IS
81
82 l_result varchar2(5);
83 l_user_name varchar2(100);
84 l_role_name varchar2(480);
85 l_user_start_date date;
86 l_user_end_date date;
87 l_role_start_date date;
88 l_role_end_date date;
89 l_effective_start_date date;
90 l_effective_end_date date;
91 l_assignment_start_date date;
92 l_assignment_end_date date;
93
94 begin
95 select start_date,end_date into l_user_start_date,l_user_end_date
96 from fnd_user where user_name = p_user_name;
97
98 select min(role_start_date) ,max(nvl(role_end_date,to_date('31-12-9999','DD-MM-YYYY'))),min(effective_start_date),
99 max(nvl(effective_end_date,to_date('31-12-9999','DD-MM-YYYY'))),min( start_date),max(nvl(end_date,to_date('31-12-9999','DD-MM-YYYY')))
100 into l_role_start_date,l_role_end_date,l_effective_start_date,l_effective_end_date,
101 l_assignment_start_date,l_assignment_end_date
102 from wf_user_role_assignments
103 where user_name=p_user_name and role_name = p_role_name;
104
105 -- check whether user is inactive
106 if(l_user_start_date > sysdate or l_user_end_date <= sysdate) then
107
108 if(l_role_start_date > sysdate or l_role_end_date <= sysdate) then
109 l_result := 'false';
110 elsif(l_assignment_start_date > sysdate or l_assignment_end_date <= sysdate) then
111 l_result := 'false';
112 else
113 l_result := 'true';
114 end if;
115 -- this means user is active, we check for effective dates
116 elsif(l_effective_start_date > sysdate or l_effective_end_date <= sysdate) then
117 l_result := 'false';
118 else
119 l_result := 'true';
120 end if;
121
122 -- return the result
123 return l_result;
124 end isFunctionAccessible;
125 /***************************************************************************************/
126
127
128 -- Function
129 -- get_excluded_function_list
130 --
131 -- Description
132 -- This method takes in the name of the responsibility, gets all the excluded function for the resps in its hierarchy and places them in a associative array
133 -- IN
134 -- p_resp_name - varchar2 (name of the responsibility)
135 -- RETURNS
136 -- result as Success on success, error message on failure
137 /* This procedure will populate the l_function_list table type with all the excluded functions*/
138
139 FUNCTION get_excluded_function_list(p_resp_name WF_ROLES.NAME%TYPE) RETURN VARCHAR2 IS
140 /*cursor to gte all the functions excluded for a given reponsibility hierarchy*/
141 CURSOR func_list IS
142 select distinct wur.name||frm.function_name
143 from fnd_responsibility fr,fnd_resp_functions frf, wf_local_roles wur,fnd_form_functions frm,
144 (select wur1.name roleName from wf_local_roles wur1
145 where wur1.name = p_resp_name
146 union
147 select super_name roleName from wf_role_hierarchies
148 where enabled_flag='Y'
149 connect by prior super_name=sub_name
150 and prior enabled_flag='Y'
151 start with sub_name= p_resp_name) roles
152 where wur.name = roles.roleName
153 and fr.responsibility_key = substr(wur.name,instr(wur.name,'|',1,2)+1,(instr(wur.name,'|',1,3)-1-instr(wur.name,'|',1,2)))
154 and frf.responsibility_id=fr.responsibility_id
155 and ((frf.rule_type='F' and frf.action_id = frm.function_id)
156 or (frf.rule_type='M' and frm.function_id in (select fcm.function_id from fnd_compiled_menu_functions fcm
157 where fcm.menu_id=frf.action_id)
158 ));
159
160 func varchar2(1000); /*temp var to store thevalues fetched from the cursor*/
161 RET NUMBER;
162 BEGIN
163 /*clear the table before adding the new data*/
164 begin
165 l_function_list.delete(l_function_list.first,l_function_list.last);
166 exception
167 when others then
168 null;
169 end;
170
171 OPEN func_list;
172 LOOP
173 FETCH func_list INTO func;
174 EXIT WHEN func_list%NOTFOUND;
175 l_function_list(func) := func; --insert the fuction into the table
176 END LOOP;
177 close func_list;
178 RETURN 'SUCCESS';
179 EXCEPTION
180 WHEN OTHERS THEN
181 RETURN 'ERROR : '||SQLCODE||' : '||SQLERRM;
182 END get_excluded_function_list;
183 /***************************************************************************************/
184
185 -- Function
186 -- is_function_menu_excluded
187 --
188 -- Description
189 -- This method takes in the name of the function to find
190 -- and the responsibility name under which the function is to be searched for accssiblity
191 -- IN
192 -- func_to_find - varchar2 (name of the function to find)
193 -- resp_name - varchar2 (responsibility name)
194 -- RETURNS
195 -- result as Yes or No
196 /*This function accepts the function anme and the resp name in which the function is tobe searched,.
197 Returns 'Yes', if the function is found under the resp else 'no' is returned.
198 */
199
200 FUNCTION is_function_menu_excluded(func_to_find FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE,resp_name WF_LOCAL_ROLES.NAME%TYPE) RETURN VARCHAR2 IS
201 BEGIN
202 /*Check whether the function is present in the function list*/
203 IF(l_function_list(resp_name||func_to_find)=resp_name||func_to_find) THEN
204 RETURN 'Yes';
205 END IF;
206 RETURN 'NO';
207 EXCEPTION
208 WHEN OTHERS THEN
209 RETURN 'No';
210 END is_function_menu_excluded;
211 /***************************************************************************************/
212
213
214 end UMX_W3H_UTL;
[Home] [Help]