[Home] [Help]
PACKAGE BODY: APPS.AMS_AGENDAS_PVT
Source
1 PACKAGE BODY AMS_Agendas_PVT as
2 /*$Header: amsvagnb.pls 120.2 2005/12/29 22:49:55 sikalyan noship $*/
3
4 /*****************************************************************************************/
5 -- NAME AMS_Agendas_PVT
6 --
7 -- HISTORY
8 -- 2/19/2002 gmadana CREATED
9 -- 08/19/2002 gmadana Sessions/Tracks cannot be created/updated/deleted
10 -- for the event schedules which are cancelled/completed/
11 -- archived/on_hold
12 -- 25-feb-2003 soagrawa Fixed bug# 2820297
13 -- 28-mar-2003 soagrawa Added add_language. Bug# 2876033
14 --24-Mar-2005 sikalyan SQL Repository BugFix 4256877
15 --30-Dec-2005 sikalyan Performance BugFix 4898041
16 /*****************************************************************************************/
17
18 G_PACKAGE_NAME CONSTANT VARCHAR2(30):='AMS_Agendas_PVT';
19 G_FILE_NAME CONSTANT VARCHAR2(15):='amsvagnb.pls';
20
21 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
22 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
23 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
24
25 PROCEDURE Rollup_StTime_EdTime (
26 p_agenda_rec IN agenda_rec_type,
27 x_return_status OUT NOCOPY VARCHAR2
28 ) ;
29
30 -- Procedure and function declarations.
31 /*****************************************************************************************/
32 -- Start of Comments
33 --
34 -- NAME
35 -- Create_Agenda
36 --
37 -- PURPOSE
38 -- This procedure is used to create a Agenda (Track/Session)
39 --
40 -- HISTORY
41 -- 02/19/2002 gmadana created
42 -- 04/14/2003 anchaudh modified to fix bug#2886784
43 /*****************************************************************************************/
44
45 PROCEDURE Create_Agenda
46 ( p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
48 p_commit IN VARCHAR2 := FND_API.G_FALSE,
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
50 p_agenda_rec IN agenda_rec_type,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 x_agenda_id OUT NOCOPY NUMBER
55 ) IS
56 l_api_name CONSTANT VARCHAR2(30) := 'Create_Agenda';
57 l_api_version CONSTANT NUMBER := 1.0;
58 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
59 l_return_status VARCHAR2(1);
60 l_agenda_rec agenda_rec_type := p_agenda_rec;
61 l_track_rec agenda_rec_type;
62 l_agenda_id NUMBER;
63 l_agenda_count NUMBER;
64 l_track_id NUMBER;
65 l_event_id NUMBER;
66 l_coordinator_id NUMBER;
67
68
69 CURSOR c_agenda_seq IS
70 SELECT ams_agendas_b_s.NEXTVAL
71 FROM DUAL;
72
73 CURSOR c_agenda_count(l_agenda_id IN NUMBER) IS
74 SELECT count(*)
75 FROM ams_agendas_v
76 WHERE agenda_id = l_agenda_id;
77
78 CURSOR c_general_track IS
79 SELECT *
80 FROM ams_agendas_b
81 WHERE default_track_flag = 'Y'
82 AND active_flag = 'Y'
83 AND parent_id = p_agenda_rec.parent_id;
84
85 l_agenda_row c_general_track%ROWTYPE;
86
87
88 CURSOR c_event_coordinator(id_in IN NUMBER) IS
89 SELECT coordinator_id
90 FROM ams_event_offers_vl
91 WHERE event_offer_id = id_in;
92
93 CURSOR c_event_id(id_in IN NUMBER) IS
94 SELECT parent_id
95 FROM ams_agendas_v
96 WHERE agenda_id = id_in ;
97
98
99 CURSOR c_track_coordinator IS
100 SELECT coordinator_id
101 FROM ams_agendas_v
102 WHERE agenda_id = p_agenda_rec.parent_id;
103
104 CURSOR c_event_timezone(id_in IN NUMBER) IS
105 SELECT timezone_id
106 FROM ams_event_offers_vl
107 WHERE event_offer_id = id_in;
108
109
110
111
112 BEGIN
113 -- Standard Start of API savepoint
114 SAVEPOINT Create_Agenda_PVT;
115
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call ( l_api_version,
118 p_api_version,
119 l_api_name,
120 G_PACKAGE_NAME)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 -- Initialize message list IF p_init_msg_list is set to TRUE.
126 IF FND_API.to_Boolean( p_init_msg_list )
127 THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130
131 -- Initialize API return status to success
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 ----------------------- validate -----------------------
135 IF (AMS_DEBUG_HIGH_ON) THEN
136
137 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
138 END IF;
139 Validate_Agenda
140 ( p_api_version => 1.0
141 ,p_init_msg_list => p_init_msg_list
142 ,p_validation_level => p_validation_level
143 ,x_return_status => l_return_status
144 ,x_msg_count => x_msg_count
145 ,x_msg_data => x_msg_data
146 ,p_agenda_rec => l_agenda_rec
147 );
148 -- If any errors happen abort API.
149 IF l_return_status = FND_API.G_RET_STS_ERROR
150 THEN
151 RAISE FND_API.G_EXC_ERROR;
152 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
153 THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157
158 ------Generating the seq num for agenda id------------------
159 IF l_agenda_rec.agenda_id IS NULL
160 THEN
161 LOOP
162 OPEN c_agenda_seq;
163 FETCH c_agenda_seq INTO l_agenda_rec.agenda_id;
164 CLOSE c_agenda_seq;
165
166 OPEN c_agenda_count(l_agenda_rec.agenda_id);
167 FETCH c_agenda_count INTO l_agenda_count;
168 CLOSE c_agenda_count;
169
170 EXIT WHEN l_agenda_count = 0;
171 END LOOP;
172 END IF;
173
174 /* If we are creating a Session with no track, then we have to create
175 a General Track. For General Track default track falg will be 'Y'.
176 If there is no track, from the JSP page, we send event_offer_id as
177 parent_id and EVEO/EONE as parent_type
178 */
179
180 IF(l_agenda_rec.agenda_type = 'SESSION')
181 THEN
182 IF(l_agenda_rec.parent_type = 'EVEO' OR l_agenda_rec.parent_type = 'EONE')
183 THEN
184 /* Get the General Track. If there is no general Track,
185 Create One.
186 */
187 OPEN c_general_track;
188 FETCH c_general_track INTO l_agenda_row;
189
190 IF c_general_track%NOTFOUND
191 THEN
192 l_track_rec.agenda_name := 'General';
193 l_track_rec.agenda_type := 'TRACK';
194 l_track_rec.parent_id := l_agenda_rec.parent_id;
195 l_track_rec.parent_type := l_agenda_rec.parent_type;
196 l_track_rec.application_id := l_agenda_rec.application_id;
197 l_track_rec.coordinator_id := l_agenda_rec.coordinator_id;
198 l_track_rec.default_track_flag := 'Y';
199
200
201 Create_Agenda
202 ( p_api_version => l_api_version,
203 p_init_msg_list => FND_API.G_FALSE,
204 p_commit => FND_API.G_FALSE,
205 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
206 p_agenda_rec => l_track_rec,
207 x_return_status => l_return_status,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data,
210 x_agenda_id => x_agenda_id
211 );
212
213 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
214 THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
217 THEN
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220
221 /* Replacing the parent_id and parent_type for Session */
222 l_agenda_rec.parent_type := 'TRACK';
223 l_agenda_rec.parent_id := x_agenda_id;
224
225 ELSE
226 l_agenda_rec.parent_type := l_agenda_row.agenda_type;
227 l_agenda_rec.parent_id := l_agenda_row.agenda_id;
228
229 END IF; --IF c_general_track%NOTFOUND
230
231 CLOSE c_general_track;
232
233 END IF; --IF(l_agenda_rec.parent_type = 'EVEO')
234
235 END IF; --IF(l_agenda_rec.agenda_type = 'SESSION')
236
237
238 /* If the coordinator_id is NULL for Session, then default it with
239 that of Track. If the Coordinator_id is NULL for Track, then
240 default it with that of EVEO/EONE.
241 */
242 --anchaudh:Start Commenting for bug#2886784.
243 /* IF(p_agenda_rec.agenda_type = 'TRACK')
244 THEN
245 IF(p_agenda_rec.coordinator_id is NULL)
246 THEN
247
248 OPEN c_event_coordinator(p_agenda_rec.parent_id);
249 fetch c_event_coordinator INTO l_coordinator_id;
250 CLOSE c_event_coordinator;
251
252 END IF;
253 ELSIF(p_agenda_rec.agenda_type = 'SESSION')
254 THEN
255 IF(p_agenda_rec.coordinator_id is NULL)
256 THEN
257
258 OPEN c_track_coordinator;
259 IF c_track_coordinator%FOUND
260 THEN
261 fetch c_track_coordinator INTO l_coordinator_id;
262 ELSE */
263 /* If track Coordinator is NULL, copy from Event Schedule */
264 /* Getting the track id and event id*/
265
266 /* OPEN c_event_id(p_agenda_rec.parent_id);
267 fetch c_event_id INTO l_event_id;
268 CLOSE c_event_id;
269
270
271 OPEN c_event_coordinator(l_event_id);
272 fetch c_event_coordinator INTO l_coordinator_id;
273 CLOSE c_event_coordinator;
274
275 END IF;
276 CLOSE c_track_coordinator;
277
278 END IF; --IF(p_agenda_rec.coordinator_id is NULL)
279 END IF; */ --ELSIF(p_agenda_rec.agenda_type = 'SESSION')
280 --anchaudh:End Commenting for bug#2886784.
281
282
283 /* If the timzone is null for Session then copy the time zone from
284 event schedule
285 */
286 IF(l_agenda_rec.agenda_type = 'SESSION')
287 THEN
288 IF(l_agenda_rec.timezone_id IS NULL)
289 THEN
290
291 OPEN c_event_id(p_agenda_rec.parent_id);
292 fetch c_event_id INTO l_event_id;
293 CLOSE c_event_id;
294
295 OPEN c_event_timezone(l_event_id);
296 FETCH c_event_timezone INTO l_agenda_rec.timezone_id;
297 CLOSE c_event_timezone;
298
299 END IF;
300 END IF;
301
302 ----------------------------create----------------------------
303 INSERT INTO AMS_AGENDAS_B
304 (
305 agenda_id,
306 setup_type_id,
307 last_update_date,
308 last_updated_by,
309 creation_date,
310 created_by,
311 last_update_login,
312 object_version_number,
313 application_id,
314 agenda_type,
315 room_id,
316 active_flag,
317 default_track_flag,
318 start_date_time,
319 end_date_time,
320 coordinator_id,
321 timezone_id,
322 parent_type,
323 parent_id,
324 attribute_category,
325 attribute1,
326 attribute2,
327 attribute3,
328 attribute4,
329 attribute5,
330 attribute6,
331 attribute7,
332 attribute8,
333 attribute9,
334 attribute10,
335 attribute11,
336 attribute12,
337 attribute13,
338 attribute14,
339 attribute15
340 )
341 VALUES
342 (
343 l_agenda_rec.agenda_id,
344 l_agenda_rec.setup_type_id,
345 sysdate,
346 FND_GLOBAL.User_Id,
347 sysdate,
348 FND_GLOBAL.User_Id,
349 FND_GLOBAL.Conc_Login_Id,
350 1, -- object_version_number
351 l_agenda_rec.application_id,
352 l_agenda_rec.agenda_type,
353 l_agenda_rec.room_id,
354 NVL(l_agenda_rec.active_flag, 'Y'),
355 NVL(l_agenda_rec.default_track_flag, 'N'),
356 l_agenda_rec.start_date_time,
357 l_agenda_rec.end_date_time,
358 nvl(l_agenda_rec.coordinator_id,l_coordinator_id),
359 l_agenda_rec.timezone_id,
360
361 l_agenda_rec.parent_type,
362 l_agenda_rec.parent_id,
363
364 l_agenda_rec.attribute_category,
365 l_agenda_rec.attribute1,
366 l_agenda_rec.attribute2,
367 l_agenda_rec.attribute3,
368 l_agenda_rec.attribute4,
369 l_agenda_rec.attribute5,
370 l_agenda_rec.attribute6,
371 l_agenda_rec.attribute7,
372 l_agenda_rec.attribute8,
373 l_agenda_rec.attribute9,
374 l_agenda_rec.attribute10,
375 l_agenda_rec.attribute11,
376 l_agenda_rec.attribute12,
377 l_agenda_rec.attribute13,
378 l_agenda_rec.attribute14,
379 l_agenda_rec.attribute15
380 );
381
382 INSERT INTO ams_agendas_tl(
383 agenda_id,
384 language,
385 last_update_date,
386 last_updated_by,
387 creation_date,
388 created_by,
389 last_update_login,
390 source_lang,
391 agenda_name,
392 description
393 )
394 SELECT
395 l_agenda_rec.agenda_id,
396 l.language_code,
397 SYSDATE,
398 FND_GLOBAL.user_id,
399 SYSDATE,
400 FND_GLOBAL.user_id,
401 FND_GLOBAL.conc_login_id,
402 USERENV('LANG'),
403 l_agenda_rec.agenda_name,
404 l_agenda_rec.description
405 FROM fnd_languages l
406 WHERE l.installed_flag in ('I', 'B')
407 AND NOT EXISTS(
408 SELECT NULL
409 FROM ams_agendas_tl t
410 WHERE t.agenda_id = l_agenda_rec.agenda_id
411 AND t.language = l.language_code );
412
413 -- set OUT value
414 x_agenda_id := l_agenda_rec.agenda_id;
415
416 /* Roll up the times to Track and then to Event Level */
417 /* Rollup_StTime_EdTime (
418 p_agenda_rec => l_agenda_rec,
419 x_return_status => x_return_status
420 );
421
422 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
423 RAISE Fnd_Api.g_exc_unexpected_error;
424 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
425 RAISE Fnd_Api.g_exc_error;
426 END IF; */
427
428 -- Standard check of p_commit.
429 IF FND_API.To_Boolean ( p_commit )
430 THEN
431 COMMIT WORK;
432 END IF;
433
434 -- Standard call to get message count AND IF count is 1, get message info.
435 FND_MSG_PUB.Count_AND_Get
436 ( p_count => x_msg_count,
437 p_data => x_msg_data,
438 p_encoded => FND_API.G_FALSE
439 );
440
441 EXCEPTION
442 WHEN FND_API.G_EXC_ERROR THEN
443 ROLLBACK TO Create_Agenda_PVT;
444 x_return_status := FND_API.G_RET_STS_ERROR ;
445
446 FND_MSG_PUB.Count_AND_Get
447 ( p_count => x_msg_count,
448 p_data => x_msg_data,
449 p_encoded => FND_API.G_FALSE
450 );
451
452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
453 ROLLBACK TO Create_Agenda_PVT;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
455
456 FND_MSG_PUB.Count_AND_Get
457 ( p_count => x_msg_count,
458 p_data => x_msg_data,
459 p_encoded => FND_API.G_FALSE
460 );
461
462 WHEN OTHERS THEN
463 ROLLBACK TO Create_Agenda_PVT;
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
465
466 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
467 THEN
468 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
469 END IF;
470
471 FND_MSG_PUB.Count_AND_Get
472 ( p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => FND_API.G_FALSE
475 );
476
477 END Create_Agenda;
478
479 /*****************************************************************************************/
480 -- Start of Comments
481 --
482 -- NAME
483 -- Update_Agenda
484 --
485 -- PURPOSE
486 -- This procedure is to update a Agenda (Track/Session)
487 --
488 -- HISTORY
489 -- 02/19/2002 gmadana created
490 --
491 /*****************************************************************************************/
492
493 PROCEDURE Update_Agenda
494 ( p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
496 p_commit IN VARCHAR2 := FND_API.G_FALSE,
497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
498 p_agenda_rec IN agenda_rec_type,
499 x_return_status OUT NOCOPY VARCHAR2,
500 x_msg_count OUT NOCOPY NUMBER,
501 x_msg_data OUT NOCOPY VARCHAR2
502 ) IS
503
504 l_api_name CONSTANT VARCHAR2(30) := 'Update_Agenda';
505 l_api_version CONSTANT NUMBER := 1.0;
506 l_return_status VARCHAR2(1);
507 l_agenda_rec agenda_rec_type;
508 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
509 l_act_res_id NUMBER;
510 l_obj_ver_num NUMBER;
511 l_dateDiff NUMBER := 0;
512 l_stdateDiff NUMBER := 0;
513 l_eddateDiff NUMBER := 0;
514 l_oldStdate DATE := NULL;
515 l_oldEddate DATE :=NULL;
516
517
518 CURSOR c_resources(l_session_id IN NUMBER) IS
519 SELECT activity_resource_id,object_version_number
520 FROM ams_act_resources
521 WHERE ACT_RESOURCE_USED_BY_ID = p_agenda_rec.agenda_id
522 AND role_cd = 'COORDINATOR'
523 AND resource_id = p_agenda_rec.coordinator_id;
524
525 CURSOR c_olddate IS
526 SELECT start_date_time, end_date_time
527 FROM ams_agendas_v
528 WHERE agenda_id = p_agenda_rec.agenda_id;
529
530
531 BEGIN
532 -- Standard Start of API savepoint
533 SAVEPOINT Update_Agenda_PVT;
534
535 -- Standard call to check for call compatibility.
536 IF NOT FND_API.Compatible_API_Call ( l_api_version,
537 p_api_version,
538 l_api_name,
539 G_PACKAGE_NAME)
540 THEN
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END IF;
543
544 -- Initialize message list IF p_init_msg_list is set to TRUE.
545 IF FND_API.to_Boolean( p_init_msg_list ) THEN
546 FND_MSG_PUB.initialize;
547 END IF;
548
549 -- Initialize API return status to success
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 complete_agenda_rec
553 (
554 p_agenda_rec,
555 l_agenda_rec
556 );
557
558
559 IF (AMS_DEBUG_HIGH_ON) THEN
560
561
562
563
564
565 AMS_Utility_PVT.debug_message(l_api_name||': check items');
566
567
568 END IF;
569 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
570 THEN
571 Validate_Agenda_Items
572 ( p_agenda_rec => l_agenda_rec,
573 p_validation_mode => JTF_PLSQL_API.g_update,
574 x_return_status => l_return_status
575 );
576
577 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
578 THEN
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
581 THEN
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584 END IF;
585
586 IF (AMS_DEBUG_HIGH_ON) THEN
587
588
589
590 AMS_Utility_PVT.debug_message(l_full_name ||': check records');
591
592 END IF;
593
594 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
595 THEN
596 validate_agenda_record(
597 p_agenda_rec => p_agenda_rec,
598 p_complete_rec => l_agenda_rec,
599 x_return_status => l_return_status
600 );
601
602 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
603 RAISE FND_API.g_exc_unexpected_error;
604 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
605 RAISE FND_API.g_exc_error;
606 END IF;
607 END IF;
608
609 /* If we are updating the Coordinator for Session, check whether that
610 coordinator(new) is attached as Resources for that Session.If so delete
611 him from resources and then update the Session. For Track, no resources
612 are attached, so the following logic is not needed for Tracks.
613 */
614
615 IF(l_agenda_rec.agenda_type = 'SESSION')
616 THEN
617 OPEN c_resources(l_agenda_rec.agenda_id);
618 FETCH c_resources INTO l_act_res_id, l_obj_ver_num;
619
620 IF (AMS_DEBUG_HIGH_ON) THEN
621
622
623
624 AMS_Utility_PVT.debug_message('resource_id :' || l_act_res_id);
625
626 END IF;
627 IF (AMS_DEBUG_HIGH_ON) THEN
628
629 AMS_Utility_PVT.debug_message('obj_ver_num :' || l_obj_ver_num);
630 END IF;
631
632 WHILE c_resources%FOUND LOOP
633
634 AMS_ACTRESOURCE_PVT.DELETE_ACT_RESOURCE
635 ( p_api_version => l_api_version,
636 p_init_msg_list => FND_API.G_FALSE,
637 p_commit => FND_API.G_FALSE,
638 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
639 x_return_status => l_return_status,
640 x_msg_count => x_msg_count,
641 x_msg_data => x_msg_data,
642 p_act_Resource_id => l_act_res_id,
643 p_object_version => l_obj_ver_num
644 );
645
646 IF l_return_status = FND_API.g_ret_sts_error THEN
647 RAISE FND_API.g_exc_error;
648 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
649 RAISE FND_API.g_exc_unexpected_error;
650 END IF; -- IF l_return_status = FND_API.g_ret_sts_error THEN
651
652 FETCH c_resources INTO l_act_res_id, l_obj_ver_num;
653
654 END LOOP; -- WHILE(c_resources%FOUND)
655 CLOSE c_resources;
656
657 END IF; --IF(p_agenda_rec.agenda_type = 'SESSION')
658
659 /* If Session date is changed, then chnage the date of the resources
660 associated to them and then update their status to 'UNCONFIRMED'.
661 If the Session start time is increased, make start time of resources
662 (associated to it) whose start time is greater than it, equal to it.
663 If the Session end time is decreased, make end time of resources
664 (associated to it) whose end time is lesser than it, equal to it.
665 If the start time is decreased or end time is increased, it will have no
666 effect on the resources.
667 */
668
669 OPEN c_olddate;
670 FETCH c_olddate INTO l_oldStdate, l_oldEddate ;
671 CLOSE c_olddate;
672
673 IF(l_oldStdate <> p_agenda_rec.start_date_time
674 OR l_oldEddate <> p_agenda_rec.end_date_time)
675 THEN
676 l_dateDiff := trunc(p_agenda_rec.start_date_time - l_oldStdate);
677 l_stdateDiff := l_oldStdate - p_agenda_rec.start_date_time;
678 l_eddateDiff := p_agenda_rec.end_date_time - l_oldEddate;
679
680 IF (AMS_DEBUG_HIGH_ON) THEN
681
682
683
684 AMS_Utility_PVT.debug_message('l_dateDiff :' || l_DateDiff);
685
686 END IF;
687 IF (AMS_DEBUG_HIGH_ON) THEN
688
689 AMS_Utility_PVT.debug_message('l_StdateDiff :' || l_stdateDiff);
690 END IF;
691 IF (AMS_DEBUG_HIGH_ON) THEN
692
693 AMS_Utility_PVT.debug_message('l_EddateDiff :' || l_eddateDiff);
694 END IF;
695
696
697 IF( ABS(l_dateDiff) > 0)
698 THEN
699
700 UPDATE ams_act_resources
701 SET system_status_code = 'UNCONFIRMED',
702 object_version_number = object_version_number + 1,
703 user_status_id = ( SELECT user_status_id
704 FROM AMS_USER_STATUSES_B
705 WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
706 AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
707 -- added by soagrawa on 25-feb-2003 for bug# 2820297
708 AND DEFAULT_FLAG = 'Y'),
709 start_date_time = start_date_time + l_DateDiff,
710 end_date_time = end_date_time + l_DateDiff
711 WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
712 AND system_status_code <> 'CANCELLED';
713
714 /* ELSIF( (p_agenda_rec.start_date_time > l_oldStdate
715 AND p_agenda_rec.end_date_time < l_oldEddate)
716 OR
717 (p_agenda_rec.start_date_time = l_oldStdate
718 AND p_agenda_rec.end_date_time < l_oldEddate)
719 OR
720 (p_agenda_rec.start_date_time > l_oldStdate
721 AND p_agenda_rec.end_date_time = l_oldEddate)
722 )
723 THEN
724 UPDATE ams_act_resources
725 SET object_version_number = object_version_number + 1,
726 start_date_time = p_agenda_rec.start_date_time,
727 end_date_time = p_agenda_rec.end_date_time
728 WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
729 AND ( (start_date_time < p_agenda_rec.start_date_time
730 AND end_date_time > p_agenda_rec.end_date_time)
731 OR
732 (start_date_time = p_agenda_rec.start_date_time
733 AND end_date_time > p_agenda_rec.end_date_time)
734 OR
735 (end_date_time = p_agenda_rec.end_date_time
736 AND start_date_time < p_agenda_rec.start_date_time) )
737 AND system_status_code <> 'CANCELLED';
738 END IF; */
739
740 ELSIF( (p_agenda_rec.start_date_time > l_oldStdate
741 AND p_agenda_rec.end_date_time < l_oldEddate) )
742 THEN
743 IF (AMS_DEBUG_HIGH_ON) THEN
744
745 AMS_Utility_PVT.debug_message('Entered for both dates');
746 END IF;
747
748 UPDATE ams_act_resources
749 SET object_version_number = object_version_number + 1,
750 -- start_date_time = p_agenda_rec.start_date_time,
751 -- end_date_time = p_agenda_rec.end_date_time,
752 system_status_code = 'UNCONFIRMED',
753 user_status_id = ( SELECT user_status_id
754 FROM AMS_USER_STATUSES_B
755 WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
756 AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
757 -- added by soagrawa on 25-feb-2003 for bug# 2820297
758 AND DEFAULT_FLAG = 'Y')
759 WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
760 AND start_date_time < p_agenda_rec.start_date_time
761 AND end_date_time > p_agenda_rec.end_date_time
762 AND system_status_code <> 'CANCELLED';
763
764 ELSIF ( p_agenda_rec.start_date_time = l_oldStdate
765 AND
766 p_agenda_rec.end_date_time < l_oldEddate)
767 THEN
768 IF (AMS_DEBUG_HIGH_ON) THEN
769
770 AMS_Utility_PVT.debug_message('Entered for end date change');
771 END IF;
772
773 UPDATE ams_act_resources
774 SET object_version_number = object_version_number + 1,
775 -- end_date_time = p_agenda_rec.end_date_time,
776 system_status_code = 'UNCONFIRMED',
777 user_status_id = ( SELECT user_status_id
778 FROM AMS_USER_STATUSES_B
779 WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
780 AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
781 -- added by soagrawa on 25-feb-2003 for bug# 2820297
782 AND DEFAULT_FLAG = 'Y')
783 WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
784 AND end_date_time > p_agenda_rec.end_date_time
785 AND system_status_code <> 'CANCELLED';
786
787 ELSIF ( p_agenda_rec.end_date_time = l_oldEddate
788 AND p_agenda_rec.start_date_time > l_oldStdate)
789 THEN
790 IF (AMS_DEBUG_HIGH_ON) THEN
791
792 AMS_Utility_PVT.debug_message('Entered for start date change');
793 END IF;
794
795 UPDATE ams_act_resources
796 SET object_version_number = object_version_number + 1,
797 -- start_date_time = p_agenda_rec.start_date_time,
798 system_status_code = 'UNCONFIRMED',
799 user_status_id = ( SELECT user_status_id
800 FROM AMS_USER_STATUSES_B
801 WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
802 AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
803 -- added by soagrawa on 25-feb-2003 for bug# 2820297
804 AND DEFAULT_FLAG = 'Y')
805 WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
806 AND start_date_time < p_agenda_rec.start_date_time
807 AND system_status_code <> 'CANCELLED';
808
809 END IF;
810
811 END IF;
812
813
814 -------------- Perform the database operation UPDATE----------------------
815
816 UPDATE AMS_AGENDAS_B
817 SET
818 setup_type_id = l_agenda_rec.setup_type_id
819 ,last_update_date = sysdate
820 ,last_updated_by = FND_GLOBAL.User_Id
821 ,last_update_login = FND_GLOBAL.Conc_Login_Id
822 ,object_version_number = l_agenda_rec.object_version_number+1
823 ,room_id = l_agenda_rec.room_id
824 ,start_date_time = l_agenda_rec.start_date_time
825 ,end_date_time = l_agenda_rec.end_date_time
826 ,coordinator_id = l_agenda_rec.coordinator_id
827 ,timezone_id = l_agenda_rec.timezone_id
828 ,parent_type = l_agenda_rec.parent_type
829 ,parent_id = l_agenda_rec.parent_id
830 ,attribute_category = l_agenda_rec.attribute_category
831 ,attribute1 = l_agenda_rec.attribute1
832 ,attribute2 = l_agenda_rec.attribute2
833 ,attribute3 = l_agenda_rec.attribute3
834 ,attribute4 = l_agenda_rec.attribute4
835 ,attribute5 = l_agenda_rec.attribute5
836 ,attribute6 = l_agenda_rec.attribute6
837 ,attribute7 = l_agenda_rec.attribute7
838 ,attribute8 = l_agenda_rec.attribute8
839 ,attribute9 = l_agenda_rec.attribute9
840 ,attribute10 = l_agenda_rec.attribute10
841 ,attribute11 = l_agenda_rec.attribute11
842 ,attribute12 = l_agenda_rec.attribute12
843 ,attribute13 = l_agenda_rec.attribute13
844 ,attribute14 = l_agenda_rec.attribute14
845 ,attribute15 = l_agenda_rec.attribute15
846 WHERE agenda_id = l_agenda_rec.agenda_id
847 AND object_version_number = l_agenda_rec.object_version_number;
848
849 IF (SQL%NOTFOUND)
850 THEN
851
852 /*Error, check the msg level and added an error message to the
853 API message list
854 */
855 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
856 THEN -- MMSG
857 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
858 FND_MSG_PUB.Add;
859 END IF;
860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
861 END IF;
862
863 UPDATE ams_agendas_tl SET
864 agenda_name = l_agenda_rec.agenda_name,
865 description = l_agenda_rec.description,
866 last_update_date = SYSDATE,
867 last_updated_by = FND_GLOBAL.user_id,
868 last_update_login = FND_GLOBAL.conc_login_id,
869 source_lang = USERENV('LANG')
870 WHERE agenda_id = l_agenda_rec.agenda_id
871 AND USERENV('LANG') IN (language, source_lang);
872
873 IF (SQL%NOTFOUND) THEN
874 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
875 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
876 FND_MSG_PUB.add;
877 END IF;
878 RAISE FND_API.g_exc_error;
879 END IF;
880
881
882 /* Roll up the times to Track and then to Event Level */
883 /* Rollup_StTime_EdTime (
884 p_agenda_rec => l_agenda_rec,
885 x_return_status => x_return_status
886 ); */
887
888 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
889 RAISE Fnd_Api.g_exc_unexpected_error;
890 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
891 RAISE Fnd_Api.g_exc_error;
892 END IF;
893
894 -- Standard check of p_commit.
895 IF FND_API.To_Boolean ( p_commit )
896 THEN
897 COMMIT WORK;
898 END IF;
899
900 -- Standard call to get message count AND IF count is 1, get message info.
901 FND_MSG_PUB.Count_AND_Get
902 ( p_count => x_msg_count,
903 p_data => x_msg_data,
904 p_encoded => FND_API.G_FALSE
905 );
906
907 EXCEPTION
908 WHEN FND_API.G_EXC_ERROR THEN
909 ROLLBACK TO Update_Agenda_PVT;
910 x_return_status := FND_API.G_RET_STS_ERROR ;
911 FND_MSG_PUB.Count_AND_Get
912 ( p_count => x_msg_count,
913 p_data => x_msg_data,
914 p_encoded => FND_API.G_FALSE
915 );
916
917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918 ROLLBACK TO Update_Agenda_PVT;
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
920 FND_MSG_PUB.Count_AND_Get
921 ( p_count => x_msg_count,
922 p_data => x_msg_data,
923 p_encoded => FND_API.G_FALSE
924 );
925
926 WHEN OTHERS THEN
927 ROLLBACK TO Update_Agenda_PVT;
928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
929
930 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
931 THEN
932 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
933 END IF;
934
935 FND_MSG_PUB.Count_AND_Get
936 ( p_count => x_msg_count,
937 p_data => x_msg_data,
938 p_encoded => FND_API.G_FALSE
939 );
940
941 END Update_Agenda;
942
943 /*****************************************************************************************/
944 -- Start of Comments
945 --
946 -- NAME
947 -- Delete_Agenda
948 --
949 -- PURPOSE
950 -- This procedure is to delete a Agenda (Track/Session)
951 --
952 -- HISTORY
953 -- 02/19/2002 gmadana created
954 --
955 /*****************************************************************************************/
956
957 PROCEDURE Delete_Agenda
958 ( p_api_version IN NUMBER,
959 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
960 p_commit IN VARCHAR2 := FND_API.G_FALSE,
961
962 p_agenda_id IN NUMBER,
963 p_object_version IN NUMBER,
964
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2
968 ) IS
969
970 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Agenda';
971 l_api_version CONSTANT NUMBER := 1.0;
972 l_return_status VARCHAR2(1);
973 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
974 l_agenda_rec agenda_rec_type;
975 l_act_res_id NUMBER;
976 l_obj_ver_num NUMBER;
977 l_agenda_id NUMBER;
978 l_count NUMBER;
979
980 CURSOR c_agenda IS
981 SELECT *
982 FROM ams_agendas_b
983 WHERE agenda_id = p_agenda_id;
984
985 CURSOR c_resources(l_session_id IN NUMBER) IS
986 SELECT activity_resource_id,object_version_number
987 FROM ams_act_resources
988 WHERE act_resource_used_by_id = l_session_id;
989
990 CURSOR c_event_status IS
991 SELECT count(event_offer_id)
992 FROM ams_event_offers_vl
993 WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
994 AND event_offer_id = ( SELECT parent_id
995 FROM ams_agendas_v
996 WHERE agenda_id = ( SELECT parent_id
997 FROM ams_agendas_v
998 WHERE agenda_id = p_agenda_id));
999
1000 CURSOR c_sessions IS
1001 SELECT agenda_id, object_version_number
1002 FROM ams_agendas_v
1003 WHERE parent_id = p_agenda_id;
1004
1005 l_agenda_row c_agenda%ROWTYPE;
1006
1007
1008 BEGIN
1009 -- Standard Start of API savepoint
1010 SAVEPOINT Delete_Agenda_PVT;
1011
1012 -- Standard call to check for call compatibility.
1013 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1014 p_api_version,
1015 l_api_name,
1016 G_PACKAGE_NAME)
1017 THEN
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF;
1020
1021 -- Initialize message list IF p_init_msg_list is set to TRUE.
1022 IF FND_API.to_Boolean( p_init_msg_list )
1023 THEN
1024 FND_MSG_PUB.initialize;
1025 END IF;
1026
1027 -- Initialize API return status to success
1028 x_return_status := FND_API.G_RET_STS_SUCCESS;
1029
1030 ------------------------ delete ------------------------
1031 IF (AMS_DEBUG_HIGH_ON) THEN
1032
1033 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
1034 END IF;
1035
1036 OPEN c_event_status;
1037 FETCH c_event_status INTO l_count;
1038 CLOSE c_event_status;
1039
1040 IF(l_count > 0)
1041 THEN
1042 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1043 THEN
1044 Fnd_Message.set_name('AMS', 'AMS_NO_SESSION');
1045 Fnd_Msg_Pub.ADD;
1046 END IF;
1047 RAISE FND_API.g_exc_error;
1048 END IF;
1049
1050
1051 OPEN c_agenda;
1052 FETCH c_agenda into l_agenda_row;
1053 CLOSE c_agenda;
1054
1055 /* When deleting the Session, delete all the Resources attached to it */
1056 IF (l_agenda_row.agenda_type = 'SESSION')
1057 THEN
1058 OPEN c_resources(p_agenda_id);
1059 FETCH c_resources INTO l_act_res_id, l_obj_ver_num ;
1060
1061 WHILE c_resources%FOUND LOOP
1062
1063 AMS_ACTRESOURCE_PVT.DELETE_ACT_RESOURCE
1064 ( p_api_version => l_api_version,
1065 p_init_msg_list => FND_API.G_FALSE,
1066 p_commit => FND_API.G_FALSE,
1067 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1068 x_return_status => l_return_status,
1069 x_msg_count => x_msg_count,
1070 x_msg_data => x_msg_data,
1071 p_act_Resource_id => l_act_res_id,
1072 p_object_version => l_obj_ver_num
1073 );
1074
1075 IF l_return_status = FND_API.g_ret_sts_error THEN
1076 RAISE FND_API.g_exc_error;
1077 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1078 RAISE FND_API.g_exc_unexpected_error;
1079 END IF;
1080
1081 FETCH c_resources INTO l_act_res_id, l_obj_ver_num ;
1082
1083 END LOOP;
1084 CLOSE c_resources;
1085 -- To remove TRACK, first remove Sessions and resources attached.
1086 ELSIF (l_agenda_row.agenda_type = 'TRACK')
1087 THEN
1088 OPEN c_sessions;
1089 FETCH c_sessions INTO l_agenda_id, l_obj_ver_num ;
1090
1091 WHILE c_sessions%FOUND LOOP
1092 /* Deleting the Seesion */
1093 UPDATE ams_agendas_b
1094 SET active_flag = 'N',
1095 object_version_number = object_version_number + 1
1096 WHERE agenda_id = l_agenda_id
1097 AND object_version_number = l_obj_ver_num;
1098
1099 /* Deleting the resources attached to deleted Session */
1100 OPEN c_resources(l_agenda_id);
1101 FETCH c_resources INTO l_act_res_id, l_obj_ver_num ;
1102
1103 WHILE c_resources%FOUND LOOP
1104
1105 AMS_ACTRESOURCE_PVT.DELETE_ACT_RESOURCE
1106 ( p_api_version => l_api_version,
1107 p_init_msg_list => FND_API.G_FALSE,
1108 p_commit => FND_API.G_FALSE,
1109 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1110 x_return_status => l_return_status,
1111 x_msg_count => x_msg_count,
1112 x_msg_data => x_msg_data,
1113 p_act_Resource_id => l_act_res_id,
1114 p_object_version => l_obj_ver_num
1115 );
1116
1117 IF l_return_status = FND_API.g_ret_sts_error THEN
1118 RAISE FND_API.g_exc_error;
1119 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1120 RAISE FND_API.g_exc_unexpected_error;
1121 END IF;
1122
1123 FETCH c_resources INTO l_act_res_id, l_obj_ver_num ;
1124
1125 END LOOP; --WHILE c_resources%FOUND LOOP
1126 CLOSE c_resources;
1127
1128 FETCH c_sessions INTO l_agenda_id, l_obj_ver_num ;
1129
1130 END LOOP; -- WHILE c_sessions%FOUND LOOP
1131 CLOSE c_sessions;
1132
1133 END IF;
1134
1135 /* Deleting the Object (Track/Session) passed in */
1136 UPDATE ams_agendas_b
1137 SET active_flag = 'N',
1138 object_version_number = object_version_number + 1
1139 WHERE agenda_id = p_agenda_id
1140 AND object_version_number = p_object_version;
1141
1142 IF (SQL%NOTFOUND) THEN
1143 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1144 THEN
1145 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1146 FND_MSG_PUB.add;
1147 END IF;
1148 RAISE FND_API.g_exc_error;
1149 END IF;
1150
1151
1152 ---Roll up the times to Track and then to Event Level---
1153 ---Creating the l_agenda_rec------------
1154 l_agenda_rec.agenda_id := p_agenda_id;
1155 l_agenda_rec.parent_id := l_agenda_row.parent_id;
1156
1157 /* Rollup_StTime_EdTime (
1158 p_agenda_rec => l_agenda_rec,
1159 x_return_status => x_return_status
1160 ); */
1161
1162 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1163 RAISE Fnd_Api.g_exc_unexpected_error;
1164 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
1165 RAISE Fnd_Api.g_exc_error;
1166 END IF;
1167
1168 -------------------- finish --------------------------
1169
1170 -- Standard check of p_commit.
1171 IF FND_API.To_Boolean ( p_commit )
1172 THEN
1173 COMMIT WORK;
1174 END IF;
1175
1176 -- Standard call to get message count AND IF count is 1, get message info.
1177 FND_MSG_PUB.Count_AND_Get
1178 ( p_count => x_msg_count,
1179 p_data => x_msg_data,
1180 p_encoded => FND_API.G_FALSE
1181 );
1182
1183 EXCEPTION
1184
1185 WHEN FND_API.G_EXC_ERROR THEN
1186 ROLLBACK TO Delete_Agenda_PVT;
1187 x_return_status := FND_API.G_RET_STS_ERROR ;
1188
1189 FND_MSG_PUB.Count_AND_Get
1190 ( p_count => x_msg_count,
1191 p_data => x_msg_data,
1192 p_encoded => FND_API.G_FALSE
1193 );
1194
1195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1196 ROLLBACK TO Delete_Agenda_PVT;
1197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1198
1199 FND_MSG_PUB.Count_AND_Get
1200 ( p_count => x_msg_count,
1201 p_data => x_msg_data,
1202 p_encoded => FND_API.G_FALSE
1203 );
1204
1205 WHEN OTHERS THEN
1206 ROLLBACK TO Delete_Agenda_PVT;
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1208
1209 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1210 THEN
1211 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1212 END IF;
1213
1214 FND_MSG_PUB.Count_AND_Get
1215 ( p_count => x_msg_count,
1216 p_data => x_msg_data,
1217 p_encoded => FND_API.G_FALSE
1218 );
1219
1220 END Delete_Agenda;
1221
1222 /*****************************************************************************************/
1223 -- Start of Comments
1224 --
1225 -- NAME
1226 -- Lock_Agenda
1227 --
1228 -- PURPOSE
1229 -- This procedure is to lock a agenda record
1230 --
1231 -- HISTORY
1232 -- 02/19/2002 gmadana created
1233 --
1234 /*****************************************************************************************/
1235
1236 PROCEDURE Lock_Agenda
1237 ( p_api_version IN NUMBER,
1238 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1239 p_agenda_id IN NUMBER,
1240 p_object_version IN NUMBER,
1241 x_return_status OUT NOCOPY VARCHAR2,
1242 x_msg_count OUT NOCOPY NUMBER,
1243 x_msg_data OUT NOCOPY VARCHAR2
1244 ) IS
1245
1246 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Agenda';
1247 l_api_version CONSTANT NUMBER := 1.0;
1248 l_return_status VARCHAR2(1);
1249 l_agenda_id NUMBER;
1250
1251
1252 CURSOR c_agenda IS
1253 SELECT agenda_id
1254 FROM AMS_AGENDAS_V
1255 WHERE agenda_id = p_agenda_id
1256 AND object_version_number = p_object_version
1257 FOR UPDATE of agenda_id NOWAIT;
1258
1259 BEGIN
1260 -- Standard call to check for call compatibility.
1261 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1262 p_api_version,
1263 l_api_name,
1264 G_PACKAGE_NAME)
1265 THEN
1266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1267 END IF;
1268
1269 -- Initialize message list if p_init_msg_list is set to TRUE.
1270 IF FND_API.to_Boolean( p_init_msg_list )
1271 THEN
1272 FND_MSG_PUB.initialize;
1273 END IF;
1274
1275 -- Initialize API return status to success
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277
1278 -- Perform the database operation
1279 OPEN c_agenda;
1280 FETCH c_agenda INTO l_agenda_id;
1281 IF (c_agenda%NOTFOUND) THEN
1282 CLOSE c_agenda;
1283
1284 /* Error, check the msg level and added an error message to the
1285 API message list
1286 */
1287 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1288 THEN -- MMSG
1289 FND_MESSAGE.Set_Name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1290 FND_MSG_PUB.Add;
1291 END IF;
1292 RAISE FND_API.G_EXC_ERROR;
1293 END IF;
1294 CLOSE c_agenda;
1295
1296 -- Standard call to get message count AND IF count is 1, get message info.
1297 FND_MSG_PUB.Count_AND_Get
1298 ( p_count => x_msg_count,
1299 p_data => x_msg_data,
1300 p_encoded => FND_API.G_FALSE
1301 );
1302 EXCEPTION
1303 WHEN FND_API.G_EXC_ERROR THEN
1304 x_return_status := FND_API.G_RET_STS_ERROR ;
1305 FND_MSG_PUB.Count_AND_Get
1306 ( p_count => x_msg_count,
1307 p_data => x_msg_data,
1308 p_encoded => FND_API.G_FALSE
1309 );
1310
1311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1313 FND_MSG_PUB.Count_AND_Get
1314 ( p_count => x_msg_count,
1315 p_data => x_msg_data,
1316 p_encoded => FND_API.G_FALSE
1317 );
1318
1319 /* WHEN AMS_Utility_PVT.agenda_locked THEN
1320 x_return_status := FND_API.g_ret_sts_error;
1321 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1322 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1323 FND_MSG_PUB.add;
1324 END IF; */
1325
1326 FND_MSG_PUB.Count_AND_Get
1327 ( p_count => x_msg_count,
1328 p_data => x_msg_data,
1329 p_encoded => FND_API.G_FALSE
1330 );
1331
1332 WHEN OTHERS THEN
1333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1334 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1335 THEN
1336 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1337 END IF;
1338
1339 FND_MSG_PUB.Count_AND_Get
1340 ( p_count => x_msg_count,
1341 p_data => x_msg_data,
1342 p_encoded => FND_API.G_FALSE
1343 );
1344
1345 END Lock_Agenda;
1346
1347 /*****************************************************************************************/
1348 -- Start of Comments
1349 --
1350 -- NAME
1351 -- Validate_Agenda
1352 --
1353 -- PURPOSE
1354 -- This procedure is to validate an agenda record
1355 --
1356 -- HISTORY
1357 -- 02/19/2002 gmadana created
1358 --
1359 /*****************************************************************************************/
1360
1361 PROCEDURE Validate_Agenda
1362 ( p_api_version IN NUMBER,
1363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1364 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1365 p_agenda_rec IN agenda_rec_type,
1366 x_return_status OUT NOCOPY VARCHAR2,
1367 x_msg_count OUT NOCOPY NUMBER,
1368 x_msg_data OUT NOCOPY VARCHAR2
1369 ) IS
1370
1371 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Agenda';
1372 l_api_version CONSTANT NUMBER := 1.0;
1373 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
1374 l_return_status VARCHAR2(1);
1375 l_agenda_rec agenda_rec_type := p_agenda_rec;
1376
1377 -- l_default_act_resource_rec act_Resource_rec_type;
1378 BEGIN
1379 -- Standard call to check for call compatibility.
1380 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1381 p_api_version,
1382 l_api_name,
1383 G_PACKAGE_NAME)
1384 THEN
1385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1386 END IF;
1387
1388 -- Initialize message list if p_init_msg_list is set to TRUE.
1389 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1390 FND_MSG_PUB.initialize;
1391 END IF;
1392
1393 -- Initialize API return status to success
1394 x_return_status := FND_API.G_RET_STS_SUCCESS;
1395
1396 IF (AMS_DEBUG_HIGH_ON) THEN
1397
1398
1399
1400 AMS_Utility_PVT.debug_message(l_full_name||': check items');
1401
1402 END IF;
1403 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
1404 THEN
1405 Validate_Agenda_Items
1406 ( p_agenda_rec => l_agenda_rec,
1407 p_validation_mode => JTF_PLSQL_API.g_create,
1408 x_return_status => l_return_status
1409 );
1410
1411 -- If any errors happen abort API.
1412 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1413 THEN
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1416 THEN
1417 RAISE FND_API.G_EXC_ERROR;
1418 END IF;
1419
1420 END IF;
1421
1422 -- Perform cross attribute validation and missing attribute checks. Record
1423 -- level validation.
1424 IF (AMS_DEBUG_HIGH_ON) THEN
1425
1426 AMS_Utility_PVT.debug_message(l_full_name||': check record level');
1427 END IF;
1428 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
1429 THEN
1430 Validate_Agenda_Record(
1431 p_agenda_rec => l_agenda_rec,
1432 x_return_status => l_return_status
1433 );
1434
1435 -- If any errors happen abort API.
1436 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1437 RAISE FND_API.G_EXC_ERROR;
1438 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1440 END IF;
1441
1442 END IF;
1443
1444 -------------------- finish --------------------------
1445 FND_MSG_PUB.count_and_get(
1446 p_encoded => FND_API.g_false,
1447 p_count => x_msg_count,
1448 p_data => x_msg_data
1449 );
1450
1451 EXCEPTION
1452 WHEN FND_API.G_EXC_ERROR THEN
1453 x_return_status := FND_API.G_RET_STS_ERROR ;
1454 FND_MSG_PUB.Count_AND_Get
1455 ( p_count => x_msg_count,
1456 p_data => x_msg_data,
1457 p_encoded => FND_API.G_FALSE
1458 );
1459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1461 FND_MSG_PUB.Count_AND_Get
1462 ( p_count => x_msg_count,
1463 p_data => x_msg_data,
1464 p_encoded => FND_API.G_FALSE
1465 );
1466 WHEN OTHERS THEN
1467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1468 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1469 THEN
1470 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1471 END IF;
1472 FND_MSG_PUB.Count_AND_Get
1473 ( p_count => x_msg_count,
1474 p_data => x_msg_data,
1475 p_encoded => FND_API.G_FALSE
1476 );
1477
1478 END Validate_Agenda;
1479
1480 /*****************************************************************************************/
1481 -- PROCEDURE
1482 -- check_agenda_req_items
1483 --
1484 -- HISTORY
1485 -- 02/20/2002 gmadana Created.
1486 /*****************************************************************************************/
1487 PROCEDURE check_agenda_req_items(
1488 p_agenda_rec IN agenda_rec_type,
1489 x_return_status OUT NOCOPY VARCHAR2
1490 )
1491 IS
1492 BEGIN
1493
1494 x_return_status := FND_API.g_ret_sts_success;
1495
1496 ------------------------ application_id --------------------------
1497 IF (p_agenda_rec.application_id IS NULL OR p_agenda_rec.application_id = FND_API.g_miss_num) THEN
1498 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1499 FND_MESSAGE.set_name('AMS', 'AMS_API_NO_APPLICATION_ID');
1500 FND_MSG_PUB.add;
1501 END IF;
1502 x_return_status := FND_API.g_ret_sts_error;
1503 RETURN;
1504 END IF;
1505 ------------------------ parent_id--------------------------
1506 IF (p_agenda_rec.parent_id IS NULL OR p_agenda_rec.parent_id = FND_API.g_miss_num) THEN
1507 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1508 IF (p_agenda_rec.agenda_type = 'TRACK')
1509 THEN
1510 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_PARENT_OFFER_ID');
1511 -- ELSIF(p_agenda_rec.agenda_type = 'SESSION')
1512 -- FND_MESSAGE.set_name('AMS', 'AMS_NO_TRACK_ID');
1513 END IF;
1514 FND_MSG_PUB.add;
1515 END IF;
1516 x_return_status := FND_API.g_ret_sts_error;
1517 RETURN;
1518 END IF;
1519
1520 ------------------------ parent_type--------------------------
1521 IF (p_agenda_rec.parent_type IS NULL OR p_agenda_rec.parent_type = FND_API.g_miss_char) THEN
1522 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1523 IF (p_agenda_rec.agenda_type = 'TRACK')
1524 THEN
1525 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_PARENT_OFFER_TYPE');
1526 ELSIF(p_agenda_rec.agenda_type = 'SESSION')THEN
1527 FND_MESSAGE.set_name('AMS', 'AMS_NO_TRACK_TYPE');
1528 END IF;
1529 FND_MSG_PUB.add;
1530 END IF;
1531 x_return_status := FND_API.g_ret_sts_error;
1532 RETURN;
1533 END IF;
1534
1535 ------------------------ agenda_type--------------------------
1536 IF (p_agenda_rec.agenda_type IS NULL OR p_agenda_rec.agenda_type = FND_API.g_miss_char) THEN
1537 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1538 FND_MESSAGE.set_name('AMS', 'AMS_NO_AGENDA_TYPE');
1539 FND_MSG_PUB.add;
1540 END IF;
1541 x_return_status := FND_API.g_ret_sts_error;
1542 RETURN;
1543 END IF;
1544
1545
1546 END check_agenda_req_items;
1547
1548 /*****************************************************************************************/
1549 -- PROCEDURE
1550 -- check_agenda_uk_items
1551 --
1552 -- HISTORY
1553 -- 02/20/2002 gmadana Created.
1554 /*****************************************************************************************/
1555 PROCEDURE check_agenda_uk_items(
1556 p_agenda_rec IN agenda_rec_type,
1557 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1558 x_return_status OUT NOCOPY VARCHAR2
1559 )
1560 IS
1561 l_valid_flag VARCHAR2(1);
1562 l_dummy NUMBER;
1563
1564 cursor c_track_name IS
1565 SELECT 1 FROM DUAL
1566 WHERE EXISTS (SELECT 1 from ams_agendas_v
1567 WHERE agenda_name = p_agenda_rec.agenda_name
1568 AND parent_id = p_agenda_rec.parent_id);
1569
1570
1571 BEGIN
1572 x_return_status := FND_API.g_ret_sts_success;
1573
1574 -- For create_agenda, when agenda_id is passed in, we need to
1575 -- check if this agenda_id is unique.
1576
1577 IF p_validation_mode = JTF_PLSQL_API.g_create
1578 AND p_agenda_rec.agenda_id IS NOT NULL
1579 THEN
1580 IF AMS_Utility_PVT.check_uniqueness(
1581 'ams_agendas_v',
1582 'agenda_id = ' || p_agenda_rec.agenda_id
1583 ) = FND_API.g_false
1584 THEN
1585 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1586 FND_MESSAGE.set_name('AMS', 'AMS_EVO_DUPLICATE_ID');
1587 FND_MSG_PUB.add;
1588 END IF;
1589 x_return_status := FND_API.g_ret_sts_error;
1590 RETURN;
1591 END IF;
1592 END IF;
1593
1594 /* IF the agenda_type = 'TRACK' then the Track Name + Parent Id has to
1595 be unique. If it is SESSION then no validation is necessary.
1596 */
1597
1598 IF(p_agenda_rec.agenda_type = 'TRACK')
1599 THEN
1600 OPEN c_track_name;
1601 fetch c_track_name into l_dummy;
1602 close c_track_name;
1603 IF l_dummy = 1 THEN
1604 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1605 FND_MESSAGE.set_name ('AMS', 'AMS_DUP_NAME');
1606 FND_MSG_PUB.add;
1607 END IF;
1608 x_return_status := FND_API.g_ret_sts_error;
1609 RETURN;
1610 END IF;
1611 END IF;
1612
1613 END check_agenda_uk_items;
1614
1615 /*****************************************************************************************/
1616 -- PROCEDURE
1617 -- check_agenda_fk_items
1618 --
1619 -- HISTORY
1620 -- 02/20/2002 gmadana Created.
1621 /*****************************************************************************************/
1622 PROCEDURE check_agenda_fk_items(
1623 p_agenda_rec IN agenda_rec_type,
1624 x_return_status OUT NOCOPY VARCHAR2
1625 )
1626 IS
1627 l_table_name VARCHAR2(30);
1628 l_pk_name VARCHAR2(30);
1629 l_pk_value VARCHAR2(30);
1630 l_pk_data_type NUMBER;
1631 l_additional_where_clause VARCHAR2(4000);
1632 l_where_clause VARCHAR2(80) := null;
1633 BEGIN
1634 x_return_status := FND_API.g_ret_sts_success;
1635
1636 --------------------- application_id ------------------------
1637 IF p_agenda_rec.application_id <> FND_API.g_miss_num AND
1638 p_agenda_rec.application_id is NOT NULL
1639 THEN
1640 IF AMS_Utility_PVT.check_fk_exists(
1641 'fnd_application',
1642 'application_id',
1643 p_agenda_rec.application_id
1644 ) = FND_API.g_false
1645 THEN
1646 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1647 THEN
1648 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_APP_ID');
1649 FND_MSG_PUB.add;
1650 END IF;
1651 x_return_status := FND_API.g_ret_sts_error;
1652 RETURN;
1653 END IF;
1654 END IF;
1655
1656
1657 ----------------------- parent_id/parent_type ------------------------
1658 IF (p_agenda_rec.parent_type = 'EVEO' OR p_agenda_rec.parent_type = 'EONE')
1659 THEN
1660 IF p_agenda_rec.parent_id <> FND_API.g_miss_num
1661 AND p_agenda_rec.parent_id IS NOT NULL THEN
1662 IF AMS_Utility_PVT.check_fk_exists(
1663 'ams_event_offers_vl',
1664 'event_offer_id',
1665 p_agenda_rec.parent_id
1666 ) = FND_API.g_false
1667 THEN
1668 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1669 THEN
1670 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_PARENT_OFFER');
1671 FND_MSG_PUB.add;
1672 END IF;
1673 x_return_status := FND_API.g_ret_sts_error;
1674 RETURN;
1675 END IF;
1676 END IF;
1677 ELSIF p_agenda_rec.parent_type = 'TRACK'
1678 THEN
1679 IF p_agenda_rec.parent_id <> FND_API.g_miss_num
1680 AND p_agenda_rec.parent_id IS NOT NULL THEN
1681 IF AMS_Utility_PVT.check_fk_exists(
1682 'ams_agendas_v',
1683 'agenda_id',
1684 p_agenda_rec.parent_id
1685 ) = FND_API.g_false
1686 THEN
1687 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1688 THEN
1689 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_PARENT_OFFER');
1690 FND_MSG_PUB.add;
1691 END IF;
1692 x_return_status := FND_API.g_ret_sts_error;
1693 RETURN;
1694 END IF;
1695 END IF;
1696 END IF;
1697
1698
1699 ----------------------- TIMEZONE_ID ------------------------
1700 IF p_agenda_rec.timezone_id <> FND_API.g_miss_num
1701 AND p_agenda_rec.timezone_id IS NOT NULL THEN
1702 IF AMS_Utility_PVT.check_fk_exists(
1703 'fnd_timezones_b',
1704 'upgrade_tz_id',
1705 p_agenda_rec.timezone_id
1706 ) = FND_API.g_false
1707 THEN
1708 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1709 THEN
1710 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_TIMEZONE_ID');
1711 FND_MSG_PUB.add;
1712 END IF;
1713 x_return_status := FND_API.g_ret_sts_error;
1714 RETURN;
1715 END IF;
1716 END IF;
1717
1718 ----------------------- room_id ------------------------
1719 IF p_agenda_rec.room_id <> FND_API.g_miss_num
1720 AND p_agenda_rec.room_id IS NOT NULL THEN
1721 IF AMS_Utility_PVT.check_fk_exists(
1722 'ams_venues_vl',
1723 'venue_id',
1724 p_agenda_rec.room_id
1725 ) = FND_API.g_false
1726 THEN
1727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1728 THEN
1729 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_VENUE_ID');
1730 FND_MSG_PUB.add;
1731 END IF;
1732 x_return_status := FND_API.g_ret_sts_error;
1733 RETURN;
1734 END IF;
1735 END IF;
1736
1737 --------------- COORDINATOR_ID -------------------------
1738 IF p_agenda_rec.COORDINATOR_ID <> FND_API.g_miss_num
1739 THEN
1740 l_table_name := 'HZ_PARTIES';
1741 l_pk_name := 'PARTY_ID';
1742 l_pk_value := p_agenda_rec.COORDINATOR_ID;
1743 IF AMS_Utility_PVT.Check_FK_Exists (
1744 p_table_name => l_table_name
1745 ,p_pk_name => l_pk_name
1746 ,p_pk_value => l_pk_value
1747 ) = FND_API.G_FALSE
1748 THEN
1749 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1750 THEN
1751 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_BAD_RESOURCE');
1752 FND_MSG_PUB.add;
1753 END IF;
1754 x_return_status := FND_API.G_RET_STS_ERROR;
1755 -- If any errors happen abort API/Procedure.
1756 RETURN;
1757 END IF; -- check_fk_exists
1758 END IF;
1759
1760
1761 END check_agenda_fk_items;
1762
1763 /*****************************************************************************************/
1764 -- PROCEDURE
1765 -- check_agenda_lookup_items
1766 --
1767 -- HISTORY
1768 -- 02/20/2002 gmadana Created.
1769 /*****************************************************************************************/
1770 PROCEDURE check_agenda_lookup_items(
1771 p_agenda_rec IN agenda_rec_type,
1772 x_return_status OUT NOCOPY VARCHAR2
1773 )
1774 IS
1775 BEGIN
1776
1777 x_return_status := FND_API.g_ret_sts_success;
1778
1779 ----------------------- agenda_type ------------------------
1780 /*IF p_agenda_rec.agenda_type <> FND_API.g_miss_char
1781 AND p_agenda_rec.agenda_type IS NOT NULL
1782 THEN
1783 IF AMS_Utility_PVT.check_lookup_exists(
1784 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
1785 p_lookup_code => p_agenda_rec.agenda_type
1786 ) = FND_API.g_false
1787 THEN
1788 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1789 THEN
1790 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_TYPE');
1791 FND_MSG_PUB.add;
1792 END IF;
1793 x_return_status := FND_API.g_ret_sts_error;
1794 RETURN;
1795 END IF;
1796 END IF;*/
1797
1798
1799 END check_agenda_lookup_items;
1800
1801 /*****************************************************************************************/
1802 -- PROCEDURE
1803 -- check_agenda_flag_items
1804 --
1805 -- HISTORY
1806 -- 02/20/2002 gmadana Created
1807 /*****************************************************************************************/
1808 PROCEDURE check_agenda_flag_items(
1809 p_agenda_rec IN agenda_rec_type,
1810 x_return_status OUT NOCOPY VARCHAR2
1811 )
1812 IS
1813 BEGIN
1814
1815 x_return_status := FND_API.g_ret_sts_success;
1816
1817
1818 ----------------------- active_flag ------------------------
1819 IF p_agenda_rec.active_flag <> FND_API.g_miss_char
1820 AND p_agenda_rec.active_flag IS NOT NULL
1821 THEN
1822 IF AMS_Utility_PVT.is_Y_or_N(p_agenda_rec.active_flag) = FND_API.g_false
1823 THEN
1824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1825 THEN
1826 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_ACTIVE_FLAG');
1827 FND_MSG_PUB.add;
1828 END IF;
1829 x_return_status := FND_API.g_ret_sts_error;
1830 RETURN;
1831 END IF;
1832 END IF;
1833
1834 ----------------------- DEFAULT_TRACK_FLAG ------------------------
1835 IF p_agenda_rec.default_track_flag <> FND_API.g_miss_char
1836 AND p_agenda_rec.default_track_flag IS NOT NULL
1837 THEN
1838 IF AMS_Utility_PVT.is_Y_or_N(p_agenda_rec.default_track_flag) = FND_API.g_false
1839 THEN
1840 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1841 THEN
1842 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_TRACK_FLAG');
1843 FND_MSG_PUB.add;
1844 END IF;
1845 x_return_status := FND_API.g_ret_sts_error;
1846 RETURN;
1847 END IF;
1848 END IF;
1849
1850
1851 END check_agenda_flag_items;
1852
1853
1854 /*****************************************************************************************/
1855 -- Start of Comments
1856 --
1857 -- NAME
1858 -- Validate_Agenda_Items
1859 --
1860 -- PURPOSE
1861 -- This procedure is to validate Agenda items
1862 --
1863 /*****************************************************************************************/
1864
1865 PROCEDURE Validate_Agenda_Items
1866 ( p_agenda_rec IN agenda_rec_type,
1867 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1868 x_return_status OUT NOCOPY VARCHAR2
1869 ) IS
1870
1871 l_table_name VARCHAR2(30);
1872 l_pk_name VARCHAR2(30);
1873 l_pk_value VARCHAR2(30);
1874 l_where_clause VARCHAR2(2000);
1875
1876 l_event_id NUMBER;
1877 l_track_id NUMBER;
1878 l_count NUMBER;
1879 l_event_stdate DATE;
1880 l_event_eddate DATE;
1881 l_parent_id NUMBER;
1882 l_start_date DATE;
1883 l_end_date DATE;
1884
1885 l_strdate VARCHAR2(30);
1886 l_strdate1 VARCHAR2(30);
1887
1888
1889 CURSOR c_get_event_dates(l_offer_id IN NUMBER) IS
1890 SELECT event_start_date_time, event_end_date_time
1891 FROM ams_event_offers_all_b
1892 WHERE event_offer_id = l_offer_id;
1893
1894 CURSOR c_get_event_id(id_in IN NUMBER) IS
1895 SELECT parent_id
1896 FROM ams_agendas_v
1897 WHERE agenda_id = id_in;
1898
1899 CURSOR c_get_resource_dates(id_in IN NUMBER) IS
1900 SELECT min(start_date_time), max(end_date_time)
1901 FROM ams_act_resources
1902 WHERE act_resource_used_by_id = id_in
1903 and arc_act_resource_used_by = 'SESSION'
1904 and system_status_code = 'CONFIRMED';
1905
1906 CURSOR c_parent_status IS
1907 SELECT count(event_offer_id)
1908 FROM ams_event_offers_all_b
1909 WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
1910 AND event_offer_id = p_agenda_rec.parent_id;
1911
1912 CURSOR c_event_status IS
1913 SELECT count(event_offer_id)
1914 FROM ams_event_offers_all_b
1915 WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
1916 AND event_offer_id = ( SELECT parent_id
1917 FROM ams_agendas_b
1918 WHERE agenda_id = p_agenda_rec.parent_id);
1919
1920
1921
1922
1923 BEGIN
1924
1925 -- Initialize API/Procedure return status to success
1926 x_return_status := FND_API.G_RET_STS_SUCCESS;
1927
1928 -------------------------- Update Mode ----------------------------
1929 -- check if the p_agenda_rec has any columns that should not be updated at this
1930 -- stage as per the business logic.
1931 -- for example, changes to source_code should not be allowed at any update.
1932 -- Also when the event is in active stage, changes to marketing message and
1933 -- budget related columns should not be allowed.
1934
1935 /* IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message('before ok_items'); END IF;
1936 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
1937 check_evo_update_ok_items(
1938 p_agenda_rec => p_agenda_rec,
1939 x_return_status => x_return_status
1940 );
1941
1942 IF x_return_status <> FND_API.g_ret_sts_success THEN
1943 RETURN;
1944 END IF;
1945 END IF; */
1946
1947 /* IF p_validation_mode = JTF_PLSQL_API.g_update THEN
1948 open c_get_resource_dates(p_agenda_rec.agenda_id);
1949 fetch c_get_resource_dates into l_start_date, l_end_date;
1950 close c_get_resource_dates;
1951 if (p_agenda_rec.START_DATE_TIME > l_start_date
1952 OR p_agenda_rec.END_DATE_TIME < l_end_date)
1953 THEN
1954 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1955 THEN
1956 Fnd_Message.set_name('AMS', 'AMS_EVT_RES_DATE_NOT_FIT_IN');
1957 Fnd_Msg_Pub.ADD;
1958 END IF;
1959 RAISE FND_API.g_exc_error;
1960 END IF;
1961 END IF; */
1962
1963
1964 --------------------------------------Create mode--------------------------
1965 IF (AMS_DEBUG_HIGH_ON) THEN
1966
1967 AMS_UTILITY_PVT.debug_message('Checking uk_items');
1968 END IF;
1969 check_agenda_uk_items(
1970 p_agenda_rec => p_agenda_rec,
1971 p_validation_mode => p_validation_mode,
1972 x_return_status => x_return_status
1973 );
1974
1975 -------------------------- Create or Update Mode ----------------------------
1976 IF (AMS_DEBUG_HIGH_ON) THEN
1977
1978 AMS_UTILITY_PVT.debug_message('Checking req_items');
1979 END IF;
1980 check_agenda_req_items(
1981 p_agenda_rec => p_agenda_rec,
1982 x_return_status => x_return_status
1983 );
1984
1985 IF x_return_status <> FND_API.g_ret_sts_success THEN
1986 RETURN;
1987 END IF;
1988
1989 IF (AMS_DEBUG_HIGH_ON) THEN
1990
1991
1992
1993 AMS_UTILITY_PVT.debug_message('Checking fk_items');
1994
1995 END IF;
1996 check_agenda_fk_items(
1997 p_agenda_rec => p_agenda_rec,
1998 x_return_status => x_return_status
1999 );
2000 IF x_return_status <> FND_API.g_ret_sts_success THEN
2001 RETURN;
2002 END IF;
2003
2004 IF (AMS_DEBUG_HIGH_ON) THEN
2005
2006
2007
2008 AMS_UTILITY_PVT.debug_message('Checking lookup_items');
2009
2010 END IF;
2011 check_agenda_lookup_items(
2012 p_agenda_rec => p_agenda_rec,
2013 x_return_status => x_return_status
2014 );
2015 IF x_return_status <> FND_API.g_ret_sts_success THEN
2016 RETURN;
2017 END IF;
2018
2019 IF (AMS_DEBUG_HIGH_ON) THEN
2020
2021
2022
2023 AMS_UTILITY_PVT.debug_message('Checking flag_items');
2024
2025 END IF;
2026 check_agenda_flag_items(
2027 p_agenda_rec => p_agenda_rec,
2028 x_return_status => x_return_status
2029 );
2030 IF x_return_status <> FND_API.g_ret_sts_success THEN
2031 RETURN;
2032 END IF;
2033
2034 /* If the Event Schedule is CANCELLED/COMPLETED/ARCHIVED /ON_HOLD/CLOSED
2035 donot create any SESSIONS
2036 */
2037
2038 IF (p_agenda_rec.parent_type = 'EVEO'
2039 OR
2040 p_agenda_rec.parent_type = 'EONE'
2041 )
2042 THEN
2043 OPEN c_parent_status;
2044 FETCH c_parent_status INTO l_count;
2045 CLOSE c_parent_status;
2046
2047 IF(l_count > 0)
2048 THEN
2049 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2050 THEN
2051 IF p_agenda_rec.agenda_type = 'SESSION'
2052 THEN
2053 Fnd_Message.set_name('AMS', 'AMS_NO_SESSION');
2054 Fnd_Msg_Pub.ADD;
2055 ELSIF p_agenda_rec.agenda_type = 'TRACK'
2056 THEN
2057 Fnd_Message.set_name('AMS', 'AMS_NO_TRACK');
2058 Fnd_Msg_Pub.ADD;
2059 END IF;
2060 END IF;
2061 RAISE FND_API.g_exc_error;
2062 END IF;
2063 ELSIF(p_agenda_rec.parent_type = 'TRACK')
2064 THEN
2065 OPEN c_event_status;
2066 FETCH c_event_status INTO l_count;
2067 CLOSE c_event_status;
2068
2069 IF(l_count > 0)
2070 THEN
2071 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2072 THEN
2073 Fnd_Message.set_name('AMS', 'AMS_NO_SESSION');
2074 Fnd_Msg_Pub.ADD;
2075 END IF;
2076 RAISE FND_API.g_exc_error;
2077 END IF;
2078 END IF;
2079
2080
2081 /* End Date time has to be greater than Start date time */
2082
2083 IF(p_agenda_rec.start_date_time > p_agenda_rec.end_date_time)
2084 THEN
2085 -- IF (AMS_DEBUG_HIGH_ON) THEN Ams_Utility_Pvt.debug_message('The End time is lesser than Start time'); END IF;
2086 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2087 THEN
2088 Fnd_Message.set_name('AMS', 'AMS_EDTIME_LS_STTIME');
2089 Fnd_Msg_Pub.ADD;
2090 END IF;
2091 RAISE FND_API.g_exc_error;
2092 END IF; -- (p_agenda_rec.start_date_time > p_agenda_rec.end_date_time)
2093
2094
2095 /* If we are creating Session, check whether the date of Session is within
2096 the Date Range of Event Schedule for which it is created.
2097 */
2098 IF (AMS_DEBUG_HIGH_ON) THEN
2099
2100 AMS_UTILITY_PVT.debug_message('Checking the Date range');
2101 END IF;
2102 IF (p_agenda_rec.agenda_type = 'SESSION')
2103 THEN
2104
2105 IF(p_agenda_rec.parent_type = 'TRACK')
2106 THEN
2107 OPEN c_get_event_id(p_agenda_rec.parent_id);
2108 FETCH c_get_event_id into l_event_id;
2109 CLOSE c_get_event_id;
2110
2111 OPEN c_get_event_dates(l_event_id);
2112 FETCH c_get_event_dates into l_event_stdate, l_event_eddate;
2113 CLOSE c_get_event_dates;
2114 ELSE
2115 OPEN c_get_event_dates(p_agenda_rec.parent_id);
2116 FETCH c_get_event_dates into l_event_stdate, l_event_eddate;
2117 CLOSE c_get_event_dates;
2118 END IF;
2119
2120 /* If start time and end time of Session are 12:00 AM, we used to consider
2121 the duration of session as 24 Hr. This is no longer valid. So following
2122 code is commented.
2123
2124 IF (l_event_stdate is not null)
2125 THEN
2126 l_strdate := to_char(l_event_stdate, 'dd-MM-rrrr');
2127 IF (AMS_DEBUG_HIGH_ON) THEN
2128
2129 AMS_UTILITY_PVT.debug_message('Date string '|| l_strdate);
2130 END IF;
2131 l_strdate1 := l_strdate ||' '|| '00:00';
2132 IF (AMS_DEBUG_HIGH_ON) THEN
2133
2134 AMS_UTILITY_PVT.debug_message('event start date '|| l_strdate1);
2135 END IF;
2136 l_event_stdate := to_date (l_strdate1, 'dd-mm-yyyy hh24:mi');
2137 END IF;
2138
2139 IF (l_event_eddate is not null)
2140 THEN
2141 l_strdate := to_char(l_event_eddate, 'dd-MM-rrrr');
2142 l_strdate1 := l_strdate ||' '|| '23:59';
2143 IF (AMS_DEBUG_HIGH_ON) THEN
2144
2145 AMS_UTILITY_PVT.debug_message('event end date '|| l_strdate1);
2146 END IF;
2147 l_event_eddate := to_date (l_strdate1, 'dd-mm-yyyy hh24:mi');
2148 END IF;
2149
2150 IF(to_char(l_event_eddate,'HH24:MI') = '00:00')
2151 THEN
2152 l_strdate := to_char(l_event_eddate, 'DD-MM-YYYY');
2153 l_strdate1 := l_strdate ||' '|| '23:59';
2154 l_event_eddate := to_date (l_strdate1, 'DD-MM-YYYY HH24:MI');
2155 END IF;
2156 */
2157
2158
2159 /* IF (AMS_DEBUG_HIGH_ON) THEN Ams_Utility_Pvt.debug_message('Session st date' ||p_agenda_rec.start_date_time ); END IF;
2160 IF (AMS_DEBUG_HIGH_ON) THEN
2161
2162 Ams_Utility_Pvt.debug_message('Session ed date' ||p_agenda_rec.end_date_time );
2163 END IF;
2164 IF (AMS_DEBUG_HIGH_ON) THEN
2165
2166 Ams_Utility_Pvt.debug_message('Session st date' ||to_date(to_char(p_agenda_rec.start_date_time,'DD-MM-YYYY'),'DD-MM-YYYY') );
2167 END IF;
2168 IF (AMS_DEBUG_HIGH_ON) THEN
2169
2170 Ams_Utility_Pvt.debug_message('Session ed date' ||to_date(to_char(p_agenda_rec.end_date_time,'DD-MM-YYYY'),'DD-MM-YYYY'));
2171 END IF;
2172 IF (AMS_DEBUG_HIGH_ON) THEN
2173
2174 Ams_Utility_Pvt.debug_message('Event st date' ||to_date(to_char(l_event_stdate,'DD-MM-YYYY'),'DD-MM-YYYY'));
2175 END IF;
2176 IF (AMS_DEBUG_HIGH_ON) THEN
2177
2178 Ams_Utility_Pvt.debug_message('Event ed date' ||to_date(to_char(l_event_eddate,'DD-MM-YYYY'),'DD-MM-YYYY'));
2179 END IF;
2180 IF (AMS_DEBUG_HIGH_ON) THEN
2181
2182 Ams_Utility_Pvt.debug_message('Event st time' || to_char(l_event_stdate, 'HH24:MI'));
2183 END IF;
2184 IF (AMS_DEBUG_HIGH_ON) THEN
2185
2186 Ams_Utility_Pvt.debug_message('Event Ed time' || to_char(l_event_eddate, 'HH24:MI'));
2187 END IF;
2188 */
2189
2190 /* The Session date has to be with in the date range of event. If the
2191 Session date is equal to event start date then session start time
2192 cannot be lesser than event start time. If the Session date is equal
2193 to event end date, then Session end time cannot be greater than event
2194 end time.
2195 */
2196
2197 IF( to_date(to_char(p_agenda_rec.start_date_time,'DD-MM-YYYY'),'DD-MM-YYYY' ) > to_date(to_char(l_event_eddate,'DD-MM-YYYY'),'DD-MM-YYYY') OR
2198 to_date(to_char(p_agenda_rec.start_date_time,'DD-MM-YYYY'),'DD-MM-YYYY') < to_date(to_char(l_event_stdate,'DD-MM-YYYY'),'DD-MM-YYYY'))
2199 THEN
2200 IF (AMS_DEBUG_HIGH_ON) THEN
2201
2202 Ams_Utility_Pvt.debug_message('Came to check with event dates');
2203 END IF;
2204 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2205 THEN
2206 Fnd_Message.set_name('AMS', 'AMS_SESSION_LS_EVENT_DATE');
2207 Fnd_Msg_Pub.ADD;
2208 END IF;
2209 RAISE FND_API.g_exc_error;
2210 END IF; -- end of start_date_time < l_event_start_date_time
2211
2212 IF(to_date(to_char(p_agenda_rec.start_date_time,'DD-MM-YYYY'),'DD-MM-YYYY') = to_date(to_char(l_event_stdate,'DD-MM-YYYY'),'DD-MM-YYYY'))
2213 THEN
2214 IF( p_agenda_rec.start_date_time < l_event_stdate )
2215 THEN
2216 IF (AMS_DEBUG_HIGH_ON) THEN
2217
2218 Ams_Utility_Pvt.debug_message('Came to check with event start time');
2219 END IF;
2220 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2221 THEN
2222 Fnd_Message.set_name('AMS', 'AMS_SESSION_LS_EVENT_TIME');
2223 Fnd_Msg_Pub.ADD;
2224 END IF;
2225 RAISE FND_API.g_exc_error;
2226 END IF; --IF( to_date(to_char(p_agenda_rec.start_date_time,'HH24:MI'))
2227 END IF;
2228
2229 IF(to_date(to_char(p_agenda_rec.end_date_time,'DD-MM-YYYY'),'DD-MM-YYYY') = to_date(to_char(l_event_eddate,'DD-MM-YYYY'),'DD-MM-YYYY'))
2230 THEN
2231 IF( p_agenda_rec.end_date_time > l_event_eddate )
2232 THEN
2233 IF (AMS_DEBUG_HIGH_ON) THEN
2234
2235 Ams_Utility_Pvt.debug_message('Came to check with event end time');
2236 END IF;
2237 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2238 THEN
2239 Fnd_Message.set_name('AMS', 'AMS_SESSION_GT_EVENT_TIME');
2240 Fnd_Msg_Pub.ADD;
2241 END IF;
2242 RAISE FND_API.g_exc_error;
2243 END IF; --IF( to_date(to_char(p_agenda_rec.start_date_time,'HH24:MI'))
2244 END IF;
2245
2246 END IF; -- end of if SESSION
2247
2248 END Validate_Agenda_Items;
2249
2250
2251 /*****************************************************************************************/
2252 -- Start of Comments
2253 --
2254 -- NAME
2255 -- Validate_Agenda_Record
2256 --
2257 -- PURPOSE
2258 -- This procedure is to validate agenda record
2259 --
2260 -- NOTES
2261 --
2262 /*****************************************************************************************/
2263
2264 PROCEDURE Validate_Agenda_Record(
2265 p_agenda_rec IN agenda_rec_type,
2266 p_complete_rec IN agenda_rec_type := NULL,
2267 x_return_status OUT NOCOPY VARCHAR2
2268 ) IS
2269
2270 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Agenda_Record';
2271 l_api_version CONSTANT NUMBER := 1.0;
2272 l_return_status VARCHAR2(1);
2273
2274 BEGIN
2275 -- Standard call to check for call compatibility.
2276 IF NOT FND_API.Compatible_API_Call (
2277 l_api_version,
2278 l_api_version,
2279 l_api_name,
2280 G_PACKAGE_NAME)
2281 THEN
2282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2283 END IF;
2284
2285 -- Initialize API return status to success
2286 x_return_status := FND_API.G_RET_STS_SUCCESS;
2287
2288 END Validate_Agenda_Record;
2289
2290 /*****************************************************************************************/
2291 -- PROCEDURE
2292 -- init_agenda_rec
2293 --
2294 -- HISTORY
2295 -- 02/20/2002 gmadana Create.
2296 /*****************************************************************************************/
2297 PROCEDURE init_agenda_rec(
2298 p_agenda_rec IN agenda_rec_type,
2299 x_agenda_rec OUT NOCOPY agenda_rec_type
2300 )
2301 IS
2302 BEGIN
2303
2304 x_agenda_rec.agenda_id := FND_API.g_miss_num;
2305 x_agenda_rec.last_update_date := FND_API.g_miss_date;
2306 x_agenda_rec.last_updated_by := FND_API.g_miss_num;
2307 x_agenda_rec.creation_date := FND_API.g_miss_date;
2308 x_agenda_rec.created_by := FND_API.g_miss_num;
2309 x_agenda_rec.last_update_login := FND_API.g_miss_num;
2310 x_agenda_rec.object_version_number := FND_API.g_miss_num;
2311 x_agenda_rec.application_id := FND_API.g_miss_num;
2312 x_agenda_rec.active_flag := FND_API.g_miss_char;
2313 x_agenda_rec.default_track_flag := FND_API.g_miss_char;
2314 x_agenda_rec.coordinator_id := FND_API.g_miss_num;
2315 x_agenda_rec.timezone_id := FND_API.g_miss_num;
2316 x_agenda_rec.attribute_category := FND_API.g_miss_char;
2317 x_agenda_rec.attribute1 := FND_API.g_miss_char;
2318 x_agenda_rec.attribute2 := FND_API.g_miss_char;
2319 x_agenda_rec.attribute3 := FND_API.g_miss_char;
2320 x_agenda_rec.attribute4 := FND_API.g_miss_char;
2321 x_agenda_rec.attribute5 := FND_API.g_miss_char;
2322 x_agenda_rec.attribute6 := FND_API.g_miss_char;
2323 x_agenda_rec.attribute7 := FND_API.g_miss_char;
2324 x_agenda_rec.attribute8 := FND_API.g_miss_char;
2325 x_agenda_rec.attribute9 := FND_API.g_miss_char;
2326 x_agenda_rec.attribute10 := FND_API.g_miss_char;
2327 x_agenda_rec.attribute11 := FND_API.g_miss_char;
2328 x_agenda_rec.attribute12 := FND_API.g_miss_char;
2329 x_agenda_rec.attribute13 := FND_API.g_miss_char;
2330 x_agenda_rec.attribute14 := FND_API.g_miss_char;
2331 x_agenda_rec.attribute15 := FND_API.g_miss_char;
2332 x_agenda_rec.agenda_name := FND_API.g_miss_char;
2333
2334 x_agenda_rec.description := FND_API.g_miss_char;
2335 x_agenda_rec.START_DATE_TIME := FND_API.g_miss_date;
2336 x_agenda_rec.END_DATE_TIME := FND_API.g_miss_date;
2337 x_agenda_rec.parent_id := FND_API.g_miss_num;
2338 x_agenda_rec.parent_type := FND_API.g_miss_char;
2339 x_agenda_rec.agenda_type := FND_API.g_miss_char;
2340 x_agenda_rec.ROOM_ID := FND_API.g_miss_num;
2341
2342 END init_agenda_rec;
2343
2344
2345
2346 /*****************************************************************************************/
2347 -- PROCEDURE
2348 -- complete_agenda_rec
2349 --
2350 -- HISTORY
2351 -- 02/20/2002 gmadana Created.
2352 /*****************************************************************************************/
2353
2354 PROCEDURE complete_agenda_rec(
2355 p_agenda_rec IN agenda_rec_type,
2356 x_agenda_rec OUT NOCOPY agenda_rec_type
2357 ) IS
2358
2359 -- Replaced ams_agendas_v to AMS_AGENDAS_B Sikalyan Perfomance BugFix
2360
2361 CURSOR c_agenda IS
2362 SELECT *
2363 FROM AMS_AGENDAS_B
2364 WHERE agenda_id = p_agenda_rec.agenda_id;
2365
2366 l_agenda_rec c_agenda%ROWTYPE;
2367
2368 BEGIN
2369 x_agenda_rec := p_agenda_rec;
2370
2371 OPEN c_agenda;
2372 FETCH c_agenda INTO l_agenda_rec;
2373 IF c_agenda%NOTFOUND THEN
2374 CLOSE c_agenda;
2375
2376 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2377 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2378 FND_MSG_PUB.add;
2379 END IF;
2380 RAISE FND_API.g_exc_error;
2381 END IF;
2382
2383 CLOSE c_agenda;
2384
2385
2386 IF p_agenda_rec.ACTIVE_FLAG = FND_API.g_miss_char THEN
2387 x_agenda_rec.ACTIVE_FLAG := l_agenda_rec.ACTIVE_FLAG;
2388 END IF;
2389
2390 IF p_agenda_rec.DEFAULT_TRACK_FLAG = FND_API.g_miss_char THEN
2391 x_agenda_rec.DEFAULT_TRACK_FLAG := l_agenda_rec.DEFAULT_TRACK_FLAG;
2392 END IF;
2393
2394 IF p_agenda_rec.room_id = FND_API.g_miss_num THEN
2395 x_agenda_rec.room_id := l_agenda_rec.room_id;
2396 END IF;
2397
2398 IF p_agenda_rec.SETUP_TYPE_ID = FND_API.g_miss_num THEN
2399 x_agenda_rec.SETUP_TYPE_ID := l_agenda_rec.SETUP_TYPE_ID;
2400 END IF;
2401
2402
2403 IF p_agenda_rec.TIMEZONE_ID = FND_API.g_miss_num THEN
2404 x_agenda_rec.TIMEZONE_ID := l_agenda_rec.TIMEZONE_ID;
2405 END IF;
2406
2407 IF p_agenda_rec.PARENT_ID = FND_API.g_miss_num THEN
2408 x_agenda_rec.PARENT_ID := l_agenda_rec.PARENT_ID;
2409 END IF;
2410
2411 IF p_agenda_rec.PARENT_TYPE = FND_API.g_miss_char THEN
2412 x_agenda_rec.PARENT_TYPE := l_agenda_rec.PARENT_TYPE;
2413 END IF;
2414
2415 IF p_agenda_rec.agenda_type = FND_API.g_miss_char THEN
2416 x_agenda_rec.agenda_type := l_agenda_rec.agenda_type;
2417 END IF;
2418
2419 IF p_agenda_rec.application_id = FND_API.g_miss_num THEN
2420 x_agenda_rec.application_id := l_agenda_rec.application_id;
2421 END IF;
2422
2423 IF p_agenda_rec.created_by = FND_API.g_miss_num THEN
2424 x_agenda_rec.created_by := l_agenda_rec.created_by;
2425 END IF;
2426
2427 IF p_agenda_rec.last_updated_by = FND_API.g_miss_num THEN
2428 x_agenda_rec.last_updated_by := l_agenda_rec.last_updated_by;
2429 END IF;
2430
2431 IF p_agenda_rec.START_DATE_TIME = FND_API.g_miss_date THEN
2432 x_agenda_rec.START_DATE_TIME := l_agenda_rec.START_DATE_TIME;
2433 END IF;
2434
2435 IF p_agenda_rec.END_DATE_TIME = FND_API.g_miss_date THEN
2436 x_agenda_rec.END_DATE_TIME := l_agenda_rec.END_DATE_TIME;
2437 END IF;
2438
2439
2440
2441 IF p_agenda_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char THEN
2442 x_agenda_rec.ATTRIBUTE_CATEGORY := l_agenda_rec.ATTRIBUTE_CATEGORY;
2443 END IF;
2444
2445 IF p_agenda_rec.ATTRIBUTE1 = FND_API.g_miss_char THEN
2446 x_agenda_rec.ATTRIBUTE1 := l_agenda_rec.ATTRIBUTE1;
2447 END IF;
2448
2449 IF p_agenda_rec.ATTRIBUTE2 = FND_API.g_miss_char THEN
2450 x_agenda_rec.ATTRIBUTE2 := l_agenda_rec.ATTRIBUTE2;
2451 END IF;
2452
2453 IF p_agenda_rec.ATTRIBUTE3 = FND_API.g_miss_char THEN
2454 x_agenda_rec.ATTRIBUTE3 := l_agenda_rec.ATTRIBUTE3;
2455 END IF;
2456
2457 IF p_agenda_rec.ATTRIBUTE4 = FND_API.g_miss_char THEN
2458 x_agenda_rec.ATTRIBUTE4 := l_agenda_rec.ATTRIBUTE4;
2459 END IF;
2460
2461 IF p_agenda_rec.ATTRIBUTE5 = FND_API.g_miss_char THEN
2462 x_agenda_rec.ATTRIBUTE5 := l_agenda_rec.ATTRIBUTE5;
2463 END IF;
2464
2465 IF p_agenda_rec.ATTRIBUTE6 = FND_API.g_miss_char THEN
2466 x_agenda_rec.ATTRIBUTE6 := l_agenda_rec.ATTRIBUTE6;
2467 END IF;
2468 IF p_agenda_rec.ATTRIBUTE7 = FND_API.g_miss_char THEN
2469 x_agenda_rec.ATTRIBUTE7 := l_agenda_rec.ATTRIBUTE7;
2470 END IF;
2471 IF p_agenda_rec.ATTRIBUTE8 = FND_API.g_miss_char THEN
2472 x_agenda_rec.ATTRIBUTE8 := l_agenda_rec.ATTRIBUTE8;
2473 END IF;
2474
2475 IF p_agenda_rec.ATTRIBUTE9 = FND_API.g_miss_char THEN
2476 x_agenda_rec.ATTRIBUTE9 := l_agenda_rec.ATTRIBUTE9;
2477 END IF;
2478
2479 IF p_agenda_rec.ATTRIBUTE10 = FND_API.g_miss_char THEN
2480 x_agenda_rec.ATTRIBUTE10 := l_agenda_rec.ATTRIBUTE10;
2481 END IF;
2482
2483 IF p_agenda_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
2484 x_agenda_rec.ATTRIBUTE11 := l_agenda_rec.ATTRIBUTE11;
2485 END IF;
2486
2487 IF p_agenda_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
2488 x_agenda_rec.ATTRIBUTE11 := l_agenda_rec.ATTRIBUTE11;
2489 END IF;
2490
2491 IF p_agenda_rec.ATTRIBUTE12 = FND_API.g_miss_char THEN
2492 x_agenda_rec.ATTRIBUTE12 := l_agenda_rec.ATTRIBUTE12;
2493 END IF;
2494
2495 IF p_agenda_rec.ATTRIBUTE13 = FND_API.g_miss_char THEN
2496 x_agenda_rec.ATTRIBUTE13 := l_agenda_rec.ATTRIBUTE13;
2497 END IF;
2498
2499 IF p_agenda_rec.ATTRIBUTE14 = FND_API.g_miss_char THEN
2500 x_agenda_rec.ATTRIBUTE14 := l_agenda_rec.ATTRIBUTE14;
2501 END IF;
2502
2503 IF p_agenda_rec.ATTRIBUTE15 = FND_API.g_miss_char THEN
2504 x_agenda_rec.ATTRIBUTE15 := l_agenda_rec.ATTRIBUTE15;
2505 END IF;
2506
2507 END complete_agenda_rec;
2508
2509
2510 /*****************************************************************************************/
2511 -- Start of Comments
2512 --
2513 -- NAME
2514 -- Rollup_StTime_EdTime
2515 --
2516 -- PURPOSE
2517 -- This procedure rolls up the start time and end time of Session to Track level
2518 -- and then to Event level.
2519 --
2520 -- NOTES
2521 --
2522 /*****************************************************************************************/
2523
2524 PROCEDURE Rollup_StTime_EdTime (
2525 p_agenda_rec IN agenda_rec_type,
2526 x_return_status OUT NOCOPY VARCHAR2
2527 ) IS
2528
2529 l_parent_id NUMBER;
2530 l_min_time DATE;
2531 l_max_time DATE;
2532
2533 cursor c_parent_id(id_in IN NUMBER) is
2534 select parent_id
2535 from ams_agendas_v
2536 where agenda_id = id_in;
2537
2538 cursor c_min_max_times(id_in IN NUMBER) is
2539 SELECT MIN(start_date_time), MAX(end_date_time)
2540 from ams_agendas_v
2541 where parent_id = id_in
2542 and active_flag = 'Y';
2543
2544 BEGIN
2545
2546 x_return_status := FND_API.g_ret_sts_success;
2547
2548 IF(p_agenda_rec.agenda_type = 'SESSION')
2549 THEN
2550
2551 /* Getting the Track Id */
2552 OPEN c_parent_id(p_agenda_rec.agenda_id);
2553 FETCH c_parent_id INTO l_parent_id;
2554 CLOSE c_parent_id;
2555
2556 /* Getting the Min start_date_time and Max end_date_time of all Sessions
2557 attached to the track_id = p_agenda_rec.parent_id
2558 */
2559 OPEN c_min_max_times (p_agenda_rec.parent_id);
2560 FETCH c_min_max_times INTO l_min_time, l_max_time;
2561 CLOSE c_min_max_times;
2562
2563 /* Rolling up times to Track level */
2564 UPDATE ams_agendas_b
2565 SET start_date_time = l_min_time,
2566 end_date_time = l_max_time,
2567 object_version_number = object_version_number + 1
2568 WHERE agenda_id = l_parent_id;
2569
2570 /* Getting the Event Id. l_parent_id contains the Track Id before
2571 OPEN CURSOR. After FETCHING l_parent_id contains the Event Id
2572 */
2573 OPEN c_parent_id(l_parent_id);
2574 FETCH c_parent_id INTO l_parent_id;
2575 CLOSE c_parent_id;
2576
2577 /* Getting the Min start_date_time and Max end_date_time of all Tracks
2578 attached to the event_id = l_parent_id.
2579 */
2580 OPEN c_min_max_times (l_parent_id);
2581 FETCH c_min_max_times INTO l_min_time, l_max_time;
2582 CLOSE c_min_max_times;
2583
2584 /* Rolling up times to Event level */
2585 UPDATE ams_event_offers_all_b
2586 SET event_start_date_time = l_min_time,
2587 event_end_date_time = l_max_time,
2588 object_version_number = object_version_number + 1
2589 WHERE event_offer_id = l_parent_id;
2590
2591 ELSIF (p_agenda_rec.agenda_type = 'TRACK')
2592 THEN
2593
2594 /* Getting the Event Id */
2595 OPEN c_parent_id(p_agenda_rec.agenda_id);
2596 FETCH c_parent_id INTO l_parent_id;
2597 CLOSE c_parent_id;
2598
2599 /* Getting the Min start_date_time and Max end_date_time of all Tracks
2600 attached to the event_id = p_agenda_rec.parent_id
2601 */
2602 OPEN c_min_max_times (p_agenda_rec.parent_id);
2603 FETCH c_min_max_times INTO l_min_time, l_max_time;
2604 CLOSE c_min_max_times;
2605
2606 /* Rolling up times to Event level */
2607 UPDATE ams_event_offers_all_b
2608 SET event_start_date_time = l_min_time,
2609 event_end_date_time = l_max_time,
2610 object_version_number = object_version_number + 1
2611 WHERE event_offer_id = l_parent_id;
2612
2613
2614 END IF; -- end of p_agenda_rec.agenda_type = 'TRACK'
2615
2616 END Rollup_StTime_EdTime;
2617
2618 procedure ADD_LANGUAGE
2619 is
2620 begin
2621 delete from ams_agendas_tl T
2622 where not exists
2623 (select NULL
2624 from ams_agendas_b B
2625 where B.AGENDA_ID = T.AGENDA_ID
2626 );
2627
2628 update ams_agendas_tl T set (
2629 DESCRIPTION
2630 ) = (select
2631 B.DESCRIPTION
2632 from ams_agendas_tl B
2633 where B.AGENDA_ID = T.AGENDA_ID
2634 and B.LANGUAGE = T.SOURCE_LANG)
2635 where (
2636 T.AGENDA_ID,
2637 T.LANGUAGE
2638 ) in (select
2639 SUBT.AGENDA_ID,
2640 SUBT.LANGUAGE
2641 from ams_agendas_tl SUBB, ams_agendas_tl SUBT
2642 where SUBB.AGENDA_ID = SUBT.AGENDA_ID
2643 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2644 and (SUBB.AGENDA_NAME <> SUBT.AGENDA_NAME
2645 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
2646 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
2647 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
2648 ));
2649
2650 insert into ams_agendas_tl (
2651 AGENDA_ID,
2652 LANGUAGE,
2653 CREATION_DATE,
2654 CREATED_BY,
2655 LAST_UPDATE_DATE,
2656 LAST_UPDATED_BY,
2657 LAST_UPDATE_LOGIN,
2658 SOURCE_LANG,
2659 AGENDA_NAME,
2660 DESCRIPTION,
2661 SECURITY_GROUP_ID
2662 ) select
2663 B.AGENDA_ID,
2664 L.LANGUAGE_CODE,
2665 B.CREATION_DATE,
2666 B.CREATED_BY,
2667 B.LAST_UPDATE_DATE,
2668 B.LAST_UPDATED_BY,
2669 B.LAST_UPDATE_LOGIN,
2670 B.SOURCE_LANG,
2671 B.AGENDA_NAME,
2672 B.DESCRIPTION,
2673 B.SECURITY_GROUP_ID
2674 from ams_agendas_tl B, FND_LANGUAGES L
2675 where L.INSTALLED_FLAG in ('I', 'B')
2676 and B.LANGUAGE = userenv('LANG')
2677 and not exists
2678 (select NULL
2679 from ams_agendas_tl T
2680 where T.AGENDA_ID = B.AGENDA_ID
2681 and T.LANGUAGE = L.LANGUAGE_CODE);
2682 end ADD_LANGUAGE;
2683
2684
2685
2686 END AMS_Agendas_PVT;