1 PACKAGE BODY fnd_profile AS
2 /* $Header: AFPFPROB.pls 120.43.12020000.3 2013/03/21 13:56:52 rarmaly 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 be used for Public Put cache
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 public cache */
12 name_tab name_tab_type; /* the table of names for public cache */
13 /* Increase table size to reduce potential hash collisions - bug 12975860 */
14 table_size BINARY_INTEGER := 2147483646; /* the size of above tables*/
15 /* change INSERTED to number to account for PUT deletes - bug 12975860 */
16 inserted NUMBER := 0; /* count of PUT profiles stored */
17
18 /*
19 ** Global Session ID tracker
20 */
21 current_icx_session_id NUMBER := DEFAULT_CONTEXT;
22
23 /*
24 ** Global Context values
25 */
26 profiles_user_id NUMBER := DEFAULT_CONTEXT;
27 profiles_resp_id NUMBER := DEFAULT_CONTEXT;
28 profiles_appl_id NUMBER := DEFAULT_CONTEXT;
29 profiles_server_id NUMBER := DEFAULT_CONTEXT;
30 profiles_org_id NUMBER := DEFAULT_CONTEXT;
31
32 /*
33 ** Constant string used to indicate that a cache entry is undefined.
34 */
35 FND_UNDEFINED_VALUE VARCHAR2(30) := '**FND_UNDEFINED_VALUE**';
36
37 /* Global LEVEL_IDs */
38 SITE_LEVEL_ID NUMBER := 10001;
39 APPL_LEVEL_ID NUMBER := 10002;
40 RESP_LEVEL_ID NUMBER := 10003;
41 USER_LEVEL_ID NUMBER := 10004;
42 SERVER_LEVEL_ID NUMBER := 10005;
43 ORG_LEVEL_ID NUMBER := 10006;
44 SERVRESP_LEVEL_ID NUMBER := 10007;
45
46 /*
47 ** Constant string used to indicate a delete request in PUT cache.
48 ** -- Bug 12875860 - PER Rewrite
49 */
50 FND_DELETE_VALUE VARCHAR2(30) := '**FND_DELETE_VALUE**';
51
52 /*
53 ** Global variable used to indicate that the PUT cache was cleared during
54 ** the current run of FND_PROFILE.INITIALIZE -- Bug 12875860 - PER Rewrite
55 */
56 put_cache_is_clear BOOLEAN;
57
58 /*
59 ** Global variable used to identify core logging is enabled or not.
60 */
61 corelog_is_enabled BOOLEAN := nvl(fnd_core_log.is_enabled, FALSE);
62
63 /*
64 ** Global variable used to identify the profile that core logging tracks.
65 */
66 profile_to_log VARCHAR2(80) := fnd_core_log.profile_to_log;
67
68 /*
69 ** Global variable that stores Applications Release Major Version
70 */
71 major_version NUMBER := fnd_release.major_version;
72
73 /*
74 ** Global variable that stores Applications Release Minor Version
75 */
76 minor_version NUMBER := fnd_release.minor_version;
77
78 /*
79 ** Global variable that stores Applications Release Point Version
80 */
81 point_version NUMBER := fnd_release.point_version;
82
83 /*
84 ** Global variable that stores the start time of initial
85 ** FND_PROFILE use
86 */
87 start_time NUMBER := dbms_utility.get_time;
88
89 /*========================================================================
90 | Private APIs
91 ========================================================================*/
92
93 /*
94 ** corelog
95 ** wrapper to FND_CORE_LOG.WRITE_PROFILE with defaulting current
96 ** profile context.
97 */
98 PROCEDURE corelog
99 (
100 profileName IN VARCHAR2,
101 profileValue IN VARCHAR2 DEFAULT NULL,
102 currentAPI IN VARCHAR2,
103 userId IN NUMBER DEFAULT profiles_user_id,
104 respId IN NUMBER DEFAULT profiles_resp_id,
105 applId IN NUMBER DEFAULT profiles_appl_id,
106 orgId IN NUMBER DEFAULT profiles_org_id,
107 serverId IN NUMBER DEFAULT profiles_server_id
108 ) IS
109 BEGIN
110 IF corelog_is_enabled THEN
111 fnd_core_log.write_profile(profileName,
112 profileValue,
113 currentAPI,
114 userId,
115 respId,
116 applId,
117 orgId,
118 serverId,
119 to_char(dbms_utility.get_time - start_time,
120 '999,999,999.99') || ' ms.');
121 END IF;
122 END corelog;
123
124 /*
125 ** corelog_using_name
126 ** wrapper to FND_CORE_LOG.PUT_LINE, uses profile option name for
127 ** filtering.
128 */
129 PROCEDURE corelog_using_name
130 (
131 profileName IN VARCHAR2,
132 textLine IN VARCHAR2
133 ) IS
134 BEGIN
135 IF corelog_is_enabled THEN
136 IF profile_to_log IS NULL THEN
137 fnd_core_log.put_line(profileName || ':' || textLine || ':' ||
138 to_char(dbms_utility.get_time - start_time,
139 '999,999,999.99') || ' ms.');
140 ELSE
141 IF profileName = profile_to_log THEN
142 fnd_core_log.put_line(profileName || ':' || textLine || ':' ||
143 to_char(dbms_utility.get_time - start_time,
144 '999,999,999.99') || ' ms.');
145 END IF;
146 END IF;
147 END IF;
148 END corelog_using_name;
149
150 /*
151 ** corelog_line
152 ** wrapper to FND_CORE_LOG.PUT_LINE, logs a textline and does not filter
153 */
154 PROCEDURE corelog_line(textLine IN VARCHAR2) IS
155 BEGIN
156 IF corelog_is_enabled THEN
157 fnd_core_log.put_line(textLine || ':' ||
158 to_char(dbms_utility.get_time - start_time,
159 '999,999,999.99') || ' ms.');
160 END IF;
161 END corelog_line;
162
163 /*========================================================================
164 | Private result set caching functions
165 ========================================================================*/
166
167 /*
168 ** get_profile_rowid
169 ** get the profile option's rowid that can be used to derive the profile
170 ** option's information. The rowid is cached using the PL/SQL
171 ** cross-session function result caching mechanism.
172 */
173 FUNCTION get_profile_rowid(profileName IN VARCHAR2)
174 RETURN ROWID RESULT_CACHE IS
175 profileRowId ROWID := NULL;
176 BEGIN
177 SELECT ROWID
178 INTO profileRowId
179 FROM fnd_profile_options
180 WHERE profile_option_name = profileName
181 AND start_date_active <= SYSDATE
182 AND nvl(end_date_active, SYSDATE) >= SYSDATE;
183 -- Log that the profile exists in fnd_profile_options
184 corelog_using_name(profileName, 'EXISTS in FND_PROFILE_OPTIONS');
185 RETURN profileRowId;
186 EXCEPTION
187 WHEN no_data_found THEN
188 -- Log that profile does not exist
189 corelog_using_name(profileName,
190 'DOES NOT EXIST in FND_PROFILE_OPTIONS');
191 RETURN NULL;
192 END get_profile_rowid;
193
194 /*
195 ** get_profile_name
196 ** get the profile option's name using the profile_option_id +
197 ** application_id. The return value is cached using the PL/SQL
198 ** cross-session function result caching mechanism.
199 */
200 FUNCTION get_profile_name
201 (
202 profileOptionId IN NUMBER,
203 applicationId IN NUMBER
204 ) RETURN VARCHAR2 RESULT_CACHE IS
205 profileName VARCHAR2(80) := NULL;
206 BEGIN
207 SELECT profile_option_name
208 INTO profileName
209 FROM fnd_profile_options
210 WHERE profile_option_id = profileOptionId
211 AND application_id = applicationId;
212 -- Log the profile_option_name given the profileOptionId + applicationId
213 corelog_using_name(profileName,
214 'PROFILE_OPTION_NAME found for ID:' ||
215 profileOptionId || ' + AID:' || applicationId);
216 RETURN profileName;
217 EXCEPTION
218 WHEN no_data_found THEN
219 -- Log no profile_option_id given the profileOptionRowId
220 corelog_using_name(profileName,
221 'PROFILE_OPTION_NAME is NULL for ID:' ||
222 profileOptionId || ' + AID:' || applicationId);
223 RETURN NULL;
224 END get_profile_name;
225
226 /*
227 ** get_profile_id
228 ** get the profile option's ID using the rowid. This is used to
229 ** obtain the profile option's value. The return value is cached using
230 ** the PL/SQL cross-session function result caching mechanism.
231 */
232 FUNCTION get_profile_id
233 (
234 profileOptionRowId IN ROWID,
235 profileName IN VARCHAR2
236 ) RETURN NUMBER RESULT_CACHE IS
237 profileId NUMBER := NULL;
238 BEGIN
239 SELECT profile_option_id
240 INTO profileId
241 FROM fnd_profile_options
242 WHERE ROWID = profileOptionRowId;
243 -- Log the profile_option_id given the profileOptionRowId
244 corelog_using_name(profileName, 'PROFILE_OPTION_ID = ' || profileId);
245 RETURN profileId;
246 EXCEPTION
247 WHEN no_data_found THEN
248 -- Log no profile_option_id given the profileOptionRowId
249 corelog_using_name(profileName, 'PROFILE_OPTION_ID is NULL');
250 RETURN NULL;
251 END get_profile_id;
252
253 /*
254 ** get_profile_appl_id
255 ** get the profile option's application ID using the rowid. This is
256 ** used to obtain the profile option's value. The return value is cached
257 ** using the PL/SQL cross-session function result caching mechanism.
258 */
259 FUNCTION get_profile_appl_id
260 (
261 profileOptionRowId IN ROWID,
262 profileName IN VARCHAR2
263 ) RETURN NUMBER RESULT_CACHE IS
264 profileApplId NUMBER := NULL;
265 BEGIN
266 SELECT application_id
267 INTO profileApplId
268 FROM fnd_profile_options
269 WHERE ROWID = profileOptionRowId;
270 -- Log the application_id given the profileOptionRowId
271 corelog_using_name(profileName, 'APPLICATION_ID = ' || profileApplId);
272 RETURN profileApplId;
273 EXCEPTION
274 WHEN no_data_found THEN
275 -- Log no application_id given the profileOptionRowId
276 corelog_using_name(profileName, 'APPLICATION_ID is NULL');
277 RETURN NULL;
278 END get_profile_appl_id;
279
280 /*
281 ** get_level_enabled_flag
282 ** get the profile option's level_enabled_flag using the rowid. This is
283 ** used to obtain the profile option's value. The return value is cached
284 ** using the PL/SQL cross-session function result caching mechanism.
285 */
286 FUNCTION get_level_enabled_flag
287 (
288 profileOptionRowId IN ROWID,
289 profileName IN VARCHAR2,
290 levelName IN VARCHAR2
291 ) RETURN VARCHAR2 RESULT_CACHE IS
292 profileLevelEnabled VARCHAR2(1) := NULL;
293 BEGIN
294 SELECT decode(levelName,
295 'USER',
296 user_enabled_flag,
297 'RESP',
298 resp_enabled_flag,
299 'APPL',
300 app_enabled_flag,
301 'SERVER',
302 server_enabled_flag,
303 'ORG',
304 org_enabled_flag,
305 'SERVRESP',
306 serverresp_enabled_flag,
307 'SITE',
308 site_enabled_flag)
309 INTO profileLevelEnabled
310 FROM fnd_profile_options
311 WHERE ROWID = profileOptionRowId;
312 -- Log the site_enabled_flag given the profileOptionRowId
313 corelog_using_name(profileName,
314 levelName||'_ENABLED_FLAG = ' || profileLevelEnabled);
315 RETURN profileLevelEnabled;
316 EXCEPTION
317 WHEN no_data_found THEN
318 -- Log no site_enabled_flag given the profileOptionRowId
319 corelog_using_name(profileName,
320 levelName || '_ENABLED_FLAG is NULL');
321 RETURN NULL;
322 END get_level_enabled_flag;
323
324 /*
325 ** get_hierarchy_type
326 ** get the profile option's hierarchy_type using the rowid. This
327 ** can be used to obtain the profile option's value. The return value is
328 ** cached using the PL/SQL cross-session function result caching
329 ** mechanism.
330 */
331 FUNCTION get_hierarchy_type
332 (
333 profileOptionRowId IN ROWID,
334 profileName IN VARCHAR2
335 ) RETURN VARCHAR2 RESULT_CACHE IS
336 profileHierarchyType VARCHAR2(8) := NULL;
337 BEGIN
338 SELECT hierarchy_type
339 INTO profileHierarchyType
340 FROM fnd_profile_options
341 WHERE ROWID = profileOptionRowId;
342 -- Log the hierarchy_type given the profileOptionRowId
343 corelog_using_name(profileName,
344 'HIERARCHY_TYPE = ' || profileHierarchyType);
345 RETURN profileHierarchyType;
346 EXCEPTION
347 WHEN no_data_found THEN
348 -- Log no hierarchy_type given the profileOptionRowId
349 corelog_using_name(profileName, 'HIERARCHY_TYPE is NULL');
350 RETURN NULL;
351 END get_hierarchy_type;
352
353 /*
354 ** get_user_changeable_flag
355 ** get the profile option's user_changeable_flag using the rowid. This
356 ** can be used to obtain the profile option's value. The return value is
357 ** cached using the PL/SQL cross-session function result caching
358 ** mechanism.
359 */
360 FUNCTION get_user_changeable_flag
361 (
362 profileOptionRowId IN ROWID,
363 profileName IN VARCHAR2
364 ) RETURN VARCHAR2 RESULT_CACHE IS
365 profileUserChangeable VARCHAR2(1) := NULL;
366 BEGIN
367 SELECT user_changeable_flag
368 INTO profileUserChangeable
369 FROM fnd_profile_options
370 WHERE ROWID = profileOptionRowId;
371 -- Log the user_changeable_flag given the profileOptionRowId
372 corelog_using_name(profileName,
373 'USER_CHANGEABLE_FLAG = ' || profileUserChangeable);
374 RETURN profileUserChangeable;
375 EXCEPTION
376 WHEN no_data_found THEN
377 -- Log no user_changeable_flag given the profileOptionRowId
378 corelog_using_name(profileName, 'USER_CHANGEABLE_FLAG is NULL');
379 RETURN NULL;
380 END get_user_changeable_flag;
381
382 /*
383 ** get_site_value
384 ** get the profile option's site-level value based on the profile
385 ** option's id. The results are cached using the PL/SQL cross-session
386 ** function result caching mechanism. This has a dependency on the view
387 ** fnd_profile_values_site_v
388 */
389 FUNCTION get_site_value
390 (
391 profileOptionId IN NUMBER,
392 applicationId IN NUMBER
393 ) RETURN VARCHAR2 RESULT_CACHE IS
394 siteLevelValue VARCHAR2(240);
395 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
396 applicationId);
397 BEGIN
398 SELECT profile_option_value
399 INTO siteLevelValue
400 FROM fnd_profile_values_site_v
401 WHERE profile_option_id = profileOptionId
402 AND application_id = applicationId
403 AND profile_option_value IS NOT NULL;
404 corelog_using_name(profileName, 'SITE LEVEL=' || siteLevelValue);
405 RETURN siteLevelValue;
406 EXCEPTION
407 WHEN no_data_found THEN
408 corelog_using_name(profileName, 'SITE LEVEL=NULL');
409 RETURN NULL;
410 END get_site_value;
411
412 /*
413 ** get_appl_value
414 ** get the profile option's application-level value based on the profile
415 ** option's id and level_value. The results are cached using the PL/SQL
416 ** cross-session function result caching mechanism. This has a
417 ** dependency on the view fnd_profile_values_appl_v
418 */
419 FUNCTION get_appl_value
420 (
421 profileOptionId IN NUMBER,
422 applicationId IN NUMBER,
423 applId IN NUMBER
424 ) RETURN VARCHAR2 RESULT_CACHE IS
425 applLevelValue VARCHAR2(240);
426 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
427 applicationId);
428 BEGIN
429 SELECT profile_option_value
430 INTO applLevelValue
431 FROM fnd_profile_values_appl_v
432 WHERE profile_option_id = profileOptionId
433 AND application_id = applicationId
434 AND level_value = applId
435 AND profile_option_value IS NOT NULL;
436 corelog_using_name(profileName,
437 'APPL LEVEL=' || applLevelValue ||
438 ' with application_id ' || applId);
439 RETURN applLevelValue;
440 EXCEPTION
441 WHEN no_data_found THEN
442 corelog_using_name(profileName,
443 'APPL LEVEL=NULL with application_id ' ||
444 applId);
445 RETURN NULL;
446 END get_appl_value;
447
448 /*
449 ** get_resp_value
450 ** get the profile option's responsibility-level value based on the
451 ** profile option's id, level_value, and level_value_application_id. The
452 ** results are cached using the PL/SQL cross-session function result
453 ** caching mechanism. This has a dependency on the view
454 ** fnd_profile_values_resp_v
455 */
456 FUNCTION get_resp_value
457 (
458 profileOptionId IN NUMBER,
459 applicationId IN NUMBER,
460 respId IN NUMBER,
461 applId IN NUMBER
462 ) RETURN VARCHAR2 RESULT_CACHE IS
463 respLevelValue VARCHAR2(240);
464 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
465 applicationId);
466 BEGIN
467 SELECT profile_option_value
468 INTO respLevelValue
469 FROM fnd_profile_values_resp_v
470 WHERE profile_option_id = profileOptionId
471 AND application_id = applicationId
472 AND level_value = respId
473 AND level_value_application_id = applId
474 AND profile_option_value IS NOT NULL;
475 corelog_using_name(profileName,
476 'RESP LEVEL=' || respLevelValue ||
477 ' with responsibility_id ' || respId ||
478 '+ application_id ' || applId);
479 RETURN respLevelValue;
480 EXCEPTION
481 WHEN no_data_found THEN
482 corelog_using_name(profileName,
483 'RESP LEVEL=NULL with responsibility_id ' ||
484 respId || '+ application_id ' || applId);
485 RETURN NULL;
486 END get_resp_value;
487
488 /*
489 ** get_user_value
490 ** get the profile option's user-level value based on the profile
491 ** option's id and level_value. The results are cached using the PL/SQL
492 ** cross-session function result caching mechanism. This has a
493 ** dependency on the view fnd_profile_values_user_v
494 **
495 ** This is called in get_specific_value().
496 */
497 FUNCTION get_user_value
498 (
499 profileOptionId IN NUMBER,
500 applicationId IN NUMBER,
501 userId IN NUMBER
502 ) RETURN VARCHAR2 RESULT_CACHE IS
503 userLevelValue VARCHAR2(240);
504 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
505 applicationId);
506 BEGIN
507 SELECT profile_option_value
508 INTO userLevelValue
509 FROM fnd_profile_values_user_v
510 WHERE profile_option_id = profileOptionId
511 AND application_id = applicationId
512 AND level_value = userId
513 AND profile_option_value IS NOT NULL;
514 corelog_using_name(profileName,
515 'USER_LEVEL=' || userLevelValue ||
516 ' with user_id ' || userId);
517 RETURN userLevelValue;
518 EXCEPTION
519 WHEN no_data_found THEN
520 corelog_using_name(profileName,
521 'USER_LEVEL=NULL with user_id ' || userId);
522 RETURN NULL;
523 END get_user_value;
524
525 /*
526 ** get_server_value
527 ** get the profile option's server-level value based on the profile
528 ** option's id and level_value. The results are cached using the PL/SQL
529 ** cross-session function result caching mechanism. This has a
530 ** dependency on the view fnd_profile_values_server_v
531 **
532 ** This is called in get_specific_value().
533 */
534 FUNCTION get_server_value
535 (
536 profileOptionId IN NUMBER,
537 applicationId IN NUMBER,
538 serverId IN NUMBER
539 ) RETURN VARCHAR2 RESULT_CACHE IS
540 serverLevelValue VARCHAR2(240);
541 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
542 applicationId);
543 BEGIN
544 SELECT profile_option_value
545 INTO serverLevelValue
546 FROM fnd_profile_values_server_v
547 WHERE profile_option_id = profileOptionId
548 AND application_id = applicationId
549 AND level_value = serverId
550 AND profile_option_value IS NOT NULL;
551 corelog_using_name(profileName,
552 'SERVER LEVEL=' || serverLevelValue ||
553 ' with server_id ' || serverId);
554 RETURN serverLevelValue;
555 EXCEPTION
556 WHEN no_data_found THEN
557 corelog_using_name(profileName,
558 'SERVER LEVEL=NULL with server_id ' || serverId);
559 RETURN NULL;
560 END get_server_value;
561
562 /*
563 ** get_org_value
564 ** get the profile option's org-level value based on the profile
565 ** option's id and level_value. The results are cached using the PL/SQL
566 ** cross-session function result caching mechanism. This has a
567 ** dependency on the view fnd_profile_values_org_v
568 **
569 ** This is called in get_specific_value().
570 */
571 FUNCTION get_org_value
572 (
573 profileOptionId IN NUMBER,
574 applicationId IN NUMBER,
575 orgId IN NUMBER DEFAULT fnd_global.org_id
576 ) RETURN VARCHAR2 RESULT_CACHE IS
577 orgLevelValue VARCHAR2(240);
578 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
579 applicationId);
580 BEGIN
581 SELECT profile_option_value
582 INTO orgLevelValue
583 FROM fnd_profile_values_org_v
584 WHERE profile_option_id = profileOptionId
585 AND application_id = applicationId
586 AND level_value = orgId
587 AND profile_option_value IS NOT NULL;
588 corelog_using_name(profileName,
589 'ORG LEVEL=' || orgLevelValue || ' with org_id ' ||
590 orgId);
591 RETURN orgLevelValue;
592 EXCEPTION
593 WHEN no_data_found THEN
594 corelog_using_name(profileName,
595 'ORG LEVEL=NULL with org_id ' || orgId);
596 RETURN NULL;
597 END get_org_value;
598
599 /*
600 ** get_servresp_level_value
601 ** get the profile option's servresp level value based on the profile
602 ** option's id, responsibility and server context. The results are cached
603 ** using the PL/SQL cross-session function result caching mechanism.
604 ** This has a dependency on the view fnd_profile_values_servresp_v
605 **
606 ** This is called in get_specific_value().
607 */
608 FUNCTION get_servresp_value
609 (
610 profileOptionId IN NUMBER,
611 applicationId IN NUMBER,
612 respId IN NUMBER,
613 applId IN NUMBER,
614 serverId IN NUMBER
615 ) RETURN VARCHAR2 RESULT_CACHE IS
616 servrespLevelValue VARCHAR2(240);
617 profileName VARCHAR2(80) := get_profile_name(profileOptionId,
618 applicationId);
619 BEGIN
620 SELECT profile_option_value
621 INTO servrespLevelValue
622 FROM fnd_profile_values_servresp_v
623 WHERE profile_option_id = profileOptionId
624 AND application_id = applicationId
625 AND level_value = respId
626 AND level_value_application_id = applId
627 AND level_value2 = serverId
628 AND profile_option_value IS NOT NULL;
629 corelog_using_name(profileName,
630 'SERVRESP LEVEL=' || servrespLevelValue ||
631 ' with resp_id ' || respId || ' + appl_id ' ||
632 applId || ' + server_id ' || serverId);
633 RETURN servrespLevelValue;
634 EXCEPTION
635 WHEN no_data_found THEN
636 corelog_using_name(profileName,
637 'SERVRESP LEVEL=NULL' || ' with resp_id ' ||
638 respId || ' + appl_id ' || applId ||
639 ' + server_id ' || serverId);
640 RETURN NULL;
641 END get_servresp_value;
642
643 /*========================================================================
644 | Private APIs used by Public PUT cache
645 ========================================================================*/
646
647 /*
648 ** find - find index of a profile option name in the given cache table
649 **
650 ** RETURNS
651 ** table index if found, TABLE_SIZE if not found.
652 */
653 FUNCTION find
654 (
655 profileName IN VARCHAR2,
656 profileNameTable IN name_tab_type,
657 profileHashValue IN BINARY_INTEGER
658 ) RETURN BINARY_INTEGER IS
659 tableIndex BINARY_INTEGER;
660 found BOOLEAN;
661 hashValue NUMBER;
662 BEGIN
663 -- Check if a hash value was provided. If not, generate one given the
664 -- profile option name and the table size
665 IF (profileHashValue IS NULL) THEN
666 tableIndex := dbms_utility.get_hash_value(profileName,
667 1,
668 table_size);
669 ELSE
670 tableIndex := profileHashValue;
671 END IF;
672
673 -- Check if the table index exists in the profile name table.
674 IF (profileNameTable.exists(tableIndex)) THEN
675 -- If it does, then check to see if the profile name stored in the
676 -- profile name table at that index value is the same as the profile
677 -- name passed in. If it is, then return the table index.
678 IF (profileNameTable(tableIndex) = profileName) THEN
679 RETURN tableIndex;
680 ELSE
681 -- search for the profile option
682
683 -- store the original hashvalue for the profile option
684 hashValue := tableIndex;
685 -- start search at next entry
686 tableIndex := tableIndex + 1;
687 found := FALSE;
688 WHILE (tableIndex < table_size)
689 AND (NOT found) LOOP
690 IF (profileNameTable.exists(tableIndex)) THEN
691 IF profileNameTable(tableIndex) = profileName THEN
692 found := TRUE;
693 ELSE
694 tableIndex := tableIndex + 1;
695 END IF;
696 ELSE
697 RETURN table_size + 1;
698 END IF;
699 END LOOP;
700
701 IF (NOT found) THEN
702 -- Didn't find any till the end
703 tableIndex := 1; -- Start from the beginning
704 WHILE (tableIndex < hashValue)
705 AND (NOT found) LOOP
706 IF (profileNameTable.exists(tableIndex)) THEN
707 IF profileNameTable(tableIndex) = profileName THEN
708 found := TRUE;
709 ELSE
710 tableIndex := tableIndex + 1;
711 END IF;
712 ELSE
713 RETURN table_size + 1;
714 END IF;
715 END LOOP;
716 END IF;
717
718 IF (NOT found) THEN
719 RETURN table_size + 1; -- Return a higher value
720 END IF;
721 END IF;
722 ELSE
723 RETURN table_size + 1;
724 END IF;
725
726 RETURN tableIndex;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 -- The entry doesn't exist
731 RETURN table_size + 1;
732 END find;
733
734 /*
735 ** find - find index of a profile option name in the Public PUT cache
736 ** table NAME_TAB.
737 **
738 ** RETURNS
739 ** table index, if found. TABLE_SIZE, if not found.
740 */
741 FUNCTION find(profileName IN VARCHAR2) RETURN BINARY_INTEGER IS
742 -- bug 14773322 - index variable to check for deleted value
743 tab_index BINARY_INTEGER;
744
745 BEGIN
746 -- bug 14773322 - find index variable to check for deleted value
747 tab_index := find(profileName,
748 name_tab,
749 dbms_utility.get_hash_value(profileName, 1, table_size));
750 -- bug 14773322 - check for valid index of profile name with deleted value
751 IF (tab_index < table_size AND
752 val_tab(tab_index) = fnd_delete_value) THEN
753 -- The entry doesn't exist
754 RETURN table_size + 1;
755 END IF;
756
757 RETURN tab_index;
758
759 EXCEPTION
760 WHEN OTHERS THEN
761 -- The entry doesn't exist
762 RETURN table_size + 1;
763 END find;
764
765 /*
766 ** put
767 ** Set or insert a profile option value into the Public Put cache. This
768 ** does not place the profile option value in the database and is only
769 ** available during the session.
770 */
771 PROCEDURE put
772 (
773 profileName IN VARCHAR2,
774 profileValue IN VARCHAR2,
775 profileNameTable IN OUT NOCOPY name_tab_type,
776 profileValueTable IN OUT NOCOPY val_tab_type,
777 profileHashValue IN BINARY_INTEGER
778 ) IS
779
780 tableIndex BINARY_INTEGER;
781 stored BOOLEAN;
782 hashValue BINARY_INTEGER;
783
784 -- bug 14773322 - remove dodelete procedure which deleted the name/value
785 -- from the PUT cache and searched for existing Collisions to move them back up.
786 -- Instead, to avoid the complexity of handling potential but rare collisions
787 -- store the fnd_delete_value flag as the value and ignore it on find
788
789 BEGIN
790 -- Log API entry
791 corelog(profileName, profileValue, 'Enter PUT');
792
793 IF (profileHashValue IS NULL) THEN
794 tableIndex := dbms_utility.get_hash_value(profileName,
795 1,
796 table_size);
797 ELSE
798 tableIndex := profileHashValue;
799 END IF;
800
801 stored := FALSE;
802
803 IF (profileNameTable.exists(tableIndex)) THEN
804 hashValue := tableIndex; -- Store the current spot
805 IF (profileNameTable(tableIndex) = profileName) THEN
806 -- Found the profile indexed by the hash value
807 -- bug 14773322 - remove call to dodelete procedure
808
809 profileValueTable(tableIndex) := profileValue; -- Store new value
810 stored := TRUE;
811 ELSE
812 -- Collision
813 -- start search at next entry
814 tableIndex := tableIndex + 1;
815 WHILE (tableIndex < table_size)
816 AND (NOT stored) LOOP
817 IF (profileNameTable.exists(tableIndex)) THEN
818 IF (profileNameTable(tableIndex) = profileName) THEN
819 -- Found the profile indexed higher than hash
820 -- bug 14773322 - remove call to dodelete procedure
821
822 profileValueTable(tableIndex) := profileValue; -- Store the new value
823 stored := TRUE;
824 ELSE
825 tableIndex := tableIndex + 1;
826 END IF;
827 ELSE
828 -- Log API collision - bug 14773322
829 corelog(profileName, profileValue,
830 'Collision FP.P stored at ' ||
831 tableIndex || ' above hash of ' || hashValue);
832 -- Store the value and profile for the first time
833 profileValueTable(tableIndex) := profileValue;
834 profileNameTable(tableIndex) := profileName;
835 stored := TRUE;
836 END IF;
837 END LOOP;
838
839 IF (NOT stored) THEN
840 -- Didn't find any free bucket till the end
841 tableIndex := 1;
842 WHILE (tableIndex < hashValue)
843 AND (NOT stored) LOOP
844 IF (profileNameTable.exists(tableIndex)) THEN
845 IF (profileNameTable(tableIndex) = profileName) THEN
846 -- Found the profile indexed lower than hash value
847 -- bug 14773322 - remove call to dodelete procedure
848
849 profileValueTable(tableIndex) := profileValue; -- Store the new value
850 stored := TRUE;
851 ELSE
852 tableIndex := tableIndex + 1;
853 END IF;
854 ELSE
855 -- Log API collision - bug 14773322
856 corelog(profileName, profileValue,
857 'Collision FP.P stored at '||
858 tableIndex || ' below hash of ' || hashValue);
859 -- Store the value and profile for the first time
860 profileValueTable(tableIndex) := profileValue;
861 profileNameTable(tableIndex) := profileName;
862 stored := TRUE;
863 END IF;
864 END LOOP;
865 END IF;
866 END IF;
867 ELSE
868 -- Store the value and profile for the first time
869 -- bug 14773322 - allow delete value flag to be stored
870 profileNameTable(tableIndex) := profileName; -- Enter the profile
871 profileValueTable(tableIndex) := profileValue; -- Store its value
872 stored := TRUE;
873 END IF;
874
875 -- bug 16327915 removed this section of code
876 -- the inserted PUT counter should only be tracking
877 -- the profiles inserted into generic PUT cache
878 --IF (stored)
879 -- AND (val <> FND_DELETE_VAL) THEN
880 -- inserted := inserted + 1; /* Increment the PUT counter */
881 --END IF;
882
883 -- Log API exit
884 corelog(profileName, profileValue, 'Exit PUT');
885
886 EXCEPTION
887 WHEN OTHERS THEN
888 -- bug14773322 - log exception to directly to corelog
889 -- even if profile filter is not enabled
890 corelog_line(profileName || ':' || profileValue ||':' ||
891 'PUT raised exception. SQLCODE:' || SQLCODE);
892 -- output exception to corelog
893 corelog_line(dbms_utility.format_error_stack);
894 -- output error line numbers to corelog
895 corelog_line(dbms_utility.format_error_backtrace);
896
897 END put;
898
899 /*
900 ** get_put_value
901 ** get the profile option's value from hash table in case a value was
902 ** placed there using PUT(). A profile value in the hash table overrides
903 ** the database value.
904 **
905 ** This is called in value() and get().
906 */
907 FUNCTION get_put_value(profileName IN VARCHAR2) RETURN VARCHAR2 IS
908 tableIndex BINARY_INTEGER;
909 profileValue VARCHAR2(240) := NULL;
910 profileNameUpper VARCHAR2(80) := upper(profileName);
911 BEGIN
912 -- Log API entry
913 corelog(profileName,
914 nvl(profileValue, 'NO VALUE'),
915 'Checking Public Put cache');
916 -- Check if the profile name exists in the NAME_TAB.
917 tableIndex := find(profileNameUpper);
918 -- If a valid table index was found, then use the index to obtain the
919 -- profile option value from the VAL_TAB table.
920 IF tableIndex < table_size THEN
921 profileValue := val_tab(tableIndex);
922 -- Log value found in Public Cache, API Exit
923 corelog(profileNameUpper,
924 nvl(profileValue, 'NO VALUE'),
925 'Value exists in Public Put cache');
926 ELSE
927 profileValue := NULL;
928 -- Log no value found in Public Put Cache, API Exit
929 corelog(profileNameUpper,
930 nvl(profileValue, 'NO VALUE'),
931 'NO VALUE exists in Public Put cache');
932 END IF;
933 RETURN profileValue;
934 EXCEPTION
935 WHEN OTHERS THEN
936 RETURN NULL;
937 END get_put_value;
938
939 /*
940 ** invalidate_cache
941 ** Call WF_EVENT.RAISE to invalidate the cache entry corresponding to
942 ** the specified profile. This is needed to notify processes monitoring
943 ** the 'oracle.apps.fnd.profile.value.update' event to invalidate their
944 ** cache and reload from the database.
945 */
946 PROCEDURE invalidate_cache
947 (
948 profileName IN VARCHAR2,
949 levelId IN NUMBER,
950 levelValue IN VARCHAR2,
951 levelValueAppId IN VARCHAR2,
952 levelValue2 IN VARCHAR2 DEFAULT NULL
953 ) IS
954
955 level_value NUMBER;
956 level_value_appl_id NUMBER;
957 profileNameUpper VARCHAR2(80) := upper(profileName);
958 event_key VARCHAR2(255);
959 level_value2 NUMBER;
960
961 BEGIN
962 IF (levelId = SITE_LEVEL_ID) THEN
963 level_value := 0;
964 level_value_appl_id := 0;
965 ELSIF (levelId = APPL_LEVEL_ID) THEN
966 level_value := to_number(levelValue);
967 level_value_appl_id := 0;
968 ELSIF (levelId = RESP_LEVEL_ID) THEN
969 level_value := to_number(levelValue);
970 level_value_appl_id := to_number(levelValueAppId);
971 ELSIF (levelId = USER_LEVEL_ID) THEN
972 level_value := to_number(levelValue);
973 level_value_appl_id := 0;
974 ELSIF (levelId = SERVER_LEVEL_ID) THEN
975 level_value := to_number(levelValue);
976 level_value_appl_id := 0;
977 ELSIF (levelId = ORG_LEVEL_ID) THEN
978 level_value := to_number(levelValue);
979 level_value_appl_id := 0;
980 ELSIF (levelId = SERVRESP_LEVEL_ID) THEN
981 level_value := to_number(levelValue);
982 level_value_appl_id := to_number(levelValueAppId);
983 level_value2 := to_number(levelValue2);
984 ELSE
985 RETURN;
986 END IF;
987
988 IF (levelId = SERVRESP_LEVEL_ID) THEN
989 -- Event Key has level_value2
990 event_key := levelId || ':' || level_value || ':' ||
991 level_value_appl_id || ':' || level_value2 || ':' ||
992 profileName;
993 ELSE
994 -- Original event_key format
995 event_key := levelId || ':' || level_value || ':' ||
996 level_value_appl_id || ':' || profileName;
997 END IF;
998
999 -- raise event 'oracle.apps.fnd.profile.value.update' to notify
1000 -- processes monitoring the event to invalidate their cache.
1001 fnd_wf_engine.default_event_raise(p_event_name => 'oracle.apps.fnd.profile.value.update',
1002 p_event_key => event_key);
1003
1004 END invalidate_cache;
1005
1006 /*========================================================================
1007 | Private APIs used to fetch profile option values
1008 ========================================================================*/
1009
1010 /*
1011 ** get_specific_value
1012 ** get the profile option value based on the profile option information
1013 ** and specific context passed into the API.
1014 ** This calls all the functions that have been enabled to use the PL/SQL
1015 ** cross-session function result caching mechanism.
1016 **
1017 ** The algorithm is such:
1018 ** 1. Using the profile option information acquired at declaration,
1019 ** determine if the profile exists in FND_PROFILE_OPTIONS. If it does
1020 ** not, return NULL.
1021 ** 2. If it exists and the user_enabled_flag is Y, get user level value.
1022 ** 3. If there was no user-level value, proceed to check the applicable
1023 ** levels based on the profile option hierarchy.
1024 ** 4. If no value was obtained in all the other levels, check the site
1025 ** level if it is enabled for the profile option.
1026 **
1027 ** Context arguments (userId, responsibilityId, applicationId,
1028 ** orgId, serverId) specify what context to use to determine the
1029 ** profile option value. Context arguments are interpreted as follows:
1030 **
1031 ** NULL - use current session context value (default)
1032 ** -1 - override current context with "undefined" value
1033 ** <value> - override current context with specified value
1034 **
1035 ** Special Notes:
1036 ** - Context override values are only used for determining the profile
1037 ** option value in this function call, the user session context is not
1038 ** changed.
1039 **
1040 ** - An undefined context value (-1) causes that context level to be
1041 ** skipped during processing, meaning that any profile option values
1042 ** set at that context level are ignored.
1043 **
1044 ** - Regardless of which context levels are specified, the profile option
1045 ** HIERARCHY_TYPE and '%_ENABLED_FLAG' flags determine which context
1046 ** levels are searched to find the value.
1047 */
1048 FUNCTION get_specific_value
1049 (
1050 profileName IN VARCHAR2,
1051 userId IN NUMBER DEFAULT NULL,
1052 responsibilityId IN NUMBER DEFAULT NULL,
1053 applicationId IN NUMBER DEFAULT NULL,
1054 orgId IN NUMBER DEFAULT NULL,
1055 serverId IN NUMBER DEFAULT NULL
1056 ) RETURN VARCHAR2 RESULT_CACHE IS
1057 profileOptionValue VARCHAR2(240) := NULL;
1058 profileRowId ROWID := get_profile_rowid(profileName);
1059 profileOptionId NUMBER := NULL;
1060 profApplicationId NUMBER := NULL;
1061 siteEnabled VARCHAR2(1) := NULL;
1062 applEnabled VARCHAR2(1) := NULL;
1063 respEnabled VARCHAR2(1) := NULL;
1064 userEnabled VARCHAR2(1) := NULL;
1065 orgEnabled VARCHAR2(1) := NULL;
1066 serverEnabled VARCHAR2(1) := NULL;
1067 servrespEnabled VARCHAR2(1) := NULL;
1068 hierarchyType VARCHAR2(8) := NULL;
1069 userChangeable VARCHAR2(1) := NULL;
1070 -- For any context passed in as NULL, substitute with the current context
1071 l_userId NUMBER := nvl(userId, profiles_user_id);
1072 l_responsibilityId NUMBER := nvl(responsibilityId, profiles_resp_id);
1073 l_applicationId NUMBER := nvl(applicationId, profiles_appl_id);
1074 l_orgId NUMBER := nvl(orgId, profiles_org_id);
1075 l_serverId NUMBER := nvl(serverId, profiles_server_id);
1076 BEGIN
1077 -- In value() and get(), if profileRowId is NULL, ***FND_UNDEFINED_VALUE***
1078 -- is placed in Public Put Cache. Since this API does not check Public Put
1079 -- Cache when evaluating a profile option, it would be best not to perform
1080 -- any action related to Public Put Cache. Hence, if profileRowId is NULL,
1081 -- then this API will return NULL for a profile that does not exist in
1082 -- FND_PROFILE_OPTIONS. This is different from value() and get() since
1083 -- profiles that do not exist in FND_PROFILE_OPTIONS are categorized as
1084 -- TRANSIENT and may have values in Public Put Cache.
1085 IF profileRowId IS NULL THEN
1086 -- Log that profile option was not found
1087 corelog_using_name(profileName,
1088 profileName ||
1089 'does not exist in FND_PROFILE_OPTIONS, returning NULL');
1090 RETURN NULL;
1091 ELSE
1092 -- If profile exists, then retrieve the definition
1093 profileOptionId := get_profile_id(profileRowId, profileName);
1094 profApplicationId := get_profile_appl_id(profileRowId, profileName);
1095 siteEnabled:= get_level_enabled_flag(profileRowId,profileName,'SITE');
1096 applEnabled := get_level_enabled_flag(profileRowId,profileName,'APPL');
1097 respEnabled := get_level_enabled_flag(profileRowId,profileName,'RESP');
1098 userEnabled := get_level_enabled_flag(profileRowId,profileName,'USER');
1099 orgEnabled := get_level_enabled_flag(profileRowId,profileName,'ORG');
1100 serverEnabled := get_level_enabled_flag(profileRowId,profileName,'SERVER');
1101 servrespEnabled := get_level_enabled_flag(profileRowId,profileName,'SERVRESP');
1102 hierarchyType := get_hierarchy_type(profileRowId,profileName);
1103 userChangeable := get_user_changeable_flag(profileRowId,profileName);
1104
1105 -- If profile option is enabled at the user level and userId context is
1106 -- not DEFAULT_CONTEXT or NULL, then check to see if there is a user
1107 -- level value. User level is a common level for all hierarchies.
1108 IF (userEnabled = 'Y')
1109 AND ((userId <> DEFAULT_CONTEXT) AND (userId IS NOT NULL)) THEN
1110 /* USER level */
1111 profileOptionValue := get_user_value(profileOptionId,
1112 profApplicationId,
1113 l_userId);
1114 END IF;
1115
1116 -- If there is a user-level value, log that it was found.
1117 IF profileOptionValue IS NOT NULL THEN
1118 corelog(profileName,
1119 profileOptionValue,
1120 'USER LEVEL value for user_id:' || l_userId);
1121 -- If there is no user-level value, then check the intermediate
1122 -- levels based on the profile option's hierarchy.
1123 ELSE
1124 -- If hierarchy_type of profile option is SECURITY, then check to
1125 -- see if there is a RESPONSIBILITY or APPLICATION level value.
1126 IF hierarchyType = 'SECURITY' THEN
1127 -- If profile option is enabled at the resp level and the
1128 -- responsibilityId and applicationId is not DEFAULT_CONTEXT or
1129 -- NULL, then check to see if there is a responsibility level
1130 -- value.
1131 IF (respEnabled = 'Y')
1132 AND ((l_responsibilityId <> DEFAULT_CONTEXT) AND
1133 (l_responsibilityId IS NOT NULL))
1134 AND ((l_applicationId <> DEFAULT_CONTEXT) AND
1135 (l_applicationId IS NOT NULL)) THEN
1136 /* RESP level */
1137 profileOptionValue := get_resp_value(profileOptionId,
1138 profApplicationId,
1139 l_responsibilityId,
1140 l_applicationId);
1141 END IF;
1142
1143 IF profileOptionValue IS NOT NULL THEN
1144 -- If there is an responsibility level value, log it was found
1145 corelog(profileName,
1146 profileOptionValue,
1147 'RESP LEVEL value for context=' ||
1148 l_responsibilityId || '+' || l_applicationId);
1149 -- If there is no resp-level value and the profile option is
1150 -- enabled at the application level and the applicationId is not
1151 -- DEFAULT_CONTEXT or NULL, then check to see if there is an
1152 -- application level value.
1153 ELSE
1154 IF (applEnabled = 'Y')
1155 AND ((l_applicationId <> DEFAULT_CONTEXT) AND
1156 (l_applicationId IS NOT NULL)) THEN
1157 /* APPL level */
1158 profileOptionValue := get_appl_value(profileOptionId,
1159 profApplicationId,
1160 l_applicationId);
1161 END IF;
1162 -- If there is an application level value, log it was found
1163 IF profileOptionValue IS NOT NULL THEN
1164 corelog(profileName,
1165 profileOptionValue,
1166 'APPL LEVEL value for appl_id=' ||
1167 l_applicationId);
1168 END IF;
1169 END IF; -- profileOptionValue IS NOT NULL
1170 END IF; -- hierarchyType = 'SECURITY'
1171
1172 -- If hierarchy_type of profile option is SERVER, then check to see
1173 -- if there is a SERVER level value.
1174 IF hierarchyType = 'SERVER' THEN
1175 -- If profile option is enabled at the server level and the
1176 -- serverId is not DEFAULT_CONTEXT or NULL, then check to see if
1177 -- there is a server level value.
1178 IF (serverEnabled = 'Y')
1179 AND ((l_serverId <> DEFAULT_CONTEXT) AND
1180 (l_serverId IS NOT NULL)) THEN
1181 /* SERVER level */
1182 profileOptionValue := get_server_value(profileOptionId,
1183 profApplicationId,
1184 l_serverId);
1185 END IF;
1186 IF profileOptionValue IS NOT NULL THEN
1187 corelog(profileName,
1188 profileOptionValue,
1189 'SERVER LEVEL value for server_id=' || l_serverId);
1190 END IF;
1191 END IF; -- hierarchyType = 'SERVER'
1192
1193 -- If hierarchy_type of profile option is ORG, then check to see if
1194 -- there is an ORG level value.
1195 IF hierarchyType = 'ORG' THEN
1196 -- If profile option is enabled at the org level and the
1197 -- orgId is not DEFAULT_CONTEXT or NULL, then check to see if
1198 -- there is a org level value.
1199 IF (orgEnabled = 'Y')
1200 AND ((orgId <> DEFAULT_CONTEXT) AND (orgId IS NOT NULL)) THEN
1201 /* ORG level */
1202 profileOptionValue := get_org_value(profileOptionId,
1203 profApplicationId,
1204 l_orgId);
1205 END IF;
1206 IF profileOptionValue IS NOT NULL THEN
1207 corelog(profileName,
1208 profileOptionValue,
1209 'ORG LEVEL value for org_id=' || l_orgId);
1210 END IF;
1211 END IF; -- hierarchyType = 'ORG'
1212
1213 -- If hierarchy_type of profile option is SERVRESP, then check to
1214 -- see if there is a SERVRESP level value.
1215 IF hierarchyType = 'SERVRESP' THEN
1216 -- If profile option is enabled at the servresp level
1217 IF (servrespEnabled = 'Y') THEN
1218 -- If responsibilityId and applicationId, or serverId is not
1219 -- DEFAULT_CONTEXT or NULL, then check to see if there is a
1220 -- servresp level value.
1221 IF ((((l_responsibilityId <> DEFAULT_CONTEXT) AND
1222 (l_responsibilityId IS NOT NULL)) AND
1223 ((l_applicationId <> DEFAULT_CONTEXT) AND
1224 (l_applicationId IS NOT NULL))) OR
1225 ((l_serverId <> DEFAULT_CONTEXT) AND
1226 (l_serverId IS NOT NULL))) THEN
1227 /* SERVRESP level, all context values */
1228 profileOptionValue := get_servresp_value(profileOptionId,
1229 profApplicationId,
1230 l_responsibilityId,
1231 l_applicationId,
1232 l_serverId);
1233 -- If profileOptionValue is NULL then check to see if there
1234 -- is a servresp level value using the resp and appl
1235 -- context values
1236 IF profileOptionValue IS NOT NULL THEN
1237 -- If there is a servresp-level value, log it was found
1238 corelog(profileName,
1239 profileOptionValue,
1240 'SERVRESP LEVEL value for context=' ||
1241 l_responsibilityId || '+' ||
1242 l_applicationId || '+' || l_serverId);
1243 ELSE
1244 /* SERVRESP level, resp and appl context values */
1245 profileOptionValue := get_servresp_value(profileOptionId,
1246 profApplicationId,
1247 l_responsibilityId,
1248 l_applicationId,
1249 DEFAULT_CONTEXT);
1250 -- If profileOptionValue is NULL then check to see if
1251 -- there is a servresp level value using the server
1252 -- context value
1253 IF profileOptionValue IS NOT NULL THEN
1254 -- If there is a servresp-level value, log it was
1255 -- found
1256 corelog(profileName,
1257 profileOptionValue,
1258 'SERVRESP LEVEL value for context=' ||
1259 l_responsibilityId || '+' ||
1260 l_applicationId || '+' ||
1261 DEFAULT_CONTEXT);
1262 ELSE
1263 /* SERVRESP level, server context value */
1264 profileOptionValue := get_servresp_value(profileOptionId,
1265 profApplicationId,
1266 DEFAULT_CONTEXT,
1267 DEFAULT_CONTEXT,
1268 l_serverId);
1269 IF profileOptionValue IS NOT NULL THEN
1270 -- If there is a servresp-level value, log it was
1271 -- found
1272 corelog(profileName,
1273 profileOptionValue,
1274 'SERVRESP LEVEL value for context=' ||
1275 DEFAULT_CONTEXT || '+' ||
1276 DEFAULT_CONTEXT || '+' || l_serverId);
1277 END IF;
1278 END IF; -- profileOptionValue IS NOT NULL
1279 END IF; -- profileOptionValue IS NOT NULL
1280 END IF; -- responsibilityId, applicationId, serverId
1281 END IF; -- servrespEnabled = 'Y'
1282 END IF; -- hierarchyType = 'SERVRESP'
1283 END IF; -- profileOptionValue IS NOT NULL
1284
1285 -- If there is still no profile option value, then check the site
1286 -- level. The site level value specifies the default value for a
1287 -- profile option. If there is a value at the site-level, it will
1288 -- be returned.
1289 IF profileOptionValue IS NULL THEN
1290 -- If profile option is enabled at the site level, then check to
1291 -- see if there is a site level value. Site level is a common
1292 -- level for all hierarchies.
1293 IF siteEnabled = 'Y' THEN
1294 /* SITE level */
1295 profileOptionValue := get_site_value(profileOptionId,
1296 profApplicationId);
1297 IF profileOptionValue IS NOT NULL THEN
1298 -- If there is a site-level value, log it was found
1299 corelog(profileName,
1300 profileOptionValue,
1301 'SITE LEVEL value');
1302 END IF; -- profileOptionValue IS NOT NULL for SITE
1303 END IF; -- siteEnabled = 'Y'
1304 END IF; -- profileOptionValue IS NULL
1305 END IF; -- profileRowId is NULL
1306
1307 RETURN profileOptionValue;
1308
1309 END get_specific_value;
1310
1311 /*========================================================================
1312 | Private AOL INTERNAL USE ONLY APIs
1313 ========================================================================*/
1314 /*
1315 ** FOR AOL INTERNAL USE ONLY - DO NOT CALL DIRECTLY,
1316 ** CALL VIA FND_GLOBAL.INITIALIZE('ORG_ID',org_id)
1317 ** FND_PROFILE.INITIALIZE also calls this API to initialize the org context.
1318 **
1319 ** initialize_org_context - Initializes the org context
1320 */
1321 PROCEDURE initialize_org_context IS
1322 BEGIN
1323 -- Set Global organization context
1324 profiles_org_id := fnd_global.org_id;
1325 -- Cache org-specific transient profile options in Public Put cache
1326 put('CURRENT_ORG_CONTEXT', to_char(fnd_global.org_id));
1327 put('ORG_NAME', fnd_global.org_name);
1328 -- Bug 16327915, ORG_ID is stored in Public PUT cache by Forms user exit.
1329 -- purge previous context cached value of ORG_ID
1330 -- so that the value for current context can be found.
1331 put('ORG_ID', FND_DELETE_VALUE);
1332
1333 -- Note: For R12, the profile option ORG_ID is not always an
1334 -- equivalent of FND_GLOBAL.ORG_ID, which is the org context. The
1335 -- global variable PROFILES_ORG_ID is the org context used for
1336 -- evaluating org-level profile option values and should be equal to
1337 -- FND_GLOBAL.ORG_ID. A value fetch on the profile option ORG_ID
1338 -- should return the profile option table value, not the org context.
1339 -- This behavior was confirmed with JMARY and SHNARAYA of the MO Team.
1340 -- CURRENT_ORG_CONTEXT is being introduced so that profiles code can
1341 -- provide similar functionality such that FND_GLOBAL.ORG_ID will be
1342 -- equivalent to FND_PROFILE.VALUE('CURRENT_ORG_CONTEXT').
1343 -- FND_PROFILE.VALUE('ORG_ID') will return a value obtained in the
1344 -- FND_PROFILE_OPTION_VALUES table.
1345
1346 END initialize_org_context;
1347
1348 /*
1349 ** FOR AOL INTERNAL USE ONLY - DO NOT CALL DIRECTLY, CALL VIA
1350 ** FND_GLOBAL.APPS_INITIALIZE
1351 ** initialize - Initialize the internal profile information
1352 ** The cache is cleared of all database (non-put) options first.
1353 ** Initializes the profiles for the level context information.
1354 */
1355 PROCEDURE initialize
1356 (
1357 user_id_z IN NUMBER DEFAULT NULL,
1358 responsibility_id_z IN NUMBER DEFAULT NULL,
1359 application_id_z IN NUMBER DEFAULT NULL,
1360 site_id_z IN NUMBER DEFAULT NULL
1361 ) IS
1362
1363 tempName VARCHAR2(256);
1364 NewICXSessionId NUMBER;
1365 BEGIN
1366 -- Get session ID
1367 NewICXSessionId := icx_sec.g_session_id;
1368
1369 -- Bug 12875860, We initialize the put cache clear flag here since it
1370 -- should only be tested by FND_GLOBAL after a call to INITIALIZE
1371 put_cache_is_clear := FALSE;
1372
1373 -- Log the session IDs for reference.
1374 corelog_line('Session IDs: CISId=' || current_icx_session_id ||
1375 ';NISId=' || NewICXSessionId || ';DBSId=' ||
1376 userenv('sessionid'));
1377
1378 -- Clear Public Put cache when ICX session_id changes.
1379 -- NOTE: This needs to stay even when other caches are not cleared on
1380 -- session change. Put cache is always only good for the current ICX
1381 -- session.
1382 --
1383 -- The only real condition for a Public PUT Cache purge is that the
1384 -- current ICX session ID is different from the new ICX session ID
1385 -- returned by ICX_SEC.G_SESSION_ID, and that the current ICX session ID
1386 -- or the new ICX session ID is not DEFAULT_CONTEXT, i.e. -1.
1387 --
1388 IF ((current_icx_session_id IS NULL) OR (NewICXSessionId IS NULL) OR
1389 ((current_icx_session_id <> NewICXSessionId) AND
1390 (current_icx_session_id <> DEFAULT_CONTEXT) AND
1391 (NewICXSessionId <> DEFAULT_CONTEXT))) THEN
1392 name_tab.delete();
1393 val_tab.delete();
1394 -- mskees 9/1/2011 this is a little used flag for FND_GLOBAL
1395 inserted := 0; -- reset PUT count
1396 -- Bug 12875860 set flag for FND_GLOBAL
1397 put_cache_is_clear := TRUE;
1398 -- userenv('sessionid') is the database session id. It is used by
1399 -- FND_GLOBAL for core logging, and is used here to facilitate better
1400 -- readability of afcorelog_*.txt.
1401 corelog_line('Public Put cache purged:CSId=' ||
1402 current_icx_session_id || ':SId=' || NewICXSessionId || ':' ||
1403 userenv('sessionid'));
1404 END IF;
1405
1406 -- Set Global context values
1407 current_icx_session_id := NewICXSessionId;
1408 profiles_user_id := user_id_z;
1409 profiles_resp_id := responsibility_id_z;
1410 profiles_appl_id := application_id_z;
1411 profiles_server_id := fnd_global.server_id;
1412
1413 -- Cache the user context passed in
1414 IF (user_id_z IS NOT NULL) THEN
1415 put('USER_ID', to_char(user_id_z));
1416 IF (user_id_z = fnd_global.user_id) THEN
1417 -- Use global to avoid select if current user
1418 tempName := fnd_global.user_name;
1419 ELSIF (user_id_z = DEFAULT_CONTEXT) THEN
1420 tempName := 'DEFAULT_USER';
1421 ELSE
1422 BEGIN
1423 SELECT user_name
1424 INTO tempName
1425 FROM fnd_user
1426 WHERE user_id = user_id_z;
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 tempName := '';
1430 END;
1431 END IF;
1432 put('userName', tempName);
1433 END IF;
1434
1435 -- Log API entry
1436 corelog('PROFILE_INIT',
1437 tempName,
1438 'INITIALIZE',
1439 user_id_z,
1440 responsibility_id_z,
1441 application_id_z,
1442 fnd_global.org_id,
1443 fnd_global.server_id);
1444
1445 -- Cache the responsibility context passed in
1446 IF ((responsibility_id_z IS NOT NULL) AND
1447 (application_id_z IS NOT NULL)) THEN
1448 put('RESP_ID', to_char(responsibility_id_z));
1449 put('RESP_APPL_ID', to_char(application_id_z));
1450 IF ((responsibility_id_z = fnd_global.resp_id) AND
1451 (application_id_z = fnd_global.resp_appl_id)) THEN
1452 -- Use global to avoid select if current resp
1453 tempName := fnd_global.resp_name;
1454 ELSIF ((responsibility_id_z = DEFAULT_CONTEXT) AND
1455 (application_id_z = DEFAULT_CONTEXT)) THEN
1456 tempName := 'DEFAULT_RESP';
1457 ELSE
1458 BEGIN
1459 SELECT responsibility_name
1460 INTO tempName
1461 FROM fnd_responsibility_vl
1462 WHERE responsibility_id = responsibility_id_z
1463 AND application_id = application_id_z;
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 tempName := '';
1467 END;
1468 END IF;
1469 put('RESP_NAME', tempName);
1470 END IF;
1471
1472 -- Place the server context in Public Put cache
1473 put('SERVER_ID', to_char(fnd_global.server_id));
1474
1475 BEGIN
1476 SELECT node_name
1477 INTO tempName
1478 FROM fnd_nodes
1479 WHERE node_id = fnd_global.server_id;
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 tempName := '';
1483 END;
1484
1485 put('SERVER_NAME', tempName);
1486
1487 -- Finally, initialize the org context
1488 initialize_org_context;
1489
1490 END initialize;
1491
1492 /*=========================================================================
1493 | Public APIs
1494 ========================================================================*/
1495
1496 /*
1497 ** put
1498 ** Set or insert a profile option value into the Public Put cache. This
1499 ** does not place the profile option value in the database and is only
1500 ** available during the session.
1501 */
1502 PROCEDURE put
1503 (
1504 name IN VARCHAR2,
1505 val IN VARCHAR2
1506 ) IS
1507 table_index BINARY_INTEGER; -- bug 16327915 manage PUT count
1508 profileNameUpper VARCHAR2(80) := upper(name);
1509 BEGIN
1510 -- Log API Entry
1511 corelog(profileNameUpper, val, 'Enter Public PUT');
1512
1513 -- bug 16327915 manage PUT count of Public PUT cache
1514 -- the inserted PUT counter should be tracking
1515 -- the actual number of true profile values in PUT cache
1516 -- not every call to insert any profile value
1517 -- Search for existing profile option in Public PUT cache
1518 table_index := find(profileNameUpper);
1519 -- if the profile exists in the Public PUT cache
1520 IF (table_index < table_size) THEN
1521 -- if profile value is NOT NULL or being marked for delete
1522 IF ((val <> FND_DELETE_VALUE) AND
1523 (val IS NOT NULL)) THEN
1524 -- if existing profile value is marked deleted
1525 IF (val_tab(table_index) = FND_DELETE_VALUE) THEN
1526 -- we will be reviving the current deleted value
1527 -- increment the PUT counter
1528 inserted := inserted + 1;
1529 END IF;
1530 ELSE -- we are marking this profile for delete
1531 -- if existing profile value is NOT marked deleted
1532 IF (val_tab(table_index) <> FND_DELETE_VALUE) THEN
1533 -- we will be removing the current value
1534 -- decrement PUT counter
1535 IF (inserted > 0) THEN
1536 inserted := inserted - 1;
1537 END IF;
1538 END IF;
1539 END IF;
1540 ELSE -- profile value NOT existing in Public PUT cache
1541 -- if profile value is NOT NULL or being marked for delete
1542 IF ((val <> FND_DELETE_VALUE) AND
1543 (val IS NOT NULL)) THEN
1544 -- we are inserting a new value
1545 inserted := inserted + 1;
1546 END IF;
1547 END IF;
1548
1549 -- Call private PUT
1550 put(profileNameUpper,
1551 val,
1552 name_tab,
1553 val_tab,
1554 dbms_utility.get_hash_value(profileNameUpper, 1, table_size));
1555 -- Log API Exit
1556 corelog(profileNameUpper, val, 'Exit Public PUT Count=' || inserted);
1557 END put;
1558
1559 /*
1560 ** get
1561 ** returns the profile option value using the prevailing security
1562 ** context. This is the procedure API equivalent to value().
1563 **
1564 ** The algorithm is such:
1565 ** 1. Check Public Put Cache to see if a value has already been placed for
1566 ** the profile.
1567 ** 2. If no value was found in Public Put Cache, check to see if the
1568 ** profile option exists in FND_PROFILE_OPTIONS using
1569 ** get_profile_rowid().
1570 ** 3. If the profile option does not exist, then return NULL.
1571 ** If the profile option exists, call get_specific_value() to get a
1572 ** profile option value.
1573 */
1574 PROCEDURE get
1575 (
1576 name IN VARCHAR2,
1577 val OUT NOCOPY VARCHAR2
1578 ) IS
1579 profileOptionValue VARCHAR2(240) := NULL;
1580 profileNameUpper VARCHAR2(80) := upper(name);
1581 BEGIN
1582 -- Log entry
1583 corelog(profileNameUpper, nvl(val, 'NO VALUE'), 'Enter GET');
1584 -- Bug 12395376:Check Public Put Cache to see if a value was placed for the
1585 -- profile option using PUT().
1586 profileOptionValue := get_put_value(profileNameUpper);
1587 -- If no value was found in Public Put cache then
1588 IF profileOptionValue IS NOT NULL THEN
1589 -- If the value found in cache is FND_UNDEFINED_VALUE, then reset it
1590 -- back to NULL.
1591 IF (profileOptionValue = fnd_undefined_value) THEN
1592 profileOptionValue := NULL;
1593 END IF;
1594 ELSE
1595 -- If profile option exists, then
1596 IF get_profile_rowid(profileNameUpper) IS NOT NULL THEN
1597 -- call get_specific_value and explicitly pass in the current context,
1598 -- to find the profile option value. Current context needs to be
1599 -- explicitly passed in, so that the result-caching function
1600 -- get_specific_value() caches a value based on that exact function
1601 -- call signature.
1602 profileOptionValue := get_specific_value(profileNameUpper,
1603 profiles_user_id,
1604 profiles_resp_id,
1605 profiles_appl_id,
1606 profiles_org_id,
1607 profiles_server_id);
1608 -- Log that profile option value was found using get_specific_value()
1609 IF profileOptionValue IS NOT NULL THEN
1610 corelog_using_name(profileNameUpper,
1611 ' get gsv() returned ' ||
1612 profileOptionValue || ':' ||
1613 userenv('sessionid'));
1614 END IF;
1615 ELSE
1616 -- Log that profile option was not found
1617 corelog_using_name(profileNameUpper,
1618 'TRANSIENT, put ' || fnd_undefined_value ||
1619 ' in Public Put cache');
1620 -- Cache FND_UNDEFINED_VALUE in Public Put cache
1621 put(profileNameUpper, fnd_undefined_value);
1622 END IF;
1623 END IF; -- profileOptionValue IS NOT NULL in Public Put cache
1624 -- Set value for return argmuent
1625 val := profileOptionValue;
1626 -- Log exit
1627 corelog(profileNameUpper, nvl(val, 'NO VALUE'), 'Exit GET');
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 NULL;
1631 END get;
1632
1633 /*
1634 ** get_specific
1635 ** returns a profile option value for a specific context or a combination
1636 ** of contexts. The context will default to current context, if no context
1637 ** was provided.
1638 **
1639 ** Context arguments (USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z,
1640 ** ORG_ID_Z, SERVER_ID_Z) specify what context to use to determine the
1641 ** profile option value. Context arguments are interpreted as follows:
1642 **
1643 ** NULL - use current session context value (default)
1644 ** -1 - override current context with "undefined" value
1645 ** <value> - override current context with specified value
1646 **
1647 ** Special Notes:
1648 ** - Context override values are only used for determining the profile
1649 ** option value in this function call, the user session context is not
1650 ** changed.
1651 **
1652 ** - An undefined context value (-1) causes that context level to be
1653 ** skipped during processing, meaning that any profile option values
1654 ** set at that context level are ignored.
1655 **
1656 ** - Regardless of which context levels are specified, the profile option
1657 ** HIERARCHY_TYPE and '%_ENABLED_FLAG' flags determine which context
1658 ** levels are searched to find the value.
1659 */
1660 PROCEDURE get_specific
1661 (
1662 name_z IN VARCHAR2,
1663 user_id_z IN NUMBER DEFAULT NULL,
1664 responsibility_id_z IN NUMBER DEFAULT NULL,
1665 application_id_z IN NUMBER DEFAULT NULL,
1666 val_z OUT NOCOPY VARCHAR2,
1667 defined_z OUT NOCOPY BOOLEAN,
1668 org_id_z IN NUMBER DEFAULT NULL,
1669 server_id_z IN NUMBER DEFAULT NULL
1670 ) IS
1671
1672 profNameUpper VARCHAR2(80) := upper(name_z);
1673 BEGIN
1674 -- Log API entry
1675 corelog(profNameUpper,
1676 nvl(val_z, 'NO VALUE'),
1677 'Enter GET_SPECIFIC',
1678 user_id_z,
1679 responsibility_id_z,
1680 application_id_z,
1681 org_id_z,
1682 server_id_z);
1683 -- If override context passed in matches the existing context, call GET
1684 IF ((user_id_z = profiles_user_id) OR (user_id_z IS NULL))
1685 AND ((responsibility_id_z = profiles_resp_id) OR
1686 (responsibility_id_z IS NULL))
1687 AND ((application_id_z = profiles_appl_id) OR
1688 (application_id_z IS NULL))
1689 AND ((org_id_z = profiles_org_id) OR (org_id_z IS NULL))
1690 AND ((server_id_z = profiles_server_id) OR (server_id_z IS NULL)) THEN
1691 -- Log redirect to GET
1692 corelog(profNameUpper,
1693 nvl(val_z, 'NO VALUE'),
1694 'CURRENT context, calling GET');
1695 -- call get()
1696 get(profNameUpper, val_z);
1697 ELSE
1698 -- call get_specific_value()
1699 val_z := get_specific_value(profNameUpper,
1700 user_id_z,
1701 responsibility_id_z,
1702 application_id_z,
1703 org_id_z,
1704 server_id_z);
1705 END IF;
1706 -- If a profile value is found, then the profile option is considered
1707 -- defined.
1708 IF (val_z IS NOT NULL)
1709 AND (val_z <> fnd_undefined_value) THEN
1710 defined_z := TRUE;
1711 ELSE
1712 defined_z := FALSE;
1713 END IF;
1714
1715 -- Log API exit
1716 corelog(profNameUpper,
1717 nvl(val_z, 'NO VALUE'),
1718 'Exit GET_SPECIFIC',
1719 user_id_z,
1720 responsibility_id_z,
1721 application_id_z,
1722 org_id_z,
1723 server_id_z);
1724 END get_specific;
1725
1726 /*
1727 ** value
1728 ** returns the profile option value using the prevailing security
1729 ** context. This is the function API equivalent to get().
1730 **
1731 ** The algorithm is such:
1732 ** 1. Check Public Put Cache to see if a value has already been placed for
1733 ** the profile.
1734 ** 2. If no value was found in Public Put Cache, check to see if the
1735 ** profile option exists in FND_PROFILE_OPTIONS using
1736 ** get_profile_rowid().
1737 ** 3. If the profile option does not exist, then return NULL.
1738 ** If the profile option exists, call get_specific_value() to get a
1739 ** profile option value.
1740 **
1741 */
1742 FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
1743 profileOptionValue VARCHAR2(240) := NULL;
1744 profileNameUpper VARCHAR2(80) := upper(NAME);
1745 BEGIN
1746 -- Log entry
1747 corelog(profileNameUpper,
1748 nvl(profileOptionValue, 'NO VALUE'),
1749 'Enter VALUE');
1750 -- Bug 12395376:Check Public Put Cache to see if a value was placed for the
1751 -- profile option using PUT().
1752 profileOptionValue := get_put_value(profileNameUpper);
1753 -- If no value was found in Public Put cache then
1754 IF profileOptionValue IS NOT NULL THEN
1755 -- If the value found in cache is FND_UNDEFINED_VALUE, then reset it
1756 -- back to NULL.
1757 IF (profileOptionValue = fnd_undefined_value) THEN
1758 profileOptionValue := NULL;
1759 END IF;
1760 ELSE
1761 -- If profile option exists, then
1762 IF get_profile_rowid(profileNameUpper) IS NOT NULL THEN
1763 -- call get_specific_value and explicitly pass in the current context,
1764 -- to find the profile option value. Current context needs to be
1765 -- explicitly passed in, so that the result-caching function
1766 -- get_specific_value() caches a value based on that exact function
1767 -- call signature.
1768 profileOptionValue := get_specific_value(profileNameUpper,
1769 profiles_user_id,
1770 profiles_resp_id,
1771 profiles_appl_id,
1772 profiles_org_id,
1773 profiles_server_id);
1774 -- Log that profile option value was found using get_specific_value()
1775 IF profileOptionValue IS NOT NULL THEN
1776 corelog_using_name(profileNameUpper,
1777 ' value gsv() returned ' ||
1778 profileOptionValue || ':' ||
1779 userenv('sessionid'));
1780 END IF;
1781 ELSE
1782 -- Log that profile option was not found
1783 corelog_using_name(profileNameUpper,
1784 'TRANSIENT, put ' || fnd_undefined_value ||
1785 ' in Public Put cache');
1786 -- Cache FND_UNDEFINED_VALUE in Public Put cache
1787 put(profileNameUpper, fnd_undefined_value);
1788 END IF;
1789 END IF; -- profileOptionValue IS NOT NULL in Public Put cache
1790 -- Log exit
1791 corelog(profileNameUpper,
1792 nvl(profileOptionValue, 'NO VALUE'),
1793 'Exit VALUE');
1794 RETURN profileOptionValue;
1795 END VALUE;
1796
1797 /*
1798 ** value_specific
1799 ** returns the profile value of a specific security context, i.e. user,
1800 ** responsibility, application, server, organization. This is a wrapper
1801 ** function to value_specific_private().
1802 **
1803 ** Context arguments (USER_ID, RESPONSIBILITY_ID, APPLICATION_ID,
1804 ** ORG_ID, SERVER_ID) specify what context to use to determine the
1805 ** profile option value. Context arguments are interpreted as follows:
1806 **
1807 ** NULL - use current session context value (default)
1808 ** -1 - override current context with "undefined" value
1809 ** <value> - override current context with specified value
1810 **
1811 ** Special Notes:
1812 ** - Context override values are only used for determining the profile
1813 ** option value in this function call, the user session context is not
1814 ** changed.
1815 **
1816 ** - An undefined context value (-1) causes that context level to be
1817 ** skipped during processing, meaning that any profile option values
1818 ** set at that context level are ignored.
1819 **
1820 ** - Regardless of which context levels are specified, the profile option
1821 ** HIERARCHY_TYPE and '%_ENABLED_FLAG' flags determine which context
1822 ** levels are searched to find the value.
1823 **
1824 ** - value_specific() used to be a wrapper for get_specific(). With the
1825 ** RDBMS result-set caching feature, calling get_specific() was
1826 ** impacting performance. Hence, value_specific() now calls either
1827 ** value() or get_specific_value() for better performance.
1828 **
1829 ** - For any NULL context value passed in, the respective profile level
1830 ** context is defaulted.
1831 */
1832 FUNCTION value_specific
1833 (
1834 name IN VARCHAR2,
1835 user_id IN NUMBER DEFAULT NULL,
1836 responsibility_id IN NUMBER DEFAULT NULL,
1837 application_id IN NUMBER DEFAULT NULL,
1838 org_id IN NUMBER DEFAULT NULL,
1839 server_id IN NUMBER DEFAULT NULL
1840 ) RETURN VARCHAR2 IS
1841 profileValue VARCHAR2(255);
1842 profileNameUpper VARCHAR2(80) := upper(name);
1843 BEGIN
1844 -- Log entry
1845 corelog(profileNameUpper,
1846 nvl(profileValue, 'NO VALUE'),
1847 'Enter VALUE_SPECIFIC',
1848 user_id,
1849 responsibility_id,
1850 application_id,
1851 org_id,
1852 server_id);
1853 -- If override context passed in matches the existing context, call
1854 -- value()
1855 IF ((user_id = profiles_user_id) OR (user_id IS NULL))
1856 AND ((responsibility_id = profiles_resp_id) OR
1857 (responsibility_id IS NULL))
1858 AND
1859 ((application_id = profiles_appl_id) OR (application_id IS NULL))
1860 AND ((org_id = profiles_org_id) OR (org_id IS NULL))
1861 AND ((server_id = profiles_server_id) OR (server_id IS NULL)) THEN
1862 -- Log redirect to value()
1863 corelog(profileNameUpper,
1864 nvl(profileValue, 'NO VALUE'),
1865 'CURRENT context, calling VALUE',
1866 user_id,
1867 responsibility_id,
1868 application_id,
1869 org_id,
1870 server_id);
1871 -- call value()
1872 profileValue := VALUE(profileNameUpper);
1873 ELSE
1874 -- call get_specific_value()
1875 --
1876 -- Bug 12319479: get_specific_value() needs to be explicitly called with
1877 -- the nvl function because the arguments passed into the API defines the
1878 -- signature of the call, and is the basis of the RDBMS result-set caching
1879 -- feature for returning the proper cached value, IF a cached value
1880 -- applies. Note from the comments above:
1881 -- NULL - use current session context value (default)
1882 -- This condition above is what the nvl function is used for.
1883 profileValue := get_specific_value(profileNameUpper,
1884 nvl(user_id, profiles_user_id),
1885 nvl(responsibility_id,
1886 profiles_resp_id),
1887 nvl(application_id,
1888 profiles_appl_id),
1889 nvl(org_id, profiles_org_id),
1890 nvl(server_id,
1891 profiles_server_id));
1892 END IF;
1893 -- Log exit
1894 corelog(profileNameUpper,
1895 nvl(profileValue, 'NO VALUE'),
1896 'Exit VALUE_SPECIFIC',
1897 user_id,
1898 responsibility_id,
1899 application_id,
1900 org_id,
1901 server_id);
1902
1903 RETURN profileValue;
1904
1905 END value_specific;
1906
1907 /*
1908 ** defined
1909 ** determines whether a profile option has a value using get().
1910 **
1911 ** RETURNS: TRUE, if the profile option has a value
1912 ** FALSE, otherwise.
1913 **
1914 ** NOTE: A return of FALSE does not imply that the profile option is not
1915 ** defined in FND_PROFILE_OPTIONS. It just means that no value was
1916 ** found in Public Put cache or FND_PROFILE_OPTION_VALUES.
1917 */
1918 FUNCTION defined(NAME IN VARCHAR2) RETURN BOOLEAN IS
1919 profileValue VARCHAR2(240);
1920 BEGIN
1921 get(NAME, profileValue);
1922 RETURN(profileValue IS NOT NULL);
1923 END defined;
1924
1925 /*
1926 ** save_user
1927 ** Saves the value of a profile option, at the user level for the current
1928 ** user, to the database permanently using save(). This routine will not
1929 ** actually commit the changes; the caller must commit.
1930 **
1931 ** RETURNS: TRUE if successful, FALSE if failure.
1932 */
1933 FUNCTION save_user
1934 (
1935 x_name IN VARCHAR2, /* Profile name you are setting */
1936 x_value IN VARCHAR2 /* Profile value you are setting */
1937 ) RETURN BOOLEAN IS
1938 result BOOLEAN;
1939 BEGIN
1940 result := SAVE(x_name, x_value, 'USER', fnd_global.user_id);
1941 RETURN result;
1942 END save_user;
1943
1944 /*
1945 ** save
1946 ** Saves the value of a profile option permanently to the database, at any
1947 ** level using the FND_PROFILE_OPTION_VALUES_PKG. This routine can be used
1948 ** at runtime or during patching. This routine will not actually commit
1949 ** the changes; the caller must commit.
1950 **
1951 ** ('SITE', 'APPL', 'RESP', 'USER', 'SERVER', 'ORG', or 'SERVRESP').
1952 **
1953 ** Examples of use:
1954 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
1955 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
1956 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
1957 ** FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);
1958 ** FND_PROFILE.SAVE('P_NAME', 'SERVER', 25);
1959 ** FND_PROFILE.SAVE('P_NAME', 'ORG', 204);
1960 ** FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, 25);
1961 ** FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, -1);
1962 ** FND_PROFILE.SAVE('P_NAME', 'SERVRESP', -1, -1, 25);
1963 **
1964 ** Arguments/Parameters:
1965 ** profileName - Profile name you are setting
1966 ** profileValue - Profile value you are setting
1967 ** levelName - Level that you're setting at:
1968 ** 'SITE','APPL','RESP','USER', etc.
1969 ** levelValue - context value used for setting profile option
1970 ** value, e.g. user_id for 'USER' level. This does
1971 ** not apply to 'SITE' level.
1972 ** levelValueAppId - applies to 'RESP' and 'SERVRESP' levels, i.e.
1973 ** the Resp Application_Id.
1974 ** levelValue2 - 2nd context value used for setting profile
1975 ** option value. This applies to the 'SERVRESP'
1976 ** hierarchy.
1977 **
1978 ** RETURNS: TRUE if successful, FALSE if failure.
1979 */
1980 FUNCTION SAVE(x_name IN VARCHAR2,
1981 /* Profile name you are setting */
1982 x_value IN VARCHAR2,
1983 /* Profile value you are setting */
1984 x_level_name IN VARCHAR2,
1985 /* Level that you're setting at: 'SITE','APPL','RESP','USER',
1986 etc. */
1987 x_level_value IN VARCHAR2 DEFAULT NULL,
1988 /* Level value that you are setting at, e.g. user id for 'USER'
1989 level. X_LEVEL_VALUE is not used at site level. */
1990 x_level_value_app_id IN VARCHAR2 DEFAULT NULL,
1991 /* Used for 'RESP' and 'SERVRESP' level; Resp Application_Id. */
1992 x_level_value2 IN VARCHAR2 DEFAULT NULL
1993 /* 2nd Level value that you are setting at. This is for the
1994 'SERVRESP' hierarchy. */)
1995 RETURN BOOLEAN IS
1996 profNameUpper VARCHAR2(80) := upper(x_name);
1997 profileRowId ROWID := get_profile_rowid(profNameUpper);
1998 profileOptionId NUMBER := NULL;
1999 applicationId NUMBER := NULL;
2000 siteEnabled VARCHAR2(1) := NULL;
2001 applEnabled VARCHAR2(1) := NULL;
2002 respEnabled VARCHAR2(1) := NULL;
2003 userEnabled VARCHAR2(1) := NULL;
2004 orgEnabled VARCHAR2(1) := NULL;
2005 serverEnabled VARCHAR2(1) := NULL;
2006 servrespEnabled VARCHAR2(1) := NULL;
2007 hierarchyType VARCHAR2(8) := NULL;
2008 userChangeable VARCHAR2(1) := NULL;
2009 profileOptionValue VARCHAR2(240);
2010 levelId NUMBER;
2011 levelValueActual NUMBER;
2012 levelValue2Actual NUMBER;
2013 userName VARCHAR2(100);
2014 BEGIN
2015
2016 fnd_core_log.write_profile_save(profNameUpper,
2017 nvl(x_value, 'NO VALUE') ||
2018 ':Enter SAVE',
2019 x_level_name,
2020 x_level_value,
2021 x_level_value_app_id,
2022 x_level_value2);
2023
2024 -- If profile option value being set is > 240 characters, then place the
2025 -- message FND_PROFILE_OPTION_VAL_TOO_LRG into the error stack and
2026 -- return FALSE.
2027 --
2028 -- The lengthb() function replaced the length() function to handle
2029 -- multibyte characters appropriately.
2030 IF lengthb(x_value) > 240 THEN
2031 fnd_message.set_name('FND', 'FND_PROFILE_OPTION_VAL_TOO_LRG');
2032 fnd_message.set_token('PROFILE_OPTION_NAME', profNameUpper);
2033 fnd_message.set_token('PROFILE_OPTION_VALUE', x_value);
2034 RETURN FALSE;
2035 END IF;
2036
2037 IF (profileRowId IS NULL) THEN
2038 -- Log save attempt for non-existent profile
2039 corelog_line('Cannot save value for non-existent profile ' ||
2040 profNameUpper);
2041 RETURN FALSE;
2042 ELSE
2043 -- If profile option exists, then retrieve the definition
2044 profileOptionId := get_profile_id(profileRowId, profNameUpper);
2045 applicationId := get_profile_appl_id(profileRowId, profNameUpper);
2046 siteEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'SITE');
2047 applEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'APPL');
2048 respEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'RESP');
2049 userEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'USER');
2050 orgEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'ORG');
2051 serverEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'SERVER');
2052 servrespEnabled := get_level_enabled_flag(profileRowId,profNameUpper,'SERVRESP');
2053 hierarchyType := get_hierarchy_type(profileRowId,profNameUpper);
2054 userChangeable := get_user_changeable_flag(profileRowId,profNameUpper);
2055 END IF;
2056
2057 /* The following IF-THEN statements check to see if the arguments passed
2058 ** into SAVE comply with FND_PROFILE_OPTION_VALUES constraints with
2059 ** regards to saving profile option values.
2060 */
2061
2062 -- The LEVEL_VALUE_APPLICATION_ID applies to the Resp and Server/Resp
2063 -- levels only.
2064 IF (x_level_value_app_id IS NOT NULL AND x_level_name <> 'RESP' AND
2065 x_level_name <> 'SERVRESP') THEN
2066 RETURN FALSE;
2067 END IF;
2068
2069 -- The LEVEL_VALUE can only be null for SITE level.
2070 IF (x_level_value IS NULL) THEN
2071 levelValueActual := 0;
2072 IF (x_level_name <> 'SITE') THEN
2073 RETURN FALSE; -- Only allow NULL levelValue at SITE level
2074 END IF;
2075
2076 -- The LEVEL_VALUE2 is required for SERVRESP level, -1 should be passed
2077 -- as a default.
2078 ELSIF ((x_level_name = 'SERVRESP') AND (x_level_value2 IS NULL)) THEN
2079 -- 'SERVRESP' requires a value for levelValue2 to save
2080 -- the profile option value properly.
2081 RETURN FALSE;
2082 ELSE
2083 levelValueActual := to_number(x_level_value);
2084 IF (x_level_name = 'SERVRESP')
2085 AND (x_level_value2 IS NOT NULL) THEN
2086 levelValue2Actual := to_number(x_level_value2);
2087 END IF;
2088 END IF;
2089
2090 /* Check if the profile option value being saved is already the profile
2091 ** option's value at the level specified. If it is, then just return TRUE.
2092 ** This IF-THEN block uses the functions that are enabled for result set
2093 ** caching.
2094 */
2095 -- SITE level
2096 IF (x_level_name = 'SITE' AND siteEnabled = 'Y') THEN
2097 levelId := SITE_LEVEL_ID;
2098
2099 IF (levelValueActual <> 0) THEN
2100 RETURN FALSE; -- the only site-level allowed is zero.
2101 END IF;
2102 corelog_using_name(profNameUpper, 'SAVE:GSV, Site');
2103 profileOptionValue := get_site_value(profileOptionId,
2104 applicationId);
2105
2106 -- APPLICATION level
2107 ELSIF (x_level_name = 'APPL' AND applEnabled = 'Y') THEN
2108 levelId := APPL_LEVEL_ID;
2109 corelog_using_name(profNameUpper, 'SAVE:GAV, Appl');
2110 profileOptionValue := get_appl_value(profileOptionId,
2111 applicationId,
2112 levelValueActual);
2113
2114 -- RESPONSIBILITY level
2115 ELSIF (x_level_name = 'RESP' AND respEnabled = 'Y') THEN
2116 levelId := RESP_LEVEL_ID;
2117 corelog_using_name(profNameUpper, 'SAVE:GRV, Resp');
2118 profileOptionValue := get_resp_value(profileOptionId,
2119 applicationId,
2120 levelValueActual,
2121 x_level_value_app_id);
2122
2123 -- USER level
2124 ELSIF (x_level_name = 'USER' AND userEnabled = 'Y') THEN
2125 levelId := USER_LEVEL_ID;
2126 corelog_using_name(profNameUpper, 'SAVE:GUV, User');
2127 profileOptionValue := get_user_value(profileOptionId,
2128 applicationId,
2129 levelValueActual);
2130 corelog_using_name(profNameUpper,
2131 'SAVE:GUV, User:' ||
2132 nvl(profileOptionValue, 'NULL'));
2133 -- SERVER level
2134 ELSIF (x_level_name = 'SERVER' AND serverEnabled = 'Y') THEN
2135 levelId := SERVER_LEVEL_ID;
2136 corelog_using_name(profNameUpper, 'SAVE:GSV, Server');
2137 profileOptionValue := get_server_value(profileOptionId,
2138 applicationId,
2139 levelValueActual);
2140
2141 -- ORGANIZATION level
2142 ELSIF (x_level_name = 'ORG' AND orgEnabled = 'Y') THEN
2143 levelId := ORG_LEVEL_ID;
2144 corelog_using_name(profNameUpper, 'SAVE:GOV, Org');
2145 profileOptionValue := get_org_value(profileOptionId,
2146 applicationId,
2147 levelValueActual);
2148
2149 -- SERVER+RESPONSIBILITY level
2150 ELSIF (x_level_name = 'SERVRESP' AND servrespEnabled = 'Y') THEN
2151 levelId := SERVRESP_LEVEL_ID;
2152 corelog_using_name(profNameUpper, 'SAVE:GSRV, ServResp');
2153 profileOptionValue := get_servresp_value(profileOptionId,
2154 applicationId,
2155 levelValueActual,
2156 x_level_value_app_id,
2157 levelValue2Actual);
2158 ELSE
2159 -- If the conditions were not met, then the SAVE() call was likely
2160 -- attempting to save a value that violates the hierarchy and enabled
2161 -- flag constraints, e.g. if level specified is USER and the
2162 -- USER_ENABLED_FLAG is disabled, the value should not be saved.
2163 corelog_line('Conditions not met to save value for ' ||
2164 'profile ' || profNameUpper);
2165 RETURN FALSE;
2166 END IF;
2167
2168 -- If the profile option value being saved is the same as the value
2169 -- obtained from the get functions, then there is no need to go further.
2170 -- Just return TRUE;
2171 IF ((profileOptionValue = x_value) OR
2172 (profileOptionValue IS NULL AND x_value IS NULL)) THEN
2173 corelog_line('Value being saved is already the ' ||
2174 'current value for profile ' || profNameUpper);
2175 RETURN TRUE;
2176 END IF;
2177
2178 -- If profile option value passed in is NULL, then clear accordingly.
2179 IF (x_value IS NULL) THEN
2180 -- If SERVRESP level, then take LEVEL_VALUE2 into consideration.
2181 IF (levelId = SERVRESP_LEVEL_ID) THEN
2182 -- DELETE
2183 fnd_profile_option_values_pkg.delete_row(applicationId,
2184 profileOptionId,
2185 levelId,
2186 levelValueActual,
2187 x_level_value_app_id,
2188 levelValue2Actual);
2189 -- raise cache invalidation event for processes monitoring the
2190 -- event
2191 invalidate_cache(profNameUpper,
2192 levelId,
2193 levelValueActual,
2194 x_level_value_app_id,
2195 levelValue2Actual);
2196 ELSE
2197 -- DELETE
2198 fnd_profile_option_values_pkg.delete_row(applicationId,
2199 profileOptionId,
2200 levelId,
2201 levelValueActual,
2202 x_level_value_app_id);
2203 -- raise cache invalidation event for processes monitoring the
2204 -- event
2205 invalidate_cache(profNameUpper,
2206 levelId,
2207 levelValueActual,
2208 x_level_value_app_id);
2209 END IF;
2210 ELSE
2211 -- If profile option value passed in is NOT NULL, then update
2212 -- accordingly. FND_PROFILE_OPTION_VALUES_PKG.UPDATE_ROW() will call
2213 -- FND_PROFILE_OPTION_VALUES_PKG.INSERT_ROW(), if needed. There is no
2214 -- need to call FND_PROFILE_OPTION_VALUES_PKG.INSERT_ROW(), explicitly,
2215 -- from here. If SERVRESP level, then take LEVEL_VALUE2 into
2216 -- consideration.
2217 IF (levelId = SERVRESP_LEVEL_ID) THEN
2218 fnd_profile_option_values_pkg.update_row(applicationId,
2219 profileOptionId,
2220 levelId,
2221 levelValueActual,
2222 x_level_value_app_id,
2223 levelValue2Actual,
2224 x_value,
2225 SYSDATE,
2226 nvl(fnd_global.user_id,
2227 DEFAULT_CONTEXT),
2228 nvl(fnd_global.login_id,
2229 DEFAULT_CONTEXT));
2230 -- raise cache invalidation event for processes monitoring the
2231 -- event
2232 invalidate_cache(profNameUpper,
2233 levelId,
2234 levelValueActual,
2235 x_level_value_app_id,
2236 levelValue2Actual);
2237 ELSE
2238 fnd_profile_option_values_pkg.update_row(applicationId,
2239 profileOptionId,
2240 levelId,
2241 levelValueActual,
2242 x_level_value_app_id,
2243 NULL,
2244 x_value,
2245 SYSDATE,
2246 nvl(fnd_global.user_id,
2247 DEFAULT_CONTEXT),
2248 nvl(fnd_global.login_id,
2249 DEFAULT_CONTEXT));
2250 -- raise cache invalidation event for processes monitoring the
2251 -- event
2252 invalidate_cache(profNameUpper,
2253 levelId,
2254 levelValueActual,
2255 x_level_value_app_id);
2256 END IF;
2257 END IF;
2258
2259 /* Bug 3203225: PREFERENCES NOT UPDATED ON FLY IN WF_ROLES VIEW
2260 ** needs to call FND_USER_PKG.User_Synch() whenever an update to
2261 ** ICX_LANGUAGE or ICX_TERRITORY is updated at the user level.
2262 */
2263 IF ((profNameUpper = 'ICX_LANGUAGE') OR
2264 (profNameUpper = 'ICX_TERRITORY')) THEN
2265 IF ((levelId = USER_LEVEL_ID) AND (levelValueActual IS NOT NULL)) THEN
2266 SELECT user_name
2267 INTO userName
2268 FROM fnd_user
2269 WHERE user_id = levelValueActual;
2270
2271 fnd_user_pkg.user_synch(userName);
2272 END IF;
2273 END IF;
2274
2275 -- Log exit
2276 fnd_core_log.write_profile_save(profNameUpper,
2277 nvl(x_value, 'NO VALUE') ||
2278 ':Exit SAVE',
2279 x_level_name,
2280 x_level_value,
2281 x_level_value_app_id,
2282 x_level_value2);
2283
2284 RETURN TRUE;
2285
2286 END SAVE;
2287
2288 /*
2289 ** delete
2290 ** deletes the value of a profile option permanently from the database, at
2291 ** any level. This routine serves as a wrapper to the SAVE routine which
2292 ** means that this routine can be used at runtime or during patching. Like
2293 ** the SAVE routine, this routine will not actually commit the changes;
2294 ** the caller must commit. This API was added for enhancement request
2295 ** 4430579.
2296 **
2297 ** ('SITE', 'APPL', 'RESP', 'USER', 'SERVER', 'ORG', or 'SERVRESP').
2298 **
2299 ** Examples of use:
2300 ** FND_PROFILE.DELETE('P_NAME', 'SITE');
2301 ** FND_PROFILE.DELETE('P_NAME', 'APPL', 321532);
2302 ** FND_PROFILE.DELETE('P_NAME', 'RESP', 321532, 345234);
2303 ** FND_PROFILE.DELETE('P_NAME', 'USER', 123321);
2304 ** FND_PROFILE.DELETE('P_NAME', 'SERVER', 25);
2305 ** FND_PROFILE.DELETE('P_NAME', 'ORG', 204);
2306 ** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', 321532, 345234, 25);
2307 ** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', 321532, 345234, -1);
2308 ** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', -1, -1, 25);
2309 **
2310 ** Arguments/Parameters:
2311 ** profileName - Profile name whose value is to be deleted
2312 ** levelName - Level deleting at:
2313 ** 'SITE','APPL','RESP','USER', etc.
2314 ** levelValue - context value used for deleting profile option
2315 ** value, e.g. user_id for 'USER' level. This does
2316 ** not apply to 'SITE' level.
2317 ** levelValueAppId - applies to 'RESP' and 'SERVRESP' levels, i.e.
2318 ** the Resp Application_Id.
2319 ** levelValue2 - 2nd context value used for deleting profile
2320 ** option value. This applies to the 'SERVRESP'
2321 ** hierarchy.
2322 **
2323 ** RETURNS: TRUE if successful, FALSE if failure.
2324 */
2325 FUNCTION DELETE(x_name IN VARCHAR2,
2326 /* Profile name you are setting */
2327 x_level_name IN VARCHAR2,
2328 /* Level that you're setting at: 'SITE','APPL','RESP','USER',
2329 etc. */
2330 x_level_value IN VARCHAR2 DEFAULT NULL,
2331 /* Level value that you are setting at, e.g. user id for
2332 'USER' level. X_LEVEL_VALUE is not used at site level. */
2333 x_level_value_app_id IN VARCHAR2 DEFAULT NULL,
2334 /* Used for 'RESP' and 'SERVRESP' level; Resp Application_Id. */
2335 x_level_value2 IN VARCHAR2 DEFAULT NULL
2336 /* 2nd Level value that you are setting at. This is for the
2337 'SERVRESP' hierarchy only. */)
2338 RETURN BOOLEAN IS
2339 deleted BOOLEAN;
2340 BEGIN
2341 -- Call SAVE() and pass NULL for the profile option value. This
2342 -- physically deletes the row from FND_PROFILE_OPTION_VALUES. Ideally,
2343 -- FND_PROFILE_OPTION_VALUES_PKG.DELETE_ROW would be called. However, all
2344 -- validation/conversion performed in SAVE() would have to be written here
2345 -- as well OR writing a completely separate API just to validate the data
2346 -- so that both SAVE and DELETE can use it.
2347 deleted := SAVE(x_name,
2348 NULL,
2349 x_level_name,
2350 x_level_value,
2351 x_level_value_app_id,
2352 x_level_value2);
2353 RETURN deleted;
2354 END DELETE;
2355
2356 /*
2357 ** bumpCacheVersion_RF
2358 ** The rule function for FND's subscription on the
2359 ** oracle.apps.fnd.profile.value.update event.
2360 ** Since level hash table caches are no longer used due to the PL/SQL
2361 ** cross-session function result caching mechanism, this is really no
2362 ** longer being used. It will return SUCCESS for backward compatibility.
2363 */
2364 FUNCTION bumpcacheversion_rf
2365 (
2366 p_subscription_guid IN RAW,
2367 p_event IN OUT NOCOPY wf_event_t
2368 ) RETURN VARCHAR2 IS
2369 BEGIN
2370 RETURN 'SUCCESS';
2371 END;
2372
2373 /*
2374 ** putmultiple
2375 ** puts multiple option pairs in the table
2376 **
2377 ** The name and val VARCHAR2s are of max size 2000, and hold the
2378 ** concatenations of the strings for each individual pair, with null
2379 ** terminators (CHR(0)) to seperate the values. The number of pairs
2380 ** is passed in numval. This setup is to avoid the overhead of calling
2381 ** the put routine multiple times.
2382 **
2383 ** [NOTE: THIS API IS FOR AOL INTERNAL USE ONLY.]
2384 */
2385 PROCEDURE putmultiple
2386 (
2387 names IN VARCHAR2,
2388 vals IN VARCHAR2,
2389 num IN NUMBER
2390 ) IS
2391 pairnum NUMBER;
2392 nstartloc NUMBER;
2393 nendloc NUMBER;
2394 vstartloc NUMBER;
2395 vendloc NUMBER;
2396 onename VARCHAR2(81);
2397 oneval VARCHAR2(256);
2398 BEGIN
2399 nstartloc := 1;
2400 vstartloc := 1;
2401 FOR pairnum IN 1 .. num LOOP
2402 nendloc := instr(names, chr(0), nstartloc);
2403 onename := substr(names, nstartloc, nendloc - nstartloc);
2404 nstartloc := nendloc + 1;
2405 vendloc := instr(vals, chr(0), vstartloc);
2406 oneval := substr(vals, vstartloc, vendloc - vstartloc);
2407 vstartloc := vendloc + 1;
2408 put(onename, oneval);
2409 END LOOP;
2410
2411 EXCEPTION
2412 WHEN OTHERS THEN
2413 NULL;
2414 END putmultiple;
2415
2416 /*
2417 ** get_all_table_values
2418 ** get all the values from the Public Put Cache. The varchar2 returned
2419 ** can be up to 32767 characters long.
2420 **
2421 ** [NOTE: THIS API IS FOR AOL INTERNAL USE ONLY.]
2422 */
2423 FUNCTION get_all_table_values(delim IN VARCHAR2) RETURN VARCHAR2 IS
2424 table_index BINARY_INTEGER;
2425 retval VARCHAR2(32767);
2426 val VARCHAR2(1000);
2427 BEGIN
2428 -- check inserted counter - bug 12975860
2429 IF (inserted = 0) THEN
2430 RETURN NULL;
2431 END IF;
2432 table_index := 1;
2433 retval := 'PUT CACHE: ';
2434 WHILE (table_index < table_size) LOOP
2435
2436 IF (name_tab.exists(table_index) AND
2437 (val_tab(table_index) IS NOT NULL)) THEN
2438 val := name_tab(table_index) || delim || val_tab(table_index) ||
2439 delim;
2440 corelog_line('FP.GATV: ' ||val);
2441 IF length(val) + length(retval) > 32767 THEN
2442 RETURN retval;
2443 END IF;
2444 retval := retval || val;
2445 END IF;
2446
2447 table_index := table_index + 1;
2448
2449 END LOOP;
2450
2451 RETURN retval;
2452
2453 EXCEPTION
2454 WHEN OTHERS THEN
2455 -- add a corelog dump on exception
2456 corelog_line('GET_ALL_TABLE_VALUES raised exception. SQLCODE:' || SQLCODE);
2457 -- output exception to corelog
2458 corelog_line(dbms_utility.format_error_stack);
2459 -- output error line numbers to corelog
2460 corelog_line(dbms_utility.format_call_stack);
2461 RETURN NULL;
2462
2463 END get_all_table_values;
2464
2465
2466 /*
2467 ** get_table_value
2468 ** get the value of a profile option from Public Put Cache. This is a
2469 ** wrapper to get_put_value().
2470 **
2471 ** [NOTE: THIS API IS FOR AOL INTERNAL USE ONLY.]
2472 */
2473 FUNCTION get_table_value(name IN VARCHAR2) RETURN VARCHAR2 IS
2474 BEGIN
2475 RETURN get_put_value(name);
2476 END get_table_value;
2477
2478 /*
2479 ** AOL INTERNAL USE ONLY
2480 **
2481 ** PUT_CACHE_CLEARED - returns true if the put cache was cleared.
2482 */
2483 FUNCTION put_cache_cleared RETURN BOOLEAN IS
2484 BEGIN
2485 RETURN(put_cache_is_clear);
2486 END put_cache_cleared;
2487
2488 /*
2489 ** value_wnps
2490 ** returns the profile option value using the prevailing security
2491 ** context. This is a wrapper function to value().
2492 **
2493 ** NOTE: Prior to 11g Profile Options Caching, this routine did the
2494 ** same thing as VALUE(); it returns a profile value from the profile
2495 ** cache, or from the database if it isn't already in the profile
2496 ** cache already. The only difference between this and VALUE() is
2497 ** that this will not put the value into the pl/sql hash table level
2498 ** caches if it is not already there, so repeated calls to this can
2499 ** be slower because it will have to hit the database each time for
2500 ** the profile value.
2501 **
2502 ** With the obsoletion of pl/sql hash table level caches, this API is
2503 ** obsolete and is being maintained for backwards compatibility.
2504 */
2505 FUNCTION value_wnps(name IN VARCHAR2) RETURN VARCHAR2 IS
2506 BEGIN
2507 RETURN value(name);
2508 END value_wnps;
2509
2510 END fnd_profile;
[Home] [Help]