[Home] [Help]
PACKAGE BODY: APPS.OTA_TDB_API_UPD2
Source
1 Package Body ota_tdb_api_upd2 as
2 /* $Header: ottdb02t.pkb 120.22.12010000.2 2009/08/12 14:15:14 smahanka ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ota_tdb_api_upd2.';
7 g_debug boolean := hr_utility.debug_enabled; -- Global Debug status variable
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< Check Status Change >--------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 -- PRIVATE
14 -- Description: Check the delegate booking Status when Updating.
15 --
16 --
17 procedure Check_Status_Change(p_event_id in number
18 ,p_booking_status_type_id in number
19 ,p_event_status in varchar2
20 ,p_number_of_places in number
21 ,p_maximum_attendees in number) is
22 --
23 -- Set up local variables
24 --
25
26 l_old_booking_status varchar2(30) := ota_tdb_bus.booking_status_type(
27 ota_tdb_shd.g_old_rec.booking_status_type_id);
28
29 l_booking_status varchar2(30) := ota_tdb_bus.booking_status_type(
30 p_booking_status_type_id);
31
32 l_booking_status_changed boolean := ota_general.value_changed(
33 ota_tdb_shd.g_old_rec.booking_status_type_id,
34 p_booking_status_type_id);
35
36 l_number_of_places_changed boolean := ota_general.value_changed(
37 ota_tdb_shd.g_old_rec.number_of_places,
38 p_number_of_places);
39
40 l_vacancies number := ota_evt_bus2.get_vacancies(p_event_id);
41
42 l_old_number_of_places number := ota_tdb_shd.g_old_rec.number_of_places;
43
44 l_old_event_id number := ota_tdb_shd.g_old_rec.event_id;
45 --
46 l_proc varchar2(72);
47 --
48 begin
49 --
50 if g_debug then
51 l_proc := g_package||'check_status_change';
52 hr_utility.set_location('Entering:'||l_proc, 5);
53 end if;
54
55 --
56 -- *** In case of Event Change -- Reset Event Status for Old Event ***
57 --
58
59 if p_event_id <> l_old_event_id and
60 l_booking_status in ('A','P','E') and
61 ( p_event_status = 'F' or
62 ota_evt_bus2.get_vacancies(p_event_id) <
63 p_number_of_places ) then
64
65 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
66 fnd_message.raise_error;
67 --
68 end if;
69 --
70 -- Check for exceeding max attendees.
71 --
72 if (l_booking_status in ('A','P','E') and
73 (ota_evt_bus2.get_vacancies(p_event_id) <
74 (p_number_of_places - l_old_number_of_places)))
75 or
76 (l_booking_status in ('A','P','E') and
77 l_old_booking_status not in ('A','P','E') and
78 ota_evt_bus2.get_vacancies(p_event_id) < p_number_of_places) then
79 --
80 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
81 fnd_message.raise_error;
82 --
83 end if;
84 --
85 -- Check booking status, if amended.
86 --
87 if l_booking_status_changed then
88 --
89 --
90 --
91 -- Check status change for Planned or Full Events.
92 --
93 if p_event_status in ('F','P','C') then
94 --
95 if l_old_booking_status not in ('P','A','E') and
96 l_booking_status in ('P','A','E') then
97 --
98
99 if l_old_booking_status = 'W' and
100 l_booking_status in ('P','A','E') and
101 ota_evt_bus2.get_vacancies(p_event_id) >= p_number_of_places then
102 null;
103 else
104 fnd_message.set_name('OTA','OTA_13521_TDB_CH_STATUS_FP');
105 fnd_message.raise_error;
106 end if;
107 --
108 end if;
109 --
110 end if;
111
112 --
113 -- Check status change for Cancelled Events.
114 --
115 if p_event_status in ('A') then
116 --
117 if l_booking_status <> 'C' then
118 --
119 fnd_message.set_name('OTA','OTA_13522_TDB_CH_STATUS_C');
120 fnd_message.raise_error;
121 --
122 end if;
123 --
124 end if;
125 --
126 end if;
127 --
128 if g_debug then
129 hr_utility.set_location('Leaving:'||l_proc, 10);
130 end if;
131 --
132 --
133 end Check_Status_Change;
134 --
135 -- ----------------------------------------------------------------------------
136 -- |-------------------------< deleteForumNotification >--------------------------|
137 -- ----------------------------------------------------------------------------
138 --
139 -- PRIVATE
140 -- Description: Delete the Forum notification record when a class is cancelled.
141 --
142 --
143 procedure deleteForumNotification(l_event_id in number
144 ,l_person_id in number
145 ,l_contact_id in number) is
146
147 --
148 -- Set up local variables
149 --
150
151 cursor c_get_forum_id is
152 select fns.forum_id,fns.object_version_number
153 from ota_frm_obj_inclusions foi,ota_frm_notif_subscribers fns
154 where foi.object_id = l_event_id
155 and foi.object_Type = 'E'
156 and foi.forum_id = fns.forum_id
157 and (fns.person_id = l_person_id or fns.contact_id = l_contact_id);
158 --
159 v_forum_id number;
160 v_object_version_number number;
161
162 l_proc varchar2(72);
163
164 begin
165 --
166 if g_debug then
167 l_proc := g_package||'deleteForumNotification';
168 hr_utility.set_location('Entering:'||l_proc, 5);
169 end if;
170
171 --Delete the forum notification record for this class,for this user
172 OPEN c_get_forum_id;
173 FETCH c_get_forum_id into v_forum_id, v_object_version_number;
174
175 LOOP
176 Exit When c_get_forum_id%notfound OR c_get_forum_id%notfound is null;
177
178 ota_fns_del.del
179 (
180 p_forum_id => v_forum_id
181 ,p_person_id => l_person_id
182 ,p_contact_id => l_contact_id
183 ,p_object_version_number => v_object_version_number
184 );
185
186 FETCH c_get_forum_id into v_forum_id, v_object_version_number;
187 End Loop;
188 Close c_get_forum_id;
189
190 --
191 if g_debug then
192 hr_utility.set_location('Leaving:'||l_proc, 10);
193 end if;
194 --
195 --
196 end deleteForumNotification;
197 --
198 -- ----------------------------------------------------------------------------
199 -- |-------------------------< createForumNotification >--------------------------|
200 -- ----------------------------------------------------------------------------
201 --
202 -- PRIVATE
203 -- Description: Create the Forum notification record when a class is changed.
204 --
205 --
206 procedure createForumNotification(l_event_id in number
207 ,l_person_id in number
208 ,l_contact_id in number
209 ,l_effective_date in date
210 ,l_booking_status_type_id in number) is
211
212 --
213 -- Set up local variables
214 --
215
216 Cursor csr_forums_for_class
217 is
218 Select fr.forum_id, fr.business_group_id from
219 ota_forums_b fr,
220 ota_frm_obj_inclusions foi
221 where fr.forum_id = foi.forum_id
222 and foi.object_type = 'E'
223 and foi.object_id = l_event_id
224 and fr.auto_notification_flag = 'Y';
225 --
226 v_forum_id number;
227 v_business_group_id number;
228 l_dummy number;
229 l_proc varchar2(72);
230 l_type ota_booking_status_types.type%type;
231
232 begin
233 --
234 if g_debug then
235 l_proc := g_package||'createForumNotification';
236 hr_utility.set_location('Entering:'||l_proc, 5);
237 end if;
238
239 select Type into l_type from ota_booking_status_types where booking_status_type_id=l_booking_status_type_id;
240
241 --create frm_notif_subscriber record for enrollment_status of 'P' or 'A'.
242 if l_type = 'P' or l_type = 'A' then
243 OPEN csr_forums_for_class;
244 FETCH csr_forums_for_class into v_forum_id, v_business_group_id;
245
246 LOOP
247 Exit When csr_forums_for_class%notfound OR csr_forums_for_class%notfound is null;
248
249 ota_fns_ins.ins
250 ( p_effective_date => l_effective_date
251 ,p_business_group_id => v_business_group_id
252 ,p_forum_id => v_forum_id
253 ,p_person_id => l_person_id
254 ,p_contact_id => l_contact_id
255 ,p_object_version_number => l_dummy
256 );
257
258
259 FETCH csr_forums_for_class into v_forum_id, v_business_group_id;
260 End Loop;
261 Close csr_forums_for_class;
262 end if;
263 --
264 if g_debug then
265 hr_utility.set_location('Leaving:'||l_proc, 10);
266 end if;
267 --
268 /* if therealready exists a frm notif record, and we try to create a new one,
269 an exception will be thrown which gets caught here.. We ignore the exception and return*/
270
271 exception
272 when OTHERS then
273 NULL;
274
275 --
276 end createForumNotification;
277 --
278 -- ----------------------------------------------------------------------------
279 -- |--------------------------< get_daemon_type >---------------------------|
280 -- ----------------------------------------------------------------------------
281 --
282 -- PRIVATE
283 -- Description: Fetches the daemon type for a cancelled enrollment
284 --
285 --
286 -- Added for bug#4654530
287 FUNCTION get_daemon_type(p_booking_id IN NUMBER)
288 RETURN VARCHAR2
289 IS
290 l_hours_until_class_starts NUMBER;
291 l_auto_waitlist_days NUMBER;
292 l_daemon_type VARCHAR2(9) := NULL;
293
294 CURSOR csr_get_class_details IS
295 SELECT evt.course_start_time
296 ,evt.course_start_date
297 ,evt.event_id
298 FROM ota_events evt,
299 ota_delegate_bookings tdb
300 WHERE tdb.event_id = evt.event_id
301 AND tdb.booking_id = p_booking_id;
302
303 CURSOR csr_get_waitlist_count(p_event_id NUMBER) IS
304 SELECT 1
305 FROM ota_delegate_bookings tdb
306 ,ota_booking_status_types bst
307 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
308 AND bst.type = 'W'
309 AND tdb.event_id = p_event_id;
310
311 l_course_start_time OTA_EVENTS.course_start_time%TYPE;
312 l_course_start_date OTA_EVENTS.course_start_date%TYPE;
313 l_event_id OTA_EVENTS.event_id%TYPE;
314 l_waitlist_count NUMBER;
315
316 BEGIN
317 OPEN csr_get_class_details;
318 FETCH csr_get_class_details INTO l_course_start_time, l_course_start_date, l_event_id;
319 CLOSE csr_get_class_details;
320 -- bug# 5231470 Date format changed from DD-MON-YYYY to DD/MM/YYYY
321 l_hours_until_class_starts := 24*(to_date(to_char(l_course_start_date, 'DD/MM/YYYY')
322 ||''||l_course_start_time, 'DD/MM/YYYYHH24:MI') - SYSDATE);
323
324 l_auto_waitlist_days := TO_NUMBER(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
325 --
326 OPEN csr_get_waitlist_count(l_event_id);
327 FETCH csr_get_waitlist_count INTO l_waitlist_count;
328 IF (csr_get_waitlist_count%FOUND)
329 AND (l_hours_until_class_starts >= l_auto_waitlist_days) THEN
330 l_daemon_type := 'W';
331 ELSE
332 l_daemon_type := NULL;
333 END IF;
334 CLOSE csr_get_waitlist_count;
335 RETURN l_daemon_type;
336
337 END get_daemon_type;
338 -- ----------------------------------------------------------------------------
339 -- |--------------------------< Update Enrollment >---------------------------|
340 -- ----------------------------------------------------------------------------
341 --
342 -- PRIVATE
343 -- Description: Updates an Enrollment.
344 --
345 --
346 procedure Update_Enrollment
347 (
348 p_booking_id in number,
349 p_booking_status_type_id in number default hr_api.g_number,
350 p_delegate_person_id in number default hr_api.g_number,
351 p_contact_id in number default hr_api.g_number,
352 p_business_group_id in number default hr_api.g_number,
353 p_event_id in number default hr_api.g_number,
354 p_customer_id in number default hr_api.g_number,
355 p_authorizer_person_id in number default hr_api.g_number,
356 p_date_booking_placed in date default hr_api.g_date,
357 p_corespondent in varchar2 default hr_api.g_varchar2,
358 p_internal_booking_flag in varchar2 default hr_api.g_varchar2,
359 p_number_of_places in number default hr_api.g_number,
360 p_object_version_number in out nocopy number,
361 p_administrator in number default hr_api.g_number,
362 p_booking_priority in varchar2 default hr_api.g_varchar2,
363 p_comments in varchar2 default hr_api.g_varchar2,
364 p_contact_address_id in number default hr_api.g_number,
365 p_delegate_contact_phone in varchar2 default hr_api.g_varchar2,
366 p_delegate_contact_fax in varchar2 default hr_api.g_varchar2,
367 p_third_party_customer_id in number default hr_api.g_number,
368 p_third_party_contact_id in number default hr_api.g_number,
369 p_third_party_address_id in number default hr_api.g_number,
370 p_third_party_contact_phone in varchar2 default hr_api.g_varchar2,
371 p_third_party_contact_fax in varchar2 default hr_api.g_varchar2,
372 p_date_status_changed in date default hr_api.g_date,
373 p_status_change_comments in varchar2 default hr_api.g_varchar2,
374 p_failure_reason in varchar2 default hr_api.g_varchar2,
375 p_attendance_result in varchar2 default hr_api.g_varchar2,
376 p_language_id in number default hr_api.g_number,
377 p_source_of_booking in varchar2 default hr_api.g_varchar2,
378 p_special_booking_instructions in varchar2 default hr_api.g_varchar2,
379 p_successful_attendance_flag in varchar2 default hr_api.g_varchar2,
380 p_tdb_information_category in varchar2 default hr_api.g_varchar2,
381 p_tdb_information1 in varchar2 default hr_api.g_varchar2,
382 p_tdb_information2 in varchar2 default hr_api.g_varchar2,
383 p_tdb_information3 in varchar2 default hr_api.g_varchar2,
384 p_tdb_information4 in varchar2 default hr_api.g_varchar2,
385 p_tdb_information5 in varchar2 default hr_api.g_varchar2,
386 p_tdb_information6 in varchar2 default hr_api.g_varchar2,
387 p_tdb_information7 in varchar2 default hr_api.g_varchar2,
388 p_tdb_information8 in varchar2 default hr_api.g_varchar2,
389 p_tdb_information9 in varchar2 default hr_api.g_varchar2,
390 p_tdb_information10 in varchar2 default hr_api.g_varchar2,
391 p_tdb_information11 in varchar2 default hr_api.g_varchar2,
392 p_tdb_information12 in varchar2 default hr_api.g_varchar2,
393 p_tdb_information13 in varchar2 default hr_api.g_varchar2,
394 p_tdb_information14 in varchar2 default hr_api.g_varchar2,
395 p_tdb_information15 in varchar2 default hr_api.g_varchar2,
396 p_tdb_information16 in varchar2 default hr_api.g_varchar2,
397 p_tdb_information17 in varchar2 default hr_api.g_varchar2,
398 p_tdb_information18 in varchar2 default hr_api.g_varchar2,
399 p_tdb_information19 in varchar2 default hr_api.g_varchar2,
400 p_tdb_information20 in varchar2 default hr_api.g_varchar2,
401 p_update_finance_line in varchar2 default 'N',
402 p_tfl_object_version_number in out nocopy number,
403 p_finance_header_id in number default hr_api.g_number,
404 p_finance_line_id in out nocopy number,
405 p_standard_amount in number default hr_api.g_number,
406 p_unitary_amount in number default hr_api.g_number,
407 p_money_amount in number default hr_api.g_number,
408 p_currency_code in varchar2 default hr_api.g_varchar2,
409 p_booking_deal_type in varchar2 default hr_api.g_varchar2,
410 p_booking_deal_id in number default hr_api.g_number,
411 p_enrollment_type in varchar2 default hr_api.g_varchar2,
412 p_validate in boolean default false,
413 p_organization_id in number default hr_api.g_number,
414 p_sponsor_person_id in number default hr_api.g_number,
415 p_sponsor_assignment_id in number default hr_api.g_number,
416 p_person_address_id in number default hr_api.g_number,
417 p_delegate_assignment_id in number default hr_api.g_number,
418 p_delegate_contact_id in number default hr_api.g_number,
419 p_delegate_contact_email in varchar2 default hr_api.g_varchar2,
420 p_third_party_email in varchar2 default hr_api.g_varchar2,
421 p_person_address_type in varchar2 default hr_api.g_varchar2,
422 p_line_id in number default hr_api.g_number,
423 p_org_id in number default hr_api.g_number,
424 p_daemon_flag in varchar2 default hr_api.g_varchar2,
425 p_daemon_type in varchar2 default hr_api.g_varchar2,
426 p_old_event_id in number default hr_api.g_number,
427 p_quote_line_id in number default hr_api.g_number,
428 p_interface_source in varchar2 default hr_api.g_varchar2,
429 p_total_training_time in varchar2 default hr_api.g_varchar2,
430 p_content_player_status in varchar2 default hr_api.g_varchar2,
431 p_score in number default hr_api.g_number,
432 p_completed_content in number default hr_api.g_number,
433 p_total_content in number default hr_api.g_number,
434 p_booking_justification_id in number default hr_api.g_number,
435 p_source_cancel in varchar2,
436 p_override_prerequisites in varchar2 default 'N',
437 p_is_history_flag in varchar2 default hr_api.g_varchar2
438 ,p_override_learner_access in varchar2 default 'N',
439 p_sign_eval_status in varchar2 default null)
440 is
441
442 l_proc varchar2(72) := g_package || ' ' || 'create_enrollment';
443 /*
444 --
445 l_status_type_id_changed boolean;
446 --Added for Bug#4106893
447 l_event_id_changed boolean := false;
448 l_person_id_changed boolean := false;
449 l_contact_id_changed boolean := false;
450
451 l_cancel_finance_line boolean;
452 l_event_rec ota_evt_shd.g_rec_type;
453 l_event_exists boolean;
454 l_effective_date date;
455 -- Bug 2982183
456 l_person_id number;
457 -- Bug 2982183
458 --Bug 2359495
459 l_status_change_comments ota_booking_status_histories.comments%TYPE;
460 --Bug 2359495
461
462 l_lp_enrollment_ids varchar2(4000);
463 l_cert_prd_enrollment_ids varchar2(4000);
464 l_item_key wf_items.item_key%type;
465
466 l_type ota_booking_status_types.type%type;
467 --
468 l_proc varchar2(72) ;
469 --l_daemon_type OTA_DELEGATE_BOOKINGS.daemon_type%TYPE := p_daemon_type;
470 --l_daemon_flag OTA_DELEGATE_BOOKINGS.daemon_flag%TYPE := p_daemon_flag;
471 l_daemon_type VARCHAR2(30) := p_daemon_type;
472 l_daemon_flag VARCHAR2(30) := p_daemon_flag;
473
474 Cursor is_contact
475 is
476 Select contact_id,delegate_contact_id from
477 Ota_delegate_bookings
478 where booking_id= p_booking_id;
479
480 l_delegate_contact_id number(15);
481 l_contact_id number(15);
482 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
483 hr_dflex_utility.l_ignore_dfcode_varray();
484 l_ignore_dff_validation varchar2(1);
485
486 Cursor chk_for_comp_upd
487 is
488 select ocu.online_flag , off.Learning_object_id from ota_category_usages ocu,
489 ota_offerings off , ota_events oev
490 where ocu.category_usage_id = off.delivery_mode_id
491 and off.offering_id = oev.parent_offering_id
492 and oev.event_id = p_event_id;
493
494 l_comp_upd varchar2(1000) :='MoveToHistoryImage';
495 l_on_flag varchar2(100);
496 l_LO_id ota_offerings.Learning_object_id%type;
497
498 cursor get_status_info is
499 select bst.Type
500 from ota_booking_status_types bst, ota_delegate_bookings tdb
501 where bst.booking_status_type_id= tdb.booking_status_type_id
502 and tdb.booking_id = p_booking_id;
503
504 l_enroll_type varchar2(30);
505 l_incoming_status_type varchar2(30);
506
507 l_customer_id_changed boolean;
508 l_organization_id_changed boolean;
509 l_delegate_person_id_changed boolean;
510 l_delegate_asg_changed boolean;
511
512 l_new_event_id ota_delegate_bookings.event_id%TYPE := p_event_id;
513 l_new_customer_id ota_delegate_bookings.customer_id%TYPE := p_customer_id;
514 l_new_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE := p_delegate_contact_id;
515 l_new_organization_id ota_delegate_bookings.organization_id%TYPE := p_organization_id;
516 l_new_del_asg_id ota_delegate_bookings.delegate_assignment_id%TYPE := p_delegate_assignment_id;
517 l_new_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE := p_delegate_person_id;
518
519 CURSOR csr_get_enr_details IS
520 SELECT event_id, customer_id, organization_id,
521 delegate_person_id, delegate_assignment_id,
522 delegate_contact_id
523 FROM ota_delegate_bookings
524 WHERE booking_id = p_booking_id;
525
526
527 l_enr_details_rec csr_get_enr_details%ROWTYPE;
528
529 l_old_booking_status varchar2(30);
530 l_evt_status_chg_comments varchar2(1000) := fnd_message.get_string('OTA','OTA_13523_TDB_STATUS_COMMENTS');
531 */
532 --
533 begin
534
535 hr_utility.set_location('Entering:'||l_proc, 5);
536
537 ota_delegate_booking_api.update_delegate_booking(
538 p_effective_date => trunc(sysdate)
539 , p_booking_id => p_booking_id
540 , p_booking_status_type_id => p_booking_status_type_id
541 , p_delegate_person_id => p_delegate_person_id
542 , p_contact_id => p_contact_id
543 , p_business_group_id => p_business_group_id
544 , p_event_id => p_event_id
545 , p_customer_id => p_customer_id
546 , p_authorizer_person_id => p_authorizer_person_id
547 , p_date_booking_placed => p_date_booking_placed
548 , p_corespondent => p_corespondent
549 , p_internal_booking_flag => p_internal_booking_flag
550 , p_number_of_places => p_number_of_places
551 , p_object_version_number => p_object_version_number
552 , p_administrator => p_administrator
553 , p_booking_priority => p_booking_priority
554 , p_comments => p_comments
555 , p_contact_address_id => p_contact_address_id
556 , p_delegate_contact_phone => p_delegate_contact_phone
557 , p_delegate_contact_fax => p_delegate_contact_fax
558 , p_third_party_customer_id => p_third_party_customer_id
559 , p_third_party_contact_id => p_third_party_contact_id
560 , p_third_party_address_id => p_third_party_address_id
561 , p_third_party_contact_phone => p_third_party_contact_phone
562 , p_third_party_contact_fax => p_third_party_contact_fax
563 , p_date_status_changed => p_date_status_changed
564 , p_status_change_comments => p_status_change_comments
565 , p_failure_reason => p_failure_reason
566 , p_attendance_result => p_attendance_result
567 , p_language_id => p_language_id
568 , p_source_of_booking => p_source_of_booking
569 , p_special_booking_instructions => p_special_booking_instructions
570 , p_successful_attendance_flag => p_successful_attendance_flag
571 , p_tdb_information_category => p_tdb_information_category
572 , p_tdb_information1 => p_tdb_information1
573 , p_tdb_information2 => p_tdb_information2
574 , p_tdb_information3 => p_tdb_information3
575 , p_tdb_information4 => p_tdb_information4
576 , p_tdb_information5 => p_tdb_information5
577 , p_tdb_information6 => p_tdb_information6
578 , p_tdb_information7 => p_tdb_information7
579 , p_tdb_information8 => p_tdb_information8
580 , p_tdb_information9 => p_tdb_information9
581 , p_tdb_information10 => p_tdb_information10
582 , p_tdb_information11 => p_tdb_information11
583 , p_tdb_information12 => p_tdb_information12
584 , p_tdb_information13 => p_tdb_information13
585 , p_tdb_information14 => p_tdb_information14
586 , p_tdb_information15 => p_tdb_information15
587 , p_tdb_information16 => p_tdb_information16
588 , p_tdb_information17 => p_tdb_information17
589 , p_tdb_information18 => p_tdb_information18
590 , p_tdb_information19 => p_tdb_information19
591 , p_tdb_information20 => p_tdb_information20
592 , p_update_finance_line => p_update_finance_line
593 , p_tfl_object_version_number => p_tfl_object_version_number
594 , p_finance_header_id => p_finance_header_id
595 , p_finance_line_id => p_finance_line_id
596 , p_standard_amount => p_standard_amount
597 , p_unitary_amount => p_unitary_amount
598 , p_money_amount => p_money_amount
599 , p_currency_code => p_currency_code
600 , p_booking_deal_type => p_booking_deal_type
601 , p_booking_deal_id => p_booking_deal_id
602 , p_enrollment_type => p_enrollment_type
603 , p_validate => p_validate
604 , p_organization_id => p_organization_id
605 , p_sponsor_person_id => p_sponsor_person_id
606 , p_sponsor_assignment_id => p_sponsor_assignment_id
607 , p_person_address_id => p_person_address_id
608 , p_delegate_assignment_id => p_delegate_assignment_id
609 , p_delegate_contact_id => p_delegate_contact_id
610 , p_delegate_contact_email => p_delegate_contact_email
611 , p_third_party_email => p_third_party_email
612 , p_person_address_type => p_person_address_type
613 , p_line_id => p_line_id
614 , p_org_id => p_org_id
615 , p_daemon_flag => p_daemon_flag
616 , p_daemon_type => p_daemon_type
617 , p_old_event_id => p_old_event_id
618 , p_quote_line_id => p_quote_line_id
619 , p_interface_source => p_interface_source
620 , p_total_training_time => p_total_training_time
621 , p_content_player_status => p_content_player_status
622 , p_score => p_score
623 , p_completed_content => p_completed_content
624 , p_total_content => p_total_content
625 , p_booking_justification_id => p_booking_justification_id
626 , p_source_cancel => p_source_cancel
627 , p_override_prerequisites => p_override_prerequisites
628 , p_is_history_flag => p_is_history_flag
629 , p_override_learner_access => p_override_learner_access
630 , p_sign_eval_status => p_sign_eval_status
631 );
632
633 hr_utility.set_location('Leaving:'||l_proc, 5);
634
635 /*
636 --
637 g_debug := hr_utility.debug_enabled;
638
639 if g_debug then
640 l_proc := g_package||'update_enrollment';
641 hr_utility.set_location('Entering:'||l_proc, 5);
642 end if;
643 --
644 -- Issue a savepoint if operating in validation only mode.
645 --
646 if p_validate then
647 --
648 savepoint update_enrollment;
649 --
650 end if;
651 -- Truncate the time portion from all IN date parameters
652 --
653 -- l_effective_date := trunc(p_effective_date);
654 --
655
656 --
657 -- Validation in addition to Table Handlers
658 --
659 --
660 --
661 -- Lock Enrollment (Bug 2468167)
662 --
663 ota_tdb_shd.lck(p_booking_id,p_object_version_number);
664 --
665 -- Prerequisite Validation Code
666 -- Can be overridden if p_override_prerequisites parameter is 'Y'
667 -- get booking status type
668 ota_utility.get_booking_status_type(p_status_type_id=>p_booking_status_type_id,
669 p_type => l_incoming_status_type);
670
671 If ( p_override_prerequisites = 'N' ) Then
672 --Call local method
673 chk_mandatory_prereqs(p_delegate_person_id, p_delegate_contact_id, p_customer_id, p_event_id, p_booking_status_type_id);
674 End If;
675
676 IF p_override_learner_access <> 'Y' THEN
677 OPEN csr_get_enr_details;
678 FETCH csr_get_enr_details INTO l_enr_details_rec;
679 CLOSE csr_get_enr_details;
680
681 -- Modified for bug#4681165
682 IF l_new_event_id = hr_api.g_number THEN l_new_event_id := l_enr_details_rec.event_id; END IF;
683 IF l_new_customer_id = hr_api.g_number THEN l_new_customer_id := l_enr_details_rec.customer_id; END IF;
684 IF l_new_delegate_contact_id = hr_api.g_number THEN l_new_delegate_contact_id := l_enr_details_rec.delegate_contact_id; END IF;
685 IF l_new_organization_id = hr_api.g_number THEN l_new_organization_id := l_enr_details_rec.organization_id; END IF;
686 IF l_new_del_asg_id = hr_api.g_number THEN l_new_del_asg_id := l_enr_details_rec.delegate_assignment_id; END IF;
687 IF l_new_delegate_person_id = hr_api.g_number THEN l_new_delegate_person_id := l_enr_details_rec.delegate_person_id; END IF;
688
689
690 l_event_id_changed := ota_general.value_changed( l_enr_details_rec.event_id, l_new_event_id);
691 l_customer_id_changed := ota_general.value_changed( l_enr_details_rec.customer_id, l_new_customer_id);
692 l_organization_id_changed := ota_general.value_changed( l_enr_details_rec.organization_id, l_new_organization_id);
693 l_delegate_person_id_changed := ota_general.value_changed( l_enr_details_rec.delegate_person_id, l_new_delegate_person_id);
694 l_delegate_asg_changed := ota_general.value_changed( l_enr_details_rec.delegate_assignment_id, l_new_del_asg_id);
695
696 if l_event_id_changed or
697 l_customer_id_changed or
698 l_organization_id_changed or
699 l_delegate_person_id_changed or
700 l_delegate_asg_changed then
701 --
702 -- check that the delegate is eligible to be booked on to the event
703 --
704 ota_tdb_bus.check_delegate_eligible(
705 p_event_id => l_new_event_id
706 ,p_customer_id => l_new_customer_id
707 ,p_delegate_contact_id => l_new_delegate_contact_id
708 ,p_organization_id => l_new_organization_id
709 ,p_delegate_person_id => l_new_delegate_person_id
710 ,p_delegate_assignment_id => l_new_del_asg_id);
711 END IF;
712 END IF;
713
714 -- Added for bug#4654530
715 IF nvl(p_status_change_comments,hr_api.g_varchar2) <> l_evt_status_chg_comments THEN
716
717 OPEN get_status_info;
718 FETCH get_status_info INTO l_old_booking_status;
719 CLOSE get_status_info;
720
721 IF l_incoming_status_type = 'C'
722 AND l_old_booking_status <> 'C'
723 AND l_daemon_type IS NULL THEN
724 l_daemon_type := get_daemon_type(p_booking_id);
725 IF l_daemon_type IS NOT NULL THEN
726 l_daemon_flag := 'Y';
727 ELSE
728 l_daemon_flag := 'N';
729 END IF;
730 END IF;
731
732 IF l_incoming_status_type <> 'C'
733 AND l_old_booking_status = 'C' THEN
734 l_daemon_flag := 'N';
735 l_daemon_type := NULL;
736 END IF;
737
738 END IF;
739
740 -- Lock the Event
741 --
742 ota_evt_bus2.lock_event(p_event_id);
743
744 -- get booking_status type to fire ntf process
745 open get_status_info;
746 fetch get_status_info into l_enroll_type;
747 close get_status_info;
748 --
749 -- Get Event record
750 --
751 ota_evt_shd.get_event_details (p_event_id,
752 l_event_rec,
753 l_event_exists);
754
755 -- Ignore Enrollment Dff Validation for some cases
756 if ( (l_event_rec.price_basis = 'C' and p_contact_id is not null) or (l_event_rec.line_id is not null) or (p_line_id is not null) ) then
757 l_add_struct_d.extend(1);
758 l_add_struct_d(l_add_struct_d.count) := 'OTA_DELEGATE_BOOKINGS';
759 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
760 l_ignore_dff_validation := 'Y';
761 else
762 l_ignore_dff_validation := 'N';
763 end if;
764
765 --
766 -- Validation Check on booking / event statuses
767 --
768 Check_Status_Change(p_event_id
769 ,p_booking_Status_type_id
770 ,l_event_rec.event_status
771 ,p_number_of_places
772 ,l_event_rec.maximum_attendees);
773
774 --
775 --Bug 2359495
776 IF p_status_change_comments IS NULL THEN
777 l_status_change_comments := hr_general_utilities.get_lookup_meaning
778 ('ENROLMENT_STATUS_REASON',
779 'A');
780 ELSE
781 l_status_change_comments := p_status_change_comments;
782 END IF;
783 --Bug 2359495
784 -- Force Update
785 --
786 ota_tdb_upd.upd
787 (
788 p_booking_id,
789 p_booking_status_type_id,
790 p_delegate_person_id,
791 p_contact_id,
792 p_business_group_id,
793 p_event_id,
794 p_customer_id,
795 p_authorizer_person_id,
796 p_date_booking_placed,
797 p_corespondent,
798 p_internal_booking_flag,
799 p_number_of_places,
800 p_object_version_number,
801 p_administrator,
802 p_booking_priority,
803 p_comments,
804 p_contact_address_id,
805 p_delegate_contact_phone,
806 p_delegate_contact_fax,
807 p_third_party_customer_id,
808 p_third_party_contact_id,
809 p_third_party_address_id,
810 p_third_party_contact_phone,
811 p_third_party_contact_fax,
812 p_date_status_changed,
813 l_status_change_comments, -- p_status_change_comments, Bug 2359495
814 p_failure_reason,
815 p_attendance_result,
816 p_language_id,
817 p_source_of_booking,
818 p_special_booking_instructions,
819 p_successful_attendance_flag,
820 p_tdb_information_category,
821 p_tdb_information1,
822 p_tdb_information2,
823 p_tdb_information3,
824 p_tdb_information4,
825 p_tdb_information5,
826 p_tdb_information6,
827 p_tdb_information7,
828 p_tdb_information8,
829 p_tdb_information9,
830 p_tdb_information10,
831 p_tdb_information11,
832 p_tdb_information12,
833 p_tdb_information13,
834 p_tdb_information14,
835 p_tdb_information15,
836 p_tdb_information16,
837 p_tdb_information17,
838 p_tdb_information18,
839 p_tdb_information19,
840 p_tdb_information20,
841 p_update_finance_line,
842 p_tfl_object_version_number,
843 p_finance_header_id,
844 p_finance_line_id,
845 p_standard_amount,
846 p_unitary_amount,
847 p_money_amount,
848 p_currency_code,
849 p_booking_deal_type,
850 p_booking_deal_id,
851 p_enrollment_type,
852 p_validate,
853 p_organization_id,
854 p_sponsor_person_id,
855 p_sponsor_assignment_id,
856 p_person_address_id,
857 p_delegate_assignment_id,
858 p_delegate_contact_id,
859 p_delegate_contact_email,
860 p_third_party_email,
861 p_person_address_type,
862 p_line_id,
863 p_org_id,
864 -- Modified for bug#4654530
865 l_daemon_flag,
866 l_daemon_type,
867 -- p_daemon_flag,
868 -- p_daemon_type,
869 p_old_event_id,
870 p_quote_line_id,
871 p_interface_source,
872 p_total_training_time,
873 p_content_player_status,
874 p_score,
875 p_completed_content,
876 p_total_content,
877 p_booking_justification_id,
878 p_is_history_flag
879 );
880 --
881 l_status_type_id_changed :=
882 ota_general.value_changed (ota_tdb_shd.g_old_rec.booking_status_type_id,
883 p_booking_status_type_id);
884
885 --Getting the old booking status to manipulate the fourm notification records
886 l_old_booking_status := ota_tdb_bus.booking_status_type(
887 ota_tdb_shd.g_old_rec.booking_status_type_id);
888
889 OPEN is_contact;
890 FETCH is_contact INTO l_contact_id,l_delegate_contact_id;
891 CLOSE is_contact;
892
893 If (p_delegate_person_id = hr_api.g_number) then
894 select delegate_person_id into l_person_id from ota_delegate_bookings
895 where booking_id = p_booking_id;
896 else l_person_id := p_delegate_person_id;
897 End If;
898 --
899 --Added by dbatra
900 -- this is to take care of granting competencies attached to LP which are completed
901 -- but course under it was not successfully attended intitially.
902 if (not l_status_type_id_changed) and p_successful_attendance_flag ='Y' then
903 if l_delegate_contact_id is null and l_contact_id is null then
904
905 ota_lrng_path_util.start_comp_proc_success_attnd(p_person_id =>l_person_id
906 ,p_event_id => p_event_id);
907
908 ota_cpe_util.crt_comp_upd_succ_att(p_person_id =>l_person_id
909 ,p_event_id => p_event_id);
910
911 end if;
912 end if;
913 --
914 if l_status_type_id_changed or p_successful_attendance_flag ='Y' then
915 --
916 -- Added by dbatra for training plan
917 -- Bug 2982183
918 if l_delegate_contact_id is null and l_contact_id is null and l_status_type_id_changed then
919
920 /* bug 3795299
921 ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
922 p_person_id => l_person_id,
923 -- Added for Bug#3479186
924 p_contact_id => NULL,
925 p_learning_path_ids => l_learning_path_ids);
926 ELSE-- Added for Bug#3479186
927 ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
928 p_person_id => NULL,
929 p_contact_id => l_delegate_contact_id,
930 p_learning_path_ids => l_learning_path_ids);
931 */
932 /*
933 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
934 p_person_id => l_person_id,
935 p_contact_id => null,
936 p_lp_enrollment_ids => l_lp_enrollment_ids);
937 -- update any associated cert member enrollment statuses
938 ota_cme_util.update_cme_status(p_event_id => p_event_id,
939 p_person_id => l_person_id,
940 p_contact_id => null,
941 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
942
943 ELSif l_delegate_contact_id is not null then
944 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
945 p_person_id => null,
946 p_contact_id => l_delegate_contact_id,
947 p_lp_enrollment_ids => l_lp_enrollment_ids);
948 -- update any associated cert member enrollment statuses
949 ota_cme_util.update_cme_status(p_event_id => p_event_id,
950 p_person_id => null,
951 p_contact_id => l_delegate_contact_id,
952 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
953
954 end if; -- contact_id
955
956 select Type into l_type from ota_booking_status_types where booking_status_type_id=p_booking_status_type_id;
957
958 if l_type='A' and l_delegate_contact_id is null and l_contact_id is null then
959
960 -- check whether class is online or not
961 */
962 /* OPEN chk_for_comp_upd;
963 FETCH chk_for_comp_upd INTO l_on_flag,l_LO_id;
964 CLOSE chk_for_comp_upd;
965
966 if l_on_flag='Y' then
967 -- check whether online class is succesfully completed or not
968 l_comp_upd := ota_lo_utility.get_history_button(p_user_id => l_person_id,
969 p_lo_id => l_LO_id ,
970 p_event_id => p_event_id,
971 p_booking_id => p_booking_id);
972 elsif p_successful_attendance_flag <>'Y' then
973 l_comp_upd := null;
974 end if; -- flag */
975 /*
976 if p_successful_attendance_flag = 'Y' then
977 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
978 p_itemtype =>'HRSSA',
979 p_person_id => l_person_id,
980 p_eventid =>p_event_id,
981 p_learningpath_ids => null,
982 p_itemkey =>l_item_key);
983
984 end if;
985
986
987 end if;
988
989 --- send ntf to waitlisted learner
990
991 if l_enroll_type = 'W' and l_type = 'P'
992 and l_delegate_contact_id is null and l_contact_id is null then
993
994 OTA_INITIALIZATION_WF.initialize_wf(p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC',
995 p_item_type => 'OTWF',
996 p_eventid => p_event_id,
997 p_person_id => l_person_id,
998 p_event_fired => 'ENROLL_STATUS_CHNG');
999
1000 end if;
1001
1002 -- send cancel enrollment ntf
1003
1004 if l_type ='C' and l_delegate_contact_id is null and l_contact_id is null
1005 and nvl(p_source_cancel,'-1') <> 'AME' then
1006
1007 OTA_LRNR_ENROLL_UNENROLL_WF.learner_unenrollment
1008 (p_process => 'OTA_LNR_TRNG_CANCEL_JSP_PRC',
1009 p_itemtype => 'HRSSA',
1010 p_person_id => l_person_id,
1011 p_eventid => p_event_id);
1012
1013 end if;
1014
1015 -- Bug 2982183
1016 --
1017 ota_tdb_bus.maintain_status_history
1018 (p_booking_status_type_id,
1019 p_date_status_changed,
1020 p_administrator,
1021 l_status_change_comments, --p_status_change_comments,Bug 2359495
1022 p_booking_id,
1023 ota_tdb_shd.g_old_rec.date_status_changed,
1024 ota_tdb_shd.g_old_rec.booking_status_type_id,
1025 ota_tdb_shd.g_created_by,
1026 p_date_booking_placed);
1027 --
1028 ota_tdb_bus.ota_letter_lines
1029 (p_booking_id => p_booking_id,
1030 p_booking_status_type_id => p_booking_status_type_id,
1031 p_event_id => p_event_id,
1032 p_delegate_person_id => l_person_id);
1033 -- Modified for bug#3007934.
1034 -- p_delegate_person_id => p_delegate_person_id);
1035 ---***Added p_delegate_person_id. Bug2791524.
1036 --
1037 end if;
1038 --
1039 --Added for Bug#4106893
1040 IF p_event_id <> hr_api.g_number
1041 AND p_event_id <> ota_tdb_shd.g_old_rec.event_id THEN
1042 l_event_id_changed:= true;
1043 END IF;
1044
1045 IF p_delegate_person_id <> hr_api.g_number
1046 AND p_delegate_person_id <> ota_tdb_shd.g_old_rec.delegate_person_id THEN
1047 l_person_id_changed := true;
1048 END IF;
1049
1050 IF p_delegate_contact_id <> hr_api.g_number
1051 AND p_delegate_contact_id <> ota_tdb_shd.g_old_rec.delegate_contact_id THEN
1052 l_contact_id_changed := true;
1053 END IF;
1054
1055
1056 --If the new enrollment status is 'C' or 'R' or 'W' then delete the forum notitifcation record
1057 if l_type = 'C' or l_type = 'R' or l_type = 'W' then
1058 deleteForumNotification(p_event_id,l_person_id, l_delegate_contact_id);
1059 end if;
1060
1061 --If the booking status is changed from 'C','W' or 'R' to 'P' or 'A',
1062 --then we need to create a new forum notification record.
1063 if l_old_booking_status = 'C' or l_old_booking_status = 'W' or l_old_booking_status ='R'
1064 and l_type = 'P' or l_type = 'A' then
1065 if NOT l_event_id_changed and NOT l_person_id_changed AND NOT l_contact_id_changed THEN
1066 IF l_person_id IS NOT NULL THEN
1067 createForumNotification(p_event_id,l_person_id, null, l_effective_date, p_booking_status_type_id);
1068 ELSIF l_delegate_contact_id IS NOT NULL THEN
1069 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1070 end if;
1071 end if;
1072 end if;
1073 */
1074
1075 /**
1076 When the class name is changed for an enrollment, the lme update must be called
1077 twice, once for the old class and once for the new class.
1078 When the learner name is changed for an enrollment, the lme update must be called
1079 twice, once for the old learner and once for the new learner.
1080 If both the learner aswell as class associated with an enrollment are changed,
1081 update lme must be called once for old class, old learner and once for new class and new learner
1082 **/
1083 /*
1084 IF l_event_id_changed AND NOT l_person_id_changed AND NOT l_contact_id_changed THEN
1085 IF l_person_id IS NOT NULL THEN
1086 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1087 p_person_id => l_person_id,
1088 p_contact_id => null,
1089 p_lp_enrollment_ids => l_lp_enrollment_ids);
1090 -- update any associated cert member enrollment statuses
1091 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1092 p_person_id => l_person_id,
1093 p_contact_id => null,
1094 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1095
1096 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id =>p_event_id,
1097 p_person_id => l_person_id,
1098 p_contact_id => null,
1099 p_lp_enrollment_ids => l_lp_enrollment_ids);
1100 -- update any associated cert member enrollment statuses
1101 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1102 p_person_id => l_person_id,
1103 p_contact_id => null,
1104 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1105 --If the event has changed, the forum notification record should be deleted and created for the new event
1106 -- FRM Notification should be created only for Placed or Attended status. Not for 'C','W' or 'R'.
1107 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null);
1108 createForumNotification(p_event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
1109 ELSIF l_delegate_contact_id IS NOT NULL THEN
1110 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1111 p_person_id => null,
1112 p_contact_id => l_delegate_contact_id,
1113 p_lp_enrollment_ids => l_lp_enrollment_ids);
1114
1115 -- update any associated cert member enrollment statuses
1116 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1117 p_person_id => null,
1118 p_contact_id => l_delegate_contact_id,
1119 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1120
1121 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
1122 p_person_id => null,
1123 p_contact_id => l_delegate_contact_id,
1124 p_lp_enrollment_ids => l_lp_enrollment_ids);
1125 -- update any associated cert member enrollment statuses
1126 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1127 p_person_id => null,
1128 p_contact_id => l_delegate_contact_id,
1129 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1130 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, l_delegate_contact_id);
1131 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1132 END IF;
1133 ELSIF l_event_id_changed THEN
1134 IF l_person_id_changed THEN
1135 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1136 p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
1137 p_contact_id => null,
1138 p_lp_enrollment_ids => l_lp_enrollment_ids);
1139
1140 -- update any associated cert member enrollment statuses
1141 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1142 p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
1143 p_contact_id => null,
1144 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1145
1146 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
1147 p_person_id => l_person_id,
1148 p_contact_id => null,
1149 p_lp_enrollment_ids => l_lp_enrollment_ids);
1150 -- update any associated cert member enrollment statuses
1151 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1152 p_person_id => l_person_id,
1153 p_contact_id => null,
1154 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1155 -- the class and the learner have change.So delete the forum record for old class and person
1156 --and create a notification record for the new class and new person
1157 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
1158 createForumNotification(p_event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
1159 ELSIF l_contact_id_changed THEN
1160 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1161 p_person_id => null,
1162 p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
1163 p_lp_enrollment_ids => l_lp_enrollment_ids);
1164 -- update any associated cert member enrollment statuses
1165 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1166 p_person_id => null,
1167 p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
1168 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1169
1170 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
1171 p_person_id => null,
1172 p_contact_id => p_delegate_contact_id,
1173 p_lp_enrollment_ids => l_lp_enrollment_ids);
1174 -- update any associated cert member enrollment statuses
1175 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1176 p_person_id => null,
1177 p_contact_id => p_delegate_contact_id,
1178 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1179 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
1180 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1181 END IF;
1182 ELSIF l_person_id_changed THEN
1183 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1184 p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
1185 p_contact_id => null,
1186 p_lp_enrollment_ids => l_lp_enrollment_ids);
1187
1188 -- update any associated cert member enrollment statuses
1189 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1190 p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
1191 p_contact_id => null,
1192 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1193
1194 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1195 p_person_id => l_person_id,
1196 p_contact_id => null,
1197 p_lp_enrollment_ids => l_lp_enrollment_ids);
1198 -- update any associated cert member enrollment statuses
1199 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1200 p_person_id => l_person_id,
1201 p_contact_id => null,
1202 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1203 -- the learner has changed.So delete the forum record for old person
1204 --and create a notification record for the new person
1205 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
1206 createForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
1207
1208 ELSIF l_contact_id_changed THEN
1209 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1210 p_person_id => null,
1211 p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
1212 p_lp_enrollment_ids => l_lp_enrollment_ids);
1213 -- update any associated cert member enrollment statuses
1214 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1215 p_person_id => null,
1216 p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
1217 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1218
1219 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1220 p_person_id => null,
1221 p_contact_id => p_delegate_contact_id,
1222 p_lp_enrollment_ids => l_lp_enrollment_ids);
1223 -- update any associated cert member enrollment statuses
1224 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1225 p_person_id => null,
1226 p_contact_id => p_delegate_contact_id,
1227 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1228 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
1229 createForumNotification(ota_tdb_shd.g_old_rec.event_id, null, p_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1230
1231 END IF;
1232
1233 if p_update_finance_line in ('C','Y') then
1234 --
1235 l_cancel_finance_line := (p_update_finance_line = 'C');
1236 ota_finance.maintain_finance_line
1237 (p_finance_header_id => p_finance_header_id,
1238 p_booking_id => p_booking_id ,
1239 p_currency_code => p_currency_code ,
1240 p_standard_amount => p_standard_amount,
1241 p_unitary_amount => p_unitary_amount ,
1242 p_money_amount => p_money_amount ,
1243 p_booking_deal_id => p_booking_deal_id ,
1244 p_booking_deal_type => p_booking_deal_type,
1245 p_object_version_number => p_tfl_object_version_number,
1246 p_finance_line_id => p_finance_line_id,
1247 p_cancel_finance_line => l_cancel_finance_line);
1248 --
1249 end if;
1250
1251 --
1252 -- Reset Event Status
1253 --
1254 ota_evt_bus2.reset_event_status(p_event_id
1255 ,l_event_rec.object_version_number
1256 ,l_event_rec.event_status
1257 ,l_event_rec.maximum_attendees);
1258 --
1259 -- *** In case of Event Change -- Reset Event Status for Old Event ***
1260 --
1261 if p_event_id <> ota_tdb_shd.g_old_rec.event_id then
1262 ota_evt_bus2.lock_event(ota_tdb_shd.g_old_rec.event_id);
1263
1264 ota_evt_shd.get_event_details (ota_tdb_shd.g_old_rec.event_id,
1265 l_event_rec,
1266 l_event_exists);
1267
1268 ota_evt_bus2.reset_event_status(ota_tdb_shd.g_old_rec.event_id
1269 ,l_event_rec.object_version_number
1270 ,l_event_rec.event_status
1271 ,l_event_rec.maximum_attendees);
1272
1273 ota_evt_shd.get_event_details (p_event_id,
1274 l_event_rec,
1275 l_event_exists);
1276 end if;
1277
1278 if ( l_ignore_dff_validation = 'Y') then
1279 hr_dflex_utility.remove_ignore_df_validation;
1280 end if;
1281
1282 --
1283 -- When in validation only mode raise the Validate_Enabled exception
1284 --
1285 if p_validate then
1286 --
1287 raise hr_api.validate_enabled;
1288 --
1289 end if;
1290
1291 --
1292 if g_debug then
1293 hr_utility.set_location('Leaving:'||l_proc, 10);
1294 end if;
1295 --
1296 exception
1297 when hr_api.validate_enabled then
1298 --
1299 -- As the Validate_Enabled exception has been raised
1300 -- we must rollback to the savepoint
1301 --
1302 ROLLBACK TO update_enrollment;
1303 --
1304 -- Only set output warning arguments
1305 -- (Any key or derived arguments must be set to null
1306 -- when validation only mode is being used.)
1307 --
1308 */
1309 end Update_Enrollment;
1310 --
1311 -- ----------------------------------------------------------------------------
1312 -- |--------------------------< Update Waitlisted >-------------------------|
1313 -- ----------------------------------------------------------------------------
1314 --
1315 -- PUBLIC
1316 -- Description: Updates Waitlisted enrollments from the Waitlist window.
1317 --
1318 --
1319 Procedure Update_Waitlisted (p_booking_id in number
1320 ,p_object_version_number in out nocopy number
1321 ,p_event_id in number
1322 ,p_booking_status_type_id in number
1323 ,p_date_status_changed in date
1324 ,p_status_change_comments in varchar2
1325 ,p_number_of_places in number
1326 ,p_finance_line_id in out nocopy number
1327 ,p_tfl_object_version_number in out nocopy number
1328 ,p_administrator in number
1329 ,p_validate in boolean
1330 ) is
1331 --
1332 --
1333 l_status_change_comments varchar2(1000);
1334 l_proc varchar2(72);
1335 l_places number;
1336 --
1337 begin
1338 --
1339 g_debug := hr_utility.debug_enabled;
1340
1341 if g_debug then
1342 l_proc := g_package||'Update_Waitlisted';
1343 hr_utility.set_location('Entering:'|| l_proc, 5);
1344 end if;
1345 --
1346 -- Call the delegate booking update API.
1347 --
1348
1349 l_places := ota_evt_bus2.get_vacancies(p_event_id);
1350
1351 if p_number_of_places <= l_places or
1352 l_places is null then
1353 --
1354 ota_tdb_api_upd2.update_enrollment
1355 (p_booking_id => p_booking_id
1356 ,p_object_version_number => p_object_version_number
1357 ,p_event_id => p_event_id
1358 ,p_booking_status_type_id => p_booking_status_type_id
1359 ,p_date_status_changed => p_date_status_changed
1360 ,p_status_change_comments => p_status_change_comments
1361 ,p_number_of_places => p_number_of_places
1362 ,p_update_finance_line => 'N'
1363 ,p_finance_line_id => p_finance_line_id
1364 ,p_tfl_object_version_number => p_tfl_object_version_number
1365 ,p_administrator => p_administrator
1366 ,p_validate => p_validate
1367 );
1368 --
1369 -- commit the changes
1370 --
1371 commit;
1372 --
1373 else
1374 --
1375 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
1376 fnd_message.raise_error;
1377 --
1378 end if;
1379 --
1380 if g_debug then
1381 hr_utility.set_location('Leaving:'|| l_proc, 10);
1382 end if;
1383 --
1384 end Update_Waitlisted;
1385
1386 Procedure chk_mandatory_prereqs
1387 (p_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE,
1388 p_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE,
1389 p_customer_id ota_delegate_bookings.customer_id%TYPE,
1390 p_event_id ota_events.event_id%TYPE,
1391 p_booking_status_type_id in ota_delegate_bookings.booking_status_type_id%TYPE
1392 ) IS
1393
1394 l_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE;
1395 l_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE;
1396 l_customer_id ota_delegate_bookings.customer_id%TYPE;
1397 l_event_id ota_events.event_id%TYPE;
1398 l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%TYPE;
1399 l_check_prereq boolean;
1400 l_old_status_type varchar2(30);
1401 l_new_status_type varchar2(30);
1402
1403 Begin
1404 -- Prerequisite Validation Code
1405 l_check_prereq := false;
1406
1407 If ( p_delegate_person_id = hr_api.g_number ) Then
1408 l_delegate_person_id := ota_tdb_shd.g_old_rec.delegate_person_id;
1409 Else
1410 l_delegate_person_id := p_delegate_person_id;
1411 End If;
1412
1413 If ( p_delegate_contact_id = hr_api.g_number ) Then
1414 l_delegate_contact_id := ota_tdb_shd.g_old_rec.delegate_contact_id;
1415 Else
1416 l_delegate_contact_id := p_delegate_contact_id;
1417 End If;
1418
1419 If ( p_customer_id = hr_api.g_number ) Then
1420 l_customer_id := ota_tdb_shd.g_old_rec.customer_id;
1421 Else
1422 l_customer_id := p_customer_id;
1423 End If;
1424
1425 If ( p_event_id = hr_api.g_number ) Then
1426 l_event_id := ota_tdb_shd.g_old_rec.event_id;
1427 Else
1428 l_event_id := p_event_id;
1429 End If;
1430
1431 If ( p_booking_status_type_id = hr_api.g_number ) Then
1432 l_booking_status_type_id := ota_tdb_shd.g_old_rec.booking_status_type_id;
1433 Else
1434 l_booking_status_type_id := p_booking_status_type_id;
1435 End If;
1436
1437 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_person_id, l_delegate_person_id) ) Then
1438 l_check_prereq := true;
1439 End If;
1440
1441 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_contact_id, l_delegate_contact_id) ) Then
1442 l_check_prereq := true;
1443 End If;
1444
1445 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.customer_id, l_customer_id) ) Then
1446 l_check_prereq := true;
1447 End If;
1448
1449 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.event_id, l_event_id) ) Then
1450 l_check_prereq := true;
1451 End If;
1452
1453 ota_utility.get_booking_status_type(p_status_type_id => ota_tdb_shd.g_old_rec.booking_status_type_id,
1454 p_type => l_old_status_type);
1455
1456 ota_utility.get_booking_status_type(p_status_type_id => l_booking_status_type_id,
1457 p_type => l_new_status_type);
1458
1459 If ( l_old_status_type = 'C' and l_new_status_type <> 'C' ) Then
1460 l_check_prereq := true;
1461 End If;
1462
1463 If ( l_check_prereq and (l_delegate_person_id is not null or l_delegate_contact_id is not null) ) Then
1464 ota_cpr_utility.chk_mandatory_prereqs(l_delegate_person_id, l_delegate_contact_id, l_event_id);
1465 End If;
1466 --
1467 End chk_mandatory_prereqs;
1468
1469 --
1470 end ota_tdb_api_upd2;