1 package body WF_NOTIFICATION_UTIL as
2 /* $Header: wfntfb.pls 120.43.12010000.27 2009/02/26 01:39:33 alepe ship $ */
3
4 -- flag to initialize default NLS values
5 g_wfcore_nls_set boolean := false;
6
7 -- logging variable
8 g_plsqlName varchar2(35) := 'wf.plsql.WF_NOTIFICATION_UTIL.';
9
10 -- <7578908> to handle timestamp in getCalendarDate()
11 g_time_format varchar2(64) := ' HH24:MI:SS';
12
13 -- SetAttrEvent Bug# 2376197
14 -- Set the value of a event notification attribute.
15 -- Attribute must be a EVENT-type attribute.
16 -- IN:
17 -- nid - Notification id
18 -- aname - Attribute Name
19 -- avalue - New value for attribute
20 --
21 procedure SetAttrEvent (nid in number,
22 aname in varchar2,
23 avalue in wf_event_t)
24 is
25 begin
26 if ((nid is null) or (aname is null)) then
27 wf_core.token('NID', to_char(nid));
28 wf_core.token('ANAME', aname);
29 wf_core.raise('WFSQL_ARGS');
30 end if;
31
32 -- Update attribute value
33 update WF_NOTIFICATION_ATTRIBUTES
34 set EVENT_VALUE = avalue
35 where NOTIFICATION_ID = nid and NAME = aname;
36
37 if (SQL%NOTFOUND) then
38 wf_core.token('NID', to_char(nid));
39 wf_core.token('ATTRIBUTE', aname);
40 wf_core.raise('WFNTF_ATTR');
41 end if;
42
43 exception
44 when others then
45 wf_core.context('Wf_Notification_Util', 'SetAttrEvent', to_char(nid), aname);
46 raise;
47 end SetAttrEvent;
48
49 -- GetAttrEvent Bug# 2376197
50 -- Get the value of a event notification attribute.
51 -- Attribute must be a EVENT-type attribute.
52 -- IN:
53 -- nid - Notification id
54 -- aname - Attribute Name
55 -- RETURNS:
56 -- Attribute value
57
58 function GetAttrEvent (nid in number,
59 aname in varchar2)
60 return wf_event_t is
61 lvalue wf_event_t;
62 begin
63 if ((nid is null) or (aname is null)) then
64 wf_core.token('NID', to_char(nid));
65 wf_core.token('ANAME', aname);
66 wf_core.raise('WFSQL_ARGS');
67 end if;
68
69 begin
70 select WNA.EVENT_VALUE
71 into lvalue
72 from WF_NOTIFICATION_ATTRIBUTES WNA
73 where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
74 exception
75 when no_data_found then
76 wf_core.token('NID', to_char(nid));
77 wf_core.token('ATTRIBUTE', aname);
78 wf_core.raise('WFNTF_ATTR');
79 end;
80 return(lvalue);
81 exception
82 when others then
83 wf_core.context('Wf_Notification_Util', 'GetAttrEvent', to_char(nid), aname);
84 raise;
85 end GetAttrEvent;
86
87
88 -- denormalize_rf
89 -- Rule function to denormalize a notificaion
90 --
91 FUNCTION denormalize_rf(p_subscription_guid in raw,
92 p_event in out nocopy wf_event_t)
93 return varchar2
94 is
95 l_nid number;
96 l_language varchar2(64);
97 l_territory varchar2(64);
98 l_subject varchar2(2000);
99 l_orig_lang varchar2(64);
100 l_orig_terr varchar2(64);
101 l_orig_chrs varchar2(64);
102 l_nls_date_format varchar2(64);
103 l_nls_date_language varchar2(64);
104 l_nls_calendar varchar2(64);
105 l_nls_numeric_characters varchar2(64);
106 l_nls_sort varchar2(64);
107 l_nls_currency varchar2(64);
108 l_orig_date_format varchar2(64);
109 l_orig_date_language varchar2(64);
110 l_orig_calendar varchar2(64);
111 l_orig_numeric_characters varchar2(64);
112 l_orig_sort varchar2(64);
113 l_orig_currency varchar2(64);
114 l_api varchar2(50):='wf.plsql.wf_notification_util.denormalize_rf';
115
116 begin
117 if (wf_log_pkg.level_procedure>= fnd_log.g_current_runtime_level) then
118 wf_log_pkg.string(wf_log_pkg.level_procedure,l_api,'BEGIN, p_subscription_guid='||p_subscription_guid);
119 end if;
120 l_nid := p_event.GetValueForParameter('NOTIFICATION_ID');
121 l_language := p_event.GetValueForParameter('LANGUAGE');
122 l_territory := p_event.GetValueForParameter('TERRITORY');
123
124 -- <7514495>
125 l_nls_date_format:=p_event.GetValueForParameter('NLS_DATE_FORMAT');
126 l_nls_date_language:=p_event.GetValueForParameter('NLS_DATE_LANGUAGE');
127 l_nls_calendar:=p_event.GetValueForParameter('NLS_CALENDAR');
128 l_nls_numeric_characters:= p_event.GetValueForParameter('NLS_NUMERIC_CHARACTERS');
129 l_nls_sort := p_event.GetValueForParameter('NLS_SORT');
130 l_nls_currency:= p_event.GetValueForParameter('NLS_CURRENCY'); -- </7514495>
131
132 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
133 wf_log_pkg.string(wf_log_pkg.level_statement,l_api,'Nid: '||l_nid);
134 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'Lang: '||l_language||' Territory: '||l_territory);
135 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,
136 'nls_date_format: '||l_nls_date_format||', nls_date_language: '||l_nls_date_language);
137 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,
138 'nls_calendar: '||l_nls_calendar||', nls_numeric_characters: '||l_nls_numeric_characters);
139 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,
140 'nls_sort: '||l_nls_sort||', nls_currency: '||l_nls_currency);
141 end if;
142 -- <7514495>
143 -- Wf_Notification.GetNLSLanguage(l_orig_lang,l_orig_terr,l_orig_chrs); commenting out
144 wf_notification_util.getNLSContext(p_nlsLanguage=>l_orig_lang, p_nlsTerritory =>l_orig_terr, p_nlsCode => l_orig_chrs,
145 p_nlsDateFormat =>l_orig_date_format, p_nlsDateLanguage =>l_orig_date_language,
146 p_nlsNumericCharacters =>l_orig_numeric_characters, p_nlsSort =>l_orig_sort,
147 p_nlsCalendar =>l_orig_calendar);
148
149 -- <7514495>
150 -- Wf_Notification.SetNLSLanguage(l_language,l_territory); commenting out
151 wf_notification_util.SetNLSContext(p_nid=> l_nid, p_nlsLanguage =>l_language, p_nlsTerritory =>l_territory,
152 p_nlsDateFormat =>l_nls_date_format, p_nlsDateLanguage => l_nls_date_language,
153 p_nlsNumericCharacters => l_nls_numeric_characters, p_nlsSort =>l_nls_sort,
154 p_nlsCalendar => l_nls_calendar);
155
156 g_allowDeferDenormalize:=false;
157 Wf_Notification.Denormalize_Notification(l_nid);
158
159 -- reset the existing session language
160 -- WF_Notification.SetNLSLanguage(l_orig_lang,l_orig_terr); <7514495> commenting out
161 wf_notification_util.SetNLSContext(p_nlsLanguage =>l_orig_lang, p_nlsTerritory =>l_orig_terr,
162 p_nlsDateFormat =>l_orig_date_format, p_nlsDateLanguage => l_orig_date_language,
163 p_nlsNumericCharacters => l_orig_numeric_characters, p_nlsSort =>l_orig_sort,
164 p_nlsCalendar => l_orig_calendar);
165
166 if (wf_log_pkg.level_procedure>= fnd_log.g_current_runtime_level) then
167 wf_log_pkg.string(wf_log_pkg.level_procedure,l_api,'END');
168 end if;
169
170 return 'SUCCESS';
171 exception
172 when others then
173 wf_core.context('Wf_Notification_util', 'Denormalize_Rf', to_char(l_nid));
174 wf_event.setErrorInfo(p_event, 'ERROR');
175 return 'ERROR';
176 end denormalize_rf;
177
178 function CheckIllegalChar(bindparam in varchar2,
179 raise_error in boolean)
180 return boolean
181 is
182 begin
183
184
185 --Check if the in parameter contains any of the illegal characters
186 --'(' , ')' or ';' which could supposedly lead to sql injection
187 --when binding.
188 if ((instr(bindparam,'(') > 0) OR (instr(bindparam,';') > 0)
189 OR (instr(bindparam,')')>0)) then
190 if (raise_error IS NULL or raise_error) then
191 --raise error
192 wf_core.token('PARAM',bindparam);
193 wf_core.raise('WFNTF_ILLEGAL_CHAR');
194
195 else
196 --If u just want to check for illegal characters
197 --and not raise any exception
198 return false;
199 end if;
200 else
201 return true;
202 end if;
203 end CheckIllegalChar;
204
205 -- getNLSContext
206 -- get the NLS session parameters from USER ENV.
207 --
208 -- OUT:
209 -- p_nlsLanguage : a varchar2 of the NLS_LANGUAGE
210 -- p_nlsTerritory : a varchar2 of the NLS_TERRITORY
211 -- p_nlsDateFormat : a varchar2 of the NLS_DATE_FORMAT
212 -- p_nlsDateLanguage : a varchar2 of the NLS_DATE_LANGUAGE
213 -- p_nlsCalendar : -- not will be used as of now but for future
214 -- p_nlsNumericCharacters : a varchar2 of the nls numeric characters
215 -- p_nlsSort : a varchar2 of the NLS_SORT
216 --
217 procedure getNLSContext( p_nlsLanguage out NOCOPY varchar2,
218 p_nlsTerritory out NOCOPY varchar2,
219 p_nlsCode OUT NOCOPY varchar2,
220 p_nlsDateFormat out NOCOPY varchar2,
221 p_nlsDateLanguage out NOCOPY varchar2,
222 p_nlsNumericCharacters out NOCOPY varchar2,
223 p_nlsSort out NOCOPY varchar2,
224 p_nlsCalendar out NOCOPY varchar2)
225 is
226 l_envLangTerritory varchar2(240);
227 l_uPos number; -- position for '_'
228 l_dotPos number; -- position for '.'
229 l_api varchar2(50):='wf.plsql.wf_notification_util.getNLSContext';
230
231 begin
232 -- ensure that wf_core NLS defaults are in fact set
233 if (g_wfcore_nls_set = false) then
234 wf_core.initializeNLSDefaults;
235 g_wfcore_nls_set := true;
236 end if;
237
238 -- Get NLS param from USERENV namespace or query v$nls_parameter
239 l_envLangTerritory := SYS_CONTEXT('USERENV', 'LANGUAGE');
240
241 l_uPos := instr(l_envLangTerritory, '_');
242 l_dotPos := instr(l_envLangTerritory, '.');
243
244 p_nlsLanguage := substr(l_envLangTerritory, 1, l_uPos-1);
245 p_nlsTerritory := substr(l_envLangTerritory, l_uPos + 1, l_dotPos - l_uPos -1);
246
247 -- Code being used by Mailer at java layer
248 p_nlsCode := substr(l_envLangTerritory, l_dotPos + 1);
249
250 p_nlsDateFormat := SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT');
251 p_nlsDateLanguage := SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE');
252 p_nlsSort := SYS_CONTEXT('USERENV', 'NLS_SORT');
253
254 -- NLS_NUMERIC_CHARACTERS is not available in USERENV namespace.
255 SELECT value INTO p_nlsNumericCharacters
256 from v$nls_parameters
257 where parameter = 'NLS_NUMERIC_CHARACTERS';
258
259 p_nlsCalendar := GetCurrentCalendar;
260
261 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
262 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'Returning following values');
263 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsLanguage: '||p_nlsLanguage||', p_nlsTerritory: '||p_nlsTerritory);
264 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsCode: '||p_nlsCode||', p_nlsDateFormat: '||p_nlsDateFormat);
265 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsDateLanguage: '||p_nlsDateLanguage||', p_nlsSort: '||p_nlsSort);
266 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsNumericCharacters: '||p_nlsNumericCharacters||', p_nlsCalendar: '||p_nlsCalendar);
267 end if;
268 exception
269 WHEN OTHERS THEN
270 raise;
271 END getNLSContext;
272
273
274 --
275 -- SetNLSContext the NLS parameters like lang and territory of the current session
276 --
277 -- IN
278 -- p_nid - a number of the notification id ( this only be used to store value
279 -- in global session variable.)
280 -- p_nlsLanguage - a varchar2 of the language code
281 -- p_nlsTerritory - a varchar2 of the territory code.
282 -- p_nlsDateFormat - a varchar2 of the nls_date_format
283 -- p_nlsDateLanguage - a varchar2 of the nls_date_language
284 -- p_nlsNumericCharacters - a varchar2 of the nls numeric characters
285 -- p_nlsSort - a varchar2 of the NLS_SORT
286 -- p_nlsCalendar - a varchar2 of the nls_calendar
287 -- (only be used to store value in global session variable).
288 --
289 procedure SetNLSContext(p_nid IN NUMBER DEFAULT null,
290 p_nlsLanguage in VARCHAR2 default null,
291 p_nlsTerritory in VARCHAR2 default null,
292 p_nlsDateFormat in VARCHAR2 default null,
293 p_nlsDateLanguage in VARCHAR2 default null,
294 p_nlsNumericCharacters in VARCHAR2 default null,
295 p_nlsSort in VARCHAR2 default null,
296 p_nlsCalendar in VARCHAR2 default null)
297 is
298
299 -- dependes on NLS_LANGUAGE
300 l_nlsDateLanguage v$nls_parameters.value%TYPE := upper(p_nlsDateLanguage);
301 l_api varchar2(50):='wf.plsql.wf_notification_util.SetNLSContext';
302
303 begin
304 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
305 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'Parameters passed');
306 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsLanguage: '||p_nlsLanguage||', p_nlsTerritory: '||p_nlsTerritory);
307 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nid: '||p_nid||', p_nlsDateFormat: '||p_nlsDateFormat);
308 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsDateLanguage: '||p_nlsDateLanguage||', p_nlsSort: '||p_nlsSort);
309 wf_log_pkg.string(wf_log_pkg.level_statement, l_api,'p_nlsNumericCharacters: '||p_nlsNumericCharacters||', p_nlsCalendar: '||p_nlsCalendar);
310 end if;
311 -- As nls_date_language may come as lang. code e.g. US, KO
312 if( (p_nlsDateLanguage IS NOT NULL) ) then
313 if(isLanguageInstalled(p_nlsDateLanguage) = FALSE ) then
314 l_nlsDateLanguage := p_nlsLanguage;
315 end if;
316 end IF;
317
318 -- Any one of them is not null
319 if( p_nlsLanguage IS NOT NULL or
320 l_nlsDateLanguage IS NOT NULL or
321 p_nlsSort IS NOT NULL or
322 p_nlsTerritory IS NOT NULL or
323 p_nlsDateFormat IS NOT NULL or
324 p_nlsNumericCharacters IS NOT NULL ) THEN
325
326 -- set context.
327 fnd_global.set_nls_context(p_nlsLanguage ,
328 p_nlsDateFormat,
329 l_nlsDateLanguage ,
330 p_nlsNumericCharacters,
331 p_nlsSort ,
332 p_nlsTerritory ) ;
333
334 -- set global variables
335 g_nls_language := p_nlsLanguage;
336 g_nls_territory := p_nlsTerritory;
337 g_nls_date_format := p_nlsDateFormat;
338 g_nls_Date_Language := l_nlsDateLanguage;
339 g_nls_Sort := p_nlsSort;
340 g_nls_Numeric_Characters := p_nlsNumericCharacters;
341
342 END if;
343
344 -- set Calendar value in global variable.
345 setCurrentCalendar (p_nlsCalendar);
346
347 SetCurrentNID(p_nid);
348
349 exception
350 when others then
351 wf_core.context('Wf_Notification_Util', 'SetNLSContext',
352 p_nid,
353 p_nlsLanguage ,
354 p_nlsTerritory ,
355 p_nlsDateFormat ,
356 p_nlsDateLanguage ,
357 p_nlsCalendar ,
358 p_nlsNumericCharacters ,
359 p_nlsSort );
360
361
362 -- WE May set default NLS settings here but what if partial NLS setting has
363 -- been done and fnd_global.set_nls_context failed in middle for a session?? .
364 -- Better to raise to caller so that source type ERROR
365 -- event would be raised.
366 --
367 raise;
368
369 END SetNLSContext;
370
371
372
373 --
374 --
375 -- setCurrentCalendar :
376 -- Sets NLS_CALENDAR parameter's value in global variables for fast accessing.
377 -- as this parameters may NOT be altered for a SESSION( Per IPG team : Database stores
378 -- all dates in Gregorian calendar)
379 --
380 -- in
381 -- p_nlsCalendar : varchar2
382 --
383 PROCEDURE setCurrentCalendar( p_nlsCalendar in varchar2)
384 is
385 begin
386 g_nls_Calendar := p_nlsCalendar;
387
388 END setCurrentCalendar;
389
390
391 --
392 --
393 -- getGlobalCalendar :
394 -- Gets NLS_CALENDAR parameter's value from global variables
395 --
396 --
397 --
398 FUNCTION GetCurrentCalendar RETURN varchar2
399 is
400 begin
401
402 RETURN g_nls_Calendar;
403
404 END GetCurrentCalendar;
405
406 --
407 --
408 -- setCurrentNID :
409 -- Sets notification id parameter's value from global variables for fast accessing
410 --
411 -- IN
412 -- p_nid - A number for notification id
413 --
414 PROCEDURE setCurrentNID( p_nid in number)
415 is
416 begin
417 g_nid:= p_nid;
418
419 END setCurrentNID;
420
421 --
422 --
423 -- getCurrentNID :
424 -- Gets NLS_CALENDAR parameter's value from global variables for fast accessing
425 --
426 -- OUT
427 -- p_nlsCalendar
428 FUNCTION getCurrentNID RETURN number
429 is
430 begin
431 RETURN g_nid;
432 END getCurrentNID;
433
434 -- isLanguageInstalled
435 -- Checks if language is installed or not by querying on WF_LANGUAGE view.
436 -- IN
437 -- p_language : varchar2 The language to be checked.
438 -- RETURN
439 -- true if installed otherwise false
440 --
441 FUNCTION isLanguageInstalled( p_language IN VARCHAR2 DEFAULT null) RETURN boolean
442 is
443 l_installed_flag wf_languages.installed_flag%TYPE;
444 begin
445
446 select installed_flag
447 into l_installed_flag
448 from wf_languages
449 where nls_language = p_language
450 and installed_flag = 'Y';
451
452 -- if here, it means language installed
453 RETURN true;
454 exception
455 WHEN OTHERS then
456 -- Language is not installed.
457 RETURN false;
458
459 END isLanguageInstalled;
460
461
462 function GetCalendarDate(p_nid number, p_date in date, p_date_format in varchar2, p_addTime in boolean) return varchar2
463 is
464 l_date_str varchar2(100);
465 l_date_format varchar2(100);
466 l_nls_calendar varchar2(100);
467 l_parTb wf_directory.wf_local_roles_tbl_type;
468 l_user varchar2(320);
469 p_mod varchar2(100):=g_plsqlName|| 'GetCalendarDate()';
470 l_logPROC boolean;
471 l_logSTMT boolean;
472 l_timeFormat varchar2(64):=null;
473 l_err varchar2(3500);
474 l_errCode varchar2(30);
475 e_wrongNLSparam exception;
476 e_unrecognizedFormat exception;
477 e_numOrValueError exception;
478
479 pragma exception_init(e_wrongNLSparam, -12702);
480 pragma exception_init(e_unrecognizedFormat, -1821);
481 pragma exception_init(e_numOrValueError, -06502);
482
483
484 begin
485 l_logSTMT := WF_LOG_PKG.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level;
486 l_logPROC := l_logSTMT or (WF_LOG_PKG.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level);
487
488 if ( l_logPROC ) then
489 wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, p_mod, 'BEGIN');
490 end if;
491
492 -- if the call is from mailer, use the preferences from global variables
493 if (wf_notification_util.G_NID > 0 and wf_notification_util.G_NID = p_nid) then
494 l_date_format := wf_notification_util.G_NLS_DATE_FORMAT;
495 l_nls_calendar := wf_notification_util.G_NLS_CALENDAR;
496 else
497 l_user := wfa_sec.GetUser();
498 if (l_user is null) then
499 -- use default values
500 l_date_format := wf_core.nls_date_format;
501 l_nls_calendar := wf_core.nls_calendar;
502 else
503 wf_directory.GetRoleInfo2(Role => l_user, Role_Info_Tbl => l_parTb);
504 l_date_format := l_parTb(1).NLS_DATE_FORMAT;
505 l_nls_calendar := l_parTb(1).NLS_CALENDAR;
506 end if;
507
508 end if;
509
510 if ( l_logSTMT ) then
511 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'before conversion, p_date_format: '||p_date_format);
512 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'l_date_format (before adding tstamp): '||l_date_format);
513 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'l_nls_calendar: '||l_nls_calendar);
514 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'p_date: '||to_char(p_date,'DD-MON-RR HH24:MI:SS'));
515 end if;
516
517 l_date_format := nvl(p_date_format, l_date_format);
518 if (p_addTime) and (instr(upper(l_date_format), 'HH') = 0) then
519 l_date_format := l_date_format|| g_time_format;
520
521 if ( l_logSTMT ) then
522 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
523 , 'l_date_format (after adding tstamp): '||l_date_format);
524 end if;
525 end if;
526
527 if ( l_logSTMT ) then
528 if (l_nls_calendar is not null) then
529 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
530 , 'returning to_char(to_date('''||to_char(p_date,'DD-MON-RRRR HH24:MI:SS')||
531 ''',''DD-MON-RRRR HH24:MI:SS''), '''||l_date_format||''', ''NLS_CALENDAR = '''''||
532 l_nls_calendar||''''' ''); ');
533 else
534 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
535 , 'returning to_char(to_date('''||to_char(p_date,'DD-MON-RRRR HH24:MI:SS')||
536 ''',''DD-MON-RRRR HH24:MI:SS''), '''||l_date_format||''' );');
537 end if;
538 end if;
539
540 if (l_logPROC) then
541 wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, p_mod, 'END');
542 end if;
543
544 if (l_nls_calendar is not null) then
545 return to_char(p_date, l_date_format, 'NLS_CALENDAR = '''||l_nls_calendar||'''');
546 else
547 return to_char(p_date, l_date_format);
548 end if;
549
550 exception
551 when e_wrongNLSparam then
552 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
553 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION ora-12702!!');
554 end if;
555 return to_char(p_date, l_date_format);
556 when e_unrecognizedFormat then
557 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
558 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION ora-1821!!');
559 end if;
560 return to_char(p_date, null, 'NLS_CALENDAR = '''||l_nls_calendar||'''');
561 when e_numOrValueError then
562 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
563 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION ora-06502!!');
564 end if;
565 return to_char(p_date);
566 when others then
567 l_errCode := SQLCODE;
568 l_err := substr(sqlerrm,1,3500);
569 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
570 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION '||l_errCode ||' - '|| l_err );
571 end if;
572 return to_char(p_date);
573 end GetCalendarDate;
574
575 -- package body end.
576 end WF_NOTIFICATION_UTIL;
577
578
579
[Home] [Help]