[Home] [Help]
PACKAGE BODY: APPS.OTA_TIMEZONE_UPGRADE
Source
1 PACKAGE BODY ota_timezone_upgrade AS
2 /* $Header: ottznupg.pkb 120.10 2006/09/04 11:38:45 niarora noship $ */
3 l_upgrade_name constant VARCHAR2(30) := 'OTTZUPG';
4 log_type_i constant VARCHAR2(30) := 'I';
5 -- log type is Information
6 log_type_n constant VARCHAR2(30) := 'N';
7 -- log type is Internal
8 log_type_e constant VARCHAR2(30) := 'E';
9 -- log type is Error
10 l_time_zone ota_events.timezone%TYPE;
11 l_primary_venue ota_events.location_id%TYPE;
12 l_loc_id ota_suppliable_resources.location_id%TYPE;
13 l_trng_center_id ota_suppliable_resources.training_center_id%TYPE;
14 l_err_msg VARCHAR2(2000);
15 l_err VARCHAR2(2000);
16 l_err_code VARCHAR2(72);
17 l_msg VARCHAR2(200);
18 l_others_msg VARCHAR2(200);
19 l_default_msg VARCHAR2(200) := 'Value for the Default timezone is missing';
20
21 FUNCTION get_location_tz(l_location_id IN ota_suppliable_resources.location_id%TYPE)
22 RETURN VARCHAR2
23 IS
24
25 l_time_zone ota_events.timezone%TYPE;
26
27 CURSOR csr_loc_tz(loc_id NUMBER) IS
28 SELECT timezone_code
29 FROM hr_locations
30 WHERE location_id = loc_id;
31 BEGIN
32
33 OPEN csr_loc_tz(l_location_id);
34
35 FETCH csr_loc_tz
36 INTO l_time_zone;
37
38 IF csr_loc_tz % NOTFOUND THEN
39 l_time_zone := NULL;
40 END IF;
41
42 CLOSE csr_loc_tz;
43
44 RETURN l_time_zone;
45 END;
46
47 PROCEDURE validate_proc_for_tz_upg(do_upg OUT nocopy VARCHAR2)
48 IS
49 ota_application_id constant NUMBER := 810;
50 ota_status_installed constant VARCHAR2(2) := 'I';
51 l_installed fnd_product_installations.status%TYPE;
52
53 CURSOR csr_ota_installed IS
54 SELECT fpi.status
55 FROM fnd_product_installations fpi
56 WHERE fpi.application_id = ota_application_id;
57
58 l_do_submit VARCHAR2(10) := 'FALSE';
59 l_raise_error boolean := FALSE;
60 l_status VARCHAR2(1) := 'N';
61 BEGIN
62
63 OPEN csr_ota_installed;
64
65 FETCH csr_ota_installed
66 INTO l_installed;
67
68 IF NOT(l_installed = ota_status_installed) THEN
69 l_do_submit := 'FALSE';
70 END IF;
71
72 CLOSE csr_ota_installed;
73
74 pay_core_utils.get_upgrade_status(NULL, 'OTATZUPG', l_status, l_raise_error);
75
76 IF l_status <> 'Y' THEN
77 l_do_submit := 'TRUE';
78 END IF;
79
80 do_upg := l_do_submit;
81 END validate_proc_for_tz_upg;
82
83 PROCEDURE get_location_trngcenter_id(l_supplied_resource_id IN ota_suppliable_resources.supplied_resource_id%TYPE,
84 l_location_id OUT nocopy ota_suppliable_resources.location_id%TYPE,
85 l_trng_center_id OUT nocopy ota_suppliable_resources.training_center_id%TYPE)
86 IS
87 BEGIN
88 SELECT location_id,
89 training_center_id
90 INTO l_location_id,
91 l_trng_center_id
92 FROM ota_suppliable_resources
93 WHERE supplied_resource_id = l_supplied_resource_id;
94
95 EXCEPTION
96 WHEN others THEN
97 l_location_id := NULL;
98 l_trng_center_id := NULL;
99 END get_location_trngcenter_id;
100
101 PROCEDURE write_log(msg IN VARCHAR2) IS
102 BEGIN
103 fnd_file.PUT_LINE(fnd_file.LOG, msg);
104 END write_log;
105
106 PROCEDURE upd_classic_data(p_event_id ota_events.event_id%TYPE,
107 p_event_type ota_events.event_type%TYPE,
108 p_public_event_flag ota_events.public_event_flag%TYPE,
109 l_upgrade_id NUMBER)
110 IS
111 --bug 5157917
112
113 CURSOR csr_lrnr_acc IS
114 SELECT 1
115 FROM ota_event_associations
116 WHERE event_id = p_event_id;
117
118 v_pblc_evt_flg VARCHAR2(10) := 'Y';
119 v_dummy NUMBER;
120 -- bug 5157917
121 BEGIN
122 l_msg := 'Error occurred while upgrading the timezone of events';
123
124 -----***************
125 -- Upgrade OM Events
126 -- 1) set book_independent_flag to N iff null
127 -- 2) Maximum_internal_attendees to 0 for price basis in 'C' or 'O'
128 -- Update TIMEZONE for iLearning imported events to
129 -- the corresponding APPS (FND_TIMEZONES_VL) timezone code.
130
131 UPDATE ota_events
132 SET book_independent_flag = nvl(book_independent_flag, 'N'),
133 secure_event_flag = nvl(secure_event_flag, 'N'),
134 maximum_internal_attendees = decode(price_basis, 'C', 0, 'O', 0, maximum_internal_attendees),
135 timezone = decode(offering_id, NULL, timezone, ota_classic_upgrade.get_apps_timezone(timezone))
136 WHERE event_id = p_event_id;
137 ------*************** bug 5157917
138 -- Upgrade Events
139 -- 1) set public_event_flag to Y iff null and no learner access exists
140 -- 2) set public_event_flag to N iff null and learner access exists.
141
142 IF p_event_type = 'SELFPACED'
143 AND p_public_event_flag IS NULL THEN
144
145 OPEN csr_lrnr_acc;
146 FETCH csr_lrnr_acc
147 INTO v_dummy;
148
149 IF csr_lrnr_acc % FOUND THEN
150 v_pblc_evt_flg := 'N';
151 END IF;
152
153 CLOSE csr_lrnr_acc;
154
155 UPDATE ota_events
156 SET public_event_flag = v_pblc_evt_flg
157 WHERE event_id = p_event_id;
158 END IF;
159
160 ------*************** bug 5157917
161
162 EXCEPTION
163 WHEN others THEN
164 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
165 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_EVENTS',
166 p_business_group_id => NULL,
167 p_source_primary_key => p_event_id,
168 p_object_value => l_upgrade_name,
169 p_message_text => l_err_msg,
170 p_upgrade_id => l_upgrade_id,
171 p_process_date => sysdate,
172 p_log_type => log_type_e,
173 p_upgrade_name => l_upgrade_name);
174 ota_timezone_upgrade.l_upgrade_status := FALSE;
175 write_log(l_err_msg);
176 END;
177
178 FUNCTION get_msg_text(l_msg_name IN VARCHAR2) RETURN VARCHAR2 IS l_msg_text VARCHAR2(2000);
179
180 CURSOR csr_msg(msg_name VARCHAR2) IS
181 SELECT message_text
182 FROM fnd_new_messages
183 WHERE message_name = TRIM(SUBSTR(msg_name, instr(msg_name, ':', 1, 1) + 1,
184 (decode(instr(msg_name, ':', 1, 2), 0, LENGTH(msg_name),
185 instr(msg_name, ':', 1, 2) -1) -instr(msg_name, ':'))))
186 AND language_code = userenv('LANG');
187 BEGIN
188
189 OPEN csr_msg(l_msg_name);
190
191 FETCH csr_msg
192 INTO l_msg_text;
193
194 IF csr_msg % NOTFOUND THEN
195 l_msg_text := NULL;
196 END IF;
197
198 CLOSE csr_msg;
199
200 RETURN l_msg_text;
201 END;
202
203 FUNCTION get_primary_venue_id(l_event_id IN ota_resource_bookings.event_id%TYPE)
204 RETURN NUMBER
205 IS
206 l_primary_venue ota_suppliable_resources.supplied_resource_id%TYPE;
207
208 CURSOR csr_pri_ven(p_event_id NUMBER) IS
209 SELECT resbkng.supplied_resource_id
210 FROM ota_resource_bookings resbkng
211 WHERE resbkng.event_id = p_event_id
212 AND resbkng.primary_venue_flag = 'Y';
213 BEGIN
214
215 OPEN csr_pri_ven(l_event_id);
216
217 FETCH csr_pri_ven
218 INTO l_primary_venue;
219
220 IF csr_pri_ven % NOTFOUND THEN
221 l_primary_venue := NULL;
222 END IF;
223
224 CLOSE csr_pri_ven;
225
226 RETURN l_primary_venue;
227 END get_primary_venue_id;
228
229 FUNCTION get_primary_venue_tz(l_primary_venue IN ota_suppliable_resources.supplied_resource_id%TYPE)
230 RETURN VARCHAR2
231 IS
232 l_time_zone ota_events.timezone%TYPE;
233
234 CURSOR csr_pri_ven_tz(p_pri_ven NUMBER) IS
235 SELECT loc.timezone_code
236 FROM ota_suppliable_resources res,
237 hr_locations loc
238 WHERE supplied_resource_id = p_pri_ven
239 AND res.location_id = loc.location_id;
240 BEGIN
241
242 OPEN csr_pri_ven_tz(l_primary_venue);
243
244 FETCH csr_pri_ven_tz
245 INTO l_time_zone;
246
247 IF csr_pri_ven_tz % NOTFOUND THEN
248 l_time_zone := NULL;
249 END IF;
250
251 CLOSE csr_pri_ven_tz;
252
253 RETURN l_time_zone;
254 END get_primary_venue_tz;
255
256 FUNCTION get_trngcenter_tz(l_trainning_center IN ota_suppliable_resources.training_center_id%TYPE)
257 RETURN VARCHAR2
258 IS
259 l_time_zone ota_events.timezone%TYPE;
260
261 CURSOR csr_trgctr_tz(trg_cen_id NUMBER) IS
262 SELECT loc.timezone_code
263
264 FROM hr_locations loc,
265 hr_all_organization_units org
266 WHERE loc.location_id = org.location_id
267 AND org.organization_id = trg_cen_id;
268 BEGIN
269
270 OPEN csr_trgctr_tz(l_trainning_center);
271
272 FETCH csr_trgctr_tz
273 INTO l_time_zone;
274
275 IF csr_trgctr_tz % NOTFOUND THEN
276 l_time_zone := NULL;
277 END IF;
278
279 CLOSE csr_trgctr_tz;
280
281 RETURN l_time_zone;
282 END get_trngcenter_tz;
283
284 /* PROCEDURE upd_res_bkng(p_res_bkng_id IN NUMBER, p_obj_ver_number IN OUT nocopy NUMBER,
285 p_time_zone IN VARCHAR2, l_upgrade_id IN NUMBER)
286 IS
287 l_msg VARCHAR2(200) := 'Error occurred while upgrading the timezone of resource bookings';
288 BEGIN
289 ota_trb_upd.upd(p_effective_date => TRUNC(sysdate),
290 p_resource_booking_id => p_res_bkng_id,
291 p_object_version_number => p_obj_ver_number,
292 p_timezone_code => p_time_zone);
293
294 EXCEPTION
295 WHEN others THEN
296 l_err := SUBSTR(sqlerrm, 1, 2000);
297
298 IF l_err LIKE '%OTA%' THEN
299 l_err_msg := get_msg_text(l_err);
300 ELSE
301 l_err_msg := nvl(l_err, l_msg);
302 END IF;
303
304 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_RESOURCE_BOOKINGS',
305 p_business_group_id => NULL,
306 p_source_primary_key => p_res_bkng_id,
307 p_object_value => l_upgrade_name,
308 p_message_text => l_err_msg,
309 p_upgrade_id => l_upgrade_id,
310 p_process_date => sysdate,
311 p_log_type => log_type_e,
312 p_upgrade_name => l_upgrade_name);
313 ota_timezone_upgrade.l_upgrade_status := FALSE;
314 write_log('Resource_booking_id: ' || p_res_bkng_id);
315 write_log(l_err_msg);
316
317 BEGIN
318
319 UPDATE ota_resource_bookings
320 SET timezone_code = p_time_zone
321 WHERE resource_booking_id = p_res_bkng_id
322 AND object_version_number = p_obj_ver_number
323 AND timezone_code IS NULL;
324
325 EXCEPTION
326 WHEN others THEN
327 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
328 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_RESOURCE_BOOKINGS',
329 p_business_group_id => NULL,
330 p_source_primary_key => p_res_bkng_id,
331 p_object_value => l_upgrade_name,
332 p_message_text => l_err_msg,
333 p_upgrade_id => l_upgrade_id,
334 p_process_date => sysdate,
335 p_log_type => log_type_e,
336 p_upgrade_name => l_upgrade_name);
337 ota_timezone_upgrade.l_upgrade_status := FALSE;
338 write_log('Resource_booking_id: ' || p_res_bkng_id);
339 write_log(l_err_msg);
340 END;
341 END;*/
342
343 PROCEDURE upd_event_bkng(p_event_id NUMBER, p_time_zone VARCHAR2, l_upgrade_id NUMBER)
344 AS
345 CURSOR csr_event_sess IS
346 SELECT event_id
347 FROM ota_events
348 WHERE event_id = p_event_id OR parent_event_id = p_event_id
349 AND timezone IS NULL;
350
351 CURSOR csr_event_res_bkng(p_event_id NUMBER) IS
352 SELECT resource_booking_id,
353 object_version_number
354 FROM ota_resource_bookings
355 WHERE event_id = p_event_id
356 AND timezone_code IS NULL;
357 BEGIN
358 l_msg := 'Error occurred while upgrading the timezone of events';
359
360 -- * FOR event_sess_row IN csr_event_sess
361 -- * LOOP
362 BEGIN
363
364 UPDATE ota_events
365 SET timezone = p_time_zone -- * WHERE event_id = event_sess_row.event_id
366 WHERE(event_id = p_event_id OR parent_event_id = p_event_id)
367 AND timezone IS NULL -- Added for bug#5110735
368 AND event_type IN('SCHEDULED', 'SESSION', 'SELFPACED','DEVELOPMENT','PROGRAMME');
369
370 EXCEPTION
371 WHEN others THEN
372 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
373 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_events',
374 p_business_group_id => NULL,
375 p_source_primary_key => p_event_id, -- * event_sess_row.event_id,
376 p_object_value => l_upgrade_name,
377 p_message_text => l_err_msg,
378 p_upgrade_id => l_upgrade_id,
379 p_process_date => sysdate,
380 p_log_type => log_type_e,
381 p_upgrade_name => l_upgrade_name);
382 ota_timezone_upgrade.l_upgrade_status := FALSE;
383 write_log(l_err_msg);
384 END;
385
386 /*FOR row_event_res_bkng IN csr_event_res_bkng (event_sess_row.event_id)
387 LOOP
388 upd_res_bkng (row_event_res_bkng.resource_booking_id,
389 row_event_res_bkng.object_version_number,
390 l_time_zone,
391 l_upgrade_id
392 );
393 END LOOP;*/
394 UPDATE ota_resource_bookings
395 SET timezone_code = p_time_zone
396 WHERE event_id in (SELECT event_id
397 FROM ota_events
398 WHERE event_id = p_event_id OR parent_event_id = p_event_id)
399 AND timezone_code IS NULL;
400 -- * event_sess_row.event_id and timezone_code IS NULL;
401 -- * END LOOP;
402 END;
403
404 PROCEDURE upd_class_frm_bkng(p_event_id NUMBER, p_time_zone VARCHAR2, l_upgrade_id NUMBER)
405 AS
406 CURSOR csr_cls_frm_res_bkng IS
407 SELECT resource_booking_id,
408 object_version_number
409 FROM ota_resource_bookings
410 WHERE forum_id IN
411 (SELECT forum_id
412 FROM ota_frm_obj_inclusions
413 WHERE object_type = 'E'
414 AND object_id = p_event_id)
415 AND timezone_code IS NULL;
416 BEGIN
417
418 /* FOR row_cls_frm_res_bkng IN csr_cls_frm_res_bkng
419 LOOP
420 upd_res_bkng (row_cls_frm_res_bkng.resource_booking_id,
421 row_cls_frm_res_bkng.object_version_number,
422 l_time_zone,
423 l_upgrade_id
424 );
425 END LOOP;*/
426
427 UPDATE ota_resource_bookings
428 SET timezone_code = p_time_zone
429 WHERE forum_id IN
430 (SELECT forum_id
431 FROM ota_frm_obj_inclusions
432 WHERE object_type = 'E'
433 AND object_id = p_event_id)
434 AND timezone_code IS NULL;
435 END;
436
437 PROCEDURE upd_class_chats_bkng(p_event_id NUMBER, p_time_zone VARCHAR2, l_upgrade_id NUMBER)
438 AS
439 CURSOR csr_chats(p_event_id NUMBER) IS
440 SELECT chat_id
441 FROM ota_chat_obj_inclusions
442 WHERE object_id = p_event_id
443 AND primary_flag = 'Y'
444 AND object_type = 'E';
445
446 CURSOR csr_cls_cha_res_bkng(p_chat_id NUMBER) IS
447 SELECT resource_booking_id,
448 object_version_number
449 FROM ota_resource_bookings
450 WHERE chat_id = p_chat_id
451 AND timezone_code IS NULL;
452 BEGIN
453 l_msg := 'Error occurred while upgrading the timezone of class chats';
454
455 FOR chats_row IN csr_chats(p_event_id)
456 LOOP
457 BEGIN
458
459 UPDATE ota_chats_b
460 SET timezone_code = p_time_zone
461 WHERE chat_id = chats_row.chat_id
462 AND timezone_code IS NULL;
463
464 EXCEPTION
465 WHEN others THEN
466 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
467 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_chats_b',
468 p_business_group_id => NULL,
469 p_source_primary_key => chats_row.chat_id,
470 p_object_value => l_upgrade_name,
471 p_message_text => l_err_msg,
472 p_upgrade_id => l_upgrade_id,
473 p_process_date => sysdate,
474 p_log_type => log_type_e,
475 p_upgrade_name => l_upgrade_name);
476 ota_timezone_upgrade.l_upgrade_status := FALSE;
477 write_log(l_err_msg);
478 END;
479
480 /* FOR row_cls_cha_res_bkng IN csr_cls_cha_res_bkng (chats_row.chat_id)
481 LOOP
482 upd_res_bkng (row_cls_cha_res_bkng.resource_booking_id,
483 row_cls_cha_res_bkng.object_version_number,
484 l_time_zone,
485 l_upgrade_id
486 );
487 END LOOP;*/
488
489 UPDATE ota_resource_bookings
490 SET timezone_code = p_time_zone
491 WHERE chat_id = chats_row.chat_id
492 AND timezone_code IS NULL;
493
494 END LOOP;
495 END;
496
497 PROCEDURE upd_cat_chat_bkng(p_default_timezone IN VARCHAR2, l_upgrade_id IN NUMBER)
498 IS
499 CURSOR csr_cat_chat_bkng IS
500 SELECT chat_id
501 FROM ota_chat_obj_inclusions
502 WHERE primary_flag = 'Y'
503 AND object_type = 'C';
504
505 CURSOR csr_cat_chat_res_bkng(p_chat_id NUMBER) IS
506 SELECT resource_booking_id,
507 object_version_number
508 FROM ota_resource_bookings
509 WHERE chat_id = p_chat_id
510 AND timezone_code IS NULL;
511 BEGIN
512 l_others_msg := 'Error occurred while upgrading the timezone of category chats ';
513
514 IF p_default_timezone IS NULL THEN
515 ota_classic_upgrade.add_log_entry(p_table_name => 'ota_chats_b',
516 p_business_group_id => NULL,
517 p_source_primary_key => -1,
518 p_object_value => 'Timezone',
519 p_message_text => l_default_msg,
520 p_upgrade_id => l_upgrade_id,
521 p_process_date => sysdate,
522 p_log_type => log_type_i,
523 p_upgrade_name => l_upgrade_name);
524 ota_timezone_upgrade.l_upgrade_status := FALSE;
525 write_log(l_default_msg);
526 ELSE
527 l_time_zone := p_default_timezone;
528
529 FOR cat_chat_bkng_row IN csr_cat_chat_bkng
530 LOOP
531 BEGIN
532
533 UPDATE ota_chats_b
534 SET timezone_code = l_time_zone
535 WHERE chat_id = cat_chat_bkng_row.chat_id
536 AND timezone_code IS NULL;
537
538 EXCEPTION
539 WHEN others THEN
540 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_others_msg);
541 ota_classic_upgrade.add_log_entry(p_table_name => 'ota_chats_b',
542 p_business_group_id => NULL,
543 p_source_primary_key => cat_chat_bkng_row.chat_id,
544 p_object_value => l_upgrade_name,
545 p_message_text => l_err_msg,
546 p_upgrade_id => l_upgrade_id,
547 p_process_date => sysdate,
548 p_log_type => log_type_e,
549 p_upgrade_name => l_upgrade_name);
550 ota_timezone_upgrade.l_upgrade_status := FALSE;
551 write_log(l_err_msg);
552 END;
553
554 /* FOR row_cat_chat_res_bkng IN
555 csr_cat_chat_res_bkng (cat_chat_bkng_row.chat_id)
556 LOOP
557 upd_res_bkng (row_cat_chat_res_bkng.resource_booking_id,
558 row_cat_chat_res_bkng.object_version_number,
559 l_time_zone,
560 l_upgrade_id
561 );
562 END LOOP;*/
563
564 UPDATE ota_resource_bookings
565 SET timezone_code = l_time_zone
566 WHERE chat_id = cat_chat_bkng_row.chat_id
567 AND timezone_code IS NULL;
568
569 END LOOP;
570 END IF;
571
572 EXCEPTION
573 WHEN others THEN
574 l_err_code := SQLCODE;
575 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_others_msg);
576 ota_classic_upgrade.add_log_entry(p_table_name => 'Dummy',
577 p_business_group_id => NULL,
578 p_source_primary_key => l_err_code,
579 p_object_value => l_upgrade_name,
580 p_message_text => l_err_msg,
581 p_upgrade_id => l_upgrade_id,
582 p_process_date => sysdate,
583 p_log_type => log_type_e,
584 p_upgrade_name => l_upgrade_name);
585 ota_timezone_upgrade.l_upgrade_status := FALSE;
586 write_log(l_err_msg);
587 END upd_cat_chat_bkng;
588
589 PROCEDURE upd_cat_frm_bkng(p_default_timezone IN VARCHAR2, l_upgrade_id IN NUMBER)
590 IS
591 CURSOR csr_cat_frm_res_bkng IS
592 SELECT resource_booking_id,
593 object_version_number
594 FROM ota_resource_bookings
595 WHERE forum_id IN
596 (SELECT forum_id
597 FROM ota_frm_obj_inclusions
598 WHERE primary_flag = 'Y'
599 AND object_type = 'C')
600 AND timezone_code IS NULL;
601 BEGIN
602 l_msg := 'Error occurred while upgrading timezone of category forum resource booking';
603
604 IF p_default_timezone IS NULL THEN
605 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_RESOURCE_BOOKINGS',
606 p_business_group_id => NULL,
607 p_source_primary_key => l_upgrade_id,
608 p_object_value => 'Timezone',
609 p_message_text => l_default_msg,
610 p_upgrade_id => l_upgrade_id,
611 p_process_date => sysdate,
612 p_log_type => log_type_i,
613 p_upgrade_name => l_upgrade_name);
614 l_upgrade_status := FALSE;
615 write_log(l_default_msg);
616 ELSE
617 l_time_zone := p_default_timezone;
618
619 /*FOR row_cat_frm_res_bkng IN csr_cat_frm_res_bkng
620 LOOP
621 upd_res_bkng (row_cat_frm_res_bkng.resource_booking_id,
622 row_cat_frm_res_bkng.object_version_number,
623 l_time_zone,
624 l_upgrade_id
625 );
626 END LOOP;*/
627
628 UPDATE ota_resource_bookings
629 SET timezone_code = l_time_zone
630 WHERE forum_id IN
631 (SELECT forum_id
632 FROM ota_frm_obj_inclusions
633 WHERE primary_flag = 'Y'
634 AND object_type = 'C')
635 AND timezone_code IS NULL;
636 END IF;
637
638 EXCEPTION
639 WHEN others THEN
640 l_err_code := SQLCODE;
641 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
642 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_RESOURCE_BOOKINGS',
643 p_business_group_id => NULL,
644 p_source_primary_key => -1,
645 p_object_value => l_upgrade_name,
646 p_message_text => l_err_msg,
647 p_upgrade_id => l_upgrade_id,
648 p_process_date => sysdate,
649 p_log_type => log_type_e,
650 p_upgrade_name => l_upgrade_name);
651 l_upgrade_status := FALSE;
652 write_log(l_err_msg);
653 END upd_cat_frm_bkng;
654
655 PROCEDURE upd_class_ses_res_bkng(p_default_timezone IN VARCHAR2, l_upgrade_id IN NUMBER)
656 IS
657
658 CURSOR csr_class_ses_res_bkng IS
659 SELECT event_id,
660 location_id,
661 training_center_id,
662 timezone,
663 event_type,
664 public_event_flag
665 FROM ota_events;
666 BEGIN
667 FOR class_ses_res_bkng_row IN csr_class_ses_res_bkng
668 LOOP
669 l_time_zone := NULL;
670 l_primary_venue := NULL;
671 upd_classic_data(class_ses_res_bkng_row.event_id,
672 class_ses_res_bkng_row.event_type,
673 class_ses_res_bkng_row.public_event_flag,
674 l_upgrade_id);
675 -- Modified for bug#5110735
676
677 IF class_ses_res_bkng_row.event_type IN('SCHEDULED', 'SELFPACED', 'DEVELOPMENT', 'PROGRAMME')
678 THEN
679
680 IF class_ses_res_bkng_row.timezone IS NULL THEN
681 l_primary_venue := get_primary_venue_id(class_ses_res_bkng_row.event_id);
682
683 IF(l_time_zone IS NULL
684 AND l_primary_venue IS NOT NULL) THEN
685 l_time_zone := get_primary_venue_tz(l_primary_venue);
686 END IF;
687
688 IF(l_time_zone IS NULL
689 AND class_ses_res_bkng_row.location_id IS NOT NULL) THEN
690 l_time_zone := get_location_tz(class_ses_res_bkng_row.location_id);
691 END IF;
692
693 IF(l_time_zone IS NULL
694 AND class_ses_res_bkng_row.training_center_id IS NOT NULL) THEN
695 l_time_zone := get_trngcenter_tz(class_ses_res_bkng_row.training_center_id);
696 END IF;
697
698 IF(l_time_zone IS NULL) THEN
699
700 IF p_default_timezone IS NULL THEN
701 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_EVENTS',
702 p_business_group_id => NULL,
703 p_source_primary_key => class_ses_res_bkng_row.event_id,
704 p_object_value => 'Timezone',
705 p_message_text => l_default_msg,
706 p_upgrade_id => l_upgrade_id,
707 p_process_date => sysdate,
708 p_log_type => log_type_i,
709 p_upgrade_name => l_upgrade_name);
710 ota_timezone_upgrade.l_upgrade_status := FALSE;
711 write_log(l_default_msg);
712 ELSE
713 l_time_zone := p_default_timezone;
714 END IF;
715
716 END IF;
717
718 ELSE
719 l_time_zone := class_ses_res_bkng_row.timezone;
720 END IF;
721
722 IF l_time_zone IS NOT NULL THEN
723 upd_event_bkng(class_ses_res_bkng_row.event_id, l_time_zone, l_upgrade_id);
724 upd_class_chats_bkng(class_ses_res_bkng_row.event_id, l_time_zone, l_upgrade_id);
725 upd_class_frm_bkng(class_ses_res_bkng_row.event_id, l_time_zone, l_upgrade_id);
726 END IF;
727
728 END IF;
729
730 END LOOP;
731
732 EXCEPTION
733 WHEN others THEN
734 l_err_code := SQLCODE;
735 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
736 ota_classic_upgrade.add_log_entry(p_table_name => 'Dummy',
737 p_business_group_id => NULL,
738 p_source_primary_key => l_err_code,
739 p_object_value => l_upgrade_name,
740 p_message_text => l_err_msg,
741 p_upgrade_id => l_upgrade_id,
742 p_process_date => sysdate,
743 p_log_type => log_type_e,
744 p_upgrade_name => l_upgrade_name);
745 ota_timezone_upgrade.l_upgrade_status := FALSE;
746 write_log(l_err_msg);
747 END upd_class_ses_res_bkng;
748
749 PROCEDURE upd_ind_res_bookings(p_default_timezone IN VARCHAR2, l_upgrade_id IN NUMBER)
750 IS
751 CURSOR csr_ind_res_bookings IS
752 SELECT supplied_resource_id
753 FROM ota_resource_bookings
754 WHERE event_id IS NULL
755 AND forum_id IS NULL
756 AND chat_id IS NULL
757 AND timezone_code IS NULL
758 GROUP BY supplied_resource_id;
759
760 CURSOR csr_ind_res_bkng(p_res_id NUMBER) IS
761 SELECT resource_booking_id,
762 object_version_number
763 FROM ota_resource_bookings
764 WHERE supplied_resource_id = p_res_id
765 AND timezone_code IS NULL;
766 BEGIN
767 l_msg := 'Error occurred while upgrading the timezone of independent resources';
768
769 FOR ind_res_bookings_row IN csr_ind_res_bookings
770 LOOP
771 l_time_zone := NULL;
772 l_trng_center_id := NULL;
773 l_loc_id := NULL;
774 get_location_trngcenter_id(ind_res_bookings_row.supplied_resource_id, l_loc_id, l_trng_center_id);
775
776 IF(l_time_zone IS NULL
777 AND l_loc_id IS NOT NULL) THEN
778 l_time_zone := get_location_tz(l_loc_id);
779 END IF;
780
781 IF(l_time_zone IS NULL
782 AND l_trng_center_id IS NOT NULL) THEN
783 l_time_zone := get_trngcenter_tz(l_trng_center_id);
784 END IF;
785
786 IF(l_time_zone IS NULL) THEN
787
788 IF p_default_timezone IS NULL THEN
789 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_RESOURCE_BOOKINGS',
790 p_business_group_id => NULL,
791 p_source_primary_key => ind_res_bookings_row.supplied_resource_id,
792 p_object_value => 'Timezone',
793 p_message_text => l_default_msg,
794 p_upgrade_id => l_upgrade_id,
795 p_process_date => sysdate,
796 p_log_type => log_type_i,
797 p_upgrade_name => l_upgrade_name);
798 ota_timezone_upgrade.l_upgrade_status := FALSE;
799 write_log(l_default_msg);
800 ELSE
801 l_time_zone := p_default_timezone;
802 END IF;
803
804 END IF;
805
806 /*FOR row_ind_res_bkng IN
807 csr_ind_res_bkng (ind_res_bookings_row.supplied_resource_id)
808 LOOP
809 upd_res_bkng (row_ind_res_bkng.resource_booking_id,
810 row_ind_res_bkng.object_version_number,
811 l_time_zone,
812 l_upgrade_id
813 );
814 END LOOP;*/
815
816 UPDATE ota_resource_bookings
817 SET timezone_code = l_time_zone
818 WHERE supplied_resource_id = ind_res_bookings_row.supplied_resource_id
819 AND timezone_code IS NULL;
820
821 END LOOP;
822
823 EXCEPTION
824 WHEN others THEN
825 l_err_code := SQLCODE;
826 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_msg);
827 ota_classic_upgrade.add_log_entry(p_table_name => 'Dummy',
828 p_business_group_id => NULL,
829 p_source_primary_key => l_err_code,
830 p_object_value => l_upgrade_name,
831 p_message_text => l_err_msg,
832 p_upgrade_id => l_upgrade_id,
833 p_process_date => sysdate,
834 p_log_type => log_type_e,
835 p_upgrade_name => l_upgrade_name);
836 ota_timezone_upgrade.l_upgrade_status := FALSE;
837 write_log(l_err_msg);
838 END upd_ind_res_bookings;
839
840 PROCEDURE run_timezone_upgrade(errbuf OUT nocopy VARCHAR2, retcode OUT nocopy VARCHAR2)
841 IS
842 l_upgrade_id NUMBER(9);
843 l_loop_counter number(6):=0;
844 p_default_timezone VARCHAR2(30) := ota_timezone_util.get_server_timezone_code;
845
846
847 CURSOR get_resource_bookings IS
848 SELECT trb.supplied_resource_id,
849 trb.chat_id,
850 trb.forum_id,
851 trb.required_date_from,
852 trb.required_date_to,
853 trb.required_end_time,
854 trb.required_start_time,
855 trb.resource_booking_id,
856 trb.timezone_code,
857 trb.book_entire_period_flag,
858 res.resource_type
859 FROM ota_resource_bookings trb,ota_suppliable_resources res
860 WHERE trb.status = 'C'
861 and res.resource_type in ('T','V')
862 and trb.required_date_to >=(trunc(sysdate)-14)
863 and trb.supplied_resource_id=res.supplied_resource_id
864 order by resource_booking_id;
865
866
867 BEGIN
868 ota_timezone_upgrade.l_upgrade_status := TRUE;
869 SELECT MAX(upgrade_id)
870 INTO l_upgrade_id
871 FROM ota_upgrade_log
872 WHERE upgrade_name = l_upgrade_name;
873
874 IF l_upgrade_id IS NULL THEN
875 l_upgrade_id := 1;
876 ELSE
877 l_upgrade_id := l_upgrade_id + 1;
878 END IF;
879
880 l_others_msg := 'Some error occurred while running the Timezone upgrade process';
881 write_log('Starting Timezone Upgrade Concurrent Process');
882 write_log('Upgrading events and their resources');
883 upd_class_ses_res_bkng(p_default_timezone, l_upgrade_id);
884 write_log('Upgrading Category chats and resources booked against them');
885 upd_cat_chat_bkng(p_default_timezone, l_upgrade_id);
886 write_log('Upgrading Category Forum resource bookings');
887 upd_cat_frm_bkng(p_default_timezone, l_upgrade_id);
888 write_log('Upgrading Independent resource bookings.');
889 upd_ind_res_bookings(p_default_timezone, l_upgrade_id);
890
891
892
893 COMMIT;
894
895 FOR get_resource_bookings_row IN get_resource_bookings
896 LOOP
897
898
899 /*
900 The check double resource booking will execute only if the resource type is trainer and it is not
901 booked to a forum or a chat or the resource type is a venue.
902 */
903 IF((get_resource_bookings_row.resource_type = 'T')
904 AND(get_resource_bookings_row.chat_id IS NOT NULL OR get_resource_bookings_row.forum_id IS NOT NULL)) THEN
905 NULL;
906 Elsif get_resource_bookings_row.resource_type = 'T' or get_resource_bookings_row.resource_type = 'V' then
907 BEGIN
908 if ota_trb_api_procedures.check_double_booking(get_resource_bookings_row.supplied_resource_id,
909 get_resource_bookings_row.required_date_from,
910 get_resource_bookings_row.required_start_time,
911 get_resource_bookings_row.required_date_to,
912 get_resource_bookings_row.required_end_time,
913 get_resource_bookings_row.resource_booking_id,
914 get_resource_bookings_row.book_entire_period_flag,
915 get_resource_bookings_row.timezone_code,
916 get_resource_bookings_row.resource_booking_id)
917 then
918 fnd_message.set_name('OTA','OTA_13395_TRB_RES_DOUBLEBOOK');
919 fnd_message.raise_error;
920 end if;
921
922 EXCEPTION
923 WHEN others THEN
924 l_err_code := SQLCODE;
925 l_err_msg := SUBSTR(sqlerrm, 1, 2000);
926 if l_err_msg like '%OTA%' then
927 l_err_msg := get_msg_text(l_err_msg);
928 else
929 l_err_msg:=nvl(l_err_msg,l_others_msg);
930 end if;
931
932 ota_classic_upgrade.add_log_entry(p_table_name => 'OTA_RESOURCE_BOOKINGS',
933 p_business_group_id => NULL,
934 p_source_primary_key => get_resource_bookings_row.resource_booking_id,
935 p_object_value => l_upgrade_name,
936 p_message_text => l_err_msg,
937 p_upgrade_id => l_upgrade_id,
938 p_process_date => sysdate,
939 p_log_type => log_type_e,
940 p_upgrade_name => l_upgrade_name);
941 write_log('Resource_booking_id: ' || get_resource_bookings_row.resource_booking_id);
942 write_log(l_err_msg);
943 ota_timezone_upgrade.l_upgrade_status := FALSE;
944 END;
945 END IF;
946 l_loop_counter:=l_loop_counter+1;
947 if l_loop_counter >=1000 then
948 l_loop_counter:=0;
949 ota_classic_upgrade.add_log_entry(p_table_name => 'Dummy',
950 p_business_group_id => NULL,
951 p_source_primary_key => get_resource_bookings_row.resource_booking_id,
952 p_object_value => l_upgrade_name,
953 p_message_text => 'Records checked for the double booking validation',
954 p_upgrade_id => l_upgrade_id,
955 p_process_date => sysdate,
956 p_log_type => log_type_i,
957 p_upgrade_name => l_upgrade_name);
958 commit;
959 end if;
960 END LOOP;
961
962
963
964 IF ota_timezone_upgrade.l_upgrade_status = FALSE THEN
965 write_log('Errors have been encountered during this Upgrade process');
966 ELSE
967 write_log('No errors have been encountered during this Upgrade process');
968 END IF;
969
970 EXCEPTION
971 WHEN others THEN
972 l_err_code := SQLCODE;
973 l_err_msg := nvl(SUBSTR(sqlerrm, 1, 2000), l_others_msg);
974 ota_classic_upgrade.add_log_entry(p_table_name => 'Dummy',
975 p_business_group_id => NULL,
976 p_source_primary_key => l_err_code,
977 p_object_value => l_upgrade_name,
978 p_message_text => l_err_msg,
979 p_upgrade_id => l_upgrade_id,
980 p_process_date => sysdate,
981 p_log_type => log_type_e,
982 p_upgrade_name => l_upgrade_name);
983
984 write_log(l_err_msg);
985 END run_timezone_upgrade;
986
987 END ota_timezone_upgrade;
988