[Home] [Help]
PACKAGE BODY: APPS.OTA_UTILITY
Source
1 Package body ota_utility as
2 /* $Header: ottomint.pkb 120.43.12010000.3 2008/09/17 08:04:02 srgnanas 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 -- ----------------------------------------------------------------------------
653 -- |-----------------------------< Check_enrollment>-------------------------|
654 -- ----------------------------------------------------------------------------
655 -- {Start Of Comments}
656 --
657 -- Description:
658 -- This procedure will be used to check whether Enrollment exist or not.
659 --
660 --
661 -- Pre Conditions:
662 -- None.
663 --
664 -- In Arguments:
665 -- p_line_id,
666 --
667 -- In Arguments:
668 -- x_valid,
669 -- x_return_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 Procedure check_enrollment (p_line_id IN Number ,
684 x_valid OUT NOCOPY varchar2,
685 x_return_status OUT NOCOPY varchar2 )
686
687 IS
688
689 CURSOR C_enrollment
690 IS
691 Select null
692 FROM
693 OTA_DELEGATE_BOOKINGS
694 WHERE
695 Line_id = p_line_id;
696
697
698 l_proc varchar2(72) := g_package||'check_enrollment';
699 l_exists varchar2(1) ;
700 l_valid varchar2(1) := 'N';
701
702 BEGIN
703
704 hr_utility.set_location('Entering:'||l_proc, 15);
705 open c_enrollment;
706 fetch c_enrollment into l_exists;
707 if c_enrollment%found then
708 l_valid := 'Y';
709 end If;
710 CLOSE C_enrollment;
711 x_valid := l_valid;
712 hr_utility.set_location('Leaving:'||l_proc, 15);
713
714 END;
715 --
716
717 -- ----------------------------------------------------------------------------
718 -- |-----------------------------------< Check_event>-------------------------|
719 -- ----------------------------------------------------------------------------
720 -- {Start Of Comments}
721 --
722 -- Description:
723 -- This Procedure will be used to check Whether Event exist.
724 --
725 --
726 -- Pre Conditions:
727 -- None.
728 --
729 -- In Arguments:
730 -- p_line_id,
731 --
732 -- In Arguments:
733 -- x_valid,
734 -- x_return_status
735 --
736 -- Post Success:
737 -- Processing continues.
738 --
739 --
740 -- Post Failure:
741 -- None.
742 --
743 -- Access Status:
744 -- Public.
745 --
746 -- {End Of Comments}
747 ----------------------------------------------------------------------------
748 Procedure check_event (p_line_id IN Number,
749 x_valid OUT NOCOPY varchar2,
750 x_return_status OUT NOCOPY varchar2 )
751 IS
752
753 CURSOR C_event
754 IS
755 Select null
756 FROM
757 OTA_EVENTS
758 WHERE
759 Line_id = p_line_id;
760
761
762 l_proc varchar2(72) := g_package||'check_event';
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_event;
770 fetch c_event into l_exists;
771 if c_event%found then
772 l_valid := 'T';
773 end If;
774 CLOSE C_event;
775 x_valid := l_valid;
776 hr_utility.set_location('Leaving:'||l_proc, 15);
777
778 END;
779
780 --
781 -- ----------------------------------------------------------------------------
782 -- |----------------------------< get_lookup_meaning>-------------------------|
783 -- ----------------------------------------------------------------------------
784 -- {Start Of Comments}
785 --
786 -- Description:
787 -- This function will be used to get lookup meaning.
788 --
789 --
790 -- Pre Conditions:
791 -- None.
792 --
793 -- In Arguments:
794 -- p_lookup_type
795 -- p_lookup_code
796 -- p_application_id
797 --
798 -- Post Success:
799 -- Processing continues.
800 --
801 --
802 -- Post Failure:
803 -- None.
804 --
805 -- Access Status:
806 -- Public.
807 --
808 -- {End Of Comments}
809 ----------------------------------------------------------------------------
810
811 Function Get_lookup_meaning (
812 --******************************************************************************
813 --* Returns the meaning for a lookup code of a specified type.
814 --******************************************************************************
815 --
816 p_lookup_type varchar2,
817 p_lookup_code varchar2,
818 p_application_id number) return varchar2 is
819 --
820 cursor csr_lookup is
821 select meaning
822 from hr_lookups
823 where lookup_type = p_lookup_type
824 and lookup_code = p_lookup_code
825 and enabled_flag = 'Y';
826 --
827 l_meaning varchar2(80) := null;
828 --
829 begin
830 --
831 -- Only open the cursor if the parameters are going to retrieve anything
832 --
833 if p_lookup_type is not null and p_lookup_code is not null then
834 --
835 open csr_lookup;
836 fetch csr_lookup into l_meaning;
837 close csr_lookup;
838 --
839 end if;
840 --
841 return l_meaning;
842 --
843 end get_lookup_meaning;
844
845
846 -- ----------------------------------------------------------------------------
847 -- |--------------------------------< CHECK_INVOICE >-------------------------|
848 -- ----------------------------------------------------------------------------
849 -- {Start Of Comments}
850 --
851 -- Description:
852 -- This procedure will be a used to check the invoice of Order Line.
853 --
854 -- IN
855 -- p_line_id
856 -- p_org_id
857 --
858 -- OUT
859 -- p_exist
860 --
861 -- Post Failure:
862 -- None.
863 --
864 -- Access Status:
865 -- Public.
866 --
867 -- {End Of Comments}
868 ----------------------------------------------------------------------------
869
870 PROCEDURE CHECK_INVOICE (
871 p_Line_id IN NUMBER,
872 p_Org_id IN NUMBER,
873 p_exist OUT NOCOPY VARCHAR2)
874 IS
875
876 l_proc varchar2(72) := g_package||'check_invoice';
877 l_invoice_quantity oe_order_lines.invoiced_quantity%type;
878
879
880 CURSOR c_invoice IS
881 SELECT
882 decode(invoiced_quantity,null,0,invoiced_quantity)
883 FROM
884 oe_order_lines_all
885 WHERE
886 line_id = p_line_id;
887
888
889 BEGIN
890 hr_utility.set_location('Entering:'||l_proc, 5);
891
892 OPEN c_invoice;
893 FETCH c_invoice into l_invoice_quantity;
894 IF c_invoice%found THEN
895 IF l_invoice_quantity = 1 then
896 p_exist := 'Y';
897 ELSE
898 p_exist := 'N';
899 END IF;
900 END IF;
901 CLOSE c_invoice;
902
903
904 hr_utility.set_location(' Leaving:'||l_proc, 10);
905 END;
906 --
907 -- ----------------------------------------------------------------------------
908 -- |--------------------------------< CHECK_WF_STATUS>-------------------------|
909 -- ----------------------------------------------------------------------------
910 -- {Start Of Comments}
911 --
912 -- Description:
913 -- This function will be a used to check the workflow status of Order Line.
914 --
915 -- IN
916 -- p_line_id
917 --
918 -- OUT
919 -- p_exist
920 --
921 -- Post Failure:
922 -- None.
923 --
924 -- Access Status:
925 -- Public.
926 --
927 -- {End Of Comments}
928 ----------------------------------------------------------------------------
929
930 FUNCTION Check_wf_Status (
931 p_Line_id NUMBER,
932 p_activity varchar2)
933
934 return boolean
935
936 IS
937
938 l_proc varchar2(72) := g_package||'Check_wf_Status' ;
939 l_exist varchar2(1);
940 l_return boolean :=False;
941
942 CURSOR line_wf IS
943 SELECT null
944 FROM wf_item_activity_statuses_v wf
945 WHERE activity_name = p_activity
946 AND activity_status_code = 'NOTIFIED'
947 AND item_type = 'OEOL'
948 AND item_key = to_char(p_line_id);
949
950 BEGIN
951 hr_utility.set_location('Entering:'||l_proc, 5);
952 OPEN line_wf;
953 fetch line_wf into l_exist;
954 if line_wf%found then
955 l_return := True;
956 end if;
957 CLOSE line_wf;
958 Return(l_return);
959
960 hr_utility.set_location('Leaving:'||l_proc, 10);
961 END check_wf_status;
962
963
964 -- ----------------------------------------------------------------------------
965 -- |-------------------------< other_bookings_clash >-------------------------|
966 -- ----------------------------------------------------------------------------
967 --
968 -- PUBLIC
969 -- Description: Other Bookings Clash
970 --
971 -- Checks if the booking being made clashes with any other
972 -- bookings for the delegate
973 -- Note - bookings only clash if they are confirmed
974 --
975 Procedure other_bookings_clash (p_delegate_person_id in varchar2,
976 p_delegate_contact_id in varchar2,
977 p_event_id in number,
978 p_booking_status_type_id in varchar2,
979 p_return_status out nocopy boolean,
980 p_warn out nocopy boolean)
981 is
982 --
983 --
984 -- cursor to select any confirmed bookings for events which
985 -- clash with the event being booked
986 --
987 cursor c_other_person_bookings is
988 select bst.type
989 from ota_delegate_bookings db,
990 ota_booking_status_types bst,
991 ota_events ev,
992 ota_events evt
993 where db.delegate_person_id = p_delegate_person_id
994 and db.booking_status_type_id = bst.booking_status_type_id
995 and bst.type <> g_cancelled_booking
996 and db.event_id = ev.event_id
997 and evt.event_id = p_event_id
998 and ev.event_id <> p_event_id
999 and ((
1000 ev.course_start_date = ev.course_end_date and
1001 evt.course_start_date = evt.course_end_date and
1002 ev.course_start_date = evt.course_start_date and
1003 nvl(evt.course_start_time, '-99:99') <= nvl(ev.course_end_time, '99:99') and
1004 nvl(evt.course_end_time, '99:99') >= nvl(ev.course_start_time, '-99:99')
1005 )
1006 or (
1007 (ev.course_start_date <> ev.course_end_date or
1008 evt.course_start_date <> evt.course_end_date) and
1009 ev.course_start_date <= evt.course_end_date and
1010 ev.course_end_date >= evt.course_start_date
1011 ))
1012 order by bst.type;
1013 --
1014 cursor c_other_contact_bookings is
1015 select bst.type
1016 from ota_delegate_bookings db,
1017 ota_booking_status_types bst,
1018 ota_events ev,
1019 ota_events evt
1020 where db.delegate_contact_id = p_delegate_contact_id
1021 and db.booking_status_type_id = bst.booking_status_type_id
1022 and bst.type <> g_cancelled_booking
1023 and db.event_id = ev.event_id
1024 and evt.event_id = p_event_id
1025 and ev.event_id <> p_event_id
1026 and ev.course_start_date <= evt.course_end_date
1027 and ev.course_end_date >= evt.course_start_date
1028 order by bst.type;
1029 --
1030 l_proc varchar2(72) := g_package||'other_bookings_clash';
1031 l_result boolean;
1032 l_warn boolean := false;
1033 l_booking_status varchar2(80);
1034 l_dummy varchar2(80);
1035 --
1036 Begin
1037 hr_utility.set_location('Entering:'||l_proc, 5);
1038 --
1039 p_warn := False;
1040 p_return_status := False;
1041 if p_delegate_person_id is not null then
1042 --
1043 open c_other_person_bookings;
1044 fetch c_other_person_bookings into l_dummy;
1045 l_result := c_other_person_bookings%found;
1046 close c_other_person_bookings;
1047 --
1048 elsif p_delegate_contact_id is not null then
1049 --
1050 open c_other_contact_bookings;
1051 fetch c_other_contact_bookings into l_dummy;
1052 l_result := c_other_contact_bookings%found;
1053 close c_other_contact_bookings;
1054 --
1055 end if;
1056 --
1057
1058 if not l_result then
1059 --
1060 l_booking_status := ota_tdb_bus.booking_status_type(p_booking_status_type_id);
1061
1062 if l_booking_status in (g_attended_booking, g_placed_booking) and
1063 l_dummy in (g_attended_booking, g_placed_booking) then
1064 --
1065 p_return_status := True;
1066 --
1067 else
1068 --
1069 if l_booking_status <> g_cancelled_booking then
1070 --
1071 p_warn := true;
1072 --
1073 end if;
1074 --
1075 end if;
1076 --
1077 end if;
1078
1079 if p_delegate_contact_id is null and p_delegate_person_id is null then
1080 p_warn := true;
1081 end if;
1082 --
1083
1084 --
1085 hr_utility.set_location(' Leaving:'||l_proc, 10);
1086 End other_bookings_clash;
1087 -- --------------------------------------------------------------------------
1088 -- |--------------------------<GET_BG_NAME>-------------------------------|--
1089 -- -----------------------------------------------------------------------
1090 -- {Start of Comments}
1091 -- Description:
1092 -- This function will be used to get the Business Group Name for the Organization ID
1093 -- that is passed into it.
1094 -- IN
1095 -- p_organization_id
1096 --
1097 -- OUT
1098 -- p_return
1099 -- Post Failure
1100 -- None
1101 --
1102 -- Access Status:
1103 -- PUBLIC
1104 -- {End of Comments}
1105 -----------------------------------------------------------------------------
1106 FUNCTION get_bg_name(p_organization_id NUMBER) RETURN VARCHAR2
1107 IS
1108 l_proc VARCHAR2(72) := g_package||'Get_Bg_Name';
1109 l_return hr_all_organization_units.name%TYPE;
1110
1111 CURSOR bg_cr IS
1112 SELECT bg.name
1113 FROM hr_all_organization_units org,
1114 hr_all_organization_units bg
1115 WHERE org.business_group_id = bg.organization_id
1116 AND org.organization_id = p_organization_id;
1117
1118 BEGIN
1119 hr_utility.set_location('Entering :'||l_proc,5);
1120 OPEN bg_cr;
1121 FETCH bg_cr INTO l_return;
1122 CLOSE bg_cr;
1123 RETURN l_return;
1124 hr_utility.set_location('Leaving:'||l_proc,10);
1125 EXCEPTION
1126 WHEN others THEN
1127 l_return := NULL;
1128 RETURN l_return;
1129 END get_bg_name;
1130
1131 -- --------------------------------------------------------------------------
1132 -- |--------------------------<get_commitment_detail>-------------------------------|--
1133 -- -----------------------------------------------------------------------
1134 -- {Start of Comments}
1135 -- Description:
1136 -- This procedure will call the OM APi to return the commitment details when a line_id
1137 -- is passed into it.
1138 -- IN Reqd Type
1139 -- p_line_id NUMBER
1140 --
1141 -- OUT
1142 -- p_commitment_id NUMBER
1143 -- p_commitment_number VARCHAR2
1144 -- p_commitment_start_date DATE
1145 -- p_commitment_end_date DATE
1146 --
1147 -- Post Failure
1148 -- None
1149 --
1150 -- Access Status:
1151 -- PUBLIC
1152 -- {End of Comments}
1153 -----------------------------------------------------------------------------
1154 PROCEDURE get_commitment_detail
1155 (p_line_id IN NUMBER,
1156 p_commitment_id OUT NOCOPY NUMBER,
1157 p_commitment_number OUT NOCOPY VARCHAR2,
1158 p_commitment_start_date OUT NOCOPY DATE,
1159 p_commitment_end_date OUT NOCOPY DATE)
1160 IS
1161 --
1162 -- Declare cursors and local variables.
1163 --
1164 l_proc VARCHAR2(72) := g_package||'get_commitment_detail';
1165 l_check_om_installed VARCHAR2(1);
1166
1167 l_execute_proc VARCHAR2(4000);
1168
1169 BEGIN
1170 hr_utility.set_location('Entering :'||l_proc,5);
1171 l_check_om_installed := check_product_installed(660);
1172 IF l_check_om_installed = 'Y' THEN
1173 l_execute_proc := '
1174 BEGIN
1175 oe_commitment_util.get_commitment_info(:p_line_id,
1176 :x_commitment_id,
1177 :x_commitment_number,
1178 :x_commitment_start_date,
1179 :x_commitment_end_date);
1180 END;';
1181 EXECUTE IMMEDIATE l_execute_proc
1182 USING IN p_line_id,
1183 OUT p_commitment_id,
1184 OUT p_commitment_number,
1185 OUT p_commitment_start_date,
1186 OUT p_commitment_end_date;
1187 END IF;
1188 hr_utility.set_location('Leaving:'||l_proc,80);
1189 EXCEPTION
1190 WHEN others THEN
1191 hr_utility.set_location('Leaving:'||l_proc,90);
1192 END get_commitment_detail;
1193 --
1194
1195 -- --------------------------------------------------------------------------
1196 -- |--------------------------<check_product_installed>-------------------------------|--
1197 -- -----------------------------------------------------------------------
1198 -- {Start of Comments}
1199 -- Description:
1200 -- This function will return a VARCHAR indicating if the particular product code
1201 -- passed in is installed or not.
1202 -- IN Reqd Type
1203 -- p_application_id NUMBER
1204 --
1205 -- OUT
1206 -- l_return VARCHAR2
1207 --
1208 -- Post Failure
1209 -- None
1210 --
1211 -- Access Status:
1212 -- PUBLIC
1213 -- {End of Comments}
1214 -----------------------------------------------------------------------------
1215 FUNCTION check_product_installed
1216 (p_application_id IN NUMBER) RETURN VARCHAR2
1217 IS
1218 --
1219 -- Declare cursors and local variables.
1220 --
1221 l_proc VARCHAR2(72) := g_package||'check_product_installed';
1222 l_status VARCHAR2(1);
1223 l_industry VARCHAR2(1);
1224 l_return_val VARCHAR2(1) := 'N';
1225
1226 BEGIN
1227 hr_utility.set_location('Entering :'||l_proc,5);
1228 IF (fnd_installation.get ( p_application_id,
1229 p_application_id,
1230 l_status,
1231 l_industry)) THEN
1232 IF l_status IN ('I', 'S') THEN
1233 l_return_val := 'Y';
1234 ELSE l_return_val := 'N';
1235 END IF;
1236 ELSE
1237 l_return_val := 'N';
1238 END IF;
1239 RETURN l_return_val;
1240 hr_utility.set_location('Leaving:'||l_proc,80);
1241 EXCEPTION
1242 WHEN others THEN
1243 l_return_val := 'N';
1244 RETURN l_return_val;
1245 hr_utility.set_location('Leaving:'||l_proc,90);
1246 END check_product_installed;
1247
1248
1249 -- ----------------------------------------------------------------
1250 -- ------------------<get_delivery_method >--------------------
1251 -- ----------------------------------------------------------------
1252 -- {Start of Comments}
1253 --
1254 -- Description:
1255 -- This function will be used to find the delivery method name/category name of the particular
1256 -- activity
1257 -- IN
1258 -- p_activity_id
1259 -- p_return_value
1260 --
1261 -- Post Failure:
1262 -- None.
1263 -- Access Status
1264 -- Public
1265 -- {End of Comments}
1266 ------------------------------------------------------------------
1267 FUNCTION get_Delivery_Method(p_activity_version_id IN NUMBER,
1268 p_return_value IN VARCHAR2)
1269 RETURN VARCHAR2 IS
1270
1271 CURSOR get_primary_dm_cr IS
1272 SELECT lookup.meaning Dm_Name,
1273 catusg.category Dm_Code
1274 FROM ota_act_cat_inclusions actcat,
1275 ota_category_usages catusg,
1276 hr_lookups lookup
1277 WHERE actcat.category_usage_id= catusg.category_usage_id
1278 AND actcat.primary_flag='Y'
1279 AND catusg.category = lookup.lookup_code
1280 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1281 AND catusg.type='DM'
1282 AND actcat.activity_version_id = p_activity_version_id;
1283
1284 CURSOR get_first_dm_cr IS
1285 SELECT lookup.meaning dm_name,
1286 catusg.category dm_code
1287 FROM ota_act_cat_inclusions actcat,
1288 ota_category_usages catusg,
1289 hr_lookups lookup
1290 WHERE actcat.category_usage_id= catusg.category_usage_id
1291 AND catusg.category = lookup.lookup_code
1292 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1293 AND catusg.type='DM'
1294 AND actcat.activity_version_id = p_activity_version_id;
1295
1296 -- Added for Bug No.2941052
1297 -- Get Delivery Method name corresponding to code 'INCLASS'
1298 CURSOR get_dm_name IS
1299 SELECT meaning
1300 FROM hr_lookups
1301 WHERE lookup_type ='ACTIVITY_CATEGORY'
1302 AND lookup_code = 'INCLASS';
1303
1304 l_proc VARCHAR2(72) := g_package||'Get_Delivery_Method';
1305
1306 l_pr_dm_cat hr_lookups.lookup_code%TYPE;
1307 l_pr_dm_name hr_lookups.meaning%TYPE;
1308
1309
1310 l_return_dm_cat hr_lookups.lookup_code%TYPE := 'INCLASS';
1311 l_return_dm_name hr_lookups.meaning%TYPE;
1312 l_return VARCHAR2(100);
1313 l_counter NUMBER := 0;
1314
1315 BEGIN
1316
1317 hr_utility.set_location('Entering :'||l_proc,5);
1318
1319 FOR primary_dm_rec IN get_primary_dm_cr
1320 LOOP
1321 l_pr_dm_name := primary_dm_rec.dm_name;
1322 l_pr_dm_cat := primary_dm_rec.dm_code;
1323 END LOOP;
1324 IF l_pr_dm_name IS NULL THEN
1325 FOR first_dm_rec IN get_first_dm_cr
1326 LOOP
1327 l_counter := l_counter + 1;
1328 IF l_counter = 1 THEN
1329 l_return_dm_name := first_dm_rec.dm_name;
1330 l_return_dm_cat := first_dm_rec.dm_code;
1331 END IF;
1332 END LOOP;
1333 ELSE
1334 IF p_return_value = 'ICON'
1335 THEN l_return_dm_cat := l_pr_dm_cat;
1336 ELSIF p_return_value = 'NAME'
1337 THEN l_return_dm_name := l_pr_dm_name;
1338 END IF;
1339 END IF;
1340
1341 -- Added for Bug No.2941052
1342 -- Fetch default Delivery Method Name.
1343 IF l_return_dm_name IS NULL THEN
1344 OPEN get_dm_name;
1345 FETCH get_dm_name INTO l_return_dm_name;
1346 CLOSE get_dm_name;
1347 END IF;
1348
1349 IF p_return_value = 'ICON'
1350 THEN l_return := l_return_dm_cat;
1351 ELSIF p_return_value = 'NAME'
1352 THEN l_return := l_return_dm_name;
1353 END IF;
1354
1355 RETURN l_return;
1356
1357 EXCEPTION
1358 WHEN others THEN
1359 hr_utility.set_location('Leaving :'||l_proc,15);
1360 RETURN l_return;
1361 END get_delivery_method;
1362
1363 Function get_delivery_method (p_offering_id in number)
1364 return varchar2
1365 is
1366
1367
1368 Cursor get_DM is
1369 select ocu.category
1370 from ota_category_usages_tl ocu , ota_offerings oaf
1371 where oaf.delivery_mode_id = ocu.category_usage_id
1372 and oaf.offering_id = p_offering_id
1373 and ocu.Language = USERENV('LANG');
1374
1375 l_delivery_method varchar2(240);
1376
1377
1378 begin
1379
1380 OPEN get_DM;
1381 FETCH get_DM INTO l_delivery_method;
1382 close get_DM;
1383 return(l_delivery_method);
1384
1385
1386 end get_delivery_method;
1387 -- ----------------------------------------------------------------
1388 -- ------------------<students_on_waitlist >--------------------
1389 -- ----------------------------------------------------------------
1390 -- {Start of Comments}
1391 --
1392 -- Description:
1393 -- This function will be used to find the number of students waitlisted
1394 -- in a particular event
1395 -- IN
1396 -- p_event_id
1397 --
1398 --
1399 -- Post Failure:
1400 -- None.
1401 -- Access Status
1402 -- Public
1403 -- {End of Comments}
1404 ------------------------------------------------------------------
1405 FUNCTION students_on_waitlist(p_event_id IN NUMBER)
1406 RETURN NUMBER
1407 IS
1408 l_proc VARCHAR2(72) := g_package||'Students_On_Waitlist';
1409 l_num_waitlisted NUMBER DEFAULT 0;
1410 BEGIN
1411 hr_utility.set_location('Entering :'||l_proc,5);
1412 SELECT COUNT(booking_id)
1413 INTO l_num_waitlisted
1414 FROM ota_delegate_bookings tdb
1415 WHERE tdb.event_id = p_event_id
1416 AND tdb.booking_status_type_id IN (SELECT bst.booking_status_type_id
1417 FROM ota_booking_status_types bst
1418 WHERE bst.type = 'W');
1419 hr_utility.set_location('Leaving :'||l_proc,10);
1420 RETURN l_num_waitlisted;
1421
1422 EXCEPTION
1423
1424 WHEN NO_DATA_FOUND THEN
1425 hr_utility.set_location('Leaving:'||l_proc,15);
1426 RETURN l_num_waitlisted;
1427
1428 END students_on_waitlist;
1429
1430 -- ----------------------------------------------------------------
1431 -- ------------------<Place_on_waitlist >--------------------
1432 -- ----------------------------------------------------------------
1433 -- {Start of Comments}
1434 --
1435 -- Description:
1436 -- This function will be used to check the place on waitlist for a particular enrollment
1437 -- in the particular event.
1438 -- IN
1439 -- p_event_id
1440 -- p_booking_id
1441 --
1442 -- Post Failure:
1443 -- None.
1444 -- Access Status
1445 -- Public
1446 -- {End of Comments}
1447 ------------------------------------------------------------------
1448 FUNCTION place_on_waitlist(p_event_id IN NUMBER,
1449 p_booking_id IN NUMBER)
1450 RETURN NUMBER
1451 IS
1452 l_proc VARCHAR2(72) := g_package||'Place_On_Waitlist';
1453 l_count number := 1;
1454
1455 CURSOR c_date_waitlist is
1456 SELECT tdb.booking_id
1457 FROM ota_delegate_bookings tdb,
1458 ota_booking_status_types bst
1459 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1460 AND bst.type = 'W'
1461 AND tdb.event_id = p_event_id
1462 ORDER BY tdb.date_booking_placed;
1463
1464 CURSOR c_priority_waitlist is
1465 SELECT tdb.booking_id
1466 FROM ota_delegate_bookings tdb,
1467 ota_booking_status_types bst
1468 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1469 AND bst.type = 'W'
1470 AND tdb.event_id = p_event_id
1471 ORDER BY tdb.booking_priority,
1472 tdb.booking_id;
1473
1474
1475 BEGIN
1476 hr_utility.set_location('Entering :'||l_proc,5);
1477
1478 IF fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') = 'BP' THEN
1479 --
1480 FOR l_waitlist_entry IN c_priority_waitlist
1481 LOOP
1482 --
1483
1484 IF p_booking_id = l_waitlist_entry.booking_id THEN
1485 RETURN l_count;
1486 ELSE l_count := l_count+1;
1487 END IF;
1488 END LOOP;
1489 --
1490 ELSE
1491 --
1492 FOR l_waitlist_entry IN c_date_waitlist LOOP
1493 --
1494 IF p_booking_id = l_waitlist_entry.booking_id THEN
1495 RETURN l_count;
1496 ELSE l_count := l_count + 1;
1497 END IF;
1498 --
1499 END LOOP;
1500 --
1501 END IF;
1502 hr_utility.set_location('Leaving :'||l_proc,10);
1503 EXCEPTION
1504 WHEN others THEN
1505 hr_utility.set_location('Leaving :'||l_proc,15);
1506 RETURN l_count;
1507 END place_on_waitlist;
1508
1509 -- ----------------------------------------------------------------
1510 -- ------------------<get_event_location >--------------------
1511 -- ----------------------------------------------------------------
1512 -- {Start of Comments}
1513 --
1514 -- Description:
1515 -- This function will be used to return the location id for the event.
1516 -- IN
1517 -- p_event_id
1518 --
1519 --
1520 -- Post Failure:
1521 -- None.
1522 -- Access Status
1523 -- Public
1524 -- {End of Comments}
1525 ------------------------------------------------------------------
1526 FUNCTION get_event_location(p_event_id IN NUMBER)
1527 RETURN NUMBER
1528 IS
1529
1530 CURSOR primary_venue_cr
1531 IS
1532 SELECT s.location_id
1533 FROM ota_suppliable_resources s,
1534 ota_resource_bookings r
1535 WHERE r.supplied_resource_id = s.supplied_resource_id
1536 AND r.event_id = p_event_id
1537 AND primary_venue_flag = 'Y';
1538
1539 CURSOR evt_loc_cr
1540 IS
1541 SELECT e.location_id,
1542 e.training_center_id
1543 FROM ota_events e
1544 WHERE e.event_id = p_event_id;
1545
1546 l_training_center_id ota_events.training_center_id%TYPE;
1547 l_location_id hr_locations_all.location_id%TYPE;
1548
1549 CURSOR tc_loc_cr(p_training_center_id ota_events.training_center_id%TYPE)
1550 IS
1551 SELECT o.location_id
1552 FROM hr_all_organization_units o
1553 WHERE o.organization_id = p_training_center_id;
1554
1555 l_proc VARCHAR2(72) := g_package||'Get_Event_Location';
1556 BEGIN
1557 hr_utility.set_location('Entering :'||l_proc,5);
1558 FOR location1 IN primary_venue_cr
1559 LOOP
1560 l_location_id := location1.location_id;
1561
1562 END LOOP;
1563
1564 IF l_location_id IS NULL THEN
1565 FOR location2 IN evt_loc_cr
1566 LOOP
1567 l_location_id := location2.location_id;
1568 l_training_center_id := location2.training_center_id;
1569 END LOOP;
1570
1571 END IF;
1572
1573 IF l_location_id IS NULL THEN
1574 FOR location3 IN tc_loc_cr(l_training_center_id)
1575 LOOP
1576 l_location_id := location3.location_id;
1577 END LOOP;
1578
1579 END IF;
1580 hr_utility.set_location('Leaving :'||l_proc,10);
1581 RETURN l_location_id;
1582
1583 EXCEPTION
1584 WHEN others THEN
1585 hr_utility.set_location('Leaving :'||l_proc,15);
1586 RETURN l_location_id;
1587 END get_event_location;
1588 -- ----------------------------------------------------------------
1589 -- ------------------<get_play_button >--------------------
1590 -- ----------------------------------------------------------------
1591 -- {Start of Comments}
1592 --
1593 -- Description:
1594 -- This function will be used to return a varchar to indicate if
1595 -- Play button will be displayed or not.
1596 -- IN
1597 -- p_person_id
1598 -- p_offering_id
1599 -- p_enrollment_status
1600 -- p_course_start_date
1601 -- p_course_end_date
1602 --
1603 -- Post Failure:
1604 -- None.
1605 -- Access Status
1606 -- Public
1607 -- {End of Comments}
1608 ------------------------------------------------------------------
1609 FUNCTION get_play_button(p_person_id IN NUMBER,
1610 p_offering_id IN NUMBER,
1611 p_enrollment_status IN VARCHAR2,
1612 p_course_start_date IN DATE,
1613 p_course_end_date IN DATE)
1614 RETURN VARCHAR2
1615 IS
1616 CURSOR get_emp_id_cr
1617 IS
1618 SELECT employee_id
1619 FROM fnd_user
1620 WHERE user_id = fnd_profile.value('USER_ID');
1621
1622 l_proc VARCHAR2(72) := g_package||'Get_Play_Button';
1623 l_fnd_user NUMBER;
1624 l_play VARCHAR2(10) := 'N';
1625
1626 BEGIN
1627 hr_utility.set_location('Entering :'||l_proc,5);
1628 FOR get_emp IN get_emp_id_cr
1629 LOOP
1630 l_fnd_user := get_emp.employee_id;
1631 END LOOP;
1632
1633 IF p_person_id = l_fnd_user
1634 AND p_offering_id IS NOT NULL
1635 AND p_enrollment_status = 'P'
1636 AND sysdate >= p_course_start_date
1637 AND p_course_end_date >= sysdate
1638 THEN l_play := 'P';
1639 ELSIF p_person_id = l_fnd_user
1640 AND p_offering_id IS NOT NULL
1641 AND p_enrollment_status ='A'
1642 AND sysdate >= p_course_start_date
1643 AND p_course_end_date >= sysdate
1644 THEN l_play := 'R';
1645 ELSE l_play := 'N';
1646 END IF;
1647 hr_utility.set_location('Leaving :'||l_proc,10);
1648 RETURN l_play;
1649
1650 EXCEPTION
1651 WHEN others THEN
1652 hr_utility.set_location('Leaving :'||l_proc,15);
1653 RETURN l_play;
1654 END get_play_button;
1655 -- ----------------------------------------------------------------
1656 -- --------------------< get_authorizer_name >---------------------
1657 -- ----------------------------------------------------------------
1658 -- {Start of Comments}
1659 --
1660 -- Description:
1661 -- This function will be used to find the name of the person who
1662 -- authorized enrollment in an event.
1663 -- IN
1664 -- p_authorizer_id
1665 -- p_course_start_date
1666 -- p_course_end_date
1667 --
1668 -- Post Failure:
1669 -- None.
1670 -- Access Status
1671 -- Public
1672 -- {End of Comments}
1673 ------------------------------------------------------------------
1674 FUNCTION get_authorizer_name(p_authorizer_id IN NUMBER,
1675 p_course_start_date IN DATE,
1676 p_course_end_date IN DATE)
1677 RETURN VARCHAR2
1678 IS
1679 --
1680 CURSOR get_name_csr (p_authorizer_id IN NUMBER, p_course_start_date IN DATE, p_course_end_date IN DATE)
1681 IS
1682
1683 /* Modified for Bug#3552493
1684 SELECT DECODE(per.last_name, NULL, NULL, per.last_name)||
1685 DECODE(per.title, NULL, DECODE(per.first_name, NULL, NULL, ', '), ', '||per.title)||
1686 --Modified for Bug#2997820
1687 --DECODE(per.first_name,NULL,NULL, per.last_name) full_name
1688 DECODE(per.first_name,NULL,NULL, per.first_name) full_name
1689 FROM per_all_people_f per, fnd_user u
1690 WHERE per.person_id = u.employee_id
1691 AND (per.effective_end_date >= DECODE(p_course_end_date, NULL, TRUNC(SYSDATE), p_course_end_date) AND
1692 per.effective_start_date <= DECODE(p_course_start_date, NULL, TRUNC(SYSDATE), p_course_start_date))
1693 AND u.user_id = p_authorizer_id;
1694 */
1695 SELECT decode(fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME'),'FN',per.full_name,
1696 per.first_name||' '|| per.last_name||' '||per.suffix) FULL_NAME
1697 FROM per_all_people_f per, fnd_user u
1698 WHERE per.person_id = u.employee_id
1699 AND u.user_id = p_authorizer_id
1700 AND trunc(SYSDATE) between per.effective_start_date and per.effective_end_date;
1701
1702 --
1703 l_full_name per_all_people_f.full_name%TYPE DEFAULT NULL;
1704 l_authorizer_not_found EXCEPTION;
1705 l_proc VARCHAR2(72) := g_package||'Get_Authorizer_Name';
1706 --
1707 BEGIN
1708 --
1709 hr_utility.set_location('Entering :'||l_proc,5);
1710 --
1711 OPEN get_name_csr (p_authorizer_id, p_course_start_date, p_course_end_date);
1712 FETCH get_name_csr INTO l_full_name;
1713 IF get_name_csr%NOTFOUND THEN
1714 RAISE l_authorizer_not_found;
1715 END IF;
1716 CLOSE get_name_csr;
1717 --
1718 --
1719 RETURN l_full_name;
1720 --
1721 EXCEPTION
1722 --
1723 WHEN l_authorizer_not_found THEN
1724 --
1725 hr_utility.set_location('Leaving :'||l_proc,25);
1726 l_full_name := NULL;
1727 RETURN l_full_name;
1728 --
1729 WHEN others THEN
1730 --
1731 hr_utility.set_location('Leaving :'||l_proc,15);
1732 RETURN l_full_name;
1733
1734 END get_authorizer_name;
1735
1736 -- ----------------------------------------------------------------
1737 -- --------------------< get_message >---------------------
1738 -- ----------------------------------------------------------------
1739 -- {Start of Comments}
1740 --
1741 -- Description:
1742 -- This function will be used to find the message text for the
1743 -- message code passed in.
1744 -- IN
1745 -- p_application_code
1746 -- p_message_code
1747 --
1748 -- Post Failure:
1749 -- None.
1750 -- Access Status
1751 -- Public
1752 -- {End of Comments}
1753 ------------------------------------------------------------------
1754 FUNCTION get_message(p_application_code IN VARCHAR2,
1755 p_message_code IN VARCHAR2)
1756 RETURN VARCHAR2
1757 IS
1758 --
1759 l_msg_not_found EXCEPTION;
1760 l_proc VARCHAR2(72) := g_package||'Get_Message';
1761 l_msg_text VARCHAR2(2000);
1762 --
1763 BEGIN
1764 --
1765 hr_utility.set_location('Entering :'||l_proc,5);
1766 --
1767 fnd_message.set_name(p_application_code, p_message_code);
1768 l_msg_text := fnd_message.get();
1769 --
1770 IF l_msg_text IS NULL THEN
1771 RAISE l_msg_not_found;
1772 END IF;
1773 --
1774 RETURN l_msg_text ;
1775 --
1776 EXCEPTION
1777 --
1778 WHEN l_msg_not_found THEN
1779 --
1780 hr_utility.set_location('Leaving :'||l_proc,25);
1781 RETURN l_msg_text;
1782 --
1783 WHEN others THEN
1784 --
1785 hr_utility.set_location('Leaving :'||l_proc,15);
1786 RETURN l_msg_text;
1787
1788 END get_message;
1789 -- ----------------------------------------------------------------
1790 -- --------------------< get_date_time >---------------------
1791 -- ----------------------------------------------------------------
1792 -- {Start of Comments}
1793 --
1794 -- Description:
1795 -- This function will be used to return date and time.
1796 -- IN
1797 -- p_date
1798 -- p_time
1799 -- p_time_of_day
1800 -- OUT
1801 -- p_date_time
1802 --
1803 -- Post Failure:
1804 -- None.
1805 -- Access Status
1806 -- Public
1807 -- {End of Comments}
1808 ------------------------------------------------------------------
1809 FUNCTION get_date_time(p_date IN DATE,
1810 p_time IN VARCHAR2,
1811 p_time_of_day IN VARCHAR2)
1812 RETURN DATE
1813 IS
1814 --
1815 l_proc VARCHAR2(72) := g_package||'Get_Date_Time';
1816 l_date_time DATE;
1817 --
1818 BEGIN
1819 --
1820 hr_utility.set_location('Entering :'||l_proc,5);
1821 --
1822 IF p_time IS NULL THEN
1823 IF p_time_of_day = 'END'
1824 THEN
1825 l_date_time := to_date(to_char(p_date,'DD-MON-YYYY')||'23:59','DD/MM/YYYYHH24:MI');
1826 ELSIF p_time_of_day = 'START'
1827 THEN l_date_time := to_date(to_char(p_date,'DD-MON-YYYY')||'00:00','DD/MM/YYYYHH24:MI');
1828 END IF;
1829 ELSE
1830 l_date_time := to_date(to_char(p_date,'DD-MON-YYYY')||p_time,'DD/MM/YYYYHH24:MI');
1831 END IF;
1832 --
1833 RETURN l_date_time ;
1834 --
1835 EXCEPTION
1836 --
1837 WHEN others THEN
1838 --
1839 hr_utility.set_location('Leaving :'||l_proc,15);
1840 RETURN l_date_time;
1841 END get_date_time;
1842
1843
1844
1845 -- ----------------------------------------------------------------
1846 -- ------------------<get_category_name >--------------------
1847 -- ----------------------------------------------------------------
1848 -- {Start of Comments}
1849 --
1850 -- Description:
1851 -- This function will be used to find the category name of the particular
1852 -- activity
1853 -- IN
1854 -- p_activity_id
1855 --
1856 -- OUT
1857 -- category name
1858 -- Post Failure:
1859 -- None.
1860 -- Access Status
1861 -- Public
1862 -- {End of Comments}
1863 ------------------------------------------------------------------
1864
1865
1866
1867 FUNCTION get_category_name(p_activity_version_id IN NUMBER)
1868 RETURN VARCHAR2 IS
1869
1870 CURSOR get_primary_dm_cr IS
1871 SELECT lookup.meaning Dm_Name,
1872 catusg.category Dm_Code
1873 FROM ota_act_cat_inclusions actcat,
1874 ota_category_usages catusg,
1875 hr_lookups lookup
1876 WHERE actcat.category_usage_id= catusg.category_usage_id
1877 AND actcat.primary_flag='Y'
1878 AND catusg.category = lookup.lookup_code
1879 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1880 AND catusg.type='C'
1881 AND actcat.activity_version_id = p_activity_version_id;
1882
1883 CURSOR get_first_dm_cr IS
1884 SELECT lookup.meaning dm_name,
1885 catusg.category dm_code
1886 FROM ota_act_cat_inclusions actcat,
1887 ota_category_usages catusg,
1888 hr_lookups lookup
1889 WHERE actcat.category_usage_id= catusg.category_usage_id
1890 AND catusg.category = lookup.lookup_code
1891 AND lookup.lookup_type ='ACTIVITY_CATEGORY'
1892 AND catusg.type='C'
1893 AND actcat.activity_version_id = p_activity_version_id;
1894
1895 l_proc VARCHAR2(72) := g_package||'Get_category_name';
1896
1897 l_pr_dm_name hr_lookups.meaning%TYPE;
1898
1899 l_return_dm_name hr_lookups.meaning%TYPE := 'Classroom (physical)';
1900 l_return VARCHAR2(100);
1901 l_counter NUMBER := 0;
1902
1903 BEGIN
1904
1905 hr_utility.set_location('Entering :'||l_proc,5);
1906
1907 FOR primary_dm_rec IN get_primary_dm_cr
1908 LOOP
1909 -- bug#2652899
1910 l_pr_dm_name := primary_dm_rec.dm_name;
1911 -- bug # 2652899
1912 l_return_dm_name := primary_dm_rec.dm_name;
1913 END LOOP;
1914 IF l_pr_dm_name IS NULL THEN
1915 FOR first_dm_rec IN get_first_dm_cr
1916 LOOP
1917 l_counter := l_counter + 1;
1918 IF l_counter = 1 THEN
1919 l_return_dm_name := first_dm_rec.dm_name;
1920 END IF;
1921 END LOOP;
1922 END IF;
1923
1924 RETURN l_return_dm_name;
1925
1926 EXCEPTION
1927 WHEN others THEN
1928 hr_utility.set_location('Leaving :'||l_proc,15);
1929 RETURN l_return;
1930 END get_category_name;
1931
1932 -- ----------------------------------------------------------------
1933 -- ------------------<get_lo_offering_count >--------------------
1934 -- ----------------------------------------------------------------
1935 -- {Start of Comments}
1936 --
1937 -- Description:
1938 -- This function will be used to find the number of offerings for the particular
1939 -- learning object
1940 -- IN
1941 -- p_learning_object_id
1942 --
1943 -- OUT
1944 -- offering count
1945 -- Post Failure:
1946 -- None.
1947 -- Access Status
1948 -- Public
1949 -- {End of Comments}
1950 ------------------------------------------------------------------
1951 function get_lo_offering_count (p_learning_object_id in number) return varchar2
1952 IS
1953 l_offering_count number;
1954
1955 CURSOR c_get_offering_count IS
1956 SELECT count(*)
1957 FROM ota_offerings
1958 WHERE learning_object_id = p_learning_object_id;
1959
1960 BEGIN
1961 open c_get_offering_count;
1962 fetch c_get_offering_count into l_offering_count;
1963 close c_get_offering_count;
1964
1965 return(l_offering_count);
1966
1967 end get_lo_offering_count ;
1968
1969
1970 -- ----------------------------------------------------------------
1971 -- ------------------<get_course_offering_count >--------------------
1972 -- ----------------------------------------------------------------
1973 -- {Start of Comments}
1974 --
1975 -- Description:
1976 -- This function will be used to find the number of offerings for the particular
1977 -- course
1978 -- IN
1979 -- p_activity_version_id
1980 --
1981 -- OUT
1982 -- offering count
1983 -- Post Failure:
1984 -- None.
1985 -- Access Status
1986 -- Public
1987 -- {End of Comments}
1988 ------------------------------------------------------------------
1989 function get_course_offering_count (p_activity_version_id in number) return varchar2
1990 IS
1991 l_offering_count number;
1992
1993 CURSOR c_get_course_offering_count IS
1994 SELECT count(*)
1995 FROM ota_offerings
1996 WHERE activity_version_id = p_activity_version_id;
1997
1998 BEGIN
1999 open c_get_course_offering_count;
2000 fetch c_get_course_offering_count into l_offering_count;
2001 close c_get_course_offering_count;
2002
2003 return(l_offering_count);
2004
2005 end get_course_offering_count ;
2006
2007
2008 -- ----------------------------------------------------------------
2009 -- ------------------<get_iln_rco_id >--------------------
2010 -- ----------------------------------------------------------------
2011 -- {Start of Comments}
2012 --
2013 -- Description:
2014 -- This function will be used to find rco_id for course
2015 -- IN
2016 -- p_activity_version_id
2017 --
2018 -- OUT
2019 -- l_rco_id
2020 -- Post Failure:
2021 -- None.
2022 -- Access Status
2023 -- Public
2024 -- {End of Comments}
2025 ------------------------------------------------------------------
2026 function get_iln_rco_id (p_activity_version_id in number
2027 ) return varchar2
2028 IS
2029 l_rco_id number;
2030
2031 CURSOR get_iln_rco_id IS
2032 SELECT rco_id
2033 FROM ota_activity_versions
2034 WHERE activity_version_id = p_activity_version_id;
2035
2036
2037 BEGIN
2038 open get_iln_rco_id;
2039 fetch get_iln_rco_id into l_rco_id;
2040 close get_iln_rco_id;
2041
2042 return(l_rco_id);
2043
2044 end get_iln_rco_id ;
2045
2046
2047
2048 -- ----------------------------------------------------------------
2049 -- ------------------<get_event_count >--------------------
2050 -- ----------------------------------------------------------------
2051 -- {Start of Comments}
2052 --
2053 -- Description:
2054 -- This function will be used to find the number of of events the particular
2055 -- offering
2056 -- IN
2057 -- p_offering_id
2058 -- p_event_type
2059 --
2060 -- OUT
2061 -- event count
2062 -- Post Failure:
2063 -- None.
2064 -- Access Status
2065 -- Public
2066 -- {End of Comments}
2067 ------------------------------------------------------------------
2068 function get_event_count (p_offering_id in number,
2069 p_event_type in varchar2 default 'ALL') return varchar2
2070 IS
2071 l_event_count number;
2072
2073 CURSOR c_get_iln_event_count IS
2074 SELECT count(event_id)
2075 FROM ota_events
2076 WHERE parent_offering_id = p_offering_id and
2077 offering_id is not null;
2078
2079 CURSOR c_get_event_count IS
2080 SELECT count(event_id)
2081 FROM ota_events
2082 WHERE parent_offering_id = p_offering_id and
2083 event_type in ('SELFPACED','SCHEDULED') and
2084 book_independent_flag = 'N';
2085
2086 BEGIN
2087 IF p_event_type = 'ILN' THEN
2088 open c_get_iln_event_count;
2089 fetch c_get_iln_event_count into l_event_count;
2090 close c_get_iln_event_count;
2091 ELSE
2092 open c_get_event_count;
2093 fetch c_get_event_count into l_event_count;
2094 close c_get_event_count;
2095 END IF;
2096
2097 return(l_event_count);
2098
2099 end get_event_count ;
2100
2101
2102
2103 -- ----------------------------------------------------------------
2104 -- ------------------<get_question_bank_count >--------------------
2105 -- ----------------------------------------------------------------
2106 -- {Start of Comments}
2107 --
2108 -- Description:
2109 -- This function will be used to find the number of question banks related to particular
2110 -- folder
2111 -- IN
2112 -- p_folder_id
2113 --
2114 -- OUT
2115 -- question bank count
2116 -- Post Failure:
2117 -- None.
2118 -- Access Status
2119 -- Public
2120 -- {End of Comments}
2121 ------------------------------------------------------------------
2122 function get_question_bank_count (p_folder_id in number) return varchar2
2123 IS
2124 l_question_bank_count number;
2125
2126 CURSOR c_get_question_bank_count IS
2127 SELECT count(*)
2128 FROM ota_question_banks
2129 WHERE folder_id = p_folder_id;
2130
2131 BEGIN
2132 open c_get_question_bank_count;
2133 fetch c_get_question_bank_count into l_question_bank_count;
2134 close c_get_question_bank_count;
2135
2136 return(l_question_bank_count);
2137
2138 end get_question_bank_count ;
2139
2140 -- Author: sbhullar
2141 FUNCTION get_enrollment_status(p_delegate_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
2142 p_delegate_contact_id IN NUMBER,
2143 p_event_id IN ota_events.event_id%TYPE,
2144 p_code IN number)
2145 RETURN VARCHAR2 IS
2146
2147 CURSOR enroll_status IS
2148 SELECT DECODE(BST.type,'C','Y',BST.type) status, BST.name
2149 FROM ota_booking_status_types_vl BST,
2150 ota_delegate_bookings ODB
2151 WHERE ODB.event_id = p_event_id
2152 AND (p_delegate_person_id IS NOT NULL AND ODB.delegate_person_id = p_delegate_person_id
2153 OR p_delegate_contact_id IS NOT NULL and ODB.delegate_contact_id = p_delegate_contact_id)
2154 AND ODB.booking_status_type_id = BST.booking_status_type_id
2155 ORDER BY status;
2156
2157
2158 l_proc VARCHAR2(72) := g_package|| 'get_enrollment_status';
2159
2160 l_enrollment_status VARCHAR2(30) := 'Z'; --Default is Not Enrolled(Status Z)
2161 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
2162
2163 BEGIN
2164 hr_utility.set_location(' Step:'|| l_proc, 10);
2165
2166 --Default is Not Enrolled
2167 l_enrollment_status_name := get_message('OTA','OTA_443407_NOT_ENROLLED');
2168
2169 FOR rec IN enroll_status
2170 LOOP
2171 l_enrollment_status := rec.status ;
2172 l_enrollment_status_name := rec.name;
2173 EXIT;
2174 END LOOP;
2175
2176 If (p_code = 1) Then
2177 RETURN l_enrollment_status;
2178 Else
2179 RETURN l_enrollment_status_name;
2180 End If;
2181
2182 hr_utility.set_location(' Step:'|| l_proc, 20);
2183
2184 END get_enrollment_status;
2185
2186
2187 FUNCTION get_user_fullname(p_user_id IN ota_attempts.user_id%TYPE,
2188 p_user_type IN ota_attempts.user_type%TYPE)
2189
2190 RETURN VARCHAR2 IS
2191
2192 CURSOR c_person_fullname IS
2193 select
2194 p.full_name person_name
2195 from per_people_f p
2196 where
2197 p.person_id = p_user_id
2198 and sysdate between p.effective_start_date and p.effective_end_date;
2199
2200 CURSOR c_customer_fullname IS
2201 select
2202 p.party_name person_name
2203 from hz_parties p
2204 where
2205 p.party_id = p_user_id;
2206
2207 l_proc VARCHAR2(72) := g_package|| 'get_user_fullname';
2208 l_return per_all_people_f.full_name%TYPE;
2209 BEGIN
2210 hr_utility.set_location(' Step:'|| l_proc, 10);
2211 IF p_user_type = 'E' THEN
2212 OPEN c_person_fullname;
2213 FETCH c_person_fullname INTO l_return;
2214 CLOSE c_person_fullname;
2215 ELSIF p_user_type = 'C' THEN
2216 OPEN c_customer_fullname;
2217 FETCH c_customer_fullname INTO l_return;
2218 CLOSE c_customer_fullname;
2219 END IF;
2220
2221
2222 RETURN l_return;
2223
2224 hr_utility.set_location(' Step:'|| l_proc, 20);
2225
2226 END get_user_fullname;
2227
2228 FUNCTION get_person_fullname(p_user_id IN ota_attempts.user_id%TYPE
2229 )RETURN VARCHAR2 IS
2230
2231 CURSOR c_person_fullname IS
2232 select
2233 p.full_name person_name
2234 from per_people_f p , fnd_user fus
2235 where p.person_id = fus.employee_id
2236 and
2237 fus.user_id = p_user_id;
2238
2239
2240 l_proc VARCHAR2(72) := g_package|| 'get_person_fullname';
2241 l_return per_all_people_f.full_name%TYPE;
2242 BEGIN
2243 hr_utility.set_location(' Step:'|| l_proc, 10);
2244
2245 OPEN c_person_fullname;
2246 FETCH c_person_fullname INTO l_return;
2247 CLOSE c_person_fullname;
2248
2249
2250 RETURN l_return;
2251
2252 hr_utility.set_location(' Step:'|| l_proc, 20);
2253
2254 END get_person_fullname;
2255
2256 FUNCTION get_learner_name(p_person_id IN per_all_people_f.person_id%TYPE,
2257 p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2258 p_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2259 RETURN VARCHAR2 IS
2260
2261 CURSOR c_person_name IS
2262 select pap.full_name from per_all_people_f pap
2263 where pap.person_id = p_person_id
2264 and trunc(sysdate) between nvl(pap.effective_start_date, trunc(sysdate))
2265 and nvl(pap.effective_end_date, trunc(sysdate));
2266
2267 CURSOR c_contact_name(l_customer_id IN ota_delegate_bookings.customer_id%TYPE) IS
2268 SELECT
2269 SUBSTRB( PARTY.PERSON_LAST_NAME,1,50) || ' ' ||
2270 SUBSTRB( PARTY.PERSON_FIRST_NAME,1,40) || ' ' ||
2271 HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE',nvl(PARTY.PERSON_PRE_NAME_ADJUNCT,PARTY.PERSON_TITLE)) LEARNER_NAME
2272 FROM
2273 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
2274 HZ_PARTIES PARTY,
2275 HZ_RELATIONSHIPS REL,
2276 HZ_CUST_ACCOUNTS ROLE_ACCT
2277 WHERE
2278 ACCT_ROLE.PARTY_ID = REL.PARTY_ID
2279 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
2280 AND REL.SUBJECT_ID = PARTY.PARTY_ID
2281 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2282 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2283 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
2284 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
2285 AND ACCT_ROLE.CUST_ACCOUNT_ID = l_customer_id
2286 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id;
2287
2288 l_proc VARCHAR2(72) := g_package|| 'get_learner_name';
2289 l_return VARCHAR2(500);
2290 BEGIN
2291 hr_utility.set_location(' Step:'|| l_proc, 10);
2292
2293 IF (p_person_id is not null) THEN
2294 -- Internal Enrollment, Get Learner Name from per_all_people_f
2295 OPEN c_person_name;
2296 FETCH c_person_name INTO l_return;
2297 CLOSE c_person_name;
2298 ELSE
2299 -- External Enrollment. Get Learner Name from
2300 -- HZ Tables if contact_id is not null
2301 IF (p_contact_id is not null) THEN
2302 IF (p_customer_id is not null) THEN
2303 OPEN c_contact_name(p_customer_id);
2304 ELSE
2305 OPEN c_contact_name(get_customer_id(p_contact_id));
2306 END IF;
2307 FETCH c_contact_name INTO l_return;
2308 CLOSE c_contact_name;
2309 ELSE
2310 l_return := NULL;
2311 END IF;
2312 END IF;
2313
2314 return l_return;
2315
2316 hr_utility.set_location(' Step:'|| l_proc, 20);
2317
2318 EXCEPTION
2319 WHEN others THEN
2320 hr_utility.set_location('Leaving :'||l_proc,15);
2321 RETURN NULL;
2322 END get_learner_name;
2323
2324 FUNCTION get_customer_id(p_contact_id IN ota_lp_enrollments.contact_id%TYPE)
2325 RETURN number IS
2326
2327 CURSOR c_customer_id IS
2328 select ACCT_ROLE.cust_account_id CUSTOMER_ID
2329 from HZ_CUST_ACCOUNT_ROLES acct_role,
2330 HZ_PARTIES party,
2331 HZ_RELATIONSHIPS rel,
2332 HZ_ORG_CONTACTS org_cont,
2333 HZ_PARTIES rel_party,
2334 HZ_CUST_ACCOUNTS role_acct
2335 where acct_role.party_id = rel.party_id
2336 and acct_role.role_type = 'CONTACT'
2337 and org_cont.party_relationship_id = rel.relationship_id
2338 and rel.subject_id = party.party_id
2339 and rel.party_id = rel_party.party_id
2340 and rel.subject_table_name = 'HZ_PARTIES'
2341 and rel.object_table_name = 'HZ_PARTIES'
2342 and acct_role.cust_account_id = role_acct.cust_account_id
2343 and role_acct.party_id = rel.object_id
2344 and ACCT_ROLE.cust_account_role_id = p_contact_id;
2345
2346 l_proc VARCHAR2(72) := g_package|| 'get_customer_id';
2347 l_return ota_delegate_bookings.customer_id%TYPE;
2348
2349 Begin
2350 hr_utility.set_location(' Step:'|| l_proc, 10);
2351
2352 OPEN c_customer_id;
2353 FETCH c_customer_id INTO l_return;
2354 CLOSE c_customer_id;
2355
2356 return l_return;
2357 hr_utility.set_location(' Step:'|| l_proc, 20);
2358 End get_customer_id;
2359
2360 FUNCTION get_cust_org_name(p_organization_id IN ota_delegate_bookings.organization_id%TYPE,
2361 p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2362 p_contact_id IN ota_lp_enrollments.contact_id%TYPE default null)
2363 RETURN VARCHAR2 IS
2364
2365 CURSOR c_organization_name IS
2366 select name from hr_all_organization_units_tl
2367 where language = userenv('LANG') and organization_id = p_organization_id;
2368
2369 CURSOR c_customer_name(l_customer_id IN ota_delegate_bookings.customer_id%TYPE) IS
2370 select substrb(party.party_name,1,50)
2371 from hz_parties party
2372 ,hz_cust_accounts cust_acct
2373 where
2374 cust_acct.party_id = party.party_id
2375 and cust_acct.cust_account_id = l_customer_id;
2376
2377 l_proc VARCHAR2(72) := g_package|| 'get_cust_org_name';
2378 l_return VARCHAR2(500);
2379 BEGIN
2380 hr_utility.set_location(' Step:'|| l_proc, 10);
2381
2382 IF (p_organization_id is not null) THEN
2383 -- Internal Enrollment, Get org_name from hr_all_organization_units
2384 OPEN c_organization_name;
2385 FETCH c_organization_name INTO l_return;
2386 CLOSE c_organization_name;
2387 ELSIF (p_customer_id is not null) THEN
2388 -- External Enrollment. Get Customer Name from HZ Tables
2389 -- p_customer_id is already known
2390 OPEN c_customer_name(p_customer_id);
2391 FETCH c_customer_name INTO l_return;
2392 CLOSE c_customer_name;
2393 ELSE
2394 -- External Enrollment. Get Customer Name from HZ Tables
2395 -- Get p_customer_id from p_contact_id
2396 OPEN c_customer_name(get_customer_id(p_contact_id));
2397 FETCH c_customer_name INTO l_return;
2398 CLOSE c_customer_name;
2399 END IF;
2400
2401 return l_return;
2402
2403 hr_utility.set_location(' Step:'|| l_proc, 20);
2404
2405 EXCEPTION
2406 WHEN others THEN
2407 hr_utility.set_location('Leaving :'||l_proc,15);
2408 RETURN NULL;
2409 END get_cust_org_name;
2410
2411
2412 -- ----------------------------------------------------------------
2413 -- ------------------<get_catalog_object_path >--------------------
2414 -- ----------------------------------------------------------------
2415 Procedure get_catalog_object_path (p_cat_id varchar2, p_path OUT NOCOPY varchar2)
2416 IS
2417 parent_id ota_category_usages.parent_cat_usage_id%TYPE;
2418 full_path varchar2(1000) :=null;
2419 current_cat_usage_id ota_category_usages.category_usage_id%TYPE := p_cat_id;
2420 l_proc VARCHAR2(72) := g_package||'get_catalog_object_path';
2421
2422 Cursor c_parent_cat_id(current_cat_usage_id IN VARCHAR2)
2423 IS
2424 select nvl(parent_cat_usage_id,-1)
2425 from ota_category_usages
2426 where category_usage_id = current_cat_usage_id;
2427
2428 Begin
2429 loop
2430 OPEN c_parent_cat_id(current_cat_usage_id);
2431 FETCH c_parent_cat_id INTO parent_id;
2432 CLOSE c_parent_cat_id;
2433
2434 full_path := 'CAT' || parent_id || '.' ||full_path;
2435 current_cat_usage_id := parent_id;
2436
2437 exit when parent_id = -1;
2438 end loop;
2439
2440 p_path := full_path;
2441
2442 EXCEPTION
2443 WHEN others THEN
2444 hr_utility.set_location('Leaving :'||l_proc,15);
2445 p_path := null;
2446 End get_catalog_object_path ;
2447
2448
2449 -- ----------------------------------------------------------------
2450 -- ------------------<get_content_object_path >--------------------
2451 -- ----------------------------------------------------------------
2452 Procedure get_content_object_path (p_obj_id varchar2,p_obj_type varchar2, p_path OUT NOCOPY varchar2)
2453 IS
2454 parent_obj_id ota_lo_folders.folder_id%TYPE;
2455 full_path varchar2(1000) :=null;
2456 current_obj_id ota_lo_folders.folder_id%TYPE := p_obj_id;
2457 l_proc VARCHAR2(72) := g_package||'get_content_object_path';
2458
2459 Cursor c_parent_lo_id(current_lo_id IN VARCHAR2)
2460 IS
2461 select nvl(parent_learning_object_id,-1)
2462 from ota_learning_objects
2463 where learning_object_id = current_lo_id;
2464
2465 Cursor c_lo_folder_id(current_lo_id IN VARCHAR2)
2466 IS
2467 select folder_id
2468 from ota_learning_objects
2469 where learning_object_id = current_lo_id;
2470
2471 Cursor c_parent_folder_id(current_folder_id IN VARCHAR2)
2472 IS
2473 select nvl(parent_folder_id,-1)
2474 from ota_lo_folders
2475 where folder_id = current_folder_id;
2476
2477 Begin
2478 if (p_obj_type = 'L') then
2479 loop
2480 OPEN c_parent_lo_id(current_obj_id);
2481 FETCH c_parent_lo_id INTO parent_obj_id;
2482 CLOSE c_parent_lo_id;
2483
2484 if (parent_obj_id <> -1) then
2485 full_path := 'L' || parent_obj_id || '.' ||full_path;
2486 current_obj_id := parent_obj_id;
2487 end if;
2488
2489 if (parent_obj_id = -1) then
2490 OPEN c_lo_folder_id(current_obj_id);
2491 FETCH c_lo_folder_id INTO current_obj_id;
2492 CLOSE c_lo_folder_id;
2493 full_path := 'F' || current_obj_id || '.' ||full_path;
2494 end if;
2495
2496 exit when parent_obj_id = -1;
2497 end loop;
2498
2499 end if;
2500
2501
2502 loop
2503 OPEN c_parent_folder_id(current_obj_id);
2504 FETCH c_parent_folder_id INTO parent_obj_id;
2505 CLOSE c_parent_folder_id;
2506
2507 full_path := 'F' || parent_obj_id || '.' ||full_path;
2508 current_obj_id := parent_obj_id;
2509
2510 exit when parent_obj_id = -1;
2511 end loop;
2512
2513 p_path := full_path;
2514
2515 EXCEPTION
2516 WHEN others THEN
2517 hr_utility.set_location('Leaving :'||l_proc,15);
2518 p_path := null;
2519 End get_content_object_path ;
2520
2521 -- ----------------------------------------------------------------
2522 -- ------------------<check_function_access >--------------------
2523 -- ----------------------------------------------------------------
2524 -- {Start of Comments}
2525 --
2526 -- Description:
2527 -- This function will be used to find out if the user logged in has access to the function
2528 -- IN
2529 -- p_function_name
2530 --
2531 -- OUT
2532 -- T for True
2533 -- F for False
2534 -- Post Failure:
2535 -- None.
2536 -- Access Status
2537 -- Public
2538 -- {End of Comments}
2539 ------------------------------------------------------------------
2540 function check_function_access (p_function_name in VARCHAR2)
2541 RETURN varchar2
2542 IS
2543 l_return VARCHAR2(1):= 'F';
2544 l_proc VARCHAR2(72) := g_package||'check_function_access';
2545
2546 BEGIN
2547 hr_utility.set_location('Entering :'||l_proc,5);
2548
2549 IF fnd_function.test_instance(function_name => p_function_name) THEN
2550 l_return := 'T';
2551 ELSE
2552 l_return := 'F';
2553 END IF;
2554 RETURN l_return;
2555
2556 hr_utility.set_location('Leaving :'||l_proc,10);
2557 EXCEPTION
2558 WHEN others THEN
2559 l_return := 'F';
2560 RETURN l_return;
2561
2562 END check_function_access;
2563
2564 -- ----------------------------------------------------------------
2565 -- ------------------< get_event_status_code >---------------------
2566 -- ----------------------------------------------------------------
2567 -- {Start of Comments}
2568 --
2569 -- Description:
2570 -- This function will be used to find the event status code for
2571 -- an event
2572 -- IN
2573 -- p_event_id
2574 --
2575 -- OUT
2576 -- returns event status code
2577 -- Post Failure:
2578 -- None.
2579 -- Access Status
2580 -- Public
2581 -- {End of Comments}
2582 ------------------------------------------------------------------
2583 Function get_event_status_code (p_event_id in ota_events.event_id%TYPE)
2584 return varchar2
2585 IS
2586 l_event_status_code ota_events.event_status%TYPE;
2587
2588 CURSOR c_get_event_status_code
2589 IS
2590 SELECT event_status from ota_events
2591 where
2592 event_id = p_event_id;
2593
2594 Begin
2595 OPEN c_get_event_status_code;
2596 FETCH c_get_event_status_code INTO l_event_status_code;
2597 CLOSE c_get_event_status_code;
2598 RETURN(l_event_status_code);
2599
2600 End get_event_status_code ;
2601
2602 -- ----------------------------------------------------------------
2603 -- ----------------------< is_applicant >--------------------------
2604 -- ----------------------------------------------------------------
2605 -- {Start of Comments}
2606 --
2607 -- Description:
2608 -- This function will be used to find whether person is
2609 -- applicant or not
2610 -- IN
2611 -- p_person_id
2612 --
2613 -- OUT
2614 -- returns Y or N
2615 -- Post Failure:
2616 -- None.
2617 -- Access Status
2618 -- Public
2619 -- {End of Comments}
2620 ------------------------------------------------------------------
2621 Function is_applicant (p_person_id IN per_all_people_f.person_id%TYPE)
2622 return varchar2 is
2623
2624 cursor get_person_type is
2625 SELECT ppt.system_person_type
2626 FROM per_all_people_f per,
2627 per_person_type_usages_f ptu,
2628 per_person_types ppt
2629 WHERE
2630 per.person_id = p_person_id
2631 AND ptu.person_id = per.person_id
2632 AND ppt.business_group_id = per.business_group_id
2633 AND ptu.person_type_id = ppt.person_type_id
2634 AND trunc(sysdate) between per.effective_start_date AND per.effective_end_date
2635 AND trunc(sysdate) between ptu.effective_start_date AND ptu.effective_end_date
2636 AND ppt.system_person_type <> 'APL'
2637 AND ppt.system_person_type in ('EMP','CWK');
2638
2639 l_system_person_type per_person_types.system_person_type%TYPE;
2640
2641 Begin
2642 Open get_person_type;
2643 Fetch get_person_type into l_system_person_type;
2644 /*
2645 Close get_person_type;
2646
2647 if ( l_system_person_type = 'APL' ) then
2648 return 'Y';
2649 else
2650 return 'N';
2651 */
2652 IF get_person_type%FOUND THEN
2653 CLOSE get_person_type;
2654 return 'N';
2655 ELSE
2656 CLOSE get_person_type;
2657 RETURN 'Y';
2658 end if;
2659 End is_applicant;
2660
2661 -- ----------------------------------------------------------------
2662 -- -------------------< get_ext_lrnr_party_id >--------------------
2663 -- ----------------------------------------------------------------
2664 -- {Start of Comments}
2665 --
2666 -- Description:
2667 -- This function will be used to fetch the party id for external learner
2668 -- IN
2669 -- p_delegate_contact_id
2670 --
2671 -- OUT
2672 -- returns party id
2673 -- Post Failure:
2674 -- None.
2675 -- Access Status
2676 -- Public
2677 -- {End of Comments}
2678 ------------------------------------------------------------------
2679 FUNCTION get_ext_lrnr_party_id
2680 (p_delegate_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2681 RETURN number IS
2682
2683 Cursor get_ext_lrn_party_id is
2684 select party.party_id
2685 from HZ_CUST_ACCOUNT_ROLES acct_role,
2686 HZ_PARTIES party,
2687 HZ_RELATIONSHIPS rel,
2688 HZ_ORG_CONTACTS org_cont,
2689 HZ_PARTIES rel_party,
2690 HZ_CUST_ACCOUNTS role_acct
2691 where acct_role.party_id = rel.party_id
2692 and acct_role.role_type = 'CONTACT'
2693 and org_cont.party_relationship_id = rel.relationship_id
2694 and rel.subject_id = party.party_id
2695 and rel.party_id = rel_party.party_id
2696 and rel.subject_table_name = 'HZ_PARTIES'
2697 and rel.object_table_name = 'HZ_PARTIES'
2698 and acct_role.cust_account_id = role_acct.cust_account_id
2699 and role_acct.party_id = rel.object_id
2700 and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
2701
2702 l_proc VARCHAR2(72) := g_package|| 'get_ext_lrnr_party_id';
2703 l_return number;
2704
2705 Begin
2706 hr_utility.set_location(' Step:'|| l_proc, 10);
2707
2708 OPEN get_ext_lrn_party_id;
2709 FETCH get_ext_lrn_party_id INTO l_return;
2710 CLOSE get_ext_lrn_party_id;
2711
2712 return l_return;
2713 hr_utility.set_location(' Step:'|| l_proc, 20);
2714 End get_ext_lrnr_party_id;
2715
2716 FUNCTION is_class_enrollable(
2717 p_class_id ota_events.event_id%TYPE)
2718 RETURN VARCHAR2
2719 IS
2720 --6762989 Added nvl check on enrolment_start_date to avoid java.sql.SQLException: ORA-01843: not a valid month on some dbs.
2721 CURSOR csr_get_class_details IS
2722 SELECT null
2723 FROM ota_events
2724 WHERE event_id = p_class_id
2725 --AND trunc(sysdate) between enrolment_start_date and nvl(enrolment_end_date, trunc(sysdate))
2726 AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, timezone)
2727 BETWEEN to_date(to_char(nvl(enrolment_start_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
2728 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')
2729 AND event_type IN ('SCHEDULED', 'SELFPACED')
2730 AND event_status IN ('N', 'P', 'F');
2731
2732 l_exists VARCHAR2(1);
2733 BEGIN
2734 OPEN csr_get_class_details;
2735 FETCH csr_get_class_details INTO l_exists;
2736 IF csr_get_class_details%NOTFOUND THEN
2737 CLOSE csr_get_class_details;
2738 RETURN 'N';
2739 ELSE
2740 CLOSE csr_get_class_details;
2741 RETURN 'Y';
2742 END IF;
2743 END is_class_enrollable;
2744
2745 FUNCTION is_lp_enrollable(
2746 p_learning_path_id ota_learning_paths.learning_path_id%TYPE)
2747 RETURN VARCHAR2
2748 IS
2749 CURSOR csr_get_lp_details IS
2750 SELECT null
2751 FROM ota_learning_paths
2752 WHERE learning_path_id = p_learning_path_id
2753 AND trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
2754
2755 CURSOR csr_sections_exist IS
2756 SELECT null
2757 from ota_lp_sections lpc,
2758 ota_learning_path_members lpm
2759 where lpc.learning_path_id = p_learning_path_id
2760 and lpc.learning_path_section_id = lpm.learning_path_section_id
2761 and lpc.completion_type_code in ('M','S');
2762
2763 l_exists VARCHAR2(1);
2764 BEGIN
2765
2766 OPEN csr_get_lp_details;
2767 FETCH csr_get_lp_details INTO l_exists;
2768 IF csr_get_lp_details%NOTFOUND THEN
2769 CLOSE csr_get_lp_details;
2770 RETURN 'N';
2771 ELSE
2772 CLOSE csr_get_lp_details;
2773
2774 OPEN csr_sections_exist;
2775 FETCH csr_sections_exist INTO l_exists;
2776 IF csr_sections_exist%NOTFOUND THEN
2777 CLOSE csr_sections_exist;
2778 RETURN 'N';
2779 ELSE
2780 CLOSE csr_sections_exist;
2781 RETURN 'Y';
2782 END IF;
2783 END IF;
2784 END is_lp_enrollable;
2785
2786 FUNCTION is_cert_enrollable(
2787 p_certification_id ota_certifications_b.certification_id%TYPE)
2788 RETURN VARCHAR2
2789 IS
2790 CURSOR csr_get_cert_details IS
2791 SELECT null
2792 FROM ota_certifications_b crb
2793 , ota_certification_members crm
2794 WHERE crb.certification_id = crm.certification_id
2795 AND crb.certification_id = p_certification_id
2796 AND trunc(sysdate) between crb.start_date_active and nvl(crb.end_date_active, trunc(sysdate))
2797 -- Added for bug#4617609, modified for 4940007
2798 and ((crb.renewable_flag = 'N' and trunc(sysdate) <= nvl( crb.INITIAL_COMPLETION_DATE ,trunc(sysdate)))
2799 or crb.renewable_flag = 'Y');
2800
2801
2802 l_exists VARCHAR2(1);
2803 BEGIN
2804 OPEN csr_get_cert_details;
2805 FETCH csr_get_cert_details INTO l_exists;
2806 IF csr_get_cert_details%NOTFOUND THEN
2807 CLOSE csr_get_cert_details;
2808 RETURN 'N';
2809 ELSE
2810 CLOSE csr_get_cert_details;
2811 RETURN 'Y';
2812 END IF;
2813 END is_cert_enrollable;
2814
2815 FUNCTION is_enrollable
2816 ( p_object_type in varchar2
2817 ,p_object_id in number
2818 )
2819 RETURN varchar2
2820 IS
2821 BEGIN
2822 IF p_object_type = 'CL' THEN
2823 RETURN is_class_enrollable(p_object_id);
2824 ELSIF p_object_type = 'LP' THEN
2825 RETURN is_lp_enrollable(p_object_id);
2826 ELSIF p_object_type = 'CRT' THEN
2827 RETURN is_cert_enrollable(p_object_id);
2828 ELSE
2829 RETURN 'N';
2830 END IF;
2831 END is_enrollable;
2832
2833
2834 PROCEDURE Get_Default_Value_Dff(
2835 appl_short_name IN VARCHAR2,
2836 flex_field_name IN VARCHAR2,
2837 p_attribute_category IN OUT NOCOPY VARCHAR2,
2838 p_attribute1 IN OUT NOCOPY VARCHAR2,
2839 p_attribute2 IN OUT NOCOPY VARCHAR2,
2840 p_attribute3 IN OUT NOCOPY VARCHAR2,
2841 p_attribute4 IN OUT NOCOPY VARCHAR2,
2842 p_attribute5 IN OUT NOCOPY VARCHAR2,
2843 p_attribute6 IN OUT NOCOPY VARCHAR2,
2844 p_attribute7 IN OUT NOCOPY VARCHAR2,
2845 p_attribute8 IN OUT NOCOPY VARCHAR2,
2846 p_attribute9 IN OUT NOCOPY VARCHAR2,
2847 p_attribute10 IN OUT NOCOPY VARCHAR2,
2848 p_attribute11 IN OUT NOCOPY VARCHAR2,
2849 p_attribute12 IN OUT NOCOPY VARCHAR2,
2850 p_attribute13 IN OUT NOCOPY VARCHAR2,
2851 p_attribute14 IN OUT NOCOPY VARCHAR2,
2852 p_attribute15 IN OUT NOCOPY VARCHAR2,
2853 p_attribute16 IN OUT NOCOPY VARCHAR2,
2854 p_attribute17 IN OUT NOCOPY VARCHAR2,
2855 p_attribute18 IN OUT NOCOPY VARCHAR2,
2856 p_attribute19 IN OUT NOCOPY VARCHAR2,
2857 p_attribute20 IN OUT NOCOPY VARCHAR2)
2858 IS
2859 p_flexfield fnd_dflex.dflex_r;
2860 p_flexinfo fnd_dflex.dflex_dr;
2861 p_contexts fnd_dflex.contexts_dr;
2862 p_segments fnd_dflex.segments_dr;
2863 j BINARY_INTEGER;
2864 i BINARY_INTEGER;
2865 k BINARY_INTEGER;
2866
2867
2868 l_appl_col_name varchar2(50);
2869 firstchar number;
2870 tempstr varchar2(2);
2871
2872 PROCEDURE Get_Flexfield
2873 IS
2874 BEGIN
2875 fnd_dflex.get_flexfield( appl_short_name, flex_field_name, p_flexfield, p_flexinfo );
2876 END Get_Flexfield;
2877
2878 PROCEDURE Get_Contexts
2879 IS
2880 BEGIN
2881 fnd_dflex.get_contexts( p_flexfield, p_contexts );
2882 END Get_Contexts;
2883
2884
2885 BEGIN
2886 IF p_attribute_category IS NOT NULL
2887 OR p_attribute1 IS NOT NULL
2888 OR p_attribute2 IS NOT NULL
2889 OR p_attribute3 IS NOT NULL
2890 OR p_attribute4 IS NOT NULL
2891 OR p_attribute5 IS NOT NULL
2892 OR p_attribute6 IS NOT NULL
2893 OR p_attribute7 IS NOT NULL
2894 OR p_attribute8 IS NOT NULL
2895 OR p_attribute9 IS NOT NULL
2896 OR p_attribute10 IS NOT NULL
2897 OR p_attribute11 IS NOT NULL
2898 OR p_attribute12 IS NOT NULL
2899 OR p_attribute13 IS NOT NULL
2900 OR p_attribute14 IS NOT NULL
2901 OR p_attribute15 IS NOT NULL
2902 OR p_attribute16 IS NOT NULL
2903 OR p_attribute17 IS NOT NULL
2904 OR p_attribute18 IS NOT NULL
2905 OR p_attribute19 IS NOT NULL
2906 OR p_attribute20 IS NOT NULL THEN RETURN;
2907
2908 END IF;
2909
2910 Get_Flexfield;
2911 GET_CONTEXTS;
2912
2913 p_attribute_category := p_flexinfo.default_context_value;
2914
2915 FOR k in 1 .. p_contexts.ncontexts LOOP
2916
2917 IF p_contexts.is_global(k)
2918 OR p_contexts.context_code(k) = p_flexinfo.default_context_value THEN
2919
2920 fnd_dflex.Get_Segments( fnd_dflex.make_context(p_flexfield,p_contexts.context_code(k))
2921 ,p_segments
2922 ,TRUE);
2923
2924 FOR j IN 1 .. p_segments.nsegments LOOP
2925
2926 l_appl_col_name := p_segments.application_column_name(j);
2927 tempstr := substr(l_appl_col_name, length(l_appl_col_name) -1);
2928 firstchar := ascii(substr(tempstr,1,2));
2929
2930 IF firstchar < 49 OR firstchar > 57 THEN
2931 i := to_number(substr(tempstr,2,1));
2932 ELSE
2933 i := to_number(tempstr);
2934 END IF;
2935
2936 IF i = 1 THEN
2937 p_attribute1 := p_segments.default_value(j);
2938 ELSIF i = 2 THEN
2939 p_attribute2 := p_segments.default_value(j);
2940 ELSIF i = 3 THEN
2941 p_attribute3 := p_segments.default_value(j);
2942 ELSIF i = 4 THEN
2943 p_attribute4 := p_segments.default_value(j);
2944 ELSIF i = 5 THEN
2945 p_attribute5 := p_segments.default_value(j);
2946 ELSIF i = 6 THEN
2947 p_attribute6 := p_segments.default_value(j);
2948 ELSIF i = 7 THEN
2949 p_attribute7 := p_segments.default_value(j);
2950 ELSIF i = 8 THEN
2951 p_attribute8 := p_segments.default_value(j);
2952 ELSIF i = 9 THEN
2953 p_attribute9 := p_segments.default_value(j);
2954 ELSIF i = 10 THEN
2955 p_attribute10 := p_segments.default_value(j);
2956 ELSIF i = 11 THEN
2957 p_attribute11 := p_segments.default_value(j);
2958 ELSIF i = 12 THEN
2959 p_attribute12 := p_segments.default_value(j);
2960 ELSIF i = 13 THEN
2961 p_attribute13 := p_segments.default_value(j);
2962 ELSIF i = 14 THEN
2963 p_attribute14 := p_segments.default_value(j);
2964 ELSIF i = 15 THEN
2965 p_attribute15 := p_segments.default_value(j);
2966 ELSIF i = 16 THEN
2967 p_attribute16 := p_segments.default_value(j);
2968 ELSIF i = 17 THEN
2969 p_attribute17 := p_segments.default_value(j);
2970 ELSIF i = 18 THEN
2971 p_attribute18 := p_segments.default_value(j);
2972 ELSIF i = 19 THEN
2973 p_attribute19 := p_segments.default_value(j);
2974 ELSIF i = 20 THEN
2975 p_attribute20 := p_segments.default_value(j);
2976 END IF;
2977 END LOOP;
2978 END IF;
2979
2980 END LOOP;
2981 /*
2982 dbms_output.put_line('Attribute cate '||p_attribute_category);
2983
2984 dbms_output.put_line('Attribute 1 '||p_attribute1);
2985 dbms_output.put_line('Attribute 2 '||p_attribute2);
2986 dbms_output.put_line('Attribute 3 '||p_attribute3);
2987 dbms_output.put_line('Attribute 4 '||p_attribute4);
2988 dbms_output.put_line('Attribute 5 '||p_attribute5);
2989 dbms_output.put_line('Attribute 6 '||p_attribute6);
2990 dbms_output.put_line('Attribute 7 '||p_attribute7);
2991 dbms_output.put_line('Attribute 8 '||p_attribute8);
2992 dbms_output.put_line('Attribute 9 '||p_attribute9);
2993 dbms_output.put_line('Attribute 10 '||p_attribute10);
2994 dbms_output.put_line('Attribute 11 '||p_attribute11);
2995 dbms_output.put_line('Attribute 12 '||p_attribute12);
2996 dbms_output.put_line('Attribute 13 '||p_attribute13);
2997 dbms_output.put_line('Attribute 14 '||p_attribute14);
2998 dbms_output.put_line('Attribute 15 '||p_attribute15);
2999 dbms_output.put_line('Attribute 16 '||p_attribute16);
3000 dbms_output.put_line('Attribute 17 '||p_attribute17);
3001 dbms_output.put_line('Attribute 18 '||p_attribute18);
3002 dbms_output.put_line('Attribute 19 '||p_attribute19);
3003 dbms_output.put_line('Attribute 20 '||p_attribute20);
3004 */
3005 END Get_Default_Value_Dff;
3006
3007 PROCEDURE Get_Default_Value_Dff(
3008 appl_short_name IN VARCHAR2,
3009 flex_field_name IN VARCHAR2,
3010 p_attribute_category IN OUT NOCOPY VARCHAR2,
3011 p_attribute1 IN OUT NOCOPY VARCHAR2,
3012 p_attribute2 IN OUT NOCOPY VARCHAR2,
3013 p_attribute3 IN OUT NOCOPY VARCHAR2,
3014 p_attribute4 IN OUT NOCOPY VARCHAR2,
3015 p_attribute5 IN OUT NOCOPY VARCHAR2,
3016 p_attribute6 IN OUT NOCOPY VARCHAR2,
3017 p_attribute7 IN OUT NOCOPY VARCHAR2,
3018 p_attribute8 IN OUT NOCOPY VARCHAR2,
3019 p_attribute9 IN OUT NOCOPY VARCHAR2,
3020 p_attribute10 IN OUT NOCOPY VARCHAR2,
3021 p_attribute11 IN OUT NOCOPY VARCHAR2,
3022 p_attribute12 IN OUT NOCOPY VARCHAR2,
3023 p_attribute13 IN OUT NOCOPY VARCHAR2,
3024 p_attribute14 IN OUT NOCOPY VARCHAR2,
3025 p_attribute15 IN OUT NOCOPY VARCHAR2,
3026 p_attribute16 IN OUT NOCOPY VARCHAR2,
3027 p_attribute17 IN OUT NOCOPY VARCHAR2,
3028 p_attribute18 IN OUT NOCOPY VARCHAR2,
3029 p_attribute19 IN OUT NOCOPY VARCHAR2,
3030 p_attribute20 IN OUT NOCOPY VARCHAR2,
3031 p_attribute21 IN OUT NOCOPY VARCHAR2,
3032 p_attribute22 IN OUT NOCOPY VARCHAR2,
3033 p_attribute23 IN OUT NOCOPY VARCHAR2,
3034 p_attribute24 IN OUT NOCOPY VARCHAR2,
3035 p_attribute25 IN OUT NOCOPY VARCHAR2,
3036 p_attribute26 IN OUT NOCOPY VARCHAR2,
3037 p_attribute27 IN OUT NOCOPY VARCHAR2,
3038 p_attribute28 IN OUT NOCOPY VARCHAR2,
3039 p_attribute29 IN OUT NOCOPY VARCHAR2,
3040 p_attribute30 IN OUT NOCOPY VARCHAR2)
3041
3042 IS
3043 p_flexfield fnd_dflex.dflex_r;
3044 p_flexinfo fnd_dflex.dflex_dr;
3045 p_contexts fnd_dflex.contexts_dr;
3046 p_segments fnd_dflex.segments_dr;
3047 j BINARY_INTEGER;
3048 i BINARY_INTEGER;
3049 k BINARY_INTEGER;
3050
3051
3052 l_appl_col_name varchar2(50);
3053 firstchar number;
3054 tempstr varchar2(2);
3055
3056 PROCEDURE Get_Flexfield
3057 IS
3058 BEGIN
3059 fnd_dflex.get_flexfield( appl_short_name, flex_field_name, p_flexfield, p_flexinfo );
3060 END Get_Flexfield;
3061
3062 PROCEDURE Get_Contexts
3063 IS
3064 BEGIN
3065 fnd_dflex.get_contexts( p_flexfield, p_contexts );
3066 END Get_Contexts;
3067
3068
3069 BEGIN
3070 IF p_attribute_category IS NOT NULL
3071 OR p_attribute1 IS NOT NULL
3072 OR p_attribute2 IS NOT NULL
3073 OR p_attribute3 IS NOT NULL
3074 OR p_attribute4 IS NOT NULL
3075 OR p_attribute5 IS NOT NULL
3076 OR p_attribute6 IS NOT NULL
3077 OR p_attribute7 IS NOT NULL
3078 OR p_attribute8 IS NOT NULL
3079 OR p_attribute9 IS NOT NULL
3080 OR p_attribute10 IS NOT NULL
3081 OR p_attribute11 IS NOT NULL
3082 OR p_attribute12 IS NOT NULL
3083 OR p_attribute13 IS NOT NULL
3084 OR p_attribute14 IS NOT NULL
3085 OR p_attribute15 IS NOT NULL
3086 OR p_attribute16 IS NOT NULL
3087 OR p_attribute17 IS NOT NULL
3088 OR p_attribute18 IS NOT NULL
3089 OR p_attribute19 IS NOT NULL
3090 OR p_attribute20 IS NOT NULL
3091 OR p_attribute21 IS NOT NULL
3092 OR p_attribute22 IS NOT NULL
3093 OR p_attribute23 IS NOT NULL
3094 OR p_attribute24 IS NOT NULL
3095 OR p_attribute25 IS NOT NULL
3096 OR p_attribute26 IS NOT NULL
3097 OR p_attribute27 IS NOT NULL
3098 OR p_attribute28 IS NOT NULL
3099 OR p_attribute29 IS NOT NULL
3100 OR p_attribute30 IS NOT NULL THEN RETURN;
3101
3102 END IF;
3103
3104
3105 Get_Flexfield;
3106 GET_CONTEXTS;
3107
3108 p_attribute_category := p_flexinfo.default_context_value;
3109
3110 FOR k in 1 .. p_contexts.ncontexts LOOP
3111
3112 IF p_contexts.is_global(k)
3113 OR p_contexts.context_code(k) = p_flexinfo.default_context_value THEN
3114
3115 fnd_dflex.Get_Segments( fnd_dflex.make_context(p_flexfield,p_contexts.context_code(k))
3116 ,p_segments
3117 ,TRUE);
3118
3119 FOR j IN 1 .. p_segments.nsegments LOOP
3120
3121 l_appl_col_name := p_segments.application_column_name(j);
3122 tempstr := substr(l_appl_col_name, length(l_appl_col_name) -1);
3123 firstchar := ascii(substr(tempstr,1,2));
3124
3125 IF firstchar < 49 OR firstchar > 57 THEN
3126 i := to_number(substr(tempstr,2,1));
3127 ELSE
3128 i := to_number(tempstr);
3129 END IF;
3130
3131 IF i = 1 THEN
3132 p_attribute1 := p_segments.default_value(j);
3133 ELSIF i = 2 THEN
3134 p_attribute2 := p_segments.default_value(j);
3135 ELSIF i = 3 THEN
3136 p_attribute3 := p_segments.default_value(j);
3137 ELSIF i = 4 THEN
3138 p_attribute4 := p_segments.default_value(j);
3139 ELSIF i = 5 THEN
3140 p_attribute5 := p_segments.default_value(j);
3141 ELSIF i = 6 THEN
3142 p_attribute6 := p_segments.default_value(j);
3143 ELSIF i = 7 THEN
3144 p_attribute7 := p_segments.default_value(j);
3145 ELSIF i = 8 THEN
3146 p_attribute8 := p_segments.default_value(j);
3147 ELSIF i = 9 THEN
3148 p_attribute9 := p_segments.default_value(j);
3149 ELSIF i = 10 THEN
3150 p_attribute10 := p_segments.default_value(j);
3151 ELSIF i = 11 THEN
3152 p_attribute11 := p_segments.default_value(j);
3153 ELSIF i = 12 THEN
3154 p_attribute12 := p_segments.default_value(j);
3155 ELSIF i = 13 THEN
3156 p_attribute13 := p_segments.default_value(j);
3157 ELSIF i = 14 THEN
3158 p_attribute14 := p_segments.default_value(j);
3159 ELSIF i = 15 THEN
3160 p_attribute15 := p_segments.default_value(j);
3161 ELSIF i = 16 THEN
3162 p_attribute16 := p_segments.default_value(j);
3163 ELSIF i = 17 THEN
3164 p_attribute17 := p_segments.default_value(j);
3165 ELSIF i = 18 THEN
3166 p_attribute18 := p_segments.default_value(j);
3167 ELSIF i = 19 THEN
3168 p_attribute19 := p_segments.default_value(j);
3169 ELSIF i = 20 THEN
3170 p_attribute20 := p_segments.default_value(j);
3171 ELSIF i = 21 THEN
3172 p_attribute21 := p_segments.default_value(j);
3173 ELSIF i = 22 THEN
3174 p_attribute22 := p_segments.default_value(j);
3175 ELSIF i = 23 THEN
3176 p_attribute23 := p_segments.default_value(j);
3177 ELSIF i = 24 THEN
3178 p_attribute24 := p_segments.default_value(j);
3179 ELSIF i = 25 THEN
3180 p_attribute25 := p_segments.default_value(j);
3181 ELSIF i = 26 THEN
3182 p_attribute26 := p_segments.default_value(j);
3183 ELSIF i = 27 THEN
3184 p_attribute27 := p_segments.default_value(j);
3185 ELSIF i = 28 THEN
3186 p_attribute28 := p_segments.default_value(j);
3187 ELSIF i = 29 THEN
3188 p_attribute29 := p_segments.default_value(j);
3189 ELSIF i = 30 THEN
3190 p_attribute30 := p_segments.default_value(j);
3191 END IF;
3192 END LOOP;
3193 END IF;
3194
3195 END LOOP;
3196 /*
3197 dbms_output.put_line('Attribute cate '||p_attribute_category);
3198
3199 dbms_output.put_line('Attribute 1 '||p_attribute1);
3200 dbms_output.put_line('Attribute 2 '||p_attribute2);
3201 dbms_output.put_line('Attribute 3 '||p_attribute3);
3202 dbms_output.put_line('Attribute 4 '||p_attribute4);
3203 dbms_output.put_line('Attribute 5 '||p_attribute5);
3204 dbms_output.put_line('Attribute 6 '||p_attribute6);
3205 dbms_output.put_line('Attribute 7 '||p_attribute7);
3206 dbms_output.put_line('Attribute 8 '||p_attribute8);
3207 dbms_output.put_line('Attribute 9 '||p_attribute9);
3208 dbms_output.put_line('Attribute 10 '||p_attribute10);
3209 dbms_output.put_line('Attribute 11 '||p_attribute11);
3210 dbms_output.put_line('Attribute 12 '||p_attribute12);
3211 dbms_output.put_line('Attribute 13 '||p_attribute13);
3212 dbms_output.put_line('Attribute 14 '||p_attribute14);
3213 dbms_output.put_line('Attribute 15 '||p_attribute15);
3214 dbms_output.put_line('Attribute 16 '||p_attribute16);
3215 dbms_output.put_line('Attribute 17 '||p_attribute17);
3216 dbms_output.put_line('Attribute 18 '||p_attribute18);
3217 dbms_output.put_line('Attribute 19 '||p_attribute19);
3218 dbms_output.put_line('Attribute 20 '||p_attribute20);
3219 dbms_output.put_line('Attribute 21 '||p_attribute21);
3220 dbms_output.put_line('Attribute 22 '||p_attribute22);
3221 dbms_output.put_line('Attribute 23 '||p_attribute23);
3222 dbms_output.put_line('Attribute 24 '||p_attribute24);
3223 dbms_output.put_line('Attribute 25 '||p_attribute25);
3224 dbms_output.put_line('Attribute 26 '||p_attribute26);
3225 dbms_output.put_line('Attribute 27 '||p_attribute27);
3226 dbms_output.put_line('Attribute 28 '||p_attribute28);
3227 dbms_output.put_line('Attribute 29 '||p_attribute29);
3228 dbms_output.put_line('Attribute 30 '||p_attribute30);
3229
3230 */
3231 END Get_Default_Value_Dff;
3232
3233
3234 -- Added for bug#4606760
3235 FUNCTION is_customer_associated(p_event_id IN NUMBER) RETURN VARCHAR2
3236 IS
3237
3238 CURSOR csr_cust_associations IS
3239 SELECT null
3240 FROM ota_event_associations
3241 WHERE event_id = p_event_id
3242 AND customer_id IS NOT NULL;
3243
3244 l_found varchar2(1) ;
3245
3246 BEGIN
3247
3248 OPEN csr_cust_associations;
3249 FETCH csr_cust_associations INTO l_found;
3250 IF csr_cust_associations%FOUND THEN
3251 CLOSE csr_cust_associations;
3252 RETURN 'Y';
3253 ELSE
3254 CLOSE csr_cust_associations;
3255 RETURN 'N';
3256 END IF;
3257
3258 END is_customer_associated;
3259
3260 -- Added for bug#4606760
3261 FUNCTION check_organization_match(
3262 p_person_id IN NUMBER
3263 ,p_sponsor_org_id IN NUMBER) return VARCHAR2
3264 IS
3265
3266 CURSOR csr_person_orgs IS
3267 SELECT NULL
3268 FROM per_all_assignments_f
3269 WHERE person_id = p_person_id
3270 AND trunc(sysdate) between effective_start_date and effective_end_date
3271 AND organization_id = p_sponsor_org_id
3272 AND assignment_type in ('E', 'C', 'A');
3273
3274 l_found VARCHAR2(1);
3275
3276 BEGIN
3277 OPEN csr_person_orgs;
3278 FETCH csr_person_orgs INTO l_found;
3279 IF csr_person_orgs%NOTFOUND THEN
3280 CLOSE csr_person_orgs;
3281 RETURN 'N';
3282 ELSE
3283 CLOSE csr_person_orgs;
3284 RETURN 'Y';
3285 END IF;
3286 END check_organization_match;
3287
3288 FUNCTION getEnrollmentChangeReason(
3289 p_booking_id IN NUMBER) return VARCHAR2 is
3290
3291 CURSOR crs_enr_change_reason_lookup(p_meaning in varchar2)
3292 IS
3293 SELECT l.lookup_code
3294 FROM fnd_lookup_values l
3295 WHERE l.lookup_type = 'ENROLMENT_STATUS_REASON'
3296 AND l.meaning = p_meaning
3297 AND l.enabled_flag = 'Y'
3298 and rownum=1;
3299
3300
3301 cursor crs_enrollment_change_reason
3302 is
3303 SELECT bsh.comments
3304 FROM ota_booking_status_histories bsh
3305 WHERE bsh.booking_id = p_booking_id
3306 AND bsh.start_date =
3307 (SELECT MAX(start_date)
3308 FROM ota_booking_status_histories
3309 WHERE booking_id = p_booking_id);
3310
3311 CURSOR crs_enr_change_reason_lang(p_lookup_code in varchar2) IS
3312 SELECT meaning
3313 FROM hr_lookups
3314 WHERE lookup_type = 'ENROLMENT_STATUS_REASON'
3315 AND enabled_flag = 'Y'
3316 AND lookup_code = p_lookup_code;
3317
3318 l_comments VARCHAR2(2000) := NULL;
3319 l_comments_lang VARCHAR2(2000) := NULL;
3320 l_change_reason VARCHAR2(2000) := NULL;
3321 l_lookup_code VARCHAR2(30);
3322 BEGIN
3323
3324 open crs_enrollment_change_reason;
3325 fetch crs_enrollment_change_reason into l_comments;
3326 close crs_enrollment_change_reason;
3327
3328 if l_comments is not null then
3329 open crs_enr_change_reason_lookup(l_comments);
3330 fetch crs_enr_change_reason_lookup into l_lookup_code;
3331 close crs_enr_change_reason_lookup;
3332 end if;
3333
3334 if l_lookup_code is not null then
3335
3336 OPEN crs_enr_change_reason_lang(l_lookup_code);
3337 FETCH crs_enr_change_reason_lang
3338 INTO l_comments_lang;
3339 CLOSE crs_enr_change_reason_lang;
3340
3341 IF l_comments_lang is not null then
3342 l_change_reason:=l_comments_lang;
3343 end if;
3344
3345 else
3346 l_change_reason:=l_comments;
3347
3348 end if;
3349 RETURN l_change_reason;
3350
3351 EXCEPTION
3352 WHEN others THEN
3353 RETURN l_change_reason;
3354 END getenrollmentchangereason;
3355
3356 function get_lang_name
3357 (
3358 p_language_code in varchar2
3359 ) return varchar2 is
3360 --
3361 cursor csr_lookup is
3362 select name
3363 from ota_natural_languages_v
3364 where language_code = p_language_code;
3365
3366 --
3367 v_name ota_natural_languages_v.name%TYPE := null;
3368 begin
3369 if p_language_code is not null then
3370 --
3371 open csr_lookup;
3372 fetch csr_lookup into v_name;
3373 close csr_lookup;
3374 end if;
3375 --
3376 return v_name;
3377 --
3378 end get_lang_name;
3379
3380
3381
3382 end ota_utility;