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