[Home] [Help]
PACKAGE BODY: APPS.OTA_OM_TDB_WAITLIST_API
Source
1 Package Body OTA_OM_TDB_WAITLIST_API as
2 /* $Header: ottomint.pkb 120.58.12020000.3 2012/06/29 11:19:18 rpahune ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_OM_TDB_WAITLIST_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< AUTO_ENROLL_FROM_WAITLIST >-------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 -- {Start Of Comments}
13 --
14 -- Description:
15 --
16 --
17 -- Prerequisites:
18 --
19 --
20 -- In Parameters:
21 -- p_validate
22 -- p_business_group_id
23 -- p_event_id
24 --
25 -- Out Parameters
26 -- p_return_status
27 --
28 --
29 -- Post Success:
30 --
31 --
32 -- Name Type Description
33 --
34 -- Post Failure:
35 --
36 --
37 -- Access Status:
38 -- Public.
39 --
40 -- {End Of Comments}
41 --
42
43 procedure AUTO_ENROLL_FROM_WAITLIST
44 (p_validate in boolean
45 --Remove default for gscc warning (p_validate in boolean default false
46 ,p_business_group_id in number
47 ,p_event_id in number
48 -- remove default for gscc warning ,p_event_id in number default null
49 ,p_return_status out nocopy varchar2
50 ) is
51
52 --
53 -- Declare cursors and local variables
54 --
55 type r_rec is record (
56 booking_id number,
57 delegate_person_id number,
58 delegate_contact_id number,
59 number_of_places number,
60 object_version_number number,
61 finance_line_id number,
62 tfl_object_version_number number,
63 date_booking_placed date,
64 internal_booking_flag varchar2(1) );
65
66 type t_table is table of r_rec index by binary_integer;
67
68 t_waitlist_table t_table;
69
70 cursor c_date_waitlist is
71 select tdb.booking_id,
72 tdb.delegate_person_id,
73 tdb.delegate_contact_id,
74 tdb.number_of_places,
75 tdb.object_version_number,
76 tfl.finance_line_id,
77 tfl.object_version_number tfl_object_version_number,
78 tdb.date_booking_placed ,
79 tdb.internal_booking_flag
80 from ota_delegate_bookings tdb,
81 ota_booking_status_types bst,
82 ota_finance_lines tfl
83 where tfl.booking_id(+) = tdb.booking_id
84 and tdb.booking_status_type_id = bst.booking_status_type_id
85 and bst.type = 'W'
86 and tdb.event_id = p_event_id
87 order by tdb.date_booking_placed;
88
89 cursor c_priority_waitlist is
90 select tdb.booking_id,
91 tdb.delegate_person_id,
92 tdb.delegate_contact_id,
93 tdb.number_of_places,
94 tdb.object_version_number,
95 tfl.finance_line_id,
96 tfl.object_version_number tfl_object_version_number,
97 tdb.date_booking_placed ,
98 tdb.internal_booking_flag
99 from ota_delegate_bookings tdb,
100 ota_booking_status_types bst,
101 ota_finance_lines tfl
102 where tfl.booking_id(+)= tdb.booking_id
103 and tdb.booking_status_type_id = bst.booking_status_type_id
104 and bst.type = 'W'
105 and tdb.event_id = p_event_id
106 order by tdb.booking_priority,
107 tdb.booking_id;
108
109 cursor c_max_internal is
110 select maximum_internal_attendees
111 from ota_events
112 where event_id = p_event_id;
113
114 cursor c_places_taken (p_booking_id in number) is
115 select sum(a.number_of_places)
116 from ota_delegate_bookings a,
117 ota_booking_status_types b
118 where a.event_id = p_event_id
119 and a.booking_status_type_id = b.booking_status_type_id
120 and b.type in ('P','A')
121 and a.internal_booking_flag = 'Y'
122 and a.booking_id <> nvl(p_booking_id, hr_api.g_number);
123
124
125 l_proc varchar2(72) := g_package||'AUTO_ENROLL_FROM_WAITLIST';
126 l_count number(9) := 1;
127 l_vacancies number(10);
128 l_status_type_id ota_delegate_bookings.booking_status_type_id%type;
129 l_dummy boolean;
130 l_return_status varchar2(1) := 'T';
131 l_warn boolean := False;
132 l_booking_ovn number;
133 l_finance_ovn number;
134 l_finance_line_id number;
135 l_error_num VARCHAR2(30) := '';
136 l_error_msg VARCHAR2(1000) := '';
137
138 l_max_internal number;
139 l_number_taken number;
140 l_enroll varchar2(1);
141 l_booking_status varchar2(30) := '';--Bug 14064437
142 l_event_rec ota_evt_shd.g_rec_type;
143 l_event_exists boolean;
144
145 l_waitlist_entry c_priority_waitlist%rowtype;
146 e_validation_error exception;
147 pragma exception_init(e_validation_error, -20002);
148
149 begin
150
151 hr_utility.set_location('Entering:'|| l_proc, 10);
152 p_return_status := 'T';
153 --
154 -- Issue a savepoint
155 --
156 savepoint AUTO_ENROLL_FROM_WAITLIST;
157 hr_utility.set_location(l_proc, 20);
158
159 --
160 -- Call Before Process User Hook
161 --
162 begin
163 OTA_TDB_WAITLIST_BK1.AUTO_ENROLL_FROM_WAITLIST_B
164 (p_business_group_id => p_business_group_id
165 ,p_event_id => p_event_id
166 );
167 exception
168 when hr_api.cannot_find_prog_unit then
169 hr_api.cannot_find_prog_unit_error
170 (p_module_name => 'AUTO_ENROLL_FROM_WAITLIST'
171 ,p_hook_type => 'BP'
172 );
173 end;
174
175 hr_utility.set_location(l_proc, 30);
176 --
177 -- Validation in addition to Row Handlers
178 --
179
180
181
182 hr_utility.set_location(l_proc, 40);
183 --
184 -- Process Logic
185 --
186
187 l_status_type_id := fnd_profile.value('OTA_AUTO_WAITLIST_BOOKING_STATUS');
188
189 if l_status_type_id is null then
190 --
191 --
192 -- As we don't know what status to set the waitlistees to, we can't continue.
193 -- Because of previous checks, this condition should never occur, so we don't take
194 -- any further action here.
195 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||
196 ','||'No Default booking status is found');
197
198 RAISE e_validation_error ;
199 --
200 else
201 --
202 --
203 -- Build a representation of the waitlist, correctly ordered, in a table.
204 -- This will save on coding, as we will only require one loop to go
205 -- through the waitlist.
206 --
207
208 if fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') = 'BP' then
209 --
210 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing, Waitlist Sort Criteria :' ||
211 fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') );
212 for l_waitlist_entry in c_priority_waitlist loop
213 --
214
215 t_waitlist_table(l_count).number_of_places := l_waitlist_entry.number_of_places;
216 t_waitlist_table(l_count).booking_id := l_waitlist_entry.booking_id;
217 t_waitlist_table(l_count).delegate_person_id := l_waitlist_entry.delegate_person_id;
218 t_waitlist_table(l_count).delegate_contact_id := l_waitlist_entry.delegate_contact_id;
219 t_waitlist_table(l_count).object_version_number := l_waitlist_entry.object_version_number;
220 t_waitlist_table(l_count).finance_line_id := l_waitlist_entry.finance_line_id;
221 t_waitlist_table(l_count).tfl_object_version_number := l_waitlist_entry.tfl_object_version_number;
222 t_waitlist_table(l_count).date_booking_placed := l_waitlist_entry.date_booking_placed;
223 t_waitlist_table(l_count).internal_booking_flag := l_waitlist_entry.internal_booking_flag;
224
225
226
227 l_count := l_count+1;
228 --
229 end loop;
230 --
231 else
232 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing, For other Waitlist Sort Criteria');
233 --
234 for l_waitlist_entry in c_date_waitlist loop
235 --
236 t_waitlist_table(l_count).number_of_places := l_waitlist_entry.number_of_places;
237 t_waitlist_table(l_count).booking_id := l_waitlist_entry.booking_id;
238 t_waitlist_table(l_count).delegate_person_id := l_waitlist_entry.delegate_person_id;
239 t_waitlist_table(l_count).delegate_contact_id := l_waitlist_entry.delegate_contact_id;
240 t_waitlist_table(l_count).object_version_number := l_waitlist_entry.object_version_number;
241 t_waitlist_table(l_count).finance_line_id := l_waitlist_entry.finance_line_id;
242 t_waitlist_table(l_count).tfl_object_version_number := l_waitlist_entry.tfl_object_version_number;
243 t_waitlist_table(l_count).date_booking_placed := l_waitlist_entry.date_booking_placed;
244 t_waitlist_table(l_count).internal_booking_flag := l_waitlist_entry.internal_booking_flag;
245 l_count := l_count+1;
246 --
247 end loop;
248 --
249 end if;
250
251 --Bug 14064437
252 l_booking_status := ota_tdb_bus.booking_status_type(l_status_type_id);
253 ota_evt_shd.get_event_details (p_event_id,
254 l_event_rec,
255 l_event_exists);
256
257 -- for i in t_waitlist_table.first..t_waitlist_table.last loop
258 for i in 1..t_waitlist_table.COUNT loop
259
260 --
261
262 l_vacancies := ota_evt_bus2.get_vacancies(p_event_id);
263
264 if nvl(l_vacancies,0) <= 0 or (l_event_rec.event_status = 'P' and l_booking_status in ('P','A','E')) then --Bug 14064437
265 --
266 exit;
267 --
268 end if;
269
270
271
272 if t_waitlist_table(i).number_of_places <= l_vacancies or
273 l_vacancies is null then
274 --
275 hr_utility.set_location('UPDATING DETAILS FOR BOOKING #'||to_char(t_waitlist_table(i).booking_id), 41);
276 begin
277 --
278
279
280 /* ota_utility.other_bookings_clash(to_char(t_waitlist_table(i).delegate_person_id),
281 to_char(t_waitlist_table(i).delegate_contact_id),
282 p_event_id,
283 l_status_type_id,
284 l_dummy,
285 l_warn);
286 */
287 if t_waitlist_table(i).delegate_person_id is not null or
288 t_waitlist_table(i).delegate_contact_id is not null then
289 l_warn := ota_tdb_bus2.other_bookings_clash(to_char(t_waitlist_table(i).delegate_person_id),
290 to_char(t_waitlist_table(i).delegate_contact_id),
291 p_event_id,
292 l_status_type_id);
293
294 end if;
295 if l_warn = True then
296 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc||':'||
297 to_char(t_waitlist_table(i).booking_id)||','||'Cannot book the person onto the event as they are
298 already booked onto another event on the same day.');
299
300
301 hr_utility.set_location('NEW STATUS = '||to_char(l_status_type_id), 42);
302 else
303
304 l_booking_ovn := t_waitlist_table(i).object_version_number ;
305 l_finance_ovn := t_waitlist_table(i).tfl_object_version_number;
306 l_finance_line_id := t_waitlist_table(i).finance_line_id;
307
308 /* Start Bug 1720734 */
309 l_enroll := 'Y';
310 if t_waitlist_table(i).internal_booking_flag = 'Y' then
311
312 open c_max_internal;
313 --
314 fetch c_max_internal into l_max_internal;
315 --
316 close c_max_internal;
317 --
318 -- If max internal is null then we can enroll freely without worrying
319 -- about limits on the event.
320 --
321 if l_max_internal is not null then
322 --
323 -- Check how many places we want to allocate are available as
324 -- internal places.
325 --
326 open c_places_taken(t_waitlist_table(i).booking_id);
327 --
328 fetch c_places_taken into l_number_taken;
329 --
330 close c_places_taken;
331
332 if l_number_taken is null then
333 l_number_taken := 0;
334 end if;
335
336 --
337 -- Check if number of places available is exceeded by number required
338 --
339 if t_waitlist_table(i).number_of_places > (l_max_internal - l_number_taken) then
340 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc||' '||
341 'Booking Id :'||t_waitlist_table(i).booking_id||','||
342 'The maximum number of internal delegates for this event has been exceed by this booking. '||
343 'Either reduce the number of places for the booking or increase the maximum amount of '||
344 'internal delegates for the event.');
345 l_enroll := 'N';
346 end if;
347 end if;
348 end if;
349 /* End Bug 1720734 */
350
351 if l_enroll = 'Y' then
352 ota_tdb_shd.lck(t_waitlist_table(i).booking_id ,l_booking_ovn);
353 --
354
355 FND_FILE.PUT_LINE(FND_FILE.LOG,'Move Booking id :'||t_waitlist_table(i).booking_id ||
356 '.. From waitlist..');
357 ota_tdb_api_upd2.update_enrollment(
358 p_booking_id => t_waitlist_table(i).booking_id
359 ,p_object_version_number => l_booking_ovn
360 ,p_tfl_object_version_number => l_finance_ovn
361 ,p_event_id => p_event_id
362 ,p_finance_line_id => l_finance_line_id
363 ,p_booking_status_type_id => l_status_type_id
364 ,p_number_of_places => t_waitlist_table(i).number_of_places
365 ,p_date_status_changed => sysdate -- Added for bug# 1708632
366 ,p_date_booking_placed => t_waitlist_table(i).date_booking_placed -- Added for bug# 1708632
367 ,p_status_change_comments => null); /* Bug# 3469326 */
368
369
370 end if;
371 end if;
372 --
373 exception
374 when e_validation_error then
375 hr_utility.set_location('ENROLLMENT ERROR BEING HANDLED', 49);
376
377 --
378 -- This exception handler is executed following any application
379 -- error encountered when attempting to enroll a delegate from
380 -- the waitlist. (e.g. a double booking was found)
381 --
382 -- If code is to be added at a later stage to handle notifications
383 -- of these errors, this would be the best place to call the code from.
384 -- At present, there are no notifications, so we take no action,
385 -- other than to proceed onto the next waitlisted booking.
386 --
387 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||
388 to_char(t_waitlist_table(i).booking_id)||','||'Error found when try to move a student from waitlisted');
389
390 p_return_status := 'F';
391 --
392 return;
393
394 when others then
395 --
396 -- A validation or unexpected error has occured
397 --
398 --rollback to AUTO_ENROLL_FROM_WAITLIST;
399 hr_utility.set_location(' Leaving:'||l_proc, 80);
400 p_return_status := 'F';
401 l_error_num := SQLCODE;
402 l_error_msg := SUBSTR(SQLERRM, 1, 300);
403 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
404 'Booking Id :'||t_waitlist_table(i).booking_id||','||
405 l_error_num||':'||l_error_msg);
406 --
407 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception other error:');
408
409 end;
410 --
411 end if;
412 --
413 end loop;
414 --
415 end if;
416
417
418 hr_utility.set_location(l_proc, 50);
419 --
420 -- Call After Process User Hook
421 --
422 begin
423 OTA_TDB_WAITLIST_BK1.AUTO_ENROLL_FROM_WAITLIST_A
424 (p_business_group_id => p_business_group_id
425 ,p_event_id => p_event_id
426 );
427 exception
428 when hr_api.cannot_find_prog_unit then
429 hr_api.cannot_find_prog_unit_error
430 (p_module_name => 'AUTO_ENROLL_FROM_WAITLIST'
431 ,p_hook_type => 'AP'
432 );
433 end;
434 hr_utility.set_location(l_proc, 60);
435 --
436 -- When in validation only mode raise the Validate_Enabled exception
437 --
438 if p_validate then
439 raise hr_api.validate_enabled;
440 end if;
441 --
442 hr_utility.set_location(' Leaving:'||l_proc, 70);
443 exception
444 when hr_api.validate_enabled then
445 --
446 -- As the Validate_Enabled exception has been raised
447 -- we must rollback to the savepoint
448 --
449 -- rollback to AUTO_ENROLL_FROM_WAITLIST;
450 --
451 hr_utility.set_location(' Leaving:'||l_proc, 80);
452 when others then
453 --
454 -- A validation or unexpected error has occured
455 --
456 rollback to AUTO_ENROLL_FROM_WAITLIST;
457 hr_utility.set_location(' Leaving:'||l_proc, 90);
458 p_return_status := 'F';
459 raise;
460 end AUTO_ENROLL_FROM_WAITLIST;
461 --
462 end OTA_OM_TDB_WAITLIST_API;