[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.43.12010000.3 2008/09/17 08:04:02 srgnanas 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
142 l_waitlist_entry c_priority_waitlist%rowtype;
143 e_validation_error exception;
144 pragma exception_init(e_validation_error, -20002);
145
146 begin
147
148 hr_utility.set_location('Entering:'|| l_proc, 10);
149 p_return_status := 'T';
150 --
151 -- Issue a savepoint
152 --
153 savepoint AUTO_ENROLL_FROM_WAITLIST;
154 hr_utility.set_location(l_proc, 20);
155
156 --
157 -- Call Before Process User Hook
158 --
159 begin
160 OTA_TDB_WAITLIST_BK1.AUTO_ENROLL_FROM_WAITLIST_B
161 (p_business_group_id => p_business_group_id
162 ,p_event_id => p_event_id
163 );
164 exception
165 when hr_api.cannot_find_prog_unit then
166 hr_api.cannot_find_prog_unit_error
167 (p_module_name => 'AUTO_ENROLL_FROM_WAITLIST'
168 ,p_hook_type => 'BP'
169 );
170 end;
171
172 hr_utility.set_location(l_proc, 30);
173 --
174 -- Validation in addition to Row Handlers
175 --
176
177
178
179 hr_utility.set_location(l_proc, 40);
180 --
181 -- Process Logic
182 --
183
184 l_status_type_id := fnd_profile.value('OTA_AUTO_WAITLIST_BOOKING_STATUS');
185
186 if l_status_type_id is null then
187 --
188 --
189 -- As we don't know what status to set the waitlistees to, we can't continue.
190 -- Because of previous checks, this condition should never occur, so we don't take
191 -- any further action here.
192 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||
193 ','||'No Default booking status is found');
194
195 RAISE e_validation_error ;
196 --
197 else
198 --
199 --
200 -- Build a representation of the waitlist, correctly ordered, in a table.
201 -- This will save on coding, as we will only require one loop to go
202 -- through the waitlist.
203 --
204
205 if fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') = 'BP' then
206 --
207 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing, Waitlist Sort Criteria :' ||
208 fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') );
209 for l_waitlist_entry in c_priority_waitlist loop
210 --
211
212 t_waitlist_table(l_count).number_of_places := l_waitlist_entry.number_of_places;
213 t_waitlist_table(l_count).booking_id := l_waitlist_entry.booking_id;
214 t_waitlist_table(l_count).delegate_person_id := l_waitlist_entry.delegate_person_id;
215 t_waitlist_table(l_count).delegate_contact_id := l_waitlist_entry.delegate_contact_id;
216 t_waitlist_table(l_count).object_version_number := l_waitlist_entry.object_version_number;
217 t_waitlist_table(l_count).finance_line_id := l_waitlist_entry.finance_line_id;
218 t_waitlist_table(l_count).tfl_object_version_number := l_waitlist_entry.tfl_object_version_number;
219 t_waitlist_table(l_count).date_booking_placed := l_waitlist_entry.date_booking_placed;
220 t_waitlist_table(l_count).internal_booking_flag := l_waitlist_entry.internal_booking_flag;
221
222
223
224 l_count := l_count+1;
225 --
226 end loop;
227 --
228 else
229 FND_FILE.PUT_LINE(FND_FILE.LOG,'Auto waitlist processing, For other Waitlist Sort Criteria');
230 --
231 for l_waitlist_entry in c_date_waitlist loop
232 --
233 t_waitlist_table(l_count).number_of_places := l_waitlist_entry.number_of_places;
234 t_waitlist_table(l_count).booking_id := l_waitlist_entry.booking_id;
235 t_waitlist_table(l_count).delegate_person_id := l_waitlist_entry.delegate_person_id;
236 t_waitlist_table(l_count).delegate_contact_id := l_waitlist_entry.delegate_contact_id;
237 t_waitlist_table(l_count).object_version_number := l_waitlist_entry.object_version_number;
238 t_waitlist_table(l_count).finance_line_id := l_waitlist_entry.finance_line_id;
239 t_waitlist_table(l_count).tfl_object_version_number := l_waitlist_entry.tfl_object_version_number;
240 t_waitlist_table(l_count).date_booking_placed := l_waitlist_entry.date_booking_placed;
241 t_waitlist_table(l_count).internal_booking_flag := l_waitlist_entry.internal_booking_flag;
242 l_count := l_count+1;
243 --
244 end loop;
245 --
246 end if;
247
248
249
250 -- for i in t_waitlist_table.first..t_waitlist_table.last loop
251 for i in 1..t_waitlist_table.COUNT loop
252
253 --
254
255 l_vacancies := ota_evt_bus2.get_vacancies(p_event_id);
256
257 if nvl(l_vacancies,0) <= 0 then
258 --
259 exit;
260 --
261 end if;
262
263
264
265 if t_waitlist_table(i).number_of_places <= l_vacancies or
266 l_vacancies is null then
267 --
268 hr_utility.set_location('UPDATING DETAILS FOR BOOKING #'||to_char(t_waitlist_table(i).booking_id), 41);
269 begin
270 --
271
272
273 /* ota_utility.other_bookings_clash(to_char(t_waitlist_table(i).delegate_person_id),
274 to_char(t_waitlist_table(i).delegate_contact_id),
275 p_event_id,
276 l_status_type_id,
277 l_dummy,
278 l_warn);
279 */
280 if t_waitlist_table(i).delegate_person_id is not null or
281 t_waitlist_table(i).delegate_contact_id is not null then
282 l_warn := ota_tdb_bus2.other_bookings_clash(to_char(t_waitlist_table(i).delegate_person_id),
283 to_char(t_waitlist_table(i).delegate_contact_id),
284 p_event_id,
285 l_status_type_id);
286
287 end if;
288 if l_warn = True then
289 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc||':'||
290 to_char(t_waitlist_table(i).booking_id)||','||'Cannot book the person onto the event as they are
291 already booked onto another event on the same day.');
292
293
294 hr_utility.set_location('NEW STATUS = '||to_char(l_status_type_id), 42);
295 else
296
297 l_booking_ovn := t_waitlist_table(i).object_version_number ;
298 l_finance_ovn := t_waitlist_table(i).tfl_object_version_number;
299 l_finance_line_id := t_waitlist_table(i).finance_line_id;
300
301 /* Start Bug 1720734 */
302 l_enroll := 'Y';
303 if t_waitlist_table(i).internal_booking_flag = 'Y' then
304
305 open c_max_internal;
306 --
307 fetch c_max_internal into l_max_internal;
308 --
309 close c_max_internal;
310 --
311 -- If max internal is null then we can enroll freely without worrying
312 -- about limits on the event.
313 --
314 if l_max_internal is not null then
315 --
316 -- Check how many places we want to allocate are available as
317 -- internal places.
318 --
319 open c_places_taken(t_waitlist_table(i).booking_id);
320 --
321 fetch c_places_taken into l_number_taken;
322 --
323 close c_places_taken;
324
325 if l_number_taken is null then
326 l_number_taken := 0;
327 end if;
328
329 --
330 -- Check if number of places available is exceeded by number required
331 --
332 if t_waitlist_table(i).number_of_places > (l_max_internal - l_number_taken) then
333 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc||' '||
334 'Booking Id :'||t_waitlist_table(i).booking_id||','||
335 'The maximum number of internal delegates for this event has been exceed by this booking. '||
336 'Either reduce the number of places for the booking or increase the maximum amount of '||
337 'internal delegates for the event.');
338 l_enroll := 'N';
339 end if;
340 end if;
341 end if;
342 /* End Bug 1720734 */
343
344 if l_enroll = 'Y' then
345 ota_tdb_shd.lck(t_waitlist_table(i).booking_id ,l_booking_ovn);
346 --
347
348 FND_FILE.PUT_LINE(FND_FILE.LOG,'Move Booking id :'||t_waitlist_table(i).booking_id ||
349 '.. From waitlist..');
350 ota_tdb_api_upd2.update_enrollment(
351 p_booking_id => t_waitlist_table(i).booking_id
352 ,p_object_version_number => l_booking_ovn
353 ,p_tfl_object_version_number => l_finance_ovn
354 ,p_event_id => p_event_id
355 ,p_finance_line_id => l_finance_line_id
356 ,p_booking_status_type_id => l_status_type_id
357 ,p_number_of_places => t_waitlist_table(i).number_of_places
358 ,p_date_status_changed => sysdate -- Added for bug# 1708632
359 ,p_date_booking_placed => t_waitlist_table(i).date_booking_placed -- Added for bug# 1708632
360 ,p_status_change_comments => null); /* Bug# 3469326 */
361
362
363 end if;
364 end if;
365 --
366 exception
367 when e_validation_error then
368 hr_utility.set_location('ENROLLMENT ERROR BEING HANDLED', 49);
369
370 --
371 -- This exception handler is executed following any application
372 -- error encountered when attempting to enroll a delegate from
373 -- the waitlist. (e.g. a double booking was found)
374 --
375 -- If code is to be added at a later stage to handle notifications
376 -- of these errors, this would be the best place to call the code from.
377 -- At present, there are no notifications, so we take no action,
378 -- other than to proceed onto the next waitlisted booking.
379 --
380 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||
381 to_char(t_waitlist_table(i).booking_id)||','||'Error found when try to move a student from waitlisted');
382
383 p_return_status := 'F';
384 --
385 return;
386
387 when others then
388 --
389 -- A validation or unexpected error has occured
390 --
391 --rollback to AUTO_ENROLL_FROM_WAITLIST;
392 hr_utility.set_location(' Leaving:'||l_proc, 80);
393 p_return_status := 'F';
394 l_error_num := SQLCODE;
395 l_error_msg := SUBSTR(SQLERRM, 1, 300);
396 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc||' '||
397 'Booking Id :'||t_waitlist_table(i).booking_id||','||
398 l_error_num||':'||l_error_msg);
399 --
400 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception other error:');
401
402 end;
403 --
404 end if;
405 --
406 end loop;
407 --
408 end if;
409
410
411 hr_utility.set_location(l_proc, 50);
412 --
413 -- Call After Process User Hook
414 --
415 begin
416 OTA_TDB_WAITLIST_BK1.AUTO_ENROLL_FROM_WAITLIST_A
417 (p_business_group_id => p_business_group_id
418 ,p_event_id => p_event_id
419 );
420 exception
421 when hr_api.cannot_find_prog_unit then
422 hr_api.cannot_find_prog_unit_error
423 (p_module_name => 'AUTO_ENROLL_FROM_WAITLIST'
424 ,p_hook_type => 'AP'
425 );
426 end;
427 hr_utility.set_location(l_proc, 60);
428 --
429 -- When in validation only mode raise the Validate_Enabled exception
430 --
431 if p_validate then
432 raise hr_api.validate_enabled;
433 end if;
434 --
435 hr_utility.set_location(' Leaving:'||l_proc, 70);
436 exception
437 when hr_api.validate_enabled then
438 --
439 -- As the Validate_Enabled exception has been raised
440 -- we must rollback to the savepoint
441 --
442 -- rollback to AUTO_ENROLL_FROM_WAITLIST;
443 --
444 hr_utility.set_location(' Leaving:'||l_proc, 80);
445 when others then
446 --
447 -- A validation or unexpected error has occured
448 --
449 rollback to AUTO_ENROLL_FROM_WAITLIST;
450 hr_utility.set_location(' Leaving:'||l_proc, 90);
451 p_return_status := 'F';
452 raise;
453 end AUTO_ENROLL_FROM_WAITLIST;
454 --
455 end OTA_OM_TDB_WAITLIST_API;