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