1 Package Body ota_evt_api_upd2 as
2 /* $Header: otevt02t.pkb 120.6 2008/01/24 10:42:18 smahanka noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ota_evt_api_upd2.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< Check Status Change >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 -- PRIVATE
13 -- Description: Check the delegate booking Status when Updating.
14 --
15 --
16 procedure Check_Status_Change(p_event_id in number
17 ,p_event_status in out nocopy varchar2
18 ,p_booking_status_type_id in number
19 ,p_maximum_attendees in number) is
20 --
21 -- Bug 3493695
22 CURSOR c_attended_enr IS
23 SELECT null
24 FROM ota_delegate_bookings odb
25 WHERE event_id = p_event_id AND
26 booking_status_type_id IN
27 (SELECT booking_status_type_id
28 FROM ota_booking_status_types
29 WHERE type = 'A');
30
31 l_att_enr_exists VARCHAR2(1);
32 -- Bug 3493695
33 l_booking_status varchar2(30) := ota_tdb_bus.booking_status_type(
34 p_booking_status_type_id);
35 l_event_rec ota_evt_shd.g_rec_type;
36 l_event_exists boolean;
37 l_total_places number;
38
39 --
40 l_proc varchar2(72) := g_package||'check_status_change';
41 --
42 begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 -- Get Event Record
47 --
48 ota_evt_shd.get_event_details (p_event_id,
49 l_event_rec,
50 l_event_exists);
51 --
52 -- Check event status change for No change.
53 --
54 if l_event_rec.event_status = p_event_status then
55 fnd_message.set_name('OTA','OTA_13537_EVT_SAME_STATUS');
56 fnd_message.raise_error;
57 end if;
58 --
59 -- Check event status change for Full Events.
60 --
61 if p_event_status = 'F' then
62 fnd_message.set_name('OTA','OTA_13515_EVT_STATUS_F');
63 fnd_message.raise_error;
64 end if;
65 --
66 -- Cannot set a full event to Normal via change status.
67 --
68 if p_event_status = 'N'and l_event_rec.event_status = 'F' then
69 fnd_message.set_name('OTA','OTA_13556_EVT_NORMAL_STATUS');
70 fnd_message.raise_error;
71 end if;
72 --
73 -- Check enrollment status change for Planned Events.
74 --
75 if p_event_status in ('P') and l_booking_status not in ('W') then
76 fnd_message.set_name('OTA','OTA_13516_EVT_STATUS_P');
77 fnd_message.raise_error;
78 end if;
79 --
80 -- Check enrollment status change for Cancelled or Closed Events.
81 --
82 if p_event_status in ('A','C') and l_booking_status not in ('C') then
83 fnd_message.set_name('OTA','OTA_13517_EVT_STATUS_AC');
84 fnd_message.raise_error;
85 end if;
86
87 --Bug 3493695
88 -- Check if there are any attended enrollments for a class being cancelled
89 IF p_event_status = 'A' THEN
90 OPEN c_attended_enr;
91 FETCH c_attended_enr INTO l_att_enr_exists;
92 IF c_attended_enr%FOUND THEN
93 CLOSE c_attended_enr;
94 fnd_message.set_name('OTA','OTA_13067_EVT_ATT_ENR_EXISTS');
95 fnd_message.raise_error;
96 END IF;
97 CLOSE c_attended_enr;
98 END IF;
99 -- Bug 3493695
100
101 --
102 -- If Status Changed to Normal and Max Attendees = total Places taken
103 -- Then reset the Event Status to Full. Would only apply if changing
104 -- Event Status from Closed to Normal.
105 --
106 if p_event_status in ('N') and p_maximum_attendees is not null then
107 l_total_places := ota_evt_bus2.get_total_places('ALL',p_event_id);
108 if l_total_places = p_maximum_attendees then
109 p_event_status := 'F';
110 end if;
111 end if;
112 --
113 --
114 hr_utility.set_location('Leaving:'||l_proc, 10);
115 --
116 end Check_Status_Change;
117 --
118 --
119 -- ----------------------------------------------------------------------------
120 -- |--------------------------< Check Maximum Attendees >---------------------|
121 -- ----------------------------------------------------------------------------
122 --
123 -- PRIVATE
124 -- Description: Check maximum attendees is not null and resent the event status
125 -- accordingly.
126 --
127 --
128 procedure Check_Maximum_Attendees(p_maximum_attendees in number,
129 p_event_status in out nocopy varchar2,
130 p_old_max_attendees in number,
131 p_event_id in number) is
132 --
133 l_total_places number;
134 l_proc varchar2(72) := g_package||'check_maximum_attendees';
135
136
137 l_no_of_waitlist_candidate number;
138
139 cursor c_check_waitlist_candidates is
140 select count(*)
141 from ota_delegate_bookings odb
142 where event_id = p_event_id and
143 booking_status_type_id in
144 (select booking_status_type_id
145 from ota_booking_status_types
146 where type = 'W');
147
148 --
149 begin
150 --
151 hr_utility.set_location('Entering:'||l_proc, 5);
152 --
153 --
154 if p_maximum_attendees is null then
155 --Bug 6640334/6705591:Admin should be able to nullify the maximum attendees set previously.
156 --As maximum attendees is set to null,the class status is changed to Normal,if it was Full previously.
157 /*fnd_message.set_name('OTA','OTA_13553_MAND_MAX_ATTENDEES');
158 fnd_message.raise_error;*/
159 if p_event_status = 'F' then
160 p_event_status := 'N';
161 end if;
162 else
163
164 if p_event_status in ('F','N') then
165 l_total_places := ota_evt_bus2.get_total_places('ALL',p_event_id);
166 if (p_maximum_attendees > p_old_max_attendees) and (l_total_places < p_maximum_attendees)
167 and (p_event_status = 'F') then
168
169 -- *** Check if total places > max attendees + wait list then change the status to 'N' else 'F'
170
171 open c_check_waitlist_candidates;
172 fetch c_check_waitlist_candidates into l_no_of_waitlist_candidate;
173 if c_check_waitlist_candidates%notfound then
174 l_no_of_waitlist_candidate := 0;
175 end if;
176 close c_check_waitlist_candidates;
177 /* Start Bug 1712445 */
178 if p_maximum_attendees <= l_total_places + l_no_of_waitlist_candidate then
179 if ota_evt_shd.g_old_rec.maximum_internal_attendees is null then
180 p_event_status := 'F';
181 else
182 p_event_status := 'N';
183
184 end if;
185 else
186 p_event_status := 'N';
187 end if;
188 /* End of Bug 1712445 */
189 -- ***
190 elsif (p_maximum_attendees = l_total_places) and (p_event_status = 'N') then
191 p_event_status := 'F';
192 end if;
193 end if;
194 end if;
195 --
196 hr_utility.set_location('Leaving:'||l_proc, 10);
197 --
198 end Check_Maximum_Attendees;
199 --
200 --
201 -- ----------------------------------------------------------------------------
202 -- |--------------------------< Process Event Change >------------------------|
203 -- ----------------------------------------------------------------------------
204 --
205 -- PRIVATE
206 -- Description: Update enrollments for the given Event.
207 -- The process will include the updating of the booking status, finance
208 -- lines of enrollments for the given Event.
209 --
210 procedure Process_Event_Change(p_event_id in number
211 ,p_event_status in varchar2
212 ,p_update_finance_line in varchar2 default 'N'
213 ,p_booking_status_type_id in varchar2 default null
214 ,p_date_status_changed in date) is
215
216 l_daemon_flag varchar2(1);
217 l_daemon_type varchar2(1);
218 l_booking_status varchar2(1);
219 --
220 --
221 cursor c_get_event_enrollments is
222 select tdb.booking_id, tdb.object_version_number, fl.object_version_number tfl_object_version_number,
223 fl.finance_line_id,
224 date_booking_placed, line_id,org_id,daemon_flag,daemon_type, tdb.booking_status_type_id,
225 tdb.delegate_person_id
226 from ota_delegate_bookings tdb
227 , ota_booking_status_types bst
228 , ota_finance_lines fl
229 where tdb.event_id = p_event_id
230 and tdb.booking_status_type_id = bst.booking_status_type_id
231 and fl.booking_id(+) = tdb.booking_id
232 and (((p_event_status = 'P') and (bst.type not in ('C','W')))
233 or ((p_event_status = 'C') and (bst.type in ('R')))
234 or ((p_event_status = 'A') and (bst.type <> 'C'))); -- Added check for "bst.type <> 'C'" for bug #2065808
235
236 CURSOR c_get_booking_status(p_booking_status_type_id in number) is
237 select type
238 from ota_booking_status_types
239 where booking_status_type_id = p_booking_status_type_id;
240
241
242 --
243 l_status_change_comments varchar(1000) := fnd_message.get_string('OTA','OTA_13523_TDB_STATUS_COMMENTS');
244 l_proc varchar2(72) := g_package||'process_event_change';
245 --
246 begin
247 --
248 hr_utility.set_location('Entering:'|| l_proc, 5);
249 --
250 FOR c_get_enrollment in c_get_event_enrollments LOOP
251 --
252 --
253 /* For bug 1763422 */
254 l_booking_status := null;
255 l_daemon_type := null;
256 l_daemon_flag := null;
257
258 if p_event_status = 'A' or p_event_status = 'C' then
259 if c_get_enrollment.daemon_flag is null then
260 For enr_status in c_get_booking_status(c_get_enrollment.booking_status_type_id)
261 Loop
262 l_booking_status := enr_status.type;
263 end loop;
264 if l_booking_status <> 'C' then
265 l_daemon_type :='W';
266 l_daemon_flag := 'Y';
267 else
268 l_daemon_type := null;
269 l_daemon_flag := null;
270 end if;
271 else
272 l_daemon_type := c_get_enrollment.daemon_type ;
273 l_daemon_flag := c_get_enrollment.daemon_flag ;
274 end if;
275 end if;
276 /* End For bug 1763422 */
277 ota_tdb_api_upd2.update_enrollment(p_booking_id => c_get_enrollment.booking_id
278 ,p_object_version_number => c_get_enrollment.object_version_number
279 ,p_finance_line_id => c_get_enrollment.finance_line_id
280 ,p_event_id => p_event_id
281 ,p_booking_status_type_id => p_booking_status_type_id
282 ,p_status_change_comments => l_status_change_comments
283 ,p_date_booking_placed => c_get_enrollment.date_booking_placed
284 ,p_update_finance_line => p_update_finance_line
285 ,p_tfl_object_version_number => c_get_enrollment.tfl_object_version_number
286 ,p_date_status_changed => p_date_status_changed
287 ,p_line_id => c_get_enrollment.line_id
288 ,p_org_id => c_get_enrollment.org_id
289 ,p_daemon_flag => l_daemon_flag
290 ,p_daemon_type => l_daemon_type);
291
292 if p_event_status ='A' then
293 --send notification to the learner for class cancelletion
294 OTA_INITIALIZATION_WF.initialize_wf(p_process => 'OTA_CLASS_CANCEL_JSP_PRC',
295 p_item_type => 'OTWF',
296 p_person_id => c_get_enrollment.delegate_person_id,
297 p_eventid => p_event_id,
298 p_event_fired => 'CLASS_CANCEL');
299
300 end if;
301 --
302 END LOOP get_enrollment;
303 --
304 hr_utility.set_location('Leaving:'|| l_proc, 10);
305 --
306 --
307 end Process_Event_Change;
308 --
309 --
310 -- ----------------------------------------------------------------------------
311 -- |--------------------------< Reset Max Attendees >-------------------------|
312 -- ----------------------------------------------------------------------------
313 --
314 -- PRIVATE
315 -- Description: Reset Maximum_Attendees and Maximum_Internal_Attendees columns.
316 --
317 --
318 procedure Reset_Max_Attendees (p_event_id in number
319 ,p_event_status in varchar2
320 ,p_reset_max_attendees in boolean default false
321 ,p_maximum_attendees in out nocopy number
322 ,p_maximum_internal_attendees in out nocopy number) is
323 --
324 l_total_places number;
325 l_total_internal_places number;
326 --
327 l_proc varchar2(72) := g_package||'reset_max_attendees';
328 --
329 begin
330 --
331 hr_utility.set_location('Entering:'|| l_proc, 5);
332 --
333 -- If reset_max_attendees selected then only fire When new event Status is
334 -- Closed.
335 --
336 if p_event_status in ('C') then
337 if p_reset_max_attendees then
338 l_total_places := ota_evt_bus2.get_total_places('ALL',p_event_id);
339 l_total_internal_places := ota_evt_bus2.get_total_places('INTERNAL',p_event_id);
340 if p_maximum_internal_attendees > l_total_places then
341 p_maximum_internal_attendees := l_total_places;
342 p_maximum_attendees := l_total_places;
343 else
344 p_maximum_attendees := l_total_places;
345 end if;
346 end if;
347 end if;
348 --
352 --
349 hr_utility.set_location('Leaving:'|| l_proc, 10);
350 --
351 end Reset_Max_Attendees;
353 --
354 -- ----------------------------------------------------------------------------
355 -- |--------------------------< Update Event >--------------------------------|
356 -- ----------------------------------------------------------------------------
357 --
358 -- PUBLIC
359 -- Description: Updates an Event. May also cascade changes to enrollments for
360 -- the given Event.
361 --
362 procedure update_event
363 (
364 p_event in varchar2,
365 p_event_id in number,
366 p_object_version_number in out nocopy number,
367 p_event_status in out nocopy varchar2,
368 p_validate in boolean default false,
369 p_reset_max_attendees in boolean default false,
370 p_update_finance_line in varchar2 default 'N',
371 p_booking_status_type_id in number default null,
372 p_date_status_changed in date default null,
373 p_maximum_attendees in number default null) is
374 --
375 l_event_rec ota_evt_shd.g_rec_type;
376 l_event_rec_ovn ota_evt_shd.g_rec_type;
377 l_event_exists boolean;
378 l_event_exists_ovn boolean;
379 l_event_status varchar2(30);
380 --
381 l_proc varchar2(72) := g_package||'update_event';
382 --
383 begin
384 --
385 hr_utility.set_location('Entering:'|| l_proc, 5);
386 --
387 -- Issue a savepoint if operating in validation only mode.
388 --
389 if p_validate then
390 savepoint update_enrollment;
391 end if;
392 --
393 hr_utility.set_location(l_proc, 6);
394 --
395 -- Validation in addition to Table Handlers.
396 -- Lock Event
397 --
398 ota_evt_shd.lck(p_event_id, p_object_version_number);
399 --
400 -- Get Event record.
401 --
402 ota_evt_shd.get_event_details (p_event_id,
403 l_event_rec,
404 l_event_exists);
405 --
406 if p_event = 'STATUS' then
407 --
408 -- Check validation of status change.
409 --
410 Check_Status_Change(p_event_id => p_event_id
411 ,p_event_status => p_event_status
412 ,p_booking_status_type_id => p_booking_status_type_id
413 ,p_maximum_attendees => l_event_rec.maximum_attendees);
414 --
415 --
416 hr_utility.set_location(l_proc, 7);
417 --
418 -- Process Event Status change.
419 --
420 Process_Event_Change(p_event_id => p_event_id
421 ,p_event_status => p_event_status
422 ,p_update_finance_line => p_update_finance_line
423 ,p_booking_status_type_id => p_booking_status_type_id
424 ,p_date_status_changed => p_date_status_changed);
425
426 --
427 hr_utility.set_location(l_proc, 8);
428 --
429 -- Reset Max Attendees and Max Internal Attendees. Only when Closed Event
430 -- Status is selected.
431 --
432 if p_event_status in ('C') then
433 Reset_Max_Attendees (p_event_id => p_event_id
434 ,p_event_status => p_event_status
435 ,p_reset_max_attendees => p_reset_max_attendees
436 ,p_maximum_attendees => l_event_rec.maximum_attendees
437 ,p_maximum_internal_attendees => l_event_rec.maximum_internal_attendees);
438 end if;
439 --
440 hr_utility.set_location(l_proc, 9);
441 --
442 --
443 --
444 else
445 Check_Maximum_Attendees(p_maximum_Attendees => p_maximum_attendees,
446 p_event_status => p_event_status,
447 p_old_max_attendees => l_event_rec.maximum_attendees,
448 p_event_id => p_event_id);
449 l_event_rec.maximum_attendees := p_maximum_attendees;
450 end if;
451
452 -- Bug 463742.
453 -- Requery event record, as object version number
454 -- may have changed.
455
456 ota_evt_shd.get_event_details(p_event_id,
457 l_event_rec_ovn,
458 l_event_exists_ovn);
459
460 -- Force Event update
461 --
462 ota_evt_upd.upd
463 (p_event_id => p_event_id
464 ,p_object_version_number => l_event_rec_ovn.object_version_number
465 ,p_event_status => p_event_status
466 ,p_maximum_attendees => l_event_rec.maximum_attendees
467 ,p_maximum_internal_attendees => l_event_rec.maximum_internal_attendees
468 ,p_validate => p_validate);
469 --
470 -- Commit the Changes
471 --
472 -- Enh# 1753511 hdshah Commented out the commit so that we can use update_event procedure for this enhancement.
473 -- Included app_form.quietcommit in OTAEVENT.pll.
474 -- commit;
475 --
476 hr_utility.set_location('Leaving:'|| l_proc, 10);
477 --
478 -- When in validation only mode raise the Validate_Enabled exception
479 --
480 if p_validate then
481 raise hr_api.validate_enabled;
482 end if;
483
484 -- call ntf to instructor and learners for cancelled class
485 if p_event_status = 'A' and p_event = 'STATUS' then
486 --send notification to all instructors for class cancelletion
487 OTA_INITIALIZATION_WF.initialize_instructor_wf(
488 p_item_type => 'OTWF',
489 p_eventid => p_event_id,
490 p_event_fired => 'CLASS_CANCEL');
491
492 end if;
493
494 --
495 exception
496 when hr_api.validate_enabled then
497 --
498 -- As the Validate_Enabled exception has been raised
499 -- we must rollback to the savepoint
500 --
501 ROLLBACK TO update_event;
502 --
503 -- Only set output warning arguments
504 -- (Any key or derived arguments must be set to null
505 -- when validation only mode is being used.)
506 --
507 end Update_Event;
508 --
509 end ota_evt_api_upd2;
[Home] [Help]