[Home] [Help]
PACKAGE BODY: APPS.AMS_ACTIVATE_EVENTSCHED_PVT
Source
1 PACKAGE BODY AMS_Activate_EventSched_PVT AS
2 /* $Header: amsvevcb.pls 120.6 2006/05/11 01:25:31 batoleti ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_Activate_EventSched_PVT';
5
6
7
8 --========================================================================
9 -- PROCEDURE
10 -- Activate_Schedule
11 --
12 -- PURPOSE
13 -- This api is created to activate available event schedules.
14 --
15 -- Note
16 -- This procedure will be called by concurrent program to activate the
17 -- event shedules which are in available state and whose Registration
18 -- start date is equal to the sys date and to complete the event
19 -- whose event_end_date is equal to the sys date.
20 --
21 -- HISTORY
22 -- 07-Jan-2002 gmadana created.
23 --========================================================================
24 PROCEDURE Activate_Schedule
25 (
26 p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
28 p_commit IN VARCHAR2 := FND_API.G_False,
29
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER ,
32 x_msg_data OUT NOCOPY VARCHAR2
33 )
34 IS
35
36 CURSOR c_all_schedule IS
37 SELECT offr.event_offer_id, offr.object_version_number,offr.event_level,offr.parent_id
38 FROM ams_event_offers_all_b offr, ams_event_headers_all_b hdr
39 WHERE offr.system_status_code = 'AVAILABLE'
40 --AND offr.reg_required_flag = 'Y'
41 --AND offr.reg_start_date <= SYSDATE
42 AND offr.event_header_id = hdr.event_header_id
43 AND hdr.system_status_code = 'ACTIVE';
44 -- AND offr.event_offer_id in (10136,10147);
45
46 CURSOR c_all_oneoffevent IS
47 SELECT event_offer_id, object_version_number, parent_id,event_level
48 FROM ams_event_offers_all_b
49 WHERE system_status_code = 'AVAILABLE'
50 --AND reg_required_flag = 'Y'
51 --AND reg_start_date <= SYSDATE
52 AND event_header_id IS NULL
53 AND nvl(parent_type, 'RCAM') <> 'CAMP';
54 -- AND event_offer_id = 10134;
55
56
57 /* CURSOR c_completed_schedule IS
58 SELECT event_offer_id, object_version_number,event_level
59 FROM ams_event_offers_all_b
60 WHERE system_status_code = 'ACTIVE'
61 AND event_end_date <= SYSDATE
62 AND nvl(parent_type, 'RCAM') <> 'CAMP';*/
63 -- AND event_offer_id = 10134;
64
65 --Added for timeZOne issue FIX : BUG 4482556 ANSKUMAR
66
67 --Will convert the sysdate to the user timezone
68 CURSOR c_completed_schedule_convdate(l_conv_sysdate DATE) IS
69 SELECT event_offer_id, object_version_number,event_level
70 FROM ams_event_offers_all_b
71 WHERE system_status_code = 'ACTIVE'
72 AND event_end_date <= l_conv_sysdate
73 AND nvl(parent_type, 'RCAM') <> 'CAMP';
74 --End Adding
75
76 CURSOR c_status(l_status_code VARCHAR2) IS
77 SELECT user_status_id
78 FROM ams_user_statuses_b
79 WHERE system_status_type = 'AMS_EVENT_STATUS'
80 AND system_status_code = l_status_code
81 AND default_flag = 'Y'
82 AND enabled_flag = 'Y' ;
83
84 /* Cursor to get the user status id of program */
85 CURSOR c_PROGRAM_status (l_event_offer_id IN NUMBER) IS
86 SELECT user_status_id
87 FROM ams_campaigns_all_b
88 WHERE campaign_id = l_event_offer_id;
89 -- added for time zone issue Fix : BUG 4482556 ANSKUMAR
90 l_system_d_time DATE;
91 l_sys_start_time DATE;
92 l_user_timezone_id NUMBER;
93 l_return_status VARCHAR2(1);
94 l_msg_count NUMBER;
95 l_msg_data VARCHAR2(2000);
96 --End Adding
97
98 l_status_id NUMBER ;
99 l_schedule_id NUMBER ;
100 l_header_id NUMBER;
101 l_obj_version NUMBER ;
102 l_program_id NUMBER;
103 l_parent_id NUMBER;
104 l_parent_status_id NUMBER;
105 l_parent_system_status_code VARCHAR2(30);
106 l_event_level VARCHAR2(30);
107 l_return_flag VARCHAR2(30);
108
109 l_api_version CONSTANT NUMBER := 1.0 ;
110 l_api_name CONSTANT VARCHAR2(30) := 'Activate_Event_Schedule';
111
112 l_evo_rec AMS_EVENTOFFER_PVT.evo_rec_type;
113 l_evo_rec_oneoff AMS_EVENTOFFER_PVT.evo_rec_type;
114 l_evo_rec_eveo AMS_EVENTOFFER_PVT.evo_rec_type;
115
116 BEGIN
117 --
118 -- Standard Start of API savepoint
119 --
120 --Added for TimeZOne Issue Fix
121 l_system_d_time:=SYSDATE;
122 --End Adding
123
124 SAVEPOINT AMS_ACTIVATE_SCHEDULE;
125
126 --
127 -- Debug Message
128 --
129 AMS_Utility_PVT.debug_message(l_api_name || ': start');
130
131 --
132 -- Initialize message list IF p_init_msg_list is set to TRUE.
133 --
134 IF FND_API.to_Boolean( p_init_msg_list ) THEN
135 FND_MSG_PUB.initialize;
136 END IF;
137
138 --
139 -- Standard call to check for call compatibility.
140 --
141 IF NOT FND_API.Compatible_API_Call ( 1.0,
142 p_api_version,
143 l_api_name,
144 G_PKG_NAME)
145 THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148
149 --
150 -- Initialize API return status to success
151 --
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153 l_return_flag := 'Y';
154
155
156 OPEN c_status('ACTIVE') ;
157 FETCH c_status INTO l_status_id ;
158 IF c_status%NOTFOUND THEN
159 CLOSE c_status;
160 AMS_Utility_PVT.error_message('AMS_EVENT_BAD_USER_STATUS');
161 RETURN ;
162 END IF ;
163 CLOSE c_status ;
164
165 /* Making all Event Schedules which are in available status to
166 Active status, if their enrollment start date is equal to sysdate
167 and their parent(EVEH) is active.
168 */
169 OPEN c_all_schedule ;
170 LOOP
171
172 BEGIN
173 SAVEPOINT C_ALL_SCHEDULE;
174
175 FETCH c_all_schedule INTO l_schedule_id, l_obj_version,l_event_level,l_parent_id ;
176 EXIT WHEN c_all_schedule%NOTFOUND ;
177
178 -- Update the status of the schedule to Active.
179 /* UPDATE ams_event_offers_all_b
180 SET system_status_code = 'ACTIVE',
181 last_status_date = SYSDATE ,
182 user_status_id = l_status_id,
183 object_version_number = l_obj_version + 1
184 WHERE event_offer_id = l_schedule_id ;*/
185
186 /* l_parent_system_status_code := '';
187 IF l_parent_id IS NOT NULL THEN
188 -- A NEW CURSOR NEEDS TO BE THERE FOR THIS BELOW.: ANCHAUDH
189 OPEN c_EVENT_status(l_parent_id);
190 FETCH c_EVENT_status INTO l_parent_status_id;
191 CLOSE c_EVENT_status;
192
193 -- Getting the system_status_code of Parent
194 l_parent_system_status_code := Ams_Utility_Pvt.get_system_status_code(l_parent_status_id);
195
196 END IF;
197
198 IF l_parent_system_status_code = 'ACTIVE' THEN*/
199
200 AMS_EVENTOFFER_PVT.init_evo_rec(l_evo_rec_eveo);
201 l_evo_rec_eveo.event_offer_id := l_schedule_id;
202 l_evo_rec_eveo.object_version_number := l_obj_version;
203 l_evo_rec_eveo.system_status_code := 'ACTIVE';
204 l_evo_rec_eveo.last_status_date := SYSDATE;
205 l_evo_rec_eveo.user_status_id := l_status_id;
206 l_evo_rec_eveo.event_level := l_event_level;
207
208 AMS_EventOffer_PVT.update_event_offer (
209 p_api_version => 1.0,
210 p_init_msg_list => FND_API.G_FALSE,
211 p_commit => FND_API.G_FALSE,
212 p_validation_level => FND_API.g_valid_level_full,
213
214 p_evo_rec => l_evo_rec_eveo,
215
216 x_return_status => x_return_status,
217 x_msg_count => x_msg_count,
218 x_msg_data => x_msg_data
219 );
220
221 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
222 l_return_flag := 'N';
223 ROLLBACK TO C_ALL_SCHEDULE;
224 END IF;
225
226 COMMIT;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 l_return_flag := 'N';
231 x_return_status := FND_API.g_ret_sts_error;
232 ROLLBACK TO C_ALL_SCHEDULE;
233
234 END;
235
236
237 -- END IF;
238
239 END LOOP;
240 CLOSE c_all_schedule ;
241
242 /* Making all OneoffEvents which are in available status to
243 Active status, if their enrollment start date is equal to sysdate
244 and their parent(PROGRAM) is active.
245 */
246 OPEN c_all_oneoffevent ;
247 LOOP
248
249 BEGIN
250 SAVEPOINT C_ALL_ONEOFFEVENT;
251
252 FETCH c_all_oneoffevent INTO l_schedule_id, l_obj_version, l_parent_id, l_event_level ;
253 EXIT WHEN c_all_oneoffevent%NOTFOUND ;
254
255 l_parent_system_status_code := '';
256 IF l_parent_id IS NOT NULL THEN
257
258 OPEN c_PROGRAM_status(l_parent_id);
259 FETCH c_PROGRAM_status INTO l_parent_status_id;
260 CLOSE c_PROGRAM_status;
261
262 /* Getting the system_status_code of Parent */
263 l_parent_system_status_code := Ams_Utility_Pvt.get_system_status_code(l_parent_status_id);
264
265 END IF;
266
267 IF l_parent_system_status_code = 'ACTIVE' OR l_parent_id IS NULL THEN
268
269 -- Update the status of the oneoffevent to Active.
270 /* UPDATE ams_event_offers_all_b
271 SET system_status_code = 'ACTIVE',
272 last_status_date = SYSDATE ,
273 user_status_id = l_status_id,
274 object_version_number = l_obj_version + 1
275 WHERE event_offer_id = l_schedule_id ;*/
276
277 AMS_EVENTOFFER_PVT.init_evo_rec(l_evo_rec_oneoff);
278 l_evo_rec_oneoff.event_offer_id := l_schedule_id;
279 l_evo_rec_oneoff.object_version_number := l_obj_version;
280 l_evo_rec_oneoff.system_status_code := 'ACTIVE';
281 l_evo_rec_oneoff.last_status_date := SYSDATE;
282 l_evo_rec_oneoff.user_status_id := l_status_id;
283 --batoleti changed the foll stmt.. Ref bug# 4404567.
284 --l_evo_rec_oneoff.user_status_id := l_event_level;
285 l_evo_rec_oneoff.event_level := l_event_level;
286
287 AMS_EventOffer_PVT.update_event_offer (
288 p_api_version => 1.0,
289 p_init_msg_list => FND_API.G_FALSE,
290 p_commit => FND_API.G_FALSE,
291 p_validation_level => FND_API.g_valid_level_full,
292
293 p_evo_rec => l_evo_rec_oneoff,
294
295 x_return_status => x_return_status,
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data
298 );
299
300 END IF;
301
302 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
303 l_return_flag := 'N';
304 ROLLBACK TO C_ALL_ONEOFFEVENT;
305 END IF;
306
307 COMMIT;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 l_return_flag := 'N';
312 x_return_status := FND_API.g_ret_sts_error;
313 ROLLBACK TO C_ALL_ONEOFFEVENT;
314
315 END;
316
317 END LOOP;
318 CLOSE c_all_oneoffevent ;
319
320
321 -- Change the status of all the schedules which are active to
322 -- completed.
323 OPEN c_status('COMPLETED') ;
324 FETCH c_status INTO l_status_id ;
325 IF c_status%NOTFOUND THEN
326 CLOSE c_status;
327 AMS_Utility_PVT.error_message('AMS_EVENT_BAD_USER_STATUS');
328 RETURN ;
329 END IF ;
330 CLOSE c_status ;
331
332 --Added for time Zone issue FIX : BUG 4482556 ANSKUMAR
333 l_user_timezone_id:= FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID');
334 --API to convert the sysdate to usertimezone Date : BUG 4482556 ANSKUMAR
335 AMS_UTILITY_PVT.Convert_Timezone(
336 p_init_msg_list => FND_API.G_TRUE,
337 x_return_status => l_return_status,
338 x_msg_count => l_msg_count,
339 x_msg_data => l_msg_data,
340
341 p_user_tz_id => l_user_timezone_id,
342 p_in_time => l_system_d_time,
343 p_convert_type => 'USER',
344
345 x_out_time => l_sys_start_time
346 );
347
348 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
349 l_sys_start_time := SYSDATE;
350 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
351 l_sys_start_time := SYSDATE;
352 END IF;
353
354 --End Adding
355
356 -- OPEN c_completed_schedule ;
357 OPEN c_completed_schedule_convdate(l_sys_start_time);
358 LOOP
359
360 BEGIN
361 SAVEPOINT C_COMPLETED_SCHEDULE;
362 --These lines are comented and adde new for BUG 4482556 ANSKUMAR
363 -- FETCH c_completed_schedule INTO l_schedule_id, l_obj_version,l_event_level ;
364 -- EXIT WHEN c_completed_schedule%NOTFOUND ;
368 AMS_EVENTOFFER_PVT.init_evo_rec( l_evo_rec);
365 FETCH c_completed_schedule_convdate INTO l_schedule_id, l_obj_version,l_event_level ;
366 EXIT WHEN c_completed_schedule_convdate%NOTFOUND ;
367
369 l_evo_rec.event_offer_id := l_schedule_id;
370 l_evo_rec.object_version_number := l_obj_version ;
371 l_evo_rec.system_status_code := 'COMPLETED';
372 l_evo_rec.last_status_date := SYSDATE;
373 l_evo_rec.user_status_id := l_status_id;
374 l_evo_rec.event_level := l_event_level;
375
376 AMS_EventOffer_PVT.update_event_offer (
377 p_api_version => 1.0,
378 p_init_msg_list => FND_API.G_FALSE,
379 p_commit => FND_API.G_FALSE,
380 p_validation_level => FND_API.g_valid_level_full,
381
382 p_evo_rec => l_evo_rec,
383
384 x_return_status => x_return_status,
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data
387 );
388
389 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
390 l_return_flag := 'N';
391 ROLLBACK TO C_COMPLETED_SCHEDULE;
392 END IF;
393
394 COMMIT;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 l_return_flag := 'N';
399 x_return_status := FND_API.g_ret_sts_error;
400 ROLLBACK TO C_COMPLETED_SCHEDULE;
401
402 END;
403
404 /*IF x_return_status = FND_API.g_ret_sts_error THEN
405 RAISE FND_API.g_exc_error;
406 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
407 RAISE FND_API.g_exc_unexpected_error;
408 END IF;*/
409
410 END LOOP;
411 -- CLOSE c_completed_schedule;
412 CLOSE c_completed_schedule_convdate;
413 --
414 -- Standard check of p_commit.
415 --
416 IF FND_API.To_Boolean ( p_commit )
417 THEN
418 COMMIT WORK;
419 END IF;
420
421 --
422 -- Standard call to get message count AND IF count is 1, get message info.
423 --
424 FND_MSG_PUB.Count_AND_Get
425 ( p_count => x_msg_count,
426 p_data => x_msg_data,
427 p_encoded => FND_API.G_FALSE
428 );
429
430 AMS_Utility_PVT.debug_message(l_api_name ||' : end Status : ' || x_return_status);
431 --dbms_output.put_line(l_api_name ||' : end Status : ' || x_return_status);
432
433 IF (l_return_flag = 'Y') THEN
434 x_return_status := FND_API.G_RET_STS_SUCCESS;
435 ELSE
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 END IF;
438
439 EXCEPTION
440 WHEN FND_API.G_EXC_ERROR THEN
441
442 IF (c_all_schedule%ISOPEN) THEN
443 CLOSE c_all_schedule ;
444 END IF;
445 ROLLBACK TO AMS_ACTIVATE_SCHEDULE;
446 x_return_status := FND_API.G_RET_STS_ERROR ;
447
448 FND_MSG_PUB.Count_AND_Get
449 ( p_count => x_msg_count,
450 p_data => x_msg_data,
451 p_encoded => FND_API.G_FALSE
452 );
453
454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
455 IF (c_all_schedule%ISOPEN) THEN
456 CLOSE c_all_schedule ;
457 END IF;
458 ROLLBACK TO AMS_ACTIVATE_SCHEDULE;
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
460
461 FND_MSG_PUB.Count_AND_Get
462 ( p_count => x_msg_count,
463 p_data => x_msg_data,
464 p_encoded => FND_API.G_FALSE
465 );
466
467 WHEN OTHERS THEN
468 IF (c_all_schedule%ISOPEN) THEN
469 CLOSE c_all_schedule ;
470 END IF;
471 ROLLBACK TO AMS_ACTIVATE_SCHEDULE;
472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
473
474 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
475 THEN
476 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
477 END IF;
478
479 FND_MSG_PUB.Count_AND_Get
480 ( p_count => x_msg_count,
481 p_data => x_msg_data,
482 p_encoded => FND_API.G_FALSE
483 );
484
485
486 END Activate_Schedule ;
487
488 --========================================================================
489 -- PROCEDURE
490 -- Activate_Schedule
491 --
492 -- PURPOSE
493 -- This api is created to be used by concurrent program to activate
494 -- schedules. It will internally call the Activate schedules api to
495 -- activate the schedule.
496
497 --
498 -- HISTORY
499 -- 08-Jan-2001 gmadana Created.
500 --
501 --========================================================================
502 PROCEDURE Activate_Schedule
503 (errbuf OUT NOCOPY VARCHAR2,
504 retcode OUT NOCOPY VARCHAR2)
505 IS
506 l_return_status VARCHAR2(1) ;
507 l_msg_count NUMBER ;
508 l_msg_data VARCHAR2(2000);
509 l_api_version NUMBER := 1.0 ;
510 BEGIN
511 FND_MSG_PUB.initialize;
512
513 /*AMS_Activate_EventSched_PVT.Activate_Schedule(
514 p_api_version => l_api_version ,
515
516 x_return_status => l_return_status,
517 x_msg_count => l_msg_count,
518 x_msg_data => l_msg_data
519 ) ;*/
520
521 AMS_Activate_EventSched_PVT.Activate_Schedule(
522 p_api_version => l_api_version,
523 p_init_msg_list => FND_API.G_False,
524 p_commit => FND_API.G_False,
525
526 x_return_status => l_return_status,
527 x_msg_count => l_msg_count ,
528 x_msg_data => l_msg_data
529 );
530
531 -- Write_log ;
532 Ams_Utility_Pvt.Write_Conc_log ;
533
534 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
535 retcode :=0;
536 ELSE
537 retcode := 2;
538 errbuf := l_msg_data ;
539 END IF;
540 END Activate_Schedule;
541
542
543 END Ams_Activate_Eventsched_Pvt ;