1 PACKAGE BODY AMW_VIOLATION_PVT AS
2 /* $Header: amwvvlab.pls 120.40 2007/09/18 10:58:44 ptulasi ship $ */
3 -- ===============================================================
4 -- Package name
5 -- AMW_VIOLATION_PVT
6 -- Purpose
7 --
8 -- History
9 -- 06/01/2005 tsho Create
10 -- ===============================================================
11
12
13 -- store potential violation info (valid for one constraint)
14 G_ROLE_NAME_LIST G_VARCHAR2_LONG_TABLE;
15 G_RESPONSIBILITY_ID_LIST G_NUMBER_TABLE;
16 G_MENU_ID_LIST G_NUMBER_TABLE;
17 G_FUNCTION_ID_LIST G_NUMBER_TABLE;
18 G_ENTRY_OBJECT_TYPE_LIST G_VARCHAR2_CODE_TABLE;
19 G_GROUP_CODE_LIST G_VARCHAR2_CODE_TABLE;
20 G_PV_COUNT NUMBER;
21
22 --store role/resp hierarchy structure
23 G_ROLE_NAME_LIST_HIER G_VARCHAR2_LONG_TABLE;
24 G_RESPONSIBILITY_ID_LIST_HIER G_NUMBER_TABLE;
25 G_MENU_ID_LIST_HIER G_NUMBER_TABLE;
26 G_FUNCTION_ID_LIST_HIER G_NUMBER_TABLE;
27 G_ENTRY_OBJECT_TYPE_LIST_HIER G_VARCHAR2_CODE_TABLE;
28 G_GROUP_CODE_LIST_HIER G_VARCHAR2_CODE_TABLE;
29 G_PV_COUNT_HIER NUMBER;
30
31
32 G_BULK_COLLECTS_SUPPORTED VARCHAR2(30) := 'TRUE';
33
34 -- copy from FND_FUNCTION.C_MAX_MENU_ENTRIES (AFSCFNSB.pls 115.51 2003/08/01)
35 -- This constant is used for recursion detection in the fallback
36 -- runtime menu scan. We keep track of how many items are on the menu,
37 -- and assume if the number of entries on the current
38 -- menu is too high then it's caused by recursion.
39 C_MAX_MENU_ENTRIES CONSTANT pls_integer := 10000;
40
41
42 -- copy from FND_FUNCTION.P_LAST_RESP_ID (AFSCFNSB.pls 115.51 2003/08/01)
43 -- copy from FND_FUNCTION.P_LAST_RESP_APPL_ID (AFSCFNSB.pls 115.51 2003/08/01)
44 -- copy from FND_FUNCTION.P_LAST_MENU_ID (AFSCFNSB.pls 115.51 2003/08/01)
45 -- This simple cache will avoid the need to find which menu is on
46 -- the current responsibility with SQL every time. We just store
47 -- the menu around after we get it for the current resp.
48 P_LAST_RESP_ID NUMBER := -1;
49 P_LAST_RESP_APPL_ID NUMBER := -1;
50 P_LAST_MENU_ID NUMBER := -1;
51
52 -- ===============================================================
53 -- Function name
54 -- Is_ICM_Installed
55 --
56 -- Purpose
57 -- check to see if ICM is installed or not.
58 -- other ICM API should be called only when 'Y' is return from Is_ICM_Installed
59 -- Params
60 --
61 -- Return
62 -- 'Y' := ICM is installed
63 -- 'N' := ICM is not installed
64 -- History
65 -- 07/19/2005 tsho Create
66 -- ===============================================================
67 Function Is_ICM_Installed
68 RETURN VARCHAR2
69 IS
70 is_icm_valid varchar2(1);
71 dummy varchar2(1);
72
73 TYPE icmCurTyp IS REF CURSOR;
74 c_has_icm icmCurTyp;
75 l_has_icm_sql varchar2(64) := 'select null from AMW_CONSTRAINTS_B where rownum = 1';
76
77 BEGIN
78
79 is_icm_valid := 'N';
80
81 OPEN c_has_icm FOR l_has_icm_sql;
82 FETCH c_has_icm INTO dummy;
83 IF (c_has_icm%notfound) THEN
84 is_icm_valid := 'N';
85 ELSE
86 is_icm_valid := 'Y';
87 END IF;
88 CLOSE c_has_icm;
89
90 return is_icm_valid;
91
92 EXCEPTION
93 WHEN others then
94 return 'N';
95 END Is_ICM_Installed;
96
97
98 -- ===============================================================
99 -- Procedure name
100 -- Has_Violations_For_Mode
101 --
102 -- Purpose
103 -- check for OICM SOD constriants that will be violated
104 -- if the user is assigned these additional roles as well as inherited roles
105 -- This Procedure is called from the UMX_REGISTRATION_UTIL.ICM_VIOLATION_CHECK
106 -- Params
107 -- p_user_id := input fnd user_id
108 -- p_role_names := input a list of new roles
109 -- p_mode := input check mode ('ADMIN', 'APPROVE', 'SUBS')
110 -- x_violat_hashtable := This API will put return parameters in a Associate Table format,
111 -- it at least contains the following key/value pairs:
112 -- HasViolations : 'Y' or 'N' to indicate if introducing violations when trying to add those roles to the user
113 -- ViolationDetail : the OAFunc/Region containing violation details for the user , mainly used for Notification.
114 --
115 -- History
116 -- 07/19/2005 tsho Create
117 -- ===============================================================
118 Procedure Has_Violations_For_Mode (
119 p_user_id IN NUMBER,
120 p_role_names IN JTF_VARCHAR2_TABLE_400,
121 p_mode IN VARCHAR2,
122 x_violat_hashtable OUT NOCOPY G_VARCHAR2_HASHTABLE
123 )
124 IS
125 l_violat_region VARCHAR2(320);
126 l_violat_btn_region VARCHAR2(320);
127 l_has_violation VARCHAR2(1);
128 l_return_status VARCHAR2(10);
129 l_msg_count NUMBER;
130 l_msg_data VARCHAR2(5000);
131 BEGIN
132 Has_Violations (
133 p_user_id=>p_user_id,
134 p_role_names=>p_role_names,
135 p_mode=>p_mode,
136 x_violat_region=>l_violat_region,
137 x_violat_btn_region=>l_violat_btn_region,
138 x_has_violation=>l_has_violation,
139 x_return_status=>l_return_status,
140 x_msg_count=>l_msg_count,
141 x_msg_data=>l_msg_data);
142
143 IF (l_has_violation = 'Y') THEN
144
145 x_violat_hashtable('HasViolations') := 'Y';
146
147 -- 07/13/2006 psomanat : Fix for bug 5388850
148 -- ptulasi : 09/03/07
149 -- bug: 6371514 : Modified below code to correct the url
150 x_violat_hashtable('ViolationDetail') := 'AMW_ROLE_APPROVAL_NOTIFY&pNewRoleName='||p_role_names(1)||'&pUserId='||p_user_id;
151 --x_violat_hashtable('ViolationDetail') := 'AMW_ROLE_APPROVAL_NOTIFY='||p_role_names(1)||'='||p_user_id;
152 --x_violat_hashtable('ViolationDetail') := 'AMW_ROLE_APPROVAL_NOTIFY=--=--';
153 ELSE
154 x_violat_hashtable('HasViolations') := 'N';
155 x_violat_hashtable('ViolationDetail') := NULL;
156 END IF;
157
158 END Has_Violations_For_Mode;
159
160 -- ===============================================================
161 -- Procedure name
162 -- Has_Violations
163 -- This procedure is obsolated due to bug 5407266
164 -- ===============================================================
165 Procedure Has_Violations (
166 p_user_id IN NUMBER,
167 p_role_names IN JTF_VARCHAR2_TABLE_400,
168 p_mode IN VARCHAR2,
169 x_violat_region OUT NOCOPY VARCHAR2,
170 x_violat_btn_region OUT NOCOPY VARCHAR2,
171 x_has_violation OUT NOCOPY VARCHAR2,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2
175 )
176 IS
177
178 L_API_NAME CONSTANT VARCHAR2(30) := 'Has_Violations';
179 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
180
181 l_new_resp_name VARCHAR2(320);
182 l_existing_resp_name VARCHAR2(320);
183
184
185 BEGIN
186
187 Has_Violations (
188 p_user_id=>p_user_id,
189 p_role_names=>p_role_names,
190 p_revoked_role_names => NULL,
191 p_mode => p_mode,
192 x_violat_region => x_violat_region,
193 x_violat_btn_region => x_violat_btn_region,
194 x_has_violation => x_has_violation,
195 x_new_resp_name => l_new_resp_name,
196 x_existing_resp_name => l_existing_resp_name,
197 x_return_status => x_return_status,
198 x_msg_count => x_msg_count,
199 x_msg_data => x_msg_data);
200
201 END Has_Violations;
202
203
204 -- ===============================================================
205 -- Procedure name
206 -- Has_Violations
207 --
208 -- Purpose
209 -- check for OICM SOD constriants that will be violated
210 -- if the user is assigned these additional roles as well as inherited roles
211 -- Params
212 -- p_user_id := input fnd user_id
213 -- p_role_names := input a list of new roles
214 -- p_revoked_role_names := input a list of revoked roles
215 -- p_mode := input check mode ('ADMIN', 'APPROVE', 'SUBS')
216 -- x_violat_region := output full path dialog region name to display potential violation detials.
217 -- (ie. /oracle/apps/amw/audit/duty/webui/....RN)
218 -- x_violat_btn_region := output full path dialog button region name to display page level buttons.
219 -- (ie. /oracle/apps/amw/audit/duty/webui/....RN)
220 -- this button region is different depending on the override privilege of Administrator
221 -- x_has_violation := output 'Y' if this user will have violations with the new roles assigned; output 'N' otherwise.
222 --
223 -- x_new_resp_name := output a list of newly assigned resps/roles which violate the sod
224 -- x_existing_resp_name := output a list of existing resps/roles which violate the sod
225 -- History
226 -- 06/01/2005 tsho Create
227 -- 08/03/2005 tsho Consider User Waivers
228 -- 04/28/2006 dliao Consider RESPALL/RESPME/RESPSET
229 -- ===============================================================
230 Procedure Has_Violations (
231 p_user_id IN NUMBER,
232 p_role_names IN JTF_VARCHAR2_TABLE_400,
233 p_revoked_role_names IN JTF_VARCHAR2_TABLE_400,
234 p_mode IN VARCHAR2,
235 x_violat_region OUT NOCOPY VARCHAR2,
236 x_violat_btn_region OUT NOCOPY VARCHAR2,
237 x_has_violation OUT NOCOPY VARCHAR2,
238 x_new_resp_name OUT NOCOPY VARCHAR2,
239 x_existing_resp_name OUT NOCOPY VARCHAR2,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2
243 )
244 IS
245
246 L_API_NAME CONSTANT VARCHAR2(30) := 'Has_Violations';
247 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
248
249
250 -- find all valid constraints
251 CURSOR c_all_valid_constraints IS
252 SELECT constraint_rev_id, type_code
253 FROM amw_constraints_b
254 WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate)
255 AND objective_code = 'PR';
256 l_all_valid_constraints c_all_valid_constraints%ROWTYPE;
257
258 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
259 l_constraint_entries_count NUMBER;
260 l_func_access_count NUMBER;
261 l_group_access_count NUMBER;
262 l_resp_access_count NUMBER;
263
264
265 CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
266 SELECT count(*)
267 FROM amw_constraint_entries
268 WHERE constraint_rev_id = l_constraint_rev_id;
269
270 TYPE refCurTyp IS REF CURSOR;
271 func_acess_count_c refCurTyp;
272 group_acess_count_c refCurTyp;
273 new_violation_count_c refCurTyp;
274 resp_acess_count_c refCurTyp;
275 violating_new_resps_c refCurTyp;
276 violating_old_resps_c refCurTyp;
277 existing_violation_c refCurTyp;
278
279 -- store passed-in p_role_names, p_revoked_role_names as string, sperated by ','
280 l_role_names VARCHAR2(32767);
281 l_sub_role_names VARCHAR2(32767);
282 l_revoked_role_names VARCHAR2(32767);
283
284 l_func_dynamic_sql VARCHAR2(32767);
285 l_vio_exist_role_sql VARCHAR2(32767);
286 l_vio_exist_resp_sql VARCHAR2(32767);
287
288 l_func_sql VARCHAR2(32767);
289 l_func_existing_sql VARCHAR2(32767);
290
291
292 l_func_set_dynamic_sql VARCHAR2(32767);
293 l_func_set_existing_sql VARCHAR2(32767);
294
295 l_resp_sql VARCHAR2(32767);
296 l_resp_dynamic_sql VARCHAR2(32767);
297 l_resp_existing_sql VARCHAR2(32767);
298
299 l_resp_set_dynamic_sql VARCHAR2(32767);
300 l_resp_set_existing_sql VARCHAR2(32767);
301
302
303
304 -- get valid user waiver
305 l_valid_user_waiver_count NUMBER;
306 CURSOR c_valid_user_waivers (l_constraint_rev_id IN NUMBER, l_user_id IN NUMBER) IS
307 SELECT count(*)
308 FROM amw_constraint_waivers_vl
309 WHERE constraint_rev_id = l_constraint_rev_id
310 AND object_type = 'USER'
311 AND PK1 = l_user_id
312 AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
313
314 -- get violated responsibilities
315 CURSOR c_violated_responsibilities(l_constraint_rev_id IN NUMBER) IS
316 SELECT resp.responsibility_name
317 FROM fnd_responsibility_vl resp
318 WHERE resp.responsibility_id in (
319 SELECT function_id
320 FROM amw_constraint_entries cons
321 WHERE constraint_rev_id = l_constraint_rev_id
322 AND cons.application_id = resp.application_id);
323
324 l_cst_new_violation_sql VARCHAR2(5000) ;
325 l_sub_revoked_role_names VARCHAR2(32767);
326
327
328 l_violating_new_roles_sql varchar2(32767);
329 l_violating_new_resp_table JTF_VARCHAR2_TABLE_400;
330 l_violating_existing_roles_sql VARCHAR2(32767);
331 l_violating_old_resp_table JTF_VARCHAR2_TABLE_400;
332
333 l_existing_violation_sql varchar2(32676);
334 l_new_violation_table JTF_VARCHAR2_TABLE_400;
335 l_existing_violation_table JTF_VARCHAR2_TABLE_400;
336
337
338 BEGIN
339 l_role_names := NULL;
340 l_revoked_role_names := NULL;
341 x_violat_region := NULL;
342 x_violat_btn_region := NULL;
343 l_valid_user_waiver_count := 0;
344
345 -- default to 'N', which means user doesn't have violations
346 x_has_violation := 'N';
347
348 IF (p_revoked_role_names IS NULL or p_revoked_role_names.FIRST IS NULL or p_revoked_role_names(1) IS NULL
349 or p_revoked_role_names(1) = '' ) THEN
350 l_sub_revoked_role_names := '';
351 ELSE
352 -- store pass-in p_revoked_role_names as flat string
353 l_revoked_role_names := ''''||p_revoked_role_names(1) || '''';
354 FOR i IN 2 .. p_revoked_role_names.COUNT
355 LOOP
356 l_revoked_role_names := l_revoked_role_names||',''' ||p_revoked_role_names(i) || '''';
357 END LOOP;
358
359 l_sub_revoked_role_names := ' and uar.role_name not in ( ' ||
360 ' select distinct ROLE_NAME from wf_user_role_assignments_v a '||
361 ' where a.assigning_role IN ( ' || l_revoked_role_names || ' ) '||
362 ' AND a.role_name = a.assigning_role ) ';
363
364 /*** add the inherited roles
365 ' UNION ALL '||
366 ' select distinct ROLE_NAME from wf_user_role_assignments_v b '||
367 ' where user_name = (select user_name from fnd_user where user_id = ' || p_user_id || ' ) '||
368 ' and b.assigning_role IN ( ' || l_revoked_role_names || ' ) '||
369 ' and b.role_name <> b.assigning_role '||
370 ' and b.start_date <= sysdate and (b.end_date is null or b.end_date > sysdate) '||
371 ' and b.role_name not in '||
372 ' (SELECT c.ROLE_NAME FROM wf_user_role_assignments_v c WHERE '||
373 ' c.start_date <= sysdate and (c.end_date is null or c.end_date > sysdate) '||
374 ' and c.ASSIGNING_ROLE IN ( '||
375 ' select ROLE_NAME from wf_user_role_assignments_v d '||
376 ' where d.role_name = d.assigning_role '||
377 ' and d.role_name not in (' || l_revoked_role_names || ' ) '||
378 ' and d.start_date <= sysdate and (d.end_date is null or d.end_date > sysdate))))';
379 *************/
380
381 END IF;
382
383 l_func_existing_sql :=
384 ' select rcd.function_id '
385 ||' from amw_role_constraint_denorm rcd '
386 ||' ,'||G_AMW_USER_ROLES||' ur '
387 ||' ,'||G_AMW_user||' u '
388 ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
389 ||' where rcd.constraint_rev_id = :1 '
390 ||' and u.user_id = :2 '
391 ||' and u.user_name = ur.user_name '
392 ||' and uar.user_name = ur.user_name '
393 ||' and uar.role_name = ur.role_name '
394 ||' and uar.start_date <= sysdate '
395 ||' and (uar.end_date is null or uar.end_date >= sysdate) '
396 ||' and ( (ur.role_name = rcd.role_name '
397 ||' and ur.role_orig_system = ''UMX'' ) '
398 ||' or ( ur.role_orig_system_id = rcd.responsibility_id '
399 ||' and ur.role_orig_system = ''FND_RESP'' ) ) '
400 || l_sub_revoked_role_names
401 ||' UNION ALL '
402 ||' select rcd.function_id '
403 ||' from amw_role_constraint_denorm rcd '
404 ||' ,'||G_AMW_GRANTS||' gra '
405 ||' where rcd.constraint_rev_id = :3 '
406 ||' and gra.instance_type = ''GLOBAL'' and gra.menu_id = rcd.menu_id '
407 ||' and gra.object_id = -1 and ( gra.grantee_type = ''GLOBAL'' '
408 ||' or ( gra.grantee_type = ''USER'' and gra.grantee_key = (select u.user_name from '
409 || G_AMW_USER || ' u where u.user_id = :4 ))) ';
410
411
412 l_func_set_existing_sql :=
413 ' select rcd.group_code '
414 ||' from amw_role_constraint_denorm rcd '
415 ||' ,'||G_AMW_USER_ROLES||' ur '
416 ||' ,'||G_AMW_user||' u '
417 ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
418 ||' where rcd.constraint_rev_id = :1 '
419 ||' and u.user_id = :2 '
420 ||' and u.user_name = ur.user_name '
421 ||' and uar.user_name = ur.user_name '
422 ||' and uar.role_name = ur.role_name '
423 ||' and uar.start_date <= sysdate '
424 ||' and (uar.end_date is null or uar.end_date >= sysdate) '
425 ||' and ( (ur.role_name = rcd.role_name '
426 ||' and ur.role_orig_system = ''UMX'' ) '
427 ||' or ( ur.role_orig_system_id = rcd.responsibility_id '
428 ||' and ur.role_orig_system = ''FND_RESP'' ) ) '
429 || l_sub_revoked_role_names
430 ||' UNION ALL '
431 ||' select rcd.group_code '
432 ||' from amw_role_constraint_denorm rcd '
433 ||' ,'||G_AMW_GRANTS||' gra '
434 ||' where rcd.constraint_rev_id = :3 '
435 ||' and gra.instance_type = ''GLOBAL'' and gra.menu_id = rcd.menu_id '
436 ||' and gra.object_id = -1 and ( gra.grantee_type = ''GLOBAL'' '
437 ||' or ( gra.grantee_type = ''USER'' and gra.grantee_key = (select u.user_name from '
438 || G_AMW_USER || ' u where u.user_id = :4 ))) ';
439
440 l_resp_existing_sql :=
441 ' select ur.role_orig_system_id '
442 ||' from '
443 || G_AMW_USER_ROLES||' ur '
444 ||' ,'||G_AMW_user||' u '
445 ||' ,amw_constraint_entries cst '
446 ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
447 ||' where u.user_id = :1 '
448 ||' and cst.constraint_rev_id = :2 '
449 ||' and u.user_name = ur.user_name '
450 ||' and ur.role_orig_system_id = cst.function_id '
451 ||' and ur.role_orig_system = ''FND_RESP'' '
452 ||' and uar.user_name = ur.user_name '
453 ||' and uar.role_name = ur.role_name '
454 ||' and uar.start_date <= sysdate '
455 ||' and (uar.end_date is null or uar.end_date >= sysdate) '
456 || l_sub_revoked_role_names ;
457
458 l_resp_set_existing_sql :=
459 ' select cst.group_code '
460 ||' from '
461 || G_AMW_USER_ROLES||' ur '
462 ||' ,'||G_AMW_user||' u '
463 ||' ,amw_constraint_entries cst '
464 ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
465 ||' where u.user_id = :1 '
466 ||' and cst.constraint_rev_id = :2 '
467 ||' and u.user_name = ur.user_name '
468 ||' and ur.role_orig_system_id = cst.function_id '
469 ||' and ur.role_orig_system = ''FND_RESP'' '
470 ||' and uar.user_name = ur.user_name '
471 ||' and uar.role_name = ur.role_name '
472 ||' and uar.start_date <= sysdate '
473 ||' and (uar.end_date is null or uar.end_date >= sysdate) '
474 || l_sub_revoked_role_names;
475
476
477 IF (p_user_id IS NOT NULL AND p_role_names IS NOT NULL AND p_role_names.FIRST IS NOT NULL) THEN
478
479 -- store pass-in p_role_names as flat string
480 l_role_names := ''''||p_role_names(1)||'''';
481 FOR i IN 2 .. p_role_names.COUNT
482 LOOP
483 l_role_names := l_role_names||','''||p_role_names(i)||'''';
484 END LOOP;
485
486 l_sub_role_names :=
487 ' SELECT distinct SUPER_NAME '||
488 ' FROM WF_ROLE_HIERARCHIES '||
489 ' WHERE ENABLED_FLAG = ''Y'' '||
490 ' CONNECT BY PRIOR SUPER_NAME = SUB_NAME '||
491 ' AND PRIOR ENABLED_FLAG = ''Y'' '||
492 ' START WITH SUB_NAME in ( '|| l_role_names || ' ) ' ||
493 ' union all '||
494 ' SELECT NAME '||
495 ' FROM WF_ROLES '||
496 ' WHERE NAME IN ( '|| l_role_names || ' ) ';
497
498
499 -- check all valid constraints
500 OPEN c_all_valid_constraints;
501 LOOP
502 FETCH c_all_valid_constraints INTO l_all_valid_constraints;
503 EXIT WHEN c_all_valid_constraints%NOTFOUND;
504
505 -- check if this user is waived (due to User Waiver) from this constraint
506 OPEN c_valid_user_waivers(l_all_valid_constraints.constraint_rev_id, p_user_id);
507 FETCH c_valid_user_waivers INTO l_valid_user_waiver_count;
508 CLOSE c_valid_user_waivers;
509
510 IF l_valid_user_waiver_count <= 0 THEN
511
512 l_func_dynamic_sql :=
513 'select count(distinct function_id) from ( '
514 || l_func_existing_sql
515 ||' UNION ALL '
516 ||' select rcd.function_id '
517 ||' from amw_role_constraint_denorm rcd '
518 ||' where rcd.constraint_rev_id = :5 '
519 ||' and rcd.role_name in ( '||l_sub_role_names||' ) '
520 ||') ';
521
522 l_func_set_dynamic_sql :=
523 'select count(distinct group_code) from ( '
524 || l_func_set_existing_sql
525 ||' UNION ALL '
526 ||' select rcd.group_code '
527 ||' from amw_role_constraint_denorm rcd '
528 ||' where rcd.constraint_rev_id = :5 '
529 ||' and rcd.role_name in ( '||l_sub_role_names||' ) '
530 ||') ';
531
532 l_resp_dynamic_sql :=
533 'select count(distinct role_orig_system_id) from ( '
534 || l_resp_existing_sql
535 ||' UNION ALL '
536 ||' select distinct rle.orig_system_id '
537 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
538 ||' , amw_constraint_entries cst '
539 ||' where rle.orig_system = ''FND_RESP'' '
540 ||' and cst.constraint_rev_id = :3 '
541 ||' and cst.function_id = rle.orig_system_id '
542 ||' and rle.name in ( '||l_sub_role_names||' ) '
543 ||') ';
544
545 l_resp_set_dynamic_sql :=
546 'select count(distinct group_code) from ( '
547 || l_resp_set_existing_sql
548 ||' UNION ALL '
549 ||' select cst.group_code '
550 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
551 ||' , amw_constraint_entries cst '
552 ||' where rle.orig_system = ''FND_RESP'' '
553 ||' and cst.constraint_rev_id = :3 '
554 ||' and cst.function_id = rle.orig_system_id '
555 ||' and rle.name in ( '||l_sub_role_names||' ) '
556 ||') ';
557
558 l_violating_new_roles_sql :=
559 'select resp.responsibility_name from fnd_responsibility_vl resp'
560 ||' where resp.responsibility_id in ( '
561 ||' select function_id from amw_constraint_entries cons, wf_roles rl '
562 ||' where constraint_rev_id = :1 '
563 ||' and cons.application_id = resp.application_id '
564 ||' and cons.function_id = rl.orig_system_id '
565 ||' and rl.orig_system = ''FND_RESP'' '
566 ||' and rl.name in (' ||l_sub_role_names||' ) '
567 ||' and rl.owner_tag = (select application_short_name '
568 ||' from fnd_application app where app.application_id = resp.application_id)) ';
569
570 l_violating_existing_roles_sql :=
571 'select resp.responsibility_name from fnd_responsibility_vl resp'
572 ||' where resp.responsibility_id in ( '
573 ||' select function_id from amw_constraint_entries cons, wf_roles rl '
574 ||' where constraint_rev_id = :1 '
575 ||' and cons.application_id = resp.application_id '
576 ||' and cons.function_id = rl.orig_system_id '
577 ||' and rl.orig_system = ''FND_RESP'' '
578 ||' and rl.name not in (' ||l_sub_role_names||' ) '
579 ||' and rl.owner_tag = (select application_short_name '
580 ||' from fnd_application app where app.application_id = resp.application_id)) ';
581
582
583 IF 'ALL' = l_all_valid_constraints.type_code THEN
584 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
585 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
586 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
587 CLOSE c_constraint_entries_count;
588
589 -- 12:16:05 : psomanat
590 -- Check to see if violation is due to the allready
591 -- assigned violating roles
592
593 -- 5:15:06 by dliao remove because we need check if the new assigned roles violate the constraint
594 -- even if the allready assigned violating roles exist.
595 /***
596 l_vio_exist_role_sql :='select count(distinct function_id)'
597 ||'from ('
598 ||' select function_id from amw_constraint_entries where constraint_rev_id = :1'
599 ||' MINUS '
600 ||'select distinct function_id from ( '
601 || l_func_existing_sql
602 ||') '
603 ||')';
604 ***/
605
606 --add 5:15:06 by dliao
607 --check the new roles
608 l_cst_new_violation_sql :=
609 ' select count(rcd.function_id) '
610 ||' from amw_role_constraint_denorm rcd '
611 ||' where rcd.constraint_rev_id = :1 '
612 ||' and rcd.role_name in ( '||l_sub_role_names||' ) ' ;
613
614
615 OPEN func_acess_count_c FOR l_cst_new_violation_sql USING
616 l_all_valid_constraints.constraint_rev_id;
617 FETCH func_acess_count_c INTO l_func_access_count;
618 CLOSE func_acess_count_c;
619
620 -- If the count is 0 then the violation is due to the allready
621 -- assigned violating role.
622 IF l_func_access_count > 0 THEN
623
624 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
625 l_all_valid_constraints.constraint_rev_id,
626 p_user_id,
627 l_all_valid_constraints.constraint_rev_id,
628 p_user_id,
629 l_all_valid_constraints.constraint_rev_id;
630 FETCH func_acess_count_c INTO l_func_access_count;
631 CLOSE func_acess_count_c;
632
633 -- in ALL type: if user can access to all entries of this constraint,
634 -- he violates this constraint
635 IF l_func_access_count = l_constraint_entries_count THEN
636
637
638
639 -- Check to see if the fuction enteries in the constraint is same
640 -- as the functions the user can access due to the assigning of
641 -- this role
642 l_func_sql :='select count(distinct function_id)'
643 ||'from ('
644 ||'select distinct function_id from ( '
645 || l_func_existing_sql
646 ||' UNION ALL '
647 ||' select rcd.function_id '
648 ||' from amw_role_constraint_denorm rcd '
649 ||' where rcd.constraint_rev_id = :5 '
650 ||' and rcd.role_name in ( '||l_sub_role_names||' ) '
651 ||') '
652 ||' MINUS '
653 ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :6'
654 ||')';
655
656
657 OPEN func_acess_count_c FOR l_func_sql USING
658 l_all_valid_constraints.constraint_rev_id,
659 p_user_id,
660 l_all_valid_constraints.constraint_rev_id,
661 p_user_id,
662 l_all_valid_constraints.constraint_rev_id,
663 l_all_valid_constraints.constraint_rev_id;
664 FETCH func_acess_count_c INTO l_func_access_count;
665 CLOSE func_acess_count_c;
666
667 IF l_func_access_count = 0 THEN
668 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
669 FND_FILE.put_line(fnd_file.log, '----fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
670 x_has_violation := 'Y';
671 EXIT;
672 END IF;
673 END IF;
674 END IF;
675 ELSIF 'ME' = l_all_valid_constraints.type_code THEN
676
677 -- 12:16:2005 : psomanat
678 -- Check to see if violation is due to the allready
679 -- assigned violating roles
680 -- 5:15:06 by dliao remove because we need check if the new assigned roles violate the constraint
681 -- even if the allready assigned violating roles exist.
682 /********************************************************
683 l_vio_exist_role_sql :='select count(distinct function_id) from ( '
684 || l_func_existing_sql
685 ||')';
686
687 OPEN func_acess_count_c FOR l_vio_exist_role_sql USING
688 l_all_valid_constraints.constraint_rev_id,
689 p_user_id,
690 l_all_valid_constraints.constraint_rev_id,
691 p_user_id,
692 l_all_valid_constraints.constraint_rev_id,
693 p_user_id,
694 l_all_valid_constraints.constraint_rev_id;
695 FETCH func_acess_count_c INTO l_func_access_count;
696 CLOSE func_acess_count_c;
697 ****************************************************/
698
699 --PSOMANAT :23:01:2006
700 -- scenario added : The user allready violates a constraint and again
701 -- a new violating role is added
702
703 --DLIAO 05:12:2006
704 -- remove this condition because l_func_access_count will be 0 if the existing
705 -- roles which the user was assigned don't violate any constraint.
706 -- IF l_func_access_count >= 1 THEN
707
708
709 l_cst_new_violation_sql :=
710 ' select count(rcd.function_id) '
711 ||' from amw_role_constraint_denorm rcd '
712 ||' where rcd.constraint_rev_id = :1 '
713 ||' and rcd.role_name in ( '||l_sub_role_names||' ) ' ;
714
715
716 -- The cursor checks if the current constraint violation is related
717 -- role being added to the user.
718 -- This is done to avoid the following problem :
719 -- The user may allready have roles violating a differnt constraint
720 -- then the one under process. If we don't check to see if the role
721 -- assigned is related with the current constraint, there is a possibility
722 -- of reporting the existing violation again and again even thought they are
723 -- allready assigned.
724
725 OPEN new_violation_count_c FOR l_cst_new_violation_sql USING
726 l_all_valid_constraints.constraint_rev_id;
727 FETCH new_violation_count_c INTO l_func_access_count;
728 CLOSE new_violation_count_c;
729
730 -- If the function count is > 0 then the violation is due to
731 -- the current constraint.
732 -- If not then the violation is due to some other constraint.
733 -- So we need to continue to find the correct constraint
734 IF l_func_access_count > 0 THEN
735
736 -- find the number of distinct constraint entries this user can access
737 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
738 l_all_valid_constraints.constraint_rev_id,
739 p_user_id,
740 l_all_valid_constraints.constraint_rev_id,
741 p_user_id,
742 l_all_valid_constraints.constraint_rev_id;
743 FETCH func_acess_count_c INTO l_func_access_count;
744 CLOSE func_acess_count_c;
745
746 -- in ME type: if user can access at least two entries of this constraint,
747 -- he violates this constraint
748 IF l_func_access_count >= 2 THEN
749 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
750 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
751 x_has_violation := 'Y';
752 exit;
753 END IF;
754 END IF;
755
756 ELSIF 'SET' = l_all_valid_constraints.type_code THEN
757 -- 12:16:2005 : psomanat
758 -- Check to see if violation is due to the allready
759 -- assigned violating roles
760 -- 5:15:06 by dliao remove because we need check if the new assigned roles violate the constraint
761 -- even if the allready assigned violating roles exist.
762 /******************************************
763 l_vio_exist_role_sql :='select count(distinct group_code) from ( '
764 || l_func_set_existing_sql
765 || ') ';
766 OPEN group_acess_count_c FOR l_vio_exist_role_sql USING
767 l_all_valid_constraints.constraint_rev_id,
768 p_user_id,
769 l_all_valid_constraints.constraint_rev_id,
770 p_user_id,
771 l_all_valid_constraints.constraint_rev_id,
772 p_user_id,
773 l_all_valid_constraints.constraint_rev_id;
774 FETCH group_acess_count_c INTO l_group_access_count;
775 CLOSE group_acess_count_c;
776 *********************************************/
777
778 -- PSOMANAT :23:01:2006
779 -- scenario added : The user allready violates a constraint and again
780 -- a new violating role is added
781 --DLIAO 05:12:2006
782 -- remove this condition because l_func_access_count will be 0 if the existing
783 -- roles which the user was assigned don't violate any constraint.
784
785 --IF l_group_access_count >= 1 THEN
786
787 l_cst_new_violation_sql :=
788 ' select count( distinct rcd.group_code) '
789 ||' from amw_role_constraint_denorm rcd '
790 ||' where rcd.constraint_rev_id = :1 '
791 ||' and rcd.role_name in ( '||l_sub_role_names||' ) ' ;
792
793 -- The cursor checks if the current constraint violation is related
794 -- role being added to the user.
795 -- This is done to avoid the following problem :
796 -- The user may allready have roles violating a differnt constraint
797 -- then the one under process. If we don't check to see if the role
798 -- assigned is related with the current constraint, there is a possibility
799 -- of reporting the existing violation again and again even thought they are
800 -- allready assigned.
801
802 OPEN new_violation_count_c FOR l_cst_new_violation_sql USING
803 l_all_valid_constraints.constraint_rev_id;
804 FETCH new_violation_count_c INTO l_group_access_count;
805 CLOSE new_violation_count_c;
806
807 -- If the group access count is > 0 then the violation is due to
808 -- the current constraint.
809 -- If not then the violation is due to some other constraint.
810 -- So we need to continue to find the correct constraint
811 IF l_group_access_count > 0 THEN
812 -- find the number of distinct constraint entries this user can access
813 OPEN group_acess_count_c FOR l_func_set_dynamic_sql USING
814 l_all_valid_constraints.constraint_rev_id,
815 p_user_id,
816 l_all_valid_constraints.constraint_rev_id,
817 p_user_id,
818 l_all_valid_constraints.constraint_rev_id;
819 FETCH group_acess_count_c INTO l_group_access_count;
820 CLOSE group_acess_count_c;
821
822
823 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
824 -- he violates this constraint
825 IF l_group_access_count >= 2 THEN
826 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
827 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
828 x_has_violation := 'Y';
829 END IF;
830 END IF;
831
832 ELSIF 'RESPALL' = l_all_valid_constraints.type_code THEN
833
834 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
835 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
836 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
837 CLOSE c_constraint_entries_count;
838
839 -- Check to see if violation is due to the allready
840 -- assigned violating roles
841 l_vio_exist_resp_sql :='select count(distinct function_id)'
842 ||'from ('
843 ||' select function_id from amw_constraint_entries where constraint_rev_id = :1'
844 ||' MINUS '
845 ||'select distinct role_orig_system_id from ( '
846 || l_resp_existing_sql
847 ||') '
848 ||')';
849
850 OPEN resp_acess_count_c FOR l_vio_exist_resp_sql USING
851 l_all_valid_constraints.constraint_rev_id,
852 p_user_id,
853 l_all_valid_constraints.constraint_rev_id;
854 FETCH resp_acess_count_c INTO l_resp_access_count;
855 CLOSE resp_acess_count_c;
856
857
858 -- If the count is 0 then the violation is due to the allready
859 -- assigned violating role.
860 IF l_resp_access_count <> 0 THEN
861
862 OPEN resp_acess_count_c FOR l_resp_dynamic_sql USING
863 p_user_id,
864 l_all_valid_constraints.constraint_rev_id,
865 l_all_valid_constraints.constraint_rev_id;
866 FETCH resp_acess_count_c INTO l_resp_access_count;
867 CLOSE resp_acess_count_c;
868
869 -- in ALL type: if user can access to all entries of this constraint,
870 -- he violates this constraint
871 IF l_resp_access_count = l_constraint_entries_count THEN
872
873 -- Check to see if the fuction enteries in the constraint is same
874 -- as the functions the user can access due to the assigning of
875 -- this role
876
877 l_resp_sql := 'select count(distinct role_orig_system_id)'
878 ||' from ('
879 || l_resp_existing_sql
880 ||' UNION ALL '
881 ||' select distinct rle.orig_system_id as role_orig_system_id '
882 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
883 ||' , amw_constraint_entries cst '
884 ||' where rle.orig_system = ''FND_RESP'' '
885 ||' and cst.constraint_rev_id = :3 '
886 ||' and cst.function_id = rle.orig_system_id '
887 ||' and rle.name in ( '||l_sub_role_names||' ) '
888 ||' MINUS '
889 ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :4'
890 ||')';
891
892
893
894 OPEN resp_acess_count_c FOR l_resp_sql USING
895 p_user_id,
896 l_all_valid_constraints.constraint_rev_id,
897 l_all_valid_constraints.constraint_rev_id,
898 l_all_valid_constraints.constraint_rev_id;
899 FETCH resp_acess_count_c INTO l_resp_access_count;
900 CLOSE resp_acess_count_c;
901
902 IF l_resp_access_count = 0 THEN
903 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
904 FND_FILE.put_line(fnd_file.log, '----fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
905 x_has_violation := 'Y';
906
907 OPEN violating_new_resps_c FOR l_violating_new_roles_sql USING
908 l_all_valid_constraints.constraint_rev_id;
909 FETCH violating_new_resps_c BULK COLLECT INTO l_violating_new_resp_table;
910 CLOSE violating_new_resps_c;
911
912 OPEN violating_old_resps_c FOR l_violating_existing_roles_sql USING
913 l_all_valid_constraints.constraint_rev_id;
914 FETCH violating_old_resps_c BULK COLLECT INTO l_violating_old_resp_table;
915 CLOSE violating_old_resps_c;
916
917 IF l_violating_new_resp_table IS NOT NULL AND l_violating_new_resp_table.FIRST IS NOT NULL THEN
918 x_new_resp_name := l_violating_new_resp_table(1);
919 FOR i in 2 .. l_violating_new_resp_table.COUNT
920 LOOP
921 x_new_resp_name := x_new_resp_name ||','|| l_violating_new_resp_table(i);
922 END LOOP;
923 END IF; -- end of if: l_violating_new_resp_table IS NOT NULL
924
925 IF l_violating_old_resp_table IS NOT NULL AND l_violating_old_resp_table.FIRST IS NOT NULL THEN
926 x_existing_resp_name := '''' || l_violating_old_resp_table(1);
927 FOR j in 2 .. l_violating_old_resp_table.COUNT
928 LOOP
929 x_existing_resp_name := x_existing_resp_name ||','|| l_violating_old_resp_table(j);
930 END LOOP;
931 x_existing_resp_name := x_existing_resp_name || '''';
932 END IF; -- end of if: l_violating_old_resp_table IS NOT NULL
933
934 EXIT;
935 END IF;
936 END IF;
937 END IF;
938
939 ELSIF 'RESPME' = l_all_valid_constraints.type_code THEN
940
941 -- Check to see if violation is due to the allready
942 -- assigned violating roles
943 /*********************
944 l_vio_exist_resp_sql :='select count(distinct role_orig_system_id)'
945 ||'from ('
946 || l_resp_existing_sql
947 ||')';
948
949 OPEN resp_acess_count_c FOR l_vio_exist_resp_sql USING
950 p_user_id,
951 l_all_valid_constraints.constraint_rev_id;
952 FETCH resp_acess_count_c INTO l_resp_access_count;
953 CLOSE resp_acess_count_c;
954
955 *********************/
956
957 -- scenario added : The user allready violates a constraint and again
958 -- a new violating role is added
959 -- remove this condition because l_resp_access_count will be 0 if the existing
960 -- roles which the user is assigned don't violate any constraint.
961 -- IF l_resp_access_count >= 1 THEN
962
963
964 l_cst_new_violation_sql :=
965 ' select distinct rle.display_name '
966 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
967 ||' , amw_constraint_entries cst '
968 ||' where rle.orig_system = ''FND_RESP'' '
969 ||' and cst.constraint_rev_id = :1 '
970 ||' and cst.function_id = rle.orig_system_id '
971 ||' and rle.name in ( '||l_sub_role_names||' ) '
972 ||' and rle.owner_tag = (select application_short_name '
973 ||' from fnd_application app where app.application_id = cst.application_id)';
974
975
976 -- The cursor checks if the current constraint violation is related
977 -- role being added to the user.
978 -- This is done to avoid the following problem :
979 -- The user may allready have roles violating a differnt constraint
980 -- then the one under process. If we don't check to see if the role
981 -- assigned is related with the current constraint, there is a possibility
982 -- of reporting the existing violation again and again even thought they are
983 -- allready assigned.
984
985 OPEN new_violation_count_c FOR l_cst_new_violation_sql USING
986 l_all_valid_constraints.constraint_rev_id;
987 FETCH new_violation_count_c BULK COLLECT INTO l_new_violation_table;
988 CLOSE new_violation_count_c;
989
990 l_existing_violation_sql :=
991 ' select distinct rle.display_name '
992 ||' from '
993 || G_AMW_USER_ROLES||' ur '
994 ||' ,'||G_AMW_user||' u '
995 ||' ,amw_constraint_entries cst '
996 ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
997 ||' ,'||G_AMW_ALL_ROLES_VL || ' rle '
998 ||' where u.user_id = :1 '
999 ||' and cst.constraint_rev_id = :2 '
1000 ||' and u.user_name = ur.user_name '
1001 ||' and ur.role_orig_system_id = cst.function_id '
1002 ||' and ur.role_orig_system = ''FND_RESP'' '
1003 ||' and ur.role_orig_system = rle.orig_system '
1004 ||' and ur.role_orig_system_id = rle.orig_system_id '
1005 ||' and ur.role_name = rle.name '
1006 ||' and rle.owner_tag = (select application_short_name '
1007 ||' from fnd_application app where app.application_id = cst.application_id) '
1008 ||' and uar.user_name = ur.user_name '
1009 ||' and uar.role_name = ur.role_name '
1010 ||' and uar.start_date <= sysdate '
1011 ||' and (uar.end_date is null or uar.end_date >= sysdate) '
1012 || l_sub_revoked_role_names ;
1013
1014
1015
1016 OPEN existing_violation_c FOR l_existing_violation_sql USING
1017 p_user_id,
1018 l_all_valid_constraints.constraint_rev_id;
1019 FETCH existing_violation_c BULK COLLECT INTO l_existing_violation_table;
1020 CLOSE existing_violation_c;
1021
1022 -- If the function count is > 0 then the violation is due to
1023 -- the current constraint.
1024 -- If not then the violation is due to some other constraint.
1025 -- So we need to continue to find the correct constraint
1026 -- in ME type: if user can access at least two entries of this constraint,
1027 -- he violates this constraint
1028
1029 IF ( l_new_violation_table.COUNT > 0) AND (l_existing_violation_table.COUNT + l_new_violation_table.COUNT >= 2) THEN
1030
1031 -- Check to see if the fuction enteries in the constraint is same
1032 -- as the functions the user can access due to the assigning of
1033 -- this role
1034
1035 l_resp_sql := 'select count(distinct role_orig_system_id)'
1036 ||' from ('
1037 || l_resp_existing_sql
1038 ||' UNION ALL '
1039 ||' select distinct rle.orig_system_id as role_orig_system_id '
1040 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
1041 ||' , amw_constraint_entries cst '
1042 ||' where rle.orig_system = ''FND_RESP'' '
1043 ||' and cst.constraint_rev_id = :3 '
1044 ||' and cst.function_id = rle.orig_system_id '
1045 ||' and rle.name in ( '||l_sub_role_names||' ) '
1046 ||' and rle.owner_tag = (select application_short_name '
1047 ||' from fnd_application app where app.application_id = cst.application_id)'
1048 ||' MINUS '
1049 ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :4'
1050 ||')';
1051
1052
1053 -- find the number of distinct constraint entries this user can access
1054
1055 OPEN resp_acess_count_c FOR l_resp_sql USING
1056 p_user_id,
1057 l_all_valid_constraints.constraint_rev_id,
1058 l_all_valid_constraints.constraint_rev_id,
1059 l_all_valid_constraints.constraint_rev_id;
1060 FETCH resp_acess_count_c INTO l_resp_access_count;
1061 CLOSE resp_acess_count_c;
1062
1063
1064
1065 IF l_resp_access_count = 0 THEN
1066 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1067 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1068 x_has_violation := 'Y';
1069
1070 IF l_new_violation_table IS NOT NULL AND l_new_violation_table.FIRST IS NOT NULL THEN
1071 x_new_resp_name := l_new_violation_table(1);
1072 FOR i in 2 .. l_new_violation_table.COUNT
1073 LOOP
1074 x_new_resp_name := x_new_resp_name ||','|| l_new_violation_table(i);
1075 END LOOP;
1076 END IF; -- end of if: l_new_violation_table IS NOT NULL
1077
1078 IF l_existing_violation_table IS NOT NULL AND l_existing_violation_table.FIRST IS NOT NULL THEN
1079 x_existing_resp_name := '''' || l_existing_violation_table(1);
1080 FOR j in 2 .. l_existing_violation_table.COUNT
1081 LOOP
1082 x_existing_resp_name := x_existing_resp_name ||','|| l_existing_violation_table(j);
1083 END LOOP;
1084 x_existing_resp_name := x_existing_resp_name || '''';
1085 END IF; -- end of if: l_existing_violation_table IS NOT NULL
1086
1087 exit;
1088 END IF;
1089 END IF;
1090 --END IF;
1091
1092 ELSIF 'RESPSET' = l_all_valid_constraints.type_code THEN
1093
1094 -- Check to see if violation is due to the allready
1095 -- assigned violating roles
1096 -- 5:15:06 by dliao remove because we need check if the new assigned roles violate the constraint
1097 -- even if the allready assigned violating roles exist.
1098 /***********************************************
1099 l_vio_exist_resp_sql :='select count(distinct role_orig_system_id)'
1100 ||'from ('
1101 ||'select distinct role_orig_system_id from ( '
1102 || l_resp_existing_sql
1103 ||') '
1104 ||')';
1105
1106
1107 OPEN group_acess_count_c FOR l_vio_exist_resp_sql USING
1108 p_user_id,
1109 l_all_valid_constraints.constraint_rev_id;
1110 FETCH group_acess_count_c INTO l_group_access_count;
1111 CLOSE group_acess_count_c;
1112
1113 *****************************************************/
1114
1115 -- scenario added : The user allready violates a constraint and again
1116 -- a new violating role is added
1117 --IF l_group_access_count >= 1 THEN
1118
1119 l_cst_new_violation_sql :=
1120 ' select distinct rle.display_name '
1121 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
1122 ||' , amw_constraint_entries cst '
1123 ||' where rle.orig_system = ''FND_RESP'' '
1124 ||' and cst.constraint_rev_id = :1 '
1125 ||' and cst.function_id = rle.orig_system_id '
1126 ||' and rle.name in ( '||l_sub_role_names||' ) '
1127 ||' and rle.owner_tag = (select application_short_name '
1128 ||' from fnd_application app where app.application_id = cst.application_id)';
1129
1130 -- The cursor checks if the current constraint violation is related
1131 -- role being added to the user.
1132 -- This is done to avoid the following problem :
1133 -- The user may allready have roles violating a differnt constraint
1134 -- then the one under process. If we don't check to see if the role
1135 -- assigned is related with the current constraint, there is a possibility
1136 -- of reporting the existing violation again and again even thought they are
1137 -- allready assigned.
1138
1139
1140 OPEN new_violation_count_c FOR l_cst_new_violation_sql USING
1141 l_all_valid_constraints.constraint_rev_id;
1142 FETCH new_violation_count_c BULK COLLECT INTO l_new_violation_table;
1143 CLOSE new_violation_count_c;
1144
1145 l_existing_violation_sql :=
1146 ' select distinct rle.display_name '
1147 ||' from '
1148 || G_AMW_USER_ROLES||' ur '
1149 ||' ,'||G_AMW_user||' u '
1150 ||' ,amw_constraint_entries cst '
1151 ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
1152 ||' ,'||G_AMW_ALL_ROLES_VL || ' rle '
1153 ||' where u.user_id = :1 '
1154 ||' and cst.constraint_rev_id = :2 '
1155 ||' and u.user_name = ur.user_name '
1156 ||' and ur.role_orig_system_id = cst.function_id '
1157 ||' and ur.role_orig_system = ''FND_RESP'' '
1158 ||' and ur.role_orig_system = rle.orig_system '
1159 ||' and ur.role_orig_system_id = rle.orig_system_id '
1160 ||' and ur.role_name = rle.name '
1161 ||' and rle.owner_tag = (select application_short_name '
1162 ||' from fnd_application app where app.application_id = cst.application_id) '
1163 ||' and uar.user_name = ur.user_name '
1164 ||' and uar.role_name = ur.role_name '
1165 ||' and uar.start_date <= sysdate '
1166 ||' and (uar.end_date is null or uar.end_date >= sysdate) '
1167 || l_sub_revoked_role_names ;
1168
1169
1170
1171 OPEN existing_violation_c FOR l_existing_violation_sql USING
1172 p_user_id,
1173 l_all_valid_constraints.constraint_rev_id;
1174 FETCH existing_violation_c BULK COLLECT INTO l_existing_violation_table;
1175 CLOSE existing_violation_c;
1176
1177
1178
1179
1180
1181 -- If the function count is > 0 then the violation is due to
1182 -- the current constraint.
1183 -- If not then the violation is due to some other constraint.
1184 -- So we need to continue to find the correct constraint
1185 IF l_new_violation_table.COUNT > 0 THEN
1186
1187 -- find the number of distinct constraint entries this user can access
1188
1189 OPEN resp_acess_count_c FOR l_resp_set_dynamic_sql USING
1190 p_user_id,
1191 l_all_valid_constraints.constraint_rev_id,
1192 l_all_valid_constraints.constraint_rev_id;
1193 FETCH resp_acess_count_c INTO l_resp_access_count;
1194 CLOSE resp_acess_count_c;
1195
1196 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
1197 -- he violates this constraint
1198 IF l_resp_access_count >= 2 THEN
1199 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1200 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1201 x_has_violation := 'Y';
1202
1203
1204 IF l_new_violation_table IS NOT NULL AND l_new_violation_table.FIRST IS NOT NULL THEN
1205 x_new_resp_name := l_new_violation_table(1);
1206 FOR i in 2 .. l_new_violation_table.COUNT
1207 LOOP
1208 x_new_resp_name := x_new_resp_name ||','|| l_new_violation_table(i);
1209 END LOOP;
1210 END IF; -- end of if: l_new_violation_table IS NOT NULL
1211
1212 IF l_existing_violation_table IS NOT NULL AND l_existing_violation_table.FIRST IS NOT NULL THEN
1213 x_existing_resp_name := '''' || l_existing_violation_table(1);
1214 FOR j in 2 .. l_existing_violation_table.COUNT
1215 LOOP
1216 x_existing_resp_name := x_existing_resp_name ||','|| l_existing_violation_table(j);
1217 END LOOP;
1218 x_existing_resp_name := x_existing_resp_name || '''';
1219 END IF; -- end of if: l_existing_violation_table IS NOT NULL
1220
1221
1222 EXIT;
1223 END IF;
1224 END IF;
1225 ELSE
1226 -- other constraint types
1227 NULL;
1228 END IF; -- end of if: constraint type_code
1229 END IF; -- end of if: l_valid_user_waiver_count <= 0
1230 END LOOP; --end of loop: c_all_valid_constraints
1231 CLOSE c_all_valid_constraints;
1232
1233 END IF; -- end of if: p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL AND p_role_names IS NOT NULL AND p_role_names.FIRST IS NOT NULL
1234 -- only output valid region name if having violation; otherwise, NULL
1235
1236 IF x_has_violation = 'Y' THEN
1237 x_violat_region := '/oracle/apps/amw/audit/duty/webui/RoleAssignViolationRN';
1238 x_violat_btn_region := '/oracle/apps/amw/audit/duty/webui/RoleAssignViolationOverrideBtnRN';
1239 END IF;
1240 exception
1241 when others then
1242 dbms_output.put_line('exception');
1243 END Has_Violations;
1244
1245
1246
1247 -- ===============================================================
1248 -- Function name
1249 -- Has_Violation_Due_To_Resp
1250 --
1251 -- Purpose
1252 -- check for OICM SOD constriants that will be violated
1253 -- if the user is assigned the additional responsibility
1254 -- Params
1255 -- p_user_id := input fnd user_id
1256 -- p_responsibility_id := input fnd responsibility_id
1257 -- Return
1258 -- 'N' := if no SOD violation found.
1259 -- 'Y' := if SOD violation exists.
1260 -- The SOD violation should NOT be restricted to
1261 -- only the new responsiblity.
1262 -- If the existing responsibilities have any violations,
1263 -- the function should return 'Y' as well.
1264 --
1265 -- History
1266 -- 07/13/2005 tsho Create
1267 -- 08/03/2005 tsho Consider User Waivers
1268 -- 08/22/2005 tsho Consider only prevent(PR) constraint objective
1269 -- ===============================================================
1270 Function Has_Violation_Due_To_Resp (
1271 p_user_id IN NUMBER,
1272 p_responsibility_id IN NUMBER
1273 ) return VARCHAR2
1274 IS
1275 L_API_NAME CONSTANT VARCHAR2(30) := 'Has_Violation_Due_To_Resp';
1276 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1277
1278 -- return result
1279 has_violation VARCHAR2(32767);
1280
1281 -- find all valid constraints
1282 CURSOR c_all_valid_constraints IS
1283 SELECT constraint_rev_id, type_code, objective_code
1284 FROM amw_constraints_b
1285 WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);
1286 l_all_valid_constraints c_all_valid_constraints%ROWTYPE;
1287
1288 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
1289 l_constraint_entries_count NUMBER;
1290 l_func_access_count NUMBER;
1291 l_group_access_count NUMBER;
1292 CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
1293 SELECT count(*)
1294 FROM amw_constraint_entries
1295 WHERE constraint_rev_id=l_constraint_rev_id;
1296
1297 TYPE refCurTyp IS REF CURSOR;
1298 func_acess_count_c refCurTyp;
1299 group_acess_count_c refCurTyp;
1300
1301 l_func_dynamic_sql VARCHAR2(2500) :=
1302 'select count(distinct function_id) from ( '
1303 ||' select rcd.function_id '
1304 ||' from amw_role_constraint_denorm rcd '
1305 ||' ,'||G_AMW_USER_ROLES||' ur '
1306 ||' ,'||G_AMW_user||' u '
1307 ||' where rcd.constraint_rev_id = :1 '
1308 ||' and u.user_id = :2 '
1309 ||' and u.user_name = ur.user_name '
1310 ||' and ur.role_name = rcd.role_name '
1311 ||' and ur.role_orig_system = ''UMX'' '
1312 ||' UNION ALL '
1313 ||' select rcd.function_id '
1314 ||' from amw_role_constraint_denorm rcd '
1315 ||' ,'||G_AMW_USER_ROLES||' ur '
1316 ||' ,'||G_AMW_user||' u '
1317 ||' where rcd.constraint_rev_id = :3 '
1318 ||' and u.user_id = :4 '
1319 ||' and u.user_name = ur.user_name '
1320 ||' and ur.role_orig_system_id = rcd.responsibility_id '
1321 ||' and ur.role_orig_system = ''FND_RESP'' '
1322 ||' UNION ALL '
1323 ||' select rcd.function_id '
1324 ||' from amw_role_constraint_denorm rcd '
1325 ||' ,'||G_AMW_GRANTS||' gra '
1326 ||' ,'||G_AMW_USER||' u '
1327 ||' where rcd.constraint_rev_id = :5 '
1328 ||' and u.user_id = :6 '
1329 ||' and u.user_name = gra.grantee_key '
1330 ||' and gra.grantee_type = ''USER'' '
1331 ||' and gra.menu_id = rcd.menu_id '
1332 ||' and gra.instance_type = ''GLOBAL'' '
1333 ||' and gra.object_id = -1 '
1334 ||' UNION ALL '
1335 ||' select rcd.function_id '
1336 ||' from amw_role_constraint_denorm rcd '
1337 ||' ,'||G_AMW_GRANTS||' gra '
1338 ||' where rcd.constraint_rev_id = :7 '
1339 ||' and gra.grantee_key = ''GLOBAL'' '
1340 ||' and gra.grantee_type = ''GLOBAL'' '
1341 ||' and gra.menu_id = rcd.menu_id '
1342 ||' and gra.instance_type = ''GLOBAL'' '
1343 ||' and gra.object_id = -1 '
1344 ||' UNION ALL '
1345 ||' select rcd.function_id '
1346 ||' from amw_role_constraint_denorm rcd '
1347 ||' where rcd.constraint_rev_id = :8 '
1348 ||' and rcd.responsibility_id = :9 '
1349 ||') ';
1350
1351 l_func_set_dynamic_sql VARCHAR2(2500) :=
1352 'select count(distinct group_code) from ( '
1353 ||' select rcd.group_code '
1354 ||' from amw_role_constraint_denorm rcd '
1355 ||' ,'||G_AMW_USER_ROLES||' ur '
1356 ||' ,'||G_AMW_user||' u '
1357 ||' where rcd.constraint_rev_id = :1 '
1358 ||' and u.user_id = :2 '
1359 ||' and u.user_name = ur.user_name '
1360 ||' and ur.role_name = rcd.role_name '
1361 ||' and ur.role_orig_system = ''UMX'' '
1362 ||' UNION ALL '
1363 ||' select rcd.group_code '
1364 ||' from amw_role_constraint_denorm rcd '
1365 ||' ,'||G_AMW_USER_ROLES||' ur '
1366 ||' ,'||G_AMW_user||' u '
1367 ||' where rcd.constraint_rev_id = :3 '
1368 ||' and u.user_id = :4 '
1369 ||' and u.user_name = ur.user_name '
1370 ||' and ur.role_orig_system_id = rcd.responsibility_id '
1371 ||' and ur.role_orig_system = ''FND_RESP'' '
1372 ||' UNION ALL '
1373 ||' select rcd.group_code '
1374 ||' from amw_role_constraint_denorm rcd '
1375 ||' ,'||G_AMW_GRANTS||' gra '
1376 ||' ,'||G_AMW_USER||' u '
1377 ||' where rcd.constraint_rev_id = :5 '
1378 ||' and u.user_id = :6 '
1379 ||' and u.user_name = gra.grantee_key '
1380 ||' and gra.grantee_type = ''USER'' '
1381 ||' and gra.menu_id = rcd.menu_id '
1382 ||' and gra.instance_type = ''GLOBAL'' '
1383 ||' and gra.object_id = -1 '
1384 ||' UNION ALL '
1385 ||' select rcd.group_code '
1386 ||' from amw_role_constraint_denorm rcd '
1387 ||' ,'||G_AMW_GRANTS||' gra '
1388 ||' where rcd.constraint_rev_id = :7 '
1389 ||' and gra.grantee_key = ''GLOBAL'' '
1390 ||' and gra.grantee_type = ''GLOBAL'' '
1391 ||' and gra.menu_id = rcd.menu_id '
1392 ||' and gra.instance_type = ''GLOBAL'' '
1393 ||' and gra.object_id = -1 '
1394 ||' UNION ALL '
1395 ||' select rcd.group_code '
1396 ||' from amw_role_constraint_denorm rcd '
1397 ||' where rcd.constraint_rev_id = :8 '
1398 ||' and rcd.responsibility_id = :9 '
1399 ||') ';
1400
1401 -- get valid user waiver
1402 l_valid_user_waiver_count NUMBER;
1403 CURSOR c_valid_user_waivers (l_constraint_rev_id IN NUMBER, l_user_id IN NUMBER) IS
1404 SELECT count(*)
1405 FROM amw_constraint_waivers
1406 WHERE constraint_rev_id = l_constraint_rev_id
1407 AND object_type = 'USER'
1408 AND PK1 = l_user_id
1409 AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
1410
1411 BEGIN
1412 -- default to 'N', which means user doesn't have violations
1413 has_violation := 'N';
1414 l_valid_user_waiver_count := 0;
1415
1416 IF (p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL) THEN
1417 -- check all valid constraints
1418 OPEN c_all_valid_constraints;
1419 LOOP
1420 FETCH c_all_valid_constraints INTO l_all_valid_constraints;
1421 EXIT WHEN c_all_valid_constraints%NOTFOUND;
1422
1423 -- check if this user is waived (due to User Waiver) from this constraint
1424 OPEN c_valid_user_waivers(l_all_valid_constraints.constraint_rev_id, p_user_id);
1425 FETCH c_valid_user_waivers INTO l_valid_user_waiver_count;
1426 CLOSE c_valid_user_waivers;
1427
1428 -- 08.22.2005 tsho: consider only Prevent Constraint Objective
1429 -- IF l_valid_user_waiver_count <= 0 THEN
1430 IF l_valid_user_waiver_count <= 0 AND l_all_valid_constraints.objective_code = 'PR' THEN
1431
1432 IF 'ALL' = l_all_valid_constraints.type_code THEN
1433 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
1434 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
1435 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
1436 CLOSE c_constraint_entries_count;
1437
1438 -- find the number of distinct constraint entries this user can access
1439 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
1440 l_all_valid_constraints.constraint_rev_id,
1441 p_user_id,
1442 l_all_valid_constraints.constraint_rev_id,
1443 p_user_id,
1444 l_all_valid_constraints.constraint_rev_id,
1445 p_user_id,
1446 l_all_valid_constraints.constraint_rev_id,
1447 l_all_valid_constraints.constraint_rev_id,
1448 p_responsibility_id;
1449 FETCH func_acess_count_c INTO l_func_access_count;
1450 CLOSE func_acess_count_c;
1451
1452 -- in ALL type: if user can access to all entries of this constraint,
1453 -- he violates this constraint
1454 IF l_func_access_count = l_constraint_entries_count THEN
1455 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1456 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1457 has_violation := 'Y';
1458 return has_violation;
1459 END IF;
1460
1461 ELSIF 'ME' = l_all_valid_constraints.type_code THEN
1462 -- find the number of distinct constraint entries this user can access
1463 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
1464 l_all_valid_constraints.constraint_rev_id,
1465 p_user_id,
1466 l_all_valid_constraints.constraint_rev_id,
1467 p_user_id,
1468 l_all_valid_constraints.constraint_rev_id,
1469 p_user_id,
1470 l_all_valid_constraints.constraint_rev_id,
1471 l_all_valid_constraints.constraint_rev_id,
1472 p_responsibility_id;
1473 FETCH func_acess_count_c INTO l_func_access_count;
1474 CLOSE func_acess_count_c;
1475
1476 -- in ME type: if user can access at least two entries of this constraint,
1477 -- he violates this constraint
1478 IF l_func_access_count >= 2 THEN
1479 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1480 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1481 has_violation := 'Y';
1482 return has_violation;
1483 END IF;
1484
1485 ELSIF 'SET' = l_all_valid_constraints.type_code THEN
1486 -- find the number of distinct constraint entries this user can access
1487 OPEN group_acess_count_c FOR l_func_set_dynamic_sql USING
1488 l_all_valid_constraints.constraint_rev_id,
1489 p_user_id,
1490 l_all_valid_constraints.constraint_rev_id,
1491 p_user_id,
1492 l_all_valid_constraints.constraint_rev_id,
1493 p_user_id,
1494 l_all_valid_constraints.constraint_rev_id,
1495 l_all_valid_constraints.constraint_rev_id,
1496 p_responsibility_id;
1497 FETCH group_acess_count_c INTO l_group_access_count;
1498 CLOSE group_acess_count_c;
1499
1500 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
1501 -- he violates this constraint
1502 IF l_group_access_count >= 2 THEN
1503 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1504 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1505 has_violation := 'Y';
1506 return has_violation;
1507 END IF;
1508 ELSE
1509 -- other constraint types
1510 NULL;
1511 END IF; -- end of if: constraint type_code
1512
1513 END IF; -- end of if: l_valid_user_waiver_count <= 0
1514
1515 END LOOP; --end of loop: c_all_valid_constraints
1516 CLOSE c_all_valid_constraints;
1517
1518 END IF; -- end of if: p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL
1519
1520 return has_violation;
1521
1522 END Has_Violation_Due_To_Resp;
1523
1524
1525
1526 -- ===============================================================
1527 -- Private Procedure name
1528 -- Clear_List
1529 --
1530 -- Purpose
1531 -- to clear the global list:
1532 -- G_FUNCTION_ID_LIST
1533 -- G_MENU_ID_LIST
1534 -- G_RESPONSIBILITY_ID_LIST
1535 -- G_ROLE_NAME_LIST
1536 -- G_ENTRY_OBJECT_TYPE_LIST
1537 -- G_GROUP_CODE_LIST
1538 --
1539 -- ===============================================================
1540 PROCEDURE Clear_List
1541 IS
1542
1543 L_API_NAME CONSTANT VARCHAR2(30) := 'Clear_List';
1544 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1545
1546 BEGIN
1547 --FND_FILE.put_line(fnd_file.log,'inside api '||L_API_NAME);
1548 G_ROLE_NAME_LIST.DELETE();
1549 G_RESPONSIBILITY_ID_LIST.DELETE();
1550 G_MENU_ID_LIST.DELETE();
1551 G_FUNCTION_ID_LIST.DELETE();
1552 G_ENTRY_OBJECT_TYPE_LIST.DELETE();
1553 G_GROUP_CODE_LIST.DELETE();
1554 G_PV_COUNT := 1;
1555
1556 G_ROLE_NAME_LIST_HIER.DELETE();
1557 G_RESPONSIBILITY_ID_LIST_HIER.DELETE();
1558 G_MENU_ID_LIST_HIER.DELETE();
1559 G_FUNCTION_ID_LIST_HIER.DELETE();
1560 G_ENTRY_OBJECT_TYPE_LIST_HIER.DELETE();
1561 G_GROUP_CODE_LIST_HIER.DELETE();
1562 G_PV_COUNT_HIER := 1;
1563
1564
1565 END Clear_List;
1566
1567
1568 -- ===============================================================
1569 -- Private Function name
1570 -- PROCESS_MENU_TREE_DOWN_FOR_MN
1571 --
1572 -- Purpose
1573 -- Plow through the menu tree, processing exclusions and figuring
1574 -- out which functions are accessible.
1575 --
1576 -- This routine processes the menu hierarchy and exclusion rules in PL/SQL
1577 -- rather than in the database.
1578 -- The basic algorithm of this routine is:
1579 -- Populate the list of exclusions by selecting from FND_RESP_FUNCTIONS
1580 -- menulist(1) = p_menu_id
1581 -- while (elements on menulist)
1582 -- {
1583 -- Remove first element off menulist
1584 -- if this menu is not excluded with a menu exclusion rule
1585 -- {
1586 -- Query all menu entry children of current menu
1587 -- for (each child) loop
1588 -- {
1589 -- If it's excluded by a func exclusion rule, go on to the next one.
1590 -- If we've got the function we're looking for,
1591 -- and grant_flag = Y, we're done- return TRUE;
1592 -- If it's got a sub_menu_id, add it to the end of menulist
1593 -- to be processed
1594 -- }
1595 -- Move to next element on menulist
1596 -- }
1597 -- }
1598 --
1599 -- Params
1600 -- p_menu_id := menu_id
1601 -- p_function_id := function to check for
1602 --
1603 -- Don't pass values for the following two params if you don't want
1604 -- exclusions processed.
1605 -- p_appl_id := application id of resp
1606 -- p_resp_id := responsibility id
1607 --
1608 -- p_access_given_date := start_date of user resp (added for AMW)
1609 -- p_access_given_by := created_by of user resp (added for AMW)
1610 --
1611 -- Return
1612 -- True := function accessible
1613 -- False := function not accessible
1614 --
1615 -- Notes
1616 -- copy from FND_FUNCTION.PROCESS_MENU_TREE_DOWN_FOR_MN (AFSCFNSB.pls 115.51 2003/08/01)
1617 -- and modify for AMW to use dynamic sql
1618 --
1619 -- 12.21.2004 tsho: set default NULL for p_access_given_date, p_access_given_by
1620 -- 12.21.2004 tsho: fix for performance bug 4036679
1621 -- History
1622 -- 05.24.2005 tsho: AMW.E Incompatible Sets,
1623 -- need to pass in amw_constraint_entries.group_code for each item
1624 -- ===============================================================
1625 FUNCTION PROCESS_MENU_TREE_DOWN_FOR_MN(
1626 p_menu_id in number,
1627 p_function_id in number,
1628 p_appl_id in number,
1629 p_resp_id in number,
1630 p_role_name in varchar2 := NULL,
1631 p_entry_object_type_list in varchar2 := NULL,
1632 p_group_code in varchar2 := NULL
1633 ) RETURN boolean
1634 IS
1635
1636 L_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_MENU_TREE_DOWN_FOR_MN';
1637 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1638
1639 l_sub_menu_id number;
1640
1641 /* Table to store the list of submenus that we are looking for */
1642 TYPE MENULIST_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
1643 MENULIST MENULIST_TYPE;
1644
1645 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
1646 TYPE VARCHAR2_TABLE_TYPE is table of VARCHAR2(1) INDEX BY BINARY_INTEGER;
1647
1648 /* The table of exclusions. The index in is the action_id, and the */
1649 /* value stored in each element is the rule_type.*/
1650 EXCLUSIONS VARCHAR2_TABLE_TYPE;
1651
1652 /* Returns from the bulk collect (fetches) */
1653 TBL_MENU_ID NUMBER_TABLE_TYPE;
1654 TBL_ENT_SEQ NUMBER_TABLE_TYPE;
1655 TBL_FUNC_ID NUMBER_TABLE_TYPE;
1656 TBL_SUBMNU_ID NUMBER_TABLE_TYPE;
1657 TBL_GNT_FLG VARCHAR2_TABLE_TYPE;
1658
1659 /* Cursor to get exclusions */
1660 TYPE exclCurTyp IS REF CURSOR;
1661 excl_c exclCurTyp;
1662 l_excl_rule_type VARCHAR2(30);
1663 l_excl_action_id NUMBER;
1664 l_excl_dynamic_sql VARCHAR2(200) :=
1665 'SELECT RULE_TYPE, ACTION_ID '
1666 ||' FROM '||G_AMW_RESP_FUNCTIONS
1667 ||' WHERE application_id = :1 '
1668 ||' AND responsibility_id = :2 ';
1669
1670 /* Cursor to get menu entries on a particular menu.*/
1671 TYPE mnesCurTyp IS REF CURSOR;
1672 get_mnes_c mnesCurTyp;
1673 l_mnes_menu_id NUMBER;
1674 l_mnes_entry_sequence NUMBER;
1675 l_mnes_function_id NUMBER;
1676 l_mnes_sub_menu_id NUMBER;
1677 l_mnes_grant_flag VARCHAR2(1);
1678 l_mnes_dynamic_sql VARCHAR2(200) :=
1679 'SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG '
1680 ||' FROM '||G_AMW_MENU_ENTRIES
1681 ||' WHERE menu_id = :1 ';
1682
1683 menulist_cur pls_integer;
1684 menulist_size pls_integer;
1685
1686 entry_excluded boolean;
1687 last_index pls_integer;
1688 i number;
1689 z number;
1690
1691 BEGIN
1692 --FND_FILE.put_line(fnd_file.log,'inside api '||L_API_NAME);
1693
1694 if(p_appl_id is not NULL) then
1695 /* Select the list of exclusion rules into our cache */
1696 OPEN excl_c FOR l_excl_dynamic_sql USING
1697 p_appl_id,
1698 p_resp_id;
1699 LOOP
1700 FETCH excl_c INTO l_excl_rule_type, l_excl_action_id;
1701 EXIT WHEN excl_c%NOTFOUND;
1702 EXCLUSIONS(l_excl_action_id) := l_excl_rule_type;
1703 END LOOP;
1704 CLOSE excl_c;
1705
1706 end if;
1707
1708 -- Initialize menulist working list to parent menu
1709 menulist_cur := 0;
1710 menulist_size := 1;
1711 menulist(0) := p_menu_id;
1712
1713 -- Continue processing until reach the end of list
1714 while (menulist_cur < menulist_size) loop
1715 -- Check if recursion limit exceeded
1716 if (menulist_cur > C_MAX_MENU_ENTRIES) then
1717 /* If the function were accessible from this menu, then we should */
1718 /* have found it before getting to this point, so we are confident */
1719 /* that the function is not on this menu. */
1720 return FALSE;
1721 end if;
1722
1723 l_sub_menu_id := menulist(menulist_cur);
1724
1725 -- See whether the current menu is excluded or not.
1726 entry_excluded := FALSE;
1727 begin
1728 if( (l_sub_menu_id is not NULL)
1729 and (exclusions(l_sub_menu_id) = 'M')) then
1730 entry_excluded := TRUE;
1731 end if;
1732 exception
1733 when no_data_found then
1734 null;
1735 end;
1736
1737 if (entry_excluded) then
1738 last_index := 0; /* Indicate that no rows were returned */
1739 else
1740 /* This menu isn't excluded, so find out whats entries are on it. */
1741 if (G_BULK_COLLECTS_SUPPORTED='TRUE') then
1742 open get_mnes_c for l_mnes_dynamic_sql USING
1743 l_sub_menu_id;
1744
1745 fetch get_mnes_c bulk collect into tbl_menu_id, tbl_ent_seq,
1746 tbl_func_id, tbl_submnu_id, tbl_gnt_flg;
1747 close get_mnes_c;
1748
1749 -- See if we found any rows. If not set last_index to zero.
1750 begin
1751 if((tbl_menu_id.FIRST is NULL) or (tbl_menu_id.FIRST <> 1)) then
1752 last_index := 0;
1753 else
1754 if (tbl_menu_id.FIRST is not NULL) then
1755 last_index := tbl_menu_id.LAST;
1756 else
1757 last_index := 0;
1758 end if;
1759 end if;
1760 exception
1761 when others then
1762 last_index := 0;
1763 end;
1764 else
1765 z:= 0;
1766 OPEN get_mnes_c FOR l_mnes_dynamic_sql USING
1767 l_sub_menu_id;
1768 LOOP
1769 FETCH get_mnes_c INTO l_mnes_menu_id,
1770 l_mnes_entry_sequence,
1771 l_mnes_function_id,
1772 l_mnes_sub_menu_id,
1773 l_mnes_grant_flag;
1774 EXIT WHEN get_mnes_c%NOTFOUND;
1775 tbl_menu_id(z) := l_mnes_menu_id;
1776 tbl_ent_seq(z) := l_mnes_entry_sequence;
1777 tbl_func_id(z) := l_mnes_function_id;
1778 tbl_submnu_id (z):= l_mnes_sub_menu_id;
1779 tbl_gnt_flg(z) := l_mnes_grant_flag;
1780 END LOOP;
1781 CLOSE get_mnes_c;
1782
1783 last_index := z;
1784 end if;
1785
1786 end if; /* entry_excluded */
1787
1788 -- Process each of the child entries fetched
1789 for i in 1 .. last_index loop
1790 -- Check if there is an exclusion rule for this entry
1791 entry_excluded := FALSE;
1792 begin
1793 if( (tbl_func_id(i) is not NULL)
1794 and (exclusions(tbl_func_id(i)) = 'F')) then
1795 entry_excluded := TRUE;
1796 end if;
1797 exception
1798 when no_data_found then
1799 null;
1800 end;
1801
1802 -- Skip this entry if it's excluded
1803 if (not entry_excluded) then
1804 -- Check if this is a matching function. If so, return success.
1805 if( (tbl_func_id(i) = p_function_id)
1806 and (tbl_gnt_flg(i) = 'Y'))
1807 then
1808 G_ROLE_NAME_LIST(G_PV_COUNT) := p_role_name;
1809 G_MENU_ID_LIST(G_PV_COUNT) := tbl_menu_id(i);
1810 G_FUNCTION_ID_LIST(G_PV_COUNT) := p_function_id;
1811 G_RESPONSIBILITY_ID_LIST(G_PV_COUNT) := p_resp_id;
1812 G_ENTRY_OBJECT_TYPE_LIST(G_PV_COUNT) := p_entry_object_type_list;
1813 G_GROUP_CODE_LIST(G_PV_COUNT) := p_group_code; -- 05.24.2005 tsho: AMW.E Incompatible Sets
1814 G_PV_COUNT := G_PV_COUNT +1;
1815 return TRUE;
1816 end if;
1817
1818 -- If this is a submenu, then add it to the end of the
1819 -- working list for processing.
1820 if (tbl_submnu_id(i) is not NULL) then
1821 menulist(menulist_size) := tbl_submnu_id(i);
1822 menulist_size := menulist_size + 1;
1823 end if;
1824 end if; -- End if not excluded
1825 end loop; -- For loop processing child entries
1826
1827 -- Advance to next menu on working list
1828 menulist_cur := menulist_cur + 1;
1829 end loop;
1830
1831 -- We couldn't find the function anywhere, so it's not available
1832 return FALSE;
1833
1834 END PROCESS_MENU_TREE_DOWN_FOR_MN;
1835
1836
1837
1838
1839 -- ===============================================================
1840 -- Private Procedure name
1841 -- BUILD_ROLE_AND_RESP_HIER
1842 --
1843 -- Purpose
1844 -- Plow through the role/resp hierarchy
1845 -- History
1846 -- 05.23.2006 dliao created
1847 -- ===============================================================
1848 PROCEDURE BUILD_ROLE_AND_RESP_HIER(
1849 P_ROLE_NAME_LIST_HIER in G_VARCHAR2_LONG_TABLE,
1850 P_RESPONSIBILITY_ID_LIST_HIER in G_NUMBER_TABLE,
1851 P_MENU_ID_LIST_HIER in G_NUMBER_TABLE,
1852 P_FUNCTION_ID_LIST_HIER in G_NUMBER_TABLE,
1853 P_ENTRY_OBJECT_TYPE_LIST_HIER in G_VARCHAR2_CODE_TABLE,
1854 P_GROUP_CODE_LIST_HIER in G_VARCHAR2_CODE_TABLE
1855 )
1856 IS
1857
1858 L_API_NAME CONSTANT VARCHAR2(30) := 'BUILD_ROLE_AND_RESP_HIER';
1859 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1860
1861 p_superiors WF_ROLE_HIERARCHY.RELTAB;
1862 p_subordinates WF_ROLE_HIERARCHY.RELTAB;
1863
1864 BEGIN
1865
1866 G_ROLE_NAME_LIST_HIER := P_ROLE_NAME_LIST_HIER;
1867 G_RESPONSIBILITY_ID_LIST_HIER := P_RESPONSIBILITY_ID_LIST_HIER;
1868 G_MENU_ID_LIST_HIER := P_MENU_ID_LIST_HIER;
1869 G_FUNCTION_ID_LIST_HIER := P_FUNCTION_ID_LIST_HIER;
1870 G_ENTRY_OBJECT_TYPE_LIST_HIER := P_ENTRY_OBJECT_TYPE_LIST_HIER;
1871 G_GROUP_CODE_LIST_HIER := P_GROUP_CODE_LIST_HIER;
1872
1873 G_PV_COUNT_HIER := P_FUNCTION_ID_LIST_HIER.COUNT;
1874
1875
1876 FOR i IN 1 .. P_FUNCTION_ID_LIST_HIER.COUNT LOOP
1877 wf_role_hierarchy.getrelationships(P_ROLE_NAME_LIST_HIER(i),p_superiors,p_subordinates,'SUBORDINATES');
1878
1879 IF p_subordinates.count > 0 THEN
1880 FOR k IN p_subordinates.first..p_subordinates.last LOOP
1881 IF p_subordinates.exists(k) THEN
1882
1883 G_PV_COUNT_HIER := G_PV_COUNT_HIER + 1;
1884
1885 G_ROLE_NAME_LIST_HIER(G_PV_COUNT_HIER) := p_subordinates(k).sub_name ;
1886 G_RESPONSIBILITY_ID_LIST_HIER(G_PV_COUNT_HIER) := P_RESPONSIBILITY_ID_LIST_HIER(i);
1887 G_MENU_ID_LIST_HIER(G_PV_COUNT_HIER) := P_MENU_ID_LIST_HIER(i);
1888 G_FUNCTION_ID_LIST_HIER(G_PV_COUNT_HIER) := P_FUNCTION_ID_LIST_HIER(i);
1889 G_ENTRY_OBJECT_TYPE_LIST_HIER(G_PV_COUNT_HIER) := P_ENTRY_OBJECT_TYPE_LIST_HIER(i);
1890 G_GROUP_CODE_LIST_HIER(G_PV_COUNT_HIER) := P_GROUP_CODE_LIST_HIER(i);
1891
1892 END IF;
1893 END LOOP;-- end of p_subordinates
1894
1895 END IF;
1896
1897
1898 END LOOP; -- end of P_FUNCTION_ID_LIST_HIER
1899
1900 EXCEPTION
1901 WHEN others then
1902 RAISE;
1903 END BUILD_ROLE_AND_RESP_HIER;
1904
1905
1906
1907 -- ===============================================================
1908 -- Procedure name
1909 -- Update_Role_Constraint_Denorm
1910 --
1911 -- Purpose
1912 -- populate AMW_ROLE_CONSTRAINT_DENORM table
1913 -- Params
1914 -- p_constraint_rev_id := input constraint_rev_id (Default is NULL)
1915 -- if p_constraint_rev_id is specified, only update/create
1916 -- the corresponding role/resp with that constraint.
1917 --
1918 -- History
1919 -- 07/14/2005 tsho Create
1920 -- 08/03/2005 tsho Consider Responsibility Waivers, leave User Waiver check to the run-time
1921 -- 09/16/2005 tsho Consider Concurrent Program and Exclusion (function/menu exclusion from Responsibility)
1922 -- ===============================================================
1923 Procedure Update_Role_Constraint_Denorm (
1924 errbuf OUT NOCOPY VARCHAR2,
1925 retcode OUT NOCOPY VARCHAR2,
1926 p_constraint_rev_id IN NUMBER := NULL
1927 )
1928 IS
1929 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Role_Constraint_Denorm';
1930 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1931
1932 TYPE respCurTyp IS REF CURSOR;
1933 role_c respCurTyp;
1934 resp_c respCurTyp;
1935
1936 p_superiors WF_ROLE_HIERARCHY.RELTAB;
1937 p_subordinates WF_ROLE_HIERARCHY.RELTAB;
1938
1939 l_g_role_name VARCHAR2(320);
1940 l_g_responsibility_id NUMBER;
1941 l_g_menu_id NUMBER;
1942 l_g_function_id NUMBER;
1943 l_g_entry_object_type VARCHAR2(30);
1944 l_g_group_code VARCHAR2(30);
1945
1946
1947 -- 09.16.2005 tsho: Consider Concurrent Program and Exclusion (function/menu exclusion from Responsibility)
1948 -- seperate the responsibility query from role
1949 -- 05.23.2006 dliao: remove the grant_flag
1950 l_role_dynamic_sql VARCHAR2(4000) :=
1951 '(SELECT gra.GRANTEE_KEY ROLE_NAME, gra.GRANTEE_ORIG_SYSTEM_ID as responsibility_id, gra.menu_id, ce.function_id, ce.object_type, ce.group_code '
1952 ||' FROM AMW_CONSTRAINT_ENTRIES ce '
1953 ||' ,'||G_AMW_GRANTS ||' gra '
1954 ||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
1955 ||' WHERE gra.menu_id = cmf.menu_id '
1956 ||' AND cmf.function_id = ce.function_id '
1957 --and cmf.grant_flag = ''Y'' '
1958 ||' AND ce.CONSTRAINT_REV_ID = :1 '
1959 ||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
1960 ||' AND gra.OBJECT_ID = -1 '
1961 ||' AND gra.GRANTEE_TYPE = ''GROUP'' '
1962 ||' AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
1963 ||' UNION '
1964 ||' SELECT gra.GRANTEE_KEY ROLE_NAME, gra.GRANTEE_ORIG_SYSTEM_ID as responsibility_id, gra.menu_id, ce.function_id, ce.object_type, ce.group_code '
1965 ||' FROM AMW_CONSTRAINT_ENTRIES ce '
1966 ||' ,'||G_AMW_GRANTS ||' gra '
1967 ||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
1968 ||' WHERE gra.menu_id = cmf.menu_id '
1969 ||' AND cmf.function_id = ce.function_id '
1970 --and cmf.grant_flag = ''Y'' '
1971 ||' AND ce.CONSTRAINT_REV_ID = :2 '
1972 ||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
1973 ||' AND gra.OBJECT_ID = -1 '
1974 ||' AND gra.GRANTEE_TYPE = ''GLOBAL'' '
1975 ||' AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
1976 ||') UNION ALL '
1977 ||' SELECT to_char(null) role_name, resp.responsibility_id, resp.request_group_id menu_id, ce.function_id, ce.object_type, ce.group_code '
1978 ||' FROM '||G_AMW_RESPONSIBILITY ||' resp '
1979 ||' ,'||G_AMW_REQUEST_GROUP_UNITS ||' rgu '
1980 ||' ,AMW_CONSTRAINT_ENTRIES ce '
1981 ||' WHERE resp.request_group_id = rgu.request_group_id '
1982 ||' AND rgu.request_unit_type = ''P'' '
1983 ||' AND rgu.request_unit_id = ce.function_id AND ce.object_type = ''CP'' '
1984 ||' AND ce.CONSTRAINT_REV_ID = :3 '
1985 ||' AND resp.responsibility_id NOT IN (select cw.pk1 from amw_constraint_waivers_vl cw '
1986 ||' where cw.constraint_rev_id=ce.CONSTRAINT_REV_ID '
1987 ||' and cw.object_type=''RESP'' '
1988 ||' and cw.start_date<=sysdate '
1989 ||' and (cw.end_date >= sysdate or cw.end_date is null)) '
1990 ||' AND resp.start_date <= sysdate AND (resp.end_date >= sysdate or resp.end_date is null) '
1991 ;
1992
1993 -- 09.16.2005 tsho: Consider Exclusion (function/menu exclusion from Responsibility)
1994 -- seperate the responsibility query from role
1995 l_applcation_id_list G_NUMBER_TABLE;
1996 l_responsibility_id_list G_NUMBER_TABLE;
1997 l_menu_id_list G_NUMBER_TABLE;
1998 l_function_id_list G_NUMBER_TABLE;
1999 l_role_name_id_list G_VARCHAR2_LONG_TABLE;
2000 l_entry_object_type_list G_VARCHAR2_CODE_TABLE;
2001 l_group_code_list G_VARCHAR2_CODE_TABLE;
2002 --17.12.06 psomanat : The FND creates a role for each responsibity.So the role name is added here.
2003 l_resp_dynamic_sql VARCHAR2(2000) :=
2004 ' SELECT war.name, resp.application_id, resp.responsibility_id, resp.menu_id, ce.function_id, ce.object_type, ce.group_code '
2005 ||' FROM AMW_CONSTRAINT_ENTRIES ce '
2006 ||' ,'||G_AMW_RESPONSIBILITY_VL ||' resp '
2007 ||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
2008 ||' ,'||G_AMW_ALL_ROLES_VL ||' war '
2009 ||' WHERE resp.menu_id = cmf.menu_id '
2010 ||' AND cmf.function_id = ce.function_id and cmf.grant_flag = ''Y'' '
2011 ||' AND (ce.OBJECT_TYPE is null OR ce.OBJECT_TYPE = ''FUNC'') '
2012 ||' AND ce.CONSTRAINT_REV_ID = :1 '
2013 ||' AND resp.responsibility_id NOT IN (select cw.pk1 from amw_constraint_waivers_vl cw '
2014 ||' where cw.constraint_rev_id=ce.CONSTRAINT_REV_ID '
2015 ||' and cw.object_type=''RESP'' '
2016 ||' and cw.start_date<=sysdate '
2017 ||' and (cw.end_date >= sysdate or cw.end_date is null)) '
2018 ||' AND resp.start_date <= sysdate AND (resp.end_date >= sysdate or resp.end_date is null) '
2019 ||' AND resp.responsibility_name = war.display_name '
2020 ||' AND war.ORIG_SYSTEM = ''FND_RESP'' '
2021 ||' AND STATUS = ''ACTIVE'' ';
2022
2023 -- find all valid constraints
2024 CURSOR c_all_valid_constraints IS
2025 SELECT constraint_rev_id
2026 FROM amw_constraints_b
2027 WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);
2028 l_all_valid_constraints c_all_valid_constraints%ROWTYPE;
2029
2030 --09.20.2005 tsho Consider Exclusion
2031 -- store the access right
2032 l_accessible BOOLEAN;
2033 TYPE exclFuncCurTyp IS REF CURSOR;
2034 excl_func_c exclFuncCurTyp;
2035 l_excl_func_count NUMBER;
2036
2037 l_excl_func_dynamic_sql VARCHAR2(200) :=
2038 'SELECT count(*) '
2039 ||' FROM '||G_AMW_RESP_FUNCTIONS
2040 ||' WHERE application_id = :1 '
2041 ||' AND responsibility_id = :2 '
2042 ||' AND rule_type = :3 '
2043 ||' AND action_id = :4 ';
2044
2045 BEGIN
2046 IF p_constraint_rev_id IS NULL THEN
2047 -- delete all the records from amw_role_constraint_denorm
2048 DELETE FROM AMW_ROLE_CONSTRAINT_DENORM;
2049
2050 -- check all constraints
2051 OPEN c_all_valid_constraints;
2052 LOOP
2053 FETCH c_all_valid_constraints INTO l_all_valid_constraints;
2054 EXIT WHEN c_all_valid_constraints%NOTFOUND;
2055
2056 -- clear global list for each constraint
2057 Clear_List();
2058
2059 OPEN role_c FOR l_role_dynamic_sql USING
2060 l_all_valid_constraints.constraint_rev_id, l_all_valid_constraints.constraint_rev_id, l_all_valid_constraints.constraint_rev_id;
2061 FETCH role_c BULK COLLECT INTO
2062 G_ROLE_NAME_LIST
2063 ,G_RESPONSIBILITY_ID_LIST
2064 ,G_MENU_ID_LIST
2065 ,G_FUNCTION_ID_LIST
2066 ,G_ENTRY_OBJECT_TYPE_LIST
2067 ,G_GROUP_CODE_LIST;
2068 CLOSE role_c;
2069
2070
2071 BUILD_ROLE_AND_RESP_HIER(
2072 P_ROLE_NAME_LIST_HIER => G_ROLE_NAME_LIST,
2073 P_RESPONSIBILITY_ID_LIST_HIER => G_RESPONSIBILITY_ID_LIST,
2074 P_MENU_ID_LIST_HIER => G_MENU_ID_LIST,
2075 P_FUNCTION_ID_LIST_HIER => G_FUNCTION_ID_LIST,
2076 P_ENTRY_OBJECT_TYPE_LIST_HIER => G_ENTRY_OBJECT_TYPE_LIST,
2077 P_GROUP_CODE_LIST_HIER => G_GROUP_CODE_LIST);
2078
2079 IF(G_FUNCTION_ID_LIST_HIER.COUNT > 0) THEN
2080 FORALL i IN 1 .. G_FUNCTION_ID_LIST_HIER.COUNT
2081 INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
2082 VALUES(sysdate -- last_update_date
2083 ,G_USER_ID -- last_updated_by
2084 ,G_LOGIN_ID -- last_update_login
2085 ,sysdate -- creation_date
2086 ,G_USER_ID -- created_by
2087 ,G_FUNCTION_ID_LIST_HIER(i) -- function_id
2088 ,G_MENU_ID_LIST_HIER(i) -- menu_id
2089 ,l_all_valid_constraints.constraint_rev_id -- constraint_rev_id
2090 ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i) -- object_type
2091 ,G_GROUP_CODE_LIST_HIER(i) -- group_code
2092 ,G_ROLE_NAME_LIST_HIER(i) -- role_name
2093 ,G_RESPONSIBILITY_ID_LIST_HIER(i)); -- responsibility_id
2094 END IF;
2095
2096 -- 09.20.2005 tsho: consider Exclusion
2097 -- clear global list for each constraint
2098 Clear_List();
2099 l_applcation_id_list.delete();
2100 l_responsibility_id_list.delete();
2101 l_role_name_id_list.delete();
2102 l_menu_id_list.delete();
2103 l_function_id_list.delete();
2104 l_entry_object_type_list.delete();
2105 l_group_code_list.delete();
2106
2107 OPEN resp_c FOR l_resp_dynamic_sql USING
2108 l_all_valid_constraints.constraint_rev_id;
2109 FETCH resp_c BULK COLLECT INTO
2110 l_role_name_id_list
2111 ,l_applcation_id_list
2112 ,l_responsibility_id_list
2113 ,l_menu_id_list
2114 ,l_function_id_list
2115 ,l_entry_object_type_list
2116 ,l_group_code_list;
2117 CLOSE resp_c;
2118
2119 FOR j IN 1 .. l_function_id_list.COUNT
2120 LOOP
2121 -- check function exclusion
2122 OPEN excl_func_c FOR l_excl_func_dynamic_sql USING
2123 l_applcation_id_list(j), l_responsibility_id_list(j), 'F', l_function_id_list(j);
2124 FETCH excl_func_c INTO l_excl_func_count;
2125 CLOSE excl_func_c;
2126
2127 IF (l_excl_func_count > 0) THEN
2128 -- the function i is excluded from the reponsibility j, check next responsibility
2129 l_accessible := FALSE;
2130 ELSE
2131 -- need to check if any menu excluded from the responsibility j
2132 l_accessible := PROCESS_MENU_TREE_DOWN_FOR_MN(
2133 p_menu_id => l_menu_id_list(j),
2134 p_function_id => l_function_id_list(j),
2135 p_appl_id => l_applcation_id_list(j),
2136 p_resp_id => l_responsibility_id_list(j),
2137 p_role_name => l_role_name_id_list(j),
2138 p_entry_object_type_list => l_entry_object_type_list(j),
2139 p_group_code => l_group_code_list(j));
2140
2141 BUILD_ROLE_AND_RESP_HIER(
2142 P_ROLE_NAME_LIST_HIER => G_ROLE_NAME_LIST,
2143 P_RESPONSIBILITY_ID_LIST_HIER => G_RESPONSIBILITY_ID_LIST,
2144 P_MENU_ID_LIST_HIER => G_MENU_ID_LIST,
2145 P_FUNCTION_ID_LIST_HIER => G_FUNCTION_ID_LIST,
2146 P_ENTRY_OBJECT_TYPE_LIST_HIER => G_ENTRY_OBJECT_TYPE_LIST,
2147 P_GROUP_CODE_LIST_HIER => G_GROUP_CODE_LIST);
2148
2149
2150 END IF; -- end of if: l_excl_func_id IS NOT NULL
2151
2152 END LOOP; --end of for: l_function_id_list
2153
2154 -- populate non-exclusion function to defnorm table
2155 IF(G_FUNCTION_ID_LIST_HIER.COUNT > 0) THEN
2156 FORALL i IN 1 .. G_FUNCTION_ID_LIST_HIER.COUNT
2157
2158 INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
2159 VALUES(sysdate -- last_update_date
2160 ,G_USER_ID -- last_updated_by
2161 ,G_LOGIN_ID -- last_update_login
2162 ,sysdate -- creation_date
2163 ,G_USER_ID -- created_by
2164 ,G_FUNCTION_ID_LIST_HIER(i) -- function_id
2165 ,G_MENU_ID_LIST_HIER(i) -- menu_id
2166 ,l_all_valid_constraints.constraint_rev_id -- constraint_rev_id
2167 ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i) -- object_type
2168 ,G_GROUP_CODE_LIST_HIER(i) -- group_code
2169 ,G_ROLE_NAME_LIST_HIER(i) -- role_name
2170 ,G_RESPONSIBILITY_ID_LIST_HIER(i)); -- responsibility_id
2171
2172 END IF; --end of g_function_id_list_hier.count > 0
2173
2174 END LOOP;
2175
2176 CLOSE c_all_valid_constraints;
2177 ELSE
2178 -- check specified constraint
2179 -- delete the records from amw_role_constraint_denorm for the specified constraint
2180 DELETE FROM AMW_ROLE_CONSTRAINT_DENORM
2181 WHERE constraint_rev_id = p_constraint_rev_id;
2182
2183 -- clear global list for each constraint
2184 Clear_List();
2185
2186
2187 OPEN role_c FOR l_role_dynamic_sql USING
2188 p_constraint_rev_id, p_constraint_rev_id, p_constraint_rev_id;
2189 FETCH role_c BULK COLLECT INTO
2190 G_ROLE_NAME_LIST
2191 ,G_RESPONSIBILITY_ID_LIST
2192 ,G_MENU_ID_LIST
2193 ,G_FUNCTION_ID_LIST
2194 ,G_ENTRY_OBJECT_TYPE_LIST
2195 ,G_GROUP_CODE_LIST;
2196 CLOSE role_c;
2197
2198 BUILD_ROLE_AND_RESP_HIER(
2199 P_ROLE_NAME_LIST_HIER => G_ROLE_NAME_LIST,
2200 P_RESPONSIBILITY_ID_LIST_HIER => G_RESPONSIBILITY_ID_LIST,
2201 P_MENU_ID_LIST_HIER => G_MENU_ID_LIST,
2202 P_FUNCTION_ID_LIST_HIER => G_FUNCTION_ID_LIST,
2203 P_ENTRY_OBJECT_TYPE_LIST_HIER => G_ENTRY_OBJECT_TYPE_LIST,
2204 P_GROUP_CODE_LIST_HIER => G_GROUP_CODE_LIST);
2205
2206 IF(G_FUNCTION_ID_LIST_HIER.COUNT > 0) THEN
2207 FORALL i IN 1 .. G_FUNCTION_ID_LIST_HIER.COUNT
2208 INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
2209 VALUES(sysdate -- last_update_date
2210 ,G_USER_ID -- last_updated_by
2211 ,G_LOGIN_ID -- last_update_login
2212 ,sysdate -- creation_date
2213 ,G_USER_ID -- created_by
2214 ,G_FUNCTION_ID_LIST_HIER(i) -- function_id
2215 ,G_MENU_ID_LIST_HIER(i) -- menu_id
2216 ,p_constraint_rev_id -- constraint_rev_id
2217 ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i) -- object_type
2218 ,G_GROUP_CODE_LIST_HIER(i) -- group_code
2219 ,G_ROLE_NAME_LIST_HIER(i) -- role_name
2220 ,G_RESPONSIBILITY_ID_LIST_HIER(i)); -- responsibility_id
2221 END IF;
2222 -- 09.20.2005 tsho: consider Exclusion
2223 -- clear global list for each constraint
2224 Clear_List();
2225 l_applcation_id_list.delete();
2226 l_responsibility_id_list.delete();
2227 l_role_name_id_list.delete();
2228 l_menu_id_list.delete();
2229 l_function_id_list.delete();
2230 l_entry_object_type_list.delete();
2231 l_group_code_list.delete();
2232
2233
2234
2235 OPEN resp_c FOR l_resp_dynamic_sql USING
2236 p_constraint_rev_id;
2237 FETCH resp_c BULK COLLECT INTO
2238 l_role_name_id_list
2239 ,l_applcation_id_list
2240 ,l_responsibility_id_list
2241 ,l_menu_id_list
2242 ,l_function_id_list
2243 ,l_entry_object_type_list
2244 ,l_group_code_list;
2245 CLOSE resp_c;
2246
2247 FOR j IN 1 .. l_function_id_list.COUNT
2248 LOOP
2249 -- check function exclusion
2250 OPEN excl_func_c FOR l_excl_func_dynamic_sql USING
2251 l_applcation_id_list(j), l_responsibility_id_list(j), 'F', l_function_id_list(j);
2252 FETCH excl_func_c INTO l_excl_func_count;
2253 CLOSE excl_func_c;
2254 IF (l_excl_func_count > 0) THEN
2255 -- the function i is excluded from the reponsibility j, check next responsibility
2256 l_accessible := FALSE;
2257 ELSE
2258 -- need to check if any menu excluded from the responsibility j
2259 l_accessible := PROCESS_MENU_TREE_DOWN_FOR_MN(
2260 p_menu_id => l_menu_id_list(j),
2261 p_function_id => l_function_id_list(j),
2262 p_appl_id => l_applcation_id_list(j),
2263 p_resp_id => l_responsibility_id_list(j),
2264 p_role_name => l_role_name_id_list(j),
2265 p_entry_object_type_list => l_entry_object_type_list(j),
2266 p_group_code => l_group_code_list(j));
2267
2268 BUILD_ROLE_AND_RESP_HIER(
2269 P_ROLE_NAME_LIST_HIER => G_ROLE_NAME_LIST,
2270 P_RESPONSIBILITY_ID_LIST_HIER => G_RESPONSIBILITY_ID_LIST,
2271 P_MENU_ID_LIST_HIER => G_MENU_ID_LIST,
2272 P_FUNCTION_ID_LIST_HIER => G_FUNCTION_ID_LIST,
2273 P_ENTRY_OBJECT_TYPE_LIST_HIER => G_ENTRY_OBJECT_TYPE_LIST,
2274 P_GROUP_CODE_LIST_HIER => G_GROUP_CODE_LIST);
2275
2276
2277 END IF; -- end of if: l_excl_func_id IS NOT NULL
2278 END LOOP; --end of for: l_function_id_list
2279
2280
2281 -- populate non-exclusion function to defnorm table
2282 IF(G_FUNCTION_ID_LIST_HIER.COUNT > 0) THEN
2283 FORALL i IN 1 .. G_FUNCTION_ID_LIST_HIER.COUNT
2284
2285 INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
2286 VALUES(sysdate -- last_update_date
2287 ,G_USER_ID -- last_updated_by
2288 ,G_LOGIN_ID -- last_update_login
2289 ,sysdate -- creation_date
2290 ,G_USER_ID -- created_by
2291 ,G_FUNCTION_ID_LIST_HIER(i) -- function_id
2292 ,G_MENU_ID_LIST_HIER(i) -- menu_id
2293 ,p_constraint_rev_id -- constraint_rev_id
2294 ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i) -- object_type
2295 ,G_GROUP_CODE_LIST_HIER(i) -- group_code
2296 ,G_ROLE_NAME_LIST_HIER(i) -- role_name
2297 ,G_RESPONSIBILITY_ID_LIST_HIER(i)); -- responsibility_id
2298 END IF; --end of if g_function_id_list_hier.count > 0
2299
2300 END IF; --end of if: p_constraint_rev_id = NULL
2301
2302 COMMIT;
2303
2304 EXCEPTION
2305 WHEN others then
2306 RAISE;
2307
2308 END Update_Role_Constraint_Denorm;
2309
2310 -- ===============================================================
2311 -- Function name
2312 -- Get_Violat_New_Role_List
2313 --
2314 -- Purpose
2315 -- get a flat string list of new role display name, which together with this user's
2316 -- exisiting role/resp , or together with those new assigned role(among p_role_names_string)
2317 -- may violate the specified constraint
2318 --
2319 -- Params
2320 -- p_user_id := input fnd user_id
2321 -- p_constraint_rev_id := input constraint_rev_id
2322 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
2323 -- p_new_role_names_string := input a string list of new roles assigning to this user,
2324 -- the role_name is seperated by ','
2325 --
2326 -- Return
2327 -- a string list of role display names which violates the specified constraint,
2328 -- each display name is seperated by ','
2329 --
2330 -- History
2331 -- 07/27/2005 tsho Create
2332 -- ===============================================================
2333 Function Get_Violat_New_Role_List (
2334 p_user_id IN NUMBER,
2335 p_constraint_rev_id IN NUMBER,
2336 p_constraint_type_code IN VARCHAR2,
2337 p_new_role_names_string IN VARCHAR2
2338 ) RETURN VARCHAR2
2339 IS
2340 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_New_Role_List';
2341 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2342
2343 -- store the new role this user has against this constraint
2344 l_new_role_table JTF_VARCHAR2_TABLE_400;
2345
2346 -- store the return value
2347 l_new_role_string VARCHAR2(32767);
2348
2349 l_new_role_names_string VARCHAR2(32767):=p_new_role_names_string;
2350
2351 TYPE refCurTyp IS REF CURSOR;
2352 new_role_c refCurTyp;
2353
2354 -- find new roles this user has (results in violating the specified constraint)
2355 l_new_role_dynamic_sql VARCHAR2(500) :=
2356 'select distinct rv.display_name '
2357 ||' from amw_role_constraint_denorm rcd '
2358 ||' ,'||G_AMW_ALL_ROLES_VL||' rv '
2359 ||' where rcd.constraint_rev_id = :1 '
2360 ||' and (rcd.role_name = rv.name or (rv.orig_system = ''FND_RESP'' and rcd.responsibility_id = rv.orig_system_id ) ) '
2361 ||' and rv.name in (:2) ';
2362
2363
2364 -- ptulasi : 08/22/2007
2365 -- Bug 5558490 : Added below query to find new responsibilities user has
2366 -- (results in violating the specified constraint)
2367 l_new_resp_dynamic_sql VARCHAR2(1500) :=
2368 ' SELECT distinct rv.display_name '
2369 ||' FROM fnd_responsibility_vl frv '
2370 ||' ,'||G_AMW_ALL_ROLES_VL||' rv ,'
2371 ||' amw_constraint_entries ace'
2372 ||' WHERE rv.name IN (:1)'
2373 ||' AND frv.responsibility_id = ace.function_id '
2374 ||' AND frv.APPLICATION_ID = ace.APPLICATION_ID '
2375 ||' AND ace.object_type = ''RESP'''
2376 ||' AND ace.constraint_rev_id = :2'
2377 ||' AND ( rv.display_name = frv.responsibility_name OR ( rv.orig_system = ''FND_RESP'' AND frv.responsibility_id = rv.orig_system_id ))'
2378 ||' AND (frv.end_date is null OR (frv.end_date is not null AND frv.end_date > sysdate))';
2379
2380 BEGIN
2381 l_new_role_string := NULL;
2382
2383 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL AND p_new_role_names_string IS NOT NULL) THEN
2384
2385 -- 24-12-2005 : psomanat
2386 -- When the Send_Notif_To_Process_Owner call this function the role name
2387 -- does not have the @ delimiter at the last.
2388 IF (instr(l_new_role_names_string,'@') = 0) THEN
2389 l_new_role_names_string := l_new_role_names_string || '@';
2390 END IF;
2391
2392 WHILE (substr(l_new_role_names_string,1,instr(l_new_role_names_string,'@')-1) IS NOT NULL)
2393 LOOP
2394
2395 -- ptulasi : 08/22/2007
2396 -- Bug 5558490 : Added below code to execute the query based on the constraint type
2397 IF p_constraint_type_code <> 'RESP' THEN
2398 OPEN new_role_c FOR l_new_role_dynamic_sql USING
2399 p_constraint_rev_id, substr(l_new_role_names_string,1,instr(l_new_role_names_string,'@')-1) ;
2400 ELSE
2401 OPEN new_role_c FOR l_new_resp_dynamic_sql USING
2402 substr(l_new_role_names_string,1,instr(l_new_role_names_string,'@')-1), p_constraint_rev_id;
2403 END IF;
2404
2405 FETCH new_role_c BULK COLLECT INTO l_new_role_table;
2406 CLOSE new_role_c;
2407
2408 IF l_new_role_table IS NOT NULL AND l_new_role_table.FIRST IS NOT NULL THEN
2409 IF l_new_role_string IS NULL THEN
2410 l_new_role_string :=l_new_role_table(1);
2411 ELSE
2412 l_new_role_string :=l_new_role_string||', '||l_new_role_table(1);
2413 END IF;
2414 FOR i in 2 .. l_new_role_table.COUNT
2415 LOOP
2416 l_new_role_string := l_new_role_string||', '||l_new_role_table(i);
2417 END LOOP;
2418 END IF; -- end of if: l_new_role_table IS NOT NULL
2419 l_new_role_names_string:=substr(l_new_role_names_string,instr(l_new_role_names_string,'@')+1);
2420 END LOOP;
2421 END IF; --end of if: p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL AND p_new_role_names_string IS NOT NULL
2422 return l_new_role_string;
2423 END Get_Violat_New_Role_List;
2424
2425
2426 -- ===============================================================
2427 -- Function name
2428 -- Get_Violat_Existing_Role_List
2429 --
2430 -- Purpose
2431 -- get a flat string list of this user's existing role display name, together with those new assigned role(among p_role_names_string)
2432 -- may violate the specified constraint
2433 --
2434 -- Params
2435 -- p_user_id := input fnd user_id
2436 -- p_constraint_rev_id := input constraint_rev_id
2437 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
2438 --
2439 -- Return
2440 -- a string list of role display names which violates the specified constraint,
2441 -- each display name is seperated by ','
2442 --
2443 -- History
2444 -- 07/27/2005 tsho Create
2445 -- ===============================================================
2446 Function Get_Violat_Existing_Role_List (
2447 p_user_id IN NUMBER,
2448 p_constraint_rev_id IN NUMBER,
2449 p_constraint_type_code IN VARCHAR2
2450 ) RETURN VARCHAR2
2451 IS
2452 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Existing_Role_List';
2453 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2454
2455 -- store the return value
2456 l_existing_role_string VARCHAR2(32767);
2457
2458 -- store the existing role this user has against this constraint
2459 l_existing_role_table JTF_VARCHAR2_TABLE_400;
2460
2461 TYPE refCurTyp IS REF CURSOR;
2462 existing_role_c refCurTyp;
2463
2464 -- find existing roles this user has (results in violating the specified constraint)
2465 l_existing_role_dynamic_sql VARCHAR2(500) :=
2466 'select distinct rv.display_name '
2467 ||' from amw_role_constraint_denorm rcd '
2468 ||' ,'||G_AMW_USER_ROLES||' ur '
2469 ||' ,'||G_AMW_user||' u '
2470 ||' ,'||G_AMW_ALL_ROLES_VL||' rv '
2471 ||' where rcd.constraint_rev_id = :1 '
2472 ||' and u.user_id = :2 '
2473 ||' and u.user_name = ur.user_name '
2474 ||' and ur.role_name = rcd.role_name '
2475 ||' and ur.role_orig_system = ''UMX'' '
2476 ||' and ur.role_name = rv.name ';
2477
2478 BEGIN
2479 l_existing_role_string := NULL;
2480
2481 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL) THEN
2482 OPEN existing_role_c FOR l_existing_role_dynamic_sql USING
2483 p_constraint_rev_id,
2484 p_user_id;
2485 FETCH existing_role_c BULK COLLECT INTO l_existing_role_table;
2486 CLOSE existing_role_c;
2487
2488 IF l_existing_role_table IS NOT NULL AND l_existing_role_table.FIRST IS NOT NULL THEN
2489 l_existing_role_string := l_existing_role_table(1);
2490 FOR i in 2 .. l_existing_role_table.COUNT
2491 LOOP
2492 l_existing_role_string := l_existing_role_string||', '||l_existing_role_table(i);
2493 END LOOP;
2494 END IF; -- end of if: l_existing_role_table IS NOT NULL
2495
2496 END IF; --end of if: p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL
2497
2498 return l_existing_role_string;
2499
2500 END Get_Violat_Existing_Role_List;
2501
2502
2503
2504
2505 -- ===============================================================
2506 -- Function name
2507 -- Get_Violat_Existing_Resp_List
2508 --
2509 -- Purpose
2510 -- get a flat string list of this user's existing responsibility display name, together with those new assigned role(among p_role_names_string)
2511 -- may violate the specified constraint
2512 --
2513 -- Params
2514 -- p_user_id := input fnd user_id
2515 -- p_constraint_rev_id := input constraint_rev_id
2516 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
2517 --
2518 -- Return
2519 -- a string list of role display names which violates the specified constraint,
2520 -- each display name is seperated by ','
2521 --
2522 -- History
2523 -- 07/27/2005 tsho Create
2524 -- ===============================================================
2525 Function Get_Violat_Existing_Resp_List (
2526 p_user_id IN NUMBER,
2527 p_constraint_rev_id IN NUMBER,
2528 p_constraint_type_code IN VARCHAR2
2529 ) RETURN VARCHAR2
2530 IS
2531 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Existing_Resp_List';
2532 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2533
2534 -- store the return value
2535 l_existing_resp_string VARCHAR2(32767);
2536
2537 -- store the existing responsibilities this user has against this constraint
2538 l_existing_resp_table JTF_VARCHAR2_TABLE_400;
2539
2540 TYPE refCurTyp IS REF CURSOR;
2541 existing_resp_c refCurTyp;
2542
2543 -- find existing responsibilities this user has (results in violating the specified constraint)
2544 l_existing_resp_dynamic_sql VARCHAR2(500) :=
2545 'select distinct resp.responsibility_name '
2546 ||' from amw_role_constraint_denorm rcd '
2547 ||' ,'||G_AMW_USER_ROLES||' ur '
2548 ||' ,'||G_AMW_user||' u '
2549 ||' ,'||G_AMW_RESPONSIBILITY_VL||' resp '
2550 ||' where rcd.constraint_rev_id = :1 '
2551 ||' and u.user_id = :2 '
2552 ||' and u.user_name = ur.user_name '
2553 ||' and rcd.responsibility_id = resp.responsibility_id '
2554 ||' and ur.role_orig_system_id = rcd.responsibility_id '
2555 ||' and ur.role_orig_system = ''FND_RESP'' '
2556 ||' and ur.role_orig_system_id = resp.responsibility_id ';
2557
2558 BEGIN
2559 l_existing_resp_string := NULL;
2560
2561 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL) THEN
2562 OPEN existing_resp_c FOR l_existing_resp_dynamic_sql USING
2563 p_constraint_rev_id,
2564 p_user_id;
2565 FETCH existing_resp_c BULK COLLECT INTO l_existing_resp_table;
2566 CLOSE existing_resp_c;
2567
2568 IF l_existing_resp_table IS NOT NULL AND l_existing_resp_table.FIRST IS NOT NULL THEN
2569 l_existing_resp_string := l_existing_resp_table(1);
2570 FOR i in 2 .. l_existing_resp_table.COUNT
2571 LOOP
2572 l_existing_resp_string := l_existing_resp_string||', '||l_existing_resp_table(i);
2573 END LOOP;
2574 END IF; -- end of if: l_existing_resp_table IS NOT NULL
2575
2576 END IF; --end of if: p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL
2577
2578 return l_existing_resp_string;
2579
2580 END Get_Violat_Existing_Resp_List;
2581
2582
2583 -- ===============================================================
2584 -- Function name
2585 -- Get_Violat_Existing_Menu_List
2586 --
2587 -- Purpose
2588 -- get a flat string list of this user's existing permission set(menu) display name, ]
2589 -- together with those new assigned role(among p_role_names_string)
2590 -- may violate the specified constraint
2591 --
2592 -- Params
2593 -- p_user_id := input fnd user_id
2594 -- p_constraint_rev_id := input constraint_rev_id
2595 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
2596 --
2597 -- Return
2598 -- a string list of role display names which violates the specified constraint,
2599 -- each display name is seperated by ','
2600 --
2601 -- History
2602 -- 07/27/2005 tsho Create
2603 -- ===============================================================
2604 Function Get_Violat_Existing_Menu_List (
2605 p_user_id IN NUMBER,
2606 p_constraint_rev_id IN NUMBER,
2607 p_constraint_type_code IN VARCHAR2
2608 ) RETURN VARCHAR2
2609 IS
2610 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Existing_Menu_List';
2611 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2612
2613 -- store the return value
2614 l_existing_menu_string VARCHAR2(32767);
2615
2616 -- store the existing menus this user has against this constraint
2617 l_existing_menu_table JTF_VARCHAR2_TABLE_400;
2618
2619 TYPE refCurTyp IS REF CURSOR;
2620 existing_menu_c refCurTyp;
2621
2622 -- find existing menus this user has (results in violating the specified constraint)
2623 l_existing_menu_dynamic_sql VARCHAR2(1500) :=
2624 ' select menu.user_menu_name '
2625 ||' from amw_role_constraint_denorm rcd '
2626 ||' ,'||G_AMW_MENUS_VL||' menu '
2627 ||' ,'||G_AMW_user||' u '
2628 ||' ,'||G_AMW_GRANTS||' gra '
2629 ||' where rcd.constraint_rev_id = :1 '
2630 ||' and u.user_id = :2 '
2631 ||' and u.user_name = gra.grantee_key '
2632 ||' and gra.grantee_type = ''USER'' '
2633 ||' and gra.instance_type = ''GLOBAL'' '
2634 ||' and gra.object_id = -1 '
2635 ||' and gra.menu_id = rcd.menu_id '
2636 ||' and gra.menu_id = menu.menu_id '
2637 ||' UNION '
2638 ||' select menu.user_menu_name '
2639 ||' from amw_role_constraint_denorm rcd '
2640 ||' ,'||G_AMW_MENUS_VL||' menu '
2641 ||' ,'||G_AMW_GRANTS||' gra '
2642 ||' where rcd.constraint_rev_id = :3 '
2643 ||' and gra.grantee_key = ''GLOBAL'' '
2644 ||' and gra.grantee_type = ''GLOBAL'' '
2645 ||' and gra.instance_type = ''GLOBAL'' '
2646 ||' and gra.object_id = -1 '
2647 ||' and gra.menu_id = rcd.menu_id '
2648 ||' and gra.menu_id = menu.menu_id ';
2649
2650 BEGIN
2651 l_existing_menu_string := NULL;
2652
2653 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL) THEN
2654 OPEN existing_menu_c FOR l_existing_menu_dynamic_sql USING
2655 p_constraint_rev_id,
2656 p_user_id,
2657 p_constraint_rev_id;
2658 FETCH existing_menu_c BULK COLLECT INTO l_existing_menu_table;
2659 CLOSE existing_menu_c;
2660
2661 IF l_existing_menu_table IS NOT NULL AND l_existing_menu_table.FIRST IS NOT NULL THEN
2662 l_existing_menu_string := l_existing_menu_table(1);
2663 FOR i in 2 .. l_existing_menu_table.COUNT
2664 LOOP
2665 l_existing_menu_string := l_existing_menu_string||', '||l_existing_menu_table(i);
2666 END LOOP;
2667 END IF; -- end of if: l_existing_menu_table IS NOT NULL
2668
2669 END IF; --end of if: p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL
2670
2671 return l_existing_menu_string;
2672
2673 END Get_Violat_Existing_Menu_List;
2674
2675
2676 -- ===============================================================
2677 -- Function name
2678 -- Get_Violat_Comments
2679 --
2680 -- Purpose
2681 -- get comments(instruction) for specified constraint_rev_id
2682 --
2683 -- Params
2684 -- p_constraint_rev_id := input constraint_rev_id
2685 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
2686 --
2687 -- Return
2688 -- a seeded mesg
2689 --
2690 -- History
2691 -- 07/27/2005 tsho Create
2692 -- ===============================================================
2693 Function Get_Violat_Comments (
2694 p_constraint_rev_id IN NUMBER,
2695 p_constraint_type_code IN VARCHAR2
2696 ) RETURN VARCHAR2
2697 IS
2698 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Comments';
2699 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2700
2701 -- store the return value
2702 l_violat_comments VARCHAR2(80);
2703
2704 BEGIN
2705 l_violat_comments := NULL;
2706
2707 IF p_constraint_type_code IS NOT NULL THEN
2708 IF p_constraint_type_code = 'ALL' THEN
2709 l_violat_comments := AMW_UTILITY_PVT.get_message_text('AMW_ROLE_VIOLAT_ALL_COMMENT');
2710 ELSE
2711 l_violat_comments := AMW_UTILITY_PVT.get_message_text('AMW_ROLE_VIOLAT_ME_COMMENT');
2712 END IF;
2713 END IF; -- end of if: p_constraint_type_code IS NOT NULL
2714
2715 return l_Violat_Comments;
2716
2717 END Get_Violat_Comments;
2718
2719
2720 -- ===============================================================
2721 -- Function name
2722 -- Do_On_Role_Assigned
2723 --
2724 -- Purpose
2725 -- listen to the worflow business event(mainly uses for oracle.apps.fnd.wf.ds.userRole.created)
2726 -- and do corresponding actions
2727 --
2728 -- Params
2729 -- p_subscription_guid
2730 -- p_event
2731 --
2732 -- Return
2733 -- 'SUCCESS' | 'ERROR'
2734 --
2735 -- History
2736 -- 07/29/2005 tsho Create
2737 -- ===============================================================
2738 FUNCTION Do_On_Role_Assigned (
2739 p_subscription_guid in raw,
2740 p_event in out NOCOPY WF_EVENT_T
2741 ) return VARCHAR2
2742 IS
2743 L_API_NAME CONSTANT VARCHAR2(30) := 'Do_On_Role_Assigned';
2744 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2745
2746 l_user_name VARCHAR2(100);
2747 l_role_name VARCHAR2(320);
2748 l_assigned_date DATE;
2749 l_assigned_by_id NUMBER;
2750 l_user_id VARCHAR2(100);
2751
2752 TYPE curTyp IS REF CURSOR;
2753 c_user_dynamic_sql curTyp;
2754 l_user_dynamic_sql VARCHAR2(200) :=
2755 'SELECT u.user_id '
2756 ||' FROM '||G_AMW_USER ||' u '
2757 ||' WHERE u.user_name = :1 ';
2758
2759 BEGIN
2760 SAVEPOINT Do_On_Role_Assigned;
2761
2762 IF p_event.EVENT_NAME = 'oracle.apps.fnd.umx.requestapproved' THEN
2763 -- This event is raised when a a role is assigned to a user.
2764 l_user_id := p_event.GetValueForParameter('REQUESTED_FOR_USER_ID');
2765 l_role_name := p_event.GetValueForParameter('WF_ROLE_NAME');
2766 l_assigned_by_id := p_event.GetValueForParameter('REQUESTED_BY_USER_ID');
2767
2768 ELSE
2769 -- This event is raised when a assigned role is updated.
2770 l_user_name := p_event.GetValueForParameter('USER_NAME');
2771 l_role_name := p_event.GetValueForParameter('ROLE_NAME');
2772 l_assigned_by_id := p_event.GetValueForParameter('CREATED_BY');
2773
2774 OPEN c_user_dynamic_sql FOR l_user_dynamic_sql USING
2775 l_user_name;
2776 FETCH c_user_dynamic_sql INTO l_user_id;
2777 CLOSE c_user_dynamic_sql;
2778
2779 END IF;
2780
2781 IF l_user_id IS NOT NULL AND l_role_name IS NOT NULL THEN
2782 Send_Notif_To_Affected_Process(p_user_id => l_user_id,
2783 p_role_name => l_role_name,
2784 p_assigned_by_id => l_assigned_by_id);
2785 -- updated by dliao on 7-31-06 because the role_name could be null for the user
2786 -- registration (bug 5396917)
2787 -- Return 'SUCCESS';
2788 END IF;
2789
2790 Return 'SUCCESS';
2791
2792 EXCEPTION
2793 WHEN OTHERS THEN
2794 ROLLBACK TO Do_On_Role_Assigned;
2795 FND_MESSAGE.SET_NAME( 'AMW', 'GENERIC_MESSAGE' );
2796 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
2797 FND_MSG_PUB.ADD;
2798
2799 WF_CORE.CONTEXT('AMW_VIOLATION_PVT','Do_On_Role_Assigned error', p_event.getEventName(), p_subscription_guid);
2800 WF_EVENT.setErrorInfo(p_event,'ERROR');
2801 RETURN 'ERROR';
2802 END Do_On_Role_Assigned;
2803
2804
2805 -- ===============================================================
2806 -- Procedure name
2807 -- Send_Notif_To_Affected_Process
2808 --
2809 -- Purpose
2810 -- send violation notification to affected process owners
2811 -- it'll find which constraints have been violated due to the user role assignment
2812 -- and send notification to each process owner of those constraints
2813 -- Params
2814 -- p_item_type := worflow template (default : AMWNOTIF)
2815 -- p_message_name := workflow mesg template (default : MWVIOLATUSERROLENOTIF)
2816 -- p_user_name := the user who got the role assigned
2817 -- p_role_name := the new role which is assigned to this user
2818 -- p_assigned_date := the assigned date
2819 -- p_assigned_by_id := the role is assigned by which user (user_id)
2820 --
2821 -- History
2822 -- 07/29/2005 tsho Create
2823 -- 02/23/2006 psomanat removied the parameter p_user_name
2824 -- 02/23/2006 psomanat added the parameter p_user_id
2825 -- ===============================================================
2826 Procedure Send_Notif_To_Affected_Process(
2827 p_item_type IN VARCHAR2 := 'AMWNOTIF',
2828 p_message_name IN VARCHAR2 := 'VIOLATIONNOTIF',
2829 p_user_id IN NUMBER,
2830 p_role_name IN VARCHAR2,
2831 p_assigned_by_id IN NUMBER
2832 )
2833 IS
2834 L_API_NAME CONSTANT VARCHAR2(30) := 'Send_Notif_To_Affected_Process';
2835 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2836
2837 l_return_status varchar2(30);
2838
2839 -- store the result of l_violated_cst_dynamic_sql
2840 violated_cst_rev_id_list G_NUMBER_TABLE;
2841 new_role_list G_VARCHAR2_LONG_TABLE;
2842
2843 -- store the result of C_Proc_Owner
2844 affected_proc_owner_id_list G_NUMBER_TABLE;
2845
2846 -- store the result of C_Controls_Ids
2847 associated_cont_ids_list G_NUMBER_TABLE;
2848 TYPE curTyp IS REF CURSOR;
2849 -- get violated constraint for specified p_user_name and newly assigned p_role_name
2850 c_violated_cst_dynamic_sql curTyp;
2851 l_violated_cst_dynamic_sql VARCHAR2(32767) :=
2852 'select * from ( '
2853 -- ALL
2854 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:1,cst.constraint_rev_id,cst.type_code,:2) NEW_ROLE '
2855 ||' ,cst.constraint_rev_id '
2856 ||' from amw_constraints_b cst '
2857 ||' where cst.type_code = ''ALL'' '
2858 ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
2859 ||' and (select count(*) '
2860 ||' from amw_constraint_entries ce '
2861 ||' where ce.constraint_rev_id = cst.constraint_rev_id) = ( '
2862 ||' select count(distinct rcd.function_id) '
2863 ||' from amw_role_constraint_denorm rcd '
2864 ||' where rcd.constraint_rev_id = cst.constraint_rev_id '
2865 ||' and ( rcd.role_name = :3 '
2866 ||' or (rcd.role_name in ( '
2867 ||' select ur.role_name '
2868 ||' from '||G_AMW_USER_ROLES||' ur '
2869 ||' ,'||G_AMW_USER||' u '
2870 ||' where u.user_id = :4 '
2871 ||' and u.user_name = ur.user_name '
2872 ||' and ur.role_orig_system = ''UMX'') '
2873 ||' ) '
2874 ||' or (rcd.responsibility_id in ( '
2875 ||' select ur.role_orig_system_id '
2876 ||' from '||G_AMW_USER_ROLES||' ur '
2877 ||' ,'||G_AMW_USER||' u '
2878 ||' where u.user_id = :5 '
2879 ||' and u.user_name = ur.user_name '
2880 ||' and ur.role_orig_system = ''FND_RESP'') '
2881 ||' ) '
2882 ||' or (rcd.menu_id in ( '
2883 ||' select gra.menu_id '
2884 ||' from '||G_AMW_GRANTS||' gra '
2885 ||' ,'||G_AMW_USER||' u '
2886 ||' where u.user_id = :6 '
2887 ||' and u.user_name = gra.grantee_key '
2888 ||' and gra.grantee_type = ''USER'' '
2889 ||' and gra.instance_type = ''GLOBAL'' '
2890 ||' and gra.object_id = -1) '
2891 ||' ) '
2892 ||' or (rcd.menu_id in ( '
2893 ||' select gra.menu_id '
2894 ||' from '||G_AMW_GRANTS||' gra '
2895 ||' where gra.grantee_key = ''GLOBAL'' '
2896 ||' and gra.grantee_type = ''GLOBAL'' '
2897 ||' and gra.instance_type = ''GLOBAL'' '
2898 ||' and gra.object_id = -1) '
2899 ||' ) '
2900 ||' ) '
2901 ||' ) '
2902 ||' UNION ALL '
2903 -- ME
2904 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:7,cst.constraint_rev_id,cst.type_code,:8) NEW_ROLE '
2905 ||' , cst.constraint_rev_id '
2906 ||' from amw_constraints_b cst '
2907 ||' where cst.type_code = ''ME'' '
2908 ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
2909 ||' and (select count(distinct rcd.function_id) '
2910 ||' from amw_role_constraint_denorm rcd '
2911 ||' where rcd.constraint_rev_id = cst.constraint_rev_id '
2912 ||' and ( rcd.role_name = :9 '
2913 ||' or (rcd.role_name in ( '
2914 ||' select ur.role_name '
2915 ||' from '||G_AMW_USER_ROLES||' ur '
2916 ||' ,'||G_AMW_USER||' u '
2917 ||' where u.user_id = :10 '
2918 ||' and u.user_name = ur.user_name '
2919 ||' and ur.role_orig_system = ''UMX'') '
2920 ||' ) '
2921 ||' or (rcd.responsibility_id in ( '
2922 ||' select ur.role_orig_system_id '
2923 ||' from '||G_AMW_USER_ROLES||' ur '
2924 ||' ,'||G_AMW_USER||' u '
2925 ||' where u.user_id = :11 '
2926 ||' and u.user_name = ur.user_name '
2927 ||' and ur.role_orig_system = ''FND_RESP'') '
2928 ||' ) '
2929 ||' or (rcd.menu_id in ( '
2930 ||' select gra.menu_id '
2931 ||' from '||G_AMW_GRANTS||' gra '
2932 ||' ,'||G_AMW_USER||' u '
2933 ||' where u.user_id = :12 '
2934 ||' and u.user_name = gra.grantee_key '
2935 ||' and gra.grantee_type = ''USER'' '
2936 ||' and gra.instance_type = ''GLOBAL'' '
2937 ||' and gra.object_id = -1) '
2938 ||' ) '
2939 ||' or (rcd.menu_id in ( '
2940 ||' select gra.menu_id '
2941 ||' from '||G_AMW_GRANTS||' gra '
2942 ||' where gra.grantee_key = ''GLOBAL'' '
2943 ||' and gra.grantee_type = ''GLOBAL'' '
2944 ||' and gra.instance_type = ''GLOBAL'' '
2945 ||' and gra.object_id = -1) '
2946 ||' ) '
2947 ||' ) '
2948 ||' ) >= 2 '
2949 ||' UNION ALL '
2950 -- SET
2951 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:13,cst.constraint_rev_id,cst.type_code,:14) NEW_ROLE '
2952 ||' ,cst.constraint_rev_id '
2953 ||' from amw_constraints_b cst '
2954 ||' where cst.type_code = ''SET'' '
2955 ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
2956 ||' and (select count(distinct rcd.group_code) '
2957 ||' from amw_role_constraint_denorm rcd '
2958 ||' where rcd.constraint_rev_id = cst.constraint_rev_id '
2959 ||' and (rcd.role_name = :15 '
2960 ||' or (rcd.role_name in ( '
2961 ||' select ur.role_name '
2962 ||' from '||G_AMW_USER_ROLES||' ur '
2963 ||' ,'||G_AMW_USER||' u '
2964 ||' where u.user_id = :16 '
2965 ||' and u.user_name = ur.user_name '
2966 ||' and ur.role_orig_system = ''UMX'') '
2967 ||' ) '
2968 ||' or (rcd.responsibility_id in ( '
2969 ||' select ur.role_orig_system_id '
2970 ||' from '||G_AMW_USER_ROLES||' ur '
2971 ||' ,'||G_AMW_USER||' u '
2972 ||' where u.user_id = :17 '
2973 ||' and u.user_name = ur.user_name '
2974 ||' and ur.role_orig_system = ''FND_RESP'') '
2975 ||' ) '
2976 ||' or (rcd.menu_id in ( '
2977 ||' select gra.menu_id '
2978 ||' from '||G_AMW_GRANTS||' gra '
2979 ||' ,'||G_AMW_USER||' u '
2980 ||' where u.user_id = :18 '
2981 ||' and u.user_name = gra.grantee_key '
2982 ||' and gra.grantee_type = ''USER'' '
2983 ||' and gra.instance_type = ''GLOBAL'' '
2984 ||' and gra.object_id = -1) '
2985 ||' ) '
2986 ||' or (rcd.menu_id in ( '
2987 ||' select gra.menu_id '
2988 ||' from '||G_AMW_GRANTS||' gra '
2989 ||' where gra.grantee_key = ''GLOBAL'' '
2990 ||' and gra.grantee_type = ''GLOBAL'' '
2991 ||' and gra.instance_type = ''GLOBAL'' '
2992 ||' and gra.object_id = -1) '
2993 ||' ) '
2994 ||' ) '
2995 ||' ) >= 2 '
2996 ||' UNION ALL '
2997 -- RESPALL
2998 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:19,cst.constraint_rev_id,cst.type_code,:20) NEW_ROLE '
2999 ||' ,cst.constraint_rev_id '
3000 ||' from amw_constraints_b cst '
3001 ||' where cst.type_code = ''RESPALL'' '
3002 ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
3003 ||' and (select count(*) '
3004 ||' from amw_constraint_entries ce '
3005 ||' where ce.constraint_rev_id = cst.constraint_rev_id) = ( '
3006 || ' select count(distinct ur.role_orig_system_id) '
3007 ||' from '
3008 || G_AMW_USER_ROLES||' ur '
3009 ||' ,'||G_AMW_user||' u '
3010 ||' ,amw_constraint_entries ce '
3011 ||' where u.user_id = :21 '
3012 ||' and ce.constraint_rev_id = cst.constraint_rev_id '
3013 ||' and u.user_name = ur.user_name '
3014 ||' and ur.role_orig_system_id = ce.function_id '
3015 ||' and (ur.role_orig_system = ''FND_RESP'' '
3016 ||' or ur.role_orig_system_id in ( '
3017 ||' select distinct rle.orig_system_id '
3018 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
3019 ||' where rle.orig_system = ''FND_RESP'' '
3020 ||' and ce.function_id = rle.orig_system_id '
3021 ||' and rle.name in (:22)) '
3022 ||') '
3023 || ') '
3024 ||' UNION ALL '
3025 -- RESPME
3026 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:23,cst.constraint_rev_id,cst.type_code,:24) NEW_ROLE '
3027 ||' , cst.constraint_rev_id '
3028 ||' from amw_constraints_b cst '
3029 ||' where cst.type_code = ''RESPME'' '
3030 ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
3031 ||' and '
3032 || ' (select count(distinct ur.role_orig_system_id ) '
3033 ||' from '
3034 || G_AMW_USER_ROLES||' ur '
3035 ||' ,'||G_AMW_user||' u '
3036 ||' ,amw_constraint_entries ce '
3037 ||' where u.user_id = :25 '
3038 ||' and ce.constraint_rev_id = cst.constraint_rev_id '
3039 ||' and u.user_name = ur.user_name '
3040 ||' and ur.role_orig_system_id = ce.function_id '
3041 ||' and (ur.role_orig_system = ''FND_RESP'' '
3042 ||' or ur.role_orig_system_id in ( '
3043 ||' select distinct rle.orig_system_id '
3044 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
3045 ||' where rle.orig_system = ''FND_RESP'' '
3046 ||' and ce.function_id = rle.orig_system_id '
3047 ||' and rle.name in (:26 )) '
3048 ||') '
3049 ||') '
3050 ||' >= 2 '
3051 ||' UNION ALL '
3052 -- RESPSET
3053 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:27,cst.constraint_rev_id,cst.type_code,:28) NEW_ROLE '
3054 ||' , cst.constraint_rev_id '
3055 ||' from amw_constraints_b cst '
3056 ||' where cst.type_code = ''RESPME'' '
3057 ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
3058 ||' and '
3059 || ' (select count(distinct ur.role_orig_system_id ) '
3060 ||' from '
3061 || G_AMW_USER_ROLES||' ur '
3062 ||' ,'||G_AMW_user||' u '
3063 ||' ,amw_constraint_entries ce '
3064 ||' where u.user_id = :29 '
3065 ||' and ce.constraint_rev_id = cst.constraint_rev_id '
3066 ||' and u.user_name = ur.user_name '
3067 ||' and ur.role_orig_system_id = ce.function_id '
3068 ||' and (ur.role_orig_system = ''FND_RESP'' '
3069 ||' or ur.role_orig_system_id in ( '
3070 ||' select distinct rle.orig_system_id '
3071 ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
3072 ||' where rle.orig_system = ''FND_RESP'' '
3073 ||' and ce.function_id = rle.orig_system_id '
3074 ||' and rle.name in (:30 )) '
3075 ||') '
3076 ||') '
3077 ||' >= 2 '
3078 ||' ) where NEW_ROLE IS NOT NULL ';
3079
3080 --27:12:05 : psomanat
3081 -- holds the control associated with the Constraint
3082 CURSOR C_Control_Ids(l_constraint_id in NUMBER) IS
3083 SELECT CONTROL_ID
3084 FROM AMW_CONTROLS_ALL_VL ctl
3085 WHERE ctl.AUTOMATION_TYPE ='SOD'
3086 AND CURR_APPROVED_FLAG ='Y'
3087 AND ctl.SOURCE =l_constraint_id;
3088
3089 -- Store the Constraint id for the given constraint revision id.
3090 l_constraint_id number;
3091
3092 --27:12:05 : psomanat
3093 -- A Control is associated a Constraint. So get the process owners of all the process
3094 -- that uses this control to mitigate the associated risk.
3095 CURSOR C_Proc_Owner(l_cont_id in NUMBER) IS
3096 SELECT distinct TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id
3097 FROM AMW_control_Associations ca,
3098 fnd_grants grants,
3099 fnd_menus granted_menu,
3100 fnd_objects obj
3101 WHERE CONTROL_ID = l_cont_id
3102 AND OBJECT_TYPE ='RISK'
3103 AND APPROVAL_DATE IS NOT NULL
3104 AND DELETION_APPROVAL_DATE IS NULL
3105 and obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
3106 AND grants.object_id = obj.object_id
3107 AND grants.grantee_type ='USER'
3108 AND grantee_key like 'HZ_PARTY%'
3109 AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
3110 AND grants.menu_id = granted_menu.menu_id
3111 AND grants.instance_type = 'INSTANCE'
3112 AND grants.instance_pk1_value = TO_CHAR(ca.PK1)
3113 AND grants.instance_pk2_value = '*NULL*'
3114 AND grants.instance_pk3_value = '*NULL*'
3115 AND grants.instance_pk4_value = '*NULL*'
3116 AND grants.instance_pk5_value = '*NULL*'
3117 AND granted_menu.menu_name = 'AMW_RL_PROC_OWNER_ROLE'
3118 UNION
3119 SELECT distinct TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id
3120 FROM AMW_control_Associations ca,
3121 fnd_grants grants,
3122 fnd_menus granted_menu,
3123 fnd_objects obj
3124 WHERE CONTROL_ID = l_cont_id
3125 AND OBJECT_TYPE ='RISK_ORG'
3126 AND APPROVAL_DATE IS NOT NULL
3127 AND DELETION_APPROVAL_DATE IS NULL
3128 AND obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
3129 AND grants.object_id = obj.object_id
3130 AND grants.grantee_type ='USER'
3131 AND grantee_key like 'HZ_PARTY%'
3132 AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
3133 AND grants.menu_id = granted_menu.menu_id
3134 AND grants.instance_type = 'INSTANCE'
3135 AND grants.instance_pk1_value = to_char(ca.pk1) -- PASS ORG_ID AS STRING
3136 AND grants.instance_pk2_value = to_char(ca.pk2) -- PASS PROCESS_ID AS STRING I.E. to_char(process_id)
3137 AND grants.instance_pk3_value = '*NULL*'
3138 AND grants.instance_pk4_value = '*NULL*'
3139 AND grants.instance_pk5_value = '*NULL*'
3140 and granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE';
3141
3142 BEGIN
3143 l_return_status := FND_API.G_RET_STS_SUCCESS;
3144
3145 IF (p_user_id IS NULL or p_role_name IS NULL) THEN
3146 RETURN;
3147 END IF;
3148
3149 -- get list of violated constraint id
3150 OPEN c_violated_cst_dynamic_sql FOR l_violated_cst_dynamic_sql USING
3151 -- ALL
3152 p_user_id
3153 ,p_role_name
3154 ,p_role_name
3155 ,p_user_id
3156 ,p_user_id
3157 ,p_user_id
3158 -- ME
3159 ,p_user_id
3160 ,p_role_name
3161 ,p_role_name
3162 ,p_user_id
3163 ,p_user_id
3164 ,p_user_id
3165 -- SET
3166 ,p_user_id
3167 ,p_role_name
3168 ,p_role_name
3169 ,p_user_id
3170 ,p_user_id
3171 ,p_user_id
3172 -- RESPALL
3173 ,p_user_id
3174 ,p_role_name
3175 ,p_user_id
3176 ,p_role_name
3177 --RESPME
3178 ,p_user_id
3179 ,p_role_name
3180 ,p_user_id
3181 ,p_role_name
3182 --RESPSET
3183 ,p_user_id
3184 ,p_role_name
3185 ,p_user_id
3186 ,p_role_name
3187 ;
3188 FETCH c_violated_cst_dynamic_sql BULK COLLECT INTO new_role_list, violated_cst_rev_id_list;
3189 CLOSE c_violated_cst_dynamic_sql;
3190
3191 IF violated_cst_rev_id_list IS NULL OR NOT violated_cst_rev_id_list.exists(1) THEN
3192 RETURN;
3193 END IF;
3194
3195 FOR i in 1 .. violated_cst_rev_id_list.COUNT
3196 LOOP
3197 -- The Constraint _id is associated to the control. So we get the
3198 --Constraint _id corresponding to the violated constraint_rev_id
3199 SELECT constraint_id into l_constraint_id
3200 FROM AMW_CONSTRAINTS_VL
3201 WHERE constraint_rev_id=violated_cst_rev_id_list(i);
3202
3203 -- A constraint can be associate to many controls. So we get the all
3204 -- the constrols with source as the constraint.
3205 OPEN C_Control_Ids(l_constraint_id);
3206 FETCH C_Control_Ids BULK COLLECT INTO associated_cont_ids_list;
3207 CLOSE C_Control_Ids;
3208
3209 -- For each control we find the affected process owner
3210 -- and send notification to them
3211 FOR k IN 1 .. associated_cont_ids_list.COUNT
3212 LOOP
3213 -- get all the affected process owners
3214 OPEN C_Proc_Owner(associated_cont_ids_list(k));
3215 FETCH C_Proc_Owner BULK COLLECT INTO affected_proc_owner_id_list;
3216 CLOSE C_Proc_Owner;
3217
3218 IF affected_proc_owner_id_list IS NOT NULL THEN
3219 FOR j in 1 ..affected_proc_owner_id_list.COUNT
3220 LOOP
3221 IF affected_proc_owner_id_list(j) IS NOT NULL THEN
3222 Send_Notif_To_Process_Owner(
3223 p_item_type => p_item_type,
3224 p_message_name => p_message_name,
3225 p_user_id => p_user_id,
3226 p_role_name => p_role_name,
3227 p_assigned_by_id => p_assigned_by_id,
3228 p_constraint_rev_id => violated_cst_rev_id_list(i),
3229 p_process_owner_id => affected_proc_owner_id_list(j),
3230 x_return_status => l_return_status);
3231 END IF;--end of if: affected_proc_owner_id_list(j) IS NOT NULL
3232 END LOOP; -- end of for: affected_proc_owner_id_list.COUNT
3233 END IF; --end of if: affected_proc_owner_id_list IS NOT NULL
3234 END LOOP; --end of for :associated_cont_ids_list.COUNT
3235 END LOOP; --end of for: violated_cst_rev_id_list.COUNT
3236 EXCEPTION
3237 WHEN OTHERS THEN
3238 fnd_file.put_line(fnd_file.LOG,'unexpected error in Send_Notif_To_Affected_Process: '||sqlerrm);
3239 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3240 END Send_Notif_To_Affected_Process;
3241
3242 -- ===============================================================
3243 -- Procedure name
3244 -- Send_Notif_To_Process_Owner
3245 --
3246 -- Purpose
3247 -- send violation notification to specified process owner
3248 -- Params
3249 -- p_item_type := worflow template (default : AMWNOTIF)
3250 -- p_message_name := workflow mesg template (default : MWVIOLATUSERROLENOTIF)
3251 -- p_user_name := the user who got the role assigned
3252 -- p_role_name := the new role which is assigned to this user
3253 -- p_assigned_date := the assigned date
3254 -- p_assigned_by_id := the role is assigned by which user (user_id)
3255 -- p_constraint_rev_id := which constraint has been violated
3256 -- p_process_owner_id := the process owner of that constraint, to whom this notif will be sent
3257 --
3258 -- History
3259 -- 07/29/2005 tsho Create
3260 -- 02/23/2006 psomanat removied the parameter p_user_name
3261 -- 02/23/2006 psomanat added the parameter p_user_id
3262 -- ===============================================================
3263 Procedure Send_Notif_To_Process_Owner(
3264 p_item_type IN VARCHAR2 := 'AMWNOTIF',
3265 p_message_name IN VARCHAR2 := 'VIOLATIONNOTIF',
3266 p_user_id IN NUMBER,
3267 p_role_name IN VARCHAR2,
3268 p_assigned_by_id IN NUMBER,
3269 p_constraint_rev_id IN NUMBER,
3270 p_process_owner_id IN NUMBER,
3271 x_return_status OUT NOCOPY VARCHAR2
3272 )
3273 IS
3274 L_API_NAME CONSTANT VARCHAR2(30) := 'Send_Notif_To_Process_Owner';
3275 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
3276
3277 l_constraint_name VARCHAR2(240);
3278 l_to_role_name VARCHAR2(100);
3279 l_display_role_name VARCHAR2(320);
3280 l_subject VARCHAR2(2000);
3281 l_user_name Varchar2(240);
3282 l_role_name VARCHAR2(320) :=p_role_name;
3283 l_assigned_by_id NUMBER :=p_assigned_by_id;
3284 l_notif_id NUMBER;
3285 emp_id_list G_NUMBER_TABLE;
3286
3287 cursor c_constraint (l_constraint_rev_id NUMBER) is
3288 select constraint_name
3289 from amw_constraints_vl
3290 where constraint_rev_id = l_constraint_rev_id;
3291
3292 cursor c_person (c_party_id NUMBER) is
3293 select employee_id
3294 from amw_employees_current_v
3295 where party_id = c_party_id;
3296
3297 TYPE curTyp IS REF CURSOR;
3298 c_user_dynamic_sql curTyp;
3299 l_user_dynamic_sql VARCHAR2(200) :=
3300 'SELECT u.user_name '
3301 ||' FROM '||G_AMW_USER ||' u '
3302 ||' WHERE u.user_id = :1 ';
3303 BEGIN
3304 x_return_status := FND_API.G_RET_STS_SUCCESS;
3305
3306 open c_constraint(p_constraint_rev_id);
3307 fetch c_constraint into l_constraint_name;
3308 close c_constraint;
3309
3310 open c_person(p_process_owner_id);
3311 fetch c_person bulk collect into emp_id_list;
3312 close c_person;
3313
3314 FOR i in 1 .. emp_id_list.COUNT
3315 LOOP
3316 l_to_role_name:=NULL;
3317
3318 WF_DIRECTORY.getrolename( p_orig_system => 'PER',
3319 p_orig_system_id => emp_id_list(i),
3320 p_name => l_to_role_name,
3321 p_display_name => l_display_role_name );
3322
3323 IF l_to_role_name IS NULL THEN
3324
3325 x_return_status := FND_API.G_RET_STS_ERROR;
3326 FND_MESSAGE.set_name('AMW','AMW_APPR_INVALID_ROLE');
3327 FND_MSG_PUB.ADD;
3328 fnd_file.put_line(fnd_file.LOG, 'to_role is null');
3329 ELSE
3330
3331 OPEN c_user_dynamic_sql FOR l_user_dynamic_sql USING
3332 p_user_id;
3333 FETCH c_user_dynamic_sql INTO l_user_name;
3334 CLOSE c_user_dynamic_sql;
3335
3336 FND_MESSAGE.set_name('AMW', 'AMW_VIOLAT_CONFIRM_NOTIF_SUBJ');
3337 FND_MESSAGE.set_token('CONSTRAINT_NAME', l_constraint_name, TRUE);
3338 FND_MESSAGE.set_token('USER_NAME', l_user_name, TRUE);
3339 FND_MSG_PUB.add;
3340 l_subject := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_LAST,
3341 p_encoded => fnd_api.g_false);
3342
3343 l_notif_id := WF_NOTIFICATION.send(
3344 role => l_to_role_name,
3345 msg_type => p_item_type,
3346 msg_name => p_message_name);
3347
3348 WF_NOTIFICATION.SetAttrText(l_notif_id,'VN_MSG_SUBJECT',l_subject);
3349 WF_NOTIFICATION.setattrnumber(l_notif_id,'VN_CONSTRAINT_REV_ID',p_constraint_rev_id);
3350 WF_NOTIFICATION.SetAttrText(l_notif_id,'VN_ROLE_NAME',l_role_name);
3351 WF_NOTIFICATION.setattrnumber(l_notif_id,'VN_USER_ID',p_user_id);
3352 WF_NOTIFICATION.setattrnumber(l_notif_id,'VN_ASSIGNED_BY_ID',l_assigned_by_id);
3353
3354 END IF; --end of if: l_to_role_name IS NULL
3355 END LOOP; --end of for: emp_id_list.COUNT
3356 EXCEPTION
3357 WHEN OTHERS THEN
3358 fnd_file.put_line(fnd_file.LOG,'unexpected error in Send_Notif_To_Process_Owner: '||sqlerrm);
3359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3360 END;
3361
3362
3363 -- ===============================================================
3364 -- Function name
3365 -- Violation_Detail_Due_To_Resp
3366 --
3367 -- Purpose
3368 -- check for OICM SOD constriants that will be violated
3369 -- if the user is assigned the additional responsibility
3370 -- Params
3371 -- p_user_id := input fnd user_id
3372 -- p_responsibility_id := input fnd responsibility_id
3373 -- Return
3374 -- 'N' := if no SOD violation found.
3375 -- 'ConstraintName:Resp_name1;Resp_name2;...' := if SOD violation exists.
3376 -- The SOD violation should NOT be restricted to
3377 -- only the new responsiblity.
3378 -- If the existing responsibilities have any violations,
3379 -- the function should return 'Y' as well.
3380 --
3381 -- History
3382 -- 08/01/2005 tsho Create
3383 -- 08/03/2005 tsho Consider User Waivers
3384 -- 08/22/2005 tsho Consider only prevent(PR) constraint objective
3385 -- ===============================================================
3386 Function Violation_Detail_Due_To_Resp (
3387 p_user_id IN NUMBER,
3388 p_responsibility_id IN NUMBER
3389 ) return VARCHAR2
3390 IS
3391
3392 L_API_NAME CONSTANT VARCHAR2(30) := 'Violation_Detail_Due_To_Resp';
3393 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
3394
3395 -- return result
3396 has_violation VARCHAR2(32767);
3397 l_violat_existing_resp VARCHAR2(10000);
3398 l_violat_existing_role VARCHAR2(10000);
3399 l_violat_existing_menu VARCHAR2(10000);
3400 l_violat_new_resp VARCHAR2(100);
3401
3402 -- find all valid constraints
3403 CURSOR c_all_valid_constraints IS
3404 SELECT constraint_rev_id, type_code, constraint_name, objective_code
3405 FROM amw_constraints_vl
3406 WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);
3407 l_all_valid_constraints c_all_valid_constraints%ROWTYPE;
3408
3409 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
3410 l_constraint_entries_count NUMBER;
3411 l_func_access_count NUMBER;
3412 l_group_access_count NUMBER;
3413 CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
3414 SELECT count(*)
3415 FROM amw_constraint_entries
3416 WHERE constraint_rev_id=l_constraint_rev_id;
3417
3418
3419 TYPE refCurTyp IS REF CURSOR;
3420 func_acess_count_c refCurTyp;
3421 group_acess_count_c refCurTyp;
3422
3423 l_func_dynamic_sql VARCHAR2(2500) :=
3424 'select count(distinct function_id) from ( '
3425 ||' select rcd.function_id '
3426 ||' from amw_role_constraint_denorm rcd '
3427 ||' ,'||G_AMW_USER_ROLES||' ur '
3428 ||' ,'||G_AMW_user||' u '
3429 ||' where rcd.constraint_rev_id = :1 '
3430 ||' and u.user_id = :2 '
3431 ||' and u.user_name = ur.user_name '
3432 ||' and ur.role_name = rcd.role_name '
3433 ||' and ur.role_orig_system = ''UMX'' '
3434 ||' UNION ALL '
3435 ||' select rcd.function_id '
3436 ||' from amw_role_constraint_denorm rcd '
3437 ||' ,'||G_AMW_USER_ROLES||' ur '
3438 ||' ,'||G_AMW_user||' u '
3439 ||' where rcd.constraint_rev_id = :3 '
3440 ||' and u.user_id = :4 '
3441 ||' and u.user_name = ur.user_name '
3442 ||' and ur.role_orig_system_id = rcd.responsibility_id '
3443 ||' and ur.role_orig_system = ''FND_RESP'' '
3444 ||' UNION ALL '
3445 ||' select rcd.function_id '
3446 ||' from amw_role_constraint_denorm rcd '
3447 ||' ,'||G_AMW_GRANTS||' gra '
3448 ||' ,'||G_AMW_USER||' u '
3449 ||' where rcd.constraint_rev_id = :5 '
3450 ||' and u.user_id = :6 '
3451 ||' and u.user_name = gra.grantee_key '
3452 ||' and gra.grantee_type = ''USER'' '
3453 ||' and gra.menu_id = rcd.menu_id '
3454 ||' and gra.instance_type = ''GLOBAL'' '
3455 ||' and gra.object_id = -1 '
3456 ||' UNION ALL '
3457 ||' select rcd.function_id '
3458 ||' from amw_role_constraint_denorm rcd '
3459 ||' ,'||G_AMW_GRANTS||' gra '
3460 ||' where rcd.constraint_rev_id = :7 '
3461 ||' and gra.grantee_key = ''GLOBAL'' '
3462 ||' and gra.grantee_type = ''GLOBAL'' '
3463 ||' and gra.menu_id = rcd.menu_id '
3464 ||' and gra.instance_type = ''GLOBAL'' '
3465 ||' and gra.object_id = -1 '
3466 ||' UNION ALL '
3467 ||' select rcd.function_id '
3468 ||' from amw_role_constraint_denorm rcd '
3469 ||' where rcd.constraint_rev_id = :8 '
3470 ||' and rcd.responsibility_id = :9 '
3471 ||') ';
3472
3473 l_func_set_dynamic_sql VARCHAR2(2500) :=
3474 'select count(distinct group_code) from ( '
3475 ||' select rcd.group_code '
3476 ||' from amw_role_constraint_denorm rcd '
3477 ||' ,'||G_AMW_USER_ROLES||' ur '
3478 ||' ,'||G_AMW_user||' u '
3479 ||' where rcd.constraint_rev_id = :1 '
3480 ||' and u.user_id = :2 '
3481 ||' and u.user_name = ur.user_name '
3482 ||' and ur.role_name = rcd.role_name '
3483 ||' and ur.role_orig_system = ''UMX'' '
3484 ||' UNION ALL '
3485 ||' select rcd.group_code '
3486 ||' from amw_role_constraint_denorm rcd '
3487 ||' ,'||G_AMW_USER_ROLES||' ur '
3488 ||' ,'||G_AMW_user||' u '
3489 ||' where rcd.constraint_rev_id = :3 '
3490 ||' and u.user_id = :4 '
3491 ||' and u.user_name = ur.user_name '
3492 ||' and ur.role_orig_system_id = rcd.responsibility_id '
3493 ||' and ur.role_orig_system = ''FND_RESP'' '
3494 ||' UNION ALL '
3495 ||' select rcd.group_code '
3496 ||' from amw_role_constraint_denorm rcd '
3497 ||' ,'||G_AMW_GRANTS||' gra '
3498 ||' ,'||G_AMW_USER||' u '
3499 ||' where rcd.constraint_rev_id = :5 '
3500 ||' and u.user_id = :6 '
3501 ||' and u.user_name = gra.grantee_key '
3502 ||' and gra.grantee_type = ''USER'' '
3503 ||' and gra.menu_id = rcd.menu_id '
3504 ||' and gra.instance_type = ''GLOBAL'' '
3505 ||' and gra.object_id = -1 '
3506 ||' UNION ALL '
3507 ||' select rcd.group_code '
3508 ||' from amw_role_constraint_denorm rcd '
3509 ||' ,'||G_AMW_GRANTS||' gra '
3510 ||' where rcd.constraint_rev_id = :7 '
3511 ||' and gra.grantee_key = ''GLOBAL'' '
3512 ||' and gra.grantee_type = ''GLOBAL'' '
3513 ||' and gra.menu_id = rcd.menu_id '
3514 ||' and gra.instance_type = ''GLOBAL'' '
3515 ||' and gra.object_id = -1 '
3516 ||' UNION ALL '
3517 ||' select rcd.group_code '
3518 ||' from amw_role_constraint_denorm rcd '
3519 ||' where rcd.constraint_rev_id = :8 '
3520 ||' and rcd.responsibility_id = :9 '
3521 ||') ';
3522
3523 -- get the name of the new respsonsibility which this user intends to have
3524 new_resp_c refCurTyp;
3525 l_new_resp_dynamic_sql VARCHAR2(500) :=
3526 'select resp.responsibility_name '
3527 ||' from amw_role_constraint_denorm rcd '
3528 ||' ,'||G_AMW_RESPONSIBILITY_VL||' resp '
3529 ||' where rcd.constraint_rev_id = :1 and resp.responsibility_id = :2 '
3530 ||' and rcd.responsibility_id = resp.responsibility_id ';
3531
3532 -- get valid user waiver
3533 l_valid_user_waiver_count NUMBER;
3534 CURSOR c_valid_user_waivers (l_constraint_rev_id IN NUMBER, l_user_id IN NUMBER) IS
3535 SELECT count(*)
3536 FROM amw_constraint_waivers
3537 WHERE constraint_rev_id = l_constraint_rev_id
3538 AND object_type = 'USER'
3539 AND PK1 = l_user_id
3540 AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
3541
3542 BEGIN
3543 -- default to 'N', which means user doesn't have violations
3544 has_violation := 'N';
3545 l_violat_existing_resp := NULL;
3546 l_violat_existing_role := NULL;
3547 l_violat_existing_menu := NULL;
3548 l_violat_new_resp := NULL;
3549 l_valid_user_waiver_count := 0;
3550
3551 IF (p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL) THEN
3552 -- check all valid constraints
3553 OPEN c_all_valid_constraints;
3554 LOOP
3555 FETCH c_all_valid_constraints INTO l_all_valid_constraints;
3556 EXIT WHEN c_all_valid_constraints%NOTFOUND;
3557
3558 -- check if this user is waived (due to User Waiver) from this constraint
3559 OPEN c_valid_user_waivers(l_all_valid_constraints.constraint_rev_id, p_user_id);
3560 FETCH c_valid_user_waivers INTO l_valid_user_waiver_count;
3561 CLOSE c_valid_user_waivers;
3562
3563 -- 08.22.2005 tsho: consider only Prevent Constraint Objective
3564 -- IF l_valid_user_waiver_count <= 0 THEN
3565 IF l_valid_user_waiver_count <= 0 AND l_all_valid_constraints.objective_code = 'PR' THEN
3566
3567 -- get the name of the new responsibility if combining this will results in violation against this constraint
3568 OPEN new_resp_c FOR l_new_resp_dynamic_sql USING
3569 l_all_valid_constraints.constraint_rev_id
3570 ,p_responsibility_id;
3571 FETCH new_resp_c INTO l_violat_new_resp;
3572 CLOSE new_resp_c;
3573
3574 IF 'ALL' = l_all_valid_constraints.type_code THEN
3575 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
3576 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
3577 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
3578 CLOSE c_constraint_entries_count;
3579
3580 -- find the number of distinct constraint entries this user can access
3581 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
3582 l_all_valid_constraints.constraint_rev_id,
3583 p_user_id,
3584 l_all_valid_constraints.constraint_rev_id,
3585 p_user_id,
3586 l_all_valid_constraints.constraint_rev_id,
3587 p_user_id,
3588 l_all_valid_constraints.constraint_rev_id,
3589 l_all_valid_constraints.constraint_rev_id,
3590 p_responsibility_id;
3591 FETCH func_acess_count_c INTO l_func_access_count;
3592 CLOSE func_acess_count_c;
3593
3594 -- in ALL type: if user can access to all entries of this constraint,
3595 -- he violates this constraint
3596 IF l_func_access_count = l_constraint_entries_count THEN
3597 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
3598 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
3599 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
3600 p_user_id => p_user_id,
3601 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3602 p_constraint_type_code => l_all_valid_constraints.type_code);
3603 l_violat_existing_role := Get_Violat_Existing_Role_List (
3604 p_user_id => p_user_id,
3605 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3606 p_constraint_type_code => l_all_valid_constraints.type_code);
3607 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
3608 p_user_id => p_user_id,
3609 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3610 p_constraint_type_code => l_all_valid_constraints.type_code);
3611
3612 -- concatinate return result(Violation Details)
3613 has_violation := l_violat_new_resp;
3614 IF l_violat_existing_resp IS NOT NULL THEN
3615 IF has_violation IS NOT NULL THEN
3616 has_violation := has_violation||', ';
3617 END IF;
3618 has_violation := has_violation||l_violat_existing_resp;
3619 END IF;
3620 IF l_violat_existing_role IS NOT NULL THEN
3621 IF has_violation IS NOT NULL THEN
3622 has_violation := has_violation||', ';
3623 END IF;
3624 has_violation := has_violation||l_violat_existing_role;
3625 END IF;
3626 IF l_violat_existing_menu IS NOT NULL THEN
3627 IF has_violation IS NOT NULL THEN
3628 has_violation := has_violation||', ';
3629 END IF;
3630 has_violation := has_violation||l_violat_existing_menu;
3631 END IF;
3632
3633 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
3634 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
3635 fnd_message.set_token('CONST_DETAILS', has_violation);
3636 return fnd_message.get;
3637 END IF;
3638
3639 ELSIF 'ME' = l_all_valid_constraints.type_code THEN
3640 -- find the number of distinct constraint entries this user can access
3641 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
3642 l_all_valid_constraints.constraint_rev_id,
3643 p_user_id,
3644 l_all_valid_constraints.constraint_rev_id,
3645 p_user_id,
3646 l_all_valid_constraints.constraint_rev_id,
3647 p_user_id,
3648 l_all_valid_constraints.constraint_rev_id,
3649 l_all_valid_constraints.constraint_rev_id,
3650 p_responsibility_id;
3651 FETCH func_acess_count_c INTO l_func_access_count;
3652 CLOSE func_acess_count_c;
3653
3654 -- in ME type: if user can access at least two entries of this constraint,
3655 -- he violates this constraint
3656 IF l_func_access_count >= 2 THEN
3657 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
3658 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
3659 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
3660 p_user_id => p_user_id,
3661 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3662 p_constraint_type_code => l_all_valid_constraints.type_code);
3663 l_violat_existing_role := Get_Violat_Existing_Role_List (
3664 p_user_id => p_user_id,
3665 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3666 p_constraint_type_code => l_all_valid_constraints.type_code);
3667 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
3668 p_user_id => p_user_id,
3669 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3670 p_constraint_type_code => l_all_valid_constraints.type_code);
3671
3672 -- concatinate return result(Violation Details)
3673 has_violation := l_violat_new_resp;
3674 IF l_violat_existing_resp IS NOT NULL THEN
3675 IF has_violation IS NOT NULL THEN
3676 has_violation := has_violation||', ';
3677 END IF;
3678 has_violation := has_violation||l_violat_existing_resp;
3679 END IF;
3680 IF l_violat_existing_role IS NOT NULL THEN
3681 IF has_violation IS NOT NULL THEN
3682 has_violation := has_violation||', ';
3683 END IF;
3684 has_violation := has_violation||l_violat_existing_role;
3685 END IF;
3686 IF l_violat_existing_menu IS NOT NULL THEN
3687 IF has_violation IS NOT NULL THEN
3688 has_violation := has_violation||', ';
3689 END IF;
3690 has_violation := has_violation||l_violat_existing_menu;
3691 END IF;
3692
3693 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
3694 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
3695 fnd_message.set_token('CONST_DETAILS', has_violation);
3696 return fnd_message.get;
3697 END IF;
3698
3699 ELSIF 'SET' = l_all_valid_constraints.type_code THEN
3700 -- find the number of distinct constraint entries this user can access
3701 OPEN group_acess_count_c FOR l_func_set_dynamic_sql USING
3702 l_all_valid_constraints.constraint_rev_id,
3703 p_user_id,
3704 l_all_valid_constraints.constraint_rev_id,
3705 p_user_id,
3706 l_all_valid_constraints.constraint_rev_id,
3707 p_user_id,
3708 l_all_valid_constraints.constraint_rev_id,
3709 l_all_valid_constraints.constraint_rev_id,
3710 p_responsibility_id;
3711 FETCH group_acess_count_c INTO l_group_access_count;
3712 CLOSE group_acess_count_c;
3713
3714 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
3715 -- he violates this constraint
3716 IF l_group_access_count >= 2 THEN
3717 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
3718 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
3719 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
3720 p_user_id => p_user_id,
3721 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3722 p_constraint_type_code => l_all_valid_constraints.type_code);
3723 l_violat_existing_role := Get_Violat_Existing_Role_List (
3724 p_user_id => p_user_id,
3725 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3726 p_constraint_type_code => l_all_valid_constraints.type_code);
3727 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
3728 p_user_id => p_user_id,
3729 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
3730 p_constraint_type_code => l_all_valid_constraints.type_code);
3731
3732 -- concatinate return result(Violation Details)
3733 has_violation := l_violat_new_resp;
3734 IF l_violat_existing_resp IS NOT NULL THEN
3735 IF has_violation IS NOT NULL THEN
3736 has_violation := has_violation||', ';
3737 END IF;
3738 has_violation := has_violation||l_violat_existing_resp;
3739 END IF;
3740 IF l_violat_existing_role IS NOT NULL THEN
3741 IF has_violation IS NOT NULL THEN
3742 has_violation := has_violation||', ';
3743 END IF;
3744 has_violation := has_violation||l_violat_existing_role;
3745 END IF;
3746 IF l_violat_existing_menu IS NOT NULL THEN
3747 IF has_violation IS NOT NULL THEN
3748 has_violation := has_violation||', ';
3749 END IF;
3750 has_violation := has_violation||l_violat_existing_menu;
3751 END IF;
3752
3753 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
3754 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
3755 fnd_message.set_token('CONST_DETAILS', has_violation);
3756 return fnd_message.get;
3757 END IF;
3758 ELSE
3759 -- other constraint types
3760 NULL;
3761 END IF; -- end of if: constraint type_code
3762
3763 END IF; --end of if: l_valid_user_waiver_count > 0
3764
3765 END LOOP; --end of loop: c_all_valid_constraints
3766 CLOSE c_all_valid_constraints;
3767
3768 END IF; -- end of if: p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL
3769
3770 return has_violation;
3771 END;
3772
3773
3774
3775
3776 /*
3777 * cpetriuc
3778 * -------------
3779 * MENU_VIOLATES
3780 * -------------
3781 * Checks if the menu provided as argument violates any SOD (Segregation of Duties)
3782 * constraints. If a constraint is violated, the function returns an error message
3783 * containing the name of the violated constraint together with the list of functions
3784 * that define the constraint. Otherwise, the function returns 'N'.
3785 */
3786 function MENU_VIOLATES(p_menu_id NUMBER) return VARCHAR2 is
3787
3788 g_constraint_function_id_list G_NUMBER_TABLE;
3789 g_constraint_group_code_list G_NUMBER_TABLE;
3790 g_group_code_list G_NUMBER_TABLE;
3791 g_menu_function_id_list G_NUMBER_TABLE;
3792 m_constraint_details VARCHAR2(3000);
3793 m_counter NUMBER;
3794 m_failed BOOLEAN;
3795 m_function_name VARCHAR2(240);
3796 m_return_text VARCHAR2(3000);
3797
3798
3799 cursor MENU_FUNCTIONS(p_menu_id NUMBER) is
3800 select distinct FUNCTION_ID
3801 from FND_COMPILED_MENU_FUNCTIONS
3802 where MENU_ID = p_menu_id;
3803
3804 cursor CONSTRAINTS is
3805 select *
3806 from AMW_CONSTRAINTS_VL
3807 where
3808 (TYPE_CODE = 'ALL' or TYPE_CODE = 'ME' or TYPE_CODE = 'SET') and
3809 START_DATE <= sysdate and
3810 (END_DATE is null or END_DATE >= sysdate);
3811
3812 cursor CONSTRAINT_ENTRIES(p_constraint_rev_id NUMBER) is
3813 select distinct FUNCTION_ID, GROUP_CODE
3814 from AMW_CONSTRAINT_ENTRIES
3815 where
3816 CONSTRAINT_REV_ID = p_constraint_rev_id and
3817 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3818
3819 cursor CONSTRAINT_GROUP_CODES(p_constraint_rev_id NUMBER) is
3820 select distinct GROUP_CODE
3821 from AMW_CONSTRAINT_ENTRIES
3822 where
3823 CONSTRAINT_REV_ID = p_constraint_rev_id and
3824 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3825
3826
3827 begin
3828
3829 open MENU_FUNCTIONS(p_menu_id);
3830 fetch MENU_FUNCTIONS bulk collect into g_menu_function_id_list;
3831 close MENU_FUNCTIONS;
3832
3833 for constraint in CONSTRAINTS loop
3834
3835 m_failed := FALSE;
3836
3837 open CONSTRAINT_ENTRIES(constraint.CONSTRAINT_REV_ID);
3838 fetch CONSTRAINT_ENTRIES bulk collect into
3839 g_constraint_function_id_list,
3840 g_constraint_group_code_list;
3841 close CONSTRAINT_ENTRIES;
3842
3843 for i in 1 .. g_constraint_function_id_list.COUNT loop
3844
3845 select USER_FUNCTION_NAME into m_function_name
3846 from FND_FORM_FUNCTIONS_VL
3847 where FUNCTION_ID = g_constraint_function_id_list(i);
3848
3849 if i = 1 then m_constraint_details := m_function_name;
3850 else m_constraint_details := m_constraint_details || ', ' || m_function_name;
3851 end if;
3852
3853 end loop;
3854
3855 ------------------------------------
3856 -- Process a constraint of type ALL.
3857 ------------------------------------
3858 if constraint.TYPE_CODE = 'ALL' then
3859
3860 for i in 1 .. g_constraint_function_id_list.COUNT loop
3861
3862 m_failed := FALSE; -- Each constraint function must exist among the menu functions.
3863
3864 for j in 1 .. g_menu_function_id_list.COUNT loop
3865 if g_constraint_function_id_list(i) = g_menu_function_id_list(j) then
3866 m_failed := TRUE; -- This constraint function exists among the menu functions.
3867 exit;
3868 end if;
3869 end loop;
3870
3871 if m_failed = FALSE then
3872 exit; -- A constraint function has not been found among the menu functions.
3873 end if;
3874
3875 end loop;
3876
3877 end if;
3878
3879 ------------------------------------------------------
3880 -- Process a constraint of type ME (Mutual Exclusion).
3881 ------------------------------------------------------
3882 if constraint.TYPE_CODE = 'ME' then
3883
3884 m_counter := 0;
3885 for i in 1 .. g_constraint_function_id_list.COUNT loop
3886 for j in 1 .. g_menu_function_id_list.COUNT loop
3887 if g_constraint_function_id_list(i) = g_menu_function_id_list(j) then
3888 m_counter := m_counter + 1;
3889 end if;
3890 end loop;
3891 end loop;
3892
3893 if m_counter >= 2 then m_failed := TRUE; end if;
3894
3895 end if;
3896
3897 ------------------------------------
3898 -- Process a constraint of type SET.
3899 ------------------------------------
3900 if constraint.TYPE_CODE = 'SET' then
3901
3902 open CONSTRAINT_GROUP_CODES(constraint.CONSTRAINT_REV_ID);
3903 fetch CONSTRAINT_GROUP_CODES bulk collect into g_group_code_list;
3904 close CONSTRAINT_GROUP_CODES;
3905
3906 m_failed := TRUE; -- Assume the contrary.
3907
3908 for i in 1 .. g_constraint_function_id_list.COUNT loop
3909 for j in 1 .. g_menu_function_id_list.COUNT loop
3910 if g_constraint_function_id_list(i) = g_menu_function_id_list(j) then
3911 g_group_code_list(g_constraint_group_code_list(i)) := 0;
3912 end if;
3913 end loop;
3914 end loop;
3915
3916 for k in 1 .. g_group_code_list.COUNT loop
3917 if g_group_code_list(k) <> 0 then
3918 m_failed := FALSE; -- Not all groups have at least one function among the menu functions.
3919 exit;
3920 end if;
3921 end loop;
3922
3923 end if;
3924
3925 -----------------------------------------------------------------------
3926 -- If this constraint has been violated, return an appropriate message.
3927 -----------------------------------------------------------------------
3928 if m_failed = TRUE then
3929
3930 FND_MESSAGE.SET_NAME('AMW', 'AMW_SOD_VIOLATION');
3931 FND_MESSAGE.SET_TOKEN('CONSTRAINT', constraint.CONSTRAINT_NAME);
3932 FND_MESSAGE.SET_TOKEN('CONST_DETAILS', m_constraint_details);
3933 m_return_text := FND_MESSAGE.GET;
3934
3935 return m_return_text;
3936
3937 end if;
3938
3939 end loop; -- CONSTRAINTS cursor loop
3940
3941 return 'N';
3942
3943
3944 end MENU_VIOLATES;
3945
3946
3947
3948
3949 /*
3950 * cpetriuc
3951 * ----------------------
3952 * FUNCTION_VIOLATES_MENU
3953 * ----------------------
3954 * Checks if any SOD (Segregation of Duties) constraints would be violated if the
3955 * argument function would be added to the menu provided as argument. If a constraint
3956 * would be violated, the function returns an error message containing the name of the
3957 * potentially violated constraint together with the list of functions that define the
3958 * constraint. Otherwise, the function returns 'N'.
3959 */
3960 function FUNCTION_VIOLATES_MENU(p_menu_id NUMBER, p_function_id NUMBER) return VARCHAR2 is
3961
3962 g_constraint_function_id_list G_NUMBER_TABLE;
3963 g_constraint_group_code_list G_NUMBER_TABLE;
3964 g_group_code_list G_NUMBER_TABLE;
3965 g_menu_function_id_list G_NUMBER_TABLE;
3966 m_constraint_details VARCHAR2(3000);
3967 m_failed BOOLEAN;
3968 m_function_name VARCHAR2(240);
3969 m_return_text VARCHAR2(3000);
3970
3971
3972 cursor MENU_FUNCTIONS(p_menu_id NUMBER) is
3973 select distinct FUNCTION_ID
3974 from FND_COMPILED_MENU_FUNCTIONS
3975 where MENU_ID in
3976 (
3977 select MENU_ID
3978 from FND_MENU_ENTRIES
3979 start with MENU_ID = p_menu_id
3980 connect by prior MENU_ID = SUB_MENU_ID
3981 );
3982
3983 cursor CONSTRAINTS is
3984 select *
3985 from AMW_CONSTRAINTS_VL
3986 where
3987 (TYPE_CODE = 'ALL' or TYPE_CODE = 'ME' or TYPE_CODE = 'SET') and
3988 START_DATE <= sysdate and
3989 (END_DATE is null or END_DATE >= sysdate);
3990
3991 cursor CONSTRAINT_ENTRIES(p_constraint_rev_id NUMBER) is
3992 select distinct FUNCTION_ID, GROUP_CODE
3993 from AMW_CONSTRAINT_ENTRIES
3994 where
3995 CONSTRAINT_REV_ID = p_constraint_rev_id and
3996 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3997
3998 cursor CONSTRAINT_GROUP_CODES(p_constraint_rev_id NUMBER) is
3999 select distinct GROUP_CODE
4000 from AMW_CONSTRAINT_ENTRIES
4001 where
4002 CONSTRAINT_REV_ID = p_constraint_rev_id and
4003 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
4004
4005
4006 begin
4007
4008 open MENU_FUNCTIONS(p_menu_id);
4009 fetch MENU_FUNCTIONS bulk collect into g_menu_function_id_list;
4010 close MENU_FUNCTIONS;
4011
4012 for constraint in CONSTRAINTS loop
4013
4014 m_failed := FALSE;
4015
4016 open CONSTRAINT_ENTRIES(constraint.CONSTRAINT_REV_ID);
4017 fetch CONSTRAINT_ENTRIES bulk collect into
4018 g_constraint_function_id_list,
4019 g_constraint_group_code_list;
4020 close CONSTRAINT_ENTRIES;
4021
4022 for i in 1 .. g_constraint_function_id_list.COUNT loop
4023
4024 select USER_FUNCTION_NAME into m_function_name
4025 from FND_FORM_FUNCTIONS_VL
4026 where FUNCTION_ID = g_constraint_function_id_list(i);
4027
4028 if i = 1 then m_constraint_details := m_function_name;
4029 else m_constraint_details := m_constraint_details || ', ' || m_function_name;
4030 end if;
4031
4032 end loop;
4033
4034 for i in 1 .. g_constraint_function_id_list.COUNT loop
4035 if g_constraint_function_id_list(i) = p_function_id then
4036
4037 ------------------------------------
4038 -- Process a constraint of type ALL.
4039 ------------------------------------
4040 if constraint.TYPE_CODE = 'ALL' then
4041 for j in 1 .. g_constraint_function_id_list.COUNT loop
4042
4043 m_failed := FALSE; -- Each constraint function must exist among the menu functions.
4044
4045 if i <> j then
4046 for k in 1 .. g_menu_function_id_list.COUNT loop
4047 if g_constraint_function_id_list(j) = g_menu_function_id_list(k) then
4048 m_failed := TRUE; -- This constraint function exists among the menu functions.
4049 exit;
4050 end if;
4051 end loop;
4052 else m_failed := TRUE; -- If i = j, continue the loop.
4053 end if;
4054
4055 if m_failed = FALSE then
4056 exit; -- A constraint function has not been found among the menu functions.
4057 end if;
4058
4059 end loop;
4060 end if;
4061
4062 ------------------------------------------------------
4063 -- Process a constraint of type ME (Mutual Exclusion).
4064 ------------------------------------------------------
4065 if constraint.TYPE_CODE = 'ME' then
4066 for j in 1 .. g_constraint_function_id_list.COUNT loop
4067
4068 if i <> j then
4069 for k in 1 .. g_menu_function_id_list.COUNT loop
4070 if g_constraint_function_id_list(j) = g_menu_function_id_list(k) then
4071 m_failed := TRUE;
4072 exit;
4073 end if;
4074 end loop;
4075 end if;
4076
4077 if m_failed = TRUE then
4078 exit; -- At least one mutual exclusivity has been violated.
4079 end if;
4080
4081 end loop;
4082 end if;
4083
4084 ------------------------------------
4085 -- Process a constraint of type SET.
4086 ------------------------------------
4087 if constraint.TYPE_CODE = 'SET' then
4088
4089 open CONSTRAINT_GROUP_CODES(constraint.CONSTRAINT_REV_ID);
4090 fetch CONSTRAINT_GROUP_CODES bulk collect into g_group_code_list;
4091 close CONSTRAINT_GROUP_CODES;
4092
4093 g_group_code_list(g_constraint_group_code_list(i)) := 0;
4094
4095 m_failed := TRUE; -- Assume the contrary.
4096
4097 for j in 1 .. g_constraint_function_id_list.COUNT loop
4098 if i <> j then
4099 for k in 1 .. g_menu_function_id_list.COUNT loop
4100 if g_constraint_function_id_list(j) = g_menu_function_id_list(k) then
4101 g_group_code_list(g_constraint_group_code_list(j)) := 0;
4102 end if;
4103 end loop;
4104 end if;
4105 end loop;
4106
4107 for l in 1 .. g_group_code_list.COUNT loop
4108 if g_group_code_list(l) <> 0 then
4109 m_failed := FALSE; -- Not all groups have at least one function among the menu functions.
4110 exit;
4111 end if;
4112 end loop;
4113
4114 end if;
4115
4116 -----------------------------------------------------------------------
4117 -- If this constraint has been violated, return an appropriate message.
4118 -----------------------------------------------------------------------
4119 if m_failed = TRUE then
4120
4121 FND_MESSAGE.SET_NAME('AMW', 'AMW_SOD_VIOLATION');
4122 FND_MESSAGE.SET_TOKEN('CONSTRAINT', constraint.CONSTRAINT_NAME);
4123 FND_MESSAGE.SET_TOKEN('CONST_DETAILS', m_constraint_details);
4124 m_return_text := FND_MESSAGE.GET;
4125
4126 return m_return_text;
4127
4128 end if;
4129
4130 end if; -- if g_constraint_function_id_list(i) = p_function_id
4131 end loop; -- g_constraint_function_id_list loop
4132
4133 end loop; -- CONSTRAINTS cursor loop
4134
4135 return 'N';
4136
4137
4138 end FUNCTION_VIOLATES_MENU;
4139
4140 -- ----------------------------------------------------------------------
4141 END AMW_VIOLATION_PVT;
[Home] [Help]