[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_DATE
Source
1 package body FND_CONC_DATE as
2 /* $Header: AFCPDATB.pls 120.2 2005/09/16 15:20:51 jtoruno ship $ */
3
4
5
6 --
7 -- STRING_TO_DATE was created to deal with dates coming from
8 -- profiles and program parameters. The problem is that we do not
9 -- know the format of the date in the string. STRING_TO_DATE
10 -- attempts to convert the date using the following format masks in
11 -- the following order:
12 --
13 -- 1. The NLS_DATE_FORMAT
14 -- 2. 'DD-MON-RR' or 'DD-MON-YYYY'
15 -- 3. 'DD-MM-RR' or 'DD-MM-YYYY'
16 -- 4. The AOL canonical format, 'YYYY/MM/DD'
17 --
18 -- The string may also include a time component in the form
19 -- HH24:MI, or HH24:MI:SS.
20 --
21
22 function STRING_TO_DATE (string in varchar2) return date is
23
24 base_len number;
25 nls_date_fmt varchar2(80);
26 out_date date;
27
28 begin
29
30 /* First try vs. NLS_DATE_FORMAT */
31 select value
32 into nls_date_fmt
33 from nls_session_parameters
34 where parameter = 'NLS_DATE_FORMAT';
35
36 base_len := length(to_char(sysdate, nls_date_fmt));
37 select decode(length (string),
38 base_len, to_date(string, nls_date_fmt),
39 base_len + 6, to_date(string, nls_date_fmt || ' HH24:MI'),
40 base_len + 9, to_date(string, nls_date_fmt || ' HH24:MI:SS'),
41 to_date(string, 'ABC')) -- Intentional exception!
42 into out_date
43 from sys.dual;
44
45 return out_date;
46
47 exception
48
49 when others then /* Try DD-MON-RR derivatives */
50 begin
51 base_len := length(to_char(sysdate, 'DD-MON-RR'));
52
53 Select Decode (Length (string),
54 base_len, To_Date (string, 'DD-MON-RR'),
55 base_len + 2, To_Date (string, 'DD-MON-YYYY'),
56 base_len + 6, To_Date (string, 'DD-MON-RR HH24:MI'),
57 base_len + 8, To_Date (string, 'DD-MON-YYYY HH24:MI'),
58 base_len + 9, To_Date (string, 'DD-MON-RR HH24:MI:SS'),
59 To_Date (string, 'DD-MON-YYYY HH24:MI:SS'))
60 into out_date
61 from Sys.Dual;
62
63 return out_date;
64
65 exception
66
67 when others then /* Try DD-MM-RR derivatives */
68 begin
69 base_len := length(to_char(sysdate, 'DD-MM-RR'));
70
71 Select Decode (Length (string),
72 base_len, To_Date (string, 'DD-MM-RR'),
73 base_len + 2, To_Date (string, 'DD-MM-YYYY'),
74 base_len + 6, To_Date (string, 'DD-MM-RR HH24:MI'),
75 base_len + 8, To_Date (string, 'DD-MM-YYYY HH24:MI'),
76 base_len + 9, To_Date (string, 'DD-MM-RR HH24:MI:SS'),
77 To_Date (string, 'DD-MM-YYYY HH24:MI:SS'))
78 into out_date
79 from Sys.Dual;
80
81 return out_date;
82
83 exception
84 when others then /* Finally try 'YYYY/MM/DD' derivatives */
85 begin
86
87 Select Decode (Length (string),
88 10, To_Date (string, 'YYYY/MM/DD'),
89 16, To_Date (string, 'YYYY/MM/DD HH24:MI'),
90 19, To_Date (string, 'YYYY/MM/DD HH24:MI:SS'))
91 into out_date
92 from Sys.Dual;
93
94 return out_date;
95
96 exception
97 when others then return null;
98 end;
99 end;
100 end;
101 end;
102
103
104 --
105 -- GET_DATE_FORMAT uses the same algorithm as STRING_TO_DATE
106 -- to return the date format of a string.
107 -- The format string can then be used in to_date or to_char.
108
109 function get_date_format (string in varchar2) return VARCHAR2 is
110
111 base_len number;
112 nls_date_fmt varchar2(80);
113 nls_date_lang varchar2(80);
114 out_fmt varchar2(30);
115
116 begin
117
118 /* First, check whether NLS_DATE_LANGUAGE is NUMERIC DATE LANGUAGE */
119 select value
120 into nls_date_lang
121 from nls_session_parameters
122 where parameter = 'NLS_DATE_LANGUAGE';
123
124 /* Try vs. NLS_DATE_FORMAT */
125 select value
126 into nls_date_fmt
127 from nls_session_parameters
128 where parameter = 'NLS_DATE_FORMAT';
129
130 base_len := length(to_char(sysdate, nls_date_fmt));
131 select decode(length (string),
132 base_len, nls_date_fmt,
133 base_len + 6, nls_date_fmt || ' HH24:MI',
134 base_len + 9, nls_date_fmt || ' HH24:MI:SS',
135 NULL)
136 into out_fmt
137 from sys.dual;
138
139 IF out_fmt IS NOT NULL then
140 return out_fmt;
141 END IF;
142
143
144 /* Try DD-MON-RR derivatives */
145 /* Bug 2976386: Only if NLS_DATE_LANGUAGE <> NUMERIC DATE LANGUAGE */
146 /* Bug 4394140:
147 The arabic/egytpian check is being used here because these languages
148 return numeric month name for MON format in the Oracle DB.
149 */
150
151 if ( nls_date_lang <> 'NUMERIC DATE LANGUAGE' and
152 nls_date_lang <> 'ARABIC' and
153 nls_date_lang <> 'EGYPTIAN' ) then
154 base_len := length(to_char(sysdate, 'DD-MON-RR'));
155
156 Select Decode (Length (string),
157 base_len, 'DD-MON-RR',
158 base_len + 2, 'DD-MON-YYYY',
159 base_len + 6, 'DD-MON-RR HH24:MI',
160 base_len + 8, 'DD-MON-YYYY HH24:MI',
161 base_len + 9, 'DD-MON-RR HH24:MI:SS',
162 base_len + 11, 'DD-MON-YYYY HH24:MI:SS',
163 NULL)
164 into out_fmt
165 from Sys.Dual;
166 end if;
167
168 IF out_fmt IS NOT NULL then
169 return out_fmt;
170 END IF;
171
172 /* Try DD-MM-RR derivatives */
173
174 base_len := length(to_char(sysdate, 'DD-MM-RR'));
175
176 Select Decode (Length (string),
177 base_len, 'DD-MM-RR',
178 base_len + 2, 'DD-MM-YYYY',
179 base_len + 6, 'DD-MM-RR HH24:MI',
180 base_len + 8, 'DD-MM-YYYY HH24:MI',
181 base_len + 9, 'DD-MM-RR HH24:MI:SS',
182 base_len + 11, 'DD-MM-YYYY HH24:MI:SS',
183 NULL)
184 into out_fmt
185 from Sys.Dual;
186
187 IF (out_fmt IS NOT NULL and INSTR(string,'-') > 0) then
188 return out_fmt;
189 END IF;
190
191 /* Finally try 'YYYY/MM/DD' derivatives */
192
193
194 Select Decode (Length (string),
195 10, 'YYYY/MM/DD',
196 16, 'YYYY/MM/DD HH24:MI',
197 19, 'YYYY/MM/DD HH24:MI:SS',
198 NULL)
199 into out_fmt
200 from Sys.Dual;
201
202
203 return out_fmt;
204
205
206
207 END get_date_format;
208
209 end fnd_conc_date;