1 PACKAGE BODY QLTDATE AS
2 /* $Header: qltdateb.plb 120.2.12020000.2 2013/01/04 15:06:40 hmakam ship $ */
3
4 canon_mask CONSTANT Varchar2(25) := 'YYYY/MM/DD';
5 canon_DT_mask CONSTANT Varchar2(26) := 'YYYY/MM/DD HH24:MI:SS';
6 Forms_canon_mask CONSTANT Varchar2(11) := fnd_date.name_in_mask;
7 Forms_canon_DT_mask CONSTANT Varchar2(26) := fnd_date.name_in_dt_mask;
8
9 --
10 -- See bug 2624112
11 -- Quality now allows decimal precision upto 12
12 -- Moved the position of D to have 12 significant values on right.
13 -- rkunchal Mon Oct 21 04:07:17 PDT 2002
14 --
15 number_canon_mask CONSTANT Varchar2(66) :=
16 'FM99999999999999999999999999999999999999999999999999D999999999999';
17
18 FUNCTION user_mask RETURN Varchar2 IS
19 --
20 -- Return the user mask as defined in the fnd_date package.
21 --
22 BEGIN
23 IF fnd_date.user_mask IS NULL THEN
24 RETURN fnd_date.name_in_mask;
25 ELSE
26 RETURN fnd_date.user_mask;
27 END IF;
28 END user_mask;
29
30
31 FUNCTION output_mask RETURN Varchar2 IS
32 --
33 -- Return the output mask as defined in the fnd_date package.
34 --
35 BEGIN
36 IF fnd_date.output_mask IS NULL THEN
37 RETURN fnd_date.name_in_mask;
38 ELSE
39 RETURN fnd_date.output_mask;
40 END IF;
41 END output_mask;
42
43
44 FUNCTION date_to_user(d Date) RETURN Varchar2 IS
45 --
46 -- Convert a date (in Date type) to output format (Varchar2).
47 -- bso
48 --
49 BEGIN
50 -- The following doesn't work if the fnd_date package is not
51 -- properly initialized. Unfortunately, it is not initialized
52 -- when submitting a report. Therefore, use my own conversion.
53 -- RETURN fnd_date.date_to_displaydate(d);
54 RETURN to_char(d, output_mask);
55 END date_to_user;
56
57
58 FUNCTION date_to_canon(d Date) RETURN Varchar2 IS
59 --
60 -- Convert a date (in Date type) to canonical format (Varchar2).
61 -- bso
62 --
63 BEGIN
64 RETURN to_char(d, canon_mask);
65 END date_to_canon;
66
67
68 FUNCTION canon_to_date(canon Varchar2) RETURN Date IS
69 --
70 -- Convert canonical date (in Varchar2) to Date type.
71 -- bso
72 --
73 BEGIN
74 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
75 -- changed date mask to DT mask
76 --
77 RETURN to_date(canon, canon_DT_mask);
78 END canon_to_date;
79
80
81 FUNCTION canon_to_date(canon Date) RETURN Date IS
82 --
83 -- Added after bug #2503882
84 -- Simply return the passed value. Useful to hide
85 -- implementation details and caller sees only one procedure
86 -- for both soft-coded and hard-coded dates.
87 -- rkunchal Mon Aug 26 04:34:42 PDT 2002
88 --
89 BEGIN
90 RETURN canon;
91 END canon_to_date;
92
93 FUNCTION canon_to_user(canon Varchar2) RETURN Varchar2 IS
94 --
95 -- Convert canonical date (in Varchar2) to output format (Varchar2).
96 -- bso
97 --
98 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
99 --
100 l_date DATE;
101 BEGIN
102 l_date:= to_date(canon,canon_mask);
103 RETURN date_to_user(canon_to_date(canon));
104
105 RETURN NULL; EXCEPTION WHEN OTHERS THEN BEGIN
106
107 l_date:= to_date(canon,canon_DT_mask);
108
109 -- convert l_date to client tz then to user display format
110 RETURN fnd_date.date_to_displayDT(l_date, calendar_aware=> FND_DATE.calendar_aware_alt);
111 END;
112
113 END canon_to_user;
114
115 FUNCTION canon_to_user(d Date) RETURN Varchar2 IS
116 --
117 -- Added after bug #2503882
118 -- Overloaded for consistency with any_to_user().
119 -- Caller now sees only one procedure for both soft-coded
120 -- and hard-coded elements.
121 -- rkunchal Mon Aug 26 04:34:42 PDT 2002
122 --
123 BEGIN
124 RETURN date_to_user(d);
125 END canon_to_user;
126
127 FUNCTION any_to_date(flex Date) RETURN Date IS
128 --
129 -- Extremely useful to have a any_to_date that takes in a real date.
130 -- Reason is, we have hardcoded and non-hardcoded dates. It would
131 -- be nice to write the same routine for both. This function can be
132 -- applied to both and yield the same result.
133 -- bso
134 --
135 BEGIN
136 RETURN flex;
137 END any_to_date;
138
139
140 FUNCTION any_to_date(flex Varchar2) RETURN Date IS
141 --
142 -- Convert any date (in Varchar2) to Date type.
143 -- bso
144 --
145 -- See bug #2503882
146 -- to_date() is behaving different than expected
147 -- if the language is set to 'ZHS' and 'Local Date Language'
148 -- is set to 'Numeric Date Language'. This might be happening
149 -- with languages other than ZHS also. But, ZHS case is
150 -- established.
151 -- This is happening with elements which are mapped to
152 -- characterxxx columns where date is stored in canonical form.
153
154 -- Modifying to call fnd_date.string_to_date() before doing
155 -- our regular Exception ladder.
156 -- rkunchal Mon Aug 19 02:40:07 PDT 2002
157
158 tmp_date DATE;
159
160 BEGIN
161 --
162 -- Bug 5921013
163 -- Passing the User format Mask instead of the
164 -- Canonical mask
165 -- ntungare Mon May 7 10:38:29 PDT 2007
166 --
167 --tmp_date := FND_DATE.string_to_date(flex, canon_mask);
168 tmp_date := FND_DATE.string_to_date(flex, user_mask);
169
170 IF tmp_date IS NULL THEN
171 -- Bug 3179845. Timezone Project. rponnusa
172 -- Added mask to fix GSCC error
173 --
174 -- Bug 5921013
175 -- Passing the User format Mask instead of the
176 -- Canonical mask
177 -- ntungare Mon May 7 10:38:29 PDT 2007
178 --
179 -- RETURN to_date(flex,canon_mask);
180 RETURN to_date(flex,user_mask);
181 ELSE
182 RETURN tmp_date;
183 END IF;
184
185 EXCEPTION WHEN OTHERS THEN BEGIN
186
187 RETURN to_date(flex, forms_canon_mask);
188
189 EXCEPTION WHEN OTHERS THEN BEGIN
190 RETURN to_date(flex, forms_canon_DT_mask);
191
192 --
193 -- Bug 5921013
194 -- Commenting out this section since the
195 -- converison with the user format mask has been
196 -- handled above
197 -- ntungare Mon May 7 10:38:29 PDT 2007
198 --
199 /*
200 EXCEPTION WHEN OTHERS THEN BEGIN
201 RETURN to_date(flex, user_mask);*/
202
203 EXCEPTION WHEN OTHERS THEN BEGIN
204 RETURN to_date(flex, output_mask);
205
206 EXCEPTION WHEN OTHERS THEN BEGIN
207 RETURN to_date(flex, canon_mask);
208
209 EXCEPTION WHEN OTHERS THEN BEGIN
210 RETURN to_date(flex, canon_DT_mask);
211 END;
212 END;
213 END;
214 --END;
215 END;
216 END;
217 END any_to_date;
218
219
220 FUNCTION any_to_canon(flex Varchar2) RETURN Varchar2 IS
221 --
222 -- Convert any date/datetime (in Varchar2) to canonical format.
223 -- bso
224 --
225 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
226 l_date DATE;
227
228 BEGIN
229
230 -- RETURN date_to_canon(any_to_date(flex));
231
232 -- To find out flex contains timeportion, we need to pass thro'
233 -- following ladder since we dont know the date mask of flex
234
235 -- Bug 3935591. Data entered through mobile was showing wrong value in Forms VQR for softcoded DATE type
236 -- elements.For this particular flow QA_VALIDATION_API.VALIDATE_DATE calls this function with flex
237 -- already in canon_mask format.So FND_DATE.string_to_date() returns a date and earlier the function was
238 -- returning l_date. Modified the code to return date_to_canon(l_date)(VARCHAR2).
239 -- srhariha. Tue Nov 9 01:12:23 PST 2004.
240
241 --
242 -- Bug 5921013
243 -- Passing the User format Mask instead of the
244 -- Canonical mask
245 -- ntungare Mon May 7 10:38:29 PDT 2007
246 --
247 --l_date := FND_DATE.string_to_date(flex, canon_mask);
248 l_date := FND_DATE.string_to_date(flex, user_mask);
249 IF l_date IS NULL THEN
250 --
251 -- Bug 5921013
252 -- Passing the User format Mask instead of the
253 -- Canonical mask
254 -- ntungare Mon May 7 10:38:29 PDT 2007
255 --
256 -- RETURN date_to_canon(to_date(flex, canon_mask));
257 RETURN date_to_canon(to_date(flex, user_mask));
258 ELSE
259 RETURN date_to_canon(l_date);
260 END IF;
261
262 EXCEPTION WHEN OTHERS THEN BEGIN
263 RETURN date_to_canon(to_date(flex, forms_canon_mask));
264
265 --
266 -- Bug 5921013
267 -- Commenting out this section since the
268 -- converison with the user format mask has been
269 -- handled above
270 -- ntungare Mon May 7 10:38:29 PDT 2007
271 --
272 /*
273 EXCEPTION WHEN OTHERS THEN BEGIN
274 RETURN date_to_canon(to_date(flex, user_mask));*/
275
276 EXCEPTION WHEN OTHERS THEN BEGIN
277 RETURN date_to_canon(to_date(flex, output_mask));
278
279 EXCEPTION WHEN OTHERS THEN BEGIN
280 RETURN date_to_canon(to_date(flex, canon_mask));
281
282 EXCEPTION WHEN OTHERS THEN BEGIN
283 -- we are here since flex contains time portion
284 RETURN date_to_canon_dt(any_to_datetime(flex));
285 END;
286 END;
287 END;
288 --END;
289 END;
290 END any_to_canon;
291
292
293 FUNCTION any_to_user(flex Varchar2) RETURN Varchar2 IS
294 --
295 -- Convert any date (in Varchar2) to user format.
296 -- bso
297 --
298 BEGIN
299 RETURN date_to_user(any_to_date(flex));
300 END any_to_user;
301
302 -- See bug #2503882
303 -- Overloaded to treat hard-coded and soft-coded
304 -- collection elements differently.
305 -- Hard-coded elements donot need to be to_date()-ed.
306 -- rkunchal Thu Aug 22 09:57:16 PDT 2002
307
308 FUNCTION any_to_user(d Date) RETURN Varchar2 IS
309 BEGIN
310 RETURN date_to_user(d);
311 END any_to_user;
312
313 FUNCTION canon_to_number(canon Varchar2) RETURN Number IS
314 --
315 -- Convert a canonical number in character string to a real number.
316 -- bso
317 --
318 BEGIN
319 -- Added the IF condition to make the code work properly if the
320 -- nls_numeric_characters is set to ',.'.
321 -- Bug 3930684.suramasw.
322
323 IF instr(canon, '.') > 0 THEN
324 RETURN to_number(canon, number_canon_mask, 'nls_numeric_characters=''.,''');
325 ELSE
326 RETURN to_number(canon, number_canon_mask, 'nls_numeric_characters='',.''');
327 END IF;
328
329 END canon_to_number;
330
331
332 FUNCTION canon_to_number(canon Number) RETURN Number IS
333 --
334 -- Extremely useful to have a canon_to_number that takes in a number.
335 -- Reason is, we have hardcoded and non-hardcoded numbers. It would
336 -- be nice to write the same routine for both. This function can be
337 -- applied to both and yield the same result.
338 -- bso
339 --
340 BEGIN
341 RETURN canon;
342 END canon_to_number;
343
344
345 FUNCTION any_to_number(n Varchar2) RETURN Number IS
346 --
347 -- Convert a fake number to a real number. The fake number can be in
348 -- any format.
349 --
350 -- *** It is important to note that this function only works for
351 -- floating point numbers, not for currency. This routine
352 -- treats either '.' or ',' as decimal point; therefore, one
353 -- cannot have a group separator (thousand separator) in the
354 -- input number.
355 -- bso
356 --
357 BEGIN
358 IF instr(n, ',') > 0 THEN
359 RETURN to_number(n, number_canon_mask, 'nls_numeric_characters='',.''');
360 ELSE
361 RETURN to_number(n, number_canon_mask, 'nls_numeric_characters=''.,''');
362 END IF;
363 END any_to_number;
364
365
366 FUNCTION any_to_number(n Number) RETURN Number IS
367 --
368 -- Extremely useful to have a any_to_number that takes in a number.
369 -- Reason is, we have hardcoded and non-hardcoded numbers. It would
370 -- be nice to write the same routine for both. This function can be
371 -- applied to both and yield the same result.
372 -- bso
373 --
374 BEGIN
375 RETURN n;
376 END any_to_number;
377
378
379 FUNCTION number_to_canon(n Number) RETURN Varchar2 IS
380 --
381 -- Convert a number to canonical format.
382 -- bso
383 --
384 BEGIN
385 -- The following returns a canonical number but if the number is an
386 -- integer, it will have a '.' at the end. It would be nice if AOL
387 -- has a routine to do the conversion.
388 -- RETURN to_char(n, number_canon_mask, 'nls_numeric_characters=''.,''');
389
390 --
391 -- Integer should not have trailing '.'
392 --
393 RETURN rtrim(
394 to_char(n, number_canon_mask, 'nls_numeric_characters=''.,'''), '.');
395 END number_to_canon;
396
397
398 FUNCTION number_canon_to_user(canon Varchar2) RETURN Varchar2 IS
399 --
400 -- Convert a number in canonical format to one in user format.
401 -- bso
402 --
403 BEGIN
404 RETURN to_char(canon_to_number(canon));
405 END number_canon_to_user;
406
407
408 FUNCTION number_user_to_canon(n Varchar2) RETURN Varchar2 IS
409 --
410 -- Convert a number in user format to one in canonical format.
411 -- bso
412 --
413 BEGIN
414 RETURN number_to_canon(to_number(n));
415 END number_user_to_canon;
416
417
418 FUNCTION get_sysdate RETURN date IS
419 BEGIN
420 RETURN sysdate;
421 END get_sysdate;
422
423
424 FUNCTION upgrade_to_canon(flex Varchar2) RETURN Varchar2 IS
425 --
426 -- For upgrade purpose only. Try to convert an old 10.7 date into
427 -- canonical format. If input is already a canonical, then don't
428 -- convert. If data cannot be converted (if it is not a date in
429 -- recognizable format), then simply return the original data.
430 --
431 d date;
432 BEGIN
433 d := to_date(flex, canon_mask);
434 return date_to_canon(d);
435
436 EXCEPTION WHEN OTHERS THEN
437 d := fnd_date.string_to_date(flex, forms_canon_dt_mask);
438 IF d IS NULL THEN
439 --
440 -- Just to be safe, if it cannot be converted, then
441 -- return the original data.
442 --
443 return flex;
444 END IF;
445 return date_to_canon(d);
446 END upgrade_to_canon;
447
448 --
449 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
450 -- Following new function are added
451 --
452
453 FUNCTION date_to_canon_dt(d Date) RETURN Varchar2 IS
454 --
455 -- replica of date_to_canon function with time portion
456 --
457
458 BEGIN
459 RETURN to_char(d, canon_DT_mask);
460 END date_to_canon_dt;
461
462 FUNCTION any_to_datetime(flex Varchar2) RETURN Date IS
463 --
464 -- Convert any datetime (in Varchar2) to Datetime type.
465 --
466
467 l_date DATE;
468
469 BEGIN
470
471 l_date := FND_DATE.string_to_date(flex, canon_DT_mask);
472 IF l_date IS NULL THEN
473 RETURN to_date(flex , canon_DT_mask);
474 ELSE
475 RETURN l_date;
476 END IF;
477
478 EXCEPTION WHEN OTHERS THEN BEGIN
479 RETURN to_date(flex, forms_canon_DT_mask);
480
481 EXCEPTION WHEN OTHERS THEN BEGIN
482 RETURN to_date(flex, canon_DT_mask);
483 END;
484 END;
485
486 END any_to_datetime;
487
488
489 FUNCTION any_to_datetime(flex DATE) RETURN Date IS
490 --
491 -- Extremely useful to have a any_to_datetime that takes in a real date.
492 -- Reason is, we have hardcoded and non-hardcoded dates. It would
493 -- be nice to write the same routine for both. This function can be
494 -- applied to both and yield the same result.
495 --
496 --
497 BEGIN
498 RETURN flex;
499 END any_to_datetime;
500
501 FUNCTION output_DT_mask RETURN Varchar2 IS
502 --
503 -- Return the output DT mask as defined in the fnd_date package.
504 --
505 BEGIN
506 IF fnd_date.outputDT_mask IS NULL THEN
507 RETURN fnd_date.name_in_DT_mask;
508 ELSE
509 RETURN fnd_date.outputDT_mask;
510 END IF;
511 END output_DT_mask;
512
513
514 FUNCTION date_to_user_dt(d Date) RETURN Varchar2 IS
515 --
516 -- Convert a datetime (in Date type) to output format (Varchar2).
517 -- Replica of date_to_user fn with time portion
518 --
519 BEGIN
520 RETURN to_char(d, output_DT_mask);
521 END date_to_user_dt;
522
523 FUNCTION any_to_user_dt(flex Varchar2) RETURN Varchar2 IS
524 --
525 -- Convert any datetime (in Varchar2) to user format.
526 -- Replica of fn any_to_user with time portion
527 --
528 BEGIN
529 RETURN date_to_user_dt(any_to_datetime(flex));
530 END any_to_user_dt;
531
532 --
533 -- Bug 16029748
534 -- Function to convert the Varchar qty to number based on the user format mask
535 --
536 FUNCTION user_to_number (p_char_val IN VARCHAR2) RETURN NUMBER IS
537 l_profile_numformat VARCHAR2(100);
538 BEGIN
539 FND_PROFILE.Get('ICX_NUMERIC_CHARACTERS', l_profile_numformat);
540
541 RETURN to_number(p_char_val,
542 number_canon_mask,
543 'nls_numeric_characters='''||NVL(l_profile_numformat,'.,')||'''');
544 END;
545
546 END QLTDATE;
[Home] [Help]