1 package body FND_PROFILE as
2 /* $Header: AFPFPROB.pls 120.18.12010000.9 2009/01/28 16:39:09 pdeluna ship $ */
3
4 type VAL_TAB_TYPE is table of varchar2(255) index by binary_integer;
5 type NAME_TAB_TYPE is table of varchar2(80) index by binary_integer;
6
7 /*
8 ** define the internal table that will cache the profile values
9 ** val_tab(x) is associated with name_tab(x) and dbflag(x)
10 */
11 VAL_TAB VAL_TAB_TYPE; /* the table of values for the Generic PUT cache */
12 NAME_TAB NAME_TAB_TYPE; /* the table of names for the Generic PUT cache */
13 TABLE_SIZE binary_integer := 8192; /* the size of above tables*/
14 INSERTED boolean := FALSE; /*if at least a profile is stored */
15
16 /*
17 ** define the internal tables that will cache the profile values
18 ** for the different levels.
19 */
20 USER_VAL_TAB VAL_TAB_TYPE; /* the user-level cache table of values */
21 USER_NAME_TAB NAME_TAB_TYPE; /* the user-level cache table of names */
22 RESP_VAL_TAB VAL_TAB_TYPE; /* the resp-level cache table of values */
23 RESP_NAME_TAB NAME_TAB_TYPE; /* the resp-level cache table of names */
24 APPL_VAL_TAB VAL_TAB_TYPE; /* the appl-level cache table of values */
25 APPL_NAME_TAB NAME_TAB_TYPE; /* the appl-level cache table of names */
26 SITE_VAL_TAB VAL_TAB_TYPE; /* the site-level cache table of values */
27 SITE_NAME_TAB NAME_TAB_TYPE; /* the site-level cache table of names */
28 SERVER_VAL_TAB VAL_TAB_TYPE; /* the server-level cache table of values */
29 SERVER_NAME_TAB NAME_TAB_TYPE; /* the server-level cache table of names */
30 ORG_VAL_TAB VAL_TAB_TYPE; /* the appl-level cache table of values */
31 ORG_NAME_TAB NAME_TAB_TYPE; /* the appl-level cache table of names */
32
33 /*
34 ** Define the current level context
35 */
36 PROFILES_USER_ID number := -1;
37 PROFILES_RESP_ID number := -1;
38 PROFILES_APPL_ID number := -1;
39 PROFILES_SERVER_ID number := -1;
40 PROFILES_ORG_ID number := -1;
41 PROFILES_SESSION_ID number := -1;
42
43 /*
44 ** Constant string used to indicate that a cache entry is undefined.
45 */
46 FND_UNDEFINED_VALUE VARCHAR2(30) := '**FND_UNDEFINED_VALUE**';
47
48 /*
49 ** Save the enabled flags and hierarchy of the last fetched profile
50 ** option.
51 */
52 PROFILE_OPTION_NAME VARCHAR2(80);
53 PROFILE_OPTION_ID NUMBER;
54 PROFILE_AID NUMBER;
55 USER_CHANGEABLE VARCHAR2(1) := 'N'; -- Bug 4257739
56 USER_ENABLED VARCHAR2(1) := 'N';
57 RESP_ENABLED VARCHAR2(1) := 'N';
58 APP_ENABLED VARCHAR2(1) := 'N';
59 SITE_ENABLED VARCHAR2(1) := 'N';
60 SERVER_ENABLED VARCHAR2(1) := 'N';
61 ORG_ENABLED VARCHAR2(1) := 'N';
62 HIERARCHY VARCHAR2(8) := 'SECURITY';
63
64 /*
65 ** Version number to be used to invalidate cache when a change in
66 ** version is detected.
67 */
68 USER_CACHE_VERSION number := 0;
69 RESP_CACHE_VERSION number := 0;
70 APPL_CACHE_VERSION number := 0;
71 SITE_CACHE_VERSION number := 0;
72 SERVER_CACHE_VERSION number := 0;
73 ORG_CACHE_VERSION number := 0;
74
75 /*
76 ** Constant strings for the cache names being stored in
77 ** FND_CACHE_VERSIONS.
78 */
79 USER_CACHE VARCHAR2(30) := 'USER_PROFILE_CACHE';
80 RESP_CACHE VARCHAR2(30) := 'RESP_PROFILE_CACHE';
81 APPL_CACHE VARCHAR2(30) := 'APPL_PROFILE_CACHE';
82 SITE_CACHE VARCHAR2(30) := 'SITE_PROFILE_CACHE';
83 SERVER_CACHE VARCHAR2(30) := 'SERVER_PROFILE_CACHE';
84 ORG_CACHE VARCHAR2(30) := 'ORG_PROFILE_CACHE';
85
86 /*
87 ** Declarations for Server/Resp Level. These were intentionally kept
88 ** separate from the other level declarations.
89 */
90 /* the server/resp-level table of values */
91 SERVRESP_VAL_TAB VAL_TAB_TYPE;
92 /* the server/resp-level table of names */
93 SERVRESP_NAME_TAB NAME_TAB_TYPE;
94 SERVRESP_ENABLED VARCHAR2(1) := 'N';
95 SERVRESP_CACHE_VERSION NUMBER := 0;
96 SERVRESP_CACHE VARCHAR2(30) := 'SERVRESP_PROFILE_CACHE';
97
98 /*
99 ** Global variable used to identify if a profile option exists or not.
100 ** This will determine whether the query for the profile_info cursor is
101 ** to be executed.
102 */
103 PROFILE_OPTION_EXISTS boolean := TRUE;
104
105 /*
106 ** Global variable used to identify core logging is enabled or not.
107 ** Added for Bug 5599946: APPSPERF:FND:LOGGING CALLS IN FND_PROFILE CAUSING
108 ** PERFORMANCE REGRESSION
109 */
110 CORELOG_IS_ENABLED boolean := FND_CORE_LOG.IS_ENABLED;
111
112 /*
113 ** Global variable that stores Applications Release Version
114 */
115 RELEASE_VERSION number := fnd_release.major_version;
116
117 /*
118 ** CORELOG - wrapper to CORELOG with defaulting current profile context.
119 */
120 procedure CORELOG(
121 LOG_PROFNAME in varchar2,
122 LOG_PROFVAL in varchar2 default NULL,
123 CURRENT_API in varchar2,
124 LOG_USER_ID in number default PROFILES_USER_ID,
125 LOG_RESPONSIBILITY_ID in number default PROFILES_RESP_ID,
126 LOG_APPLICATION_ID in number default PROFILES_APPL_ID,
127 LOG_ORG_ID in number default PROFILES_ORG_ID,
128 LOG_SERVER_ID in number default PROFILES_SERVER_ID)
129 is
130 begin
131 FND_CORE_LOG.WRITE_PROFILE(
132 LOG_PROFNAME,
133 LOG_PROFVAL,
134 CURRENT_API,
135 LOG_USER_ID,
136 LOG_RESPONSIBILITY_ID,
137 LOG_APPLICATION_ID,
138 LOG_ORG_ID,
139 LOG_SERVER_ID);
140 end CORELOG;
141
142 /*
143 ** CHECK_CACHE_VERSIONS
144 **
145 ** Bug 5477866: INCONSISTENT VALUES RETURNED BY FND_PROFILE.VALUE_SPECIFIC
146 ** Broke this algorithm out of INITIALIZE so that VALUE_SPECIFIC can use
147 ** the algorithm also.
148 */
149 procedure CHECK_CACHE_VERSIONS
150 is
151 begin
152 /*
153 ** Bug 4864218: CU2: DATE FORMAT CHANGE IN PREFERENCES DOES NOT TAKE
154 ** EFFECT IMMEDIATELY
155 **
156 ** Profile option value cache invalidation relies on cache versions
157 ** to signal whether level caches should be purged. Cache versions
158 ** are stored in PL/SQL tables to utilize bulk loading for better
159 ** performance. Due to the performance enhancements made for bug
160 ** 3901095, a cache refresh issue was introduced. The PL/SQL tables
161 ** used for cache versions were not being refreshed properly, so the
162 ** profile option value cache invalidation was not performing properly.
163 **
164 ** The following call refreshes the cache version PL/SQL tables so that
165 ** the version check, used to determine whether level caches are to be
166 ** purged, are performed properly.
167 **
168 ** This change will introduce a slight performance hit but should not
169 ** be as severe as the performance levels that bug 3901095 had.
170 */
171 FND_CACHE_VERSIONS_PKG.get_values;
172
173 /*
174 ** Add cache(s) entries in FND_CACHE_VERSIONS if one does not exist.
175 ** If a cache exists however, we will check to see if there has been any
176 ** changes within that profile level to refresh it (delete it).
177 */
178 if (FND_CACHE_VERSIONS_PKG.check_version(USER_CACHE,USER_CACHE_VERSION)
179 = FALSE) then
180 if (USER_CACHE_VERSION = -1) then
181 FND_CACHE_VERSIONS_PKG.add_cache_name(USER_CACHE);
182 USER_CACHE_VERSION := 0;
183 else
184 USER_NAME_TAB.DELETE();
185 USER_VAL_TAB.DELETE();
186 end if;
187 end if;
188
189 if (FND_CACHE_VERSIONS_PKG.check_version(RESP_CACHE,RESP_CACHE_VERSION)
190 = FALSE) then
191 if (RESP_CACHE_VERSION = -1) then
192 FND_CACHE_VERSIONS_PKG.add_cache_name(RESP_CACHE);
193 RESP_CACHE_VERSION := 0;
194 else
195 RESP_NAME_TAB.DELETE();
196 RESP_VAL_TAB.DELETE();
197 end if;
198 end if;
199
200 if (FND_CACHE_VERSIONS_PKG.check_version(APPL_CACHE,APPL_CACHE_VERSION)
201 = FALSE) then
202 if (APPL_CACHE_VERSION = -1) then
203 FND_CACHE_VERSIONS_PKG.add_cache_name(APPL_CACHE);
204 APPL_CACHE_VERSION := 0;
205 else
206 APPL_NAME_TAB.DELETE();
207 APPL_VAL_TAB.DELETE();
208 end if;
209 end if;
210
211 if (FND_CACHE_VERSIONS_PKG.check_version(ORG_CACHE,ORG_CACHE_VERSION)
212 = FALSE) then
213 if (ORG_CACHE_VERSION = -1) then
214 FND_CACHE_VERSIONS_PKG.add_cache_name(ORG_CACHE);
215 ORG_CACHE_VERSION := 0;
216 else
217 ORG_NAME_TAB.DELETE();
218 ORG_VAL_TAB.DELETE();
219 end if;
220 end if;
221
222 if (FND_CACHE_VERSIONS_PKG.check_version
223 (SERVER_CACHE, SERVER_CACHE_VERSION) = FALSE) then
224 if (SERVER_CACHE_VERSION = -1) then
225 FND_CACHE_VERSIONS_PKG.add_cache_name(SERVER_CACHE);
226 SERVER_CACHE_VERSION := 0;
227 else
228 SERVER_NAME_TAB.DELETE();
229 SERVER_VAL_TAB.DELETE();
230 end if;
231 end if;
232
233 if (FND_CACHE_VERSIONS_PKG.check_version
234 (SERVRESP_CACHE,SERVRESP_CACHE_VERSION) = FALSE) then
235 if (SERVRESP_CACHE_VERSION = -1) then
236 FND_CACHE_VERSIONS_PKG.add_cache_name(SERVRESP_CACHE);
237 SERVRESP_CACHE_VERSION := 0;
238 else
239 SERVRESP_NAME_TAB.DELETE();
240 SERVRESP_VAL_TAB.DELETE();
241 end if;
242 end if;
243
244 if (FND_CACHE_VERSIONS_PKG.check_version(SITE_CACHE,SITE_CACHE_VERSION)
245 = FALSE) then
246 if (SITE_CACHE_VERSION = -1) then
247 FND_CACHE_VERSIONS_PKG.add_cache_name(SITE_CACHE);
248 SITE_CACHE_VERSION := 0;
249 else
250 SITE_NAME_TAB.DELETE();
251 SITE_VAL_TAB.DELETE();
252 end if;
253 end if;
254
255 end CHECK_CACHE_VERSIONS;
256
257
258 /*
259 ** FIND - find index of a profile option name in the given cache table
260 **
261 ** RETURNS
262 ** table index if found, TABLE_SIZE if not found.
263 */
264 function FIND(
265 NAME_UPPER in varchar2,
266 nameTable in NAME_TAB_TYPE,
267 PROFILE_HASH_VALUE in binary_integer)
268 return binary_integer is
269
270 TAB_INDEX binary_integer;
271 FOUND boolean;
272 HASH_VALUE number;
273
274 /* Bug 4271555: UPPER function is not to be called in FIND. Instead, the
275 ** API calling find passes UPPER(profile option name).
276 ** NAME_UPPER varchar2(80);
277 */
278 begin
279
280 /* Bug 4271555: UPPER function is not to be called in FIND. Instead, the
281 ** API calling find passes UPPER(profile option name).
282 ** NAME_UPPER := upper(NAME);
283 */
284
285 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
286 ** This is a failsafe. PROFILE_HASH_VALUE should always be passed by the
287 ** calling api.
288 **
289 ** TAB_INDEX := dbms_utility.get_hash_value(NAME_UPPER,1,TABLE_SIZE);
290 */
291 if (PROFILE_HASH_VALUE is NULL) then
292 TAB_INDEX := dbms_utility.get_hash_value(NAME_UPPER,1,TABLE_SIZE);
293 else
294 TAB_INDEX := PROFILE_HASH_VALUE;
295 end if;
296
297 if (nameTable.EXISTS(TAB_INDEX)) then
298 if (nameTable(TAB_INDEX) = NAME_UPPER) then
299 return TAB_INDEX;
300 else
301 HASH_VALUE := TAB_INDEX;
302 FOUND := false;
303
304 while (TAB_INDEX < TABLE_SIZE) and (not FOUND) loop
305 if (nameTable.EXISTS(TAB_INDEX)) then
306 if nameTable(TAB_INDEX) = NAME_UPPER then
307 FOUND := true;
308 else
309 TAB_INDEX := TAB_INDEX + 1;
310 end if;
311 else
312 return TABLE_SIZE+1;
313 end if;
314 end loop;
315
316 if (not FOUND) then -- Didn't find any till the end
317 TAB_INDEX := 1; -- Start from the beginning
318 while (TAB_INDEX < HASH_VALUE) and (not FOUND) loop
319 if (nameTable.EXISTS(TAB_INDEX)) then
320 if nameTable(TAB_INDEX) = NAME_UPPER then
321 FOUND := true;
322 else
323 TAB_INDEX := TAB_INDEX + 1;
324 end if;
325 else
326 return TABLE_SIZE+1;
327 end if;
328 end loop;
329 end if;
330
331 if (not FOUND) then
332 return TABLE_SIZE+1; -- Return a higher value
333 end if;
334 end if;
335 else
336 return TABLE_SIZE+1;
337 end if;
338
339 return TAB_INDEX;
340
341 exception
342 when others then -- The entry doesn't exist
343 return TABLE_SIZE+1;
344 end FIND;
345
346
347 /*
348 ** FIND - find index of a profile option name in the Generic PUT cache table
349 ** NAME_TAB, not the level cache tables.
350 **
351 ** RETURNS
352 ** table index if found, TABLE_SIZE if not found.
353 */
354 function FIND(NAME in varchar2) return binary_integer is
355 begin
356 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
357 ** UPPER function call removed, calling API would have done UPPER before
358 ** calling FIND
359 ** return FIND(UPPER(NAME),NAME_TAB);
360 */
361 return FIND(NAME,NAME_TAB,
362 dbms_utility.get_hash_value(NAME,1,TABLE_SIZE));
363 exception
364 when others then -- The entry doesn't exist
365 return TABLE_SIZE+1;
366 end FIND;
367
368 /*
369 ** PUT - Set or Insert a profile option value in cache
370 */
371 procedure PUT(
372 NAME in varchar2, -- should be passed UPPER value
373 VAL in varchar2,
374 nameTable in out NOCOPY NAME_TAB_TYPE,
375 valueTable in out NOCOPY VAL_TAB_TYPE,
376 PROFILE_HASH_VALUE in binary_integer) is
377
378 TABLE_INDEX binary_integer;
379 STORED boolean;
380 HASH_VALUE number;
381
382 begin
383 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
384 ** Assignment removed since calling API would have used UPPER and passed
385 ** resulting value for NAME into PUT
386 **
387 ** NAME_UPPER := upper(NAME);
388 */
389
390 -- Log API entry
391 if CORELOG_IS_ENABLED then
392 CORELOG(NAME,VAL,'Enter FP.P');
393 end if;
394
395 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
396 ** This is a failsafe. PROFILE_HASH_VALUE should always be passed by the
397 ** calling api.
398 **
399 ** TABLE_INDEX := dbms_utility.get_hash_value(NAME,1,TABLE_SIZE);
400 */
401 if (PROFILE_HASH_VALUE is NULL) then
402 TABLE_INDEX := dbms_utility.get_hash_value(NAME,1,TABLE_SIZE);
403 else
404 TABLE_INDEX := PROFILE_HASH_VALUE;
405 end if;
406
407 -- Search for the option name
408 STORED := FALSE;
409
410 if (nameTable.EXISTS(TABLE_INDEX)) then
411 if (nameTable(TABLE_INDEX) = NAME) then -- Found the profile
412 valueTable(TABLE_INDEX) := VAL; -- Store the new value
413 STORED := TRUE;
414 else -- Collision
415 HASH_VALUE := TABLE_INDEX; -- Store the current spot
416 while (TABLE_INDEX < TABLE_SIZE) and (not STORED) loop
417 if (nameTable.EXISTS(TABLE_INDEX)) then
418 if (nameTable(TABLE_INDEX) = NAME) then
419 valueTable(TABLE_INDEX) := VAL;
420 STORED := TRUE;
421 else
422 TABLE_INDEX := TABLE_INDEX + 1;
423 end if;
424 else
425 valueTable(TABLE_INDEX) := VAL;
426 nameTable(TABLE_INDEX) := NAME;
427 STORED := TRUE;
428 end if;
429 end loop;
430
431 if (not STORED) then -- Didn't find any free bucket till the end
432 TABLE_INDEX := 1;
433 while (TABLE_INDEX < HASH_VALUE) and (not STORED) loop
434 if (nameTable.EXISTS(TABLE_INDEX)) then
435 if (nameTable(TABLE_INDEX) = NAME) then
436 valueTable(TABLE_INDEX) := VAL;
437 STORED := TRUE;
438 else
439 TABLE_INDEX := TABLE_INDEX + 1;
440 end if;
441 else
442 valueTable(TABLE_INDEX) := VAL;
443 nameTable(TABLE_INDEX) := NAME;
444 STORED := TRUE;
445 end if;
446 end loop;
447 end if;
448 end if;
449 else
450 nameTable(TABLE_INDEX) := NAME; -- Enter the profile
451 valueTable(TABLE_INDEX) := VAL; -- Store its value
452 STORED := TRUE;
453 end if;
454
455 if (STORED) then
456 INSERTED := TRUE; /* At least, a profile is stored */
457 -- AFPFPROB.pls 115.90 erroneously added an else condition that sets
458 -- INSERTED := FALSE;
459 end if;
460
461 -- Log API exit
462 if CORELOG_IS_ENABLED then
463 CORELOG(NAME,VAL,'Exit FP.P');
464 end if;
465
466 exception
467 when others then
468 null;
469 end PUT;
470
471
472 /*
473 ** PUT - Set or Insert a profile option value into the generic PUT cache
474 */
475 procedure PUT(
476 NAME in varchar2,
477 VAL in varchar2)
478 is
479 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
480 ** UPPER call is done early and value is passed on, which minimizes
481 ** number of UPPER calls
482 */
483 NAME_UPPER varchar2(80) := UPPER(NAME);
484 begin
485
486 -- Log GENERIC PUT Entry
487 if CORELOG_IS_ENABLED then
488 CORELOG(NAME_UPPER,VAL,'Enter Generic FP.P');
489 end if;
490
491 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
492 ** Call dbms_utility.get_hash_value and pass as an argument to PUT
493 */
494 -- Private PUT call
495 PUT(NAME_UPPER,VAL,NAME_TAB,VAL_TAB,
496 dbms_utility.get_hash_value(NAME_UPPER,1,TABLE_SIZE));
497 -- Log GENERIC PUT Exit
498 if CORELOG_IS_ENABLED then
499 CORELOG(NAME_UPPER,VAL,'Exit Generic FP.P');
500 end if;
501
502 end PUT;
503
504 /*
505 ** GET_SPECIFIC_LEVEL_WNPS -
506 ** Get a profile value for a specific user/resp/appl level without
507 ** changing package state.
508 */
509 procedure GET_SPECIFIC_LEVEL_WNPS(
510 name_z in varchar2, -- should be passed UPPER value
511 level_id_z in number,
512 level_value_z in number,
513 level_value_application_z in number,
514 val_z out NOCOPY varchar2,
515 cached_z out NOCOPY boolean,
516 level_value2_z in number default null,
517 PROFILE_HASH_VALUE in binary_integer) is
518
519 tableIndex binary_integer;
520 contextLevelValue number;
521 nameTable NAME_TAB_TYPE;
522 valueTable VAL_TAB_TYPE;
523 contextLevelValue2 number; -- Added for Server/Resp Hierarchy
524 hashValue binary_integer;
525
526 begin
527
528 val_z := NULL;
529 cached_z := FALSE;
530
531 /* Bug 3679441: The collection assignments, i.e. assigning the entire
532 ** collection SITE_NAME_TAB to nameTable, was causing a performance
533 ** degradation and should be avoided. The suggestions put forth in bug
534 ** 3679441 by OM Product Team are being implemented as the solution.
535 ** Specifically, instead of assigning the entire collection to local
536 ** variables nameTable and valueTable, just pass the 'name' collection
537 ** into FIND to determine the tableIndex and if applicable, use the
538 ** 'value' collection to obtain the value using the tableIndex
539 ** obtained. This fix was approved by the ATG Performance Team.
540 */
541
542 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
543 ** This is a failsafe. PROFILE_HASH_VALUE should always be passed by the
544 ** calling api.
545 */
546 if PROFILE_HASH_VALUE is NULL then
547 hashValue := dbms_utility.get_hash_value(name_z,1,TABLE_SIZE);
548 else
549 hashValue := PROFILE_HASH_VALUE;
550 end if;
551
552 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
553 ** Removed all UPPER in FIND calls since calling API would have already
554 ** used UPPER and passed in resulting name_z. This minimizes UPPER calls.
555 */
556 if(level_id_z = 10001) then
557 contextLevelValue := 0;
558 if (contextLevelValue = level_value_z) then
559 tableIndex := FIND(name_z,SITE_NAME_TAB,hashValue);
560 if (tableIndex < TABLE_SIZE) then
561 val_z := SITE_VAL_TAB(tableIndex);
562 cached_z := TRUE;
563 return;
564 end if;
565 end if;
566 elsif (level_id_z = 10002) then
567 contextLevelValue := PROFILES_APPL_ID;
568 if (contextLevelValue = level_value_z) then
569 tableIndex := FIND(name_z,APPL_NAME_TAB,hashValue);
570 if (tableIndex < TABLE_SIZE) then
571 val_z := APPL_VAL_TAB(tableIndex);
572 cached_z := TRUE;
573 return;
574 end if;
575 end if;
576 elsif (level_id_z = 10003) then
577 contextLevelValue := PROFILES_RESP_ID;
578 if ((contextLevelValue = level_value_z) and
579 -- Level-value application ID needs to be taken into account for
580 -- Resp-level cache if level_id = 10003
581 (PROFILES_APPL_ID = level_value_application_z)) then
582 tableIndex := FIND(name_z,RESP_NAME_TAB,hashValue);
583 if (tableIndex < TABLE_SIZE) then
584 val_z := RESP_VAL_TAB(tableIndex);
585 cached_z := TRUE;
586 return;
587 end if;
588 end if;
589 elsif (level_id_z = 10004) then
590 contextLevelValue := PROFILES_USER_ID;
591 if (contextLevelValue = level_value_z) then
592 tableIndex := FIND(name_z,USER_NAME_TAB,hashValue);
593 if (tableIndex < TABLE_SIZE) then
594 val_z := USER_VAL_TAB(tableIndex);
595 cached_z := TRUE;
596 return;
597 end if;
598 end if;
599 elsif (level_id_z = 10005) then
600 contextLevelValue := PROFILES_SERVER_ID;
601 if (contextLevelValue = level_value_z) then
602 tableIndex := FIND(name_z,SERVER_NAME_TAB,hashValue);
603 if (tableIndex < TABLE_SIZE) then
604 val_z := SERVER_VAL_TAB(tableIndex);
605 cached_z := TRUE;
606 return;
607 end if;
608 end if;
609 elsif (level_id_z = 10006) then
610 contextLevelValue := PROFILES_ORG_ID;
611 if (contextLevelValue = level_value_z) then
612 tableIndex := FIND(name_z,ORG_NAME_TAB,hashValue);
613 if (tableIndex < TABLE_SIZE) then
614 val_z := ORG_VAL_TAB(tableIndex);
615 cached_z := TRUE;
616 return;
617 end if;
618 end if;
619 elsif (level_id_z = 10007) then -- Added for Server/Resp Hierarchy
620 contextLevelValue := PROFILES_RESP_ID;
621 contextLevelValue2 := PROFILES_SERVER_ID;
622 if ((contextLevelValue = level_value_z) and
623 (contextLevelValue2 = level_value2_z) and
624 -- Level-value application ID needs to be taken into account for
625 -- ServResp-level cache if level_id = 10007
626 (PROFILES_APPL_ID = level_value_application_z)) then
627 tableIndex := FIND(name_z,SERVRESP_NAME_TAB,hashValue);
628 if (tableIndex < TABLE_SIZE) then
629 val_z := SERVRESP_VAL_TAB(tableIndex);
630 cached_z := TRUE;
631 return;
632 end if;
633 end if;
634 end if;
635
636 end GET_SPECIFIC_LEVEL_WNPS;
637
638
639 procedure GET_SPECIFIC_LEVEL_DB(
640 profile_id_z in number,
641 application_id_z in number default null,
642 level_id_z in number,
643 level_value_z in number,
644 level_value_aid in number default null,
645 val_z out NOCOPY varchar2,
646 defined_z out NOCOPY boolean,
647 level_value2_z in number default null) is
648
649 --
650 -- this cursor fetches profile option values for site, application,
651 -- and user levels (10001/10002/10004)
652 --
653 cursor value_uas(pid number, aid number, lid number, lval number) is
654 select profile_option_value
655 from fnd_profile_option_values
656 where profile_option_id = pid
657 and application_id = aid
658 and level_id = lid
659 and level_value = lval
660 and profile_option_value is not null;
661 --
662 -- this cursor fetches profile option values at the responsibility
663 -- level (10003)
664 --
665 cursor value_resp(pid number, aid number, lval number, laid number) is
666 select profile_option_value
667 from fnd_profile_option_values
668 where profile_option_id = pid
669 and application_id = aid
670 and level_id = 10003
671 and level_value = lval
672 and level_value_application_id = laid
673 and profile_option_value is not null;
674 --
675 -- this cursor fetches profile option values at the server/resp
676 -- level (10007)
677 --
678 cursor value_servresp(pid number, aid number, lval number, laid number,
679 lval2 number) is
680 select profile_option_value
681 from fnd_profile_option_values
682 where profile_option_id = pid
683 and application_id = aid
684 and level_id = 10007
685 and level_value = lval
686 and level_value_application_id = laid
687 and level_value2 = lval2
688 and profile_option_value is not null;
689
690 begin
691 -- Added for Server/Resp Hierarchy
692 -- If the level_value_aid is not NULL, then check if the level is for
693 -- RESP or for SERVRESP.
694 if (level_value_aid is not NULL) then
695 -- If SERVRESP level, use value_servresp cursor.
696 if (level_id_z = 10007) then
697
698 open value_servresp(profile_id_z,application_id_z,level_value_z,
699 level_value_aid,level_value2_z);
700 fetch value_servresp into val_z;
701
702 if (value_servresp%NOTFOUND) then
703 defined_z := FALSE;
704 val_z := NULL;
705 else
706 defined_z := TRUE;
707 end if; -- Found
708
709 close value_servresp;
710
711 else
712 -- Use value_resp cursor instead.
713 open value_resp(profile_id_z,application_id_z,level_value_z,
714 level_value_aid);
715 fetch value_resp into val_z;
716
717 if (value_resp%NOTFOUND) then
718 defined_z := FALSE;
719 val_z := NULL;
720 else
721 defined_z := TRUE;
722 end if; -- Found
723
724 close value_resp;
725
726 end if;
727 else
728 -- level_value_aid is null, use value_uas cursor.
729 open value_uas(profile_id_z,application_id_z,level_id_z,
730 level_value_z);
731 fetch value_uas into val_z;
732
733 if (value_uas%NOTFOUND) then
734 defined_z := FALSE;
735 val_z := NULL;
736 else
737 defined_z := TRUE;
738 end if; -- Found
739
740 close value_uas;
741
742 end if;
743
744 END GET_SPECIFIC_LEVEL_DB;
745
746
747 procedure GET_SPECIFIC_DB(
748 name_z in varchar2, -- UPPER value should be passed in
749 user_id_z in number default null,
750 responsibility_id_z in number default null,
751 application_id_z in number default null,
752 val_z out NOCOPY varchar2,
753 defined_z out NOCOPY boolean,
754 org_id_z in number default null,
755 server_id_z in number default null,
756 level_id_z in number,
757 PROFILE_HASH_VALUE in binary_integer) is
758
759 --
760 -- this cursor fetches profile information that will allow subsequent
761 -- fetches to be more efficient
762 --
763 cursor profile_info is
764 select profile_option_id,
765 application_id,
766 site_enabled_flag ,
767 app_enabled_flag ,
768 resp_enabled_flag ,
769 user_enabled_flag,
770 org_enabled_flag ,
771 server_enabled_flag,
772 SERVERRESP_ENABLED_FLAG,
773 hierarchy_type,
774 user_changeable_flag -- Bug 4257739
775 from fnd_profile_options
776 where profile_option_name = name_z -- Bug 5599946: Removed UPPER call
777 and start_date_active <= sysdate
778 and nvl(end_date_active, sysdate) >= sysdate;
779
780 hashValue binary_integer;
781
782 begin
783
784 -- Log API Entry
785 if CORELOG_IS_ENABLED then
786 CORELOG(name_z,nvl(val_z,'NOVAL'),'Enter FP.GSD',user_id_z,
787 responsibility_id_z,application_id_z,org_id_z,server_id_z);
788 end if;
789
790 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
791 ** This is a failsafe. PROFILE_HASH_VALUE should always be passed by the
792 ** calling api.
793 */
794 if PROFILE_HASH_VALUE is NULL then
795 hashValue := dbms_utility.get_hash_value(name_z,1,TABLE_SIZE);
796 else
797 hashValue := PROFILE_HASH_VALUE;
798 end if;
799
800 /* Check if the current profile option stored in PROFILE_OPTION_NAME is
801 ** being evaluated. If not, then open the cursor and store those values
802 ** into the GLOBAL variables.
803 */
804 if ((PROFILE_OPTION_NAME is null) or
805 ((PROFILE_OPTION_NAME is NOT null) and
806 (name_z <> PROFILE_OPTION_NAME))) then
807
808 -- Get profile info from database
809 open profile_info;
810 fetch profile_info into
811 PROFILE_OPTION_ID,
812 PROFILE_AID,
813 SITE_ENABLED,
814 APP_ENABLED,
815 RESP_ENABLED,
816 USER_ENABLED,
817 ORG_ENABLED,
818 SERVER_ENABLED,
819 SERVRESP_ENABLED,
820 HIERARCHY,
821 USER_CHANGEABLE; -- Bug 4257739
822
823 if (profile_info%NOTFOUND) then
824 val_z := NULL;
825 defined_z := FALSE;
826 PROFILE_OPTION_EXISTS := FALSE;
827 close profile_info;
828
829 -- Log cursor executed but no profile found
830 if CORELOG_IS_ENABLED then
831 CORELOG(name_z,nvl(val_z,'NOVAL'),
832 'CURSOR EXEC in FP.GSD, NOPROF',user_id_z,
833 responsibility_id_z,application_id_z,org_id_z,server_id_z);
834 end if;
835 return;
836 end if; -- profile_info%NOTFOUND
837
838 -- Log cursor executed and profile found
839 if CORELOG_IS_ENABLED then
840 CORELOG(name_z,nvl(val_z,'NOVAL'),'CURSOR EXEC in FP.GSD, PROF'
841 ||':'||name_z||':'||PROFILE_OPTION_NAME,
842 user_id_z,responsibility_id_z,application_id_z,org_id_z,
843 server_id_z);
844 -- Log profile definition
845 FND_CORE_LOG.PUT_LINE(name_z,PROFILE_OPTION_ID||':'||
846 PROFILE_AID||':'||SITE_ENABLED||':'||APP_ENABLED||':'||
847 RESP_ENABLED||':'||USER_ENABLED||':'||ORG_ENABLED||':'||
848 SERVER_ENABLED||':'||SERVRESP_ENABLED||':'||HIERARCHY||':'||USER_CHANGEABLE);
849 end if;
850
851 close profile_info;
852 PROFILE_OPTION_NAME := name_z;
853 PROFILE_OPTION_EXISTS := TRUE;
854
855 else
856
857 /* Bug 5209533: FND_GLOBAL.INITIALIZE RAISES APP-FND-02500 EXECUTING
858 ** RULE FUNCTIONS FOR WF EVENT
859 ** Setting PROFILE_OPTION_EXISTS = TRUE explicitly IF the condition is
860 ** not satisfied. This guarantees that the profile gets evaluated if
861 ** PROFILE_OPTION_EXISTS is not FALSE, e.g. NULL;
862 */
863 PROFILE_OPTION_EXISTS := TRUE;
864
865 -- Log cursor NOT executed and profile found
866 if CORELOG_IS_ENABLED then
867 CORELOG(name_z,nvl(val_z,'NOVAL'),
868 'CURSOR *NOEXEC* in FP.GSD, PROF',user_id_z,responsibility_id_z,
869 application_id_z,org_id_z,server_id_z);
870 end if;
871 end if; -- SAME profile option is being evaluated
872
873 if PROFILE_OPTION_EXISTS then
874
875 -- Go through each level, based on HIERARCHY
876 -- User-level with Security hierarchy
877 if ((user_id_z <> -1) and (HIERARCHY = 'SECURITY') and
878 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
879 (level_id_z = 10004)) then
880
881 if CORELOG_IS_ENABLED then
882 FND_CORE_LOG.PUT_LINE(name_z,'UL Sec in FP.GSD');
883 end if;
884 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10004,
885 user_id_z,NULL,val_z,defined_z);
886
887 if defined_z then
888 -- Log value found at user-level and cached
889 if CORELOG_IS_ENABLED then
890 CORELOG(name_z,nvl(val_z,'NOVAL'),
891 'GSLD VAL cached in USER_TABS FP.GSD, Exit FP.GSD',
892 user_id_z,responsibility_id_z,application_id_z,org_id_z,
893 server_id_z);
894 end if;
895 PUT(name_z,val_z,USER_NAME_TAB,USER_VAL_TAB,hashValue);
896 return;
897 end if;
898 end if;
899
900 -- Resp-level with Security hierarchy
901 if ((responsibility_id_z <> -1) and
902 (HIERARCHY = 'SECURITY' and RESP_ENABLED = 'Y') and
903 (level_id_z = 10003)) then
904
905 if CORELOG_IS_ENABLED then
906 FND_CORE_LOG.PUT_LINE(name_z,'RL Sec in FP.GSD');
907 end if;
908 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10003,
909 nvl(responsibility_id_z,PROFILES_RESP_ID),
910 nvl(application_id_z,PROFILES_APPL_ID),val_z,defined_z);
911
912 if defined_z then
913 -- Log value found at resp-level and cached
914 if CORELOG_IS_ENABLED then
915 CORELOG(name_z,nvl(val_z,'NOVAL'),
916 'GSLD VAL cached in RESP_TABS FP.GSD, Exit FP.GSD',
917 user_id_z,responsibility_id_z,application_id_z,org_id_z,
918 server_id_z);
919 end if;
920 PUT(name_z,val_z,RESP_NAME_TAB,RESP_VAL_TAB,hashValue);
921 return;
922 end if;
923 end if;
924
925 -- Appl-level with Security hierarchy
926 if ((application_id_z <> -1) and
927 (HIERARCHY = 'SECURITY' and APP_ENABLED = 'Y') and
928 (level_id_z = 10002)) then
929
930 if CORELOG_IS_ENABLED then
931 FND_CORE_LOG.PUT_LINE(name_z,'AL Sec in FP.GSD');
932 end if;
933 get_specific_level_db (PROFILE_OPTION_ID,PROFILE_AID,10002,
934 application_id_z,NULL,val_z,defined_z);
935
936 if defined_z then
937 -- Log value found at appl-level and cached
938 if CORELOG_IS_ENABLED then
939 CORELOG(name_z,nvl(val_z,'NOVAL'),
940 'GSLD VAL cached in APPL_TABS FP.GSD, Exit FP.GSD',
941 user_id_z,responsibility_id_z,application_id_z,org_id_z,
942 server_id_z);
943 end if;
944 PUT(name_z,val_z,APPL_NAME_TAB,APPL_VAL_TAB,hashValue);
945 return;
946 end if;
947 end if;
948
949 --
950 -- If none of the context levels are set, i.e. user_id=-1, etc., then
951 -- this is the only situation wherein we check the site-level value to
952 -- ensure that context-level calls do not inadvertently return the
953 -- site-level value. This is only done for the SECURITY hierarchy.
954 --
955 -- Site-level with Security hierarchy --
956 if ((HIERARCHY = 'SECURITY') and
957 (SITE_ENABLED = 'Y') and
958 (level_id_z = 10001)) then
959
960 if CORELOG_IS_ENABLED then
961 FND_CORE_LOG.PUT_LINE(name_z,'SL Sec in FP.GSD');
962 end if;
963 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10001,0,NULL,
964 val_z,defined_z);
965
966 if defined_z then
967 /* Log value found at site-level and cached */
968 if CORELOG_IS_ENABLED then
969 CORELOG(name_z,nvl(val_z,'NOVAL'),
970 'GSLD VAL cached in SITE_TABS FP.GSD, Exit FP.GSD',
971 user_id_z,responsibility_id_z,application_id_z,org_id_z,
972 server_id_z);
973 end if;
974 PUT(name_z,val_z,SITE_NAME_TAB,SITE_VAL_TAB,hashValue);
975 return;
976 end if;
977 end if;
978
979 -- User-level with Organization hierarchy
980 if ((user_id_z <> -1) and (HIERARCHY = 'ORG') and
981 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
982 (level_id_z = 10004)) then
983
984 if CORELOG_IS_ENABLED then
985 FND_CORE_LOG.PUT_LINE(name_z,'UL Org in FP.GSD');
986 end if;
987 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10004,
988 user_id_z,NULL,val_z,defined_z);
989
990 if defined_z then
991 -- Log value found at user-level and cached
992 if CORELOG_IS_ENABLED then
993 CORELOG(name_z,nvl(val_z,'NOVAL'),
994 'GSLD VAL cached in USER_TABS FP.GSD, Exit FP.GSD',
995 user_id_z,responsibility_id_z,application_id_z,org_id_z,
996 server_id_z);
997 end if;
998 PUT(name_z,val_z,USER_NAME_TAB,USER_VAL_TAB,hashValue);
999 return;
1000 end if;
1001 end if;
1002
1003 -- Org-level with Organization hierarchy
1004 if ((org_id_z <> -1) and
1005 (HIERARCHY = 'ORG' and ORG_ENABLED ='Y') and
1006 (level_id_z = 10006)) then
1007
1008 if CORELOG_IS_ENABLED then
1009 FND_CORE_LOG.PUT_LINE(name_z,'OL Org in FP.GSD');
1010 end if;
1011 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10006,org_id_z,
1012 NULL,val_z,defined_z);
1013
1014 if defined_z then
1015 -- Log value found at org-level and cached
1016 if CORELOG_IS_ENABLED then
1017 CORELOG(name_z,nvl(val_z,'NOVAL'),
1018 'GSLD VAL cached in ORG_TABS FP.GSD, Exit FP.GSD',
1019 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1020 server_id_z);
1021 end if;
1022 PUT(name_z,val_z,ORG_NAME_TAB,ORG_VAL_TAB,hashValue);
1023 return;
1024 end if;
1025 end if;
1026
1027 -- Site-level with Organization hierarchy
1028 if (HIERARCHY = 'ORG' and SITE_ENABLED = 'Y' and
1029 level_id_z = 10001) then
1030
1031 if CORELOG_IS_ENABLED then
1032 FND_CORE_LOG.PUT_LINE(name_z,'SL Org in FP.GSD');
1033 end if;
1034 get_specific_level_db (PROFILE_OPTION_ID,PROFILE_AID,10001,0,NULL,
1035 val_z,defined_z);
1036
1037 if defined_z then
1038 -- Log value found at site-level and cached
1039 if CORELOG_IS_ENABLED then
1040 CORELOG(name_z,nvl(val_z,'NOVAL'),
1041 'GSLD VAL cached in SITE_TABS FP.GSD, Exit FP.GSD',
1042 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1043 server_id_z);
1044 end if;
1045 PUT(name_z,val_z,SITE_NAME_TAB,SITE_VAL_TAB,hashValue);
1046 return;
1047 end if;
1048 end if;
1049
1050 -- User-level with Server hierarchy
1051 if ((user_id_z <> -1) and (HIERARCHY = 'SERVER') and
1052 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
1053 (level_id_z = 10004)) then
1054
1055 if CORELOG_IS_ENABLED then
1056 FND_CORE_LOG.PUT_LINE(name_z,'UL Server in FP.GSD');
1057 end if;
1058 get_specific_level_db (PROFILE_OPTION_ID,PROFILE_AID,10004,
1059 user_id_z,NULL,val_z,defined_z);
1060
1061 if defined_z then
1062 -- Log value found at user-level and cached
1063 if CORELOG_IS_ENABLED then
1064 CORELOG(name_z,nvl(val_z,'NOVAL'),
1065 'GSLD VAL cached in USER_TABS FP.GSD, Exit FP.GSD',
1066 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1067 server_id_z);
1068 end if;
1069 PUT(name_z,val_z,USER_NAME_TAB,USER_VAL_TAB,hashValue);
1070 return;
1071 end if;
1072
1073 end if;
1074
1075 -- Server-level with Server hierarchy
1076 if ((server_id_z <> -1) and
1077 (HIERARCHY = 'SERVER' and SERVER_ENABLED ='Y') and
1078 (level_id_z = 10005))then
1079
1080 if CORELOG_IS_ENABLED then
1081 FND_CORE_LOG.PUT_LINE(name_z,'SRVL Server in FP.GSD');
1082 end if;
1083 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10005,
1084 server_id_z,NULL,val_z,defined_z);
1085
1086 if defined_z then
1087 -- Log value found at server-level and cached
1088 if CORELOG_IS_ENABLED then
1089 CORELOG(name_z,nvl(val_z,'NOVAL'),
1090 'GSLD VAL cached in SERVER_TABS FP.GSD, Exit FP.GSD',
1091 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1092 server_id_z);
1093 end if;
1094 PUT(name_z,val_z,SERVER_NAME_TAB,SERVER_VAL_TAB,hashValue);
1095 return;
1096 end if;
1097 end if;
1098
1099 -- Site-level with Server hierarchy
1100 if (HIERARCHY = 'SERVER' and SITE_ENABLED ='Y' and
1101 level_id_z = 10001) then
1102
1103 if CORELOG_IS_ENABLED then
1104 FND_CORE_LOG.PUT_LINE(name_z,'SL Server in FP.GSD');
1105 end if;
1106 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10001,0,NULL,
1107 val_z,defined_z);
1108
1109 if defined_z then
1110 -- Log value found at site-level and cached
1111 if CORELOG_IS_ENABLED then
1112 CORELOG(name_z,nvl(val_z,'NOVAL'),
1113 'GSLD VAL cached in SITE_TABS FP.GSD, Exit FP.GSD',
1114 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1115 server_id_z);
1116 end if;
1117 PUT(name_z,val_z,SITE_NAME_TAB,SITE_VAL_TAB,hashValue);
1118 return;
1119 end if;
1120 end if;
1121
1122 -- User-level with Server/Resp hierarchy
1123 if ((user_id_z <> -1) and (HIERARCHY = 'SERVRESP') and
1124 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
1125 (level_id_z = 10004)) then
1126
1127 if CORELOG_IS_ENABLED then
1128 FND_CORE_LOG.PUT_LINE(name_z,'UL ServResp in FP.GSD');
1129 end if;
1130 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10004,
1131 user_id_z,NULL,val_z,defined_z);
1132
1133 if defined_z then
1134 -- Log value found at user-level and cached
1135 if CORELOG_IS_ENABLED then
1136 CORELOG(name_z,nvl(val_z,'NOVAL'),
1137 'GSLD VAL cached in USER_TABS FP.GSD, Exit FP.GSD',
1138 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1139 server_id_z);
1140 end if;
1141 PUT(name_z,val_z,USER_NAME_TAB,USER_VAL_TAB,hashValue);
1142 return;
1143 end if;
1144 end if;
1145
1146 -- Server-level with Server/Resp hierarchy
1147 if (HIERARCHY = 'SERVRESP' and SERVRESP_ENABLED ='Y' and
1148 level_id_z = 10007) then
1149 --
1150 -- This IF block may not really be required since the call to
1151 -- get_specific_level_db, as is, is likely able to handle all
1152 -- situations without the IF-ELSIF conditions. That is:
1153 -- get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,
1154 -- responsibility_id_z,NULL,val_z,defined_z,server_id_z);
1155 -- should be able to return the correct value no matter what
1156 -- server_id_z and responsibility_id_z values are, even when value
1157 -- is -1 for any or both.
1158 --
1159 -- However, the IF block was placed to illustrate the order of
1160 -- precedence that the SERVRESP level has:
1161 -- Server/Responsibility > Responsibility > Server > Site
1162 --
1163 -- Accordingly, the calls to get_specific_level_db were
1164 -- deliberately coded depending on precedence.
1165 --
1166
1167 if CORELOG_IS_ENABLED then
1168 FND_CORE_LOG.PUT_LINE(name_z,'ServRespL ServResp in FP.GSD');
1169 end if;
1170
1171 -- Responsibility ID and Server ID
1172 if (responsibility_id_z <> -1 and server_id_z <> -1) then
1173 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,
1174 responsibility_id_z,nvl(application_id_z,PROFILES_APPL_ID),
1175 val_z,defined_z,server_id_z);
1176
1177 if defined_z then
1178 -- Log value found at servresp-level and cached
1179 if CORELOG_IS_ENABLED then
1180 CORELOG(name_z,nvl(val_z,'NOVAL'),
1181 'GSLD VAL cached in SERVRESP_TABS FP.GSD, Exit FP.GSD',
1182 user_id_z,responsibility_id_z,application_id_z,
1183 org_id_z,server_id_z);
1184 end if;
1185 PUT(name_z,val_z,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
1186 hashValue);
1187 return;
1188 else
1189 -- Responsibility ID and -1 for Server
1190 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,
1191 responsibility_id_z,
1192 nvl(application_id_z,PROFILES_APPL_ID),val_z,
1193 defined_z,-1);
1194
1195 if defined_z then
1196 -- Log value found at servresp-level and cached
1197 if CORELOG_IS_ENABLED then
1198 CORELOG(name_z,nvl(val_z,'NOVAL'),
1199 'GSLD VAL cached in SERVRESP_TABS FP.GSD,'||
1200 'Exit FP.GSD',
1201 user_id_z,responsibility_id_z,application_id_z,
1202 org_id_z,
1203 server_id_z);
1204 end if;
1205 PUT(name_z,val_z,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
1206 hashValue);
1207 return;
1208 else
1209 -- -1 for Responsibility and Server ID
1210 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,
1211 -1,-1,val_z,defined_z,server_id_z);
1212
1213 if defined_z then
1214 -- Log value found at servresp-level and cached
1215 if CORELOG_IS_ENABLED then
1216 CORELOG(name_z,nvl(val_z,'NOVAL'),
1217 'GSLD VAL cached in SERVRESP_TABS FP.GSD,'||
1218 'Exit FP.GSD');
1219 end if;
1220 PUT(name_z,val_z,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
1221 hashValue);
1222 return;
1223 end if; -- -1 for Responsibility and Server ID
1224 end if; -- Responsibility ID and -1 for Server
1225 end if; -- Responsibility ID and Server ID
1226
1227 -- Responsibility ID and -1 for Server
1228 elsif (responsibility_id_z <> -1 and server_id_z = -1) then
1229 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,
1230 responsibility_id_z,nvl(application_id_z,PROFILES_APPL_ID),
1231 val_z,defined_z,-1);
1232
1233 if defined_z then
1234 -- Log value found at servresp-level and cached
1235 if CORELOG_IS_ENABLED then
1236 CORELOG(name_z,nvl(val_z,'NOVAL'),
1237 'GSLD VAL cached in SERVRESP_TABS FP.GSD,'||
1238 'Exit FP.GSD',
1239 user_id_z,responsibility_id_z,application_id_z,
1240 org_id_z,server_id_z);
1241 end if;
1242 PUT(name_z,val_z,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
1243 hashValue);
1244 return;
1245 else
1246 -- -1 for Responsibility and Server ID
1247 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,
1248 -1,-1,val_z,defined_z,server_id_z);
1249
1250 if defined_z then
1251 -- Log value found at servresp-level and cached
1252 if CORELOG_IS_ENABLED then
1253 CORELOG(name_z,nvl(val_z,'NOVAL'),
1254 'GSLD VAL cached in SERVRESP_TABS FP.GSD,'||
1255 'Exit FP.GSD',
1256 user_id_z,responsibility_id_z,application_id_z,
1257 org_id_z,server_id_z);
1258 end if;
1259 PUT(name_z,val_z,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
1260 hashValue);
1261 return;
1262 end if; -- -1 for Responsibility and Server ID
1263 end if; -- Responsibility ID and -1 for Server
1264
1265 -- -1 for Responsibility and Server ID
1266 elsif (server_id_z <> -1 and responsibility_id_z = -1) then
1267 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10007,-1,-1,
1268 val_z,defined_z,server_id_z);
1269
1270 if defined_z then
1271 -- Log value found at servresp-level and cached
1272 if CORELOG_IS_ENABLED then
1273 CORELOG(name_z,nvl(val_z,'NOVAL'),
1274 'GSLD VAL cached in SERVRESP_TABS FP.GSD,'||
1275 'Exit FP.GSD',
1276 user_id_z,responsibility_id_z,application_id_z,
1277 org_id_z,server_id_z);
1278 end if;
1279 PUT(name_z,val_z,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
1280 hashValue);
1281 return;
1282 end if; -- -1 for Responsibility and Server ID
1283 end if;
1284 end if;
1285
1286 -- Site-level with Server/Resp hierarchy --
1287 if (HIERARCHY = 'SERVRESP' and SITE_ENABLED ='Y' and
1288 level_id_z = 10001) then
1289
1290 if CORELOG_IS_ENABLED then
1291 FND_CORE_LOG.PUT_LINE(name_z,'SL ServResp in FP.GSD');
1292 end if;
1293 get_specific_level_db(PROFILE_OPTION_ID,PROFILE_AID,10001,0,NULL,
1294 val_z,defined_z);
1295
1296 if defined_z then
1297 -- Log value found at site-level and cached
1298 if CORELOG_IS_ENABLED then
1299 CORELOG(name_z,nvl(val_z,'NOVAL'),
1300 'GSLD VAL cached in SITE_TABS FP.GSD, Exit FP.GSD',
1301 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1302 server_id_z);
1303 end if;
1304 PUT(name_z,val_z,SITE_NAME_TAB,SITE_VAL_TAB,hashValue);
1305 return;
1306 end if;
1307 end if;
1308
1309 end if; -- PROFILE_OPTION_EXISTS if-then block
1310
1311 -- If the call gets here, then no value was found.
1312 val_z := NULL;
1313 defined_z := FALSE;
1314
1315 -- Log value not found at any level
1316 if CORELOG_IS_ENABLED then
1317 CORELOG(name_z,nvl(val_z,'NOVAL'),'Exit FP.GSD',
1318 user_id_z,responsibility_id_z,application_id_z,org_id_z,
1319 server_id_z);
1320 end if;
1321 END GET_SPECIFIC_DB;
1322
1323
1324 /*
1325 ** This procedure is needed to get around the WNPS pragma.
1326 */
1327 procedure GET_SPECIFIC_DB_WNPS (
1328 name_z in varchar2,
1329 user_id_z in number default null,
1330 responsibility_id_z in number default null,
1331 application_id_z in number default null,
1332 val_z out NOCOPY varchar2,
1333 defined_z out NOCOPY boolean,
1334 org_id_z in number default null,
1335 server_id_z in number default null,
1336 level_id_z in number) is
1337
1338 --
1339 -- this cursor fetches profile information that will allow subsequent
1340 -- fetches to be more efficient
1341 --
1342 cursor profile_info is
1343 select profile_option_id,
1344 application_id,
1345 site_enabled_flag ,
1346 app_enabled_flag ,
1347 resp_enabled_flag ,
1348 user_enabled_flag,
1349 org_enabled_flag ,
1350 server_enabled_flag,
1351 serverresp_enabled_flag,
1352 hierarchy_type,
1353 user_changeable_flag -- Bug 4257739
1354 from fnd_profile_options
1355 where profile_option_name = name_z
1356 and start_date_active <= sysdate
1357 and nvl(end_date_active, sysdate) >= sysdate;
1358
1359 --
1360 -- this cursor fetches profile option values for site, application,
1361 -- and user levels (10001/10002/10004)
1362 --
1363 cursor value_uas(pid number, aid number, lid number, lval number) is
1364 select profile_option_value
1365 from fnd_profile_option_values
1366 where profile_option_id = pid
1367 and application_id = aid
1368 and level_id = lid
1369 and level_value = lval
1370 and profile_option_value is not null;
1371 --
1372 -- this cursor fetches profile option values at the responsibility
1373 -- level (10003)
1374 --
1375 cursor value_resp(pid number, aid number, lval number, laid number) is
1376 select profile_option_value
1377 from fnd_profile_option_values
1378 where profile_option_id = pid
1379 and application_id = aid
1380 and level_id = 10003
1381 and level_value = lval
1382 and level_value_application_id = laid
1383 and profile_option_value is not null;
1384 --
1385 -- this cursor fetches profile option values at the server+responsibility
1386 -- level (10007)
1387 --
1388 cursor value_servresp(pid number, aid number, lval number, laid number,
1389 lval2 number) is
1390 select profile_option_value
1391 from fnd_profile_option_values
1392 where profile_option_id = pid
1393 and application_id = aid
1394 and level_id = 10007
1395 and level_value = lval
1396 and level_value_application_id = laid
1397 and level_value2 = lval2
1398 and profile_option_value is not null;
1399
1400 begin
1401
1402 -- Log API Entry
1403 if CORELOG_IS_ENABLED then
1404 CORELOG(
1405 name_z,
1406 nvl(val_z,'NOVAL'),
1407 'Enter FP.GSDW',
1408 user_id_z,
1409 responsibility_id_z,
1410 application_id_z,
1411 org_id_z,
1412 server_id_z);
1413 end if;
1414
1415 val_z := NULL;
1416 defined_z := FALSE;
1417
1418 --
1419 -- Check if the same profile option is being evaluated. If not, then
1420 -- open the cursor and store those values into the GLOBAL variables.
1421 --
1422 if ((PROFILE_OPTION_NAME is null) or (name_z <> PROFILE_OPTION_NAME))
1423 then
1424
1425 -- Get profile info from database
1426 open profile_info;
1427 fetch profile_info into
1428 PROFILE_OPTION_ID,
1429 PROFILE_AID,
1430 SITE_ENABLED,
1431 APP_ENABLED,
1432 RESP_ENABLED,
1433 USER_ENABLED,
1434 ORG_ENABLED,
1435 SERVER_ENABLED,
1436 SERVRESP_ENABLED,
1437 HIERARCHY,
1438 USER_CHANGEABLE; -- Bug 4257739
1439
1440 if (profile_info%NOTFOUND) then
1441 val_z := NULL;
1442 defined_z := FALSE;
1443 PROFILE_OPTION_EXISTS := FALSE;
1444 close profile_info;
1445
1446 -- Log cursor executed but no profile
1447 if CORELOG_IS_ENABLED then
1448 CORELOG(
1449 name_z,
1450 nvl(val_z,'NOVAL'),
1451 'CURSOR EXEC in FP.GSDW, NOPROF',
1452 user_id_z,
1453 responsibility_id_z,
1454 application_id_z,
1455 org_id_z,
1456 server_id_z);
1457 end if;
1458
1459 return;
1460 end if; -- profile_info%NOTFOUND
1461
1462 close profile_info;
1463 PROFILE_OPTION_NAME := name_z;
1464 PROFILE_OPTION_EXISTS := TRUE;
1465
1466 -- Log cursor executed and profile found
1467 if CORELOG_IS_ENABLED then
1468 CORELOG(
1469 name_z,
1470 nvl(val_z,'NOVAL'),
1471 'CURSOR EXEC in FP.GSDW, PROF',
1472 user_id_z,
1473 responsibility_id_z,
1474 application_id_z,
1475 org_id_z,
1476 server_id_z);
1477 -- Log profile definition
1478 FND_CORE_LOG.PUT_LINE(name_z,PROFILE_OPTION_ID||':'||
1479 PROFILE_AID||':'||SITE_ENABLED||':'||APP_ENABLED||':'||
1480 RESP_ENABLED||':'||USER_ENABLED||':'||ORG_ENABLED||':'||
1481 SERVER_ENABLED||':'||SERVRESP_ENABLED||':'||HIERARCHY||':'||
1482 USER_CHANGEABLE);
1483 end if;
1484 else
1485
1486 /* Bug 5209533: FND_GLOBAL.INITIALIZE RAISES APP-FND-02500 EXECUTING
1487 ** RULE FUNCTIONS FOR WF EVENT
1488 ** Setting PROFILE_OPTION_EXISTS = TRUE explicitly IF the condition is
1489 ** not satisfied. This guarantees that the profile gets evaluated if
1490 ** PROFILE_OPTION_EXISTS is not FALSE, e.g. NULL;
1491 */
1492 PROFILE_OPTION_EXISTS := TRUE;
1493
1494 -- Log cursor NOT executed and profile found
1495 if CORELOG_IS_ENABLED then
1496 CORELOG(
1497 name_z,
1498 nvl(val_z,'NOVAL'),
1499 'CURSOR *NOEXEC* in FP.GSDW, PROF',
1500 user_id_z,
1501 responsibility_id_z,
1502 application_id_z,
1503 org_id_z,
1504 server_id_z);
1505 end if;
1506
1507 end if; -- SAME profile option is being evaluated
1508 --
1509 -- The conditions have been modelled after GET_SPECIFIC_DB to make
1510 -- behavior consistent between GET_SPECIFIC_DB and GET_SPECIFIC_DB_WNPS.
1511 --
1512 if PROFILE_OPTION_EXISTS then
1513
1514 -- USER level with Security hierarchy
1515 if ((user_id_z <> -1) and (HIERARCHY = 'SECURITY') and
1516 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
1517 (level_id_z = 10004)) then
1518
1519 if CORELOG_IS_ENABLED then
1520 FND_CORE_LOG.PUT_LINE(name_z,'UL Sec in FP.GSDW');
1521 end if;
1522 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10004,nvl(user_id_z,
1523 PROFILES_USER_ID));
1524 fetch value_uas into val_z;
1525 if (value_uas%NOTFOUND) then
1526 defined_z := FALSE;
1527 close value_uas;
1528 else
1529 defined_z := TRUE;
1530 close value_uas;
1531 -- Log value found at user-level
1532 if CORELOG_IS_ENABLED then
1533 CORELOG(
1534 name_z,
1535 nvl(val_z,'NOVAL'),
1536 'UL VAL in GSDW',
1537 user_id_z,
1538 responsibility_id_z,
1539 application_id_z,
1540 org_id_z,
1541 server_id_z);
1542 end if;
1543 return;
1544 end if; -- value_uas%NOTFOUND
1545
1546 end if;
1547
1548 -- RESP level with Security hierarchy
1549 if ((responsibility_id_z <> -1) and (HIERARCHY = 'SECURITY'
1550 and RESP_ENABLED = 'Y') and (level_id_z = 10003)) then
1551
1552 if CORELOG_IS_ENABLED then
1553 FND_CORE_LOG.PUT_LINE(name_z,'RL Sec in FP.GSDW');
1554 end if;
1555 open value_resp(PROFILE_OPTION_ID,PROFILE_AID,
1556 nvl(responsibility_id_z,PROFILES_RESP_ID),
1557 nvl(application_id_z,PROFILES_APPL_ID));
1558 fetch value_resp into val_z;
1559 if (value_resp%NOTFOUND) then
1560 defined_z := FALSE;
1561 close value_resp;
1562 else
1563 defined_z := TRUE;
1564 close value_resp;
1565 -- Log value found at resp-level
1566 if CORELOG_IS_ENABLED then
1567 CORELOG(
1568 name_z,
1569 nvl(val_z,'NOVAL'),
1570 'RL VAL in GSDW',
1571 user_id_z,
1572 responsibility_id_z,
1573 application_id_z,
1574 org_id_z,
1575 server_id_z);
1576 end if;
1577 return;
1578 end if; -- value_resp%NOTFOUND
1579
1580 end if;
1581
1582 -- APPL level with Security hierarchy
1583 if ((application_id_z <> -1) and (HIERARCHY = 'SECURITY'
1584 and APP_ENABLED = 'Y') and (level_id_z = 10002)) then
1585
1586 if CORELOG_IS_ENABLED then
1587 FND_CORE_LOG.PUT_LINE(name_z,'AL Sec in FP.GSDW');
1588 end if;
1589 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10002,
1590 nvl(application_id_z,PROFILES_APPL_ID));
1591 fetch value_uas into val_z;
1592 if (value_uas%NOTFOUND) then
1593 defined_z := FALSE;
1594 close value_uas;
1595 else
1596 defined_z := TRUE;
1597 close value_uas;
1598 -- Log value found at appl-level
1599 if CORELOG_IS_ENABLED then
1600 CORELOG(
1601 name_z,
1602 nvl(val_z,'NOVAL'),
1603 'AL VAL in GSDW',
1604 user_id_z,
1605 responsibility_id_z,
1606 application_id_z,
1607 org_id_z,
1608 server_id_z);
1609 end if;
1610 return;
1611 end if; -- value_uas%NOTFOUND
1612
1613 end if;
1614
1615 --
1616 -- If none of the context levels are set, i.e. user_id= -1, etc., then
1617 -- this is the only situation wherein we check the site-level value to
1618 -- ensure that context-level calls do not inadvertently return the
1619 -- site-level value. This is only done for the SECURITY hierarchy.
1620 --
1621 -- Site level with Security hierarchy
1622 if (HIERARCHY = 'SECURITY' and SITE_ENABLED = 'Y' and
1623 level_id_z = 10001) then
1624
1625 if CORELOG_IS_ENABLED then
1626 FND_CORE_LOG.PUT_LINE(name_z,'SL Sec in FP.GSDW');
1627 end if;
1628 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10001,0);
1629 fetch value_uas into val_z;
1630 if (value_uas%NOTFOUND) then
1631 defined_z := FALSE;
1632 close value_uas;
1633 else
1634 defined_z := TRUE;
1635 close value_uas;
1636 -- Log value found at site-level
1637 if CORELOG_IS_ENABLED then
1638 CORELOG(
1639 name_z,
1640 nvl(val_z,'NOVAL'),
1641 'SL VAL in GSDW',
1642 user_id_z,
1643 responsibility_id_z,
1644 application_id_z,
1645 org_id_z,
1646 server_id_z);
1647 end if;
1648 return;
1649 end if; -- value_uas%NOTFOUND
1650 end if;
1651
1652 -- USER level with Organization hierarchy
1653 if ((user_id_z <> -1) and (HIERARCHY = 'ORG') and
1654 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
1655 (level_id_z = 10004)) then
1656
1657 if CORELOG_IS_ENABLED then
1658 FND_CORE_LOG.PUT_LINE(name_z,'UL Org in FP.GSDW');
1659 end if;
1660 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10004,nvl(user_id_z,
1661 PROFILES_USER_ID));
1662 fetch value_uas into val_z;
1663 if (value_uas%NOTFOUND) then
1664 defined_z := FALSE;
1665 close value_uas;
1666 else
1667 defined_z := TRUE;
1668 close value_uas;
1669 -- Log value found at user-level
1670 if CORELOG_IS_ENABLED then
1671 CORELOG(
1672 name_z,
1673 nvl(val_z,'NOVAL'),
1674 'UL VAL in GSDW',
1675 user_id_z,
1676 responsibility_id_z,
1677 application_id_z,
1678 org_id_z,
1679 server_id_z);
1680 end if;
1681 return;
1682 end if; -- value_uas%NOTFOUND
1683
1684 end if;
1685
1686 -- ORG level with Organization hierarchy
1687 if ((org_id_z <> -1) and (HIERARCHY = 'ORG' and ORG_ENABLED ='Y')
1688 and (level_id_z = 10006)) then
1689
1690 if CORELOG_IS_ENABLED then
1691 FND_CORE_LOG.PUT_LINE(name_z,'OL Org in FP.GSDW');
1692 end if;
1693 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10006,
1694 nvl(org_id_z,PROFILES_ORG_ID));
1695 fetch value_uas into val_z;
1696 if (value_uas%NOTFOUND) then
1697 close value_uas;
1698 defined_z := FALSE;
1699 else
1700 defined_z := TRUE;
1701 close value_uas;
1702 -- Log value found at org-level
1703 if CORELOG_IS_ENABLED then
1704 CORELOG(
1705 name_z,
1706 nvl(val_z,'NOVAL'),
1707 'OL VAL in GSDW',
1708 user_id_z,
1709 responsibility_id_z,
1710 application_id_z,
1711 org_id_z,
1712 server_id_z);
1713 end if;
1714 return;
1715 end if; -- value_uas%NOTFOUND
1716
1717 end if;
1718
1719 -- SITE level with Organization hierarchy
1720 if (HIERARCHY = 'ORG' and SITE_ENABLED = 'Y' and
1721 level_id_z = 10001) then
1722
1723 if CORELOG_IS_ENABLED then
1724 FND_CORE_LOG.PUT_LINE(name_z,'SL Org in FP.GSDW');
1725 end if;
1726 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10001,0);
1727 fetch value_uas into val_z;
1728 if (value_uas%NOTFOUND) then
1729 defined_z := FALSE;
1730 close value_uas;
1731 else
1732 defined_z := TRUE;
1733 close value_uas;
1734 -- Log value found at site-level
1735 if CORELOG_IS_ENABLED then
1736 CORELOG(
1737 name_z,
1738 nvl(val_z,'NOVAL'),
1739 'SL VAL in GSDW',
1740 user_id_z,
1741 responsibility_id_z,
1742 application_id_z,
1743 org_id_z,
1744 server_id_z);
1745 end if;
1746 return;
1747 end if; -- value_uas%NOTFOUND
1748
1749 end if;
1750
1751 -- USER level with Server hierarchy
1752 if ((user_id_z <> -1) and (HIERARCHY = 'SERVER') and
1753 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
1754 (level_id_z = 10004)) then
1755
1756 if CORELOG_IS_ENABLED then
1757 FND_CORE_LOG.PUT_LINE(name_z,'UL Server in FP.GSDW');
1758 end if;
1759 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10004,nvl(user_id_z,
1760 PROFILES_USER_ID));
1761 fetch value_uas into val_z;
1762 if (value_uas%NOTFOUND) then
1763 defined_z := FALSE;
1764 close value_uas;
1765 else
1766 defined_z := TRUE;
1767 close value_uas;
1768 -- Log value found at user-level
1769 if CORELOG_IS_ENABLED then
1770 CORELOG(
1771 name_z,
1772 nvl(val_z,'NOVAL'),
1773 'UL VAL in GSDW',
1774 user_id_z,
1775 responsibility_id_z,
1776 application_id_z,
1777 org_id_z,
1778 server_id_z);
1779 end if;
1780 return;
1781 end if; -- value_uas%NOTFOUND
1782
1783 end if;
1784
1785 -- SERVER level with Server hierarchy
1786 if ((server_id_z <> -1) and
1787 (HIERARCHY = 'SERVER' and SERVER_ENABLED ='Y') and
1788 (level_id_z = 10005)) then
1789
1790 if CORELOG_IS_ENABLED then
1791 FND_CORE_LOG.PUT_LINE(name_z,'SRVL Server in FP.GSDW');
1792 end if;
1793 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10005,nvl(server_id_z,
1794 PROFILES_SERVER_ID));
1795 fetch value_uas into val_z;
1796 if (value_uas%NOTFOUND) then
1797 defined_z := FALSE;
1798 close value_uas;
1799 else
1800 defined_z := TRUE;
1801 close value_uas;
1802 -- Log value found at server-level
1803 if CORELOG_IS_ENABLED then
1804 CORELOG(
1805 name_z,
1806 nvl(val_z,'NOVAL'),
1807 'SRVL VAL in GSDW',
1808 user_id_z,
1809 responsibility_id_z,
1810 application_id_z,
1811 org_id_z,
1812 server_id_z);
1813 end if;
1814 return;
1815 end if; -- value_uas%NOTFOUND
1816
1817 end if;
1818
1819 -- SITE level with Server hierarchy
1820 if (HIERARCHY = 'SERVER' and SITE_ENABLED ='Y' and
1821 level_id_z = 10001) then
1822
1823 if CORELOG_IS_ENABLED then
1824 FND_CORE_LOG.PUT_LINE(name_z,'SL Server in FP.GSDW');
1825 end if;
1826 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10001,0);
1827 fetch value_uas into val_z;
1828 if (value_uas%NOTFOUND) then
1829 defined_z := FALSE;
1830 close value_uas;
1831 else
1832 defined_z := TRUE;
1833 close value_uas;
1834 -- Log value found at site-level
1835 if CORELOG_IS_ENABLED then
1836 CORELOG(
1837 name_z,
1838 nvl(val_z,'NOVAL'),
1839 'SL VAL in GSDW',
1840 user_id_z,
1841 responsibility_id_z,
1842 application_id_z,
1843 org_id_z,
1844 server_id_z);
1845 end if;
1846 return;
1847 end if; -- value_uas%NOTFOUND
1848
1849 end if;
1850
1851 -- USER level with Server/Responsibility hierarchy
1852 if ((user_id_z <> -1) and (HIERARCHY = 'SERVRESP') and
1853 ((USER_ENABLED ='Y') or (USER_CHANGEABLE='Y')) and -- Bug 4257739
1854 (level_id_z = 10004)) then
1855
1856 if CORELOG_IS_ENABLED then
1857 FND_CORE_LOG.PUT_LINE(name_z,'UL ServResp in FP.GSDW');
1858 end if;
1859 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10004,nvl(user_id_z,
1860 PROFILES_USER_ID));
1861 fetch value_uas into val_z;
1862 if (value_uas%NOTFOUND) then
1863 defined_z := FALSE;
1864 close value_uas;
1865 else
1866 defined_z := TRUE;
1867 close value_uas;
1868 -- Log value found at user-level
1869 if CORELOG_IS_ENABLED then
1870 CORELOG(
1871 name_z,
1872 nvl(val_z,'NOVAL'),
1873 'UL VAL in GSDW',
1874 user_id_z,
1875 responsibility_id_z,
1876 application_id_z,
1877 org_id_z,
1878 server_id_z);
1879 end if;
1880 return;
1881 end if; -- value_uas%NOTFOUND
1882
1883 end if;
1884
1885 -- SERVRESP level with Server/Responsibility hierarchy
1886 if (HIERARCHY = 'SERVRESP' and SERVRESP_ENABLED = 'Y' and
1887 level_id_z = 10007) then
1888 -- Responsibility and Server
1889 if CORELOG_IS_ENABLED then
1890 FND_CORE_LOG.PUT_LINE(name_z,'ServRespL ServResp in FP.GSDW');
1891 end if;
1892 if (responsibility_id_z <> -1 and server_id_z <> -1) then
1893 if CORELOG_IS_ENABLED then
1894 FND_CORE_LOG.PUT_LINE('ServRespL:R <> -1 and S <> -1');
1895 end if;
1896 open value_servresp(PROFILE_OPTION_ID,PROFILE_AID,
1897 nvl(responsibility_id_z,PROFILES_RESP_ID),
1898 nvl(application_id_z,PROFILES_APPL_ID),
1899 nvl(server_id_z,PROFILES_SERVER_ID));
1900 -- Bug 4017612
1901 fetch value_servresp into val_z;
1902 if (value_servresp%NOTFOUND) then
1903 defined_z := FALSE;
1904 close value_servresp;
1905 else
1906 defined_z := TRUE;
1907 close value_servresp;
1908 -- Log value found at user-level
1909 if CORELOG_IS_ENABLED then
1910 CORELOG(
1911 name_z,
1912 nvl(val_z,'NOVAL'),
1913 'ServRespL VAL in GSDW',
1914 user_id_z,
1915 responsibility_id_z,
1916 application_id_z,
1917 org_id_z,
1918 server_id_z);
1919 end if;
1920 return;
1921 end if; -- value_servresp%NOTFOUND
1922 -- Responsibility and -1 for Server
1923 elsif (responsibility_id_z <> -1 and server_id_z = -1) then
1924 if CORELOG_IS_ENABLED then
1925 FND_CORE_LOG.PUT_LINE('ServRespL:R <> -1 and S = -1');
1926 end if;
1927 open value_servresp(PROFILE_OPTION_ID,PROFILE_AID,
1928 nvl(responsibility_id_z,PROFILES_RESP_ID),
1929 nvl(application_id_z,PROFILES_APPL_ID),
1930 -1);
1931 -- Bug 4017612
1932 fetch value_servresp into val_z;
1933 if (value_servresp%NOTFOUND) then
1934 defined_z := FALSE;
1935 close value_servresp;
1936 else
1937 defined_z := TRUE;
1938 close value_servresp;
1939 -- Log value found at user-level
1940 if CORELOG_IS_ENABLED then
1941 CORELOG(
1942 name_z,
1943 nvl(val_z,'NOVAL'),
1944 'ServRespL VAL in GSDW',
1945 user_id_z,
1946 responsibility_id_z,
1947 application_id_z,
1948 org_id_z,
1949 server_id_z);
1950 end if;
1951 return;
1952 end if; -- value_servresp%NOTFOUND
1953 -- Server and -1 for Responsibility
1954 elsif (server_id_z <> -1 and responsibility_id_z = -1) then
1955 if CORELOG_IS_ENABLED then
1956 FND_CORE_LOG.PUT_LINE('ServRespL:R = -1 and S <> -1');
1957 end if;
1958 open value_servresp(PROFILE_OPTION_ID,PROFILE_AID,
1959 -1,
1960 -1,
1961 nvl(server_id_z,PROFILES_SERVER_ID));
1962 -- Bug 4017612
1963 fetch value_servresp into val_z;
1964 if (value_servresp%NOTFOUND) then
1965 defined_z := FALSE;
1966 close value_servresp;
1967 else
1968 defined_z := TRUE;
1969 close value_servresp;
1970 -- Log value found at user-level
1971 if CORELOG_IS_ENABLED then
1972 CORELOG(
1973 name_z,
1974 nvl(val_z,'NOVAL'),
1975 'ServRespL VAL in GSDW',
1976 user_id_z,
1977 responsibility_id_z,
1978 application_id_z,
1979 org_id_z,
1980 server_id_z);
1981 end if;
1982 return;
1983 end if; -- value_servresp%NOTFOUND
1984 else
1985 -- Context does not fit into the 3 *valid* servresp-level
1986 -- contexts.
1987 defined_z := FALSE;
1988 end if;
1989 end if;
1990
1991 -- SITE level with Server hierarchy
1992 if (HIERARCHY = 'SERVRESP' and SITE_ENABLED ='Y' and
1993 level_id_z = 10001) then
1994 if CORELOG_IS_ENABLED then
1995 FND_CORE_LOG.PUT_LINE(name_z,'SL ServResp in FP.GSDW');
1996 end if;
1997 open value_uas(PROFILE_OPTION_ID,PROFILE_AID,10001,0);
1998 fetch value_uas into val_z;
1999 if (value_uas%NOTFOUND) then
2000 defined_z := FALSE;
2001 close value_uas;
2002 else
2003 defined_z := TRUE;
2004 close value_uas;
2005
2006 -- Log value found at site-level
2007 if CORELOG_IS_ENABLED then
2008 CORELOG(
2009 name_z,
2010 nvl(val_z,'NOVAL'),
2011 'SL VAL in GSDW',
2012 user_id_z,
2013 responsibility_id_z,
2014 application_id_z,
2015 org_id_z,
2016 server_id_z);
2017 end if;
2018 return;
2019 end if; -- value_uas%NOTFOUND
2020
2021 end if;
2022
2023 end if; -- PROFILE_OPTION_EXISTS if-then block
2024
2025 -- If the call gets here, then no value was found.
2026 val_z := NULL;
2027 defined_z := FALSE;
2028
2029 -- Log value not found at any level
2030 if CORELOG_IS_ENABLED then
2031 CORELOG(
2032 name_z,
2033 nvl(val_z,'NOVAL'),
2034 'Exit FP.GSDW',
2035 user_id_z,
2036 responsibility_id_z,
2037 application_id_z,
2038 org_id_z,
2039 server_id_z);
2040 end if;
2041
2042 END GET_SPECIFIC_DB_WNPS;
2043
2044 /*
2045 ** GET_SPECIFIC_WNPS -
2046 ** Get the profile option value for a specific context (without changing
2047 ** package state).
2048 **
2049 ** Context arguments (user_id_z, responsibility_id_z, application_id_z,
2050 ** org_id_z, server_id_z) specify what context to use to determine the
2051 ** profile option value. Context arguments are interpreted as follows:
2052 **
2053 ** NULL - use current session context value (default)
2054 ** -1 - override current context with "undefined" value
2055 ** <value> - override current context with specified value
2056 **
2057 ** Special Notes:
2058 ** - Context override values are only used for determining the profile
2059 ** option value in this function call, the user session context is not
2060 ** changed.
2061 **
2062 ** - An undefined context value (-1) causes that context level to be
2063 ** skipped during processing, meaning that any profile option values
2064 ** set at that context level are ignored.
2065 **
2066 ** - Regardless of which context levels are defined, the profile option
2067 ** HIERARCHY_TYPE and '%_ENABLED_FLAG' flags determine which context
2068 ** levels are searched to find the value.
2069 **
2070 ** - Dynamic profile option values (PUT()) are NOT considered in this
2071 ** function, we only search values that are stored in the database.
2072 **
2073 */
2074 procedure GET_SPECIFIC_WNPS(
2075 name_z in varchar2, -- calling api should pass UPPER value
2076 user_id_z in number default null,
2077 responsibility_id_z in number default null,
2078 application_id_z in number default null,
2079 val_z out NOCOPY varchar2,
2080 defined_z out NOCOPY boolean,
2081 org_id_z in number default null,
2082 server_id_z in number default null) is
2083
2084 value varchar2(240);
2085 cached boolean;
2086 hashValue binary_integer;
2087 userLevelSkip boolean := FALSE;
2088 respLevelSkip boolean := FALSE;
2089 applLevelSkip boolean := FALSE;
2090 orgLevelSkip boolean := FALSE;
2091 serverLevelSkip boolean := FALSE;
2092 servrespLevelSkip boolean := FALSE;
2093
2094 begin
2095
2096 if CORELOG_IS_ENABLED then
2097 CORELOG(name_z,nvl(val_z, 'NOVAL'),'Enter FP.GSW',user_id_z,
2098 responsibility_id_z,application_id_z,org_id_z,server_id_z);
2099 end if;
2100
2101 val_z := NULL;
2102 defined_z := FALSE;
2103
2104 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
2105 ** Generate hashValue and pass it on to FIND and PUT calls.
2106 */
2107 hashValue := dbms_utility.get_hash_value(name_z,1,TABLE_SIZE);
2108
2109 -- Determine if any of the context parameters, passed in, is equal to -1.
2110 -- -1 means that the level will be skipped for evaluation. These boolean
2111 -- flags replace the context conditions that check whether the context is
2112 -- <> -1. These conditions do not work when the context value is NUsLL since
2113 -- the comparison condition NULL <> -1 will equal FALSE even when NULL is
2114 -- not equal to -1. NULL cannot be directly compared with a number.
2115 --
2116 -- Skip user level if user_id_z = -1
2117 if user_id_z = -1 then
2118 userLevelSkip := TRUE;
2119 end if;
2120
2121 -- Skip responsibility level if responsibility_id_z = -1 and
2122 -- application_id_z = -1
2123 if (responsibility_id_z = -1 and application_id_z = -1) then
2124 respLevelSkip := TRUE;
2125 end if;
2126
2127 -- Skip application level if application_id_z = -1
2128 if application_id_z = -1 then
2129 applLevelSkip := TRUE;
2130 end if;
2131
2132 -- Skip organization level if org_id_z = -1
2133 if org_id_z = -1 then
2134 orgLevelSkip := TRUE;
2135 end if;
2136
2137 -- Skip server level if server_id_z = -1
2138 if server_id_z = -1 then
2139 serverLevelSkip := TRUE;
2140 end if;
2141
2142 -- Skip servresp level if responsibility_id_z, application_id_z and
2143 -- server_id_z all equal to -1
2144 if (responsibility_id_z = -1 and application_id_z = -1) and
2145 server_id_z = -1 then
2146 servrespLevelSkip := TRUE;
2147 end if;
2148
2149 --
2150 -- The algorithm checks the context-level caches before going to the DB.
2151 -- If no value was obtained from context-level cache, then it checks the
2152 -- DB to ensure that accurate values are returned.
2153 --
2154 -- User-level cache is initially evaluated. If there is no level cache
2155 -- value at the user-level, then a database fetch is done. If no DB value is
2156 -- found at the user-level AND the context passed in is EQUAL to the
2157 -- current context, then the string **FND_UNDEFINED_VALUE** is placed at the
2158 -- user-level cache. This does 2 things: it prevents another DB fetch for
2159 -- the level and it also says that the level applies to the profile without
2160 -- having the profile option's definition. The code then "drops" to the next
2161 -- level and performs the same algorithm.
2162 --
2163 -- The benefit of just "dropping" to the next level without knowing whether
2164 -- the level applies to the profile or not is that a DB fetch can be avoided
2165 -- IF the levels have values already cached. Again, if a level has a value
2166 -- cached, then the level probably applies to the profile. Otherwise, there
2167 -- would not be a value cached.
2168 --
2169 -- This is a similar algorithm used in GET_CACHED to return accurate values.
2170 --
2171 -- By design, PROFILE_OPTION_EXISTS is not being checked here so that the
2172 -- code allows the profile to be *initially* (at least once) evaluated
2173 -- in GET_SPECIFIC_DB_WNPS which determines whether the profile exists.
2174 --
2175 --
2176 -- Evaluate User-level starting with the level cache if the context passed
2177 -- in <> -1.
2178 if userLevelSkip then
2179 -- If user context = -1, then user level should not be evaluated.
2180 -- This GET_SPECIFIC_DB_WNPS call will allow the profile option's
2181 -- definition to be fetched and used by the other applicable levels.
2182 -- The db fetch will also set PROFILE_OPTION_EXISTS accordingly.
2183 --
2184 -- NOTE: Should a value be found with the database fetch, the value is
2185 -- likely from the site-level and may not accurately represent the return
2186 -- value given the context passed in. The variables that hold the return
2187 -- values are reset just to be safe.
2188 GET_SPECIFIC_DB_WNPS(name_z,-1,-1,-1,val_z,defined_z,-1,-1,10004);
2189 -- Logging that user_id = -1 and that values were reset
2190 if CORELOG_IS_ENABLED then
2191 CORELOG(name_z,nvl(val_z,'RESET'),'user_id_z=-1 in FP.GSW',
2192 user_id_z,responsibility_id_z,application_id_z,org_id_z,server_id_z);
2193 end if;
2194 val_z := NULL;
2195 defined_z := FALSE;
2196 else
2197 -- Check the user-level cache for a value.
2198 GET_SPECIFIC_LEVEL_WNPS(name_z,10004,nvl(user_id_z,PROFILES_USER_ID),0,
2199 value,cached,NULL,hashValue);
2200 if (value is not null) then
2201 -- Profile exists because a value is cached.
2202 PROFILE_OPTION_EXISTS := TRUE;
2203 -- Log value found in user-level cache
2204 if CORELOG_IS_ENABLED then
2205 CORELOG(name_z,nvl(value,'NOVAL'),'UL Cache not null in FP.GSW',
2206 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2207 server_id_z);
2208 end if;
2209
2210 if (value <> FND_UNDEFINED_VALUE) then
2211 val_z := value;
2212 defined_z := TRUE;
2213 -- Log value found in user-level cache
2214 if CORELOG_IS_ENABLED then
2215 CORELOG(name_z,nvl(val_z,'NOVAL'),'UL Cache VAL in FP.GSW',
2216 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2217 server_id_z);
2218 end if;
2219 return;
2220 end if;
2221 else
2222 -- If no value was found in cache, i.e. NULL was returned, then
2223 -- see if user-level context has a value in database.
2224 GET_SPECIFIC_DB_WNPS(name_z,nvl(user_id_z,PROFILES_USER_ID),-1,-1,val_z,
2225 defined_z,-1,-1,10004);
2226 if defined_z then -- Value found at user-level
2227 -- Log value found
2228 if CORELOG_IS_ENABLED then
2229 CORELOG(name_z,nvl(val_z,'NOVAL'),'UL VAL via GSDW in FP.GSW',
2230 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2231 server_id_z);
2232 end if;
2233 return;
2234 elsif (user_id_z = PROFILES_USER_ID) then
2235 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at user-level
2236 -- if context is the same, i.e. user_id_z = PROFILES_USER_ID.
2237 PUT(name_z,FND_UNDEFINED_VALUE,USER_NAME_TAB,USER_VAL_TAB,hashValue);
2238 end if;
2239 end if;
2240 end if;
2241
2242 -- Evaluate Responsibility-level and see if the cache has a value.
2243 -- Bypass if responsibility_id_z and/or application_id_z = -1.
2244 if PROFILE_OPTION_EXISTS and not respLevelSkip then
2245 -- Check Responsibility-level cache
2246 GET_SPECIFIC_LEVEL_WNPS(name_z,10003,
2247 nvl(responsibility_id_z,PROFILES_RESP_ID),
2248 nvl(application_id_z,PROFILES_APPL_ID),value,cached,NULL,hashValue);
2249 if (value is not null) then
2250 -- Log value found in resp-level cache
2251 if CORELOG_IS_ENABLED then
2252 CORELOG(name_z,nvl(value,'NOVAL'),'RL Cache not null in FP.GSW',
2253 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2254 server_id_z);
2255 end if;
2256
2257 if (value <> FND_UNDEFINED_VALUE) then
2258 val_z := value;
2259 defined_z := TRUE;
2260 -- Log value found in resp-level cache
2261 if CORELOG_IS_ENABLED then
2262 CORELOG(name_z,nvl(value,'NOVAL'),'RL Cache VAL in FP.GSW',
2263 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2264 server_id_z);
2265 end if;
2266 return;
2267 end if;
2268 else
2269 -- See if Responsibility-level context has a value in database.
2270 GET_SPECIFIC_DB_WNPS(name_z,-1,
2271 nvl(responsibility_id_z,PROFILES_RESP_ID),
2272 nvl(application_id_z,PROFILES_APPL_ID),val_z,defined_z,-1,-1,10003);
2273 if defined_z then -- Value found at responsibility-level
2274 -- Log value found
2275 if CORELOG_IS_ENABLED then
2276 CORELOG(name_z,nvl(val_z,'NOVAL'),'RL VAL via GSDW in FP.GSW',
2277 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2278 server_id_z);
2279 end if;
2280 return;
2281 elsif ((responsibility_id_z = PROFILES_RESP_ID) and
2282 (application_id_z = PROFILES_APPL_ID)) then
2283 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2284 -- resp-level if context is the same, i.e. responsibility_id_z =
2285 -- PROFILES_RESP_ID and application_id_z = PROFILES_APPL_ID.
2286 PUT(name_z,FND_UNDEFINED_VALUE,RESP_NAME_TAB,RESP_VAL_TAB,hashValue);
2287 end if;
2288 end if;
2289 end if;
2290
2291 -- Evaluate the Application-level and see if the cache has a value.
2292 -- Bypass if application_id_z = -1.
2293 if PROFILE_OPTION_EXISTS and not applLevelSkip then
2294 -- Check Application-level cache
2295 GET_SPECIFIC_LEVEL_WNPS(name_z,10002,
2296 nvl(application_id_z,PROFILES_APPL_ID),0,value,cached,NULL,hashValue);
2297 if (value is not null) then
2298 -- Log value found in appl-level cache
2299 if CORELOG_IS_ENABLED then
2300 CORELOG(name_z,nvl(value,'NOVAL'),'AL Cache not null in FP.GSW',
2301 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2302 server_id_z);
2303 end if;
2304
2305 if (value <> FND_UNDEFINED_VALUE) then
2306 val_z := value;
2307 defined_z := TRUE;
2308 -- Log value found in appl-level cache
2309 if CORELOG_IS_ENABLED then
2310 CORELOG(name_z,nvl(value,'NOVAL'),'AL Cache VAL in FP.GSW',
2311 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2312 server_id_z);
2313 end if;
2314 return;
2315 end if;
2316 else
2317 -- See if Application-level context has a value in DB
2318 GET_SPECIFIC_DB_WNPS(name_z,-1,-1,
2319 nvl(application_id_z,PROFILES_APPL_ID),val_z,defined_z,-1,-1,10002);
2320 if defined_z then -- Value found at application-level
2321 -- Log value found
2322 if CORELOG_IS_ENABLED then
2323 CORELOG( name_z,nvl(val_z,'NOVAL'),'AL VAL via GSDW in FP.GSW',
2324 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2325 server_id_z);
2326 end if;
2327 return;
2328 elsif (application_id_z = PROFILES_APPL_ID) then
2329 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2330 -- appl-level if context is the same, i.e. application_id_z =
2331 -- PROFILES_APPL_ID.
2332 PUT(name_z,FND_UNDEFINED_VALUE,APPL_NAME_TAB,APPL_VAL_TAB,hashValue);
2333 end if;
2334 end if;
2335 end if;
2336
2337 -- Evaluate the Organization-level and see if the cache has a value.
2338 if PROFILE_OPTION_EXISTS and not orgLevelSkip then
2339 -- Bug 7526805: get_specific_wnps MUST USE current context
2340 -- (PROFILES_ORG_ID) in the absence of a context passed in
2341 -- (org_id_z)
2342 if (PROFILES_ORG_ID is not null) or (org_id_z is not null) then
2343 -- Check Organization-level cache
2344 GET_SPECIFIC_LEVEL_WNPS(name_z,10006,
2345 nvl(org_id_z,PROFILES_ORG_ID),0,value,cached,NULL,hashValue);
2346 if (value is not null) then
2347 -- Log value found in org-level cache
2348 if CORELOG_IS_ENABLED then
2349 CORELOG(name_z,nvl(value,'NOVAL'),'OL Cache not null in FP.GSW',
2350 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2351 server_id_z);
2352 end if;
2353
2354 if (value <> FND_UNDEFINED_VALUE) then
2355 val_z := value;
2356 defined_z := TRUE;
2357 -- Log value found in org-level cache
2358 if CORELOG_IS_ENABLED then
2359 CORELOG(name_z,nvl(value,'NOVAL'),'OL Cache VAL in FP.GSW',
2360 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2361 server_id_z);
2362 end if;
2363 return;
2364 end if;
2365 else
2366 -- See if Organization-level context has a value in DB
2367 GET_SPECIFIC_DB_WNPS(name_z,-1,-1,-1,val_z,defined_z,
2368 nvl(org_id_z,PROFILES_ORG_ID),-1,10006);
2369 if defined_z then -- Value found at organization-level
2370 -- Log value found
2371 if CORELOG_IS_ENABLED then
2372 CORELOG(name_z,nvl(val_z,'NOVAL'),'OL VAL via GSDW in FP.GSW',
2373 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2374 server_id_z);
2375 end if;
2376 return;
2377 elsif (org_id_z = PROFILES_ORG_ID) then
2378 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2379 -- org-level if context is the same, i.e.
2380 -- org_id_z = PROFILES_ORG_ID.
2381 PUT(name_z,FND_UNDEFINED_VALUE,ORG_NAME_TAB,ORG_VAL_TAB,hashValue);
2382 end if;
2383 end if;
2384 end if;
2385 end if;
2386
2387 -- Evaluate the Server-level and see if the cache has a value.
2388 if PROFILE_OPTION_EXISTS and not serverLevelSkip then
2389 -- Bug 7526805: get_specific_wnps MUST USE current context
2390 -- (PROFILES_SERVER_ID) in the absence of a context passed in
2391 -- (server_id_z).
2392 if ((PROFILES_SERVER_ID is not null) or (server_id_z is not null)) then
2393 -- Check Server-level cache
2394 GET_SPECIFIC_LEVEL_WNPS(name_z,10005,
2395 nvl(server_id_z,PROFILES_SERVER_ID),0,value,cached,NULL,hashValue);
2396 if (value is not null) then
2397 -- Log value found in server-level cache
2398 if CORELOG_IS_ENABLED then
2399 CORELOG(name_z,nvl(value,'NOVAL'),'SRVL Cache not null in FP.GSW',
2400 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2401 server_id_z);
2402 end if;
2403
2404 if (value <> FND_UNDEFINED_VALUE) then
2405 val_z := value;
2406 defined_z := TRUE;
2407 -- Log value found in server-level cache
2408 if CORELOG_IS_ENABLED then
2409 CORELOG(name_z,nvl(value,'NOVAL'),'SRVL Cache VAL in FP.GSW',
2410 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2411 server_id_z);
2412 end if;
2413 return;
2414 end if;
2415 else
2416 -- See if Server-level context has a value in DB
2417 GET_SPECIFIC_DB_WNPS(name_z,-1,-1,-1,val_z,defined_z,-1,
2418 nvl(server_id_z,PROFILES_SERVER_ID),10005);
2419 if defined_z then -- Value found at server-level
2420 -- Log value found
2421 if CORELOG_IS_ENABLED then
2422 CORELOG(name_z,nvl(val_z,'NOVAL'),'SRVL VAL via GSDW in FP.GSW',
2423 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2424 server_id_z);
2425 end if;
2426 return;
2427 elsif (server_id_z = PROFILES_SERVER_ID) then
2428 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2429 -- server-level if context is the same,
2430 -- i.e. server_id_z = PROFILES_SERVER_ID.
2431 PUT(name_z,FND_UNDEFINED_VALUE,SERVER_NAME_TAB,SERVER_VAL_TAB,
2432 hashValue);
2433 end if;
2434 end if;
2435 end if;
2436 end if;
2437
2438 -- Evaluate the Servresp-level and see if the cache has a value.
2439 if PROFILE_OPTION_EXISTS and not servrespLevelSkip then
2440 -- Check Servresp-level cache
2441 GET_SPECIFIC_LEVEL_WNPS(name_z,10007,
2442 nvl(responsibility_id_z,PROFILES_RESP_ID),
2443 nvl(application_id_z,PROFILES_APPL_ID),value,cached,
2444 nvl(server_id_z,PROFILES_SERVER_ID),hashValue);
2445 if (value is not null) then
2446 -- Log value found in server-level cache
2447 if CORELOG_IS_ENABLED then
2448 CORELOG(name_z,nvl(value,'NOVAL'),
2449 'ServRespL Cache not null in FP.GSW',user_id_z,responsibility_id_z,
2450 application_id_z,org_id_z,server_id_z);
2451 end if;
2452
2453 if (value <> FND_UNDEFINED_VALUE) then
2454 val_z := value;
2455 defined_z := TRUE;
2456 -- Log value found in servresp-level cache
2457 if CORELOG_IS_ENABLED then
2458 CORELOG(name_z,nvl(value,'NOVAL'),
2459 'ServRespL Cache VAL in FP.GSW',
2460 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2461 server_id_z);
2462 end if;
2463 return;
2464 end if;
2465 else
2466 -- See if Servresp-level context has a value in DB
2467 /* Bug 4021624: FND_RUN_FUNCTION.GET_JSP_AGENT calls
2468 ** FND_PROFILE.VALUE_SPECIFIC and site value is consistently
2469 ** returned, given a Resp ID and Server ID. GET_SPECIFIC_DB_WNPS
2470 ** was being called for the Resp ID + Server ID combination ONLY
2471 ** and was missing the values set for Resp ID + (Server ID = -1)
2472 ** and (Resp ID = -1) + Server ID combos. GET_SPECIFIC_DB_WNPS
2473 ** needs to be called for those combinations, as well.
2474 */
2475 -- Start with Resp ID + Server ID combination --
2476 GET_SPECIFIC_DB_WNPS(name_z,-1,
2477 nvl(responsibility_id_z,PROFILES_RESP_ID),
2478 nvl(application_id_z,PROFILES_APPL_ID),
2479 val_z,defined_z,-1,
2480 nvl(server_id_z,PROFILES_SERVER_ID),10007);
2481 if defined_z then -- Value found at servresp-level
2482 -- Log value found in servresp-level cache
2483 if CORELOG_IS_ENABLED then
2484 CORELOG(name_z,nvl(val_z,'NOVAL'),
2485 'ServRespL R+S VAL via GSDW in FP.GSW',user_id_z,
2486 responsibility_id_z,application_id_z,org_id_z,server_id_z);
2487 end if;
2488 return;
2489 else
2490 /* Bug 4021624: SERVERLEVEL CONTEXT NOT INITALIZED BEFORE
2491 ** FND_RUN_FUNCTION IN ICX_PORTLET
2492 ** If Resp ID + Server ID combination yields no results, try
2493 ** Resp ID + (Server ID = -1) combination
2494 */
2495 GET_SPECIFIC_DB_WNPS(name_z,-1,
2496 nvl(responsibility_id_z,PROFILES_RESP_ID),
2497 nvl(application_id_z,PROFILES_APPL_ID),val_z,defined_z,-1,-1,10007);
2498 if defined_z then -- Value found at servresp-level
2499 -- Log value found in servresp-level cache
2500 if CORELOG_IS_ENABLED then
2501 CORELOG(name_z,nvl(val_z,'NOVAL'),
2502 'ServRespL R+-1 VAL via GSDW in FP.GSW',user_id_z,
2503 responsibility_id_z,application_id_z,org_id_z,server_id_z);
2504 end if;
2505 return;
2506 else
2507 /* Bug 4021624: SERVERLEVEL CONTEXT NOT INITALIZED BEFORE
2508 ** FND_RUN_FUNCTION IN ICX_PORTLET
2509 ** If Resp ID + (Server ID = -1) combination yields no
2510 ** results, try (Resp ID = -1) + Server ID combination
2511 */
2512 GET_SPECIFIC_DB_WNPS(name_z,-1,-1,-1,val_z,defined_z,-1,
2513 nvl(server_id_z,PROFILES_SERVER_ID),10007);
2514 if defined_z then -- Value found at servresp-level */
2515 -- Log value found in servresp-level cache */
2516 if CORELOG_IS_ENABLED then
2517 CORELOG(name_z,nvl(val_z,'NOVAL'),
2518 'ServRespL S+-1 VAL via GSDW in FP.GSW',user_id_z,
2519 responsibility_id_z,application_id_z,org_id_z,server_id_z);
2520 end if;
2521 return;
2522 elsif ((responsibility_id_z = PROFILES_RESP_ID)
2523 and (server_id_z = PROFILES_SERVER_ID)) then
2524 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2525 -- server-level. If context is the same,
2526 -- i.e. server_id_z = PROFILES_SERVER_ID.
2527 PUT(name_z,FND_UNDEFINED_VALUE,SERVRESP_NAME_TAB,SERVRESP_VAL_TAB,
2528 hashValue);
2529 end if; -- servresp-level
2530 end if;
2531 end if;
2532 end if;
2533 end if;
2534
2535 -- Evaluate site-level if none of the levels yield a value.
2536 if PROFILE_OPTION_EXISTS then
2537 -- Finally, check Site-level cache
2538 GET_SPECIFIC_LEVEL_WNPS(name_z,10001,0,0,value,cached,NULL,hashValue);
2539 if (value is not null) then
2540 -- Log value found in site-level cache
2541 if CORELOG_IS_ENABLED then
2542 CORELOG(name_z,nvl(value,'NOVAL'),'SL Cache not null in FP.GSW',
2543 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2544 server_id_z);
2545 end if;
2546
2547 if (value <> FND_UNDEFINED_VALUE) then
2548 val_z := value;
2549 defined_z := TRUE;
2550 -- Log value found in site-level cache
2551 if CORELOG_IS_ENABLED then
2552 CORELOG(name_z,nvl(value,'NOVAL'),'SL Cache VAL in FP.GSW',
2553 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2554 server_id_z);
2555 end if;
2556 return;
2557 end if;
2558 else
2559 -- See if site-level has a value in DB
2560 GET_SPECIFIC_DB_WNPS(name_z,-1,-1,-1,val_z,defined_z,-1,-1,10001);
2561 if defined_z then -- Value found at site-level
2562 -- Log value found
2563 if CORELOG_IS_ENABLED then
2564 CORELOG(name_z,nvl(val_z,'NOVAL'),'SL VAL via GSDW in FP.GSW',
2565 user_id_z,responsibility_id_z,application_id_z,org_id_z,
2566 server_id_z);
2567 end if;
2568 return;
2569 else
2570 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2571 -- site-level
2572 PUT(name_z,FND_UNDEFINED_VALUE,SITE_NAME_TAB,SITE_VAL_TAB,hashValue);
2573 end if;
2574 end if;
2575 end if;
2576 --
2577 -- End of Cache calls
2578 -- If the call gets here, then no value was found in cache or in DB
2579 --
2580 val_z := null;
2581 defined_z := FALSE;
2582
2583 -- Log value not found at any level
2584 if CORELOG_IS_ENABLED then
2585 CORELOG(name_z,nvl(val_z, 'NOVAL'),'Exit FP.GSW',user_id_z,
2586 responsibility_id_z,application_id_z,org_id_z,server_id_z);
2587 end if;
2588
2589 end GET_SPECIFIC_WNPS;
2590
2591 /*
2592 ** GET_CACHED -
2593 ** Get the profile value for the current user/resp/appl.
2594 ** This API will also save the profile value in its appropriate level
2595 ** cache.
2596 */
2597 procedure GET_CACHED(
2598 name_z in varchar2, -- should be passed UPPER value
2599 val_z out NOCOPY varchar2,
2600 defined_z out NOCOPY boolean) is
2601
2602 value varchar2(240);
2603 cached boolean;
2604 hashValue binary_integer;
2605
2606 begin
2607
2608 -- Log API Entry
2609 if CORELOG_IS_ENABLED then
2610 CORELOG(name_z,nvl(val_z,'NOVAL'),'Enter FP.GC');
2611 end if;
2612
2613 val_z := NULL;
2614 defined_z := FALSE;
2615
2616 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
2617 ** Generate hashValue and pass it on to FIND and PUT calls.
2618 */
2619 hashValue := dbms_utility.get_hash_value(name_z,1,TABLE_SIZE);
2620
2621 --
2622 -- The algorithm is to check the cache first, if a profile option has
2623 -- been cached before, we will check if the tables were updated since it
2624 -- was last cached. If they were, then we need to refresh the cache, by
2625 -- deleting and repopulating via GET_SPECIFIC_DB. The algorithm also
2626 -- follows the profile hierarchy. If the the profile option/value has
2627 -- never been cached, we will go to the DB after the cached calls.
2628 --
2629
2630 /* Bug 3637977: FND_PROFILE:CONTEXT-LEVEL CHANGES NOT REFLECTED BY RETURN
2631 ** VALUES
2632 ** For each level, a call to GET_SPECIFIC_DB was added to
2633 ** ensure that a context-level value does not exist, if no value was
2634 ** found at context-level cache. The GET_SPECIFIC_DB call done is
2635 ** context-level specific, i.e. if user-level is the value that needs to
2636 ** be obtained, only the user-id is passed. The GET_SPECIFIC_DB call for
2637 ** the site-level is done with no context taken into account.
2638 **
2639 ** Bug 3714184 and 3733896: The suggestion by the ATG Performance Team is
2640 ** to cache null or '**FND_UNDEFINED_VALUE**' via a PUT() call for
2641 ** profiles that return no values or are undefined. This will minimize
2642 ** the GET_SPECIFIC_DB calls.
2643 */
2644
2645 --
2646 -- By design, PROFILE_OPTION_EXISTS is not being checked here so that the
2647 -- code allows the profile to be evaluated, at least once, in
2648 -- GET_SPECIFIC_DB which determines whether the profile exists.
2649 --
2650
2651 -- Check User-level cache
2652 GET_SPECIFIC_LEVEL_WNPS(name_z,10004,PROFILES_USER_ID,0,value,cached,NULL,
2653 hashValue);
2654 if (value is not null) then
2655 -- Profile exists because a value is cached.
2656 PROFILE_OPTION_EXISTS := TRUE;
2657 -- Log value found in user-level cache
2658 if CORELOG_IS_ENABLED then
2659 CORELOG(name_z,nvl(value,'NOVAL'),'UL Cache not null in FP.GC');
2660 end if;
2661 if (value <> FND_UNDEFINED_VALUE) then
2662 val_z := value;
2663 defined_z := TRUE;
2664 -- Log value found in user-level cache
2665 if CORELOG_IS_ENABLED then
2666 CORELOG(name_z,nvl(val_z,'NOVAL'),'UL Cache VAL in FP.GC');
2667 end if;
2668 return;
2669 end if;
2670 else
2671 /* Bug 3637977, see if user-level context has a value in DB */
2672 GET_SPECIFIC_DB(name_z,PROFILES_USER_ID,-1,-1,val_z,defined_z,-1,-1,
2673 10004,hashValue);
2674 if defined_z then -- Value found at user-level
2675 -- Log value found
2676 if CORELOG_IS_ENABLED then
2677 CORELOG(name_z,nvl(val_z,'NOVAL'),'UL VAL via GSD in FP.GC');
2678 end if;
2679 return;
2680 else
2681 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at user-level
2682 PUT(name_z,FND_UNDEFINED_VALUE,USER_NAME_TAB,USER_VAL_TAB,
2683 hashValue);
2684 end if;
2685 end if;
2686
2687 if PROFILE_OPTION_EXISTS then
2688 -- Check Responsibility-level cache
2689 GET_SPECIFIC_LEVEL_WNPS(name_z,10003,PROFILES_RESP_ID,
2690 PROFILES_APPL_ID,value,cached,NULL,hashValue);
2691 if (value is not null) then
2692 -- Log value found in resp-level cache
2693 if CORELOG_IS_ENABLED then
2694 CORELOG(name_z,nvl(value,'NOVAL'),'RL Cache not null in FP.GC');
2695 end if;
2696 if (value <> FND_UNDEFINED_VALUE) then
2697 val_z := value;
2698 defined_z := TRUE;
2699 -- Log value found in resp-level cache
2700 if CORELOG_IS_ENABLED then
2701 CORELOG(name_z,nvl(val_z,'NOVAL'),'RL Cache VAL in FP.GC');
2702 end if;
2703 return;
2704 end if;
2705 else
2706 /* Bug 3637977, see if resp-level context has a value in DB */
2707 GET_SPECIFIC_DB(name_z,-1,PROFILES_RESP_ID,PROFILES_APPL_ID,val_z,
2708 defined_z,-1,-1, 10003,hashValue);
2709 if defined_z then -- Value found at resp-level
2710 -- Log value found
2711 if CORELOG_IS_ENABLED then
2712 CORELOG(name_z,nvl(val_z,'NOVAL'),'RL VAL via GSD in FP.GC');
2713 end if;
2714 return;
2715 else
2716 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2717 -- resp-level
2718 PUT(name_z,FND_UNDEFINED_VALUE,RESP_NAME_TAB,RESP_VAL_TAB,
2719 hashValue);
2720 end if;
2721 end if;
2722 end if;
2723
2724 if PROFILE_OPTION_EXISTS then
2725 -- Check Application-level cache --
2726 GET_SPECIFIC_LEVEL_WNPS(name_z,10002,PROFILES_APPL_ID,0,value,cached,
2727 NULL,hashValue);
2728 if (value is not null) then
2729 -- Log value found in appl-level cache
2730 if CORELOG_IS_ENABLED then
2731 CORELOG(name_z,nvl(value,'NOVAL'),'AL Cache not null in FP.GC');
2732 end if;
2733 if (value <> FND_UNDEFINED_VALUE) then
2734 val_z := value;
2735 defined_z := TRUE;
2736 -- Log value found in appl-level cache
2737 if CORELOG_IS_ENABLED then
2738 CORELOG(name_z,nvl(val_z,'NOVAL'),'AL Cache VAL in FP.GC');
2739 end if;
2740 return;
2741 end if;
2742 else
2743 /* Bug 3637977, see if appl-level context has a value in DB */
2744 GET_SPECIFIC_DB(name_z,-1,-1,PROFILES_APPL_ID,val_z,defined_z,-1,
2745 -1,10002,hashValue);
2746 if defined_z then -- Value found at application-level
2747 -- Log value found
2748 if CORELOG_IS_ENABLED then
2749 CORELOG(name_z,nvl(val_z,'NOVAL'),'AL VAL via GSD in FP.GC');
2750 end if;
2751 return;
2752 else
2753 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2754 -- appl-level
2755 PUT(name_z,FND_UNDEFINED_VALUE,APPL_NAME_TAB,APPL_VAL_TAB,
2756 hashValue);
2757 end if;
2758 end if;
2759 end if;
2760
2761 if PROFILE_OPTION_EXISTS then
2762 if PROFILES_ORG_ID is not NULL then
2763 -- Check Organization-level cache
2764 GET_SPECIFIC_LEVEL_WNPS(name_z,10006,PROFILES_ORG_ID,0,value,cached,
2765 NULL,hashValue);
2766 if (value is not null) then
2767 -- Log value found in org-level cache
2768 if CORELOG_IS_ENABLED then
2769 CORELOG(name_z,nvl(value,'NOVAL'),'OL Cache not null in FP.GC');
2770 end if;
2771 if (value <> FND_UNDEFINED_VALUE) then
2772 val_z := value;
2773 defined_z := TRUE;
2774 -- Log value found in org-level cache
2775 if CORELOG_IS_ENABLED then
2776 CORELOG(name_z,nvl(val_z,'NOVAL'),'OL Cache VAL in FP.GC');
2777 end if;
2778 return;
2779 end if;
2780 else
2781 /* Bug 3637977, see if org-level context has a value in DB */
2782 GET_SPECIFIC_DB(name_z,-1,-1,-1,val_z,defined_z,PROFILES_ORG_ID,-1,
2783 10006,hashValue);
2784 if defined_z then -- Value found at org-level
2785 -- Log value found
2786 if CORELOG_IS_ENABLED then
2787 CORELOG(name_z,nvl(val_z,'NOVAL'),'OL VAL via GSD in FP.GC');
2788 end if;
2789 return;
2790 else
2791 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2792 -- org-level
2793 PUT(name_z,FND_UNDEFINED_VALUE,ORG_NAME_TAB,ORG_VAL_TAB,
2794 hashValue);
2795 end if;
2796 end if;
2797 end if;
2798 end if;
2799
2800 if PROFILE_OPTION_EXISTS then
2801 -- Check Server-level cache
2802 GET_SPECIFIC_LEVEL_WNPS(name_z,10005,PROFILES_SERVER_ID,0,value,
2803 cached,NULL,hashValue);
2804 if (value is not null) then
2805 -- Log value found in server-level cache
2806 if CORELOG_IS_ENABLED then
2807 CORELOG(name_z,nvl(value,'NOVAL'),
2808 'SRVL Cache not null in FP.GC');
2809 end if;
2810 if (value <> FND_UNDEFINED_VALUE) then
2811 val_z := value;
2812 defined_z := TRUE;
2813 -- Log value found in server-level cache
2814 if CORELOG_IS_ENABLED then
2815 CORELOG(name_z,nvl(val_z,'NOVAL'),'SRVL Cache VAL in FP.GC');
2816 end if;
2817 return;
2818 end if;
2819 else
2820 /* Bug 3637977, see if server-level context has a value in DB */
2821 GET_SPECIFIC_DB(name_z,-1,-1,-1,val_z,defined_z,-1,
2822 PROFILES_SERVER_ID,10005,hashValue);
2823 if defined_z then -- Value found at server-level
2824 -- Log value found
2825 if CORELOG_IS_ENABLED then
2826 CORELOG(name_z,nvl(val_z,'NOVAL'),'SRVL VAL via GSD in FP.GC');
2827 end if;
2828 return;
2829 else
2830 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2831 -- server-level
2832 PUT(name_z,FND_UNDEFINED_VALUE,SERVER_NAME_TAB,SERVER_VAL_TAB,
2833 hashValue);
2834 end if;
2835 end if;
2836 end if;
2837
2838 if PROFILE_OPTION_EXISTS then
2839 -- Check Server/Responsibility-level cache
2840 GET_SPECIFIC_LEVEL_WNPS(name_z,10007,PROFILES_RESP_ID,
2841 PROFILES_APPL_ID,value,cached,PROFILES_SERVER_ID,hashValue);
2842 if (value is not null) then
2843 -- Log value found in ServResp-level cache
2844 if CORELOG_IS_ENABLED then
2845 CORELOG(name_z,nvl(value,'NOVAL'),
2846 'ServRespL Cache not null in FP.GC');
2847 end if;
2848 if (value <> FND_UNDEFINED_VALUE) then
2849 val_z := value;
2850 defined_z := TRUE;
2851 -- Log value found in ServResp-level cache
2852 if CORELOG_IS_ENABLED then
2853 CORELOG(name_z,nvl(val_z,'NOVAL'),
2854 'ServRespL Cache VAL in FP.GC');
2855 end if;
2856 return;
2857 end if;
2858 else
2859 -- See if servresp-level context has a value in DB
2860 GET_SPECIFIC_DB(name_z,-1,PROFILES_RESP_ID,PROFILES_APPL_ID,val_z,
2861 defined_z,-1,PROFILES_SERVER_ID, 10007,hashValue);
2862 if defined_z then -- Value found at ServResp-level
2863 -- Log value found
2864 if CORELOG_IS_ENABLED then
2865 CORELOG(name_z,nvl(val_z,'NOVAL'),
2866 'ServRespL VAL via GSD in FP.GC');
2867 end if;
2868 return;
2869 else
2870 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2871 -- resp-level
2872 PUT(name_z,FND_UNDEFINED_VALUE,SERVRESP_NAME_TAB,
2873 SERVRESP_VAL_TAB,hashValue);
2874 end if;
2875 end if;
2876 end if;
2877
2878 if PROFILE_OPTION_EXISTS then
2879 -- Check Site-level cache
2880 GET_SPECIFIC_LEVEL_WNPS(name_z,10001,0,0,value,cached,NULL,hashValue);
2881 if (value is not null) then
2882 -- Log value found in site-level cache
2883 if CORELOG_IS_ENABLED then
2884 CORELOG(name_z,nvl(value,'NOVAL'),'SL Cache not null in FP.GC');
2885 end if;
2886 if (value <> FND_UNDEFINED_VALUE) then
2887 val_z := value;
2888 defined_z := TRUE;
2889 -- Log value found in site-level cache
2890 if CORELOG_IS_ENABLED then
2891 CORELOG(name_z,nvl(val_z,'NOVAL'),'SL Cache VAL in FP.GC');
2892 end if;
2893 return;
2894 end if;
2895 else
2896 /* Bug 3637977, see if site-level context has a value in DB */
2897 GET_SPECIFIC_DB(name_z,-1,-1,-1,val_z,defined_z,-1,-1, 10001,
2898 hashValue);
2899 if defined_z then -- Value found at site-level
2900 -- Log value found
2901 if CORELOG_IS_ENABLED then
2902 CORELOG(name_z,nvl(val_z,'NOVAL'),'SL VAL via GSD in FP.GC');
2903 end if;
2904 return;
2905 else
2906 -- Cache '**FND_UNDEFINED_VALUE**' value for profile at
2907 -- site-level
2908 PUT(name_z,FND_UNDEFINED_VALUE,SITE_NAME_TAB,SITE_VAL_TAB,
2909 hashValue);
2910 end if;
2911 end if;
2912 end if;
2913 -- End of Cache calls
2914
2915 -- If it gets here, then there is no value for the profile option and it
2916 -- is not defined.
2917 val_z := null;
2918 defined_z := FALSE;
2919
2920 -- Log value not found at any level
2921 if CORELOG_IS_ENABLED then
2922 CORELOG(name_z,nvl(val_z,'NOVAL'),'Exit FP.GC');
2923 end if;
2924
2925 end GET_CACHED;
2926
2927
2928 /*
2929 ** DEFINED - test if profile option is defined
2930 */
2931 function DEFINED(NAME in varchar2) return boolean is
2932 VAL varchar2(255);
2933 begin
2934 GET(NAME, VAL);
2935 return (VAL is not NULL);
2936 end DEFINED;
2937
2938 /*
2939 ** GET - get the value of a profile option
2940 **
2941 ** NOTES
2942 ** If the option cannot be found, the out buffer is set to NULL
2943 ** Since a profile value can never be set to NULL,
2944 ** if this returns a NULL, then the profile doesn't exist.
2945 */
2946 procedure GET(NAME in varchar2, VAL out NOCOPY varchar2) is
2947 TABLE_INDEX binary_integer;
2948 DEFINED boolean;
2949 OUTVAL varchar2(255);
2950 NAME_UPPER varchar2(80) := UPPER(NAME);
2951 begin
2952
2953 -- Log API Entry
2954 if CORELOG_IS_ENABLED then
2955 CORELOG(NAME_UPPER,nvl(VAL,'NOVAL'),'Enter FP.G');
2956 end if;
2957
2958 -- Search for the profile option
2959 TABLE_INDEX := FIND(NAME_UPPER);
2960
2961 if TABLE_INDEX < TABLE_SIZE then
2962 VAL := VAL_TAB(TABLE_INDEX);
2963 -- Log value found in Generic Put Cache, API Exit
2964 if CORELOG_IS_ENABLED then
2965 CORELOG(NAME_UPPER,nvl(VAL,'NOVAL'),'VAL in GEN PUT, Exit FP.G');
2966 end if;
2967 else
2968 -- Can't find the value in the table; look in the database
2969 GET_CACHED(NAME_UPPER, OUTVAL, DEFINED);
2970 VAL := OUTVAL;
2971 -- Log API Exit
2972 if CORELOG_IS_ENABLED then
2973 CORELOG(NAME_UPPER,nvl(VAL,'NOVAL'),'VAL in FP.GC, Exit FP.G');
2974 end if;
2975 end if;
2976
2977 exception
2978 when others then
2979 null;
2980 end GET;
2981
2982
2983 /*
2984 ** INVALIDATE_CACHE - Call WF_EVENT.RAISE to invalidate the cache entry
2985 ** corresponding to the specified profile.
2986 */
2987 procedure INVALIDATE_CACHE(
2988 x_level_name in varchar2,
2989 x_level_value in varchar2,
2990 x_level_value_app_id in varchar2,
2991 x_name in varchar2,
2992 x_level_value2 in varchar2 default null) is
2993
2994 level_id number;
2995 level_value number;
2996 level_value_appl_id number;
2997 name varchar2(80) := upper(x_name);
2998 event_key varchar2(255);
2999 level_value2 number;
3000
3001 begin
3002 if (x_level_name = 'SITE') then
3003 level_id := 10001;
3004 level_value := 0;
3005 level_value_appl_id := 0;
3006 elsif (x_level_name = 'APPL') then
3007 level_id := 10002;
3008 level_value := to_number(x_level_value);
3009 level_value_appl_id := 0;
3010 elsif (x_level_name = 'RESP') then
3011 level_id := 10003;
3012 level_value := to_number(x_level_value);
3013 level_value_appl_id := to_number(x_level_value_app_id);
3014 elsif (x_level_name = 'USER') then
3015 level_id := 10004;
3016 level_value := to_number(x_level_value);
3017 level_value_appl_id := 0;
3018 elsif (x_level_name = 'SERVER') then
3019 level_id := 10005;
3020 level_value := to_number(x_level_value);
3021 level_value_appl_id := 0;
3022 elsif (x_level_name = 'ORG') then
3023 level_id := 10006;
3024 level_value := to_number(x_level_value);
3025 level_value_appl_id := 0;
3026 elsif (x_level_name = 'SERVRESP') then -- Added for server/resp hierarchy
3027 level_id := 10007;
3028 level_value := to_number(x_level_value);
3029 level_value_appl_id := to_number(x_level_value_app_id);
3030 --
3031 -- level_value2 was added for the Server/Resp Hierarchy.
3032 -- The subscription that executes the FND_PROFILE.bumpCacheVersion_RF
3033 -- rule function uses the level_id. For this subscription, the
3034 -- level_value2 value is irrelevant. However, it may become relevant
3035 -- to other subscriptions subscribing to the
3036 -- oracle.apps.fnd.profile.value.update event. At this time, the
3037 -- level_value2 value will be stored but not passed into the
3038 -- event_key.
3039 --
3040 --Added for server/resp hierarchy
3041 level_value2 := to_number(x_level_value2);
3042 else
3043 return;
3044 end if;
3045
3046 if (level_id = 10007) then
3047 -- Event Key has level_value2
3048 event_key := level_id||':'||level_value||':'||level_value_appl_id||':'
3049 ||level_value2||':'||name;
3050 else
3051 -- Original event_key format
3052 event_key := level_id||':'||level_value||':'||level_value_appl_id||':'
3053 ||name;
3054 end if;
3055
3056 --
3057 -- Modified this direct call to wf_event.raise to use the
3058 -- fnd_wf_engine.default_event_raise wrapper API
3059 --
3060 -- wf_event.raise(p_event_name=>'oracle.apps.fnd.profile.value.update',
3061 -- p_event_key=>event_key);
3062 --
3063
3064 fnd_wf_engine.default_event_raise(
3065 p_event_name=>'oracle.apps.fnd.profile.value.update',
3066 p_event_key=>event_key);
3067
3068 end INVALIDATE_CACHE;
3069
3070 /*
3071 ** SAVE_USER - Sets the value of a profile option permanently
3072 ** to the database, at the user level for the current user.
3073 ** Also saves in the profile cache for this database session.
3074 ** Note that this will not save in the profile caches
3075 ** for any other database sessions that may be up, so those
3076 ** could potentially be out of sync. This routine will not
3077 ** actually commit the changes; the caller must commit.
3078 **
3079 ** returns: TRUE if successful, FALSE if failure.
3080 **
3081 */
3082 function SAVE_USER(
3083 X_NAME in varchar2, /* Profile name you are setting */
3084 X_VALUE in varchar2 /* Profile value you are setting */
3085 ) return boolean is
3086
3087 result BOOLEAN;
3088
3089 begin
3090 result := SAVE(X_NAME, X_VALUE, 'USER', PROFILES_USER_ID);
3091 return result;
3092 end SAVE_USER;
3093
3094 /*
3095 ** SAVE - sets the value of a profile option permanently
3096 ** to the database, at any level. This routine can be used
3097 ** at runtime or during patching. This routine will not
3098 ** actually commit the changes; the caller must commit.
3099 **
3100 ** ('SITE', 'APPL', 'RESP', 'USER', 'SERVER', 'ORG', or 'SERVRESP').
3101 **
3102 ** Examples of use:
3103 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
3104 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
3105 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
3106 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);
3107 ** FND_PROFILE.SAVE('P_NAME', 'SERVER', 25);
3108 ** FND_PROFILE.SAVE('P_NAME', 'ORG', 204);
3109 ** FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, 25);
3110 ** FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, -1);
3111 ** FND_PROFILE.SAVE('P_NAME', 'SERVRESP', -1, -1, 25);
3112 **
3113 ** returns: TRUE if successful, FALSE if failure.
3114 */
3115 function SAVE(
3116 X_NAME in varchar2,
3117 -- Profile name you are setting
3118 X_VALUE in varchar2,
3119 -- Profile value you are setting
3120 X_LEVEL_NAME in varchar2,
3121 -- Level that you're setting at: 'SITE','APPL','RESP','USER', etc.
3122 X_LEVEL_VALUE in varchar2 default NULL,
3123 -- Level value that you are setting at, e.g. user id for 'USER' level.
3124 -- X_LEVEL_VALUE is not used at site level.
3125 X_LEVEL_VALUE_APP_ID in varchar2 default NULL,
3126 -- Used for 'RESP' and 'SERVRESP' level; Resp Application_Id.
3127 X_LEVEL_VALUE2 in varchar2 default NULL
3128 -- 2nd Level value that you are setting at. This is for the
3129 -- 'SERVRESP' hierarchy.
3130 ) return boolean is
3131
3132 x_level_id NUMBER;
3133 x_level_value_actual NUMBER;
3134 x_last_updated_by NUMBER;
3135 x_last_update_login NUMBER;
3136 x_last_update_date DATE;
3137 x_application_id NUMBER := NULL;
3138 x_profile_option_id NUMBER := NULL;
3139 x_user_name VARCHAR2(100); -- Bug 3203225
3140 x_level_value2_actual NUMBER; -- Added for Server/Resp Hierarchy
3141 l_profile_option_value VARCHAR2(240); -- Bug 3958546
3142 l_defined BOOLEAN; -- Bug 3958546
3143
3144 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE */
3145 X_NAME_UPPER VARCHAR2(80) := upper(X_NAME);
3146
3147 cursor C1 is
3148 select application_id, profile_option_id
3149 from fnd_profile_options po
3150 where po.profile_option_name = X_NAME_UPPER
3151 /* Bug 5591340: FND_PROFILE.SAVE SHOULD NOT UPDATE VALUES FOR END_DATED
3152 ** PROFILE OPTIONS
3153 ** Added these date-sensitive conditions to prevent processing of
3154 ** end-dated profile options
3155 */
3156 and po.start_date_active <= sysdate
3157 and nvl(po.end_date_active, sysdate) >= sysdate;
3158
3159 hashValue binary_integer;
3160
3161 begin
3162
3163 if CORELOG_IS_ENABLED then
3164 FND_CORE_LOG.WRITE_PROFILE_SAVE(
3165 X_NAME_UPPER,
3166 nvl(X_VALUE,'NOVAL')||':ENTER',
3167 X_LEVEL_NAME,
3168 X_LEVEL_VALUE,
3169 X_LEVEL_VALUE_APP_ID,
3170 X_LEVEL_VALUE2);
3171 end if;
3172
3173 -- If profile option value being set is > 240 characters, then place the
3174 -- message FND_PROFILE_OPTION_VAL_TOO_LRG into the error stack and
3175 -- return FALSE.
3176 --
3177 -- The lengthb() function replaced the length() function to handle
3178 -- multibyte characters appropriately.
3179 if lengthb(X_VALUE) > 240 then
3180 fnd_message.set_name('FND', 'FND_PROFILE_OPTION_VAL_TOO_LRG');
3181 fnd_message.set_token('PROFILE_OPTION_NAME', X_NAME);
3182 fnd_message.set_token('PROFILE_OPTION_VALUE', X_VALUE);
3183 return FALSE;
3184 end if;
3185
3186 -- Get the profile ID and Appid for this Profile Name
3187 open C1;
3188 fetch C1 into x_application_id, x_profile_option_id;
3189 if (C1%NOTFOUND) then
3190 return FALSE;
3191 end if;
3192 close C1;
3193
3194 -- The LEVEL_VALUE_APPLICATION_ID applies to the Resp and Server/Resp
3195 -- levels only.
3196 if (X_LEVEL_VALUE_APP_ID is not null and
3197 X_LEVEL_NAME <> 'RESP' and X_LEVEL_NAME <> 'SERVRESP') then
3198 return FALSE;
3199 end if;
3200
3201 -- The LEVEL_VALUE can only be null for SITE level.
3202 if(X_LEVEL_VALUE is NULL) then
3203 x_level_value_actual := 0;
3204 if(X_LEVEL_NAME <> 'SITE') then
3205 return FALSE; -- Only allow X_LEVEL_VALUE NULL at SITE level
3206 end if;
3207
3208 -- The LEVEL_VALUE2 is required for SERVRESP level, -1 should be passed
3209 -- as a default.
3210 elsif ((X_LEVEL_NAME = 'SERVRESP') and (X_LEVEL_VALUE2 is NULL)) then
3211 -- 'SERVRESP' requires a value for X_LEVEL_VALUE2 to save
3212 -- the profile option value properly.
3213 return FALSE;
3214 else
3215 x_level_value_actual := X_LEVEL_VALUE;
3216 if (X_LEVEL_NAME = 'SERVRESP') and (X_LEVEL_VALUE2 is not NULL) then
3217 x_level_value2_actual := X_LEVEL_VALUE2;
3218 end if;
3219 end if;
3220
3221
3222 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
3223 ** Generate hashValue and pass it on to FIND and PUT calls.
3224 */
3225 hashValue := dbms_utility.get_hash_value(X_NAME_UPPER,1,TABLE_SIZE);
3226
3227 if (X_LEVEL_NAME = 'SITE') then
3228 x_level_id := 10001;
3229
3230 if((x_level_id = 10001) and (x_level_value_actual <> 0)) then
3231 return FALSE; -- the only site-level allowed is zero.
3232 end if;
3233
3234 if CORELOG_IS_ENABLED then
3235 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSD call FP.S, SL');
3236 end if;
3237
3238 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3239 ** IF NO CHANGE IS MADE
3240 */
3241 GET_SPECIFIC_DB(
3242 name_z => X_NAME_UPPER,
3243 val_z => l_profile_option_value,
3244 defined_z => l_defined,
3245 level_id_z => x_level_id,
3246 PROFILE_HASH_VALUE => hashValue);
3247
3248 elsif (X_LEVEL_NAME = 'APPL') then
3249
3250 x_level_id := 10002;
3251
3252 if CORELOG_IS_ENABLED then
3253 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSD call FP.S, AL');
3254 end if;
3255
3256 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3257 ** IF NO CHANGE IS MADE
3258 */
3259 GET_SPECIFIC_DB(
3260 name_z => X_NAME_UPPER,
3261 application_id_z => X_LEVEL_VALUE,
3262 val_z => l_profile_option_value,
3263 defined_z => l_defined,
3264 level_id_z => x_level_id,
3265 PROFILE_HASH_VALUE => hashValue);
3266
3267 elsif (X_LEVEL_NAME = 'RESP') then
3268
3269 x_level_id := 10003;
3270
3271 if CORELOG_IS_ENABLED then
3272 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSD call FP.S, RL');
3273 end if;
3274
3275 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3276 ** IF NO CHANGE IS MADE
3277 */
3278 GET_SPECIFIC_DB(
3279 name_z => X_NAME_UPPER,
3280 responsibility_id_z => X_LEVEL_VALUE,
3281 application_id_z => X_LEVEL_VALUE_APP_ID,
3282 val_z => l_profile_option_value,
3283 defined_z => l_defined,
3284 level_id_z => x_level_id,
3285 PROFILE_HASH_VALUE => hashValue);
3286
3287 elsif (X_LEVEL_NAME = 'USER') then
3288
3289 x_level_id := 10004;
3290
3291 if CORELOG_IS_ENABLED then
3292 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSD call FP.S, UL');
3293 end if;
3294
3295 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3296 ** IF NO CHANGE IS MADE
3297 */
3298 GET_SPECIFIC_DB(
3299 name_z => X_NAME_UPPER,
3300 user_id_z => X_LEVEL_VALUE,
3301 val_z => l_profile_option_value,
3302 defined_z => l_defined,
3303 level_id_z => x_level_id,
3304 PROFILE_HASH_VALUE => hashValue);
3305
3306 elsif (X_LEVEL_NAME = 'SERVER') then
3307
3308 x_level_id := 10005;
3309
3310 if CORELOG_IS_ENABLED then
3311 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSD call FP.S, SRVL');
3312 end if;
3313
3314 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3315 ** IF NO CHANGE IS MADE
3316 */
3317 GET_SPECIFIC_DB(
3318 name_z => X_NAME_UPPER,
3319 val_z => l_profile_option_value,
3320 defined_z => l_defined,
3321 server_id_z => X_LEVEL_VALUE,
3322 level_id_z => x_level_id,
3323 PROFILE_HASH_VALUE => hashValue);
3324
3325 elsif (X_LEVEL_NAME = 'ORG') then
3326
3327 x_level_id := 10006;
3328
3329 if CORELOG_IS_ENABLED then
3330 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSD call FP.S, OL');
3331 end if;
3332
3333 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3334 ** IF NO CHANGE IS MADE
3335 */
3336 GET_SPECIFIC_DB(
3337 name_z => X_NAME_UPPER,
3338 val_z => l_profile_option_value,
3339 defined_z => l_defined,
3340 org_id_z => X_LEVEL_VALUE,
3341 level_id_z => x_level_id,
3342 PROFILE_HASH_VALUE => hashValue);
3343
3344 elsif (X_LEVEL_NAME = 'SERVRESP') then --Added for Server/Resp Level
3345
3346 x_level_id := 10007;
3347
3348 if CORELOG_IS_ENABLED then
3349 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'GSDW call FP.S, ServRespL');
3350 end if;
3351
3352 /*
3353 ** Bug 4025399 :3958546:SERVRESP:FND_PROFILE.SAVE RETURNS TRUE BUT
3354 ** DOES NOT SAVE VALUE
3355 **
3356 ** Due to the unique nature of the SERVRESP hierarchy, GET_SPECIFIC_DB
3357 ** cannot be used to check the existing value of the profile option
3358 ** being evaluated since GET_SPECIFIC_DB looks at
3359 ** (RESP+SERVER) > (RESP+-1) > (-1+SERVER) for a value. When saving
3360 ** values, the context passed in should be the only context evaluated.
3361 ** GET_SPECIFIC_DB_WNPS will be used instead.
3362 **
3363 ** GET_SPECIFIC_DB(
3364 ** name_z => X_NAME,
3365 ** responsibility_id_z => X_LEVEL_VALUE,
3366 ** application_id_z => X_LEVEL_VALUE_APP_ID,
3367 ** val_z => l_profile_option_value,
3368 ** defined_z => l_defined,
3369 ** server_id_z => X_LEVEL_VALUE2,
3370 ** level_id_z => x_level_id);
3371 */
3372
3373 /* Bug 3958546: FND_PROFILE.SAVE SHOULD NOT RAISE INVALIDATION EVENT
3374 ** IF NO CHANGE IS MADE
3375 */
3376 GET_SPECIFIC_DB_WNPS(
3377 name_z => X_NAME_UPPER,
3378 responsibility_id_z => X_LEVEL_VALUE,
3379 application_id_z => X_LEVEL_VALUE_APP_ID,
3380 val_z => l_profile_option_value,
3381 defined_z => l_defined,
3382 server_id_z => X_LEVEL_VALUE2,
3383 level_id_z => x_level_id);
3384
3385 else
3386 return FALSE;
3387 end if;
3388
3389 -- If the profile option value being saved is the same as the value
3390 -- obtained from GET_SPECIFIC_DB, then there is no need to go further.
3391 -- Just return TRUE;
3392 if ((l_profile_option_value = X_VALUE) or
3393 (l_profile_option_value is null) and (X_VALUE is null)) then
3394 if CORELOG_IS_ENABLED then
3395 FND_CORE_LOG.WRITE_PROFILE_SAVE(
3396 X_NAME,
3397 nvl(X_VALUE,'NOVAL')||':EXIT',
3398 X_LEVEL_NAME,
3399 X_LEVEL_VALUE,
3400 X_LEVEL_VALUE_APP_ID,
3401 X_LEVEL_VALUE2);
3402 end if;
3403 return TRUE;
3404 end if;
3405
3406 -- If profile option value passed in is NULL, then clear accordingly.
3407 IF (X_VALUE is null) then
3408 -- If SERVRESP level, then take LEVEL_VALUE2 into consideration.
3409 if (x_level_id = 10007) then
3410 -- D E L E T E --
3411 FND_PROFILE_OPTION_VALUES_PKG.DELETE_ROW(x_application_id,
3412 x_profile_option_id, x_level_id, x_level_value_actual,
3413 X_LEVEL_VALUE_APP_ID, x_level_value2_actual);
3414 else
3415 -- D E L E T E --
3416 FND_PROFILE_OPTION_VALUES_PKG.DELETE_ROW(x_application_id,
3417 x_profile_option_id, x_level_id, x_level_value_actual,
3418 X_LEVEL_VALUE_APP_ID);
3419 end if;
3420
3421 ELSE
3422
3423 x_last_update_date := SYSDATE;
3424 x_last_updated_by := fnd_profile.value('USER_ID');
3425 if x_last_updated_by is NULL then
3426 x_last_updated_by := -1;
3427 end if;
3428 x_last_update_login := fnd_profile.value('LOGIN_ID');
3429 if x_last_update_login is NULL then
3430 x_last_update_login := -1;
3431 end if;
3432
3433 -- If profile option value passed in NOT NULL, then update
3434 -- accordingly. If SERVRESP level, then take LEVEL_VALUE2 into
3435 -- consideration.
3436 if (x_level_id = 10007) then
3437 -- U P D A T E --
3438 FND_PROFILE_OPTION_VALUES_PKG.UPDATE_ROW(x_application_id,
3439 x_profile_option_id, x_level_id, x_level_value_actual,
3440 X_LEVEL_VALUE_APP_ID, x_level_value2_actual, X_VALUE,
3441 x_last_update_date, x_last_updated_by, x_last_update_login);
3442 else
3443 -- U P D A T E --
3444 FND_PROFILE_OPTION_VALUES_PKG.UPDATE_ROW(x_application_id,
3445 x_profile_option_id, x_level_id, x_level_value_actual,
3446 X_LEVEL_VALUE_APP_ID, X_VALUE, x_last_update_date,
3447 x_last_updated_by, x_last_update_login);
3448 end if;
3449
3450 END IF;
3451
3452 /* Bug 5477866:INCONSISTENT VALUES RETURNED BY FND_PROFILE.VALUE_SPECIFIC
3453 ** This block of code was separated from the update/insert code block of
3454 ** SAVE() so that deleted values are properly reflected in level caches
3455 ** just like non-NULL values are cached when saved.
3456 ** Previously, only non-NULL values were being cached in level caches
3457 ** when a new non-NULL value was saved, such that when a value is
3458 ** deleted, the get apis would still return the previous cached value.
3459 */
3460 if (x_level_id = 10007) then
3461 invalidate_cache(x_level_name,x_level_value,x_level_value_app_id,
3462 X_NAME_UPPER,x_level_value2);
3463 else
3464 invalidate_cache(x_level_name,x_level_value,x_level_value_app_id,
3465 X_NAME_UPPER);
3466 end if;
3467
3468 -- Cache the value in user-level table.
3469 if (x_level_id = 10004 and
3470 profiles_user_id = nvl(x_level_value,profiles_user_id)) then
3471 if CORELOG_IS_ENABLED then
3472 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'UL Val cached in USER_TABS');
3473 end if;
3474 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),USER_NAME_TAB,
3475 USER_VAL_TAB,hashValue);
3476 end if;
3477
3478 -- Cache the value in resp-level table.
3479 if (x_level_id = 10003 and
3480 profiles_resp_id = nvl(x_level_value,profiles_resp_id) and
3481 profiles_appl_id = nvl(x_level_value_app_id,profiles_appl_id)) then
3482 if CORELOG_IS_ENABLED then
3483 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'RL Val cached in RESP_TABS');
3484 end if;
3485 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),RESP_NAME_TAB,
3486 RESP_VAL_TAB,hashValue);
3487 end if;
3488
3489 -- Cache the value in appl-level table.
3490 if (x_level_id = 10002 and
3491 profiles_appl_id = nvl(x_level_value,profiles_appl_id)) then
3492 if CORELOG_IS_ENABLED then
3493 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'AL Val cached in APPL_TABS');
3494 end if;
3495 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),APPL_NAME_TAB,
3496 APPL_VAL_TAB,hashValue);
3497 end if;
3498
3499 -- Cache the value in server-level table.
3500 if (x_level_id = 10005 and
3501 profiles_server_id = nvl(x_level_value,profiles_server_id)) then
3502 if CORELOG_IS_ENABLED then
3503 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,
3504 'SRVL Val cached in SERVER_TABS');
3505 end if;
3506 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),SERVER_NAME_TAB,
3507 SERVER_VAL_TAB,hashValue);
3508 end if;
3509
3510 -- Cache the value in org-level table.
3511 if (x_level_id = 10006) then
3512 if (profiles_org_id = nvl(x_level_value,profiles_org_id)) then
3513 if CORELOG_IS_ENABLED then
3514 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'OL Val cached in ORG_TABS');
3515 end if;
3516 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),ORG_NAME_TAB,
3517 ORG_VAL_TAB,hashValue);
3518 end if;
3519 end if;
3520
3521 -- Cache the value in servresp-level table.
3522 if (x_level_id = 10007 and
3523 profiles_resp_id = nvl(x_level_value,profiles_resp_id) and
3524 profiles_server_id = nvl(x_level_value2,profiles_server_id)) then
3525 if CORELOG_IS_ENABLED then
3526 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,
3527 'ServRespL Val cached in SERVRESP_TABS');
3528 end if;
3529 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),SERVRESP_NAME_TAB,
3530 SERVRESP_VAL_TAB,hashValue);
3531 end if;
3532
3533 -- Cache the value in site-level table.
3534 if (x_level_id = 10001) then
3535 if CORELOG_IS_ENABLED then
3536 FND_CORE_LOG.PUT_LINE(X_NAME_UPPER,'SL Val cached in SITE_TABS');
3537 end if;
3538 PUT(X_NAME_UPPER,nvl(x_value,FND_UNDEFINED_VALUE),SITE_NAME_TAB,
3539 SITE_VAL_TAB,hashValue);
3540 end if;
3541
3542 /* Bug 3203225: PREFERENCES NOT UPDATED ON FLY IN WF_ROLES VIEW
3543 ** needs to call FND_USER_PKG.User_Synch() whenever an update to
3544 ** ICX_LANGUAGE or ICX_TERRITORY is updated at the user level.
3545 */
3546 if ((X_NAME_UPPER = 'ICX_LANGUAGE')
3547 or (X_NAME_UPPER = 'ICX_TERRITORY')) then
3548 if ((X_LEVEL_NAME = 'USER') and (X_LEVEL_VALUE is not null)) then
3549 select user_name
3550 into x_user_name
3551 from fnd_user
3552 where user_id = to_number(X_LEVEL_VALUE);
3553
3554 FND_USER_PKG.user_synch(x_user_name);
3555 end if;
3556 end if;
3557
3558 -- Log API exit
3559 if CORELOG_IS_ENABLED then
3560 FND_CORE_LOG.WRITE_PROFILE_SAVE(
3561 X_NAME,
3562 X_VALUE ||':EXIT',
3563 X_LEVEL_NAME,
3564 X_LEVEL_VALUE,
3565 X_LEVEL_VALUE_APP_ID,
3566 X_LEVEL_VALUE2);
3567 end if;
3568
3569 return TRUE;
3570
3571 end SAVE;
3572
3573 /*
3574 ** GET_SPECIFIC - Get a profile value for a specific user/resp/appl combo.
3575 ** Default for user/resp/appl is the current login.
3576 */
3577 procedure GET_SPECIFIC(
3578 name_z in varchar2,
3579 user_id_z in number default null,
3580 responsibility_id_z in number default null,
3581 application_id_z in number default null,
3582 val_z out NOCOPY varchar2,
3583 defined_z out NOCOPY boolean,
3584 org_id_z in number default null,
3585 server_id_z in number default null) is
3586
3587 /* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE */
3588 NAME_UPPER varchar2(80) := UPPER(name_z);
3589
3590 begin
3591
3592 -- Log API entry
3593 if CORELOG_IS_ENABLED then
3594 CORELOG(
3595 NAME_UPPER,
3596 nvl(val_z, 'NOVAL'),
3597 'Enter FP.GS',
3598 user_id_z,
3599 responsibility_id_z,
3600 application_id_z,
3601 org_id_z,
3602 server_id_z);
3603 end if;
3604
3605 /* Bug 5477866: INCONSISTENT VALUES RETURNED BY
3606 ** FND_PROFILE.VALUE_SPECIFIC
3607 ** Check if fnd_cache_versions was updated. This refreshes level caches
3608 ** in order for value_specific to return accurate values should a new
3609 ** profile value be saved in another session. This will introduce a
3610 ** performance degradation which has been deemed necessary for
3611 ** value_specific return values.
3612 */
3613 CHECK_CACHE_VERSIONS();
3614
3615 /* Bug 4438015: APPSPERF: TOO MANY EXECUTIONS OF CURSOR PROFILE_INFO
3616 ** If the context passed in is exactly the same as the current context,
3617 ** then redirect to GET instead.
3618 */
3619 if (user_id_z = PROFILES_USER_ID) and
3620 (responsibility_id_z = PROFILES_RESP_ID) and
3621 (application_id_z = PROFILES_APPL_ID) and
3622 (org_id_z = PROFILES_ORG_ID) and
3623 (server_id_z = PROFILES_SERVER_ID) then
3624
3625 if CORELOG_IS_ENABLED then
3626 CORELOG(NAME_UPPER,nvl(val_z, 'NOVAL'),
3627 'No context change in FP.GS, Redirect to FP.G');
3628 end if;
3629
3630 GET(NAME_UPPER, val_z);
3631
3632 if (val_z is not null) and
3633 (val_z <> FND_UNDEFINED_VALUE) then
3634 defined_z := TRUE;
3635 end if;
3636 -- If NULLs were passed for the context levels, default to current
3637 -- context. This would normally happen when value_specific was called as
3638 -- such:
3639 -- fnd_profile.value_specific('PROFILE_OPTION_NAME');
3640 -- Note that there was no context passed in. Defaulting to current
3641 -- context effectively satisfies the IF condition above. Hence, redirect
3642 -- to GET also.
3643 elsif (user_id_z is NULL) and
3644 (responsibility_id_z is NULL) and
3645 (application_id_z is NULL) and
3646 (org_id_z is NULL) and
3647 (server_id_z is NULL) then
3648
3649 if CORELOG_IS_ENABLED then
3650 CORELOG(NAME_UPPER,nvl(val_z, 'NOVAL'),
3651 'No context passed in FP.GS, Redirect to FP.G');
3652 end if;
3653
3654 GET(NAME_UPPER, val_z);
3655
3656 if (val_z is not null) and
3657 (val_z <> FND_UNDEFINED_VALUE) then
3658 defined_z := TRUE;
3659 end if;
3660 else
3661 -- If a specific level context is passed, then proceed the usual way.
3662 -- This will likely hit get_specific_db_wnps and make a database
3663 -- fetch.
3664 GET_SPECIFIC_WNPS(NAME_UPPER,user_id_z,responsibility_id_z,
3665 application_id_z,val_z,defined_z,org_id_z,server_id_z);
3666 end if;
3667
3668 -- Log API exit
3669 if CORELOG_IS_ENABLED then
3670 CORELOG(
3671 NAME_UPPER,
3672 nvl(val_z, 'NOVAL'),
3673 'Exit FP.GS',
3674 user_id_z,
3675 responsibility_id_z,
3676 application_id_z,
3677 org_id_z,
3678 server_id_z);
3679 end if;
3680
3681 end GET_SPECIFIC;
3682
3683 /*
3684 ** VALUE_SPECIFIC - Get profile value for a specific context
3685 **
3686 */
3687 function VALUE_SPECIFIC(
3688 NAME in varchar2,
3689 USER_ID in number default null,
3690 RESPONSIBILITY_ID in number default null,
3691 APPLICATION_ID in number default null,
3692 ORG_ID in number default null,
3693 SERVER_ID in number default null) return varchar2 is
3694
3695 RETVALUE varchar2(255);
3696 DEFINED boolean;
3697
3698 begin
3699
3700 -- Log API entry
3701 if CORELOG_IS_ENABLED then
3702 CORELOG(
3703 NAME,
3704 nvl(RETVALUE, 'NOVAL'),
3705 'Enter FP.VS',
3706 USER_ID,
3707 RESPONSIBILITY_ID,
3708 APPLICATION_ID,
3709 ORG_ID,
3710 SERVER_ID);
3711 end if;
3712
3713 -- Use GET_SPECIFIC() to obtain value
3714 GET_SPECIFIC(NAME,USER_ID,RESPONSIBILITY_ID,APPLICATION_ID,RETVALUE,
3715 DEFINED,ORG_ID,SERVER_ID);
3716
3717 -- Log API exit
3718 if CORELOG_IS_ENABLED then
3719 CORELOG(
3720 NAME,
3721 nvl(RETVALUE, 'NOVAL'),
3722 'Exit FP.VS',
3723 USER_ID,
3724 RESPONSIBILITY_ID,
3725 APPLICATION_ID,
3726 ORG_ID,
3727 SERVER_ID);
3728 end if;
3729
3730 if (DEFINED) then
3731 return (RETVALUE);
3732 else
3733 return(NULL);
3734 end if;
3735
3736 end VALUE_SPECIFIC;
3737
3738 /*
3739 ** VALUE - get profile value, return as function value
3740 */
3741 function VALUE(NAME in varchar2) return varchar2 is
3742 RETVALUE varchar2(255);
3743 begin
3744
3745 -- Log API entry
3746 if CORELOG_IS_ENABLED then
3747 CORELOG(NAME,nvl(RETVALUE,'NOVAL'),'Enter FP.V');
3748 end if;
3749
3750 -- Use GET() to obtain value
3751 GET(NAME, RETVALUE);
3752
3753 -- Log API exit
3754 if CORELOG_IS_ENABLED then
3755 CORELOG(NAME,nvl(RETVALUE,'NOVAL'),'Exit FP.V');
3756 end if;
3757
3758 return (RETVALUE);
3759 end VALUE;
3760
3761 /*
3762 ** VALUE_WNPS
3763 ** returns the value of a profile option without caching it.
3764 **
3765 ** The main usage for this routine would be in a SELECT statement where
3766 ** VALUE() is not allowed since it writes package state.
3767 **
3768 ** This routine does the same thing as VALUE(); it returns a profile value
3769 ** from the profile cache, or from the database if it isn't already in the
3770 ** profile cache already. The only difference between this and VALUE() is
3771 ** that this will not put the value into the cache if it is not already
3772 ** there, so repeated calls to this can be slower because it will have to
3773 ** hit the database each time for the profile value.
3774 **
3775 ** In most cases, however, you can and should use VALUE() instead of
3776 ** VALUE_WNPS(), because VALUE() will give better performance.
3777 */
3778 function VALUE_WNPS(NAME in varchar2) return varchar2 is
3779 TABLE_INDEX binary_integer;
3780 DEFINED boolean;
3781 OUTVAL varchar2(255);
3782 NAME_UPPER varchar2(80) := UPPER(NAME);
3783 begin
3784
3785 -- Search for the profile option
3786 TABLE_INDEX := FIND(NAME_UPPER);
3787
3788 if TABLE_INDEX < TABLE_SIZE then
3789 OUTVAL := VAL_TAB(TABLE_INDEX);
3790 else
3791 -- Can't find the value in the table; look in the database
3792 GET_SPECIFIC_WNPS(NAME_UPPER, PROFILES_USER_ID, PROFILES_RESP_ID,
3793 PROFILES_APPL_ID,OUTVAL,DEFINED,PROFILES_ORG_ID,
3794 PROFILES_SERVER_ID);
3795 if ( not defined) then
3796 OUTVAL := null;
3797 end if;
3798 end if;
3799
3800 return outval;
3801 exception
3802 when others then
3803 return null;
3804 end VALUE_WNPS;
3805
3806
3807 /*
3808 ** PUTMULTIPLE - puts multiple option pairs in the table
3809 **
3810 ** AOL INTERNAL USE ONLY
3811 **
3812 ** The name and val VARCHAR2s are of max size 2000, and hold the
3813 ** concatenations of the strings for each individual pair, with null
3814 ** terminators (CHR(0)) to seperate the values. The number of pairs
3815 ** is passed in numval. This setup is to avoid the overhead of
3816 ** calling the put routine multiple times.
3817 */
3818 procedure PUTMULTIPLE(
3819 NAMES in varchar2,
3820 VALS in varchar2,
3821 NUM in number) is
3822 PAIRNUM number;
3823 NSTARTLOC number;
3824 NENDLOC number;
3825 VSTARTLOC number;
3826 VENDLOC number;
3827 ONENAME varchar2(81);
3828 ONEVAL varchar2(256);
3829
3830 begin
3831
3832 NSTARTLOC:= 1;
3833 VSTARTLOC:= 1;
3834
3835 for PAIRNUM in 1.. NUM loop
3836 NENDLOC := instr(NAMES, chr(0), NSTARTLOC);
3837 ONENAME := substr(NAMES, NSTARTLOC, NENDLOC - NSTARTLOC);
3838 NSTARTLOC := NENDLOC + 1;
3839
3840 VENDLOC := instr(VALS, chr(0), VSTARTLOC);
3841 ONEVAL := substr(VALS, VSTARTLOC, VENDLOC - VSTARTLOC);
3842 VSTARTLOC := VENDLOC + 1;
3843
3844 PUT(ONENAME, ONEVAL);
3845 end loop;
3846
3847 exception
3848 when others then
3849 null;
3850 end PUTMULTIPLE;
3851
3852 /*
3853 ** FOR AOL INTERNAL USE ONLY - DO NOT CALL DIRECTLY,
3854 ** CALL VIA FND_GLOBAL.INITIALIZE('ORG_ID',org_id)
3855 ** FND_PROFILE.INITIALIZE also calls this API to initialize the org context.
3856 **
3857 ** initialize_org_context - Initializes the org context used by profiles.
3858 ** The org-level cache is cleared of all database (non-put) options first.
3859 ** Sets PROFILES_ORG_ID to the current value fnd_global.org_id
3860 */
3861 procedure INITIALIZE_ORG_CONTEXT
3862 is
3863 begin
3864 -- Clear org-level cache, if applicable
3865 if ((PROFILES_ORG_ID is null) or (PROFILES_ORG_ID <> fnd_global.org_id))
3866 then
3867 ORG_NAME_TAB.DELETE();
3868 ORG_VAL_TAB.DELETE();
3869 end if;
3870
3871 -- Set profiles org context variable to fnd_global.org_id
3872 PROFILES_ORG_ID := fnd_global.org_id;
3873
3874 if release_version < 12 then
3875 -- For releases less than R12, the ORG_ID profile is the source of the
3876 -- org context. FND_GLOBAL.ORG_ID = FND_PROFILE.VALUE('ORG_ID')
3877 PUT('ORG_ID', to_char(PROFILES_ORG_ID));
3878 else
3879 -- Bug 7423364: For R12, the profile option ORG_ID is not always an
3880 -- equivalent of FND_GLOBAL.ORG_ID, which is the org context. The
3881 -- global variable PROFILES_ORG_ID is the org context used for
3882 -- evaluating org-level profile option values and should be equal to
3883 -- FND_GLOBAL.ORG_ID. A value fetch on the profile option ORG_ID
3884 -- should return the profile option table value, not the org context.
3885 -- This behavior was confirmed with JMARY and SHNARAYA of the MO Team.
3886 -- CURRENT_ORG_CONTEXT is being introduced so that profiles code can
3887 -- provide similar functionality such that FND_GLOBAL.ORG_ID will be
3888 -- equivalent to FND_PROFILE.VALUE('CURRENT_ORG_CONTEXT').
3889 -- FND_GLOBAL.VALUE('ORG_ID') will return a value obtained in the
3890 -- FND_PROFILE_OPTION_VALUES table.
3891 PUT('CURRENT_ORG_CONTEXT', to_char(PROFILES_ORG_ID));
3892 end if;
3893
3894 PUT('ORG_NAME', fnd_global.org_name);
3895
3896 end INITIALIZE_ORG_CONTEXT;
3897
3898 /*
3899 ** FOR AOL INTERNAL USE ONLY - DO NOT CALL DIRECTLY,
3900 ** CALL VIA FND_GLOBAL.APPS_INITIALIZE
3901 ** initialize - Initialize the internal profile information
3902 ** The cache is cleared of all database (non-put) options first.
3903 ** Initializes the profiles for the level context information.
3904 **
3905 */
3906 procedure INITIALIZE(
3907 USER_ID_Z in number default NULL,
3908 RESPONSIBILITY_ID_Z in number default NULL,
3909 APPLICATION_ID_Z in number default NULL,
3910 SITE_ID_Z in number default NULL) is
3911
3912 NAME varchar2(256);
3913 ORG varchar2(8);
3914 SESSION_ID number;
3915 CACHE_VERSION number;
3916 same_version boolean;
3917 TEMP_UTL varchar2(2000);
3918
3919 begin
3920
3921 -- Clear old db entries
3922 SESSION_ID := ICX_SEC.G_SESSION_ID;
3923
3924 -- Check cache versions
3925 CHECK_CACHE_VERSIONS();
3926
3927 --
3928 -- Clear the "put" cache when session_id changes.
3929 -- NOTE: This needs to stay even when other caches are not
3930 -- cleared on session change. Puts are always only good for
3931 -- the current session.
3932 --
3933 if((PROFILES_SESSION_ID is null) or (SESSION_ID is null) or
3934 (PROFILES_SESSION_ID = -1) or (SESSION_ID = -1) or
3935 (PROFILES_SESSION_ID <> SESSION_ID)) then
3936 NAME_TAB.DELETE();
3937 VAL_TAB.DELETE();
3938 PROFILE_OPTION_EXISTS := TRUE;
3939 if CORELOG_IS_ENABLED then
3940 fnd_core_log.put_line('Generic PUT Cache purged');
3941 end if;
3942 end if;
3943
3944 --
3945 -- Clear the individual caches whose levels have changed.
3946 --
3947 if ((PROFILES_USER_ID is null) or (USER_ID_Z is null) or
3948 (PROFILES_USER_ID <> USER_ID_Z)) then
3949 USER_NAME_TAB.DELETE();
3950 USER_VAL_TAB.DELETE();
3951 end if;
3952
3953 if ((PROFILES_RESP_ID is null) or (RESPONSIBILITY_ID_Z is null) or
3954 (PROFILES_RESP_ID <> RESPONSIBILITY_ID_Z)) then
3955 RESP_NAME_TAB.DELETE();
3956 RESP_VAL_TAB.DELETE();
3957 -- A change in responsibility affects the SERVRESP hierarchy and the cache
3958 -- should be emptied if the responsibility changes.
3959 SERVRESP_NAME_TAB.DELETE();
3960 SERVRESP_VAL_TAB.DELETE();
3961 end if;
3962
3963 if ((PROFILES_APPL_ID is null) or (APPLICATION_ID_Z is null) or
3964 (PROFILES_APPL_ID <> APPLICATION_ID_Z)) then
3965 APPL_NAME_TAB.DELETE();
3966 APPL_VAL_TAB.DELETE();
3967 /* Bug 4738009: RESP SWITCH DOES NOT FLUSH RESP-LEVEL CACHE IF SAME
3968 ** RESP_ID BUT DIFF APPL_ID
3969 ** It is possible for responsibility_ids to be the same between
3970 ** applications. So, if there is a switch in context between
3971 ** applications having the same responsibility_id, the resp-level
3972 ** and servresp-level cache is flushed.
3973 */
3974 if (PROFILES_RESP_ID = RESPONSIBILITY_ID_Z) then
3975 RESP_NAME_TAB.DELETE();
3976 RESP_VAL_TAB.DELETE();
3977 SERVRESP_NAME_TAB.DELETE();
3978 SERVRESP_VAL_TAB.DELETE();
3979 end if;
3980 end if;
3981
3982 if ((PROFILES_SERVER_ID is null) or
3983 (PROFILES_SERVER_ID <> fnd_global.server_id)) then
3984 SERVER_NAME_TAB.DELETE();
3985 SERVER_VAL_TAB.DELETE();
3986 -- A change in server affects the SERVRESP hierarchy and the cache
3987 -- should be emptied if the server changes.
3988 SERVRESP_NAME_TAB.DELETE();
3989 SERVRESP_VAL_TAB.DELETE();
3990 end if;
3991
3992 PROFILES_USER_ID := USER_ID_Z;
3993 PROFILES_RESP_ID := RESPONSIBILITY_ID_Z;
3994 PROFILES_APPL_ID := APPLICATION_ID_Z;
3995 PROFILES_SERVER_ID := fnd_global.server_id;
3996 PROFILES_SESSION_ID := SESSION_ID;
3997
3998 -- Set login appl/resp/user specific security profiles
3999 if (user_id_z is not null) then
4000 PUT('USER_ID', to_char(user_id_z));
4001
4002 if (user_id_z = fnd_global.user_id) then
4003 -- Use global to avoid select if current user
4004 NAME := fnd_global.user_name;
4005 elsif (user_id_z = -1) then
4006 NAME := 'DEFAULT_USER';
4007 else
4008 begin
4009 SELECT USER_NAME
4010 INTO NAME
4011 FROM FND_USER
4012 WHERE USER_ID = user_id_z;
4013 exception
4014 when others then
4015 NAME := '';
4016 end;
4017 end if;
4018 PUT('USERNAME', NAME);
4019 end if;
4020
4021 -- For FND_PROFILE.INITIALIZE(), the CORELOG
4022 -- LOG_PROFNAME argument will be the code phase. LOG_PROFVAL will be
4023 -- user_name.
4024 if CORELOG_IS_ENABLED then
4025 CORELOG(
4026 'PROFILE_INIT',
4027 NAME,
4028 'FP.I',
4029 USER_ID_Z,
4030 RESPONSIBILITY_ID_Z,
4031 APPLICATION_ID_Z,
4032 fnd_global.org_id,
4033 fnd_global.server_id);
4034 end if;
4035
4036 if ((responsibility_id_z is not null) and (application_id_z is not null))
4037 then
4038 PUT('RESP_ID', to_char(responsibility_id_z));
4039 PUT('RESP_APPL_ID', to_char(application_id_z));
4040 if ((responsibility_id_z = fnd_global.resp_id) and
4041 (application_id_z = fnd_global.resp_appl_id)) then
4042 -- Use global to avoid select if current resp
4043 NAME := fnd_global.resp_name;
4044 elsif ((responsibility_id_z = -1) and (application_id_z = -1)) then
4045 NAME := 'DEFAULT_RESP';
4046 else
4047 begin
4048 SELECT RESPONSIBILITY_NAME
4049 INTO NAME
4050 FROM FND_RESPONSIBILITY_VL
4051 WHERE RESPONSIBILITY_ID = responsibility_id_z
4052 AND APPLICATION_ID = application_id_z;
4053 exception
4054 when others then
4055 NAME := '';
4056 end;
4057 end if;
4058 PUT('RESP_NAME', NAME);
4059 end if;
4060
4061 -- Set the Server profile
4062 PUT('SERVER_ID', to_char(PROFILES_SERVER_ID));
4063 begin
4064 select node_name
4065 into NAME
4066 from fnd_nodes
4067 where node_id = PROFILES_SERVER_ID;
4068 exception
4069 when others then
4070 NAME := '';
4071 end;
4072 PUT('SERVER_NAME', NAME);
4073
4074 -- Finally, initialize the org context
4075 initialize_org_context;
4076
4077 end INITIALIZE;
4078
4079 /*
4080 ** GET_TABLE_VALUE - get the value of a profile option from the table
4081 */
4082 function GET_TABLE_VALUE(NAME in varchar2) return varchar2 is
4083 TABLE_INDEX binary_integer;
4084 RETVAL varchar2(255);
4085 NAME_UPPER varchar2(80) := UPPER(NAME);
4086 begin
4087
4088 TABLE_INDEX := FIND(NAME_UPPER);
4089 if TABLE_INDEX < TABLE_SIZE then
4090 RETVAL := VAL_TAB(TABLE_INDEX);
4091 else
4092 RETVAL := null;
4093 end if;
4094 return RETVAL;
4095
4096 exception
4097 when others then
4098 return null;
4099
4100 end GET_TABLE_VALUE;
4101
4102 /*
4103 ** GET_ALL_TABLE_VALUES - get all the values from the table
4104 */
4105 function GET_ALL_TABLE_VALUES(DELIM in varchar2) return varchar2 is
4106 TABLE_INDEX binary_integer;
4107 RETVAL varchar2(32767);
4108 VAL varchar2(1000);
4109 begin
4110 if (not INSERTED) then
4111 return null;
4112 end if;
4113
4114 TABLE_INDEX := 1;
4115 RETVAL := '';
4116
4117 while (TABLE_INDEX < TABLE_SIZE) loop
4118
4119 VAL := NAME_TAB(TABLE_INDEX) || DELIM ||
4120 VAL_TAB(TABLE_INDEX) || DELIM;
4121
4122 if length(VAL) + length(RETVAL) > 32767 then
4123 return RETVAL;
4124 end if;
4125
4126 RETVAL := RETVAL || VAL;
4127 TABLE_INDEX := TABLE_INDEX + 1;
4128
4129 end loop;
4130
4131 return RETVAL;
4132
4133 exception
4134 when others then
4135 return null;
4136
4137 end GET_ALL_TABLE_VALUES;
4138
4139 /*
4140 * bumpCacheVersion_RF
4141 * The rule function for FND's subscription on the
4142 * oracle.apps.fnd.profile.value.update event. This function calls
4143 * FND_CACHE_VERSION_PKG.bump_version to increase the version of the
4144 * appropriate profile level cache.
4145 */
4146 function bumpCacheVersion_RF (
4147 p_subscription_guid in raw,
4148 p_event in out NOCOPY WF_EVENT_T)
4149 return varchar2 is
4150
4151 l_event_key varchar2(255);
4152 l_level_id number;
4153 l_cache_name varchar2(30);
4154
4155 begin
4156 -- First thing to do is to get the event key. The event key holds the
4157 -- information that is required to determine which profile level cache
4158 -- needs a version bump. The event key is passed in this format:
4159 -- level_id||':'||level_value||':'||level_value_appl_id||':'||name
4160 l_event_key := p_event.getEventKey();
4161
4162 -- Since all this function does is call
4163 -- FND_CACHE_VERSION_PKG.bump_version, the only information required from
4164 -- the event key is the level_id. This will indicate the profile level
4165 -- cache to be bumped.
4166 l_level_id:=to_number(SUBSTR(l_event_key,1,INSTR(l_event_key,':')-1));
4167
4168 -- Using the level_id, determine the profile level cache name.
4169 if (l_level_id = 10001) then
4170 l_cache_name := SITE_CACHE;
4171 elsif (l_level_id = 10002) then
4172 l_cache_name := APPL_CACHE;
4173 elsif (l_level_id = 10003) then
4174 l_cache_name := RESP_CACHE;
4175 elsif (l_level_id = 10004) then
4176 l_cache_name := USER_CACHE;
4177 elsif (l_level_id = 10005) then
4178 l_cache_name := SERVER_CACHE;
4179 elsif (l_level_id = 10006) then
4180 l_cache_name := ORG_CACHE;
4181 elsif (l_level_id = 10007) then
4182 l_cache_name := SERVRESP_CACHE;
4183 else
4184 -- The level_id obtained is not valid.
4185 return 'ERROR';
4186 end if;
4187
4188 -- Bump cache version using the appropriate cache name
4189 FND_CACHE_VERSIONS_PKG.bump_version(l_cache_name);
4190 return 'SUCCESS';
4191
4192 exception
4193 when others then
4194 WF_CORE.CONTEXT('FND_PROFILE', 'bumpCacheVersion_RF',
4195 p_event.getEventName(), p_subscription_guid);
4196 WF_EVENT.setErrorInfo(p_event, 'ERROR');
4197 return 'ERROR';
4198 end;
4199
4200
4201 /*
4202 ** DELETE - deletes the value of a profile option permanently from the
4203 ** database, at any level. This routine serves as a wrapper to
4204 ** the SAVE routine which means that this routine can be used at
4205 ** runtime or during patching. Like the SAVE routine, this
4206 ** routine will not actually commit the changes; the caller must
4207 ** commit. This API was added for enhancement request 4430579.
4208 **
4209 ** ('SITE', 'APPL', 'RESP', 'USER', 'SERVER', 'ORG', or 'SERVRESP').
4210 **
4211 ** Examples of use:
4212 ** FND_PROFILE.DELETE('P_NAME', 'SITE');
4213 ** FND_PROFILE.DELETE('P_NAME', 'APPL', 321532);
4214 ** FND_PROFILE.DELETE('P_NAME', 'RESP', 321532, 345234);
4215 ** FND_PROFILE.DELETE('P_NAME', 'USER', 123321);
4216 ** FND_PROFILE.DELETE('P_NAME', 'SERVER', 25);
4217 ** FND_PROFILE.DELETE('P_NAME', 'ORG', 204);
4218 ** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', 321532, 345234, 25);
4219 ** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', 321532, 345234, -1);
4220 ** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', -1, -1, 25);
4221 **
4222 ** returns: TRUE if successful, FALSE if failure.
4223 **
4224 */
4225 function DELETE(
4226 X_NAME in varchar2,
4227 -- Profile name you are setting
4228 X_LEVEL_NAME in varchar2,
4229 -- Level that you're setting at: 'SITE','APPL','RESP','USER', etc.
4230 X_LEVEL_VALUE in varchar2 default NULL,
4231 -- Level value that you are setting at, e.g. user id for 'USER' level.
4232 -- X_LEVEL_VALUE is not used at site level.
4233 X_LEVEL_VALUE_APP_ID in varchar2 default NULL,
4234 -- Used for 'RESP' and 'SERVRESP' level; Resp Application_Id.
4235 X_LEVEL_VALUE2 in varchar2 default NULL
4236 -- 2nd Level value that you are setting at. This is for the 'SERVRESP'
4237 -- hierarchy only.
4238 ) return boolean is
4239
4240 l_deleted boolean;
4241
4242 begin
4243
4244 -- Call SAVE routine and pass NULL for the profile option value. This
4245 -- physically deletes the row from fnd_profile_option_values.
4246 l_deleted := SAVE(X_NAME,
4247 NULL,
4248 X_LEVEL_NAME,
4249 X_LEVEL_VALUE,
4250 X_LEVEL_VALUE_APP_ID,
4251 X_LEVEL_VALUE2);
4252
4253 return l_deleted;
4254
4255 end;
4256
4257 begin
4258 -- Initialization section
4259 TABLE_SIZE := 8192;
4260
4261 end FND_PROFILE;
[Home] [Help]