1 PACKAGE BODY FND_FUNCTION_SECURITY AS
2 /* $Header: AFSCFUNB.pls 115.10 2004/05/25 22:58:39 pdeluna ship $ */
3
4
5 --
6 -- RESPONSIBILITY_EXISTS
7 -- Check if responsibility exists.
8 -- IN
9 -- responsibility_key (REQUIRED, KEY) - responsibility key
10 -- RETURNS
11 -- TRUE if responsibility exists
12 -- NOTES:
13 -- 1. The responsibility_id in the script must match the id in the tape
14 -- database. To get the id, first create the responsibility in tape,
15 -- then query the id using either examine in the form or sqlplus.
16 --
17 FUNCTION RESPONSIBILITY_EXISTS(
18 responsibility_key IN VARCHAR2)
19 RETURN BOOLEAN
20 IS
21 dummy NUMBER;
22 BEGIN
23 SELECT 1
24 INTO dummy
25 FROM fnd_responsibility r
26 WHERE r.responsibility_key = responsibility_exists.responsibility_key;
27
28 RETURN TRUE;
29 EXCEPTION
30 WHEN no_data_found THEN
31 RETURN FALSE;
32 END RESPONSIBILITY_EXISTS;
33
34 --
35 -- FORM_FUNCTION_EXISTS
36 -- Check if function exists.
37 -- IN
38 -- function_name (KEY, REQUIRED) - Function developer key name
39 -- RETURNS
40 -- TRUE if function exists
41 --
42 FUNCTION FORM_FUNCTION_EXISTS(
43 function_name IN VARCHAR2)
44 RETURN BOOLEAN
45 IS
46 dummy NUMBER;
47 BEGIN
48 SELECT 1
49 INTO dummy
50 FROM fnd_form_functions f
51 WHERE f.function_name = form_function_exists.function_name;
52
53 RETURN TRUE;
54 EXCEPTION
55 WHEN no_data_found THEN
56 RETURN FALSE;
57 END FORM_FUNCTION_EXISTS;
58
59 --
60 -- MENU_EXISTS
61 -- Check if menu exists.
62 -- IN
63 -- menu_name (KEY, REQUIRED) - Menu developer key name
64 -- RETURNS
65 -- TRUE if menu exists
66 --
67 FUNCTION MENU_EXISTS(
68 menu_name IN VARCHAR2)
69 RETURN BOOLEAN
70 IS
71 dummy NUMBER;
72 BEGIN
73 SELECT 1
74 INTO dummy
75 FROM fnd_menus f
76 WHERE f.menu_name = menu_exists.menu_name;
77
78 RETURN TRUE;
79 EXCEPTION
80 WHEN no_data_found THEN
81 RETURN FALSE;
82 END MENU_EXISTS;
83
84 --
85 -- MENU_ENTRY_EXISTS
86 -- Check if menu entry exists.
87 -- IN
88 -- menu_name (KEY, REQUIRED) - Menu developer key name
89 -- sub_menu_name (KEY) - Developer key name of submenu
90 -- function_name (KEY) - Developer key name of function
91 -- RETURNS
92 -- TRUE if menu entry exists
93 --
94 FUNCTION MENU_ENTRY_EXISTS(
95 menu_name IN VARCHAR2,
96 sub_menu_name IN VARCHAR2,
97 function_name IN VARCHAR2)
98 RETURN BOOLEAN
99 IS
100 dummy NUMBER;
101 BEGIN
102 SELECT 1
103 INTO dummy
104 FROM fnd_menu_entries me, fnd_menus m, fnd_menus s, fnd_form_functions f
105 WHERE me.menu_id = m.menu_id
106 AND m.menu_name = menu_entry_exists.menu_name
107 AND me.sub_menu_id = s.menu_id (+)
108 AND nvl(s.menu_name, 'x') = nvl(menu_entry_exists.sub_menu_name, 'x')
109 AND me.function_id = f.function_id (+)
110 AND nvl(f.function_name, 'x') = nvl(menu_entry_exists.function_name, 'x');
111
112 RETURN TRUE;
113 EXCEPTION
114 WHEN no_data_found THEN
115 RETURN FALSE;
116 END MENU_ENTRY_EXISTS;
117
118 --
119 -- SECURITY_RULE_EXISTS
120 -- Check if security rule exists.
121 -- IN
122 -- responsibility_key (KEY, REQUIRED) - Key of responsibility owning rule
123 -- rule_type (KEY, REQUIRED) - Rule type
124 -- 'F' = Function exclusion
125 -- 'M' = Menu exclusion
126 -- rule_name (KEY, REQUIRED) - Rule name
127 -- Function developer key name (if rule_type = 'F')
128 -- Menu developer key name (if rule_type = 'M')
129 -- RETURNS
130 -- TRUE if security rule exists
131 --
135 rule_name IN VARCHAR2) -- Function_name or menu_name
132 FUNCTION SECURITY_RULE_EXISTS(
133 responsibility_key IN VARCHAR2,
134 rule_type IN VARCHAR2 DEFAULT 'F', -- F = Function, M = Menu
136 RETURN BOOLEAN
137 IS
138 dummy NUMBER;
139 BEGIN
140 IF (rule_type = 'F') THEN
141 SELECT 1
142 INTO dummy
143 FROM fnd_resp_functions rf, fnd_responsibility r, fnd_form_functions f
144 WHERE rf.responsibility_id = r.responsibility_id
145 AND rf.application_id = r.application_id
146 AND r.responsibility_key = security_rule_exists.responsibility_key
147 AND rf.rule_type = 'F'
148 AND rf.action_id = f.function_id
149 AND f.function_name = security_rule_exists.rule_name;
150 ELSE
151 SELECT 1
152 INTO dummy
153 FROM fnd_resp_functions rf, fnd_responsibility r, fnd_menus m
154 WHERE rf.responsibility_id = r.responsibility_id
155 AND rf.application_id = r.application_id
156 AND r.responsibility_key = security_rule_exists.responsibility_key
157 AND rf.rule_type = 'M'
158 AND rf.action_id = m.menu_id
159 AND m.menu_name = security_rule_exists.rule_name;
160 END IF;
161
162 RETURN TRUE;
163 EXCEPTION
164 WHEN no_data_found THEN
165 RETURN FALSE;
166 END SECURITY_RULE_EXISTS;
167
168 --
169 -- RESPONSIBILITY
170 -- Insert/update/delete a GUI responsibility (not 2.3 responsibilities).
171 --
172 -- IN:
173 -- responsibility_id (REQUIRED, KEY) - Responsibility id (see note 1)
174 -- responsibility_key (REQUIRED, KEY) - Responsibility key
175 -- responsibility_name (REQUIRED) - Responsibility name
176 -- application_name (REQUIRED) - Application short name
177 -- description - Description
178 -- start_date (REQUIRED) - Effective Date From
179 -- end_date - Effective Date To
180 -- data_group_name (REQUIRED) - Data Group Name
181 -- data_group_application (REQUIRED) - Data group application short name
182 -- menu_name (REQUIRED) - Menu developer key name
183 -- request_group_name - Request group name
184 -- request_group_application - Request group application short name
185 -- version - '4' for Forms Resp, 'W' for Web Resp, 'M' for Mobile Apps
186 -- web_host_name - Web Host Name (for Web Resp)
187 -- web_agent_name - Web Agent Name (for Web Resp)
188 -- delete_flag (REQUIRED) - Delete mode (see package comments)
189 --
190 -- CHILD REFERENCES: (Delete is cascaded to ...)
191 -- (none - see note 2)
192 -- FOREIGN REFERENCES: (Delete prevented if referenced in ...)
193 -- (none - see note 2)
194 --
195 -- NOTES:
196 -- 1. The responsibility_id in the script must match the id in the tape
197 -- database. To get the id, first create the responsibility in tape,
198 -- then query the id using either examine in the form or sqlplus.
199 -- 2. Responsibilities are never deleted. If this procedure is called
200 -- with delete_flag = 'Y' or 'F', the end_date will be set to sysdate
201 -- to effectively disable the responsibility.
202 --
203 PROCEDURE RESPONSIBILITY (
204 responsibility_id IN NUMBER,
205 responsibility_key IN VARCHAR2,
206 responsibility_name IN VARCHAR2,
207 application IN VARCHAR2,
208 description IN VARCHAR2 DEFAULT '',
209 START_DATE IN DATE,
210 end_date IN DATE DEFAULT '',
211 data_group_name IN VARCHAR2,
212 data_group_application IN VARCHAR2,
213 menu_name IN VARCHAR2,
214 request_group_name IN VARCHAR2 DEFAULT '',
215 request_group_application IN VARCHAR2 DEFAULT '',
216 version IN VARCHAR2 DEFAULT '4',
217 web_host_name IN VARCHAR2 DEFAULT NULL,
218 web_agent_name IN VARCHAR2 DEFAULT NULL,
219 delete_flag IN VARCHAR2 DEFAULT 'N'
220 )
221 IS
222 namebuf VARCHAR2(100);
223 application_id NUMBER DEFAULT '';
224 data_group_id NUMBER DEFAULT '';
225 data_group_application_id NUMBER DEFAULT '';
226 menu_id NUMBER DEFAULT '';
227 request_group_id NUMBER DEFAULT '';
228 group_application_id NUMBER DEFAULT '';
229 dummy NUMBER;
230
231 BEGIN
232
233 -- Get application_id
234 SELECT a.application_id
235 INTO application_id
236 FROM fnd_application a
237 WHERE a.application_short_name = responsibility.application;
238
239 -- Delete if requested
240 IF (delete_flag <> 'N') THEN
241 -- Resps are never deleted. Set the end_date instead.
242 UPDATE fnd_responsibility
243 SET end_date = sysdate
244 WHERE responsibility_key = responsibility.responsibility_key;
245
246 -- Added for Function Security Cache Invalidation Project.
247 fnd_function_security_cache.update_resp(responsibility.responsibility_id,
248 responsibility.application_id);
249
250 RETURN;
251 END IF;
252
253 -- This is an insert/update.
254 -- Bump responsibility_name by prepending '@' if needed to avoid any
255 -- possible unique key violations.
256 namebuf := responsibility.responsibility_name;
257 LOOP
258 SELECT count(1)
259 INTO dummy
260 FROM fnd_responsibility_vl fr
261 WHERE fr.responsibility_name = namebuf
262 AND (fr.responsibility_id <> responsibility.responsibility_id
263 OR fr.application_id <> responsibility.application_id);
264
265 EXIT WHEN dummy = 0;
266
267 namebuf := '@'||substr(namebuf, 1, 79);
268 END LOOP;
269
270 -- Select all other hidden keys
271 -- Data group
272 SELECT dg.data_group_id, a.application_id
273 INTO data_group_id, data_group_application_id
274 FROM fnd_data_groups_standard_view dg, fnd_data_group_units dgu, fnd_application a
275 WHERE dg.data_group_name = responsibility.data_group_name
276 AND dg.data_group_id = dgu.data_group_id
280 -- Menu
277 AND dgu.application_id = a.application_id
278 AND a.application_short_name = responsibility.data_group_application;
279
281 SELECT m.menu_id
282 INTO menu_id
283 FROM fnd_menus m
284 WHERE m.menu_name = responsibility.menu_name;
285
286 -- Request group, if supplied
287 IF (request_group_name IS NOT NULL) THEN
288 SELECT rg.request_group_id, a.application_id
289 INTO request_group_id, group_application_id
290 FROM fnd_request_groups rg, fnd_application a
291 WHERE rg.request_group_name = responsibility.request_group_name
292 AND rg.application_id = a.application_id
293 AND a.application_short_name = responsibility.request_group_application;
294 END IF;
295
296 -- Select to decide if this is insert or update
297 BEGIN
298 SELECT responsibility_id
299 INTO dummy
300 FROM fnd_responsibility r
301 WHERE r.responsibility_key = responsibility.responsibility_key;
302 EXCEPTION
303 WHEN no_data_found THEN
304 -- Insert into base
305 INSERT INTO fnd_responsibility (
306 application_id,
307 responsibility_id,
308 responsibility_key,
309 creation_date,
310 created_by,
311 last_update_date,
312 last_updated_by,
313 last_update_login,
314 data_group_id,
315 data_group_application_id,
316 menu_id,
317 START_DATE,
318 end_date,
319 group_application_id,
320 request_group_id,
321 version,
322 web_host_name,
323 web_agent_name
324 )
325 VALUES (
326 responsibility.application_id,
327 responsibility.responsibility_id,
328 responsibility.responsibility_key,
329 sysdate,
330 1,
331 sysdate,
332 1,
333 0,
334 responsibility.data_group_id,
335 responsibility.data_group_application_id,
336 responsibility.menu_id,
337 responsibility.START_DATE,
338 responsibility.end_date,
339 responsibility.group_application_id,
340 responsibility.request_group_id,
341 responsibility.version,
342 responsibility.web_host_name,
343 responsibility.web_agent_name
344 );
345
346 -- Added for Function Security Cache Invalidation Project.
347 fnd_function_security_cache.insert_resp(responsibility.responsibility_id,
348 responsibility.application_id);
349
350 -- Insert into tl
351 INSERT INTO fnd_responsibility_tl (
352 application_id,
353 responsibility_id,
354 LANGUAGE,
355 responsibility_name,
356 description,
357 created_by,
358 creation_date,
359 last_updated_by,
360 last_update_date,
361 last_update_login,
362 source_lang
363 )
364 SELECT responsibility.application_id,
365 responsibility.responsibility_id,
366 l.language_code,
367 namebuf,
368 responsibility.description,
369 1,
370 sysdate,
371 1,
372 sysdate,
373 0,
374 userenv('LANG')
375 FROM fnd_languages l
376 WHERE l.installed_flag IN ('I', 'B');
377
378 RETURN;
379 END;
380
381 -- Update existing row
382 UPDATE fnd_responsibility r
383 SET responsibility_key = responsibility.responsibility_key,
384 START_DATE = responsibility.START_DATE,
385 end_date = responsibility.end_date,
386 data_group_id = responsibility.data_group_id,
387 data_group_application_id = responsibility.data_group_application_id,
388 menu_id = responsibility.menu_id,
389 request_group_id = responsibility.request_group_id,
390 group_application_id = responsibility.group_application_id,
391 version = responsibility.version,
392 web_host_name = responsibility.web_host_name,
393 web_agent_name = responsibility.web_agent_name
394 WHERE r.responsibility_id = responsibility.responsibility_id
395 AND r.application_id = responsibility.application_id;
396
397 -- Added for Function Security Cache Invalidation Project.
398 fnd_function_security_cache.update_resp(responsibility.responsibility_id,
399 responsibility.application_id);
400
401 -- Update TL
402 UPDATE fnd_responsibility_tl r
403 SET responsibility_name = namebuf,
404 description = responsibility.description
405 WHERE r.responsibility_id = responsibility.responsibility_id
406 AND r.application_id = responsibility.application_id
407 AND r.LANGUAGE = userenv('LANG');
408
409 END RESPONSIBILITY;
410
411 --
412 -- FORM_FUNCTION
413 -- Insert/update/delete a function.
414 --
415 -- IN:
416 -- function_name (KEY, REQUIRED) - Function developer key name
417 -- form_name - Name of form attached to function
418 -- (Use the actual form name, not the user name or title.)
419 -- parameters - Parameter string for the form
420 -- type - Type flag of the function
421 -- user_function_name (REQUIRED) - User name of function
422 -- (in current language)
423 -- description - Description of function
424 -- delete_flag (REQUIRED) - Delete mode (see package comments)
425 --
426 -- CHILD REFERENCES: (Delete is cascaded to ...)
427 -- Function Security Exclusion Rules
428 --
429 -- FOREIGN REFERENCES: (Delete prevented if referenced in ...)
430 -- GUI Menu Entry
431 -- Attachments
432 -- 2.3 Menu Entry
433 --
434 PROCEDURE FORM_FUNCTION (
435 function_name IN VARCHAR2,
436 form_name IN VARCHAR2 DEFAULT '',
437 PARAMETERS IN VARCHAR2 DEFAULT '',
438 TYPE IN VARCHAR2 DEFAULT '',
442 )
439 user_function_name IN VARCHAR2 DEFAULT '',
440 description IN VARCHAR2 DEFAULT '',
441 delete_flag IN VARCHAR2 DEFAULT 'N'
443 IS
444 curlang VARCHAR2(30);
445 namebuf VARCHAR2(80);
446 form_id NUMBER DEFAULT '';
447 application_id NUMBER DEFAULT '';
448 function_id NUMBER DEFAULT '';
449 dummy NUMBER;
450
451 CURSOR RESP_FUNC IS
452 SELECT APPLICATION_ID, RESPONSIBILITY_ID
453 FROM FND_RESP_FUNCTIONS
454 WHERE rule_type = 'F'
455 AND action_id = form_function.function_id;
456
457 BEGIN
458 BEGIN
459 SELECT function_id
460 INTO form_function.function_id
461 FROM fnd_form_functions
462 WHERE function_name = form_function.function_name;
463 EXCEPTION
464 WHEN no_data_found THEN
465 function_id := -1;
466 END;
467
468 -- Delete if requested
469 IF (delete_flag <> 'N') THEN
470 -- Check for foreign key references
471 IF (delete_flag <> 'F') THEN
472 BEGIN
473 SELECT 1
474 INTO dummy
475 FROM sys.dual
476 WHERE NOT EXISTS
477 (SELECT 1
478 FROM fnd_menu_entries me
479 WHERE me.function_id = form_function.function_id);
480
481 SELECT 1
482 INTO dummy
483 FROM sys.dual
484 WHERE NOT EXISTS
485 (SELECT 1
486 FROM fnd_attachment_functions af
487 WHERE af.function_type = 'F'
488 AND af.function_id = form_function.function_id);
489
490 EXCEPTION
491 WHEN no_data_found THEN
492 RETURN;
493 END;
494 END IF;
495
496 DELETE FROM fnd_form_functions
497 WHERE function_id = form_function.function_id;
498
499 -- Added for Function Security Cache Invalidation Project.
500 fnd_function_security_cache.delete_function(form_function.function_id);
501
502 DELETE FROM fnd_form_functions_tl
503 WHERE function_id = form_function.function_id;
504
505 -- Cascade deletes to resp functions
506 DELETE FROM fnd_resp_functions rf
507 WHERE rf.rule_type = 'F'
508 AND rf.action_id = form_function.function_id;
509
510 -- Added for Function Security Cache Invalidation Project.
511 -- Seems that I need make sure that each responsibility excluded is taken into account.
512 FOR rs IN RESP_FUNC LOOP
513 fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
514 END LOOP;
515
516 RETURN;
517 END IF;
518
519 -- This is an insert/update.
520 -- Bump user_name by prepending '@' if needed to avoid any
521 -- possible unique key violations.
522 namebuf := form_function.user_function_name;
523 LOOP
524 SELECT count(1)
525 INTO dummy
526 FROM fnd_form_functions_vl ff
527 WHERE ff.user_function_name = namebuf
528 AND ff.function_id <> form_function.function_id;
529
530 EXIT WHEN dummy = 0;
531
532 namebuf := '@'||substr(namebuf, 1, 79);
533 END LOOP;
534
535 -- Get form ids if form name supplied
536 IF (form_name IS NOT NULL) THEN
537 SELECT f.form_id, f.application_id
538 INTO form_function.form_id, form_function.application_id
539 FROM fnd_form f
540 WHERE f.form_name = form_function.form_name;
541 END IF;
542
543 curlang := fnd_global.current_language;
544
545 -- Decide if this is insert or update
546 IF (function_id = -1) THEN
547
548 SELECT fnd_form_functions_s.NEXTVAL
549 INTO form_function.function_id
550 FROM dual;
551
552 -- Insert into base
553 INSERT INTO fnd_form_functions (
554 function_id,
555 function_name,
556 application_id,
557 form_id,
558 PARAMETERS,
559 creation_date,
560 created_by,
561 last_update_date,
562 last_updated_by,
563 last_update_login,
564 TYPE)
565 SELECT
566 form_function.function_id,
567 form_function.function_name,
568 form_function.application_id,
569 form_function.form_id,
570 form_function.PARAMETERS,
571 sysdate,
572 1,
573 sysdate,
574 1,
575 1,
576 form_function.TYPE
577 FROM sys.dual;
578
579 -- Added for Function Security Cache Invalidation Project.
580 fnd_function_security_cache.insert_function(form_function.function_id);
581
582 -- Insert into _TL
583 INSERT INTO fnd_form_functions_tl (
584 LANGUAGE,
585 function_id,
586 user_function_name,
587 description,
588 creation_date,
589 created_by,
590 last_update_date,
591 last_updated_by,
592 last_update_login,
593 source_lang)
594 SELECT
595 l.language_code,
596 f.function_id,
597 form_function.namebuf,
598 form_function.description,
599 sysdate,
600 1,
601 sysdate,
602 1,
603 1,
604 userenv('LANG')
605 FROM fnd_form_functions f, fnd_languages l
606 WHERE f.function_name = form_function.function_name
607 AND l.installed_flag IN ('I', 'B');
608
612 -- Update base
609 RETURN;
610 END IF;
611
613 UPDATE fnd_form_functions SET
614 application_id = form_function.application_id,
615 form_id = form_function.form_id,
616 PARAMETERS = form_function.PARAMETERS,
617 last_update_date = sysdate,
618 last_updated_by = 1,
619 last_update_login = 1,
620 TYPE = form_function.TYPE
621 WHERE function_id = form_function.function_id;
622
623 -- Added for Function Security Cache Invalidation Project.
624 fnd_function_security_cache.update_function(form_function.function_id);
625
626 -- Update TL
627 UPDATE fnd_form_functions_tl SET
628 user_function_name = form_function.namebuf,
629 description = form_function.description,
630 last_update_date = sysdate,
631 last_updated_by = 1,
632 last_update_login = 1,
633 source_lang = userenv('LANG')
634 WHERE function_id = form_function.function_id
635 AND userenv('LANG') IN (LANGUAGE, source_lang);
636 END FORM_FUNCTION;
637
638 --
639 -- MENU
640 -- Insert/update/delete a menu.
641 --
642 -- IN:
643 -- menu_name (KEY, REQUIRED) - Menu developer key
644 -- user_menu_name (REQUIRED) - Menu user name (in current language)
645 -- description - Menu description (in current language)
646 -- delete_flag (REQUIRED) - Delete mode (see package comments)
647 --
648 -- CHILD REFERENCES: (Delete is cascaded to ...)
649 -- Menu Entry (entries of this menu)
650 -- Function Security Exclusion Rules
651 --
652 -- FOREIGN REFERENCES: (Delete prevented if referenced in ...)
653 -- Menu Entry (as a submenu of another menu) (see note)
654 -- Responsibility (as the main menu of a responsibility)
655 --
656 -- NOTE:
657 -- To delete an entire menu tree, delete the top level menu of the tree
658 -- first, then work down to the leaves one level at a time to avoid
659 -- invalidating foreign references along the way.
660 --
661 PROCEDURE MENU (
662 menu_name IN VARCHAR2,
663 user_menu_name IN VARCHAR2 DEFAULT '',
664 description IN VARCHAR2 DEFAULT '',
665 delete_flag IN VARCHAR2 DEFAULT 'N'
666 )
667 IS
668 curlang VARCHAR2(30);
669 namebuf VARCHAR2(80);
670 menu_id NUMBER DEFAULT '';
671 dummy NUMBER;
672
673 CURSOR RESP_FUNC IS
674 SELECT APPLICATION_ID, RESPONSIBILITY_ID
675 FROM FND_RESP_FUNCTIONS
676 WHERE rule_type = 'M'
677 AND action_id = menu.menu_id;
678
679 CURSOR MN_ENTRY is
680 SELECT sub_menu_id, function_id
681 FROM fnd_menu_entries
682 WHERE menu_id = menu.menu_id;
683
684 BEGIN
685
686 BEGIN
687 SELECT menu_id
688 INTO menu.menu_id
689 FROM fnd_menus
690 WHERE menu_name = menu.menu_name;
691 EXCEPTION
692 WHEN no_data_found THEN
693 menu_id := -1;
694 END;
695
696 -- Delete if requested
697 IF (delete_flag <> 'N') THEN
698 -- Check for foreign key references
699 IF (delete_flag <> 'F') THEN
700 BEGIN
701 SELECT 1
702 INTO dummy
703 FROM sys.dual
704 WHERE NOT EXISTS
705 (SELECT 1
706 FROM fnd_menu_entries me
707 WHERE me.sub_menu_id = menu.menu_id);
708
709 SELECT 1
710 INTO dummy
711 FROM sys.dual
712 WHERE NOT EXISTS
713 (SELECT 1
714 FROM fnd_responsibility r
715 WHERE r.menu_id = menu.menu_id);
716
717 EXCEPTION
718 WHEN no_data_found THEN
719 RETURN;
720 END;
721 END IF;
722
723 DELETE FROM fnd_menus
724 WHERE menu_id = menu.menu_id;
725
726 -- Added for Function Security Cache Invalidation Project.
727 fnd_function_security_cache.delete_menu(menu.menu_id);
728
729 DELETE FROM fnd_menus_tl
730 WHERE menu_id = menu.menu_id;
731
732 -- Cascade delete to menu entries and resp functions
733 DELETE FROM fnd_menu_entries
734 WHERE menu_id = menu.menu_id;
735
736 -- Added for Function Security Cache Invalidation Project.
737 -- Need make sure that each menu entry deleted is taken into account.
738 FOR mn IN MN_ENTRY LOOP
739 fnd_function_security_cache.delete_menu_entry(menu.menu_id,
740 mn.sub_menu_id, mn.function_id);
741 END LOOP;
742
743 DELETE FROM fnd_menu_entries_tl
744 WHERE menu_id = menu.menu_id;
745
746 DELETE FROM fnd_resp_functions rf
747 WHERE rf.rule_type = 'M'
748 AND rf.action_id = menu.menu_id;
749
750 -- Added for Function Security Cache Invalidation Project.
751 -- Need make sure that each responsibility updated is taken into account.
752 FOR rs IN RESP_FUNC LOOP
753 fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
754 END LOOP;
755
756 RETURN;
757 END IF;
758
759 curlang := fnd_global.current_language;
760
761 -- This is an insert/update.
762 -- Bump responsibility_name by prepending '@' if needed to avoid any
763 -- possible unique key violations.
764 namebuf := menu.user_menu_name;
765 LOOP
766 SELECT count(1)
767 INTO dummy
768 FROM fnd_menus_vl fm
769 WHERE fm.user_menu_name = namebuf
770 AND fm.menu_id <> menu.menu_id;
771
772 EXIT WHEN dummy = 0;
773
774 namebuf := '@'||substr(namebuf, 1, 79);
775 END LOOP;
776
777 -- Select to decide if this is insert or update
778 IF (menu_id = -1) THEN
779
783
780 SELECT fnd_menus_s.NEXTVAL
781 INTO menu.menu_id
782 FROM dual;
784 -- Insert into base
785 INSERT INTO fnd_menus (
786 menu_id,
787 menu_name,
788 creation_date,
789 created_by,
790 last_update_date,
791 last_updated_by,
792 last_update_login
793 )
794 SELECT menu.menu_id,
795 menu.menu_name,
796 sysdate,
797 1,
798 sysdate,
799 1,
800 1
801 FROM sys.dual;
802
803
804 -- Added for Function Security Cache Invalidation Project.
805 fnd_function_security_cache.insert_menu(menu.menu_id);
806
807 -- Insert into _TL
808 INSERT INTO fnd_menus_tl (
809 LANGUAGE,
810 menu_id,
811 user_menu_name,
812 description,
813 creation_date,
814 created_by,
815 last_update_date,
816 last_updated_by,
817 last_update_login,
818 source_lang
819 )
820 SELECT l.language_code,
821 f.menu_id,
822 menu.namebuf,
823 menu.description,
824 sysdate,
825 1,
826 sysdate,
827 1,
828 1,
829 userenv('LANG')
830 FROM fnd_menus f, fnd_languages l
831 WHERE f.menu_name = menu.menu_name
832 AND l.installed_flag IN ('I', 'B');
833
834 RETURN;
835 END IF;
836
837 -- Update base -- nothing updatable in base table
838
839 -- Update TL
840 UPDATE fnd_menus_tl
841 SET user_menu_name = menu.namebuf,
842 description = menu.description,
843 last_update_date = sysdate,
844 last_updated_by = 1,
845 last_update_login = 1,
846 source_lang = userenv('LANG')
847 WHERE menu_id = menu.menu_id
848 AND userenv('LANG') IN (LANGUAGE, source_lang);
849
850 -- Added for Function Security Cache Invalidation Project
851 FND_FUNCTION_SECURITY_CACHE.update_menu(menu.menu_id);
852
853 END MENU;
854
855 --
856 -- MENU_ENTRY
857 -- Insert/update/delete an individual menu entry.
858 --
859 -- IN:
860 -- menu_name (KEY, REQUIRED) - Menu developer key
861 -- entry_sequence - Sequence number (see note below)
862 -- prompt - Entry prompt (in current language)
863 -- sub_menu_name (KEY) - Developer key name of submenu
864 -- function_name (KEY) - Developer key name of function
865 -- description - Entry description (in current language)
866 -- delete_flag (REQUIRED) - Delete mode (see package comments)
867 --
868 -- CHILD REFERENCES: (Delete is cascaded to ...)
869 -- (none)
870 -- FOREIGN REFERENCES: (Delete prevented if referenced in ...)
871 -- (none)
872 --
873 -- NOTE:
874 -- Menu entries are identified by the triple of menu_name, sub_menu_name,
875 -- and function_name, not by entry_sequence. The entry_sequence argument is
876 -- used only when inserting a new entry.
877 --
878 PROCEDURE MENU_ENTRY (
879 menu_name IN VARCHAR2,
880 entry_sequence IN NUMBER,
881 PROMPT IN VARCHAR2 DEFAULT '',
882 sub_menu_name IN VARCHAR2 DEFAULT '',
883 function_name IN VARCHAR2 DEFAULT '',
884 description IN VARCHAR2 DEFAULT '',
885 delete_flag IN VARCHAR2 DEFAULT 'N'
886 )
887 IS
888 curlang VARCHAR2(30);
889 menu_id NUMBER DEFAULT '';
890 sub_menu_id NUMBER DEFAULT '';
891 function_id NUMBER DEFAULT '';
892 l_entry_sequence NUMBER DEFAULT '';
893
894 BEGIN
895 curlang := fnd_global.current_language;
896
897 -- Get menu_id
898 SELECT menu_id
899 INTO menu_entry.menu_id
900 FROM fnd_menus
901 WHERE menu_name = menu_entry.menu_name;
902
903 -- Get sub_menu_id
904 IF (sub_menu_name IS NOT NULL) THEN
905 SELECT menu_id
906 INTO menu_entry.sub_menu_id
907 FROM fnd_menus
908 WHERE menu_name = menu_entry.sub_menu_name;
909 END IF;
910
911 -- Get function_id
912 IF (function_name IS NOT NULL) THEN
913 SELECT function_id
914 INTO menu_entry.function_id
915 FROM fnd_form_functions
916 WHERE function_name = menu_entry.function_name;
917 END IF;
918
919 -- Find local entry sequence matching submenu/function pair.
920 BEGIN
921 SELECT fme.entry_sequence
922 INTO l_entry_sequence
923 FROM fnd_menu_entries fme
924 WHERE fme.menu_id = menu_entry.menu_id
925 AND nvl(fme.sub_menu_id, -1) = nvl(menu_entry.sub_menu_id, -1)
926 AND nvl(fme.function_id, -1) = nvl(menu_entry.function_id, -1);
927 EXCEPTION
928 WHEN no_data_found THEN
929 -- Submenu/function not found. Use argument.
930 l_entry_sequence := entry_sequence;
931 END;
932
933 -- Delete if requested
934 IF (delete_flag = 'Y') THEN
935
936 -- Determine the correct sub_menu_id and function_id using the menu_id
937 -- and entry sequence before deleting. It may not be safe to use the
938 -- previous values determined above since those values may have failed
939 -- in the matching test previous to this code.
940 SELECT sub_menu_id, function_id
941 INTO menu_entry.sub_menu_id, menu_entry.function_id
942 FROM fnd_menu_entries
943 WHERE menu_id = menu_entry.menu_id
944 AND entry_sequence = l_entry_sequence;
945
946 DELETE FROM fnd_menu_entries
947 WHERE menu_id = menu_entry.menu_id
948 AND entry_sequence = l_entry_sequence;
949
950 -- Added for Function Security Cache Invalidation Project.
951 -- If menu_id exists, then sub_menu_id and function_id also exist and has already been
952 -- derived above.
953 fnd_function_security_cache.delete_menu_entry(menu_entry.menu_id,
954 menu_entry.sub_menu_id, menu_entry.function_id);
955
959
956 DELETE FROM fnd_menu_entries_tl
957 WHERE menu_id = menu_entry.menu_id
958 AND entry_sequence = l_entry_sequence;
960 RETURN;
961 END IF;
962
963 -- Select to decide if this is insert or update
964 BEGIN
965 SELECT menu_id
966 INTO menu_entry.menu_id
967 FROM fnd_menu_entries
968 WHERE menu_id = menu_entry.menu_id
969 AND entry_sequence = l_entry_sequence;
970 EXCEPTION
971 WHEN no_data_found THEN
972 -- Insert into base
973 INSERT INTO fnd_menu_entries (
974 menu_id,
975 entry_sequence,
976 sub_menu_id,
977 function_id,
978 creation_date,
979 created_by,
980 last_update_date,
981 last_updated_by,
982 last_update_login
983 )
984 VALUES (
985 menu_entry.menu_id,
986 menu_entry.entry_sequence,
987 menu_entry.sub_menu_id,
988 menu_entry.function_id,
989 sysdate,
990 1,
991 sysdate,
992 1,
993 1
994 );
995
996 -- Added for Function Security Cache Invalidation Project.
997 fnd_function_security_cache.insert_menu_entry(menu_entry.menu_id,
998 menu_entry.sub_menu_id, menu_entry.function_id);
999
1000 -- Insert into _TL
1001 INSERT INTO fnd_menu_entries_tl (
1002 LANGUAGE,
1003 menu_id,
1004 entry_sequence,
1005 PROMPT,
1006 description,
1007 creation_date,
1008 created_by,
1009 last_update_date,
1010 last_updated_by,
1011 last_update_login,
1012 source_lang
1013 )
1014 SELECT l.language_code,
1015 menu_entry.menu_id,
1016 menu_entry.entry_sequence,
1017 menu_entry.PROMPT,
1018 menu_entry.description,
1019 sysdate,
1020 1,
1021 sysdate,
1022 1,
1023 1,
1024 userenv('LANG')
1025 FROM fnd_languages l
1026 WHERE l.installed_flag IN ('I', 'B');
1027
1028 RETURN;
1029 END;
1030
1031 -- Update base
1032 UPDATE fnd_menu_entries
1033 SET sub_menu_id = menu_entry.sub_menu_id,
1034 function_id = menu_entry.function_id,
1035 last_update_date = sysdate,
1036 last_updated_by = 1,
1037 last_update_login = 1
1038 WHERE menu_id = menu_entry.menu_id
1039 AND entry_sequence = l_entry_sequence;
1040
1041 -- Added for Function Security Cache Invalidation Project.
1042 fnd_function_security_cache.update_menu_entry(menu_entry.menu_id,
1043 menu_entry.sub_menu_id, menu_entry.function_id);
1044
1045 -- Update TL
1046 UPDATE fnd_menu_entries_tl
1047 SET PROMPT = menu_entry.PROMPT,
1048 description = menu_entry.description,
1049 last_update_date = sysdate,
1050 last_updated_by = 1,
1051 last_update_login = 1,
1052 source_lang = userenv('LANG')
1053 WHERE menu_id = menu_entry.menu_id
1054 AND entry_sequence = l_entry_sequence
1055 AND userenv('LANG') IN (LANGUAGE, source_lang);
1056
1057 END MENU_ENTRY;
1058
1059 --
1060 -- SECURITY_RULE
1061 -- Insert/update/delete a function security exclusion rule.
1062 --
1063 -- IN:
1064 -- responsibility_key (KEY, REQUIRED) - Key of responsibility owning rule
1065 -- rule_type (KEY, REQUIRED) - Rule type
1066 -- 'F' = Function exclusion
1067 -- 'M' = Menu exclusion
1068 -- rule_name (KEY, REQUIRED) - Rule name
1069 -- Function developer key name (if rule_type = 'F')
1070 -- Menu developer key name (if rule_type = 'M')
1071 -- delete_flag (REQUIRED) - Delete mode (see package comments)
1072 --
1073 -- CHILD REFERENCES: (Delete is cascaded to ...)
1074 -- (none)
1075 -- FOREIGN REFERENCES: (Delete prevented if referenced in ...)
1076 -- (none)
1077 --
1078 PROCEDURE SECURITY_RULE (
1079 responsibility_key IN VARCHAR2,
1080 rule_type IN VARCHAR2 DEFAULT 'F', /* F = Function, M = Menu */
1081 rule_name IN VARCHAR2, /* Function_name or menu_name */
1082 delete_flag IN VARCHAR2 DEFAULT 'N')
1083 IS
1084 curlang VARCHAR2(30);
1085 responsibility_id NUMBER;
1086 application_id NUMBER;
1087 action_id NUMBER;
1088 BEGIN
1089 curlang := fnd_global.current_language;
1090
1091 -- Get responsibility ids
1092 SELECT fr.responsibility_id, fr.application_id
1093 INTO security_rule.responsibility_id, security_rule.application_id
1094 FROM fnd_responsibility fr
1095 WHERE fr.responsibility_key = security_rule.responsibility_key;
1096
1097 -- Get action id
1098 IF (rule_type = 'F') THEN
1099 SELECT function_id
1100 INTO security_rule.action_id
1101 FROM fnd_form_functions
1102 WHERE function_name = security_rule.rule_name;
1103 ELSE
1104 SELECT menu_id
1105 INTO security_rule.action_id
1106 FROM fnd_menus
1107 WHERE menu_name = security_rule.rule_name;
1108 END IF;
1109
1110 -- Delete if requested
1111 IF (delete_flag = 'Y') THEN
1112 DELETE FROM fnd_resp_functions
1113 WHERE responsibility_id = security_rule.responsibility_id
1114 AND application_id = security_rule.application_id
1115 AND rule_type = security_rule.rule_type
1116 AND action_id = security_rule.action_id;
1117
1118 -- Added for Function Security Cache Invalidation Project.
1119 fnd_function_security_cache.update_resp(security_rule.responsibility_id, security_rule.application_id);
1120
1121 RETURN;
1122 END IF;
1123
1124 -- Must always be an insert - nothing to update
1125 INSERT INTO fnd_resp_functions (
1126 application_id,
1127 responsibility_id,
1128 action_id,
1132 last_update_login,
1129 rule_type,
1130 last_update_date,
1131 last_updated_by,
1133 creation_date,
1134 created_by)
1135 VALUES (
1136 security_rule.application_id,
1137 security_rule.responsibility_id,
1138 security_rule.action_id,
1139 security_rule.rule_type,
1140 sysdate,
1141 1,
1142 1,
1143 sysdate,
1144 1);
1145
1146 -- Added for Function Security Cache Invalidation Project.
1147 fnd_function_security_cache.update_resp(security_rule.responsibility_id,
1148 security_rule.application_id);
1149
1150 -- No TL table
1151
1152 END SECURITY_RULE;
1153
1154 --
1155 -- UPDATE_FUNCTION_NAME
1156 -- This procedure updates the developer key of an existing function.
1157 -- The function with name oldname is located, and the name updated to newname.
1158 -- If a function already exists with name newname, it is deleted in favor of
1159 -- the updated row with oldname.
1160 --
1161 -- IN:
1162 -- oldname (REQUIRED) - old function developer name
1163 -- newname (REQUIRED) - new function developer name
1164 --
1165 -- NOTES:
1166 -- The user is responsible for making sure all references to the
1167 -- function in forms, code, etc, are updated to the new name.
1168 -- Under normal circumstances developer keys should never be changed. This
1169 -- procedure should only be used for new functions not in general use, or to
1170 -- fix bugs caused by inconsistent data created in previous patches.
1171 --
1172 PROCEDURE UPDATE_FUNCTION_NAME (
1173 oldname IN VARCHAR2,
1174 newname IN VARCHAR2
1175 )
1176 IS
1177 oldid NUMBER;
1178 newid NUMBER;
1179
1180 CURSOR MNU_ENTRY IS
1181 SELECT MENU_ID, ENTRY_SEQUENCE, SUB_MENU_ID
1182 FROM FND_MENU_ENTRIES
1183 WHERE FUNCTION_ID = newid;
1184
1185 CURSOR RESP_FUNC IS
1186 SELECT APPLICATION_ID, RESPONSIBILITY_ID
1187 FROM FND_RESP_FUNCTIONS
1188 WHERE rule_type = 'F'
1189 AND action_id = newid;
1190
1191 BEGIN
1192 -- Find which function names have already been changed
1193 BEGIN
1194 SELECT FUNCTION_ID
1195 INTO oldid
1196 FROM FND_FORM_FUNCTIONS
1197 WHERE FUNCTION_NAME = oldname;
1198 EXCEPTION
1199 WHEN no_data_found THEN
1200 oldid := -1;
1201 END;
1202
1203 BEGIN
1204 SELECT FUNCTION_ID
1205 INTO newid
1206 FROM FND_FORM_FUNCTIONS
1207 WHERE FUNCTION_NAME = newname;
1208 EXCEPTION
1209 WHEN no_data_found THEN
1210 newid := -1;
1211 END;
1212
1213 -- If neither exists then do nothing
1214 IF ((oldid = -1) AND (newid = -1)) THEN
1215 RETURN;
1216 END IF;
1217
1218 -- If only newname exists, already done
1219 IF ((oldid = -1) AND (newid <> -1)) THEN
1220 RETURN;
1221 END IF;
1222
1223 -- If only oldname exists, only update oldname to newname
1224 IF ((oldid <> -1) AND (newid = -1)) THEN
1225 UPDATE FND_FORM_FUNCTIONS
1226 SET FUNCTION_NAME = newname
1227 WHERE FUNCTION_NAME = oldname;
1228
1229 -- Added for Function Security Cache Invalidation Project.
1230 fnd_function_security_cache.update_function(oldid);
1231
1232 RETURN;
1233 END IF;
1234
1235 -- If both oldname and newname exist, then
1236 -- 1. Reset FKs to all point to old row
1237 -- 2. Delete new row
1238 -- 3. Update oldname to newname in old row
1239
1240 -- 1. Reset Fks to all point to old row
1241 UPDATE FND_MENU_ENTRIES
1242 SET FUNCTION_ID = oldid
1243 WHERE FUNCTION_ID = newid;
1244
1245 -- Added for Function Security Cache Invalidation Project.
1246 -- Need make sure that each menu entry updated is taken into account.
1247 FOR mn IN MNU_ENTRY LOOP
1248 fnd_function_security_cache.update_menu_ENTRY(mn.menu_id, mn.sub_menu_id, newid);
1249 END LOOP;
1250
1251 UPDATE FND_RESP_FUNCTIONS
1252 SET ACTION_ID = oldid
1253 WHERE RULE_TYPE = 'F'
1254 AND ACTION_ID = newid;
1255
1256 -- Added for Function Security Cache Invalidation Project.
1257 -- Need make sure that each responsibility updated is taken into account.
1258 FOR rs IN RESP_FUNC LOOP
1259 fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
1260 END LOOP;
1261
1262 UPDATE FND_ATTACHMENT_FUNCTIONS
1263 SET FUNCTION_ID = oldid
1264 WHERE FUNCTION_TYPE = 'F'
1265 AND FUNCTION_ID = newid;
1266
1267 -- 2. Delete new row
1268 DELETE FROM FND_FORM_FUNCTIONS
1269 WHERE FUNCTION_ID = newid;
1270
1271 -- Added for Function Security Cache Invalidation Project.
1272 fnd_function_security_cache.update_function(newid);
1273
1274 DELETE FROM FND_FORM_FUNCTIONS_TL
1275 WHERE FUNCTION_ID = newid;
1276
1277 -- 3. Update oldname to newname in old row
1278 UPDATE FND_FORM_FUNCTIONS
1279 SET FUNCTION_NAME = newname
1280 WHERE FUNCTION_ID = oldid;
1281
1282 -- Added for Function Security Cache Invalidation Project.
1283 fnd_function_security_cache.update_function(oldid);
1284
1285 END UPDATE_FUNCTION_NAME;
1286
1287 --
1288 -- UPDATE_MENU_NAME
1289 -- This procedure updates the developer key of an existing menu.
1290 -- The menu with name oldname is located, and the name updated to newname.
1291 -- If a menu already exists with name newname, it is deleted in favor of
1292 -- the updated row with oldname.
1293 --
1294 -- IN:
1295 -- oldname (REQUIRED) - old menu developer name
1296 -- newname (REQUIRED) - new menu developer name
1297 --
1298 -- NOTES:
1299 -- The user is responsible for making sure all references to the
1300 -- menu in forms, code, etc, are updated to the new name.
1301 -- Under normal circumstances developer keys should never be changed. This
1302 -- procedure should only be used for new menus not in general use, or to
1303 -- fix bugs caused by inconsistent data created in previous patches.
1304 --
1305 PROCEDURE UPDATE_MENU_NAME (
1306 oldname IN VARCHAR2,
1307 newname IN VARCHAR2
1308 )
1309 IS
1310 oldid NUMBER;
1311 newid NUMBER;
1312
1313 CURSOR RESP_CURSOR IS
1314 SELECT RESPONSIBILITY_ID, APPLICATION_ID
1315 FROM FND_RESPONSIBILITY
1316 WHERE MENU_ID = oldid;
1317
1318 CURSOR RESP_FUNC IS
1319 SELECT APPLICATION_ID, RESPONSIBILITY_ID
1320 FROM FND_RESP_FUNCTIONS
1321 WHERE rule_type = 'M'
1322 AND action_id = newid;
1323
1324 CURSOR mn_entry IS
1325 SELECT SUB_MENU_ID, FUNCTION_ID
1326 FROM FND_MENU_ENTRIES
1327 WHERE MENU_ID = newid;
1328
1329 BEGIN
1330 -- Find which menu names have already been changed
1331 BEGIN
1332 SELECT MENU_ID
1333 INTO oldid
1334 FROM FND_MENUS
1335 WHERE MENU_NAME = oldname;
1336 EXCEPTION
1337 WHEN no_data_found THEN
1338 oldid := -1;
1339 END;
1340
1341 BEGIN
1342 SELECT MENU_ID
1343 INTO newid
1344 FROM FND_MENUS
1345 WHERE MENU_NAME = newname;
1346 EXCEPTION
1347 WHEN no_data_found THEN
1348 newid := -1;
1349 END;
1350
1351 -- If neither exists then do nothing
1352 IF ((oldid = -1) AND (newid = -1)) THEN
1353 RETURN;
1354 END IF;
1355
1356 -- If only newname exists, already done
1357 IF ((oldid = -1) AND (newid <> -1)) THEN
1358 RETURN;
1359 END IF;
1360
1361 -- If only oldname exists, only update oldname to newname
1362 IF ((oldid <> -1) AND (newid = -1)) THEN
1363 UPDATE FND_MENUS
1364 SET MENU_NAME = newname
1365 WHERE MENU_NAME = oldname;
1366
1367 -- Added for Function Security Cache Invalidation Project.
1368 fnd_function_security_cache.update_menu(oldid);
1369
1370 RETURN;
1371 END IF;
1372
1373 -- If both oldname and newname exist, then
1374 -- 1. Reset FKs to all point to old row
1375 -- 2. Delete new row
1376 -- 3. Update oldname to newname in old row
1377
1378 -- 1. Reset Fks to all point to old row
1379 UPDATE FND_MENU_ENTRIES
1380 SET SUB_MENU_ID = oldid
1381 WHERE MENU_ID = newid;
1382
1383 -- Added for Function Security Cache Invalidation Project.
1384 -- Need make sure that each menu entry updated is taken into account.
1385 FOR mn IN mn_entry LOOP
1386 fnd_function_security_cache.update_menu_entry(newid, mn.sub_menu_id, mn.function_id);
1387 END LOOP;
1388
1389 UPDATE FND_RESPONSIBILITY
1390 SET MENU_ID = oldid
1391 WHERE MENU_ID = newid;
1392
1393 -- Added for Function Security Cache Invalidation Project.
1394 -- Need make sure that each responsibility updated is taken into account.
1395 FOR rs IN RESP_CURSOR LOOP
1396 fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
1397 END LOOP;
1398
1399 UPDATE FND_MENU_ENTRIES
1400 SET MENU_ID = oldid
1401 WHERE MENU_ID = newid;
1402
1403 -- Added for Function Security Cache Invalidation Project.
1404 -- Need make sure that each menu entry updated is taken into account.
1405 FOR mn IN mn_entry LOOP
1406 fnd_function_security_cache.update_menu_entry(newid, mn.sub_menu_id, mn.function_id);
1407 END LOOP;
1408
1409 UPDATE FND_MENU_ENTRIES_TL
1410 SET MENU_ID = oldid
1411 WHERE MENU_ID = newid;
1412
1413 UPDATE FND_RESP_FUNCTIONS
1414 SET ACTION_ID = oldid
1415 WHERE RULE_TYPE = 'M'
1416 AND ACTION_ID = newid;
1417
1418 -- Added for Function Security Cache Invalidation Project.
1419 -- Seems that I need make sure that each responsibility updated is taken into account.
1420 FOR rs IN RESP_FUNC LOOP
1421 fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
1422 END LOOP;
1423
1424 -- 2. Delete new row
1425 DELETE FROM FND_MENUS
1426 WHERE MENU_ID = newid;
1427
1428 -- Added for Function Security Cache Invalidation Project.
1429 fnd_function_security_cache.update_menu(newid);
1430
1431 DELETE FROM FND_MENUS_TL
1432 WHERE MENU_ID = newid;
1433
1434 -- 3. Update oldname to newname in old row
1435 UPDATE FND_MENUS
1436 SET MENU_NAME = newname
1437 WHERE MENU_ID = oldid;
1438
1439 -- Added for Function Security Cache Invalidation Project.
1440 fnd_function_security_cache.update_menu(oldid);
1441
1442 END UPDATE_MENU_NAME;
1443
1444 END FND_FUNCTION_SECURITY;
[Home] [Help]