[Home] [Help]
PACKAGE BODY: APPS.OTA_TDB_API_UPD2
Source
1 Package Body ota_tdb_api_upd2 as
2 /* $Header: ottdb02t.pkb 120.22 2008/01/21 07:21:52 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 is
440
441 l_proc varchar2(72) := g_package || ' ' || 'create_enrollment';
442 /*
443 --
444 l_status_type_id_changed boolean;
445 --Added for Bug#4106893
446 l_event_id_changed boolean := false;
447 l_person_id_changed boolean := false;
448 l_contact_id_changed boolean := false;
449
450 l_cancel_finance_line boolean;
451 l_event_rec ota_evt_shd.g_rec_type;
452 l_event_exists boolean;
453 l_effective_date date;
454 -- Bug 2982183
455 l_person_id number;
456 -- Bug 2982183
457 --Bug 2359495
458 l_status_change_comments ota_booking_status_histories.comments%TYPE;
459 --Bug 2359495
460
461 l_lp_enrollment_ids varchar2(4000);
462 l_cert_prd_enrollment_ids varchar2(4000);
463 l_item_key wf_items.item_key%type;
464
465 l_type ota_booking_status_types.type%type;
466 --
467 l_proc varchar2(72) ;
468 --l_daemon_type OTA_DELEGATE_BOOKINGS.daemon_type%TYPE := p_daemon_type;
469 --l_daemon_flag OTA_DELEGATE_BOOKINGS.daemon_flag%TYPE := p_daemon_flag;
470 l_daemon_type VARCHAR2(30) := p_daemon_type;
471 l_daemon_flag VARCHAR2(30) := p_daemon_flag;
472
473 Cursor is_contact
474 is
475 Select contact_id,delegate_contact_id from
476 Ota_delegate_bookings
477 where booking_id= p_booking_id;
478
479 l_delegate_contact_id number(15);
480 l_contact_id number(15);
481 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
482 hr_dflex_utility.l_ignore_dfcode_varray();
483 l_ignore_dff_validation varchar2(1);
484
485 Cursor chk_for_comp_upd
486 is
487 select ocu.online_flag , off.Learning_object_id from ota_category_usages ocu,
488 ota_offerings off , ota_events oev
489 where ocu.category_usage_id = off.delivery_mode_id
490 and off.offering_id = oev.parent_offering_id
491 and oev.event_id = p_event_id;
492
493 l_comp_upd varchar2(1000) :='MoveToHistoryImage';
494 l_on_flag varchar2(100);
495 l_LO_id ota_offerings.Learning_object_id%type;
496
497 cursor get_status_info is
498 select bst.Type
499 from ota_booking_status_types bst, ota_delegate_bookings tdb
500 where bst.booking_status_type_id= tdb.booking_status_type_id
501 and tdb.booking_id = p_booking_id;
502
503 l_enroll_type varchar2(30);
504 l_incoming_status_type varchar2(30);
505
506 l_customer_id_changed boolean;
507 l_organization_id_changed boolean;
508 l_delegate_person_id_changed boolean;
509 l_delegate_asg_changed boolean;
510
511 l_new_event_id ota_delegate_bookings.event_id%TYPE := p_event_id;
512 l_new_customer_id ota_delegate_bookings.customer_id%TYPE := p_customer_id;
513 l_new_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE := p_delegate_contact_id;
514 l_new_organization_id ota_delegate_bookings.organization_id%TYPE := p_organization_id;
515 l_new_del_asg_id ota_delegate_bookings.delegate_assignment_id%TYPE := p_delegate_assignment_id;
516 l_new_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE := p_delegate_person_id;
517
518 CURSOR csr_get_enr_details IS
519 SELECT event_id, customer_id, organization_id,
520 delegate_person_id, delegate_assignment_id,
521 delegate_contact_id
522 FROM ota_delegate_bookings
523 WHERE booking_id = p_booking_id;
524
525
526 l_enr_details_rec csr_get_enr_details%ROWTYPE;
527
528 l_old_booking_status varchar2(30);
529 l_evt_status_chg_comments varchar2(1000) := fnd_message.get_string('OTA','OTA_13523_TDB_STATUS_COMMENTS');
530 */
531 --
532 begin
533
534 hr_utility.set_location('Entering:'||l_proc, 5);
535
536 ota_delegate_booking_api.update_delegate_booking(
537 p_effective_date => trunc(sysdate)
538 , p_booking_id => p_booking_id
539 , p_booking_status_type_id => p_booking_status_type_id
540 , p_delegate_person_id => p_delegate_person_id
541 , p_contact_id => p_contact_id
542 , p_business_group_id => p_business_group_id
543 , p_event_id => p_event_id
544 , p_customer_id => p_customer_id
545 , p_authorizer_person_id => p_authorizer_person_id
546 , p_date_booking_placed => p_date_booking_placed
547 , p_corespondent => p_corespondent
548 , p_internal_booking_flag => p_internal_booking_flag
549 , p_number_of_places => p_number_of_places
550 , p_object_version_number => p_object_version_number
551 , p_administrator => p_administrator
552 , p_booking_priority => p_booking_priority
553 , p_comments => p_comments
554 , p_contact_address_id => p_contact_address_id
555 , p_delegate_contact_phone => p_delegate_contact_phone
556 , p_delegate_contact_fax => p_delegate_contact_fax
557 , p_third_party_customer_id => p_third_party_customer_id
558 , p_third_party_contact_id => p_third_party_contact_id
559 , p_third_party_address_id => p_third_party_address_id
560 , p_third_party_contact_phone => p_third_party_contact_phone
561 , p_third_party_contact_fax => p_third_party_contact_fax
562 , p_date_status_changed => p_date_status_changed
563 , p_status_change_comments => p_status_change_comments
564 , p_failure_reason => p_failure_reason
565 , p_attendance_result => p_attendance_result
566 , p_language_id => p_language_id
567 , p_source_of_booking => p_source_of_booking
568 , p_special_booking_instructions => p_special_booking_instructions
569 , p_successful_attendance_flag => p_successful_attendance_flag
570 , p_tdb_information_category => p_tdb_information_category
571 , p_tdb_information1 => p_tdb_information1
572 , p_tdb_information2 => p_tdb_information2
573 , p_tdb_information3 => p_tdb_information3
574 , p_tdb_information4 => p_tdb_information4
575 , p_tdb_information5 => p_tdb_information5
576 , p_tdb_information6 => p_tdb_information6
577 , p_tdb_information7 => p_tdb_information7
578 , p_tdb_information8 => p_tdb_information8
579 , p_tdb_information9 => p_tdb_information9
580 , p_tdb_information10 => p_tdb_information10
581 , p_tdb_information11 => p_tdb_information11
582 , p_tdb_information12 => p_tdb_information12
583 , p_tdb_information13 => p_tdb_information13
584 , p_tdb_information14 => p_tdb_information14
585 , p_tdb_information15 => p_tdb_information15
586 , p_tdb_information16 => p_tdb_information16
587 , p_tdb_information17 => p_tdb_information17
588 , p_tdb_information18 => p_tdb_information18
589 , p_tdb_information19 => p_tdb_information19
590 , p_tdb_information20 => p_tdb_information20
591 , p_update_finance_line => p_update_finance_line
592 , p_tfl_object_version_number => p_tfl_object_version_number
593 , p_finance_header_id => p_finance_header_id
594 , p_finance_line_id => p_finance_line_id
595 , p_standard_amount => p_standard_amount
596 , p_unitary_amount => p_unitary_amount
597 , p_money_amount => p_money_amount
598 , p_currency_code => p_currency_code
599 , p_booking_deal_type => p_booking_deal_type
600 , p_booking_deal_id => p_booking_deal_id
601 , p_enrollment_type => p_enrollment_type
602 , p_validate => p_validate
603 , p_organization_id => p_organization_id
604 , p_sponsor_person_id => p_sponsor_person_id
605 , p_sponsor_assignment_id => p_sponsor_assignment_id
606 , p_person_address_id => p_person_address_id
607 , p_delegate_assignment_id => p_delegate_assignment_id
608 , p_delegate_contact_id => p_delegate_contact_id
609 , p_delegate_contact_email => p_delegate_contact_email
610 , p_third_party_email => p_third_party_email
611 , p_person_address_type => p_person_address_type
612 , p_line_id => p_line_id
613 , p_org_id => p_org_id
614 , p_daemon_flag => p_daemon_flag
615 , p_daemon_type => p_daemon_type
616 , p_old_event_id => p_old_event_id
617 , p_quote_line_id => p_quote_line_id
618 , p_interface_source => p_interface_source
619 , p_total_training_time => p_total_training_time
620 , p_content_player_status => p_content_player_status
621 , p_score => p_score
622 , p_completed_content => p_completed_content
623 , p_total_content => p_total_content
624 , p_booking_justification_id => p_booking_justification_id
625 , p_source_cancel => p_source_cancel
626 , p_override_prerequisites => p_override_prerequisites
627 , p_is_history_flag => p_is_history_flag
628 , p_override_learner_access => p_override_learner_access
629 );
630
631 hr_utility.set_location('Leaving:'||l_proc, 5);
632
633 /*
634 --
635 g_debug := hr_utility.debug_enabled;
636
637 if g_debug then
638 l_proc := g_package||'update_enrollment';
639 hr_utility.set_location('Entering:'||l_proc, 5);
640 end if;
641 --
642 -- Issue a savepoint if operating in validation only mode.
643 --
644 if p_validate then
645 --
646 savepoint update_enrollment;
647 --
648 end if;
649 -- Truncate the time portion from all IN date parameters
650 --
651 -- l_effective_date := trunc(p_effective_date);
652 --
653
654 --
655 -- Validation in addition to Table Handlers
656 --
657 --
658 --
659 -- Lock Enrollment (Bug 2468167)
660 --
661 ota_tdb_shd.lck(p_booking_id,p_object_version_number);
662 --
663 -- Prerequisite Validation Code
664 -- Can be overridden if p_override_prerequisites parameter is 'Y'
665 -- get booking status type
666 ota_utility.get_booking_status_type(p_status_type_id=>p_booking_status_type_id,
667 p_type => l_incoming_status_type);
668
669 If ( p_override_prerequisites = 'N' ) Then
670 --Call local method
671 chk_mandatory_prereqs(p_delegate_person_id, p_delegate_contact_id, p_customer_id, p_event_id, p_booking_status_type_id);
672 End If;
673
674 IF p_override_learner_access <> 'Y' THEN
675 OPEN csr_get_enr_details;
676 FETCH csr_get_enr_details INTO l_enr_details_rec;
677 CLOSE csr_get_enr_details;
678
679 -- Modified for bug#4681165
680 IF l_new_event_id = hr_api.g_number THEN l_new_event_id := l_enr_details_rec.event_id; END IF;
681 IF l_new_customer_id = hr_api.g_number THEN l_new_customer_id := l_enr_details_rec.customer_id; END IF;
682 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;
683 IF l_new_organization_id = hr_api.g_number THEN l_new_organization_id := l_enr_details_rec.organization_id; END IF;
684 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;
685 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;
686
687
688 l_event_id_changed := ota_general.value_changed( l_enr_details_rec.event_id, l_new_event_id);
689 l_customer_id_changed := ota_general.value_changed( l_enr_details_rec.customer_id, l_new_customer_id);
690 l_organization_id_changed := ota_general.value_changed( l_enr_details_rec.organization_id, l_new_organization_id);
691 l_delegate_person_id_changed := ota_general.value_changed( l_enr_details_rec.delegate_person_id, l_new_delegate_person_id);
692 l_delegate_asg_changed := ota_general.value_changed( l_enr_details_rec.delegate_assignment_id, l_new_del_asg_id);
693
694 if l_event_id_changed or
695 l_customer_id_changed or
696 l_organization_id_changed or
697 l_delegate_person_id_changed or
698 l_delegate_asg_changed then
699 --
700 -- check that the delegate is eligible to be booked on to the event
701 --
702 ota_tdb_bus.check_delegate_eligible(
703 p_event_id => l_new_event_id
704 ,p_customer_id => l_new_customer_id
705 ,p_delegate_contact_id => l_new_delegate_contact_id
706 ,p_organization_id => l_new_organization_id
707 ,p_delegate_person_id => l_new_delegate_person_id
708 ,p_delegate_assignment_id => l_new_del_asg_id);
709 END IF;
710 END IF;
711
712 -- Added for bug#4654530
713 IF nvl(p_status_change_comments,hr_api.g_varchar2) <> l_evt_status_chg_comments THEN
714
715 OPEN get_status_info;
716 FETCH get_status_info INTO l_old_booking_status;
717 CLOSE get_status_info;
718
719 IF l_incoming_status_type = 'C'
720 AND l_old_booking_status <> 'C'
721 AND l_daemon_type IS NULL THEN
722 l_daemon_type := get_daemon_type(p_booking_id);
723 IF l_daemon_type IS NOT NULL THEN
724 l_daemon_flag := 'Y';
725 ELSE
726 l_daemon_flag := 'N';
727 END IF;
728 END IF;
729
730 IF l_incoming_status_type <> 'C'
731 AND l_old_booking_status = 'C' THEN
732 l_daemon_flag := 'N';
733 l_daemon_type := NULL;
734 END IF;
735
736 END IF;
737
738 -- Lock the Event
739 --
740 ota_evt_bus2.lock_event(p_event_id);
741
742 -- get booking_status type to fire ntf process
743 open get_status_info;
744 fetch get_status_info into l_enroll_type;
745 close get_status_info;
746 --
747 -- Get Event record
748 --
749 ota_evt_shd.get_event_details (p_event_id,
750 l_event_rec,
751 l_event_exists);
752
753 -- Ignore Enrollment Dff Validation for some cases
754 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
755 l_add_struct_d.extend(1);
756 l_add_struct_d(l_add_struct_d.count) := 'OTA_DELEGATE_BOOKINGS';
757 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
758 l_ignore_dff_validation := 'Y';
759 else
760 l_ignore_dff_validation := 'N';
761 end if;
762
763 --
764 -- Validation Check on booking / event statuses
765 --
766 Check_Status_Change(p_event_id
767 ,p_booking_Status_type_id
768 ,l_event_rec.event_status
769 ,p_number_of_places
770 ,l_event_rec.maximum_attendees);
771
772 --
773 --Bug 2359495
774 IF p_status_change_comments IS NULL THEN
775 l_status_change_comments := hr_general_utilities.get_lookup_meaning
776 ('ENROLMENT_STATUS_REASON',
777 'A');
778 ELSE
779 l_status_change_comments := p_status_change_comments;
780 END IF;
781 --Bug 2359495
782 -- Force Update
783 --
784 ota_tdb_upd.upd
785 (
786 p_booking_id,
787 p_booking_status_type_id,
788 p_delegate_person_id,
789 p_contact_id,
790 p_business_group_id,
791 p_event_id,
792 p_customer_id,
793 p_authorizer_person_id,
794 p_date_booking_placed,
795 p_corespondent,
796 p_internal_booking_flag,
797 p_number_of_places,
798 p_object_version_number,
799 p_administrator,
800 p_booking_priority,
801 p_comments,
802 p_contact_address_id,
803 p_delegate_contact_phone,
804 p_delegate_contact_fax,
805 p_third_party_customer_id,
806 p_third_party_contact_id,
807 p_third_party_address_id,
808 p_third_party_contact_phone,
809 p_third_party_contact_fax,
810 p_date_status_changed,
811 l_status_change_comments, -- p_status_change_comments, Bug 2359495
812 p_failure_reason,
813 p_attendance_result,
814 p_language_id,
815 p_source_of_booking,
816 p_special_booking_instructions,
817 p_successful_attendance_flag,
818 p_tdb_information_category,
819 p_tdb_information1,
820 p_tdb_information2,
821 p_tdb_information3,
822 p_tdb_information4,
823 p_tdb_information5,
824 p_tdb_information6,
825 p_tdb_information7,
826 p_tdb_information8,
827 p_tdb_information9,
828 p_tdb_information10,
829 p_tdb_information11,
830 p_tdb_information12,
831 p_tdb_information13,
832 p_tdb_information14,
833 p_tdb_information15,
834 p_tdb_information16,
835 p_tdb_information17,
836 p_tdb_information18,
837 p_tdb_information19,
838 p_tdb_information20,
839 p_update_finance_line,
840 p_tfl_object_version_number,
841 p_finance_header_id,
842 p_finance_line_id,
843 p_standard_amount,
844 p_unitary_amount,
845 p_money_amount,
846 p_currency_code,
847 p_booking_deal_type,
848 p_booking_deal_id,
849 p_enrollment_type,
850 p_validate,
851 p_organization_id,
852 p_sponsor_person_id,
853 p_sponsor_assignment_id,
854 p_person_address_id,
855 p_delegate_assignment_id,
856 p_delegate_contact_id,
857 p_delegate_contact_email,
858 p_third_party_email,
859 p_person_address_type,
860 p_line_id,
861 p_org_id,
862 -- Modified for bug#4654530
863 l_daemon_flag,
864 l_daemon_type,
865 -- p_daemon_flag,
866 -- p_daemon_type,
867 p_old_event_id,
868 p_quote_line_id,
869 p_interface_source,
870 p_total_training_time,
871 p_content_player_status,
872 p_score,
873 p_completed_content,
874 p_total_content,
875 p_booking_justification_id,
876 p_is_history_flag
877 );
878 --
879 l_status_type_id_changed :=
880 ota_general.value_changed (ota_tdb_shd.g_old_rec.booking_status_type_id,
881 p_booking_status_type_id);
882
883 --Getting the old booking status to manipulate the fourm notification records
884 l_old_booking_status := ota_tdb_bus.booking_status_type(
885 ota_tdb_shd.g_old_rec.booking_status_type_id);
886
887 OPEN is_contact;
888 FETCH is_contact INTO l_contact_id,l_delegate_contact_id;
889 CLOSE is_contact;
890
891 If (p_delegate_person_id = hr_api.g_number) then
892 select delegate_person_id into l_person_id from ota_delegate_bookings
893 where booking_id = p_booking_id;
894 else l_person_id := p_delegate_person_id;
895 End If;
896 --
897 --Added by dbatra
898 -- this is to take care of granting competencies attached to LP which are completed
899 -- but course under it was not successfully attended intitially.
900 if (not l_status_type_id_changed) and p_successful_attendance_flag ='Y' then
901 if l_delegate_contact_id is null and l_contact_id is null then
902
903 ota_lrng_path_util.start_comp_proc_success_attnd(p_person_id =>l_person_id
904 ,p_event_id => p_event_id);
905
906 ota_cpe_util.crt_comp_upd_succ_att(p_person_id =>l_person_id
907 ,p_event_id => p_event_id);
908
909 end if;
910 end if;
911 --
912 if l_status_type_id_changed or p_successful_attendance_flag ='Y' then
913 --
914 -- Added by dbatra for training plan
915 -- Bug 2982183
916 if l_delegate_contact_id is null and l_contact_id is null and l_status_type_id_changed then
917
918 /* bug 3795299
919 ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
920 p_person_id => l_person_id,
921 -- Added for Bug#3479186
922 p_contact_id => NULL,
923 p_learning_path_ids => l_learning_path_ids);
924 ELSE-- Added for Bug#3479186
925 ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
926 p_person_id => NULL,
927 p_contact_id => l_delegate_contact_id,
928 p_learning_path_ids => l_learning_path_ids);
929 */
930 /*
931 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
932 p_person_id => l_person_id,
933 p_contact_id => null,
934 p_lp_enrollment_ids => l_lp_enrollment_ids);
935 -- update any associated cert member enrollment statuses
936 ota_cme_util.update_cme_status(p_event_id => p_event_id,
937 p_person_id => l_person_id,
938 p_contact_id => null,
939 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
940
941 ELSif l_delegate_contact_id is not null then
942 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
943 p_person_id => null,
944 p_contact_id => l_delegate_contact_id,
945 p_lp_enrollment_ids => l_lp_enrollment_ids);
946 -- update any associated cert member enrollment statuses
947 ota_cme_util.update_cme_status(p_event_id => p_event_id,
948 p_person_id => null,
949 p_contact_id => l_delegate_contact_id,
950 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
951
952 end if; -- contact_id
953
954 select Type into l_type from ota_booking_status_types where booking_status_type_id=p_booking_status_type_id;
955
956 if l_type='A' and l_delegate_contact_id is null and l_contact_id is null then
957
958 -- check whether class is online or not
959 */
960 /* OPEN chk_for_comp_upd;
961 FETCH chk_for_comp_upd INTO l_on_flag,l_LO_id;
962 CLOSE chk_for_comp_upd;
963
964 if l_on_flag='Y' then
965 -- check whether online class is succesfully completed or not
966 l_comp_upd := ota_lo_utility.get_history_button(p_user_id => l_person_id,
967 p_lo_id => l_LO_id ,
968 p_event_id => p_event_id,
969 p_booking_id => p_booking_id);
970 elsif p_successful_attendance_flag <>'Y' then
971 l_comp_upd := null;
972 end if; -- flag */
973 /*
974 if p_successful_attendance_flag = 'Y' then
975 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
976 p_itemtype =>'HRSSA',
977 p_person_id => l_person_id,
978 p_eventid =>p_event_id,
979 p_learningpath_ids => null,
980 p_itemkey =>l_item_key);
981
982 end if;
983
984
985 end if;
986
987 --- send ntf to waitlisted learner
988
989 if l_enroll_type = 'W' and l_type = 'P'
990 and l_delegate_contact_id is null and l_contact_id is null then
991
992 OTA_INITIALIZATION_WF.initialize_wf(p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC',
993 p_item_type => 'OTWF',
994 p_eventid => p_event_id,
995 p_person_id => l_person_id,
996 p_event_fired => 'ENROLL_STATUS_CHNG');
997
998 end if;
999
1000 -- send cancel enrollment ntf
1001
1002 if l_type ='C' and l_delegate_contact_id is null and l_contact_id is null
1003 and nvl(p_source_cancel,'-1') <> 'AME' then
1004
1005 OTA_LRNR_ENROLL_UNENROLL_WF.learner_unenrollment
1006 (p_process => 'OTA_LNR_TRNG_CANCEL_JSP_PRC',
1007 p_itemtype => 'HRSSA',
1008 p_person_id => l_person_id,
1009 p_eventid => p_event_id);
1010
1011 end if;
1012
1013 -- Bug 2982183
1014 --
1015 ota_tdb_bus.maintain_status_history
1016 (p_booking_status_type_id,
1017 p_date_status_changed,
1018 p_administrator,
1019 l_status_change_comments, --p_status_change_comments,Bug 2359495
1020 p_booking_id,
1021 ota_tdb_shd.g_old_rec.date_status_changed,
1022 ota_tdb_shd.g_old_rec.booking_status_type_id,
1023 ota_tdb_shd.g_created_by,
1024 p_date_booking_placed);
1025 --
1026 ota_tdb_bus.ota_letter_lines
1027 (p_booking_id => p_booking_id,
1028 p_booking_status_type_id => p_booking_status_type_id,
1029 p_event_id => p_event_id,
1030 p_delegate_person_id => l_person_id);
1031 -- Modified for bug#3007934.
1032 -- p_delegate_person_id => p_delegate_person_id);
1033 ---***Added p_delegate_person_id. Bug2791524.
1034 --
1035 end if;
1036 --
1037 --Added for Bug#4106893
1038 IF p_event_id <> hr_api.g_number
1039 AND p_event_id <> ota_tdb_shd.g_old_rec.event_id THEN
1040 l_event_id_changed:= true;
1041 END IF;
1042
1043 IF p_delegate_person_id <> hr_api.g_number
1044 AND p_delegate_person_id <> ota_tdb_shd.g_old_rec.delegate_person_id THEN
1045 l_person_id_changed := true;
1046 END IF;
1047
1048 IF p_delegate_contact_id <> hr_api.g_number
1049 AND p_delegate_contact_id <> ota_tdb_shd.g_old_rec.delegate_contact_id THEN
1050 l_contact_id_changed := true;
1051 END IF;
1052
1053
1054 --If the new enrollment status is 'C' or 'R' or 'W' then delete the forum notitifcation record
1055 if l_type = 'C' or l_type = 'R' or l_type = 'W' then
1056 deleteForumNotification(p_event_id,l_person_id, l_delegate_contact_id);
1057 end if;
1058
1059 --If the booking status is changed from 'C','W' or 'R' to 'P' or 'A',
1060 --then we need to create a new forum notification record.
1061 if l_old_booking_status = 'C' or l_old_booking_status = 'W' or l_old_booking_status ='R'
1062 and l_type = 'P' or l_type = 'A' then
1063 if NOT l_event_id_changed and NOT l_person_id_changed AND NOT l_contact_id_changed THEN
1064 IF l_person_id IS NOT NULL THEN
1065 createForumNotification(p_event_id,l_person_id, null, l_effective_date, p_booking_status_type_id);
1066 ELSIF l_delegate_contact_id IS NOT NULL THEN
1067 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1068 end if;
1069 end if;
1070 end if;
1071 */
1072
1073 /**
1074 When the class name is changed for an enrollment, the lme update must be called
1075 twice, once for the old class and once for the new class.
1076 When the learner name is changed for an enrollment, the lme update must be called
1077 twice, once for the old learner and once for the new learner.
1078 If both the learner aswell as class associated with an enrollment are changed,
1079 update lme must be called once for old class, old learner and once for new class and new learner
1080 **/
1081 /*
1082 IF l_event_id_changed AND NOT l_person_id_changed AND NOT l_contact_id_changed THEN
1083 IF l_person_id IS NOT NULL THEN
1084 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1085 p_person_id => l_person_id,
1086 p_contact_id => null,
1087 p_lp_enrollment_ids => l_lp_enrollment_ids);
1088 -- update any associated cert member enrollment statuses
1089 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1090 p_person_id => l_person_id,
1091 p_contact_id => null,
1092 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1093
1094 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id =>p_event_id,
1095 p_person_id => l_person_id,
1096 p_contact_id => null,
1097 p_lp_enrollment_ids => l_lp_enrollment_ids);
1098 -- update any associated cert member enrollment statuses
1099 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1100 p_person_id => l_person_id,
1101 p_contact_id => null,
1102 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1103 --If the event has changed, the forum notification record should be deleted and created for the new event
1104 -- FRM Notification should be created only for Placed or Attended status. Not for 'C','W' or 'R'.
1105 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null);
1106 createForumNotification(p_event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
1107 ELSIF l_delegate_contact_id IS NOT NULL THEN
1108 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1109 p_person_id => null,
1110 p_contact_id => l_delegate_contact_id,
1111 p_lp_enrollment_ids => l_lp_enrollment_ids);
1112
1113 -- update any associated cert member enrollment statuses
1114 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1115 p_person_id => null,
1116 p_contact_id => l_delegate_contact_id,
1117 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1118
1119 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
1120 p_person_id => null,
1121 p_contact_id => l_delegate_contact_id,
1122 p_lp_enrollment_ids => l_lp_enrollment_ids);
1123 -- update any associated cert member enrollment statuses
1124 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1125 p_person_id => null,
1126 p_contact_id => l_delegate_contact_id,
1127 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1128 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, l_delegate_contact_id);
1129 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1130 END IF;
1131 ELSIF l_event_id_changed THEN
1132 IF l_person_id_changed THEN
1133 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1134 p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
1135 p_contact_id => null,
1136 p_lp_enrollment_ids => l_lp_enrollment_ids);
1137
1138 -- update any associated cert member enrollment statuses
1139 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1140 p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
1141 p_contact_id => null,
1142 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1143
1144 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
1145 p_person_id => l_person_id,
1146 p_contact_id => null,
1147 p_lp_enrollment_ids => l_lp_enrollment_ids);
1148 -- update any associated cert member enrollment statuses
1149 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1150 p_person_id => l_person_id,
1151 p_contact_id => null,
1152 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1153 -- the class and the learner have change.So delete the forum record for old class and person
1154 --and create a notification record for the new class and new person
1155 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
1156 createForumNotification(p_event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
1157 ELSIF l_contact_id_changed THEN
1158 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1159 p_person_id => null,
1160 p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
1161 p_lp_enrollment_ids => l_lp_enrollment_ids);
1162 -- update any associated cert member enrollment statuses
1163 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1164 p_person_id => null,
1165 p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
1166 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1167
1168 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
1169 p_person_id => null,
1170 p_contact_id => p_delegate_contact_id,
1171 p_lp_enrollment_ids => l_lp_enrollment_ids);
1172 -- update any associated cert member enrollment statuses
1173 ota_cme_util.update_cme_status(p_event_id => p_event_id,
1174 p_person_id => null,
1175 p_contact_id => p_delegate_contact_id,
1176 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1177 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
1178 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1179 END IF;
1180 ELSIF l_person_id_changed THEN
1181 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1182 p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
1183 p_contact_id => null,
1184 p_lp_enrollment_ids => l_lp_enrollment_ids);
1185
1186 -- update any associated cert member enrollment statuses
1187 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1188 p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
1189 p_contact_id => null,
1190 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1191
1192 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1193 p_person_id => l_person_id,
1194 p_contact_id => null,
1195 p_lp_enrollment_ids => l_lp_enrollment_ids);
1196 -- update any associated cert member enrollment statuses
1197 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1198 p_person_id => l_person_id,
1199 p_contact_id => null,
1200 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1201 -- the learner has changed.So delete the forum record for old person
1202 --and create a notification record for the new person
1203 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
1204 createForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
1205
1206 ELSIF l_contact_id_changed THEN
1207 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1208 p_person_id => null,
1209 p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
1210 p_lp_enrollment_ids => l_lp_enrollment_ids);
1211 -- update any associated cert member enrollment statuses
1212 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1213 p_person_id => null,
1214 p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
1215 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1216
1217 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1218 p_person_id => null,
1219 p_contact_id => p_delegate_contact_id,
1220 p_lp_enrollment_ids => l_lp_enrollment_ids);
1221 -- update any associated cert member enrollment statuses
1222 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
1223 p_person_id => null,
1224 p_contact_id => p_delegate_contact_id,
1225 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
1226 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
1227 createForumNotification(ota_tdb_shd.g_old_rec.event_id, null, p_delegate_contact_id, l_effective_date, p_booking_status_type_id);
1228
1229 END IF;
1230
1231 if p_update_finance_line in ('C','Y') then
1232 --
1233 l_cancel_finance_line := (p_update_finance_line = 'C');
1234 ota_finance.maintain_finance_line
1235 (p_finance_header_id => p_finance_header_id,
1236 p_booking_id => p_booking_id ,
1237 p_currency_code => p_currency_code ,
1238 p_standard_amount => p_standard_amount,
1239 p_unitary_amount => p_unitary_amount ,
1240 p_money_amount => p_money_amount ,
1241 p_booking_deal_id => p_booking_deal_id ,
1242 p_booking_deal_type => p_booking_deal_type,
1243 p_object_version_number => p_tfl_object_version_number,
1244 p_finance_line_id => p_finance_line_id,
1245 p_cancel_finance_line => l_cancel_finance_line);
1246 --
1247 end if;
1248
1249 --
1250 -- Reset Event Status
1251 --
1252 ota_evt_bus2.reset_event_status(p_event_id
1253 ,l_event_rec.object_version_number
1254 ,l_event_rec.event_status
1255 ,l_event_rec.maximum_attendees);
1256 --
1257 -- *** In case of Event Change -- Reset Event Status for Old Event ***
1258 --
1259 if p_event_id <> ota_tdb_shd.g_old_rec.event_id then
1260 ota_evt_bus2.lock_event(ota_tdb_shd.g_old_rec.event_id);
1261
1262 ota_evt_shd.get_event_details (ota_tdb_shd.g_old_rec.event_id,
1263 l_event_rec,
1264 l_event_exists);
1265
1266 ota_evt_bus2.reset_event_status(ota_tdb_shd.g_old_rec.event_id
1267 ,l_event_rec.object_version_number
1268 ,l_event_rec.event_status
1269 ,l_event_rec.maximum_attendees);
1270
1271 ota_evt_shd.get_event_details (p_event_id,
1272 l_event_rec,
1273 l_event_exists);
1274 end if;
1275
1276 if ( l_ignore_dff_validation = 'Y') then
1277 hr_dflex_utility.remove_ignore_df_validation;
1278 end if;
1279
1280 --
1281 -- When in validation only mode raise the Validate_Enabled exception
1282 --
1283 if p_validate then
1284 --
1285 raise hr_api.validate_enabled;
1286 --
1287 end if;
1288
1289 --
1290 if g_debug then
1291 hr_utility.set_location('Leaving:'||l_proc, 10);
1292 end if;
1293 --
1294 exception
1295 when hr_api.validate_enabled then
1296 --
1297 -- As the Validate_Enabled exception has been raised
1298 -- we must rollback to the savepoint
1299 --
1300 ROLLBACK TO update_enrollment;
1301 --
1302 -- Only set output warning arguments
1303 -- (Any key or derived arguments must be set to null
1304 -- when validation only mode is being used.)
1305 --
1306 */
1307 end Update_Enrollment;
1308 --
1309 -- ----------------------------------------------------------------------------
1310 -- |--------------------------< Update Waitlisted >-------------------------|
1311 -- ----------------------------------------------------------------------------
1312 --
1313 -- PUBLIC
1314 -- Description: Updates Waitlisted enrollments from the Waitlist window.
1315 --
1316 --
1317 Procedure Update_Waitlisted (p_booking_id in number
1318 ,p_object_version_number in out nocopy number
1319 ,p_event_id in number
1320 ,p_booking_status_type_id in number
1321 ,p_date_status_changed in date
1322 ,p_status_change_comments in varchar2
1323 ,p_number_of_places in number
1324 ,p_finance_line_id in out nocopy number
1325 ,p_tfl_object_version_number in out nocopy number
1326 ,p_administrator in number
1327 ,p_validate in boolean
1328 ) is
1329 --
1330 --
1331 l_status_change_comments varchar2(1000);
1332 l_proc varchar2(72);
1333 l_places number;
1334 --
1335 begin
1336 --
1337 g_debug := hr_utility.debug_enabled;
1338
1339 if g_debug then
1340 l_proc := g_package||'Update_Waitlisted';
1341 hr_utility.set_location('Entering:'|| l_proc, 5);
1342 end if;
1343 --
1344 -- Call the delegate booking update API.
1345 --
1346
1347 l_places := ota_evt_bus2.get_vacancies(p_event_id);
1348
1349 if p_number_of_places <= l_places or
1350 l_places is null then
1351 --
1352 ota_tdb_api_upd2.update_enrollment
1353 (p_booking_id => p_booking_id
1354 ,p_object_version_number => p_object_version_number
1355 ,p_event_id => p_event_id
1356 ,p_booking_status_type_id => p_booking_status_type_id
1357 ,p_date_status_changed => p_date_status_changed
1358 ,p_status_change_comments => p_status_change_comments
1359 ,p_number_of_places => p_number_of_places
1360 ,p_update_finance_line => 'N'
1361 ,p_finance_line_id => p_finance_line_id
1362 ,p_tfl_object_version_number => p_tfl_object_version_number
1363 ,p_administrator => p_administrator
1364 ,p_validate => p_validate
1365 );
1366 --
1367 -- commit the changes
1368 --
1369 commit;
1370 --
1371 else
1372 --
1373 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
1374 fnd_message.raise_error;
1375 --
1376 end if;
1377 --
1378 if g_debug then
1379 hr_utility.set_location('Leaving:'|| l_proc, 10);
1380 end if;
1381 --
1382 end Update_Waitlisted;
1383
1384 Procedure chk_mandatory_prereqs
1385 (p_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE,
1386 p_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE,
1387 p_customer_id ota_delegate_bookings.customer_id%TYPE,
1388 p_event_id ota_events.event_id%TYPE,
1389 p_booking_status_type_id in ota_delegate_bookings.booking_status_type_id%TYPE
1390 ) IS
1391
1392 l_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE;
1393 l_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE;
1394 l_customer_id ota_delegate_bookings.customer_id%TYPE;
1395 l_event_id ota_events.event_id%TYPE;
1396 l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%TYPE;
1397 l_check_prereq boolean;
1398 l_old_status_type varchar2(30);
1399 l_new_status_type varchar2(30);
1400
1401 Begin
1402 -- Prerequisite Validation Code
1403 l_check_prereq := false;
1404
1405 If ( p_delegate_person_id = hr_api.g_number ) Then
1406 l_delegate_person_id := ota_tdb_shd.g_old_rec.delegate_person_id;
1407 Else
1408 l_delegate_person_id := p_delegate_person_id;
1409 End If;
1410
1411 If ( p_delegate_contact_id = hr_api.g_number ) Then
1412 l_delegate_contact_id := ota_tdb_shd.g_old_rec.delegate_contact_id;
1413 Else
1414 l_delegate_contact_id := p_delegate_contact_id;
1415 End If;
1416
1417 If ( p_customer_id = hr_api.g_number ) Then
1418 l_customer_id := ota_tdb_shd.g_old_rec.customer_id;
1419 Else
1420 l_customer_id := p_customer_id;
1421 End If;
1422
1423 If ( p_event_id = hr_api.g_number ) Then
1424 l_event_id := ota_tdb_shd.g_old_rec.event_id;
1425 Else
1426 l_event_id := p_event_id;
1427 End If;
1428
1429 If ( p_booking_status_type_id = hr_api.g_number ) Then
1430 l_booking_status_type_id := ota_tdb_shd.g_old_rec.booking_status_type_id;
1431 Else
1432 l_booking_status_type_id := p_booking_status_type_id;
1433 End If;
1434
1435 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_person_id, l_delegate_person_id) ) Then
1436 l_check_prereq := true;
1437 End If;
1438
1439 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_contact_id, l_delegate_contact_id) ) Then
1440 l_check_prereq := true;
1441 End If;
1442
1443 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.customer_id, l_customer_id) ) Then
1444 l_check_prereq := true;
1445 End If;
1446
1447 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.event_id, l_event_id) ) Then
1448 l_check_prereq := true;
1449 End If;
1450
1451 ota_utility.get_booking_status_type(p_status_type_id => ota_tdb_shd.g_old_rec.booking_status_type_id,
1452 p_type => l_old_status_type);
1453
1454 ota_utility.get_booking_status_type(p_status_type_id => l_booking_status_type_id,
1455 p_type => l_new_status_type);
1456
1457 If ( l_old_status_type = 'C' and l_new_status_type <> 'C' ) Then
1458 l_check_prereq := true;
1459 End If;
1460
1461 If ( l_check_prereq and (l_delegate_person_id is not null or l_delegate_contact_id is not null) ) Then
1462 ota_cpr_utility.chk_mandatory_prereqs(l_delegate_person_id, l_delegate_contact_id, l_event_id);
1463 End If;
1464 --
1465 End chk_mandatory_prereqs;
1466
1467 --
1468 end ota_tdb_api_upd2;