[Home] [Help]
PACKAGE BODY: APPS.OTA_CANCEL_TRAINING_SS
Source
4 -- ----------------------------------------------------------------------------
1 PACKAGE BODY ota_cancel_training_ss AS
2 /* $Header: otssctrn.pkb 120.0 2005/05/29 07:33:19 appldev noship $ */
3 --
5 -- |----------------------------<create_enroll_wf_process>-------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- This procedure will be used to set the item attributes in workflow.
11 --
12 -- Pre Conditions:
13 -- None.
14 --
15 -- In Arguments:
16 -- p_item_key
17 -- p_item_type
18 -- p_person_id
19 -- p_event_title
20 -- p_course_start_date
21 -- p_course_end_date
22 -- Out Arguments:
23 -- x_return_status
24 -- x_msg_data
25 --
26 -- Post Success:
27 -- Processing continues.
28 --
29 --
30 -- Post Failure:
31 -- None.
32 --
33 -- Access Status:
34 -- Public.
35 --
36 -- {End Of Comments}
37 ------------------------------------------------------------------------------
38 PROCEDURE create_enroll_wf_process
39 (x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_item_key IN wf_items.item_key%TYPE,
42 p_item_type IN wf_items.item_type%TYPE,
43 p_person_id IN number default NULL,
44 p_event_title IN ota_events.title%TYPE,
45 p_course_start_date IN ota_events.course_start_date%TYPE,
46 p_course_end_date IN ota_events.course_end_date%TYPE,
47 p_version_name IN ota_activity_versions.Version_name%type )
48 --
49 --
50 --
51 IS
52 --
53 l_proc VARCHAR2(72) := 'ota_cancel_enrollment_ss.create_enroll_wf_process';
54 l_current_username VARCHAR2(80):= fnd_profile.value('USERNAME');
55 l_current_userid NUMBER := fnd_profile.value('USER_ID');
56 l_display_person_id fnd_user.employee_id%TYPE;
57 l_current_person_name VARCHAR2(80);
58 l_person_displayname per_all_people_f.full_name%TYPE;
59 l_creator_displayname per_all_people_f.full_name%TYPE;
60 --
61 --Bug 2480134
62 l_supervisor_id per_all_people_f.person_id%Type;
63 l_supervisor_username fnd_user.user_name%TYPE;
64 l_supervisor_full_name per_all_people_f.full_name%TYPE;
65 --Bug 2480134
66 CURSOR person_username_csr (p_person_id IN NUMBER) IS
67 SELECT user_name
68 FROM fnd_user
69 WHERE employee_id = p_person_id;
70 --
71 CURSOR display_person_id_csr (l_current_user_id IN NUMBER) IS
72 SELECT employee_id
73 FROM fnd_user
74 WHERE user_id = l_current_userid;
75 --
76 CURSOR display_name_csr (l_display_person_id IN NUMBER) IS
80 FROM per_all_people_f p
77 -- ,p_course_start_date IN DATE,
78 -- p_course_end_date IN DATE) IS
79 SELECT full_name
81 WHERE person_id = l_display_person_id
82 --Modified for bug#4057241
83 --AND effective_start_date <= p_course_start_date
84 --AND effective_start_date <= nvl(p_course_start_date, SYSDATE)
85 --AND NVL(effective_end_date, SYSDATE) >= NVL(p_course_end_date, SYSDATE);
86 AND trunc(sysdate) between p.effective_start_date and p.effective_end_date;
87 --
88 -- Bug 2480134
89 CURSOR csr_supervisor_id IS
90 SELECT asg.supervisor_id, per.full_name
91 FROM per_all_assignments_f asg,
92 per_all_people_f per
93 WHERE asg.person_id = p_person_id
94 AND per.person_id = asg.supervisor_id
95 AND asg.primary_flag = 'Y'
96 AND trunc(sysdate)
97 BETWEEN asg.effective_start_date AND asg.effective_end_date
98 AND trunc(sysdate)
99 BETWEEN per.effective_start_date AND per.effective_end_date;
100
101 CURSOR csr_supervisor_user IS
102 SELECT user_name
103 FROM fnd_user
104 WHERE employee_id= l_supervisor_id;
105 -- Bug 2480134
106
107 BEGIN
108 --
109 hr_utility.set_location('Entering:'||l_proc, 10);
110 -- Get the current user name
111 --
112 OPEN person_username_csr (p_person_id);
113 FETCH person_username_csr INTO l_current_person_name;
114 CLOSE person_username_csr;
115 --
116 -- Get the current display person id
117 --
118 OPEN display_person_id_csr (l_current_userid);
119 FETCH display_person_id_csr INTO l_display_person_id;
120 CLOSE display_person_id_csr;
121 --
122 -- Get the person display name
123 --
124 OPEN display_name_csr (p_person_id);
125 --,p_course_start_date,
126 -- p_course_end_date);
127 FETCH display_name_csr INTO l_person_displayname;
128 CLOSE display_name_csr;
129
130 -- Get value for creator display name attribute if current user is
131 -- different than person whose class will be canceled
132 --
133 IF l_display_person_id <> p_person_id THEN
134 --
135 OPEN display_name_csr (l_display_person_id);
136 --,p_course_start_date,
137 -- p_course_end_date);
138 FETCH display_name_csr INTO l_creator_displayname;
139 CLOSE display_name_csr;
140 --
141 ELSE
142 l_creator_displayname := l_person_displayname;
143 END IF;
144
145 --Bug 2480134
146 FOR a IN csr_supervisor_id LOOP
147 l_supervisor_id := a.supervisor_id;
148 l_supervisor_full_name := a.full_name;
149 END LOOP;
150
151
152 FOR b IN csr_supervisor_user LOOP
153 l_supervisor_username := b.user_name;
154 END LOOP;
155
156
157 wf_engine.setitemattrtext
158 (p_item_type,
159 p_item_key,
160 'SUPERVISOR_USERNAME',
161 l_supervisor_username);
162
163
164 wf_engine.setitemattrtext
165 (p_item_type,
166 p_item_key,
167 'SUPERVISOR_DISPLAY_NAME',
168 l_supervisor_full_name);
169
170 wf_engine.setitemattrtext
171 (p_item_type,
172 p_item_key,
173 'SUPERVISOR_ID',
174 l_supervisor_id);
175
176 --Bug 2480134
177 --
178 wf_engine.setitemattrtext
179 (p_item_type,
180 p_item_key,
181 'OTA_EVENT_TITLE',
182 p_event_title);
183 wf_engine.setitemattrtext
184 (p_item_type,
185 p_item_key,
186 'CURRENT_PERSON_ID',
187 p_person_id);
188 --
189 wf_engine.setitemattrtext
190 (p_item_type,
191 p_item_key,
192 'CURRENT_PERSON_USERNAME',
193 l_current_person_name);
194 --
195 wf_engine.setitemattrtext
196 (p_item_type,
197 p_item_key,
198 'CREATOR_PERSON_USERNAME',
199 l_current_username);
200 --
201 wf_engine.setitemattrtext
202 (p_item_type,
203 p_item_key,
204 'CURRENT_PERSON_DISPLAY_NAME',
205 l_person_displayname);
206 --
207 wf_engine.setitemattrtext
208 (p_item_type,
209 p_item_key,
210 'APPROVAL_CREATOR_DISPLAY_NAME',
211 l_creator_displayname);
212 --
213 wf_engine.setitemattrtext
214 (p_item_type,
215 p_item_key,
216 'OTA_COURSE_START_DATE',
217 p_course_start_date);
218
219 wf_engine.setitemattrtext
220 (p_item_type,
221 p_item_key,
222 'OTA_ACTIVITY_VERSION_NAME',
223 p_version_name);
224
225 --
226 hr_utility.set_location('Leaving:'||l_proc, 20);
227 EXCEPTION
228 WHEN OTHERS THEN
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 x_msg_data := SQLCODE||': '||SUBSTR(SQLERRM, 1, 950);
231 --
232 hr_utility.set_location('Leaving:'||l_proc, 30);
233 --
234 --
235 END create_enroll_wf_process;
236 --
237 --
238 --
239 -- ----------------------------------------------------------------------------
240 -- |-----------------------------< cancel_enrollment>-------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 -- This procedure is the entry point to this package and will be called from the
246 -- View Enrollment Details Screen on pressing 'Submit'.
247 -- This procedure will be used to call the cancel the enrollment Id passed in and
248 -- update the Enrollment with the Cancellation details.
249 --
250 -- Pre Conditions:
251 -- None.
252 --
253 -- In Arguments:
254 -- p_init_msg_list
255 -- p_booking_id
256 -- p_event_id
257 -- p_person_id
258 -- p_booking_status_type_id
259 -- p_cancel_reason
260 -- p_username
261 -- p_waitlist_size
262 -- p_item_key
263 -- p_item_type
264 --
265 -- Out Arguments:
266 -- x_return_status
267 -- x_msg_count
268 -- x_msg_data
269 --
270 -- Post Success:
271 -- Processing continues.
272 --
273 --
274 -- Post Failure:
275 -- None.
276 --
277 -- Access Status:
278 -- Public.
279 --
280 -- {End Of Comments}
281 ----------------------------------------------------------------------------
282 PROCEDURE cancel_enrollment
283 (p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2,
287 p_booking_id IN NUMBER,
288 p_event_id IN NUMBER,
289 p_person_id IN NUMBER,
290 p_booking_status_type_id IN NUMBER,
291 p_cancel_reason IN VARCHAR2,
292 p_username IN VARCHAR2,
293 p_waitlist_size IN NUMBER,
294 p_item_key IN VARCHAR2 DEFAULT NULL,
295 p_item_type IN VARCHAR2 DEFAULT NULL,
296 p_tdb_information_category IN VARCHAR2 DEFAULT NULL,
297 p_tdb_information1 IN VARCHAR2 DEFAULT NULL,
298 p_tdb_information2 IN VARCHAR2 DEFAULT NULL,
299 p_tdb_information3 IN VARCHAR2 DEFAULT NULL,
300 p_tdb_information4 IN VARCHAR2 DEFAULT NULL,
301 p_tdb_information5 IN VARCHAR2 DEFAULT NULL,
302 p_tdb_information6 IN VARCHAR2 DEFAULT NULL,
303 p_tdb_information7 IN VARCHAR2 DEFAULT NULL,
304 p_tdb_information8 IN VARCHAR2 DEFAULT NULL,
305 p_tdb_information9 IN VARCHAR2 DEFAULT NULL,
306 p_tdb_information10 IN VARCHAR2 DEFAULT NULL,
307 p_tdb_information11 IN VARCHAR2 DEFAULT NULL,
308 p_tdb_information12 IN VARCHAR2 DEFAULT NULL,
309 p_tdb_information13 IN VARCHAR2 DEFAULT NULL,
310 p_tdb_information14 IN VARCHAR2 DEFAULT NULL,
311 p_tdb_information15 IN VARCHAR2 DEFAULT NULL,
312 p_tdb_information16 IN VARCHAR2 DEFAULT NULL,
313 p_tdb_information17 IN VARCHAR2 DEFAULT NULL,
314 p_tdb_information18 IN VARCHAR2 DEFAULT NULL,
315 p_tdb_information19 IN VARCHAR2 DEFAULT NULL,
316 p_tdb_information20 IN VARCHAR2 DEFAULT NULL
317 )
318 --
319 IS
320 --
321 -- ------------------------
322 -- event_csr variables
323 -- ------------------------
324 --
325 l_event_title ota_events.title%TYPE;
326 l_event_status ota_events.event_status%TYPE;
327 l_course_start_date ota_events.course_start_date%TYPE;
328 l_course_start_time ota_events.course_start_time%TYPE;
329 l_course_end_date ota_events.course_start_date%TYPE;
330 l_owner_id ota_events.owner_id%TYPE;
331 --
332 -- ------------------------
333 -- booking_csr variables
334 -- ------------------------
335 --
336 l_date_booking_placed ota_delegate_bookings.date_booking_placed%TYPE;
337 l_content_player_status ota_delegate_bookings.content_player_status%TYPE;
338 l_object_version_number ota_delegate_bookings.object_version_number%TYPE;
339 --
340 -- ------------------------
341 -- Finance_csr Variables
342 -- ------------------------
343 --
344 l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
345 l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
346 l_transfer_status ota_finance_lines.transfer_status%TYPE;
347 lf_booking_id ota_finance_lines.booking_id%TYPE;
348 lf_object_version_number ota_finance_lines.object_version_number%TYPE;
349 l_sequence_number ota_finance_lines.sequence_number%TYPE;
350 l_finance_count number(10);
351 l_cancelled_flag ota_finance_lines.cancelled_flag%type;
352 l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
353 --
354 -- ------------------------
355 -- header_csr Variables
356 -- ------------------------
357 --
358 lh_finance_header_id ota_finance_headers.finance_header_id%TYPE;
359 lh_cancelled_flag ota_finance_headers.cancelled_flag%TYPE;
360 lh_transfer_status ota_finance_headers.transfer_status%TYPE;
361 lh_object_version_number ota_finance_headers.object_version_number%TYPE;
362 --
363 -- ------------------------
364 -- other local Variables
365 -- ------------------------
366 --
367 l_hours_until_class_starts NUMBER;
368 l_minimum_advance_notice NUMBER;
369 l_auto_waitlist_days NUMBER;
370 l_sysdate DATE := SYSDATE;
371 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
372 l_msg_data VARCHAR2(1000);
373 l_daemon_flag ota_delegate_bookings.daemon_flag%TYPE;
374 l_daemon_type ota_delegate_bookings.daemon_type%TYPE;
375 lb_object_version_number ota_delegate_bookings.object_version_number%TYPE;
376 l_wf_exception EXCEPTION;
377 ltt_item_attribute hr_workflow_service.g_varchar2_tab_type;
378 ltt_item_attribute_value hr_workflow_service.g_varchar2_tab_type;
379 l_proc VARCHAR2(72) := 'ota_cancel_enrollment_ss.cancel_enrollment';
380 l_activity_version_name ota_activity_versions.version_name%TYPE;
381 --
382 CURSOR event_csr (p_event_id ota_events.event_id%TYPE)
383 IS
384 SELECT a.version_name,
385 e.title,
386 e.event_status,
387 e.course_start_date,
388 e.course_start_time,
389 e.course_end_date,
390 e.owner_id
391 FROM ota_events_vl e,
392 ota_activity_versions_tl a
393 WHERE a.activity_version_id = e.activity_version_id
394 AND e.event_id = p_event_id
395 AND language=userenv('LANG');
396 --
397
398 CURSOR booking_csr (p_booking_id ota_delegate_bookings.booking_id%TYPE)
399 IS
400 SELECT b.date_booking_placed, b.content_player_status, b.object_version_number
401 FROM ota_delegate_bookings b
402 WHERE b.booking_id = p_booking_id;
403 --
404 CURSOR finance_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
405 IS
406 SELECT fln.finance_line_id finance_line_id,
407 fln.finance_header_id finance_header_id,
408 fln.transfer_status transfer_status,
409 fln.booking_id booking_id,
410 fln.object_version_number object_version_number,
411 fln.sequence_number sequence_number,
412 fln.Cancelled_flag cancelled_flag
413 FROM ota_finance_lines fln
414 WHERE fln.booking_id = p_booking_id;
415 --
416 CURSOR finance_count_csr (p_finance_header_id ota_finance_lines.finance_header_id%TYPE)
417 IS
418 SELECT COUNT(*)
419 FROM ota_finance_lines fln
420 WHERE fln.finance_header_id = p_finance_header_id;
421 --
422 CURSOR header_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
423 IS
424 SELECT flh.finance_header_id finance_header_id,
425 flh.cancelled_flag cancelled_flag,
426 flh.transfer_status transfer_status,
427 flh.object_version_number object_version_number
428 FROM ota_finance_headers flh,
429 ota_finance_lines fln
430 WHERE flh.finance_header_id = fln.finance_header_id
431 AND fln.booking_id = p_booking_id;
432 --
433 --
434 CURSOR C_USER(p_owner_id NUMBER) IS
435 SELECT USER_NAME
436 FROM FND_USER
437 WHERE Employee_id = p_owner_id;
438 l_username fnd_user.user_name%TYPE;
439 --
440 BEGIN
441 --
442 hr_utility.set_location('Entering:'||l_proc, 10);
443
444 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
445 FND_MSG_PUB.INITIALIZE;
446 END IF;
447 --
448 OPEN event_csr (p_event_id);
449 FETCH event_csr INTO l_activity_version_name,
450 l_event_title,
451 l_event_status,
452 l_course_start_date,
453 l_course_start_time,
454 l_course_end_date, --l_course_start_date,
455 l_owner_id;
456 CLOSE event_csr;
457 --
458 IF l_owner_id IS NULL THEN
459 l_owner_id := fnd_profile.value('OTA_DEFAULT_EVENT_OWNER');
460 END IF;
461
462
463 OPEN c_user(l_owner_id);
464 FETCH c_user INTO l_username;
465 CLOSE c_user;
466
467 OPEN booking_csr (p_booking_id);
468 FETCH booking_csr INTO l_date_booking_placed,
469 l_content_player_status,
470 l_object_version_number;
471 CLOSE booking_csr;
472 --
473 OPEN finance_csr (p_booking_id);
474 FETCH finance_csr INTO l_finance_line_id,
475 l_finance_header_id,
476 l_transfer_status,
477 lf_booking_id,
478 lf_object_version_number,
479 l_sequence_number,
480 l_cancelled_flag ;
481 --
482 --Start Bug 2700158--
483 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);
484 --End Bug 2700158--
485
486 IF finance_csr%found THEN
487 --
488 l_minimum_advance_notice := NVL(TO_NUMBER(fnd_profile.value('OTA_CANCEL_HOURS_BEFORE_EVENT')), 0);
489 --
490 --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);
491
492
493 --
494 IF l_transfer_status = 'ST' OR
495 l_cancelled_flag = 'Y' OR
496 l_content_player_status IS NOT NULL OR
497 l_hours_until_class_starts < l_minimum_advance_notice THEN
498 NULL;
499 ELSE -- Call Finance Lines API (Cancel Finance Line)
500 --
501 OPEN finance_count_csr (l_finance_header_id);
502 FETCH finance_count_csr INTO l_finance_count;
503 CLOSE finance_count_csr;
504 --
505 IF l_finance_count = 1 THEN -- If only one Finance Line
506 --
507 OPEN header_csr (p_booking_id);
508 FETCH header_csr INTO lh_finance_header_id,
509 lh_cancelled_flag,
510 lh_transfer_status,
511 lh_object_version_number;
512 CLOSE header_csr;
513
514 --
515 IF lh_transfer_status <> 'ST' or lh_cancelled_flag <>'Y' THEN -- Call Finance Header API
516 -- to Cancel Finance Header
517 --
518 ota_tfh_api_business_rules.cancel_header
519 (p_finance_header_id => lh_finance_header_id,
520 p_cancel_header_id => l_cancel_header_id,
521 p_date_raised => l_sysdate,
522 p_validate => false,
523 p_commit => false);
524 END IF;
525 --
526 ELSE
527 --
528 ota_tfl_api_upd.upd(p_finance_line_id => l_finance_line_id,
532 p_sequence_number => l_sequence_number,
529 p_date_raised => l_sysdate,
530 p_cancelled_flag => 'Y',
531 p_object_version_number => lf_object_version_number,
533 p_validate => false,
534 p_transaction_type => 'CANCEL_HEADER_LINE');
535 --
536 END IF;
537 --
538 --
539 END IF; -- For Lines;
540 --
541 END IF;
542 --
543 CLOSE finance_csr;
544 --
545 -- Initialize workflow setings
546 --
547 l_auto_waitlist_days := TO_NUMBER(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
548 --
549 IF (p_waitlist_size > 0) THEN
550 --
551 IF (l_hours_until_class_starts >= l_auto_waitlist_days) THEN
552 --
553 l_daemon_flag := 'Y';
554 l_daemon_type := 'W';
555 --
556 ELSE
557 --
558 IF l_username IS NOT NULL THEN
559 ota_initialization_wf.manual_waitlist
560 (p_itemtype => 'OTWF',
561 p_process => 'OTA_MANUAL_WAITLIST',
562 p_event_title => l_event_title,
563 p_event_id => p_event_id,
564 p_item_key => p_booking_id||':'||to_char(l_sysdate,'DD-MON-YYYY:HH24:MI:SS'),
565 -- fnd_date.date_to_displaydate(l_sysdate),
566 p_user_name => l_username);
567 END IF;
568 --
569 END IF;
570 --
571 ELSE
572 --
573 l_daemon_flag := NULL;
574 l_daemon_type := NULL;
575 --
576 END IF;
577 --
578 -- Call update enrollment API to cancel Enrollment
579 --
580 ota_tdb_api_upd2.update_enrollment
581 (p_booking_id => p_booking_id,
582 p_booking_status_type_id => p_booking_status_type_id,
583 p_object_version_number => l_object_version_number,
584 p_event_id => p_event_id,
585 p_status_change_comments => p_cancel_reason, --Bug 2332743
586 p_tfl_object_version_number => lf_object_version_number,
587 p_finance_line_id => l_finance_line_id,
588 p_daemon_flag => l_daemon_flag,
589 p_daemon_type => l_daemon_type,
590 p_date_status_changed => l_sysdate,
591 p_date_booking_placed => l_date_booking_placed,
592 p_tdb_information_category => p_tdb_information_category,
593 p_tdb_information1 => p_tdb_information1,
594 p_tdb_information2 => p_tdb_information2,
595 p_tdb_information3 => p_tdb_information3,
596 p_tdb_information4 => p_tdb_information4,
597 p_tdb_information5 => p_tdb_information5,
598 p_tdb_information6 => p_tdb_information6,
599 p_tdb_information7 => p_tdb_information7,
600 p_tdb_information8 => p_tdb_information8,
601 p_tdb_information9 => p_tdb_information9,
602 p_tdb_information10 => p_tdb_information10,
603 p_tdb_information11 => p_tdb_information11,
604 p_tdb_information12 => p_tdb_information12,
605 p_tdb_information13 => p_tdb_information13,
606 p_tdb_information14 => p_tdb_information14,
607 p_tdb_information15 => p_tdb_information15,
608 p_tdb_information16 => p_tdb_information16,
609 p_tdb_information17 => p_tdb_information17,
610 p_tdb_information18 => p_tdb_information18,
611 p_tdb_information19 => p_tdb_information19,
612 p_tdb_information20 => p_tdb_information20
613 );
614 --
615 -- Call cancel enrollment workflow
616 --
617 create_enroll_wf_process
618 (x_return_status => l_return_status,
619 x_msg_data => l_msg_data,
620 p_item_key => p_item_key,
621 p_item_type => p_item_type ,
622 p_person_id => p_person_id,
623 p_event_title => l_event_title,
624 p_course_start_date => l_course_start_date,
625 p_course_end_date => l_course_end_date,
626 p_version_name => l_activity_version_name);
627
628 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
629 RAISE l_wf_exception;
630 END IF;
631 --
632 -- Populate standard return values if cancel enrollment
633 -- and work flow creation were successful
634 --
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636 x_msg_count := 1;
637 x_msg_data := 'OTA_CANCEL_ENROLLMENT'; -- return message name
638 --
639 hr_utility.set_location('Leaving:'||l_proc, 20);
640
641 EXCEPTION
642 --
643 WHEN l_wf_exception THEN
644 --
645 FND_MESSAGE.SET_NAME('OTA', 'OTA_IBE_UNEXP_ERR');
646 FND_MESSAGE.SET_TOKEN('OTA_IBE_UNEXP_ERR_MSG', l_msg_data);
647 FND_MSG_PUB.ADD;
648 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
649 p_data => x_msg_data);
650 hr_utility.set_location('Leaving:'||l_proc, 30);
651
652 x_return_status := 'E';
653 --
654
655 --
656 WHEN OTHERS THEN
657 --
658 l_msg_data := SQLCODE||': '||SUBSTR(SQLERRM, 1, 950);
659 FND_MESSAGE.SET_NAME('OTA', 'OTA_IBE_UNEXP_ERR');
660 FND_MESSAGE.SET_TOKEN('OTA_IBE_UNEXP_ERR_MSG', l_msg_data);
661 FND_MSG_PUB.ADD;
662 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
663 p_data => x_msg_data);
664 x_return_status := 'E';
665 hr_utility.set_location('Leaving:'||l_proc, 40);
666 --
667 END cancel_enrollment;
668 --
669 -- ----------------------------------------------------------------------------
670 -- |-------------------------< get_booking_status_comments >----------------------------|
671 -- ----------------------------------------------------------------------------
672 --
673 -- Description: get the comments from the booking history table for the
674 -- booking_id and booking_status_type_id passed in as parameters.
675 --
676 --
677
678 FUNCTION get_booking_status_comments(p_booking_id IN NUMBER,
679 p_booking_status_type_id IN NUMBER) RETURN VARCHAR2
680 IS
681 CURSOR comments_cr IS
682 SELECT bsh.comments
683 FROM ota_booking_status_histories bsh
684 WHERE bsh.booking_id = p_booking_id
685 AND bsh.booking_status_type_id = p_booking_status_type_id
686 ORDER BY bsh.start_date ASC;
687 --
688 --
689 l_comments ota_booking_status_histories.comments%TYPE := null;
690 l_proc varchar2(72) := 'ota_cancel_enrollment_ss.get_booking_status_comments';
691 --
692 begin
693 --
694 --
695 hr_utility.set_location('Entering:'|| l_proc, 5);
696 --
697 --
698 FOR comments_rec IN comments_cr
699 LOOP
700 l_comments := comments_rec.comments;
701
702 END LOOP;
703
704 RETURN l_comments;
705 --
706 --
707 EXCEPTION
708 WHEN others then
709 RETURN l_comments;
710 END get_booking_status_comments;
711
712 END ota_cancel_training_ss;