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