1 package body AMW_VIOLATION_PUB as
2 /* $Header: amwvpubb.pls 120.9 2008/02/18 09:23:59 ptulasi ship $ */
3
4
5 l_curr_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_log_stmt_level number := FND_LOG.LEVEL_STATEMENT;
7 g_menu_id_list G_NUMBER_TABLE;
8 g_function_id_list G_NUMBER_TABLE;
9 g_menu_function_id_list G_NUMBER_TABLE;
10
11 -- ===============================================================
12 -- Private Function name
13 -- get_User_Id
14 --
15 -- Purpose
16 -- This function takes user_name as input and returns user_id
17 -- Params
18 -- p_user_name := user_name
19 -- Return
20 -- Notes
21 -- History
22 -- 18.02.2008 ptulasi: Created for bug 6715425
23 --
24 -- ===============================================================
25 Function get_User_Id (
26 p_user_name IN VARCHAR2
27 ) return NUMBER IS
28 p_user_id NUMBER;
29 BEGIN
30
31 SELECT user_id INTO p_user_id FROM fnd_user WHERE user_name = p_user_name;
32 RETURN p_user_id;
33 END get_User_Id;
34
35 -- ===============================================================
36 -- Private Function name
37 -- PROCESS_MENU_TREE_DOWN_FOR_MN
38 --
39 -- Purpose
40 -- Plow through the menu tree to find all the functions in it.
41 -- Params
42 -- p_menu_id := menu_id
43 -- Return
44 -- Notes
45 -- History
46 -- 07.05.2007 psomanat: Created for bug 6010908
47 --
48 -- ===============================================================
49 FUNCTION PROCESS_MENU_TREE_DOWN_FOR_MN( p_menu_id IN number )
50 RETURN boolean IS
51 L_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_MENU_TREE_DOWN_FOR_MN';
52 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
53
54 TYPE NUMBER_TABLE_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
55 TYPE MENULIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
56 TYPE MNESCURTYP IS REF CURSOR;
57
58 get_mnes_c MNESCURTYP;
59 l_mnes_dynamic_sql VARCHAR2(200) :=
60 'SELECT MENU_ID,FUNCTION_ID, SUB_MENU_ID '
61 ||' FROM '||G_AMW_MENU_ENTRIES
62 ||' WHERE menu_id = :1 ';
63
64
65 tbl_func_id NUMBER_TABLE_TYPE;
66 tbl_submnu_id NUMBER_TABLE_TYPE;
67 tbl_menu_id NUMBER_TABLE_TYPE;
68
69 menulist MENULIST_TYPE;
70 menulist_cur PLS_INTEGER;
71 menulist_size PLS_INTEGER;
72 last_index PLS_INTEGER;
73 c_max_menu_entries CONSTANT PLS_INTEGER := 10000;
74 l_sub_menu_id NUMBER;
75
76
77
78 BEGIN
79 -- Initialize menulist working list to parent menu
80 menulist_cur := 0;
81 menulist_size := 1;
82 menulist(0) := p_menu_id;
83
84 -- Continue processing until reach the end of list
85 WHILE (menulist_cur < menulist_size)
86 LOOP
87 -- Check if recursion limit exceeded
88 IF (menulist_cur > c_max_menu_entries) THEN
89 /* If the function were accessible from this menu, then we should */
90 /* have found it before getting to this point, so we are confident */
91 /* that the function is not on this menu. */
92 RETURN false;
93 END IF;
94
95 l_sub_menu_id := menulist(menulist_cur);
96
97 IF g_menu_id_list.EXISTS(l_sub_menu_id) THEN
98 menulist_cur := menulist_cur + 1;
99 ELSE
100 IF l_sub_menu_id IS NOT NULL THEN
101 g_menu_id_list(l_sub_menu_id):=l_sub_menu_id;
102
103 END IF;
104
105 OPEN get_mnes_c FOR l_mnes_dynamic_sql USING l_sub_menu_id;
106 FETCH get_mnes_c BULK COLLECT INTO tbl_menu_id,tbl_func_id, tbl_submnu_id;
107 CLOSE get_mnes_c;
108
109 -- See if we found any rows. If not set last_index to zero.
110 BEGIN
111 IF((tbl_menu_id.FIRST IS NULL) OR (tbl_menu_id.FIRST <> 1)) THEN
112 last_index := 0;
113 ELSE
114 IF (tbl_menu_id.FIRST IS NOT NULL) THEN
115 last_index := tbl_menu_id.LAST;
116 ELSE
117 last_index := 0;
118 END IF;
119 END IF;
120 EXCEPTION
121 WHEN others THEN
122 last_index := 0;
123 END;
124
125 -- Process each of the child entries fetched
126 FOR i IN 1 .. last_index LOOP
127
128 -- If this is a submenu, then add it to the end of the
129 -- working list for processing.
130 IF (tbl_submnu_id(i) IS NOT NULL) THEN
131 menulist(menulist_size) := tbl_submnu_id(i);
132 menulist_size := menulist_size + 1;
133 ELSE
134 IF NOT(g_function_id_list.EXISTS(tbl_func_id(i))) THEN
135 g_function_id_list(tbl_func_id(i)):=tbl_func_id(i);
136 g_menu_function_id_list(g_menu_function_id_list.count+1):=tbl_func_id(i);
137 END IF;
138 END IF;
139 END LOOP; -- For loop processing child entries
140
141 -- Advance to next menu on working list
142 menulist_cur := menulist_cur + 1;
143 END IF;
144 END LOOP;
145 -- We couldn't find the function anywhere, so it's not available
146 RETURN true;
147 END PROCESS_MENU_TREE_DOWN_FOR_MN;
148
149
150 -- ===============================================================
151 -- Function name
152 -- Check_Resp_Violations
153 --
154 -- Purpose
155 -- check for OICM SOD constriants that will be violated
156 -- if the user is assigned the additional responsibility
157 -- Params
158 -- p_user_id := input fnd user_id
159 -- p_responsibility_id := input fnd responsibility_id
160 -- Return
161 -- 'N' := if no SOD violation found.
162 -- 'Y' := if SOD violation exists.
163 -- The SOD violation should NOT be restricted to
164 -- only the new responsiblity.
165 -- If the existing responsibilities have any violations,
166 -- the function should return 'Y' as well.
167 --
168 -- History
169 -- 07/13/2005 tsho Create
170 -- 08/03/2005 tsho Consider User Waivers
171 -- 08/22/2005 tsho Consider only prevent(PR) constraint objective
172 -- ===============================================================
173 Function Check_Resp_Violations (
174 p_user_id IN NUMBER,
175 p_responsibility_id IN NUMBER
176 ) return VARCHAR2
177 IS
178 L_API_NAME CONSTANT VARCHAR2(30) := 'Has_Violation_Due_To_Resp';
179 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
180
181 -- return result
182 has_violation VARCHAR2(10);
183
184 -- find all valid preventive constraints
185 CURSOR c_all_valid_constraints IS
186 SELECT constraint_rev_id, type_code
187 FROM amw_constraints_b
188 WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate)
189 AND objective_code = 'PR';
190
191
192 l_all_valid_constraints c_all_valid_constraints%ROWTYPE;
193
194 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
195 l_constraint_entries_count NUMBER;
196 l_func_access_count NUMBER;
197 l_group_access_count NUMBER;
198 l_resp_access_count NUMBER;
199
200
201 CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
202 SELECT count(*)
203 FROM amw_constraint_entries
204 WHERE constraint_rev_id=l_constraint_rev_id;
205
206 TYPE refCurTyp IS REF CURSOR;
207 func_acess_count_c refCurTyp;
208 group_acess_count_c refCurTyp;
209 new_violation_count_c refCurTyp;
210 resp_acess_count_c refCurTyp;
211
212 l_vio_new_resp_sql VARCHAR2(32767);
213
214 l_func_sql VARCHAR2(32767);
215 -- in amw.e, we don't consider UMX integration and role/resp hierarchy structure
216 l_func_id_sql VARCHAR2(32767) :=
217 'select distinct function_id from ( '
218 ||' select rcd.function_id '
219 ||' from amw_role_constraint_denorm rcd '
220 ||' ,'||G_AMW_USER_ROLES||' ur '
221 ||' ,'||G_AMW_user||' u '
222 ||' where rcd.constraint_rev_id = :1 '
223 ||' and u.user_id = :2 '
224 ||' and u.user_name = ur.user_name '
225 ||' and ur.role_name = rcd.role_name '
226 ||' and ur.role_orig_system = ''UMX'' '
227 ||' UNION ALL '
228 ||' select rcd.function_id '
229 ||' from amw_role_constraint_denorm rcd '
230 ||' ,'||G_AMW_USER_ROLES||' ur '
231 ||' ,'||G_AMW_user||' u '
232 ||' where rcd.constraint_rev_id = :3 '
233 ||' and u.user_id = :4 '
234 ||' and u.user_name = ur.user_name '
235 ||' and ur.role_orig_system_id = rcd.responsibility_id '
236 ||' and ur.role_orig_system = ''FND_RESP'' '
237 ||' UNION ALL '
238 ||' select rcd.function_id '
239 ||' from amw_role_constraint_denorm rcd '
240 ||' ,'||G_AMW_GRANTS||' gra '
241 ||' ,'||G_AMW_USER||' u '
242 ||' where rcd.constraint_rev_id = :5 '
243 ||' and u.user_id = :6 '
244 ||' and u.user_name = gra.grantee_key '
245 ||' and gra.grantee_type = ''USER'' '
246 ||' and gra.menu_id = rcd.menu_id '
247 ||' and gra.instance_type = ''GLOBAL'' '
248 ||' and gra.object_id = -1 '
249 ||' UNION ALL '
250 ||' select rcd.function_id '
251 ||' from amw_role_constraint_denorm rcd '
252 ||' ,'||G_AMW_GRANTS||' gra '
253 ||' where rcd.constraint_rev_id = :7 '
254 ||' and gra.grantee_key = ''GLOBAL'' '
255 ||' and gra.grantee_type = ''GLOBAL'' '
256 ||' and gra.menu_id = rcd.menu_id '
257 ||' and gra.instance_type = ''GLOBAL'' '
258 ||' and gra.object_id = -1 '
259 ||' UNION ALL '
260 ||' select rcd.function_id '
261 ||' from amw_role_constraint_denorm rcd '
262 ||' where rcd.constraint_rev_id = :8 '
263 ||' and rcd.responsibility_id = :9 '
264 ||') ';
265
266 l_func_dynamic_sql VARCHAR2(32767) :=
267 'select count(function_id) from ( '
268 || l_func_id_sql
269 || ') ';
270
271 l_func_set_id_sql VARCHAR2(2500) :=
272 'select distinct group_code from ( '
273 ||' select rcd.group_code '
274 ||' from amw_role_constraint_denorm rcd '
275 ||' ,'||G_AMW_USER_ROLES||' ur '
276 ||' ,'||G_AMW_user||' u '
277 ||' where rcd.constraint_rev_id = :1 '
278 ||' and u.user_id = :2 '
279 ||' and u.user_name = ur.user_name '
280 ||' and ur.role_name = rcd.role_name '
281 ||' and ur.role_orig_system = ''UMX'' '
282 ||' UNION ALL '
283 ||' select rcd.group_code '
284 ||' from amw_role_constraint_denorm rcd '
285 ||' ,'||G_AMW_USER_ROLES||' ur '
286 ||' ,'||G_AMW_user||' u '
287 ||' where rcd.constraint_rev_id = :3 '
288 ||' and u.user_id = :4 '
289 ||' and u.user_name = ur.user_name '
290 ||' and ur.role_orig_system_id = rcd.responsibility_id '
291 ||' and ur.role_orig_system = ''FND_RESP'' '
292 ||' UNION ALL '
293 ||' select rcd.group_code '
294 ||' from amw_role_constraint_denorm rcd '
295 ||' ,'||G_AMW_GRANTS||' gra '
296 ||' ,'||G_AMW_USER||' u '
297 ||' where rcd.constraint_rev_id = :5 '
298 ||' and u.user_id = :6 '
299 ||' and u.user_name = gra.grantee_key '
300 ||' and gra.grantee_type = ''USER'' '
301 ||' and gra.menu_id = rcd.menu_id '
302 ||' and gra.instance_type = ''GLOBAL'' '
303 ||' and gra.object_id = -1 '
304 ||' UNION ALL '
305 ||' select rcd.group_code '
306 ||' from amw_role_constraint_denorm rcd '
307 ||' ,'||G_AMW_GRANTS||' gra '
308 ||' where rcd.constraint_rev_id = :7 '
309 ||' and gra.grantee_key = ''GLOBAL'' '
310 ||' and gra.grantee_type = ''GLOBAL'' '
311 ||' and gra.menu_id = rcd.menu_id '
312 ||' and gra.instance_type = ''GLOBAL'' '
313 ||' and gra.object_id = -1 '
314 ||' UNION ALL '
315 ||' select rcd.group_code '
316 ||' from amw_role_constraint_denorm rcd '
317 ||' where rcd.constraint_rev_id = :8 '
318 ||' and rcd.responsibility_id = :9 '
319 ||') ';
320
321 l_func_set_dynamic_sql VARCHAR2(2500) :=
322 'select count(group_code) from ( '
323 || l_func_set_id_sql
324 ||' ) ';
325
326
327 l_resp_sql VARCHAR2(32767);
328
329
330 -- all of roles including the existing ones and the newly assigned ones
331 l_resp_all_sql VARCHAR2(32767) :=
332 ' select ur.role_orig_system_id '
333 ||' from '
334 || G_AMW_USER_ROLES||' ur '
335 ||' ,'||G_AMW_user||' u '
336 ||' ,amw_constraint_entries cst '
337 ||' where u.user_id = :1 '
338 ||' and cst.constraint_rev_id = :2 '
339 ||' and u.user_name = ur.user_name '
340 ||' and ur.role_orig_system_id = cst.function_id '
341 ||' and ur.role_orig_system = ''FND_RESP'' '
342 ||' UNION '
343 ||' select cst.function_id as orig_system_id '
344 ||' from amw_constraint_entries cst '
345 ||' where cst.constraint_rev_id = :3 '
346 ||' and cst.function_id = :4 ';
347
348
349 l_resp_dynamic_sql VARCHAR2(32767) :=
350 ' select count(role_orig_system_id) from ( '
351 || l_resp_all_sql
352 ||')';
353
354
355 l_resp_set_all_sql VARCHAR2(32767) :=
356 ' select cst.group_code '
357 ||' from '
358 || G_AMW_USER_ROLES||' ur '
359 ||' ,'||G_AMW_user||' u '
360 ||' ,amw_constraint_entries cst '
361 ||' where u.user_id = :1 '
362 ||' and cst.constraint_rev_id = :2 '
363 ||' and u.user_name = ur.user_name '
364 ||' and ur.role_orig_system_id = cst.function_id '
365 ||' and ur.role_orig_system = ''FND_RESP'' '
366 ||' UNION '
367 ||' select cst.group_code '
368 ||' from amw_constraint_entries cst '
369 ||' where cst.constraint_rev_id = :3 '
370 ||' and cst.function_id = :4 ';
371
372 l_resp_set_dynamic_sql VARCHAR2(32767) :=
373 ' select count(group_code) from ( '
374 || l_resp_set_all_sql
375 ||')';
376
377
378 -- get valid user waiver
379 l_valid_user_waiver_count NUMBER;
380 CURSOR c_valid_user_waivers (l_constraint_rev_id IN NUMBER, l_user_id IN NUMBER) IS
381 SELECT count(*)
382 FROM amw_constraint_waivers_vl
383 WHERE constraint_rev_id = l_constraint_rev_id
384 AND object_type = 'USER'
385 AND PK1 = l_user_id
386 AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
387
388 l_cst_new_violation_sql VARCHAR2(5000) ;
389
390 BEGIN
391
392 IF( l_log_stmt_level >= l_curr_log_level ) THEN
393 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Check_Resp_Violations Start');
394 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','p_user_id = '|| p_user_id );
395 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','p_responsibility_id = '|| p_responsibility_id);
396 END IF;
397
398 -- default to 'N', which means user doesn't have violations
399 has_violation := 'N';
400 l_valid_user_waiver_count := 0;
401
402 IF (p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL) THEN
403 -- check all valid constraints
404 OPEN c_all_valid_constraints;
405 LOOP
406 FETCH c_all_valid_constraints INTO l_all_valid_constraints;
407 EXIT WHEN c_all_valid_constraints%NOTFOUND;
408
409 -- check if this user is waived (due to User Waiver) from this constraint
410 OPEN c_valid_user_waivers(l_all_valid_constraints.constraint_rev_id, p_user_id);
411 FETCH c_valid_user_waivers INTO l_valid_user_waiver_count;
412 CLOSE c_valid_user_waivers;
413
414 IF l_valid_user_waiver_count <= 0 THEN
415
416 IF 'ALL' = l_all_valid_constraints.type_code THEN
417
418 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
419 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
420 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
421 CLOSE c_constraint_entries_count;
422
423 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
424 l_all_valid_constraints.constraint_rev_id,
425 p_user_id,
426 l_all_valid_constraints.constraint_rev_id,
427 p_user_id,
428 l_all_valid_constraints.constraint_rev_id,
429 p_user_id,
430 l_all_valid_constraints.constraint_rev_id,
431 l_all_valid_constraints.constraint_rev_id,
432 p_responsibility_id;
433 FETCH func_acess_count_c INTO l_func_access_count;
434 CLOSE func_acess_count_c;
435
436 -- in ALL type: if user can access to all entries of this constraint,
437 -- he violates this constraint
438 IF l_func_access_count = l_constraint_entries_count THEN
439
440 -- Check to see if the fuction enteries in the constraint is same
441 -- as the functions the user can access due to the assigning of
442 -- this role
443 l_func_sql :='select count(function_id)'
444 ||'from ('
445 ||'select function_id from ( '
446 || l_func_id_sql
447 ||') '
448 ||' MINUS '
449 ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :10'
450 ||')';
451
452
453
454 OPEN func_acess_count_c FOR l_func_sql USING
455 l_all_valid_constraints.constraint_rev_id,
456 p_user_id,
457 l_all_valid_constraints.constraint_rev_id,
458 p_user_id,
459 l_all_valid_constraints.constraint_rev_id,
460 p_user_id,
461 l_all_valid_constraints.constraint_rev_id,
462 l_all_valid_constraints.constraint_rev_id,
463 p_responsibility_id,
464 l_all_valid_constraints.constraint_rev_id;
465 FETCH func_acess_count_c INTO l_func_access_count;
466 CLOSE func_acess_count_c;
467
468 IF l_func_access_count = 0 THEN
469 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
470 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
471 has_violation := 'Y';
472 return has_violation;
473 END IF;
474 END IF;
475 ELSIF 'ME' = l_all_valid_constraints.type_code THEN
476
477 -- find the number of distinct constraint entries this user can access
478 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
479 l_all_valid_constraints.constraint_rev_id,
480 p_user_id,
481 l_all_valid_constraints.constraint_rev_id,
482 p_user_id,
483 l_all_valid_constraints.constraint_rev_id,
484 p_user_id,
485 l_all_valid_constraints.constraint_rev_id,
486 l_all_valid_constraints.constraint_rev_id,
487 p_responsibility_id;
488 FETCH func_acess_count_c INTO l_func_access_count;
489 CLOSE func_acess_count_c;
490
491 -- in ME type: if user can access at least two entries of this constraint,
492 -- he violates this constraint
493 IF l_func_access_count >= 2 THEN
494 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
495 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
496 has_violation := 'Y';
497 return has_violation;
498 END IF;
499 ELSIF 'SET' = l_all_valid_constraints.type_code THEN
500
501 -- find the number of distinct constraint entries this user can access
502 OPEN group_acess_count_c FOR l_func_set_dynamic_sql USING
503 l_all_valid_constraints.constraint_rev_id,
504 p_user_id,
505 l_all_valid_constraints.constraint_rev_id,
506 p_user_id,
507 l_all_valid_constraints.constraint_rev_id,
508 p_user_id,
509 l_all_valid_constraints.constraint_rev_id,
510 l_all_valid_constraints.constraint_rev_id,
511 p_responsibility_id;
512 FETCH group_acess_count_c INTO l_group_access_count;
513 CLOSE group_acess_count_c;
514
515 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
516 -- he violates this constraint
517 IF l_group_access_count >= 2 THEN
518 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
519 -- FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
520 has_violation := 'Y';
521 return has_violation;
522 END IF;
523 ELSIF 'RESPALL' = l_all_valid_constraints.type_code THEN
524 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
525 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
526 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
527 CLOSE c_constraint_entries_count;
528
529 OPEN resp_acess_count_c FOR l_resp_dynamic_sql USING
530 p_user_id,
531 l_all_valid_constraints.constraint_rev_id,
532 l_all_valid_constraints.constraint_rev_id,
533 p_responsibility_id;
534 FETCH resp_acess_count_c INTO l_resp_access_count;
535 CLOSE resp_acess_count_c;
536
537 -- in ALL type: if user can access to all entries of this constraint,
538 -- he violates this constraint
539 IF l_resp_access_count = l_constraint_entries_count THEN
540
541 -- Check to see if the fuction enteries in the constraint is same
542 -- as the functions the user can access due to the assigning of
543 -- this role
544
545 l_resp_sql := 'select count(distinct role_orig_system_id)'
546 ||' from ('
547 || l_resp_all_sql
548 ||' MINUS '
549 ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :5'
550 ||')';
551
552
553 OPEN resp_acess_count_c FOR l_resp_sql USING
554 p_user_id,
555 l_all_valid_constraints.constraint_rev_id,
556 l_all_valid_constraints.constraint_rev_id,
557 p_responsibility_id,
558 l_all_valid_constraints.constraint_rev_id;
559 FETCH resp_acess_count_c INTO l_resp_access_count;
560 CLOSE resp_acess_count_c;
561
562 IF l_resp_access_count = 0 THEN
563 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
564 FND_FILE.put_line(fnd_file.log, '----fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
565 has_violation := 'Y';
566 return has_violation;
567 END IF;
568 END IF;
569
570 ELSIF 'RESPME' = l_all_valid_constraints.type_code THEN
571
572
573 -- find the number of distinct constraint entries this user can access
574 OPEN resp_acess_count_c FOR l_resp_dynamic_sql USING
575 p_user_id,
576 l_all_valid_constraints.constraint_rev_id,
577 l_all_valid_constraints.constraint_rev_id,
578 p_responsibility_id;
579 FETCH resp_acess_count_c INTO l_resp_access_count;
580 CLOSE resp_acess_count_c;
581
582
583 IF( l_log_stmt_level >= l_curr_log_level ) THEN
584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','l_resp_access_count = '|| l_resp_access_count );
585 END IF;
586
587 -- in ME type: if user can access at least two entries of this constraint,
588 -- he violates this constraint
589 IF l_resp_access_count >= 2 THEN
590 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
591 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
592 has_violation := 'Y';
593
594 IF( l_log_stmt_level >= l_curr_log_level ) THEN
595 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','has_violation = '|| has_violation );
596 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Check_Resp_Violations End');
597 END IF;
598
599 return has_violation;
600 END IF;
601
602 ELSIF 'RESPSET' = l_all_valid_constraints.type_code THEN
603
604
605 -- find the number of distinct constraint entries this user can access
606
607 OPEN resp_acess_count_c FOR l_resp_set_dynamic_sql USING
608 p_user_id,
609 l_all_valid_constraints.constraint_rev_id,
610 l_all_valid_constraints.constraint_rev_id,
611 p_responsibility_id;
612 FETCH resp_acess_count_c INTO l_resp_access_count;
613 CLOSE resp_acess_count_c;
614
615 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
616 -- he violates this constraint
617 IF l_resp_access_count >= 2 THEN
618 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
619 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
620 has_violation := 'Y';
621 return has_violation;
622 END IF;
623 ELSE
624 -- other constraint types
625 NULL;
626 END IF; -- end of if: constraint type_code
627
628 END IF; -- end of if: l_valid_user_waiver_count <= 0
629
630 END LOOP; --end of loop: c_all_valid_constraints
631 CLOSE c_all_valid_constraints;
632
633 END IF; -- end of if: p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL
634
635 IF( l_log_stmt_level >= l_curr_log_level ) THEN
636 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Last has_violation = '|| has_violation );
637 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Check_Resp_Violations End');
638 END IF;
639
640 return has_violation;
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 IF( l_log_stmt_level >= l_curr_log_level ) THEN
645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
646 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Check_Resp_Violations End');
647 END IF;
648 RAISE;
649
650 END Check_Resp_Violations;
651
652
653
654 -- ===============================================================
655 -- Function name
656 -- User_Resp_Violation_Details
657 --
658 -- Purpose
659 -- check for OICM SOD constriants that will be violated
660 -- if the user is assigned the additional responsibility
661 -- Params
662 -- p_user_id := input fnd user_id
663 -- p_responsibility_id := input fnd responsibility_id
664 -- Return
665 -- 'N' := if no SOD violation found.
666 -- 'ConstraintName:Resp_name1;Resp_name2;...' := if SOD violation exists.
667 -- The SOD violation should NOT be restricted to
668 -- only the new responsiblity.
669 -- If the existing responsibilities have any violations,
670 -- the function should return 'Y' as well.
671 --
672 -- History
673 -- 08/01/2005 tsho Create
674 -- 08/03/2005 tsho Consider User Waivers
675 -- 08/22/2005 tsho Consider only prevent(PR) constraint objective
676 -- ===============================================================
677 Function User_Resp_Violation_Details (
678 p_user_id IN NUMBER,
679 p_responsibility_id IN NUMBER
680 ) return VARCHAR2
681 IS
682
683 L_API_NAME CONSTANT VARCHAR2(30) := 'Violation_Detail_Due_To_Resp';
684 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
685
686 -- return result
687 has_violation VARCHAR2(32767);
688 l_violat_existing_resp VARCHAR2(10000);
689 l_violat_existing_role VARCHAR2(10000);
690 l_violat_existing_menu VARCHAR2(10000);
691 l_violat_new_resp VARCHAR2(10000);
692 l_violat_new_func VARCHAR2(10000);
693
694 l_resp_access_count NUMBER;
695
696 l_new_func_table JTF_VARCHAR2_TABLE_400;
697
698 -- 05.23.2006 dliao: consider only Prevent Constraint Objective
699 CURSOR c_all_valid_constraints IS
700 SELECT constraint_rev_id, type_code, constraint_name
701 FROM amw_constraints_vl
702 WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate)
703 and objective_code = 'PR';
704 l_all_valid_constraints c_all_valid_constraints%ROWTYPE;
705
706 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
707 l_constraint_entries_count NUMBER;
708 l_func_access_count NUMBER;
709 l_group_access_count NUMBER;
710 CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
711 SELECT count(*)
712 FROM amw_constraint_entries
713 WHERE constraint_rev_id=l_constraint_rev_id;
714
715
716 TYPE refCurTyp IS REF CURSOR;
717 func_acess_count_c refCurTyp;
718 group_acess_count_c refCurTyp;
719 resp_acess_count_c refCurTyp;
720
721 l_func_dynamic_sql VARCHAR2(2500) :=
722 'select count(distinct function_id) from ( '
723 ||' select rcd.function_id '
724 ||' from amw_role_constraint_denorm rcd '
725 ||' ,'||G_AMW_USER_ROLES||' ur '
726 ||' ,'||G_AMW_user||' u '
727 ||' where rcd.constraint_rev_id = :1 '
728 ||' and u.user_id = :2 '
729 ||' and u.user_name = ur.user_name '
730 ||' and ur.role_name = rcd.role_name '
731 ||' and ur.role_orig_system = ''UMX'' '
732 ||' UNION ALL '
733 ||' select rcd.function_id '
734 ||' from amw_role_constraint_denorm rcd '
735 ||' ,'||G_AMW_USER_ROLES||' ur '
736 ||' ,'||G_AMW_user||' u '
737 ||' where rcd.constraint_rev_id = :3 '
738 ||' and u.user_id = :4 '
739 ||' and u.user_name = ur.user_name '
740 ||' and ur.role_orig_system_id = rcd.responsibility_id '
741 ||' and ur.role_orig_system = ''FND_RESP'' '
742 ||' UNION ALL '
743 ||' select rcd.function_id '
744 ||' from amw_role_constraint_denorm rcd '
745 ||' ,'||G_AMW_GRANTS||' gra '
746 ||' ,'||G_AMW_USER||' u '
747 ||' where rcd.constraint_rev_id = :5 '
748 ||' and u.user_id = :6 '
749 ||' and u.user_name = gra.grantee_key '
750 ||' and gra.grantee_type = ''USER'' '
751 ||' and gra.menu_id = rcd.menu_id '
752 ||' and gra.instance_type = ''GLOBAL'' '
753 ||' and gra.object_id = -1 '
754 ||' UNION ALL '
755 ||' select rcd.function_id '
756 ||' from amw_role_constraint_denorm rcd '
757 ||' ,'||G_AMW_GRANTS||' gra '
758 ||' where rcd.constraint_rev_id = :7 '
759 ||' and gra.grantee_key = ''GLOBAL'' '
760 ||' and gra.grantee_type = ''GLOBAL'' '
761 ||' and gra.menu_id = rcd.menu_id '
762 ||' and gra.instance_type = ''GLOBAL'' '
763 ||' and gra.object_id = -1 '
764 ||' UNION ALL '
765 ||' select rcd.function_id '
766 ||' from amw_role_constraint_denorm rcd '
767 ||' where rcd.constraint_rev_id = :8 '
768 ||' and rcd.responsibility_id = :9 '
769 ||') ';
770
771 l_func_set_dynamic_sql VARCHAR2(2500) :=
772 'select count(distinct group_code) from ( '
773 ||' select rcd.group_code '
774 ||' from amw_role_constraint_denorm rcd '
775 ||' ,'||G_AMW_USER_ROLES||' ur '
776 ||' ,'||G_AMW_user||' u '
777 ||' where rcd.constraint_rev_id = :1 '
778 ||' and u.user_id = :2 '
779 ||' and u.user_name = ur.user_name '
780 ||' and ur.role_name = rcd.role_name '
781 ||' and ur.role_orig_system = ''UMX'' '
782 ||' UNION ALL '
783 ||' select rcd.group_code '
784 ||' from amw_role_constraint_denorm rcd '
785 ||' ,'||G_AMW_USER_ROLES||' ur '
786 ||' ,'||G_AMW_user||' u '
787 ||' where rcd.constraint_rev_id = :3 '
788 ||' and u.user_id = :4 '
789 ||' and u.user_name = ur.user_name '
790 ||' and ur.role_orig_system_id = rcd.responsibility_id '
791 ||' and ur.role_orig_system = ''FND_RESP'' '
792 ||' UNION ALL '
793 ||' select rcd.group_code '
794 ||' from amw_role_constraint_denorm rcd '
795 ||' ,'||G_AMW_GRANTS||' gra '
796 ||' ,'||G_AMW_USER||' u '
797 ||' where rcd.constraint_rev_id = :5 '
798 ||' and u.user_id = :6 '
799 ||' and u.user_name = gra.grantee_key '
800 ||' and gra.grantee_type = ''USER'' '
801 ||' and gra.menu_id = rcd.menu_id '
802 ||' and gra.instance_type = ''GLOBAL'' '
803 ||' and gra.object_id = -1 '
804 ||' UNION ALL '
805 ||' select rcd.group_code '
806 ||' from amw_role_constraint_denorm rcd '
807 ||' ,'||G_AMW_GRANTS||' gra '
808 ||' where rcd.constraint_rev_id = :7 '
809 ||' and gra.grantee_key = ''GLOBAL'' '
810 ||' and gra.grantee_type = ''GLOBAL'' '
811 ||' and gra.menu_id = rcd.menu_id '
812 ||' and gra.instance_type = ''GLOBAL'' '
813 ||' and gra.object_id = -1 '
814 ||' UNION ALL '
815 ||' select rcd.group_code '
816 ||' from amw_role_constraint_denorm rcd '
817 ||' where rcd.constraint_rev_id = :8 '
818 ||' and rcd.responsibility_id = :9 '
819 ||') ';
820
821 -- get the name of the new respsonsibility which this user intends to have
822 new_resp_c refCurTyp;
823 l_new_resp_dynamic_sql VARCHAR2(500) :=
824 'select resp.responsibility_name '
825 ||' from amw_constraint_entries rcd '
826 ||' ,'||G_AMW_RESPONSIBILITY_VL||' resp '
827 ||' where rcd.constraint_rev_id = :1 and resp.responsibility_id = :2 '
828 ||' and rcd.function_id = resp.responsibility_id ';
829
830 new_func_c refCurTyp;
831 l_new_func_dynamic_sql VARCHAR2(500) :=
832 'select func.user_function_name '
833 ||' from amw_constraint_entries rcd '
834 ||' ,'|| G_AMW_FORM_FUNCTIONS_VL ||' func '
835 ||' where rcd.constraint_rev_id = :1 '
836 ||' and rcd.function_id = func.function_id ';
837
838 -- get valid user waiver
839 l_valid_user_waiver_count NUMBER;
840 CURSOR c_valid_user_waivers (l_constraint_rev_id IN NUMBER, l_user_id IN NUMBER) IS
841 SELECT count(*)
842 FROM amw_constraint_waivers_vl
843 WHERE constraint_rev_id = l_constraint_rev_id
844 AND object_type = 'USER'
845 AND PK1 = l_user_id
846 AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
847
848 l_resp_sql VARCHAR2(32767);
849
850
851 -- all of roles including the existing ones and the newly assigned ones
852 l_resp_all_sql VARCHAR2(32767) :=
853 ' select ur.role_orig_system_id '
854 ||' from '
855 || G_AMW_USER_ROLES||' ur '
856 ||' ,'||G_AMW_user||' u '
857 ||' ,amw_constraint_entries cst '
858 ||' where u.user_id = :1 '
859 ||' and cst.constraint_rev_id = :2 '
860 ||' and u.user_name = ur.user_name '
861 ||' and ur.role_orig_system_id = cst.function_id '
862 ||' and ur.role_orig_system = ''FND_RESP'' '
863 ||' UNION '
864 ||' select cst.function_id as orig_system_id '
865 ||' from amw_constraint_entries cst '
866 ||' where cst.constraint_rev_id = :3 '
867 ||' and cst.function_id = :4 ';
868
869
870 l_resp_dynamic_sql VARCHAR2(32767) :=
871 ' select count(role_orig_system_id) from ( '
872 || l_resp_all_sql
873 ||')';
874
875
876 l_resp_set_all_sql VARCHAR2(32767) :=
877 ' select cst.group_code '
878 ||' from '
879 || G_AMW_USER_ROLES||' ur '
880 ||' ,'||G_AMW_user||' u '
881 ||' ,amw_constraint_entries cst '
882 ||' where u.user_id = :1 '
883 ||' and cst.constraint_rev_id = :2 '
884 ||' and u.user_name = ur.user_name '
885 ||' and ur.role_orig_system_id = cst.function_id '
886 ||' and ur.role_orig_system = ''FND_RESP'' '
887 ||' UNION '
888 ||' select cst.group_code '
889 ||' from amw_constraint_entries cst '
890 ||' where cst.constraint_rev_id = :3 '
891 ||' and cst.function_id = :4 ';
892
893 l_resp_set_dynamic_sql VARCHAR2(32767) :=
894 ' select count(group_code) from ( '
895 || l_resp_set_all_sql
896 ||')';
897
898
899 BEGIN
900 IF( l_log_stmt_level >= l_curr_log_level ) THEN
901 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','User_Resp_Violation_Details Start');
902 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','p_user_id = '|| p_user_id );
903 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','p_responsibility_id = '|| p_responsibility_id);
904 END IF;
905
906 -- default to 'N', which means user doesn't have violations
907 has_violation := 'N';
908 l_violat_existing_resp := NULL;
909 l_violat_existing_role := NULL;
910 l_violat_existing_menu := NULL;
911 l_violat_new_resp := NULL;
912 l_violat_new_func := NULL;
913 l_valid_user_waiver_count := 0;
914
915 IF (p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL) THEN
916 -- check all valid constraints
917 OPEN c_all_valid_constraints;
918 LOOP
919 FETCH c_all_valid_constraints INTO l_all_valid_constraints;
920 EXIT WHEN c_all_valid_constraints%NOTFOUND;
921
922 -- check if this user is waived (due to User Waiver) from this constraint
923 OPEN c_valid_user_waivers(l_all_valid_constraints.constraint_rev_id, p_user_id);
924 FETCH c_valid_user_waivers INTO l_valid_user_waiver_count;
925 CLOSE c_valid_user_waivers;
926
927
928 -- IF l_valid_user_waiver_count <= 0 THEN
929 IF l_valid_user_waiver_count <= 0 THEN
930
931 -- get the name of the new responsibility if combining this will results in violation against this constraint
932 OPEN new_resp_c FOR l_new_resp_dynamic_sql USING
933 l_all_valid_constraints.constraint_rev_id
934 ,p_responsibility_id;
935 FETCH new_resp_c INTO l_violat_new_resp;
936 CLOSE new_resp_c;
937
938 -- get the name of the new function if combining this will results in violation against this constraint
939 OPEN new_func_c FOR l_new_func_dynamic_sql USING
940 l_all_valid_constraints.constraint_rev_id;
941 FETCH new_func_c BULK COLLECT INTO l_new_func_table;
942 CLOSE new_func_c;
943
944 IF l_new_func_table IS NOT NULL AND l_new_func_table.FIRST IS NOT NULL THEN
945 l_violat_new_func := l_new_func_table(1);
946 FOR i in 2 .. l_new_func_table.COUNT
947 LOOP
948 l_violat_new_func := l_violat_new_func ||', '||l_new_func_table(i);
949 END LOOP;
950 END IF; -- end of if: l_new_func_table IS NOT NULL
951
952 IF 'ALL' = l_all_valid_constraints.type_code THEN
953 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
954 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
955 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
956 CLOSE c_constraint_entries_count;
957
958 -- find the number of distinct constraint entries this user can access
959 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
960 l_all_valid_constraints.constraint_rev_id,
961 p_user_id,
962 l_all_valid_constraints.constraint_rev_id,
963 p_user_id,
964 l_all_valid_constraints.constraint_rev_id,
965 p_user_id,
966 l_all_valid_constraints.constraint_rev_id,
967 l_all_valid_constraints.constraint_rev_id,
968 p_responsibility_id;
969 FETCH func_acess_count_c INTO l_func_access_count;
970 CLOSE func_acess_count_c;
971
972 -- in ALL type: if user can access to all entries of this constraint,
973 -- he violates this constraint
974 IF l_func_access_count = l_constraint_entries_count THEN
975 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
976 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
977 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
978 p_user_id => p_user_id,
979 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
980 p_constraint_type_code => l_all_valid_constraints.type_code);
981 l_violat_existing_role := Get_Violat_Existing_Role_List (
982 p_user_id => p_user_id,
983 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
984 p_constraint_type_code => l_all_valid_constraints.type_code);
985 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
986 p_user_id => p_user_id,
987 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
988 p_constraint_type_code => l_all_valid_constraints.type_code);
989
990 -- concatinate return result(Violation Details)
991 has_violation := substrb(l_violat_new_func, 1, 4000);
992 IF l_violat_existing_resp IS NOT NULL THEN
993 IF has_violation IS NOT NULL THEN
994 has_violation := substrb((has_violation||', '), 1, 4000);
995 END IF;
996 has_violation := substrb((has_violation||l_violat_existing_resp), 1, 4000);
997 END IF;
998 IF l_violat_existing_role IS NOT NULL THEN
999 IF has_violation IS NOT NULL THEN
1000 has_violation := substrb((has_violation||', '), 1, 4000);
1001 END IF;
1002 has_violation := substrb((has_violation||l_violat_existing_role), 1, 4000);
1003 END IF;
1004 IF l_violat_existing_menu IS NOT NULL THEN
1005 IF has_violation IS NOT NULL THEN
1006 has_violation := substrb((has_violation||', '), 1, 4000);
1007 END IF;
1008 has_violation := substrb((has_violation||l_violat_existing_menu), 1, 4000);
1009 END IF;
1010
1011 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
1012 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
1013 fnd_message.set_token('CONST_DETAILS', has_violation);
1014 return substrb((FND_MESSAGE.GET), 1, 2000);
1015 END IF;
1016
1017 ELSIF 'ME' = l_all_valid_constraints.type_code THEN
1018 -- find the number of distinct constraint entries this user can access
1019 OPEN func_acess_count_c FOR l_func_dynamic_sql USING
1020 l_all_valid_constraints.constraint_rev_id,
1021 p_user_id,
1022 l_all_valid_constraints.constraint_rev_id,
1023 p_user_id,
1024 l_all_valid_constraints.constraint_rev_id,
1025 p_user_id,
1026 l_all_valid_constraints.constraint_rev_id,
1027 l_all_valid_constraints.constraint_rev_id,
1028 p_responsibility_id;
1029 FETCH func_acess_count_c INTO l_func_access_count;
1030 CLOSE func_acess_count_c;
1031
1032 -- in ME type: if user can access at least two entries of this constraint,
1033 -- he violates this constraint
1034 IF l_func_access_count >= 2 THEN
1035 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1036 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - ME = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1037 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
1038 p_user_id => p_user_id,
1039 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1040 p_constraint_type_code => l_all_valid_constraints.type_code);
1041 l_violat_existing_role := Get_Violat_Existing_Role_List (
1042 p_user_id => p_user_id,
1043 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1044 p_constraint_type_code => l_all_valid_constraints.type_code);
1045 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
1046 p_user_id => p_user_id,
1047 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1048 p_constraint_type_code => l_all_valid_constraints.type_code);
1049
1050 -- concatinate return result(Violation Details)
1051 has_violation := substrb(l_violat_new_func, 1, 4000);
1052 IF l_violat_existing_resp IS NOT NULL THEN
1053 IF has_violation IS NOT NULL THEN
1054 has_violation := substrb((has_violation||', '), 1, 4000);
1055 END IF;
1056 has_violation := substrb((has_violation||l_violat_existing_resp), 1, 4000);
1057 END IF;
1058 IF l_violat_existing_role IS NOT NULL THEN
1059 IF has_violation IS NOT NULL THEN
1060 has_violation := substrb((has_violation||', '), 1, 4000);
1061 END IF;
1062 has_violation := substrb((has_violation||l_violat_existing_role), 1, 4000);
1063 END IF;
1064 IF l_violat_existing_menu IS NOT NULL THEN
1065 IF has_violation IS NOT NULL THEN
1066 has_violation := has_violation||', ';
1067 END IF;
1068 has_violation := substrb((has_violation||l_violat_existing_menu), 1, 4000);
1069 END IF;
1070
1071 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
1072 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
1073 fnd_message.set_token('CONST_DETAILS', has_violation);
1074 return substrb((FND_MESSAGE.GET), 1, 2000);
1075 END IF;
1076
1077 ELSIF 'SET' = l_all_valid_constraints.type_code THEN
1078 -- find the number of distinct constraint entries this user can access
1079 OPEN group_acess_count_c FOR l_func_set_dynamic_sql USING
1080 l_all_valid_constraints.constraint_rev_id,
1081 p_user_id,
1082 l_all_valid_constraints.constraint_rev_id,
1083 p_user_id,
1084 l_all_valid_constraints.constraint_rev_id,
1085 p_user_id,
1086 l_all_valid_constraints.constraint_rev_id,
1087 l_all_valid_constraints.constraint_rev_id,
1088 p_responsibility_id;
1089 FETCH group_acess_count_c INTO l_group_access_count;
1090 CLOSE group_acess_count_c;
1091
1092 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
1093 -- he violates this constraint
1094 IF l_group_access_count >= 2 THEN
1095 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1096 FND_FILE.put_line(fnd_file.log,'------------ fail on constraint - SET = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1097 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
1098 p_user_id => p_user_id,
1099 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1100 p_constraint_type_code => l_all_valid_constraints.type_code);
1101 l_violat_existing_role := Get_Violat_Existing_Role_List (
1102 p_user_id => p_user_id,
1103 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1104 p_constraint_type_code => l_all_valid_constraints.type_code);
1105 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
1106 p_user_id => p_user_id,
1107 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1108 p_constraint_type_code => l_all_valid_constraints.type_code);
1109
1110 -- concatinate return result(Violation Details)
1111 has_violation := substrb(l_violat_new_func, 1, 4000);
1112 IF l_violat_existing_resp IS NOT NULL THEN
1113 IF has_violation IS NOT NULL THEN
1114 has_violation := has_violation||', ';
1115 END IF;
1116 has_violation := substrb((has_violation||l_violat_existing_resp), 1, 4000);
1117 END IF;
1118 IF l_violat_existing_role IS NOT NULL THEN
1119 IF has_violation IS NOT NULL THEN
1120 has_violation := has_violation||', ';
1121 END IF;
1122 has_violation := substrb((has_violation||l_violat_existing_role), 1, 4000);
1123 END IF;
1124 IF l_violat_existing_menu IS NOT NULL THEN
1125 IF has_violation IS NOT NULL THEN
1126 has_violation := has_violation||', ';
1127 END IF;
1128 has_violation := substrb((has_violation||l_violat_existing_menu), 1, 4000);
1129 END IF;
1130
1131 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
1132 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
1133 fnd_message.set_token('CONST_DETAILS', has_violation);
1134 return substrb((FND_MESSAGE.GET), 1, 2000);
1135 END IF;
1136
1137 ELSIF 'RESPALL' = l_all_valid_constraints.type_code THEN
1138 -- find the number of constraint entries(incompatible functions) by specified constraint_rev_id
1139 OPEN c_constraint_entries_count (l_all_valid_constraints.constraint_rev_id);
1140 FETCH c_constraint_entries_count INTO l_constraint_entries_count;
1141 CLOSE c_constraint_entries_count;
1142
1143
1144
1145 OPEN resp_acess_count_c FOR l_resp_dynamic_sql USING
1146 p_user_id,
1147 l_all_valid_constraints.constraint_rev_id,
1148 l_all_valid_constraints.constraint_rev_id,
1149 p_responsibility_id;
1150 FETCH resp_acess_count_c INTO l_resp_access_count;
1151 CLOSE resp_acess_count_c;
1152
1153 -- in ALL type: if user can access to all entries of this constraint,
1154 -- he violates this constraint
1155 IF l_resp_access_count = l_constraint_entries_count THEN
1156
1157 -- Check to see if the fuction enteries in the constraint is same
1158 -- as the functions the user can access due to the assigning of
1159 -- this role
1160
1161 l_resp_sql := 'select count(distinct role_orig_system_id)'
1162 ||' from ('
1163 || l_resp_all_sql
1164 ||' MINUS '
1165 ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :5'
1166 ||')';
1167
1168
1169 OPEN resp_acess_count_c FOR l_resp_sql USING
1170 p_user_id,
1171 l_all_valid_constraints.constraint_rev_id,
1172 l_all_valid_constraints.constraint_rev_id,
1173 p_responsibility_id,
1174 l_all_valid_constraints.constraint_rev_id;
1175 FETCH resp_acess_count_c INTO l_resp_access_count;
1176 CLOSE resp_acess_count_c;
1177
1178 IF l_resp_access_count = 0 THEN
1179 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1180 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1181 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
1182 p_user_id => p_user_id,
1183 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1184 p_constraint_type_code => l_all_valid_constraints.type_code);
1185 l_violat_existing_role := Get_Violat_Existing_Role_List (
1186 p_user_id => p_user_id,
1187 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1188 p_constraint_type_code => l_all_valid_constraints.type_code);
1189 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
1190 p_user_id => p_user_id,
1191 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1192 p_constraint_type_code => l_all_valid_constraints.type_code);
1193
1194 -- concatinate return result(Violation Details)
1195 has_violation := l_violat_new_resp;
1196 IF l_violat_existing_resp IS NOT NULL THEN
1197 IF has_violation IS NOT NULL THEN
1198 has_violation := has_violation||', ';
1199 END IF;
1200 has_violation := substrb((has_violation||l_violat_existing_resp), 1, 4000);
1201 END IF;
1202 IF l_violat_existing_role IS NOT NULL THEN
1203 IF has_violation IS NOT NULL THEN
1204 has_violation := has_violation||', ';
1205 END IF;
1206 has_violation := substrb((has_violation||l_violat_existing_role), 1, 4000);
1207 END IF;
1208 IF l_violat_existing_menu IS NOT NULL THEN
1209 IF has_violation IS NOT NULL THEN
1210 has_violation := has_violation||', ';
1211 END IF;
1212 has_violation := substrb((has_violation||l_violat_existing_menu), 1, 4000);
1213 END IF;
1214
1215 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
1216 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
1217 fnd_message.set_token('CONST_DETAILS', has_violation);
1218 return substrb((FND_MESSAGE.GET), 1, 2000);
1219 END IF; -- end of l_resp_access_count = 0
1220 END IF; -- l_resp_access_count = l_constraint_entries_count
1221
1222 ELSIF 'RESPME' = l_all_valid_constraints.type_code THEN
1223
1224
1225 -- find the number of distinct constraint entries this user can access
1226 OPEN resp_acess_count_c FOR l_resp_dynamic_sql USING
1227 p_user_id,
1228 l_all_valid_constraints.constraint_rev_id,
1229 l_all_valid_constraints.constraint_rev_id,
1230 p_responsibility_id;
1231 FETCH resp_acess_count_c INTO l_resp_access_count;
1232 CLOSE resp_acess_count_c;
1233
1234 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1235 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','l_resp_access_count = '|| l_resp_access_count );
1236 END IF;
1237
1238
1239 -- in ME type: if user can access at least two entries of this constraint,
1240 -- he violates this constraint
1241 IF l_resp_access_count >= 2 THEN
1242 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1243 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1244 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
1245 p_user_id => p_user_id,
1246 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1247 p_constraint_type_code => l_all_valid_constraints.type_code);
1248 l_violat_existing_role := Get_Violat_Existing_Role_List (
1249 p_user_id => p_user_id,
1250 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1251 p_constraint_type_code => l_all_valid_constraints.type_code);
1252 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
1253 p_user_id => p_user_id,
1254 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1255 p_constraint_type_code => l_all_valid_constraints.type_code);
1256
1257 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1258 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','l_violat_new_resp = '|| l_violat_new_resp );
1259 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','l_violat_existing_resp = '|| l_violat_existing_resp );
1260 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','l_violat_existing_role = '|| l_violat_existing_role );
1261 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','l_violat_existing_menu = '|| l_violat_existing_menu );
1262 END IF;
1263
1264 -- concatinate return result(Violation Details)
1265 has_violation := l_violat_new_resp;
1266 IF l_violat_existing_resp IS NOT NULL THEN
1267 IF has_violation IS NOT NULL THEN
1268 has_violation := has_violation||', ';
1269 END IF;
1270 has_violation := substrb((has_violation||l_violat_existing_resp), 1, 4000);
1271 END IF;
1272 IF l_violat_existing_role IS NOT NULL THEN
1273 IF has_violation IS NOT NULL THEN
1274 has_violation := has_violation||', ';
1275 END IF;
1276 has_violation := substrb((has_violation||l_violat_existing_role), 1, 4000);
1277 END IF;
1278 IF l_violat_existing_menu IS NOT NULL THEN
1279 IF has_violation IS NOT NULL THEN
1280 has_violation := has_violation||', ';
1281 END IF;
1282 has_violation := substrb((has_violation||l_violat_existing_menu), 1, 4000);
1283 END IF;
1284
1285 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
1286 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
1287 fnd_message.set_token('CONST_DETAILS', has_violation);
1288
1289
1290 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1291 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','User_Resp_Violation_Details End');
1292 END IF;
1293
1294 return substrb((FND_MESSAGE.GET), 1, 2000);
1295 END IF; -- end of l_resp_access_count >= 2
1296
1297
1298 ELSIF 'RESPSET' = l_all_valid_constraints.type_code THEN
1299
1300
1301 -- find the number of distinct constraint entries this user can access
1302
1303 OPEN resp_acess_count_c FOR l_resp_set_dynamic_sql USING
1304 p_user_id,
1305 l_all_valid_constraints.constraint_rev_id,
1306 l_all_valid_constraints.constraint_rev_id,
1307 p_responsibility_id;
1308 FETCH resp_acess_count_c INTO l_resp_access_count;
1309 CLOSE resp_acess_count_c;
1310
1311 -- in SET type: if user can access at least two distinct groups(set) of this constraint,
1312 -- he violates this constraint
1313 IF l_resp_access_count >= 2 THEN
1314 -- once he violates at least one constraint, break the loop and inform FALSE to the caller
1315 FND_FILE.put_line(fnd_file.log, '------------ fail on constraint - ALL = '||l_all_valid_constraints.constraint_rev_id ||' ---------------');
1316 l_violat_existing_resp := Get_Violat_Existing_Resp_List (
1317 p_user_id => p_user_id,
1318 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1319 p_constraint_type_code => l_all_valid_constraints.type_code);
1320 l_violat_existing_role := Get_Violat_Existing_Role_List (
1321 p_user_id => p_user_id,
1322 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1323 p_constraint_type_code => l_all_valid_constraints.type_code);
1324 l_violat_existing_menu := Get_Violat_Existing_Menu_List (
1325 p_user_id => p_user_id,
1326 p_constraint_rev_id => l_all_valid_constraints.constraint_rev_id,
1327 p_constraint_type_code => l_all_valid_constraints.type_code);
1328
1329 -- concatinate return result(Violation Details)
1330 has_violation := l_violat_new_resp;
1331 IF l_violat_existing_resp IS NOT NULL THEN
1332 IF has_violation IS NOT NULL THEN
1333 has_violation := has_violation||', ';
1334 END IF;
1335 has_violation := substrb((has_violation||l_violat_existing_resp), 1, 4000);
1336 END IF;
1337 IF l_violat_existing_role IS NOT NULL THEN
1338 IF has_violation IS NOT NULL THEN
1339 has_violation := has_violation||', ';
1340 END IF;
1341 has_violation := substrb((has_violation||l_violat_existing_role), 1, 4000);
1342 END IF;
1343 IF l_violat_existing_menu IS NOT NULL THEN
1344 IF has_violation IS NOT NULL THEN
1345 has_violation := has_violation||', ';
1346 END IF;
1347 has_violation := substrb((has_violation||l_violat_existing_menu), 1, 4000);
1348 END IF;
1349
1350 fnd_message.set_name('AMW', 'AMW_SOD_VIOLATION');
1351 fnd_message.set_token('CONSTRAINT', l_all_valid_constraints.constraint_name);
1352 fnd_message.set_token('CONST_DETAILS', has_violation);
1353 return substrb((FND_MESSAGE.GET), 1, 2000);
1354 END IF; -- end of l_resp_access_count >= 2
1355 ELSE
1356 -- other constraint types
1357 NULL;
1358 END IF; -- end of if: constraint type_code
1359
1360 END IF; --end of if: l_valid_user_waiver_count > 0
1361
1362 END LOOP; --end of loop: c_all_valid_constraints
1363 CLOSE c_all_valid_constraints;
1364
1365 END IF; -- end of if: p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL
1366
1367 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Last has_violation '|| has_violation);
1369 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','User_Resp_Violation_Details End');
1370 END IF;
1371
1372 return has_violation;
1373
1374 EXCEPTION
1375 WHEN OTHERS THEN
1376 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1377 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
1378 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','User_Resp_Violation_Details End');
1379 END IF;
1380 RAISE;
1381
1382 END User_Resp_Violation_Details;
1383
1384
1385
1386
1387 -- ===============================================================
1388 -- Function name
1389 -- Get_Violat_Existing_Role_List
1390 --
1391 -- Purpose
1392 -- get a flat string list of this user's existing role display name, together with those new assigned role(among p_role_names_string)
1393 -- may violate the specified constraint
1394 --
1395 -- Params
1396 -- p_user_id := input fnd user_id
1397 -- p_constraint_rev_id := input constraint_rev_id
1398 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
1399 --
1400 -- Return
1401 -- a string list of role display names which violates the specified constraint,
1402 -- each display name is seperated by ','
1403 --
1404 -- History
1405 -- 07/27/2005 tsho Create
1406 -- ===============================================================
1407 Function Get_Violat_Existing_Role_List (
1408 p_user_id IN NUMBER,
1409 p_constraint_rev_id IN NUMBER,
1410 p_constraint_type_code IN VARCHAR2
1411 ) RETURN VARCHAR2
1412 IS
1413 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Existing_Role_List';
1414 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1415
1416 -- store the return value
1417 l_existing_role_string VARCHAR2(32767);
1418
1419 -- store the existing role this user has against this constraint
1420 l_existing_role_table JTF_VARCHAR2_TABLE_400;
1421
1422 TYPE refCurTyp IS REF CURSOR;
1423 existing_role_c refCurTyp;
1424
1425 -- find existing roles this user has (results in violating the specified constraint)
1426 l_existing_role_dynamic_sql VARCHAR2(500) :=
1427 'select distinct rv.display_name '
1428 ||' from amw_role_constraint_denorm rcd '
1429 ||' ,'||G_AMW_USER_ROLES||' ur '
1430 ||' ,'||G_AMW_user||' u '
1431 ||' ,'||G_AMW_ALL_ROLES_VL||' rv '
1432 ||' where rcd.constraint_rev_id = :1 '
1433 ||' and u.user_id = :2 '
1434 ||' and u.user_name = ur.user_name '
1435 ||' and ur.role_name = rcd.role_name '
1436 ||' and ur.role_orig_system = ''UMX'' '
1437 ||' and ur.role_name = rv.name ';
1438
1439 BEGIN
1440 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Role_List Start');
1442 END IF;
1443
1444 l_existing_role_string := NULL;
1445
1446 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL) THEN
1447 OPEN existing_role_c FOR l_existing_role_dynamic_sql USING
1448 p_constraint_rev_id,
1449 p_user_id;
1450 FETCH existing_role_c BULK COLLECT INTO l_existing_role_table;
1451 CLOSE existing_role_c;
1452
1453 IF l_existing_role_table IS NOT NULL AND l_existing_role_table.FIRST IS NOT NULL THEN
1454 l_existing_role_string := l_existing_role_table(1);
1455 FOR i in 2 .. l_existing_role_table.COUNT
1456 LOOP
1457 l_existing_role_string := l_existing_role_string||', '||l_existing_role_table(i);
1458 END LOOP;
1459 END IF; -- end of if: l_existing_role_table IS NOT NULL
1460
1461 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
1462
1463 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1464 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Role_List End');
1465 END IF;
1466
1467 return l_existing_role_string;
1468
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1472 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
1473 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Role_List End');
1474 END IF;
1475 RAISE;
1476 END Get_Violat_Existing_Role_List;
1477
1478
1479
1480
1481 -- ===============================================================
1482 -- Function name
1483 -- Get_Violat_Existing_Resp_List
1484 --
1485 -- Purpose
1486 -- get a flat string list of this user's existing responsibility display name, together with those new assigned role(among p_role_names_string)
1487 -- may violate the specified constraint
1488 --
1489 -- Params
1490 -- p_user_id := input fnd user_id
1491 -- p_constraint_rev_id := input constraint_rev_id
1492 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
1493 --
1494 -- Return
1495 -- a string list of role display names which violates the specified constraint,
1496 -- each display name is seperated by ','
1497 --
1498 -- History
1499 -- 07/27/2005 tsho Create
1500 -- ===============================================================
1501 Function Get_Violat_Existing_Resp_List (
1502 p_user_id IN NUMBER,
1503 p_constraint_rev_id IN NUMBER,
1504 p_constraint_type_code IN VARCHAR2
1505 ) RETURN VARCHAR2
1506 IS
1507 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Existing_Resp_List';
1508 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1509
1510 -- store the return value
1511 l_existing_resp_string VARCHAR2(32767);
1512
1513 -- store the existing responsibilities this user has against this constraint
1514 l_existing_resp_table JTF_VARCHAR2_TABLE_400;
1515
1516 TYPE refCurTyp IS REF CURSOR;
1517 existing_resp_c refCurTyp;
1518
1519
1520 -- find existing responsibilities this user has (results in violating the specified constraint)
1521 l_existing_resp_dynamic_sql VARCHAR2(2000) :=
1522 'select distinct resp.responsibility_name '
1523 ||' from amw_role_constraint_denorm rcd '
1524 ||' ,'||G_AMW_USER_ROLES||' ur '
1525 ||' ,'||G_AMW_user||' u '
1526 ||' ,'||G_AMW_RESPONSIBILITY_VL||' resp '
1527 ||' where rcd.constraint_rev_id = :1 '
1528 ||' and u.user_id = :2 '
1529 ||' and u.user_name = ur.user_name '
1530 ||' and ur.role_orig_system_id = rcd.responsibility_id '
1531 ||' and ur.role_orig_system = ''FND_RESP'' '
1532 ||' and ur.role_orig_system_id = resp.responsibility_id '
1533 ||' UNION ALL '
1534 ||' select distinct resp.responsibility_name '
1535 ||' from amw_constraint_entries cste '
1536 ||' ,'||G_AMW_USER_ROLES||' ur '
1537 ||' ,'||G_AMW_user||' u '
1538 ||' ,'||G_AMW_RESPONSIBILITY_VL||' resp '
1539 ||' where cste.constraint_rev_id = :3 '
1540 ||' and u.user_id = :4 '
1541 ||' and u.user_name = ur.user_name '
1542 ||' and ur.role_orig_system_id = cste.function_id '
1543 ||' and ur.role_orig_system = ''FND_RESP'' '
1544 ||' and ur.role_orig_system_id = resp.responsibility_id ';
1545
1546 BEGIN
1547
1548 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1549 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Resp_List Start');
1550 END IF;
1551
1552 l_existing_resp_string := NULL;
1553
1554 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL) THEN
1555 OPEN existing_resp_c FOR l_existing_resp_dynamic_sql USING
1556 p_constraint_rev_id,
1557 p_user_id,
1558 p_constraint_rev_id,
1559 p_user_id;
1560 FETCH existing_resp_c BULK COLLECT INTO l_existing_resp_table;
1561 CLOSE existing_resp_c;
1562
1563 IF l_existing_resp_table IS NOT NULL AND l_existing_resp_table.FIRST IS NOT NULL THEN
1564 l_existing_resp_string := l_existing_resp_table(1);
1565 FOR i in 2 .. l_existing_resp_table.COUNT
1566 LOOP
1567 l_existing_resp_string := l_existing_resp_string||', '||l_existing_resp_table(i);
1568 END LOOP;
1569 END IF; -- end of if: l_existing_resp_table IS NOT NULL
1570
1571 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
1572
1573 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1574 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Resp_List End');
1575 END IF;
1576
1577 return l_existing_resp_string;
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1581 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
1582 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Resp_List End');
1583 END IF;
1584 RAISE;
1585
1586 END Get_Violat_Existing_Resp_List;
1587
1588
1589
1590
1591 -- ===============================================================
1592 -- Function name
1593 -- Get_Violat_Existing_Menu_List
1594 --
1595 -- Purpose
1596 -- get a flat string list of this user's existing permission set(menu) display name, ]
1597 -- together with those new assigned role(among p_role_names_string)
1598 -- may violate the specified constraint
1599 --
1600 -- Params
1601 -- p_user_id := input fnd user_id
1602 -- p_constraint_rev_id := input constraint_rev_id
1603 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
1604 --
1605 -- Return
1606 -- a string list of role display names which violates the specified constraint,
1607 -- each display name is seperated by ','
1608 --
1609 -- History
1610 -- 07/27/2005 tsho Create
1611 -- ===============================================================
1612 Function Get_Violat_Existing_Menu_List (
1613 p_user_id IN NUMBER,
1614 p_constraint_rev_id IN NUMBER,
1615 p_constraint_type_code IN VARCHAR2
1616 ) RETURN VARCHAR2
1617 IS
1618 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Violat_Existing_Menu_List';
1619 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1620
1621 -- store the return value
1622 l_existing_menu_string VARCHAR2(32767);
1623
1624 -- store the existing menus this user has against this constraint
1625 l_existing_menu_table JTF_VARCHAR2_TABLE_400;
1626
1627 TYPE refCurTyp IS REF CURSOR;
1628 existing_menu_c refCurTyp;
1629
1630 -- find existing menus this user has (results in violating the specified constraint)
1631 l_existing_menu_dynamic_sql VARCHAR2(1500) :=
1632 ' select menu.user_menu_name '
1633 ||' from amw_role_constraint_denorm rcd '
1634 ||' ,'||G_AMW_MENUS_VL||' menu '
1635 ||' ,'||G_AMW_user||' u '
1636 ||' ,'||G_AMW_GRANTS||' gra '
1637 ||' where rcd.constraint_rev_id = :1 '
1638 ||' and u.user_id = :2 '
1639 ||' and u.user_name = gra.grantee_key '
1640 ||' and gra.grantee_type = ''USER'' '
1641 ||' and gra.instance_type = ''GLOBAL'' '
1642 ||' and gra.object_id = -1 '
1643 ||' and gra.menu_id = rcd.menu_id '
1644 ||' and gra.menu_id = menu.menu_id '
1645 ||' UNION '
1646 ||' select menu.user_menu_name '
1647 ||' from amw_role_constraint_denorm rcd '
1648 ||' ,'||G_AMW_MENUS_VL||' menu '
1649 ||' ,'||G_AMW_GRANTS||' gra '
1650 ||' where rcd.constraint_rev_id = :3 '
1651 ||' and gra.grantee_key = ''GLOBAL'' '
1652 ||' and gra.grantee_type = ''GLOBAL'' '
1653 ||' and gra.instance_type = ''GLOBAL'' '
1654 ||' and gra.object_id = -1 '
1655 ||' and gra.menu_id = rcd.menu_id '
1656 ||' and gra.menu_id = menu.menu_id ';
1657
1658 BEGIN
1659
1660 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1661 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Menu_List Start');
1662 END IF;
1663
1664 l_existing_menu_string := NULL;
1665
1666 IF (p_user_id IS NOT NULL AND p_constraint_rev_id IS NOT NULL AND p_constraint_type_code IS NOT NULL) THEN
1667 OPEN existing_menu_c FOR l_existing_menu_dynamic_sql USING
1668 p_constraint_rev_id,
1669 p_user_id,
1670 p_constraint_rev_id;
1671 FETCH existing_menu_c BULK COLLECT INTO l_existing_menu_table;
1672 CLOSE existing_menu_c;
1673
1674 IF l_existing_menu_table IS NOT NULL AND l_existing_menu_table.FIRST IS NOT NULL THEN
1675 l_existing_menu_string := l_existing_menu_table(1);
1676 FOR i in 2 .. l_existing_menu_table.COUNT
1677 LOOP
1678 l_existing_menu_string := l_existing_menu_string||', '||l_existing_menu_table(i);
1679 END LOOP;
1680 END IF; -- end of if: l_existing_menu_table IS NOT NULL
1681
1682 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
1683
1684 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1685 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Menu_List End');
1686 END IF;
1687
1688 return l_existing_menu_string;
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 IF( l_log_stmt_level >= l_curr_log_level ) THEN
1692 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
1693 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.PreventFormCustomization','Get_Violat_Existing_Menu_List End');
1694 END IF;
1695 RAISE;
1696
1697 END Get_Violat_Existing_Menu_List;
1698
1699
1700 /*
1701 * cpetriuc
1702 * ---------------------
1703 * CHECK_MENU_VIOLATIONS
1704 * ---------------------
1705 * Checks if the menu provided as argument violates any SOD (Segregation of Duties)
1706 * constraints. If a constraint is violated, the function returns an error message
1707 * containing the name of the violated constraint together with the list of functions
1708 * that define the constraint. Otherwise, the function returns 'N'.
1709 *
1710 * psomanat : bug 5692905 : consider Responsibility waiver. Added the parameters
1711 * p_responsibility_id,p_application_id
1712 */
1713 function CHECK_MENU_VIOLATIONS(p_menu_id NUMBER,
1714 p_responsibility_id IN NUMBER :=NULL,
1715 p_application_id IN NUMBER :=NULL
1716 ) return VARCHAR2 is
1717
1718 g_menu_function_id_list G_NUMBER_TABLE;
1719
1720
1721 cursor MENU_FUNCTIONS(p_menu_id NUMBER) is
1722 select distinct FUNCTION_ID
1723 from FND_COMPILED_MENU_FUNCTIONS
1724 where MENU_ID = p_menu_id;
1725
1726
1727 begin
1728
1729 g_menu_function_id_list.delete();
1730 open MENU_FUNCTIONS(p_menu_id);
1731 fetch MENU_FUNCTIONS bulk collect into g_menu_function_id_list;
1732 close MENU_FUNCTIONS;
1733
1734 return CHECK_FUNCTION_LIST_VIOLATIONS(g_menu_function_id_list,p_responsibility_id,p_application_id);
1735
1736
1737 end CHECK_MENU_VIOLATIONS;
1738
1739
1740
1741
1742 /*
1743 * cpetriuc
1744 * -------------------------
1745 * CHECK_FUNCTION_VIOLATIONS
1746 * -------------------------
1747 * Checks if any SOD (Segregation of Duties) constraints would be violated if the
1748 * argument function or submenu would be added to the menu provided as argument. If a
1749 * constraint would be violated, the function returns an error message containing the name
1750 * of the potentially violated constraint together with the list of functions that define
1751 * the constraint. Otherwise, the function returns 'N'.
1752 */
1753 function CHECK_FUNCTION_VIOLATIONS(p_menu_id NUMBER, p_sub_menu_id NUMBER, p_function_id NUMBER) return VARCHAR2 is
1754 l_menu_id_list G_NUMBER_TABLE;
1755
1756 m_return_text VARCHAR2(3000);
1757
1758 CURSOR all_menus_in_hierarchy(p_menu_id NUMBER) is
1759 SELECT sub_menu_id menu_id
1760 FROM fnd_menu_entries
1761 START WITH menu_id =p_menu_id
1762 CONNECT BY PRIOR sub_menu_id = menu_id
1763 UNION
1764 SELECT menu_id
1765 FROM fnd_menu_entries
1766 START WITH menu_id =p_menu_id
1767 CONNECT BY PRIOR menu_id = sub_menu_id;
1768
1769
1770 /** dliao modified on 11/08/2006 for the bug 5610537
1771 use FND_MENU_ENTRIES i.s.o. FND_COMPILED_MENU_FUNCTION because FND_COMPILED_MENU_FUNCTION
1772 won't be populated until all of menu entries are created. Our goal is to check multiple
1773 menu entries that violating the sod before they are created in the database.
1774 ***/
1775
1776 cursor MENU_FUNCTIONS(p_menu_id NUMBER) is
1777 select distinct FUNCTION_ID
1778 from FND_MENU_ENTRIES --FND_COMPILED_MENU_FUNCTIONS
1779 where MENU_ID = p_menu_id;
1780
1781 cursor MENU_AND_SUB_MENU_FUNCTIONS(p_menu_id NUMBER, p_sub_menu_id NUMBER) is
1782 select distinct FUNCTION_ID
1783 from FND_MENU_ENTRIES --FND_COMPILED_MENU_FUNCTIONS
1784 where MENU_ID = p_menu_id or MENU_ID = p_sub_menu_id;
1785
1786 flag BOOLEAN;
1787
1788 BEGIN
1789
1790 g_menu_id_list.delete();
1791 g_function_id_list.delete();
1792 g_menu_function_id_list.delete();
1793
1794 OPEN all_menus_in_hierarchy(p_menu_id);
1795 FETCH all_menus_in_hierarchy BULK COLLECT INTO l_menu_id_list;
1796 CLOSE all_menus_in_hierarchy;
1797
1798 IF l_menu_id_list.COUNT <> 0 THEN
1799 FOR i IN 1 .. l_menu_id_list.COUNT
1800 LOOP
1801 IF NOT (g_menu_id_list.EXISTS(i)) THEN
1802 flag:=process_menu_tree_down_for_mn(l_menu_id_list(i));
1803 END IF;
1804 END LOOP;
1805 END IF;
1806
1807 -- ptulasi : 07/11/2007
1808 -- bug: 6208788 : Modified below code to eliminate the duplicate entry of
1809 -- pfunction id to g_menu_function_id_list
1810 IF p_function_id IS NOT NULL THEN
1811 IF NOT (g_function_id_list.EXISTS(p_function_id)) THEN
1812 g_menu_function_id_list(g_menu_function_id_list.count+1):=p_function_id;
1813 END IF;
1814 END IF;
1815
1816 IF p_sub_menu_id IS NOT NULL THEN
1817 flag:=process_menu_tree_down_for_mn(p_sub_menu_id);
1818 END IF;
1819
1820 m_return_text := CHECK_FUNCTION_LIST_VIOLATIONS(g_menu_function_id_list,NULL,NULL);
1821 if (m_return_text <> 'N') then
1822 return m_return_text;
1823 end if;
1824
1825 return 'N';
1826
1827
1828 -- psomanat : Commenting the below code for bug 6010908
1829
1830 /*for menu in ALL_MENUS_IN_HIERARCHY(p_menu_id) loop
1831
1832 g_menu_function_id_list.delete();
1833
1834 if (p_sub_menu_id is null) then
1835 open MENU_FUNCTIONS(menu.MENU_ID);
1836 fetch MENU_FUNCTIONS bulk collect into g_menu_function_id_list;
1837 close MENU_FUNCTIONS;
1838 else
1839 open MENU_AND_SUB_MENU_FUNCTIONS(menu.MENU_ID, p_sub_menu_id);
1840 fetch MENU_AND_SUB_MENU_FUNCTIONS bulk collect into g_menu_function_id_list;
1841 close MENU_AND_SUB_MENU_FUNCTIONS;
1842 m_return_text := CHECK_FUNCTION_LIST_VIOLATIONS(g_menu_function_id_list,NULL,NULL);
1843 if (m_return_text <> 'N') then return m_return_text; end if;
1844 end if;
1845
1846 if (p_function_id is not null) then
1847 m_return_text := CHECK_ADD_FUNCTION_VIOLATIONS(g_menu_function_id_list, p_function_id);
1848 if (m_return_text <> 'N') then return m_return_text; end if;
1849 end if;
1850
1851 end loop;
1852
1853 return 'N'; */
1854
1855
1856 end CHECK_FUNCTION_VIOLATIONS;
1857
1858
1859
1860
1861 /*
1862 * cpetriuc
1863 * ------------------------------
1864 * CHECK_FUNCTION_LIST_VIOLATIONS
1865 * ------------------------------
1866 * Created initially as a helper function, to be used internally.
1867 *
1868 * Checks if the list of menu functions provided as argument violates any SOD
1869 * (Segregation of Duties) constraints. If a constraint is violated, the function
1870 * returns an error message containing the name of the violated constraint together
1871 * with the list of functions that define the constraint. Otherwise, the function
1872 * returns 'N'.
1873 *
1874 * psomanat : bug 5692905 : consider Responsibility waiver. Added the parameters
1875 * p_responsibility_id,p_application_id
1876 */
1877 function CHECK_FUNCTION_LIST_VIOLATIONS(g_menu_function_id_list G_NUMBER_TABLE,
1878 p_responsibility_id IN NUMBER,
1879 p_application_id IN NUMBER) return VARCHAR2 is
1880
1881 g_constraint_function_id_list G_NUMBER_TABLE;
1882 g_constraint_group_code_list G_NUMBER_TABLE;
1883 g_group_code_list G_NUMBER_TABLE;
1884 m_constraint_details VARCHAR2(4000);
1885 m_counter NUMBER;
1886 m_failed BOOLEAN;
1887 m_function_name VARCHAR2(240);
1888 --fnd.message returns up to 2000 bytes of message
1889 m_return_text VARCHAR2(2000);
1890
1891
1892 cursor CONSTRAINTS is
1893 select *
1894 from AMW_CONSTRAINTS_VL
1895 where
1896 (TYPE_CODE = 'ALL' or TYPE_CODE = 'ME' or TYPE_CODE = 'SET') and
1897 START_DATE <= sysdate and
1898 (END_DATE is null or END_DATE >= sysdate)
1899 and objective_code = 'PR';
1900
1901 cursor CONSTRAINT_ENTRIES(p_constraint_rev_id NUMBER) is
1902 select distinct FUNCTION_ID, GROUP_CODE
1903 from AMW_CONSTRAINT_ENTRIES
1904 where
1905 CONSTRAINT_REV_ID = p_constraint_rev_id and
1906 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
1907
1908 cursor CONSTRAINT_GROUP_CODES(p_constraint_rev_id NUMBER) is
1909 select distinct GROUP_CODE
1910 from AMW_CONSTRAINT_ENTRIES
1911 where
1912 CONSTRAINT_REV_ID = p_constraint_rev_id and
1913 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
1914
1915 l_valid_resp_waiver_count NUMBER;
1916 CURSOR c_valid_resp_waivers (l_constraint_rev_id IN NUMBER, l_resp_id IN NUMBER, l_appl_id IN NUMBER) IS
1917 SELECT count(*)
1918 FROM amw_constraint_waivers_vl
1919 WHERE constraint_rev_id = l_constraint_rev_id
1920 AND object_type = 'RESP'
1921 AND PK1 = l_resp_id
1922 AND PK2 = l_appl_id
1923 AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
1924
1925 begin
1926
1927 for constraint in CONSTRAINTS loop
1928 m_failed := FALSE;
1929 g_constraint_function_id_list.delete();
1930 g_constraint_group_code_list.delete();
1931
1932 open CONSTRAINT_ENTRIES(constraint.CONSTRAINT_REV_ID);
1933 fetch CONSTRAINT_ENTRIES bulk collect into
1934 g_constraint_function_id_list,
1935 g_constraint_group_code_list;
1936 close CONSTRAINT_ENTRIES;
1937
1938 for i in 1 .. g_constraint_function_id_list.COUNT loop
1939 select USER_FUNCTION_NAME into m_function_name
1940 from FND_FORM_FUNCTIONS_VL
1941 where FUNCTION_ID = g_constraint_function_id_list(i);
1942
1943 if i = 1 then m_constraint_details := m_function_name;
1944 else m_constraint_details := substrb((m_constraint_details || ', ' || m_function_name), 1, 4000);
1945 end if;
1946
1947 end loop;
1948
1949 ------------------------------------
1950 -- Process a constraint of type ALL.
1951 ------------------------------------
1952
1953 if constraint.TYPE_CODE = 'ALL' then
1954 for i in 1 .. g_constraint_function_id_list.COUNT loop
1955 m_failed := FALSE; -- Each constraint function must exist among the menu functions.
1956 for j in 1 .. g_menu_function_id_list.COUNT loop
1957 if g_constraint_function_id_list(i) = g_menu_function_id_list(j) then
1958 m_failed := TRUE; -- This constraint function exists among the menu functions.
1959 exit;
1960 end if;
1961 end loop;
1962 if m_failed = FALSE then
1963 exit; -- A constraint function has not been found among the menu functions.
1964 end if;
1965 end loop;
1966 end if;
1967
1968 ------------------------------------------------------
1969 -- Process a constraint of type ME (Mutual Exclusion).
1970 ------------------------------------------------------
1971
1972 if constraint.TYPE_CODE = 'ME' then
1973 m_counter := 0;
1974 for i in 1 .. g_constraint_function_id_list.COUNT loop
1975 for j in 1 .. g_menu_function_id_list.COUNT loop
1976 if g_constraint_function_id_list(i) = g_menu_function_id_list(j) then
1977 m_counter := m_counter + 1;
1978 end if;
1979 end loop;
1980 end loop;
1981
1982 if m_counter >= 2 then
1983 m_failed := TRUE;
1984 end if;
1985 end if;
1986
1987 ------------------------------------
1988 -- Process a constraint of type SET.
1989 ------------------------------------
1990 if constraint.TYPE_CODE = 'SET' then
1991 g_group_code_list.delete();
1992
1993 open CONSTRAINT_GROUP_CODES(constraint.CONSTRAINT_REV_ID);
1994 fetch CONSTRAINT_GROUP_CODES bulk collect into g_group_code_list;
1995 close CONSTRAINT_GROUP_CODES;
1996
1997 m_failed := TRUE; -- Assume the contrary.
1998
1999 for i in 1 .. g_constraint_function_id_list.COUNT loop
2000 for j in 1 .. g_menu_function_id_list.COUNT loop
2001 if g_constraint_function_id_list(i) = g_menu_function_id_list(j) then
2002 g_group_code_list(g_constraint_group_code_list(i)) := 0;
2003 end if;
2004 end loop;
2005 end loop;
2006
2007 for k in 1 .. g_group_code_list.COUNT loop
2008 if g_group_code_list(k) <> 0 then
2009 m_failed := FALSE; -- Not all groups have at least one function among the menu functions.
2010 exit;
2011 end if;
2012 end loop;
2013
2014 end if;
2015
2016 -- psomanat : bug 5692905 : consider Responsibility waiver when this function
2017 -- is called from CHECK_MENU_VIOLATIONS
2018 IF p_responsibility_id IS NOT NULL AND p_application_id IS NOT NULL THEN
2019 -- check if this responsibility is waived (due to User Waiver) from this constraint
2020 OPEN c_valid_resp_waivers(constraint.CONSTRAINT_REV_ID,p_responsibility_id,p_application_id);
2021 FETCH c_valid_resp_waivers INTO l_valid_resp_waiver_count;
2022 CLOSE c_valid_resp_waivers;
2023
2024 IF (l_valid_resp_waiver_count > 0) THEN
2025 m_failed := FALSE;
2026 END IF;
2027 END IF;
2028
2029 -----------------------------------------------------------------------
2030 -- If this constraint has been violated, return an appropriate message.
2031 -----------------------------------------------------------------------
2032
2033 if m_failed = TRUE then
2034
2035 FND_MESSAGE.SET_NAME('AMW', 'AMW_SOD_VIOLATION');
2036 FND_MESSAGE.SET_TOKEN('CONSTRAINT', constraint.CONSTRAINT_NAME);
2037 FND_MESSAGE.SET_TOKEN('CONST_DETAILS', m_constraint_details);
2038 m_return_text := substrb((FND_MESSAGE.GET), 1, 2000);
2039
2040 return m_return_text;
2041
2042 end if;
2043
2044 end loop; -- CONSTRAINTS cursor loop
2045
2046 return 'N';
2047
2048
2049 end CHECK_FUNCTION_LIST_VIOLATIONS;
2050
2051
2052
2053
2054 /*
2055 * cpetriuc
2056 * -----------------------------
2057 * CHECK_ADD_FUNCTION_VIOLATIONS
2058 * -----------------------------
2059 * Created initially as a helper function, to be used internally.
2060 *
2061 * Checks if adding the argument function to the list of menu functions provided as
2062 * argument violates any SOD (Segregation of Duties) constraints. If a constraint is
2063 * violated, the function returns an error message containing the name of the violated
2064 * constraint together with the list of functions that define the constraint. Otherwise,
2065 * the function returns 'N'.
2066 */
2067 function CHECK_ADD_FUNCTION_VIOLATIONS(g_menu_function_id_list G_NUMBER_TABLE, p_function_id NUMBER) return VARCHAR2 is
2068
2069 g_constraint_function_id_list G_NUMBER_TABLE;
2070 g_constraint_group_code_list G_NUMBER_TABLE;
2071 g_group_code_list G_NUMBER_TABLE;
2072 m_constraint_details VARCHAR2(4000);
2073 m_failed BOOLEAN;
2074 m_function_name VARCHAR2(240);
2075 --fnd_message.get returns up to 2000 bytes of message
2076 m_return_text VARCHAR2(2000);
2077
2078
2079 cursor CONSTRAINTS is
2080 select *
2081 from AMW_CONSTRAINTS_VL
2082 where
2083 (TYPE_CODE = 'ALL' or TYPE_CODE = 'ME' or TYPE_CODE = 'SET') and
2084 START_DATE <= sysdate and
2085 (END_DATE is null or END_DATE >= sysdate)
2086 and objective_code = 'PR';
2087
2088 cursor CONSTRAINT_ENTRIES(p_constraint_rev_id NUMBER) is
2089 select distinct FUNCTION_ID, GROUP_CODE
2090 from AMW_CONSTRAINT_ENTRIES
2091 where
2092 CONSTRAINT_REV_ID = p_constraint_rev_id and
2093 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
2094
2095 cursor CONSTRAINT_GROUP_CODES(p_constraint_rev_id NUMBER) is
2096 select distinct GROUP_CODE
2097 from AMW_CONSTRAINT_ENTRIES
2098 where
2099 CONSTRAINT_REV_ID = p_constraint_rev_id and
2100 (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
2101
2102
2103 begin
2104
2105 for constraint in CONSTRAINTS loop
2106 m_failed := FALSE;
2107
2108 g_constraint_function_id_list.delete();
2109 g_constraint_group_code_list.delete();
2110
2111 open CONSTRAINT_ENTRIES(constraint.CONSTRAINT_REV_ID);
2112 fetch CONSTRAINT_ENTRIES bulk collect into
2113 g_constraint_function_id_list,
2114 g_constraint_group_code_list;
2115 close CONSTRAINT_ENTRIES;
2116
2117 for i in 1 .. g_constraint_function_id_list.COUNT loop
2118
2119 select USER_FUNCTION_NAME into m_function_name
2120 from FND_FORM_FUNCTIONS_VL
2121 where FUNCTION_ID = g_constraint_function_id_list(i);
2122
2123 if i = 1 then m_constraint_details := m_function_name;
2124 else m_constraint_details := m_constraint_details || ', ' || m_function_name;
2125 end if;
2126
2127 end loop;
2128
2129 for i in 1 .. g_constraint_function_id_list.COUNT loop
2130 if g_constraint_function_id_list(i) = p_function_id then
2131 ------------------------------------
2132 -- Process a constraint of type ALL.
2133 ------------------------------------
2134 if constraint.TYPE_CODE = 'ALL' then
2135 for j in 1 .. g_constraint_function_id_list.COUNT loop
2136 m_failed := FALSE; -- Each constraint function must exist among the menu functions.
2137 if i <> j then
2138 for k in 1 .. g_menu_function_id_list.COUNT loop
2139 if g_constraint_function_id_list(j) = g_menu_function_id_list(k) then
2140 m_failed := TRUE; -- This constraint function exists among the menu functions.
2141 exit;
2142 end if;
2143 end loop;
2144 else
2145 m_failed := TRUE; -- If i = j, continue the loop.
2146 end if;
2147 if m_failed = FALSE then
2148 exit; -- A constraint function has not been found among the menu functions.
2149 end if;
2150 end loop;
2151 end if;
2152
2153 ------------------------------------------------------
2154 -- Process a constraint of type ME (Mutual Exclusion).
2155 ------------------------------------------------------
2156 if constraint.TYPE_CODE = 'ME' then
2157 for j in 1 .. g_constraint_function_id_list.COUNT loop
2158 if i <> j then
2159 for k in 1 .. g_menu_function_id_list.COUNT loop
2160 if g_constraint_function_id_list(j) = g_menu_function_id_list(k) then
2161 m_failed := TRUE;
2162 exit;
2163 end if;
2164 end loop;
2165 end if;
2166
2167 if m_failed = TRUE then
2168 exit; -- At least one mutual exclusivity has been violated.
2169 end if;
2170 end loop;
2171 end if;
2172
2173 ------------------------------------
2174 -- Process a constraint of type SET.
2175 ------------------------------------
2176 if constraint.TYPE_CODE = 'SET' then
2177 g_group_code_list.delete();
2178 open CONSTRAINT_GROUP_CODES(constraint.CONSTRAINT_REV_ID);
2179 fetch CONSTRAINT_GROUP_CODES bulk collect into g_group_code_list;
2180 close CONSTRAINT_GROUP_CODES;
2181
2182 g_group_code_list(g_constraint_group_code_list(i)) := 0;
2183 m_failed := TRUE; -- Assume the contrary.
2184 for j in 1 .. g_constraint_function_id_list.COUNT loop
2185 if i <> j then
2186 for k in 1 .. g_menu_function_id_list.COUNT loop
2187 if g_constraint_function_id_list(j) = g_menu_function_id_list(k) then
2188 g_group_code_list(g_constraint_group_code_list(j)) := 0;
2189 end if;
2190 end loop;
2191 end if;
2192 end loop;
2193
2194 for l in 1 .. g_group_code_list.COUNT loop
2195 if g_group_code_list(l) <> 0 then
2196 m_failed := FALSE; -- Not all groups have at least one function among the menu functions.
2197 exit;
2198 end if;
2199 end loop;
2200 end if;
2201
2202 -----------------------------------------------------------------------
2203 -- If this constraint has been violated, return an appropriate message.
2204 -----------------------------------------------------------------------
2205 if m_failed = TRUE then
2206 FND_MESSAGE.SET_NAME('AMW', 'AMW_SOD_VIOLATION');
2207 FND_MESSAGE.SET_TOKEN('CONSTRAINT', constraint.CONSTRAINT_NAME);
2208 FND_MESSAGE.SET_TOKEN('CONST_DETAILS', m_constraint_details);
2209 m_return_text := substrb((FND_MESSAGE.GET), 1, 2000);
2210 return m_return_text;
2211 end if;
2212
2213 end if; -- if g_constraint_function_id_list(i) = p_function_id
2214 end loop; -- g_constraint_function_id_list loop
2215
2216 end loop; -- CONSTRAINTS cursor loop
2217
2218 return 'N';
2219
2220
2221 end CHECK_ADD_FUNCTION_VIOLATIONS;
2222
2223
2224
2225
2226
2227 end AMW_VIOLATION_PUB;
[Home] [Help]