DBA Data[Home] [Help]

PACKAGE BODY: APPS.FND_MESSAGE

Source


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;