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