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