1 PACKAGE BODY CS_TZ_GET_DETAILS_PVT AS
2 /* $Header: csvtzgdb.pls 120.2 2005/11/09 10:15:17 pnkalari ship $ */
3
4 /*******************************************************************************
5 -- Start of comments
6 -- API name : Get_GMT_DEVIATION
7 -- Type : Private
8 -- Pre-reqs : None.
9 -- Function : This procedure finds the GMT offset of p_end_tz_id
10 --
11 -- Parameters :
12 -- IN :
13 -- p_api_version NUMBER Required
14 -- p_init_msg_list VARCHAR2 Required
15 -- p_commit VARCHAR2 Required
16 -- p_start_tz_id NUMBER
17 -- p_end_tz_id NUMBER
18 -- p_Time_Lag NUMBER
19 -- OUT :
20 --
21 -- x_GMT_DEV NUMBER
22 -- x_return_status VARCHAR2
23 -- x_msg_count NUMBER
24 -- x_msg_data VARCHAR2
25 --End of comments
26
27 *******************************************************************************/
28
29 PROCEDURE GET_GMT_DEVIATION(P_API_VERSION IN NUMBER,
30 P_INIT_MSG_LIST IN VARCHAR2,
31 P_START_TZ_ID IN NUMBER,
32 P_END_TZ_ID IN NUMBER,
33 P_TIME_LAG IN NUMBER,
34 X_GMT_DEV OUT NOCOPY NUMBER,
35 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
36 X_MSG_COUNT OUT NOCOPY NUMBER,
37 X_MSG_DATA OUT NOCOPY VARCHAR2)
38
39 IS
40
41 l_api_version number := 1.0 ;
42 l_return_status VARCHAR2(2);
43 l_start_tz_id number;
44 l_end_tz_id number;
45 l_time_lag number;
46 l_status varchar2(2);
47 l_name varchar2(80);
48 l_g_name varchar2(80);
49 l_date date;
50 l_s_GMT_dev number;
51 l_e_GMT_dev number;
52 l_msg_count number;
53 l_msg_data varchar2(2000);
54 l_api_name varchar2(30) := 'GET_GMT_DEVIATION';
55
56 BEGIN
57
58 SAVEPOINT get_gmt_deviation;
59
60 l_start_tz_id := P_START_TZ_ID;
61 l_end_tz_id := P_END_TZ_ID;
62 l_time_lag := P_TIME_LAG;
63
64 -- Current System date is used in the Get_Timezone procedure to find out
65 -- the Daylight Savings time for the current year, if applicable.
66
67 l_date := sysdate;
68
69 -- If END Time Zone Id is not null, then calculate the GMT offset
70 -- and return that value.
71 -- If it is null, then calculate the GMT offset of the Start Time and
72 -- subtract the time lag to find out the GMT offset of the End time
73
74
75 IF (l_end_tz_id IS NOT NULL) THEN
76
77 HZ_TIMEZONE_PUB.Get_Timezone_GMT_Deviation(p_api_version,
78 p_init_msg_list,
79 l_end_tz_id,
80 l_date,
81 l_e_GMT_dev,
82 l_g_name,
83 l_name,
84 l_return_status,
85 l_msg_Count,
86 l_msg_data);
87
88 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
89 RAISE FND_API.G_EXC_ERROR ;
90 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
92 END IF ;
93
94 X_GMT_DEV := l_e_GMT_dev;
95
96 ELSIF (l_start_tz_id IS NOT NULL) THEN
97
98 HZ_TIMEZONE_PUB.Get_Timezone_GMT_Deviation (p_api_version,
99 p_init_msg_list,
100 l_start_tz_id,
101 l_date,
102 l_s_GMT_dev,
103 l_g_name,
104 l_name,
105 l_return_status,
106 l_msg_count,
107 l_msg_data);
108
109 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
110 RAISE FND_API.G_EXC_ERROR ;
111 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
113 END IF ;
114
115 -- If time lag is null, then return the GMT offset of the Server time.
116
117 IF (l_time_lag IS NOT NULL) THEN
118 X_GMT_DEV := l_s_GMT_dev - l_time_lag;
119 ELSE
120 X_GMT_DEV := l_s_GMT_dev;
121 END IF;
122 END IF;
123
124 x_return_status := l_return_status;
125
126 EXCEPTION
127
128 WHEN FND_API.G_EXC_ERROR THEN
129 ROLLBACK TO get_gmt_deviation;
130 x_return_status := FND_API.G_RET_STS_ERROR ;
131 FND_MSG_PUB.COUNT_AND_GET (p_count =>x_msg_count ,
132 p_data => x_msg_data ,
133 p_encoded => fnd_api.g_false );
134
135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
136 ROLLBACK TO get_gmt_deviation;
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138 FND_MSG_PUB.COUNT_AND_GET (p_count =>x_msg_count ,
139 p_data => x_msg_data ,
140 p_encoded => fnd_api.g_false );
141
142 WHEN OTHERS THEN
143 ROLLBACK TO get_gmt_deviation;
144 x_return_status := FND_API.G_RET_STS_unexp_error ;
145
146 IF fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
147 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
148 END IF;
149
150 fnd_msg_pub.count_and_get (p_count =>x_msg_count ,
151 p_data => x_msg_data ,
152 p_encoded => fnd_api.g_false );
153
154 END;
155
156
157 /*******************************************************************************
158 -- Start of comments
159 -- API name : Get_Leadtime
160 -- Type : Private
161 -- Pre-reqs : None.
162 -- Function : This procedure finds the time difference between
163 -- two given timezones
164 --
165 -- Parameters :
166 -- IN :
167 -- p_api_version NUMBER Required
168 -- p_init_msg_list VARCHAR2 Required
169 -- p_commit VARCHAR2 Required
170 -- p_start_tz_id NUMBER Required
171 -- p_end_tz_id NUMBER Required
172 -- OUT :
173 --
174 -- x_leadtime NUMBER
175 -- x_return_status VARCHAR2
176 -- x_msg_count NUMBER
177 -- x_msg_data VARCHAR2
178 --End of comments
179 *******************************************************************************/
180
181
182 PROCEDURE GET_LEADTIME(P_API_VERSION IN NUMBER,
183 P_INIT_MSG_LIST IN VARCHAR2,
184 P_START_TZ_ID IN NUMBER,
185 P_END_TZ_ID IN NUMBER,
186 X_LEADTIME OUT NOCOPY NUMBER,
187 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
188 X_MSG_COUNT OUT NOCOPY NUMBER,
189 X_MSG_DATA OUT NOCOPY VARCHAR2)
190
191 IS
192
193
194 l_api_version number := 1.0 ;
195 l_return_status VARCHAR2(2);
196 l_start_tz_id number;
197 l_end_tz_id number;
198 l_status varchar2(2);
199 l_name varchar2(80);
200 l_g_name varchar2(80);
201 l_date date;
202 l_s_GMT_dev number;
203 l_e_GMT_dev number;
204 l_msg_count number;
205 l_msg_data varchar2(2000);
206 l_api_name varchar2(30) := 'GET_LEADTIME';
207
208
209 BEGIN
210
211 SAVEPOINT get_leadtime;
212
213 l_start_tz_id := P_START_TZ_ID;
214 l_end_tz_id := P_END_TZ_ID;
215
216 -- Current System date is used in the Get_Timezone procedure
217 -- to find out the Daylight Savings time for the current year,
218 -- if applicable.
219
220 l_date := sysdate;
221
222 -- Find the time deviation from the GMT for both the timezonesi
223 -- if the sites are in two different time zones.
224
225 IF (l_start_tz_id <> l_end_tz_id)
226
227 THEN
228
229 -- Calls the procedure to find the difference between the time
230 -- in each Time zone and GMT. The return values l_s_GMT_dev and
231 -- l_e_GMT_dev store the time difference.
232
233 HZ_TIMEZONE_PUB.Get_Timezone_GMT_Deviation(p_api_version,
234 p_init_msg_list,
235 l_start_tz_id,
236 l_date,
237 l_s_GMT_dev,
238 l_g_name,
239 l_name,
240 l_return_status,
241 l_msg_count,
242 l_msg_data);
243
244 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
245 RAISE FND_API.G_EXC_ERROR ;
246 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
248 END IF ;
249
250 HZ_TIMEZONE_PUB.Get_Timezone_GMT_Deviation(p_api_version,
251 p_init_msg_list,
252 l_end_tz_id,
253 l_date,
254 l_e_GMT_dev,
255 l_g_name,
256 l_name,
257 l_return_status,
258 l_msg_count,
259 l_msg_data);
260
261 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
262 RAISE FND_API.G_EXC_ERROR ;
263 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
265 END IF ;
266
267 -- Calculate the timelag between two timezones.
268
269 x_leadtime := l_s_GMT_dev - l_e_GMT_dev;
270
271 ELSE
272
273 l_return_status := FND_API.G_RET_STS_SUCCESS;
274 x_leadtime := 0;
275
276 END IF;
277
278 x_return_status := l_return_status;
279
280 EXCEPTION
281
282 WHEN FND_API.G_EXC_ERROR THEN
283 ROLLBACK TO get_leadtime;
284 x_return_status := FND_API.G_RET_STS_ERROR ;
285 FND_MSG_PUB.COUNT_AND_GET (p_count =>x_msg_count ,
286 p_data => x_msg_data ,
287 p_encoded => fnd_api.g_false );
288
289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
290 ROLLBACK TO get_leadtime;
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
292 FND_MSG_PUB.COUNT_AND_GET (p_count =>x_msg_count ,
293 p_data => x_msg_data ,
294 p_encoded => fnd_api.g_false );
295
296 WHEN OTHERS THEN
297 ROLLBACK TO get_leadtime;
298 x_return_status := FND_API.G_RET_STS_unexp_error ;
299
300 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
301 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
302 END IF;
303
304 fnd_msg_pub.count_and_get ( p_count =>x_msg_count ,
305 p_data => x_msg_data ,
306 p_encoded => fnd_api.g_false );
307
308 END;
309
310
311 /*******************************************************************************
312
313 -- Start of comments
314 -- API name : Get_Leadtime
315 -- Type : Private
316 -- Pre-reqs : None.
317 -- Function : This procedure finds the lead time lag between
318 -- two cities given the Timezone of one City and
319 -- the City name, State the Country it belongs to
320 -- for the other
321 --
322 -- Parameters :
323 -- IN :
324 -- p_api_version NUMBER Required
325 -- p_init_msg_list VARCHAR2 Required
326 -- p_commit VARCHAR2 Required
327 -- p_start_tz_id NUMBER Required
328 -- p_end_zip_code VARCHAR2 Required
329 -- p_end_city VARCHAR2 Required
330 -- p_end_state VARCHAR2 Required
331 -- p_end_country VARCHAR2 Required
332 --
333 -- OUT :
334 --
335 -- x_GMT_DEV NUMBER
336 -- x_return_status VARCHAR2
337 -- x_msg_count NUMBER
338 -- x_msg_data VARCHAR2
339 --End of comments
340 *******************************************************************************/
341
342
343 PROCEDURE GET_LEADTIME (P_API_VERSION IN NUMBER,
344 P_INIT_MSG_LIST IN VARCHAR2,
345 P_START_TZ_ID IN NUMBER,
346 P_END_ZIP_CODE IN VARCHAR2,
347 P_END_CITY IN VARCHAR2,
348 P_END_STATE IN VARCHAR2,
349 P_END_COUNTRY IN VARCHAR2,
350 X_LEADTIME OUT NOCOPY NUMBER,
351 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
352 X_MSG_COUNT OUT NOCOPY NUMBER,
353 X_MSG_DATA OUT NOCOPY VARCHAR2)
354
355
356 IS
357
358
359 l_api_version number := 1.0 ;
360 l_return_status VARCHAR2(2) ;
361 l_start_tz_id number;
362 l_end_tz_id number;
363 l_status varchar2(2);
364 l_name varchar2(80);
365 l_g_name varchar2(80);
366 l_date date;
367 l_s_GMT_dev number;
368 l_e_GMT_dev number;
369 l_msg_count number;
370 l_msg_data varchar2(2000);
371 l_api_name varchar2(30) := 'GET_LEADTIME';
372
373
374 BEGIN
375
376 SAVEPOINT get_leadtime;
377
378 l_start_tz_id := P_START_TZ_ID;
379
380 -- Find the Time Zone Id for a given city. End City is the city
381 -- given.
385 p_end_zip_code,
382 -- Commented out this code becasue of post_query error
383 /* HZ_TIMEZONE_PUB.Get_Timezone_ID (p_api_version,
384 p_init_msg_list,
386 p_end_city,
387 p_end_state,
388 p_end_country,
389 l_end_tz_id,
390 l_return_status,
391 l_msg_count,
392 l_msg_data);
393
394 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
395 RAISE FND_API.G_EXC_ERROR ;
396 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
398 END IF ;
399
400 -- Current System date is used in the Get_Timezone procedure to
401 -- find out the Daylight Savings time for the current year, if applicable.
402
403 l_date := sysdate;
404 */
405
406 -- Find the time deviation from the GMT for both the timezones
407 -- if the sites are in two different time zones.
408
409 IF (l_start_tz_id <> l_end_tz_id)
410
411 THEN
412
413 -- Calls the procedure to find the difference between the time in
414 -- each Time zone and GMT. The return values l_s_GMT_dev and
415 -- l_e_GMT_dev store the time difference.
416
417 HZ_TIMEZONE_PUB.Get_Timezone_GMT_Deviation(p_api_version,
418 p_init_msg_list,
419 l_start_tz_id,
420 l_date,
421 l_s_GMT_dev,
422 l_g_name,
423 l_name,
424 l_return_status,
425 l_msg_count,
426 l_msg_data);
427
428
429 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
430 RAISE FND_API.G_EXC_ERROR ;
431 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
433 END IF ;
434
435
436 HZ_TIMEZONE_PUB.Get_Timezone_GMT_Deviation(p_api_version,
437 p_init_msg_list,
438 l_end_tz_id,
439 l_date,
440 l_e_GMT_dev,
441 l_g_name,
442 l_name,
443 l_return_status,
444 l_msg_count,
445 l_msg_data);
446
447 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
448 RAISE FND_API.G_EXC_ERROR ;
449 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
451 END IF ;
452
453
454 -- Calculate the timelag between two timezones.
455
456 x_leadtime := l_s_GMT_dev - l_e_GMT_dev;
457
458 ELSE
459
460 l_return_status := FND_API.G_RET_STS_SUCCESS;
461 x_leadtime := 0;
462
463
464 END IF;
465
466 x_return_status := l_return_status;
467
468 EXCEPTION
469
470 WHEN FND_API.G_EXC_ERROR THEN
471 ROLLBACK TO get_leadtime;
472 x_return_status := FND_API.G_RET_STS_ERROR ;
473 FND_MSG_PUB.COUNT_AND_GET (p_count =>x_msg_count ,
474 p_data => x_msg_data ,
475 p_encoded => fnd_api.g_false );
476
477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
478 ROLLBACK TO get_leadtime;
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
480 FND_MSG_PUB.COUNT_AND_GET (p_count =>x_msg_count ,
481 p_data => x_msg_data ,
482 p_encoded => fnd_api.g_false );
483
484 WHEN OTHERS THEN
485 ROLLBACK TO get_leadtime;
486 x_return_status := FND_API.G_RET_STS_unexp_error ;
487
488 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
489 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
490 END IF;
491
492 fnd_msg_pub.count_and_get (p_count =>x_msg_count ,
493 p_data => x_msg_data ,
494 p_encoded => fnd_api.g_false );
495
496 END;
497 /*====================================================================================================+
498 ==
499 == Procedure name : Customer_Preferred_Time_Zone
500 ==
501 == Description :
502 == This procedure derives the time zone in the following order :
503 == Incident timezone, Contact address timezone, Contact phone timezone, User entered contact timezone,
504 == Customer address timezone, Customer phone timezone, and Server timezone.
505 == Modification History :
506 ==
507 == Date Name Desc
508 == ---------- --------- ---------------------------------------------
509 == 11/09/2005 PNKALARI Bug Fixed 4705669. Replace timezone_code with name in timezone_name cursor.
510 ======================================================================================================*/
511 PROCEDURE CUSTOMER_PREFERRED_TIME_ZONE
512 ( p_incident_id IN NUMBER
513 , p_task_id IN NUMBER
514 , p_resource_id IN NUMBER
515 , p_cont_pref_time_zone_id IN NUMBER
516 , p_incident_location_id IN NUMBER DEFAULT NULL
517 , p_incident_location_type IN VARCHAR2 DEFAULT NULL
518 , p_contact_party_id IN NUMBER DEFAULT NULL
519 , p_contact_phone_id IN NUMBER DEFAULT NULL
520 , p_contact_address_id IN NUMBER DEFAULT NULL
521 , p_customer_id IN NUMBER DEFAULT NULL
522 , p_customer_phone_id IN NUMBER DEFAULT NULL
523 , p_customer_address_id IN NUMBER DEFAULT NULL
524 , x_timezone_id OUT NOCOPY NUMBER
525 , x_timezone_name OUT NOCOPY VARCHAR2
526 )
527 IS
528
529 l_tz_id NUMBER ;
530 l_incident_location_type VARCHAR2(20) ;
531 l_incident_location_id NUMBER ;
532
533 -- Cursor to derive time zone from Charges API.
534
535 -- Cursor to derive Task location type
536 CURSOR task_address_location_type IS
537 SELECT a.incident_location_id, a.incident_location_type
538 FROM cs_incidents_all_b a, jtf_tasks_b b
539 WHERE a.incident_id = b.source_object_id
540 AND b.source_object_type_code = 'SR'
541 AND b.task_id = p_task_id ;
542
543 -- Cursor to derive Task time zone for party site
544 CURSOR task_party_site_timezone IS
545 SELECT b.timezone_id
546 FROM hz_party_sites a, hz_locations b
547 WHERE a.location_id = b.location_id
548 --AND a.identifying_address_flag = 'Y'
549 AND a.party_site_id = l_incident_location_id ;
550
551 -- Cursor to derive Task time zone for location
552 CURSOR task_location_timezone IS
553 SELECT timezone_id
554 FROM hz_locations
555 WHERE location_id = l_incident_location_id ;
556
557 -- Cursor to derive time zone for technician
558 CURSOR technician_timezone IS
559 SELECT time_zone
560 FROM jtf_rs_resource_extns_vl
561 WHERE resource_id = p_resource_id ;
562
563 -- Cursor to derive time zone for Primary Contact address
564 CURSOR contact_timezone IS
565 SELECT b.timezone_id
566 FROM hz_party_sites a, hz_locations b, cs_hz_sr_contact_points c
567 WHERE a.party_id = c.party_id
568 AND a.identifying_address_flag = 'Y'
569 AND c.primary_flag = 'Y'
570 AND a.location_id = b.location_id
571 AND c.incident_id = p_incident_id ;
572
573 -- Cursor to derive time zone for Primary Contact phone
577 WHERE a.owner_table_id = b.party_id
574 CURSOR contact_cont_point_tz IS
575 SELECT a.timezone_id
576 FROM hz_contact_points a, cs_hz_sr_contact_points b
578 AND a.contact_point_type = b.contact_point_type
579 AND b.contact_point_type = 'PHONE'
580 AND a.primary_flag = b.primary_flag
581 AND b.primary_flag = 'Y'
582 AND b.incident_id = p_incident_id ;
583
584 -- Cursor to derive time zone for contact entered on UI
585 CURSOR cont_pref_time_zone_id IS
586 SELECT time_zone_id
587 FROM cs_incidents_all_b
588 WHERE incident_id = p_incident_id;
589
590 -- Cursor to derive time zone for Customer address
591 CURSOR customer_timezone IS
592 SELECT b.timezone_id
593 FROM hz_party_sites a, hz_locations b, cs_incidents_all_b c
594 WHERE a.party_id = c.customer_id
595 AND a.identifying_address_flag = 'Y'
596 AND a.location_id = b.location_id
597 AND c.incident_id = p_incident_id ;
598
599 -- Cursor to derive time zone for Customer phone
600 CURSOR customer_cont_point_tz IS
601 SELECT a.timezone_id
602 FROM hz_contact_points a, cs_incidents_all_b b
603 WHERE a.owner_table_id = b.customer_id
604 AND a.contact_point_type = 'PHONE'
605 AND a.primary_flag = 'Y'
606 AND b.incident_id = p_incident_id ;
607
608
609 -- Cursor to derive time zone from SR Form and SR Tab.
610
611 -- Cursor to derive time zone for party site
612 CURSOR f_incident_party_site_timezone IS
613 SELECT b.timezone_id
614 FROM hz_party_sites a, hz_locations b
615 WHERE a.location_id = b.location_id
616 --AND a.identifying_address_flag = 'Y'
617 AND a.party_site_id = p_incident_location_id ;
618
619 -- Cursor to derive time zone for location
620 CURSOR f_incident_location_timezone IS
621 SELECT timezone_id
622 FROM hz_locations
623 WHERE location_id = p_incident_location_id ;
624
625 -- Cursor to derive time zone for Primary Contact address
626 CURSOR f_contact_timezone IS
627 SELECT b.timezone_id
628 FROM hz_party_sites a, hz_locations b
629 WHERE a.location_id = b.location_id
630 AND a.identifying_address_flag = 'Y'
631 AND a.party_id = p_contact_party_id ;
632
633 -- Cursor to derive time zone for Primary Contact phone
634 CURSOR f_contact_cont_point_tz IS
635 SELECT timezone_id
636 FROM hz_contact_points
637 WHERE contact_point_type = 'PHONE'
638 AND primary_flag = 'Y'
639 AND owner_table_id = p_contact_party_id ;
640
641 -- Cursor to derive time zone for Customer address
642 CURSOR f_customer_timezone IS
643 SELECT b.timezone_id
644 FROM hz_party_sites a, hz_locations b
645 WHERE a.location_id = b.location_id
646 AND a.identifying_address_flag = 'Y'
647 AND a.party_id = p_customer_id ;
648
649 -- Cursor to derive time zone for Customer phone
650 CURSOR f_customer_cont_point_tz IS
651 SELECT timezone_id
652 FROM hz_contact_points
653 WHERE contact_point_type = 'PHONE'
654 AND primary_flag = 'Y'
655 AND owner_table_id = p_customer_id ;
656
657 -- Cursor to derive time zone name from time zone id
658 CURSOR timezone_name IS
659 SELECT name
660 FROM fnd_timezones_vl
661 WHERE upgrade_tz_id = l_tz_id ;
662
663 l_task_timezone_id NUMBER ;
664 l_task_timezone VARCHAR2(50) ;
665
666 l_incident_timezone_id NUMBER ;
667 l_incident_timezone VARCHAR2(50) ;
668
669 l_technician_timezone_id NUMBER ;
670 l_technician_timezone VARCHAR2(50) ;
671
672 l_contact_timezone_id NUMBER ;
673 l_contact_timezone VARCHAR2(50) ;
674
675 l_customer_timezone_id NUMBER ;
676 l_customer_timezone VARCHAR2(50) ;
677
678 l_server_timezone_id NUMBER ;
679 l_server_timezone VARCHAR2(50) ;
680
681 l_contact_cont_point_tz_id NUMBER ;
682 l_customer_cont_point_tz_id NUMBER ;
683
684 ll_contact_cont_point_tz_id NUMBER ;
685 ll_customer_cont_point_tz_id NUMBER ;
686
687 l_cont_pref_time_zone_id NUMBER ;
688 ll_cont_pref_time_zone_id NUMBER ;
689
690 BEGIN
691
692 l_server_timezone_id := fnd_profile.value('SERVER_TIMEZONE_ID') ;
693 ll_cont_pref_time_zone_id := p_cont_pref_time_zone_id ;
694
695 IF (p_task_id is not null) THEN
696
697 OPEN task_address_location_type ;
698 FETCH task_address_location_type into l_incident_location_id, l_incident_location_type ;
699 IF (task_address_location_type%notfound) THEN
700 null ;
701 END IF ;
702 CLOSE task_address_location_type ;
703
704 IF(l_incident_location_id is not null AND l_incident_location_type = 'HZ_PARTY_SITE') THEN
705 OPEN task_party_site_timezone ;
706 FETCH task_party_site_timezone into l_task_timezone_id ;
707 IF (task_party_site_timezone%notfound) THEN
708 null ;
709 END IF ;
710 CLOSE task_party_site_timezone ;
711 END IF ;
712
713 IF(l_incident_location_id is not null AND l_incident_location_type = 'HZ_LOCATION') THEN
714 OPEN task_location_timezone ;
715 FETCH task_location_timezone into l_task_timezone_id ;
716 IF (task_location_timezone%notfound) THEN
717 null ;
718 END IF ;
719 CLOSE task_location_timezone ;
720 END IF ;
721
722 IF (l_task_timezone_id is not null) THEN
723 l_tz_id := l_task_timezone_id ;
724 OPEN timezone_name ;
725 FETCH timezone_name into x_timezone_name ;
729 CLOSE timezone_name ;
726 IF (timezone_name%notfound) THEN
727 null ;
728 END IF ;
730 x_timezone_id := l_task_timezone_id ;
731 RETURN ;
732 END IF ;
733
734 END IF ; -- p_task_id is not null
735
736
737 IF (p_incident_id is not null) THEN
738
739 OPEN technician_timezone ;
740 FETCH technician_timezone into l_technician_timezone_id ;
741 IF (technician_timezone%notfound) THEN
742 null ;
743 END IF ;
744 CLOSE technician_timezone ;
745
746 IF (l_technician_timezone_id is not null) THEN
747 l_tz_id := l_technician_timezone_id ;
748 OPEN timezone_name ;
749 FETCH timezone_name into x_timezone_name ;
750 IF (timezone_name%notfound) THEN
751 null ;
752 END IF ;
753 CLOSE timezone_name ;
754 x_timezone_id := l_technician_timezone_id ;
755 RETURN ;
756 END IF ;
757
758 OPEN contact_timezone ;
759 FETCH contact_timezone into l_contact_timezone_id ;
760 IF (contact_timezone%notfound) THEN
761 null ;
762 END IF ;
763 CLOSE contact_timezone ;
764
765 IF (l_contact_timezone_id is not null) THEN
766 l_tz_id := l_contact_timezone_id ;
767 OPEN timezone_name ;
768 FETCH timezone_name into x_timezone_name ;
769 IF (timezone_name%notfound) THEN
770 null ;
771 END IF ;
772 CLOSE timezone_name ;
773 x_timezone_id := l_contact_timezone_id ;
774 RETURN ;
775 END IF ;
776
777 OPEN contact_cont_point_tz ;
778 FETCH contact_cont_point_tz into l_contact_cont_point_tz_id ;
779 IF (contact_cont_point_tz%notfound) THEN
780 null ;
781 END IF ;
782 CLOSE contact_cont_point_tz ;
783
784 IF (l_contact_cont_point_tz_id is not null) THEN
785 l_tz_id := l_contact_cont_point_tz_id ;
786 OPEN timezone_name ;
787 FETCH timezone_name into x_timezone_name ;
788 IF (timezone_name%notfound) THEN
789 null ;
790 END IF ;
791 CLOSE timezone_name ;
792 x_timezone_id := l_contact_cont_point_tz_id ;
793 RETURN ;
794 END IF ;
795
796 OPEN cont_pref_time_zone_id ;
797 FETCH cont_pref_time_zone_id into l_cont_pref_time_zone_id ;
798 IF (cont_pref_time_zone_id%notfound) THEN
799 null ;
800 END IF ;
801 CLOSE cont_pref_time_zone_id ;
802
803 IF (l_cont_pref_time_zone_id is not null) THEN
804 l_tz_id := l_cont_pref_time_zone_id ;
805 OPEN timezone_name ;
806 FETCH timezone_name into x_timezone_name ;
807 IF (timezone_name%notfound) THEN
808 null ;
809 END IF ;
810 CLOSE timezone_name ;
811 x_timezone_id := l_cont_pref_time_zone_id ;
812 RETURN ;
813 END IF ;
814
815 OPEN customer_timezone ;
816 FETCH customer_timezone into l_customer_timezone_id ;
817 IF (customer_timezone%notfound) THEN
818 null ;
819 END IF ;
820 CLOSE customer_timezone ;
821
822 IF (l_customer_timezone_id is not null) THEN
823 l_tz_id := l_customer_timezone_id ;
824 OPEN timezone_name ;
825 FETCH timezone_name into x_timezone_name ;
826 IF (timezone_name%notfound) THEN
827 null ;
828 END IF ;
829 CLOSE timezone_name ;
830 x_timezone_id := l_customer_timezone_id ;
831 RETURN ;
832 END IF ;
833
834 OPEN customer_cont_point_tz ;
835 FETCH customer_cont_point_tz into l_customer_cont_point_tz_id ;
836 IF (customer_cont_point_tz%notfound) THEN
837 null ;
838 END IF ;
839 CLOSE customer_cont_point_tz ;
840
841 IF (l_customer_cont_point_tz_id is not null) THEN
842 l_tz_id := l_customer_cont_point_tz_id ;
843 OPEN timezone_name ;
844 FETCH timezone_name into x_timezone_name ;
845 IF (timezone_name%notfound) THEN
846 null ;
847 END IF ;
848 CLOSE timezone_name ;
849 x_timezone_id := l_customer_cont_point_tz_id ;
850 RETURN ;
851 END IF ;
852
853 l_tz_id := l_server_timezone_id ;
854 OPEN timezone_name ;
855 FETCH timezone_name into x_timezone_name ;
856 IF (timezone_name%notfound) THEN
857 null ;
858 END IF ;
859 CLOSE timezone_name ;
860 x_timezone_id := l_server_timezone_id ;
861
862 ELSE
863
864 IF (p_incident_location_id is not null) THEN
865
866 IF (p_incident_location_type = 'HZ_PARTY_SITE') THEN
867 OPEN f_incident_party_site_timezone ;
868 FETCH f_incident_party_site_timezone into l_incident_timezone_id ;
869 IF (f_incident_party_site_timezone%notfound) THEN
870 null ;
871 END IF ;
872 CLOSE f_incident_party_site_timezone ;
873 END IF ;
874
875 IF (p_incident_location_type = 'HZ_LOCATION') THEN
876 OPEN f_incident_location_timezone ;
877 FETCH f_incident_location_timezone into l_incident_timezone_id ;
878 IF (f_incident_location_timezone%notfound) THEN
879 null ;
880 END IF ;
881 CLOSE f_incident_location_timezone ;
882 END IF ;
883
884 IF (l_incident_timezone_id is not null) THEN
885 l_tz_id := l_incident_timezone_id ;
886 OPEN timezone_name ;
887 FETCH timezone_name into x_timezone_name ;
888 IF (timezone_name%notfound) THEN
889 null ;
893 x_timezone_id := l_incident_timezone_id ;
890 END IF ;
891 CLOSE timezone_name ;
892
894 RETURN ;
895 END IF ;
896
897 END IF ; -- p_incident_location_id is not null
898
899 IF (p_contact_party_id is not null) THEN
900
901 OPEN f_contact_timezone ;
902 FETCH f_contact_timezone into l_contact_timezone_id ;
903 IF (f_contact_timezone%notfound) THEN
904 null ;
905 END IF ;
906 CLOSE f_contact_timezone ;
907
908 IF (l_contact_timezone_id is not null) THEN
909 l_tz_id := l_contact_timezone_id ;
910 OPEN timezone_name ;
911 FETCH timezone_name into x_timezone_name ;
912 IF (timezone_name%notfound) THEN
913 null ;
914 END IF ;
915 CLOSE timezone_name ;
916 x_timezone_id := l_contact_timezone_id ;
917 RETURN ;
918 END IF ;
919
920 OPEN f_contact_cont_point_tz ;
921 FETCH f_contact_cont_point_tz into ll_contact_cont_point_tz_id ;
922 IF (f_contact_cont_point_tz%notfound) THEN
923 null ;
924 END IF ;
925 CLOSE f_contact_cont_point_tz ;
926
927 IF (ll_contact_cont_point_tz_id is not null) THEN
928 l_tz_id := ll_contact_cont_point_tz_id ;
929 OPEN timezone_name ;
930 FETCH timezone_name into x_timezone_name ;
931 IF (timezone_name%notfound) THEN
932 null ;
933 END IF ;
934 CLOSE timezone_name ;
935 x_timezone_id := ll_contact_cont_point_tz_id ;
936 RETURN ;
937 END IF ;
938
939 END IF ; -- p_contact_party_id is not null
940
941 IF (ll_cont_pref_time_zone_id is not null) THEN
942 l_tz_id := ll_cont_pref_time_zone_id ;
943 OPEN timezone_name ;
944 FETCH timezone_name into x_timezone_name ;
945 IF (timezone_name%notfound) THEN
946 null ;
947 END IF ;
948 CLOSE timezone_name ;
949 x_timezone_id := ll_cont_pref_time_zone_id ;
950 RETURN ;
951 END IF ;
952
953 IF (p_customer_id is not null) THEN
954
955 OPEN f_customer_timezone ;
956 FETCH f_customer_timezone into l_customer_timezone_id ;
957 IF (f_customer_timezone%notfound) THEN
958 null ;
959 END IF ;
960 CLOSE f_customer_timezone ;
961
962 IF (l_customer_timezone_id is not null) THEN
963 l_tz_id := l_customer_timezone_id ;
964 OPEN timezone_name ;
965 FETCH timezone_name into x_timezone_name ;
966 IF (timezone_name%notfound) THEN
967 null ;
968 END IF ;
969 CLOSE timezone_name ;
970 x_timezone_id := l_customer_timezone_id ;
971 RETURN ;
972 END IF ;
973
974 OPEN f_customer_cont_point_tz ;
975 FETCH f_customer_cont_point_tz into ll_customer_cont_point_tz_id ;
976 IF (f_customer_cont_point_tz%notfound) THEN
977 null ;
978 END IF ;
979 CLOSE f_customer_cont_point_tz ;
980
981 IF (ll_customer_cont_point_tz_id is not null) THEN
982 l_tz_id := ll_customer_cont_point_tz_id ;
983 OPEN timezone_name ;
984 FETCH timezone_name into x_timezone_name ;
985 IF (timezone_name%notfound) THEN
986 null ;
987 END IF ;
988 CLOSE timezone_name ;
989 x_timezone_id := ll_customer_cont_point_tz_id ;
990 RETURN ;
991 END IF ;
992
993 END IF ; -- p_customer_id is not null
994
995 l_tz_id := l_server_timezone_id ;
996 OPEN timezone_name ;
997 FETCH timezone_name into x_timezone_name ;
998 IF (timezone_name%notfound) THEN
999 null ;
1000 END IF ;
1001 CLOSE timezone_name ;
1002 x_timezone_id := l_server_timezone_id ;
1003
1004 END IF ; -- if p_incident_id is not null
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 NULL ;
1009
1010 END CUSTOMER_PREFERRED_TIME_ZONE ;
1011
1012
1013 END CS_TZ_GET_DETAILS_PVT;
[Home] [Help]