[Home] [Help]
PACKAGE BODY: APPS.OTA_UTILITY
Source
1 Package body ota_utility as
2 /* $Header: ottomint.pkb 120.58.12020000.3 2012/06/29 11:19:18 rpahune ship $ */
3 g_package varchar2(33) := ' ota_utility.'; -- Global package name
4
5 g_wait_list_booking varchar2(1) := 'W';
6 g_placed_booking varchar2(1) := 'P';
7 g_attended_booking varchar2(1) := 'A';
8 g_cancelled_booking varchar2(1) := 'C';
9 g_requested_booking varchar2(1) := 'R';
10 --
11 -- Event Statuses
12 --
13 g_full_event varchar2(1) := 'W';
14 g_normal_event varchar2(1) := 'N';
15 g_planned_event varchar2(1) := 'P';
16 g_closed_event varchar2(1) := 'C';
17
18
19 Function get_test_time(p_lo_time number)
20 return varchar2
21 is
22 l_lo_time number := nvl(p_lo_time,0);
23 l_Seconds number;
24 l_Minutes number;
25 l_Hours number;
26 l_formatted_hour varchar(20) := '';
27 l_formatted_min varchar(20) := '';
28 l_formatted_sec varchar(20) := '';
29 l_formatted_time varchar(50) := '';
30
31 begin
32
33 l_lo_time := round(l_lo_time);
34
35 l_Seconds := l_lo_time mod 60;
36 l_Minutes := floor(l_lo_time / 60);
37 l_Hours := floor(l_Minutes/60);
38 l_Minutes := l_Minutes - l_Hours * 60;
39
40 If (l_Hours < 10) Then
41 l_formatted_hour := '0' || l_Hours;
42 Else
43 l_formatted_hour := l_Hours;
44 End If;
45
46 If (l_Minutes < 10) Then
47 l_formatted_min := '0' || l_Minutes;
48 Else
49 l_formatted_min := l_Minutes;
50 End If;
51
52 If (l_Seconds < 10) Then
53 l_formatted_sec := '0' || l_Seconds;
54 Else
55 l_formatted_sec := l_Seconds;
56 End If;
57
58 fnd_message.set_name('OTA', 'OTA_443358_SRCH_LO_TIME');
59 fnd_message.set_token ('HOUR', l_formatted_hour);
60 fnd_message.set_token ('MIN', l_formatted_min);
61 fnd_message.set_token ('SEC', l_formatted_sec);
62 l_formatted_time := fnd_message.get();
63
64 return l_formatted_time;
65 end get_test_time;
66
67 function is_con_prog_periodic(p_name in varchar2)
68 return boolean
69 is
70
71 cursor get_con_prog_sch
72 is
73 select a.resubmit_interval,a.resubmit_end_date
74 from fnd_concurrent_requests a, fnd_concurrent_programs b
75 where a.concurrent_program_id = b.concurrent_program_id
76 and b.concurrent_program_name = p_name
77 and b.application_id = 810
78 and a.status_code ='I'
79 and a.hold_flag ='N'
80 and rownum=1
81 order by a.request_id desc;
82
83 l_resubmit_interval varchar2(10);
84 l_resubmit_end_date date ;
85
86 begin
87
88 open get_con_prog_sch;
89 fetch get_con_prog_sch into
90 l_resubmit_interval,l_resubmit_end_date;
91 close get_con_prog_sch;
92
93 if l_resubmit_interval is not null and trunc(sysdate) <= nvl(l_resubmit_end_date,hr_api.g_eot) then
94
95 return true;
96 else
97 return false;
98
99 end if;
100
101 end is_con_prog_periodic;
102
103 -- ----------------------------------------------------------------
104 -- ------------------<get_resource_count >--------------------
105 -- ----------------------------------------------------------------
106 function get_resource_count(peventid number)
107 return varchar2
108 is
109 l_resource_count number := 0;
110 l_meaning varchar2(30);
111 l_return_value Varchar2(100);
112
113 cursor getcount
114 is select count(resource_booking_id)
115 from ota_resource_bookings
116 where event_id = peventid;
117 --and status ='C';
118
119 begin
120
121 open getcount;
122 fetch getcount into l_resource_count;
123 close getcount;
124
125 l_meaning := get_lookup_meaning('OTA_OBJECT_TYPE','R',810);
126
127 l_return_value := l_resource_count||' ' ||l_meaning;
128
129 return l_return_value;
130
131 end get_resource_count;
132
133
134
135 function get_default_comp_upd_level(p_obj_id in number,
136 p_obj_type varchar2)
137 return varchar2
138 is
139
140 l_return_value varchar2(200) := null;
141 l_lookup_value varchar2(30) := null;
142
143 cursor default_run_value is
144 SELECT waav.text_value FROM WF_ACTIVITY_ATTR_VALUES WAAV
145 WHERE WAAV.PROCESS_ACTIVITY_ID = (select max(instance_id) from
146 wf_process_activities wpa where wpa.process_name =
147 'OTA_COMPETENCE_UPDATE_JSP_PRC'
148 and wpa.activity_name = 'OTA_COMPETENCE_NOTIFY_APPROVAL'
149 and wpa.instance_label = 'OTA_COMPETENCE_NOTIFY_APPROVAL'
150 and wpa.process_version = wpa.process_version
151 and wpa.process_item_type = 'HRSSA' )
152 AND WAAV.NAME = 'HR_APPROVAL_REQ_FLAG' ;
153
154 -- if above returns null then take from below
155 cursor default_value is
156 select text_default from wf_activity_attributes where NAME = 'HR_APPROVAL_REQ_FLAG'
157 and activity_item_type = 'HRSSA'
158 and activity_name = 'OTA_COMPETENCE_NOTIFY_APPROVAL';
159
160 cursor value_course_level is
161 select oav.competency_update_level from
162 ota_activity_versions oav
163 --, ota_offerings off
164 where oav.activity_version_id = p_obj_id;
165 --and off.offering_id = p_obj_id;
166
167 begin
168
169
170
171 if p_obj_type = 'OFFERING' then
172 open value_course_level;
173 fetch value_course_level into l_lookup_value;
174 close value_course_level;
175
176
177 end if;
178
179 if l_lookup_value is null then
180
181 open default_run_value;
182 fetch default_run_value into l_lookup_value;
183 close default_run_value;
184 end if;
185
186 if l_lookup_value is null then
187
188 open default_value;
189 fetch default_value into l_lookup_value;
190 close default_value;
191
192 end if;
193
194 l_return_value := get_lookup_meaning('OTA_COMPETENCY_UPDATE_LEVEL',l_lookup_value,810);
195
196
197 return l_return_value;
198
199 end get_default_comp_upd_level;
200
201 -- ----------------------------------------------------------------
202 -- ------------------<get_session_count >--------------------
203 -- ----------------------------------------------------------------
204 function get_session_count(peventid number)
205 return varchar2
206 is
207 l_event_count number := 0;
208 l_meaning varchar2(30);
209 l_return_value Varchar2(2000);
210
211 cursor getcount
212 is select count(event_id) from
213 ota_events where
214 parent_event_id = peventid and
215 parent_event_id is not null;
216
217 begin
218
219 open getcount;
220 fetch getcount into l_event_count;
221 close getcount;
222
223 fnd_message.set_name('OTA','OTA_443973_SSN_COUNT');
224 fnd_message.set_token('COUNT', l_event_count);
225
226 l_return_value := fnd_message.get();
227
228 return l_return_value;
229
230 end get_session_count;
231
232 -- ----------------------------------------------------------------
233 -- ------------------<get_child_count >--------------------
234 -- ----------------------------------------------------------------
235 function get_child_count(p_object_id NUMBER,
236 p_object_type VARCHAR2)
237 return varchar2
238 is
239 l_child_count NUMBER := 0;
240 l_message_name VARCHAR2(40);
241 l_return_value VARCHAR2(2000);
242
243 begin
244
245 IF p_object_type = 'OFS' THEN
246 l_message_name := 'OTA_443971_OFF_COUNT';
247 l_child_count := ota_utility.get_course_offering_count(p_object_id);
248 ELSIF p_object_type = 'CLS' THEN
249 l_message_name := 'OTA_443972_EVT_COUNT';
250 l_child_count := ota_utility.get_event_count(p_object_id);
251 END IF;
252 -- Modified for bug#5158213
253 -- IF l_child_count <> 0 THEN
254 fnd_message.set_name('OTA',l_message_name);
255 fnd_message.set_token('COUNT', l_child_count);
256
257 l_return_value := fnd_message.get();
258 -- ELSE
259 -- l_return_value := null;
260 -- END IF;
261
262 RETURN l_return_value;
263
264 end get_child_count;
265
266 -- ----------------------------------------------------------------------------
267 -- |-----------------------------< ignore_dff_validation >---------------------------|
268 -- ----------------------------------------------------------------------------
269 Procedure ignore_dff_validation(p_dff_name in varchar2)
270 is
271 l_proc varchar2(72) := g_package||'ignore_dff_validation';
272
273 l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
274 hr_dflex_utility.l_ignore_dfcode_varray();
275
276 begin
277 hr_utility.set_location('Entering:'||l_proc, 5);
278
279 l_add_struct_d.extend(1);
280 l_add_struct_d(l_add_struct_d.count) := p_dff_name;
281
282 hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
283
284
285 hr_utility.set_location('Leaving:'||l_proc, 5);
286
287 end ignore_dff_validation;
288
289
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------------< GET_DESCIRPTION >---------------------------|
292 -- ----------------------------------------------------------------------------
293 -- {Start Of Comments}
294 --
295 -- Description:
296 -- This procedure will be used to retrieve enrollment and event information
297 -- for AR interface.
298 --
299 --
300 -- Pre Conditions:
301 -- None.
302 --
303 -- In Arguments:
304 -- p_line_id,
305 -- p_uom
306 --
307 -- Out Arguments:
308 -- p_description
309 -- p_course_end_date
310 -- e_return_status
311 --
312 -- Post Success:
313 -- Processing continues.
314 --
315 --
316 -- Post Failure:
317 -- None.
318 --
319 -- Access Status:
320 -- Public.
321 --
322 -- {End Of Comments}
323 ----------------------------------------------------------------------------
324
325 Procedure GET_DESCRIPTION (p_line_id in number,
326 p_uom in varchar2,
327 x_description out nocopy varchar2,
328 x_course_end_date out nocopy date,
329 x_return_status out nocopy varchar2)
330 IS
331
332 l_event_title ota_events_tl.title%type; --MLS Change added _tl
333 l_start_date date;
334 l_end_date date;
335 l_full_name ota_customer_contacts_v.full_name%type;
336 l_contact_id hz_cust_account_roles.cust_account_role_id%type;
337 l_version_name ota_activity_versions_tl.version_name%type; --MLS Change added _tl
338
339
340 l_max_attendee number(3);
341 l_min_attendee number(3);
342
343
344 l_title_prompt varchar2(80) ;
345 l_start_prompt varchar2(80) ;
346 l_end_prompt varchar2(80) ;
347
348 l_student_prompt varchar2(80) ;
349
350 l_version_name_prompt varchar2(80) ;
351
352 l_date_format varchar2(200);
353
354 l_max_prompt varchar2(80) ;
355
356
357 Cursor c_enrollment
358 IS
359 select tav.version_name,
360 evt.Title,
361 evt.Course_Start_Date,
362 evt.Course_End_Date,
363 tdb.delegate_contact_id
364 From
365 ota_Delegate_bookings tdb,
366 ota_Events_vl evt, --MLS change _vl added
367 ota_activity_versions_tl tav -- MLS change _tl added
368 Where
369 evt.event_id = tdb.event_id and
370 tdb.line_id = p_line_id and
371 evt.activity_version_id = tav.activity_version_id;
372
373
374
375 Cursor c_student
376 IS
377 Select full_name
378 From
379 ota_customer_contacts_v
380 Where
381 contact_id = l_contact_id;
382
383 CURSOR C_Event
384 IS
385 Select
386 tav.version_name,
387 evt.title,
388 evt.Course_Start_Date,
389 evt.course_End_Date,
390 evt.Maximum_Attendees
391 FROM ota_events_vl evt, -- MLS change _vl added
392 ota_activity_versions_tl tav --MLS change _tl added
393 WHERE evt.line_id = p_line_id and
394 evt.activity_version_id = tav.activity_version_id;
395
396 cursor c_date_format is
397 select value
398 from v$parameter
399 where name ='nls_date_format';
400
401
402 l_proc varchar2(72) := g_package||'get_description';
403
404 BEGIN
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 OPEN c_date_format;
407 FETCH c_date_format into l_date_format;
408 CLOSE c_date_format;
409
410 IF p_uom= 'ENR' THEN
411 hr_utility.set_location('Entering:'||l_proc, 10);
412 l_title_prompt :=
413 ota_utility.Get_lookup_meaning ('OTA_ENROLL_INVOICE' ,'EVENT',810);
414 l_start_prompt :=
415 ota_utility.Get_lookup_meaning ('OTA_ENROLL_INVOICE' ,'START',810);
416 l_end_prompt :=
417 ota_utility.Get_lookup_meaning ('OTA_ENROLL_INVOICE' ,'END',810);
418
419 l_student_prompt :=
420 ota_utility.Get_lookup_meaning ('OTA_ENROLL_INVOICE' ,'STUDENT',810);
421
422 l_version_name_prompt :=
423 ota_utility.Get_lookup_meaning ('OTA_ENROLL_INVOICE' ,'ACTIVITY',810);
424
425
426 OPEN C_Enrollment;
427 FETCH C_enrollment into l_version_name,
428 l_event_title,
429 l_start_date,
430 l_end_date,
431 l_contact_id;
432
433 IF c_enrollment%found then
434
435
436 OPEN c_student;
437
438 FETCH c_student into l_full_name;
439 CLOSE c_student;
440
441 IF l_version_name_prompt is not null then
442 x_description := l_version_name_prompt ||' '|| l_version_name;
443 END IF;
444
445 IF l_student_prompt is not null then
446 x_description := x_description||','||l_student_prompt ||' '|| l_full_name;
447 END IF;
448
449 IF l_start_prompt is not null then
450 x_description := x_description||','||l_start_prompt ||' '|| to_char(l_start_date,l_date_format);
451 END IF;
452
453 IF l_end_prompt is not null then
454 x_description := x_description||','||l_end_prompt ||' '|| to_char(l_end_date,l_date_format);
455 END IF;
456
457 IF l_title_prompt is not null then
458 x_description := x_description||','||l_title_prompt ||' '|| l_event_title;
459 END IF;
460
461
462 -- x_course_end_date := l_end_date;
463 x_course_end_date := null;
464 End if;
465 CLOSE C_enrollment;
466
467 ELSIF p_uom = 'EVT' THEN
468 hr_utility.set_location('Entering:'||l_proc, 15);
469 l_title_prompt :=
470 ota_utility.Get_lookup_meaning ('OTA_EVENT_INVOICE' ,'EVENT',810);
471
472 l_start_prompt :=
473 ota_utility.Get_lookup_meaning ('OTA_EVENT_INVOICE' ,'START',810);
474
475 l_end_prompt :=
476 ota_utility.Get_lookup_meaning ('OTA_EVENT_INVOICE' ,'END',810);
477
478 l_max_prompt :=
479 ota_utility.Get_lookup_meaning ('OTA_EVENT_INVOICE' ,'MAX',810);
480
481 l_version_name_prompt :=
482 ota_utility.Get_lookup_meaning ('OTA_EVENT_INVOICE' ,'ACTIVITY',810);
483
484 OPEN C_event;
485 FETCH C_event into
486 l_version_name,
487 l_event_title,
488 l_start_date,
489 l_end_date,
490 l_max_attendee;
491 If c_event%found then
492
493
494 IF l_version_name_prompt is not null then
495 x_description := l_version_name_prompt ||' '|| l_version_name;
496 END IF;
497 IF l_max_prompt is not null then
498 x_description := x_description||','||l_max_prompt ||' '||to_char(l_max_attendee);
499 END IF;
500
501 IF l_start_prompt is not null then
502 x_description := x_description||','||l_start_prompt ||' '|| to_char(l_start_date,l_date_format);
503 END IF;
504
505 IF l_end_prompt is not null then
506 x_description := x_description||','||l_end_prompt ||' '|| to_char(l_end_date,l_date_format);
507 END IF;
508
509 IF l_title_prompt is not null then
510 x_description := x_description||','||l_title_prompt ||' '|| l_event_title;
511 END IF;
512
513 -- x_course_end_date := l_end_date;
514 x_course_end_date := null;
515
516
517 End if;
518
519 CLOSE C_event;
520 END IF;
521 hr_utility.set_location('Leaving:'||l_proc, 15);
522
523 END;
524
525 -- ----------------------------------------------------------------------------
526 -- |------------------------< get_invoice_rule >----------------------------|
527 -- ----------------------------------------------------------------------------
528 -- {Start Of Comments}
529 --
530 -- Description:
531 -- This procedure is used to retrieve invoicing rule for Order Line.
532 --
533 --
534 -- Pre Conditions:
535 -- None.
536 --
537 -- In Arguments:
538 -- p_line_id
539 --
540 -- Out Argument
541 -- p_invoice_rule
542 --
543 -- Post Success:
544 -- Processing continues.
545 --
546 --
547 -- Post Failure:
548 -- None.
549 --
550 -- Access Status:
551 -- Public.
552 --
553 -- {End Of Comments}
554 ----------------------------------------------------------------------------
555
556 PROCEDURE GET_INVOICE_RULE
557 (
558 p_line_id IN NUMBER,
559 p_invoice_rule OUT NOCOPY VARCHAR2
560 )
561 IS
562
563 CURSOR c_invoice_rule
564 IS
565 SELECT
566 invoicing_rule_id
567 FROM
568 oe_order_lines_all
569 WHERE
570 line_id = p_line_id;
571
572 l_proc varchar2(72) := g_package||'get_invoice_rule';
573 l_rule_id ra_rules.rule_id%type;
574
575 BEGIN
576 hr_utility.set_location('Entering:'||l_proc, 5);
577 OPEN c_invoice_rule;
578 FETCH c_invoice_rule INTO l_rule_id;
579 IF c_invoice_rule%found THEN
580 IF l_rule_id = -2 THEN
581 p_invoice_rule := 'ADVANCED';
582 ELSIF l_rule_id = -3 THEN
583 p_invoice_rule := 'ARREARS';
584 END IF;
585 END IF;
586 CLOSE c_invoice_rule;
587 hr_utility.set_location(' Leaving:'||l_proc, 10);
588
589 END;
590
591 -- ----------------------------------------------------------------------------
592 -- |----------------------< get_booking_status_type >-------------------------|
593 -- ----------------------------------------------------------------------------
594 -- {Start Of Comments}
595 --
596 -- Description:
597 -- This procedure will retrieve enrollment Status Type.
598 --
599 --
600 -- Pre Conditions:
601 -- None.
602 --
603 -- In Arguments:
604 -- p_status_type_id,
605 --
606 -- Out Arguments:
607 -- p_type
608 --
609 -- Post Success:
610 -- Processing continues.
611 --
612 --
613 -- Post Failure:
614 -- None.
615 --
616 -- Access Status:
617 -- Public.
618 --
619 -- {End Of Comments}
620 ----------------------------------------------------------------------------
621
622
623 PROCEDURE get_booking_status_type(
624 p_status_type_id IN number,
625 p_type OUT NOCOPY Varchar2)
626
627 IS
628
629 l_proc varchar2(72) := g_package||'get_booking_status_type';
630
631
632 CURSOR c_status_type IS
633 SELECT Type
634 FROM OTA_BOOKING_STATUS_TYPES
635 WHERE booking_status_type_id = p_status_type_id;
636
637 l_status_type ota_booking_status_types.type%type ;
638
639
640 BEGIN
641 hr_utility.set_location('Entering:'||l_proc, 5);
642 OPEN c_status_type;
643 FETCH c_status_type INTO l_status_type;
644 IF c_status_type%found THEN
645 p_type := l_status_type;
646 END IF;
647 CLOSE c_status_type;
648 hr_utility.set_location(' Leaving:'||l_proc, 10);
649
650 END;
651
652 --8855548
653 -- ----------------------------------------------------------------------------
654 -- |----------------------< get_booking_status >-------------------------|
655 -- ----------------------------------------------------------------------------
656 -- {Start Of Comments}
657 --
658 -- Description:
659 -- This procedure will retrieve enrollment Status.
660 --
661 --
662 -- Pre Conditions:
663 -- None.
664 --
665 -- In Arguments:
666 -- p_status_type_id,
667 --
668 -- Out Arguments:
669 -- p_status
670 --
671 -- Post Success:
672 -- Processing continues.
673 --
674 --
675 -- Post Failure:
676 -- None.
677 --
678 -- Access Status:
679 -- Public.
680 --
681 -- {End Of Comments}
682 ----------------------------------------------------------------------------
683
684
685 PROCEDURE get_booking_status(
686 p_status_type_id IN number,
687 p_status OUT NOCOPY Varchar2)
688
689 IS
690
691 l_proc varchar2(72) := g_package||'get_booking_status';
692
693
694 CURSOR c_status_type IS
695 SELECT name
696 FROM OTA_BOOKING_STATUS_TYPES_VL
697 WHERE booking_status_type_id = p_status_type_id;
698
699 l_status_name ota_booking_status_types.name%type ;
700
701
702 BEGIN
703 hr_utility.set_location('Entering:'||l_proc, 5);
704 OPEN c_status_type;
705 FETCH c_status_type INTO l_status_name;
706 IF c_status_type%found THEN
707 p_status := l_status_name;
708 END IF;
709 CLOSE c_status_type;
710
711 hr_utility.set_location(' Leaving:'||l_proc, 10);
712
713 END;
714
715
716 -- ----------------------------------------------------------------------------
717 -- |-----------------------------< Check_enrollment>-------------------------|
718 -- ----------------------------------------------------------------------------
719 -- {Start Of Comments}
720 --
721 -- Description:
722 -- This procedure will be used to check whether Enrollment exist or not.
723 --
724 --
725 -- Pre Conditions:
726 -- None.
727 --
728 -- In Arguments:
729 -- p_line_id,
730 --
731 -- In Arguments:
732 -- x_valid,
733 -- x_return_status
734
735 -- Post Success:
736 -- Processing continues.
737 --
738 --
739 -- Post Failure:
740 -- None.
741 --
742 -- Access Status:
743 -- Public.
744 --
745 -- {End Of Comments}
746 ----------------------------------------------------------------------------
747 Procedure check_enrollment (p_line_id IN Number ,
748 x_valid OUT NOCOPY varchar2,
749 x_return_status OUT NOCOPY varchar2 )
750
751 IS
752
753 CURSOR C_enrollment
754 IS
755 Select null
756 FROM
757 OTA_DELEGATE_BOOKINGS
758 WHERE
759 Line_id = p_line_id;
760
761
762 l_proc varchar2(72) := g_package||'check_enrollment';
763 l_exists varchar2(1) ;
764 l_valid varchar2(1) := 'N';
765
766 BEGIN
767
768 hr_utility.set_location('Entering:'||l_proc, 15);
769 open c_enrollment;
770 fetch c_enrollment into l_exists;
771 if c_enrollment%found then
772 l_valid := 'Y';
773 end If;
774 CLOSE C_enrollment;
775 x_valid := l_valid;
776 hr_utility.set_location('Leaving:'||l_proc, 15);
777
778 END;
779 --
780
781 -- ----------------------------------------------------------------------------
782 -- |-----------------------------------< Check_event>-------------------------|
783 -- ----------------------------------------------------------------------------
784 -- {Start Of Comments}
785 --
786 -- Description:
787 -- This Procedure will be used to check Whether Event exist.
788 --
789 --
790 -- Pre Conditions:
791 -- None.
792 --
793 -- In Arguments:
794 -- p_line_id,
795 --
796 -- In Arguments:
797 -- x_valid,
798 -- x_return_status
799 --
800 -- Post Success:
801 -- Processing continues.
802 --
803 --
804 -- Post Failure:
805 -- None.
806 --
807 -- Access Status:
808 -- Public.
809 --
810 -- {End Of Comments}
811 ----------------------------------------------------------------------------
812 Procedure check_event (p_line_id IN Number,
813 x_valid OUT NOCOPY varchar2,
814 x_return_status OUT NOCOPY varchar2 )
815 IS
816
817 CURSOR C_event
818 IS
819 Select null
820 FROM
821 OTA_EVENTS
822 WHERE
823 Line_id = p_line_id;
824
825
826 l_proc varchar2(72) := g_package||'check_event';
827 l_exists varchar2(1) ;
828 l_valid varchar2(1) := 'N';
829
830 BEGIN
831
832 hr_utility.set_location('Entering:'||l_proc, 15);
833 open c_event;
834 fetch c_event into l_exists;
835 if c_event%found then
836 l_valid := 'T';
837 end If;
838 CLOSE C_event;
839 x_valid := l_valid;
840 hr_utility.set_location('Leaving:'||l_proc, 15);
841
842 END;
843
844 --
845 -- ----------------------------------------------------------------------------
846 -- |----------------------------< get_lookup_meaning>-------------------------|
847 -- ----------------------------------------------------------------------------
848 -- {Start Of Comments}
849 --
850 -- Description:
851 -- This function will be used to get lookup meaning.
852 --
853 --
854 -- Pre Conditions:
855 -- None.
856 --
857 -- In Arguments:
858 -- p_lookup_type
859 -- p_lookup_code
860 -- p_application_id
861 --
862 -- Post Success:
863 -- Processing continues.
864 --
865 --
866 -- Post Failure:
867 -- None.
868 --
869 -- Access Status:
870 -- Public.
871 --
872 -- {End Of Comments}
873 ----------------------------------------------------------------------------
874
875 Function Get_lookup_meaning (
876 --******************************************************************************
877 --* Returns the meaning for a lookup code of a specified type.
878 --******************************************************************************
879 --
880 p_lookup_type varchar2,
881 p_lookup_code varchar2,
882 p_application_id number) return varchar2 is
883 --
884 cursor csr_lookup is
885 select meaning
886 from hr_lookups
887 where lookup_type = p_lookup_type
888 and lookup_code = p_lookup_code
889 and enabled_flag = 'Y';
890 --
891 l_meaning varchar2(80) := null;
892 --
893 begin
894 --
895 -- Only open the cursor if the parameters are going to retrieve anything
896 --
897 if p_lookup_type is not null and p_lookup_code is not null then
898 --
899 open csr_lookup;
900 fetch csr_lookup into l_meaning;
901 close csr_lookup;
902 --
903 end if;
904 --
905 return l_meaning;
906 --
907 end get_lookup_meaning;
908
909
910 -- ----------------------------------------------------------------------------
911 -- |--------------------------------< CHECK_INVOICE >-------------------------|
912 -- ----------------------------------------------------------------------------
913 -- {Start Of Comments}
914 --
915 -- Description:
916 -- This procedure will be a used to check the invoice of Order Line.
917 --
918 -- IN
919 -- p_line_id
920 -- p_org_id
921 --
922 -- OUT
923 -- p_exist
924 --
925 -- Post Failure:
926 -- None.
927 --
928 -- Access Status:
929 -- Public.
930 --
931 -- {End Of Comments}
932 ----------------------------------------------------------------------------
933
934 PROCEDURE CHECK_INVOICE (
935 p_Line_id IN NUMBER,
936 p_Org_id IN NUMBER,
937 p_exist OUT NOCOPY VARCHAR2)
938 IS
939
940 l_proc varchar2(72) := g_package||'check_invoice';
941 l_invoice_quantity oe_order_lines.invoiced_quantity%type;
942
943
944 CURSOR c_invoice IS
945 SELECT
946 decode(invoiced_quantity,null,0,invoiced_quantity)
947 FROM
948 oe_order_lines_all
949 WHERE
950 line_id = p_line_id;
951
952
953 BEGIN
954 hr_utility.set_location('Entering:'||l_proc, 5);
955
956 OPEN c_invoice;
957 FETCH c_invoice into l_invoice_quantity;
958 IF c_invoice%found THEN
959 IF l_invoice_quantity = 1 then
960 p_exist := 'Y';
961 ELSE
962 p_exist := 'N';
963 END IF;
964 END IF;
965 CLOSE c_invoice;
966
967
968 hr_utility.set_location(' Leaving:'||l_proc, 10);
969 END;
970 --
971 -- ----------------------------------------------------------------------------
972 -- |--------------------------------< CHECK_WF_STATUS>-------------------------|
973 -- ----------------------------------------------------------------------------
974 -- {Start Of Comments}
975 --
976 -- Description:
977 -- This function will be a used to check the workflow status of Order Line.
978 --
979 -- IN
980 -- p_line_id
981 --
982 -- OUT
983 -- p_exist
984 --
985 -- Post Failure:
986 -- None.
987 --
988 -- Access Status:
989 -- Public.
990 --
991 -- {End Of Comments}
992 ----------------------------------------------------------------------------
993
994 FUNCTION Check_wf_Status (
995 p_Line_id NUMBER,
996 p_activity varchar2)
997
998 return boolean
999
1000 IS
1001
1002 l_proc varchar2(72) := g_package||'Check_wf_Status' ;
1003 l_exist varchar2(1);
1004 l_return boolean :=False;
1005
1006 CURSOR line_wf IS
1007 SELECT null
1008 FROM wf_item_activity_statuses_v wf
1009 WHERE activity_name = p_activity
1010 AND activity_status_code = 'NOTIFIED'
1011 AND item_type = 'OEOL'
1012 AND item_key = to_char(p_line_id);
1013
1014 BEGIN
1015 hr_utility.set_location('Entering:'||l_proc, 5);
1016 OPEN line_wf;
1017 fetch line_wf into l_exist;
1018 if line_wf%found then
1019 l_return := True;
1020 end if;
1021 CLOSE line_wf;
1022 Return(l_return);
1023
1024 hr_utility.set_location('Leaving:'||l_proc, 10);
1025 END check_wf_status;
1026
1027
1028 -- ----------------------------------------------------------------------------
1029 -- |-------------------------< other_bookings_clash >-------------------------|
1030 -- ----------------------------------------------------------------------------
1031 --
1032 -- PUBLIC
1033 -- Description: Other Bookings Clash
1034 --
1035 -- Checks if the booking being made clashes with any other
1036 -- bookings for the delegate
1037 -- Note - bookings only clash if they are confirmed
1038 --
1039 Procedure other_bookings_clash (p_delegate_person_id in varchar2,
1040 p_delegate_contact_id in varchar2,
1041 p_event_id in number,
1042 p_booking_status_type_id in varchar2,
1043 p_return_status out nocopy boolean,
1044 p_warn out nocopy boolean)
1045 is
1046 --
1047 --
1048 -- cursor to select any confirmed bookings for events which
1049 -- clash with the event being booked
1050 --
1051 cursor c_other_person_bookings is
1052 select bst.type
1053 from ota_delegate_bookings db,
1054 ota_booking_status_types bst,
1055 ota_events ev,
1056 ota_events evt
1057 where db.delegate_person_id = p_delegate_person_id
1058 and db.booking_status_type_id = bst.booking_status_type_id
1059 and bst.type <> g_cancelled_booking
1060 and db.event_id = ev.event_id
1061 and evt.event_id = p_event_id
1062 and ev.event_id <> p_event_id
1063 and ((
1064 ev.course_start_date = ev.course_end_date and
1065 evt.course_start_date = evt.course_end_date and
1066 ev.course_start_date = evt.course_start_date and
1067 nvl(evt.course_start_time, '-99:99') <= nvl(ev.course_end_time, '99:99') and
1068 nvl(evt.course_end_time, '99:99') >= nvl(ev.course_start_time, '-99:99')
1069 )
1070 or (
1071 (ev.course_start_date <> ev.course_end_date or
1072 evt.course_start_date <> evt.course_end_date) and
1073 ev.course_start_date <= evt.course_end_date and
1074 ev.course_end_date >= evt.course_start_date
1075 ))
1076 order by bst.type;
1077 --
1078 cursor c_other_contact_bookings is
1079 select bst.type
1080 from ota_delegate_bookings db,
1081 ota_booking_status_types bst,
1082 ota_events ev,
1083 ota_events evt
1084 where db.delegate_contact_id = p_delegate_contact_id
1085 and db.booking_status_type_id = bst.booking_status_type_id
1086 and bst.type <> g_cancelled_booking
1087 and db.event_id = ev.event_id
1088 and evt.event_id = p_event_id
1089 and ev.event_id <> p_event_id
1090 and ev.course_start_date <= evt.course_end_date
1091 and ev.course_end_date >= evt.course_start_date
1092 order by bst.type;
1093 --
1094 l_proc varchar2(72) := g_package||'other_bookings_clash';
1095 l_result boolean;
1096 l_warn boolean := false;
1097 l_booking_status varchar2(80);
1098 l_dummy varchar2(80);
1099 --
1100 Begin
1101 hr_utility.set_location('Entering:'||l_proc, 5);
1102 --
1103 p_warn := False;
1104 p_return_status := False;
1105 if p_delegate_person_id is not null then
1106 --
1107 open c_other_person_bookings;
1108 fetch c_other_person_bookings into l_dummy;
1109 l_result := c_other_person_bookings%found;
1110 close c_other_person_bookings;
1111 --
1112 elsif p_delegate_contact_id is not null then
1113 --
1114 open c_other_contact_bookings;
1115 fetch c_other_contact_bookings into l_dummy;
1116 l_result := c_other_contact_bookings%found;
1117 close c_other_contact_bookings;
1118 --
1119 end if;
1120 --
1121
1122 if not l_result then
1123 --
1124 l_booking_status := ota_tdb_bus.booking_status_type(p_booking_status_type_id);
1125
1126 if l_booking_status in (g_attended_booking, g_placed_booking) and
1127 l_dummy in (g_attended_booking, g_placed_booking) then
1128 --
1129 p_return_status := True;
1130 --
1131 else
1132 --
1133 if l_booking_status <> g_cancelled_booking then
1134 --
1135 p_warn := true;
1136 --
1137 end if;
1138 --
1139 end if;
1140 --
1141 end if;
1142
1143 if p_delegate_contact_id is null and p_delegate_person_id is null then
1144 p_warn := true;
1145 end if;
1146 --
1147
1148 --
1149 hr_utility.set_location(' Leaving:'||l_proc, 10);
1150 End other_bookings_clash;
1151 -- --------------------------------------------------------------------------
1152 -- |--------------------------<GET_BG_NAME>-------------------------------|--
1153 -- -----------------------------------------------------------------------
1154 -- {Start of Comments}
1155 -- Description:
1156 -- This function will be used to get the Business Group Name for the Organization ID
1157 -- that is passed into it.
1158 -- IN
1159 -- p_organization_id
1160 --
1161 -- OUT
1162 -- p_return
1163 -- Post Failure
1164 -- None
1165 --
1166 -- Access Status:
1167 -- PUBLIC
1168 -- {End of Comments}
1169 -----------------------------------------------------------------------------
1170 FUNCTION get_bg_name(p_organization_id NUMBER) RETURN VARCHAR2
1171 IS
1172 l_proc VARCHAR2(72) := g_package||'Get_Bg_Name';
1173 l_return hr_all_organization_units.name%TYPE;
1174
1175 CURSOR bg_cr IS
1176 SELECT bg.name
1177 FROM hr_all_organization_units org,
1178 hr_all_organization_units bg
1179 WHERE org.business_group_id = bg.organization_id
1180 AND org.organization_id = p_organization_id;
1181
1182 BEGIN
1183 hr_utility.set_location('Entering :'||l_proc,5);
1184 OPEN bg_cr;
1185 FETCH bg_cr INTO l_return;
1186 CLOSE bg_cr;
1187 RETURN l_return;
1188 hr_utility.set_location('Leaving:'||l_proc,10);
1189 EXCEPTION
1190 WHEN others THEN
1191 l_return := NULL;
1192 RETURN l_return;
1193 END get_bg_name;
1194
1195 -- --------------------------------------------------------------------------
1196 -- |--------------------------<get_commitment_detail>-------------------------------|--
1197 -- -----------------------------------------------------------------------
1198 -- {Start of Comments}
1199 -- Description:
1200 -- This procedure will call the OM APi to return the commitment details when a line_id
1201 -- is passed into it.
1202 -- IN Reqd Type
1203 -- p_line_id NUMBER
1204 --
1205 -- OUT
1206 -- p_commitment_id NUMBER
1207 -- p_commitment_number VARCHAR2
1208 -- p_commitment_start_date DATE
1209 -- p_commitment_end_date DATE
1210 --
1211 -- Post Failure
1212 -- None
1213 --
1214 -- Access Status:
1215 -- PUBLIC
1216 -- {End of Comments}
1217 -----------------------------------------------------------------------------
1218 PROCEDURE get_commitment_detail
1219 (p_line_id IN NUMBER,
1220 p_commitment_id OUT NOCOPY NUMBER,
1221 p_commitment_number OUT NOCOPY VARCHAR2,
1222 p_commitment_start_date OUT NOCOPY DATE,
1223 p_commitment_end_date OUT NOCOPY DATE)
1224 IS
1225 --
1226 -- Declare cursors and local variables.
1227 --
1228 l_proc VARCHAR2(72) := g_package||'get_commitment_detail';
1229 l_check_om_installed VARCHAR2(1);
1230
1231 l_execute_proc VARCHAR2(4000);
1232
1233 BEGIN
1234 hr_utility.set_location('Entering :'||l_proc,5);
1235 l_check_om_installed := check_product_installed(660);
1236 IF l_check_om_installed = 'Y' THEN
1237 l_execute_proc := '
1238 BEGIN
1239 oe_commitment_util.get_commitment_info(:p_line_id,
1240 :x_commitment_id,
1241 :x_commitment_number,
1242 :x_commitment_start_date,
1243 :x_commitment_end_date);
1244 END;';
1245 EXECUTE IMMEDIATE l_execute_proc
1246 USING IN p_line_id,
1247 OUT p_commitment_id,
1248 OUT p_commitment_number,
1249 OUT p_commitment_start_date,
1250 OUT p_commitment_end_date;
1251 END IF;
1252 hr_utility.set_location('Leaving:'||l_proc,80);
1253 EXCEPTION
1254 WHEN others THEN
1255 hr_utility.set_location('Leaving:'||l_proc,90);
1256 END get_commitment_detail;
1257 --
1258
1259 -- --------------------------------------------------------------------------
1260 -- |--------------------------<check_product_installed>-------------------------------|--
1261 -- -----------------------------------------------------------------------
1262 -- {Start of Comments}
1263 -- Description:
1264 -- This function will return a VARCHAR indicating if the particular product code
1265 -- passed in is installed or not.
1266 -- IN Reqd Type
1267 -- p_application_id NUMBER
1268 --
1269 -- OUT
1270 -- l_return VARCHAR2
1271 --
1272 -- Post Failure
1273 -- None
1274 --
1275 -- Access Status:
1276 -- PUBLIC
1277 -- {End of Comments}
1278 -----------------------------------------------------------------------------
1279 FUNCTION check_product_installed
1280 (p_application_id IN NUMBER) RETURN VARCHAR2
1281 IS
1282 --
1283 -- Declare cursors and local variables.
1284 --
1285 l_proc VARCHAR2(72) := g_package||'check_product_installed';
1286 l_status VARCHAR2(1);
1287 l_industry VARCHAR2(1);
1288 l_return_val VARCHAR2(1) := 'N';
1289
1290 BEGIN
1291 hr_utility.set_location('Entering :'||l_proc,5);
1292 IF (fnd_installation.get ( p_application_id,
1293 p_application_id,
1294 l_status,
1295 l_industry)) THEN
1296 IF l_status IN ('I', 'S') THEN
1297 l_return_val := 'Y';
1298 ELSE l_return_val := 'N';
1299 END IF;
1300 ELSE
1301 l_return_val := 'N';
1302 END IF;
1303 RETURN l_return_val;
1304 hr_utility.set_location('Leaving:'||l_proc,80);
1305 EXCEPTION
1306 WHEN others THEN
1307 l_return_val := 'N';
1308 RETURN l_return_val;
1309 hr_utility.set_location('Leaving:'||l_proc,90);
1310 END check_product_installed;
1311
1312
1313 -- ----------------------------------------------------------------
1314 -- ------------------<get_delivery_method >--------------------
1315 -- ----------------------------------------------------------------
1316 -- {Start of Comments}
1317 --
1318 -- Description:
1319 -- This function will be used to find the delivery method name/category name of the particular
1320 -- activity
1321 -- IN
1322 -- p_activity_id
1323 -- p_return_value
1324 --
1325 -- Post Failure:
1326 -- None.
1327 -- Access Status
1328 -- Public
1329 -- {End of Comments}
1330 ------------------------------------------------------------------
1331 FUNCTION get_Delivery_Method(p_activity_version_id IN NUMBER,
1332 p_return_value IN VARCHAR2)
1333 RETURN VARCHAR2 IS
1334
1335 CURSOR get_primary_dm_cr IS
1336 SELECT lookup.meaning Dm_Name,
1337 catusg.category Dm_Code
1338 FROM ota_act_cat_inclusions actcat,
1339 ota_category_usages catusg,
1340 hr_lookups lookup
1341 WHERE actcat.category_usage_id= catusg.category_usage_id
1342 AND actcat.primary_flag='Y'
1343 AND catusg.category = lookup.lookup_code
1344 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1345 AND catusg.type='DM'
1346 AND actcat.activity_version_id = p_activity_version_id;
1347
1348 CURSOR get_first_dm_cr IS
1349 SELECT lookup.meaning dm_name,
1350 catusg.category dm_code
1351 FROM ota_act_cat_inclusions actcat,
1352 ota_category_usages catusg,
1353 hr_lookups lookup
1354 WHERE actcat.category_usage_id= catusg.category_usage_id
1355 AND catusg.category = lookup.lookup_code
1356 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1357 AND catusg.type='DM'
1358 AND actcat.activity_version_id = p_activity_version_id;
1359
1360 -- Added for Bug No.2941052
1361 -- Get Delivery Method name corresponding to code 'INCLASS'
1362 CURSOR get_dm_name IS
1363 SELECT meaning
1364 FROM hr_lookups
1365 WHERE lookup_type ='ACTIVITY_CATEGORY'
1366 AND lookup_code = 'INCLASS';
1367
1368 l_proc VARCHAR2(72) := g_package||'Get_Delivery_Method';
1369
1370 l_pr_dm_cat hr_lookups.lookup_code%TYPE;
1371 l_pr_dm_name hr_lookups.meaning%TYPE;
1372
1373
1374 l_return_dm_cat hr_lookups.lookup_code%TYPE := 'INCLASS';
1375 l_return_dm_name hr_lookups.meaning%TYPE;
1376 l_return VARCHAR2(100);
1377 l_counter NUMBER := 0;
1378
1379 BEGIN
1380
1381 hr_utility.set_location('Entering :'||l_proc,5);
1382
1383 FOR primary_dm_rec IN get_primary_dm_cr
1384 LOOP
1385 l_pr_dm_name := primary_dm_rec.dm_name;
1386 l_pr_dm_cat := primary_dm_rec.dm_code;
1387 END LOOP;
1388 IF l_pr_dm_name IS NULL THEN
1389 FOR first_dm_rec IN get_first_dm_cr
1390 LOOP
1391 l_counter := l_counter + 1;
1392 IF l_counter = 1 THEN
1393 l_return_dm_name := first_dm_rec.dm_name;
1394 l_return_dm_cat := first_dm_rec.dm_code;
1395 END IF;
1396 END LOOP;
1397 ELSE
1398 IF p_return_value = 'ICON'
1399 THEN l_return_dm_cat := l_pr_dm_cat;
1400 ELSIF p_return_value = 'NAME'
1401 THEN l_return_dm_name := l_pr_dm_name;
1402 END IF;
1403 END IF;
1404
1405 -- Added for Bug No.2941052
1406 -- Fetch default Delivery Method Name.
1407 IF l_return_dm_name IS NULL THEN
1408 OPEN get_dm_name;
1409 FETCH get_dm_name INTO l_return_dm_name;
1410 CLOSE get_dm_name;
1411 END IF;
1412
1413 IF p_return_value = 'ICON'
1414 THEN l_return := l_return_dm_cat;
1415 ELSIF p_return_value = 'NAME'
1416 THEN l_return := l_return_dm_name;
1417 END IF;
1418
1419 RETURN l_return;
1420
1421 EXCEPTION
1422 WHEN others THEN
1423 hr_utility.set_location('Leaving :'||l_proc,15);
1424 RETURN l_return;
1425 END get_delivery_method;
1426
1427 Function get_delivery_method (p_offering_id in number)
1428 return varchar2
1429 is
1430
1431
1432 Cursor get_DM is
1433 select ocu.category
1434 from ota_category_usages_tl ocu , ota_offerings oaf
1435 where oaf.delivery_mode_id = ocu.category_usage_id
1436 and oaf.offering_id = p_offering_id
1437 and ocu.Language = USERENV('LANG');
1438
1439 l_delivery_method varchar2(240);
1440
1441
1442 begin
1443
1444 OPEN get_DM;
1445 FETCH get_DM INTO l_delivery_method;
1446 close get_DM;
1447 return(l_delivery_method);
1448
1449
1450 end get_delivery_method;
1451 -- ----------------------------------------------------------------
1452 -- ------------------<students_on_waitlist >--------------------
1453 -- ----------------------------------------------------------------
1454 -- {Start of Comments}
1455 --
1456 -- Description:
1457 -- This function will be used to find the number of students waitlisted
1458 -- in a particular event
1459 -- IN
1460 -- p_event_id
1461 --
1462 --
1463 -- Post Failure:
1464 -- None.
1465 -- Access Status
1466 -- Public
1467 -- {End of Comments}
1468 ------------------------------------------------------------------
1469 FUNCTION students_on_waitlist(p_event_id IN NUMBER)
1470 RETURN NUMBER
1471 IS
1472 l_proc VARCHAR2(72) := g_package||'Students_On_Waitlist';
1473 l_num_waitlisted NUMBER DEFAULT 0;
1474 BEGIN
1475 hr_utility.set_location('Entering :'||l_proc,5);
1476 SELECT COUNT(booking_id)
1477 INTO l_num_waitlisted
1478 FROM ota_delegate_bookings tdb
1479 WHERE tdb.event_id = p_event_id
1480 AND tdb.booking_status_type_id IN (SELECT bst.booking_status_type_id
1481 FROM ota_booking_status_types bst
1482 WHERE bst.type = 'W');
1483 hr_utility.set_location('Leaving :'||l_proc,10);
1484 RETURN l_num_waitlisted;
1485
1486 EXCEPTION
1487
1488 WHEN NO_DATA_FOUND THEN
1489 hr_utility.set_location('Leaving:'||l_proc,15);
1490 RETURN l_num_waitlisted;
1491
1492 END students_on_waitlist;
1493
1494 -- ----------------------------------------------------------------
1495 -- ------------------<Place_on_waitlist >--------------------
1496 -- ----------------------------------------------------------------
1497 -- {Start of Comments}
1498 --
1499 -- Description:
1500 -- This function will be used to check the place on waitlist for a particular enrollment
1501 -- in the particular event.
1502 -- IN
1503 -- p_event_id
1504 -- p_booking_id
1505 --
1506 -- Post Failure:
1507 -- None.
1508 -- Access Status
1509 -- Public
1510 -- {End of Comments}
1511 ------------------------------------------------------------------
1512 FUNCTION place_on_waitlist(p_event_id IN NUMBER,
1513 p_booking_id IN NUMBER)
1514 RETURN NUMBER
1515 IS
1516 l_proc VARCHAR2(72) := g_package||'Place_On_Waitlist';
1517 l_count number := 1;
1518
1519 CURSOR c_date_waitlist is
1520 SELECT tdb.booking_id
1521 FROM ota_delegate_bookings tdb,
1522 ota_booking_status_types bst
1523 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1524 AND bst.type = 'W'
1525 AND tdb.event_id = p_event_id
1526 ORDER BY tdb.date_booking_placed;
1527
1528 CURSOR c_priority_waitlist is
1529 SELECT tdb.booking_id
1530 FROM ota_delegate_bookings tdb,
1531 ota_booking_status_types bst
1532 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1533 AND bst.type = 'W'
1534 AND tdb.event_id = p_event_id
1535 ORDER BY tdb.booking_priority,
1536 tdb.booking_id;
1537
1538
1539 BEGIN
1540 hr_utility.set_location('Entering :'||l_proc,5);
1541
1542 IF fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') = 'BP' THEN
1543 --
1544 FOR l_waitlist_entry IN c_priority_waitlist
1545 LOOP
1546 --
1547
1548 IF p_booking_id = l_waitlist_entry.booking_id THEN
1549 RETURN l_count;
1550 ELSE l_count := l_count+1;
1551 END IF;
1552 END LOOP;
1553 --
1554 ELSE
1555 --
1556 FOR l_waitlist_entry IN c_date_waitlist LOOP
1557 --
1558 IF p_booking_id = l_waitlist_entry.booking_id THEN
1559 RETURN l_count;
1560 ELSE l_count := l_count + 1;
1561 END IF;
1562 --
1563 END LOOP;
1564 --
1565 END IF;
1566 hr_utility.set_location('Leaving :'||l_proc,10);
1567 EXCEPTION
1568 WHEN others THEN
1569 hr_utility.set_location('Leaving :'||l_proc,15);
1570 RETURN l_count;
1571 END place_on_waitlist;
1572
1573 -- ----------------------------------------------------------------
1574 -- ------------------<get_event_location >--------------------
1575 -- ----------------------------------------------------------------
1576 -- {Start of Comments}
1577 --
1578 -- Description:
1579 -- This function will be used to return the location id for the event.
1580 -- IN
1581 -- p_event_id
1582 --
1583 --
1584 -- Post Failure:
1585 -- None.
1586 -- Access Status
1587 -- Public
1588 -- {End of Comments}
1589 ------------------------------------------------------------------
1590 FUNCTION get_event_location(p_event_id IN NUMBER)
1591 RETURN NUMBER
1592 IS
1593
1594 CURSOR primary_venue_cr
1595 IS
1596 SELECT s.location_id
1597 FROM ota_suppliable_resources s,
1598 ota_resource_bookings r
1599 WHERE r.supplied_resource_id = s.supplied_resource_id
1600 AND r.event_id = p_event_id
1601 AND primary_venue_flag = 'Y';
1602
1603 CURSOR evt_loc_cr
1604 IS
1605 SELECT e.location_id,
1606 e.training_center_id
1607 FROM ota_events e
1608 WHERE e.event_id = p_event_id;
1609
1610 l_training_center_id ota_events.training_center_id%TYPE;
1611 l_location_id hr_locations_all.location_id%TYPE;
1612
1613 CURSOR tc_loc_cr(p_training_center_id ota_events.training_center_id%TYPE)
1614 IS
1615 SELECT o.location_id
1616 FROM hr_all_organization_units o
1617 WHERE o.organization_id = p_training_center_id;
1618
1619 l_proc VARCHAR2(72) := g_package||'Get_Event_Location';
1620 BEGIN
1621 hr_utility.set_location('Entering :'||l_proc,5);
1622 FOR location1 IN primary_venue_cr
1623 LOOP
1624 l_location_id := location1.location_id;
1625
1626 END LOOP;
1627
1628 IF l_location_id IS NULL THEN
1629 FOR location2 IN evt_loc_cr
1630 LOOP
1631 l_location_id := location2.location_id;
1632 l_training_center_id := location2.training_center_id;
1633 END LOOP;
1634
1635 END IF;
1636
1637 IF l_location_id IS NULL THEN
1638 FOR location3 IN tc_loc_cr(l_training_center_id)
1639 LOOP
1640 l_location_id := location3.location_id;
1641 END LOOP;
1642
1643 END IF;
1644 hr_utility.set_location('Leaving :'||l_proc,10);
1645 RETURN l_location_id;
1646
1647 EXCEPTION
1648 WHEN others THEN
1649 hr_utility.set_location('Leaving :'||l_proc,15);
1650 RETURN l_location_id;
1651 END get_event_location;
1652 -- ----------------------------------------------------------------
1653 -- ------------------<get_play_button >--------------------
1654 -- ----------------------------------------------------------------
1655 -- {Start of Comments}
1656 --
1657 -- Description:
1658 -- This function will be used to return a varchar to indicate if
1659 -- Play button will be displayed or not.
1660 -- IN
1661 -- p_person_id
1662 -- p_offering_id
1663 -- p_enrollment_status
1664 -- p_course_start_date
1665 -- p_course_end_date
1666 --
1667 -- Post Failure:
1668 -- None.
1669 -- Access Status
1670 -- Public
1671 -- {End of Comments}
1672 ------------------------------------------------------------------
1673 FUNCTION get_play_button(p_person_id IN NUMBER,
1674 p_offering_id IN NUMBER,
1675 p_enrollment_status IN VARCHAR2,
1676 p_course_start_date IN DATE,
1677 p_course_end_date IN DATE)
1678 RETURN VARCHAR2
1679 IS
1680 CURSOR get_emp_id_cr
1681 IS
1682 SELECT employee_id
1683 FROM fnd_user
1684 WHERE user_id = fnd_profile.value('USER_ID');
1685
1686 l_proc VARCHAR2(72) := g_package||'Get_Play_Button';
1687 l_fnd_user NUMBER;
1688 l_play VARCHAR2(10) := 'N';
1689
1690 BEGIN
1691 hr_utility.set_location('Entering :'||l_proc,5);
1692 FOR get_emp IN get_emp_id_cr
1693 LOOP
1694 l_fnd_user := get_emp.employee_id;
1695 END LOOP;
1696
1697 IF p_person_id = l_fnd_user
1698 AND p_offering_id IS NOT NULL
1699 AND p_enrollment_status = 'P'
1700 AND sysdate >= p_course_start_date
1701 AND p_course_end_date >= sysdate
1702 THEN l_play := 'P';
1703 ELSIF p_person_id = l_fnd_user
1704 AND p_offering_id IS NOT NULL
1705 AND p_enrollment_status ='A'
1706 AND sysdate >= p_course_start_date
1707 AND p_course_end_date >= sysdate
1708 THEN l_play := 'R';
1709 ELSE l_play := 'N';
1710 END IF;
1711 hr_utility.set_location('Leaving :'||l_proc,10);
1712 RETURN l_play;
1713
1714 EXCEPTION
1715 WHEN others THEN
1716 hr_utility.set_location('Leaving :'||l_proc,15);
1717 RETURN l_play;
1718 END get_play_button;
1719 -- ----------------------------------------------------------------
1720 -- --------------------< get_authorizer_name >---------------------
1721 -- ----------------------------------------------------------------
1722 -- {Start of Comments}
1723 --
1724 -- Description:
1725 -- This function will be used to find the name of the person who
1726 -- authorized enrollment in an event.
1727 -- IN
1728 -- p_authorizer_id
1729 -- p_course_start_date
1730 -- p_course_end_date
1731 --
1732 -- Post Failure:
1733 -- None.
1734 -- Access Status
1735 -- Public
1736 -- {End of Comments}
1737 ------------------------------------------------------------------
1738 FUNCTION get_authorizer_name(p_authorizer_id IN NUMBER,
1739 p_course_start_date IN DATE,
1740 p_course_end_date IN DATE)
1741 RETURN VARCHAR2
1742 IS
1743 --
1744 CURSOR get_name_csr (p_authorizer_id IN NUMBER, p_course_start_date IN DATE, p_course_end_date IN DATE)
1745 IS
1746
1747 /* Modified for Bug#3552493
1748 SELECT DECODE(per.last_name, NULL, NULL, per.last_name)||
1749 DECODE(per.title, NULL, DECODE(per.first_name, NULL, NULL, ', '), ', '||per.title)||
1750 --Modified for Bug#2997820
1751 --DECODE(per.first_name,NULL,NULL, per.last_name) full_name
1752 DECODE(per.first_name,NULL,NULL, per.first_name) full_name
1753 FROM per_all_people_f per, fnd_user u
1754 WHERE per.person_id = u.employee_id
1755 AND (per.effective_end_date >= DECODE(p_course_end_date, NULL, TRUNC(SYSDATE), p_course_end_date) AND
1756 per.effective_start_date <= DECODE(p_course_start_date, NULL, TRUNC(SYSDATE), p_course_start_date))
1757 AND u.user_id = p_authorizer_id;
1758 */
1759 SELECT decode(fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME'),'FN',per.full_name,
1760 per.first_name||' '|| per.last_name||' '||per.suffix) FULL_NAME
1761 FROM per_all_people_f per, fnd_user u
1762 WHERE per.person_id = u.employee_id
1763 AND u.user_id = p_authorizer_id
1764 AND trunc(SYSDATE) between per.effective_start_date and per.effective_end_date;
1765
1766 --
1767 l_full_name per_all_people_f.full_name%TYPE DEFAULT NULL;
1768 l_authorizer_not_found EXCEPTION;
1769 l_proc VARCHAR2(72) := g_package||'Get_Authorizer_Name';
1770 --
1771 BEGIN
1772 --
1773 hr_utility.set_location('Entering :'||l_proc,5);
1774 --
1775 OPEN get_name_csr (p_authorizer_id, p_course_start_date, p_course_end_date);
1776 FETCH get_name_csr INTO l_full_name;
1777 IF get_name_csr%NOTFOUND THEN
1778 RAISE l_authorizer_not_found;
1779 END IF;
1780 CLOSE get_name_csr;
1781 --
1782 --
1783 RETURN l_full_name;
1784 --
1785 EXCEPTION
1786 --
1787 WHEN l_authorizer_not_found THEN
1788 --
1789 hr_utility.set_location('Leaving :'||l_proc,25);
1790 l_full_name := NULL;
1791 RETURN l_full_name;
1792 --
1793 WHEN others THEN
1794 --
1795 hr_utility.set_location('Leaving :'||l_proc,15);
1796 RETURN l_full_name;
1797
1798 END get_authorizer_name;
1799
1800 -- ----------------------------------------------------------------
1801 -- --------------------< get_message >---------------------
1802 -- ----------------------------------------------------------------
1803 -- {Start of Comments}
1804 --
1805 -- Description:
1806 -- This function will be used to find the message text for the
1807 -- message code passed in.
1808 -- IN
1809 -- p_application_code
1810 -- p_message_code
1811 --
1812 -- Post Failure:
1813 -- None.
1814 -- Access Status
1815 -- Public
1816 -- {End of Comments}
1817 ------------------------------------------------------------------
1818 FUNCTION get_message(p_application_code IN VARCHAR2,
1819 p_message_code IN VARCHAR2)
1820 RETURN VARCHAR2
1821 IS
1822 --
1823 l_msg_not_found EXCEPTION;
1824 l_proc VARCHAR2(72) := g_package||'Get_Message';
1825 l_msg_text VARCHAR2(2000);
1826 --
1827 BEGIN
1828 --
1829 hr_utility.set_location('Entering :'||l_proc,5);
1830 --
1831 fnd_message.set_name(p_application_code, p_message_code);
1832 l_msg_text := fnd_message.get();
1833 --
1834 IF l_msg_text IS NULL THEN
1835 RAISE l_msg_not_found;
1836 END IF;
1837 --
1838 RETURN l_msg_text ;
1839 --
1840 EXCEPTION
1841 --
1842 WHEN l_msg_not_found THEN
1843 --
1844 hr_utility.set_location('Leaving :'||l_proc,25);
1845 RETURN l_msg_text;
1846 --
1847 WHEN others THEN
1848 --
1849 hr_utility.set_location('Leaving :'||l_proc,15);
1850 RETURN l_msg_text;
1851
1852 END get_message;
1853 -- ----------------------------------------------------------------
1854 -- --------------------< get_date_time >---------------------
1855 -- ----------------------------------------------------------------
1856 -- {Start of Comments}
1857 --
1858 -- Description:
1859 -- This function will be used to return date and time.
1860 -- IN
1861 -- p_date
1862 -- p_time
1863 -- p_time_of_day
1864 -- OUT
1865 -- p_date_time
1866 --
1867 -- Post Failure:
1868 -- None.
1869 -- Access Status
1870 -- Public
1871 -- {End of Comments}
1872 ------------------------------------------------------------------
1873 FUNCTION get_date_time(p_date IN DATE,
1874 p_time IN VARCHAR2,
1875 p_time_of_day IN VARCHAR2)
1876 RETURN DATE
1877 IS
1878 --
1879 l_proc VARCHAR2(72) := g_package||'Get_Date_Time';
1880 l_date_time DATE;
1881 --
1882 BEGIN
1883 --
1884 hr_utility.set_location('Entering :'||l_proc,5);
1885 --
1886 IF p_time IS NULL THEN
1887 IF p_time_of_day = 'END'
1888 THEN
1889 l_date_time := to_date(to_char(p_date,'DD-MON-YYYY')||'23:59','DD/MM/YYYYHH24:MI');
1890 ELSIF p_time_of_day = 'START'
1891 THEN l_date_time := to_date(to_char(p_date,'DD-MON-YYYY')||'00:00','DD/MM/YYYYHH24:MI');
1892 END IF;
1893 ELSE
1894 l_date_time := to_date(to_char(p_date,'DD-MON-YYYY')||p_time,'DD/MM/YYYYHH24:MI');
1895 END IF;
1896 --
1897 RETURN l_date_time ;
1898 --
1899 EXCEPTION
1900 --
1901 WHEN others THEN
1902 --
1903 hr_utility.set_location('Leaving :'||l_proc,15);
1904 RETURN l_date_time;
1905 END get_date_time;
1906
1907
1908
1909 -- ----------------------------------------------------------------
1910 -- ------------------<get_category_name >--------------------
1911 -- ----------------------------------------------------------------
1912 -- {Start of Comments}
1913 --
1914 -- Description:
1915 -- This function will be used to find the category name of the particular
1916 -- activity
1917 -- IN
1918 -- p_activity_id
1919 --
1920 -- OUT
1921 -- category name
1922 -- Post Failure:
1923 -- None.
1924 -- Access Status
1925 -- Public
1926 -- {End of Comments}
1927 ------------------------------------------------------------------
1928
1929
1930
1931 FUNCTION get_category_name(p_activity_version_id IN NUMBER)
1932 RETURN VARCHAR2 IS
1933
1934 CURSOR get_primary_dm_cr IS
1935 SELECT lookup.meaning Dm_Name,
1936 catusg.category Dm_Code
1937 FROM ota_act_cat_inclusions actcat,
1938 ota_category_usages catusg,
1939 hr_lookups lookup
1940 WHERE actcat.category_usage_id= catusg.category_usage_id
1941 AND actcat.primary_flag='Y'
1942 AND catusg.category = lookup.lookup_code
1943 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1944 AND catusg.type='C'
1945 AND actcat.activity_version_id = p_activity_version_id;
1946
1947 CURSOR get_first_dm_cr IS
1948 SELECT lookup.meaning dm_name,
1949 catusg.category dm_code
1950 FROM ota_act_cat_inclusions actcat,
1951 ota_category_usages catusg,
1952 hr_lookups lookup
1953 WHERE actcat.category_usage_id= catusg.category_usage_id
1954 AND catusg.category = lookup.lookup_code
1955 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1956 AND catusg.type='C'
1957 AND actcat.activity_version_id = p_activity_version_id;
1958
1959 l_proc VARCHAR2(72) := g_package||'Get_category_name';
1960
1961 l_pr_dm_name hr_lookups.meaning%TYPE;
1962
1963 l_return_dm_name hr_lookups.meaning%TYPE := 'Classroom (physical)';
1964 l_return VARCHAR2(100);
1965 l_counter NUMBER := 0;
1966
1967 BEGIN
1968
1969 hr_utility.set_location('Entering :'||l_proc,5);
1970
1971 FOR primary_dm_rec IN get_primary_dm_cr
1972 LOOP
1973 -- bug#2652899
1974 l_pr_dm_name := primary_dm_rec.dm_name;
1975 -- bug # 2652899
1976 l_return_dm_name := primary_dm_rec.dm_name;
1977 END LOOP;
1978 IF l_pr_dm_name IS NULL THEN
1979 FOR first_dm_rec IN get_first_dm_cr
1980 LOOP
1981 l_counter := l_counter + 1;
1982 IF l_counter = 1 THEN
1983 l_return_dm_name := first_dm_rec.dm_name;
1984 END IF;
1985 END LOOP;
1986 END IF;
1987
1988 RETURN l_return_dm_name;
1989
1990 EXCEPTION
1991 WHEN others THEN
1992 hr_utility.set_location('Leaving :'||l_proc,15);
1993 RETURN l_return;
1994 END get_category_name;
1995
1996 -- ----------------------------------------------------------------
1997 -- ------------------<get_lo_offering_count >--------------------
1998 -- ----------------------------------------------------------------
1999 -- {Start of Comments}
2000 --
2001 -- Description:
2002 -- This function will be used to find the number of offerings for the particular
2003 -- learning object
2004 -- IN
2005 -- p_learning_object_id
2006 --
2007 -- OUT
2008 -- offering count
2009 -- Post Failure:
2010 -- None.
2011 -- Access Status
2012 -- Public
2013 -- {End of Comments}
2014 ------------------------------------------------------------------
2015 function get_lo_offering_count (p_learning_object_id in number) return varchar2
2016 IS
2017 l_offering_count number;
2018
2019 CURSOR c_get_offering_count IS
2020 SELECT count(*)
2021 FROM ota_offerings
2022 WHERE learning_object_id = p_learning_object_id;
2023
2024 BEGIN
2025 open c_get_offering_count;
2026 fetch c_get_offering_count into l_offering_count;
2027 close c_get_offering_count;
2028
2029 return(l_offering_count);
2030
2031 end get_lo_offering_count ;
2032
2033
2034 -- ----------------------------------------------------------------
2035 -- ------------------<get_course_offering_count >--------------------
2036 -- ----------------------------------------------------------------
2037 -- {Start of Comments}
2038 --
2039 -- Description:
2040 -- This function will be used to find the number of offerings for the particular
2041 -- course
2042 -- IN
2043 -- p_activity_version_id
2044 --
2045 -- OUT
2046 -- offering count
2047 -- Post Failure:
2048 -- None.
2049 -- Access Status
2050 -- Public
2051 -- {End of Comments}
2052 ------------------------------------------------------------------
2053 function get_course_offering_count (p_activity_version_id in number) return varchar2
2054 IS
2055 l_offering_count number;
2056
2057 CURSOR c_get_course_offering_count IS
2058 SELECT count(*)
2059 FROM ota_offerings
2060 WHERE activity_version_id = p_activity_version_id;
2061
2062 BEGIN
2063 open c_get_course_offering_count;
2064 fetch c_get_course_offering_count into l_offering_count;
2065 close c_get_course_offering_count;
2066
2067 return(l_offering_count);
2068
2069 end get_course_offering_count ;
2070
2071
2072 -- ----------------------------------------------------------------
2073 -- ------------------<get_iln_rco_id >--------------------
2074 -- ----------------------------------------------------------------
2075 -- {Start of Comments}
2076 --
2077 -- Description:
2078 -- This function will be used to find rco_id for course
2079 -- IN
2080 -- p_activity_version_id
2081 --
2082 -- OUT
2083 -- l_rco_id
2084 -- Post Failure:
2085 -- None.
2086 -- Access Status
2087 -- Public
2088 -- {End of Comments}
2089 ------------------------------------------------------------------
2090 function get_iln_rco_id (p_activity_version_id in number
2091 ) return varchar2
2092 IS
2093 l_rco_id number;
2094
2095 CURSOR get_iln_rco_id IS
2096 SELECT rco_id
2097 FROM ota_activity_versions
2098 WHERE activity_version_id = p_activity_version_id;
2099
2100
2101 BEGIN
2102 open get_iln_rco_id;
2103 fetch get_iln_rco_id into l_rco_id;
2104 close get_iln_rco_id;
2105
2106 return(l_rco_id);
2107
2108 end get_iln_rco_id ;
2109
2110
2111
2112 -- ----------------------------------------------------------------
2113 -- ------------------<get_event_count >--------------------
2114 -- ----------------------------------------------------------------
2115 -- {Start of Comments}
2116 --
2117 -- Description:
2118 -- This function will be used to find the number of of events the particular
2119 -- offering
2120 -- IN
2121 -- p_offering_id
2122 -- p_event_type
2123 --
2124 -- OUT
2125 -- event count
2126 -- Post Failure:
2127 -- None.
2128 -- Access Status
2129 -- Public
2130 -- {End of Comments}
2131 ------------------------------------------------------------------
2132 function get_event_count (p_offering_id in number,
2133 p_event_type in varchar2 default 'ALL') return varchar2
2134 IS
2135 l_event_count number;
2136
2137 CURSOR c_get_iln_event_count IS
2138 SELECT count(event_id)
2139 FROM ota_events
2140 WHERE parent_offering_id = p_offering_id and
2141 offering_id is not null;
2142
2143 CURSOR c_get_event_count IS
2144 SELECT count(event_id)
2145 FROM ota_events
2146 WHERE parent_offering_id = p_offering_id and
2147 event_type in ('SELFPACED','SCHEDULED') and
2148 book_independent_flag = 'N';
2149
2150 BEGIN
2151 IF p_event_type = 'ILN' THEN
2152 open c_get_iln_event_count;
2153 fetch c_get_iln_event_count into l_event_count;
2154 close c_get_iln_event_count;
2155 ELSE
2156 open c_get_event_count;
2157 fetch c_get_event_count into l_event_count;
2158 close c_get_event_count;
2159 END IF;
2160
2161 return(l_event_count);
2162
2163 end get_event_count ;
2164
2165
2166
2167 -- ----------------------------------------------------------------
2168 -- ------------------<get_question_bank_count >--------------------
2169 -- ----------------------------------------------------------------
2170 -- {Start of Comments}
2171 --
2172 -- Description:
2173 -- This function will be used to find the number of question banks related to particular
2174 -- folder
2175 -- IN
2176 -- p_folder_id
2177 --
2178 -- OUT
2179 -- question bank count
2180 -- Post Failure:
2181 -- None.
2182 -- Access Status
2183 -- Public
2184 -- {End of Comments}
2185 ------------------------------------------------------------------
2186 function get_question_bank_count (p_folder_id in number) return varchar2
2187 IS
2188 l_question_bank_count number;
2189
2190 CURSOR c_get_question_bank_count IS
2191 SELECT count(*)
2192 FROM ota_question_banks
2193 WHERE folder_id = p_folder_id;
2194
2195 BEGIN
2196 open c_get_question_bank_count;
2197 fetch c_get_question_bank_count into l_question_bank_count;
2198 close c_get_question_bank_count;
2199
2200 return(l_question_bank_count);
2201
2202 end get_question_bank_count ;
2203
2204 -- Author: sbhullar
2205 FUNCTION get_enrollment_status(p_delegate_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
2206 p_delegate_contact_id IN NUMBER,
2207 p_event_id IN ota_events.event_id%TYPE,
2208 p_code IN number)
2209 RETURN VARCHAR2 IS
2210
2211 CURSOR enroll_status IS
2212 SELECT DECODE(BST.type,'C','Y',BST.type) status, BST.name
2213 FROM ota_booking_status_types_vl BST,
2214 ota_delegate_bookings ODB
2215 WHERE ODB.event_id = p_event_id
2216 AND (p_delegate_person_id IS NOT NULL AND ODB.delegate_person_id = p_delegate_person_id
2217 OR p_delegate_contact_id IS NOT NULL and ODB.delegate_contact_id = p_delegate_contact_id)
2218 AND ODB.booking_status_type_id = BST.booking_status_type_id
2219 ORDER BY status;
2220
2221
2222 l_proc VARCHAR2(72) := g_package|| 'get_enrollment_status';
2223
2224 l_enrollment_status VARCHAR2(30) := 'Z'; --Default is Not Enrolled(Status Z)
2225 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
2226
2227 BEGIN
2228 hr_utility.set_location(' Step:'|| l_proc, 10);
2229
2230 --Default is Not Enrolled
2231 l_enrollment_status_name := get_message('OTA','OTA_443407_NOT_ENROLLED');
2232
2233 FOR rec IN enroll_status
2234 LOOP
2235 l_enrollment_status := rec.status ;
2236 l_enrollment_status_name := rec.name;
2237 EXIT;
2238 END LOOP;
2239
2240 If (p_code = 1) Then
2241 RETURN l_enrollment_status;
2242 Else
2243 RETURN l_enrollment_status_name;
2244 End If;
2245
2246 hr_utility.set_location(' Step:'|| l_proc, 20);
2247
2248 END get_enrollment_status;
2249
2250
2251 FUNCTION get_user_fullname(p_user_id IN ota_attempts.user_id%TYPE,
2252 p_user_type IN ota_attempts.user_type%TYPE)
2253
2254 RETURN VARCHAR2 IS
2255
2256 CURSOR c_person_fullname IS
2257 select
2258 p.full_name person_name
2259 from per_people_f p
2260 where
2261 p.person_id = p_user_id
2262 and sysdate between p.effective_start_date and p.effective_end_date;
2263
2264 CURSOR c_customer_fullname IS
2265 select
2266 p.party_name person_name
2267 from hz_parties p
2268 where
2269 p.party_id = p_user_id;
2270
2271 l_proc VARCHAR2(72) := g_package|| 'get_user_fullname';
2272 l_return per_all_people_f.full_name%TYPE;
2273 BEGIN
2274 hr_utility.set_location(' Step:'|| l_proc, 10);
2275 IF p_user_type = 'E' THEN
2276 OPEN c_person_fullname;
2277 FETCH c_person_fullname INTO l_return;
2278 CLOSE c_person_fullname;
2279 ELSIF p_user_type = 'C' THEN
2280 OPEN c_customer_fullname;
2281 FETCH c_customer_fullname INTO l_return;
2282 CLOSE c_customer_fullname;
2283 END IF;
2284
2285
2286 RETURN l_return;
2287
2288 hr_utility.set_location(' Step:'|| l_proc, 20);
2289
2290 END get_user_fullname;
2291
2292 FUNCTION get_person_fullname(p_user_id IN ota_attempts.user_id%TYPE
2293 )RETURN VARCHAR2 IS
2294
2295 CURSOR c_person_fullname IS
2296 select
2297 p.full_name person_name
2298 from per_people_f p , fnd_user fus
2299 where p.person_id = fus.employee_id
2300 and
2301 fus.user_id = p_user_id;
2302
2303
2304 l_proc VARCHAR2(72) := g_package|| 'get_person_fullname';
2305 l_return per_all_people_f.full_name%TYPE;
2306 BEGIN
2307 hr_utility.set_location(' Step:'|| l_proc, 10);
2308
2309 OPEN c_person_fullname;
2310 FETCH c_person_fullname INTO l_return;
2311 CLOSE c_person_fullname;
2312
2313
2314 RETURN l_return;
2315
2316 hr_utility.set_location(' Step:'|| l_proc, 20);
2317
2318 END get_person_fullname;
2319
2320 FUNCTION get_learner_name(p_person_id IN per_all_people_f.person_id%TYPE,
2321 p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2322 p_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2323 RETURN VARCHAR2 IS
2324
2325 CURSOR c_person_name IS
2326 select pap.full_name from per_all_people_f pap
2327 where pap.person_id = p_person_id
2328 and trunc(sysdate) between nvl(pap.effective_start_date, trunc(sysdate))
2329 and nvl(pap.effective_end_date, trunc(sysdate));
2330
2331 CURSOR c_contact_name(l_customer_id IN ota_delegate_bookings.customer_id%TYPE) IS
2332 SELECT
2333 SUBSTRB( PARTY.PERSON_LAST_NAME,1,50) || ' ' ||
2334 SUBSTRB( PARTY.PERSON_FIRST_NAME,1,40) || ' ' ||
2335 HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE',nvl(PARTY.PERSON_PRE_NAME_ADJUNCT,PARTY.PERSON_TITLE)) LEARNER_NAME
2336 FROM
2337 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
2338 HZ_PARTIES PARTY,
2339 HZ_RELATIONSHIPS REL,
2340 HZ_CUST_ACCOUNTS ROLE_ACCT
2341 WHERE
2342 ACCT_ROLE.PARTY_ID = REL.PARTY_ID
2343 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
2344 AND REL.SUBJECT_ID = PARTY.PARTY_ID
2345 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2346 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2347 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
2348 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
2349 AND ACCT_ROLE.CUST_ACCOUNT_ID = l_customer_id
2350 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id;
2351
2352 l_proc VARCHAR2(72) := g_package|| 'get_learner_name';
2353 l_return VARCHAR2(500);
2354 BEGIN
2355 hr_utility.set_location(' Step:'|| l_proc, 10);
2356
2357 IF (p_person_id is not null) THEN
2358 -- Internal Enrollment, Get Learner Name from per_all_people_f
2359 OPEN c_person_name;
2360 FETCH c_person_name INTO l_return;
2361 CLOSE c_person_name;
2362 ELSE
2363 -- External Enrollment. Get Learner Name from
2364 -- HZ Tables if contact_id is not null
2365 IF (p_contact_id is not null) THEN
2366 IF (p_customer_id is not null) THEN
2367 OPEN c_contact_name(p_customer_id);
2368 ELSE
2369 OPEN c_contact_name(get_customer_id(p_contact_id));
2370 END IF;
2371 FETCH c_contact_name INTO l_return;
2372 CLOSE c_contact_name;
2373 ELSE
2374 l_return := NULL;
2375 END IF;
2376 END IF;
2377
2378 return l_return;
2379
2380 hr_utility.set_location(' Step:'|| l_proc, 20);
2381
2382 EXCEPTION
2383 WHEN others THEN
2384 hr_utility.set_location('Leaving :'||l_proc,15);
2385 RETURN NULL;
2386 END get_learner_name;
2387
2388 FUNCTION get_customer_id(p_contact_id IN ota_lp_enrollments.contact_id%TYPE)
2389 RETURN number IS
2390
2391 CURSOR c_customer_id IS
2392 select ACCT_ROLE.cust_account_id CUSTOMER_ID
2393 from HZ_CUST_ACCOUNT_ROLES acct_role,
2394 HZ_PARTIES party,
2395 HZ_RELATIONSHIPS rel,
2396 HZ_ORG_CONTACTS org_cont,
2397 HZ_PARTIES rel_party,
2398 HZ_CUST_ACCOUNTS role_acct
2399 where acct_role.party_id = rel.party_id
2400 and acct_role.role_type = 'CONTACT'
2401 and org_cont.party_relationship_id = rel.relationship_id
2402 and rel.subject_id = party.party_id
2403 and rel.party_id = rel_party.party_id
2404 and rel.subject_table_name = 'HZ_PARTIES'
2405 and rel.object_table_name = 'HZ_PARTIES'
2406 and acct_role.cust_account_id = role_acct.cust_account_id
2407 and role_acct.party_id = rel.object_id
2408 and ACCT_ROLE.cust_account_role_id = p_contact_id;
2409
2410 l_proc VARCHAR2(72) := g_package|| 'get_customer_id';
2411 l_return ota_delegate_bookings.customer_id%TYPE;
2412
2413 Begin
2414 hr_utility.set_location(' Step:'|| l_proc, 10);
2415
2416 OPEN c_customer_id;
2417 FETCH c_customer_id INTO l_return;
2418 CLOSE c_customer_id;
2419
2420 return l_return;
2421 hr_utility.set_location(' Step:'|| l_proc, 20);
2422 End get_customer_id;
2423
2424 FUNCTION get_cust_org_name(p_organization_id IN ota_delegate_bookings.organization_id%TYPE,
2425 p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2426 p_contact_id IN ota_lp_enrollments.contact_id%TYPE default null)
2427 RETURN VARCHAR2 IS
2428
2429 CURSOR c_organization_name IS
2430 select name from hr_all_organization_units_tl
2431 where language = userenv('LANG') and organization_id = p_organization_id;
2432
2433 CURSOR c_customer_name(l_customer_id IN ota_delegate_bookings.customer_id%TYPE) IS
2434 select substrb(party.party_name,1,50)
2435 from hz_parties party
2436 ,hz_cust_accounts cust_acct
2437 where
2438 cust_acct.party_id = party.party_id
2439 and cust_acct.cust_account_id = l_customer_id;
2440
2441 l_proc VARCHAR2(72) := g_package|| 'get_cust_org_name';
2442 l_return VARCHAR2(500);
2443 BEGIN
2444 hr_utility.set_location(' Step:'|| l_proc, 10);
2445
2446 IF (p_organization_id is not null) THEN
2447 -- Internal Enrollment, Get org_name from hr_all_organization_units
2448 OPEN c_organization_name;
2449 FETCH c_organization_name INTO l_return;
2450 CLOSE c_organization_name;
2451 ELSIF (p_customer_id is not null) THEN
2452 -- External Enrollment. Get Customer Name from HZ Tables
2453 -- p_customer_id is already known
2454 OPEN c_customer_name(p_customer_id);
2455 FETCH c_customer_name INTO l_return;
2456 CLOSE c_customer_name;
2457 ELSE
2458 -- External Enrollment. Get Customer Name from HZ Tables
2459 -- Get p_customer_id from p_contact_id
2460 OPEN c_customer_name(get_customer_id(p_contact_id));
2461 FETCH c_customer_name INTO l_return;
2462 CLOSE c_customer_name;
2463 END IF;
2464
2465 return l_return;
2466
2467 hr_utility.set_location(' Step:'|| l_proc, 20);
2468
2469 EXCEPTION
2470 WHEN others THEN
2471 hr_utility.set_location('Leaving :'||l_proc,15);
2472 RETURN NULL;
2473 END get_cust_org_name;
2474
2475
2476 -- ----------------------------------------------------------------
2477 -- ------------------<get_catalog_object_path >--------------------
2478 -- ----------------------------------------------------------------
2479 Procedure get_catalog_object_path (p_cat_id varchar2, p_path OUT NOCOPY varchar2)
2480 IS
2481 parent_id ota_category_usages.parent_cat_usage_id%TYPE;
2482 full_path varchar2(1000) :=null;
2483 current_cat_usage_id ota_category_usages.category_usage_id%TYPE := p_cat_id;
2484 l_proc VARCHAR2(72) := g_package||'get_catalog_object_path';
2485
2486 Cursor c_parent_cat_id(current_cat_usage_id IN VARCHAR2)
2487 IS
2488 select nvl(parent_cat_usage_id,-1)
2489 from ota_category_usages
2490 where category_usage_id = current_cat_usage_id;
2491
2492 Begin
2493 loop
2494 OPEN c_parent_cat_id(current_cat_usage_id);
2495 FETCH c_parent_cat_id INTO parent_id;
2496 CLOSE c_parent_cat_id;
2497
2498 full_path := 'CAT' || parent_id || '.' ||full_path;
2499 current_cat_usage_id := parent_id;
2500
2501 exit when parent_id = -1;
2502 end loop;
2503
2504 p_path := full_path;
2505
2506 EXCEPTION
2507 WHEN others THEN
2508 hr_utility.set_location('Leaving :'||l_proc,15);
2509 p_path := null;
2510 End get_catalog_object_path ;
2511
2512
2513 -- ----------------------------------------------------------------
2514 -- ------------------<get_content_object_path >--------------------
2515 -- ----------------------------------------------------------------
2516 Procedure get_content_object_path (p_obj_id varchar2,p_obj_type varchar2, p_path OUT NOCOPY varchar2)
2517 IS
2518 parent_obj_id ota_lo_folders.folder_id%TYPE;
2519 full_path varchar2(1000) :=null;
2520 current_obj_id ota_lo_folders.folder_id%TYPE := p_obj_id;
2521 l_proc VARCHAR2(72) := g_package||'get_content_object_path';
2522
2523 Cursor c_parent_lo_id(current_lo_id IN VARCHAR2)
2524 IS
2525 select nvl(parent_learning_object_id,-1)
2526 from ota_learning_objects
2527 where learning_object_id = current_lo_id;
2528
2529 Cursor c_lo_folder_id(current_lo_id IN VARCHAR2)
2530 IS
2531 select folder_id
2532 from ota_learning_objects
2533 where learning_object_id = current_lo_id;
2534
2535 Cursor c_parent_folder_id(current_folder_id IN VARCHAR2)
2536 IS
2537 select nvl(parent_folder_id,-1)
2538 from ota_lo_folders
2539 where folder_id = current_folder_id;
2540
2541 Begin
2542 if (p_obj_type = 'L') then
2543 loop
2544 OPEN c_parent_lo_id(current_obj_id);
2545 FETCH c_parent_lo_id INTO parent_obj_id;
2546 CLOSE c_parent_lo_id;
2547
2548 if (parent_obj_id <> -1) then
2549 full_path := 'L' || parent_obj_id || '.' ||full_path;
2550 current_obj_id := parent_obj_id;
2551 end if;
2552
2553 if (parent_obj_id = -1) then
2554 OPEN c_lo_folder_id(current_obj_id);
2555 FETCH c_lo_folder_id INTO current_obj_id;
2556 CLOSE c_lo_folder_id;
2557 full_path := 'F' || current_obj_id || '.' ||full_path;
2558 end if;
2559
2560 exit when parent_obj_id = -1;
2561 end loop;
2562
2563 end if;
2564
2565
2566 loop
2567 OPEN c_parent_folder_id(current_obj_id);
2568 FETCH c_parent_folder_id INTO parent_obj_id;
2569 CLOSE c_parent_folder_id;
2570
2571 full_path := 'F' || parent_obj_id || '.' ||full_path;
2572 current_obj_id := parent_obj_id;
2573
2574 exit when parent_obj_id = -1;
2575 end loop;
2576
2577 p_path := full_path;
2578
2579 EXCEPTION
2580 WHEN others THEN
2581 hr_utility.set_location('Leaving :'||l_proc,15);
2582 p_path := null;
2583 End get_content_object_path ;
2584
2585 -- ----------------------------------------------------------------
2586 -- ------------------<check_function_access >--------------------
2587 -- ----------------------------------------------------------------
2588 -- {Start of Comments}
2589 --
2590 -- Description:
2591 -- This function will be used to find out if the user logged in has access to the function
2592 -- IN
2593 -- p_function_name
2594 --
2595 -- OUT
2596 -- T for True
2597 -- F for False
2598 -- Post Failure:
2599 -- None.
2600 -- Access Status
2601 -- Public
2602 -- {End of Comments}
2603 ------------------------------------------------------------------
2604 function check_function_access (p_function_name in VARCHAR2)
2605 RETURN varchar2
2606 IS
2607 l_return VARCHAR2(1):= 'F';
2608 l_proc VARCHAR2(72) := g_package||'check_function_access';
2609
2610 BEGIN
2611 hr_utility.set_location('Entering :'||l_proc,5);
2612
2613 IF fnd_function.test_instance(function_name => p_function_name) THEN
2614 l_return := 'T';
2615 ELSE
2616 l_return := 'F';
2617 END IF;
2618 RETURN l_return;
2619
2620 hr_utility.set_location('Leaving :'||l_proc,10);
2621 EXCEPTION
2622 WHEN others THEN
2623 l_return := 'F';
2624 RETURN l_return;
2625
2626 END check_function_access;
2627
2628 -- ----------------------------------------------------------------
2629 -- ------------------< get_event_status_code >---------------------
2630 -- ----------------------------------------------------------------
2631 -- {Start of Comments}
2632 --
2633 -- Description:
2634 -- This function will be used to find the event status code for
2635 -- an event
2636 -- IN
2637 -- p_event_id
2638 --
2639 -- OUT
2640 -- returns event status code
2641 -- Post Failure:
2642 -- None.
2643 -- Access Status
2644 -- Public
2645 -- {End of Comments}
2646 ------------------------------------------------------------------
2647 Function get_event_status_code (p_event_id in ota_events.event_id%TYPE)
2648 return varchar2
2649 IS
2650 l_event_status_code ota_events.event_status%TYPE;
2651
2652 CURSOR c_get_event_status_code
2653 IS
2654 SELECT event_status from ota_events
2655 where
2656 event_id = p_event_id;
2657
2658 Begin
2659 OPEN c_get_event_status_code;
2660 FETCH c_get_event_status_code INTO l_event_status_code;
2661 CLOSE c_get_event_status_code;
2662 RETURN(l_event_status_code);
2663
2664 End get_event_status_code ;
2665
2666 -- ----------------------------------------------------------------
2667 -- ----------------------< is_applicant >--------------------------
2668 -- ----------------------------------------------------------------
2669 -- {Start of Comments}
2670 --
2671 -- Description:
2672 -- This function will be used to find whether person is
2673 -- applicant or not
2674 -- IN
2675 -- p_person_id
2676 --
2677 -- OUT
2678 -- returns Y or N
2679 -- Post Failure:
2680 -- None.
2681 -- Access Status
2682 -- Public
2683 -- {End of Comments}
2684 ------------------------------------------------------------------
2685 Function is_applicant (p_person_id IN per_all_people_f.person_id%TYPE)
2686 return varchar2 is
2687
2688 cursor get_person_type is
2689 SELECT ppt.system_person_type
2690 FROM per_all_people_f per,
2691 per_person_type_usages_f ptu,
2692 per_person_types ppt
2693 WHERE
2694 per.person_id = p_person_id
2695 AND ptu.person_id = per.person_id
2696 AND ppt.business_group_id = per.business_group_id
2697 AND ptu.person_type_id = ppt.person_type_id
2698 AND trunc(sysdate) between per.effective_start_date AND per.effective_end_date
2699 AND trunc(sysdate) between ptu.effective_start_date AND ptu.effective_end_date
2700 AND ppt.system_person_type <> 'APL'
2701 AND ppt.system_person_type in ('EMP','CWK');
2702
2703 l_system_person_type per_person_types.system_person_type%TYPE;
2704
2705 Begin
2706 Open get_person_type;
2707 Fetch get_person_type into l_system_person_type;
2708 /*
2709 Close get_person_type;
2710
2711 if ( l_system_person_type = 'APL' ) then
2712 return 'Y';
2713 else
2714 return 'N';
2715 */
2716 IF get_person_type%FOUND THEN
2717 CLOSE get_person_type;
2718 return 'N';
2719 ELSE
2720 CLOSE get_person_type;
2721 RETURN 'Y';
2722 end if;
2723 End is_applicant;
2724
2725 -- ----------------------------------------------------------------
2726 -- -------------------< get_ext_lrnr_party_id >--------------------
2727 -- ----------------------------------------------------------------
2728 -- {Start of Comments}
2729 --
2730 -- Description:
2731 -- This function will be used to fetch the party id for external learner
2732 -- IN
2733 -- p_delegate_contact_id
2734 --
2735 -- OUT
2736 -- returns party id
2737 -- Post Failure:
2738 -- None.
2739 -- Access Status
2740 -- Public
2741 -- {End of Comments}
2742 ------------------------------------------------------------------
2743 FUNCTION get_ext_lrnr_party_id
2744 (p_delegate_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2745 RETURN number IS
2746
2747 Cursor get_ext_lrn_party_id is
2748 select party.party_id
2749 from HZ_CUST_ACCOUNT_ROLES acct_role,
2750 HZ_PARTIES party,
2751 HZ_RELATIONSHIPS rel,
2752 HZ_ORG_CONTACTS org_cont,
2753 HZ_PARTIES rel_party,
2754 HZ_CUST_ACCOUNTS role_acct
2755 where acct_role.party_id = rel.party_id
2756 and acct_role.role_type = 'CONTACT'
2757 and org_cont.party_relationship_id = rel.relationship_id
2758 and rel.subject_id = party.party_id
2759 and rel.party_id = rel_party.party_id
2760 and rel.subject_table_name = 'HZ_PARTIES'
2761 and rel.object_table_name = 'HZ_PARTIES'
2762 and acct_role.cust_account_id = role_acct.cust_account_id
2763 and role_acct.party_id = rel.object_id
2764 and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
2765
2766 l_proc VARCHAR2(72) := g_package|| 'get_ext_lrnr_party_id';
2767 l_return number;
2768
2769 Begin
2770 hr_utility.set_location(' Step:'|| l_proc, 10);
2771
2772 OPEN get_ext_lrn_party_id;
2773 FETCH get_ext_lrn_party_id INTO l_return;
2774 CLOSE get_ext_lrn_party_id;
2775
2776 return l_return;
2777 hr_utility.set_location(' Step:'|| l_proc, 20);
2778 End get_ext_lrnr_party_id;
2779
2780 FUNCTION is_class_enrollable(
2781 p_class_id ota_events.event_id%TYPE)
2782 RETURN VARCHAR2
2783 IS
2784 --6762989 Added nvl check on enrolment_start_date to avoid java.sql.SQLException: ORA-01843: not a valid month on some dbs.
2785 CURSOR csr_get_class_details IS
2786 SELECT null
2787 FROM ota_events
2788 WHERE event_id = p_class_id
2789 --AND trunc(sysdate) between enrolment_start_date and nvl(enrolment_end_date, trunc(sysdate))
2790 AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, timezone)
2791 BETWEEN to_date(to_char(nvl(enrolment_start_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
2792 AND to_date(to_char(nvl(enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
2793 AND event_type IN ('SCHEDULED', 'SELFPACED')
2794 AND event_status IN ('N', 'P', 'F');
2795
2796 l_exists VARCHAR2(1);
2797 BEGIN
2798 OPEN csr_get_class_details;
2799 FETCH csr_get_class_details INTO l_exists;
2800 IF csr_get_class_details%NOTFOUND THEN
2801 CLOSE csr_get_class_details;
2802 RETURN 'N';
2803 ELSE
2804 CLOSE csr_get_class_details;
2805 RETURN 'Y';
2806 END IF;
2807 END is_class_enrollable;
2808
2809 FUNCTION is_lp_enrollable(
2810 p_learning_path_id ota_learning_paths.learning_path_id%TYPE)
2811 RETURN VARCHAR2
2812 IS
2813 CURSOR csr_get_lp_details IS
2814 SELECT null
2815 FROM ota_learning_paths
2816 WHERE learning_path_id = p_learning_path_id
2817 AND trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
2818
2819 CURSOR csr_sections_exist IS
2820 SELECT null
2821 from ota_lp_sections lpc,
2822 ota_learning_path_members lpm
2823 where lpc.learning_path_id = p_learning_path_id
2824 and lpc.learning_path_section_id = lpm.learning_path_section_id
2825 and lpc.completion_type_code in ('M','S');
2826
2827 l_exists VARCHAR2(1);
2828 BEGIN
2829
2830 OPEN csr_get_lp_details;
2831 FETCH csr_get_lp_details INTO l_exists;
2832 IF csr_get_lp_details%NOTFOUND THEN
2833 CLOSE csr_get_lp_details;
2834 RETURN 'N';
2835 ELSE
2836 CLOSE csr_get_lp_details;
2837
2838 OPEN csr_sections_exist;
2839 FETCH csr_sections_exist INTO l_exists;
2840 IF csr_sections_exist%NOTFOUND THEN
2841 CLOSE csr_sections_exist;
2842 RETURN 'N';
2843 ELSE
2844 CLOSE csr_sections_exist;
2845 RETURN 'Y';
2846 END IF;
2847 END IF;
2848 END is_lp_enrollable;
2849
2850 FUNCTION is_cert_enrollable(
2851 p_certification_id ota_certifications_b.certification_id%TYPE)
2852 RETURN VARCHAR2
2853 IS
2854 CURSOR csr_get_cert_details IS
2855 SELECT null
2856 FROM ota_certifications_b crb
2857 , ota_certification_members crm
2858 WHERE crb.certification_id = crm.certification_id
2859 AND crb.certification_id = p_certification_id
2860 AND trunc(sysdate) between crb.start_date_active and nvl(crb.end_date_active, trunc(sysdate))
2861 -- Added for bug#4617609, modified for 4940007
2862 and ((crb.renewable_flag = 'N' and trunc(sysdate) <= nvl( crb.INITIAL_COMPLETION_DATE ,trunc(sysdate)))
2863 or crb.renewable_flag = 'Y');
2864
2865
2866 l_exists VARCHAR2(1);
2867 BEGIN
2868 OPEN csr_get_cert_details;
2869 FETCH csr_get_cert_details INTO l_exists;
2870 IF csr_get_cert_details%NOTFOUND THEN
2871 CLOSE csr_get_cert_details;
2872 RETURN 'N';
2873 ELSE
2874 CLOSE csr_get_cert_details;
2875 RETURN 'Y';
2876 END IF;
2877 END is_cert_enrollable;
2878
2879 FUNCTION is_enrollable
2880 ( p_object_type in varchar2
2881 ,p_object_id in number
2882 )
2883 RETURN varchar2
2884 IS
2885 BEGIN
2886 IF p_object_type = 'CL' THEN
2887 RETURN is_class_enrollable(p_object_id);
2888 ELSIF p_object_type = 'LP' THEN
2889 RETURN is_lp_enrollable(p_object_id);
2890 ELSIF p_object_type = 'CRT' THEN
2891 RETURN is_cert_enrollable(p_object_id);
2892 ELSE
2893 RETURN 'N';
2894 END IF;
2895 END is_enrollable;
2896
2897
2898 PROCEDURE Get_Default_Value_Dff(
2899 appl_short_name IN VARCHAR2,
2900 flex_field_name IN VARCHAR2,
2901 p_attribute_category IN OUT NOCOPY VARCHAR2,
2902 p_attribute1 IN OUT NOCOPY VARCHAR2,
2903 p_attribute2 IN OUT NOCOPY VARCHAR2,
2904 p_attribute3 IN OUT NOCOPY VARCHAR2,
2905 p_attribute4 IN OUT NOCOPY VARCHAR2,
2906 p_attribute5 IN OUT NOCOPY VARCHAR2,
2907 p_attribute6 IN OUT NOCOPY VARCHAR2,
2908 p_attribute7 IN OUT NOCOPY VARCHAR2,
2909 p_attribute8 IN OUT NOCOPY VARCHAR2,
2910 p_attribute9 IN OUT NOCOPY VARCHAR2,
2911 p_attribute10 IN OUT NOCOPY VARCHAR2,
2912 p_attribute11 IN OUT NOCOPY VARCHAR2,
2913 p_attribute12 IN OUT NOCOPY VARCHAR2,
2914 p_attribute13 IN OUT NOCOPY VARCHAR2,
2915 p_attribute14 IN OUT NOCOPY VARCHAR2,
2916 p_attribute15 IN OUT NOCOPY VARCHAR2,
2917 p_attribute16 IN OUT NOCOPY VARCHAR2,
2918 p_attribute17 IN OUT NOCOPY VARCHAR2,
2919 p_attribute18 IN OUT NOCOPY VARCHAR2,
2920 p_attribute19 IN OUT NOCOPY VARCHAR2,
2921 p_attribute20 IN OUT NOCOPY VARCHAR2)
2922 IS
2923 p_flexfield fnd_dflex.dflex_r;
2924 p_flexinfo fnd_dflex.dflex_dr;
2925 p_contexts fnd_dflex.contexts_dr;
2926 p_segments fnd_dflex.segments_dr;
2927 j BINARY_INTEGER;
2928 i BINARY_INTEGER;
2929 k BINARY_INTEGER;
2930
2931
2932 l_appl_col_name varchar2(50);
2933 firstchar number;
2934 tempstr varchar2(2);
2935
2936 PROCEDURE Get_Flexfield
2937 IS
2938 BEGIN
2939 fnd_dflex.get_flexfield( appl_short_name, flex_field_name, p_flexfield, p_flexinfo );
2940 END Get_Flexfield;
2941
2942 PROCEDURE Get_Contexts
2943 IS
2944 BEGIN
2945 fnd_dflex.get_contexts( p_flexfield, p_contexts );
2946 END Get_Contexts;
2947
2948
2949 BEGIN
2950 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Get_Default_Value_Dff');
2951 IF p_attribute_category IS NOT NULL
2952 OR p_attribute1 IS NOT NULL
2953 OR p_attribute2 IS NOT NULL
2954 OR p_attribute3 IS NOT NULL
2955 OR p_attribute4 IS NOT NULL
2956 OR p_attribute5 IS NOT NULL
2957 OR p_attribute6 IS NOT NULL
2958 OR p_attribute7 IS NOT NULL
2959 OR p_attribute8 IS NOT NULL
2960 OR p_attribute9 IS NOT NULL
2961 OR p_attribute10 IS NOT NULL
2962 OR p_attribute11 IS NOT NULL
2963 OR p_attribute12 IS NOT NULL
2964 OR p_attribute13 IS NOT NULL
2965 OR p_attribute14 IS NOT NULL
2966 OR p_attribute15 IS NOT NULL
2967 OR p_attribute16 IS NOT NULL
2968 OR p_attribute17 IS NOT NULL
2969 OR p_attribute18 IS NOT NULL
2970 OR p_attribute19 IS NOT NULL
2971 OR p_attribute20 IS NOT NULL THEN RETURN;
2972
2973 END IF;
2974 FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Get_Flexfield');
2975 FND_FILE.PUT_LINE(FND_FILE.LOG,'appl_short_name'||appl_short_name);
2976 --FND_FILE.PUT_LINE(FND_FILE.LOG,'p_flexfield'||p_flexfield);
2977 FND_FILE.PUT_LINE(FND_FILE.LOG,'flex_field_name'||flex_field_name);
2978 --FND_FILE.PUT_LINE(FND_FILE.LOG,'p_flexinfo'||p_flexinfo);
2979
2980 Get_Flexfield;
2981 --FND_FILE.PUT_LINE(FND_FILE.LOG,' p_flexfield'||p_flexfield);
2982 --FND_FILE.PUT_LINE(FND_FILE.LOG,'p_contexts'||p_contexts);
2983 FND_FILE.PUT_LINE(FND_FILE.LOG,'Before GET_CONTEXTS');
2984 GET_CONTEXTS;
2985 FND_FILE.PUT_LINE(FND_FILE.LOG,'After GET_CONTEXTS');
2986
2987 p_attribute_category := p_flexinfo.default_context_value;
2988
2989 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_attribute_category :'||p_attribute_category);
2990 FOR k in 1 .. p_contexts.ncontexts LOOP
2991
2992 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside FOR k in 1 .. p_contexts.ncontexts LOOP'||k);
2993 IF p_contexts.is_global(k)
2994 OR p_contexts.context_code(k) = p_flexinfo.default_context_value THEN
2995
2996 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside IF p_contexts.is_global(k) OR p_contexts.context_code(k) = p_flexinfo.default_context_value THEN');
2997 fnd_dflex.Get_Segments( fnd_dflex.make_context(p_flexfield,p_contexts.context_code(k))
2998 ,p_segments
2999 ,TRUE);
3000 FND_FILE.PUT_LINE(FND_FILE.LOG,' After fnd_dflex.Get_Segments');
3001
3002 FOR j IN 1 .. p_segments.nsegments LOOP
3003 FND_FILE.PUT_LINE(FND_FILE.LOG,' Inside FOR j IN 1 .. p_segments.nsegments LOOP'||j);
3004
3005 l_appl_col_name := p_segments.application_column_name(j);
3006 tempstr := substr(l_appl_col_name, length(l_appl_col_name) -1);
3007 firstchar := ascii(substr(tempstr,1,2));
3008 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_appl_col_name'||l_appl_col_name);
3009 FND_FILE.PUT_LINE(FND_FILE.LOG,' tempstr'||tempstr);
3010 FND_FILE.PUT_LINE(FND_FILE.LOG,' firstchar'||firstchar);
3011
3012 IF firstchar < 49 OR firstchar > 57 THEN
3013 i := to_number(substr(tempstr,2,1));
3014 ELSE
3015 i := to_number(tempstr);
3016 END IF;
3017
3018 IF i = 1 THEN
3019 p_attribute1 := p_segments.default_value(j);
3020 ELSIF i = 2 THEN
3021 p_attribute2 := p_segments.default_value(j);
3022 ELSIF i = 3 THEN
3023 p_attribute3 := p_segments.default_value(j);
3024 ELSIF i = 4 THEN
3025 p_attribute4 := p_segments.default_value(j);
3026 ELSIF i = 5 THEN
3027 p_attribute5 := p_segments.default_value(j);
3028 ELSIF i = 6 THEN
3029 p_attribute6 := p_segments.default_value(j);
3030 ELSIF i = 7 THEN
3031 p_attribute7 := p_segments.default_value(j);
3032 ELSIF i = 8 THEN
3033 p_attribute8 := p_segments.default_value(j);
3034 ELSIF i = 9 THEN
3035 p_attribute9 := p_segments.default_value(j);
3036 ELSIF i = 10 THEN
3037 p_attribute10 := p_segments.default_value(j);
3038 ELSIF i = 11 THEN
3039 p_attribute11 := p_segments.default_value(j);
3040 ELSIF i = 12 THEN
3041 p_attribute12 := p_segments.default_value(j);
3042 ELSIF i = 13 THEN
3043 p_attribute13 := p_segments.default_value(j);
3044 ELSIF i = 14 THEN
3045 p_attribute14 := p_segments.default_value(j);
3046 ELSIF i = 15 THEN
3047 p_attribute15 := p_segments.default_value(j);
3048 ELSIF i = 16 THEN
3049 p_attribute16 := p_segments.default_value(j);
3050 ELSIF i = 17 THEN
3051 p_attribute17 := p_segments.default_value(j);
3052 ELSIF i = 18 THEN
3053 p_attribute18 := p_segments.default_value(j);
3054 ELSIF i = 19 THEN
3055 p_attribute19 := p_segments.default_value(j);
3056 ELSIF i = 20 THEN
3057 p_attribute20 := p_segments.default_value(j);
3058 END IF;
3059 END LOOP;
3060 END IF;
3061
3062 END LOOP;
3063 /*
3064 dbms_output.put_line('Attribute cate '||p_attribute_category);
3065
3066 dbms_output.put_line('Attribute 1 '||p_attribute1);
3067 dbms_output.put_line('Attribute 2 '||p_attribute2);
3068 dbms_output.put_line('Attribute 3 '||p_attribute3);
3069 dbms_output.put_line('Attribute 4 '||p_attribute4);
3070 dbms_output.put_line('Attribute 5 '||p_attribute5);
3071 dbms_output.put_line('Attribute 6 '||p_attribute6);
3072 dbms_output.put_line('Attribute 7 '||p_attribute7);
3073 dbms_output.put_line('Attribute 8 '||p_attribute8);
3074 dbms_output.put_line('Attribute 9 '||p_attribute9);
3075 dbms_output.put_line('Attribute 10 '||p_attribute10);
3076 dbms_output.put_line('Attribute 11 '||p_attribute11);
3077 dbms_output.put_line('Attribute 12 '||p_attribute12);
3078 dbms_output.put_line('Attribute 13 '||p_attribute13);
3079 dbms_output.put_line('Attribute 14 '||p_attribute14);
3080 dbms_output.put_line('Attribute 15 '||p_attribute15);
3081 dbms_output.put_line('Attribute 16 '||p_attribute16);
3082 dbms_output.put_line('Attribute 17 '||p_attribute17);
3083 dbms_output.put_line('Attribute 18 '||p_attribute18);
3084 dbms_output.put_line('Attribute 19 '||p_attribute19);
3085 dbms_output.put_line('Attribute 20 '||p_attribute20);
3086 */
3087 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exit Get_Default_Value_Dff');
3088 END Get_Default_Value_Dff;
3089
3090 PROCEDURE Get_Default_Value_Dff(
3091 appl_short_name IN VARCHAR2,
3092 flex_field_name IN VARCHAR2,
3093 p_attribute_category IN OUT NOCOPY VARCHAR2,
3094 p_attribute1 IN OUT NOCOPY VARCHAR2,
3095 p_attribute2 IN OUT NOCOPY VARCHAR2,
3096 p_attribute3 IN OUT NOCOPY VARCHAR2,
3097 p_attribute4 IN OUT NOCOPY VARCHAR2,
3098 p_attribute5 IN OUT NOCOPY VARCHAR2,
3099 p_attribute6 IN OUT NOCOPY VARCHAR2,
3100 p_attribute7 IN OUT NOCOPY VARCHAR2,
3101 p_attribute8 IN OUT NOCOPY VARCHAR2,
3102 p_attribute9 IN OUT NOCOPY VARCHAR2,
3103 p_attribute10 IN OUT NOCOPY VARCHAR2,
3104 p_attribute11 IN OUT NOCOPY VARCHAR2,
3105 p_attribute12 IN OUT NOCOPY VARCHAR2,
3106 p_attribute13 IN OUT NOCOPY VARCHAR2,
3107 p_attribute14 IN OUT NOCOPY VARCHAR2,
3108 p_attribute15 IN OUT NOCOPY VARCHAR2,
3109 p_attribute16 IN OUT NOCOPY VARCHAR2,
3110 p_attribute17 IN OUT NOCOPY VARCHAR2,
3111 p_attribute18 IN OUT NOCOPY VARCHAR2,
3112 p_attribute19 IN OUT NOCOPY VARCHAR2,
3113 p_attribute20 IN OUT NOCOPY VARCHAR2,
3114 p_attribute21 IN OUT NOCOPY VARCHAR2,
3115 p_attribute22 IN OUT NOCOPY VARCHAR2,
3116 p_attribute23 IN OUT NOCOPY VARCHAR2,
3117 p_attribute24 IN OUT NOCOPY VARCHAR2,
3118 p_attribute25 IN OUT NOCOPY VARCHAR2,
3119 p_attribute26 IN OUT NOCOPY VARCHAR2,
3120 p_attribute27 IN OUT NOCOPY VARCHAR2,
3121 p_attribute28 IN OUT NOCOPY VARCHAR2,
3122 p_attribute29 IN OUT NOCOPY VARCHAR2,
3123 p_attribute30 IN OUT NOCOPY VARCHAR2)
3124
3125 IS
3126 p_flexfield fnd_dflex.dflex_r;
3127 p_flexinfo fnd_dflex.dflex_dr;
3128 p_contexts fnd_dflex.contexts_dr;
3129 p_segments fnd_dflex.segments_dr;
3130 j BINARY_INTEGER;
3131 i BINARY_INTEGER;
3132 k BINARY_INTEGER;
3133
3134
3135 l_appl_col_name varchar2(50);
3136 firstchar number;
3137 tempstr varchar2(2);
3138
3139 PROCEDURE Get_Flexfield
3140 IS
3141 BEGIN
3142 fnd_dflex.get_flexfield( appl_short_name, flex_field_name, p_flexfield, p_flexinfo );
3143 END Get_Flexfield;
3144
3145 PROCEDURE Get_Contexts
3146 IS
3147 BEGIN
3148 fnd_dflex.get_contexts( p_flexfield, p_contexts );
3149 END Get_Contexts;
3150
3151
3152 BEGIN
3153 IF p_attribute_category IS NOT NULL
3154 OR p_attribute1 IS NOT NULL
3155 OR p_attribute2 IS NOT NULL
3156 OR p_attribute3 IS NOT NULL
3157 OR p_attribute4 IS NOT NULL
3158 OR p_attribute5 IS NOT NULL
3159 OR p_attribute6 IS NOT NULL
3160 OR p_attribute7 IS NOT NULL
3161 OR p_attribute8 IS NOT NULL
3162 OR p_attribute9 IS NOT NULL
3163 OR p_attribute10 IS NOT NULL
3164 OR p_attribute11 IS NOT NULL
3165 OR p_attribute12 IS NOT NULL
3166 OR p_attribute13 IS NOT NULL
3167 OR p_attribute14 IS NOT NULL
3168 OR p_attribute15 IS NOT NULL
3169 OR p_attribute16 IS NOT NULL
3170 OR p_attribute17 IS NOT NULL
3171 OR p_attribute18 IS NOT NULL
3172 OR p_attribute19 IS NOT NULL
3173 OR p_attribute20 IS NOT NULL
3174 OR p_attribute21 IS NOT NULL
3175 OR p_attribute22 IS NOT NULL
3176 OR p_attribute23 IS NOT NULL
3177 OR p_attribute24 IS NOT NULL
3178 OR p_attribute25 IS NOT NULL
3179 OR p_attribute26 IS NOT NULL
3180 OR p_attribute27 IS NOT NULL
3181 OR p_attribute28 IS NOT NULL
3182 OR p_attribute29 IS NOT NULL
3183 OR p_attribute30 IS NOT NULL THEN RETURN;
3184
3185 END IF;
3186
3187
3188 Get_Flexfield;
3189 GET_CONTEXTS;
3190
3191 p_attribute_category := p_flexinfo.default_context_value;
3192
3193 FOR k in 1 .. p_contexts.ncontexts LOOP
3194
3195 IF p_contexts.is_global(k)
3196 OR p_contexts.context_code(k) = p_flexinfo.default_context_value THEN
3197
3198 fnd_dflex.Get_Segments( fnd_dflex.make_context(p_flexfield,p_contexts.context_code(k))
3199 ,p_segments
3200 ,TRUE);
3201
3202 FOR j IN 1 .. p_segments.nsegments LOOP
3203
3204 l_appl_col_name := p_segments.application_column_name(j);
3205 tempstr := substr(l_appl_col_name, length(l_appl_col_name) -1);
3206 firstchar := ascii(substr(tempstr,1,2));
3207
3208 IF firstchar < 49 OR firstchar > 57 THEN
3209 i := to_number(substr(tempstr,2,1));
3210 ELSE
3211 i := to_number(tempstr);
3212 END IF;
3213
3214 IF i = 1 THEN
3215 p_attribute1 := p_segments.default_value(j);
3216 ELSIF i = 2 THEN
3217 p_attribute2 := p_segments.default_value(j);
3218 ELSIF i = 3 THEN
3219 p_attribute3 := p_segments.default_value(j);
3220 ELSIF i = 4 THEN
3221 p_attribute4 := p_segments.default_value(j);
3222 ELSIF i = 5 THEN
3223 p_attribute5 := p_segments.default_value(j);
3224 ELSIF i = 6 THEN
3225 p_attribute6 := p_segments.default_value(j);
3226 ELSIF i = 7 THEN
3227 p_attribute7 := p_segments.default_value(j);
3228 ELSIF i = 8 THEN
3229 p_attribute8 := p_segments.default_value(j);
3230 ELSIF i = 9 THEN
3231 p_attribute9 := p_segments.default_value(j);
3232 ELSIF i = 10 THEN
3233 p_attribute10 := p_segments.default_value(j);
3234 ELSIF i = 11 THEN
3235 p_attribute11 := p_segments.default_value(j);
3236 ELSIF i = 12 THEN
3237 p_attribute12 := p_segments.default_value(j);
3238 ELSIF i = 13 THEN
3239 p_attribute13 := p_segments.default_value(j);
3240 ELSIF i = 14 THEN
3241 p_attribute14 := p_segments.default_value(j);
3242 ELSIF i = 15 THEN
3243 p_attribute15 := p_segments.default_value(j);
3244 ELSIF i = 16 THEN
3245 p_attribute16 := p_segments.default_value(j);
3246 ELSIF i = 17 THEN
3247 p_attribute17 := p_segments.default_value(j);
3248 ELSIF i = 18 THEN
3249 p_attribute18 := p_segments.default_value(j);
3250 ELSIF i = 19 THEN
3251 p_attribute19 := p_segments.default_value(j);
3252 ELSIF i = 20 THEN
3253 p_attribute20 := p_segments.default_value(j);
3254 ELSIF i = 21 THEN
3255 p_attribute21 := p_segments.default_value(j);
3256 ELSIF i = 22 THEN
3257 p_attribute22 := p_segments.default_value(j);
3258 ELSIF i = 23 THEN
3259 p_attribute23 := p_segments.default_value(j);
3260 ELSIF i = 24 THEN
3261 p_attribute24 := p_segments.default_value(j);
3262 ELSIF i = 25 THEN
3263 p_attribute25 := p_segments.default_value(j);
3264 ELSIF i = 26 THEN
3265 p_attribute26 := p_segments.default_value(j);
3266 ELSIF i = 27 THEN
3267 p_attribute27 := p_segments.default_value(j);
3268 ELSIF i = 28 THEN
3269 p_attribute28 := p_segments.default_value(j);
3270 ELSIF i = 29 THEN
3271 p_attribute29 := p_segments.default_value(j);
3272 ELSIF i = 30 THEN
3273 p_attribute30 := p_segments.default_value(j);
3274 END IF;
3275 END LOOP;
3276 END IF;
3277
3278 END LOOP;
3279 /*
3280 dbms_output.put_line('Attribute cate '||p_attribute_category);
3281
3282 dbms_output.put_line('Attribute 1 '||p_attribute1);
3283 dbms_output.put_line('Attribute 2 '||p_attribute2);
3284 dbms_output.put_line('Attribute 3 '||p_attribute3);
3285 dbms_output.put_line('Attribute 4 '||p_attribute4);
3286 dbms_output.put_line('Attribute 5 '||p_attribute5);
3287 dbms_output.put_line('Attribute 6 '||p_attribute6);
3288 dbms_output.put_line('Attribute 7 '||p_attribute7);
3289 dbms_output.put_line('Attribute 8 '||p_attribute8);
3290 dbms_output.put_line('Attribute 9 '||p_attribute9);
3291 dbms_output.put_line('Attribute 10 '||p_attribute10);
3292 dbms_output.put_line('Attribute 11 '||p_attribute11);
3293 dbms_output.put_line('Attribute 12 '||p_attribute12);
3294 dbms_output.put_line('Attribute 13 '||p_attribute13);
3295 dbms_output.put_line('Attribute 14 '||p_attribute14);
3296 dbms_output.put_line('Attribute 15 '||p_attribute15);
3297 dbms_output.put_line('Attribute 16 '||p_attribute16);
3298 dbms_output.put_line('Attribute 17 '||p_attribute17);
3299 dbms_output.put_line('Attribute 18 '||p_attribute18);
3300 dbms_output.put_line('Attribute 19 '||p_attribute19);
3301 dbms_output.put_line('Attribute 20 '||p_attribute20);
3302 dbms_output.put_line('Attribute 21 '||p_attribute21);
3303 dbms_output.put_line('Attribute 22 '||p_attribute22);
3304 dbms_output.put_line('Attribute 23 '||p_attribute23);
3305 dbms_output.put_line('Attribute 24 '||p_attribute24);
3306 dbms_output.put_line('Attribute 25 '||p_attribute25);
3307 dbms_output.put_line('Attribute 26 '||p_attribute26);
3308 dbms_output.put_line('Attribute 27 '||p_attribute27);
3309 dbms_output.put_line('Attribute 28 '||p_attribute28);
3310 dbms_output.put_line('Attribute 29 '||p_attribute29);
3311 dbms_output.put_line('Attribute 30 '||p_attribute30);
3312
3313 */
3314 END Get_Default_Value_Dff;
3315
3316
3317 -- Added for bug#4606760
3318 FUNCTION is_customer_associated(p_event_id IN NUMBER) RETURN VARCHAR2
3319 IS
3320
3321 CURSOR csr_cust_associations IS
3322 SELECT null
3323 FROM ota_event_associations
3324 WHERE event_id = p_event_id
3325 AND customer_id IS NOT NULL;
3326
3327 l_found varchar2(1) ;
3328
3329 BEGIN
3330
3331 OPEN csr_cust_associations;
3332 FETCH csr_cust_associations INTO l_found;
3333 IF csr_cust_associations%FOUND THEN
3334 CLOSE csr_cust_associations;
3335 RETURN 'Y';
3336 ELSE
3337 CLOSE csr_cust_associations;
3338 RETURN 'N';
3339 END IF;
3340
3341 END is_customer_associated;
3342
3343 -- Added for bug#4606760
3344 FUNCTION check_organization_match(
3345 p_person_id IN NUMBER
3346 ,p_sponsor_org_id IN NUMBER) return VARCHAR2
3347 IS
3348
3349 CURSOR csr_person_orgs IS
3350 SELECT NULL
3351 FROM per_all_assignments_f
3352 WHERE person_id = p_person_id
3353 AND trunc(sysdate) between effective_start_date and effective_end_date
3354 AND organization_id = p_sponsor_org_id
3355 AND assignment_type in ('E', 'C', 'A');
3356
3357 l_found VARCHAR2(1);
3358
3359 BEGIN
3360 OPEN csr_person_orgs;
3361 FETCH csr_person_orgs INTO l_found;
3362 IF csr_person_orgs%NOTFOUND THEN
3363 CLOSE csr_person_orgs;
3364 RETURN 'N';
3365 ELSE
3366 CLOSE csr_person_orgs;
3367 RETURN 'Y';
3368 END IF;
3369 END check_organization_match;
3370
3371 FUNCTION getEnrollmentChangeReason(
3372 p_booking_id IN NUMBER) return VARCHAR2 is
3373
3374 CURSOR crs_enr_change_reason_lookup(p_meaning in varchar2)
3375 IS
3376 SELECT l.lookup_code
3377 FROM fnd_lookup_values l
3378 WHERE l.lookup_type = 'ENROLMENT_STATUS_REASON'
3379 AND l.meaning = p_meaning
3380 AND l.enabled_flag = 'Y'
3381 and rownum=1;
3382
3383
3384 cursor crs_enrollment_change_reason
3385 is
3386 SELECT bsh.comments
3387 FROM ota_booking_status_histories bsh
3388 WHERE bsh.booking_id = p_booking_id
3389 AND bsh.start_date =
3390 (SELECT MAX(start_date)
3391 FROM ota_booking_status_histories
3392 WHERE booking_id = p_booking_id);
3393
3394 CURSOR crs_enr_change_reason_lang(p_lookup_code in varchar2) IS
3395 SELECT meaning
3396 FROM hr_lookups
3397 WHERE lookup_type = 'ENROLMENT_STATUS_REASON'
3398 AND enabled_flag = 'Y'
3399 AND lookup_code = p_lookup_code;
3400
3401 l_comments VARCHAR2(2000) := NULL;
3402 l_comments_lang VARCHAR2(2000) := NULL;
3403 l_change_reason VARCHAR2(2000) := NULL;
3404 l_lookup_code VARCHAR2(30);
3405 BEGIN
3406
3407 open crs_enrollment_change_reason;
3408 fetch crs_enrollment_change_reason into l_comments;
3409 close crs_enrollment_change_reason;
3410
3411 if l_comments is not null then
3412 open crs_enr_change_reason_lookup(l_comments);
3413 fetch crs_enr_change_reason_lookup into l_lookup_code;
3414 close crs_enr_change_reason_lookup;
3415 end if;
3416
3417 if l_lookup_code is not null then
3418
3419 OPEN crs_enr_change_reason_lang(l_lookup_code);
3420 FETCH crs_enr_change_reason_lang
3421 INTO l_comments_lang;
3422 CLOSE crs_enr_change_reason_lang;
3423
3424 IF l_comments_lang is not null then
3425 l_change_reason:=l_comments_lang;
3426 end if;
3427
3428 else
3429 l_change_reason:=l_comments;
3430
3431 end if;
3432 RETURN l_change_reason;
3433
3434 EXCEPTION
3435 WHEN others THEN
3436 RETURN l_change_reason;
3437 END getenrollmentchangereason;
3438
3439 function get_lang_name
3440 (
3441 p_language_code in varchar2
3442 ) return varchar2 is
3443 --
3444 cursor csr_lookup is
3445 select name
3446 from ota_natural_languages_v
3447 where language_code = p_language_code;
3448
3449 --
3450 v_name ota_natural_languages_v.name%TYPE := null;
3451 begin
3452 if p_language_code is not null then
3453 --
3454 open csr_lookup;
3455 fetch csr_lookup into v_name;
3456 close csr_lookup;
3457 end if;
3458 --
3459 return v_name;
3460 --
3461 end get_lang_name;
3462
3463 function get_class_available_seats(p_event_id ota_events.event_id%type) return varchar2 is
3464 l_vacancies varchar2(30);
3465 begin
3466 l_vacancies := ota_evt_bus2.get_vacancies(p_event_id);
3467 if(l_vacancies is null) then
3468 fnd_message.set_name('OTA', 'OTA_467151_LP_CLS_UNLIMITED');
3469 l_vacancies := fnd_message.get;
3470 end if;
3471 return l_vacancies ;
3472 end get_class_available_seats;
3473
3474 function get_cls_enroll_image(p_manager_flag in varchar2,
3475 p_person_id in number,
3476 p_contact_id in number,
3477 p_event_id in ota_events.event_id%TYPE,
3478 p_mandatory_flag in ota_delegate_bookings.is_mandatory_enrollment%TYPE,
3479 p_booking_status_type in ota_booking_status_types.type%TYPE) return varchar2 is
3480
3481 CURSOR lp_cls_enroll_p IS
3482 SELECT lpe.lp_enrollment_id
3483 FROM ota_lp_enrollments lpe,
3484 ota_lp_member_enrollments lpme
3485 WHERE lpe.lp_enrollment_id = lpme.lp_enrollment_id AND
3486 lpe.enrollment_source_code = 'ADMIN' AND
3487 lpe.person_id = p_person_id AND
3488 lpme.event_id = p_event_id;
3489
3490 CURSOR lp_cls_enroll_c IS
3491 SELECT lpe.lp_enrollment_id
3492 FROM ota_lp_enrollments lpe,
3493 ota_lp_member_enrollments lpme
3494 WHERE lpe.lp_enrollment_id = lpme.lp_enrollment_id AND
3495 lpe.enrollment_source_code = 'ADMIN' AND
3496 lpe.contact_id = p_contact_id AND
3497 lpme.event_id = p_event_id;
3498
3499 CURSOR cls_enroll_image IS
3500 SELECT decode(p_manager_flag,'NOT_MANAGER',decode(nvl(p_mandatory_flag,'N'),'Y','UD','N',decode(p_booking_status_type, 'A','UD', 'UE')),
3501 'IS_MANAGER',decode(p_booking_status_type, 'A','UD', 'UE')) enroll_image
3502 FROM DUAL;
3503
3504 l_enroll_image varchar2(30);
3505 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%type;
3506
3507 begin
3508
3509 OPEN cls_enroll_image;
3510 FETCH cls_enroll_image into l_enroll_image;
3511 CLOSE cls_enroll_image;
3512
3513 if( l_enroll_image = 'UE') then
3514 if(p_person_id is not null) then
3515 OPEN lp_cls_enroll_p;
3516 FETCH lp_cls_enroll_p into l_lp_enrollment_id;
3517 if(lp_cls_enroll_p%FOUND) then
3518 l_enroll_image := 'UD';
3519 end if;
3520 CLOSE lp_cls_enroll_p;
3521 else
3522 OPEN lp_cls_enroll_c;
3523 FETCH lp_cls_enroll_c into l_lp_enrollment_id;
3524 if(lp_cls_enroll_c%FOUND) then
3525 l_enroll_image := 'UD';
3526 end if;
3527 CLOSE lp_cls_enroll_c;
3528 end if;
3529 end if;
3530 return l_enroll_image;
3531 end get_cls_enroll_image;
3532
3533
3534 function get_learners_email_addresses(p_event_id ota_events.event_id%type) return clob is
3535 cursor get_internal_email_addresses is
3536 select paf.email_address
3537 from ota_delegate_bookings odb,
3538 ota_booking_status_types obst,
3539 per_all_people_f paf
3540 where odb.event_id = p_event_id and
3541 odb.booking_status_type_id = obst.booking_status_type_id and
3542 obst.type in ('A', 'P', 'E') and
3543 odb.delegate_person_id = paf.person_id and
3544 trunc(sysdate) between paf.effective_start_date and paf.effective_end_date and
3545 paf.email_address is not null;
3546
3547 cursor get_external_email_addresses is
3548 SELECT PARTY.EMAIL_ADDRESS
3549 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
3550 HZ_PARTIES PARTY,
3551 HZ_RELATIONSHIPS REL,
3552 HZ_CUST_ACCOUNTS ROLE_ACCT,
3553 HZ_ROLE_RESPONSIBILITY ROL ,
3554 HZ_ORG_CONTACTS ORG_CONT,
3555 HZ_LOCATIONS LOC,
3556 HZ_PARTY_SITES PARTY_SITE,
3557 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
3558 ota_delegate_bookings odb,
3559 ota_booking_status_types obst
3560 WHERE
3561 ACCT_ROLE.PARTY_ID = REL.PARTY_ID
3562 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
3563 AND REL.SUBJECT_ID = PARTY.PARTY_ID
3564 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3565 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3566 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
3567 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
3568 AND ROL.PRIMARY_FLAG (+) = 'Y'
3569 AND ROL.CUST_ACCOUNT_ROLE_ID (+) = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
3570 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
3571 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID(+)
3572 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
3573 AND ACCT_SITE.CUST_ACCT_SITE_ID (+) = ACCT_ROLE.CUST_ACCT_SITE_ID
3574 AND ACCT_ROLE.STATUS = 'A'
3575 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = odb.delegate_contact_id
3576 AND odb.event_id = p_event_id
3577 AND odb.booking_status_type_id = obst.booking_status_type_id
3578 AND obst.type in ('A', 'P', 'E')
3579 AND PARTY.EMAIL_ADDRESS is not null;
3580
3581 --email_addresses varchar2(4000) := ''; bug 9724972
3582 email_addresses clob := '';
3583 email_count number := 0;
3584
3585 begin
3586 for intemailids in get_internal_email_addresses loop
3587 if(email_count = 0) then
3588 email_addresses := intemailids.email_address;
3589 else
3590 email_addresses := email_addresses || ',' || intemailids.email_address;
3591 end if;
3592 email_count := email_count+1;
3593 end loop;
3594
3595 for extemailids in get_external_email_addresses loop
3596 if(email_count = 0) then
3597 email_addresses := extemailids.email_address;
3598 else
3599 email_addresses := email_addresses || ',' || extemailids.email_address;
3600 end if;
3601 email_count := email_count+1;
3602 end loop;
3603
3604 return email_addresses;
3605
3606 end get_learners_email_addresses;
3607
3608
3609 --function to determine switcher action for bulk and single on list of classes page
3610 --p_enr_type is 'B' for bulk enroll switcher and 'S' for Single enroll switcher
3611 FUNCTION is_class_enrollable(
3612 p_enr_type varchar2,
3613 p_class_id ota_events.event_id%TYPE)
3614 RETURN VARCHAR2
3615 IS
3616
3617 CURSOR csr_get_class_details IS
3618 SELECT null
3619 FROM ota_events
3620 WHERE event_id = p_class_id
3621
3622 AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, timezone)
3623 BETWEEN to_date(to_char(nvl(enrolment_start_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
3624 AND to_date(to_char(nvl(enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
3625 AND event_type IN ('SCHEDULED', 'SELFPACED')
3626 AND event_status IN ('N', 'P', 'F');
3627
3628 l_exists VARCHAR2(1);
3629 BEGIN
3630 OPEN csr_get_class_details;
3631 FETCH csr_get_class_details INTO l_exists;
3632 IF csr_get_class_details%NOTFOUND THEN
3633 CLOSE csr_get_class_details;
3634 if p_enr_type = 'B' then
3635 RETURN 'BULK_ENR_DISABLE';
3636 else
3637 RETURN 'SINGLE_ENR_DISABLE';
3638 end if;
3639 ELSE
3640 CLOSE csr_get_class_details;
3641 if p_enr_type = 'B' then
3642 RETURN 'BULK_ENR_ENABLE';
3643 else
3644 RETURN 'SINGLE_ENR_ENABLE';
3645 end if;
3646 END IF;
3647 END is_class_enrollable;
3648
3649 end ota_utility;