[Home] [Help]
PACKAGE BODY: APPS.OTA_LEARNER_ENROLL_SS
Source
1 PACKAGE BODY OTA_LEARNER_ENROLL_SS as
2 /* $Header: otlnrenr.pkb 120.12.12010000.3 2008/08/22 06:30:21 pvelugul ship $ */
3
4 g_package varchar2(33) := ' ota_learner_enroll_ss.'; -- Global package name
5
6 --
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------------<ProcessSaveEnrollment>-----------------------|
10 -- ----------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- This package is used for self service application to create enrollment
15 -- data when user enroll in the class.
16 --
17 -- Prerequisites:
18 --
19 -- In Parameters:
20 --
21 -- Post Success:
22 -- Enrollment data will be created.
23 --
24 -- Post Failure:
25 -- Status will be passed to the caller and the caller will raise a notification.
26 --
27 -- Developer Implementation Notes:
28 -- The attrbute in parameters should be modified as to the business process
29 -- requirements.
30 --
31 -- Access Status:
32 -- Internal Development Use Only.
33 --
34 -- {End Of Comments}
35 -- ----------------------------------------------------------------------------
36 PROCEDURE ProcessSaveEnrollment( p_event_id IN VARCHAR2
37 ,p_extra_information IN VARCHAR2
38 ,p_mode IN VARCHAR2
39 ,p_cost_centers IN VARCHAR2
40 ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
41 ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
42 ,p_business_group_name IN PER_BUSINESS_GROUPS.name%TYPE
43 ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
44 ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
45 -- Added for External LearnerSupport
46 ,p_delegate_contact_id IN NUMBER
47 ,p_booking_id out nocopy OTA_DELEGATE_BOOKINGS.Booking_id%type
48 ,p_message_name out nocopy varchar2
49 ,p_tdb_information_category in varchar2
50 ,p_tdb_information1 in varchar2
51 ,p_tdb_information2 in varchar2
52 ,p_tdb_information3 in varchar2
53 ,p_tdb_information4 in varchar2
54 ,p_tdb_information5 in varchar2
55 ,p_tdb_information6 in varchar2
56 ,p_tdb_information7 in varchar2
57 ,p_tdb_information8 in varchar2
58 ,p_tdb_information9 in varchar2
59 ,p_tdb_information10 in varchar2
60 ,p_tdb_information11 in varchar2
61 ,p_tdb_information12 in varchar2
62 ,p_tdb_information13 in varchar2
63 ,p_tdb_information14 in varchar2
64 ,p_tdb_information15 in varchar2
65 ,p_tdb_information16 in varchar2
66 ,p_tdb_information17 in varchar2
67 ,p_tdb_information18 in varchar2
68 ,p_tdb_information19 in varchar2
69 ,p_tdb_information20 in varchar2
70 ,p_booking_justification_id in varchar2)
71 IS
72
73 CURSOR bg_to (pp_event_id ota_events.event_id%TYPE) IS
74 SELECT hao.business_group_id,
75 evt.organization_id,
76 evt.currency_code,
77 evt.course_start_date,
78 evt.course_end_date,
79 evt.Title,
80 evt.owner_id,
81 off.activity_version_id,
82 evt.offering_id
83 FROM OTA_EVENTS_VL evt,
84 OTA_OFFERINGS off,
85 HR_ALL_ORGANIZATION_UNITS hao
86 WHERE evt.event_id = pp_event_id
87 AND off.offering_id = evt.parent_offering_id
88 AND evt.organization_id = hao.organization_id (+);
89
90
91 Cursor Get_Event_status is
92 Select event_status, maximum_internal_attendees, maximum_attendees
93 from OTA_EVENTS
94 WHERE EVENT_ID = TO_NUMBER(p_event_id);
95
96 CURSOR get_existing_internal IS
97 SELECT count(booking_id)
98 FROM OTA_DELEGATE_BOOKINGS dbt,
99 OTA_BOOKING_STATUS_TYPES bst
100 WHERE dbt.event_id = TO_NUMBER(p_event_id)
101 AND dbt.internal_booking_flag = 'Y'
102 AND dbt.booking_status_type_id = bst.booking_status_type_id
103 AND bst.type in ('P','A','E'); --Bug 4301617
104
105 CURSOR get_existing_bookings IS
106 SELECT sum(number_of_places)
107 FROM OTA_DELEGATE_BOOKINGS dbt,
108 OTA_BOOKING_STATUS_TYPES bst
109 WHERE dbt.event_id = TO_NUMBER(p_event_id)
110 AND dbt.booking_status_type_id = bst.booking_status_type_id
111 AND bst.type in ('P','A','E');
112
113
114 CURSOR c_get_price_basis is
115 SELECT nvl(price_basis,NULL)
116 FROM ota_events
117 where event_id = p_event_id;
118
119 CURSOR csr_user(p_owner_id in number) IS
120 SELECT
121 USER_NAME
122 FROM
123 FND_USER
124 WHERE
125 Employee_id = p_owner_id ;
126
127 CURSOR csr_activity(p_activity_version_id number )
128 IS
129 SELECT version_name
130 FROM OTA_ACTIVITY_VERSIONS_TL
131 WHERE activity_version_id = p_activity_version_id
132 AND language=userenv('LANG');
133
134 CURSOR csr_get_priority IS
135 SELECT bjs.priority_level
136 FROM ota_bkng_justifications_b BJS
137 WHERE bjs.booking_justification_id = p_booking_justification_id;
138
139
140 l_price_basis OTA_EVENTS.price_basis%TYPE;
141
142 l_person_details OTA_LEARNER_ENROLL_SS.csr_person_to_enroll_details%ROWTYPE;
143 --
144 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
145 l_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type := null;
146 l_object_version_number BINARY_INTEGER;
147 l_tfl_ovn BINARY_INTEGER;
148 l_finance_line_id OTA_FINANCE_LINES.finance_line_id%type:= null;
149 l_booking_type VARCHAR2(4000);
150 l_error_crypt VARCHAR2(4000);
151 --
152 l_mode VARCHAR2(200);
153 l_delegate_id PER_PEOPLE_F.person_id%TYPE;
154 l_restricted_assignment_id PER_ASSIGNMENTS_F.assignment_id%type;
155 l_cancel_boolean BOOLEAN;
156 -- -------------------
157 -- Finance API Vars
158 -- -------------------
159 l_auto_create_finance VARCHAR2(40);
160 fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
161 fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
162 fapi_result VARCHAR2(40);
163 fapi_from VARCHAR2(5);
164 fapi_to VARCHAR2(5);
165
166 result_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
167 result_create_finance_line VARCHAR2(5) := 'Y';
168 result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
169
170 l_logged_in_user NUMBER;
171 l_user NUMBER;
172 l_automatic_transfer_gl VARCHAR2(40);
173 l_notification_text VARCHAR2(1000);
174 l_cost_allocation_keyflex_id VARCHAR2(1000);
175
176 l_event_status varchar2(30);
177
178 l_maximum_internal_attendees NUMBER;
179 l_existing_internal NUMBER;
180 l_maximum_internal_allowed NUMBER;
181
182 l_called_from varchar2(80);
183 l_business_group_id_to hr_all_organization_units.organization_id%type;
184 l_sponsor_organization_id hr_all_organization_units.organization_id%type;
185 l_event_currency_code ota_events.currency_code%type;
186 l_event_title ota_events.title%type;
187 l_course_start_date ota_events.course_start_date%type;
188 l_course_end_date ota_events.course_end_date%type;
189 l_owner_id ota_events.owner_id%type;
190 l_activity_version_id ota_activity_versions.activity_version_id%type;
191 l_version_name ota_activity_versions.version_name%type;
192 l_owner_username fnd_user.user_name%type;
193 l_offering_id ota_events.offering_id%type;
194 l_booking_status_used varchar2(20);
195
196 l_existing_bookings NUMBER;
197 l_maximum_external_allowed NUMBER;
198 l_maximum_attendees NUMBER;
199 l_internal_booking_flag OTA_DELEGATE_BOOKINGS.internal_booking_flag%TYPE;
200 l_work_telephone OTA_DELEGATE_BOOKINGS.delegate_contact_phone%TYPE := NULL;
201 l_work_fax OTA_DELEGATE_BOOKINGS.delegate_contact_fax%TYPE := NULL;
202 l_organization_id OTA_DELEGATE_BOOKINGS.organization_id%TYPE := NULL;
203 l_assignment_id OTA_DELEGATE_BOOKINGS.delegate_assignment_id%TYPE := NULL;
204 l_email_address OTA_DELEGATE_BOOKINGS.delegate_contact_email%TYPE := NULL;
205 l_person_address_type varchar2(1);
206 l_ext_lrnr_details csr_ext_lrnr_Details%ROWTYPE;
207 l_customer_id HZ_CUST_ACCOUNT_ROLES.cust_account_id%type := NULL;
208 l_corespondent varchar2(1) := NULL;
209 l_source_of_booking varchar2(30) := NULL; --Bug 5580960 : Incleased the SIZE.
210 l_enrollment_type varchar2(1) := 'S';
211 l_priority_level varchar2(30) := null;
212 BEGIN
213
214 HR_UTIL_MISC_WEB.VALIDATE_SESSION(p_person_id => l_logged_in_user);
215
216 -- ----------------------------------------------------------------------
217 -- RETRIEVE THE DATA REQUIRED
218 -- ----------------------------------------------------------------------
219
220 BEGIN
221
222 IF p_booking_justification_id IS NOT NULL THEN
223 OPEN csr_get_priority;
224 FETCH csr_get_priority INTO l_priority_level;
225 CLOSE csr_get_priority;
226 END IF;
227
228 IF p_person_id IS NOT NULL THEN
229 l_delegate_id := p_person_id;
230 l_person_address_type := 'I';
231 l_corespondent := 'S';
232 --l_source_of_booking := 'E'; Bug 5580960: removed hardcoding. Now Source of Booking will be decided by profile value OTA_DEFAULT_ENROLLMENT_SOURCE
233 l_source_of_booking := fnd_profile.value('OTA_DEFAULT_ENROLLMENT_SOURCE');
234
235 l_restricted_assignment_id := CHK_DELEGATE_OK_FOR_EVENT(p_delegate_id => l_delegate_id
236 ,p_event_id => p_event_id);
237
238
239 l_person_details := Get_Person_To_Enroll_Details(p_person_id => l_delegate_id);
240 l_internal_booking_flag := 'Y';
241 l_work_telephone := l_person_details.work_telephone;
242 l_work_fax := l_person_details.work_fax;
243 l_organization_id := l_person_details.organization_id;
244 l_assignment_id := l_person_details.assignment_id;
245 l_email_address := l_person_details.email_address;
246
247 IF l_restricted_assignment_id IS NULL OR
248 l_restricted_assignment_id = '-1' THEN
249 NULL;
250 ELSE
251 l_person_details.assignment_id := l_restricted_assignment_id;
252 END IF;
253 ELSE
254 l_internal_booking_flag := 'N';
255 l_person_address_type := null;
256 l_ext_lrnr_details := Get_ext_lrnr_Details(p_delegate_contact_id);
257 l_customer_id := l_ext_lrnr_details.customer_id;
258 END IF;
259
260 -- -----------------------------------------------
261 -- Open BG Cursor to get the Business Group TO
262 -- -----------------------------------------------
263 OPEN bg_to(p_event_id);
264 FETCH bg_to INTO l_business_group_id_to,
265 l_sponsor_organization_id,
266 l_event_currency_code,
267 l_course_start_date,
268 l_course_end_date,
269 l_event_title,
270 l_owner_id,
271 l_activity_version_id,
272 l_offering_id;
273 CLOSE bg_to;
274
275
276 For act in csr_activity(l_activity_version_id)
277 Loop
278 l_version_name := act.version_name;
279 End Loop;
280
281 if l_owner_id is not null then
282 For owner in csr_user(l_owner_id)
283 Loop
284 l_owner_username := owner.user_name;
285 End Loop;
286 end if;
287
288
289 -- The enrollment doesn't need mangerial approval so check the mode
290 -- to find out whether they can only be waitlisted and then get the
291 -- default booking status for either waitlisted or placed.
292
293 OPEN get_event_status;
294 FETCH get_event_status into l_event_status, l_maximum_internal_attendees,l_maximum_attendees;
295 CLOSE get_event_status;
296
297 IF p_person_id IS NOT NULL THEN
298 OPEN get_existing_internal;
299 FETCH get_existing_internal into l_existing_internal;
300 CLOSE get_existing_internal;
301
302 l_maximum_internal_allowed := nvl(l_maximum_internal_attendees,0) - nvl(l_existing_internal,0);
303 ELSE
304 OPEN get_existing_bookings;
305 FETCH get_existing_bookings into l_existing_bookings;
306 CLOSE get_existing_bookings;
307
308 l_maximum_external_allowed := nvl(l_maximum_attendees,0) - nvl(l_existing_bookings,0);
309 END IF;
310
311 IF l_event_status in ('F') THEN
312
313 l_booking_status_row := Get_Booking_Status_for_web
314 (p_web_booking_status_type => 'WAITLISTED'
315 ,p_business_group_id => ota_general.get_business_group_id);
316
317 l_booking_status_used := 'WAITLISTED';
318
319 ELSIF l_event_status in ('P') THEN
320
321 l_booking_status_row := Get_Booking_Status_for_web
322 (p_web_booking_status_type => 'REQUESTED'
323 ,p_business_group_id => ota_general.get_business_group_id);
324
325 l_booking_status_used := 'REQUESTED';
326
327 ELSIF l_event_status = 'N' THEN
328
329 IF l_maximum_internal_attendees is null then
330 l_booking_status_row := Get_Booking_Status_for_web
331 (p_web_booking_status_type => 'PLACED'
332 ,p_business_group_id => ota_general.get_business_group_id);
333
334 l_booking_status_used := 'PLACED';
335
336 ELSE
337
338 IF l_maximum_internal_allowed > 0 OR l_maximum_external_allowed > 0 THEN
339 l_booking_status_row := Get_Booking_Status_for_web
340 (p_web_booking_status_type => 'PLACED'
341 ,p_business_group_id => ota_general.get_business_group_id);
342
343 l_booking_status_used := 'PLACED';
344
345 ELSIF l_maximum_internal_allowed <= 0 OR l_maximum_external_allowed <= 0 THEN
346 l_booking_status_row := Get_Booking_Status_for_web
347 (p_web_booking_status_type => 'WAITLISTED'
348 ,p_business_group_id => ota_general.get_business_group_id);
349
350 l_booking_status_used := 'WAITLISTED';
351
352 END IF;
353 END IF;
354 IF l_booking_status_row.booking_Status_type_id is null then
355 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
356 RAISE g_mesg_on_stack_exception ;
357 END IF ;
358 END IF;
359
360 EXCEPTION
361 WHEN g_mesg_on_stack_exception THEN
362 --
363 -- Store the technical message which will have been seeded
364 -- if this exception has been raised. This will be used to provide
365 -- the code.
366 --
367 hr_message.provide_error;
368 --
369 -- Now distinguish which error was raised.
370 --
371 IF (hr_message.last_message_name = 'OTA_13667_WEB_STATUS_NOT_SEEDE') THEN
372 --
373 -- Seed the user friendly message
374 --
375 fnd_message.set_name ('OTA','OTA_WEB_INCORRECT_CONF');
376 --
377 -- Raise the error for the main procedure exception handler
378 -- to handle
379 --
380 p_message_name := hr_message.last_message_name;
381 p_message_name := SUBSTR(SQLERRM, 1,300);
382 --
383 ELSIF (hr_message.last_message_name = 'HR_51396_WEB_PERSON_NOT_FND')
384 THEN
385 --
386 -- Seed the user friendly message
387 --
388 fnd_message.set_name ('OTA','OTA_NO_DELEGATE_INFORMATION');
389 --
390 -- Raise the error for the main procedure exception handler
391 -- to handle
392 p_message_name := 'OTA_NO_DELEGATE_INFORMATION';
393 p_message_name := SUBSTR(SQLERRM, 1,300);
394 --
395 /* ELSIF (hr_message.last_message_name = 'OTA_13658_WF_ERR_GETTING_TYPE')
396 THEN
397 --
398 -- Seed the user friendly message
399 --
400 fnd_message.set_name ('OTA','OTA_WEB_WF_PROBLEM');
401 -- Raise the error for the main procedure exception handler
402 -- to handle
403 --
404 p_message_name := fnd_message.get;
405 p_message_name := SUBSTR(SQLERRM, 1,300);*/
406 --
407 ELSE
408 -- Raise the error for the main procedure exception handler
409 -- to handle
410 p_message_name := hr_message.get_message_text;
411
412 --
413 END IF;
414 --
415 WHEN OTHERS THEN
416 --
417 -- Can't store a technical message, as we don't know what it is
418 -- and a message may not have been put on the stack
419 --
420 hr_message.provide_error;
421 --
422 -- Seed the user friendly message
423 --
424 fnd_message.set_name ('OTA','OTA_WEB_ERR_GETTING_INFO');
425 --
426 --
427 -- Raise the error for the main procedure exception handler
428 -- to handle
429 -- p_message_name := 'OTA_WEB_ERR_GETTING_INFO';
430
431 p_message_name := SUBSTR(SQLERRM, 1,300);
432
433 END ;
434 --
435 -- ----------------------------------------------------------------------
436 -- Save
437 -- ----------------------------------------------------------------------
438 -- If there are no errors, save to the database
439 -- (there shouldn't be as the main exception handler will be used
440 --
441 -- IF NOT hr_errors_api.errorExists THEN
442 IF p_message_name is null then
443
444
445 BEGIN
446 --
447 -- Check to see if delegate has a booking status of CANCELLED for
448 -- this event, if cancelled l_cancel_boolean is set to true
449 -- FIX for bug 900679
450 --
451 l_cancel_boolean := Chk_Event_Cancelled_for_Person(p_event_id => p_event_id
452 ,p_delegate_person_id => l_delegate_id
453 ,p_delegate_contact_id => p_delegate_contact_id
454 ,p_booking_id => l_booking_id);
455
456 l_auto_create_finance := FND_PROFILE.value('OTA_AUTO_CREATE_FINANCE');
457 l_automatic_transfer_gl := FND_PROFILE.value('OTA_SSHR_AUTO_GL_TRANSFER');
458 l_user := FND_PROFILE.value('USER_ID');
459
460 IF (l_cancel_boolean) THEN
461 --
462 -- Delegate has a Cancelled status for this event, hence
463 -- we must update the existing record by changing Cancelled
464 -- to Requested status
465 --
466
467 l_object_version_number := OTA_LEARNER_ENROLL_SS.Get_Booking_OVN (p_booking_id => l_booking_id);
468
469 /* Call Cancel procedure to cancel the Finance if person Re-enroll */
470 cancel_finance(l_booking_id);
471
472
473 -- ----------------------------------------------------------------
474 -- Delegate has no record for this event, hence create a record
475 -- with requested status
476 -- ----------------------------------------------------------------
477 -- Check if the Profile AutoCreate Finance is ON or OFF
478 -- ----------------------------------------------------------------
479 END IF;
480 open c_get_price_basis;
481 fetch c_get_price_basis into l_price_basis;
482 close c_get_price_basis;
483
484
485 IF l_delegate_id IS NOT NULL
486 AND l_auto_create_finance = 'Y'
487 and l_price_basis <> 'N'
488 and l_event_currency_code is not null THEN
489
490 l_cost_allocation_keyflex_id := TO_NUMBER(p_cost_centers);
491 result_finance_header_id := fapi_finance_header_id;
492 result_object_version_number := l_object_version_number;
493
494 ota_crt_finance_segment.Create_Segment(
495 p_assignment_id => p_assignment_id,
496 p_business_group_id_from => p_business_group_id_from,
497 p_business_group_id_to => l_business_group_id_to,
498 p_organization_id => p_organization_id,
499 p_sponsor_organization_id => l_sponsor_organization_id,
500 p_event_id => p_event_id,
501 p_person_id => l_delegate_id,
502 p_currency_code => l_event_currency_code,
503 p_cost_allocation_keyflex_id=> l_cost_allocation_keyflex_id,
504 p_user_id => l_user,
505 p_finance_header_id => fapi_finance_header_id,
506 p_object_version_number => fapi_object_version_number,
507 p_result => fapi_result,
508 p_from_result => fapi_from,
509 p_to_result => fapi_to );
510
511 IF fapi_result = 'S' THEN
512 result_object_version_number := fapi_object_version_number;
513 result_finance_header_id := fapi_finance_header_id;
514
515 ELSIF fapi_result = 'E' THEN
516 result_object_version_number := l_object_version_number;
517 result_finance_header_id := NULL;
518 result_create_finance_line := NULL;
519 END IF;
520
521 ota_tdb_api_ins2.Create_Enrollment(p_booking_id => l_booking_id
522 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
523 ,p_delegate_person_id => l_delegate_id
524 ,p_delegate_contact_id => null
525 ,p_contact_id => null
526 ,p_business_group_id => ota_general.get_business_group_id
527 ,p_event_id => p_event_id
528 --,p_date_booking_placed => trunc(sysdate)
529 ,p_date_booking_placed => sysdate
530 ,p_corespondent => l_corespondent
531 ,p_internal_booking_flag => l_internal_booking_flag
532 ,p_person_address_type => l_person_address_type
533 ,p_number_of_places => 1
534 ,p_object_version_number => result_object_version_number
535 ,p_delegate_contact_phone => l_work_telephone
536 ,p_delegate_contact_fax => l_work_fax
537 ,p_source_of_booking => l_source_of_booking
538 ,p_special_booking_instructions => p_extra_information
539 ,p_successful_attendance_flag => 'N'
540 ,p_finance_header_id => result_finance_header_id
541 ,p_create_finance_line => result_create_finance_line
542 ,p_finance_line_id => l_finance_line_id
543 ,p_enrollment_type => l_enrollment_type
544 ,p_validate => FALSE
545 ,p_currency_code => l_event_currency_code
546 ,p_organization_id => l_organization_id
547 ,p_delegate_assignment_id => l_assignment_id
548 ,p_delegate_contact_email => l_email_address
549 ,p_tdb_information_category => p_tdb_information_category
550 ,p_tdb_information1 => p_tdb_information1
551 ,p_tdb_information2 => p_tdb_information2
552 ,p_tdb_information3 => p_tdb_information3
553 ,p_tdb_information4 => p_tdb_information4
554 ,p_tdb_information5 => p_tdb_information5
555 ,p_tdb_information6 => p_tdb_information6
556 ,p_tdb_information7 => p_tdb_information7
557 ,p_tdb_information8 => p_tdb_information8
558 ,p_tdb_information9 => p_tdb_information9
559 ,p_tdb_information10 => p_tdb_information10
560 ,p_tdb_information11 => p_tdb_information11
561 ,p_tdb_information12 => p_tdb_information12
562 ,p_tdb_information13 => p_tdb_information13
563 ,p_tdb_information14 => p_tdb_information14
564 ,p_tdb_information15 => p_tdb_information15
565 ,p_tdb_information16 => p_tdb_information16
566 ,p_tdb_information17 => p_tdb_information17
567 ,p_tdb_information18 => p_tdb_information18
568 ,p_tdb_information19 => p_tdb_information19
569 ,p_tdb_information20 => p_tdb_information20
570 ,p_booking_justification_id => p_booking_justification_id
571 ,p_booking_priority => l_priority_level);
572
573
574 IF l_automatic_transfer_gl = 'Y' AND l_finance_line_id IS NOT NULL AND l_offering_id is null THEN
575
576 UPDATE ota_finance_lines SET transfer_status = 'AT'
577 WHERE finance_line_id = l_finance_line_id;
578
579
580
581 END IF;
582
583 ELSE
584
585 ota_tdb_api_ins2.Create_Enrollment(p_booking_id => l_booking_id
586 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
587 ,p_delegate_person_id => l_delegate_id
588 ,p_delegate_contact_id => p_delegate_contact_id
589 ,p_customer_id => l_customer_id
590 ,p_contact_id => null
591 ,p_business_group_id => ota_general.get_business_group_id
592 ,p_event_id => p_event_id
593 --,p_date_booking_placed => trunc(sysdate)
594 ,p_date_booking_placed => sysdate
595 ,p_corespondent => l_corespondent
596 ,p_internal_booking_flag => l_internal_booking_flag
597 ,p_person_address_type => l_person_address_type
598 ,p_number_of_places => 1
599 ,p_object_version_number => l_object_version_number
600 ,p_delegate_contact_phone => l_work_telephone
601 ,p_delegate_contact_fax => l_work_fax
602 ,p_source_of_booking => l_source_of_booking
603 ,p_special_booking_instructions => p_extra_information
604 ,p_successful_attendance_flag => 'N'
605 ,p_finance_line_id => l_finance_line_id
606 ,p_enrollment_type => l_enrollment_type
607 ,p_validate => FALSE
608 ,p_organization_id => l_organization_id
609 ,p_delegate_assignment_id => l_assignment_id
610 ,p_delegate_contact_email => l_email_address
611 ,p_tdb_information_category => p_tdb_information_category
612 ,p_tdb_information1 => p_tdb_information1
613 ,p_tdb_information2 => p_tdb_information2
614 ,p_tdb_information3 => p_tdb_information3
615 ,p_tdb_information4 => p_tdb_information4
616 ,p_tdb_information5 => p_tdb_information5
617 ,p_tdb_information6 => p_tdb_information6
618 ,p_tdb_information7 => p_tdb_information7
619 ,p_tdb_information8 => p_tdb_information8
620 ,p_tdb_information9 => p_tdb_information9
621 ,p_tdb_information10 => p_tdb_information10
622 ,p_tdb_information11 => p_tdb_information11
623 ,p_tdb_information12 => p_tdb_information12
624 ,p_tdb_information13 => p_tdb_information13
625 ,p_tdb_information14 => p_tdb_information14
626 ,p_tdb_information15 => p_tdb_information15
627 ,p_tdb_information16 => p_tdb_information16
628 ,p_tdb_information17 => p_tdb_information17
629 ,p_tdb_information18 => p_tdb_information18
630 ,p_tdb_information19 => p_tdb_information19
631 ,p_tdb_information20 => p_tdb_information20
632 ,p_booking_justification_id => p_booking_justification_id
633 ,p_booking_priority => l_priority_level);
634
635
636 END IF;
637 p_booking_id := l_booking_id;
638
639 IF l_booking_id is not null then
640
641 IF l_booking_status_used = 'PLACED' then
642 p_message_name := 'OTA_443526_CONFIRMED_PLACED';
643 ELSIF l_booking_status_used = 'WAITLISTED' then
644 p_message_name := 'OTA_443527_CONFIRMED_WAITLIST';
645 ELSIF l_booking_status_used = 'REQUESTED' then
646 p_message_name := 'OTA_443528_CONFIRMED_REQUESTED';
647 END IF;
648 END IF;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 -- Both the Confirm Procedure and the API return APP-20002 or -20001
653 -- so provide error can be used, as if the confirm procedure errors
654 -- a different tool bar will be used.
655 -- If the API has errored, the WF won't have been activated
656 -- whereas if the confirm procedure errored, then it probably will have
657 -- been.
658 -- p_mode will be changed to indicate an error and,if it's a WF error
659 -- the mode will also indicate this.
660 -- Then the "Confirmation" page will be called from the main handler.
661 --
662 -- It is OK to use hr_message.provide_error as an application
663 -- error will have been raised which will have put an error onto
664 -- the stack
665 --
666 /* IF (hr_message.last_message_name = 'OTA_13668_WEB_NO_TRANMISSION')
667 THEN
668 --
669 -- The WF may have already be transissioned, so change the mode.
670 l_mode := l_mode || 'BADWF';
671 --
672
673 END IF; */
674 p_message_name := fnd_message.get;
675 -- p_message_name := SUBSTR(SQLERRM, 1,300);
676 --
677 END; -- End of if p_message is not null
678
679 END IF;
680 EXCEPTION
681 WHEN OTHERS THEN
682 p_message_name := SUBSTR(SQLERRM, 1,300);
683 END ProcessSaveEnrollment;
684
685 -- -----------------------------------------------------
686 -- Procedure cancel_finance
687 -- -----------------------------------------------------
688 PROCEDURE cancel_finance(p_booking_id in number)
689 IS
690
691 -- ------------------------
692 -- Finance_cur Variables
693 -- ------------------------
694 l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
695 l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
696 l_transfer_status ota_finance_lines.transfer_status%TYPE;
697 lf_booking_id ota_finance_lines.booking_id%TYPE;
698 lf_object_version_number ota_finance_lines.object_version_number%TYPE;
699 l_sequence_number ota_finance_lines.sequence_number%TYPE;
700 l_raised_date date;
701 l_finance_count number(10);
702 l_cancelled_flag ota_finance_lines.cancelled_flag%type;
703 l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
704 -- ------------------------
705 -- header_cur Variables
706 -- ------------------------
707 lh_finance_header_id ota_finance_headers.finance_header_id%TYPE;
708 lh_cancelled_flag ota_finance_headers.cancelled_flag%TYPE;
709 lh_transfer_status ota_finance_headers.transfer_status%TYPE;
710 lh_object_version_number ota_finance_headers.object_version_number%TYPE;
711
712 l_hours_until_class_starts NUMBER;
713 l_minimum_advance_notice NUMBER;
714 l_event_id ota_events.event_id%TYPE;
715 l_course_start_date ota_events.course_start_date%TYPE;
716 l_course_start_time ota_events.course_start_time%TYPE;
717 l_course_end_date ota_events.course_start_date%TYPE;
718 l_date_booking_placed ota_delegate_bookings.date_booking_placed%TYPE;
719 l_content_player_status ota_delegate_bookings.content_player_status%TYPE;
720 l_object_version_number ota_delegate_bookings.object_version_number%TYPE;
721 l_delegate_id ota_delegate_bookings.delegate_person_id%TYPE;
722 l_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE;
723 l_tmp_booking_id ota_delegate_bookings.booking_id%TYPE;
724 l_cancel_boolean BOOLEAN;
725
726 CURSOR finance (p_booking_id ota_finance_lines.booking_id%TYPE) IS
727 SELECT FLN.finance_line_id finance_line_id,
728 FLN.finance_header_id finance_header_id,
729 FLN.transfer_status transfer_status,
730 FLN.booking_id booking_id,
731 FLN.object_version_number object_version_number,
732 FLN.sequence_number sequence_number,
733 FLN.Cancelled_flag cancelled_flag
734 FROM OTA_FINANCE_LINES FLN
735 WHERE FLN.booking_id = p_booking_id;
736
737 CURSOR finance_count (p_finance_header_id ota_finance_lines.finance_header_id%TYPE) IS
738 SELECT count(*)
739 FROM OTA_FINANCE_LINES FLN
740 WHERE FLN.finance_header_id = p_finance_header_id;
741
742 CURSOR header (p_booking_id ota_finance_lines.booking_id%TYPE) IS
743 SELECT FLH.finance_header_id finance_header_id,
744 FLH.cancelled_flag cancelled_flag,
745 FLH.transfer_status transfer_status,
746 FLH.object_version_number object_version_number
747 FROM OTA_FINANCE_HEADERS FLH,
748 OTA_FINANCE_LINES FLN
749 WHERE FLH.finance_header_id = FLN.finance_header_id
750 AND FLN.booking_id = p_booking_id;
751
752 CURSOR event_csr (p_event_id ota_events.event_id%TYPE) IS
753 SELECT e.course_start_date,
754 e.course_start_time,
755 e.course_end_date
756 FROM ota_events_vl e,
757 ota_offerings o,
758 ota_activity_versions_tl a
759 WHERE e.parent_offering_id = o.offering_id
760 AND o.activity_version_id = a.activity_version_id
761 AND e.event_id = p_event_id
762 AND language=userenv('LANG');
763
764 CURSOR booking_csr (p_booking_id ota_delegate_bookings.booking_id%TYPE) IS
765 SELECT b.event_id,
766 b.delegate_person_id,
767 b.delegate_contact_id,
768 b.date_booking_placed,
769 b.content_player_status,
770 b.object_version_number
771 FROM ota_delegate_bookings b
772 WHERE b.booking_id = p_booking_id;
773
774 BEGIN
775
776 OPEN booking_csr (p_booking_id);
777 FETCH booking_csr INTO l_event_id,
778 l_delegate_id,
779 l_delegate_contact_id,
780 l_date_booking_placed,
781 l_content_player_status,
782 l_object_version_number;
783 CLOSE booking_csr;
784
785 OPEN event_csr (l_event_id);
786 FETCH event_csr INTO l_course_start_date,
787 l_course_start_time,
788 l_course_end_date;
789 CLOSE event_csr;
790
791 OPEN finance (p_booking_id);
792 FETCH finance INTO l_finance_line_id,
793 l_finance_header_id,
794 l_transfer_status,
795 lf_booking_id,
796 lf_object_version_number,
797 l_sequence_number,
798 l_cancelled_flag ;
799
800 l_minimum_advance_notice := NVL(TO_NUMBER(fnd_profile.value('OTA_CANCEL_HOURS_BEFORE_EVENT')), 0);
801 l_hours_until_class_starts := 0;
802 IF ( l_course_start_date IS NOT NULL ) THEN
803 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);
804 END IF;
805
806 l_cancel_boolean := Chk_Event_Cancelled_for_Person(p_event_id => l_event_id
807 ,p_delegate_person_id => l_delegate_id
808 ,p_delegate_contact_id => l_delegate_contact_id
809 ,p_booking_id => l_tmp_booking_id);
810
811 IF (finance%found and l_cancel_boolean) THEN
812 IF (l_transfer_status = 'ST' OR
813 l_cancelled_flag = 'Y' OR
814 l_content_player_status IS NOT NULL OR
815 l_hours_until_class_starts < l_minimum_advance_notice) THEN
816 NULL;
817 ELSE
818 OPEN header (p_booking_id);
819 FETCH header INTO lh_finance_header_id,
820 lh_cancelled_flag,
821 lh_transfer_status,
822 lh_object_version_number;
823 CLOSE header;
824 IF lh_transfer_status <> 'ST' or lh_cancelled_flag <>'Y' THEN
825 l_raised_date := sysdate;
826 ota_tfh_api_business_rules.cancel_header(p_finance_header_id => lh_finance_header_id
827 ,p_cancel_header_id => l_cancel_header_id
828 ,p_date_raised => l_raised_date
829 ,p_validate => false
830 ,p_commit => false);
831 END IF;
832 END IF;
833 END IF;
834 CLOSE finance;
835 END cancel_finance;
836
837 --
838 --
839 -- ----------------------------------------------------------------------------
840 -- |----------------------<Get_Booking_Status_For_Web >-----------------------|
841 -- ----------------------------------------------------------------------------
842 -- {Start Of Comments}
843 --
844 -- Description:
845 -- This package is used for self service application to create enrollment
846 -- data when user enroll in the class.
847 --
848 -- Prerequisites:
849 --
850 -- In Parameters:
851 --
852 -- Post Success:
853 -- Enrollment data will be created.
854 --
855 -- Post Failure:
856 -- Status will be passed to the caller and the caller will raise a notification.
857 --
858 -- Developer Implementation Notes:
859 -- The attrbute in parameters should be modified as to the business process
860 -- requirements.
861 --
862 -- Access Status:
863 -- Internal Development Use Only.
864 --
865 -- {End Of Comments}
866 -- ----------------------------------------------------------------------------
867
868
869 FUNCTION Get_Booking_Status_For_Web (p_web_booking_status_type VARCHAR2
870 ,p_business_group_id NUMBER)
871 RETURN OTA_BOOKING_STATUS_TYPES%ROWTYPE
872
873 IS
874
875 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE DEFAULT NULL;
876
877
878 BEGIN
879
880 OPEN csr_booking_status_id (p_business_group_id => p_business_group_id
881 ,p_web_booking_status_type => p_web_booking_status_type);
882 FETCH csr_booking_status_id INTO l_booking_status_row;
883
884 IF ( csr_booking_status_id%NOTFOUND ) THEN
885 --
886 CLOSE csr_booking_status_id;
887 --
888 -- Seed a technical message so that if the calling procedure decides
889 -- that it is an error having nothing returned, it can use it.
890 --
891 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
892 Fnd_message.raise_error;
893 --
894 RETURN l_booking_status_row;
895 --
896 ELSE
897 --
898 CLOSE csr_booking_status_id;
899 --
900 RETURN l_booking_status_row;
901 --
902 END IF;
903 RETURN l_booking_status_row;
904 END Get_Booking_Status_For_Web;
905 --
906
907 --
908 -- ----------------------------------------------------------------------------
909 -- |-----------------------------<Check_Cost_Center>--------------------------|
910 -- ----------------------------------------------------------------------------
911 -- {Start Of Comments}
912 --
913 -- Description:
914 -- This package is used for self service application to create enrollment
915 -- data when user enroll in the class.
916 --
917 -- Prerequisites:
918 --
919 -- In Parameters:
920 --
921 -- Post Success:
922 -- Enrollment data will be created.
923 --
924 -- Post Failure:
925 -- Status will be passed to the caller and the caller will raise a notification.
926 --
927 -- Developer Implementation Notes:
928 -- The attrbute in parameters should be modified as to the business process
929 -- requirements.
930 --
931 -- Access Status:
932 -- Internal Development Use Only.
933 --
934 -- {End Of Comments}
935 -- ----------------------------------------------------------------------------
936 Procedure check_cost_center
937 (p_person_id in number,
938 p_no_cost_center out nocopy number,
939 p_cost_alloc_keyflex_id out nocopy number,
940 p_business_group_id out nocopy number,
941 p_assignment_id out nocopy number,
942 p_organization_id out nocopy number,
943 p_cost_center out nocopy varchar2
944 )
945 IS
946
947 Cursor csr_cost IS
948 SELECT assg.assignment_id,
949 assg.business_group_id,
950 assg.organization_id,
951 pcak.cost_allocation_keyflex_id,
952 pcak.concatenated_segments,
953 pcaf.proportion
954 FROM per_all_people_f per,
955 per_all_assignments_f assg,
956 pay_cost_allocations_f pcaf,
957 pay_cost_allocation_keyflex pcak
958 WHERE per.person_id = p_person_id
959 AND per.person_id = assg.person_id
960 AND assg.assignment_id = pcaf.assignment_id
961 AND assg.Primary_flag = 'Y'
962 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
963 AND pcak.enabled_flag = 'Y'
964 AND sysdate between nvl(pcaf.effective_start_date,sysdate)
965 and nvl(pcaf.effective_end_date,sysdate+1)
966 AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
967 and nvl(assg.effective_end_date,trunc(sysdate+1))
968 AND trunc(sysdate) between nvl(per.effective_start_date,trunc(sysdate))
969 and nvl(per.effective_end_date,trunc(sysdate+1));
970
971
972 CURSOR get_assignment(p_delegate_id per_all_people_f.person_id%TYPE) IS
973 SELECT assg.assignment_id,
974 assg.business_group_id,
975 assg.organization_id
976 FROM per_all_people_f per,
977 per_all_assignments_f assg
978 WHERE per.person_id = p_delegate_id
979 AND per.person_id = assg.person_id
980 AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
981 and nvl(assg.effective_end_date,trunc(sysdate+1))
982 AND assg.primary_flag = 'Y'
983 AND trunc(sysdate) between nvl(per.effective_start_date,trunc(sysdate))
984 and nvl(per.effective_end_date,trunc(sysdate+1));
985
986
987 l_proc varchar2(72) := g_package||'return_api_dml_status';
988 l_no number := 0;
989 BEGIN
990
991 --
992 hr_utility.set_location('Entering:'||l_proc, 5);
993 --
994 For a in csr_cost loop
995 l_no := l_no+1;
996 p_cost_alloc_keyflex_id :=a.cost_allocation_keyflex_id;
997 p_business_group_id :=a.business_group_id ;
998 p_assignment_id :=a.assignment_id ;
999 p_organization_id :=a.organization_id ;
1000 --Bug#2228669 hdshah proportion and Percentage sign included.
1001 p_cost_center := a.concatenated_segments ||' ----- '|| a.proportion*100 ||' %';
1002 end loop;
1003 p_no_cost_center := l_no;
1004
1005 if l_no = 0 then
1006 for a in get_assignment(p_person_id) loop
1007 p_business_group_id :=a.business_group_id ;
1008 p_assignment_id :=a.assignment_id ;
1009 p_organization_id :=a.organization_id ;
1010 end loop;
1011 end if;
1012 --
1013 hr_utility.set_location(' Leaving:'||l_proc, 10);
1014
1015 END check_cost_center;
1016
1017 Procedure supervisor_exists ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1018 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1019 actid IN NUMBER,
1020 funcmode IN VARCHAR2,
1021 resultout OUT nocopy VARCHAR2 ) IS
1022
1023 l_supervisor_username fnd_user.user_name%TYPE;
1024
1025 BEGIN
1026
1027 IF (funcmode='RUN') THEN
1028 l_supervisor_username := wf_engine.getItemAttrText ( itemtype
1029 ,itemkey
1030 , 'SUPERVISOR_USERNAME');
1031 if l_supervisor_username is not null then
1032 resultout:='COMPLETE:Y';
1033 RETURN;
1034 else
1035 resultout:='COMPLETE:N';
1036 RETURN;
1037 end if;
1038 END IF;
1039
1040 IF (funcmode='CANCEL') THEN
1041 resultout:='COMPLETE:Y';
1042 RETURN;
1043 END IF;
1044
1045 END supervisor_exists;
1046
1047 --
1048 -- ----------------------------------------------------------------------------
1049 -- |----------------------<Get_Person_To_Enroll_Details >----------------------|
1050 -- ----------------------------------------------------------------------------
1051 -- {Start Of Comments}
1052 --
1053 -- Description:
1054 -- This package is used for self service application to create enrollment
1055 -- data when user enroll in the class.
1056 --
1057 -- Prerequisites:
1058 --
1059 -- In Parameters:
1060 --
1061 -- Post Success:
1062 -- Enrollment data will be created.
1063 --
1064 -- Post Failure:
1065 -- Status will be passed to the caller and the caller will raise a notification.
1066 --
1067 -- Developer Implementation Notes:
1068 -- The attrbute in parameters should be modified as to the business process
1069 -- requirements.
1070 --
1071 -- Access Status:
1072 -- Internal Development Use Only.
1073 --
1074 -- {End Of Comments}
1075 -- ----------------------------------------------------------------------------
1076
1077 FUNCTION Get_Person_To_Enroll_Details (p_person_id per_all_people_f.PERSON_ID%TYPE)
1078 RETURN csr_person_to_enroll_details%ROWTYPE
1079
1080 IS
1081 --
1082 l_csr_person_to_enroll_details csr_person_to_enroll_details%ROWTYPE;
1083 --
1084 l_person_id BINARY_INTEGER;
1085
1086 BEGIN
1087 --
1088 OPEN csr_person_to_enroll_details (p_person_id);
1089 --
1090 FETCH csr_person_to_enroll_details INTO l_csr_person_to_enroll_details;
1091 --
1092 IF csr_person_to_enroll_details%NOTFOUND THEN
1093 CLOSE csr_person_to_enroll_details;
1094 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
1095 RAISE g_mesg_on_stack_exception ;
1096 ELSE
1097 CLOSE csr_person_to_enroll_details;
1098 RETURN l_csr_person_to_enroll_details;
1099 END IF;
1100
1101 EXCEPTION
1102 WHEN g_mesg_on_stack_exception THEN
1103 --
1104 -- Handle the exception in the calling code.
1105 RAISE;
1106
1107 WHEN OTHERS THEN
1108 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
1109 RAISE;
1110
1111 END Get_Person_To_Enroll_Details;
1112
1113
1114 -- ----------------------------------------------------------------------------
1115 -- |----------------------<Get_ext_lrnr_Details >----------------------|
1116 -- ----------------------------------------------------------------------------
1117 -- {Start Of Comments}
1118 --
1119 -- Description:
1120 -- This package is used for self service application to create enrollment
1121 -- data when user enroll in the class.
1122 --
1123 -- Prerequisites:
1124 --
1125 -- In Parameters:
1126 --
1127 -- Post Success:
1128 -- Enrollment data will be created.
1129 --
1130 -- Post Failure:
1131 -- Status will be passed to the caller and the caller will raise a notification.
1132 --
1133 -- Developer Implementation Notes:
1134 -- The attrbute in parameters should be modified as to the business process
1135 -- requirements.
1136 --
1137 -- Access Status:
1138 -- Internal Development Use Only.
1139 --
1140 -- {End Of Comments}
1141 -- ----------------------------------------------------------------------------
1142
1143 FUNCTION Get_ext_lrnr_Details (p_delegate_contact_id IN NUMBER)
1144 RETURN csr_ext_lrnr_Details%ROWTYPE
1145
1146 IS
1147 --
1148 l_csr_ext_lrnr_Details csr_ext_lrnr_Details%ROWTYPE;
1149
1150
1151 BEGIN
1152 --
1153 OPEN csr_ext_lrnr_Details (p_delegate_contact_id);
1154 --
1155 FETCH csr_ext_lrnr_Details INTO l_csr_ext_lrnr_Details;
1156 --
1157 IF csr_ext_lrnr_Details%NOTFOUND THEN
1158 CLOSE csr_ext_lrnr_Details;
1159 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
1160 RAISE g_mesg_on_stack_exception ;
1161 ELSE
1162 CLOSE csr_ext_lrnr_Details;
1163 RETURN l_csr_ext_lrnr_Details;
1164 END IF;
1165
1166 EXCEPTION
1167 WHEN g_mesg_on_stack_exception THEN
1168 --
1169 -- Handle the exception in the calling code.
1170 RAISE;
1171
1172 WHEN OTHERS THEN
1173 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
1174 RAISE;
1175
1176 END Get_ext_lrnr_Details;
1177
1178 --
1179 -- ----------------------------------------------------------------------------
1180 -- |-------------------------------<Validate_Enrollment>----------------------|
1181 -- ----------------------------------------------------------------------------
1182 -- {Start Of Comments}
1183 --
1184 -- Description:
1185 -- This package is used for self service application to create enrollment
1186 -- data when user enroll in the class.
1187 --
1188 -- Prerequisites:
1189 --
1190 -- In Parameters:
1191 --
1192 -- Post Success:
1193 -- Enrollment data will be created.
1194 --
1195 -- Post Failure:
1196 -- Status will be passed to the caller and the caller will raise a notification.
1197 --
1198 -- Developer Implementation Notes:
1199 -- The attrbute in parameters should be modified as to the business process
1200 -- requirements.
1201 --
1202 -- Access Status:
1203 -- Internal Development Use Only.
1204 --
1205 -- {End Of Comments}
1206 -- ----------------------------------------------------------------------------
1207
1208 PROCEDURE Validate_enrollment(p_person_id per_all_people_f.PERSON_ID%TYPE,
1209 p_delegate_contact_id IN NUMBER,
1210 p_event_id IN VARCHAR2,
1211 p_double_book out nocopy VARCHAR2 )
1212
1213 IS
1214
1215 l_person_details
1216 csr_person_to_enroll_details%ROWTYPE;
1217
1218 l_ext_lrnr_details
1219 csr_ext_lrnr_Details%ROWTYPE;
1220 l_cancel_boolean boolean;
1221 l_dummy number;
1222 Begin
1223 p_double_book := 'N';
1224
1225 IF p_person_id IS NOT NULL THEN
1226 l_person_details := Get_Person_To_Enroll_Details(p_person_id => p_person_id);
1227 ELSIF p_delegate_contact_id IS NOT NULL THEN
1228 l_ext_lrnr_details := Get_ext_lrnr_Details(p_delegate_contact_id);
1229 END IF;
1230
1231 l_cancel_boolean :=
1232 Chk_Event_Cancelled_for_Person
1233 (p_event_id => p_event_id
1234 ,p_delegate_person_id => p_person_id
1235 ,p_delegate_contact_id => p_delegate_contact_id
1236 ,p_booking_id => l_dummy);
1237 IF (l_cancel_boolean) THEN
1238 -- Delegate has Cancelled status, so dont check for unique_booking
1239 -- as a row exists for delegate, for this event
1240 null;
1241 ELSE
1242 IF p_person_id IS NOT NULL THEN
1243 ota_tdb_bus.check_unique_booking
1244 (p_customer_id => ''
1245 ,p_organization_id => l_person_details.organization_id
1246 ,p_event_id => p_event_id
1247 ,p_delegate_person_id => p_person_id
1248 ,p_delegate_contact_id => ''
1249 ,p_booking_id => '');
1250 ELSIF p_delegate_contact_id IS NOT NULL THEN
1251 ota_tdb_bus.check_unique_booking
1252 (p_customer_id => l_ext_lrnr_details.customer_id
1253 ,p_organization_id => ''
1254 ,p_event_id => p_event_id
1255 ,p_delegate_person_id => ''
1256 ,p_delegate_contact_id => p_delegate_contact_id
1257 ,p_booking_id => '');
1258 END IF;
1259 END IF;
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 p_double_book := 'Y';
1263
1264
1265 end Validate_Enrollment;
1266
1267
1268 -- |--------------------------------------------------------------------------|
1269 -- |--< Chk_Event_Cancelled_for_Person >--------------------------------------|
1270 -- |--------------------------------------------------------------------------|
1271 --
1272 -- {Start Of Comments}
1273 --
1274 -- Description:
1275 -- This package is used for self service application to create enrollment
1276 -- data when user enroll in the class.
1277 --
1278 -- Prerequisites:
1279 --
1280 -- In Parameters:
1281 --
1282 -- Post Success:
1283 -- Enrollment data will be created.
1284 --
1285 -- Post Failure:
1286 -- Status will be passed to the caller and the caller will raise a notification.
1287 --
1288 -- Developer Implementation Notes:
1289 -- The attrbute in parameters should be modified as to the business process
1290 -- requirements.
1291 --
1292 -- Access Status:
1293 -- Internal Development Use Only.
1294 --
1295 -- {End Of Comments}
1296 -- ----------------------------------------------------------------------------
1297 FUNCTION Chk_Event_Cancelled_for_Person (p_event_id IN NUMBER
1298 ,p_delegate_person_id IN NUMBER
1299 ,p_delegate_contact_id IN NUMBER
1300 ,p_booking_id OUT nocopy NUMBER)
1301 RETURN BOOLEAN
1302
1303 IS
1304
1305 CURSOR csr_chk_event
1306 (p_event_id IN NUMBER
1307 ,p_person_id IN NUMBER
1308 ,p_delegate_contact_id IN NUMBER) IS
1309 SELECT ov.booking_id
1310 FROM ota_booking_status_types os,
1311 ota_delegate_bookings ov
1312 WHERE ov.event_id = p_event_id
1313 AND (p_person_id IS NOT NULL AND ov.delegate_person_id = p_person_id
1314 OR p_delegate_contact_id IS NOT NULL and ov.delegate_contact_id = p_delegate_contact_id)
1315 AND os.booking_status_type_id = ov.booking_status_type_id
1316 AND os.type = 'C';
1317
1318 CURSOR csr_chk_event_placed
1319 (p_event_id IN NUMBER
1320 ,p_person_id IN NUMBER
1321 ,p_delegate_contact_id IN NUMBER) IS
1322 SELECT ov.booking_id
1323 FROM ota_booking_status_types os,
1324 ota_delegate_bookings ov
1325 WHERE ov.event_id = p_event_id
1326 AND (p_person_id IS NOT NULL AND ov.delegate_person_id = p_person_id
1327 OR p_delegate_contact_id IS NOT NULL and ov.delegate_contact_id = p_delegate_contact_id)
1328 AND os.booking_status_type_id = ov.booking_status_type_id
1329 AND os.type <> 'C';
1330
1331 l_temp csr_chk_event%rowtype;
1332 l_enroll_exist boolean := False;
1333
1334 BEGIN
1335
1336 OPEN csr_chk_event(p_event_id, p_delegate_person_id,p_delegate_contact_id);
1337 FETCH csr_chk_event INTO l_temp;
1338
1339 p_booking_id := l_temp.booking_id;
1340
1341 IF csr_chk_event%FOUND THEN
1342
1343
1344 For r_enroll in csr_chk_event_placed(p_event_id, p_delegate_person_id,p_delegate_contact_id)
1345 LOOP
1346 if r_enroll.booking_id is not null then
1347 l_enroll_exist := True;
1348 end if;
1349 END LOOP;
1350 if l_enroll_exist then
1351 RETURN FALSE;
1352 else
1353 RETURN TRUE;
1354 end if;
1355
1356
1357 ELSE
1358 -- PERSON DOES NOT HAVE A BOOKING STATUS OF CANCELLED FOR THIS EVENT
1359 --
1360 RETURN FALSE;
1361 --
1362 END IF;
1363
1364 -- p_booking_id := l_temp.booking_id;
1365 CLOSE csr_chk_event;
1366 END Chk_Event_Cancelled_for_Person;
1367
1368 -- |--------------------------------------------------------------------------|
1369 -- |---------------< Chk_booking_clash >--------------------------------------|
1370 -- |--------------------------------------------------------------------------|
1371 --
1372 -- {Start Of Comments}
1373 --
1374 -- Description:
1375 -- This package is used for self service application to create enrollment
1376 -- data when user enroll in the class.
1377 --
1378 -- Prerequisites:
1379 --
1380 -- In Parameters:
1381 --
1382 -- Post Success:
1383 -- Enrollment data will be created.
1384 --
1385 -- Post Failure:
1386 -- Status will be passed to the caller and the caller will raise a notification.
1387 --
1388 -- Developer Implementation Notes:
1389 -- The attrbute in parameters should be modified as to the business process
1390 -- requirements.
1391 --
1392 -- Access Status:
1393 -- Internal Development Use Only.
1394 --
1395 -- {End Of Comments}
1396 -- ----------------------------------------------------------------------------
1397 procedure Chk_booking_clash (p_event_id IN NUMBER
1398 ,p_person_id IN NUMBER
1399 -- Added for External Learner support
1400 ,p_delegate_contact_id IN NUMBER
1401 ,p_booking_Clash OUT nocopy varchar2)
1402 IS
1403 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
1404 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1405
1406
1407 Begin
1408
1409 l_booking_status_row := Get_Booking_Status_for_web
1410 (p_web_booking_status_type => 'PLACED'
1411 ,p_business_group_id => ota_general.get_business_group_id);
1412
1413 l_booking_status_type_id := l_booking_status_row.booking_status_type_id;
1414
1415 IF ota_tdb_bus2.other_bookings_clash(
1416 p_delegate_person_id => p_person_id
1417 -- Modified for External Learner Support
1418 -- ,p_delegate_contact_id=> ''
1419 ,p_delegate_contact_id=> p_delegate_contact_id
1420 ,p_event_id => p_event_id
1421 ,p_booking_status_type_id => l_booking_status_type_id ) THEN
1422
1423 p_booking_clash := 'Y';
1424 ELSE
1425 p_booking_clash := 'N';
1426
1427 END IF;
1428
1429 EXCEPTION
1430 When OTHERS Then
1431 p_booking_Clash := fnd_message.get();
1432 If p_booking_Clash is NULL then
1433 p_booking_Clash := substr(SQLERRM,11,(length(SQLERRM)-10));
1434 End If;
1435
1436
1437 end Chk_booking_clash;
1438
1439 --|--------------------------------------------------------------------------|
1440 --|--< CHK_FOR_SECURE_EVT >-------------------------------------------|
1441 --|--------------------------------------------------------------------------|
1442
1443 FUNCTION CHK_FOR_SECURE_EVT (
1444 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1445 ,p_delegate_contact_id IN NUMBER
1446 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
1447 RETURN VARCHAR2
1448 IS
1449
1450 CURSOR C_GET_SECURE_FLAG is
1451 SELECT secure_event_flag,organization_id
1452 from ota_events
1453 where event_id = p_event_id;
1454
1455 CURSOR C_GET_ORG_ID is
1456 SELECT organization_id
1457 from per_all_assignments_f
1458 where person_id = p_delegate_id and
1459 trunc(sysdate) between effective_start_date and
1460 effective_end_date;
1461
1462
1463 l_secure_flag varchar2(1);
1464 l_evt_organization_id OTA_EVENTS.ORGANIZATION_ID%TYPE;
1465 l_per_organization_id PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE;
1466 l_return_value varchar2(2000);
1467
1468 BEGIN
1469
1470 OPEN C_GET_SECURE_FLAG;
1471 FETCH C_GET_SECURE_FLAG into l_secure_flag,l_evt_organization_id;
1472 IF C_GET_SECURE_FLAG%NOTFOUND then
1473 CLOSE C_GET_SECURE_FLAG;
1474 l_return_value := NULL;
1475 return l_return_value;
1476 ELSE
1477 CLOSE C_GET_SECURE_FLAG;
1478 END IF;
1479 IF l_secure_flag = 'Y' then
1480
1481 IF p_delegate_id IS NOT NULL THEN
1482 OPEN C_GET_ORG_ID;
1483 FETCH C_GET_ORG_ID into l_per_organization_id;
1484 IF C_GET_ORG_ID%NOTFOUND then
1485 CLOSE C_GET_ORG_ID;
1486 l_return_value := NULL;
1487 return l_return_value;
1488 ELSE
1489 CLOSE C_GET_ORG_ID;
1490 END IF;
1491 ELSE
1492 l_per_organization_id := ota_general.get_business_group_id;
1493 END IF;
1494 if l_per_organization_id = l_evt_organization_id then
1495 l_return_value := '-1';
1496 else
1497 l_return_value := NULL;
1498 end if;
1499 ELSE
1500 l_return_value := '-1';
1501
1502 END IF;
1503
1504 RETURN l_return_value;
1505
1506 END CHK_FOR_SECURE_EVT;
1507
1508
1509 --|--------------------------------------------------------------------------|
1510 --|--< CHK_DELEGATE_OK_FOR_EVENT>-------------------------------------------|
1511 --|--------------------------------------------------------------------------|
1512
1513 FUNCTION CHK_DELEGATE_OK_FOR_EVENT (
1514 p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1515 , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
1516 , p_event_start_date IN OTA_EVENTS.COURSE_START_DATE%TYPE
1517 )
1518 RETURN VARCHAR2
1519
1520 IS
1521 CURSOR csr_event_associations IS
1522 SELECT ea.organization_id, ea.job_id, ea.position_id
1523 FROM ota_event_associations ea
1524 WHERE ea.event_id = p_event_id;
1525 --
1526
1527 CURSOR csr_event_start_date IS
1528 SELECT course_start_date
1529 -- FROM otv_scheduled_events
1530 FROM ota_events
1531 WHERE event_id = p_event_id and
1532 --Bug#2201434 SELFPACED event_type included.
1533 -- event_type='SCHEDULED' and
1534 event_type in ('SCHEDULED','SELFPACED') and
1535 event_status in('P','N','F')and
1536 TRUNC(SYSDATE) BETWEEN NVL( ENROLMENT_START_DATE, TRUNC(
1537 SYSDATE)) AND
1538 NVL( ENROLMENT_END_DATE, TRUNC( SYSDATE)) AND
1539 TRUNC(SYSDATE) <= NVL( COURSE_END_DATE, TRUNC(SYSDATE));
1540 --
1541 CURSOR csr_asg_details
1542 (p_organization_id OTA_EVENT_ASSOCIATIONS.organization_id%TYPE
1543 ,p_job_id OTA_EVENT_ASSOCIATIONS.job_id%TYPE
1544 ,p_position_id OTA_EVENT_ASSOCIATIONS.position_id%TYPE
1545 ,p_course_start_date
1546 otv_scheduled_events.course_start_date%type) IS
1547 SELECT a.assignment_id
1548 FROM per_all_assignments_f a
1549 WHERE a.person_id = p_delegate_id
1550 --Modified for bug#5032859
1551 AND( nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
1552 OR
1553 NVL(p_course_start_date,trunc(sysdate)) BETWEEN
1554 a.effective_start_date AND a.effective_end_date
1555 )
1556 AND NVL(p_organization_id, -1) = DECODE(p_organization_id, null, -1,
1557 NVL(a.organization_id,-1))
1558 AND NVL(p_job_id, -1) = DECODE(p_job_id, null, -1,
1559 NVL(a.job_id, -1))
1560 AND NVL(p_position_id,-1) = DECODE(p_position_id, null, -1,
1561 NVL(a.position_id, -1))
1562 AND a.assignment_type = 'E';
1563 --
1564 l_return_value varchar2(4000);
1565 l_event_start_date otv_scheduled_events.course_start_date%type;
1566
1567 BEGIN
1568 --Bug#2201434 default value of l_return_value modified to '-1' from null.
1569 -- l_return_value := null;
1570 l_return_value := '-1';
1571 IF p_event_start_date IS null THEN
1572 OPEN csr_event_start_date;
1573 FETCH csr_event_start_date INTO l_event_start_date;
1574 CLOSE csr_event_start_date;
1575 ELSE
1576 l_event_start_date := p_event_start_date;
1577 END IF;
1578
1579 FOR assoc IN csr_event_associations LOOP
1580 -- For each of the event restrictions, loop
1581 OPEN csr_asg_details
1582 (p_organization_id => assoc.organization_id
1583 ,p_job_id => assoc.job_id
1584 ,p_position_id => assoc.position_id
1585 ,p_course_start_date => l_event_start_date);
1586 FETCH csr_asg_details INTO l_return_value;
1587 IF (csr_asg_details%FOUND) THEN
1588 l_return_value := '-1';
1589 CLOSE csr_asg_details;
1590 EXIT;
1591 ELSE
1592 -- The delegate hasn't got an assignment which satified the needs
1593 -- of all the event associations, so set the return value to 'N'
1594 CLOSE csr_asg_details;
1595 l_return_value := NULL;
1596 END IF;
1597 END LOOP;
1598
1599 RETURN l_return_value;
1600 END CHK_DELEGATE_OK_FOR_EVENT;
1601
1602 --|--------------------------------------------------------------------------|
1603 --|--< CHK_FOR_RESTRICTED_EVT >-------------------------------------------|
1604 --|--------------------------------------------------------------------------|
1605
1606 FUNCTION CHK_FOR_RESTRICTED_EVT (
1607 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE,
1608 p_delegate_contact_id IN NUMBER)
1609 RETURN VARCHAR2
1610 IS
1611 CURSOR C_GET_EVT_DETAILS is
1612 SELECT EVT.public_event_flag,EVT.maximum_internal_attendees
1613 from ota_events EVT
1614 where EVT.event_id = p_event_id;
1615
1616 CURSOR C_GET_EVT_ACCESS_DETAILS(l_customer_id NUMBER) is
1617 SELECT EVT.public_event_flag,EVT.maximum_internal_attendees
1618 from ota_events EVT, ota_event_associations EVA
1619 where EVT.event_id = p_event_id and
1620 EVT.event_id = EVA.event_id and
1621 EVA.customer_id = l_customer_id;
1622
1623 l_public_event_flag OTA_EVENTS.PUBLIC_EVENT_FLAG%TYPE;
1624 l_maximum_internal_attendees OTA_EVENTS.MAXIMUM_INTERNAL_ATTENDEES%TYPE;
1625 l_return_value varchar2(10);
1626
1627 l_ext_lrnr_details csr_ext_lrnr_Details%ROWTYPE;
1628 l_customer_id NUMBER := null;
1629 BEGIN
1630
1631 IF p_delegate_contact_id IS NOT NULL THEN
1632 l_ext_lrnr_details := Get_ext_lrnr_Details(p_delegate_contact_id);
1633 l_customer_id := l_ext_lrnr_details.customer_id;
1634 END IF;
1635
1636
1637 OPEN C_GET_EVT_DETAILS;
1638 FETCH C_GET_EVT_DETAILS into l_public_event_flag,l_maximum_internal_attendees;
1639 IF C_GET_EVT_DETAILS%NOTFOUND then
1640 l_return_value := NULL;
1641 ELSE
1642 IF l_public_event_flag = 'Y' THEN
1643 CLOSE C_GET_EVT_DETAILS;
1644 l_return_value := -1;
1645 return l_return_value;
1646 ELSIF l_customer_id IS NULL THEN
1647 -- Person Case
1648 IF l_maximum_internal_attendees = 0 THEN
1649 l_return_value := NULL;
1650 END IF;
1651 ELSE
1652 -- Customer Case
1653 OPEN C_GET_EVT_ACCESS_DETAILS(l_customer_id);
1654 IF C_GET_EVT_ACCESS_DETAILS%NOTFOUND THEN
1655 l_return_value := NULL;
1656 ELSE
1657 l_return_value := -1;
1658 END IF;
1659 END IF;
1660
1661 END IF;
1662
1663 CLOSE C_GET_EVT_DETAILS;
1664 RETURN l_return_value;
1665
1666 END CHK_FOR_RESTRICTED_EVT;
1667
1668 --
1669 -- ----------------------------------------------------------------------------
1670 -- |--------------------------------< Get_Booking_OVN >-----------------------|
1671 -- ----------------------------------------------------------------------------
1672 -- {Start Of Comments}
1673 --
1674 -- Description:
1675 -- This function will be used to return object version number of an enrollment.
1676 --
1677 -- IN
1678 -- p_booking_id
1679 --
1680 -- OUT
1681 -- p_ovn
1682 --
1683 -- Post Failure:
1684 -- None.
1685 --
1686 -- Access Status:
1687 -- Public.
1688 --
1689 -- {End Of Comments}
1690 ----------------------------------------------------------------------------
1691 FUNCTION Get_Booking_OVN (p_booking_id IN NUMBER)
1692 RETURN NUMBER
1693
1694 IS
1695
1696 CURSOR csr_get_ovn IS
1697 SELECT object_version_number
1698 FROM ota_delegate_bookings
1699 WHERE booking_id = p_booking_id;
1700 --
1701 l_ovn BINARY_INTEGER DEFAULT '';
1702
1703 BEGIN
1704 -- Get the Object Version No. of the Enrollment.
1705 --
1706 OPEN csr_get_ovn;
1707 FETCH csr_get_ovn INTO l_ovn;
1708
1709 IF csr_get_ovn%NOTFOUND THEN
1710 --
1711 -- Major Problem as the record can't be located.
1712 --
1713 CLOSE csr_get_ovn;
1714 --
1715 -- Set a technical message, then the calling proc can decide
1716 -- what to do.
1717 --
1718 fnd_message.set_name('OTA','OTA_13661_PROB_GETTING_DATA');
1719 --
1720 --
1721 ELSE
1722 --
1723 CLOSE csr_get_ovn;
1724 --
1725 END IF;
1726 --
1727 RETURN l_ovn;
1728 --
1729 END Get_Booking_OVN;
1730
1731
1732 --
1733 -- ----------------------------------------------------------------------------
1734 -- |-----------------------------< cancel_enrollment>-------------------------|
1735 -- ----------------------------------------------------------------------------
1736 -- {Start Of Comments}
1737 --
1738 -- Description:
1739 -- This procedure is the entry point to this package and will be called from the
1740 -- View Enrollment Details Screen on pressing 'Submit'.
1741 -- This procedure will be used to call the cancel the enrollment Id passed in and
1742 -- update the Enrollment with the Cancellation details.
1743 --
1744 -- Pre Conditions:
1745 -- None.
1746 --
1747 -- In Arguments:
1748 -- p_booking_id
1749 -- p_event_id
1750 -- p_booking_status_type_id
1751 -- p_cancel_reason
1752 -- p_waitlist_size
1753 --
1754 -- Out Arguments:
1755 -- x_return_status
1756 --
1757 -- Post Success:
1758 -- Processing continues.
1759 --
1760 --
1761 -- Post Failure:
1762 -- None.
1763 --
1764 -- Access Status:
1765 -- Public.
1766 --
1767 -- {End Of Comments}
1768 ----------------------------------------------------------------------------
1769 PROCEDURE cancel_enrollment
1770 (x_return_status OUT NOCOPY VARCHAR2,
1771 p_booking_id IN NUMBER,
1772 p_event_id IN NUMBER,
1773 p_booking_status_type_id IN NUMBER,
1774 p_cancel_reason IN VARCHAR2,
1775 p_waitlist_size IN NUMBER,
1776 p_tdb_information_category IN VARCHAR2 DEFAULT NULL,
1777 p_tdb_information1 IN VARCHAR2 DEFAULT NULL,
1778 p_tdb_information2 IN VARCHAR2 DEFAULT NULL,
1779 p_tdb_information3 IN VARCHAR2 DEFAULT NULL,
1780 p_tdb_information4 IN VARCHAR2 DEFAULT NULL,
1781 p_tdb_information5 IN VARCHAR2 DEFAULT NULL,
1782 p_tdb_information6 IN VARCHAR2 DEFAULT NULL,
1783 p_tdb_information7 IN VARCHAR2 DEFAULT NULL,
1784 p_tdb_information8 IN VARCHAR2 DEFAULT NULL,
1785 p_tdb_information9 IN VARCHAR2 DEFAULT NULL,
1786 p_tdb_information10 IN VARCHAR2 DEFAULT NULL,
1787 p_tdb_information11 IN VARCHAR2 DEFAULT NULL,
1788 p_tdb_information12 IN VARCHAR2 DEFAULT NULL,
1789 p_tdb_information13 IN VARCHAR2 DEFAULT NULL,
1790 p_tdb_information14 IN VARCHAR2 DEFAULT NULL,
1791 p_tdb_information15 IN VARCHAR2 DEFAULT NULL,
1792 p_tdb_information16 IN VARCHAR2 DEFAULT NULL,
1793 p_tdb_information17 IN VARCHAR2 DEFAULT NULL,
1794 p_tdb_information18 IN VARCHAR2 DEFAULT NULL,
1795 p_tdb_information19 IN VARCHAR2 DEFAULT NULL,
1796 p_tdb_information20 IN VARCHAR2 DEFAULT NULL,
1797 p_failure_reason IN varchar2 DEFAULT NULL,
1798 p_attendance_result IN varchar2 DEFAULT NULL,
1799 p_successful_attendance_flag IN varchar2 DEFAULT NULL,
1800 p_comments in varchar2 DEFAULT NULL )
1801 IS
1802 -- ------------------------
1803 -- event_csr variables
1804 -- ------------------------
1805 l_event_title ota_events.title%TYPE;
1806 l_event_status ota_events.event_status%TYPE;
1807 l_course_start_date ota_events.course_start_date%TYPE;
1808 l_course_start_time ota_events.course_start_time%TYPE;
1809 l_course_end_date ota_events.course_start_date%TYPE;
1810 l_owner_id ota_events.owner_id%TYPE;
1811
1812 -- ------------------------
1813 -- booking_csr variables
1814 -- ------------------------
1815 l_date_booking_placed ota_delegate_bookings.date_booking_placed%TYPE;
1816 l_content_player_status ota_delegate_bookings.content_player_status%TYPE;
1817 l_object_version_number ota_delegate_bookings.object_version_number%TYPE;
1818
1819 -- ------------------------
1820 -- Finance_csr Variables
1821 -- ------------------------
1822 l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
1823 l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1824 l_transfer_status ota_finance_lines.transfer_status%TYPE;
1825 lf_booking_id ota_finance_lines.booking_id%TYPE;
1826 lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1827 l_sequence_number ota_finance_lines.sequence_number%TYPE;
1828 l_finance_count number(10);
1829 l_cancelled_flag ota_finance_lines.cancelled_flag%type;
1830 l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
1831
1832 -- ------------------------
1833 -- header_csr Variables
1834 -- ------------------------
1835 lh_finance_header_id ota_finance_headers.finance_header_id%TYPE;
1836 lh_cancelled_flag ota_finance_headers.cancelled_flag%TYPE;
1837 lh_transfer_status ota_finance_headers.transfer_status%TYPE;
1838 lh_object_version_number ota_finance_headers.object_version_number%TYPE;
1839 -- ------------------------
1840 -- other local Variables
1841 -- ------------------------
1842 l_hours_until_class_starts NUMBER;
1843 l_minimum_advance_notice NUMBER;
1844 l_auto_waitlist_days NUMBER;
1845 l_sysdate DATE := SYSDATE;
1846 l_daemon_flag ota_delegate_bookings.daemon_flag%TYPE;
1847 l_daemon_type ota_delegate_bookings.daemon_type%TYPE;
1848 lb_object_version_number ota_delegate_bookings.object_version_number%TYPE;
1849 l_proc VARCHAR2(72) := 'ota_learner_enroll_ss.cancel_enrollment';
1850 l_activity_version_name ota_activity_versions.version_name%TYPE;
1851
1852 CURSOR event_csr (p_event_id ota_events.event_id%TYPE)
1853 IS
1854 SELECT a.version_name,
1855 e.title,
1856 e.event_status,
1857 e.course_start_date,
1858 e.course_start_time,
1859 e.course_end_date,
1860 e.owner_id
1861 FROM ota_events_vl e,
1862 ota_offerings o,
1863 ota_activity_versions_tl a
1864 WHERE e.parent_offering_id = o.offering_id
1865 AND o.activity_version_id = a.activity_version_id
1866 AND e.event_id = p_event_id
1867 AND language=userenv('LANG');
1868
1869 CURSOR booking_csr (p_booking_id ota_delegate_bookings.booking_id%TYPE)
1870 IS
1871 SELECT b.date_booking_placed, b.content_player_status, b.object_version_number
1872 FROM ota_delegate_bookings b
1873 WHERE b.booking_id = p_booking_id;
1874
1875 CURSOR finance_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
1876 IS
1877 SELECT fln.finance_line_id finance_line_id,
1878 fln.finance_header_id finance_header_id,
1879 fln.transfer_status transfer_status,
1880 fln.booking_id booking_id,
1881 fln.object_version_number object_version_number,
1882 fln.sequence_number sequence_number,
1883 fln.Cancelled_flag cancelled_flag
1884 FROM ota_finance_lines fln
1885 WHERE fln.booking_id = p_booking_id;
1886
1887 CURSOR finance_count_csr (p_finance_header_id ota_finance_lines.finance_header_id%TYPE)
1888 IS
1889 SELECT count(finance_line_id)
1890 FROM ota_finance_lines fln
1891 WHERE fln.finance_header_id = p_finance_header_id;
1892
1893 CURSOR header_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
1894 IS
1895 SELECT flh.finance_header_id finance_header_id,
1896 flh.cancelled_flag cancelled_flag,
1897 flh.transfer_status transfer_status,
1898 flh.object_version_number object_version_number
1899 FROM ota_finance_headers flh,
1900 ota_finance_lines fln
1901 WHERE flh.finance_header_id = fln.finance_header_id
1902 AND fln.booking_id = p_booking_id;
1903
1904 CURSOR C_USER(p_owner_id NUMBER) IS
1905 SELECT USER_NAME
1906 FROM FND_USER
1907 WHERE Employee_id = p_owner_id
1908 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
1909
1910 l_username fnd_user.user_name%TYPE;
1911
1912 BEGIN
1913 hr_utility.set_location('Entering:'||l_proc, 10);
1914
1915 savepoint cancel_enrollment;
1916
1917 --Enable multi messaging
1918 hr_multi_message.enable_message_list;
1919
1920 OPEN event_csr (p_event_id);
1921 FETCH event_csr INTO l_activity_version_name,
1922 l_event_title,
1923 l_event_status,
1924 l_course_start_date,
1925 l_course_start_time,
1926 l_course_end_date,
1927 l_owner_id;
1928 CLOSE event_csr;
1929
1930 IF l_owner_id IS NULL THEN
1931 l_owner_id := fnd_profile.value('OTA_DEFAULT_EVENT_OWNER');
1932 END IF;
1933
1934 OPEN c_user(l_owner_id);
1935 FETCH c_user INTO l_username;
1936 CLOSE c_user;
1937
1938 OPEN booking_csr (p_booking_id);
1939 FETCH booking_csr INTO l_date_booking_placed,
1940 l_content_player_status,
1941 l_object_version_number;
1942 CLOSE booking_csr;
1943
1944 OPEN finance_csr (p_booking_id);
1945 FETCH finance_csr INTO l_finance_line_id,
1946 l_finance_header_id,
1947 l_transfer_status,
1948 lf_booking_id,
1949 lf_object_version_number,
1950 l_sequence_number,
1951 l_cancelled_flag ;
1952
1953 /*
1954 --Bug 3445831
1955 l_hours_until_class_starts := 0;
1956 If ( l_course_start_date is not null ) Then
1957 -- Modified date format for Bug#4450940
1958 --l_hours_until_class_starts := 24*(to_date(to_char(l_course_start_date, 'DD-MON-YYYY')||''||l_course_start_time, 'DD/MM/YYYYHH24:MI') - SYSDATE);
1959 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);
1960 End If;
1961
1962 IF finance_csr%found THEN
1963 l_minimum_advance_notice := NVL(TO_NUMBER(fnd_profile.value('OTA_CANCEL_HOURS_BEFORE_EVENT')), 0);
1964
1965 IF l_transfer_status = 'ST' OR
1966 l_cancelled_flag = 'Y' OR
1967 l_content_player_status IS NOT NULL OR
1968 l_hours_until_class_starts < l_minimum_advance_notice THEN
1969 NULL;
1970 ELSE
1971 -- Call Finance Lines API (Cancel Finance Line)
1972 OPEN finance_count_csr (l_finance_header_id);
1973 FETCH finance_count_csr INTO l_finance_count;
1974 CLOSE finance_count_csr;
1975
1976 IF l_finance_count = 1 THEN -- If only one Finance Line
1977 OPEN header_csr (p_booking_id);
1978 FETCH header_csr INTO lh_finance_header_id,
1979 lh_cancelled_flag,
1980 lh_transfer_status,
1981 lh_object_version_number;
1982 CLOSE header_csr;
1983
1984 IF lh_transfer_status <> 'ST' or lh_cancelled_flag <>'Y' THEN -- Call Finance Header API
1985 -- to Cancel Finance Header
1986 ota_tfh_api_business_rules.cancel_header
1987 (p_finance_header_id => lh_finance_header_id,
1988 p_cancel_header_id => l_cancel_header_id,
1989 p_date_raised => l_sysdate,
1990 p_validate => false,
1991 p_commit => false);
1992 END IF;
1993 ELSE
1994 ota_tfl_api_upd.upd(p_finance_line_id => l_finance_line_id,
1995 p_date_raised => l_sysdate,
1996 p_cancelled_flag => 'Y',
1997 p_object_version_number => lf_object_version_number,
1998 p_sequence_number => l_sequence_number,
1999 p_validate => false,
2000 p_transaction_type => 'CANCEL_HEADER_LINE');
2001 END IF;
2002 END IF; -- For Lines;
2003 END IF;
2004 CLOSE finance_csr;
2005 */
2006 --
2007 -- Initialize workflow setings
2008 --
2009 l_auto_waitlist_days := TO_NUMBER(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
2010 --
2011 IF (p_waitlist_size > 0) THEN
2012 IF (l_hours_until_class_starts >= l_auto_waitlist_days) THEN
2013 l_daemon_flag := 'Y';
2014 l_daemon_type := 'W';
2015 /* Bug#6063768 Since this notification is sent from ota_delegate_booking_api.update_delegate_booking_api
2016 commenting this notification
2017 ELSE
2018 IF l_username IS NOT NULL THEN
2019 ota_initialization_wf.manual_waitlist
2020 (p_itemtype => 'OTWF',
2021 p_process => 'OTA_MANUAL_WAITLIST',
2022 p_event_title => l_event_title,
2023 p_event_id => p_event_id,
2024 p_item_key => p_booking_id||':'||to_char(l_sysdate,'DD-MON-YYYY:HH24:MI:SS'),
2025 p_user_name => l_username);
2026 END IF;
2027 */
2028 END IF;
2029 ELSE
2030 l_daemon_flag := NULL;
2031 l_daemon_type := NULL;
2032 END IF;
2033
2034 --
2035 -- Call update enrollment API to cancel Enrollment
2036 --
2037 ota_tdb_api_upd2.update_enrollment
2038 (p_booking_id => p_booking_id,
2039 p_booking_status_type_id => p_booking_status_type_id,
2040 p_object_version_number => l_object_version_number,
2041 p_event_id => p_event_id,
2042 p_status_change_comments => p_cancel_reason,
2043 p_tfl_object_version_number => lf_object_version_number,
2044 p_finance_line_id => l_finance_line_id,
2045 p_daemon_flag => l_daemon_flag,
2046 p_daemon_type => l_daemon_type,
2047 p_date_status_changed => l_sysdate,
2048 p_date_booking_placed => l_date_booking_placed,
2049 p_tdb_information_category => p_tdb_information_category,
2050 p_tdb_information1 => p_tdb_information1,
2051 p_tdb_information2 => p_tdb_information2,
2052 p_tdb_information3 => p_tdb_information3,
2053 p_tdb_information4 => p_tdb_information4,
2054 p_tdb_information5 => p_tdb_information5,
2055 p_tdb_information6 => p_tdb_information6,
2056 p_tdb_information7 => p_tdb_information7,
2057 p_tdb_information8 => p_tdb_information8,
2058 p_tdb_information9 => p_tdb_information9,
2059 p_tdb_information10 => p_tdb_information10,
2060 p_tdb_information11 => p_tdb_information11,
2061 p_tdb_information12 => p_tdb_information12,
2062 p_tdb_information13 => p_tdb_information13,
2063 p_tdb_information14 => p_tdb_information14,
2064 p_tdb_information15 => p_tdb_information15,
2065 p_tdb_information16 => p_tdb_information16,
2066 p_tdb_information17 => p_tdb_information17,
2067 p_tdb_information18 => p_tdb_information18,
2068 p_tdb_information19 => p_tdb_information19,
2069 p_tdb_information20 => p_tdb_information20,
2070 p_failure_reason => p_failure_reason,
2071 p_attendance_result => p_attendance_result,
2072 p_successful_attendance_flag => p_successful_attendance_flag,
2073 p_comments => p_comments
2074 );
2075
2076 x_return_status := hr_multi_message.get_return_status_disable;
2077
2078 cancel_finance(p_booking_id);
2079
2080 hr_utility.set_location('Leaving:'||l_proc, 20);
2081 EXCEPTION
2082 When hr_multi_message.error_message_exist then
2083 --
2084 -- Catch the Multiple Message List exception which
2085 -- indicates API processing has been aborted because
2086 -- at least one message exists in the list.
2087 --
2088 rollback to cancel_enrollment;
2089 --
2090 -- Reset IN OUT parameters and set OUT parameters
2091 --
2092
2093 x_return_status := hr_multi_message.get_return_status_disable;
2094 hr_utility.set_location(' Leaving:' || l_proc, 30);
2095 When others then
2096 --
2097 -- When Multiple Message Detection is enabled catch
2098 -- any Application specific or other unexpected
2099 -- exceptions. Adding appropriate details to the
2100 -- Multiple Message List. Otherwise re-raise the
2101 -- error.
2102 --
2103 rollback to cancel_enrollment;
2104 if hr_multi_message.unexpected_error_add(l_proc) then
2105 hr_utility.set_location(' Leaving:' || l_proc,40);
2106 raise;
2107 end if;
2108 --
2109 -- Reset IN OUT and set OUT parameters
2110 --
2111
2112 x_return_status := hr_multi_message.get_return_status_disable;
2113 hr_utility.set_location(' Leaving:' || l_proc,50);
2114 END cancel_enrollment;
2115
2116 Procedure CHK_UNIQUE_FUNC(p_function_name in varchar2,
2117 p_user_id in number,
2118 result out nocopy varchar2)
2119 IS
2120 /*
2121 --Bug 4905774
2122 responsibility_id_tab resp_id_tab;
2123 security_group_id_tab sec_id_tab;
2124
2125 cursor getFunctionId(l_function_name in varchar2) is
2126 select function_id
2127 from fnd_form_functions
2128 where function_name = l_function_name;
2129
2130 cursor getResp(l_user_id in varchar2) is
2131 select fr.menu_id, furg.responsibility_id,
2132 furg.security_group_id, furg.responsibility_application_id
2133 from fnd_responsibility fr,
2134 fnd_user_resp_groups furg,
2135 fnd_user fu
2136 where fu.USER_ID = l_user_id
2137 and fu.START_DATE <= sysdate
2138 and (fu.END_DATE is null or fu.END_DATE > sysdate)
2139 and furg.USER_ID = fu.USER_ID
2140 and furg.START_DATE <= sysdate
2141 and (furg.END_DATE is null or furg.END_DATE > sysdate)
2142 and furg.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
2143 and furg.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
2144 and fr.VERSION = 'W'
2145 and fr.START_DATE <= sysdate
2146 and (fr.END_DATE is null or fr.END_DATE > sysdate);
2147
2148 l_function_id number(10);
2149 l_responsibility_id number(10);
2150 l_security_group_id number(10);
2151 i number(5):=0;
2152 j number(5):=0;
2153 tot_cnt number(5);
2154 */
2155 Begin
2156
2157 result :='S';
2158 /*
2159 --Bug 4905774
2160 -- Get function id of current function
2161 open getFunctionId(p_function_name);
2162 fetch getFunctionId into l_function_id;
2163 close getFunctionId;
2164 -- Get user ID of current user (If not available)
2165 -- select USER_ID
2166 -- into l_user_id
2167 -- from FND_USER
2168 -- where USER_NAME = l_username;
2169
2170 -- Get responsibility ID
2171
2172 for ri in getResp(p_user_id) loop
2173
2174 if fnd_function.is_function_on_menu(ri.menu_id, l_function_id)
2175 then
2176 i:=i+1;
2177 responsibility_id_tab(i) := ri.responsibility_id;
2178 security_group_id_tab(i) := ri.security_group_id;
2179 end if;
2180 end loop;
2181
2182 tot_cnt:=i;
2183
2184 if (tot_cnt=0) then
2185 result :='NR'; --(no Responsibility)
2186 Elsif (tot_cnt>1) then
2187 result:='ME';
2188 End if;
2189 */
2190 end CHK_UNIQUE_FUNC;
2191
2192 --|--------------------------------------------------------------------------|
2193 --|--< CHK_VALID_ACTIVITY >-------------------------------------------|
2194 --|--------------------------------------------------------------------------|
2195
2196 FUNCTION CHK_VALID_ACTIVITY(
2197 p_activity_id IN OTA_ACTIVITY_VERSIONS.Activity_Version_Id%type
2198 )
2199 RETURN VARCHAR2
2200 IS
2201 -- check for presence of activity
2202 CURSOR C_GET_ACT(l_activity_id OTA_ACTIVITY_VERSIONS.Activity_Version_Id%type)
2203 is
2204 SELECT activity_version_id from ota_activity_versions
2205 where Activity_version_id = l_activity_id;
2206
2207 -- checking for expired AND BG validation
2208 CURSOR C_GET_ACT_DET(l_activity_id OTA_ACTIVITY_VERSIONS.Activity_Version_Id%type)
2209 is
2210 SELECT activity_version_id,end_date from ota_activity_versions
2211 where Activity_version_id = l_activity_id
2212 and business_group_id = ota_general.get_business_group_id;
2213
2214
2215 CURSOR C_GET_OFR_ATTACHED(l_activity_id OTA_ACTIVITY_VERSIONS.Activity_Version_Id%type)
2216 is
2217 SELECT 1 from ota_offerings
2218 where Activity_version_id = l_activity_id
2219 and business_group_id = ota_general.get_business_group_id;
2220
2221
2222 l_return_value varchar2(10):='V';
2223 l_date Date;
2224 l_activity number(10);
2225 l_offering number(2);
2226
2227 BEGIN
2228
2229 OPEN C_GET_ACT(p_activity_id);
2230 FETCH C_GET_ACT into l_activity;
2231 IF C_GET_ACT%NOTFOUND then --implies invalid activityid
2232 CLOSE C_GET_ACT;
2233 l_return_value := 'I';
2234 return l_return_value;
2235 END IF;
2236 CLOSE C_GET_ACT;
2237
2238 OPEN C_GET_ACT_DET(p_activity_id);
2239 FETCH C_GET_ACT_DET into l_activity,l_date;
2240 IF C_GET_ACT_DET%NOTFOUND then --implies different BG
2241 CLOSE C_GET_ACT_DET;
2242 l_return_value := 'DFBG';
2243 return l_return_value;
2244 ElsIf(l_date < trunc(sysdate)) then --implies Expired Activity
2245 CLOSE C_GET_ACT_DET;
2246 l_return_value := 'E';
2247 return l_return_value;
2248 ELSE
2249 OPEN C_GET_OFR_ATTACHED(p_activity_id);
2250 FETCH C_GET_OFR_ATTACHED into l_offering;
2251 IF C_GET_OFR_ATTACHED%NOTFOUND then --implies no offering attached with activityid
2252 CLOSE C_GET_OFR_ATTACHED;
2253 CLOSE C_GET_ACT_DET;
2254 l_return_value := 'NO';
2255 return l_return_value;
2256
2257 END IF;
2258 CLOSE C_GET_OFR_ATTACHED;
2259 END IF;
2260
2261 CLOSE C_GET_ACT_DET;
2262 Return l_return_value;
2263 END CHK_VALID_ACTIVITY;
2264
2265
2266 --|--------------------------------------------------------------------------|
2267 --|--< CHK_VALID_OFFERING >-------------------------------------------|
2268 --|--------------------------------------------------------------------------|
2269
2270 FUNCTION CHK_VALID_OFFERING(
2271 p_offering_id IN OTA_OFFERINGS.offering_id%type
2272 )
2273 RETURN VARCHAR2
2274 IS
2275 -- check for presence of offering
2276 CURSOR C_GET_OFR(l_offering_id OTA_OFFERINGS.offering_id%type)
2277 is
2278 SELECT offering_id from ota_offerings
2279 where offering_id = l_offering_id;
2280
2281 -- checking for expired AND BG validation
2282 CURSOR C_GET_OFR_DET(l_offering_id OTA_OFFERINGS.offering_id%type)
2283 is
2284 SELECT offering_id,end_date from ota_offerings
2285 where offering_id = l_offering_id
2286 and business_group_id = ota_general.get_business_group_id;
2287
2288
2289 CURSOR C_GET_EVT_ATTACHED(l_offering_id OTA_OFFERINGS.offering_id%type)
2290 is
2291 SELECT 1 from ota_events
2292 where parent_offering_id = l_offering_id
2293 and business_group_id = ota_general.get_business_group_id;
2294
2295
2296 l_return_value varchar2(10):='V';
2297 l_date Date;
2298 l_offering number(10);
2299 l_event number(2);
2300
2301 BEGIN
2302
2303 OPEN C_GET_OFR(p_offering_id);
2304 FETCH C_GET_OFR into l_offering;
2305 IF C_GET_OFR%NOTFOUND then -- implies invalid offering id
2306 CLOSE C_GET_OFR;
2307 l_return_value := 'I';
2308 return l_return_value;
2309 END IF;
2310 CLOSE C_GET_OFR;
2311
2312 OPEN C_GET_OFR_DET(p_offering_id);
2313 FETCH C_GET_OFR_DET into l_offering,l_date;
2314 IF C_GET_OFR_DET%NOTFOUND then --implies different BG
2315 CLOSE C_GET_OFR_DET;
2316 l_return_value := 'DFBG';
2317 return l_return_value;
2318 ElsIf(l_date < trunc(sysdate)) then --implies Expired Offering
2319 CLOSE C_GET_OFR_DET;
2320 l_return_value := 'E';
2321 return l_return_value;
2322 ELSE
2323 OPEN C_GET_EVT_ATTACHED(p_offering_id);
2324 FETCH C_GET_EVT_ATTACHED into l_event;
2325 IF C_GET_EVT_ATTACHED%NOTFOUND then --implies no event attached with offeringid
2326 CLOSE C_GET_EVT_ATTACHED;
2327 CLOSE C_GET_OFR_DET;
2328 l_return_value := 'NE';
2329 return l_return_value;
2330 END IF;
2331 CLOSE C_GET_EVT_ATTACHED;
2332 END IF;
2333
2334 CLOSE C_GET_OFR_DET;
2335 Return l_return_value;
2336 END CHK_VALID_OFFERING;
2337
2338
2339
2340
2341 --|--------------------------------------------------------------------------|
2342 --|--< CHK_VALID_EVENT >-------------------------------------------|
2343 --|--------------------------------------------------------------------------|
2344
2345 FUNCTION CHK_VALID_EVENT(
2346 p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
2347 )
2348 RETURN VARCHAR2
2349 IS
2350 -- checking for INVALID Events
2351 CURSOR C_GET_EVT(l_event_id OTA_EVENTS.EVENT_ID%TYPE)
2352 is
2353 SELECT event_id from ota_events
2354 where event_id = l_event_id;
2355
2356 CURSOR C_GET_EVT_DET(l_event_id OTA_EVENTS.EVENT_ID%TYPE)
2357 is
2358 SELECT event_id,event_status,course_start_date,course_end_date,enrolment_start_date, enrolment_end_date, course_end_time
2359 from ota_events
2360 where event_id = l_event_id
2361 and event_type in ('SCHEDULED','SELFPACED')
2362 and business_group_id = ota_general.get_business_group_id ;
2363
2364
2365 l_return_value varchar2(10):='V';
2366 l_stDate Date;
2367 l_endDate Date;
2368 l_endTime varchar2(5);
2369 l_evtStatus varchar2(10);
2370 l_enrlStDate Date;
2371 l_enrlEndDate Date;
2372 l_event Number(10);
2373
2374 BEGIN
2375
2376 OPEN C_GET_EVT(p_event_id);
2377 FETCH C_GET_EVT into l_event;
2378 IF C_GET_EVT%NOTFOUND then --implies invalid eventid
2379 CLOSE C_GET_EVT;
2380 l_return_value := 'I';
2381 return l_return_value;
2382 END IF;
2383
2384 CLOSE C_GET_EVT;
2385
2386 OPEN C_GET_EVT_DET(p_event_id);
2387 FETCH C_GET_EVT_DET into l_event,l_evtStatus,l_stDate,l_endDate,l_enrlStDate,l_enrlEndDate,l_endTime;
2388 IF C_GET_EVT_DET%NOTFOUND then
2389 CLOSE C_GET_EVT_DET;
2390 l_return_value:= 'DFBG'; --implies different BG for event
2391 Return l_return_value;
2392 ElSIF (l_evtStatus <> 'N' and l_evtStatus <> 'P' and l_evtStatus <> 'F') then
2393 CLOSE C_GET_EVT_DET;
2394 l_return_value:= 'C'; --implies canceled event
2395 Return l_return_value;
2396 ELSIF (l_endDate is not null) then
2397 IF (l_endTime is null and trunc(sysdate) > l_endDate) then
2398 CLOSE C_GET_EVT_DET;
2399 l_return_value:= 'E';--Expired
2400 Return l_return_value;
2401 ELSIF (l_endTime is not null and sysdate > to_date(to_char(l_endDate,'YYYY/MM/DD') ||' '|| l_endTime, 'YYYY/MM/DD HH24:MI') ) then
2402 CLOSE C_GET_EVT_DET;
2403 l_return_value:= 'E';--Expired
2404 Return l_return_value;
2405 END IF;
2406 ELSIF (trunc(sysdate) > l_enrlEndDate) then
2407 CLOSE C_GET_EVT_DET;
2408 l_return_value:= 'EC'; --Enrollment closed
2409 Return l_return_value;
2410 ELSIF (trunc(sysdate) < l_enrlStDate) then
2411 CLOSE C_GET_EVT_DET;
2412 l_return_value:= 'ENS'; --enrollment not started
2413 Return l_return_value;
2414 END IF;
2415
2416 CLOSE C_GET_EVT_DET;
2417 Return l_return_value;
2418
2419 END CHK_VALID_EVENT;
2420
2421 PROCEDURE get_wf_attr_for_cancel_ntf
2422 (p_event_id IN ota_events.event_id%TYPE,
2423 p_person_id IN number,
2424 p_supervisor_username OUT NOCOPY fnd_user.user_name%TYPE,
2425 p_supervisor_full_name OUT NOCOPY per_all_people_f.full_name%TYPE,
2426 p_supervisor_id OUT NOCOPY per_all_people_f.person_id%Type,
2427 p_current_person_name OUT NOCOPY VARCHAR2,
2428 p_current_username OUT NOCOPY VARCHAR2,
2429 p_person_displayname OUT NOCOPY per_all_people_f.full_name%TYPE,
2430 p_creator_displayname OUT NOCOPY per_all_people_f.full_name%TYPE,
2431 x_return_status OUT NOCOPY VARCHAR2)
2432 IS
2433
2434 l_proc VARCHAR2(72) := g_package || 'get_wf_attr_for_cancel_ntf';
2435
2436 l_current_userid NUMBER := fnd_profile.value('USER_ID');
2437 l_display_person_id fnd_user.employee_id%TYPE;
2438
2439 CURSOR person_username_csr (p_person_id IN NUMBER) IS
2440 SELECT user_name
2441 FROM fnd_user
2442 WHERE employee_id = p_person_id;
2443 --
2444 CURSOR display_person_id_csr (l_current_user_id IN NUMBER) IS
2445 SELECT employee_id
2446 FROM fnd_user
2447 WHERE user_id = l_current_userid;
2448 --
2449 --Bug 3841658
2450 CURSOR display_name_csr (l_display_person_id IN NUMBER) IS
2451 SELECT full_name
2452 FROM per_all_people_f p
2453 WHERE person_id = l_display_person_id
2454 and trunc(SYSDATE) between effective_start_date and effective_end_date;
2455 --
2456
2457 CURSOR csr_supervisor_id IS
2458 SELECT a.supervisor_id, per.full_name
2459 FROM per_all_assignments_f a,
2460 per_all_people_f per
2461 WHERE a.person_id = p_person_id
2462 AND per.person_id = a.supervisor_id
2463 AND a.primary_flag = 'Y'
2464 AND trunc(sysdate)
2465 BETWEEN a.effective_start_date AND a.effective_end_date
2466 AND trunc(sysdate)
2467 BETWEEN per.effective_start_date AND per.effective_end_date;
2468
2469 CURSOR csr_supervisor_user IS
2470 SELECT user_name
2471 FROM fnd_user
2472 WHERE employee_id = p_supervisor_id;
2473
2474 BEGIN
2475
2476 hr_utility.set_location('Entering:'||l_proc, 10);
2477 --Enable multi messaging
2478 hr_multi_message.enable_message_list;
2479
2480 p_current_username := fnd_profile.value('USERNAME');
2481
2482 -- Get the current user name
2483 OPEN person_username_csr (p_person_id);
2484 FETCH person_username_csr INTO p_current_person_name;
2485 CLOSE person_username_csr;
2486
2487 -- Get the current display person id
2488 OPEN display_person_id_csr (l_current_userid);
2489 FETCH display_person_id_csr INTO l_display_person_id;
2490 CLOSE display_person_id_csr;
2491
2492 -- Get the person display name
2493 OPEN display_name_csr (p_person_id);
2494 FETCH display_name_csr INTO p_person_displayname;
2495 CLOSE display_name_csr;
2496
2497 -- Get value for creator display name attribute if current user is
2498 -- different than person whose class will be canceled
2499 IF l_display_person_id <> p_person_id THEN
2500 OPEN display_name_csr (l_display_person_id);
2501 FETCH display_name_csr INTO p_creator_displayname;
2502 CLOSE display_name_csr;
2503 ELSE
2504 p_creator_displayname := p_person_displayname;
2505 END IF;
2506
2507 FOR a IN csr_supervisor_id LOOP
2508 p_supervisor_id := a.supervisor_id;
2509 p_supervisor_full_name := a.full_name;
2510 END LOOP;
2511
2512 FOR b IN csr_supervisor_user LOOP
2513 p_supervisor_username := b.user_name;
2514 END LOOP;
2515
2516 x_return_status := hr_multi_message.get_return_status_disable;
2517 hr_utility.set_location('Leaving:'||l_proc, 20);
2518 EXCEPTION
2519 When hr_multi_message.error_message_exist then
2520 --
2521 -- Catch the Multiple Message List exception which
2522 -- indicates API processing has been aborted because
2523 -- at least one message exists in the list.
2524 --
2525
2526 --
2527 -- Reset IN OUT parameters and set OUT parameters
2528 --
2529
2530 x_return_status := hr_multi_message.get_return_status_disable;
2531 hr_utility.set_location(' Leaving:' || l_proc, 30);
2532 When others then
2533 --
2534 -- When Multiple Message Detection is enabled catch
2535 -- any Application specific or other unexpected
2536 -- exceptions. Adding appropriate details to the
2537 -- Multiple Message List. Otherwise re-raise the
2538 -- error.
2539 --
2540 if hr_multi_message.unexpected_error_add(l_proc) then
2541 hr_utility.set_location(' Leaving:' || l_proc,40);
2542 raise;
2543 end if;
2544 --
2545 -- Reset IN OUT and set OUT parameters
2546 --
2547
2548 x_return_status := hr_multi_message.get_return_status_disable;
2549 hr_utility.set_location(' Leaving:' || l_proc,50);
2550 END get_wf_attr_for_cancel_ntf;
2551 --
2552 -- ----------------------------------------------------------------------------
2553 -- |-------------------------< get_booking_status_comments >----------------------------|
2554 -- ----------------------------------------------------------------------------
2555 --
2556 -- Description: get the comments from the booking history table for the
2557 -- booking_id and booking_status_type_id passed in as parameters.
2558 --
2559 --
2560
2561 FUNCTION get_booking_status_comments(p_booking_id IN NUMBER,
2562 p_booking_status_type_id IN NUMBER) RETURN VARCHAR2
2563 IS
2564
2565 --
2566 --
2567 l_comments ota_booking_status_histories.comments%TYPE := null;
2568 l_proc varchar2(72) := 'ota_learner_enroll_ss.get_booking_status_comments';
2569 --
2570 begin
2571 --
2572 --
2573 hr_utility.set_location('Entering:'|| l_proc, 5);
2574 --
2575 l_comments:=ota_utility.getEnrollmentChangeReason(p_booking_id);
2576
2577 RETURN l_comments;
2578 --
2579 --
2580 EXCEPTION
2581 WHEN others then
2582 RETURN l_comments;
2583 END get_booking_status_comments;
2584
2585 --
2586 -- ---------------------------------------------------------------------------------|
2587 -- |-------------------------< getCancellationStatusId >----------------------------|
2588 -- ---------------------------------------------------------------------------------|
2589 --
2590 -- Description: Retrieves the default cancellation enrollment status id
2591 --
2592 Function getCancellationStatusId
2593 RETURN ota_booking_status_types.booking_status_type_id%type
2594 IS
2595 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
2596 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
2597 l_proc VARCHAR2(72) := g_package || 'getCancellationStatusId';
2598 Begin
2599
2600 l_booking_status_row := Get_Booking_Status_for_web
2601 (p_web_booking_status_type => 'CANCELLED'
2602 ,p_business_group_id => ota_general.get_business_group_id);
2603
2604 l_booking_status_type_id := l_booking_status_row.booking_status_type_id;
2605 RETURN l_booking_status_type_id;
2606 End getCancellationStatusId;
2607
2608 end ota_learner_enroll_ss;