1 PACKAGE BODY hr_bpl_message AS
2 /* $Header: perbamsg.pkb 120.0 2005/05/31 16:40:26 appldev noship $ */
3
4 MSGNAME varchar2(30);
5 MSGDATA varchar2(32000);
6 MSGSET boolean := FALSE;
7 MSGAPP varchar2(50);
8 g_mes_lng varchar2(10);
9
10 /*----------------------------------------------------*/
11 /* Updated versions of the SET_NAME procedure which */
12 /* which allow the settng of the translating lang */
13 /*----------------------------------------------------*/
14
15
16 procedure SET_NAME_PSN(APPLICATION in varchar2,
17 NAME in varchar2,
18 p_person_id in number) is
19 begin
20 MSGAPP := APPLICATION;
21 MSGNAME := NAME;
22 MSGDATA := '';
23 MSGSET := TRUE;
24 /* SET LANG*/
25 g_mes_lng := hr_bpl_alert_recipient.get_psn_lng(p_person_id);
26
27 end;
28
29 procedure SET_NAME_SUP(APPLICATION in varchar2,
30 NAME in varchar2,
31 p_assignment_id in number) is
32 begin
33 MSGAPP := APPLICATION;
34 MSGNAME := NAME;
35 MSGDATA := '';
36 MSGSET := TRUE;
37 /* SET LANG*/
38 g_mes_lng := hr_bpl_alert_recipient.Get_asg_sup_lng(p_assignment_id);
39
40 end;
41
42 procedure SET_NAME_PSUP(APPLICATION in varchar2,
43 NAME in varchar2,
44 p_assignment_id in number) is
45 begin
46 MSGAPP := APPLICATION;
47 MSGNAME := NAME;
48 MSGDATA := '';
49 MSGSET := TRUE;
50 /* SET LANG*/
51 g_mes_lng := hr_bpl_alert_recipient.Get_pasg_sup_lng(p_assignment_id);
52
53 end;
54
55 procedure SET_NAME_PSN_PSUP(APPLICATION in varchar2,
56 NAME in varchar2,
57 p_person_id in number) is
58 begin
59 MSGAPP := APPLICATION;
60 MSGNAME := NAME;
61 MSGDATA := '';
62 MSGSET := TRUE;
63 /* SET LANG*/
64 g_mes_lng := hr_bpl_alert_recipient.get_psn_lng(p_person_id);
65
66 end;
67
68 procedure SET_NAME(APPLICATION in varchar2,
69 NAME in varchar2,
70 p_business_group_id in number) is
71 begin
72 MSGAPP := APPLICATION;
73 MSGNAME := NAME;
74 MSGDATA := '';
75 MSGSET := TRUE;
76 /* SET LANG*/
77 g_mes_lng := hr_bpl_alert_recipient.get_bg_lng(p_business_group_id);
78 end;
79
80
81 /*----------------------------------------------------*/
82 /* Updated version of the GET_STRING function, which */
83 /* allows the passing of a translation langauge */
84 /* */
85 /*----------------------------------------------------*/
86
87
88 function GET_STRING_LNG_BG(APPIN in varchar2,
89 NAMEIN in varchar2)
90 return varchar2 is MSG varchar2(2000) := NULL;
91
92 cursor c1(NAME_ARG varchar2) is
93 select message_text
94 from fnd_new_messages m, fnd_application a
95 where NAME_ARG = m.message_name
96 and m.language_code = g_mes_lng
97 and APPIN = a.application_short_name
98 and m.application_id = a.application_id;
99 cursor c2(NAME_ARG varchar2) is
100 select message_text
101 from fnd_new_messages m, fnd_application a
102 where NAME_ARG = m.message_name
103 and 'US' = m.language_code
104 and APPIN = a.application_short_name
105 and m.application_id = a.application_id;
106 begin
107 /* get the message text out of the table */
108 open c1(UPPER(NAMEIN));
109 fetch c1 into MSG;
110 if (c1%NOTFOUND) then
111 open c2(UPPER(NAMEIN));
112 fetch c2 into MSG;
113 if (c2%NOTFOUND) then
114 MSG := NAMEIN;
115 end if;
116 close c2;
117 end if;
118 close c1;
119 /* double ampersands don't have anything to do with tokens, they */
120 /* represent access keys. So we translate them to single ampersands*/
121 /* so that the access key code will recognize them. */
122 MSG := substrb(REPLACE(MSG, '&&', '&'),1,2000);
123 return MSG;
124 end;
125
126 /*----------------------------------------------------*/
127 /* Updated version of the GET function, which allows */
128 /* the passing of a translation langauge */
129 /*----------------------------------------------------*/
130
131 function GET return varchar2 is
132 MSG varchar2(2000);
133 TOK_NAM varchar2(30);
134 TOK_VAL varchar2(2000);
135 SRCH varchar2(2000);
136 TTYPE varchar2(1);
137 POS NUMBER;
138 NEXTPOS NUMBER;
139 DATA_SIZE NUMBER;
140 begin
141 if (not MSGSET) then
142 MSG := '';
143 return MSG;
144 end if;
145 MSG := GET_STRING_LNG_BG(MSGAPP, MSGNAME);
146 if ((msg is NULL) OR (msg = '')) then
147 MSG := MSGNAME;
148 end if;
149 POS := 1;
150 DATA_SIZE := LENGTH(MSGDATA);
151 while POS < DATA_SIZE loop
152 TTYPE := SUBSTR(MSGDATA, POS, 1);
153 POS := POS + 2;
154 /* Note that we are intentionally using chr(0) rather than */
155 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
156 NEXTPOS := INSTR(MSGDATA, chr(0), POS);
157 if (NEXTPOS = 0) then /* For bug 1893617 */
158 exit; /* Should never happen, but prevent spins on bad data*/
159 end if;
160 TOK_NAM := SUBSTR(MSGDATA, POS, NEXTPOS - POS);
161 POS := NEXTPOS + 1;
162 NEXTPOS := INSTR(MSGDATA, chr(0), POS);
163 if (NEXTPOS = 0) then /* For bug 1893617 */
164 exit; /* Should never happen, but prevent spins on bad data*/
165 end if;
166 TOK_VAL := SUBSTR(MSGDATA, POS, NEXTPOS - POS);
167 POS := NEXTPOS + 1;
168
169 if (TTYPE = 'Y') then /* translated token */
170 TOK_VAL := GET_STRING_LNG_BG(MSGAPP, TOK_VAL); ---------------
171 elsif (TTYPE = 'S') then /* SQL query token */
172 TOK_VAL := FETCH_SQL_TOKEN(TOK_VAL);
173 end if;
174 SRCH := '&' || TOK_NAM;
175 if (INSTR(MSG, SRCH) <> 0) then
176 MSG := substrb(REPLACE(MSG, SRCH, TOK_VAL),1,2000);
177 else
178 /* try the uppercased version of the token name in case */
179 /* the caller is (wrongly) passing a mixed case token name */
180 /* Because now (July 99) all tokens in msg text should be */
181 /* uppercase. */
182 SRCH := '&' || UPPER(TOK_NAM);
183 if (INSTR(MSG, SRCH) <> 0) then
184 MSG := substrb(REPLACE(MSG, SRCH, TOK_VAL),1,2000);
185 else
186 MSG :=substrb(MSG||' ('||TOK_NAM||'='||TOK_VAL||')',1,2000);
187 end if;
188 end if;
189 END LOOP;
190 /* double ampersands don't have anything to do with tokens, they */
191 /* represent access keys. So we translate them to single ampersands*/
192 /* so that the access key code will recognize them. */
193 MSG := substrb(REPLACE(MSG, '&&', '&'),1,2000);
194 MSGSET := FALSE;
195 return MSG;
196 end;
197
198
199 /* --------------------------------------------------*/
200 /* Required unchanged FND_MESSAGE Functions */
201 /* SET_NAME PROCEDURE HAS BEEN UPDATEED */
202 /*---------------------------------------------------*/
203 /* */
204 /* FETCH_SQL_TOKEN- get the value for a SQL Query token */
205 /* ** This procedure is only to be called by the ATG */
206 /* ** not for external use */
207
208 function FETCH_SQL_TOKEN(TOK_VAL in varchar2) return varchar2 is
209 token_text varchar2(2000);
210 begin
211 if ( UPPER(SUBSTR(TOK_VAL, 1, 6) ) = 'SELECT' ) then
212 execute immediate TOK_VAL
213 into token_text;
214 else
215 token_text :=
216 'Parameter error in FND_MESSAGE.FETCH_SQL_TOKEN(Token SQL): '
217 || FND_GLOBAL.NEWLINE
218 || 'TOK_VAL must begin with keyword SELECT';
219 end if;
220 return token_text;
221 exception
222 when others then
223 token_text :=
224 'SQL-Generic error in FND_MESSAGE.FETCH_SQL_TOKEN(Token SQL): '
225 || FND_GLOBAL.NEWLINE
226 || SUBSTR(sqlerrm, 1, 1900);
227 return token_text;
228 end;
229
230 /*
231 ** ### OVERLOADED (new private version) ###
232 **
233 ** SET_TOKEN - define a message token with a value
234 ** Private: This procedure is only to be called by the ATG
235 ** not for external use
236 ** Arguments:
237 ** token - message token
238 ** value - value to substitute for token
239 ** ttype - type of token substitution:
240 ** 'Y' translated, or "Yes, translated"
241 ** 'N' constant, or "No, not translated"
242 ** 'S' SQL query
243 **
244 */
245 procedure SET_TOKEN(TOKEN in varchar2,
246 VALUE in varchar2,
247 TTYPE in varchar2 default 'N') is
248 tok_type varchar2(1);
249 begin
250
251 if ( TTYPE not in ('Y','N','S')) then
252 tok_type := 'N';
253 else
254 tok_type := TTYPE;
255 end if;
256
257 /* Note that we are intentionally using chr(0) rather than */
258 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
259 MSGDATA := MSGDATA||tok_type||chr(0)||TOKEN||chr(0)||VALUE||chr(0);
260
261 end set_token;
262
263 /* */
264 /* ### OVERLOADED (original version) ### */
265 /* */
266 /* SET_TOKEN - define a message token with a value,*/
267 /* either constant or translated */
268 /* Public: This procedure to be used by all */
269 /* */
270 procedure SET_TOKEN(TOKEN in varchar2,
271 VALUE in varchar2,
272 TRANSLATE in boolean default false) is
273 TTYPE varchar2(1);
274 begin
275 if TRANSLATE then
276 TTYPE := 'Y';
277 else
278 TTYPE := 'N';
279 end if;
280
281 SET_TOKEN(TOKEN, VALUE, TTYPE);
282
283 end set_token;
284
285 /* */
286 /* SET_TOKEN_SQL - define a message token with a SQL query value */
287 /* */
288 /* Description: */
289 /* Like SET_TOKEN, except here the value is a SQL statement which */
290 /* returns a single varchar2 value. (e.g. A translated concurrent */
291 /* manager name.) This statement is run when the message text is */
292 /* resolved, and the result is used in the token substitution. */
293 /* */
294 /* Arguments: */
295 /* token - Token name */
296 /* value - Token value. A SQL statement */
297 /* */
298
299 procedure SET_TOKEN_SQL (TOKEN in varchar2,
300 VALUE in varchar2) is
301
302 TTYPE varchar2(1) := 'S'; -- SQL Query
303 begin
304
305 SET_TOKEN(TOKEN, VALUE, TTYPE );
306
307 end set_token_sql;
308
309 /* This procedure is only to be called by the ATG; */
310 /* not for external use */
311 procedure RETRIEVE(MSGOUT out NOCOPY varchar2) is
312 OUT_VAL varchar2(2000);
313 begin
314 if MSGSET then
315 /* Note that we are intentionally using chr(0) rather than */
316 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
317 OUT_VAL := MSGAPP||chr(0)||MSGNAME||chr(0)||MSGDATA;
318 MSGSET := FALSE;
319 else
320 OUT_VAL := '';
321 end if;
322
323 MSGOUT := OUT_VAL;
324 end;
325
326 procedure CLEAR is
327 begin
328 msgset := FALSE;
329 end;
330
331 procedure RAISE_ERROR is
332 begin
333 /* Note that we are intentionally using chr(0) rather than */
334 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
335 raise_application_error(-20001,
336 MSGNAME||': '||replace(rtrim(MSGDATA,chr(0)),
337 chr(0), ', '));
338 end;
339
340
341
342 end hr_bpl_message;