1 package body FND_FUNCTION as
2 /* $Header: AFSCFNSB.pls 120.11 2011/12/05 12:28:21 srinnakk ship $ */
3
4 C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_FUNCTION';
5 C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_FUNCTION.';
6
7 /* These column values mark a menu as uncompiled. */
8 /* These will be stored in the FUNCTION_ID and GRANT_FLAG to indicate */
9 /* that a particular menu has been modified and needs to be recompiled */
10 C_INVALID_MENU_VAL CONSTANT NUMBER := -99999;
11 C_INVALID_GRANT_VAL CONSTANT VARCHAR2(1) := 'U'; /*U stands for Uncompiled */
12
13 /* These column values mark a menu as blank (yielding no compilation). */
14 /* These will be stored in the FUNCTION_ID and GRANT_FLAG to indicate */
15 /* that the corresponding FND_MENU row doesn't produce any information */
16 /* when compiled. */
17 /* Some possible reasons: The menu isn't used by any menu entries, or */
18 /* the menu doesn't contain any functions */
19 C_BLANK_MENU_VAL CONSTANT NUMBER := -99990;
20 C_BLANK_GRANT_VAL CONSTANT VARCHAR2(1) := 'B'; /* B stands for Blank */
21
22 /*
23 ** Lock ID for coordinating compilations (2004/10/22)
24 */
25 C_MENU_LOCK_ID constant NUMBER := 20041022;
26
27 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
28
29 /* Bulk collects are a feature that will have the benefit of increasing */
30 /* performance and hopefully reducing I/O reads on dev115 */
31 /* But the problem is they cause random failures in 8.1.6.1 databases */
32 /* so we can't use them before 8.1.7.1. (due to database bug 1688232).*/
33 /* So we will detect the database version and set this flag to one of: */
34 /* 'UNKNOWN'- Flag needs to be initialized by call to init routine */
35 /* 'TRUE' - supported */
36 /* 'FALSE'- unsupported */
37 /* Once 8.1.7.1+ is required for all Apps customers then this hack */
38 /* Can be gotten rid of, and this can be hardcoded to 'TRUE' */
39 /* 2/03- TM- That time is now... we now require 8.1.7.1+.*/
40 G_BULK_COLLECTS_SUPPORTED VARCHAR2(30) := 'TRUE';
41
42 /* This table stores marks while being called from row level db triggers,*/
43 /* because those row level db triggers can't actually mark the menu */
44 /* That involves reading the FND_MENU_ENTRIES table, which is considered */
45 /* a mutating table. So we store the marks here and then process them in*/
46 /* the after statement trigger. */
47 TBL_QUEUED_MENU_ID NUMBER_TABLE_TYPE;
48 TBL_QUEUED_MENU_ID_MAX NUMBER := 0;
49
50
51 /* This table stores the list of menus being visited as we */
52 /* Compile menus down recursively. */
53 /* This is basically a list that has the parents of a particular menu */
54 /* and it is maintained by pushing and popping items on it as we go down */
55 /* and up the menu hierarchy. If the current menu is also on its list */
56 /* of parents, then we know we've found an infinite recursion. */
57 TBL_RECURS_DETEC_MENU_ID NUMBER_TABLE_TYPE;
58 TBL_RECURS_DETEC_MENU_ID_MAX NUMBER := 0;
59
60 /* This constant is used for recursion detection in the fallback */
61 /* runtime menu scan. We keep track of how many items are on the menu,
62 /* and assume if the number of entries on the current */
63 /* menu is too high then it's caused by recursion. */
64 C_MAX_MENU_ENTRIES CONSTANT pls_integer := 10000;
65
66 /* This simple cache will avoid the need to find which menu is on */
67 /* the current responsibility with SQL every time. We just store */
68 /* the menu around after we get it for the current resp. */
69 P_LAST_RESP_ID NUMBER := -1;
70 P_LAST_RESP_APPL_ID NUMBER := -1;
71 P_LAST_MENU_ID NUMBER := -1;
72
73 g_func_id_cache NUMBER := NULL;
74 -- modified for bug#5395351
75 g_func_name_cache fnd_form_functions.function_name%type := NULL;
76
77
78 /* AVAILABILITY - This function compares the MAINTENANCE_MODE_SUPPORT
79 ** of a particular function to the APPS_MAINTENANCE_MODE
80 ** profile value and determines whether the function is
81 ** available during that maintenance phase.
82 **
83 ** in: MAINTENANCE_MODE_SUPPORT- the value from the database column
84 **
85 ** out: 'Y'= available, 'N'= not available
86 */
87 function AVAILABILITY(MAINTENANCE_MODE_SUPPORT in varchar2) return varchar2 is
88 apps_maintenance_mode varchar2(255);
89 retval boolean;
90 l_api_name CONSTANT VARCHAR2(30) := 'AVAILABILITY';
91 begin
92 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
93 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
94 c_log_head || l_api_name || '.begin',
95 c_pkg_name || '.' ||l_api_name|| '(' ||
96 'MAINTENANCE_MODE_SUPPORT =>'|| MAINTENANCE_MODE_SUPPORT
97 ||');');
98 end if;
99
100 apps_maintenance_mode := FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE');
101
102 if(fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
103 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
104 c_log_head || l_api_name || '.got_mode',
105 'profile APPS_MAINTENANCE_MODE is set to value:'
106 || apps_maintenance_mode);
107 end if;
108
109 -- This following if statement is added to support the new function
110 -- mantenance_mode_support 'OFFLINE'
111 -- Note that we will always preventing function access if is OFFLINE.
112 -- RSheh : I did not want to rewrite the whole thing below so I just
113 -- added the following if statement to support OFFLINE
114 if (MAINTENANCE_MODE_SUPPORT = 'OFFLINE') then
115 retval := FALSE;
116 else
117 -- Original code before adding support for OFFLINE
118 retval := TRUE;
119 if (apps_maintenance_mode is NULL) then
120 retval := TRUE; /* Feature not yet supported, so ignore. */
121 elsif (apps_maintenance_mode = 'NORMAL') then
122 retval := TRUE; /* In normal mode everything is supported */
123 elsif (apps_maintenance_mode = 'DISABLED') then
124 retval := FALSE; /* In normal mode nothing is supported */
125 elsif (apps_maintenance_mode = 'MAINT') then
126 if ( (MAINTENANCE_MODE_SUPPORT = 'MAINT')
127 OR (MAINTENANCE_MODE_SUPPORT = 'QUERY')
128 OR (MAINTENANCE_MODE_SUPPORT = 'FUZZY')) then
129 retval := TRUE;
130 elsif (MAINTENANCE_MODE_SUPPORT = 'NONE') then
131 retval := FALSE;
132 else
133 retval := TRUE; /* Mode not supported so ignore */
134 end if;
135 elsif (apps_maintenance_mode = 'FUZZY') then
136 if (MAINTENANCE_MODE_SUPPORT = 'FUZZY') then
137 retval := TRUE;
138 elsif ( (MAINTENANCE_MODE_SUPPORT = 'NONE')
139 OR (MAINTENANCE_MODE_SUPPORT = 'QUERY')
140 OR (MAINTENANCE_MODE_SUPPORT = 'MAINT')) then
141 retval := FALSE;
142 else
143 retval := TRUE;
144 end if;
145 else
146 /* Unrecognized value for APPS_MAINTENANCE_MODE profile... */
147 /* assume the best and allow access. */
148 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
149 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
150 c_log_head || l_api_name || '.unrecognized_mode',
151 'Unrecognized value for profile APPS_MAINTENANCE_MODE:'
152 || apps_maintenance_mode);
153 end if;
154 retval := TRUE;
155 end if;
156 end if; /* If statement for supporting OFFLINE */
157
158 if (retval) then
159 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
160 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
161 c_log_head || l_api_name || '.end_true',
162 'returning Y');
163 end if;
164 return 'Y';
165 else
166 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
167 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
168 c_log_head || l_api_name || '.end_false',
169 'returning N');
170 end if;
171 return 'N';
172 end if;
173
174 end;
175
176
177 -- BULK_COLLECTS_SUPPORTED
178 -- This temporary routine determines whether bulk collects are supported.
179 -- See comments around G_BULK_COLLECTS_SUPPORTED above
180 --
181 function BULK_COLLECTS_SUPPORTED return boolean is
182 ver varchar2(255);
183 begin
184 begin
185 if (G_BULK_COLLECTS_SUPPORTED = 'TRUE') then
186 return TRUE;
187 elsif (G_BULK_COLLECTS_SUPPORTED = 'FALSE') then
188 return FALSE;
189 elsif (G_BULK_COLLECTS_SUPPORTED = 'UNKNOWN') then
190 select version
191 into ver
192 from v$instance
193 where rownum = 1;
194
195 if(ver >= '8.1.7.1.0') then
196 G_BULK_COLLECTS_SUPPORTED := 'TRUE';
197 return (TRUE);
198 else
199 G_BULK_COLLECTS_SUPPORTED := 'FALSE';
200 return (FALSE);
201 end if;
202 else
203 return (FALSE); /* Should never happen */
204 end if;
205 exception
206 when others then
207 return FALSE; /* Should never happen */
208 end;
209 end BULK_COLLECTS_SUPPORTED;
210
211
212 -- PROCESS_MENU_TREE_DOWN_FOR_MN
213 -- Plow through the menu tree, processing exclusions and figuring
214 -- out which functions are accessible.
215 -- IN
216 -- p_menu_id - menu_id
217 -- p_function_id- function to check for
218 -- Don't pass values for the following two params if you don't want
219 -- exclusions processed.
220 -- p_appl_id - application id of resp
221 -- p_resp_id - responsibility id of current user
222 -- p_ignore_exclusions - passing 'Y' will ignore the exclusions.
223 --
224 -- RETURNS
225 -- TRUE if function is accessible
226 --
227 function PROCESS_MENU_TREE_DOWN_FOR_MN(
228 p_menu_id in number,
229 p_function_id in number,
230 p_appl_id in number,
231 p_resp_id in number) return boolean is
232
233 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MENU_TREE_DOWN_FOR_MN';
234
235 l_sub_menu_id number;
236
237 /* Table to store the list of submenus that we are looking for */
238 TYPE MENULIST_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
239 MENULIST MENULIST_TYPE;
240
241 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
242 TYPE VARCHAR2_TABLE_TYPE is table of VARCHAR2(1) INDEX BY BINARY_INTEGER;
243
244 /* The table of exclusions. The index in is the action_id, and the */
245 /* value stored in each element is the rule_type.*/
246 EXCLUSIONS VARCHAR2_TABLE_TYPE;
247
248 /* Returns from the bulk collect (fetches) */
249 TBL_MENU_ID NUMBER_TABLE_TYPE;
250 TBL_ENT_SEQ NUMBER_TABLE_TYPE;
251 TBL_FUNC_ID NUMBER_TABLE_TYPE;
252 TBL_SUBMNU_ID NUMBER_TABLE_TYPE;
253 TBL_GNT_FLG VARCHAR2_TABLE_TYPE;
254
255
256 /* Cursor to get exclusions */
257 cursor excl_c is
258 SELECT RULE_TYPE, ACTION_ID from fnd_resp_functions
259 where application_id = p_appl_id
260 and responsibility_id = p_resp_id;
261
262 /* Cursor to get menu entries on a particular menu.*/
263 cursor get_mnes_c is
264 SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG
265 from fnd_menu_entries
266 where MENU_ID = l_sub_menu_id;
267
268 menulist_cur pls_integer;
269 menulist_size pls_integer;
270
271 entry_excluded boolean;
272 last_index pls_integer;
273 i number;
274 z number;
275
276 begin
277 --
278 -- This routine processes the menu hierarchy and exclusion rules in PL/SQL
279 -- rather than in the database.
280 -- The basic algorithm of this routine is:
281 -- Populate the list of exclusions by selecting from FND_RESP_FUNCTIONS
282 -- menulist(1) = p_menu_id
283 -- while (elements on menulist)
284 -- {
285 -- Remove first element off menulist
286 -- if this menu is not excluded with a menu exclusion rule
287 -- {
288 -- Query all menu entry children of current menu
289 -- for (each child) loop
290 -- {
291 -- If it's excluded by a func exclusion rule, go on to the next one.
292 -- If we've got the function we're looking for,
293 -- and grant_flag = Y, we're done- return TRUE;
294 -- If it's got a sub_menu_id, add it to the end of menulist
295 -- to be processed
296 -- }
297 -- Move to next element on menulist
298 -- }
299 -- }
300 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
301 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
302 c_log_head || l_api_name || '.begin',
303 c_pkg_name || '.' ||l_api_name|| '(' ||
304 'p_menu_id =>'|| to_char(p_menu_id) ||
305 'p_function_id =>'|| to_char(p_function_id) ||
306 'p_appl_id =>'|| to_char(p_appl_id) ||
307 'p_resp_id =>'|| to_char(p_resp_id) ||');');
308 end if;
309
310 if(p_appl_id is not NULL) then
311 /* Select the list of exclusion rules into our cache */
312 for excl_rec in excl_c loop
313 EXCLUSIONS(excl_rec.action_id) := excl_rec.rule_type;
314 end loop;
315 end if;
316
317
318 -- Initialize menulist working list to parent menu
319 menulist_cur := 0;
320 menulist_size := 1;
321 menulist(0) := p_menu_id;
322
323 -- Continue processing until reach the end of list
327 c_log_head || l_api_name || '.proc_menulist',
324 while (menulist_cur < menulist_size) loop
325 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
326 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
328 'menulist_cur: ' || to_char(menulist_cur));
329 end if;
330
331 -- Check if recursion limit exceeded
332 if (menulist_cur > C_MAX_MENU_ENTRIES) then
333 fnd_message.set_name('FND', 'MENU-MENU LOOP');
334 fnd_message.set_token('MENU_ID', p_menu_id);
335 if (fnd_log.LEVEL_ERROR >= fnd_log.g_current_runtime_level) then
336 fnd_log.message(FND_LOG.LEVEL_ERROR,
337 c_log_head || l_api_name || '.recursion');
338 end if;
339
340 /* If the function were accessible from this menu, then we should */
341 /* have found it before getting to this point, so we are confident */
342 /* that the function is not on this menu. */
343 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
344 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
345 c_log_head || l_api_name || '.end_found_recur',
346 'returning FALSE');
347 end if;
348
349 return FALSE;
350 end if;
351
352 l_sub_menu_id := menulist(menulist_cur);
353
354 -- See whether the current menu is excluded or not.
355 entry_excluded := FALSE;
356 begin
357 if( (l_sub_menu_id is not NULL)
358 and (exclusions(l_sub_menu_id) = 'M')) then
359 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
360 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
361 c_log_head || l_api_name || '.menu_excl',
362 'l_sub_menu_id:' || l_sub_menu_id );
363 end if;
364 entry_excluded := TRUE;
365 end if;
366 exception
367 when no_data_found then
368 null;
369 end;
370
371 if (entry_excluded) then
372 last_index := 0; /* Indicate that no rows were returned */
373 else
374 /* This menu isn't excluded, so find out whats entries are on it. */
375
376 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
377 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
378 c_log_head || l_api_name || '.not_excl',
379 ' Following was not excluded: l_sub_menu_id: ' ||
380 to_char(l_sub_menu_id));
381 end if;
382
383 if (BULK_COLLECTS_SUPPORTED) then
384 open get_mnes_c;
385 fetch get_mnes_c bulk collect into tbl_menu_id, tbl_ent_seq,
386 tbl_func_id, tbl_submnu_id, tbl_gnt_flg;
387 close get_mnes_c;
388 -- See if we found any rows. If not set last_index to zero.
389 begin
390 if((tbl_menu_id.FIRST is NULL) or (tbl_menu_id.FIRST <> 1)) then
391 last_index := 0;
392 else
393 if (tbl_menu_id.FIRST is not NULL) then
394 last_index := tbl_menu_id.LAST;
395 else
396 last_index := 0;
397 end if;
398 end if;
399 exception
400 when others then
401 last_index := 0;
402 end;
403 else
404 z:= 0;
405 for rec in get_mnes_c loop
406 z := z + 1;
407 tbl_menu_id(z) := rec.MENU_ID;
408 tbl_ent_seq(z) := rec.ENTRY_SEQUENCE;
409 tbl_func_id(z) := rec.FUNCTION_ID;
410 tbl_submnu_id (z):= rec.SUB_MENU_ID;
411 tbl_gnt_flg(z) := rec.GRANT_FLAG;
412 end loop;
413 last_index := z;
414 end if;
415
416
417 end if; /* entry_excluded */
418
419 -- Process each of the child entries fetched
420 for i in 1 .. last_index loop
421 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
422 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
423 c_log_head || l_api_name || '.proc_child',
424 'Processing child of current menu. tbl_func_id(i):' ||
425 tbl_func_id(i) || ' tbl_submenu_id(i):'||
426 tbl_submnu_id(i));
427 end if;
428
429 -- Check if there is an exclusion rule for this entry
430 entry_excluded := FALSE;
431 begin
432 if( (tbl_func_id(i) is not NULL)
433 and (exclusions(tbl_func_id(i)) = 'F')) then
434 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
435 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
436 c_log_head || l_api_name || '.func_excl',
437 'tbl_func_id(i):' || tbl_func_id(i) );
438 end if;
439 entry_excluded := TRUE;
440 end if;
441 exception
442 when no_data_found then
443 null;
444 end;
445
446 -- Skip this entry if it's excluded
447 if (not entry_excluded) then
448 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
449 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
450 c_log_head || l_api_name || '.not_excl',
451 'Entry not excluded. Checking function:'
452 ||to_char(tbl_func_id(i))|| ' against p_function_id:'
453 ||to_char(p_function_id)||' where grant_flag='||tbl_gnt_flg(i));
454 end if;
455 -- Check if this is a matching function. If so, return success.
456 if( (tbl_func_id(i) = p_function_id)
457 and (tbl_gnt_flg(i) = 'Y'))
458 then
459 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
460 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
461 c_log_head || l_api_name || '.end_found',
462 'returning TRUE');
466
463 end if;
464 return TRUE;
465 end if;
467 -- If this is a submenu, then add it to the end of the
468 -- working list for processing.
469 if (tbl_submnu_id(i) is not NULL) then
470 menulist(menulist_size) := tbl_submnu_id(i);
471 menulist_size := menulist_size + 1;
472 end if;
473 end if; -- End if not excluded
474 end loop; -- For loop processing child entries
475
476 -- Advance to next menu on working list
477 menulist_cur := menulist_cur + 1;
478 end loop;
479
480 -- We couldn't find the function anywhere, so it's not available
481 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
482 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
483 c_log_head || l_api_name || '.end_not_found',
484 'returning FALSE');
485 end if;
486 return FALSE;
487 end PROCESS_MENU_TREE_DOWN_FOR_MN;
488
489
490 -- PROCESS_MENU_TREE_DOWN
491 -- Plow through the menu tree, processing exclusions and figuring
492 -- out which functions are accessible.
493 -- IN
494 -- p_appl_id - application id of resp
495 -- p_resp_id - responsibility id of current user
496 -- p_function_id- function to check for
497 --
498 -- RETURNS
499 -- TRUE if function is accessible
500 --
501 function PROCESS_MENU_TREE_DOWN(
502 p_appl_id in number,
503 p_resp_id in number,
504 p_function_id in number
505 ) return boolean is
506 l_menu_id NUMBER;
507 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MENU_TREE_DOWN';
508 begin
509 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
510 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
511 c_log_head || l_api_name || '.begin',
512 c_pkg_name || '.' ||l_api_name|| '(' ||
513 'p_appl_id=>'|| to_char(p_appl_id) ||
514 ', p_resp_id=>'|| p_resp_id ||
515 ', p_function_id=>'|| p_function_id||
516 ')');
517 end if;
518
519 if ( (P_LAST_RESP_ID = p_resp_id)
520 and (P_LAST_RESP_APPL_ID = p_appl_id)) then
521 /* If the cache is valid just use the cache */
522 l_menu_id := P_LAST_MENU_ID;
523 else
524 /* Find the root menu for this responsibility */
525 begin
526 select menu_id
527 into l_menu_id
528 from fnd_responsibility
529 where responsibility_id = p_resp_id
530 and application_id = p_appl_id;
531 /* Store the new value in the cache */
532 P_LAST_RESP_ID := p_resp_id;
533 P_LAST_RESP_APPL_ID := p_appl_id;
534 P_LAST_MENU_ID := l_menu_id;
535 exception
536 when no_data_found then
537 /* No menu for this resp, so there can't be any functions */
538
539 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
540 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
541 c_log_head || l_api_name || '.end_no_menu_resp',
542 'returning FALSE');
543 end if;
544 return FALSE;
545 end;
546 end if;
547 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
548 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
549 c_log_head || l_api_name || '.end',
550 'return call to PROCESS_MENU_TREE_DOWN_FOR_MN '||
551 ' l_menu_id = '||l_menu_id);
552 end if;
553 return PROCESS_MENU_TREE_DOWN_FOR_MN(l_menu_id, p_function_id,
554 p_appl_id, p_resp_id);
555 end PROCESS_MENU_TREE_DOWN;
556
557
558 -- MARK_MENU
559 -- Plow through the menu tree upwards, marking menus as we go up, so that
560 -- all the menus that include this menu will be marked. Stop when we reach
561 -- menus that have already been marked.
562 -- IN
563 -- menu_id - menu to mark
564 -- RETURNS
565 -- TRUE if success
566 --
567 procedure MARK_MENU_I(p_menu_id in number) is
568 pragma autonomous_transaction;
569 /*
570 ** Normally it would be bad to pragma this as autonomous since it gets called
571 ** from db triggers in transactions that can get rolled back. The author
572 ** claims it's harmless for this routine to have marked rows even if the
573 ** mark is for a rolled back change, but another problem with this scheme
574 ** is that this code won't see the changes that the surrounding transaction
575 ** is trying to commit. The author claims that the pragma should help
576 ** prevent deadlocks (change added for 3229888).
577 ** ### Is this still needed now that locking code has been added?
578 ** ### George thinks that it's best to have marks written and visible
579 ** ### as quickly as possible for situations such as data loading, so
580 ** ### for now the code is left as-is. In theory, though, this pragma
581 ** ### should be moved to MARK_MENU, the locking surround for this routine.
582 */
583 l_api_name CONSTANT VARCHAR2(30) := 'MARK_MENU';
584 l_menu_id number;
585 l_sub_menu_id number;
586 mark_existed boolean;
587
588 /* Menu Entry table record type */
589 TYPE MNE_REC_TYPE IS RECORD
590 (MENU_ID NUMBER,
591 ENTRY_SEQ NUMBER,
592 FUNCTION_ID NUMBER,
593 SUB_MENU_ID NUMBER,
594 GRANT_FLAG VARCHAR2(1));
595
596 /* Define the menu entry table type */
597 TYPE MNE_TYPE is table of MNE_REC_TYPE INDEX BY BINARY_INTEGER;
598
599 /* The actual menu entry tables */
600 CUR_MNES MNE_TYPE;
601 CUR_MNES_SIZE BINARY_INTEGER := 0;
602
603
604
605 /* Table to store the list of submenus that we are looking for */
606 TYPE MENULIST_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
607 MENULIST MENULIST_TYPE;
608
612 /* Returns from the bulk collect (fetches) */
609 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
610 TYPE VARCHAR2_TABLE_TYPE is table of VARCHAR2(1) INDEX BY BINARY_INTEGER;
611
613 TBL_MENU_ID NUMBER_TABLE_TYPE;
614 TBL_ENT_SEQ NUMBER_TABLE_TYPE;
615 TBL_FUNC_ID NUMBER_TABLE_TYPE;
616 TBL_SUBMNU_ID NUMBER_TABLE_TYPE;
617 TBL_GNT_FLG VARCHAR2_TABLE_TYPE;
618
619
620 /* Cursor to get menu entries that have a submenu*/
621 cursor get_mnes_w_sm_c is
622 SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG
623 from fnd_menu_entries
624 where SUB_MENU_ID = l_sub_menu_id;
625
626 entry_excluded boolean;
627 already_in_list boolean;
628 i number;
629 j number;
630 k number;
631 m number;
632 p number;
633 z number;
634 last_index pls_integer;
635
636
637 begin
638 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
639 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
640 c_log_head || l_api_name || '.begin',
641 c_pkg_name || '.' ||l_api_name || '(' ||
642 'p_menu_id=>'|| p_menu_id||');');
643 end if;
644 --
645 -- This routine processes the menu hierarchy upwards, marking on the way.
646 -- The basic algorithm of this routine is:
647 -- cur_mne(1).menu_id = menu passed in
648 -- loop
649 -- {
650 -- for each element of cur_mne
651 -- {
652 -- Mark this menu as uncompiled.
653 -- If it was already marked, go on to another one.
654 -- Select and add all the menu entries under its menu_id onto cur_mne
655 -- }
656 -- } until there are no more elements in cur_mne
657
658 /* We are going to add all the menu entries that have this function */
659 /* to the list, processing exclusion rules and checking them as we go. */
660 CUR_MNES_SIZE := 0;
661
662 CUR_MNES(CUR_MNES_SIZE).MENU_ID := p_menu_id;
663 CUR_MNES(CUR_MNES_SIZE).ENTRY_SEQ := 0;
664 CUR_MNES(CUR_MNES_SIZE).SUB_MENU_ID := 0;
665 CUR_MNES(CUR_MNES_SIZE).FUNCTION_ID := 0;
666 CUR_MNES(CUR_MNES_SIZE).GRANT_FLAG := 0;
667 CUR_MNES_SIZE := CUR_MNES_SIZE + 1;
668
669 /* Keep processing until there are no more menu entries in the list */
670 /* (or until we break out of this loop upon finding the menu) */
671 while (CUR_MNES_SIZE > 0) loop /* For each level */
672 i := 0;
673 m := 0;
674
675 /* Loop through all the menu entries on the current list */
676 while (i < CUR_MNES_SIZE) loop /* For each entry at the level */
677 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
678 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
679 c_log_head || l_api_name || '.proc_ent',
680 'Processing entry. i:' || to_char(i)||
681 ' CUR_MNES(i).MENU_ID:'|| to_char(CUR_MNES(i).MENU_ID) ||
682 ' CUR_MNES(i).ENTRY_SEQ:'|| to_char(CUR_MNES(i).ENTRY_SEQ) ||
683 ' CUR_MNES(i).SUB_MENU_ID:'|| to_char(CUR_MNES(i).SUB_MENU_ID) ||
684 ' CUR_MNES(i).FUNCTION_ID:'|| to_char(CUR_MNES(i).FUNCTION_ID) ||
685 ' CUR_MNES(i).GRANT_FLAG:'|| CUR_MNES(i).GRANT_FLAG);
686 end if;
687 begin
688 insert into fnd_compiled_menu_functions
689 (menu_id, function_id, grant_flag)
690 values
691 (CUR_MNES(i).MENU_ID, C_INVALID_MENU_VAL, C_INVALID_GRANT_VAL);
692 commit;
693 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
694 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
695 c_log_head || l_api_name || '.marked',
696 'Mark inserted.');
697 end if;
698 mark_existed := FALSE;
699 exception
700 when dup_val_on_index then
701 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
702 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
703 c_log_head || l_api_name || '.marked_existed',
704 'Mark was already there. ');
705 end if;
706 mark_existed := TRUE;
707 end;
708
709 if (not mark_existed) then
710 /* Put this on the list of menus we will get */
711 if(CUR_MNES(i).MENU_ID is not NULL) then
712 MENULIST(m) := CUR_MNES(i).MENU_ID;
713 m := m + 1;
714 end if;
715 end if;
716
717 i := i + 1;
718 end loop;
719
720 CUR_MNES_SIZE := 0;
721
722 /* Process the list of parent menuids */
723 p := 0;
724 while (p < m) loop
725 /* Get the list of menu entries above a particular submenu */
726 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
727 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
728 c_log_head || l_api_name || '.handle_menulist',
729 'Handling Menulist. p:' || to_char(p)||
730 ' MENULIST(p):'|| to_char(MENULIST(p)));
731 end if;
732 l_sub_menu_id := MENULIST(p);
733
734 if (BULK_COLLECTS_SUPPORTED) then
735 open get_mnes_w_sm_c;
736 fetch get_mnes_w_sm_c bulk collect into tbl_menu_id, tbl_ent_seq,
737 tbl_func_id, tbl_submnu_id, tbl_gnt_flg;
738 close get_mnes_w_sm_c;
739 -- See if we found any rows. If not set last_index to zero.
740 begin
741 if((tbl_menu_id.FIRST is NULL) or (tbl_menu_id.FIRST <> 1)) then
742 last_index := 0;
743 else
744 if (tbl_menu_id.FIRST is not NULL) then
745 last_index := tbl_menu_id.LAST;
746 else
747 last_index := 0;
748 end if;
749 end if;
750 exception
751 when others then
755 z:= 0;
752 last_index := 0;
753 end;
754 else
756 for rec in get_mnes_w_sm_c loop
757 z := z + 1;
758 tbl_menu_id(z) := rec.MENU_ID;
759 tbl_ent_seq(z) := rec.ENTRY_SEQUENCE;
760 tbl_func_id(z) := rec.FUNCTION_ID;
761 tbl_submnu_id (z):= rec.SUB_MENU_ID;
762 tbl_gnt_flg(z) := rec.GRANT_FLAG;
763 end loop;
764 last_index := z;
765 end if;
766
767
768 /* put those menu entries into the list for next time */
769 for q in 1..last_index loop
770 CUR_MNES(CUR_MNES_SIZE).MENU_ID := tbl_menu_id(q);
771 CUR_MNES(CUR_MNES_SIZE).ENTRY_SEQ := tbl_ent_seq(q);
772 CUR_MNES(CUR_MNES_SIZE).SUB_MENU_ID := tbl_submnu_id(q);
773 CUR_MNES(CUR_MNES_SIZE).FUNCTION_ID := tbl_func_id(q);
774 CUR_MNES(CUR_MNES_SIZE).GRANT_FLAG := tbl_gnt_flg(q);
775 CUR_MNES_SIZE := CUR_MNES_SIZE + 1;
776 end loop; /* for q in...*/
777 p := p + 1;
778 end loop; /* while (j < EXCLUSIONS_SIZE) loop */
779
780 end loop; /* while (i < CUR_MNES_SIZE) loop */
781
782 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
783 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
784 c_log_head || l_api_name || '.end',
785 'end');
786 end if;
787 end MARK_MENU_I;
788
789
790 -- ADD_QUEUED_MARKS-
791 -- Calls MARK_MENUS for all menus that we called QUEUE_MARK on.
792 --
793 procedure ADD_QUEUED_MARKS
794 is
795 l_api_name CONSTANT VARCHAR2(30) := 'ADD_QUEUED_MARKS';
796 RSTATUS number;
797 begin
798 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
799 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
800 c_log_head || l_api_name || '.begin',
801 c_pkg_name || '.' ||l_api_name);
802 end if;
803 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.S_MODE) = 0) then
804 begin
805 for i in 1..TBL_QUEUED_MENU_ID_MAX loop
806 MARK_MENU_I(TBL_QUEUED_MENU_ID(TBL_QUEUED_MENU_ID_MAX));
807 end loop;
808 -- Commit;
809 TBL_QUEUED_MENU_ID_MAX := 0;
810 exception when OTHERS then
811 null;
812 end;
813 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
814 end if;
815 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
816 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
817 c_log_head || l_api_name || '.end',
818 'return');
819 end if;
820 end;
821
822
823
824 -- QUEUE_MARK- store up a mark so it can later be processed. This is
825 -- normally only called from db triggers; other code can and should
826 -- just call mark_menu directly.
827 -- IN
828 -- p_menu_id - menu to mark
829 --
830 procedure QUEUE_MARK(
831 p_menu_id in number) is
832 l_api_name CONSTANT VARCHAR2(30) := 'QUEUE_MARK';
833 begin
834 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
835 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
836 c_log_head || l_api_name || '.begin',
837 c_pkg_name || '.' ||l_api_name ||'(' ||
838 'p_menu_id=>'|| p_menu_id||');');
839 end if;
840 if (TBL_QUEUED_MENU_ID_MAX <> 0)
841 AND (TBL_QUEUED_MENU_ID(TBL_QUEUED_MENU_ID_MAX) = p_menu_id) then
842 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
843 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
844 c_log_head || l_api_name || '.end_dupfound',
845 'return');
846 end if;
847 return; /* Dont make duplicate marks */
848 end if;
849
850 /* Store the mark away. */
851 TBL_QUEUED_MENU_ID_MAX := TBL_QUEUED_MENU_ID_MAX + 1;
852 TBL_QUEUED_MENU_ID(TBL_QUEUED_MENU_ID_MAX) := p_menu_id;
853
854 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
855 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
856 c_log_head || l_api_name || '.end',
857 'mark stored as TBL_QUEUED_MENU_ID_MAX: '||TBL_QUEUED_MENU_ID_MAX);
858 end if;
859 end;
860
861
862
863 -- MARK_MENU
864 -- Plow through the menu tree upwards, marking menus as we go up, so that
865 -- all the menus that include this menu will be marked. Stop when we reach
866 -- menus that have already been marked.
867 -- IN
868 -- menu_id - menu to mark
869 -- RETURNS
870 -- TRUE if success
871 --
872 procedure MARK_MENU(p_menu_id in number) is
873 RSTATUS number;
874 begin
875 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.S_MODE) = 0) then
876 begin
877 MARK_MENU_I(p_menu_id);
878 -- Commit;
879 exception when OTHERS then
880 null;
881 end;
882 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
883 end if;
884 end MARK_MENU;
885
886
887
888
889 -- TEST_ID_NO_GRANTS
890 -- Test if function id is accessible under current responsibility.
891 -- Looks only at the menus on current resp, not any grants.
892 -- IN
893 -- function_id - function id to test
894 -- MAINTENANCE_MODE_SUPPORT- the value from the column in fnd_form_functions
895 -- CONTEXT_DEPENDENCE- the value from the column in fnd_form_functions
896 -- TEST_MAINT_AVAILABILTY- 'Y' (default) means check if available for
897 -- current value of profile APPS_MAINTENANCE_MODE
898 -- 'N' means the caller is checking so it's
899 -- unnecessary to check.
900 -- RETURNS
901 -- TRUE if function is accessible
902 --
903 function TEST_ID_NO_GRANTS(function_id in number,
907 return boolean
904 MAINTENANCE_MODE_SUPPORT in varchar2,
905 CONTEXT_DEPENDENCE in varchar2,
906 TEST_MAINT_AVAILABILITY in varchar2)
908 is
909 l_api_name CONSTANT VARCHAR2(30) := 'TEST_ID_NO_GRANTS(4_ARGS)';
910 l_function_id number;
911 l_menu_id number;
912 dummy number;
913 l_resp_id number;
914 l_resp_appl_id number;
915 result boolean;
916 L_TEST_MAINT_AVAILABILITY boolean;
917 begin
918 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
919 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
920 c_log_head || l_api_name || '.begin',
921 c_pkg_name || '.' ||l_api_name|| '(' ||
922 'function_id =>'|| function_id ||
923 'MAINTENANCE_MODE_SUPPORT =>'|| MAINTENANCE_MODE_SUPPORT ||
924 'CONTEXT_DEPENDENCE =>'|| CONTEXT_DEPENDENCE ||
925 'TEST_MAINT_AVAILABILITY =>'|| TEST_MAINT_AVAILABILITY ||
926 ');');
927 end if;
928
929 l_function_id := function_id;
930 l_resp_id := Fnd_Global.Resp_Id;
931 l_resp_appl_id := Fnd_Global.Resp_Appl_Id;
932
933 if ( (TEST_MAINT_AVAILABILITY = 'Y')
934 OR (TEST_MAINT_AVAILABILITY is NULL)) then
935 L_TEST_MAINT_AVAILABILITY := TRUE;
936 else
937 L_TEST_MAINT_AVAILABILITY := FALSE;
938 end if;
939
940 begin
941 /* See if there are any exclusions */
942 select 1
943 into dummy
944 from fnd_resp_functions
945 where responsibility_id = l_resp_id
946 and application_id = l_resp_appl_id
947 and rownum = 1;
948
949 /* If we got here then there are exclusions, so don't use compiled */
950 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
951 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
952 c_log_head || l_api_name || '.end_excl',
953 'Because of exclusions, falling back, calling process_menu_tree_down.');
954 end if;
955
956 result := process_menu_tree_down(Fnd_Global.Resp_Appl_Id,
957 Fnd_Global.Resp_Id,
958 l_function_id);
959 if(result = FALSE) then
960 return FALSE;
961 else
962 if(L_TEST_MAINT_AVAILABILITY) then
963 if(AVAILABILITY(MAINTENANCE_MODE_SUPPORT) = 'Y') then
964 return TRUE;
965 else
966 return FALSE;
967 end if;
968 else
969 return TRUE;
970 end if;
971 end if;
972
973 exception
974 when no_data_found then
975 /* If we got here, there are no exclusions. */
976 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
977 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
978 c_log_head || l_api_name || '.not_excl',
979 'No exclusions, so finding menu');
980 end if;
981 if ( (P_LAST_RESP_ID = l_resp_id)
982 and (P_LAST_RESP_APPL_ID = l_resp_appl_id)) then
983 /* If the cache is valid just use the cache */
984 l_menu_id := P_LAST_MENU_ID;
985 else
986 /* Find the root menu for this responsibility */
987 begin
988 select menu_id
989 into l_menu_id
990 from fnd_responsibility
991 where responsibility_id = l_resp_id
992 and application_id = l_resp_appl_id;
993 /* Store the new value in the cache */
994 P_LAST_RESP_ID := l_resp_id;
995 P_LAST_RESP_APPL_ID := l_resp_appl_id;
996 P_LAST_MENU_ID := l_menu_id;
997 exception
998 when no_data_found then
999 /* No menu for this resp, so there can't be any functions */
1000 if (fnd_log.LEVEL_PROCEDURE >=
1001 fnd_log.g_current_runtime_level) then
1002 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1003 c_log_head || l_api_name || '.end_no_menu',
1004 'Couldnt find root menu for resp. returning FALSE ');
1005 end if;
1006 return FALSE;
1007 end;
1008 end if;
1009 end;
1010
1011 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1012 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1013 c_log_head || l_api_name || '.end',
1014 'Returning with call to is_function_on_menu; ');
1015 end if;
1016
1017 /* This is that call to actually do the test */
1018 result := IS_FUNCTION_ON_MENU(l_menu_id, l_function_id, TRUE);
1019
1020 if(result = FALSE) then
1021 return FALSE;
1022 else
1023 if(L_TEST_MAINT_AVAILABILITY) then
1024 if(AVAILABILITY(MAINTENANCE_MODE_SUPPORT) = 'Y') then
1025 return TRUE;
1026 else
1027 return FALSE;
1028 end if;
1029 else
1030 return TRUE;
1031 end if;
1032 end if;
1033
1034 end TEST_ID_NO_GRANTS;
1035
1036
1037 /* TEST_ID_SLOW- used for testing the security system. Note that this */
1038 /* code is no longer maintained and is actually out of date */
1039 /* because it doesn't consider MAINTENANCE_MODE_SUPPORT */
1040 function TEST_ID_SLOW(function_id in number) return boolean
1041 is
1042 l_api_name CONSTANT VARCHAR2(30) := 'TEST_ID';
1043 l_function_id number;
1044 begin
1045 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1046 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1047 c_log_head || l_api_name || '.begin',
1048 'function_id: '||function_id);
1049 end if;
1050
1051 l_function_id := function_id;
1052
1056 'Returning with call to process_menu_tree_down; ');
1053 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1054 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1055 c_log_head || l_api_name || '.end',
1057 end if;
1058 return process_menu_tree_down(Fnd_Global.Resp_Appl_Id,
1059 Fnd_Global.Resp_Id,
1060 l_function_id);
1061 end TEST_ID_SLOW;
1062
1063
1064 function IS_FUNCTION_ON_MENU(p_menu_id IN NUMBER,
1065 p_function_id IN NUMBER,
1066 p_check_grant_flag IN BOOLEAN)
1067 return boolean is
1068 l_api_name CONSTANT VARCHAR2(30) := 'IS_FUNCTION_ON_MENU';
1069
1070 dummy number;
1071 marked_as_not_compiled boolean;
1072 some_compiled_menus boolean;
1073 p_chk_gnt_as_vc varchar2(1);
1074 begin
1075 if (p_check_grant_flag) then
1076 p_chk_gnt_as_vc := 'Y';
1077 else
1078 p_chk_gnt_as_vc := 'N';
1079 end if;
1080
1081 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1082 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1083 c_log_head || l_api_name || '.begin',
1084 c_pkg_name || '.' ||l_api_name|| '(' ||
1085 'p_menu_id =>'|| to_char(p_menu_id) ||
1086 'p_check_grant_flag (as vc) =>'|| p_chk_gnt_as_vc || ');');
1087 end if;
1088
1089 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
1090 FND_FUNCTION.FAST_COMPILE;
1091 end if;
1092
1093 -- Check first if there are any compiled rows at all for the menu
1094 some_compiled_menus := FALSE;
1095 begin
1096 select 1
1097 into dummy
1098 from fnd_compiled_menu_functions
1099 where menu_id = p_menu_id
1100 and rownum = 1;
1101 /* If we got here, there are compiled rows */
1102 some_compiled_menus := TRUE;
1103
1104 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1105 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1106 c_log_head || l_api_name || '.compiled_rows',
1107 'There are compiled rows.');
1108 end if;
1109 exception
1110 when no_data_found then
1111 some_compiled_menus := FALSE;
1112 end;
1113
1114 -- If any compiled menus at all, see if any of those are invalid
1115 marked_as_not_compiled := FALSE;
1116 if (some_compiled_menus) then
1117 begin
1118 select 1
1119 into dummy
1120 from fnd_compiled_menu_functions
1121 where menu_id = p_menu_id
1122 and grant_flag = C_INVALID_GRANT_VAL
1123 and rownum = 1;
1124 /* If we got here we know this is marked as not compiled. */
1125 marked_as_not_compiled := TRUE;
1126 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1127 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1128 c_log_head || l_api_name || '.marked_as_not_comp',
1129 'Menu Marked as not compiled.');
1130 end if;
1131 exception
1132 when no_data_found then
1133 marked_as_not_compiled := FALSE;
1134 end;
1135 end if;
1136
1137 -- If there are any rows, AND none of the rows are invalid, then
1138 -- assume the menu must be compiled.
1139 if (some_compiled_menus and (not marked_as_not_compiled)) then
1140 begin
1141 if (p_check_grant_flag) then
1142 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1143 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1144 c_log_head || l_api_name || '.check_w_gflg',
1145 'Checking with grantflag for p_menu_id: '|| to_char(p_menu_id)
1146 ||' and p_function_id:'||to_char(p_function_id));
1147 end if;
1148
1149 select 1
1150 into dummy
1151 from fnd_compiled_menu_functions
1152 where menu_id = p_menu_id
1153 and function_id = p_function_id
1154 and grant_flag = 'Y' ;
1155 else
1156 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1157 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1158 c_log_head || l_api_name || '.check_wo_gflg',
1159 'Checking without grantflag for p_menu_id: '|| to_char(p_menu_id)
1160 ||' and p_function_id:'||to_char(p_function_id));
1161 end if;
1162
1163 select 1
1164 into dummy
1165 from fnd_compiled_menu_functions
1166 where menu_id = p_menu_id
1167 and function_id = p_function_id;
1168 end if;
1169
1170 /* If we got here that means we found the compiled row */
1171 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1172 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1173 c_log_head || l_api_name || '.end_comp_found',
1174 'found compiled row. returning TRUE');
1175 end if;
1176 return TRUE;
1177 exception
1178 when no_data_found then
1179 /* Not in compilation, so this function is not in the menu */
1180 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1181 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1182 c_log_head || l_api_name || '.end_comp_nfound',
1183 'found no compiled row. returning FALSE');
1184 end if;
1185 return FALSE;
1186 end;
1187 else
1188 /* Submit a concurrent request to compile the marked menus. */
1189 begin
1190 FND_JOBS_PKG.SUBMIT_MENU_COMPILE;
1191 exception
1192 when others then /* Don't error out if we can't submit the request.*/
1193 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1194 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1195 c_log_head || l_api_name || '.req_submit_fail',
1199
1196 'Could not submit concurrent request FNDSCMPI to recompile menus');
1197 end if;
1198 end;
1200 /* The menu is uncompiled so fall back to the full tree search */
1201 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1202 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1203 c_log_head || l_api_name || '.end',
1204 'uncompiled, fall back. return process_menu_tree_down_for_mn()');
1205 end if;
1206 return process_menu_tree_down_for_mn(p_menu_id, p_function_id,
1207 NULL, NULL);
1208 end if;
1209 end IS_FUNCTION_ON_MENU;
1210
1211
1212 function TEST_INSTANCE_ID_MAINTMODE(function_id in varchar2,
1213 object_name IN VARCHAR2,
1214 instance_pk1_value IN VARCHAR2,
1215 instance_pk2_value IN VARCHAR2,
1216 instance_pk3_value IN VARCHAR2,
1217 instance_pk4_value IN VARCHAR2,
1218 instance_pk5_value IN VARCHAR2,
1219 user_name IN VARCHAR2,
1220 MAINTENANCE_MODE_SUPPORT in varchar2,
1221 CONTEXT_DEPENDENCE in varchar2,
1222 TEST_MAINT_AVAILABILITY in varchar2
1223 ) return boolean is
1224 l_api_name CONSTANT VARCHAR2(30) := 'TEST_INSTANCE_ID_MAINTMODE';
1225 ret_val varchar2(1) := 'F';
1226 ret_bool boolean := FALSE;
1227 L_MAINTENANCE_MODE_SUPPORT varchar2(8) := NULL;
1228 L_CONTEXT_DEPENDENCE varchar2(8) := NULL;
1229 -- modified for bug#5395351
1230 l_function_name fnd_form_functions.function_name%type := NULL;
1231 begin
1232 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1233 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1234 c_log_head || l_api_name || '.begin',
1235 c_pkg_name || '.' ||l_api_name || '(' ||
1236 'function_id=>'|| function_id||
1237 ', object_name=>'|| object_name||
1238 ', instance_pk1_value=>'|| instance_pk1_value||
1239 ', instance_pk2_value=>'|| instance_pk2_value||
1240 ', instance_pk3_value=>'|| instance_pk3_value||
1241 ', instance_pk4_value=>'|| instance_pk4_value||
1242 ', instance_pk5_value=>'|| instance_pk5_value||
1243 ', user_name=>'|| nvl(user_name, '[NULL]')||
1244 ');');
1245 end if;
1246
1247
1248 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
1249 and ( (user_name <> SYS_CONTEXT('FND','USER_NAME'))
1250 or ( (user_name is not null)
1251 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
1252 /* In R12 we do not allow passing values other than */
1253 /* the current user name (which is the default), */
1254 /* so we raise a runtime exception if that deprecated */
1255 /* kind of call is made to this routine. */
1256 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1257 fnd_message.set_token('ROUTINE',
1258 c_pkg_name || '.'|| l_api_name);
1259 fnd_message.set_token('REASON',
1260 'Invalid API call. Parameter user_name: '||user_name||
1261 ' was passed to API '||c_pkg_name || '.TEST_INSTANCE' ||
1262 '. object_name: '||object_name||'. '||
1263 ' In Release 12 and beyond the user_name parameter '||
1264 'is unsupported, and any product team that passes it '||
1265 'must correct their code because it does not work '||
1266 'correctly. Please see the deprecated API document at '||
1267 'http://files.oraclecorp.com/content/AllPublic/'||
1268 'SharedFolders/ATG%20Requirements-Public/R12/'||
1269 'Requirements%20Definition%20Document/'||
1270 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
1271 'Oracle employees who encounter this error should log '||
1272 'a bug against the product that owns the call to this '||
1273 'routine, which is likely the owner of the object that '||
1274 'was passed to this routine: '||
1275 object_name);
1276 if (fnd_log.LEVEL_EXCEPTION >=
1277 fnd_log.g_current_runtime_level) then
1278 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1279 c_log_head || l_api_name || '.end_depr_param',
1280 FALSE);
1281 end if;
1282 fnd_message.raise_error;
1283 end if;
1284
1285
1286 -- Change function name to id
1287 begin
1288 select F.FUNCTION_NAME, F.MAINTENANCE_MODE_SUPPORT, F.CONTEXT_DEPENDENCE
1289 into l_function_name, L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE
1290 from FND_FORM_FUNCTIONS F
1291 where F.FUNCTION_ID = test_instance_id_maintmode.function_id;
1292 exception
1293 when no_data_found then
1294 -- Invalid function name
1295 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1296 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1297 c_log_head || l_api_name || '.end_notfound',
1298 'returning FALSE');
1299 end if;
1300 return(FALSE);
1301 end;
1302
1303
1304 if (TEST_ID_NO_GRANTS(function_id,
1305 L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE, 'N')) then
1306 ret_bool := TRUE;
1307 goto check_avail;
1308 end if;
1309
1310 if (object_name is not NULL) then
1311 ret_val := FND_DATA_SECURITY.CHECK_FUNCTION(p_api_version => 1.0,
1312 p_function => l_function_name,
1316 p_instance_pk3_value => instance_pk3_value,
1313 p_object_name => object_name,
1314 p_instance_pk1_value => instance_pk1_value,
1315 p_instance_pk2_value => instance_pk2_value,
1317 p_instance_pk4_value => instance_pk4_value,
1318 p_instance_pk5_value => instance_pk5_value,
1319 p_user_name => user_name);
1320 if (ret_val = 'T') then
1321 ret_bool := TRUE;
1322 goto check_avail;
1323 else
1324 if (ret_val = 'E') or (ret_val = 'U') then
1325 FND_MESSAGE.CLEAR;
1326 end if;
1327 ret_bool := FALSE;
1328 end if;
1329 end if;
1330
1331 if (ret_bool = FALSE) then /* Check global object type grant */
1332 ret_val := FND_DATA_SECURITY.check_global_object_type_grant
1333 (p_api_version => 1.0,
1334 p_function => l_function_name,
1335 p_user_name => user_name);
1336 if (ret_val = 'T') then
1337 ret_bool := TRUE;
1338 goto check_avail;
1339 else
1340 ret_bool := FALSE;
1341 goto all_done;
1342 end if;
1343 end if;
1344
1345
1346 <<check_avail>>
1347 if (ret_bool = TRUE) then
1348 if(AVAILABILITY(L_MAINTENANCE_MODE_SUPPORT) = 'Y') then
1349 ret_bool := TRUE;
1350 else
1351 ret_bool := FALSE;
1352 end if;
1353 end if;
1354
1355 <<all_done>>
1356
1357 if (ret_bool) then
1358 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1359 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1360 c_log_head || l_api_name || '.end_available',
1361 'returning TRUE;');
1362 end if;
1363 return TRUE;
1364 else
1365 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1366 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1367 c_log_head || l_api_name || '.end_false',
1368 'returning FALSE;');
1369 end if;
1370 return FALSE;
1371 end if;
1372
1373 end TEST_INSTANCE_ID_MAINTMODE;
1374
1375
1376 function TEST_INSTANCE_ID(function_id in varchar2,
1377 object_name IN VARCHAR2,
1378 instance_pk1_value IN VARCHAR2,
1379 instance_pk2_value IN VARCHAR2,
1380 instance_pk3_value IN VARCHAR2,
1381 instance_pk4_value IN VARCHAR2,
1382 instance_pk5_value IN VARCHAR2,
1383 user_name IN VARCHAR2
1384 ) return boolean is
1385 l_api_name CONSTANT VARCHAR2(30) := 'TEST_INSTANCE_ID';
1386 ret_val boolean := FALSE;
1387 L_MAINTENANCE_MODE_SUPPORT varchar2(8) := NULL;
1388 L_CONTEXT_DEPENDENCE varchar2(8) := NULL;
1389 begin
1390 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1391 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1392 c_log_head || l_api_name || '.begin',
1393 c_pkg_name || '.' ||l_api_name|| '(' ||
1394 'function_id =>'|| function_id ||
1395 ');');
1396 end if;
1397
1398 -- Change function name to id
1399 begin
1400 -- Bug 5059644. Modified bind variable name from function_id to
1401 -- TEST_INSTANCE_ID.function_id. This change is to avoid FTS.
1402 select F.MAINTENANCE_MODE_SUPPORT, F.CONTEXT_DEPENDENCE
1403 into L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE
1404 from FND_FORM_FUNCTIONS F
1405 where F.function_id = test_instance_id.function_id;
1406 exception
1407 when no_data_found then
1408 -- Invalid function name
1409 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1410 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1411 c_log_head || l_api_name || '.end_notfound',
1412 'returning FALSE');
1413 end if;
1414 return(FALSE);
1415 end;
1416
1417 ret_val := TEST_INSTANCE_ID_MAINTMODE(
1418 function_id,
1419 object_name,
1420 instance_pk1_value,
1421 instance_pk2_value,
1422 instance_pk3_value,
1423 instance_pk4_value,
1424 instance_pk5_value,
1425 user_name,
1426 L_MAINTENANCE_MODE_SUPPORT,
1427 L_CONTEXT_DEPENDENCE,
1428 'Y');
1429
1430 if (ret_val) then
1431 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1432 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1433 c_log_head || l_api_name || '.end_available',
1434 'returning TRUE;');
1435 end if;
1436 return TRUE;
1437 else
1438 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1439 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1440 c_log_head || l_api_name || '.end_false',
1441 'returning FALSE;');
1442 end if;
1443 return FALSE;
1444 end if;
1445
1446 end TEST_INSTANCE_ID;
1447
1448
1449 --
1450 -- TEST_INSTANCE
1451 -- Test if function is accessible under current resp and user, for
1452 -- the object instance (database row) which is the current instance.
1453 -- This actually checks both the function security
1454 -- and data security system as described at the oracle internal link:
1455 -- http://www-apps.us.oracle.com/atg/plans/r115x/datasec.txt
1456 -- Note that this takes account of global object type grants (grants
1460 -- Generally the user should pass the object_name and whichever
1457 -- that apply to all object types), as well as global object instance
1458 -- grants (which apply to all instances of a particular object type).
1459 --
1461 -- of the instance_pkX_values apply to that object.
1462 -- If the user does not pass the object_name param, then only global
1463 -- object type grants will get picked up from fnd_grants.
1464 -- If the user does not pass the instance_pkXvalues, but does pass
1465 -- object_name, only instance type grants will get picked up.
1466 --
1467 -- IN
1468 -- function_name - function to test
1469 -- object_name and pk values- object and primary key values of the current
1470 -- object.
1471 -- user_name- Normally the caller leaves this blank so it will test
1472 -- with the current FND user. But folks who populate their
1473 -- grants with special "compound" usernames might need
1474 -- to pass the grantee_key (user_name) of the current user.
1475 -- RETURNS
1476 -- TRUE if function is accessible
1477 -- FALSE if function is not accessible or if there was an error.
1478 --
1479 function TEST_INSTANCE(function_name in varchar2,
1480 object_name IN VARCHAR2,
1481 instance_pk1_value IN VARCHAR2,
1482 instance_pk2_value IN VARCHAR2,
1483 instance_pk3_value IN VARCHAR2,
1484 instance_pk4_value IN VARCHAR2,
1485 instance_pk5_value IN VARCHAR2,
1486 user_name IN VARCHAR2
1487 ) return boolean is
1488 l_api_name CONSTANT VARCHAR2(30) := 'TEST_INSTANCE';
1489 ret_val boolean := FALSE;
1490 function_id number;
1491 L_MAINTENANCE_MODE_SUPPORT varchar2(8);
1492 L_CONTEXT_DEPENDENCE varchar2(8);
1493 begin
1494 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1495 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1496 c_log_head || l_api_name || '.begin',
1497 c_pkg_name || '.' ||l_api_name|| '(' ||
1498 'function_name =>'|| function_name ||
1499 'object_name =>'|| object_name ||
1500 'instance_pk1_value =>'|| instance_pk1_value ||
1501 'instance_pk2_value =>'|| instance_pk2_value ||
1502 'instance_pk3_value =>'|| instance_pk3_value ||
1503 'instance_pk4_value =>'|| instance_pk4_value ||
1504 'instance_pk5_value =>'|| instance_pk5_value ||
1505 'user_name =>'|| user_name ||
1506 ');');
1507 end if;
1508
1509
1510 -- Change function name to id
1511 begin
1512 select F.FUNCTION_ID, F.MAINTENANCE_MODE_SUPPORT, F.CONTEXT_DEPENDENCE
1513 into function_id, L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE
1514 from FND_FORM_FUNCTIONS F
1515 where F.FUNCTION_NAME = test_instance.function_name;
1516 exception
1517 when no_data_found then
1518 -- Invalid function name
1519 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1520 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1521 c_log_head || l_api_name || '.end_notfound',
1522 'returning FALSE');
1523 end if;
1524 return(FALSE);
1525 end;
1526
1527 ret_val := TEST_INSTANCE_ID_MAINTMODE(
1528 function_id,
1529 object_name,
1530 instance_pk1_value,
1531 instance_pk2_value,
1532 instance_pk3_value,
1533 instance_pk4_value,
1534 instance_pk5_value,
1535 user_name,
1536 L_MAINTENANCE_MODE_SUPPORT,
1537 L_CONTEXT_DEPENDENCE,
1538 'Y');
1539
1540 if (ret_val) then
1541 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1542 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1543 c_log_head || l_api_name || '.end_available',
1544 'returning TRUE;');
1545 end if;
1546 return TRUE;
1547 else
1548 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1549 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1550 c_log_head || l_api_name || '.end_false',
1551 'returning FALSE;');
1552 end if;
1553 return FALSE;
1554 end if;
1555
1556 end TEST_INSTANCE;
1557
1558
1559 -- TEST_ID
1560 -- Test if function id is accessible under current responsibility.
1561 -- IN
1562 -- function_id - function id to test
1563 -- MAINTENANCE_MODE_SUPPORT- the value from the column in fnd_form_functions
1564 -- CONTEXT_DEPENDENCE- the value from the column in fnd_form_functions
1565 -- TEST_MAINT_AVAILABILTY- 'Y' (default) means check if available for
1566 -- current value of profile APPS_MAINTENANCE_MODE
1567 -- 'N' means the caller is checking so it's
1568 -- unnecessary to check.
1569 -- RETURNS
1570 -- TRUE if function is accessible
1571 --
1572 function TEST_ID(function_id in number,
1573 MAINTENANCE_MODE_SUPPORT in varchar2,
1574 CONTEXT_DEPENDENCE in varchar2,
1575 TEST_MAINT_AVAILABILITY in varchar2)
1576 return boolean
1577 is
1578 l_api_name CONSTANT VARCHAR2(30) := 'TEST_ID(4_ARGS)';
1579 result boolean;
1580 begin
1581 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1582 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1583 c_log_head || l_api_name || '.begin',
1584 c_pkg_name || '.' ||l_api_name|| '(' ||
1585 'function_id =>'|| function_id ||
1586 'MAINTENANCE_MODE_SUPPORT =>'|| MAINTENANCE_MODE_SUPPORT ||
1587 'CONTEXT_DEPENDENCE =>'|| CONTEXT_DEPENDENCE ||
1591
1588 'TEST_MAINT_AVAILABILITY =>'|| TEST_MAINT_AVAILABILITY ||
1589 ');');
1590 end if;
1592 result := TEST_INSTANCE_ID_MAINTMODE(
1593 function_id => function_id,
1594 object_name => NULL,
1595 instance_pk1_value => NULL,
1596 instance_pk2_value => NULL,
1597 instance_pk3_value => NULL,
1598 instance_pk4_value => NULL,
1599 instance_pk5_value => NULL,
1600 user_name => NULL,
1601 MAINTENANCE_MODE_SUPPORT =>MAINTENANCE_MODE_SUPPORT,
1602 CONTEXT_DEPENDENCE => CONTEXT_DEPENDENCE,
1603 TEST_MAINT_AVAILABILITY => TEST_MAINT_AVAILABILITY);
1604
1605 if (result) then
1606 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1607 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1608 c_log_head || l_api_name || '.end_available',
1609 'returning TRUE;');
1610 end if;
1611 return TRUE;
1612 else
1613 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1614 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1615 c_log_head || l_api_name || '.end_false',
1616 'returning FALSE;');
1617 end if;
1618 return FALSE;
1619 end if;
1620
1621 end TEST_ID;
1622
1623
1624
1625
1626
1627 -- TEST_ID
1628 -- Test if function id is accessible under current responsibility.
1629 -- IN
1630 -- function_id - function id to test
1631 -- RETURNS
1632 -- TRUE if function is accessible
1633 --
1634 function TEST_ID(function_id in number) return boolean
1635 is
1636 L_MAINTENANCE_MODE_SUPPORT varchar2(8);
1637 L_CONTEXT_DEPENDENCE varchar2(8);
1638 l_function_id number;
1639 l_api_name CONSTANT VARCHAR2(30) := 'TEST_ID(1_ARG)';
1640 begin
1641 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1642 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1643 c_log_head || l_api_name || '.begin',
1644 c_pkg_name || '.' ||l_api_name|| '(' ||
1645 'function_id =>'|| function_id || ');');
1646 end if;
1647
1648 L_MAINTENANCE_MODE_SUPPORT := NULL;
1649 L_CONTEXT_DEPENDENCE := NULL;
1650 l_function_id := function_id;
1651 begin
1652 /* Get the extra columns */
1653 select MAINTENANCE_MODE_SUPPORT, CONTEXT_DEPENDENCE
1654 into L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE
1655 from fnd_form_functions
1656 where function_id = l_function_id;
1657
1658 exception
1659 when no_data_found then
1660 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1661 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1662 c_log_head || l_api_name || '.end_bad_fnid',
1663 'function_id passed to test_id cant be found in fnd_form_functions.');
1664 end if;
1665 return FALSE; /* Bad function id passed */
1666 end;
1667
1668 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1669 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1670 c_log_head || l_api_name || '.end_call_3_arg',
1671 'Passing MAINTENANCE_MODE_SUPPORT and CONTEXT_DEPENDENCE to test_id().');
1672 end if;
1673
1674 return test_id(l_function_id,
1675 L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE, 'Y');
1676 end TEST_ID;
1677
1678
1679
1680 --
1681 -- TEST
1682 -- Test if function is accessible under current responsibility.
1683 -- IN
1684 -- function_name - function to test
1685 -- TEST_MAINT_AVAILABILTY- 'Y' (default) means check if available for
1686 -- current value of profile APPS_MAINTENANCE_MODE
1687 -- 'N' means the caller is checking so it's
1688 -- unnecessary to check.
1689 -- RETURNS
1690 -- TRUE if function is accessible
1691 --
1692 function TEST(function_name in varchar2,
1693 TEST_MAINT_AVAILABILITY in varchar2) return boolean
1694 is
1695 l_api_name CONSTANT VARCHAR2(30) := 'TEST';
1696 function_id number;
1697 L_MAINTENANCE_MODE_SUPPORT varchar2(8);
1698 L_CONTEXT_DEPENDENCE varchar2(8);
1699 begin
1700 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1701 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1702 c_log_head || l_api_name || '.begin',
1703 c_pkg_name || '.' ||l_api_name|| '(' ||
1704 'function_name =>'|| function_name || ');');
1705 end if;
1706
1707 -- Change function name to id
1708 begin
1709 select F.FUNCTION_ID, F.MAINTENANCE_MODE_SUPPORT, F.CONTEXT_DEPENDENCE
1710 into function_id, L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE
1711 from FND_FORM_FUNCTIONS F
1712 where F.FUNCTION_NAME = test.function_name;
1713 exception
1714 when no_data_found then
1715 -- Invalid function name
1716 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1717 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1718 c_log_head || l_api_name || '.end_notfound',
1719 'returning FALSE');
1720 end if;
1721 return(FALSE);
1722 end;
1723
1724 -- Call test_id to complete
1725 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1726 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1727 c_log_head || l_api_name || '.end',
1728 'returning call to test_id of function_id:'||function_id
1729 ||' L_MAINTENANCE_MODE_SUPPORT:'||L_MAINTENANCE_MODE_SUPPORT
1730 ||' L_CONTEXT_DEPENDENCE:'||L_CONTEXT_DEPENDENCE);
1731 end if;
1732 return(Test_Id(function_id,
1736
1733 L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE,
1734 TEST_MAINT_AVAILABILITY));
1735 end TEST;
1737 /* Note: The reason for having separate _I (internal) routines is so that*/
1738 /* the non- _I routines will always be top level routines and will never */
1739 /* call other top level routines. That way those top level routines can */
1740 /* be pragma autonomous_transaction. */
1741 /* If we didn't have it this way, we would have the situation where */
1742 /* COMPILE_ALL_FROM_SCRATCH calls COMPILE_ALL_MARKED, which calls */
1743 /* COMPILE_MENU_MARKED, and all those routines are */
1744 /* pragma autononous_transaction because they can be called independently.*/
1745 /* That would yield three nested autononous transactions which would at */
1746 /* minimum waste rollback segments and at maximum would cause weird behavior */
1747
1748 function COMPILE_MENU_MARKED_I(p_menu_id NUMBER,
1749 p_force in varchar2) return NUMBER;
1750 function COMPILE_ALL_FROM_SCRATCH_I return NUMBER;
1751 function COMPILE_ALL_MARKED_I(compile_missing in VARCHAR2)
1752 return NUMBER;
1753 procedure MARK_ALL_I;
1754
1755 /* COMPILE_I- This API is invoked by the concurrent program */
1756 procedure COMPILE_I( errbuf out NOCOPY varchar2,
1757 retcode out NOCOPY varchar2,
1758 everything in varchar2 /* 'Y'= from scratch*/) is
1759 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE';
1760 numrows NUMBER;
1761 msgbuf varchar2(2000);
1762 begin
1763 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1764 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1765 c_log_head || l_api_name || '.begin',
1766 c_pkg_name || '.' ||l_api_name ||'(' ||
1767 'everything=>'|| everything||');');
1768 end if;
1769
1770 if(everything = 'Y') then
1771 numrows := COMPILE_ALL_FROM_SCRATCH_I;
1772 fnd_message.set_name('FND', 'SECURITY_COMPILED_FROM_SCRATCH');
1773 msgbuf := fnd_message.get;
1774 FND_FILE.put_line(FND_FILE.log, msgbuf);
1775 else
1776 numrows := COMPILE_ALL_MARKED_I('Y');
1777 fnd_message.set_name('FND', 'SECURITY_COMPILED_MARKED_MENUS');
1778 msgbuf := fnd_message.get;
1779 FND_FILE.put_line(FND_FILE.log, msgbuf);
1780 end if;
1781
1782 fnd_message.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1783 fnd_message.set_token('ROWS', numrows);
1784 msgbuf := fnd_message.get;
1785 FND_FILE.put_line(FND_FILE.log, msgbuf);
1786
1787 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1788 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1789 c_log_head || l_api_name || '.end',
1790 'returning from compile. numrows='||numrows);
1791 end if;
1792 exception
1793 when others then
1794 errbuf := sqlerrm;
1795 retcode := '2';
1796 FND_FILE.put_line(FND_FILE.log,errbuf);
1797 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1798 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1799 c_log_head || l_api_name || '.end_exception',
1800 'returning from compile with exception. numrows='||numrows);
1801 end if;
1802 raise;
1803 end compile_I;
1804
1805 /* COMPILE- This API is invoked by the concurrent program */
1806 procedure COMPILE( errbuf out NOCOPY varchar2,
1807 retcode out NOCOPY varchar2,
1808 everything in varchar2 /* 'Y'= from scratch*/) is
1809 pragma autonomous_transaction;
1810 RSTATUS number;
1811 begin
1812 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.X_MODE) = 0) then
1813 begin
1814 COMPILE_I(errbuf, retcode, everything);
1815 exception when OTHERS then
1816 null;
1817 end;
1818 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
1819 end if;
1820 end;
1821
1822
1823 /* COMPILE_CHANGES - This API is invoked by the DBMS_SCHEDULER */
1824 procedure COMPILE_CHANGES is
1825 pragma autonomous_transaction;
1826 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE_CHANGES';
1827 numrows NUMBER;
1828 RSTATUS number;
1829 begin
1830 FND_JOBS_PKG.APPS_INITIALIZE_SYSADMIN;
1831
1832 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1833 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1834 c_log_head || l_api_name || '.begin',
1835 c_pkg_name || '.' ||l_api_name ||';');
1836 end if;
1837
1838 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.X_MODE) = 0) then
1839 begin
1840 numrows := COMPILE_ALL_MARKED_I('Y');
1841 exception when OTHERS then
1842 null;
1843 end;
1844 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
1845 end if;
1846
1847 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1848 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1849 c_log_head || l_api_name || '.end',
1850 'returning from compile. numrows='||numrows);
1851 end if;
1852
1853 exception
1854 when others then
1855 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1856 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1857 c_log_head || l_api_name || '.end_exception',
1858 'returning from compile with exception. numrows='||numrows);
1859 end if;
1860 -- ### Should log return code = 2 and sqlerrm buffer
1861 raise;
1862 end;
1863
1864
1865 -- COMPILE_ALL_FROM_SCRATCH_I-
1866 -- Recompiles everything from scratch.
1867 function COMPILE_ALL_FROM_SCRATCH_I return NUMBER is
1868 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE_ALL_FROM_SCRATCH';
1869 begin
1870 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1871 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1875 MARK_ALL_I;
1872 c_log_head || l_api_name || '.begin',
1873 c_pkg_name || '.' ||l_api_name );
1874 end if;
1876 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1877 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1878 c_log_head || l_api_name || '.end',
1879 'returning COMPILE_ALL_MARKED');
1880 end if;
1881 return COMPILE_ALL_MARKED_I('Y');
1882 end compile_all_from_scratch_I;
1883
1884 -- COMPILE_ALL_FROM_SCRATCH-
1885 -- Recompiles everything from scratch.
1886 function COMPILE_ALL_FROM_SCRATCH return NUMBER is
1887 pragma autonomous_transaction;
1888 RESULT_COUNT number;
1889 RSTATUS number;
1890 begin
1891 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.X_MODE) = 0) then
1892 begin
1893 RESULT_COUNT := COMPILE_ALL_FROM_SCRATCH_I;
1894 exception when OTHERS then
1895 null;
1896 end;
1897 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
1898 end if;
1899 return RESULT_COUNT;
1900 end;
1901
1902
1903
1904 -- MARK_MISSING-
1905 --
1906 -- Marks as uncompiled all the menus that don't have any rows
1907 -- in the compiled table.
1908 -- No locking needed because it's only called internally from
1909 -- a procedure that already has a lock.
1910 --
1911 procedure MARK_MISSING is
1912 l_api_name CONSTANT VARCHAR2(30) := 'MARK_MISSING';
1913
1914 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
1915 TBL_MENU_ID NUMBER_TABLE_TYPE;
1916
1917 last_index pls_integer;
1918
1919 /* Bug 5196541. Added sql hints to improve the performance */
1920 cursor get_missing_menus_c is
1921 select /*+ INDEX_FFS(menus) */ menu_id from fnd_menus menus
1922 where not exists
1923 (select 'X'
1924 from fnd_compiled_menu_functions cmf
1925 where menus.menu_id = cmf.menu_id);
1926 z number;
1927 begin
1928 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1929 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1930 c_log_head || l_api_name || '.begin',
1931 c_pkg_name || '.' ||l_api_name );
1932 end if;
1933
1934 /* Mark as uncompiled any menus that don't have a representation */
1935 /* in the compiled table */
1936
1937 if (BULK_COLLECTS_SUPPORTED) then
1938 open get_missing_menus_c;
1939 fetch get_missing_menus_c bulk collect into tbl_menu_id;
1940 close get_missing_menus_c;
1941
1942 -- See if we found any rows. If not set last_index to zero.
1943 begin
1944 if((tbl_menu_id.FIRST is NULL) or (tbl_menu_id.FIRST <> 1)) then
1945 last_index := 0;
1946 else
1947 if (tbl_menu_id.FIRST is not NULL) then
1948 last_index := tbl_menu_id.LAST;
1949 else
1950 last_index := 0;
1951 end if;
1952 end if;
1953 exception
1954 when others then
1955 last_index := 0;
1956 end;
1957 else
1958 z:= 0;
1959 for rec in get_missing_menus_c loop
1960 z := z + 1;
1961 tbl_menu_id(z) := rec.MENU_ID;
1962 end loop;
1963 last_index := z;
1964 end if;
1965
1966 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1967 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1968 c_log_head || l_api_name || '.got_list',
1969 'Got list of menus. Number of elements- last_index:' ||
1970 last_index);
1971 end if;
1972
1973 /* mark that menu as uncompiled */
1974 for q in 1..last_index loop
1975 begin
1976 insert into fnd_compiled_menu_functions
1977 (menu_id, function_id, grant_flag)
1978 values
1979 (tbl_menu_id(q), C_INVALID_MENU_VAL, C_INVALID_GRANT_VAL);
1980 -- We commit the menu mark in order to keep the rollback segment
1981 -- from getting too big as we compile lots of menus.
1982 commit;
1983 exception
1984 when dup_val_on_index then
1985 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1986 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1987 c_log_head || l_api_name || '.exception',
1988 'Duplicate Value on index. Should never happen.');
1989 end if;
1990 null; /* Should never happen but better safe than sorry */
1991 end;
1992 end loop;
1993 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1994 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1995 c_log_head || l_api_name || '.end',
1996 'end');
1997 end if;
1998 end MARK_MISSING;
1999
2000
2001 -- COMPILE_ALL_MARKED_I-
2002 --
2003 -- recompiles all the marked menus.
2004 --
2005 -- for (each menu_id marked)
2006 -- FND_FUNCTION.COMPILE_MENU_MARKED(menu_id)
2007 --
2008 -- Returns number of compiled rows changed.
2009 --
2010 function COMPILE_ALL_MARKED_I(compile_missing in VARCHAR2)
2011 return number is
2012 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE_ALL_MARKED';
2013 l_menu_id number;
2014 l_rows_processed number;
2015
2016 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
2017 TBL_MENU_ID NUMBER_TABLE_TYPE;
2018
2019 begin
2020 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2021 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2022 c_log_head || l_api_name || '.begin',
2023 c_pkg_name || '.' ||l_api_name ||'(' ||
2024 'compile_missing=>'|| compile_missing||');');
2025 end if;
2026
2027 /* Mark any menus that aren't in the compiled representation, */
2028 /* so they will get recompiled */
2032
2029 if (compile_missing = 'Y') then
2030 mark_missing;
2031 end if;
2033 l_rows_processed := 0;
2034 while (TRUE) loop
2035 /* Find a menu that needs compilation */
2036 begin /* The hint below was suggested to avoid FTS in bug 2078561 */
2037 select
2038 /*+ INDEX (fnd_compiled_menu_functions fnd_compiled_menu_functions_n3) */
2039 menu_id
2040 into l_menu_id
2041 from fnd_compiled_menu_functions
2042 where grant_flag = C_INVALID_GRANT_VAL
2043 and rownum = 1;
2044 exception
2045 when no_data_found then
2046 /* We've gotten to all the marked rows, so we are done */
2047 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2048 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2049 c_log_head || l_api_name || '.end_none_marked',
2050 'returning l_rows_processed:' || l_rows_processed );
2051 end if;
2052
2053 -- Set the alreadt fast compiled flag since we are done bug5184601
2054
2055 fnd_function.g_already_fast_compiled := 'T';
2056 return l_rows_processed;
2057 end;
2058
2059 /* Reset recursion detector and recompile that menu */
2060 TBL_RECURS_DETEC_MENU_ID_MAX := 0;
2061
2062 l_rows_processed := l_rows_processed +
2063 COMPILE_MENU_MARKED_I(l_menu_id, 'N');
2064
2065 end loop;
2066
2067 fnd_function.g_already_fast_compiled := 'T';
2068 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2069 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2070 c_log_head || l_api_name || '.end',
2071 'returning l_rows_processed:' || l_rows_processed );
2072 end if;
2073 return l_rows_processed;
2074
2075 end compile_all_marked_I;
2076
2077
2078 -- COMPILE_ALL_MARKED-
2079 --
2080 -- recompiles all the marked menus.
2081 --
2082 -- for (each menu_id marked)
2083 -- FND_FUNCTION.COMPILE_MENU_MARKED(menu_id)
2084 --
2085 -- Returns number of compiled rows changed.
2086 --
2087 function COMPILE_ALL_MARKED(compile_missing in VARCHAR2)
2088 return number is
2089 pragma autonomous_transaction;
2090 RESULT_COUNT number;
2091 RSTATUS number;
2092 begin
2093 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.X_MODE) = 0) then
2094 begin
2095 RESULT_COUNT := COMPILE_ALL_MARKED_I(compile_missing);
2096 exception when OTHERS then
2097 null;
2098 end;
2099 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
2100 end if;
2101 return RESULT_COUNT;
2102 end;
2103
2104
2105
2106 -- FAST_COMPILE-
2107 --
2108 -- Recompiles all the marked menus if and only if they haven't yet
2109 -- been already compiled in this session.
2110 --
2111 -- Other packages that reference FND_COMPILED_MENU_FUNCTIONS should
2112 -- call it like this in their package initialization block:
2113 --
2114 -- if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
2115 -- FND_FUNCTION.FAST_COMPILE;
2116 -- end if;
2117 --
2118 -- Administrators can also call it from SQL*Plus in order to compile
2119 -- the FND_COMPILED_MENU_FUNCTIONS table, like this:
2120 --
2121 -- execute FND_FUNCTION.FAST_COMPILE;
2122 --
2123 procedure FAST_COMPILE is
2124 pragma autonomous_transaction;
2125 RESULT_COUNT number;
2126 RSTATUS number;
2127 L_MENU_ID number;
2128 begin
2129 if (G_ALREADY_FAST_COMPILED <> 'T') then
2130 -- Check if any menus need compilation bug5184601
2131 begin
2132
2133 select
2134 /*+ INDEX (fnd_compiled_menu_functions fnd_compiled_menu_functions_n3) */
2135 MENU_ID into L_MENU_ID
2136 from FND_COMPILED_MENU_FUNCTIONS
2137 where GRANT_FLAG = C_INVALID_GRANT_VAL
2138 and ROWNUM = 1;
2139 exception
2140 when NO_DATA_FOUND then
2141 G_ALREADY_FAST_COMPILED:= 'T';
2142 return;
2143 when OTHERS then
2144 null;
2145 end;
2146 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.X_MODE) = 0) then
2147 begin
2148 RESULT_COUNT := COMPILE_ALL_MARKED_I('N');
2149 exception when OTHERS then
2150 null;
2151 end;
2152 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
2153 end if;
2154 end if;
2155 end;
2156
2157
2158 --
2159 -- COMPILE_MENU_MARKED_I
2160 -- if (not force mode) and (this menu is not marked)
2161 -- return; we're done
2162 -- Delete all rows from fnd_compiled_menu_functions for this menu_id
2163 -- for each menu_entry on this menu
2164 -- if this is a function
2165 -- add it to the compiled table
2166 -- if this is a menu
2167 -- Call FND_FUNCTION.COMPILE_MENU_MARKED() to compile this submenu
2168 -- Copy all the submenu elements from the compiled table
2169 -- delete the marker row for this menu_id
2170 --
2171 -- IN:
2172 -- p_menu_id- menu to compile.
2173 -- p_force- 'Y' means compile even if already marked compiled.
2174 -- 'N' is default, meaning only compile if not marked as compiled.
2175 -- RETURNS - a count of how many rows needed to be processed.
2176 --
2177 function COMPILE_MENU_MARKED_I(p_menu_id NUMBER,
2178 p_force in varchar2)
2179 return NUMBER is
2180
2181 l_api_name CONSTANT VARCHAR2(30) := 'COMPILE_MENU_MARKED';
2182 result boolean;
2183 dummy number;
2184 cursor get_mnes_down_c is
2185 SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG
2186 from fnd_menu_entries
2187 where MENU_ID = p_menu_id;
2188
2189 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
2193 TBL_MENU_ID NUMBER_TABLE_TYPE;
2190 TYPE VARCHAR2_TABLE_TYPE is table of VARCHAR2(1) INDEX BY BINARY_INTEGER;
2191
2192 /* Returns from the bulk collect (fetches) */
2194 TBL_ENT_SEQ NUMBER_TABLE_TYPE;
2195 TBL_FUNC_ID NUMBER_TABLE_TYPE;
2196 TBL_SUBMNU_ID NUMBER_TABLE_TYPE;
2197 TBL_GNT_FLG VARCHAR2_TABLE_TYPE;
2198
2199 l_submnu_id NUMBER;
2200 cursor get_compiled_menu_fns_c is
2201 select function_id, grant_flag
2202 from fnd_compiled_menu_functions
2203 where menu_id = l_submnu_id
2204 and grant_flag <> C_INVALID_GRANT_VAL
2205 and grant_flag <> C_BLANK_GRANT_VAL;
2206 TBL_COPY_FUNC_ID NUMBER_TABLE_TYPE;
2207 TBL_COPY_GNT_FLG VARCHAR2_TABLE_TYPE;
2208
2209 l_rows_processed NUMBER;
2210 l_sub_rows_processed NUMBER;
2211 last_index pls_integer;
2212 comp_fail exception;
2213 i number;
2214 j number;
2215 begin
2216 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2217 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2218 c_log_head || l_api_name || '.begin',
2219 c_pkg_name || '.' ||l_api_name|| '(' ||
2220 'p_menu_id =>'|| p_menu_id ||
2221 'p_force =>'|| p_force || ');');
2222 end if;
2223
2224 l_rows_processed := 0;
2225
2226 /* Check to see if this menu is marked and bail if not marked. */
2227 if((p_force is NULL) OR (p_force <> 'Y')) then
2228 begin
2229 select 1
2230 into dummy
2231 from fnd_compiled_menu_functions
2232 where menu_id = p_menu_id
2233 and grant_flag = C_INVALID_GRANT_VAL
2234 and rownum = 1;
2235 exception
2236 when no_data_found then
2237 return 0; /* If we got here that means this menu is not marked; bail*/
2238 end;
2239 end if;
2240
2241 /* Check the recursion stack to make sure this menu isn't also a */
2242 /* parent of this menu. */
2243 for i in 1..TBL_RECURS_DETEC_MENU_ID_MAX loop
2244 if ( TBL_RECURS_DETEC_MENU_ID(i) = p_menu_id ) then
2245 if (fnd_log.LEVEL_ERROR >= fnd_log.g_current_runtime_level) then
2246 fnd_log.string(FND_LOG.LEVEL_ERROR,
2247 c_log_head || l_api_name || '.recursion',
2248 'A looping menu has been detected in menu_id:'||to_char(p_menu_id));
2249 end if;
2250
2251 /* Don't go into an infinite recursion; this menu is already */
2252 /* being compiled so we don't need to do it again. */
2253 return 0;
2254 end if;
2255 end loop;
2256
2257 /* Push this menu_id onto the recursion detection stack */
2258 TBL_RECURS_DETEC_MENU_ID_MAX := TBL_RECURS_DETEC_MENU_ID_MAX + 1;
2259 TBL_RECURS_DETEC_MENU_ID(TBL_RECURS_DETEC_MENU_ID_MAX) := p_menu_id;
2260
2261 /* If we havent already deleted the data for this menu, delete it now. */
2262 begin
2263 delete from fnd_compiled_menu_functions
2264 where menu_id = p_menu_id
2265 and grant_flag <> C_INVALID_GRANT_VAL;
2266 exception when no_data_found then
2267 null;
2268 end;
2269
2270 /* Get the list of menu entries below this submenu. */
2271 if(BULK_COLLECTS_SUPPORTED) then
2272 open get_mnes_down_c;
2273 fetch get_mnes_down_c bulk collect into tbl_menu_id, tbl_ent_seq,
2274 tbl_func_id, tbl_submnu_id, tbl_gnt_flg;
2275 close get_mnes_down_c;
2276
2277 -- See if we found any rows. If not set last_index to zero.
2278 begin
2279 if((tbl_menu_id.FIRST is NULL) or (tbl_menu_id.FIRST <> 1)) then
2280 last_index := 0;
2281 else
2282 if (tbl_menu_id.FIRST is not NULL) then
2283 last_index := tbl_menu_id.LAST;
2284 else
2285 last_index := 0;
2286 end if;
2287 end if;
2288 exception
2289 when others then
2290 last_index := 0;
2291 end;
2292 else
2293 i:= 0;
2294 for below in get_mnes_down_c loop
2295 i := i + 1;
2296 tbl_menu_id(i) := below.MENU_ID;
2297 tbl_ent_seq(i) := below.ENTRY_SEQUENCE;
2298 tbl_func_id(i) := below.FUNCTION_ID;
2299 tbl_submnu_id (i):= below.SUB_MENU_ID;
2300 tbl_gnt_flg(i) := below.GRANT_FLAG;
2301 end loop;
2302 last_index := i;
2303 end if;
2304
2305
2306
2307 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2308 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2309 c_log_head || l_api_name || '.got_mnulist',
2310 'Got list of mnes below p_menu_id '||
2311 to_char(p_menu_id)||'. Number of elements- last_index:'
2312 ||to_char(last_index));
2313 end if;
2314
2315 /* put those menu entries into the list for next time */
2316 for q in 1..last_index loop
2317 /* If this is a function, Put this function into the cache */
2318 if (tbl_func_id(q) is not NULL) then
2319 begin
2320 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2321 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2322 c_log_head || l_api_name || '.insert1',
2323 'Inserting into compiled table menu_id: '|| to_char(p_menu_id)
2324 ||' func_id:'||to_char(tbl_func_id(q) )
2325 ||' gnt_flag:'||tbl_gnt_flg(q) );
2326 end if;
2327 l_rows_processed := l_rows_processed + 1;
2328 insert into fnd_compiled_menu_functions
2329 (MENU_ID, FUNCTION_ID, GRANT_FLAG)
2330 values
2331 (p_menu_id, tbl_func_id(q), tbl_gnt_flg(q));
2332 exception
2333 when dup_val_on_index then
2334 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2335 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2336 c_log_head || l_api_name || '.dup_val_index',
2340 if (fnd_log.LEVEL_STATEMENT >=
2337 'Insert failed with dup val on index. No Problem.');
2338 end if;
2339 if (tbl_gnt_flg(q) <> 'N') then /* don't overwrite 'Y' with 'N'*/
2341 fnd_log.g_current_runtime_level) then
2342 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2343 c_log_head || l_api_name || '.upd_gnt_flag',
2344 'About to update grant_flag.');
2345 end if;
2346 /* If the grant flag isn't right, update it. */
2347 update fnd_compiled_menu_functions
2348 set grant_flag = tbl_gnt_flg(q)
2349 where menu_id = p_menu_id
2350 and function_id = tbl_func_id(q)
2351 and grant_flag = 'N';
2352 end if;
2353 end;
2354 end if;
2355
2356 /* Compile the sub menu, recursively, and then copy the compiled */
2357 /* rows from the submenu into this menu. */
2358 if (tbl_submnu_id(q) is not NULL) then
2359 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2360 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2361 c_log_head || l_api_name || '.call_recursive',
2362 'Calling recursively for tbl_submnu_id(q): '||
2363 tbl_submnu_id(q));
2364 end if;
2365
2366 -- ### What if the submenu has never been compiled?
2367 l_sub_rows_processed :=
2368 COMPILE_MENU_MARKED_I(tbl_submnu_id(q), p_force);
2369
2370 /* for all the entries in this just-compiled menu */
2371 l_submnu_id := tbl_submnu_id(q);
2372
2373 if (BULK_COLLECTS_SUPPORTED) then
2374 open get_compiled_menu_fns_c;
2375 fetch get_compiled_menu_fns_c bulk collect
2376 into tbl_copy_func_id, tbl_copy_gnt_flg;
2377 close get_compiled_menu_fns_c;
2378
2379 -- See if we found any rows. If not set last_index to zero.
2380 begin
2381 if( (tbl_copy_func_id.FIRST is NULL)
2382 or (tbl_copy_func_id.FIRST <> 1)) then
2383 last_index := 0;
2384 else
2385 if (tbl_copy_func_id.FIRST is not NULL) then
2386 last_index := tbl_copy_func_id.LAST;
2387 else
2388 last_index := 0;
2389 end if;
2390 end if;
2391 exception
2392 when others then
2393 last_index := 0;
2394 end;
2395 else
2396 j:= 0;
2397 for rec in get_compiled_menu_fns_c loop
2398 j := j + 1;
2399 tbl_copy_func_id(j) := rec.FUNCTION_ID;
2400 tbl_copy_gnt_flg(j) := rec.GRANT_FLAG;
2401 end loop;
2402 last_index := j;
2403 end if;
2404
2405 -- ### Use the correct count for sub-menus
2406 l_rows_processed := l_rows_processed + last_index;
2407
2408 /* put those entries into the compilation for the menu worked on. */
2409 for z in 1..last_index loop
2410 begin
2411
2412 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2413 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2414 c_log_head || l_api_name || '.insert2',
2415 'Inserting into compiled table p_menu_id: '|| to_char(p_menu_id)
2416 ||' tbl_copy_func_id(z):'||tbl_copy_func_id(z)
2417 ||' tbl_copy_gnt_flg(ze):'||tbl_copy_gnt_flg(z) );
2418 end if;
2419 insert into fnd_compiled_menu_functions
2420 (menu_id, function_id, grant_flag)
2421 values
2422 (p_menu_id, tbl_copy_func_id(z), tbl_copy_gnt_flg(z));
2423 exception
2424 when dup_val_on_index then
2425 if (l_rows_processed > 1) then
2426 l_rows_processed := l_rows_processed - 1;
2427 end if;
2428 if (fnd_log.LEVEL_STATEMENT >=
2429 fnd_log.g_current_runtime_level) then
2430 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2431 c_log_head || l_api_name || '.dup_val_index2',
2432 'Insert failed with dup val on index. No Problem.');
2433 end if;
2434 if (tbl_copy_gnt_flg(z) <> 'N') then/*don't overwrite 'Y' w/ 'N'*/
2435 if (fnd_log.LEVEL_STATEMENT >=
2436 fnd_log.g_current_runtime_level) then
2437 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2438 c_log_head || l_api_name || '.upd_gnt_flag2',
2439 'About to update grant_flag.');
2440 end if;
2441 /* If the grant flag isn't right, update it. */
2442 update fnd_compiled_menu_functions
2443 set grant_flag = tbl_copy_gnt_flg(z)
2444 where menu_id = p_menu_id
2445 and function_id = tbl_copy_func_id(z)
2446 and grant_flag = 'N';
2447 end if;
2448 end;
2449 end loop;
2450 end if;
2451 end loop;
2452
2453 /* If there were no functions in this menu, then insert a "blank" mark */
2454 /* to indicate that we've compiled this menu but there are no fns. */
2455 if (l_rows_processed = 0) then
2456 begin
2457 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2458 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2459 c_log_head || l_api_name || '.insert_blank',
2460 'Inserting blank row into compiled table p_menu_id: '||
2461 to_char(p_menu_id) );
2462 end if;
2463 insert into fnd_compiled_menu_functions
2464 (menu_id, function_id, grant_flag)
2465 values
2466 (p_menu_id, C_BLANK_MENU_VAL, C_BLANK_GRANT_VAL);
2467 exception
2468 when dup_val_on_index then
2469 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2473 end if;
2470 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2471 c_log_head || l_api_name || '.dup_val_index3',
2472 'Insert failed with dup val on index. No Problem.');
2474 null; /* Only put one copy of this row */
2475 end;
2476 end if;
2477
2478 /* now that this menu has been compiled, remove the "uncompiled mark" */
2479 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2480 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2481 c_log_head || l_api_name || '.del_mark',
2482 'Removing uncompiled mark from p_menu_id:'||p_menu_id);
2483 end if;
2484 begin
2485 delete from fnd_compiled_menu_functions
2486 where menu_id = p_menu_id
2487 and grant_flag = C_INVALID_GRANT_VAL;
2488 exception when no_data_found then
2489 null; /* We should never get here, but dont bomb if we do */
2490 end;
2491
2492 -- We commit the menu compilation in order to keep the rollback segment
2493 -- from getting too big as we compile lots of menus.
2494 commit;
2495
2496 -- Pop the recursion detection stack
2497 if(TBL_RECURS_DETEC_MENU_ID_MAX <= 1) then
2498 TBL_RECURS_DETEC_MENU_ID_MAX := 0;
2499 else
2500 TBL_RECURS_DETEC_MENU_ID_MAX := TBL_RECURS_DETEC_MENU_ID_MAX - 1;
2501 end if;
2502
2503 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2504 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2505 c_log_head || l_api_name || '.end',
2506 'returning l_rows_processed:' || l_rows_processed );
2507 end if;
2508 return l_rows_processed;
2509 end compile_menu_marked_I;
2510
2511
2512
2513 --
2514 -- COMPILE_MENU_MARKED
2515 -- if (not force mode) and (this menu is not marked)
2516 -- return; we're done
2517 -- Delete all rows from fnd_compiled_menu_functions for this menu_id
2518 -- for each menu_entry on this menu
2519 -- if this is a function
2520 -- add it to the compiled table
2521 -- if this is a menu
2522 -- Call FND_FUNCTION.COMPILE_MENU_MARKED() to compile this submenu
2523 -- Copy all the submenu elements from the compiled table
2524 -- delete the marker row for this menu_id
2525 --
2526 -- RETURNS - a count of how many rows needed to be processed.
2527 --
2528 function COMPILE_MENU_MARKED(p_menu_id NUMBER,
2529 p_force in varchar2) return NUMBER is
2530 pragma autonomous_transaction;
2531 RESULT_COUNT number;
2532 RSTATUS number;
2533 begin
2534 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.X_MODE) = 0) then
2535 begin
2536 RESULT_COUNT := COMPILE_MENU_MARKED_I(p_menu_id, p_force);
2537 exception when OTHERS then
2538 null;
2539 end;
2540 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
2541 end if;
2542 return RESULT_COUNT;
2543 end;
2544
2545
2546
2547 -- MARK_ALL_I-
2548 --
2549 -- truncate compiled table.
2550 -- for each menu_id in fnd_menus
2551 -- Mark that menu_id in the compiled table
2552 --
2553 --
2554 procedure MARK_ALL_I is
2555 l_api_name CONSTANT VARCHAR2(30) := 'MARK_ALL';
2556 l_menu_id number;
2557
2558 cursor get_all_menus_c is
2559 select menu_id from fnd_menus;
2560
2561 last_index pls_integer;
2562
2563 TYPE NUMBER_TABLE_TYPE is table of NUMBER INDEX BY BINARY_INTEGER;
2564 TBL_MENU_ID NUMBER_TABLE_TYPE;
2565 more_to_delete boolean;
2566
2567 z number;
2568 begin
2569 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2570 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2571 c_log_head || l_api_name || '.begin',
2572 c_pkg_name || '.' ||l_api_name );
2573 end if;
2574
2575 more_to_delete := TRUE;
2576 while (more_to_delete) loop
2577 begin
2578 more_to_delete := FALSE;
2579 -- Delete menus one menu at a time.
2580 delete from fnd_compiled_menu_functions
2581 where menu_id = (select menu_id
2582 from fnd_compiled_menu_functions
2583 where rownum = 1);
2584 -- Commit to keep the rollback small
2585 commit;
2586 if (sql%rowcount > 0) then
2587 more_to_delete := TRUE;
2588 end if;
2589 exception
2590 when no_data_found then
2591 more_to_delete := FALSE;
2592 end;
2593 end loop;
2594
2595 /* Mark all menus as uncompiled */
2596
2597 -- We commit the menu mark in order to keep the rollback segment
2598 -- from getting too big as we compile lots of menus.
2599 commit;
2600 if (BULK_COLLECTS_SUPPORTED) then
2601 open get_all_menus_c;
2602 fetch get_all_menus_c bulk collect into tbl_menu_id;
2603 close get_all_menus_c;
2604 -- See if we found any rows. If not set last_index to zero.
2605 begin
2606 if((tbl_menu_id.FIRST is NULL) or (tbl_menu_id.FIRST <> 1)) then
2607 last_index := 0;
2608 else
2609 if (tbl_menu_id.FIRST is not NULL) then
2610 last_index := tbl_menu_id.LAST;
2611 else
2612 last_index := 0;
2613 end if;
2614 end if;
2615 exception
2616 when others then
2617 last_index := 0;
2618 end;
2619 else
2620 z:= 0;
2621 for rec in get_all_menus_c loop
2622 z := z + 1;
2623 tbl_menu_id(z) := rec.MENU_ID;
2624 end loop;
2625 last_index := z;
2626 end if;
2627
2628 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2632 last_index);
2629 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2630 c_log_head || l_api_name || '.got_mnulist',
2631 'Got list of menus. Number of elements- last_index:'||
2633 end if;
2634
2635 /* mark that menu as uncompiled */
2636 for q in 1..last_index loop
2637 begin
2638 insert into fnd_compiled_menu_functions
2639 (menu_id, function_id, grant_flag)
2640 values
2641 (tbl_menu_id(q), C_INVALID_MENU_VAL, C_INVALID_GRANT_VAL);
2642 -- We commit the menu mark in order to keep the rollback segment
2643 -- from getting too big as we compile lots of menus.
2644 commit;
2645 exception
2646 when dup_val_on_index then
2647 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2648 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2649 c_log_head || l_api_name || '.dup_val_index',
2650 'Mark failed because of dup val on index. Shouldnt happen.');
2651 end if;
2652 null; /* Should never happen but better safe than sorry */
2653 end;
2654 end loop;
2655
2656 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2657 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2658 c_log_head || l_api_name || '.end',
2659 'end');
2660 end if;
2661 end mark_all_I;
2662
2663 -- MARK_ALL-
2664 --
2665 -- truncate compiled table.
2666 -- for each menu_id in fnd_menus
2667 -- Mark that menu_id in the compiled table
2668 --
2669 --
2670 procedure MARK_ALL is
2671 pragma autonomous_transaction;
2672 RSTATUS number;
2673 begin
2674 if (DBMS_LOCK.REQUEST(C_MENU_LOCK_ID, DBMS_LOCK.S_MODE) = 0) then
2675 begin
2676 MARK_ALL_I;
2677 exception when OTHERS then
2678 null;
2679 end;
2680 RSTATUS := DBMS_LOCK.RELEASE(C_MENU_LOCK_ID);
2681 end if;
2682 end;
2683
2684
2685 ---Function get_function_id
2686 ------------------------------
2687 Function get_function_id(p_function_name in varchar2
2688 ) return number is
2689 v_function_id number;
2690 l_api_name CONSTANT VARCHAR2(30) := 'GET_FUNCTION_ID';
2691 Begin
2692 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2693 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2694 c_log_head || l_api_name || '.begin',
2695 c_pkg_name || '.' ||l_api_name|| '(' ||
2696 'p_function_name =>'|| p_function_name ||');');
2697 end if;
2698
2699 if (p_function_name = g_func_name_cache) then
2700 v_function_id := g_func_id_cache; /* If we have it cached, use value */
2701 else /* not cached, hit db */
2702 select function_id
2703 into v_function_id
2704 from fnd_form_functions
2705 where function_name=p_function_name;
2706
2707 /* Store in cache */
2708 g_func_id_cache := v_function_id;
2709 g_func_name_cache := p_function_name;
2710 end if;
2711
2712
2713 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2714 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2715 c_log_head || l_api_name || '.end',
2716 'returning v_function_id:' || v_function_id);
2717 end if;
2718 return v_function_id;
2719 exception
2720 when no_data_found then
2721 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2722 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2723 c_log_head || l_api_name || '.end_null',
2724 'returning null');
2725 end if;
2726 return null;
2727 end;
2728
2729 -- COMPILE_MENU
2730 --
2731 -- Called as a Concurrent Program to compile menu when entries are added
2732 --
2733 --
2734 procedure COMPILE_MENU(errbuf out NOCOPY varchar2, retcode out NOCOPY number, p_menu_id in number, p_force in varchar2) is
2735
2736 numrows number;
2737 begin
2738 numrows := COMPILE_MENU_MARKED(p_menu_id, p_force);
2739 errbuf := ' Compile successful. ';
2740 retcode :=0;
2741
2742 exception
2743 when others then
2744 retcode := 2;
2745 errbuf := 'ERROR: '|| sqlerrm;
2746 end;
2747
2748 end FND_FUNCTION;
[Home] [Help]