1 package body OTA_GENERAL as
2 /* $Header: otgenral.pkb 120.3 2005/07/20 01:03:03 dbatra noship $ */
3 /*
4 ===========================================================================
5 | Copyright (c) 1996 Oracle Corporation |
6 | All rights reserved. |
7 ===========================================================================
8 Name
9 General Oracle Training utilities
10 Purpose
11 To provide widely used functions in a single shared area
12 History
13 9 Nov 94 M Roychowdhury Created
14 10 Nov 94 M Roychowdhury Added procedure
15 check_start_end_dates
16 11 Nov 94 M Roychowdhury Added check for person
17 17 Nov 94 M Roychowdhury Added check for current employee
18 7 Dec 94 N Simpson Added functions
19 VALID_VENDOR,
20 VALID_CURRENCY
21 07 Mar 95 J Rhodes Include correct messages
22 24 Jul 95 J Rhodes Changed per_people_f to
23 per_all_people_f
24 23 Aug 95 J Rhodes Dummy cursor get_default_values
25 31 Aug 95 J Rhodes Added check_fnd_user
26 31 Aug 95 J Rhodes Added get_fnd_user
27 16 Feb 96 G Perry Added get_session_date
28 10.18 29 Mar 96 S Shah Added check_par_child_dates_fun
29 110.2 03 Dec 98 C Tredwin Added char_to_number
30 115.4 11-OCT-99 R Raina Added function fnd_lang_desc
31 115.5 12-OCT-99 R Raina Added function fnd_curr_name,
32 fnd_lang_code,
33 hr_org_name
34 115.6 31-Aug-00 D Hmulia modified length of decription
35 in fnd_lang_desc to 255
36 115.8 11-MAY-01 D HMulia Added function get_training_center,
37 function get_location
38 115.10 10-JUL-01 D HMulia Added Function get_finance.
39 115.11 12-Mar-02 A Sud Bug 2256328. Modified the length
40 of variables for Organization name
41 and training center.
42 115.12 26-Oct-02 J Bharath Enh 253086 - Modified the ota_general.check_current_employee
43 cursor(c_current_employee) query to support PTU for CWK.
44 115.15 29-Nov-02 Jbharath Bug#2684733 NOCOPY added for IN OUT/OUT arguments of procedure
45 115.16 11-Dec-02 Arkashya Bug#2705857 Removed the debug routines eg.set_location as a part
46 debug performance changes.Package classified as R11.5.9 category A.
47 115.17 13-Mar-03 Pbhasin CSR_LOOKUP cursor in hr_org_name function changed.
48 115.20 16-Jan-04 Hdshah get_business_group_id function modified.
49 115.21 03-Nov-04 sgokhale Bug 3953333 Validation for checking vendor validity wrt start date
50 rem 115.22 07-Apr-05 dbatra Added get_event_name and get_course_name
51 rem 115.23 16-May-2005 jbharath 3885568 Modified cursor c_current_employee to support applicants.
52 rem 115.24 30-Jun-05 dbatra 4465618 Modified get_course_name signature
53 rem 115.25 18-Jul-05 rdola 4490656 Added get_legislation_code method
54 rem 115.13 20-Jul-2005 dbatra 4496361 Added get_offering_name
55 */
56
57 -- Global package name
58 --
59 g_package varchar2(33) := ' ota_general.';
60 g_dummy number (1);
61 --
62 --------------------------------------------------------------------------------
63 --
64
65 function get_event_name (p_event_id in number)
66 return varchar2
67 IS
68
69 l_event_name ota_events.title%TYPE;
70
71 CURSOR c_get_event_name
72 IS
73 SELECT title from ota_events_vl
74 where
75 event_id = p_event_id;
76
77 BEGIN
78
79
80 OPEN c_get_event_name;
81 FETCH c_get_event_name INTO l_event_name;
82 close c_get_event_name;
83 return(l_event_name);
84
85 end get_event_name ;
86
87 function get_offering_name (p_offering_id in number)
88 return varchar2
89 IS
90
91 l_offering_name ota_offerings_tl.name%TYPE;
92
93 CURSOR c_get_offering_name
94 IS
95 SELECT name from ota_offerings_tl
96 where
97 language=userenv('LANG')
98 and
99 offering_id = p_offering_id;
100
101 BEGIN
102
103
104 OPEN c_get_offering_name;
105 FETCH c_get_offering_name INTO l_offering_name;
106 close c_get_offering_name;
107 return(l_offering_name);
108
109 end get_offering_name ;
110
111 function get_course_name (p_activity_version_id in number default null,p_eventid in number default null)
112 return varchar2
113 IS
114 l_business_group_id number;
115 l_course_name OTA_ACTIVITY_VERSIONS_TL.version_name%TYPE;
116
117 CURSOR c_get_course_name
118 IS
119 SELECT version_name
120 FROM OTA_ACTIVITY_VERSIONS_TL
121 WHERE activity_version_id = p_activity_version_id
122 AND language=userenv('LANG');
123
124 CURSOR c_get_course_name2
125 IS
126 SELECT oav.version_name
127 FROM OTA_ACTIVITY_VERSIONS_TL oav,ota_events oev
128 WHERE oav.activity_version_id = oev.activity_version_id
129 AND oav.language=userenv('LANG')
130 and oev.event_id = p_eventid;
131
132 BEGIN
133
134 if p_activity_version_id is not null then
135 OPEN c_get_course_name;
136 FETCH c_get_course_name INTO l_course_name;
137 close c_get_course_name;
138 elsif p_eventid is not null then
139 OPEN c_get_course_name2;
140 FETCH c_get_course_name2 INTO l_course_name;
141 close c_get_course_name2;
142 end if;
143
144 return(l_course_name);
145
146 end get_course_name ;
147
148
149 -- ----------------------------------------------------------------------------
150 -- |------------------------< check_current_employee >------------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- PUBLIC
154 -- Description: Check Current Employee
155 --
156 -- Checks if the given person is a current employee on a given
157 -- date
158 --
159 Function check_current_employee (p_person_id in number,
160 p_date in date)
161 Return boolean is
162 --
163 -- cursor to check if the person has current employee flag set
164 --
165 Cursor c_current_employee is
166 select 'X'
167 from per_all_people_f ppf,
168 per_person_type_usages_f ptu,
169 per_person_types ppt
170 where p_date between ptu.effective_start_date and ptu.effective_end_date
171 and p_date between ppf.effective_start_date and ppf.effective_end_date
172 and ptu.person_id = ppf.person_id
173 and ppt.system_person_type in ('EMP','CWK','APL') -- Added 'APL' for 3885568
174 and ppt.business_group_id = ppf.business_group_id
175 and ptu.person_type_id = ppt.person_type_id
176 and ppf.person_id = p_person_id ;
177 --
178 --
179 l_result boolean;
180 l_dummy varchar2(1);
181 --
182 Begin
183 --
184 open c_current_employee;
185 fetch c_current_employee into l_dummy;
186 l_result := c_current_employee%found;
187 close c_current_employee;
188 --
189 --
190 Return (l_result);
191 --
192 End check_current_employee;
193 --
194 -- ----------------------------------------------------------------------------
195 -- |------------------------< check_domain_value >----------------------------|
196 -- ----------------------------------------------------------------------------
197 -- PUBLIC
198 --
199 -- Description : Used to check if a value is in the specified domain
200 --
201 Procedure check_domain_value
202 (
203 p_domain_type in varchar2
204 ,p_domain_value in varchar2
205 ) is
206 --
207 l_lookup_exists boolean;
208 l_proc varchar2(72) := g_package||'check_domain_value';
209 --
210 cursor csr_check_domain is
211 --
212 select 1
213 from hr_lookups
214 where lookup_type = p_domain_type
215 and lookup_code = p_domain_value;
216 --
217 procedure check_parameters is
218 --
219 begin
220 hr_api.mandatory_arg_error ( g_package,
221 'p_domain_type',
222 p_domain_type);
223 end check_parameters;
224 --
225 Begin
226 --
227 --
228 check_parameters;
229 --
230 if p_domain_value is not null then
231 --
232 Open csr_check_domain;
233 Fetch csr_check_domain into g_dummy;
234 l_lookup_exists := csr_check_domain%found;
235 close csr_check_domain;
236 --
237 If not l_lookup_exists then
238 hr_utility.set_message(801, 'HR_7033_ELE_ENTRY_LKUP_INVLD');
239 hr_utility.set_message_token('LOOKUP_TYPE', p_domain_type);
240 hr_utility.set_message_token('PROCEDURE', l_proc);
241 hr_utility.set_message_token('STEP','5');
242 hr_utility.raise_error;
243 End if;
244 --
245 --
246 end if;
247 --
248 End check_domain_value;
249 -- ----------------------------------------------------------------------------
250 -- |---------------------------< check_start_end_dates >----------------------|
251 -- ----------------------------------------------------------------------------
252 -- PUBLIC (See header for details.)
253 --
254 Procedure check_start_end_dates
255 (
256 p_start_date in date,
257 p_end_date in date
258 ) is
259 --
260 --
261 Begin
262 --
263 If p_end_date is not null then
264 if p_start_date > p_end_date then
265 fnd_message.set_name('OTA', 'OTA_13312_GEN_DATE_ORDER');
266 fnd_message.raise_error;
267 end if;
268 end if;
269 --
270 End check_start_end_dates;
271 --
272 -- ----------------------------------------------------------------------------
273 -- |-----------------------< check_par_child_dates >--------------------------|
274 -- ----------------------------------------------------------------------------
275 --
276 -- PUPLIC
277 -- Description:
278 -- Validate the parent startdate, enddate and the child startdate, enddate.
279 -- The child start- and enddate have to be whitin the parent start-, enddate.
280 --
281 Procedure check_par_child_dates
282 (
283 p_par_start in date
284 ,p_par_end in date
285 ,p_child_start in date
286 ,p_child_end in date
287 ) Is
288 --
289 v_proc varchar2(72) := g_package||'check_par_child_dates';
290 --
291 Begin
292 --
293 -- Existing date for the parent startdate => Boundary parent startdate
294 --
295 If p_par_start is not null Then
296 --
297 -- Child startdate is earlier than parent startdate
298 --
299 If nvl( p_child_start, hr_api.g_sot) < p_par_start Then
300 --
301 -- ** TEMP ** Add error message with the following text.
302 fnd_message.set_name('OTA', 'OTA_13314_GEN_CS_PS');
303 fnd_message.raise_error;
304 --
305 End if;
306 --
307 -- Child enddate is earlier than parent startdate
308 --
309 If nvl( p_child_end, hr_api.g_eot) < p_par_start Then
310 --
311 -- ** TEMP ** Add error message with the following text.
312 fnd_message.set_name('OTA', 'OTA_13314_GEN_CS_PS');
313 fnd_message.raise_error;
314 --
315 End if;
316 --
317 End if;
318 --
319 -- Existing date for the parent enddate => Boundary parent enddate
320 --
321 If p_par_end is not null Then
322 --
323 -- Child startdate is later than parent enddate
324 --
325 If nvl( p_child_start, hr_api.g_sot) > p_par_end Then
326 --
327 -- ** TEMP ** Add error message with the following text.
328 fnd_message.set_name('OTA', 'OTA_13314_GEN_CS_PS');
329 fnd_message.raise_error;
330 --
331 End if;
332 --
333 -- Child enddate is later than parent enddate
334 --
335 If nvl( p_child_end, hr_api.g_eot) > p_par_end Then
336 --
337 -- ** TEMP ** Add error message with the following text.
338 fnd_message.set_name('OTA', 'OTA_13314_GEN_CS_PS');
339 fnd_message.raise_error;
340 --
341 End if;
342 --
343 End if;
344 --
345 End check_par_child_dates;
346 --
347 -- ----------------------------------------------------------------------------
348 -- |-----------------------< check_par_child_dates_fun >----------------------|
349 -- ----------------------------------------------------------------------------
350 --
351 -- PUPLIC
352 -- Description:
353 -- Validate the parent startdate, enddate and the child startdate, enddate.
354 -- The child start- and enddate have to be whitin the parent start-, enddate.
355 --
356 Function check_par_child_dates_fun
357 (
358 p_par_start in date
359 ,p_par_end in date
360 ,p_child_start in date
361 ,p_child_end in date
362 ) Return Boolean Is
363 --
364 v_proc varchar2(72) := g_package||'check_par_child_dates_fun';
365 --
366 Begin
367 --
368 -- Existing date for the parent startdate => Boundary parent startdate
369 --
370 If p_par_start is not null Then
371 --
372 -- Child startdate is earlier than parent startdate
373 --
374 If nvl( p_child_start, hr_api.g_sot) < p_par_start Then
375 --
376 return TRUE;
377 --
378 End if;
379 --
380 -- Child enddate is earlier than parent startdate
381 --
382 If nvl( p_child_end, hr_api.g_eot) < p_par_start Then
383 --
384 return TRUE;
385 --
386 End if;
387 --
388 End if;
389 --
390 -- Existing date for the parent enddate => Boundary parent enddate
391 --
392 If p_par_end is not null Then
393 --
394 -- Child startdate is later than parent enddate
395 --
396 If nvl( p_child_start, hr_api.g_sot) > p_par_end Then
397 --
398 return TRUE;
399 --
400 End if;
401 --
402 -- Child enddate is later than parent enddate
403 --
404 If nvl( p_child_end, hr_api.g_eot) > p_par_end Then
405 --
406 return TRUE;
407 --
408 End if;
409 --
410 End if;
411 --
412 return FALSE;
413 --
414 End check_par_child_dates_fun;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |---------------------------< check_start_end_time >-----------------------|
418 -- ----------------------------------------------------------------------------
419 --
420 -- PUBLIC
421 -- Description:
422 -- Validates the starttime and endtime.
423 -- p_start_time must be less than, or equal to, p_end_time.
424 --
425 Procedure check_start_end_time
426 (
427 p_start_time in varchar2
428 ,p_end_time in varchar2
429 ) is
430 --
431 --
432 Begin
433 --
434 if p_start_time is null AND p_end_time is NOT null then
435 --
436 fnd_message.set_name('OTA', 'OTA_13316_GEN_TIMES_ORDER');
437 fnd_message.raise_error;
438 --
439 elsif p_start_time is NOT null AND p_end_time is null then
440 --
441 fnd_message.set_name('OTA', 'OTA_13316_GEN_TIMES_ORDER');
442 fnd_message.raise_error;
443 --
444 elsif substr( p_start_time, 1, 2) = substr( p_end_time, 1, 2) then
445 --
446 if substr( p_start_time, 4, 2) > substr( p_end_time, 4, 2) then
447 --
448 fnd_message.set_name('OTA', 'OTA_13316_GEN_TIMES_ORDER');
449 fnd_message.raise_error;
450 --
451 end if;
452 --
453 elsif substr( p_start_time, 1, 2) > substr( p_end_time, 1, 2) then
454 --
455 fnd_message.set_name('OTA', 'OTA_13316_GEN_TIMES_ORDER');
456 fnd_message.raise_error;
457 --
458 end if;
459 --
460 End check_start_end_time;
461 --
462 -- ----------------------------------------------------------------------------
463 -- |----------------------------< get_fnd_user >----------------------------|
464 -- ----------------------------------------------------------------------------
465 --
466 -- PUBLIC
467 -- Description: Get FND USer
468 --
469 function get_fnd_user (p_user_id in number) return varchar2 is
470 --
471 l_username fnd_user.user_name%TYPE;
472 --
473 cursor get_user is
474 select user_name
475 from fnd_user
476 where user_id = p_user_id;
477 --
478 begin
479 open get_user;
480 fetch get_user into l_username;
481 close get_user;
482 return l_username;
483 end;
484 --
485 -- ----------------------------------------------------------------------------
486 -- |----------------------------< get_session_date >--------------------------|
487 -- ----------------------------------------------------------------------------
488 --
489 -- PUBLIC
490 -- Description: Get Session Date
491 --
492 function get_session_date(p_session_id number) return date is
493 l_date date;
494 cursor c_session_date is
495 select effective_date
496 from fnd_sessions
497 where session_id = p_session_id;
498 --
499 begin
500 --
501 open c_session_date;
502 fetch c_session_date into l_date;
503 close c_session_date;
504 return l_date;
505 --
506 end get_session_date;
507 --
508 -- ----------------------------------------------------------------------------
509 -- |----------------------------< check_fnd_user >----------------------------|
510 -- ----------------------------------------------------------------------------
511 --
512 -- PUBLIC
513 -- Description: Check FND USer
514 --
515 Function check_fnd_user (p_user_id in number) return boolean is
516 --
517 l_user varchar2(30);
518 --
519 cursor get_user is
520 select 'Y'
521 from fnd_user
522 where user_id = p_user_id;
523 --
524 begin
525 open get_user;
526 fetch get_user into l_user;
527 if get_user%notfound then
528 close get_user;
529 return FALSE;
530 end if;
531 close get_user;
532 return TRUE;
533 end;
534 -- ----------------------------------------------------------------------------
535 -- |----------------------------< check_person >------------------------------|
536 -- ----------------------------------------------------------------------------
537 --
538 -- PUBLIC
539 -- Description: Check Person
540 --
541 -- Checks that a given person is active on a given date
542 --
543 Function check_person (p_person_id in number,
544 p_date in date) return boolean is
545 --
546 -- cursor to check the existence of the person on a given date
547 --
548 Cursor c_person (l_date IN DATE) is -- **** l_date added to cursor definition for bug #2154926
549 -- **** l_date added to where clause for bug #2154926
550 select 'X'
551 from per_all_people_f
552 where person_id = p_person_id
553 and l_date between effective_start_date and effective_end_date;
554 --
555 l_dummy varchar2(1);
556 l_result boolean;
557 l_date date; -- **** added for bug #2154926
558 --
559 Begin
560 --
561 -- **** start added for bug #2154926
562 --
563 l_date := p_date;
564 --
565 if l_date = hr_api.g_date or l_date is null or l_date = hr_api.g_sot then
566 -- l_date := get_session_date(USERENV('SESSIONID'));
567 -- changed for bug 3242405.
568 l_date := trunc(sysdate);
569 end if;
570 --
571 -- **** end added for bug #2154926
572 --
573 open c_person (l_date); -- **** l_date added for bug #2154926
574 fetch c_person into l_dummy;
575 l_result := c_person%found;
576 close c_person;
577 --
578 Return l_result;
579 --
580 End check_person;
581 --
582 -- ----------------------------------------------------------------------------
583 -- |-----------------------------< value_changed >----------------------------|
584 -- ----------------------------------------------------------------------------
585 --
586 -- PUBLIC
587 -- Description: Value Changed (for varchar2 types)
588 --
589 -- Checks if two values are different
590 --
591 Function value_changed (p_old_value in varchar2,
592 p_new_value in varchar2) return boolean is
593 --
594 --
595 Begin
596 --
597 return nvl(p_old_value,hr_api.g_varchar2) <>
598 nvl(p_new_value,hr_api.g_varchar2);
599 --
600 End value_changed;
601 --
602 -- ----------------------------------------------------------------------------
603 -- |-----------------------------< value_changed >----------------------------|
604 -- ----------------------------------------------------------------------------
605 --
606 -- PUBLIC
607 -- Description: Value Changed (for number types)
608 --
609 -- Checks if two values are different
610 --
611 Function value_changed (p_old_value in number,
612 p_new_value in number) return boolean is
613 --
614 --
615 Begin
616 --
617 return nvl(p_old_value,hr_api.g_number) <>
618 nvl(p_new_value,hr_api.g_number);
619 --
620 End value_changed;
621 --
622 -- ----------------------------------------------------------------------------
623 -- |-----------------------------< value_changed >----------------------------|
624 -- ----------------------------------------------------------------------------
625 --
626 -- PUBLIC
627 -- Description: Value Changed (for date types)
628 --
629 -- Checks if two values are different
630 --
631 Function value_changed (p_old_value in date,
632 p_new_value in date) return boolean is
633 --
634 --
635 Begin
636 --
637 return nvl(p_old_value,hr_api.g_date) <>
638 nvl(p_new_value,hr_api.g_date);
639 --
640 End value_changed;
641 --
642 -- ----------------------------------------------------------------------------
643 -- |----------------------------< char_to_number >----------------------------|
644 -- ----------------------------------------------------------------------------
645 --
646 -- PUBLIC
647 -- Description: Converts character to number. Executed on the server
648 -- because clients are unable to read non-US values for
649 -- NLS_NUMERIC_CHARACTERS.
650 --
651 function char_to_number (p_input in varchar2) return number is
652 --
653 --
654 begin
655 --
656 return to_number(p_input);
657 --
658 exception
659 when others then
660 raise;
661 --
662 end;
663 --
664 --------------------------------------------------------------------------------
665 function valid_currency (p_currency_code varchar2) return boolean is
666 --
667 --******************************************************************************
668 --* Returns TRUE if the currency exists on the currencies table.
669 --******************************************************************************
670 --
671 cursor csr_currency is
672 select 1
673 from fnd_currencies
674 where currency_code = p_currency_code;
675 --
676 l_currency_OK boolean;
677 --
678 begin
679 --
680 --
681 open csr_currency;
682 fetch csr_currency into g_dummy;
683 l_currency_OK := csr_currency%found;
684 close csr_currency;
685 --
686 --
687 return l_currency_OK;
688 --
689 end valid_currency;
690 --------------------------------------------------------------------------------
691 procedure CHECK_CURRENCY_IS_VALID (p_currency_code varchar2) is
692 --
693 begin
694 --
695 if p_currency_code is not null and NOT Valid_currency (p_currency_code) then
696 hr_utility.set_message (810,'OTA_13038_INVALID_CURRENCY');
697 hr_utility.raise_error;
698 end if;
699 --
700 end check_currency_is_valid;
701 --------------------------------------------------------------------------------
702 function Valid_language (
703 --
704 --******************************************************************************
705 --* Returns TRUE if the language ID exists on the language table. If it does not
706 --* exist, then an error will be produced unless p_suppress_messages is set
707 --* to TRUE, in which case it will just return FALSE
708 --******************************************************************************
709 --
710 p_language_id number ) return boolean is
711 --
712 cursor csr_language is
713 select 1
714 from fnd_languages
715 where language_id = p_language_id;
716 --
717 l_language_OK boolean;
718 --
719 begin
720 --
721 --
722 open csr_language;
723 fetch csr_language into g_dummy;
724 l_language_OK := csr_language%found;
725 close csr_language;
726 --
727 --
728 return l_language_OK;
729 --
730 end valid_language;
731 --------------------------------------------------------------------------------
732 procedure CHECK_LANGUAGE_IS_VALID (p_language_id number) is
733 --
734 begin
735 --
736 if p_language_id is not null and NOT valid_language (p_language_id) then
737 hr_utility.set_message (810,'OTA_13448_EVT_INVALID_LANGUAGE');
738 hr_utility.raise_error;
739 end if;
740 --
741 end check_language_is_valid;
742 --------------------------------------------------------------------------------
743 function vendor_name (p_vendor_id number) return varchar2 is
744 --
745 --******************************************************************************
746 --* Returns the vendor_name for a vendor_ID.
747 --******************************************************************************
748 --
749 cursor csr_vendor is
750 select vendor_name
751 from po_vendors
752 where vendor_id = p_vendor_id;
753 --
754 l_vendor_name po_vendors.vendor_name%type := null;
755 --
756 begin
757 --
758 if p_vendor_id is not null then
759 --
760 open csr_vendor;
761 fetch csr_vendor into l_vendor_name;
762 close csr_vendor;
763 --
764 end if;
765 --
766 return l_vendor_name;
767 --
768 end vendor_name;
769 --------------------------------------------------------------------------------
770 function Valid_vendor (
771 --
772 --******************************************************************************
773 --* Returns TRUE if the vendor name exists on the vendor table.
774 --******************************************************************************
775 --
776 p_vendor_name varchar2 ) return boolean is
777 --
778 cursor csr_vendor is
779 select 1
780 from po_vendors
781 where vendor_name = p_vendor_name;
782 --
783 l_vendor_OK boolean;
784
785 --
786 begin
787 --
788 --
789 open csr_vendor;
790 fetch csr_vendor into g_dummy;
791 l_vendor_OK := csr_vendor%found;
792 close csr_vendor;
793 --
794 return l_vendor_OK;
795 --
796 end valid_vendor;
797 --------------------------------------------------------------------------------
798 function Valid_vendor (
799 --
800 --******************************************************************************
801 --* Returns TRUE if the vendor ID exists on the vendor table.
802 --******************************************************************************
803 --
804 p_vendor_id number,p_date date default null) return boolean is
805 --
806 cursor csr_vendor(l_date IN DATE) is
807 select 1
808 from po_vendors
809 where vendor_id = p_vendor_id
810 and nvl(end_date_active,to_date('31-12-4712', 'DD-MM-YYYY')) > l_date;
811 --
812 l_vendor_OK boolean;
813 l_date date;
814 --
815 begin
816 --
817 --
818 l_date := p_date;
819 if p_date is null then
820 l_date := to_date('01-01-1001','DD-MM-YYYY');
821 end if;
822 open csr_vendor(l_date);
823 fetch csr_vendor into g_dummy;
824 l_vendor_OK := csr_vendor%found;
825 close csr_vendor;
826 --
827 return l_vendor_OK;
828 --
829 --
830 end valid_vendor;
831 --------------------------------------------------------------------------------
832 function fnd_lang_desc
833 (
834 p_language_id number
835 ) return varchar2 is
836 --
837 cursor csr_lookup is
838 select description
839 from fnd_languages_vl
840 where language_id = p_language_id;
841
842 --
843 v_description fnd_languages_vl.description%TYPE := null;
844 begin
845 if p_language_id is not null then
846 --
847 open csr_lookup;
848 fetch csr_lookup into v_description;
849 close csr_lookup;
850 end if;
851 --
852 return v_description;
853 --
854 end fnd_lang_desc;
855
856 --------------------------------------------------------------------------------
857 function hr_org_name
858 (
859 p_organization_id number
860 ) return varchar2 is
861 --
862 cursor csr_lookup is
863 --Bug 2846475
864 select orgs_tl.name
865 from hr_all_organization_units orgs,hr_all_organization_units_tl orgs_tl
866 where orgs.organization_id = p_organization_id
867 and orgs.organization_id = orgs_tl.organization_id
868 and orgs_tl.language = userenv('LANG');
869 -- select name
870 -- from hr_organization_units
871 -- where organization_id = p_organization_id;
872
873 --
874 v_description hr_all_organization_units.name%TYPE := null;
875 begin
876 if p_organization_id is not null then
877 --
878 open csr_lookup;
879 fetch csr_lookup into v_description;
880 close csr_lookup;
881 end if;
882 --
883 return v_description;
884 --
885 end hr_org_name;
886
887
888 --------------------------------------------------------------------------------
889 function fnd_currency_name
890 (
891 p_currency_code varchar2
892 ) return varchar2 is
893 --
894 cursor csr_lookup is
895 select name
896 from fnd_currencies_vl
897 where currency_code = p_currency_code;
898
899 --
900 v_name fnd_currencies_vl.name%TYPE := null;
901 begin
902 if p_currency_code is not null then
903 --
904 open csr_lookup;
905 fetch csr_lookup into v_name;
906 close csr_lookup;
907 end if;
908 --
909 return v_name;
910 --
911 end fnd_currency_name;
912
913 ---------------------------------------------------------------------------------
914 function fnd_lang_code
915 (
916 p_language_id number
917 ) return varchar2 is
918 --
919 cursor csr_lookup is
920 select language_code
921 from fnd_languages_vl
922 where language_id = p_language_id;
923
924 --
925 v_language_code fnd_languages_vl.language_code%TYPE := null;
926 begin
927 if p_language_id is not null then
928 --
929 open csr_lookup;
930 fetch csr_lookup into v_language_code;
931 close csr_lookup;
932 end if;
933 --
934 return v_language_code;
935 --
936 end fnd_lang_code;
937
938 ----------------------------------------------------------------------------------
939 procedure CHECK_VENDOR_IS_VALID (p_vendor_id number,p_date date default null) is
940 --
941 begin
942 --
943 if p_vendor_id is not null and NOT valid_vendor (p_vendor_id,p_date) then
944 hr_utility.set_message (810,'OTA_13039_INVALID_VENDOR');
945 hr_utility.raise_error;
946 end if;
947 --
948 end check_vendor_is_valid;
949 --------------------------------------------------------------------------------
950 /* Procedure get_defaults returns a number of values derived from the
951 Business Group set up
952 */
953 procedure get_defaults(p_business_group_id in number
954 ,p_default_activity_version out nocopy varchar2
955 ,p_default_source_of_booking out nocopy varchar2
956 ,p_default_enrolment_status out nocopy varchar2
957 ,p_overbooking_rule out nocopy varchar2
958 ,p_autogen_scheduled_event out nocopy varchar2
959 ,p_update_scheduled_event out nocopy varchar2
960 ,p_autogen_development_event out nocopy varchar2
961 ,p_update_development_event out nocopy varchar2) is
962 cursor get_default_values is
963 select null
964 , null
965 , null
966 , null
967 , null
968 , null
969 , null
970 , null
971 from sys.dual;
972 begin
973 open get_default_values;
974 fetch get_default_values into p_default_activity_version
975 ,p_default_source_of_booking
976 ,p_default_enrolment_status
977 ,p_overbooking_rule
978 ,p_autogen_scheduled_event
979 ,p_update_scheduled_event
980 ,p_autogen_development_event
981 ,p_update_development_event;
982 close get_default_values;
983 end;
984 --
985 procedure get_defaults(p_business_group_id in number
986 ,p_default_activity_version out nocopy varchar2) is
987 l_default_activity_version varchar2(30);
988 l_default_source_of_booking varchar2(30);
989 l_default_enrolment_status varchar2(30);
990 l_overbooking_rule varchar2(150);
991 l_autogen_scheduled_event varchar2(30);
992 l_update_scheduled_event varchar2(30);
993 l_autogen_development_event varchar2(30);
994 l_update_development_event varchar2(30);
995 begin
996 get_defaults(p_business_group_id
997 ,p_default_activity_version
998 ,l_default_source_of_booking
999 ,l_default_enrolment_status
1000 ,l_overbooking_rule
1001 ,l_autogen_scheduled_event
1002 ,l_update_scheduled_event
1003 ,l_autogen_development_event
1004 ,l_update_development_event);
1005 end;
1006
1007 procedure get_defaults(p_default_source_of_booking out nocopy varchar2
1008 ,p_default_enrolment_status out nocopy varchar2
1009 ,p_overbooking_rule out nocopy varchar2) is
1010 begin
1011 null;
1012 end;
1013
1014 procedure get_defaults(p_autogen_scheduled_event out nocopy varchar2
1015 ,p_update_scheduled_event out nocopy varchar2) is
1016 begin
1017 null;
1018 end;
1019
1020 procedure get_defaults(p_autogen_development_event out nocopy varchar2
1021 ,p_update_development_event out nocopy varchar2) is
1022 begin
1023 null;
1024 end;
1025
1026 function get_business_group_id return number is
1027 --
1028 -- If the user has signed on through applications then returns the value
1029 -- of the Business Group profile option otherwise returns null.
1030 --
1031 --
1032 -- Note the check that the user has signed on is so that views which
1033 -- would normally restrict on business group can be made to retrieve
1034 -- all rows when running in sql*plus or other environments. By default
1035 -- the Business Group Profile option is defined at site level to be
1036 -- the Setup Business group.
1037 --
1038
1039 begin
1040
1041 -- Below if condition commented out to avoid empty pages issues in eBS project.
1042 -- if ( fnd_global.user_id = -1 ) then
1043 -- return(null);
1044 -- else
1045 if (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'))is not null then
1046 return(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'));
1047 else
1048 return(fnd_profile.value('PER_BUSINESS_GROUP_ID'));
1049 end if;
1050 -- end if;
1051
1052 end get_business_group_id ;
1053
1054
1055 function get_training_center (p_training_center_id in number)
1056 return varchar2
1057 IS
1058 l_business_group_id number;
1059 l_training_center hr_all_organization_units.name%TYPE;
1060
1061 CURSOR c_get_training_center
1062 IS
1063 SELECT org.name
1064 FROM hr_all_organization_units org, hr_organization_information ori
1065 WHERE org.business_group_id = l_business_group_id
1066 AND org.organization_id = p_training_center_id
1067 AND org.organization_id = ori.organization_id
1068 AND ori.org_information_context = 'CLASS'
1069 AND ori.org_information1 ='OTA_TC';
1070
1071 BEGIN
1072 l_business_group_id := OTA_GENERAL.get_business_group_id;
1073 For a in c_get_training_center
1074 loop
1075 l_training_center := a.name;
1076 end loop;
1077 return(l_training_center);
1078
1079 end get_training_center ;
1080
1081
1082 function get_location (p_location_id in number)
1083 return varchar2 IS
1084
1085 l_location_code hr_locations.location_code%type;
1086
1087 CURSOR c_get_location IS
1088 SELECT loc.location_code
1089 FROM hr_locations_all loc
1090 WHERE loc.Location_id = p_location_id;
1091
1092 BEGIN
1093 For a in c_get_location
1094 loop
1095 l_location_code := a.location_code;
1096 end loop;
1097 return(l_location_code);
1098
1099 end get_location ;
1100
1101 FUNCTION get_finance (p_booking_id in number)
1102 return number
1103 IS
1104 Cursor
1105 C_finance
1106 IS
1107 SELECT count(*) total
1108 FROM ota_finance_lines fl,
1109 ota_finance_headers fh
1110 where fl.booking_id = p_booking_id and
1111 fh.finance_header_id = fl.Finance_header_id and
1112 fh.type= 'CT' and
1113 fl.cancelled_flag ='Y';
1114 l_return number;
1115
1116 Begin
1117 For r_fl in c_finance
1118 LOOP
1119 l_return := r_fl.total;
1120 END LOOP;
1121 return(l_return);
1122 end;
1123
1124 --------------------------------------------------------------------------------
1125 -- ----------------------------------------------------------------------------
1126 -- |-------------------------< Get_Location_Code >--------------------------|
1127 -- ----------------------------------------------------------------------------
1128 --
1129 -- Description: Get the location code for the location id passed in as a parameter.
1130 --
1131 --
1132 FUNCTION get_Location_code(p_location_id IN NUMBER) RETURN VARCHAR2
1133 IS
1134 --
1135 CURSOR loc_country_cr IS
1136 SELECT loc.location_code
1137 FROM hr_locations_all_tl loc
1138 WHERE loc.location_id = p_location_id
1139 AND loc.language = USERENV('LANG');
1140
1141 --
1142 l_loc_code hr_locations_all.location_code%TYPE := null;
1143 --
1144 BEGIN
1145 --
1146 --
1147 --
1148 -- get country for OM org
1149
1150 if p_location_id is not null then
1151 --
1152 FOR loc_country IN loc_country_cr
1153 LOOP
1154 l_loc_code := loc_country.location_code;
1155
1156 END LOOP;
1157
1158 end if;
1159 RETURN l_loc_code;
1160 --
1161 --
1162 EXCEPTION
1163 WHEN others then
1164 RETURN l_loc_code;
1165 END get_location_code;
1166
1167 --------------------------------------------------------------------------------
1168 -- ----------------------------------------------------------------------------
1169 -- |-------------------------< Get_Org_Name >----------------------------|
1170 -- ----------------------------------------------------------------------------
1171 --
1172 -- Description: get the name for the organization id passed in.
1173 --
1174 --
1175 FUNCTION get_org_name(p_organization_id IN NUMBER) RETURN VARCHAR2
1176 IS
1177 --
1178 CURSOR org_name_cr IS
1179 SELECT org.name
1180 FROM hr_all_organization_units_tl org
1181 WHERE org.organization_id = p_organization_id
1182 AND org.language = USERENV('LANG');
1183
1184 --
1185 l_org_name hr_all_organization_units.name%TYPE := null;
1186 --
1187 begin
1188 --
1189 --
1190 --
1191 -- get country for OM org
1192
1193 if p_organization_id is not null then
1194 --
1195 FOR org_name IN org_name_cr
1196 LOOP
1197 l_org_name := org_name.name;
1198
1199 END LOOP;
1200
1201 end if;
1202 RETURN l_org_name;
1203 --
1204 --
1205 EXCEPTION
1206 WHEN others then
1207 RETURN l_org_name;
1208 END get_org_name;
1209 --
1210 --------------------------------------------------------------------------------
1211 -- ----------------------------------------------------------------------------
1212 -- |-------------------------< get_legislation_code >----------------------------|
1213 -- ----------------------------------------------------------------------------
1214 --
1215 -- Description: get the legislation code of the business group
1216 --
1217 --
1218 FUNCTION get_legislation_code
1219 RETURN VARCHAR2
1220 IS
1221 CURSOR csr_get_legislation_code IS
1222 SELECT PBG.legislation_code
1223 FROM per_business_groups PBG
1224 WHERE PBG.business_group_id = ota_general.get_business_group_id;
1225
1226 l_legislation_code per_business_groups.legislation_code%TYPE;
1227 BEGIN
1228 OPEN csr_get_legislation_code;
1229 FETCH csr_get_legislation_code INTO l_legislation_code;
1230 CLOSE csr_get_legislation_code;
1231
1232 RETURN l_legislation_code;
1233
1234 END get_legislation_code;
1235 end OTA_GENERAL;
[Home] [Help]