[Home] [Help]
PACKAGE BODY: APPS.OTA_DELEGATE_BOOKING_API
Source
1 PACKAGE BODY OTA_DELEGATE_BOOKING_API as
2 /* $Header: otenrapi.pkb 120.27.12010000.6 2008/09/09 10:14:44 pekasi ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_DELEGATE_BOOKING_API.';
7 g_debug boolean := hr_utility.debug_enabled; -- Global Debug status variable
8 --
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-------------------------< Check New Status >----------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- PRIVATE
15 -- Description: Check the delegate booking Status when Inserting.
16 --
17 --
18 procedure Check_New_Status(p_event_id in number
19 ,p_booking_status_type_id in number
20 ,p_event_status in varchar2
21 ,p_maximum_attendees in number
22 ,p_number_of_places in number) is
23 --
24 l_booking_status varchar2(30) := ota_tdb_bus.booking_status_type(
25 p_booking_status_type_id);
26
27 Cursor c_eval_info is
28 select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
29 decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369
30 from ota_evaluations evt_eval,
31 ota_evaluations act_eval,
32 ota_events evt
33 where evt_eval.object_id(+) = evt.event_id
34 and (evt_eval.object_type is null or evt_eval.object_type = 'E')
35 and act_eval.object_id(+) = evt.activity_version_id
36 and (act_eval.object_type is null or act_eval.object_type = 'A')
37 and evt.event_id = p_event_id
38 and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --Bug7174996
39
40 l_eval_mand varchar2(1);
41 --
42 l_proc varchar2(72) := g_package||'check_new_status';
43 --
44 begin
45 --
46 --
47 hr_utility.set_location('Entering:'|| l_proc, 5);
48 --
49 -- Check for exceeding max attendees.
50 --
51
52 if p_maximum_attendees is not null then
53 --
54 if (ota_evt_bus2.get_vacancies(p_event_id) < p_number_of_places) and
55 l_booking_status in ('P','A','E') then
56 --
57 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
58 fnd_message.raise_error;
59 --
60 end if;
61 --
62 end if;
63
64 --
65 -- Check new status for Full Events.
66 --
67
68 if p_event_status = 'F' and l_booking_status in ('P','A','E','C') then
69 --
70 fnd_message.set_name('OTA','OTA_13518_TDB_NEW_STATUS_P');
71 fnd_message.raise_error;
72 --
73 end if;
74
75 --
76 -- 6683076.Check new status for Events with voluntary or null evaluation.
77 --
78 open c_eval_info;
79 fetch c_eval_info into l_eval_mand;
80 close c_eval_info;
81 if (l_eval_mand is null or l_eval_mand = 'N') and l_booking_status = 'E' then
82 --
83 fnd_message.set_name('OTA','OTA_467111_TDB_MAND_EVL_STATUS');
84 fnd_message.raise_error;
85 --
86 end if;
87
88 --
89 -- Check new status for Planned Events.
90 --
91
92 if p_event_status = 'P' and l_booking_status not in ('W','R') then
93 --
94 fnd_message.set_name('OTA','OTA_13518_TDB_NEW_STATUS_P');
95 fnd_message.raise_error;
96 --
97 end if;
98
99 --
100 -- Check new status for Cancelled or Closed Events.
101 --
102 if p_event_status in ('A','C') and l_booking_status is not null then
103 --
104 fnd_message.set_name('OTA','OTA_13519_TDB_NEW_STATUS_A');
105 fnd_message.raise_error;
106 --
107 end if;
108
109 --
110 -- Check new status for Normal Events.
111 --
112 if p_event_status = 'N' and l_booking_status in ('C') then
113 --
114 fnd_message.set_name('OTA','OTA_13520_TDB_NEW_STATUS_N');
115 fnd_message.raise_error;
116 --
117 end if;
118
119 --
120 hr_utility.set_location('Leaving:'|| l_proc, 10);
121 --
122 end Check_New_Status;
123 --
124
125
126 -- ----------------------------------------------------------------------------
127 -- |-------------------------< create_finance_header >------------------------|
128 -- ----------------------------------------------------------------------------
129 --
130 procedure create_finance_header
131 ( p_finance_header_id in number,
132 p_result_finance_header_id out nocopy number,
133 p_result_create_finance_line out nocopy varchar2,
134 p_create_finance_line in varchar2,
135 p_event_id in number,
136 p_delegate_person_id in number,
137 p_delegate_assignment_id in number,
138 p_business_group_id_from in number
139 ) is
140
141 l_auto_create_finance varchar2(40);
142 l_price_basis ota_events.price_basis%type;
143 l_business_group_id_to hr_all_organization_units.organization_id%type;
144 l_sponsor_organization_id hr_all_organization_units.organization_id%type;
145 l_event_currency_code ota_events.currency_code%type;
146 l_event_title ota_events.title%type;
147 l_course_start_date ota_events.course_start_date%type;
148 l_course_end_date ota_events.course_end_date%type;
149 l_owner_id ota_events.owner_id%type;
150 l_activity_version_id ota_activity_versions.activity_version_id%type;
151 l_offering_id ota_events.offering_id%type;
152 l_user number;
153 l_cost_allocation_keyflex_id VARCHAR2(1000);
154 l_business_group_id_from PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE;
155 l_organization_id PER_ALL_ASSIGNMENTS_F.organization_id%TYPE;
156
157 fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
158 fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
159 fapi_result VARCHAR2(40);
160 fapi_from VARCHAR2(5);
161 fapi_to VARCHAR2(5);
162
163 CURSOR bg_to IS
164 SELECT hao.business_group_id,
165 evt.organization_id,
166 evt.currency_code,
167 evt.course_start_date,
168 evt.course_end_date,
169 evt.Title,
170 evt.owner_id,
171 off.activity_version_id,
172 evt.offering_id,
173 nvl(evt.price_basis,NULL)
174 FROM OTA_EVENTS_VL evt,
175 OTA_OFFERINGS off,
176 HR_ALL_ORGANIZATION_UNITS hao
177 WHERE evt.event_id = p_event_id
178 AND off.offering_id = evt.parent_offering_id
179 AND evt.organization_id = hao.organization_id (+);
180
181 CURSOR csr_get_assignment_info IS
182 SELECT paf.organization_id
183 FROM per_all_assignments_f paf
184 WHERE paf.assignment_id = p_delegate_assignment_id;
185
186 CURSOR csr_get_cost_center_info IS
187 SELECT pcak.cost_allocation_keyflex_id
188 FROM per_all_assignments_f assg,
189 pay_cost_allocations_f pcaf,
190 pay_cost_allocation_keyflex pcak
191 WHERE assg.assignment_id = pcaf.assignment_id
192 AND assg.assignment_id = p_delegate_assignment_id
193 AND assg.Primary_flag = 'Y'
194 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
195 AND pcak.enabled_flag = 'Y'
196 AND sysdate between nvl(pcaf.effective_start_date,sysdate)
197 and nvl(pcaf.effective_end_date,sysdate+1)
198 AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
199 and nvl(assg.effective_end_date,trunc(sysdate+1));
200
201 begin
202
203 if(p_finance_header_id is null) then
204
205 l_auto_create_finance := FND_PROFILE.value('OTA_AUTO_CREATE_FINANCE');
206 l_user := FND_PROFILE.value('USER_ID');
207
208 open bg_to;
209 fetch bg_to into l_business_group_id_to,
210 l_sponsor_organization_id,
211 l_event_currency_code,
212 l_course_start_date,
213 l_course_end_date,
214 l_event_title,
215 l_owner_id,
216 l_activity_version_id,
217 l_offering_id,
218 l_price_basis;
219 close bg_to;
220
221
222 OPEN csr_get_assignment_info;
223 FETCH csr_get_assignment_info INTO l_organization_id;
224 CLOSE csr_get_assignment_info;
225
226 OPEN csr_get_cost_center_info;
227 FETCH csr_get_cost_center_info INTO l_cost_allocation_keyflex_id;
228 CLOSE csr_get_cost_center_info;
229
230 if p_delegate_person_id is not null and l_auto_create_finance = 'Y'
231 and l_price_basis <> 'N' and l_event_currency_code is not null THEN
232
233 ota_crt_finance_segment.Create_Segment(
234 p_assignment_id => p_delegate_assignment_id,
235 p_business_group_id_from => p_business_group_id_from,
236 p_business_group_id_to => l_business_group_id_to,
237 p_organization_id => l_organization_id,
238 p_sponsor_organization_id => l_sponsor_organization_id,
239 p_event_id => p_event_id,
240 p_person_id => p_delegate_person_id,
241 p_currency_code => l_event_currency_code,
242 p_cost_allocation_keyflex_id=> l_cost_allocation_keyflex_id,
243 p_user_id => l_user,
244 p_finance_header_id => fapi_finance_header_id,
245 p_object_version_number => fapi_object_version_number,
246 p_result => fapi_result,
247 p_from_result => fapi_from,
248 p_to_result => fapi_to );
249
250 if fapi_result = 'S' then
251 p_result_finance_header_id := fapi_finance_header_id;
252 p_result_create_finance_line := 'Y';
253 elsif fapi_result = 'E' then
254 p_result_finance_header_id := NULL;
255 p_result_create_finance_line := NULL;
256 end if;
257 end if;
258 else
259 p_result_create_finance_line := p_create_finance_line;
260 p_result_finance_header_id := p_finance_header_id;
261 end if;
262 end create_finance_header;
263 -- ----------------------------------------------------------------------------
264 -- |-------------------------< create_delegate_booking >----------------------|
265 -- ----------------------------------------------------------------------------
266 --
267 procedure create_delegate_booking
268 ( p_validate in boolean,
269 p_effective_date in date,
270 p_booking_id out nocopy number,
271 p_booking_status_type_id in number,
272 p_delegate_person_id in number ,
273 p_contact_id in number,
274 p_business_group_id in number,
275 p_event_id in number,
276 p_customer_id in number ,
277 p_authorizer_person_id in number ,
278 p_date_booking_placed in date,
279 p_corespondent in varchar2 ,
280 p_internal_booking_flag in varchar2,
281 p_number_of_places in number,
282 p_object_version_number out nocopy number,
283 p_administrator in number ,
284 p_booking_priority in varchar2 ,
285 p_comments in varchar2 ,
286 p_contact_address_id in number ,
287 p_delegate_contact_phone in varchar2 ,
288 p_delegate_contact_fax in varchar2 ,
289 p_third_party_customer_id in number ,
290 p_third_party_contact_id in number ,
291 p_third_party_address_id in number ,
292 p_third_party_contact_phone in varchar2 ,
293 p_third_party_contact_fax in varchar2 ,
294 p_date_status_changed in date ,
295 p_failure_reason in varchar2 ,
296 p_attendance_result in varchar2 ,
297 p_language_id in number ,
298 p_source_of_booking in varchar2 ,
299 p_special_booking_instructions in varchar2 ,
300 p_successful_attendance_flag in varchar2 ,
301 p_tdb_information_category in varchar2 ,
302 p_tdb_information1 in varchar2 ,
303 p_tdb_information2 in varchar2 ,
304 p_tdb_information3 in varchar2 ,
305 p_tdb_information4 in varchar2 ,
306 p_tdb_information5 in varchar2 ,
307 p_tdb_information6 in varchar2 ,
308 p_tdb_information7 in varchar2 ,
309 p_tdb_information8 in varchar2 ,
310 p_tdb_information9 in varchar2 ,
311 p_tdb_information10 in varchar2 ,
312 p_tdb_information11 in varchar2 ,
313 p_tdb_information12 in varchar2 ,
314 p_tdb_information13 in varchar2 ,
315 p_tdb_information14 in varchar2 ,
316 p_tdb_information15 in varchar2 ,
317 p_tdb_information16 in varchar2 ,
318 p_tdb_information17 in varchar2 ,
319 p_tdb_information18 in varchar2 ,
320 p_tdb_information19 in varchar2 ,
321 p_tdb_information20 in varchar2 ,
322 p_create_finance_line in varchar2 ,
323 p_finance_header_id in number ,
324 p_currency_code in varchar2 ,
325 p_standard_amount in number ,
326 p_unitary_amount in number ,
327 p_money_amount in number ,
328 p_booking_deal_id in number ,
329 p_booking_deal_type in varchar2 ,
330 p_finance_line_id in out nocopy number,
331 p_enrollment_type in varchar2 ,
332 p_organization_id in number ,
333 p_sponsor_person_id in number ,
334 p_sponsor_assignment_id in number ,
335 p_person_address_id in number ,
336 p_delegate_assignment_id in number ,
337 p_delegate_contact_id in number ,
338 p_delegate_contact_email in varchar2 ,
339 p_third_party_email in varchar2 ,
340 p_person_address_type in varchar2 ,
341 p_line_id in number ,
342 p_org_id in number ,
343 p_daemon_flag in varchar2 ,
344 p_daemon_type in varchar2 ,
345 p_old_event_id in number ,
346 p_quote_line_id in number ,
347 p_interface_source in varchar2 ,
348 p_total_training_time in varchar2 ,
349 p_content_player_status in varchar2 ,
350 p_score in number ,
351 p_completed_content in number ,
352 p_total_content in number ,
353 p_booking_justification_id in number ,
354 p_is_history_flag in varchar2 ,
355 p_override_prerequisites in varchar2 ,
356 p_override_learner_access in varchar2 ,
357 p_book_from in varchar2 ,
358 p_is_mandatory_enrollment in varchar2
359 ) is
360 --
361 -- Declare cursors and local variables
362 --
363 l_proc varchar2(72) := g_package||' create_delegate_booking ';
364
365 l_date_booking_placed date;
366 l_date_status_changed date;
367 l_effective_date date;
368
369 l_object_version_number number;
370 l_booking_id number;
371 l_dummy number;
372 l_event_status varchar2(30);
373 l_maximum_attendees number;
374 l_maximum_internal_attendees number;
375 l_evt_object_version_number number;
376 l_event_rec ota_evt_shd.g_rec_type;
377 l_event_exists boolean;
378
379 l_lp_enrollment_ids varchar2(4000);
380 l_cert_prd_enrollment_ids varchar2(4000);
381 l_item_key wf_items.item_key%type;
382
383 l_type ota_booking_status_types.type%type;
384
385
386 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
387 hr_dflex_utility.l_ignore_dfcode_varray();
388 l_ignore_dff_validation varchar2(1);
389 v_forum_id number;
390 v_business_group_id number;
391
392 l_create_finance_line varchar2(4);
393 l_finance_header_id number;
394 l_result_create_finance_line varchar2(4);
395 l_result_finance_header_id number;
396 l_automatic_transfer_gl varchar2(40);
397
398 Cursor chk_for_comp_upd is
399 select ocu.online_flag , off.Learning_object_id from ota_category_usages ocu,
400 ota_offerings off , ota_events oev
401 where ocu.category_usage_id = off.delivery_mode_id
402 and off.offering_id = oev.parent_offering_id
403 and oev.event_id = p_event_id;
404
405 Cursor csr_forums_for_class is
406 Select fr.forum_id, fr.business_group_id from
407 ota_forums_b fr,
408 ota_frm_obj_inclusions foi
409 where fr.forum_id = foi.forum_id
410 and foi.object_type = 'E'
411 and foi.object_id = p_event_id
412 and fr.auto_notification_flag = 'Y';
413 l_comp_upd varchar2(1000) :='MoveToHistoryImage';
414 l_on_flag varchar2(100);
415 l_LO_id ota_offerings.Learning_object_id%type;
416
417 --Bug 5386501
418 Cursor csr_class_data is
419 Select title from ota_events_vl
420 where event_id = p_event_id;
421 l_class_name ota_events_tl.title%type;
422 l_incoming_status_type varchar2(30);
423
424 --Bug 6683076
425 Cursor c_eval_info is
426 select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
427 decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369,6935364,7174996
428 from ota_evaluations evt_eval,
429 ota_evaluations act_eval,
430 ota_events evt
431 where evt_eval.object_id(+) = evt.event_id
432 and (evt_eval.object_type is null or evt_eval.object_type = 'E')
433 and act_eval.object_id(+) = evt.activity_version_id
434 and (act_eval.object_type is null or act_eval.object_type = 'A')
435 and evt.event_id = p_event_id
436 and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --bug 7174996
437
438 l_eval_mand varchar2(1);
439
440 cursor csr_get_currency_code is
441 select currency_code from ota_events
442 where event_id = p_event_id;
443
444 l_currency_code varchar2(40);
445
446 begin
447 hr_utility.set_location('Entering:'|| l_proc, 10);
448 --
449 -- Issue a savepoint
450 --
451 savepoint create_delegate_booking;
452
453 open csr_get_currency_code;
454 fetch csr_get_currency_code into l_currency_code;
455 close csr_get_currency_code;
456
457 --
458 -- Truncate the time portion from all IN date parameters
459 --
460 l_effective_date := trunc(p_effective_date);
461 -- l_date_booking_placed := trunc(p_date_booking_placed);
462 l_date_booking_placed := p_date_booking_placed;
463 l_date_status_changed := trunc(p_date_status_changed);
464
465 --
466 -- Validation in addition to Table Handlers
467 --
468 -- Prerequisite Validation Code
469 -- Can be overridden if p_override_prerequisites parameter is 'Y'
470 -- get booking status type
471
472 ota_utility.get_booking_status_type(p_status_type_id=>p_booking_status_type_id,
473 p_type => l_incoming_status_type);
474
475 If ( p_override_prerequisites = 'N' and nvl(l_incoming_status_type,'-1')<>'C'
476 and (p_delegate_person_id is not null or p_delegate_contact_id is not null) ) Then --Bug 4686100
477 --Call local method
478 ota_cpr_utility.chk_mandatory_prereqs(p_delegate_person_id, p_delegate_contact_id, p_event_id);
479 End If;
480
481 IF p_override_learner_access <> 'Y' THEN
482 --
483 -- check that the delegate is eligible to be booked on to the event
484 --
485 ota_tdb_bus.check_delegate_eligible (p_event_id,
486 p_customer_id,
487 p_delegate_contact_id,
488 p_organization_id,
489 p_delegate_person_id,
490 p_delegate_assignment_id);
491 -- Added for bug#4606760
492 ELSIF p_delegate_person_id IS NOT NULL THEN
493 ota_tdb_bus.check_secure_event(p_event_id, p_delegate_person_id);
494
495 END IF;
496
497 --
498 -- Lock Event
499 --
500 OPEN csr_class_data;--Bug 5386501
501 FETCH csr_class_data into l_class_name;
502 begin
503 ota_evt_bus2.lock_event(p_event_id);
504 exception
505 when others then
506 fnd_message.set_name('OTA','OTA_443997_EVT_ROW_LCK_ERR');
507 fnd_message.set_token('CLASS_NAME', l_class_name);
508 fnd_message.raise_error;
509 end;
510
511
512 --
513 -- Get Event record
514 --
515 ota_evt_shd.get_event_details (p_event_id,
516 l_event_rec,
517 l_event_exists);
518
519 -- Ignore Enrollment Dff Validation for some cases
520 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
521 l_add_struct_d.extend(1);
522 l_add_struct_d(l_add_struct_d.count) := 'OTA_DELEGATE_BOOKINGS';
523 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
524 l_ignore_dff_validation := 'Y';
525 else
526 l_ignore_dff_validation := 'N';
527 end if;
528
529 --
530 -- Validation Check on event / booking statuses
531 --
532
533 Check_New_Status(p_event_id
534 ,p_booking_Status_type_id
535 ,l_event_rec.event_status
536 ,l_event_rec.maximum_attendees
537 ,p_number_of_places);
538
539 -- Bug#7270603
540 create_finance_header(
541 p_finance_header_id => p_finance_header_id,
542 p_result_finance_header_id => l_finance_header_id,
543 p_result_create_finance_line => l_create_finance_line,
544 p_create_finance_line => p_create_finance_line,
545 p_event_id => p_event_id,
546 p_delegate_person_id => p_delegate_person_id,
547 p_delegate_assignment_id => p_delegate_assignment_id,
548 p_business_group_id_from => p_business_group_id);
549
550 --
551 -- Call Before Process User Hook
552 --
553 begin
554 ota_delegate_booking_bk1.create_delegate_booking_b
555 (p_effective_date => l_effective_date ,
556 p_booking_status_type_id => p_booking_status_type_id ,
557 p_delegate_person_id => p_delegate_person_id ,
558 p_contact_id => p_contact_id ,
559 p_business_group_id => p_business_group_id ,
560 p_event_id => p_event_id ,
561 p_customer_id => p_customer_id ,
562 p_authorizer_person_id => p_authorizer_person_id ,
563 p_date_booking_placed => l_date_booking_placed ,
564 p_corespondent => p_corespondent ,
565 p_internal_booking_flag => p_internal_booking_flag ,
566 p_number_of_places => p_number_of_places ,
567 p_administrator => p_administrator ,
568 p_booking_priority => p_booking_priority ,
569 p_comments => p_comments ,
570 p_contact_address_id => p_contact_address_id ,
571 p_delegate_contact_phone => p_delegate_contact_phone ,
572 p_delegate_contact_fax => p_delegate_contact_fax ,
573 p_third_party_customer_id => p_third_party_customer_id ,
574 p_third_party_contact_id => p_third_party_contact_id ,
575 p_third_party_address_id => p_third_party_address_id ,
576 p_third_party_contact_phone => p_third_party_contact_phone ,
577 p_third_party_contact_fax => p_third_party_contact_fax ,
578 p_date_status_changed => l_date_status_changed ,
579 p_failure_reason => p_failure_reason ,
580 p_attendance_result => p_attendance_result ,
581 p_language_id => p_language_id ,
582 p_source_of_booking => p_source_of_booking ,
583 p_special_booking_instructions =>p_special_booking_instructions ,
584 p_successful_attendance_flag => p_successful_attendance_flag ,
585 p_tdb_information_category => p_tdb_information_category ,
586 p_tdb_information1 => p_tdb_information1 ,
587 p_tdb_information2 => p_tdb_information2 ,
588 p_tdb_information3 => p_tdb_information3 ,
589 p_tdb_information4 => p_tdb_information4 ,
590 p_tdb_information5 => p_tdb_information5 ,
591 p_tdb_information6 => p_tdb_information6 ,
592 p_tdb_information7 => p_tdb_information7 ,
593 p_tdb_information8 => p_tdb_information8 ,
594 p_tdb_information9 => p_tdb_information9 ,
595 p_tdb_information10 => p_tdb_information10 ,
596 p_tdb_information11 => p_tdb_information11 ,
597 p_tdb_information12 => p_tdb_information12 ,
598 p_tdb_information13 => p_tdb_information13 ,
599 p_tdb_information14 => p_tdb_information14 ,
600 p_tdb_information15 => p_tdb_information15 ,
601 p_tdb_information16 => p_tdb_information16 ,
602 p_tdb_information17 => p_tdb_information17 ,
603 p_tdb_information18 => p_tdb_information18 ,
604 p_tdb_information19 => p_tdb_information19 ,
605 p_tdb_information20 => p_tdb_information20 ,
606 p_create_finance_line => l_create_finance_line ,
607 p_finance_header_id => l_finance_header_id ,
608 p_currency_code => p_currency_code ,
609 p_standard_amount => p_standard_amount ,
610 p_unitary_amount => p_unitary_amount ,
611 p_money_amount => p_money_amount ,
612 p_booking_deal_id => p_booking_deal_id ,
613 p_booking_deal_type => p_booking_deal_type ,
614 p_finance_line_id => p_finance_line_id ,
615 p_enrollment_type => p_enrollment_type ,
616 p_organization_id => p_organization_id ,
617 p_sponsor_person_id => p_sponsor_person_id ,
618 p_sponsor_assignment_id => p_sponsor_assignment_id ,
619 p_person_address_id => p_person_address_id ,
620 p_delegate_assignment_id => p_delegate_assignment_id ,
621 p_delegate_contact_id => p_delegate_contact_id ,
622 p_delegate_contact_email => p_delegate_contact_email ,
623 p_third_party_email => p_third_party_email ,
624 p_person_address_type => p_person_address_type ,
625 p_line_id => p_line_id ,
626 p_org_id => p_org_id ,
627 p_daemon_flag => p_daemon_flag ,
628 p_daemon_type => p_daemon_type ,
629 p_old_event_id => p_old_event_id ,
630 p_quote_line_id => p_quote_line_id ,
631 p_interface_source => p_interface_source ,
632 p_total_training_time => p_total_training_time ,
633 p_content_player_status => p_content_player_status ,
634 p_score => p_score ,
635 p_completed_content => p_completed_content ,
636 p_total_content => p_total_content ,
637 p_booking_justification_id => p_booking_justification_id ,
638 p_is_history_flag => p_is_history_flag ,
639 p_is_mandatory_enrollment => p_is_mandatory_enrollment
640 );
641 exception
642 when hr_api.cannot_find_prog_unit then
643 hr_api.cannot_find_prog_unit_error
644 (p_module_name => 'create_delegate_booking_b'
645 ,p_hook_type => 'BP'
646 );
647 end;
648 --
649 -- Validation in addition to Row Handlers
650 --
651 --
652 -- Process Logic
653 --
654 --
655 ota_tdb_ins.ins(
656 l_booking_id
657 , p_booking_status_type_id
658 , p_delegate_person_id
659 , p_contact_id
660 , p_business_group_id
661 , p_event_id
662 , p_customer_id
663 , p_authorizer_person_id
664 , p_date_booking_placed
665 , p_corespondent
666 , p_internal_booking_flag
667 , p_number_of_places
668 , l_object_version_number
669 , p_administrator
670 , p_booking_priority
671 , p_comments
672 , p_contact_address_id
673 , p_delegate_contact_phone
674 , p_delegate_contact_fax
675 , p_third_party_customer_id
676 , p_third_party_contact_id
677 , p_third_party_address_id
678 , p_third_party_contact_phone
679 , p_third_party_contact_fax
680 , p_date_status_changed
681 , p_failure_reason
682 , p_attendance_result
683 , p_language_id
684 , p_source_of_booking
685 , p_special_booking_instructions
686 , p_successful_attendance_flag
687 , p_tdb_information_category
688 , p_tdb_information1
689 , p_tdb_information2
690 , p_tdb_information3
691 , p_tdb_information4
692 , p_tdb_information5
693 , p_tdb_information6
694 , p_tdb_information7
695 , p_tdb_information8
696 , p_tdb_information9
697 , p_tdb_information10
698 , p_tdb_information11
699 , p_tdb_information12
700 , p_tdb_information13
701 , p_tdb_information14
702 , p_tdb_information15
703 , p_tdb_information16
704 , p_tdb_information17
705 , p_tdb_information18
706 , p_tdb_information19
707 , p_tdb_information20
708 , l_create_finance_line
709 , l_finance_header_id
710 , p_currency_code
711 , p_standard_amount
712 , p_unitary_amount
713 , p_money_amount
714 , p_booking_deal_id
715 , p_booking_deal_type
716 , p_finance_line_id
717 , p_enrollment_type
718 , p_validate
719 , p_organization_id
720 , p_sponsor_person_id
721 , p_sponsor_assignment_id
722 , p_person_address_id
723 , p_delegate_assignment_id
724 , p_delegate_contact_id
725 , p_delegate_contact_email
726 , p_third_party_email
727 , p_person_address_type
728 , p_line_id
729 , p_org_id
730 , p_daemon_flag
731 , p_daemon_type
732 , p_old_event_id
733 , p_quote_line_id
734 , p_interface_source
735 , p_total_training_time
736 , p_content_player_status
737 , p_score
738 , p_completed_content
739 , p_total_content
740 , p_booking_justification_id
741 , p_is_history_flag
742 , p_is_mandatory_enrollment);
743 --
744
745 --
746 -- Set all output arguments
747 --
748 p_booking_id := l_booking_id;
749 p_object_version_number := l_object_version_number;
750
751 ota_tdb_bus.ota_letter_lines
752 (p_booking_id => p_booking_id,
753 p_booking_status_type_id => p_booking_status_type_id,
754 p_event_id => p_event_id,
755 p_delegate_person_id => p_delegate_person_id);
756
757 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
758 p_person_id => p_delegate_person_id,
759 p_contact_id => p_delegate_contact_id,
760 p_lp_enrollment_ids => l_lp_enrollment_ids);
761
762 -- update any associated cert member enrollment statuses
763 ota_cme_util.update_cme_status(p_event_id => p_event_id,
764 p_person_id => p_delegate_person_id,
765 p_contact_id => p_delegate_contact_id,
766 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
767
768 select Type into l_type from ota_booking_status_types where booking_status_type_id=p_booking_status_type_id;
769
770 if l_type='A' and p_delegate_contact_id is null and p_contact_id is null then
771
772 -- check whether class is online or not
773 if p_successful_attendance_flag = 'Y' then
774 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
775 p_itemtype =>'HRSSA',
776 p_person_id => p_delegate_person_id,
777 p_eventid =>p_event_id,
778 p_learningpath_ids => null,
779 p_itemkey =>l_item_key);
780
781 end if;
782
783 end if;
784
785 --
786 -- fire learner enrollment notification
787 if p_contact_id is null and p_delegate_contact_id is null
788 and nvl(p_book_from,'-1') <> 'AME'
789 and l_event_rec.event_type in ('SCHEDULED','SELFPACED')then
790 -- call learner ntf process
791
792 OTA_LRNR_ENROLL_UNENROLL_WF.learner_enrollment(p_process => 'OTA_LNR_TRNG_APPROVAL_JSP_PRC',
793 p_itemtype => 'HRSSA',
794 p_person_id => p_delegate_person_id,
795 p_eventid => p_event_id,
796 p_booking_id => p_booking_id);
797 --Bug 6683076
798 open c_eval_info;
799 fetch c_eval_info into l_eval_mand;
800 close c_eval_info;
801
802 if((l_type = 'E' and l_eval_mand = 'Y') or (l_type = 'A' and l_eval_mand = 'N')) then
803 ota_initialization_wf.init_course_eval_notif(p_booking_id);
804 end if;
805
806 end if;
807
808 if l_create_finance_line = 'Y' then
809 --
810 ota_finance.maintain_finance_line(p_finance_header_id => l_finance_header_id,
811 p_booking_id => p_booking_id ,
812 p_currency_code => l_currency_code ,
813 p_standard_amount => p_standard_amount,
814 p_unitary_amount => p_unitary_amount ,
815 p_money_amount => p_money_amount ,
816 p_booking_deal_id => p_booking_deal_id ,
817 p_booking_deal_type => p_booking_deal_type,
818 p_object_version_number => l_dummy,
819 p_finance_line_id => p_finance_line_id);
820
821 l_automatic_transfer_gl := FND_PROFILE.value('OTA_SSHR_AUTO_GL_TRANSFER');
822 if l_automatic_transfer_gl = 'Y' AND p_finance_line_id IS NOT NULL THEN
823 UPDATE ota_finance_lines SET transfer_status = 'AT'
824 WHERE finance_line_id = p_finance_line_id;
825 end if;
826 --
827 end if;
828 --
829 --
830 hr_utility.set_location(l_proc, 7);
831
832 --
833 -- Reset Event Status
834 --
835 ota_evt_bus2.reset_event_status(p_event_id
836 ,l_event_rec.object_version_number
837 ,l_event_rec.event_status
838 ,l_event_rec.maximum_attendees);
839 --
840 hr_utility.set_location(l_proc, 8);
841
842 if ( l_ignore_dff_validation = 'Y' ) then
843 hr_dflex_utility.remove_ignore_df_validation;
844 end if;
845
846
847 --create frm_notif_subscriber record, only if the enrollment status is 'P' or 'A'
848 if (l_type='A' OR l_type = 'P') then
849 OPEN csr_forums_for_class;
850 FETCH csr_forums_for_class into v_forum_id, v_business_group_id;
851
852 LOOP
853 Exit When csr_forums_for_class%notfound OR csr_forums_for_class%notfound is null;
854
855 ota_fns_ins.ins
856 ( p_effective_date => l_effective_date
857 ,p_business_group_id => v_business_group_id
858 ,p_forum_id => v_forum_id
859 ,p_person_id => p_delegate_person_id
860 ,p_contact_id => p_delegate_contact_id
861 ,p_object_version_number => p_object_version_number
862 );
863
864 FETCH csr_forums_for_class into v_forum_id, v_business_group_id;
865 End Loop;
866 Close csr_forums_for_class;
867 end if;
868
869 /*
870 ota_tdb_api_ins2.create_enrollment
871 (p_booking_id => p_booking_id ,
872 p_booking_status_type_id => p_booking_status_type_id ,
873 p_delegate_person_id => p_delegate_person_id ,
874 p_contact_id => p_contact_id ,
875 p_business_group_id => p_business_group_id ,
876 p_event_id => p_event_id ,
877 p_customer_id => p_customer_id ,
878 p_authorizer_person_id => p_authorizer_person_id ,
879 p_date_booking_placed => l_date_booking_placed ,
880 p_corespondent => p_corespondent ,
881 p_internal_booking_flag => p_internal_booking_flag ,
882 p_number_of_places => p_number_of_places ,
883 p_object_version_number => p_object_version_number ,
884 p_administrator => p_administrator ,
885 p_booking_priority => p_booking_priority ,
886 p_comments => p_comments ,
887 p_contact_address_id => p_contact_address_id ,
888 p_delegate_contact_phone => p_delegate_contact_phone ,
889 p_delegate_contact_fax => p_delegate_contact_fax ,
890 p_third_party_customer_id => p_third_party_customer_id ,
891 p_third_party_contact_id => p_third_party_contact_id ,
892 p_third_party_address_id => p_third_party_address_id ,
893 p_third_party_contact_phone => p_third_party_contact_phone ,
894 p_third_party_contact_fax => p_third_party_contact_fax ,
895 p_date_status_changed => l_date_status_changed ,
896 p_failure_reason => p_failure_reason ,
897 p_attendance_result => p_attendance_result ,
898 p_language_id => p_language_id ,
899 p_source_of_booking => p_source_of_booking ,
900 p_special_booking_instructions =>p_special_booking_instructions ,
901 p_successful_attendance_flag => p_successful_attendance_flag ,
902 p_tdb_information_category => p_tdb_information_category ,
903 p_tdb_information1 => p_tdb_information1 ,
904 p_tdb_information2 => p_tdb_information2 ,
905 p_tdb_information3 => p_tdb_information3 ,
906 p_tdb_information4 => p_tdb_information4 ,
907 p_tdb_information5 => p_tdb_information5 ,
908 p_tdb_information6 => p_tdb_information6 ,
909 p_tdb_information7 => p_tdb_information7 ,
910 p_tdb_information8 => p_tdb_information8 ,
911 p_tdb_information9 => p_tdb_information9 ,
912 p_tdb_information10 => p_tdb_information10 ,
913 p_tdb_information11 => p_tdb_information11 ,
914 p_tdb_information12 => p_tdb_information12 ,
915 p_tdb_information13 => p_tdb_information13 ,
916 p_tdb_information14 => p_tdb_information14 ,
917 p_tdb_information15 => p_tdb_information15 ,
918 p_tdb_information16 => p_tdb_information16 ,
919 p_tdb_information17 => p_tdb_information17 ,
920 p_tdb_information18 => p_tdb_information18 ,
921 p_tdb_information19 => p_tdb_information19 ,
922 p_tdb_information20 => p_tdb_information20 ,
923 p_create_finance_line => p_create_finance_line ,
924 p_finance_header_id => p_finance_header_id ,
925 p_currency_code => p_currency_code ,
926 p_standard_amount => p_standard_amount ,
927 p_unitary_amount => p_unitary_amount ,
928 p_money_amount => p_money_amount ,
929 p_booking_deal_id => p_booking_deal_id ,
930 p_booking_deal_type => p_booking_deal_type ,
931 p_finance_line_id => p_finance_line_id ,
932 p_enrollment_type => p_enrollment_type ,
933 p_validate => p_validate ,
934 p_organization_id => p_organization_id ,
935 p_sponsor_person_id => p_sponsor_person_id ,
936 p_sponsor_assignment_id => p_sponsor_assignment_id ,
937 p_person_address_id => p_person_address_id ,
938 p_delegate_assignment_id => p_delegate_assignment_id ,
939 p_delegate_contact_id => p_delegate_contact_id ,
940 p_delegate_contact_email => p_delegate_contact_email ,
941 p_third_party_email => p_third_party_email ,
942 p_person_address_type => p_person_address_type ,
943 p_line_id => p_line_id ,
944 p_org_id => p_org_id ,
945 p_daemon_flag => p_daemon_flag ,
946 p_daemon_type => p_daemon_type ,
947 p_old_event_id => p_old_event_id ,
948 p_quote_line_id => p_quote_line_id ,
949 p_interface_source => p_interface_source ,
950 p_total_training_time => p_total_training_time ,
951 p_content_player_status => p_content_player_status ,
952 p_score => p_score ,
953 p_completed_content => p_completed_content ,
954 p_total_content => p_total_content ,
955 p_booking_justification_id => p_booking_justification_id ,
956 p_override_prerequisites => p_override_prerequisites ,
957 p_override_learner_access => p_override_learner_access
958 );
959 */
960
961 --
962 -- Call After Process User Hook
963 --
964 begin
965 OTA_delegate_booking_bk1.create_delegate_booking_a
966 (p_effective_date => l_effective_date ,
967 p_booking_status_type_id => p_booking_status_type_id ,
968 p_delegate_person_id => p_delegate_person_id ,
969 p_contact_id => p_contact_id ,
970 p_business_group_id => p_business_group_id ,
971 p_event_id => p_event_id ,
972 p_customer_id => p_customer_id ,
973 p_authorizer_person_id => p_authorizer_person_id ,
974 p_date_booking_placed => l_date_booking_placed ,
975 p_corespondent => p_corespondent ,
976 p_internal_booking_flag => p_internal_booking_flag ,
977 p_number_of_places => p_number_of_places ,
978 p_administrator => p_administrator ,
979 p_booking_priority => p_booking_priority ,
980 p_comments => p_comments ,
981 p_contact_address_id => p_contact_address_id ,
982 p_delegate_contact_phone => p_delegate_contact_phone ,
983 p_delegate_contact_fax => p_delegate_contact_fax ,
984 p_third_party_customer_id => p_third_party_customer_id ,
985 p_third_party_contact_id => p_third_party_contact_id ,
986 p_third_party_address_id => p_third_party_address_id ,
987 p_third_party_contact_phone => p_third_party_contact_phone ,
988 p_third_party_contact_fax => p_third_party_contact_fax ,
989 p_date_status_changed => l_date_status_changed ,
990 p_failure_reason => p_failure_reason ,
991 p_attendance_result => p_attendance_result ,
992 p_language_id => p_language_id ,
993 p_source_of_booking => p_source_of_booking ,
994 p_special_booking_instructions =>p_special_booking_instructions ,
995 p_successful_attendance_flag => p_successful_attendance_flag ,
996 p_tdb_information_category => p_tdb_information_category ,
997 p_tdb_information1 => p_tdb_information1 ,
998 p_tdb_information2 => p_tdb_information2 ,
999 p_tdb_information3 => p_tdb_information3 ,
1000 p_tdb_information4 => p_tdb_information4 ,
1001 p_tdb_information5 => p_tdb_information5 ,
1002 p_tdb_information6 => p_tdb_information6 ,
1003 p_tdb_information7 => p_tdb_information7 ,
1004 p_tdb_information8 => p_tdb_information8 ,
1005 p_tdb_information9 => p_tdb_information9 ,
1006 p_tdb_information10 => p_tdb_information10 ,
1007 p_tdb_information11 => p_tdb_information11 ,
1008 p_tdb_information12 => p_tdb_information12 ,
1009 p_tdb_information13 => p_tdb_information13 ,
1010 p_tdb_information14 => p_tdb_information14 ,
1011 p_tdb_information15 => p_tdb_information15 ,
1012 p_tdb_information16 => p_tdb_information16 ,
1013 p_tdb_information17 => p_tdb_information17 ,
1014 p_tdb_information18 => p_tdb_information18 ,
1015 p_tdb_information19 => p_tdb_information19 ,
1016 p_tdb_information20 => p_tdb_information20 ,
1017 p_create_finance_line => l_create_finance_line ,
1018 p_finance_header_id => l_finance_header_id ,
1019 p_currency_code => p_currency_code ,
1020 p_standard_amount => p_standard_amount ,
1021 p_unitary_amount => p_unitary_amount ,
1022 p_money_amount => p_money_amount ,
1023 p_booking_deal_id => p_booking_deal_id ,
1024 p_booking_deal_type => p_booking_deal_type ,
1025 p_finance_line_id => p_finance_line_id ,
1026 p_enrollment_type => p_enrollment_type ,
1027 p_organization_id => p_organization_id ,
1028 p_sponsor_person_id => p_sponsor_person_id ,
1029 p_sponsor_assignment_id => p_sponsor_assignment_id ,
1030 p_person_address_id => p_person_address_id ,
1031 p_delegate_assignment_id => p_delegate_assignment_id ,
1032 p_delegate_contact_id => p_delegate_contact_id ,
1033 p_delegate_contact_email => p_delegate_contact_email ,
1034 p_third_party_email => p_third_party_email ,
1035 p_person_address_type => p_person_address_type ,
1036 p_line_id => p_line_id ,
1037 p_org_id => p_org_id ,
1038 p_daemon_flag => p_daemon_flag ,
1039 p_daemon_type => p_daemon_type ,
1040 p_old_event_id => p_old_event_id ,
1041 p_quote_line_id => p_quote_line_id ,
1042 p_interface_source => p_interface_source ,
1043 p_total_training_time => p_total_training_time ,
1044 p_content_player_status => p_content_player_status ,
1045 p_score => p_score ,
1046 p_completed_content => p_completed_content ,
1047 p_total_content => p_total_content,
1048 p_booking_justification_id => p_booking_justification_id,
1049 p_is_history_flag => p_is_history_flag ,
1050 p_is_mandatory_enrollment => p_is_mandatory_enrollment,
1051 p_booking_id => p_booking_id
1052 );
1053
1054 exception
1055 when hr_api.cannot_find_prog_unit then
1056 hr_api.cannot_find_prog_unit_error
1057 (p_module_name => 'create_delegate_booking_a'
1058 ,p_hook_type => 'AP'
1059 );
1060 end;
1061 --
1062 -- When in validation only mode raise the Validate_Enabled exception
1063 --
1064 if p_validate then
1065 raise hr_api.validate_enabled;
1066 end if;
1067
1068 --
1069 hr_utility.set_location(' Leaving:'||l_proc, 70);
1070 exception
1071 when hr_api.validate_enabled then
1072 --
1073 -- As the Validate_Enabled exception has been raised
1074 -- we must rollback to the savepoint
1075 --
1076 rollback to create_delegate_booking;
1077 --
1078 -- Only set output warning arguments
1079 -- (Any key or derived arguments must be set to null
1080 -- when validation only mode is being used.)
1081 --
1082 p_object_version_number := null;
1083 p_booking_id := null;
1084 hr_utility.set_location(' Leaving:'||l_proc, 80);
1085 when others then
1086 --
1087 -- A validation or unexpected error has occured
1088 --
1089 rollback to create_delegate_booking;
1090 p_object_version_number := null;
1091 p_booking_id := null;
1092 hr_utility.set_location(' Leaving:'||l_proc, 90);
1093 raise;
1094 end create_delegate_booking ;
1095 --
1096 --
1097 -- ----------------------------------------------------------------------------
1098 -- |-------------------------< Check Status Change >--------------------------|
1099 -- ----------------------------------------------------------------------------
1100 --
1101 -- PRIVATE
1102 -- Description: Check the delegate booking Status when Updating.
1103 --
1104 --
1105 procedure Check_Status_Change(p_event_id in number
1106 ,p_booking_status_type_id in number
1107 ,p_event_status in varchar2
1108 ,p_number_of_places in number
1109 ,p_maximum_attendees in number) is
1110 --
1111 -- Set up local variables
1112 --
1113
1114 l_old_booking_status varchar2(30) := ota_tdb_bus.booking_status_type(
1115 ota_tdb_shd.g_old_rec.booking_status_type_id);
1116
1117 l_booking_status varchar2(30) := ota_tdb_bus.booking_status_type(
1118 p_booking_status_type_id);
1119
1120 l_booking_status_changed boolean := ota_general.value_changed(
1121 ota_tdb_shd.g_old_rec.booking_status_type_id,
1122 p_booking_status_type_id);
1123
1124 l_number_of_places_changed boolean := ota_general.value_changed(
1125 ota_tdb_shd.g_old_rec.number_of_places,
1126 p_number_of_places);
1127
1128 l_vacancies number := ota_evt_bus2.get_vacancies(p_event_id);
1129
1130 l_old_number_of_places number := ota_tdb_shd.g_old_rec.number_of_places;
1131
1132 l_old_event_id number := ota_tdb_shd.g_old_rec.event_id;
1133
1134 Cursor c_eval_info is
1135 select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
1136 decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369
1137 from ota_evaluations evt_eval,
1138 ota_evaluations act_eval,
1139 ota_events evt
1140 where evt_eval.object_id(+) = evt.event_id
1141 and (evt_eval.object_type is null or evt_eval.object_type = 'E')
1142 and act_eval.object_id(+) = evt.activity_version_id
1143 and (act_eval.object_type is null or act_eval.object_type = 'A')
1144 and evt.event_id = p_event_id
1145 and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --Bug7174996
1146
1147 l_eval_mand varchar2(1);
1148 --
1149 l_proc varchar2(72);
1150 --
1151 begin
1152 --
1153 if g_debug then
1154 l_proc := g_package||'check_status_change';
1155 hr_utility.set_location('Entering:'||l_proc, 5);
1156 end if;
1157
1158 --
1159 -- *** In case of Event Change -- Reset Event Status for Old Event ***
1160 --
1161
1162 if p_event_id <> l_old_event_id and
1163 l_booking_status in ('A','P','E') and
1164 ( p_event_status = 'F' or
1165 ota_evt_bus2.get_vacancies(p_event_id) <
1166 p_number_of_places ) then
1167
1168 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
1169 fnd_message.raise_error;
1170 --
1171 end if;
1172
1173 --Added for bug 6817203-Error message is displayed when learners with
1174 --enrollment status Placed or Attended are moved to Planned class.
1175 if p_event_id <> l_old_event_id and
1176 l_booking_status in ('A','P') and
1177 p_event_status = 'P' then
1178
1179 fnd_message.set_name('OTA','OTA_13518_TDB_NEW_STATUS_P');
1180 fnd_message.raise_error;
1181 --
1182 end if;
1183 --
1184 -- Check for exceeding max attendees.
1185 --
1186 if (l_booking_status in ('A','P','E') and
1187 (ota_evt_bus2.get_vacancies(p_event_id) <
1188 (p_number_of_places - l_old_number_of_places)))
1189 or
1190 (l_booking_status in ('A','P','E') and
1191 l_old_booking_status not in ('A','P','E') and
1192 ota_evt_bus2.get_vacancies(p_event_id) < p_number_of_places) then
1193 --
1194 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
1195 fnd_message.raise_error;
1196 --
1197 end if;
1198 --
1199 -- 6683076.Check for Events with voluntary or null evaluation.
1200 --
1201 open c_eval_info;
1202 fetch c_eval_info into l_eval_mand;
1203 close c_eval_info;
1204 if (l_eval_mand is null or l_eval_mand = 'N') and l_booking_status = 'E' then
1205 --
1206 fnd_message.set_name('OTA','OTA_467111_TDB_MAND_EVL_STATUS');
1207 fnd_message.raise_error;
1208 --
1209 end if;
1210 --
1211 -- Check booking status, if amended.
1212 --
1213 if l_booking_status_changed then
1214 --
1215 --
1216 --
1217 -- Check status change for Planned or Full Events.
1218 --
1219 if p_event_status in ('F','P','C') then
1220 --
1221 if l_old_booking_status not in ('P','A','E') and
1222 l_booking_status in ('P','A','E') then
1223 --
1224
1225 if l_old_booking_status = 'W' and
1226 l_booking_status in ('P','A','E') and
1227 ota_evt_bus2.get_vacancies(p_event_id) >= p_number_of_places
1228 and p_event_status <> 'P' then
1229 null;
1230 else
1231 fnd_message.set_name('OTA','OTA_13521_TDB_CH_STATUS_FP');
1232 fnd_message.raise_error;
1233 end if;
1234 --
1235 end if;
1236 --
1237 end if;
1238
1239 --
1240 -- Check status change for Cancelled Events.
1241 --
1242 if p_event_status in ('A') then
1243 --
1244 if l_booking_status <> 'C' then
1245 --
1246 fnd_message.set_name('OTA','OTA_13522_TDB_CH_STATUS_C');
1247 fnd_message.raise_error;
1248 --
1249 end if;
1250 --
1251 end if;
1252 --
1253 end if;
1254 --
1255 if g_debug then
1256 hr_utility.set_location('Leaving:'||l_proc, 10);
1257 end if;
1258 --
1259 --
1260 end Check_Status_Change;
1261
1262
1263
1264 --
1265 -- ----------------------------------------------------------------------------
1266 -- |-------------------------< deleteForumNotification >--------------------------|
1267 -- ----------------------------------------------------------------------------
1268 --
1269 -- PRIVATE
1270 -- Description: Delete the Forum notification record when a class is cancelled.
1271 --
1272 --
1273 procedure deleteForumNotification(l_event_id in number
1274 ,l_person_id in number
1275 ,l_contact_id in number) is
1276
1277 --
1278 -- Set up local variables
1279 --
1280
1281 cursor c_get_forum_id is
1282 select fns.forum_id,fns.object_version_number
1283 from ota_frm_obj_inclusions foi,ota_frm_notif_subscribers fns
1284 where foi.object_id = l_event_id
1285 and foi.object_Type = 'E'
1286 and foi.forum_id = fns.forum_id
1287 and (fns.person_id = l_person_id or fns.contact_id = l_contact_id);
1288 --
1289 v_forum_id number;
1290 v_object_version_number number;
1291
1292 l_proc varchar2(72);
1293
1294 begin
1295 --
1296 if g_debug then
1297 l_proc := g_package||'deleteForumNotification';
1298 hr_utility.set_location('Entering:'||l_proc, 5);
1299 end if;
1300
1301 --Delete the forum notification record for this class,for this user
1302 OPEN c_get_forum_id;
1303 FETCH c_get_forum_id into v_forum_id, v_object_version_number;
1304
1305 LOOP
1306 Exit When c_get_forum_id%notfound OR c_get_forum_id%notfound is null;
1307
1308 ota_fns_del.del
1309 (
1310 p_forum_id => v_forum_id
1311 ,p_person_id => l_person_id
1312 ,p_contact_id => l_contact_id
1313 ,p_object_version_number => v_object_version_number
1314 );
1315
1316 FETCH c_get_forum_id into v_forum_id, v_object_version_number;
1317 End Loop;
1318 Close c_get_forum_id;
1319
1320 --
1321 if g_debug then
1322 hr_utility.set_location('Leaving:'||l_proc, 10);
1323 end if;
1324 --
1325 --
1326 end deleteForumNotification;
1327 --
1328 -- ----------------------------------------------------------------------------
1329 -- |-------------------------< createForumNotification >--------------------------|
1330 -- ----------------------------------------------------------------------------
1331 --
1332 -- PRIVATE
1333 -- Description: Create the Forum notification record when a class is changed.
1334 --
1335 --
1336 procedure createForumNotification(l_event_id in number
1337 ,l_person_id in number
1338 ,l_contact_id in number
1339 ,l_effective_date in date
1340 ,l_booking_status_type_id in number) is
1341
1342 --
1343 -- Set up local variables
1344 --
1345
1346 Cursor csr_forums_for_class
1347 is
1348 Select fr.forum_id, fr.business_group_id from
1349 ota_forums_b fr,
1350 ota_frm_obj_inclusions foi
1351 where fr.forum_id = foi.forum_id
1352 and foi.object_type = 'E'
1353 and foi.object_id = l_event_id
1354 and fr.auto_notification_flag = 'Y';
1355 --
1356 v_forum_id number;
1357 v_business_group_id number;
1358 l_dummy number;
1359 l_proc varchar2(72);
1360 l_type ota_booking_status_types.type%type;
1361
1362 begin
1363 --
1364 if g_debug then
1365 l_proc := g_package||'createForumNotification';
1366 hr_utility.set_location('Entering:'||l_proc, 5);
1367 end if;
1368
1369 select Type into l_type from ota_booking_status_types where booking_status_type_id=l_booking_status_type_id;
1370
1371 --create frm_notif_subscriber record for enrollment_status of 'P' or 'A'.
1372 if l_type = 'P' or l_type = 'A' then
1373 OPEN csr_forums_for_class;
1374 FETCH csr_forums_for_class into v_forum_id, v_business_group_id;
1375
1376 LOOP
1377 Exit When csr_forums_for_class%notfound OR csr_forums_for_class%notfound is null;
1378
1379 ota_fns_ins.ins
1380 ( p_effective_date => l_effective_date
1381 ,p_business_group_id => v_business_group_id
1382 ,p_forum_id => v_forum_id
1383 ,p_person_id => l_person_id
1384 ,p_contact_id => l_contact_id
1385 ,p_object_version_number => l_dummy
1386 );
1387
1388
1389 FETCH csr_forums_for_class into v_forum_id, v_business_group_id;
1390 End Loop;
1391 Close csr_forums_for_class;
1392 end if;
1393 --
1394 if g_debug then
1395 hr_utility.set_location('Leaving:'||l_proc, 10);
1396 end if;
1397 --
1398 /* if therealready exists a frm notif record, and we try to create a new one,
1399 an exception will be thrown which gets caught here.. We ignore the exception and return*/
1400
1401 exception
1402 when OTHERS then
1403 NULL;
1404
1405 --
1406 end createForumNotification;
1407 --
1408 -- ----------------------------------------------------------------------------
1409 -- |--------------------------< get_daemon_type >---------------------------|
1410 -- ----------------------------------------------------------------------------
1411 --
1412 -- PRIVATE
1413 -- Description: Fetches the daemon type for a cancelled enrollment
1414 --
1415 --
1416 -- Added for bug#4654530
1417 FUNCTION get_daemon_type(p_booking_id IN NUMBER)
1418 RETURN VARCHAR2
1419 IS
1420 l_hours_until_class_starts NUMBER;
1421 l_auto_waitlist_days NUMBER;
1422 l_daemon_type VARCHAR2(9) := NULL;
1423
1424 CURSOR csr_get_class_details IS
1425 SELECT evt.course_start_time
1426 ,evt.course_start_date
1427 ,evt.event_id
1428 FROM ota_events evt,
1429 ota_delegate_bookings tdb
1430 WHERE tdb.event_id = evt.event_id
1431 AND tdb.booking_id = p_booking_id;
1432
1433 CURSOR csr_get_waitlist_count(p_event_id NUMBER) IS
1434 SELECT 1
1435 FROM ota_delegate_bookings tdb
1436 ,ota_booking_status_types bst
1437 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1438 AND bst.type = 'W'
1439 AND tdb.event_id = p_event_id;
1440
1441 l_course_start_time OTA_EVENTS.course_start_time%TYPE;
1442 l_course_start_date OTA_EVENTS.course_start_date%TYPE;
1443 l_event_id OTA_EVENTS.event_id%TYPE;
1444 l_waitlist_count NUMBER;
1445
1446 BEGIN
1447 OPEN csr_get_class_details;
1448 FETCH csr_get_class_details INTO l_course_start_time, l_course_start_date, l_event_id;
1449 CLOSE csr_get_class_details;
1450 --bug# 5231470 first date format changed from DD-MON-YYYY to DD/MM/YYYY
1451 l_hours_until_class_starts := 24*(to_date(to_char(l_course_start_date, 'DD/MM/YYYY')
1452 ||''||l_course_start_time, 'DD/MM/YYYYHH24:MI') - SYSDATE);
1453
1454 l_auto_waitlist_days := TO_NUMBER(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
1455 --
1456 OPEN csr_get_waitlist_count(l_event_id);
1457 FETCH csr_get_waitlist_count INTO l_waitlist_count;
1458 IF (csr_get_waitlist_count%FOUND)
1459 AND (l_hours_until_class_starts >= l_auto_waitlist_days) THEN
1460 l_daemon_type := 'W';
1461 ELSE
1462 l_daemon_type := NULL;
1463 END IF;
1464 CLOSE csr_get_waitlist_count;
1465 RETURN l_daemon_type;
1466
1467 END get_daemon_type;
1468 --
1469 -- ----------------------------------------------------------------------------
1470 -- |-------------------------< update_delegate_booking >-----------------------|
1471 -- ----------------------------------------------------------------------------
1472 --
1473 procedure update_delegate_booking
1474 (
1475 p_validate in boolean,
1476 p_effective_date in date,
1477 p_booking_id in number,
1478 p_booking_status_type_id in number,
1479 p_delegate_person_id in number,
1480 p_contact_id in number,
1481 p_business_group_id in number,
1482 p_event_id in number,
1483 p_customer_id in number,
1484 p_authorizer_person_id in number,
1485 p_date_booking_placed in date ,
1486 p_corespondent in varchar2,
1487 p_internal_booking_flag in varchar2,
1488 p_number_of_places in number,
1489 p_object_version_number in out nocopy number,
1490 p_administrator in number,
1491 p_booking_priority in varchar2,
1492 p_comments in varchar2,
1493 p_contact_address_id in number,
1494 p_delegate_contact_phone in varchar2,
1495 p_delegate_contact_fax in varchar2,
1496 p_third_party_customer_id in number,
1497 p_third_party_contact_id in number,
1498 p_third_party_address_id in number,
1499 p_third_party_contact_phone in varchar2,
1500 p_third_party_contact_fax in varchar2,
1501 p_date_status_changed in date ,
1502 p_status_change_comments in varchar2,
1503 p_failure_reason in varchar2,
1504 p_attendance_result in varchar2,
1505 p_language_id in number,
1506 p_source_of_booking in varchar2,
1507 p_special_booking_instructions in varchar2,
1508 p_successful_attendance_flag in varchar2,
1509 p_tdb_information_category in varchar2,
1510 p_tdb_information1 in varchar2,
1511 p_tdb_information2 in varchar2,
1512 p_tdb_information3 in varchar2,
1513 p_tdb_information4 in varchar2,
1514 p_tdb_information5 in varchar2,
1515 p_tdb_information6 in varchar2,
1516 p_tdb_information7 in varchar2,
1517 p_tdb_information8 in varchar2,
1518 p_tdb_information9 in varchar2,
1519 p_tdb_information10 in varchar2,
1520 p_tdb_information11 in varchar2,
1521 p_tdb_information12 in varchar2,
1522 p_tdb_information13 in varchar2,
1523 p_tdb_information14 in varchar2,
1524 p_tdb_information15 in varchar2,
1525 p_tdb_information16 in varchar2,
1526 p_tdb_information17 in varchar2,
1527 p_tdb_information18 in varchar2,
1528 p_tdb_information19 in varchar2,
1529 p_tdb_information20 in varchar2,
1530 p_update_finance_line in varchar2,
1531 p_tfl_object_version_number in out nocopy number,
1532 p_finance_header_id in number,
1533 p_finance_line_id in out nocopy number,
1534 p_standard_amount in number,
1535 p_unitary_amount in number,
1536 p_money_amount in number,
1537 p_currency_code in varchar2,
1538 p_booking_deal_type in varchar2,
1539 p_booking_deal_id in number,
1540 p_enrollment_type in varchar2,
1541 p_organization_id in number,
1542 p_sponsor_person_id in number,
1543 p_sponsor_assignment_id in number,
1544 p_person_address_id in number,
1545 p_delegate_assignment_id in number,
1546 p_delegate_contact_id in number,
1547 p_delegate_contact_email in varchar2,
1548 p_third_party_email in varchar2,
1549 p_person_address_type in varchar2,
1550 p_line_id in number,
1551 p_org_id in number,
1552 p_daemon_flag in varchar2,
1553 p_daemon_type in varchar2,
1554 p_old_event_id in number,
1555 p_quote_line_id in number,
1556 p_interface_source in varchar2,
1557 p_total_training_time in varchar2,
1558 p_content_player_status in varchar2,
1559 p_score in number,
1560 p_completed_content in number,
1561 p_total_content in number,
1562 p_booking_justification_id in number,
1563 p_is_history_flag in varchar2
1564 ,p_override_prerequisites in varchar2
1565 ,p_override_learner_access in varchar2
1566 ,p_source_cancel in varchar2
1567 ) is
1568 --
1569 -- Declare cursors and local variables
1570 --
1571 l_proc varchar2(72) := g_package||' update_delegate_booking ';
1572 l_object_version_number number := p_object_version_number;
1573 l_effective_date date;
1574 l_date_booking_placed date;
1575 l_date_status_changed date;
1576 l_tfl_object_version_number number := p_tfl_object_version_number ;
1577 l_finance_line_id number := p_finance_line_id ;
1578
1579 l_status_type_id_changed boolean;
1580 --Added for Bug#4106893
1581 l_event_id_changed boolean := false;
1582 l_person_id_changed boolean := false;
1583 l_contact_id_changed boolean := false;
1584
1585 l_cancel_finance_line boolean;
1586 l_event_rec ota_evt_shd.g_rec_type;
1587 l_event_exists boolean;
1588 -- Bug 2982183
1589 l_person_id number;
1590 -- Bug 2982183
1591 --Bug 2359495
1592 l_status_change_comments ota_booking_status_histories.comments%TYPE;
1593 --Bug 2359495
1594
1595 l_lp_enrollment_ids varchar2(4000);
1596 l_cert_prd_enrollment_ids varchar2(4000);
1597 l_item_key wf_items.item_key%type;
1598
1599 l_type ota_booking_status_types.type%type;
1600 --
1601 l_daemon_type VARCHAR2(30) := p_daemon_type;
1602 l_daemon_flag VARCHAR2(30) := p_daemon_flag;
1603
1604 --bug 603768 changes starts
1605 l_hours_until_class_starts NUMBER := 0;
1606 l_sysdate VARCHAR2(30);
1607 l_course_start_date ota_events.course_start_date%TYPE;
1608 l_course_start_time ota_events.course_start_time%TYPE;
1609 l_event_title ota_events.title%TYPE;
1610 l_old_event_id ota_delegate_bookings.event_id%TYPE;
1611 l_owner_id ota_events.owner_id%TYPE;
1612 l_username fnd_user.user_name%TYPE;
1613 l_auto_waitlist_days NUMBER;
1614 l_auto_waitlist varchar2(1) := 'N';
1615 l_waitlist_size NUMBER := 0;
1616
1617
1618 CURSOR event_csr(p_old_event_id ota_events.event_id%TYPE) IS
1619 SELECT oet.title,oe.course_start_date,oe.course_start_time,oe.owner_id
1620 FROM ota_events_tl oet, ota_events oe
1621 WHERE oet.event_id = oe.event_id
1622 AND oe.event_id = p_old_event_id
1623 AND oet.language = userenv('LANG');
1624
1625
1626 CURSOR sys_date_csr IS
1627 SELECT to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')
1628 FROM dual;
1629
1630 CURSOR fnduser_csr(l_owner_id ota_events.owner_id%TYPE) IS
1631 SELECT user_name
1632 FROM fnd_user
1633 WHERE employee_id = l_owner_id
1634 AND trunc(sysdate) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(sysdate)+1);
1635 --bug 603768 changes ends
1636
1637 --Bug6801749:ANY CHANGE BY ADMIN TO ENROLLMENT CAUSED DATE_STATUS_CHANGED UPDATE
1638 l_booking_status_type_changed boolean := false;
1639 l_existing_booking_status_id ota_delegate_bookings.booking_status_type_id%TYPE;
1640 l_new_booking_status_type_id ota_delegate_bookings.booking_status_type_id%TYPE := p_booking_status_type_id;
1641
1642 CURSOR csr_get_cur_booking_status IS
1643 SELECT booking_status_type_id
1644 FROM ota_delegate_bookings
1645 WHERE booking_id = p_booking_id;
1646
1647 Cursor is_contact
1648 is
1649 Select contact_id,delegate_contact_id from
1650 Ota_delegate_bookings
1651 where booking_id= p_booking_id;
1652
1653 l_delegate_contact_id number(15);
1654 l_contact_id number(15);
1655 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
1656 hr_dflex_utility.l_ignore_dfcode_varray();
1657 l_ignore_dff_validation varchar2(1);
1658
1659 Cursor chk_for_comp_upd
1660 is
1661 select ocu.online_flag , off.Learning_object_id from ota_category_usages ocu,
1662 ota_offerings off , ota_events oev
1663 where ocu.category_usage_id = off.delivery_mode_id
1664 and off.offering_id = oev.parent_offering_id
1665 and oev.event_id = p_event_id;
1666
1667 l_comp_upd varchar2(1000) :='MoveToHistoryImage';
1668 l_on_flag varchar2(100);
1669 l_LO_id ota_offerings.Learning_object_id%type;
1670
1671 cursor get_status_info is
1672 select bst.Type
1673 from ota_booking_status_types bst, ota_delegate_bookings tdb
1674 where bst.booking_status_type_id= tdb.booking_status_type_id
1675 and tdb.booking_id = p_booking_id;
1676
1677 l_enroll_type varchar2(30);
1678 l_incoming_status_type varchar2(30);
1679
1680 l_customer_id_changed boolean;
1681
1682 l_organization_id_changed boolean;
1683 l_delegate_person_id_changed boolean;
1684 l_delegate_asg_changed boolean;
1685
1686 l_new_event_id ota_delegate_bookings.event_id%TYPE := p_event_id;
1687 l_new_customer_id ota_delegate_bookings.customer_id%TYPE := p_customer_id;
1688 l_new_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE := p_delegate_contact_id;
1689 l_new_organization_id ota_delegate_bookings.organization_id%TYPE := p_organization_id;
1690 l_new_del_asg_id ota_delegate_bookings.delegate_assignment_id%TYPE := p_delegate_assignment_id;
1691 l_new_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE := p_delegate_person_id;
1692
1693 CURSOR csr_get_enr_details IS
1694 SELECT event_id, customer_id, organization_id,
1695 delegate_person_id, delegate_assignment_id,
1696 delegate_contact_id
1697 FROM ota_delegate_bookings
1698 WHERE booking_id = p_booking_id;
1699
1700
1701 l_enr_details_rec csr_get_enr_details%ROWTYPE;
1702
1703 l_old_booking_status varchar2(30);
1704 l_evt_status_chg_comments varchar2(1000) := fnd_message.get_string('OTA','OTA_13523_TDB_STATUS_COMMENTS');
1705
1706 CURSOR c_get_fin_line_status IS
1707 SELECT cancelled_flag
1708 FROM ota_finance_lines
1709 WHERE finance_line_id = p_finance_line_id;
1710
1711 l_cancelled_flag ota_finance_lines.cancelled_flag%TYPE;
1712 --Bug 6683076
1713 Cursor c_eval_info is
1714 select evt_eval.evaluation_id evt_eval_id,decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
1715 decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag, --bug 7184369,6935364,7174996
1716 act_eval.evaluation_id act_eval_id
1717 from ota_evaluations evt_eval,
1718 ota_evaluations act_eval,
1719 ota_events evt
1720 where evt_eval.object_id(+) = evt.event_id
1721 and (evt_eval.object_type is null or evt_eval.object_type = 'E')
1722 and act_eval.object_id(+) = evt.activity_version_id
1723 and (act_eval.object_type is null or act_eval.object_type = 'A')
1724 and evt.event_id = p_event_id
1725 and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --Bug7174996
1726
1727 l_eval_mand varchar2(1);
1728 l_evt_eval_id ota_tests.test_id%type;
1729 l_act_eval_id ota_tests.test_id%type;
1730 l_temp number;
1731
1732 Cursor c_attempt_info(l_user_id ota_attempts.user_id%type,l_user_type ota_attempts.user_type%type) is
1733 select 1
1734 from ota_attempts
1735 where event_id = p_event_id
1736 and test_id = l_evt_eval_id
1737 and user_id = l_user_id
1738 and user_type = l_user_type;
1739
1740 Cursor c_contact_user_id is
1741 select rel.subject_id
1742 from hz_cust_account_roles acct_role,
1743 hz_relationships rel,
1744 hz_cust_accounts role_acct
1745 where acct_role.party_id = rel.party_id
1746 and acct_role.role_type = 'CONTACT'
1747 and acct_role.cust_account_id = role_acct.cust_account_id
1748 and role_acct.party_id = rel.object_id
1749 and rel.subject_table_name = 'HZ_PARTIES'
1750 and rel.object_table_name = 'HZ_PARTIES'
1751 and acct_role.cust_account_role_id = nvl(p_delegate_contact_id,p_contact_id);
1752
1753 l_contact_user_id ota_attempts.user_id%type;
1754
1755
1756 begin
1757 hr_utility.set_location('Entering:'|| l_proc, 10);
1758 --
1759 -- Issue a savepoint
1760 --
1761 savepoint update_delegate_booking ;
1762 --
1763 -- Truncate the time portion from all IN date parameters
1764 --
1765 l_effective_date := trunc(p_effective_date);
1766 --l_date_booking_placed := trunc(p_date_booking_placed);
1767 l_date_booking_placed := p_date_booking_placed;
1768 l_date_status_changed := trunc(p_date_status_changed);
1769 --
1770
1771 -- Call Before Process User Hook
1772 --
1773 begin
1774 ota_delegate_booking_bk2.update_delegate_booking_b
1775 (
1776 p_effective_date => l_effective_date ,
1777 p_booking_id => p_booking_id ,
1778 p_booking_status_type_id => p_booking_status_type_id ,
1779 p_delegate_person_id => p_delegate_person_id ,
1780 p_contact_id => p_contact_id ,
1781 p_business_group_id => p_business_group_id ,
1782 p_event_id => p_event_id ,
1783 p_customer_id => p_customer_id ,
1784 p_authorizer_person_id => p_authorizer_person_id ,
1785 p_date_booking_placed => l_date_booking_placed ,
1786 p_corespondent => p_corespondent ,
1787 p_internal_booking_flag => p_internal_booking_flag ,
1788 p_number_of_places => p_number_of_places ,
1789 p_object_version_number => p_object_version_number ,
1790 p_administrator => p_administrator ,
1791 p_booking_priority => p_booking_priority ,
1792 p_comments => p_comments ,
1793 p_contact_address_id => p_contact_address_id ,
1794 p_delegate_contact_phone => p_delegate_contact_phone ,
1795 p_delegate_contact_fax => p_delegate_contact_fax ,
1796 p_third_party_customer_id => p_third_party_customer_id ,
1797 p_third_party_contact_id => p_third_party_contact_id ,
1798 p_third_party_address_id => p_third_party_address_id ,
1799 p_third_party_contact_phone => p_third_party_contact_phone ,
1800 p_third_party_contact_fax => p_third_party_contact_fax ,
1801 p_date_status_changed => l_date_status_changed ,
1802 p_status_change_comments => p_status_change_comments ,
1803 p_failure_reason => p_failure_reason ,
1804 p_attendance_result => p_attendance_result ,
1805 p_language_id => p_language_id ,
1806 p_source_of_booking => p_source_of_booking ,
1807 p_special_booking_instructions =>p_special_booking_instructions ,
1808 p_successful_attendance_flag => p_successful_attendance_flag ,
1809 p_tdb_information_category => p_tdb_information_category ,
1810 p_tdb_information1 => p_tdb_information1 ,
1811 p_tdb_information2 => p_tdb_information2 ,
1812 p_tdb_information3 => p_tdb_information3 ,
1813 p_tdb_information4 => p_tdb_information4 ,
1814 p_tdb_information5 => p_tdb_information5 ,
1815 p_tdb_information6 => p_tdb_information6 ,
1816 p_tdb_information7 => p_tdb_information7 ,
1817 p_tdb_information8 => p_tdb_information8 ,
1818 p_tdb_information9 => p_tdb_information9 ,
1819 p_tdb_information10 => p_tdb_information10 ,
1820 p_tdb_information11 => p_tdb_information11 ,
1821 p_tdb_information12 => p_tdb_information12 ,
1822 p_tdb_information13 => p_tdb_information13 ,
1823 p_tdb_information14 => p_tdb_information14 ,
1824 p_tdb_information15 => p_tdb_information15 ,
1825 p_tdb_information16 => p_tdb_information16 ,
1826 p_tdb_information17 => p_tdb_information17 ,
1827 p_tdb_information18 => p_tdb_information18 ,
1828 p_tdb_information19 => p_tdb_information19 ,
1829 p_tdb_information20 => p_tdb_information20 ,
1830 p_update_finance_line => p_update_finance_line ,
1831 p_tfl_object_version_number => p_tfl_object_version_number ,
1832 p_finance_header_id => p_finance_header_id ,
1833 p_finance_line_id => l_finance_line_id ,
1834 p_standard_amount => p_standard_amount ,
1835 p_unitary_amount => p_unitary_amount ,
1836 p_money_amount => p_money_amount ,
1837 p_currency_code => p_currency_code ,
1838 p_booking_deal_type => p_booking_deal_type ,
1839 p_booking_deal_id => p_booking_deal_id ,
1840 p_enrollment_type => p_enrollment_type ,
1841 p_organization_id => p_organization_id ,
1842 p_sponsor_person_id => p_sponsor_person_id ,
1843 p_sponsor_assignment_id => p_sponsor_assignment_id ,
1844 p_person_address_id => p_person_address_id ,
1845 p_delegate_assignment_id => p_delegate_assignment_id ,
1846 p_delegate_contact_id => p_delegate_contact_id ,
1847 p_delegate_contact_email => p_delegate_contact_email ,
1848 p_third_party_email => p_third_party_email ,
1849 p_person_address_type => p_person_address_type ,
1850 p_line_id => p_line_id ,
1851 p_org_id => p_org_id ,
1852 p_daemon_flag => p_daemon_flag ,
1853 p_daemon_type => p_daemon_type ,
1854 p_old_event_id => p_old_event_id ,
1855 p_quote_line_id => p_quote_line_id ,
1856 p_interface_source => p_interface_source ,
1857 p_total_training_time => p_total_training_time ,
1858 p_content_player_status => p_content_player_status ,
1859 p_score => p_score ,
1860 p_completed_content => p_completed_content ,
1861 p_total_content => p_total_content,
1862 p_booking_justification_id => p_booking_justification_id,
1863 p_is_history_flag => p_is_history_flag
1864 );
1865
1866
1867 exception
1868 when hr_api.cannot_find_prog_unit then
1869 hr_api.cannot_find_prog_unit_error
1870 (p_module_name => 'update_delegate_booking_b'
1871 ,p_hook_type => 'BP'
1872 );
1873 end;
1874 --
1875 -- Validation in addition to Row Handlers
1876 --
1877 --
1878 -- Lock Enrollment (Bug 2468167)
1879 --
1880 ota_tdb_shd.lck(p_booking_id,p_object_version_number);
1881 --
1882
1883 -- Prerequisite Validation Code
1884 -- Can be overridden if p_override_prerequisites parameter is 'Y'
1885 -- get booking status type
1886 ota_utility.get_booking_status_type(p_status_type_id=>p_booking_status_type_id,
1887 p_type => l_incoming_status_type);
1888
1889 IF ( p_override_prerequisites = 'N' ) Then
1890 --Call local method
1891 chk_mandatory_prereqs(p_delegate_person_id, p_delegate_contact_id, p_customer_id, p_event_id, p_booking_status_type_id);
1892 END IF;
1893
1894 IF p_override_learner_access <> 'Y' THEN
1895 OPEN csr_get_enr_details;
1896 FETCH csr_get_enr_details INTO l_enr_details_rec;
1897 CLOSE csr_get_enr_details;
1898
1899 -- Modified for bug#4681165
1900 IF l_new_event_id = hr_api.g_number THEN l_new_event_id := l_enr_details_rec.event_id; END IF;
1901 IF l_new_customer_id = hr_api.g_number THEN l_new_customer_id := l_enr_details_rec.customer_id; END IF;
1902 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;
1903 IF l_new_organization_id = hr_api.g_number THEN l_new_organization_id := l_enr_details_rec.organization_id; END IF;
1904 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;
1905 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;
1906
1907
1908 l_event_id_changed := ota_general.value_changed( l_enr_details_rec.event_id, l_new_event_id);
1909 l_customer_id_changed := ota_general.value_changed( l_enr_details_rec.customer_id, l_new_customer_id);
1910 l_organization_id_changed := ota_general.value_changed( l_enr_details_rec.organization_id, l_new_organization_id);
1911 l_delegate_person_id_changed := ota_general.value_changed( l_enr_details_rec.delegate_person_id, l_new_delegate_person_id);
1912 l_delegate_asg_changed := ota_general.value_changed( l_enr_details_rec.delegate_assignment_id, l_new_del_asg_id);
1913
1914 IF l_event_id_changed or
1915 l_customer_id_changed or
1916 l_organization_id_changed or
1917 l_delegate_person_id_changed or
1918 l_delegate_asg_changed then
1919 --
1920 -- check that the delegate is eligible to be booked on to the event
1921 --
1922 ota_tdb_bus.check_delegate_eligible(
1923 p_event_id => l_new_event_id
1924 ,p_customer_id => l_new_customer_id
1925 ,p_delegate_contact_id => l_new_delegate_contact_id
1926 ,p_organization_id => l_new_organization_id
1927 ,p_delegate_person_id => l_new_delegate_person_id
1928 ,p_delegate_assignment_id => l_new_del_asg_id);
1929 END IF;
1930 END IF;
1931
1932 --Bug6801749
1933 OPEN csr_get_cur_booking_status;
1934 FETCH csr_get_cur_booking_status INTO l_existing_booking_status_id;
1935 CLOSE csr_get_cur_booking_status;
1936
1937 IF l_new_booking_status_type_id = hr_api.g_number THEN
1938 l_new_booking_status_type_id := l_existing_booking_status_id;
1939 END IF;
1940 l_booking_status_type_changed := ota_general.value_changed(l_existing_booking_status_id,l_new_booking_status_type_id);
1941
1942 IF l_booking_status_type_changed then
1943 l_date_status_changed := trunc(sysdate);
1944 END IF;
1945
1946 -- Added for bug#4654530
1947 IF nvl(p_status_change_comments,hr_api.g_varchar2) <> l_evt_status_chg_comments THEN
1948
1949 OPEN get_status_info;
1950 FETCH get_status_info INTO l_old_booking_status;
1951 CLOSE get_status_info;
1952
1953 IF l_incoming_status_type = 'C'
1954 AND l_old_booking_status <> 'C'
1955 AND l_daemon_type IS NULL THEN
1956 l_daemon_type := get_daemon_type(p_booking_id);
1957 IF l_daemon_type IS NOT NULL THEN
1958 l_daemon_flag := 'Y';
1959 ELSE
1960 l_daemon_flag := 'N';
1961 END IF;
1962 END IF;
1963
1964 IF l_incoming_status_type <> 'C'
1965 AND l_old_booking_status = 'C' THEN
1966 l_daemon_flag := 'N';
1967 l_daemon_type := NULL;
1968 END IF;
1969
1970 END IF;
1971
1972 -- Lock the Event
1973 --
1974 ota_evt_bus2.lock_event(p_event_id);
1975
1976 -- get booking_status type to fire ntf process
1977 OPEN get_status_info;
1978 FETCH get_status_info INTO l_enroll_type;
1979 CLOSE get_status_info;
1980 --
1981 -- Get Event record
1982 --
1983 ota_evt_shd.get_event_details (p_event_id,
1984 l_event_rec,
1985 l_event_exists);
1986
1987 -- Ignore Enrollment Dff Validation for some cases
1988 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
1989 l_add_struct_d.extend(1);
1990 l_add_struct_d(l_add_struct_d.count) := 'OTA_DELEGATE_BOOKINGS';
1991 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
1992 l_ignore_dff_validation := 'Y';
1993 ELSE
1994 l_ignore_dff_validation := 'N';
1995 END IF;
1996
1997 --
1998 -- Validation Check on booking / event statuses
1999 --
2000 Check_Status_Change(p_event_id
2001 ,p_booking_Status_type_id
2002 ,l_event_rec.event_status
2003 ,p_number_of_places
2004 ,l_event_rec.maximum_attendees);
2005
2006 --Added for 7046809.
2007 open chk_for_comp_upd;
2008 fetch chk_for_comp_upd into l_on_flag,l_LO_id ;
2009 close chk_for_comp_upd;
2010
2011 open c_eval_info;
2012 fetch c_eval_info into l_evt_eval_id,l_eval_mand,l_act_eval_id;
2013 close c_eval_info;
2014 l_evt_eval_id := nvl(l_evt_eval_id,l_act_eval_id);
2015 if l_incoming_status_type = 'E' and l_booking_status_type_changed and l_on_flag = 'N' then
2016 if p_delegate_contact_id is null and p_contact_id is null then
2017 open c_attempt_info(p_delegate_person_id,'E');
2018 fetch c_attempt_info into l_temp;
2019 close c_attempt_info;
2020 else
2021 open c_contact_user_id;
2022 fetch c_contact_user_id into l_contact_user_id;
2023 close c_contact_user_id;
2024 open c_attempt_info(l_contact_user_id,'C');
2025 fetch c_attempt_info into l_temp;
2026 close c_attempt_info;
2027 end if;
2028 if l_temp=1 then
2029 fnd_message.set_name('OTA','OTA_467126_STAT_CHG_PE_ERR');
2030 fnd_message.raise_error;
2031 end if;
2032 end if;
2033 --end of changes for 7046809.
2034
2035 --
2036 --Bug 2359495
2037 IF ( p_status_change_comments IS NULL or p_status_change_comments = hr_api.g_varchar2) THEN --Bug 5586486
2038 l_status_change_comments := hr_general_utilities.get_lookup_meaning
2039 ('ENROLMENT_STATUS_REASON',
2040 'A');
2041 ELSE
2042 l_status_change_comments := p_status_change_comments;
2043 END IF;
2044
2045 --
2046 -- Process Logic
2047 --
2048 ota_tdb_upd.upd
2049 (
2050 p_booking_id,
2051 p_booking_status_type_id,
2052 p_delegate_person_id,
2053 p_contact_id,
2054 p_business_group_id,
2055 p_event_id,
2056 p_customer_id,
2057 p_authorizer_person_id,
2058 p_date_booking_placed,
2059 p_corespondent,
2060 p_internal_booking_flag,
2061 p_number_of_places,
2062 p_object_version_number,
2063 p_administrator,
2064 p_booking_priority,
2065 p_comments,
2066 p_contact_address_id,
2067 p_delegate_contact_phone,
2068 p_delegate_contact_fax,
2069 p_third_party_customer_id,
2070 p_third_party_contact_id,
2071 p_third_party_address_id,
2072 p_third_party_contact_phone,
2073 p_third_party_contact_fax,
2074 l_date_status_changed, --p_date_status_changed,Bug6801749
2075 l_status_change_comments, -- p_status_change_comments, Bug 2359495
2076 p_failure_reason,
2077 p_attendance_result,
2078 p_language_id,
2079 p_source_of_booking,
2080 p_special_booking_instructions,
2081 p_successful_attendance_flag,
2082 p_tdb_information_category,
2083 p_tdb_information1,
2084 p_tdb_information2,
2085 p_tdb_information3,
2086 p_tdb_information4,
2087 p_tdb_information5,
2088 p_tdb_information6,
2089 p_tdb_information7,
2090 p_tdb_information8,
2091 p_tdb_information9,
2092 p_tdb_information10,
2093 p_tdb_information11,
2094 p_tdb_information12,
2095 p_tdb_information13,
2096 p_tdb_information14,
2097 p_tdb_information15,
2098 p_tdb_information16,
2099 p_tdb_information17,
2100 p_tdb_information18,
2101 p_tdb_information19,
2102 p_tdb_information20,
2103 p_update_finance_line,
2104 p_tfl_object_version_number,
2105 p_finance_header_id,
2106 p_finance_line_id,
2107 p_standard_amount,
2108 p_unitary_amount,
2109 p_money_amount,
2110 p_currency_code,
2111 p_booking_deal_type,
2112 p_booking_deal_id,
2113 p_enrollment_type,
2114 p_validate,
2115 p_organization_id,
2116 p_sponsor_person_id,
2117 p_sponsor_assignment_id,
2118 p_person_address_id,
2119 p_delegate_assignment_id,
2120 p_delegate_contact_id,
2121 p_delegate_contact_email,
2122 p_third_party_email,
2123 p_person_address_type,
2124 p_line_id,
2125 p_org_id,
2126 -- Modified for bug#4654530
2127 l_daemon_flag,
2128 l_daemon_type,
2129 -- p_daemon_flag,
2130 -- p_daemon_type,
2131 p_old_event_id,
2132 p_quote_line_id,
2133 p_interface_source,
2134 p_total_training_time,
2135 p_content_player_status,
2136 p_score,
2137 p_completed_content,
2138 p_total_content,
2139 p_booking_justification_id,
2140 p_is_history_flag
2141 );
2142
2143 --
2144 /*
2145 ota_tdb_api_upd2.update_enrollment
2146 (p_booking_id => p_booking_id ,
2147 p_booking_status_type_id => p_booking_status_type_id ,
2148 p_delegate_person_id => p_delegate_person_id ,
2149 p_contact_id => p_contact_id ,
2150 p_business_group_id => p_business_group_id ,
2151 p_event_id => p_event_id ,
2152 p_customer_id => p_customer_id ,
2153 p_authorizer_person_id => p_authorizer_person_id ,
2154 p_date_booking_placed => l_date_booking_placed ,
2155 p_corespondent => p_corespondent ,
2156 p_internal_booking_flag => p_internal_booking_flag ,
2157 p_number_of_places => p_number_of_places ,
2158 p_object_version_number => p_object_version_number ,
2159 p_administrator => p_administrator ,
2160 p_booking_priority => p_booking_priority ,
2161 p_comments => p_comments ,
2162 p_contact_address_id => p_contact_address_id ,
2163 p_delegate_contact_phone => p_delegate_contact_phone ,
2164 p_delegate_contact_fax => p_delegate_contact_fax ,
2165 p_third_party_customer_id => p_third_party_customer_id ,
2166 p_third_party_contact_id => p_third_party_contact_id ,
2167 p_third_party_address_id => p_third_party_address_id ,
2168 p_third_party_contact_phone => p_third_party_contact_phone ,
2169 p_third_party_contact_fax => p_third_party_contact_fax ,
2170 p_date_status_changed => l_date_status_changed ,
2171 p_status_change_comments => p_status_change_comments ,
2172 p_failure_reason => p_failure_reason ,
2173 p_attendance_result => p_attendance_result ,
2174 p_language_id => p_language_id ,
2175 p_source_of_booking => p_source_of_booking ,
2176 p_special_booking_instructions =>p_special_booking_instructions ,
2177 p_successful_attendance_flag => p_successful_attendance_flag ,
2178 p_tdb_information_category => p_tdb_information_category ,
2179 p_tdb_information1 => p_tdb_information1 ,
2180 p_tdb_information2 => p_tdb_information2 ,
2181 p_tdb_information3 => p_tdb_information3 ,
2182 p_tdb_information4 => p_tdb_information4 ,
2183 p_tdb_information5 => p_tdb_information5 ,
2184 p_tdb_information6 => p_tdb_information6 ,
2185 p_tdb_information7 => p_tdb_information7 ,
2186 p_tdb_information8 => p_tdb_information8 ,
2187 p_tdb_information9 => p_tdb_information9 ,
2188 p_tdb_information10 => p_tdb_information10 ,
2189 p_tdb_information11 => p_tdb_information11 ,
2190 p_tdb_information12 => p_tdb_information12 ,
2191 p_tdb_information13 => p_tdb_information13 ,
2192 p_tdb_information14 => p_tdb_information14 ,
2193 p_tdb_information15 => p_tdb_information15 ,
2194 p_tdb_information16 => p_tdb_information16 ,
2195 p_tdb_information17 => p_tdb_information17 ,
2196 p_tdb_information18 => p_tdb_information18 ,
2197 p_tdb_information19 => p_tdb_information19 ,
2198 p_tdb_information20 => p_tdb_information20 ,
2199 p_update_finance_line => p_update_finance_line ,
2200 p_tfl_object_version_number => p_tfl_object_version_number ,
2201 p_finance_header_id => p_finance_header_id ,
2202 p_currency_code => p_currency_code ,
2203 p_standard_amount => p_standard_amount ,
2204 p_unitary_amount => p_unitary_amount ,
2205 p_money_amount => p_money_amount ,
2206 p_booking_deal_id => p_booking_deal_id ,
2207 p_booking_deal_type => p_booking_deal_type ,
2208 p_finance_line_id => l_finance_line_id ,
2209 p_enrollment_type => p_enrollment_type ,
2210 p_validate => p_validate ,
2211 p_organization_id => p_organization_id ,
2212 p_sponsor_person_id => p_sponsor_person_id ,
2213 p_sponsor_assignment_id => p_sponsor_assignment_id ,
2214 p_person_address_id => p_person_address_id ,
2215 p_delegate_assignment_id => p_delegate_assignment_id ,
2216 p_delegate_contact_id => p_delegate_contact_id ,
2217 p_delegate_contact_email => p_delegate_contact_email ,
2218 p_third_party_email => p_third_party_email ,
2219 p_person_address_type => p_person_address_type ,
2220 p_line_id => p_line_id ,
2221 p_org_id => p_org_id ,
2222 p_daemon_flag => p_daemon_flag ,
2223 p_daemon_type => p_daemon_type ,
2224 p_old_event_id => p_old_event_id ,
2225 p_quote_line_id => p_quote_line_id ,
2226 p_interface_source => p_interface_source ,
2227 p_total_training_time => p_total_training_time ,
2228 p_content_player_status => p_content_player_status ,
2229 p_score => p_score ,
2230 p_completed_content => p_completed_content ,
2231 p_total_content => p_total_content,
2232 p_booking_justification_id => p_booking_justification_id,
2233 p_is_history_flag => p_is_history_flag
2234 ,p_override_prerequisites => p_override_prerequisites
2235 ,p_override_learner_access => p_override_learner_access
2236 );
2237 */
2238 --
2239 l_status_type_id_changed :=
2240 ota_general.value_changed (ota_tdb_shd.g_old_rec.booking_status_type_id,
2241 p_booking_status_type_id);
2242
2243 -- Getting the old booking status to manipulate the fourm notification records
2244 l_old_booking_status := ota_tdb_bus.booking_status_type(
2245 ota_tdb_shd.g_old_rec.booking_status_type_id);
2246
2247 OPEN is_contact;
2248 FETCH is_contact INTO l_contact_id,l_delegate_contact_id;
2249 CLOSE is_contact;
2250
2251 IF (p_delegate_person_id = hr_api.g_number) THEN
2252 select delegate_person_id into l_person_id from ota_delegate_bookings
2253 where booking_id = p_booking_id;
2254 else l_person_id := p_delegate_person_id;
2255 END IF;
2256
2257 --
2258 -- Added by dbatra
2259 -- this is to take care of granting competencies attached to LP which are completed
2260 -- but course under it was not successfully attended intitially.
2261
2262 IF (not l_status_type_id_changed) and p_successful_attendance_flag ='Y' THEN
2263 IF l_delegate_contact_id is null and l_contact_id is null THEN
2264
2265 ota_lrng_path_util.start_comp_proc_success_attnd(p_person_id =>l_person_id
2266 ,p_event_id => p_event_id);
2267
2268 ota_cpe_util.crt_comp_upd_succ_att(p_person_id =>l_person_id
2269 ,p_event_id => p_event_id);
2270
2271 END IF;
2272 END IF;
2273
2274 --
2275 IF l_status_type_id_changed or p_successful_attendance_flag ='Y' THEN
2276 --
2277 -- Added by dbatra for training plan
2278 -- Bug 2982183
2279 IF l_delegate_contact_id is null and l_contact_id is null and l_status_type_id_changed THEN
2280
2281 /* bug 3795299
2282 ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
2283 p_person_id => l_person_id,
2284 -- Added for Bug#3479186
2285 p_contact_id => NULL,
2286 p_learning_path_ids => l_learning_path_ids);
2287 ELSE-- Added for Bug#3479186
2288 ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
2289 p_person_id => NULL,
2290 p_contact_id => l_delegate_contact_id,
2291 p_learning_path_ids => l_learning_path_ids);
2292 */
2293
2294 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
2295 p_person_id => l_person_id,
2296 p_contact_id => null,
2297 p_lp_enrollment_ids => l_lp_enrollment_ids);
2298
2299 -- update any associated cert member enrollment statuses
2300 ota_cme_util.update_cme_status(p_event_id => p_event_id,
2301 p_person_id => l_person_id,
2302 p_contact_id => null,
2303 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2304
2305 ELSIF l_delegate_contact_id IS NOT NULL THEN
2306 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
2307 p_person_id => null,
2308 p_contact_id => l_delegate_contact_id,
2309 p_lp_enrollment_ids => l_lp_enrollment_ids);
2310 -- update any associated cert member enrollment statuses
2311 ota_cme_util.update_cme_status(p_event_id => p_event_id,
2312 p_person_id => null,
2313 p_contact_id => l_delegate_contact_id,
2314 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2315
2316 END IF; -- contact_id
2317
2318 select Type into l_type from ota_booking_status_types where booking_status_type_id=p_booking_status_type_id;
2319
2320 IF l_type='A' and l_delegate_contact_id is null and l_contact_id IS NULL THEN
2321
2322 -- check whether class is online or not
2323 /* OPEN chk_for_comp_upd;
2324 FETCH chk_for_comp_upd INTO l_on_flag,l_LO_id;
2325 CLOSE chk_for_comp_upd;
2326
2327 if l_on_flag='Y' then
2328 -- check whether online class is succesfully completed or not
2329 l_comp_upd := ota_lo_utility.get_history_button(p_user_id => l_person_id,
2330 p_lo_id => l_LO_id ,
2331 p_event_id => p_event_id,
2332 p_booking_id => p_booking_id);
2333 elsif p_successful_attendance_flag <>'Y' then
2334 l_comp_upd := null;
2335 end if; -- flag */
2336
2337 IF p_successful_attendance_flag = 'Y' THEN
2338 ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
2339 p_itemtype =>'HRSSA',
2340 p_person_id => l_person_id,
2341 p_eventid =>p_event_id,
2342 p_learningpath_ids => null,
2343 p_itemkey =>l_item_key);
2344
2345 END IF;
2346
2347
2348 END IF;
2349
2350 -- fire learner enrollment notification --Bug#7111940
2351 if (p_contact_id is null
2352 and p_delegate_contact_id is null
2353 --and nvl(p_book_from,'-1') <> 'AME'
2354 and l_event_rec.event_type in ('SCHEDULED','SELFPACED')
2355 and l_type = 'P') then
2356 -- call learner ntf process
2357 OTA_LRNR_ENROLL_UNENROLL_WF.learner_enrollment(p_process => 'OTA_LNR_TRNG_APPROVAL_JSP_PRC',
2358 p_itemtype => 'HRSSA',
2359 p_person_id => p_delegate_person_id,
2360 p_eventid => p_event_id,
2361 p_booking_id => p_booking_id);
2362
2363 end if;
2364
2365 --- send ntf to waitlisted learner
2366
2367 IF l_enroll_type = 'W' and l_type = 'P'
2368 and l_delegate_contact_id is null and l_contact_id is null then
2369
2370 OTA_INITIALIZATION_WF.initialize_wf(p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC',
2371 p_item_type => 'OTWF',
2372 p_eventid => p_event_id,
2373 p_person_id => l_person_id,
2374 p_event_fired => 'ENROLL_STATUS_CHNG');
2375
2376 END IF;
2377
2378 -- send cancel enrollment ntf
2379
2380 IF l_type ='C' and l_delegate_contact_id is null and l_contact_id IS NULL
2381 and nvl(p_source_cancel,'-1') <> 'AME'
2382 and l_event_rec.event_type in ('SCHEDULED','SELFPACED') then
2383
2384 OTA_LRNR_ENROLL_UNENROLL_WF.learner_unenrollment(p_process => 'OTA_LNR_TRNG_CANCEL_JSP_PRC',
2385 p_itemtype => 'HRSSA',
2386 p_person_id => l_person_id,
2387 p_eventid => p_event_id);
2388
2389 END IF;
2390
2391 -- Bug 2982183
2392 --Bug 5452795
2393 if l_status_type_id_changed then
2394 ota_tdb_bus.maintain_status_history
2395 (p_booking_status_type_id,
2396 l_date_status_changed, --p_date_status_changed,Bug6801749
2397 p_administrator,
2398 l_status_change_comments, --p_status_change_comments,Bug 2359495
2399 p_booking_id,
2400 ota_tdb_shd.g_old_rec.date_status_changed,
2401 ota_tdb_shd.g_old_rec.booking_status_type_id,
2402 ota_tdb_shd.g_created_by,
2403 p_date_booking_placed);
2404 end if;
2405 --
2406 ota_tdb_bus.ota_letter_lines
2407 (p_booking_id => p_booking_id,
2408 p_booking_status_type_id => p_booking_status_type_id,
2409 p_event_id => p_event_id,
2410 p_delegate_person_id => l_person_id);
2411 -- Modified for bug#3007934.
2412 -- p_delegate_person_id => p_delegate_person_id);
2413 ---***Added p_delegate_person_id. Bug2791524.
2414 --
2415 END IF;
2416 --
2417 --Added for Bug#4106893
2418 IF p_event_id <> hr_api.g_number
2419 AND p_event_id <> ota_tdb_shd.g_old_rec.event_id THEN
2420 l_event_id_changed:= true;
2421 END IF;
2422
2423 IF p_delegate_person_id <> hr_api.g_number
2424 AND p_delegate_person_id <> ota_tdb_shd.g_old_rec.delegate_person_id THEN
2425 l_person_id_changed := true;
2426 END IF;
2427
2428 IF p_delegate_contact_id <> hr_api.g_number
2429 AND p_delegate_contact_id <> ota_tdb_shd.g_old_rec.delegate_contact_id THEN
2430 l_contact_id_changed := true;
2431 END IF;
2432
2433 --bug 603768 changes starts
2434 --Send ntf to class owner if any enrollment has been cancelled
2435 --or deleted or class changed
2436 l_old_event_id := ota_tdb_shd.g_old_rec.event_id;
2437 l_auto_waitlist := fnd_profile.value('OTA_AUTO_WAITLIST_ACTIVE');
2438 l_waitlist_size := ota_utility.students_on_waitlist(l_old_event_id);
2439
2440 if (l_auto_waitlist = 'Y' and l_waitlist_size > 0) then
2441
2442 if(l_old_booking_status = 'P' or l_old_booking_status = 'A' ) then
2443
2444 OPEN event_csr (l_old_event_id);
2445 FETCH event_csr INTO l_event_title,l_course_start_date,l_course_start_time,l_owner_id;
2446 CLOSE event_csr;
2447
2448 if ( l_course_start_date is not null ) then
2449 l_hours_until_class_starts := 24*(to_date(to_char(l_course_start_date, 'DD-MM-YYYY')||''||l_course_start_time, 'DD/MM/YYYYHH24:MI') - SYSDATE);
2450 end if;
2451
2452 l_auto_waitlist_days := TO_NUMBER(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
2453
2454 if (l_hours_until_class_starts <= nvl(l_auto_waitlist_days,0) )then
2455
2456 OPEN sys_date_csr;
2457 FETCH sys_date_csr INTO l_sysdate;
2458 CLOSE sys_date_csr;
2459
2460 if (l_owner_id is null) then
2461 l_owner_id := fnd_profile.value('OTA_DEFAULT_EVENT_OWNER');
2462 end if;
2463
2464 OPEN fnduser_csr(l_owner_id);
2465 FETCH fnduser_csr INTO l_username;
2466 CLOSE fnduser_csr;
2467
2468 if(l_event_id_changed) then
2469
2470 OTA_INITIALIZATION_WF.MANUAL_WAITLIST(
2471 p_itemtype => 'OTWF',
2472 p_process => 'OTA_ENROLLMENT_EVENT_CHANGED',
2473 p_Event_title => l_event_title,
2474 p_event_id => l_old_event_id,
2475 p_item_key => p_booking_id ||':'||l_sysdate,
2476 p_user_name => l_username);
2477
2478 else
2479 if( l_type is not null and (l_type = 'C' or l_type = 'R') ) then
2480
2481 OTA_INITIALIZATION_WF.MANUAL_WAITLIST(
2482 p_itemtype => 'OTWF',
2483 p_process => 'OTA_MANUAL_WAITLIST',
2484 p_Event_title => l_event_title,
2485 p_event_id => l_old_event_id,
2486 p_item_key => p_booking_id ||':'||l_sysdate,
2487 p_user_name => l_username);
2488 end if;
2489 end if;
2490 end if;
2491 end if;
2492 end if;
2493 --bug 603768 changes ends
2494 --If the new enrollment status is 'C' or 'R' or 'W' then delete the forum notitifcation record
2495 IF l_type = 'C' or l_type = 'R' or l_type = 'W' then
2496 deleteForumNotification(p_event_id,l_person_id, l_delegate_contact_id);
2497 END IF;
2498
2499 --If the booking status is changed from 'C','W' or 'R' to 'P' or 'A',
2500 --then we need to create a new forum notification record.
2501 IF l_old_booking_status = 'C' or l_old_booking_status = 'W' or l_old_booking_status ='R'
2502 and l_type = 'P' or l_type = 'A' THEN
2503 IF NOT l_event_id_changed and NOT l_person_id_changed AND NOT l_contact_id_changed THEN
2504 IF l_person_id IS NOT NULL THEN
2505 createForumNotification(p_event_id,l_person_id, null, l_effective_date, p_booking_status_type_id);
2506 ELSIF l_delegate_contact_id IS NOT NULL THEN
2507 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
2508 END IF;
2509 END IF;
2510 END IF;
2511
2512 /**
2513 When the class name is changed for an enrollment, the lme update must be called
2514 twice, once for the old class and once for the new class.
2515 When the learner name is changed for an enrollment, the lme update must be called
2516 twice, once for the old learner and once for the new learner.
2517 If both the learner aswell as class associated with an enrollment are changed,
2518 update lme must be called once for old class, old learner and once for new class and new learner
2519 **/
2520 IF l_event_id_changed AND NOT l_person_id_changed AND NOT l_contact_id_changed THEN
2521 IF l_person_id IS NOT NULL THEN
2522 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2523 p_person_id => l_person_id,
2524 p_contact_id => null,
2525 p_lp_enrollment_ids => l_lp_enrollment_ids);
2526
2527 -- update any associated cert member enrollment statuses
2528 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2529 p_person_id => l_person_id,
2530 p_contact_id => null,
2531 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2532
2533 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id =>p_event_id,
2534 p_person_id => l_person_id,
2535 p_contact_id => null,
2536 p_lp_enrollment_ids => l_lp_enrollment_ids);
2537
2538 -- update any associated cert member enrollment statuses
2539 ota_cme_util.update_cme_status(p_event_id => p_event_id,
2540 p_person_id => l_person_id,
2541 p_contact_id => null,
2542 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2543
2544 --If the event has changed, the forum notification record should be deleted and created for the new event
2545 -- FRM Notification should be created only for Placed or Attended status. Not for 'C','W' or 'R'.
2546 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null);
2547 createForumNotification(p_event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
2548
2549 ELSIF l_delegate_contact_id IS NOT NULL THEN
2550 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2551 p_person_id => null,
2552 p_contact_id => l_delegate_contact_id,
2553 p_lp_enrollment_ids => l_lp_enrollment_ids);
2554
2555 -- update any associated cert member enrollment statuses
2556 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2557 p_person_id => null,
2558 p_contact_id => l_delegate_contact_id,
2559 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2560
2561 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
2562 p_person_id => null,
2563 p_contact_id => l_delegate_contact_id,
2564 p_lp_enrollment_ids => l_lp_enrollment_ids);
2565
2566 -- update any associated cert member enrollment statuses
2567 ota_cme_util.update_cme_status(p_event_id => p_event_id,
2568 p_person_id => null,
2569 p_contact_id => l_delegate_contact_id,
2570 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2571
2572 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, l_delegate_contact_id);
2573 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
2574
2575 END IF;
2576 ELSIF l_event_id_changed THEN
2577 IF l_person_id_changed THEN
2578 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2579 p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
2580 p_contact_id => null,
2581 p_lp_enrollment_ids => l_lp_enrollment_ids);
2582
2583 -- update any associated cert member enrollment statuses
2584 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2585 p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
2586 p_contact_id => null,
2587 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2588
2589 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
2590 p_person_id => l_person_id,
2591 p_contact_id => null,
2592 p_lp_enrollment_ids => l_lp_enrollment_ids);
2593
2594 -- update any associated cert member enrollment statuses
2595 ota_cme_util.update_cme_status(p_event_id => p_event_id,
2596 p_person_id => l_person_id,
2597 p_contact_id => null,
2598 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2599
2600 -- the class and the learner have change.So delete the forum record for old class and person
2601 -- and create a notification record for the new class and new person
2602 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
2603 createForumNotification(p_event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
2604
2605 ELSIF l_contact_id_changed THEN
2606
2607 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2608 p_person_id => null,
2609 p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
2610 p_lp_enrollment_ids => l_lp_enrollment_ids);
2611
2612 -- update any associated cert member enrollment statuses
2613 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2614 p_person_id => null,
2615 p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
2616 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2617
2618 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
2619 p_person_id => null,
2620 p_contact_id => p_delegate_contact_id,
2621 p_lp_enrollment_ids => l_lp_enrollment_ids);
2622
2623 -- update any associated cert member enrollment statuses
2624 ota_cme_util.update_cme_status(p_event_id => p_event_id,
2625 p_person_id => null,
2626 p_contact_id => p_delegate_contact_id,
2627 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2628
2629 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
2630 createForumNotification(p_event_id, null, l_delegate_contact_id, l_effective_date, p_booking_status_type_id);
2631
2632 END IF;
2633
2634 ELSIF l_person_id_changed THEN
2635 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2636 p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
2637 p_contact_id => null,
2638 p_lp_enrollment_ids => l_lp_enrollment_ids);
2639
2640 -- update any associated cert member enrollment statuses
2641 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2642 p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
2643 p_contact_id => null,
2644 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2645
2646 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2647 p_person_id => l_person_id,
2648 p_contact_id => null,
2649 p_lp_enrollment_ids => l_lp_enrollment_ids);
2650 -- update any associated cert member enrollment statuses
2651 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2652 p_person_id => l_person_id,
2653 p_contact_id => null,
2654 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2655
2656 -- the learner has changed.So delete the forum record for old person
2657 --and create a notification record for the new person
2658 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
2659 createForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null, l_effective_date, p_booking_status_type_id);
2660
2661 ELSIF l_contact_id_changed THEN
2662 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2663 p_person_id => null,
2664 p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
2665 p_lp_enrollment_ids => l_lp_enrollment_ids);
2666
2667 -- update any associated cert member enrollment statuses
2668 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2669 p_person_id => null,
2670 p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
2671 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2672
2673 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2674 p_person_id => null,
2675 p_contact_id => p_delegate_contact_id,
2676 p_lp_enrollment_ids => l_lp_enrollment_ids);
2677
2678 -- update any associated cert member enrollment statuses
2679 ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
2680 p_person_id => null,
2681 p_contact_id => p_delegate_contact_id,
2682 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
2683
2684 deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
2685 createForumNotification(ota_tdb_shd.g_old_rec.event_id, null, p_delegate_contact_id, l_effective_date, p_booking_status_type_id);
2686
2687 END IF;
2688
2689 --bug#6683076.Added for sending notifications to learners for
2690 -- taking evaluations.
2691 if l_status_type_id_changed then --bug#7046631.
2692 open c_eval_info;
2693 fetch c_eval_info into l_evt_eval_id,l_eval_mand,l_act_eval_id;
2694 close c_eval_info;
2695
2696 if((l_type = 'E' and l_eval_mand = 'Y') or (l_type = 'A' and l_eval_mand = 'N')) then
2697 ota_initialization_wf.init_course_eval_notif(p_booking_id);
2698 end if;
2699 end if;
2700 --end of changes for evaluations.
2701
2702 IF p_update_finance_line in ('C','Y') THEN
2703 --
2704 l_cancel_finance_line := (p_update_finance_line = 'C');
2705 -- Added for bug#5519140
2706 OPEN c_get_fin_line_status;
2707 FETCH c_get_fin_line_status INTO l_cancelled_flag;
2708
2709 IF l_cancelled_flag <> 'Y' OR c_get_fin_line_status%NOTFOUND THEN
2710 IF (p_update_finance_line = 'C') THEN
2711 OTA_LEARNER_ENROLL_SS.cancel_finance(p_booking_id); --Bug#7110214
2712 ELSE
2713 ota_finance.maintain_finance_line
2714 (p_finance_header_id => p_finance_header_id,
2715 p_booking_id => p_booking_id ,
2716 p_currency_code => p_currency_code ,
2717 p_standard_amount => p_standard_amount,
2718 p_unitary_amount => p_unitary_amount ,
2719 p_money_amount => p_money_amount ,
2720 p_booking_deal_id => p_booking_deal_id ,
2721 p_booking_deal_type => p_booking_deal_type,
2722 p_object_version_number => p_tfl_object_version_number,
2723 p_finance_line_id => p_finance_line_id,
2724 p_cancel_finance_line => l_cancel_finance_line);
2725 END IF;
2726 END IF;
2727 CLOSE c_get_fin_line_status;
2728 --
2729 end if;
2730
2731 --
2732 -- Reset Event Status
2733 --
2734 ota_evt_bus2.reset_event_status(p_event_id
2735 ,l_event_rec.object_version_number
2736 ,l_event_rec.event_status
2737 ,l_event_rec.maximum_attendees);
2738 --
2739 -- *** In case of Event Change -- Reset Event Status for Old Event ***
2740 --
2741 IF p_event_id <> ota_tdb_shd.g_old_rec.event_id THEN
2742 ota_evt_bus2.lock_event(ota_tdb_shd.g_old_rec.event_id);
2743
2744 ota_evt_shd.get_event_details (ota_tdb_shd.g_old_rec.event_id,
2745 l_event_rec,
2746 l_event_exists);
2747
2748 ota_evt_bus2.reset_event_status(ota_tdb_shd.g_old_rec.event_id
2749 ,l_event_rec.object_version_number
2750 ,l_event_rec.event_status
2751 ,l_event_rec.maximum_attendees);
2752
2753 ota_evt_shd.get_event_details (p_event_id,
2754 l_event_rec,
2755 l_event_exists);
2756 END IF;
2757
2758 IF ( l_ignore_dff_validation = 'Y') THEN
2759 hr_dflex_utility.remove_ignore_df_validation;
2760 END IF;
2761
2762 --
2763 -- Call After Process User Hook
2764 --
2765 begin
2766 OTA_delegate_booking_bk2.update_delegate_booking_a
2767 (p_effective_date => l_effective_date ,
2768 p_booking_id => p_booking_id ,
2769 p_booking_status_type_id => p_booking_status_type_id ,
2770 p_delegate_person_id => p_delegate_person_id ,
2771 p_contact_id => p_contact_id ,
2772 p_business_group_id => p_business_group_id ,
2773 p_event_id => p_event_id ,
2774 p_customer_id => p_customer_id ,
2775 p_authorizer_person_id => p_authorizer_person_id ,
2776 p_date_booking_placed => l_date_booking_placed ,
2777 p_corespondent => p_corespondent ,
2778 p_internal_booking_flag => p_internal_booking_flag ,
2779 p_number_of_places => p_number_of_places ,
2780 p_object_version_number => p_object_version_number ,
2781 p_administrator => p_administrator ,
2782 p_booking_priority => p_booking_priority ,
2783 p_comments => p_comments ,
2784 p_contact_address_id => p_contact_address_id ,
2785 p_delegate_contact_phone => p_delegate_contact_phone ,
2786 p_delegate_contact_fax => p_delegate_contact_fax ,
2787 p_third_party_customer_id => p_third_party_customer_id ,
2788 p_third_party_contact_id => p_third_party_contact_id ,
2789 p_third_party_address_id => p_third_party_address_id ,
2790 p_third_party_contact_phone => p_third_party_contact_phone ,
2791 p_third_party_contact_fax => p_third_party_contact_fax ,
2792 p_date_status_changed => l_date_status_changed ,
2793 p_status_change_comments => p_status_change_comments ,
2794 p_failure_reason => p_failure_reason ,
2795 p_attendance_result => p_attendance_result ,
2796 p_language_id => p_language_id ,
2797 p_source_of_booking => p_source_of_booking ,
2798 p_special_booking_instructions =>p_special_booking_instructions ,
2799 p_successful_attendance_flag => p_successful_attendance_flag ,
2800 p_tdb_information_category => p_tdb_information_category ,
2801 p_tdb_information1 => p_tdb_information1 ,
2802 p_tdb_information2 => p_tdb_information2 ,
2803 p_tdb_information3 => p_tdb_information3 ,
2804 p_tdb_information4 => p_tdb_information4 ,
2805 p_tdb_information5 => p_tdb_information5 ,
2806 p_tdb_information6 => p_tdb_information6 ,
2807 p_tdb_information7 => p_tdb_information7 ,
2808 p_tdb_information8 => p_tdb_information8 ,
2809 p_tdb_information9 => p_tdb_information9 ,
2810 p_tdb_information10 => p_tdb_information10 ,
2811 p_tdb_information11 => p_tdb_information11 ,
2812 p_tdb_information12 => p_tdb_information12 ,
2813 p_tdb_information13 => p_tdb_information13 ,
2814 p_tdb_information14 => p_tdb_information14 ,
2815 p_tdb_information15 => p_tdb_information15 ,
2816 p_tdb_information16 => p_tdb_information16 ,
2817 p_tdb_information17 => p_tdb_information17 ,
2818 p_tdb_information18 => p_tdb_information18 ,
2819 p_tdb_information19 => p_tdb_information19 ,
2820 p_tdb_information20 => p_tdb_information20 ,
2821 p_update_finance_line => p_update_finance_line ,
2822 p_tfl_object_version_number => p_tfl_object_version_number ,
2823 p_finance_header_id => p_finance_header_id ,
2824 p_finance_line_id => l_finance_line_id ,
2825 p_standard_amount => p_standard_amount ,
2826 p_unitary_amount => p_unitary_amount ,
2827 p_money_amount => p_money_amount ,
2828 p_currency_code => p_currency_code ,
2829 p_booking_deal_type => p_booking_deal_type ,
2830 p_booking_deal_id => p_booking_deal_id ,
2831 p_enrollment_type => p_enrollment_type ,
2832 p_organization_id => p_organization_id ,
2833 p_sponsor_person_id => p_sponsor_person_id ,
2834 p_sponsor_assignment_id => p_sponsor_assignment_id ,
2835 p_person_address_id => p_person_address_id ,
2836 p_delegate_assignment_id => p_delegate_assignment_id ,
2837 p_delegate_contact_id => p_delegate_contact_id ,
2838 p_delegate_contact_email => p_delegate_contact_email ,
2839 p_third_party_email => p_third_party_email ,
2840 p_person_address_type => p_person_address_type ,
2841 p_line_id => p_line_id ,
2842 p_org_id => p_org_id ,
2843 p_daemon_flag => p_daemon_flag ,
2844 p_daemon_type => p_daemon_type ,
2845 p_old_event_id => p_old_event_id ,
2846 p_quote_line_id => p_quote_line_id ,
2847 p_interface_source => p_interface_source ,
2848 p_total_training_time => p_total_training_time ,
2849 p_content_player_status => p_content_player_status ,
2850 p_score => p_score ,
2851 p_completed_content => p_completed_content ,
2852 p_total_content => p_total_content,
2853 p_booking_justification_id => p_booking_justification_id,
2854 p_is_history_flag => p_is_history_flag
2855 );
2856 exception
2857 when hr_api.cannot_find_prog_unit then
2858 hr_api.cannot_find_prog_unit_error
2859 (p_module_name => 'update_delegate_booking_a'
2860 ,p_hook_type => 'AP'
2861 );
2862 end;
2863 --
2864 -- When in validation only mode raise the Validate_Enabled exception
2865 --
2866 if p_validate then
2867 raise hr_api.validate_enabled;
2868 end if;
2869 --
2870 -- Set all output arguments
2871 --
2872 -- p_object_version_number := l_object_version_number;
2873 --
2874 hr_utility.set_location(' Leaving:'||l_proc, 70);
2875 exception
2876 when hr_api.validate_enabled then
2877 --
2878 -- As the Validate_Enabled exception has been raised
2879 -- we must rollback to the savepoint
2880 --
2881 rollback to update_delegate_booking ;
2882 --
2883 -- Only set output warning arguments
2884 -- (Any key or derived arguments must be set to null
2885 -- when validation only mode is being used.)
2886 --
2887 -- p_object_version_number := null;
2888 hr_utility.set_location(' Leaving:'||l_proc, 80);
2889 when others then
2890 --
2891 -- A validation or unexpected error has occured
2892 --
2893 rollback to update_delegate_booking ;
2894 hr_utility.set_location(' Leaving:'||l_proc, 90);
2895 -- p_object_version_number := l_object_version_number;
2896 raise;
2897 end update_delegate_booking ;
2898
2899 --
2900 -- ----------------------------------------------------------------------------
2901 -- |-------------------------< delete_delegate_booking >----------------------|
2902 -- ----------------------------------------------------------------------------
2903 --
2904 procedure delete_delegate_booking
2905 (
2906 p_validate in boolean,
2907 p_booking_id in number,
2908 p_object_version_number in number
2909 ) is
2910 --
2911 -- Declare cursors and local variables
2912 --
2913 l_proc varchar2(72) := g_package||' delete_delegate_booking ';
2914
2915 --bug 6063768 changes starts
2916
2917 l_hours_until_class_starts NUMBER := 0;
2918 l_sysdate VARCHAR2(30) ;
2919 l_course_start_date ota_events.course_start_date%TYPE;
2920 l_course_start_time ota_events.course_start_time%TYPE;
2921 l_event_title ota_events.title%TYPE;
2922 l_owner_id ota_events.owner_id%TYPE;
2923 l_event_id ota_delegate_bookings.event_id%TYPE;
2924 l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%TYPE;
2925 l_booking_status ota_booking_status_types.type%TYPE;
2926 l_username fnd_user.user_name%TYPE;
2927 l_auto_waitlist_days NUMBER;
2928 l_auto_waitlist VARCHAR2(2) := 'N';
2929 l_waitlist_size NUMBER := 0;
2930
2931 CURSOR event_csr(p_event_id ota_events.event_id%TYPE) IS
2932 SELECT oet.title,oe.course_start_date,oe.course_start_time,oe.owner_id
2933 FROM ota_events_tl oet, ota_events oe
2934 WHERE oet.event_id = oe.event_id
2935 AND oe.event_id = p_event_id
2936 AND oet.language = userenv('LANG');
2937
2938 CURSOR sys_date_csr IS
2939 SELECT to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')
2940 FROM dual;
2941
2942 CURSOR fnduser_csr(l_owner_id ota_events.owner_id%TYPE) IS
2943 SELECT user_name
2944 FROM fnd_user
2945 WHERE employee_id = l_owner_id
2946 AND trunc(sysdate) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(sysdate)+1);
2947
2948 CURSOR booking_csr(p_booking_id NUMBER) IS
2949 SELECT event_id,booking_status_type_id
2950 FROM ota_delegate_bookings
2951 WHERE booking_id = p_booking_id;
2952
2953 CURSOR booking_status_csr(l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%TYPE) IS
2954 SELECT type
2955 FROM ota_booking_status_types
2956 WHERE booking_status_type_id = l_booking_status_type_id;
2957
2958 --bug 603768 changes ends
2959 --
2960 begin
2961 hr_utility.set_location('Entering:'|| l_proc, 10);
2962 --
2963 -- Issue a savepoint
2964 --
2965 savepoint delete_delegate_booking ;
2966 --
2967 -- Call Before Process User Hook
2968 --
2969 begin
2970 OTA_delegate_booking_bk3.delete_delegate_booking_b
2971 (p_booking_id => p_booking_id ,
2972 p_object_version_number => p_object_version_number);
2973
2974 exception
2975 when hr_api.cannot_find_prog_unit then
2976 hr_api.cannot_find_prog_unit_error
2977 (p_module_name => 'delete_delegate_booking_b'
2978 ,p_hook_type => 'BP'
2979 );
2980 end;
2981
2982 -- 6063768 changes starts
2983 --Send ntf to class owner if any enrollment has been deleted
2984 l_auto_waitlist := fnd_profile.value('OTA_AUTO_WAITLIST_ACTIVE');
2985 l_auto_waitlist_days := TO_NUMBER(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
2986
2987 if (l_auto_waitlist is not null and l_auto_waitlist = 'Y' ) then
2988
2989 OPEN booking_csr (p_booking_id);
2990 FETCH booking_csr INTO l_event_id,l_booking_status_type_id;
2991 CLOSE booking_csr;
2992
2993 l_waitlist_size := ota_utility.students_on_waitlist(l_event_id);
2994
2995 if(l_waitlist_size > 0) then
2996
2997 OPEN booking_status_csr (l_booking_status_type_id);
2998 FETCH booking_status_csr INTO l_booking_status;
2999 CLOSE booking_status_csr;
3000
3001 if(l_booking_status = 'P' or l_booking_status = 'A' ) then
3002
3003 OPEN event_csr (l_event_id);
3004 FETCH event_csr INTO l_event_title,l_course_start_date,l_course_start_time,l_owner_id;
3005 CLOSE event_csr;
3006
3007 if ( l_course_start_date is not null ) then
3008 l_hours_until_class_starts := 24*(to_date(to_char(l_course_start_date, 'DD-MM-YYYY')||''||l_course_start_time, 'DD/MM/YYYYHH24:MI') - SYSDATE);
3009 end if;
3010
3011 if (l_hours_until_class_starts <= nvl(l_auto_waitlist_days,0) )then
3012
3013 OPEN sys_date_csr;
3014 FETCH sys_date_csr INTO l_sysdate;
3015 CLOSE sys_date_csr;
3016
3017 if (l_owner_id is null) then
3018 l_owner_id := fnd_profile.value('OTA_DEFAULT_EVENT_OWNER');
3019 end if;
3020
3021 OPEN fnduser_csr(l_owner_id);
3022 FETCH fnduser_csr INTO l_username;
3023 CLOSE fnduser_csr;
3024
3025 OTA_INITIALIZATION_WF.MANUAL_WAITLIST(
3026 p_itemtype => 'OTWF',
3027 p_process => 'OTA_MANUAL_WAITLIST',
3028 p_Event_title => l_event_title,
3029 p_event_id => l_event_id,
3030 p_item_key => p_booking_id ||':'||l_sysdate,
3031 p_user_name => l_username);
3032
3033 end if;
3034 end if;
3035 end if;
3036 end if;
3037 --bug 603768 changes ends
3038 --
3039 -- Validation in addition to Row Handlers
3040 --
3041 --
3042 -- Process Logic
3043 --
3044 OTA_tdb_del.del
3045 (p_booking_id => p_booking_id ,
3046 p_object_version_number => p_object_version_number,
3047 p_validate => p_validate) ;
3048 --
3049 -- Call After Process User Hook
3050 --
3051 begin
3052 OTA_delegate_booking_bk3.delete_delegate_booking_a
3053 (p_booking_id => p_booking_id ,
3054 p_object_version_number => p_object_version_number);
3055 exception
3056 when hr_api.cannot_find_prog_unit then
3057 hr_api.cannot_find_prog_unit_error
3058 (p_module_name => 'delete_delegate_booking_a'
3059 ,p_hook_type => 'AP'
3060 );
3061 end;
3062 --
3063 -- When in validation only mode raise the Validate_Enabled exception
3064 --
3065 if p_validate then
3066 raise hr_api.validate_enabled;
3067 end if;
3068 --
3069 -- Set all output arguments
3070 --
3071 --
3072 hr_utility.set_location(' Leaving:'||l_proc, 170);
3073 exception
3074 when hr_api.validate_enabled then
3075 --
3076 -- As the Validate_Enabled exception has been raised
3077 -- we must rollback to the savepoint
3078 --
3079 rollback to delete_delegate_booking ;
3080 --
3081 -- Only set output warning arguments
3082 -- (Any key or derived arguments must be set to null
3083 -- when validation only mode is being used.)
3084 --
3085 hr_utility.set_location(' Leaving:'||l_proc, 180);
3086 when others then
3087 --
3088 -- A validation or unexpected error has occured
3089 --
3090 rollback to delete_delegate_booking ;
3091 hr_utility.set_location(' Leaving:'||l_proc, 190);
3092 raise;
3093 end delete_delegate_booking;
3094 --
3095 -- ----------------------------------------------------------------------------
3096 -- |--------------------------< Update Waitlisted >-------------------------|
3097 -- ----------------------------------------------------------------------------
3098 --
3099 -- PUBLIC
3100 -- Description: Updates Waitlisted enrollments from the Waitlist window.
3101 --
3102 --
3103 Procedure Update_Waitlisted (p_booking_id in number
3104 ,p_object_version_number in out nocopy number
3105 ,p_event_id in number
3106 ,p_booking_status_type_id in number
3107 ,p_date_status_changed in date
3108 ,p_status_change_comments in varchar2
3109 ,p_number_of_places in number
3110 ,p_finance_line_id in out nocopy number
3111 ,p_tfl_object_version_number in out nocopy number
3112 ,p_administrator in number
3113 ,p_validate in boolean
3114 ) is
3115 --
3116 --
3117 l_status_change_comments varchar2(1000);
3118 l_proc varchar2(72);
3119 l_places number;
3120 --
3121 begin
3122 --
3123 g_debug := hr_utility.debug_enabled;
3124
3125 if g_debug then
3126 l_proc := g_package||'Update_Waitlisted';
3127 hr_utility.set_location('Entering:'|| l_proc, 5);
3128 end if;
3129 --
3130 -- Call the delegate booking update API.
3131 --
3132
3133 l_places := ota_evt_bus2.get_vacancies(p_event_id);
3134
3135 if p_number_of_places <= l_places or
3136 l_places is null then
3137 --
3138 ota_tdb_api_upd2.update_enrollment
3139 (p_booking_id => p_booking_id
3140 ,p_object_version_number => p_object_version_number
3141 ,p_event_id => p_event_id
3142 ,p_booking_status_type_id => p_booking_status_type_id
3143 ,p_date_status_changed => p_date_status_changed
3144 ,p_status_change_comments => p_status_change_comments
3145 ,p_number_of_places => p_number_of_places
3146 ,p_update_finance_line => 'N'
3147 ,p_finance_line_id => p_finance_line_id
3148 ,p_tfl_object_version_number => p_tfl_object_version_number
3149 ,p_administrator => p_administrator
3150 ,p_validate => p_validate
3151 );
3152 --
3153 -- commit the changes
3154 --
3155 commit;
3156 --
3157 else
3158 --
3159 fnd_message.set_name('OTA','OTA_13558_TDB_PLACES_INC');
3160 fnd_message.raise_error;
3161 --
3162 end if;
3163 --
3164 if g_debug then
3165 hr_utility.set_location('Leaving:'|| l_proc, 10);
3166 end if;
3167 --
3168 end Update_Waitlisted;
3169
3170 Procedure chk_mandatory_prereqs
3171 (p_delegate_person_id in number,
3172 p_delegate_contact_id in number,
3173 p_customer_id in number,
3174 p_event_id in number,
3175 p_booking_status_type_id in number
3176 ) IS
3177
3178 l_delegate_person_id ota_delegate_bookings.delegate_person_id%TYPE;
3179 l_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE;
3180 l_customer_id ota_delegate_bookings.customer_id%TYPE;
3181 l_event_id ota_events.event_id%TYPE;
3182 l_booking_status_type_id ota_delegate_bookings.booking_status_type_id%TYPE;
3183 l_check_prereq boolean;
3184 l_old_status_type varchar2(30);
3185 l_new_status_type varchar2(30);
3186
3187 Begin
3188 -- Prerequisite Validation Code
3189 l_check_prereq := false;
3190
3191 If ( p_delegate_person_id = hr_api.g_number ) Then
3192 l_delegate_person_id := ota_tdb_shd.g_old_rec.delegate_person_id;
3193 Else
3194 l_delegate_person_id := p_delegate_person_id;
3195 End If;
3196
3197 If ( p_delegate_contact_id = hr_api.g_number ) Then
3198 l_delegate_contact_id := ota_tdb_shd.g_old_rec.delegate_contact_id;
3199 Else
3200 l_delegate_contact_id := p_delegate_contact_id;
3201 End If;
3202
3203 If ( p_customer_id = hr_api.g_number ) Then
3204 l_customer_id := ota_tdb_shd.g_old_rec.customer_id;
3205 Else
3206 l_customer_id := p_customer_id;
3207 End If;
3208
3209 If ( p_event_id = hr_api.g_number ) Then
3210 l_event_id := ota_tdb_shd.g_old_rec.event_id;
3211 Else
3212 l_event_id := p_event_id;
3213 End If;
3214
3215 If ( p_booking_status_type_id = hr_api.g_number ) Then
3216 l_booking_status_type_id := ota_tdb_shd.g_old_rec.booking_status_type_id;
3217 Else
3218 l_booking_status_type_id := p_booking_status_type_id;
3219 End If;
3220
3221 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_person_id, l_delegate_person_id) ) Then
3222 l_check_prereq := true;
3223 End If;
3224
3225 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_contact_id, l_delegate_contact_id) ) Then
3226 l_check_prereq := true;
3227 End If;
3228
3229 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.customer_id, l_customer_id) ) Then
3230 l_check_prereq := true;
3231 End If;
3232
3233 If (ota_general.value_changed (ota_tdb_shd.g_old_rec.event_id, l_event_id) ) Then
3234 l_check_prereq := true;
3235 End If;
3236
3237 ota_utility.get_booking_status_type(p_status_type_id => ota_tdb_shd.g_old_rec.booking_status_type_id,
3238 p_type => l_old_status_type);
3239
3240 ota_utility.get_booking_status_type(p_status_type_id => l_booking_status_type_id,
3241 p_type => l_new_status_type);
3242
3243 If ( l_old_status_type = 'C' and l_new_status_type <> 'C' ) Then
3244 l_check_prereq := true;
3245 End If;
3246
3247 If ( l_check_prereq and (l_delegate_person_id is not null or l_delegate_contact_id is not null) ) Then
3248 ota_cpr_utility.chk_mandatory_prereqs(l_delegate_person_id, l_delegate_contact_id, l_event_id);
3249 End If;
3250 --
3251 End chk_mandatory_prereqs;
3252
3253 --
3254 --
3255
3256 end ota_delegate_booking_api;