1 PACKAGE BODY FND_DATE as
2 -- $Header: AFDDATEB.pls 120.7 2011/01/03 20:55:51 dbowles ship $
3
4
5 --
6 -- PUBLIC
7 --
8 -- Initialization routines
9
10 procedure initialize(p_user_mask varchar2,
11 p_userDT_mask varchar2 default NULL)
12 is
13 begin
14 initialize_with_calendar( p_user_mask,
15 p_userDT_mask,
16 'GREGORIAN');
17 end;
18
19
20 procedure initialize_with_calendar(p_user_mask varchar2,
21 p_userDT_mask varchar2 default NULL,
22 p_user_calendar varchar2 default 'GREGORIAN')
23 is
24 my_user_mask varchar2(100) := p_user_mask;
25 my_userDT_mask varchar2(200) := p_userDT_mask;
26 begin
27 if instr(my_user_mask,'|') > 0 then
28 my_user_mask := substr(my_user_mask,1,instr(my_user_mask,'|'));
29 end if;
30 if instr(my_userDT_mask,'|') > 0 then
31 my_userDT_mask := substr(my_userDT_mask,1,instr(my_userDT_mask,'|'));
32 end if;
33
34 -- Assign the user masks. If the userDT_mask is null than derive it
35 -- from the user_mask
36 FND_DATE.user_mask := my_user_mask;
37 FND_DATE.userDT_mask := NVL(my_userDT_mask,my_user_mask||' HH24:MI:SS');
38
39 -- Assign the output masks - for now we'll derive them from the user mask.
40 -- Strip off any FX or FM in the mask. This wouldn't actually affect
41 -- the output, but we use this mask as the error mask as well.
42 FND_DATE.output_mask := REPLACE(REPLACE(FND_DATE.user_mask,'FM'),'FX');
43 FND_DATE.outputDT_mask := REPLACE(REPLACE(FND_DATE.userDT_mask,'FM'),'FX');
44 FND_DATE.user_calendar := upper(p_user_calendar);
45 if not (FND_DATE.user_calendar = 'GREGORIAN'
46 or FND_DATE.user_calendar = 'THAI BUDDHA'
47 or FND_DATE.user_calendar = 'ARABIC HIJRAH'
48 or FND_DATE.user_calendar = 'ENGLISH HIJRAH') then
49 FND_DATE.user_calendar := 'GREGORIAN';
50 end if;
51
52 if (FND_DATE.user_calendar <> 'GREGORIAN') then
53 FND_DATE.is_non_gregorian := true;
54 else
55 FND_DATE.is_non_gregorian := false;
56 end if;
57 end;
58
59 -- to_char/to_date for non Gregorian calendar support. Private functions
60 -- Bug 10130806 to_char_intl is now a public function for 12.2
61 FUNCTION to_char_intl(dateval DATE,
62 output_mask VARCHAR2,
63 calendar_aware number) RETURN VARCHAR2 IS
64 BEGIN
65 -- Only non-Gregorian calendar.
66 if (calendar_aware = fnd_date.calendar_aware AND fnd_date.is_non_gregorian) OR
67 (calendar_aware = fnd_date.calendar_aware_alt AND fnd_date.is_non_gregorian)then
68 RETURN to_char(dateval, output_mask, 'NLS_CALENDAR='''||FND_DATE.user_calendar||'''');
69 else
70 RETURN to_char(dateval, output_mask);
71 end if;
72 END to_char_intl;
73
74 FUNCTION to_date_intl(chardt VARCHAR2,
75 output_mask VARCHAR2,
76 calendar_aware number) RETURN DATE IS
77 BEGIN
78 -- Only non-Gregorian calendar.
79 if (calendar_aware = fnd_date.calendar_aware AND fnd_date.is_non_gregorian) OR
80 (calendar_aware = fnd_date.calendar_aware_alt AND fnd_date.is_non_gregorian)then
81 RETURN to_date(chardt, output_mask, 'NLS_CALENDAR='''||FND_DATE.user_calendar||'''');
82 else
83 RETURN to_date(chardt, output_mask);
84 end if;
85 END to_date_intl;
86
87
88 --
89 -- Canonical functions
90 --
91 function canonical_to_date(canonical varchar2) return date is
92 dateval date;
93 new_canonical varchar2(30);
94 begin
95 new_canonical := canonical;
96 dateval := to_date(new_canonical, canonical_DT_mask);
97 return dateval;
98 end canonical_to_date;
99
100 function date_to_canonical(dateval date) return varchar2 is
101 begin
102 return to_char(dateval, canonical_DT_mask);
103 end date_to_canonical;
104
105 --
106 -- Date/DisplayDate functions - covers on the now obsolete Date/CharDate
107 -- functions. These functions are used to convert a date to and from
108 -- the display format.
109 --
110
111 function displaydate_to_date(chardate varchar2) return date is
112 begin
113 return displaydate_to_date(chardate, fnd_date.calendar_aware_default);
114 end displaydate_to_date;
115
116 function displaydate_to_date(chardate varchar2,
117 calendar_aware number) return date is
118 begin
119 return chardate_to_date(chardate, calendar_aware);
120 end displaydate_to_date;
121
122
123 -- TZ*
124 function displayDT_to_date(charDT varchar2) return date is
125 begin
126 return displayDT_to_date(charDT, fnd_date.calendar_aware_default);
127 end displayDT_to_date;
128
129 function displayDT_to_date(charDT varchar2,
130 calendar_aware number ) return date is
131 begin
132 return displayDT_to_date(charDT, null, calendar_aware);
133 end displayDT_to_date;
134 -- *TZ
135
136 function date_to_displaydate(dateval date) return varchar2 is
137 begin
138 return date_to_displaydate(dateval, fnd_date.calendar_aware_default);
139 end date_to_displaydate;
140
141 function date_to_displaydate(dateval date,
142 calendar_aware number) return varchar2 is
143 begin
144 return date_to_chardate(dateval, calendar_aware);
145 end date_to_displaydate;
146
147 -- TZ*
148 function date_to_displayDT(dateval date) return varchar2 is
149 begin
150 return date_to_displayDT(dateval, fnd_date.calendar_aware_default);
151 end date_to_displayDT;
152
153 function date_to_displayDT(dateval date,
154 calendar_aware number) return varchar2 is
155 begin
156 return date_to_displayDT(dateval, null, calendar_aware);
157 end date_to_displayDT;
158 -- *TZ
159
160 -- Date/CharDate functions
161
162 function chardate_to_date(chardate varchar2) return date is
163 begin
164 return chardate_to_date(chardate, fnd_date.calendar_aware_default);
165 end chardate_to_date;
166
167 function chardate_to_date(chardate varchar2,
168 calendar_aware number) return date is
169 dateval date;
170 new_chardate varchar2(30);
171 begin
172 new_chardate := chardate;
173 dateval := to_date_intl(new_chardate, user_mask, calendar_aware);
174 return dateval;
175 end chardate_to_date;
176
177 -- TZ*
178 function charDT_to_date(charDT varchar2) return date is
179 begin
180 return charDT_to_date(chardt, fnd_date.calendar_aware_default);
181 end charDT_to_date;
182
183 function charDT_to_date(charDT varchar2,
184 calendar_aware number) return date is
185 begin
186 return displayDT_to_date(chardt, null, calendar_aware);
187 end charDT_to_date;
188 -- *TZ
189
190 function date_to_chardate(dateval date) return varchar2 is
191 begin
192 return date_to_chardate(dateval, fnd_date.calendar_aware_default);
193 end date_to_chardate;
194
195 function date_to_chardate(dateval date,
196 calendar_aware number) return varchar2 is
197 begin
198 return to_char_intl(dateval, output_mask, calendar_aware);
199 end date_to_chardate;
200
201 -- TZ*
202 function date_to_charDT(dateval date) return varchar2 is
203 begin
204 return date_to_charDT(dateval, fnd_date.calendar_aware_default);
205 end date_to_charDT;
206
207 function date_to_charDT(dateval date,
208 calendar_aware number) return varchar2 is
209 begin
210 return date_to_displayDT(dateval, null, calendar_aware);
211 end date_to_charDT;
212
213 -- *TZ
214
215 FUNCTION string_to_date(p_string IN VARCHAR2,
216 p_mask IN VARCHAR2)
217 RETURN DATE
218 IS
219 BEGIN
220 --
221 -- First, try default settings.
222 --
223 BEGIN
224 RETURN(To_date(p_string, p_mask));
225 EXCEPTION
226 WHEN OTHERS THEN
227 NULL;
228 END;
229
230 --
231 -- Now try 'NUMERIC DATE LANGUAGE'
232 --
233 BEGIN
234 RETURN(To_date(p_string, p_mask,
235 'NLS_DATE_LANGUAGE = ''NUMERIC DATE LANGUAGE'''));
236 EXCEPTION
237 WHEN OTHERS THEN
238 NULL;
239 END;
240
241 --
242 -- For backward compatibility try 'ARABIC'.
243 -- 'ARABIC' uses numeric month names.
244 --
245 BEGIN
246 RETURN(To_date(p_string, p_mask,
247 'NLS_DATE_LANGUAGE = ''ARABIC'''));
248 EXCEPTION
249 WHEN OTHERS THEN
250 NULL;
251 END;
252
253 --
254 -- Now try currently installed languages.
255 --
256 DECLARE
257 --
258 -- Base language should come first.
259 --
260 CURSOR lang_cur IS
261 SELECT nls_language
262 FROM fnd_languages
263 WHERE installed_flag IN ('B','I')
264 ORDER BY installed_flag, nls_language;
265 BEGIN
266 FOR lang_rec IN lang_cur LOOP
267 BEGIN
268 RETURN(To_date(p_string, p_mask,
269 'NLS_DATE_LANGUAGE = ''' ||
270 lang_rec.nls_language || ''''));
271 EXCEPTION
272 WHEN OTHERS THEN
273 NULL;
274 END;
275 END LOOP;
276 EXCEPTION
277 WHEN OTHERS THEN
278 NULL;
279 END;
280
281 --
282 -- Now it is time to return NULL.
283 --
284 RETURN(NULL);
285 EXCEPTION
286 WHEN OTHERS THEN
287 --
288 -- This is Top Level Exception.
289 --
290 RETURN(NULL);
291 END string_to_date;
292
293 FUNCTION string_to_canonical(p_string IN VARCHAR2,
294 p_mask IN VARCHAR2)
295 RETURN VARCHAR2
296 IS
297 BEGIN
298 RETURN(To_char(string_to_date(p_string, p_mask),
299 fnd_date.canonical_dt_mask));
300 EXCEPTION
301 WHEN OTHERS THEN
302 RETURN(NULL);
303 END string_to_canonical;
304
305 -- use 'set serverout on;' to see the output from this test program
306
307 -- NOTE: If this test program is run twice in a row you get an ORA-600. This
308 -- is logged against PL/SQL as 771171
309 procedure test is
310 my_date date := SYSDATE;
311 my_char varchar2(20) := '01/01/2000 21:20:20';
312 begin
313 null;
314
315 /*
316 --commented out to avoid aru check constraints.
317
318 DBMS_OUTPUT.PUT_LINE('About to call initialize with FMMM/DD/RRRR');
319 fnd_date.initialize('FMMM/DD/RRRR');
320
321 -- tz*
322 DBMS_OUTPUT.PUT_LINE('About to call timezone initialize');
323 if fnd_timezones.TIMEZONES_ENABLED = 'Y' then
324 fnd_date_tz.init_timezones_for_fnd_date;
325 else
326 DBMS_OUTPUT.PUT_LINE('Timezones are not enabled');
327 end if;
328
329 DBMS_OUTPUT.PUT_LINE('Timezones are on (y/n) ' ||
330 fnd_timezones.get_timezone_enabled_flag);
331 DBMS_OUTPUT.PUT_LINE('Server timezone is ' ||
332 nvl(fnd_timezones.GET_SERVER_TIMEZONE_CODE,'null'));
333 DBMS_OUTPUT.PUT_LINE('Client timezone is ' ||
334 nvl(fnd_timezones.GET_CLIENT_TIMEZONE_CODE,'null'));
335 -- *tz
336
337 DBMS_OUTPUT.PUT_LINE('User date mask is '||fnd_date.user_mask);
338 DBMS_OUTPUT.PUT_LINE('Output date mask is '||fnd_date.output_mask);
339 DBMS_OUTPUT.PUT_LINE('UserDT mask is '||fnd_date.userDT_mask);
340 DBMS_OUTPUT.PUT_LINE('OutputDT mask is '||fnd_date.outputDT_mask);
341 DBMS_OUTPUT.PUT_LINE('Display date is
342 '||fnd_date.date_to_displaydate(my_date));
343 DBMS_OUTPUT.PUT_LINE('Display DT is '||fnd_date.date_to_displayDT(my_date));
344
345 DBMS_OUTPUT.PUT_LINE('Valid date is '||
346
347 date_to_displayDT(fnd_date.displaydate_to_date('02/01/2
348 000')));
349 DBMS_OUTPUT.PUT_LINE('Valid DT is '||
350 date_to_displayDT(fnd_date.displayDT_to_date(my_char)));
351
352 DBMS_OUTPUT.PUT_LINE('Canon date is '||fnd_date.date_to_canonical(sysdate));
353 DBMS_OUTPUT.PUT_LINE('and back is
354 '||fnd_date.date_to_displayDT(fnd_date.canonical_to_date('2001/03/12
355 14:22:22')));
356
357
358 select date_to_displayDT(sysdate+5)
359 into my_char
360 from dual;
361
362 DBMS_OUTPUT.PUT_LINE('Display date from SQL is '||my_char);
363
364 select date_to_canonical(sysdate+5)
365 into my_char
366 from dual;
367
368 DBMS_OUTPUT.PUT_LINE('Canonical date from SQL is '||my_char);
369
370 DBMS_OUTPUT.PUT_LINE('Valid date (w/no FX in mask) is
371 '||to_char(fnd_date.displayDT_to_date('DEC-01-2000'),'DD-MON-YYYY
372 HH24:MI:SS'));
373
374 DBMS_OUTPUT.PUT_LINE('Next line should raise an exception.');
375 DBMS_OUTPUT.PUT_LINE('Invalid date is
376 '||to_char(fnd_date.displaydate_to_date('01-MAR-1999'),'DD-MON-YYYY
377 HH24:MI:SS'));
378
379 DBMS_OUTPUT.PUT_LINE('Error - exception not raised.');
380 */
381
382 end;
383
384
385 -- tz*
386 function date_to_displayDT(dateval date,new_client_tz_code varchar2) return
387 varchar2 is
388 begin
389
390
391 return date_to_displayDT(dateval, new_client_tz_code, fnd_date.calendar_aware_default);
392
393 end date_to_displayDT;
394
395 function date_to_displayDT(dateval date,
396 new_client_tz_code varchar2,
397 calendar_aware number ) return
398 varchar2 is
399 t_dateval date;
400 tz_code varchar2(50);
401 begin
402
403 t_dateval := dateval;
404
405 if fnd_date.timezones_enabled then
406
407 if new_client_tz_code is not null then
408 tz_code := new_client_tz_code;
409 else
410 tz_code := fnd_date.client_timezone_code;
411 end if;
412
413 if tz_code <> fnd_date.server_timezone_code
414 and tz_code <> 'FND_NO_CONVERT' then
415 t_dateval := fnd_timezones_pvt.adjust_datetime(dateval
416 ,fnd_date.
417 server_timezone_code
418 ,tz_code);
419 end if;
420 end if;
421
422 return to_char_intl(t_dateval, outputDT_mask, calendar_aware);
423
424 end date_to_displayDT;
425
426 function displayDT_to_date(charDT varchar2,new_client_tz_code varchar2) return
427 date is
428 begin
429 return displayDT_to_date(charDT,new_client_tz_code, fnd_date.calendar_aware_default);
430
431 end displayDT_to_date;
432
433 function displayDT_to_date(charDT varchar2,
434 new_client_tz_code varchar2,
435 calendar_aware number) return
436 date is
437 dateval date;
438 -- new_charDT varchar2(20);
439
440 -- Bug 3485847: Modified size of variable from 20 to 30 due to 'ORA-06502:
441 -- PL/SQL: numeric or value error: character string buffer too small'
442 new_charDT varchar2(30);
443 tz_code varchar2(50);
444 begin
445 new_charDT := charDT;
446
447 dateval := to_date_intl(new_charDT, userDT_mask, calendar_aware);
448
449 if fnd_date.timezones_enabled then
450
451 if new_client_tz_code is not null then
452 tz_code := new_client_tz_code;
453 else
454 tz_code := fnd_date.client_timezone_code;
455 end if;
456
457 if tz_code <> fnd_date.server_timezone_code
458 and tz_code <> 'FND_NO_CONVERT' then
459 dateval := fnd_timezones_pvt.adjust_datetime(dateval
460 ,tz_code
461 ,fnd_date.
462 server_timezone_code);
463 end if;
464 end if;
465
466 return dateval;
467
468 end displayDT_to_date;
469
470 function adjust_datetime(date_time date
471 ,from_tz varchar2
472 ,to_tz varchar2) return date is
473 begin
474 if fnd_date.timezones_enabled then
475 return fnd_timezones_pvt.adjust_datetime(date_time,from_tz,to_tz);
476 else
477 return date_time;
481 -- *tz
478 end if;
479 end adjust_datetime;
480
482 function calendar_awareness_profile(p_application_id number) return varchar2 is
483 begin
484 return nvl(fnd_profile.value_specific(name => 'FND_DATE_API_CALENDAR_AWARENESS_DEFAULT',
485 application_id => p_application_id), '0');
486 end calendar_awareness_profile;
487
488 BEGIN
489
490 -- If the initialize routine is not called (for example on the concurrent
491 -- manager side in 11.5) the routines will use the hardcoded format of
492 -- DD-MON-RRRR.
493 FND_DATE.user_mask := 'DD-MON-RRRR';
494 FND_DATE.userDT_mask := FND_DATE.user_mask||' HH24:MI:SS';
495
496 -- Assign the output masks - for now we'll derive them from the user mask.
497 -- Strip off any FX or FM in the mask. This wouldn't actually affect
498 -- the output, but we use this mask as the error mask as well.
499 FND_DATE.output_mask := REPLACE(REPLACE(FND_DATE.user_mask,'FM'),'FX');
500 FND_DATE.outputDT_mask := REPLACE(REPLACE(FND_DATE.userDT_mask,'FM'),'FX');
501
502 -- TZ*
503 fnd_date.timezones_enabled := false;
504 -- *TZ
505 -- For non-Gregorian calendar support.
506 fnd_date.user_calendar := 'GREGORIAN';
507 fnd_date.is_non_gregorian := false;
508
509 end FND_DATE;
[Home] [Help]