1 package body FND_MESSAGE as
2 /* $Header: AFNLMSGB.pls 120.10 2008/02/11 18:13:27 emiranda ship $ */
3
4 MSGNAME varchar2(30);
5 MSGDATA varchar2(32000);
6 MSGSET boolean := FALSE;
7 MSGAPP varchar2(50);
8 GLOBAL_AUTO_LOG boolean := TRUE;
9 DEFAULT_FND_LOG_MODULE varchar2(255) := 'fnd.plsql.FND_MESSAGE.auto_log';
10 FND_LOG_MODULE varchar2(255) := DEFAULT_FND_LOG_MODULE;
11
12 TYPE MSG_REC_TYPE is RECORD (
13 message_name VARCHAR(90), -- Concat of MSGNAME(30), LANGCODE(4), APPSHRT(50)
14 message_text FND_NEW_MESSAGES.message_text%TYPE,
15 message_number FND_NEW_MESSAGES.message_number%TYPE,
16 type FND_NEW_MESSAGES.type%TYPE,
17 fnd_log_severity FND_NEW_MESSAGES.fnd_log_severity%TYPE,
18 category FND_NEW_MESSAGES.category%TYPE,
19 severity FND_NEW_MESSAGES.severity%TYPE
20 );
21
22 type MSG_TAB_TYPE is table of MSG_REC_TYPE index by binary_integer;
23
24 TABLE_SIZE binary_integer := 8192; /* the cache size */
25 INSERTED boolean := FALSE;
26
27
28 /*
29 ** define the internal table that will cache the messages
30 */
31 MSG_TAB MSG_TAB_TYPE;
32
33 -------------------- Cache Routines AOL - INTERNAL ONLY -----------------------------------
34
35 /*
36 ** find - find index of an option name in the given table
37 **
38 ** RETURNS
39 ** table index if found, TABLE_SIZE if not found.
40 */
41 function FIND(NAME in varchar2, msg_table in MSG_TAB_TYPE)
42 return binary_integer is
43
44 TAB_INDEX binary_integer;
45 FOUND boolean;
46 HASH_VALUE number;
47 NAME_UPPER varchar2(90); -- Concat of MSGNAME(30), LANGCODE(4), APPSHRT(50)
48
49 begin
50
51 NAME_UPPER := upper(NAME);
52 TAB_INDEX := dbms_utility.get_hash_value(NAME_UPPER,1,TABLE_SIZE);
53
54 if (msg_table.EXISTS(TAB_INDEX)) then
55
56 if (msg_table(TAB_INDEX).message_name = NAME_UPPER) then
57 return TAB_INDEX;
58 else
59
60 HASH_VALUE := TAB_INDEX;
61 FOUND := false;
62
63 while (TAB_INDEX < TABLE_SIZE) and (not FOUND) loop
64 if (msg_table.EXISTS(TAB_INDEX)) then
65 if msg_table(TAB_INDEX).message_name = NAME_UPPER then
66 FOUND := true;
67 else
68 TAB_INDEX := TAB_INDEX + 1;
69 end if;
70 else
71 return TABLE_SIZE+1;
72 end if;
73 end loop;
74
75 if (not FOUND) then /* Didn't find any till the end */
76 TAB_INDEX := 1; /* Start from the beginning */
77 while (TAB_INDEX < HASH_VALUE) and (not FOUND) loop
78 if (msg_table.EXISTS(TAB_INDEX)) then
79 if msg_table(TAB_INDEX).message_name = NAME_UPPER then
80 FOUND := true;
81 else
82 TAB_INDEX := TAB_INDEX + 1;
83 end if;
84 else
85 return TABLE_SIZE+1;
86 end if;
87 end loop;
88 end if;
89
90 if (not FOUND) then
91 return TABLE_SIZE+1; /* Return a higher value */
92 end if;
93
94 end if;
95
96 else
97 return TABLE_SIZE+1;
98 end if;
99
100 return TAB_INDEX;
101
102 exception
103 when others then /* The entry doesn't exists */
104 return TABLE_SIZE+1;
105 end;
106
107 /*
108 ** find - find index of an option name
109 **
110 ** RETURNS
111 ** table index if found, TABLE_SIZE if not found.
112 */
113 function FIND(NAME in varchar2) return binary_integer is
114 TAB_INDEX binary_integer;
115 FOUND boolean;
116 HASH_VALUE number;
117 begin
118
119 return FIND(NAME,MSG_TAB);
120
121 exception
122 when others then /* The entry doesn't exists */
123 return TABLE_SIZE+1;
124 end;
125
126 /*
127 ** put - Set or Insert a Message
128 */
129 procedure PUT(
130 NAME in varchar2,
131 VAL in MSG_REC_TYPE,
132 msg_table in out NOCOPY MSG_TAB_TYPE)
133 is
134 TABLE_INDEX binary_integer;
135 STORED boolean;
136 HASH_VALUE number;
137 NAME_UPPER varchar2(90); -- Concat of MSGNAME(30), LANGCODE(4), APPSHRT(50)
138 begin
139
140 NAME_UPPER := upper(NAME);
141
142 /*
143 ** search for the option name
144 */
145 STORED := false;
146 TABLE_INDEX := dbms_utility.get_hash_value(NAME_UPPER,1,TABLE_SIZE);
147 if (msg_table.EXISTS(TABLE_INDEX)) then
148 if (msg_table(TABLE_INDEX).message_name = NAME_UPPER) then /* Found the message */
149 msg_table(TABLE_INDEX) := VAL; /* Store the new value */
150 STORED := TRUE;
151 else /* Collision */
152 HASH_VALUE := TABLE_INDEX; /* Store the current spot */
153
154 while (TABLE_INDEX < TABLE_SIZE) and (not STORED) loop
155 if (msg_table.EXISTS(TABLE_INDEX)) then
156 if (msg_table(TABLE_INDEX).message_name = NAME_UPPER) then
157 msg_table(TABLE_INDEX) := VAL;
158 STORED := true;
159 else
160 TABLE_INDEX := TABLE_INDEX + 1;
161 end if;
162 else
163 msg_table(TABLE_INDEX) := VAL;
164 STORED := true;
165 end if;
166 end loop;
167
168 if (not STORED) then /* Didn't find any free bucket till the end*/
169 TABLE_INDEX := 1;
170
171 while (TABLE_INDEX < HASH_VALUE) and (not STORED) loop
172 if (msg_table.EXISTS(TABLE_INDEX)) then
173 if (msg_table(TABLE_INDEX).message_name = NAME_UPPER) then
174 msg_table(TABLE_INDEX) := VAL;
175 STORED := true;
176 else
177 TABLE_INDEX := TABLE_INDEX + 1;
178 end if;
179 else
180 msg_table(TABLE_INDEX) := VAL;
181 STORED := true;
182 end if;
183 end loop;
184 end if;
185 if (not STORED) then
186 msg_table(HASH_VALUE) := VAL; /* Store its value */
187 STORED := TRUE;
188 end if;
189 end if;
190 else
191 msg_table(TABLE_INDEX) := VAL; /* Store its value */
192 STORED := TRUE;
193 end if;
194
195 if (STORED) then
196 INSERTED := TRUE;
197 end if;
198 exception
199 when others then
200 null;
201 end;
202
203 /*
204 ** put - Set or Insert a message
205 */
206 procedure PUT(NAME in varchar2, VAL in MSG_REC_TYPE) is
207 TABLE_INDEX binary_integer;
208 STORED boolean;
209 HASH_VALUE number;
210 begin
211 PUT(NAME,VAL,MSG_TAB);
212 end;
213
214 -------------------------------- END of cache routines ----------------------------------
215
216 --------------------------- TOKEN REPLACEMENT ROUTINES - AOL Internal -------------------
217 /****************************************************************************
218 This procedure will take an input string(p_build_msg), determine if there is
219 an EXACT match between the input token and a token contained in the message.
220 If there is a match, the beginning location of the token in the message
221 is returned.
222 p_build_msg is the input string.
223 p_token is the string that must be matched within the input string.
224 p_srch_begin is the location in the string where the search for token begins.
225 x_tok_begin is the location in the string of the matched token.
226 ***************************************************************************/
227
228 procedure find_token(
229 p_build_msg IN VARCHAR2,
230 p_token IN VARCHAR2,
231 p_srch_begin IN NUMBER,
232 x_tok_begin OUT NOCOPY NUMBER)
233
234 IS
235
236 l_start NUMBER;
237 l_check_pos NUMBER;
238 l_char_after_pos NUMBER;
239 -- This creates 'Buffer Overflow' error for Multibyte character langauges.
240 -- Hence commenting.
241 -- l_char_after VARCHAR2(1);
242
243 -- Bug 6634185.
244 -- This variable requires NVARCHAR declaration to handle multibyte languages
245 -- WARNING!!!
246 -- NVARCHAR2 introduces dependency with RDBMS 9i version for FND_MESSAGE pkg
247 -- So this change can't be backported to older 11i RUPs like RUP4 which are
248 -- still certified with RDBMS 8i. Only since 11i RUP6, RDBMS 9i is mandatory
249 -- WARNING!!!
250 l_char_after NVARCHAR2(1);
251 ALPHANUMERIC_UNDERSCORE_MASK CONSTANT VARCHAR2(255) :=
252 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_';
253
254 BEGIN
255
256 l_start := p_srch_begin;
257
258 LOOP
259 -- Find the ampersand token value in the string.
260 -- This signifies a possible token match.
261 -- We say possible because the match could be a partial match to
262 -- another token name (i.e. VALUE in VALUESET).
263 l_check_pos := INSTR(p_build_msg, '&' || p_token, l_start);
264 IF (l_check_pos = 0) THEN
265 -- No more potential token matches exist in string.
266 -- Return o for token position
267 x_tok_begin := 0;
268 EXIT;
269 END IF;
270
271 -- Insure that match is not '&&' variable indicating an access key
272 IF ((l_check_pos <> 1) AND
273 (substr(p_build_msg, l_check_pos - 1, 1) = '&')) THEN
274 l_start := l_check_pos + 2;
275 ELSE
276
277 -- Determine if the potential match for the token is an EXACT match
278 -- or only a partial matc.
279 -- Determine if the character following the token match is an
280 -- acceptable trailing character for a token (i.e. something
281 -- other than an English uppercase alphabetic character,
282 -- a number, or an underscore - these indicate the token name
283 -- has additional characters)
284 -- If so, the token is considered an exact match.
285 l_char_after_pos := l_check_pos + LENGTH(p_token) + 1;
286 l_char_after := substr(p_build_msg, l_char_after_pos, 1);
287 IF ((INSTR(ALPHANUMERIC_UNDERSCORE_MASK, l_char_after) = 0) OR
288 (l_char_after_pos > LENGTH(p_build_msg))) THEN
289 x_tok_begin := l_check_pos;
290 EXIT;
291 ELSE
292 l_start := l_char_after_pos;
293 END IF;
294 END IF;
295 END LOOP;
296 END find_token;
297
298
299 /****************************************************************************
300 This procedure will take an input string(p_msg), call find_token to determine
301 if there is an EXACT match between the input token and a token contained in
302 the message, and replace the instance of the token found in the message
303 text with the input token value. The code loops until all instances of the
304 token name are found in the message text.
305 p_msg is the input string.
306 p_token is the string that must be replaced within the input string.
307 p_token_val is the value to replace a token name in the message string.
308 x_msg is the output string with matched token names replaced with token values
309 ***************************************************************************/
310
311 procedure token_value_replace(
312 p_msg IN VARCHAR2,
313 p_token IN VARCHAR2,
314 p_token_val IN VARCHAR2,
315 x_msg OUT NOCOPY VARCHAR2)
316
317 IS
318 l_token_exists NUMBER;
319 l_token VARCHAR2(30);
320 l_build_tmpmsg VARCHAR2(2000);
321 l_msg VARCHAR2(2000);
322 l_srch_begin NUMBER;
323 l_tok_begin NUMBER;
324
325 BEGIN
326 -- Check to see if any tokens exist in the error message
327 l_token := p_token;
328 l_token_exists := instr(p_msg, '&' || l_token);
329
330 /* If the input token isn't found in the message text, */
331 /* try the uppercased version of the token name in case */
332 /* the caller is (wrongly) passing a mixed case token name */
333 /* As of July 99 all tokens in msg text should be */
334 /* uppercase. */
335 IF (l_token_exists = 0) THEN
336 l_token := UPPER(l_token);
337 l_token_exists := instr(p_msg, '&' || l_token);
338 END IF;
339
340 -- Only process if instances of the token exist in the msg
341 IF(l_token_exists <> 0)THEN
342 l_build_tmpmsg := '';
343 l_srch_begin := 1;
344 l_msg := p_msg;
345
346 LOOP
347
348 find_token(l_msg, l_token, l_srch_begin, l_tok_begin);
349
350 IF (l_tok_begin = 0) THEN
351 -- No more tokens found in message
352 EXIT;
353 END IF;
354
355 -- Build string, replacing token with token value
356 l_build_tmpmsg := l_build_tmpmsg ||
357 substr(l_msg, l_srch_begin, l_tok_begin - l_srch_begin) ||
358 p_token_val;
359
360 -- Begin next search at the end of the processed token
361 -- including ampersand (the +1)
362 l_srch_begin := l_tok_begin + LENGTH(l_token) + 1;
363
364 END LOOP;
365
366 -- No more tokens in message. Concatenate the remainder
367 -- of the message.
368 l_build_tmpmsg := l_build_tmpmsg ||
369 substr(l_msg, l_srch_begin, LENGTH(l_msg) - l_srch_begin + 1);
370
371 x_msg := l_build_tmpmsg;
372
373 END IF;
374 END token_value_replace;
375
376
377 ---------------------------------END of TOKEN REPLACEMENT routines ----------------------
378
379 procedure SET_NAME(APPLICATION in varchar2, NAME in varchar2) is
380 begin
381 MSGAPP := APPLICATION;
382 -- Bug 5397597. Added SUBSTR to handle worst cases where product teams
383 -- pass incorrect message names with more than 30 chars.
384 MSGNAME := SUBSTR(NAME,1,30);
385 MSGDATA := '';
386 FND_LOG_MODULE := DEFAULT_FND_LOG_MODULE;
387 MSGSET := TRUE;
388 end;
389
390 /*
391 ** ### OVERLOADED (new private version) ###
392 **
393 ** SET_TOKEN - define a message token with a value
394 ** Private: This procedure is only to be called by the ATG
395 ** not for external use
396 ** Arguments:
397 ** token - message token
398 ** value - value to substitute for token
399 ** ttype - type of token substitution:
400 ** 'Y' translated, or "Yes, translated"
401 ** 'N' constant, or "No, not translated"
402 ** 'S' SQL query
403 **
404 */
405 procedure SET_TOKEN(TOKEN in varchar2,
406 VALUE in varchar2,
407 TTYPE in varchar2 default 'N') is
408 tok_type varchar2(1);
409 begin
410
411 if ( TTYPE not in ('Y','N','S')) then
412 tok_type := 'N';
413 else
414 tok_type := TTYPE;
415 end if;
416
417 /* Note that we are intentionally using chr(0) rather than */
418 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
419 /* 3722358 - replace chr(0) in VALUE with spaces */
420
421 /* Bug 5397597. If TTYPE='Y' then substrb(VALUE,1,30) (intentionally
422 * used substrb instead of substr to handle the worst cases that the
423 * product teams can introduce by calling this api improperly).
424 * This is to avoid ORA-6502 error when the product teams
425 * incorrectly calls this api.
426 */
427 if (TTYPE = 'Y') then /* translated token */
428 MSGDATA := MSGDATA||tok_type||chr(0)||TOKEN||chr(0)||
432 replace(VALUE,chr(0),' ')||chr(0);
429 replace(SUBSTRB(VALUE,1,30),chr(0),' ')||chr(0);
430 else
431 MSGDATA := MSGDATA||tok_type||chr(0)||TOKEN||chr(0)||
433 end if;
434
435 end set_token;
436
437 /*
438 ** ### OVERLOADED (original version) ###
439 **
440 ** SET_TOKEN - define a message token with a value,
441 ** either constant or translated
442 ** Public: This procedure to be used by all
443 */
444 procedure SET_TOKEN(TOKEN in varchar2,
445 VALUE in varchar2,
446 TRANSLATE in boolean default false) is
447 TTYPE varchar2(1);
448 begin
449 if TRANSLATE then
450 TTYPE := 'Y';
451 else
452 TTYPE := 'N';
453 end if;
454
455 SET_TOKEN(TOKEN, VALUE, TTYPE);
456
457 end set_token;
458
459 /*
460 ** SET_TOKEN_SQL - define a message token with a SQL query value
461 **
462 ** Description:
463 ** Like SET_TOKEN, except here the value is a SQL statement which
464 ** returns a single varchar2 value. (e.g. A translated concurrent
465 ** manager name.) This statement is run when the message text is
466 ** resolved, and the result is used in the token substitution.
467 **
468 ** Arguments:
469 ** token - Token name
470 ** value - Token value. A SQL statement
471 **
472 */
473 procedure SET_TOKEN_SQL (TOKEN in varchar2,
474 VALUE in varchar2) is
475
476 TTYPE varchar2(1) := 'S'; -- SQL Query
477 begin
478
479 SET_TOKEN(TOKEN, VALUE, TTYPE );
480
481 end set_token_sql;
482
483 /* This procedure is only to be called by the ATG; */
484 /* not for external use */
485 procedure RETRIEVE(MSGOUT out NOCOPY varchar2) is
486 OUT_VAL varchar2(2000);
487 begin
488 if MSGSET then
489 /* Note that we are intentionally using chr(0) rather than */
490 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
491 OUT_VAL := MSGAPP||chr(0)||MSGNAME||chr(0)||MSGDATA;
492 MSGSET := FALSE;
493 else
494 OUT_VAL := '';
495 end if;
496
497 MSGOUT := OUT_VAL;
498 end;
499
500 procedure CLEAR is
501 begin
502 msgset := FALSE;
503 end;
504
505 procedure RAISE_ERROR is
506 begin
507 /* Note that we are intentionally using chr(0) rather than */
508 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
509 raise_application_error(-20001,
510 MSGNAME||': '||replace(rtrim(MSGDATA,chr(0)),
511 chr(0), ', '));
512 end;
513
514 /*
515 ** SET_MODULE - defines the Module for FND_LOG purposes
516 */
517 procedure SET_MODULE(MODULE in varchar2) is
518 begin
519 FND_LOG_MODULE := MODULE;
520 end;
521
522 --------------------------
523 procedure GET_MESSAGE_INTERNAL(APPIN in varchar2,
524 NAMEIN in varchar2,
525 LANGIN in varchar2,
526 AUTO_LOG in varchar2,
527 MSG out NOCOPY varchar2, MSG_NUMBER out NOCOPY NUMBER,
528 MSG_TYPE out NOCOPY varchar2,
529 FND_LOG_SEVERITY out NOCOPY NUMBER,
530 ALERT_CATEGORY out NOCOPY varchar2,
531 ALERT_SEVERITY out NOCOPY varchar2) is
532 MSG_INDEX binary_integer;
533 MSG_REC MSG_REC_TYPE;
534 LANG_CODE varchar2(4);
535 cursor c1(NAME_ARG varchar2, LANG_ARG varchar2) is
536 select message_text, message_number, type, fnd_log_severity, category, severity
537 from fnd_new_messages m, fnd_application a
538 where NAME_ARG = m.message_name
539 and LANG_ARG = m.language_code
540 and APPIN = a.application_short_name
541 and m.application_id = a.application_id;
542 begin
543 /* Bug 5005625. */
544 /* This API is used only within this package and is called from
545 * GET_STRING and GET_NUMBER passing NULL for LANGIN parameter.
546 * So irrespective of the NLS session language, the message
547 * was always cached and retrieved with an index 'NAME|NULL|APP'.
548 * Hence the same message text was returned irrespective of the
549 * session language. Now, the code is modified to properly set
550 * the LANGIN to correct value and consequently to properly
551 * cache and retrieve the messages with index 'NAME|LANGIN|APP'
552 * This resolves the issue in bug 5005625
553 */
554
555 /* If the passed LANGIN is NULL then get the userenv('LANG') */
556 if (LANGIN is NULL) then
557 LANG_CODE := userenv('LANG');
558 else
559 LANG_CODE := LANGIN;
560 end if;
561
562 /* Get Message from cache */
563 MSG_INDEX:=FIND(NAMEIN||CHR(0)||LANG_CODE||CHR(0)||APPIN);
564
565 if ( MSG_INDEX <= TABLE_SIZE) then
569 FND_LOG_SEVERITY:=MSG_TAB(MSG_INDEX).fnd_log_severity;
566 MSG:=MSG_TAB(MSG_INDEX).message_text;
567 MSG_NUMBER:=MSG_TAB(MSG_INDEX).message_number;
568 MSG_TYPE:=MSG_TAB(MSG_INDEX).type;
570 ALERT_CATEGORY:=MSG_TAB(MSG_INDEX).category;
571 ALERT_SEVERITY:=MSG_TAB(MSG_INDEX).severity;
572 return;
573 end if;
574
575 /* Message is not available in the cache. So get it from the table */
576 open c1(UPPER(NAMEIN), LANG_CODE);
577 fetch c1 into MSG, MSG_NUMBER, MSG_TYPE, FND_LOG_SEVERITY,
578 ALERT_CATEGORY, ALERT_SEVERITY;
579
580 if (c1%NOTFOUND) then
581 close c1;
582 /* MessageText is not available in the table for LANG_CODE language.
583 * So get the MessageText in 'US' language
584 */
585 LANG_CODE := 'US';
586
587 /* First Check the Cache for Message in 'US' language */
588 MSG_INDEX:=FIND(NAMEIN||CHR(0)||LANG_CODE||CHR(0)||APPIN);
589
590 if ( MSG_INDEX <= TABLE_SIZE) then
591 MSG:=MSG_TAB(MSG_INDEX).message_text;
592 MSG_NUMBER:=MSG_TAB(MSG_INDEX).message_number;
593 MSG_TYPE:=MSG_TAB(MSG_INDEX).type;
594 FND_LOG_SEVERITY:=MSG_TAB(MSG_INDEX).fnd_log_severity;
595 ALERT_CATEGORY:=MSG_TAB(MSG_INDEX).category;
596 ALERT_SEVERITY:=MSG_TAB(MSG_INDEX).severity;
597 return;
598 end if;
599
600 /* Not found in the Cache, so get from table */
601 open c1(UPPER(NAMEIN), 'US');
602 fetch c1 into MSG, MSG_NUMBER, MSG_TYPE, FND_LOG_SEVERITY,
603 ALERT_CATEGORY, ALERT_SEVERITY;
604 end if;
605 close c1;
606
607 /* NULL Handling */
608 if ( MSG is NULL ) then -- i.e. Message was not found
609 MSG := NAMEIN;
610 else
611 if (MSG_NUMBER is NULL) then -- per GET_NUMBER api- NUMBER should be NULL
612 MSG_NUMBER := 0; -- only if Message does not exist
613 end if;
614 end if;
615
616 /* double ampersands don't have anything to do with tokens, they */
617 /* represent access keys. So we translate them to single ampersands*/
618 /* so that the access key code will recognize them. */
619 MSG := substrb(REPLACE(MSG, '&&', '&'),1,2000);
620
621 /* PUT the message in the cache */
622 MSG_REC.message_name := UPPER(NAMEIN||CHR(0)||LANG_CODE||CHR(0)||APPIN);
623 MSG_REC.message_text := MSG;
624 MSG_REC.message_number := MSG_NUMBER;
625 MSG_REC.type := MSG_TYPE;
626 MSG_REC.fnd_log_severity := FND_LOG_SEVERITY;
627 MSG_REC.category := ALERT_CATEGORY;
628 MSG_REC.severity := ALERT_SEVERITY;
629 PUT(NAMEIN||CHR(0)||LANG_CODE||CHR(0)||APPIN, MSG_REC);
630 end;
631 --------------------------
632 /*
633 ** GET_STRING- get a particular translated message
634 ** from the message dictionary database.
635 **
636 ** This is a one-call interface for when you just want to get a
637 ** message without doing any token substitution.
638 ** Returns NAMEIN (Msg name) if the message cannot be found.
639 */
640 function GET_STRING(APPIN in varchar2,
641 NAMEIN in varchar2) return varchar2 is
642 begin
643 /* get the message text out of the table */
644 return GET_STRING(APPIN, NAMEIN, 'Y');
645 end;
646
647 /*
648 ** GET_STRING- get a particular translated message
649 ** from the message dictionary database.
650 **
651 ** This is a one-call interface for when you just want to get a
652 ** message without doing any token substitution.
653 ** Returns NAMEIN (Msg name) if the message cannot be found.
654 */
655 function GET_STRING(APPIN in varchar2,
656 NAMEIN in varchar2, AUTO_LOG in varchar2) return varchar2 is
657 MSG varchar2(2000) := NULL;
658 MSG_NUMBER NUMBER := 0;
659 MSG_TYPE varchar2(30):= NULL;
660 FND_LOG_SEVERITY NUMBER := 0;
661 ALERT_CATEGORY varchar2(10) := NULL;
662 ALERT_SEVERITY varchar2(10) := NULL;
663 begin
664 /* get the message text out of the table */
665 GET_MESSAGE_INTERNAL(APPIN, NAMEIN, NULL, AUTO_LOG,
666 MSG, MSG_NUMBER, MSG_TYPE, FND_LOG_SEVERITY,
667 ALERT_CATEGORY, ALERT_SEVERITY);
668 return MSG;
669 end;
670
671 /*
672 ** FETCH_SQL_TOKEN- get the value for a SQL Query token
673 ** This procedure is only to be called by the ATG
674 ** not for external use
675 */
676 function FETCH_SQL_TOKEN(TOK_VAL in varchar2) return varchar2 is
677 token_text varchar2(2000);
678 username varchar2(2000);
679 apps_schema_name varchar2(2000);
680 begin
681
682 select user into username from dual;
683
684 select distinct oracle_username into apps_schema_name from fnd_oracle_userid where upper(read_only_flag)='U';
685
686 if(upper(username) <> upper(apps_schema_name) ) then
687 return NULL;
688 end if;
689
690 if ( UPPER(SUBSTR(TOK_VAL, 1, 6) ) = 'SELECT' ) then
691 execute immediate TOK_VAL
695 'Parameter error in FND_MESSAGE.FETCH_SQL_TOKEN(Token SQL): '
692 into token_text;
693 else
694 token_text :=
696 || FND_GLOBAL.NEWLINE
697 || 'TOK_VAL must begin with keyword SELECT';
698 end if;
699 return token_text;
700 exception
701 when others then
702 token_text :=
703 'SQL-Generic error in FND_MESSAGE.FETCH_SQL_TOKEN(Token SQL): '
704 || FND_GLOBAL.NEWLINE
705 || SUBSTR(sqlerrm, 1, 1900);
706 return token_text;
707 end;
708
709 /*
710 ** GET_NUMBER- get the message number of a particular message.
711 **
712 ** This routine returns only the message number, given a message
713 ** name. This routine will be only used in rare cases; normally
714 ** the message name will get displayed automatically by message
715 ** dictionary when outputting a message on the client.
716 **
717 ** You should _not_ use this routine to construct a system for
718 ** storing translated messages (along with numbers) on the server.
719 ** If you need to store translated messages on a server for later
720 ** display on a client, use the set_encoded/get_encoded routines
721 ** to store the messages as untranslated, encoded messages.
722 **
723 ** If you don't know the name of the message on the stack, you
724 ** can use get_encoded and parse_encoded to find it out.
725 **
726 ** Returns 0 if the message has no message number,
727 ** or if its message number is zero.
728 ** NULL if the message can't be found.
729 */
730 function GET_NUMBER(APPIN in varchar2,
731 NAMEIN in varchar2) return NUMBER is
732 MSG varchar2(2000) := NULL;
733 MSG_NUMBER NUMBER := 0;
734 MSG_TYPE varchar2(30):= NULL;
735 FND_LOG_SEVERITY NUMBER := 0;
736 ALERT_CATEGORY varchar2(10) := NULL;
737 ALERT_SEVERITY varchar2(10) := NULL;
738 begin
739 /* get the message text out of the table */
740 GET_MESSAGE_INTERNAL(APPIN, NAMEIN, NULL, 'Y', MSG, MSG_NUMBER,
741 MSG_TYPE, FND_LOG_SEVERITY, ALERT_CATEGORY, ALERT_SEVERITY);
742 return MSG_NUMBER;
743 end;
744
745 /*
746 ** GET- get a translated and token substituted message
747 ** from the message dictionary database.
748 ** Returns NULL if the message cannot be found.
749 */
750 function GET return varchar2 is
751 begin
752 return GET('Y');
753 end;
754
755
756 /*
757 ** GET- get a translated and token substituted message
758 ** from the message dictionary database.
759 ** Returns NULL if the message cannot be found.
760 */
761 function GET(AUTO_LOG in varchar2) return varchar2 is
762 MSG varchar2(2000);
763 TOK_NAM varchar2(30);
764 TOK_VAL varchar2(2000);
765 SRCH varchar2(2000);
766 TTYPE varchar2(1);
767 POS NUMBER;
768 NEXTPOS NUMBER;
769 DATA_SIZE NUMBER;
770 l_pop_msg VARCHAR2(2000);
771
772 begin
773 if (not MSGSET) then
774 MSG := '';
775 return MSG;
776 end if;
777 MSG := GET_STRING(MSGAPP, MSGNAME, AUTO_LOG);
778 if ((msg is NULL) OR (msg = '')) then
779 MSG := MSGNAME;
780 end if;
781 POS := 1;
782 DATA_SIZE := LENGTH(MSGDATA);
783 while POS < DATA_SIZE loop
784 TTYPE := SUBSTR(MSGDATA, POS, 1);
785 POS := POS + 2;
786 /* Note that we are intentionally using chr(0) rather than */
787 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
788 NEXTPOS := INSTR(MSGDATA, chr(0), POS);
789 if (NEXTPOS = 0) then /* For bug 1893617 */
790 exit; /* Should never happen, but prevent spins on bad data*/
791 end if;
792 TOK_NAM := SUBSTR(MSGDATA, POS, NEXTPOS - POS);
793 POS := NEXTPOS + 1;
794 NEXTPOS := INSTR(MSGDATA, chr(0), POS);
795 if (NEXTPOS = 0) then /* For bug 1893617 */
796 exit; /* Should never happen, but prevent spins on bad data*/
797 end if;
798 TOK_VAL := SUBSTR(MSGDATA, POS, NEXTPOS - POS);
799 POS := NEXTPOS + 1;
800
801 if (TTYPE = 'Y') then /* translated token */
802 TOK_VAL := GET_STRING(MSGAPP, TOK_VAL, AUTO_LOG);
803 elsif (TTYPE = 'S') then /* SQL query token */
804 TOK_VAL := FETCH_SQL_TOKEN(TOK_VAL);
805 end if;
806 l_pop_msg := ' ';
807 token_value_replace(MSG, TOK_NAM, TOK_VAL, l_pop_msg);
808
809 -- BUG 6734576 fixed by replacing this line with a simpler
810 -- validation.
811 IF (l_pop_msg <> ' ') THEN
812 MSG := substrb(l_pop_msg, 1, 2000);
813 ELSE
814 -- Bug6779374 complete the error message with
815 -- token-name and token-value
816 MSG := substrb(MSG||' ('||TOK_NAM||'='||TOK_VAL||')',1,2000);
817 END IF;
818
819 END LOOP;
820 /* double ampersands don't have anything to do with tokens, they */
824 MSGSET := FALSE;
821 /* represent access keys. So we translate them to single ampersands*/
822 /* so that the access key code will recognize them. */
823 MSG := substrb(REPLACE(MSG, '&&', '&'),1,2000);
825 return MSG;
826 end;
827
828 function GET_ENCODED(AUTO_LOG in varchar2) return varchar2 is
829 begin
830 if MSGSET then
831 if AUTO_LOG <> 'Y' then
832 MSGSET := FALSE;
833 end if;
834 /* Note that we are intentionally using chr(0) rather than */
835 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
836 return (MSGAPP||chr(0)||MSGNAME||chr(0)||MSGDATA);
837 else
838 return ('');
839 end if;
840 end;
841
842 function GET_ENCODED
843 return varchar2 is
844 begin
845 return GET_ENCODED('N');
846 end;
847
848 /*
849 ** SET_ENCODED- Set an encoded message onto the message stack
850 */
851 procedure SET_ENCODED(ENCODED_MESSAGE IN varchar2) is
852 POS NUMBER;
853 NEXTPOS NUMBER;
854 begin
855 POS := 1;
856
857 /* Note that we are intentionally using chr(0) rather than */
858 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
859 NEXTPOS := INSTR(ENCODED_MESSAGE, chr(0), POS);
860 MSGAPP := SUBSTR(ENCODED_MESSAGE, POS, NEXTPOS - POS);
861 POS := NEXTPOS + 1;
862
863 NEXTPOS := INSTR(ENCODED_MESSAGE, chr(0), POS);
864 MSGNAME := SUBSTR(ENCODED_MESSAGE, POS, NEXTPOS - POS);
865 POS := NEXTPOS + 1;
866
867 MSGDATA := SUBSTR(ENCODED_MESSAGE, POS);
868
869 if((MSGAPP is not null) and (MSGNAME is not null)) then
870 MSGSET := TRUE;
871 end if;
872 end;
873
874
875 /*
876 ** PARSE_ENCODED- Parse the message name and application short name
877 ** out of a message in "encoded" format.
878 */
879 procedure PARSE_ENCODED(ENCODED_MESSAGE IN varchar2,
880 APP_SHORT_NAME OUT NOCOPY varchar2,
881 MESSAGE_NAME OUT NOCOPY varchar2) is
882 POS NUMBER;
883 NEXTPOS NUMBER;
884 begin
885 null;
886 POS := 1;
887
888 /* Note that we are intentionally using chr(0) rather than */
889 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
890 NEXTPOS := INSTR(ENCODED_MESSAGE, chr(0), POS);
891 APP_SHORT_NAME := SUBSTR(ENCODED_MESSAGE, POS, NEXTPOS - POS);
892 POS := NEXTPOS + 1;
893
894 NEXTPOS := INSTR(ENCODED_MESSAGE, chr(0), POS);
895 MESSAGE_NAME := SUBSTR(ENCODED_MESSAGE, POS, NEXTPOS - POS);
896 POS := NEXTPOS + 1;
897 end;
898
899 /*
900 ** GET_TOKEN- Obtains the value of a named token from the
901 ** current message.
902 */
903 function GET_TOKEN(TOKEN IN VARCHAR2
904 ,REMOVE_FROM_MESSAGE IN VARCHAR2 default NULL /* NULL means 'N'*/
905 ) return varchar2 is
906 begin
907 return GET_TOKEN(TOKEN, REMOVE_FROM_MESSAGE, 'Y');
908 end;
909
910 /*
911 ** GET_TOKEN- Obtains the value of a named token from the
912 ** current message.
913 */
914 function GET_TOKEN(TOKEN IN VARCHAR2
915 ,REMOVE_FROM_MESSAGE IN VARCHAR2 default NULL /* NULL means 'N'*/
916 ,AUTO_LOG in varchar2) return varchar2 is
917 TOK_NAM varchar2(30);
918 TOK_VAL varchar2(2000);
919 TTYPE varchar2(1);
920 POS NUMBER;
921 NEXTPOS NUMBER;
922 STARTPOS NUMBER;
923 DATA_SIZE NUMBER;
924 L_REMOVE_FROM_MESSAGE VARCHAR2(1):= NULL;
925 begin
926 if (REMOVE_FROM_MESSAGE is NULL) then
927 L_REMOVE_FROM_MESSAGE := 'N';
928 else
929 L_REMOVE_FROM_MESSAGE := substrb(REMOVE_FROM_MESSAGE,1,1);
930 end if;
931 if (not MSGSET) then
932 return null;
933 end if;
934 POS := 1;
935 DATA_SIZE := LENGTH(MSGDATA);
936 while POS < DATA_SIZE loop
937 STARTPOS := POS;
938 TTYPE := SUBSTR(MSGDATA, POS, 1);
939 POS := POS + 2;
940 /* Note that we are intentionally using chr(0) rather than */
941 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
942 NEXTPOS := INSTR(MSGDATA, chr(0), POS);
943 if (NEXTPOS = 0) then /* For bug 1893617 */
944 exit; /* Should never happen, but prevent spins on bad data*/
945 end if;
946 TOK_NAM := SUBSTR(MSGDATA, POS, NEXTPOS - POS);
947 POS := NEXTPOS + 1;
948 NEXTPOS := INSTR(MSGDATA, chr(0), POS);
949 if (NEXTPOS = 0) then /* For bug 1893617 */
950 exit; /* Should never happen, but prevent spins on bad data*/
951 end if;
952 /* if token matches return value */
953 if (TOK_NAM = TOKEN) then
954 TOK_VAL := SUBSTR(MSGDATA, POS, NEXTPOS - POS);
955
956 if (TTYPE = 'Y') then /* translated token */
957 TOK_VAL := GET_STRING(MSGAPP, TOK_VAL, AUTO_LOG);
958 elsif (TTYPE = 'S') then /* SQL query token */
959 TOK_VAL := FETCH_SQL_TOKEN(TOK_VAL);
960 end if;
961
962 if (L_REMOVE_FROM_MESSAGE = 'Y') then
963 MSGDATA := replace(MSGDATA,SUBSTR(MSGDATA, STARTPOS,
964 NEXTPOS - STARTPOS + 1));
965 end if;
966 return TOK_VAL;
967 end if;
968 POS := NEXTPOS + 1;
969 END LOOP;
970 /* token not found */
971 return null;
972 end;
973
974 /*
975 ** GET_TEXT_NUMBER - get a particular message text
976 ** and message number from the message dictionary database.
977 **
978 ** This is a one-call interface for when you just want to get a
979 ** message and number without doing any token substitution.
980 **
981 ** IN
982 ** APPIN: Application Short Name of message
983 ** NAMEIN: Message Name
984 ** OUT
985 ** MSGTEXT: Returns NAMEIN (Msg name) if the message cannot be found.
986 ** MSGNUMBER: Returns 0 if the message has no message number,
987 ** or if its message number is zero.
988 ** NULL if the message can't be found.
989 */
990 procedure GET_TEXT_NUMBER(APPIN in varchar2,
991 NAMEIN in varchar2,
992 MSGTEXT out nocopy varchar2,
993 MSGNUMBER out nocopy number) is
994 begin
995 MSGTEXT := GET_STRING(APPIN, NAMEIN, 'Y');
996 MSGNUMBER := GET_NUMBER(APPIN, NAMEIN);
997 end;
998
999
1000 end FND_MESSAGE;