[Home] [Help]
PACKAGE BODY: APPS.OTA_EVT_BUS2
Source
1 Package Body ota_evt_bus2 as
2 /* $Header: otevt02t.pkb 120.6 2008/01/24 10:42:18 smahanka noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_evt_bus2.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< Lock_Event >----------------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- PUBLIC
15 -- Description: Locks the Event
16 --
17 --
18 procedure LOCK_EVENT (p_event_id in number) is
19 --
20 l_evt_object_version_number number;
21 --
22 cursor get_event is
23 select object_version_number
24 from ota_events
25 where event_id = p_event_id;
26 --
27 l_proc varchar2(72) := g_package||'lock_event';
28 --
29 Begin
30 --
31 hr_utility.set_location('Entering:'||l_proc, 5);
32 --
33 open get_event;
34 fetch get_event into l_evt_object_version_number;
35 if get_event%notfound then
36 null;
37 end if;
38 --
39 ota_evt_shd.lck(p_event_id
40 ,l_evt_object_version_number);
41 --
42 hr_utility.set_location('Leaving:'||l_proc, 10);
43 --
44 end LOCK_EVENT;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |--------------------------< Get Total Places >----------------------------|
48 -- ----------------------------------------------------------------------------
49 --
50 -- PUBLIC
51 -- Description: Get total number of placed delegates.
52 --
53 --
54 function Get_Total_Places(p_all_or_internal in varchar2
55 ,p_event_id in number) return number is
56 --
57 l_tdb_total_places number;
58 --
59 cursor get_total_places is
60 select nvl(sum(number_of_places),0)
61 from ota_delegate_bookings tdb
62 , ota_booking_status_types bst
63 , ota_events evt
64 where tdb.event_id = p_event_id
65 and evt.event_id = p_event_id
66 and tdb.booking_status_type_id = bst.booking_status_type_id
67 and bst.type in ('P','A','E')
68 and tdb.internal_booking_flag = decode(p_all_or_internal,
69 'INTERNAL','Y',tdb.internal_booking_flag)
70 and ((price_basis = 'C' and delegate_contact_id is null)
71 or (price_basis <> 'C'));
72 --
73 l_proc varchar2(72) := g_package||'get_total_places';
74 --
75 begin
76 --
77 hr_utility.set_location('Entering:'||l_proc, 5);
78 --
79 open get_total_places;
80 fetch get_total_places into l_tdb_total_places;
81 close get_total_places;
82 --
83 return l_tdb_total_places;
84 --
85 hr_utility.set_location('Leaving:'||l_proc, 10);
86 --
87 end Get_Total_Places;
88 --
89 -- ----------------------------------------------------------------------------
90 -- |--------------------------< Check Places >--------------------------------|
91 -- ----------------------------------------------------------------------------
92 --
93 -- PUBLIC
94 -- Description: Checks the Maximum_Attendees and Maximum_Internal_Attendees
95 -- when updated.
96 --
97 --
98 procedure Check_Places(p_event_id in number
99 ,p_maximum_attendees in number
100 ,p_maximum_internal_attendees in number) is
101 --
102 --
103 l_total_places number := ota_evt_bus2.get_total_places('ALL',p_event_id);
104 l_total_internal_places number := ota_evt_bus2.get_total_places('INTERNAL',p_event_id);
105 --
106 l_proc varchar2(72) := g_package||'check_places';
107 --
108 begin
109 --
110 hr_utility.set_location('Entering:'||l_proc, 5);
111 --
112 if p_maximum_attendees is not null and p_maximum_internal_attendees is not null then
113 if p_maximum_internal_attendees > p_maximum_attendees then
114 fnd_message.set_name('OTA','OTA_13512_EVT_MAX_ATT');
115 fnd_message.raise_error;
116 end if;
117 end if;
118 --
119 if p_maximum_attendees is not null then
120 if l_total_places > p_maximum_attendees then
121 fnd_message.set_name('OTA','OTA_13513_EVT_MAX_INT_ATT');
122 fnd_message.raise_error;
123 end if;
124 end if;
125 --
126 if p_maximum_internal_attendees is not null then
127 if l_total_internal_places > p_maximum_internal_attendees then
128 fnd_message.set_name('OTA','OTA_13514_EVT_MAX_NORM_ATT');
129 fnd_message.raise_error;
130 end if;
131 end if;
132 --
133 hr_utility.set_location('Leaving:'||l_proc, 10);
134 --
135 end Check_Places;
136 --
137 -- ----------------------------------------------------------------------------
138 -- |------------------------------< Reset_Event_Status >----------------------|
139 -- ----------------------------------------------------------------------------
140 --
141 -- PUBLIC
142 -- Description: Reset Event Status
143 --
144 -- Resets the Event Status for the event record if event is reached
145 -- to full.
146 --
147 procedure Reset_Event_Status(p_event_id in number
148 ,p_object_version_number in out nocopy number
149 ,p_event_status in varchar2
150 ,p_maximum_attendees in number) is
151 --
152 l_total_places number;
153 l_new_event_status varchar2(30);
154 --
155 --
156 l_proc varchar2(72) := g_package||'reset_event_status';
157 --
158 l_no_of_waitlist_candidate number;
159 l_no_of_int_waitlist_can number;
160
161 cursor c_check_waitlist_candidates is
162 select count(rowid)
163 from ota_delegate_bookings odb
164 where event_id = p_event_id and
165 booking_status_type_id in
166 (select booking_status_type_id
167 from ota_booking_status_types
168 where type = 'W');
169
170
171 cursor c_check_int_waitlist_can is
172 select count(rowid)
173 from ota_delegate_bookings odb
174 where event_id = p_event_id and
175 internal_booking_flag = 'Y' and
176 booking_status_type_id in
177 (select booking_status_type_id
178 from ota_booking_status_types
179 where type = 'W' );
180
181 --
182 begin
183 --
184 hr_utility.set_location('Entering:'||l_proc, 5);
185 --
186 if p_event_status in ('N','F') then
187 if p_maximum_attendees is not null then
188 l_total_places := get_total_places('ALL',p_event_id);
189 --
190 if l_total_places = p_maximum_attendees and p_event_status in ('N') then
191 l_new_event_status := 'F';
192 elsif l_total_places < p_maximum_attendees then
193
194
195
196 if fnd_profile.value('OTA_AUTO_WAITLIST_ACTIVE') = 'Y' then
197
198 open c_check_waitlist_candidates;
199 fetch c_check_waitlist_candidates into l_no_of_waitlist_candidate;
200 close c_check_waitlist_candidates;
201
202 open c_check_int_waitlist_can;
203 fetch c_check_int_waitlist_can into l_no_of_int_waitlist_can;
204 close c_check_int_waitlist_can;
205
206 if l_no_of_waitlist_candidate - l_no_of_int_waitlist_can > 0 then
207 l_new_event_status := 'F';
208 else
209 l_new_event_status := 'N';
210 end if;
211 else
212 l_new_event_status := 'N';
213 end if;
214
215 end if;
216 --
217 if p_event_status <> l_new_event_status then
218 ota_evt_upd.upd(p_event_id => p_event_id
219 ,p_object_version_number => p_object_version_number
220 ,p_event_status => l_new_event_status);
221 end if;
222 end if;
223 end if;
224 --
225 hr_utility.set_location('Leaving:'||l_proc, 10);
226 --
227 end Reset_Event_Status;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |--------------------------< Resource Bookings Exists >--------------------|
231 -- ----------------------------------------------------------------------------
232 --
233 -- PUBLIC
234 -- Description: Checks whether Resource bookings exists for a particular event.
235 --
236 --
237 Function Resource_Booking_Exists (p_event_id in number) return boolean is
238 --
239 cursor c_check_resource_bookings is
240 select count(resource_booking_id)
241 from ota_resource_bookings res
242 where res.event_id = p_event_id;
243 --
244 l_resource_booking_exists number;
245 l_proc varchar2(72) := g_package||'resource_booking_exists';
246 --
247 begin
248 --
249 hr_utility.set_location('Entering:'|| l_proc, 5);
250 --
251 -- Check if resource_booking exists.
252 --
253 open c_check_resource_bookings;
254 fetch c_check_resource_bookings into l_resource_booking_exists;
255 close c_check_resource_bookings;
256 --
257 if l_resource_booking_exists = 0 then
258 return false;
259 else
260 return true;
261 end if;
262 --
263 hr_utility.set_location('Leaving:'|| l_proc, 10);
264 --
265 end Resource_Booking_Exists;
266 --
267 --
268 -- ----------------------------------------------------------------------------
269 -- |--------------------------< Finance Line Exists >-------------------------|
270 -- ----------------------------------------------------------------------------
271 --
272 -- PUBLIC
273 -- Description: Checks whether a finance line exists for a particular booking_Id.
274 --
275 --
276 Function Finance_Line_Exists (p_booking_id in number
277 ,p_cancelled_flag in varchar2) return boolean is
278 --
279 cursor c_check_finance_line is
280 select nvl(sum(booking_id),0)
281 from ota_finance_lines tfl
282 where tfl.booking_id = p_booking_id
283 and tfl.cancelled_flag = p_cancelled_flag;
284 l_finance_line_exists number;
285 --
286 l_proc varchar2(72) := g_package||'finance_line_exists';
287 --
288 begin
289 --
290 hr_utility.set_location('Entering:'|| l_proc, 5);
291 --
292 -- Check if finance line exists.
293 --
294 open c_check_finance_line;
295 fetch c_check_finance_line into l_finance_line_exists;
296 close c_check_finance_line;
297 --
298 if l_finance_line_exists = 0 then
299 return false;
300 else
301 return true;
302 end if;
303 --
304 hr_utility.set_location('Leaving:'|| l_proc, 10);
305 --
306 end Finance_Line_Exists;
307 --
308 --
309 -- ----------------------------------------------------------------------------
310 -- |--------------------------< Get Vacancies >-------------------------------|
311 -- ----------------------------------------------------------------------------
312 --
313 -- PUBLIC
314 -- Description: Get Vacancies
315 --
316 -- Get current vacancies for a particular event.
317 --
318 Function Get_Vacancies(p_event_id in number) return number is
319 --
320 p_rec ota_evt_shd.g_rec_type;
321 p_event_exists boolean;
322 l_vacancies number;
323 l_total_places number := ota_evt_bus2.get_total_places('ALL',p_event_id);
324 l_proc varchar2(72) := g_package||'get_vacancies';
325 --
326 --
327 begin
328 --
329 hr_utility.set_location('Entering:'|| l_proc, 5);
330 --
331 ota_evt_shd.get_event_details(p_event_id,p_rec,p_event_exists);
332 --
333 if p_rec.maximum_attendees is not null then
334 l_vacancies := p_rec.maximum_attendees - l_total_places;
335 else
336 l_vacancies := null;
337 end if;
338 --
339 return l_vacancies;
340 --
341 hr_utility.set_location('Leaving:'|| l_proc, 10);
342 --
343 end Get_Vacancies;
344 --
345 --
346 -- ----------------------------------------------------------------------------
347 -- |--------------------------< Wait List Required >--------------------------|
348 -- ----------------------------------------------------------------------------
349 --
350 -- PUBLIC
351 -- Description: Check if Wait List window is required.
352 --
353 -- Returns Boolean - True for Yes.
354 --
355 Function Wait_List_Required (p_event_type in varchar2
356 ,p_event_id in number
357 ,p_event_status in varchar2
358 ,p_booking_status_type_id in number default null)
359 Return Boolean is
360 --
361 l_rec ota_evt_shd.g_rec_type;
362 l_event_exists boolean;
363 l_old_booking_status varchar2(30);
364 l_new_booking_status varchar2(30);
365 l_total_attendees number := ota_evt_bus2.get_total_places('ALL',p_event_id);
366 l_total_waitlisted number :=
367 ota_tdb_bus.places_for_status(p_event_id => p_event_id
368 ,p_all_or_internal => 'ALL'
369 ,p_status_type => 'W');
370 l_proc varchar2(72) := g_package||'wait_list_required';
371 --
372 Begin
373 --
374 ota_evt_shd.get_event_details(p_event_id,l_rec,l_event_exists);
375 --
376 hr_utility.set_location('Entering:'|| l_proc, 5);
377 --
378 if p_event_type = 'ENROLLMENT' then
379 --
380 l_old_booking_status := ota_tdb_bus.booking_status_type(
381 ota_tdb_shd.g_old_rec.booking_status_type_id);
382 l_new_booking_status := ota_tdb_bus.booking_status_type(
383 p_booking_status_type_id);
384 if l_old_booking_status in ('P','A','E') and --6683076.Added 'E' as new status.
385 l_new_booking_status in ('W','C','R') and
386 l_total_waitlisted <> 0 then
387 return (true);
388 else
389 return (false);
390 end if;
391 --
392 elsif p_event_type = 'EVENT' then
393 --
394 -- ***
395 if (p_event_status = 'N' or p_event_status = 'F')
396 and l_rec.maximum_attendees > l_total_attendees
397 and l_total_waitlisted <> 0 then
398 return (true);
399 else
400 return (false);
401 end if;
402 --
403 else
404 --
405 return (false);
406 --
407 end if;
408 --
409 hr_utility.set_location('Leaving:'|| l_proc, 10);
410 --
411 end Wait_List_Required;
412 --
413 -- --------------------------------------------------------------------------------------------
414 -- |--------------------------< Check if Mandatory Associations exists for a particular event >--------------------------------|
415 -- ------------------------------------------------------------------------------------------
416 --
417 function mandatory_associations_exists(p_event_id in number)return boolean is
418
419 cursor check_mandatory_assoc is
420 select count(*)
421 from
422 ota_event_associations
423 where
424 nvl(mandatory_enrollment_flag,'N')= 'Y' and
425 event_id = p_event_id;
426
427 l_proc varchar2(72) := g_package ||'mandatory_associations_exists';
428 l_mandatory_assoc_exists number;
429
430 begin
431
432 hr_utility.set_location('Leaving:'|| l_proc, 5);
433 -- check if mandatory associations are defined for the class
434 OPEN check_mandatory_assoc;
435 FETCH check_mandatory_assoc into l_mandatory_assoc_exists;
436 CLOSE check_mandatory_assoc;
437
438 if l_mandatory_assoc_exists = 0 then
439 return false;
440 else
441 return true;
442 end if;
443
444 hr_utility.set_location('Leaving:'|| l_proc, 10);
445
446 end mandatory_associations_exists;
447
448
449
450 -- ----------------------------------------------------------------------------
451 -- |--------------------------< Check Mandatory Associations >--------------------------------|
452 -- ----------------------------------------------------------------------------
453 --
454 -- PUBLIC
455 -- Description: Checks if mandatory enrollment type of event association has been defined
456 --while updating the Maximum_Attendees and Maximum_Internal_Attendees.This is performed as mandatory enrollments
457 --can be created for a class only if Maximum_Attendees and Maximum_Internal_Attendees are not defined for
458 --the class
459 --
460 --
461 procedure Check_Mandatory_Associations(p_event_id in number
462 ,p_maximum_attendees in number
463 ,p_maximum_internal_attendees in number) is
464 --
465 --
466 l_mandatory_association_exists boolean := ota_evt_bus2.mandatory_associations_exists(p_event_id);
467
468 l_proc varchar2(72) := g_package||'check_mandatory_associations';
469 --
470 begin
471 --
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 if p_maximum_attendees is not null or p_maximum_internal_attendees is not null then
475 if ((l_mandatory_association_exists and p_maximum_attendees >= 0) or
476 (l_mandatory_association_exists and p_maximum_internal_attendees >= 0)) then
477 fnd_message.set_name('OTA','OTA_467070_MANDATORY_ENR_ERR');
478 fnd_message.raise_error;
479 end if;
480
481 end if;
482 --
483 hr_utility.set_location('Leaving:'||l_proc, 10);
484 --
485 end Check_Mandatory_Associations;
486 end ota_evt_bus2;