1 package body WF_NOTIFICATION_UTIL as
2 /* $Header: wfntfb.pls 120.113.12020000.8 2013/03/20 04:46:53 alsosa 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 l_nls_language varchar2(100);
480 l_nls_territory varchar2(100);
481
482 l_ret_value varchar2(127) := null;
483
484 pragma exception_init(e_wrongNLSparam, -12702);
485 pragma exception_init(e_unrecognizedFormat, -1821);
486 pragma exception_init(e_numOrValueError, -06502);
487
488
489 begin
490 l_logSTMT := WF_LOG_PKG.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level;
491 l_logPROC := l_logSTMT or (WF_LOG_PKG.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level);
492
493 if ( l_logPROC ) then
494 wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, p_mod, 'BEGIN');
495 end if;
496
497 -- if the call is from mailer, use the preferences from global variables
498 if (wf_notification_util.G_NID > 0 and wf_notification_util.G_NID = p_nid) then
499 l_date_format := wf_notification_util.G_NLS_DATE_FORMAT;
500 l_nls_calendar := wf_notification_util.G_NLS_CALENDAR;
501 -- <<bug 8430385 >>
502 l_nls_language := wf_notification_util.G_NLS_LANGUAGE;
503 l_nls_territory := wf_notification_util.G_NLS_TERRITORY;
504
505 else
506 l_user := wfa_sec.GetUser();
507 if (l_user is null) then
508 -- use default values
509 l_date_format := wf_core.nls_date_format;
510 l_nls_calendar := wf_core.nls_calendar;
511
512 l_nls_language := wf_core.nls_language;
513 l_nls_territory := wf_core.nls_territory;
514
515 else
516
517 wf_directory.GetRoleInfo2(Role => l_user, Role_Info_Tbl => l_parTb);
518
519 l_date_format := l_parTb(1).NLS_DATE_FORMAT;
520 l_nls_calendar := l_parTb(1).NLS_CALENDAR;
521
522 l_nls_language := l_parTb(1).LANGUAGE;
523 l_nls_territory := l_parTb(1).TERRITORY;
524 end if;
525
526 end if;
527
528 if ( l_logSTMT ) then
529 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'before conversion, p_date_format: '||p_date_format);
530 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'l_date_format (before adding tstamp): '||l_date_format);
531 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'l_nls_calendar: '||l_nls_calendar);
532 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod, 'p_date: '||to_char(p_date,'DD-MON-RR HH24:MI:SS'));
533 end if;
534
535 l_date_format := nvl(p_date_format, l_date_format);
536 if (p_addTime) and (instr(upper(l_date_format), 'HH') = 0) then
537 l_date_format := l_date_format|| g_time_format;
538
539 if ( l_logSTMT ) then
540 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
541 , 'l_date_format (after adding tstamp): '||l_date_format);
542 end if;
543 end if;
544
545 --if (l_nls_calendar is not null) then
546 -- return to_char(p_date, l_date_format, 'NLS_CALENDAR = '''||l_nls_calendar||'''');
547 --else
548 -- return to_char(p_date, l_date_format);
549 --end if;
550
551 -- <<sstomar>>: bug 8596153
552 --
553 -- NON-Gregorion calendar
554 -- CASE1: use NLS_CALENDAR
555 if (l_nls_calendar is not null and
556 upper(l_nls_calendar) <> 'GREGORIAN' ) then
557
558 -- Bug 8872332: replacing the 'MON' string in date format with 'FMMON' to remove
559 -- trailing and leading spaces
560 -- Bug 9096378: changed the replacing string from 'FMMON' to 'FMMONFM' to disable 'FM' modifier
561 -- effects (removing the leading zeros) for the portion following its second occurence.
562 -- A FM modifier can appear in a format model more than once. In such a case,
563 -- each subsequent occurrence toggles the effects of the modifier. Its effects
564 -- are enabled for the portion of the model following its first occurrence, and
565 -- then disabled for the portion following its second occurrence.
566
567 l_date_format := replace(l_date_format,'MON','FMMONFM');
568
569 if ( l_logSTMT ) then
570 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
571 , 'returning to_char(to_date('''||to_char(p_date,'DD-MON-RRRR HH24:MI:SS')||
572 ''',''DD-MON-RRRR HH24:MI:SS''), '''||l_date_format||''', ''NLS_CALENDAR = '''''||
573 l_nls_calendar||'''''''); ');
574 end if;
575
576 l_ret_value := to_char(p_date, l_date_format, 'NLS_CALENDAR = '''||l_nls_calendar||'''');
577
578 -- CASE2: Use NLS_DATE_LANGUAGE
579 -- Bug 11897707: Use current session language instead of default application language in the below condition
580 elsif (sys_context('USERENV','LANG') = 'AR' and
581 l_nls_territory not in ('JORDAN', 'LEBANON', 'SYRIA', 'IRAQ')) then
582
583 if ( l_logSTMT ) then
584 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
585 , 'returning to_char(to_date('''||to_char(p_date,'DD-MON-RRRR HH24:MI:SS')||
586 ''',''DD-MON-RRRR HH24:MI:SS''), '''||l_date_format||''', ''NLS_DATE_LANGUAGE='''''||
587 'EGYPTIAN''''''); ');
588 end if;
589
590 l_ret_value := to_char(p_date, l_date_format, 'NLS_DATE_LANGUAGE = ''EGYPTIAN''');
591
592 -- All Other CASES
593 else
594 if ( l_logSTMT ) then
595 wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, p_mod
596 , 'returning to_char(to_date('''||to_char(p_date,'DD-MON-RRRR HH24:MI:SS')||
597 ''',''DD-MON-RRRR HH24:MI:SS''), '''||l_date_format||''' );');
598 end if;
599
600 l_ret_value := to_char(p_date, l_date_format);
601 end if;
602
603 if ( l_logSTMT OR l_logPROC) then
604 wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, p_mod, 'Returning value '|| l_ret_value );
605 wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, p_mod, 'END');
606 end if;
607
608 return l_ret_value;
609
610 exception
611 when e_wrongNLSparam then
612 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
613 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION ora-12702!!');
614 end if;
615 return to_char(p_date, l_date_format);
616
617 when e_unrecognizedFormat then
618 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
619 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION ora-1821!!');
620 end if;
621 return to_char(p_date, null, 'NLS_CALENDAR = '''||l_nls_calendar||'''');
622
623 when e_numOrValueError then
624 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
625 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION ora-06502!!');
626 end if;
627 return to_char(p_date);
628
629 when others then
630 l_errCode := SQLCODE;
631 l_err := substr(sqlerrm,1,3500);
632 if (wf_log_pkg.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
633 wf_log_pkg.String(wf_log_pkg.LEVEL_EXCEPTION, p_mod, 'EXCEPTION '||l_errCode ||' - '|| l_err );
634 end if;
635 return to_char(p_date);
636 end GetCalendarDate;
637
638 FUNCTION GetLocalDateTime(p_date in date) return date
639 IS
640 BEGIN
641 return fnd_timezones_pvt.adjust_datetime(p_date, fnd_timezones.get_server_timezone_code, fnd_timezones.get_client_timezone_code);
642 END GetLocalDateTime;
643
644
645 --
646 -- Complete_RF
647 -- ER 10177347: This is a custom rule function that calls the
648 -- Complete() API to execute the callback function in
649 -- COMPLETE mode to comeplete the notification activity
650 -- IN
651 -- p_subscription_guid Subscription GUID as a CLOB
652 -- p_event Event Message
653 -- OUT
654 -- Status as ERROR, SUCCESS, WARNING
655 function Complete_RF(p_subscription_guid in raw,
656 p_event in out nocopy wf_event_t) return varchar2
657 is
658
659 l_nid number;
660 l_resp_found varchar2(10);
661
662 begin
663
664 l_nid := p_event.getValueForParameter('NOTIFICATION_ID');
665 l_resp_found := p_event.getValueForParameter('RESPONSE_FOUND');
666
667 if (l_resp_found = 'TRUE') then
668 wf_notification.Complete(l_nid);
669 end if;
670
671 return 'SUCCESS';
672
673 exception
674 when others then
675 wf_core.context('WF_NOTIFICATION', 'COMPLETE_RF', p_event.getEventName(),
676 p_subscription_guid);
677 wf_event.setErrorInfo(p_event, 'ERROR');
678 return 'ERROR';
679
680 end Complete_RF;
681
682
683 -- package body end.
684 end WF_NOTIFICATION_UTIL;
685
686
687
[Home] [Help]