1 package body HZ_TIMEZONE_PUB as
2 /*$Header: ARHTMZOB.pls 120.19 2006/08/14 00:59:33 kbaird ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_TIMEZONE_PUB';
5
6 /*===========================================================================+
7 | PROCEDURE |
8 | Get_Timezone_ID |
9 | |
10 | DESCRIPTION |
11 | Get Timezone ID given the address element. |
12 | |
13 | SCOPE - PUBLIC |
14 | |
15 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
16 | |
17 | ARGUMENTS : IN: |
18 | p_api_version |
19 | p_init_msg_list |
20 | p_postal_code |
21 | p_city |
22 | p_state |
23 | p_country |
24 | OUT: |
25 | x_return_status |
26 | x_msg_count |
27 | x_msg_data |
28 | x_timezone_id |
29 | IN/ OUT: |
30 | |
31 | RETURNS : NONE |
32 | |
33 | NOTES |
34 | |
35 | MODIFICATION HISTORY |
36 | Stephanie Zhang 23-AUG-99 Created |
37 | |
38 +===========================================================================*/
39 Procedure Get_Timezone_ID
40 (
41 p_api_version in number,
42 -- p_init_msg_list in varchar2:= FND_API.G_FLASE ,
43 p_init_msg_list in varchar2,
44 p_postal_code in varchar2,
45 p_city in varchar2,
46 p_state in varchar2,
47 p_country in varchar2,
48 x_timezone_id out nocopy number,
49 x_return_status out nocopy varchar2,
50 x_msg_count out nocopy number,
51 x_msg_data out nocopy varchar2)
52 IS
53 l_api_name CONSTANT VARCHAR2(30) := 'Get_Timezone_ID';
54 l_api_version CONSTANT NUMBER := 1.0;
55
56 l_msg_token VARCHAR2(2000);
57 BEGIN
58
59 --Standard call to check for call compatibility.
60 IF NOT FND_API.Compatible_API_Call(
61 l_api_version,
62 p_api_version,
63 l_api_name,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 --Initialize message list if p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean(p_init_msg_list) THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73
74 --Initialize API return status to success.
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 BEGIN
78 -- postal_code is unique within a country. There should never be more than one mapping row
79 -- for the same postal code within the same country. Thus, if the postal code and country are passed in
80 -- and a match on that postal code and country are found, that is the correct time zone (without regard
81 -- to the city or state on the mapping row or the city or state passed in)
82 SELECT timezone_id
83 INTO x_timezone_id
84 FROM HZ_TIMEZONE_MAPPING
85 WHERE postal_code = p_postal_code
86 AND country = p_country;
87 EXCEPTION WHEN NO_DATA_FOUND THEN
88 -- if no postal code direct match is found, then start walking the geographic hierarchy.
89 -- match first at city level, then state, then country.
90 -- Since state is not required in many countries of the world, there are slightly
91 -- different checks for the case where people expect state to be passed and when not.
92 -- We need to avoid the case where a city and country are passed and there might be multiple
93 -- cities in the same country with the same name. City and Country cannot be used
94 -- to match a mapping row without a state present.
95 IF (p_state is null) THEN --compare all, no state
96 BEGIN
97 SELECT timezone_id
98 INTO x_timezone_id
99 FROM HZ_TIMEZONE_MAPPING
100 WHERE postal_code = p_postal_code
101 AND upper(city) = upper(p_city)
102 AND state is null
103 AND country = p_country;
104 EXCEPTION WHEN NO_DATA_FOUND THEN --compare city, no state
105 BEGIN
106 SELECT timezone_id
107 INTO x_timezone_id
108 FROM HZ_TIMEZONE_MAPPING
109 WHERE postal_code is null
110 AND upper(city) = upper(p_city)
111 AND state is null
112 AND country = p_country;
113 EXCEPTION WHEN NO_DATA_FOUND THEN --compare country, no state
114 BEGIN
115 SELECT timezone_id
116 INTO x_timezone_id
117 FROM HZ_TIMEZONE_MAPPING
118 WHERE postal_code is null
119 AND city is null
120 AND state is null
121 AND country = p_country;
122 EXCEPTION WHEN NO_DATA_FOUND THEN --not found
123 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
124 FND_MESSAGE.SET_TOKEN('RECORD', 'mapping');
125 --------Bug no: 3565475--------------------
126 IF p_postal_code IS NOT NULL and p_postal_code <> fnd_api.g_miss_char THEN
127 l_msg_token := p_postal_code||',';
128 END IF;
129 IF p_city IS NOT NULL and p_city <> fnd_api.g_miss_char THEN
130 l_msg_token := l_msg_token||p_city||',';
131 END IF;
132 IF p_state IS NOT NULL and p_state <> fnd_api.g_miss_char THEN
133 l_msg_token := l_msg_token||p_state||',';
134 END IF;
135 IF p_country IS NOT NULL and p_country <> fnd_api.g_miss_char THEN
136 l_msg_token := l_msg_token||p_country||',';
137 END IF;
138 l_msg_token := substrb(l_msg_token,1,instrb(l_msg_token,',',-1)-1);
139 FND_MESSAGE.SET_TOKEN('VALUE', l_msg_token);
140 --FND_MESSAGE.SET_TOKEN('VALUE', p_postal_code||','||p_city||','||p_state||','||p_country);
141 ------End of Bug no: 3565475----------------
142 FND_MSG_PUB.ADD;
143 RAISE FND_API.G_EXC_ERROR;
144 END; -- compare country, no state
145 END; -- compare city, no state
146 END; -- compare all, no state
147
148 ELSE -- if state is passed
149
150 BEGIN -- compare all
151 SELECT timezone_id
152 INTO x_timezone_id
153 FROM HZ_TIMEZONE_MAPPING
154 WHERE postal_code = p_postal_code
155 AND upper(city) = upper(p_city)
156 AND state = p_state
157 AND country = p_country;
158 EXCEPTION WHEN NO_DATA_FOUND THEN --compare city, state, country
159 BEGIN
160 SELECT timezone_id
161 INTO x_timezone_id
162 FROM HZ_TIMEZONE_MAPPING
163 WHERE postal_code is null
164 AND upper(city) = upper(p_city)
165 AND state = p_state
166 AND country = p_country;
167 EXCEPTION WHEN NO_DATA_FOUND THEN --compare state, country
168 BEGIN
169 SELECT timezone_id
170 INTO x_timezone_id
171 FROM HZ_TIMEZONE_MAPPING
172 WHERE postal_code is null
173 AND city is null
174 AND state = p_state
175 AND country = p_country;
176 EXCEPTION WHEN NO_DATA_FOUND THEN --compare country
177 BEGIN
178 SELECT timezone_id
179 INTO x_timezone_id
180 FROM HZ_TIMEZONE_MAPPING
181 WHERE postal_code is null
182 AND city is null
183 AND state is null
184 AND country = p_country;
185 EXCEPTION WHEN NO_DATA_FOUND THEN
186 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
187 FND_MESSAGE.SET_TOKEN('RECORD', 'mapping');
188 --------Bug no: 3565475--------------------
189 IF p_postal_code IS NOT NULL and p_postal_code <> fnd_api.g_miss_char THEN
190 l_msg_token := p_postal_code||',';
191 END IF;
192 IF p_city IS NOT NULL and p_city <> fnd_api.g_miss_char THEN
193 l_msg_token := l_msg_token||p_city||',';
194 END IF;
195 IF p_state IS NOT NULL and p_state <> fnd_api.g_miss_char THEN
196 l_msg_token := l_msg_token||p_state||',';
197 END IF;
198 IF p_country IS NOT NULL and p_country <> fnd_api.g_miss_char THEN
199 l_msg_token := l_msg_token||p_country||',';
200 END IF;
201 l_msg_token := substrb(l_msg_token,1,instrb(l_msg_token,',',-1)-1);
202 FND_MESSAGE.SET_TOKEN('VALUE', l_msg_token);
203 --FND_MESSAGE.SET_TOKEN('VALUE', p_postal_code||','||p_city||','||p_state||','||p_country);
204 ------End of Bug no: 3565475----------------
205 FND_MSG_PUB.ADD;
206 RAISE FND_API.G_EXC_ERROR;
207 END; --compare country
208 END; --compare state, country
209 END; --compare city, state, country
210 END; --compare all
211 END IF; --if state is passed
212 END; --if postal code and country match
213
214 --Standard call to get message count and if count is 1, get message info.
215 FND_MSG_PUB.Count_And_Get(
216 p_encoded => FND_API.G_FALSE,
217 p_count => x_msg_count,
218 p_data => x_msg_data);
219
220 EXCEPTION
221 WHEN FND_API.G_EXC_ERROR THEN
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 FND_MSG_PUB.Count_And_Get(
224 p_encoded => FND_API.G_FALSE,
225 p_count => x_msg_count,
226 p_data => x_msg_data);
227 /* comment out arp_util.debug for fixing bug 3655764
228 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
229 arp_util.debug('x_msg_data '|| x_msg_data);
230 arp_util.debug('EXC');
231 arp_util.debug('error code : '|| to_char(SQLCODE));
232 arp_util.debug('error text : '|| SQLERRM); */
233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 FND_MSG_PUB.Count_And_Get(
236 p_encoded => FND_API.G_FALSE,
237 p_count => x_msg_count,
238 p_data => x_msg_data);
239 /* comment out arp_util.debug for fixing bug 3655764
240 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
241 arp_util.debug('x_msg_data '|| x_msg_data);
242 arp_util.debug('UNEXC'); */
243 WHEN OTHERS THEN
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245 FND_MSG_PUB.Count_And_Get(
246 p_encoded => FND_API.G_FALSE,
247 p_count => x_msg_count,
248 p_data => x_msg_data);
249 /* comment out arp_util.debug for fixing bug 3655764
250 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
251 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
252 arp_util.debug('x_msg_data '|| x_msg_data);
253 arp_util.debug('OTHERS');
254 arp_util.debug('error code : '|| to_char(SQLCODE));
255 arp_util.debug('error text : '|| SQLERRM); */
256 END Get_Timezone_ID;
257
258 /*===========================================================================+
259 | PROCEDURE |
260 | Get_Phone_Timezone_ID |
261 | |
262 | DESCRIPTION |
263 | Return timezone id by passing in area code and phone |
264 | country code. |
265 | parameter p_phone_prefix is for future use. No logic on it. |
266 | p_country_code needed only if non-unique row returned. |
267 | SCOPE - PUBLIC |
268 | |
269 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
270 | |
271 | ARGUMENTS : IN: |
272 | p_api_version |
273 | p_init_msg_list |
274 | p_phone_country_code (required) |
275 | p_area_code |
276 | p_phone_prefix (for future use) |
277 | p_country_code(only need to pass in if two countries |
278 | have same phone_country_code / area code |
279 | passed in) |
280 | OUT: |
281 | x_return_status |
282 | x_msg_count |
286 | |
283 | x_msg_data |
284 | x_timezone_id |
285 | IN/ OUT: |
287 | RETURNS : NONE |
288 | |
289 | NOTES |
290 | |
291 | MODIFICATION HISTORY |
292 | AWU 19-AUG-03 Created |
293 | |
294 +===========================================================================*/
295
296 Procedure Get_Phone_Timezone_ID (
297 p_api_version in number,
298 p_init_msg_list in varchar2,
299 p_phone_country_code in varchar2,
300 p_area_code in varchar2,
301 p_phone_prefix in varchar2,
302 p_country_code in varchar2,
303 x_timezone_id out nocopy number,
304 x_return_status out nocopy varchar2,
305 x_msg_count out nocopy number,
306 x_msg_data out nocopy varchar2
307 ) IS
308 l_api_name CONSTANT VARCHAR2(30) := 'Get_Phone_Timezone_ID';
309 l_api_version CONSTANT NUMBER := 1.0;
310
311 cursor get_tz_by_pcc_csr is
312 select timezone_id
313 from hz_phone_country_codes
314 where phone_country_code = p_phone_country_code;
315
316 cursor get_tz_by_pcc_cc_csr is
317 select timezone_id
318 from hz_phone_country_codes
319 where territory_code = p_country_code
320 and phone_country_code = p_phone_country_code;
321
322 cursor get_country_tz_count_csr is
323 select count(*)
324 from hz_phone_country_codes
325 where phone_country_code = p_phone_country_code;
326
327 cursor get_area_code_tzone_csr is
328 select timezone_id
329 from hz_phone_area_codes
330 where phone_country_code = p_phone_country_code
331 and area_code = p_area_code;
332
333 cursor get_area_code_count_csr is
334 select count(*)
335 from hz_phone_area_codes
336 where phone_country_code = p_phone_country_code
337 and area_code = p_area_code;
338
339 cursor get_area_code_tz_csr is
340 select timezone_id
341 from hz_phone_area_codes
342 where phone_country_code = p_phone_country_code
343 and area_code = p_area_code
344 and territory_code = p_country_code;
345
346 l_count number := 0;
347 l_tz_count number := 0;
348
349 BEGIN
350
351 --Standard call to check for call compatibility.
352 IF NOT FND_API.Compatible_API_Call(
353 l_api_version,
354 p_api_version,
355 l_api_name,
356 G_PKG_NAME)
357 THEN
358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 END IF;
360
361 --Initialize message list if p_init_msg_list is set to TRUE.
362 IF FND_API.to_Boolean(p_init_msg_list) THEN
363 FND_MSG_PUB.initialize;
364 END IF;
365
366 --Initialize API return status to success.
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368 x_timezone_id := null;
369
370 if p_phone_country_code is null
371 then
372 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
373 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_phone_country_code' );
374 FND_MSG_PUB.ADD;
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 RAISE FND_API.G_EXC_ERROR;
377 else -- phone_country_code is not null
378 if p_area_code is not null
379 then
380 if p_country_code is not null
381 then
382 open get_area_code_tz_csr;
383 fetch get_area_code_tz_csr into x_timezone_id;
384 close get_area_code_tz_csr;
385
386 else -- p_country_code is null
387 open get_area_code_count_csr;
388 fetch get_area_code_count_csr into l_tz_count;
389 close get_area_code_count_csr;
390
391 if l_tz_count >1 -- need country code to be passed in
392 then
393 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
394 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_country_code' );
395 FND_MSG_PUB.ADD;
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 RAISE FND_API.G_EXC_ERROR;
398 elsif l_tz_count = 1
399 then
400 open get_area_code_tzone_csr;
401 fetch get_area_code_tzone_csr into x_timezone_id;
402 close get_area_code_tzone_csr;
403 end if;
404 end if; -- country code is not null
405 end if; -- p_area_code is not null
406
407 -- other case such as l_tz_count = 0 or area_code not passed in, then logic below
408
409 if x_timezone_id is null
410 then
411 open get_country_tz_count_csr;
412 fetch get_country_tz_count_csr into l_count;
413 close get_country_tz_count_csr;
414 if l_count = 1
415 then
416 open get_tz_by_pcc_csr;
417 fetch get_tz_by_pcc_csr into x_timezone_id;
418 close get_tz_by_pcc_csr;
419 elsif l_count > 1
420 then
421 if p_country_code is not null
422 then
423 open get_tz_by_pcc_cc_csr;
424 fetch get_tz_by_pcc_cc_csr into x_timezone_id;
425 close get_tz_by_pcc_cc_csr;
426 if x_timezone_id is null
427 then
428 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
429 FND_MESSAGE.SET_TOKEN('COLUMN','phone_country_code+country_code');
430 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PHONE_COUNTRY_CODES');
431 FND_MSG_PUB.ADD;
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 RAISE FND_API.G_EXC_ERROR;
434 end if; -- x_timezone_id is null
435
436 else
437 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
438 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'country_code' );
442 end if;
439 FND_MSG_PUB.ADD;
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 RAISE FND_API.G_EXC_ERROR;
443 elsif l_count = 0
444 then
445 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
446 FND_MESSAGE.SET_TOKEN('COLUMN','PHONE_COUNTRY_CODE');
447 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PHONE_COUNTRY_CODES');
448 FND_MSG_PUB.ADD;
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 RAISE FND_API.G_EXC_ERROR;
451 end if; -- l_count = 1
452 end if; -- if timezone_id is null
453 end if;
454 FND_MSG_PUB.Count_And_Get(
455 p_encoded => FND_API.G_FALSE,
456 p_count => x_msg_count,
457 p_data => x_msg_data);
458 EXCEPTION
459 WHEN FND_API.G_EXC_ERROR THEN
460 x_return_status := FND_API.G_RET_STS_ERROR;
461
462 FND_MSG_PUB.Count_And_Get(
463 p_encoded => FND_API.G_FALSE,
464 p_count => x_msg_count,
465 p_data => x_msg_data );
466
467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469
470 FND_MSG_PUB.Count_And_Get(
471 p_encoded => FND_API.G_FALSE,
472 p_count => x_msg_count,
473 p_data => x_msg_data );
474
475 WHEN OTHERS THEN
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477
478 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
479 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
480 FND_MSG_PUB.ADD;
481
482 FND_MSG_PUB.Count_And_Get(
483 p_encoded => FND_API.G_FALSE,
484 p_count => x_msg_count,
485 p_data => x_msg_data );
486
487 end get_phone_timezone_id;
488
489
490 /*===========================================================================+
491 | PROCEDURE |
492 | Get_Timezone_GMT_Deviation |
493 | |
494 | DESCRIPTION |
495 | Get Timezone GMT Deviation, Name given the reference date |
496 | and Timezone_id |
497 | SCOPE - PUBLIC |
498 | |
499 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
500 | |
501 | ARGUMENTS : IN: |
502 | p_api_version |
503 | p_init_msg_list |
504 | p_date |
505 | p_timezone_id |
506 | OUT: |
507 | x_return_status |
508 | x_msg_count |
509 | x_msg_data |
510 | x_GMT_deviation |
511 | x_global_timezone_name |
512 | x_name |
513 | IN/ OUT: |
514 | |
515 | RETURNS : NONE |
516 | |
517 | NOTES |
518 | |
519 | MODIFICATION HISTORY |
520 | Stephanie Zhang 23-AUG-99 Created |
521 | |
522 +===========================================================================*/
523 Procedure Get_Timezone_GMT_Deviation(
524 p_api_version in number,
525 p_init_msg_list in varchar2,
526 p_timezone_id in number,
527 p_date in date,
528 x_GMT_deviation out nocopy number,
529 x_global_timezone_name out nocopy varchar2,
530 x_name out nocopy varchar2,
531 x_return_status out nocopy varchar2,
532 x_msg_count out nocopy number,
533 x_msg_data out nocopy varchar2)
534 IS
535 l_api_name CONSTANT VARCHAR2(30) := 'Get_Timezone_GMT_Deviation';
536 l_api_version CONSTANT NUMBER := 1.0;
537 l_GMT_deviation number;
538 l_dst_flag varchar2(1);
539 l_date_in_gmt date;
540 l_timezone_code varchar2(50);
541 BEGIN
542
543 --Standard call to check for call compatibility.
544 IF NOT FND_API.Compatible_API_Call(
545 l_api_version,
546 p_api_version,
547 l_api_name,
548 G_PKG_NAME)
549 THEN
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 END IF;
552
553 --Initialize message list if p_init_msg_list is set to TRUE.
554 IF FND_API.to_Boolean(p_init_msg_list) THEN
555 FND_MSG_PUB.initialize;
556 END IF;
557
558 --Initialize API return status to success.
559 x_return_status := FND_API.G_RET_STS_SUCCESS;
560
561 /* this should be changed in the next release to get the global_timezone_name from the tzabbrev
562 of v$timezone_names which is the database table of timezones, but we will leave it for now
566
563 because some product may be using the %s substitution capability in order to turn a P%sT into
564 PST or PDT based on dst - although I doubt it
565 */
567 BEGIN
568 SELECT H.GLOBAL_TIMEZONE_NAME,
569 F.NAME,
570 F.GMT_OFFSET,
571 F.DAYLIGHT_SAVINGS_FLAG,
572 F.TIMEZONE_CODE
573 INTO x_global_timezone_name,
574 x_name,
575 l_GMT_deviation,
576 l_dst_flag,
577 l_timezone_code
578 FROM HZ_TIMEZONES H, FND_TIMEZONES_VL F
579 WHERE H.timezone_id = p_timezone_id
580 AND H.timezone_id = F.upgrade_tz_id;
581
582 EXCEPTION WHEN NO_DATA_FOUND THEN
583 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
584 FND_MESSAGE.SET_TOKEN('RECORD', 'timezone');
585 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_timezone_id));
586 FND_MSG_PUB.ADD;
587 RAISE FND_API.G_EXC_ERROR;
588 END;
589
590 IF (l_dst_flag = 'N')THEN
591 x_GMT_deviation := l_GMT_Deviation;
592 ELSIF (l_dst_flag = 'Y') then
593
594 /* Find the gmt deviation by converting the date to gmt and then finding the difference
595 between the original date and the date in gmt. This routine will fail with an unexpected
596 error if the timezone_code that is stored in fnd_timezones does not match the tzname in
597 v$timezone_names. Check that the customer (or db instance) is running with the large timezone.dat
598 file from the database. There are two versions of the timezone.dat file available from the db
599 and one has the whole list of timezones and the other has a smaller list. Only the larger file
600 has all the timezones which are supported in apps (present in fnd_timezones)
601 */
602 l_date_in_gmt := to_timestamp_tz(to_char(p_date,'YYYY-MM-DD HH24:MI:SS') || ' ' || l_timezone_code,
603 'YYYY-MM-DD HH24:MI:SS TZR') at time zone 'GMT';
604 x_GMT_deviation := to_char((p_date-l_date_in_gmt)*24);
605
606 END IF;
607
608 --Standard call to get message count and if count is 1, get message info.
609 FND_MSG_PUB.Count_And_Get(
610 p_encoded => FND_API.G_FALSE,
611 p_count => x_msg_count,
612 p_data => x_msg_data);
613 EXCEPTION
614 WHEN FND_API.G_EXC_ERROR THEN
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 FND_MSG_PUB.Count_And_Get(
617 p_encoded => FND_API.G_FALSE,
618 p_count => x_msg_count,
619 p_data => x_msg_data);
620 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
621 arp_util.debug('x_msg_data '|| x_msg_data);
622 arp_util.debug('EXC');
623 arp_util.debug('error code : '|| to_char(SQLCODE));
624 arp_util.debug('error text : '|| SQLERRM);
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 FND_MSG_PUB.Count_And_Get(
628 p_encoded => FND_API.G_FALSE,
629 p_count => x_msg_count,
630 p_data => x_msg_data);
631 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
632 arp_util.debug('x_msg_data '|| x_msg_data);
633 arp_util.debug('UNEXC');
634 WHEN OTHERS THEN
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 FND_MSG_PUB.Count_And_Get(
637 p_encoded => FND_API.G_FALSE,
638 p_count => x_msg_count,
639 p_data => x_msg_data);
640 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
641 arp_util.debug('x_msg_data '|| x_msg_data);
642 arp_util.debug('OTHERS');
643 arp_util.debug('error code : '|| to_char(SQLCODE));
644 arp_util.debug('error text : '|| SQLERRM);
645 END Get_Timezone_GMT_Deviation;
646
647 /*===========================================================================+
648 | FUNCTION |
649 | Convert_DateTime |
650 | |
651 | DESCRIPTION |
652 | Returns the datetime in the destination timezone, |
653 | given the source datetime, source |
654 | timezone_id and destination timezone_id |
655 | SCOPE - PUBLIC |
656 | |
657 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
658 | |
659 | ARGUMENTS : IN: |
660 | p_source_tz_id |
661 | p_dest_tz_id |
662 | p_source_day_time |
663 | |
664 | RETURNS : datetime |
665 | |
666 | NOTES |
667 | |
668 | MODIFICATION HISTORY |
669 | Kris Doherty 03-SEP-03 Created |
670 +===========================================================================*/
671 Function Convert_DateTime(
672 p_source_tz_id in number,
673 p_dest_tz_id in number,
674 p_source_day_time in date
675 ) RETURN DATE
676 IS
680 l_dest_datetime date;
677 l_dest_gmt_deviation number;
678 l_dest_tz_code varchar2(50);
679 l_source_tz_code varchar2(50);
681 l_return_status varchar2(1);
682 l_msg_count number;
683 l_msg_data varchar2(2000);
684
685 BEGIN
686 BEGIN
687 SELECT timezone_code
688 INTO l_dest_tz_code
689 FROM fnd_timezones_b
690 WHERE upgrade_tz_id = p_dest_tz_id;
691
692 SELECT timezone_code
693 INTO l_source_tz_code
694 FROM fnd_timezones_b
695 WHERE upgrade_tz_id = p_source_tz_id;
696
697 EXCEPTION WHEN NO_DATA_FOUND THEN
698 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
699 FND_MESSAGE.SET_TOKEN('RECORD', 'timezone');
700 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_dest_tz_id)||to_char(p_source_tz_id));
701 FND_MSG_PUB.ADD;
702 RAISE FND_API.G_EXC_ERROR;
703 END;
704
705 return fnd_timezone_pub.adjust_datetime(p_source_day_time, l_source_tz_code, l_dest_tz_code);
706
707 EXCEPTION WHEN OTHERS THEN
708 RAISE;
709 END Convert_DateTime;
710
711
712 /*===========================================================================+
713 | PROCEDURE |
714 | Get_Time |
715 | |
716 | DESCRIPTION |
717 | Get destination day time, given the source day time, source |
718 | timezone_id and destination timezone_id |
719 | SCOPE - PUBLIC |
720 | |
721 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
722 | |
723 | ARGUMENTS : IN: |
724 | p_api_version |
725 | p_init_msg_list |
726 | p_source_tz_id |
727 | p_dest_tz_id |
728 | p_source_day_time |
729 | OUT: |
730 | x_return_status |
731 | x_msg_count |
732 | x_msg_data |
733 | x_dest_day_time |
734 | IN/ OUT: |
735 | |
736 | RETURNS : NONE |
737 | |
738 | NOTES |
739 | |
740 | MODIFICATION HISTORY |
741 | Stephanie Zhang 23-AUG-99 Created |
742 | Kris Doherty 03-AUG-03 Modified to call Get_Time_and_Code |
743 | we need to keep this signature as is |
744 | for backward compatibility |
745 +===========================================================================*/
746 Procedure Get_Time(
747 p_api_version IN NUMBER,
748 p_init_msg_list in varchar2,
749 p_source_tz_id in number,
750 p_dest_tz_id in number,
751 p_source_day_time in date,
752 x_dest_day_time out nocopy date,
753 x_return_status out nocopy VARCHAR2,
754 x_msg_count out nocopy NUMBER,
755 x_msg_data out nocopy VARCHAR2)
756 IS
757 l_api_name CONSTANT VARCHAR2(30) := 'Get_Time';
758 l_api_version CONSTANT NUMBER := 1.0;
759 l_dest_gmt_deviation number;
760 l_dest_tz_code varchar2(5);
761
762 BEGIN
763
764 --Standard call to check for call compatibility.
765 IF NOT FND_API.Compatible_API_Call(
766 l_api_version,
767 p_api_version,
768 l_api_name,
769 G_PKG_NAME)
770 THEN
771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
772 END IF;
773
774 --Initialize message list if p_init_msg_list is set to TRUE.
775 IF FND_API.to_Boolean(p_init_msg_list) THEN
776 FND_MSG_PUB.initialize;
777 END IF;
778
779 --Initialize API return status to success.
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781
782 -- Validate that non null parameters have been passed
783 IF ((p_source_tz_id is null) OR
784 (p_dest_tz_id is null) OR
785 (p_source_day_time is null))
786 THEN
787 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NULL_PARAMETER_RECEIVED');
788 FND_MESSAGE.SET_TOKEN('PROC','Get_Time');
789 FND_MESSAGE.SET_TOKEN('P_SOURCE_TZ_ID',p_source_tz_id);
790 FND_MESSAGE.SET_TOKEN('P_DEST_TZ_ID',p_dest_tz_id);
791 FND_MESSAGE.SET_TOKEN('P_SOURCE_DAY_TIME',p_source_day_time);
792 FND_MSG_PUB.ADD;
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795 END IF;
796
797 x_dest_day_time := hz_timezone_pub.convert_datetime(p_source_tz_id, p_dest_tz_id, p_source_day_time);
798
799 EXCEPTION
800 WHEN FND_API.G_EXC_ERROR THEN
804 p_count => x_msg_count,
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 FND_MSG_PUB.Count_And_Get(
803 p_encoded => FND_API.G_FALSE,
805 p_data => x_msg_data);
806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
808 FND_MSG_PUB.Count_And_Get(
809 p_encoded => FND_API.G_FALSE,
810 p_count => x_msg_count,
811 p_data => x_msg_data);
812 WHEN OTHERS THEN
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
814 FND_MSG_PUB.Count_And_Get(
815 p_encoded => FND_API.G_FALSE,
816 p_count => x_msg_count,
817 p_data => x_msg_data);
818 END Get_Time;
819
820 /*===========================================================================+
821 | PROCEDURE |
822 | Get_Time_and_Code |
823 | |
824 | DESCRIPTION |
825 | Get destination day time, destination tz short code (which |
826 | will be different if day is in daylight savings or not), |
827 | and the destination tz offset from GMT (again for the given |
828 | day as this also changes based on daylight savings) |
829 | given the source day time, source |
830 | timezone_id and destination timezone_id |
831 | SCOPE - PUBLIC |
832 | |
833 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
834 | |
835 | ARGUMENTS : IN: |
836 | p_api_version |
837 | p_init_msg_list |
838 | p_source_tz_id |
839 | p_dest_tz_id |
840 | p_source_day_time |
841 | OUT: |
842 | x_dest_day_time |
843 | x_dest_tz_code |
844 | x_dest_gmt_deviation |
845 | x_return_status |
846 | x_msg_count |
847 | x_msg_data |
848
849 | IN/ OUT: |
850 | |
851 | RETURNS : NONE |
852 | |
853 | NOTES |
854 | |
855 | MODIFICATION HISTORY |
856 | Kris Baird 03-AUG-03 Created |
857 | |
858 +===========================================================================*/
859 Procedure Get_Time_and_Code(
860 p_api_version IN NUMBER,
861 p_init_msg_list in varchar2,
862 p_source_tz_id in number,
863 p_dest_tz_id in number,
864 p_source_day_time in date,
865 x_dest_day_time out nocopy date,
866 x_dest_tz_code out nocopy varchar2,
867 x_dest_gmt_deviation out nocopy number,
868 x_return_status out nocopy VARCHAR2,
869 x_msg_count out nocopy NUMBER,
870 x_msg_data out nocopy VARCHAR2)
871 IS
872 l_api_name CONSTANT VARCHAR2(30) := 'Get_Time_and_Code';
873 l_api_version CONSTANT NUMBER := 1.0;
874 l_dest_day_time date;
875 std_GMT_deviation number;
876 l_global_timezone_name varchar2(50);
877 l_name varchar2(80);
878 s_status varchar2(1);
879 s_msg_count number;
880 s_msg_data varchar2(2000);
881 d_GMT_deviation number;
882 d_dst_flag varchar2(1);
883 l_standard_short_code varchar2(5);
884 l_daylight_short_code varchar2(5);
885
886 BEGIN
887
888 --Standard call to check for call compatibility.
889 IF NOT FND_API.Compatible_API_Call(
890 l_api_version,
891 p_api_version,
892 l_api_name,
893 G_PKG_NAME)
894 THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 END IF;
897
898 --Initialize message list if p_init_msg_list is set to TRUE.
899 IF FND_API.to_Boolean(p_init_msg_list) THEN
900 FND_MSG_PUB.initialize;
901 END IF;
902
903 --Initialize API return status to success.
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905
906 /* convert p_source_day_time to new timezone */
907 l_dest_day_time := hz_timezone_pub.convert_datetime(p_source_tz_id, p_dest_tz_id, p_source_day_time);
908
909 /* now figure out if this is in dst or not in order to return the correct code */
913 STANDARD_TIME_SHORT_CODE,
910 /* if the gmt offset for this new timezone is the standard offset then it is not dst */
911
912 SELECT GMT_DEVIATION_HOURS,
914 DAYLIGHT_SAVINGS_SHORT_CODE
915 INTO std_GMT_deviation,
916 l_standard_short_code,
917 l_daylight_short_code
918 FROM HZ_TIMEZONES
919 WHERE timezone_id = p_dest_tz_id;
920
921 /* Get GMT Deviation for the dest day in the destination timezone */
922
923 Get_Timezone_GMT_Deviation(1.0, 'F', p_dest_tz_id, l_dest_day_time,
924 d_GMT_deviation, l_global_timezone_name, l_name, s_status,
925 s_msg_count, s_msg_data);
926 IF(s_status <> FND_API.G_RET_STS_SUCCESS )THEN
927 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RETURN_ERROR');
928 FND_MESSAGE.SET_TOKEN('PROC', 'GET_GMT_DEVIATION');
929 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_dest_tz_id)||','
930 ||to_char(l_dest_day_time, 'MM-DD-RR')||','
931 ||to_char(p_dest_tz_id));
932 FND_MSG_PUB.ADD;
933 RAISE FND_API.G_EXC_ERROR;
934 END IF;
935
936 /* if the current offset is different than the standard offset, then it must be daylight savings */
937 IF (std_GMT_deviation <> d_GMT_deviation) THEN
938 x_dest_tz_code := l_daylight_short_code;
939 ELSE
940 x_dest_tz_code := l_standard_short_code;
941 END IF;
942
943 x_dest_gmt_deviation := d_GMT_deviation;
944 x_dest_day_time := l_dest_day_time;
945
946 --Standard call to get message count and if count is 1, get message info.
947 FND_MSG_PUB.Count_And_Get(
948 p_encoded => FND_API.G_FALSE,
949 p_count => x_msg_count,
950 p_data => x_msg_data);
951
952 EXCEPTION
953 WHEN FND_API.G_EXC_ERROR THEN
954 x_return_status := FND_API.G_RET_STS_ERROR;
955 FND_MSG_PUB.Count_And_Get(
956 p_encoded => FND_API.G_FALSE,
957 p_count => x_msg_count,
958 p_data => x_msg_data);
959 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
961 FND_MSG_PUB.Count_And_Get(
962 p_encoded => FND_API.G_FALSE,
963 p_count => x_msg_count,
964 p_data => x_msg_data);
965 WHEN OTHERS THEN
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 FND_MSG_PUB.Count_And_Get(
968 p_encoded => FND_API.G_FALSE,
969 p_count => x_msg_count,
970 p_data => x_msg_data);
971 END Get_Time_and_Code;
972
973
974 /*===========================================================================+
975 | PROCEDURE |
976 | Get_Primary_Zone |
977 | |
978 | DESCRIPTION |
979 | Takes in an offset from GMT and a daylight savings rule |
980 | and returns a default primary timezone which meets that |
981 | definition. This is to facilitate the automatic mapping |
982 | of an address to a timezone in the future (when we integrate |
983 | with the spatial data which will have a more accurate |
984 | timezone boundry definition |
985 | SCOPE - PUBLIC |
986 | |
987 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
988 | |
989 | ARGUMENTS : IN: |
990 | p_api_version |
991 | p_init_msg_list |
992 | p_gmt_deviation_hours |
993 | p_daylight_savings_time_flag |
994 | p_begin_dst_month |
995 | p_begin_dst_day |
996 | p_begin_dst_week_of_month |
997 | p_begin_dst_day_of_week |
998 | p_begin_dst_hour |
999 | p_end_dst_month |
1000 | p_end_dst_day |
1001 | p_end_dst_week_of_month |
1002 | p_end_dst_day_of_week |
1003 | p_end_dst_hour |
1004 | |
1005 | OUT: |
1006 | x_timezone_id |
1007 | x_timezone_name |
1008 | x_timezone_code |
1009 | x_return_status |
1010 | x_msg_count |
1011 | x_msg_data |
1012 |
1013 | IN/ OUT: |
1014 | |
1015 | RETURNS : NONE |
1016 | |
1017 | NOTES |
1018 | |
1019 | MODIFICATION HISTORY |
1020 | Kris Baird 03-AUG-03 Created |
1021 | |
1022 +===========================================================================*/
1023
1027 p_gmt_deviation_hours in number,
1024 PROCEDURE Get_Primary_Zone (
1025 p_api_version in number,
1026 p_init_msg_list in varchar2,
1028 p_daylight_savings_time_flag in varchar2,
1029 p_begin_dst_month in varchar2,
1030 p_begin_dst_day in number,
1031 p_begin_dst_week_of_month in number,
1032 p_begin_dst_day_of_week in number,
1033 p_begin_dst_hour in number,
1034 p_end_dst_month in varchar2,
1035 p_end_dst_day in number,
1036 p_end_dst_week_of_month in number,
1037 p_end_dst_day_of_week in number,
1038 p_end_dst_hour in number,
1039 x_timezone_id out nocopy number,
1040 x_timezone_name out nocopy varchar2,
1041 x_timezone_code out nocopy varchar2,
1042 x_return_status out nocopy varchar2,
1043 x_msg_count out nocopy number,
1044 x_msg_data out nocopy varchar2)
1045 IS
1046 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Zone';
1047 l_api_version CONSTANT NUMBER := 1.0;
1048
1049 BEGIN
1050
1051 --Standard call to check for call compatibility.
1052 IF NOT FND_API.Compatible_API_Call(
1053 l_api_version,
1054 p_api_version,
1055 l_api_name,
1056 G_PKG_NAME)
1057 THEN
1058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1059 END IF;
1060 BEGIN
1061
1062 select h.timezone_id,
1063 f.name,
1064 f.timezone_code
1065 into x_timezone_id,
1066 x_timezone_name,
1067 x_timezone_code
1068 from hz_timezones_vl h, fnd_timezones_vl f
1069 where h.timezone_id = f.upgrade_tz_id
1070 and h.gmt_deviation_hours = p_gmt_deviation_hours
1071 and h.daylight_savings_time_flag = p_daylight_savings_time_flag
1072 and nvl(h.begin_dst_month,'-99') = nvl(p_begin_dst_month,'-99')
1073 and nvl(h.begin_dst_day,-99) = nvl(p_begin_dst_day,-99)
1074 and nvl(h.begin_dst_week_of_month,-99) = nvl(p_begin_dst_week_of_month,-99)
1075 and nvl(h.begin_dst_day_of_week,-99) = nvl(p_begin_dst_day_of_week,-99)
1076 and nvl(begin_dst_hour,-99) = nvl(p_begin_dst_hour,-99)
1077 and nvl(h.end_dst_month,'-99') = nvl(p_end_dst_month,'-99')
1078 and nvl(h.end_dst_day,-99) = nvl(p_end_dst_day,-99)
1079 and nvl(h.end_dst_week_of_month,-99) = nvl(p_end_dst_week_of_month,-99)
1080 and nvl(h.end_dst_day_of_week,-99) = nvl(p_end_dst_day_of_week,-99)
1081 and nvl(end_dst_hour,-99) = nvl(p_end_dst_hour,-99)
1082 and h.primary_zone_flag = 'Y';
1083
1084 EXCEPTION WHEN NO_DATA_FOUND THEN
1085 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1086 FND_MESSAGE.SET_TOKEN('RECORD', 'zone mapping');
1087 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_gmt_deviation_hours)||
1088 ','||p_daylight_savings_time_flag||','
1089 ||p_begin_dst_month||','||to_char(p_begin_dst_day)
1090 ||to_char(p_begin_dst_week_of_month)||','||to_char(p_begin_dst_day_of_week));
1091 FND_MSG_PUB.ADD;
1092 RAISE FND_API.G_EXC_ERROR;
1093
1094 END;
1095
1096 --Standard call to get message count and if count is 1, get message info.
1097 FND_MSG_PUB.Count_And_Get(
1098 p_encoded => FND_API.G_FALSE,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data);
1101
1102 EXCEPTION
1103 WHEN FND_API.G_EXC_ERROR THEN
1104 x_return_status := FND_API.G_RET_STS_ERROR;
1105 FND_MSG_PUB.Count_And_Get(
1106 p_encoded => FND_API.G_FALSE,
1107 p_count => x_msg_count,
1108 p_data => x_msg_data);
1109 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
1110 arp_util.debug('x_msg_data '|| x_msg_data);
1111 arp_util.debug('EXC');
1112 arp_util.debug('error code : '|| to_char(SQLCODE));
1113 arp_util.debug('error text : '|| SQLERRM);
1114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1116 FND_MSG_PUB.Count_And_Get(
1117 p_encoded => FND_API.G_FALSE,
1118 p_count => x_msg_count,
1119 p_data => x_msg_data);
1120 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
1121 arp_util.debug('x_msg_data '|| x_msg_data);
1122 arp_util.debug('UNEXC');
1123 WHEN OTHERS THEN
1124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1125 FND_MSG_PUB.Count_And_Get(
1126 p_encoded => FND_API.G_FALSE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data);
1129 arp_util.debug('x_msg_count ' || to_char(x_msg_count));
1130 arp_util.debug('x_msg_data '|| x_msg_data);
1131 arp_util.debug('OTHERS');
1132 arp_util.debug('error code : '|| to_char(SQLCODE));
1133 arp_util.debug('error text : '|| SQLERRM);
1134 END Get_Primary_Zone;
1135
1136
1137 /*===========================================================================+
1138 | PROCEDURE |
1139 | Get_Timezone_Short_Code |
1140 | |
1141 | DESCRIPTION |
1145 | SCOPE - PUBLIC |
1142 | Takes in a datetime and a timezone id or a timezone_code |
1143 | and returns the gmt deviation, tz short code for that day |
1144 | and the tz name for display to an end user. |
1146 | |
1147 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1148 | |
1149 | ARGUMENTS : IN: |
1150 | p_api_version |
1151 | p_init_msg_list |
1152 | p_timezone_id |
1153 | p_timezone_code |
1154 | p_date |
1155 | |
1156 | OUT: |
1157 | x_gmt_deviation |
1158 | x_timezone_short_code |
1159 | x_timezone_name |
1160 | x_return_status |
1161 | x_msg_count |
1162 | x_msg_data |
1163 |
1164 | IN/ OUT: |
1165 | |
1166 | RETURNS : NONE |
1167 | |
1168 | NOTES |
1169 | |
1170 | MODIFICATION HISTORY |
1171 | Kris Baird 03-AUG-03 Created |
1172 | |
1173 +===========================================================================*/
1174 Procedure Get_Timezone_Short_Code
1175 (
1176 p_api_version in number,
1177 p_init_msg_list in varchar2,
1178 p_timezone_id in number,
1179 p_timezone_code in varchar2,
1180 p_date in date,
1181 x_gmt_deviation out nocopy number,
1182 x_timezone_short_code out nocopy varchar2,
1183 x_name out nocopy varchar2,
1184 x_return_status out nocopy varchar2,
1185 x_msg_count out nocopy number,
1186 x_msg_data out nocopy varchar2)
1187 IS
1188 l_api_name CONSTANT VARCHAR2(30) := 'Get_Timezone_Short_Code';
1189 l_api_version CONSTANT NUMBER := 1.0;
1190 l_gmt_deviation number;
1191 l_current_GMT_deviation number;
1192 l_date_in_gmt date;
1193 l_standard_short_code varchar2(5);
1194 l_daylight_short_code varchar2(5);
1195 l_timezone_id number;
1196 l_timezone_code varchar2(50);
1197
1198 BEGIN
1199
1200 --Standard call to check for call compatibility.
1201 IF NOT FND_API.Compatible_API_Call(
1202 l_api_version,
1203 p_api_version,
1204 l_api_name,
1205 G_PKG_NAME)
1206 THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END IF;
1209
1210 --Initialize message list if p_init_msg_list is set to TRUE.
1211 IF FND_API.to_Boolean(p_init_msg_list) THEN
1212 FND_MSG_PUB.initialize;
1213 END IF;
1214
1215 --Initialize API return status to success.
1216 x_return_status := FND_API.G_RET_STS_SUCCESS;
1217
1218 IF (p_timezone_id is not null) THEN
1219 BEGIN
1220
1221 SELECT F.GMT_OFFSET,
1222 H.STANDARD_TIME_SHORT_CODE,
1223 H.DAYLIGHT_SAVINGS_SHORT_CODE,
1224 F.NAME,
1225 F.TIMEZONE_CODE
1226 INTO l_gmt_deviation,
1227 l_standard_short_code,
1228 l_daylight_short_code,
1229 x_name,
1230 l_timezone_code
1231 FROM HZ_TIMEZONES_VL H, FND_TIMEZONES_VL F
1232 WHERE H.timezone_id = p_timezone_id
1233 AND H.timezone_id = F.upgrade_tz_id;
1234
1235 EXCEPTION WHEN NO_DATA_FOUND THEN
1236 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1237 FND_MESSAGE.SET_TOKEN('RECORD', 'timezone id');
1238 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_timezone_id));
1239 FND_MSG_PUB.ADD;
1240 RAISE FND_API.G_EXC_ERROR;
1241 END;
1242 ELSE
1243 BEGIN
1244 SELECT f.GMT_OFFSET,
1245 h.STANDARD_TIME_SHORT_CODE,
1246 h.DAYLIGHT_SAVINGS_SHORT_CODE,
1247 f.NAME
1248 INTO l_gmt_deviation,
1249 l_standard_short_code,
1250 l_daylight_short_code,
1251 x_name
1252 FROM FND_TIMEZONES_VL f, HZ_TIMEZONES_VL h
1253 WHERE f.timezone_code = p_timezone_code
1254 AND f.upgrade_tz_id = h.timezone_id;
1255
1256 l_timezone_code := p_timezone_code;
1257
1258 EXCEPTION WHEN NO_DATA_FOUND THEN
1259 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1260 FND_MESSAGE.SET_TOKEN('RECORD', 'timezone code');
1261 FND_MESSAGE.SET_TOKEN('VALUE', p_timezone_code);
1262 FND_MSG_PUB.ADD;
1263 RAISE FND_API.G_EXC_ERROR;
1264 END;
1265
1266 END IF;
1267
1268 /* find out what this date is in gmt so I can then find the current offset (since the db does not expose the
1272 l_date_in_gmt := to_timestamp_tz(to_char(p_date,'YYYY-MM-DD HH24:MI:SS') || ' ' || l_timezone_code,
1269 offset values, we have to do a little math to calculate it
1270 */
1271
1273 'YYYY-MM-DD HH24:MI:SS TZR') at time zone 'GMT';
1274 l_current_GMT_deviation := to_char((p_date-l_date_in_gmt)*24);
1275
1276
1277 /* if the current offset is different than the standard offset, then it must be daylight savings */
1278
1279 IF (l_GMT_deviation <> l_current_GMT_deviation) THEN
1280 x_timezone_short_code := l_daylight_short_code;
1281 ELSE
1282 x_timezone_short_code := l_standard_short_code;
1283 END IF;
1284
1285 x_gmt_deviation := l_current_GMT_deviation;
1286
1287 --Standard call to get message count and if count is 1, get message info.
1288 FND_MSG_PUB.Count_And_Get(
1289 p_encoded => FND_API.G_FALSE,
1290 p_count => x_msg_count,
1291 p_data => x_msg_data);
1292
1293 EXCEPTION
1294 WHEN FND_API.G_EXC_ERROR THEN
1295 x_return_status := FND_API.G_RET_STS_ERROR;
1296 FND_MSG_PUB.Count_And_Get(
1297 p_encoded => FND_API.G_FALSE,
1298 p_count => x_msg_count,
1299 p_data => x_msg_data);
1300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 FND_MSG_PUB.Count_And_Get(
1303 p_encoded => FND_API.G_FALSE,
1304 p_count => x_msg_count,
1305 p_data => x_msg_data);
1306 WHEN OTHERS THEN
1307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1308 FND_MSG_PUB.Count_And_Get(
1309 p_encoded => FND_API.G_FALSE,
1310 p_count => x_msg_count,
1311 p_data => x_msg_data);
1312 END Get_Timezone_Short_Code;
1313
1314
1315 /*===========================================================================+
1316 | PROCEDURE |
1317 | Get_begin_end_dst_day_time |
1318 | |
1319 | DESCRIPTION |
1320 | THIS ROUTINE IS OBSOLETE AND SHOULD NO LONGER BE USED |
1321 | DAYLIGHT SAVINGS INFORMATION SHOULD BE TAKEN FROM THE DATABASE
1322 | TIMEZONE DEFINITIONS AND NOT FROM THE HZ DEFINITIONS |
1323 | Get the begin/end daylight saving day and time |
1324 | in date data type by constructing each component stored in
1325 | the timezone table, given the year and the timezone_id.
1326 | SCOPE - OBSOLETE |
1327 | |
1328 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1329 | |
1330 | ARGUMENTS : IN: |
1331 | p_year |
1332 | p_timezone_id |
1333 | OUT: |
1334 | x_begin_dst_date |
1335 | x_end_dst_date |
1336 | IN/ OUT: |
1337 | |
1338 | RETURNS : NONE |
1339 | |
1340 | NOTES |
1341 | |
1342 | MODIFICATION HISTORY |
1343 | Stephanie Zhang 23-AUG-99 Created |
1344 | |
1345 +===========================================================================*/
1346
1347 Procedure Get_begin_end_dst_day_time(
1348 p_year in varchar2,
1349 p_timezone_id in number,
1350 x_begin_dst_date out nocopy date,
1351 x_end_dst_date out nocopy date
1352 ) IS
1353 v_dst_flag varchar2(1);
1354 v_begin_dst_month varchar2(3);
1355 v_begin_dst_day number;
1356 v_begin_dst_week_of_m number;
1357 v_begin_dst_day_of_w number;
1358 v_begin_dst_hour number;
1359 v_end_dst_month varchar2(3);
1360 v_end_dst_day number;
1361 v_end_dst_week_of_m number;
1362 v_end_dst_day_of_w number;
1363 v_end_dst_hour number;
1364 v_date date:= null;
1365 BEGIN
1366 SELECT DAYLIGHT_SAVINGS_TIME_FLAG,
1367 LPAD(BEGIN_DST_MONTH,2,'0'),
1368 BEGIN_DST_DAY,
1369 BEGIN_DST_WEEK_OF_MONTH,
1370 BEGIN_DST_DAY_OF_WEEK,
1371 BEGIN_DST_HOUR,
1372 LPAD(END_DST_MONTH,2,'0'),
1373 END_DST_DAY,
1374 END_DST_WEEK_OF_MONTH,
1375 END_DST_DAY_OF_WEEK,
1376 END_DST_HOUR
1377 INTO v_dst_flag,
1378 v_begin_dst_month,
1379 v_begin_dst_day,
1380 v_begin_dst_week_of_m,
1381 v_begin_dst_day_of_w,
1382 v_begin_dst_hour,
1383 v_end_dst_month,
1384 v_end_dst_day,
1385 v_end_dst_week_of_m,
1386 v_end_dst_day_of_w,
1387 v_end_dst_hour
1388 FROM HZ_TIMEZONES_VL
1389 WHERE timezone_id = p_timezone_id;
1390
1391 IF(v_dst_flag = 'N')THEN
1392 x_begin_dst_date := null;
1393 x_end_dst_date := null;
1394 return;
1395 ELSIF(v_dst_flag = 'Y')THEN
1399 '-'||p_year||
1396 IF(v_begin_dst_day is not NULL)THEN
1397 x_begin_dst_date := to_date(v_begin_dst_month||
1398 '-'||to_char(v_begin_dst_day)||
1400 ' '||to_char(trunc(v_begin_dst_hour))||
1401 ' '||to_char(round((v_begin_dst_hour - trunc(v_begin_dst_hour))*60,2)),
1402 'MM-DD-YYYY HH24 MI');
1403 ELSE
1404 IF(v_begin_dst_week_of_m <> -1)THEN
1405 Get_date_from_W_and_D(p_year, v_begin_dst_month,
1406 to_char(v_begin_dst_week_of_m),
1407 to_char(v_begin_dst_day_of_w),
1408 v_date);
1409 IF v_date is null THEN
1410 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RETURN_ERROR');
1411 FND_MESSAGE.SET_TOKEN('PROC', 'Get_date_from_W_and_D');
1412 FND_MESSAGE.SET_TOKEN('VALUE', p_year||','
1413 ||v_begin_dst_month
1414 ||','||to_char(v_begin_dst_week_of_m)
1415 ||','||to_char(v_begin_dst_day_of_w));
1416 FND_MSG_PUB.ADD;
1417 RAISE FND_API.G_EXC_ERROR;
1418 END IF;
1419 x_begin_dst_date := to_date(to_char(v_date, 'MM-DD-YYYY')||
1420 ' '||to_char(trunc(v_begin_dst_hour))||
1421 ' '||to_char(round((v_begin_dst_hour - trunc(v_begin_dst_hour))*60,2)),
1422 'MM-DD-YYYY HH24 MI');
1423 ELSE
1424 /* -1 means begin daylight saving week is the last week
1425 of the month, we have to figure out whether
1426 the last week is the 4th or 5th
1427 */
1428 Get_date_from_W_and_D(p_year, v_begin_dst_month,
1429 '5',
1430 to_char(v_begin_dst_day_of_w),
1431 v_date);
1432 IF(to_char(v_date, 'MM') = v_begin_dst_month)THEN
1433 /* The 5th week is the last week of the month */
1434 x_begin_dst_date := to_date(to_char(v_date, 'MM-DD-YYYY')||
1435 ' '||to_char(trunc(v_begin_dst_hour))||
1436 ' '||to_char(round((v_begin_dst_hour - trunc(v_begin_dst_hour))*60,2)),
1437 'MM-DD-YYYY HH24 MI');
1438 ELSE
1439 Get_date_from_W_and_D(p_year, v_begin_dst_month,
1440 '4',
1441 to_char(v_begin_dst_day_of_w),
1442 v_date);
1443 x_begin_dst_date := to_date(to_char(v_date, 'MM-DD-YYYY')||
1444 ' '||to_char(trunc(v_begin_dst_hour))||
1445 ' '||to_char(round((v_begin_dst_hour - trunc(v_begin_dst_hour))*60,2)),
1446 'MM-DD-YYYY HH24 MI');
1447 END IF;
1448 END IF;
1449 END IF;
1450
1451 IF(v_end_dst_day is not NULL)THEN
1452 x_end_dst_date := to_date(v_end_dst_month||
1453 '-'||to_char(v_end_dst_day)||
1454 '-'||p_year||
1455 ' '||to_char(trunc(v_end_dst_hour))||
1456 ' '||to_char(round((v_end_dst_hour - trunc(v_end_dst_hour))*60,2)),
1457 'MM-DD-YYYY HH24 MI');
1458 ELSE
1459 IF(v_end_dst_week_of_m <> -1)THEN
1460 Get_date_from_W_and_D(p_year, v_end_dst_month,
1461 to_char(v_end_dst_week_of_m),
1462 to_char(v_end_dst_day_of_w),
1463 v_date);
1464 IF v_date is null THEN
1465 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RETURN_ERROR');
1466 FND_MESSAGE.SET_TOKEN('PROC', 'Get_date_from_W_and_D');
1467 FND_MESSAGE.SET_TOKEN('VALUE', p_year||','||v_end_dst_month
1468 ||','||to_char(v_end_dst_week_of_m)
1469 ||','||to_char(v_end_dst_day_of_w));
1470 FND_MSG_PUB.ADD;
1471 RAISE FND_API.G_EXC_ERROR;
1472 END IF;
1473 x_end_dst_date := to_date(to_char(v_date, 'MM-DD-YYYY')
1474 ||' '||to_char(trunc(v_end_dst_hour))||
1475 ' '||to_char(round((v_end_dst_hour - trunc(v_end_dst_hour))*60,2)),
1476 'MM-DD-YYYY HH24 MI');
1477 ELSE
1478 Get_date_from_W_and_D(p_year, v_end_dst_month,
1479 '5',
1480 to_char(v_end_dst_day_of_w),
1481 v_date);
1482 IF(to_char(v_date, 'MM') = v_end_dst_month)THEN
1483 x_end_dst_date := to_date(to_char(v_date, 'MM-DD-YYYY')
1484 ||' '||to_char(trunc(v_end_dst_hour))||
1485 ' '||to_char(round((v_end_dst_hour - trunc(v_end_dst_hour))*60,2)),
1486 'MM-DD-YYYY HH24 MI');
1487 ELSE
1488 Get_date_from_W_and_D(p_year, v_end_dst_month,
1489 '4',
1490 to_char(v_end_dst_day_of_w),
1491 v_date);
1492 x_end_dst_date := to_date(to_char(v_date, 'MM-DD-YYYY')
1493 ||' '||to_char(trunc(v_end_dst_hour))||
1494 ' '||to_char(round((v_end_dst_hour - trunc(v_end_dst_hour))*60,2)),
1495 'MM-DD-YYYY HH24 MI');
1496 END IF;
1497 END IF;
1498 END IF;
1499 END IF;
1500 EXCEPTION WHEN NO_DATA_FOUND THEN
1501 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1502 FND_MESSAGE.SET_TOKEN('RECORD', 'timezone');
1503 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_timezone_id));
1504 FND_MSG_PUB.ADD;
1505 RAISE FND_API.G_EXC_ERROR;
1506 END Get_begin_end_dst_day_time;
1507
1508 /*===========================================================================+
1509 | PROCEDURE |
1510 | Get_date_from_W_and_D |
1511 | |
1512 | DESCRIPTION |
1513 | Get the date (in date format) given the year, month, week of |
1514 | the month, day of the week. |
1515 | SCOPE - PUBLIC |
1519 | ARGUMENTS : IN: |
1516 | |
1517 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1518 | |
1520 | p_year |
1521 | p_month |
1522 | p_week |
1523 | p_day |
1524 | OUT: |
1525 | x_date |
1526 | IN/ OUT: |
1527 | |
1528 | RETURNS : NONE |
1529 | |
1530 | NOTES |
1531 | |
1532 | MODIFICATION HISTORY |
1533 | Stephanie Zhang 23-AUG-99 Created |
1534 | |
1535 +===========================================================================*/
1536
1537 PROCEDURE Get_date_from_W_and_D (
1538 p_year in varchar2,
1539 p_month in varchar2,
1540 p_week in varchar2,
1541 p_day in varchar2,
1542 x_date out nocopy varchar2)
1543 IS
1544 l_date date;
1545 l_first_date_of_m date;
1546 l_last_date_of_m date;
1547 l_week_of_m varchar2(1);
1548 l_day_of_w varchar2(1);
1549 l_total number;
1550 l_db_sunday number;
1551 l_day number;
1552 BEGIN
1553 -- Initialize first_date as first day of the month
1554 l_first_date_of_m := to_date(p_year||' '||p_month||' '||'01', 'YYYY MM DD');
1555 l_last_date_of_m := last_day(l_first_date_of_m);
1556 l_total := l_last_date_of_m - l_first_date_of_m;
1557 l_date := l_first_date_of_m;
1558 l_day := to_number(p_day);
1559
1560 -- the p_day passed in is from the timezone data seeded in apps. this is always
1561 -- the day of the week assuming that sunday is day 1.
1562 -- the database allows configuration of which day of the week is day one, so
1563 -- we have to see what the database thinks a sunday is and then adjust p_day
1564 -- accordingly. we know that 01-01-1978 was a sunday so we test that date.
1565
1566 l_db_sunday := to_number(to_char(to_date('01-01-1978','DD-MM-YYYY'),'D'));
1567
1568 IF (l_db_sunday = 1) THEN NULL;
1569 ELSIF (l_day > (8- l_db_sunday))
1570 THEN l_day := l_day - (8- l_db_sunday);
1571 ELSIF (l_day <= (8- l_db_sunday))
1572 THEN l_day := l_day + l_db_sunday -1;
1573 END IF;
1574
1575 for i in 0..l_total LOOP
1576 l_date := l_first_date_of_m + i ;
1577 /* Bug Fix 2651358
1578 select to_char(l_date, 'W'),
1579 to_char(l_date, 'D')
1580 into l_week_of_m,
1581 l_day_of_w
1582 from sys.dual;
1583 */
1584
1585 IF (to_char(l_date, 'W') = p_week) and (to_number(to_char(l_date, 'D')) = l_day) THEN
1586 x_date := l_date;
1587 return;
1588 END IF;
1589 END LOOP;
1590 END Get_date_from_W_and_D;
1591
1592
1593 END HZ_TIMEZONE_PUB;
[Home] [Help]