1 PACKAGE BODY hr_date AS
2 /* $Header: hrutldtw.pkb 120.1 2005/06/15 02:42:20 sturlapa noship $ */
3
4 CURSOR gcsr_group_separator(p_parameter in varchar2) IS
5 SELECT value
6 FROM V$NLS_PARAMETERS
7 WHERE parameter = p_parameter;
8
9 g_group_separator varchar2(2000);
10
11 -- ------------------------------------------------------------------------
12 -- chardate_to_date
13 -- takes a character in display format and return a date
14 -- ------------------------------------------------------------------------
15 FUNCTION chardate_to_date
16 (p_chardate in varchar2)
17 RETURN date IS
18
19 BEGIN
20
21 RETURN TRUNC(TO_DATE(p_chardate, hr_util_misc_web.get_user_date_format()));
22
23 END chardate_to_date;
24
25 -- ------------------------------------------------------------------------
26 -- date_to_chardate
27 -- convert date to display format
28 -- ------------------------------------------------------------------------
29 FUNCTION date_to_chardate
30 (p_date in date)
31 RETURN varchar2 IS
32
33 BEGIN
34
35 RETURN TO_CHAR(p_date, hr_util_misc_web.get_user_date_format());
36
37 END date_to_chardate;
38
39 -- ------------------------------------------------------------------------
40 -- date_to_canonical
41 -- convert date to canonical date format
42 -- ------------------------------------------------------------------------
43 FUNCTION date_to_canonical
44 (p_date in date)
45 RETURN varchar2 IS
46
47 BEGIN
48 RETURN to_char(p_date,'RRRR/MM/DD');
49 --RETURN fnd_date.date_to_canonical(p_date);
50 END date_to_canonical;
51
52 -- ------------------------------------------------------------------------
53 -- canonical_to_date
54 -- convert canonical date to Oracle date
55 -- ------------------------------------------------------------------------
56 FUNCTION canonical_to_date
57 (p_canonical in varchar2)
58 RETURN date IS
59
60 BEGIN
61 RETURN trunc(to_date(p_canonical, 'RRRR/MM/DD'));
62 --RETURN trunc(fnd_date.canonical_to_date(p_canonical));
63 END canonical_to_date;
64
65 -- ------------------------------------------------------------------------
66 -- chardate_to_canonical
67 -- take a character in display format and return a canonical date
68 -- ------------------------------------------------------------------------
69 FUNCTION chardate_to_canonical
70 (p_chardate in varchar2)
71 RETURN varchar2 IS
72
73 BEGIN
74 RETURN date_to_canonical(chardate_to_date(p_chardate));
75 END chardate_to_canonical;
76
77 -- ------------------------------------------------------------------------
78 -- canonical_to_chardate
79 -- convert canonical date to chardate in display format
80 -- ------------------------------------------------------------------------
81 FUNCTION canonical_to_chardate
82 (p_canonical in varchar2)
83 RETURN varchar2 IS
84
85 temp varchar2(2000);
86
87 BEGIN
88 -- this fix is for oracle IT. In r11 we use 'DD-MON-RRRR' date format
89 -- in r11i, we use 'RRRR/MM/DD'.
90 -- we have to convert the old date value into the correct date format
91 -- in oracle IT database, all old date values have the 'DD-MON-RRRR'
92 -- format.
93 -- For other customers, we may need to improve this function.
94
95 if (instr(p_canonical,'-') = 3 and length(p_canonical) = 11) then
96 -- bug fix 2132425 start
97 temp := to_char(to_date(p_canonical, 'DD-MM-RRRR'),'RRRR/MM/DD');
98 -- bug fix 2132425 end
99 else
100 temp := p_canonical;
101 end if;
102
103 RETURN date_to_chardate(canonical_to_date(temp));
104 EXCEPTION
105 WHEN OTHERS THEN
106 RETURN p_canonical;
107 END canonical_to_chardate;
108
109 END hr_date;