1 PACKAGE BODY OTA_TIMEZONE_UTIL as
2 /* $Header: ottznutl.pkb 120.6 2006/12/06 09:14:21 rmoolave noship $ */
3
4 g_package varchar2(33) := 'ota_timezone_util.'; -- Global package name
5
6 /*
7 --This method will used in R12 code
8 FUNCTION convert_date_fnd(p_date in DATE
9 ,p_src_timezone_code IN VARCHAR2
10 ,p_dest_timezone_code IN VARCHAR2)
11 RETURN DATE
12 IS
13 BEGIN
14
15 RETURN fnd_timezones_pvt.adjust_datetime(
16 date_time => p_date
17 ,from_tz => p_src_timezone_code
18 ,to_tz => p_dest_timezone_code);
19 END convert_date_fnd;
20 */
21
22 FUNCTION convert_date_hz(p_date in DATE
23 ,p_src_timezone_code IN VARCHAR2
24 ,p_dest_timezone_code IN VARCHAR2)
25 RETURN DATE
26 IS
27 l_src_timezone_id HZ_TIMEZONES.TIMEZONE_ID%TYPE;
28 l_dest_timezone_id HZ_TIMEZONES.TIMEZONE_ID%TYPE;
29 BEGIN
30 l_src_timezone_id := Get_Timezone_ID(p_src_timezone_code);
31 l_dest_timezone_id := Get_Timezone_ID(p_dest_timezone_code);
32 RETURN HZ_TIMEZONE_PUB.Convert_DateTime(l_src_timezone_id, l_dest_timezone_id, p_date);
33 END convert_date_hz;
34
35 FUNCTION Get_Timezone_ID
36 (p_timezone_code in varchar2)
37 RETURN NUMBER
38 IS
39 CURSOR csr_get_timezone_id IS
40 SELECT UPGRADE_TZ_ID
41 FROM fnd_timezones_b
42 WHERE timezone_code = p_timezone_code;
43
44 l_timezone_id fnd_timezones_b.UPGRADE_TZ_ID%TYPE := NULL;
45 BEGIN
46 OPEN csr_get_timezone_id;
47 FETCH csr_get_timezone_id INTO l_timezone_id;
48 CLOSE csr_get_timezone_id;
49
50 RETURN l_timezone_id;
51 END Get_Timezone_ID;
52
53 PROCEDURE get_client_timezone_vals(
54 p_timezone_code OUT NOCOPY VARCHAR2
55 ,p_timezone_name OUT NOCOPY VARCHAR2)
56 IS
57 CURSOR csr_get_client_tzvals IS
58 SELECT timezone_code
59 ,name
60 FROM fnd_timezones_vl
61 WHERE UPGRADE_TZ_ID = fnd_profile.VALUE ('CLIENT_TIMEZONE_ID');
62 BEGIN
63 p_timezone_code := NULL;
64 p_timezone_name := NULL;
65 OPEN csr_get_client_tzvals;
66 FETCH csr_get_client_tzvals INTO p_timezone_code, p_timezone_name;
67 CLOSE csr_get_client_tzvals;
68 END;
69
70 FUNCTION get_server_timezone_code
71 RETURN VARCHAR2
72 IS
73 l_timezone_code VARCHAR2(50) := fnd_timezones.get_server_timezone_code;
74 /*
75 l_db_timezone VARCHAR2(6) := NULL;
76 l_offset NUMBER;
77
78 CURSOR csr_timezone_code(p_offset NUMBER) IS
79 SELECT timezone_code
80 FROM FND_TIMEZONES_B
81 WHERE GMT_OFFSET = p_offset
82 AND enabled_flag = 'Y';
83 */
84
85 BEGIN
86 /*
87 IF l_timezone_code IS NULL THEN
88 SELECT DBTIMEZONE INTO l_db_timezone FROM DUAL;
89 l_offset := substr(l_db_timezone,1,3)
90 + sign(substr(l_db_timezone,1,3)) * substr(l_db_timezone,5,6)/60;
91
92 OPEN csr_timezone_code(l_offset);
93 FETCH csr_timezone_code INTO l_timezone_code;
94 CLOSE csr_timezone_code;
95
96 RETURN l_timezone_code;
97
98 ELSE
99 RETURN l_timezone_code;
100 END IF;
101 */
102 RETURN l_timezone_code;
103 END get_server_timezone_code;
104
105 PROCEDURE get_event_timezone_vals(
106 p_event_id IN NUMBER
107 ,p_timezone_code OUT NOCOPY VARCHAR2
108 ,p_timezone_name OUT NOCOPY VARCHAR2)
109 IS
110 CURSOR csr_get_evt_tzvals IS
111 SELECT timezone_code
112 ,name
113 FROM fnd_timezones_tl ftt
114 ,ota_events evt
115 WHERE event_id = p_event_id
116 AND evt.timezone = ftt.timezone_code
117 AND ftt.language = userenv('LANG');
118
119 BEGIN
120 p_timezone_code := NULL;
121 p_timezone_name := NULL;
122 OPEN csr_get_evt_tzvals;
123 FETCH csr_get_evt_tzvals INTO p_timezone_code, p_timezone_name;
124 CLOSE csr_get_evt_tzvals;
125 END;
126
127
128 FUNCTION get_timezone_name(p_timezone_code in varchar2)
129 RETURN VARCHAR2
130 IS
131 CURSOR csr_get_timezone_vals IS
132 SELECT name
133 FROM fnd_timezones_tl
134 WHERE timezone_code = p_timezone_code
135 AND LANGUAGE = userenv('LANG');
136
137 l_timezone_name fnd_timezones_tl.name%TYPE := NULL;
138 BEGIN
139 OPEN csr_get_timezone_vals;
140 FETCH csr_get_timezone_vals INTO l_timezone_name;
141 CLOSE csr_get_timezone_vals;
142
143 RETURN l_timezone_name;
144 END;
145
146 FUNCTION convert_date(p_datevalue in DATE
147 ,p_timevalue IN VARCHAR2
148 ,p_src_timezone_code IN VARCHAR2
149 ,p_dest_timezone_code IN VARCHAR2)
150 RETURN DATE
151 IS
152 l_timevalue VARCHAR2(10);
153 l_return_date date := NULL;
154 l_return_time VARCHAR2(5);
155
156 l_src_timezone_id HZ_TIMEZONES.TIMEZONE_ID%TYPE;
157 l_dest_timezone_id HZ_TIMEZONES.TIMEZONE_ID%TYPE;
158 --l_datevalue DATE;
159
160 BEGIN
161
162 if p_datevalue is null then
163 return null;
164 end if;
165
166 l_timevalue:=nvl(p_timevalue,'00:00:00');
167
168
169
170 IF p_dest_timezone_code <> p_src_timezone_code THEN
171
172 l_return_date:= convert_date_hz(
173 fnd_date.canonical_to_date(to_char(p_datevalue,'YYYY/MM/DD')||' '||l_timevalue)
174 ,p_src_timezone_code
175 ,p_dest_timezone_code);
176 ELSE
177 l_return_date:= fnd_date.canonical_to_date(to_char(p_datevalue,'YYYY/MM/DD')||' '||l_timevalue);
178 END IF;
179 /*
180 l_return_time := to_char(l_return_date,'HH24:MI');
181 IF l_return_time = '00:00' THEN
182 RETURN to_char(l_return_date);
183 ELSE
184 RETURN to_char(l_return_date) ||' ' ||l_return_time;
185 END IF;
186 */
187 return l_return_date;
188
189 END convert_date;
190
191
192 FUNCTION convert_dateDT_time(p_datevalue in DATE
193 ,p_timevalue IN VARCHAR2
194 ,p_src_timezone_code IN VARCHAR2
195 ,p_dest_timezone_code IN VARCHAR2)
196 RETURN VARCHAR2
197 IS
198 l_date DATE := convert_date(p_datevalue,p_timevalue,p_src_timezone_code,p_dest_timezone_code);
199 BEGIN
200 IF l_date IS NOT NULL THEN --Bug 5233939
201 RETURN to_char(l_date,'HH24:MI');
202 ELSE
203 RETURN NULL;
204 END IF;
205 END convert_dateDT_time;
206
207
208 FUNCTION get_DateDT(
209 p_datevalue IN DATE
210 ,p_timevalue IN VARCHAR2
211 ,p_online_flag IN VARCHAR2
212 ,p_src_timezone IN VARCHAR2)
213 RETURN DATE
214 IS
215 l_timevalue VARCHAR2(10);
216 BEGIN
217 IF p_datevalue IS NOT NULL
218 -- Modified for bug#5532980
219 AND to_char(p_datevalue, 'RRRR/MM/DD') <> '4712/12/31' THEN
220 -- If there is no date, return NULL
221 IF p_timevalue IS NULL THEN
222 l_timevalue := '00:00:00';
223 ELSE
224 If ( length(p_timevalue) > 5 ) Then
225 l_timevalue := p_timevalue;
226 Else
227 l_timevalue := p_timevalue ||':00';
228 End If;
229 END IF;
230 IF p_online_flag = 'N' THEN
231 -- Do not convert for Offline Classes
232 RETURN convert_date(p_datevalue, p_timevalue, p_src_timezone, NULL);
233 ELSE
234 RETURN convert_date(p_datevalue, p_timevalue, p_src_timezone,fnd_timezones.get_client_timezone_code);
235 END IF;
236 ELSE
237 RETURN NULL;
238 END IF;
239
240 END get_DateDT;
241
242 FUNCTION get_dateDT_Time(
243 p_datevalue IN DATE
244 ,p_timevalue IN VARCHAR2
245 ,p_online_flag IN VARCHAR2
246 ,p_src_timezone IN VARCHAR2)
247 RETURN VARCHAR2
248 IS
249 l_converted_date DATE := get_DateDT(p_datevalue, p_timevalue, p_online_flag, p_src_timezone);
250 BEGIN
251 IF l_converted_date IS NOT NULL THEN --Bug 5233939
252 RETURN to_char(l_converted_date,'HH24:MI');
253 ELSE
254 RETURN NULL;
255 END IF;
256 END;
257
258 FUNCTION get_nls_language
259 RETURN varchar2
260 IS
261 CURSOR csr_get_nls_lang IS
262 SELECT NLS_LANGUAGE
263 FROM fnd_languages
264 WHERE language_code = userenv('LANG');
265 l_nls_language fnd_languages.NLS_LANGUAGE%TYPE;
266 BEGIN
267 OPEN csr_get_nls_lang;
268 FETCH csr_get_nls_lang INTO l_nls_language;
269 CLOSE csr_get_nls_lang;
270
271 RETURN l_nls_language;
272 END get_nls_language;
273
274 FUNCTION get_date_time(
275 p_datevalue IN DATE
276 ,p_timevalue IN VARCHAR2
277 ,p_online_flag IN VARCHAR2
278 ,p_src_timezone IN VARCHAR2
279 ,p_time_format IN VARCHAR2 default 'HH24:MI')
280 RETURN VARCHAR2
281 IS
282 l_converted_date DATE := get_DateDT(p_datevalue, p_timevalue, p_online_flag, p_src_timezone);
283 BEGIN
284 IF l_converted_date IS NOT NULL THEN --Bug 5233939
285 RETURN to_char(trunc(l_converted_date)
286 ,hr_util_misc_web.get_nls_parameter('NLS_DATE_FORMAT')
287 , 'nls_date_language = ''' || get_nls_language()||'''') || ' ' ||to_char(l_converted_date, p_time_format);
288 ELSE
289 RETURN NULL;
290 END IF;
291 END;
292
293 FUNCTION get_Class_DateDT(
294 p_datevalue IN DATE
295 ,p_timevalue IN VARCHAR2
296 ,p_event_id IN NUMBER)
297 RETURN DATE
298 IS
299 l_online_flag ota_category_usages.online_flag%TYPE;
300 l_evt_timezone ota_events.timezone%TYPE;
301 BEGIN
302 OPEN get_class_info(p_event_id);
303 FETCH get_class_info INTO l_online_flag, l_evt_timezone;
304 CLOSE get_class_info;
305
306 RETURN get_DateDT(p_datevalue, p_timevalue, l_online_flag, l_evt_timezone);
307
308 END get_Class_DateDT;
309
310
311 FUNCTION get_resource_bookingDT(
312 p_datevalue IN DATE
313 ,p_timevalue IN VARCHAR2
314 ,p_resource_booking_id IN NUMBER)
315 RETURN DATE
316 IS
317 l_online_flag ota_category_usages.online_flag%TYPE;
318 --l_trb_timezone ota_resource_bookings.timezone%TYPE;
319 l_trb_timezone fnd_timezones_tl.timezone_code%TYPE;
320 l_trb_timezone_name fnd_timezones_tl.name%TYPE;
321 BEGIN
322 OPEN get_resource_booking_info(p_resource_booking_id);
323 FETCH get_resource_booking_info INTO l_online_flag, l_trb_timezone, l_trb_timezone_name;
324 CLOSE get_resource_booking_info;
325
326 RETURN get_DateDT(p_datevalue, p_timevalue, l_online_flag, l_trb_timezone);
327 END get_resource_bookingDT;
328
329 end ota_timezone_util;
330
[Home] [Help]