[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PENSION_FUNCTIONS
Source
1 PACKAGE BODY pqp_gb_pension_functions
2 -- /* $Header: pqpgbpef.pkb 120.3.12010000.2 2008/09/23 08:36:22 namgoyal ship $ */
3 AS
4
5 --
6 -- Global Variables
7 g_tab_pension_types_info t_pension_types;
8 g_tab_element_types_info t_element_types;
9
10
11 -- ----------------------------------------------------------------------------
12 -- |--------------------------------< debug >---------------------------------|
13 -- ----------------------------------------------------------------------------
14
15 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
16 IS
17
18 --
19 BEGIN
20 --
21
22 pqp_utilities.DEBUG (
23 p_trace_message => p_trace_message
24 ,p_trace_location => p_trace_location
25 );
26 --
27 END DEBUG;
28
29
30 --
31 -- ----------------------------------------------------------------------------
32 -- |--------------------------------< debug >---------------------------------|
33 -- ----------------------------------------------------------------------------
34
35 PROCEDURE DEBUG (p_trace_number IN NUMBER)
36 IS
37
38 --
39 BEGIN
40 --
41 DEBUG (fnd_number.number_to_canonical (p_trace_number));
42 --
43
44 END DEBUG;
45
46
47 --
48 -- ----------------------------------------------------------------------------
49 -- |--------------------------------< debug >---------------------------------|
50 -- ----------------------------------------------------------------------------
51
52 PROCEDURE DEBUG (p_trace_date IN DATE)
53 IS
54
55 --
56 BEGIN
57 --
58 DEBUG (fnd_date.date_to_canonical (p_trace_date));
59 --
60
61 END DEBUG;
62
63
64 -- This procedure is used for debug purposes
65 -- debug_enter checks the debug flag and sets the trace on/off
66 --
67 -- ----------------------------------------------------------------------------
68 -- |----------------------------< debug_enter >-------------------------------|
69 -- ----------------------------------------------------------------------------
70
71 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
72 IS
73 BEGIN
74 --
75 pqp_utilities.debug_enter (
76 p_proc_name => p_proc_name
77 ,p_trace_on => p_trace_on
78 );
79 --
80 END debug_enter;
81
82
83 -- This procedure is used for debug purposes
84 --
85 -- ----------------------------------------------------------------------------
86 -- |----------------------------< debug_exit >--------------------------------|
87 -- ----------------------------------------------------------------------------
88
89 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
90 IS
91 BEGIN
92 --
93 pqp_utilities.debug_exit (
94 p_proc_name => p_proc_name
95 ,p_trace_off => p_trace_off
96 );
97 --
98 END debug_exit;
99
100
101 -- This procedure is used for debug purposes
102 --
103 -- ----------------------------------------------------------------------------
104 -- |----------------------------< debug_others >--------------------------------|
105 -- ----------------------------------------------------------------------------
106
107 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
108 IS
109 BEGIN
110 --
111 pqp_utilities.debug_others (
112 p_proc_name => p_proc_name
113 ,p_proc_step => p_proc_step
114 );
115 --
116 END debug_others;
117
118
119 -- This function populates element entry id's in a collection
120 -- and returns it
121
122
123 -- This procedure is used to identify whether an employee is a member
124 -- of any other pension scheme for a given pension category
125 --
126 --
127 -- ----------------------------------------------------------------------------
128 -- |----------------------------< get_element_types_info >--------------------|
129 -- ----------------------------------------------------------------------------
130 FUNCTION get_element_types_info (
131 p_assignment_id IN NUMBER
132 ,p_element_type_id IN NUMBER
133 ,p_effective_date IN DATE
134 )
135 RETURN t_number
136 IS
137 --
138 l_proc_name VARCHAR2 (80)
139 := g_proc_name
140 || 'get_element_types_info';
141 l_ele_entry_id NUMBER;
142 l_ele_type_id NUMBER;
143 l_tab_ele_type_info t_number;
144 l_proc_step NUMBER;
145
146 --
147 BEGIN
148 --
149 IF g_debug
150 THEN
151 l_proc_step := 10;
152 DEBUG ( 'Entering: '
153 || l_proc_name, l_proc_step);
154 END IF;
155
156 -- Get the element link id for this assignment
157 IF g_debug
158 THEN
159 DEBUG ( 'Assignment ID: '
160 || TO_CHAR (p_assignment_id));
161 DEBUG ( 'Element Type ID: '
162 || TO_CHAR (p_element_type_id));
163 END IF;
164
165 OPEN csr_get_ele_entry_info (
166 p_assignment_id
167 ,p_element_type_id
168 ,p_effective_date
169 );
170
171 LOOP
172 FETCH csr_get_ele_entry_info INTO l_ele_entry_id;
173 EXIT WHEN csr_get_ele_entry_info%NOTFOUND;
174
175 IF g_debug
176 THEN
177 DEBUG ( 'Element Link ID: '
178 || TO_CHAR (l_ele_entry_id));
179 END IF;
180
181 -- Get the element type id for this link id
182 IF g_debug
183 THEN
184 l_proc_step := 20;
185 DEBUG (l_proc_name, l_proc_step);
186 END IF;
187
188 OPEN csr_get_ele_type_id (l_ele_entry_id, p_effective_date);
189 FETCH csr_get_ele_type_id INTO l_ele_type_id;
190
191 IF g_debug
192 THEN
193 DEBUG ( 'Element Type ID: '
194 || TO_CHAR (l_ele_type_id));
195 END IF;
196
197 -- No need to check for row existence, as this is done
198 -- in the first cursor
199 l_tab_ele_type_info (l_ele_type_id) := l_ele_type_id;
200 CLOSE csr_get_ele_type_id;
201 END LOOP; -- End loop of ele entry ...
202
203 CLOSE csr_get_ele_entry_info;
204
205 IF g_debug
206 THEN
207 l_proc_step := 30;
208 DEBUG ( 'Leaving: '
209 || l_proc_name, l_proc_step);
210 END IF;
211
212 RETURN l_tab_ele_type_info;
213 --
214 END;
215
216
217 -- ----------------------------------------------------------------------------
218 -- |----------------------------< get_ele_type_from_link >--------------------|
219 -- ----------------------------------------------------------------------------
220 FUNCTION get_ele_type_from_link (
221 p_element_link_id IN NUMBER
222 ,p_effective_date IN DATE
223 )
224 RETURN NUMBER
225 IS
226 --
227 l_proc_name VARCHAR2 (80)
228 := g_proc_name
229 || 'get_ele_type_from_link';
230 l_proc_step NUMBER;
231 l_element_type_id NUMBER;
232 --
233 BEGIN
234 --
235 IF g_debug
236 THEN
237 DEBUG ( 'Entering: '
238 || l_proc_name, l_proc_step);
239 END IF;
240
241 --
242 OPEN csr_get_ele_type_id (p_element_link_id, p_effective_date);
243 FETCH csr_get_ele_type_id INTO l_element_type_id;
244 CLOSE csr_get_ele_type_id;
245
246 IF g_debug
247 THEN
248 DEBUG ( 'Element Type ID: '
249 || TO_CHAR (l_element_type_id));
250 l_proc_step := 20;
251 DEBUG ( 'Leaving: '
252 || l_proc_name, l_proc_step);
253 END IF;
254
255 --
256 RETURN l_element_type_id;
257 --
258 END get_ele_type_from_link;
259
260 --
261
262 -- ----------------------------------------------------------------------------
263 -- |----------------------------< get_ele_link_info >-------------------------|
264 -- ----------------------------------------------------------------------------
265 FUNCTION get_ele_link_info (
266 p_element_type_id IN NUMBER
267 ,p_business_group_id IN NUMBER
268 ,p_effective_date IN DATE
269 )
270 RETURN NUMBER
271 IS
272 --
273 l_proc_name VARCHAR2 (80)
274 := g_proc_name
275 || 'get_ele_link_info';
276 l_proc_step NUMBER;
277 l_element_link_id NUMBER;
278 --
279 BEGIN
280 --
281 IF g_debug
282 THEN
283 l_proc_step := 10;
284 DEBUG ( 'Entering: '
285 || l_proc_name, l_proc_step);
286 END IF;
287
288 --
289 OPEN csr_get_ele_link_id (
290 p_element_type_id
291 ,p_business_group_id
292 ,p_effective_date
293 );
294 FETCH csr_get_ele_link_id INTO l_element_link_id;
295 CLOSE csr_get_ele_link_id;
296
297 IF g_debug
298 THEN
299 DEBUG ( 'Element link ID: '
300 || TO_CHAR (l_element_link_id));
301 l_proc_step := 20;
302 DEBUG ( 'Leaving: '
303 || l_proc_name, l_proc_step);
304 END IF;
305
306 --
307 RETURN l_element_link_id;
308 --
309 END get_ele_link_info;
310
311
312 -- ----------------------------------------------------------------------------
313 -- |----------------------------< get_element_name >--------------------------|
314 -- ----------------------------------------------------------------------------
315 FUNCTION get_element_name (
316 p_element_type_id IN NUMBER
317 ,p_effective_date IN DATE
318 )
319 RETURN NUMBER
320 IS
321 --
322 l_proc_name VARCHAR2 (80) := g_proc_name
323 || 'get_element_name';
324 l_proc_step NUMBER;
325 l_element_name pay_element_types_f.element_name%TYPE;
326 --
327 BEGIN
328 --
329 IF g_debug
330 THEN
331 l_proc_step := 10;
332 DEBUG ( 'Entering: '
333 || l_proc_name, l_proc_step);
334 END IF;
335
336 --
337 OPEN csr_get_ele_name (p_element_type_id, p_effective_date);
338 FETCH csr_get_ele_name INTO l_element_name;
339 CLOSE csr_get_ele_name;
340
341 IF g_debug
342 THEN
343 DEBUG ( 'Element Name: '
344 || l_element_name);
345 l_proc_step := 20;
346 DEBUG ( 'Leaving: '
347 || l_proc_name, l_proc_step);
348 END IF;
349
350 --
351 RETURN l_element_name;
352 --
353 END get_element_name;
354
355
356 -- ----------------------------------------------------------------------------
357 -- |----------------------------< chk_multiple_membership >-------------------|
358 -- ----------------------------------------------------------------------------
359
360 FUNCTION chk_multiple_membership (
361 p_assignment_id IN NUMBER
362 , -- Context
363 p_element_type_id IN NUMBER
364 , -- Context
365 p_effective_date IN DATE
366 , -- Context
367 p_pension_category IN VARCHAR2
368 ,p_yes_no OUT NOCOPY VARCHAR2
369 ,p_error_msg OUT NOCOPY VARCHAR2
370 )
371 RETURN NUMBER
372 IS
373 --
374 l_proc_name VARCHAR2 (80)
375 := g_proc_name
376 || 'chk_multiple_membership';
377 l_proc_step NUMBER;
378 l_ele_link_id NUMBER;
379 l_ele_type_id NUMBER;
380 l_exists VARCHAR2 (1);
381 l_yes_no VARCHAR2 (1);
382 l_error_msg VARCHAR2 (1000);
383 l_return NUMBER := 0;
384 l_pension_category hr_lookups.lookup_code%TYPE;
385
386 --For bug 7334468: Version 115.8
387 CURSOR csr_get_sch_cate_type(c_element_type_id NUMBER)
388 IS
389 SELECT eei_information4, --Scheme Category
390 eei_information8 --Scheme Type(COSR/COMP)
391 FROM pay_element_type_extra_info
392 WHERE element_type_id = c_element_type_id
393 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
394
395 l_curr_ele_cate VARCHAR2 (10);
396 l_curr_ele_type VARCHAR2 (10);
397 l_prev_ele_cate VARCHAR2 (10);
398 l_prev_ele_type VARCHAR2 (10);
399
400 --
401 BEGIN
402 --
403 IF g_debug
404 THEN
405 l_proc_step := 10;
406 DEBUG ( 'Entering: '
407 || l_proc_name, l_proc_step);
408 END IF;
409
410 l_yes_no := 'N';
411
412 -- Ignore the check for 'Free Standing AVC'
413 IF (p_pension_category = 'Free Standing AVC') then
414 return 0;
415 END IF;
416
417 -- Get the element link id for this assignment
418
419 IF g_debug
420 THEN
421 DEBUG ( 'Assignment ID: '
422 || TO_CHAR (p_assignment_id));
423 DEBUG ( 'Element Type ID: '
424 || TO_CHAR (p_element_type_id));
425 END IF;
426
427 -- Get the pension category lookup code from the lookup type
428
429 l_pension_category :=
430 pqp_utilities.get_lookup_code (
431 p_lookup_type => 'PQP_PENSION_CATEGORY'
432 ,p_lookup_meaning => p_pension_category
433 ,p_message => l_error_msg
434 );
435
436 IF g_debug
437 THEN
438 DEBUG ( 'Error Message: '
439 || l_error_msg);
440 DEBUG ( 'Pension Category Code: '
441 || l_pension_category);
442 END IF;
443
444 IF l_error_msg IS NOT NULL
445 THEN
446 -- Error Occurred
447 l_return := -1;
448 p_error_msg := l_error_msg;
449 RETURN l_return;
450 END IF; -- End if of error msg check ...
451
452 --For bug 7334468: Version 115.8
453 --Fetch current pension scheme details
454 OPEN csr_get_sch_cate_type (p_element_type_id);
455 FETCH csr_get_sch_cate_type INTO l_curr_ele_cate,l_curr_ele_type;
456 CLOSE csr_get_sch_cate_type;
457
458 DEBUG ('Element Type ID: '|| TO_CHAR (p_element_type_id));
459 DEBUG ('l_curr_ele_cate: '||l_curr_ele_cate);
460 DEBUG ('l_curr_ele_type: '||l_curr_ele_type);
461
462 OPEN csr_get_ele_entry_info (
463 p_assignment_id
464 ,p_element_type_id
465 ,p_effective_date
466 );
467
468 LOOP
469 FETCH csr_get_ele_entry_info INTO l_ele_link_id;
470 EXIT WHEN csr_get_ele_entry_info%NOTFOUND;
471
472 IF g_debug
473 THEN
474 DEBUG ( 'Element Link ID: '
475 || TO_CHAR (l_ele_link_id));
476 END IF;
477
478 IF g_debug
479 THEN
480 l_proc_step := 20;
481 DEBUG (l_proc_name, l_proc_step);
482 END IF;
483
484 -- Get the element type id for this link id
485 l_ele_type_id :=
486 get_ele_type_from_link (
487 p_element_link_id => l_ele_link_id
488 ,p_effective_date => p_effective_date
489 );
490
491 -- Check whether the element type is of the same pension
492 -- category
493 IF g_debug
494 THEN
495 DEBUG ( 'Element Type ID: '
496 || TO_CHAR (l_ele_type_id));
497 DEBUG ( 'YES or NO: '
498 || l_yes_no);
499 DEBUG ( 'Pension Category: '
500 || p_pension_category);
501 l_proc_step := 30;
502 DEBUG (l_proc_name, l_proc_step);
503 END IF;
504
505
506 IF g_debug
507 THEN
508 l_proc_step := 40;
509 DEBUG (l_proc_name, l_proc_step);
510 END IF;
511
512 OPEN csr_chk_is_this_pens_ele (
513 l_ele_type_id
514 ,p_effective_date
515 ,l_pension_category
516 );
517 FETCH csr_chk_is_this_pens_ele INTO l_exists;
518
519 IF csr_chk_is_this_pens_ele%FOUND
520 THEN
521
522 --For bug 7334468: Version 115.8
523 IF l_curr_ele_cate = 'OCP'
524 THEN
525 DEBUG ('Current element is OCP');
526
527 --Fetch other pension scheme details
528 OPEN csr_get_sch_cate_type (l_ele_type_id);
529 FETCH csr_get_sch_cate_type INTO l_prev_ele_cate,l_prev_ele_type;
530 CLOSE csr_get_sch_cate_type;
531
532 DEBUG ( 'Element Type ID: ' || l_ele_type_id);
533 DEBUG ('l_prev_ele_cate: '||l_prev_ele_cate);
534 DEBUG ('l_prev_ele_type: '||l_prev_ele_type);
535
536 IF nvl(l_curr_ele_type,'BLANK') <> nvl(l_prev_ele_type,'BLANK')
537 THEN
538 DEBUG ('OCP elements are of different type');
539 CLOSE csr_chk_is_this_pens_ele;
540 l_yes_no := 'Y';
541 EXIT;
542 END IF;
543 ELSE
544 DEBUG ('Current element is not OCP');
545
546 CLOSE csr_chk_is_this_pens_ele;
547 -- Employee is a member of another pension scheme
548 --
549 l_yes_no := 'Y';
550 EXIT;
551 END IF; --End if pension element is OCP
552
553 END IF; -- End if of pens element found check ...
554
555 CLOSE csr_chk_is_this_pens_ele;
556 END LOOP; -- End loop of element entry ...
557
558 CLOSE csr_get_ele_entry_info;
559
560 IF g_debug
561 THEN
562 DEBUG ( 'YES or NO: '
563 || l_yes_no);
564 l_proc_step := 50;
565 DEBUG ( 'Leaving: '
566 || l_proc_name, l_proc_step);
567 END IF;
568
569 p_yes_no := l_yes_no;
570 --
571
572 RETURN l_return;
573 EXCEPTION
574 WHEN OTHERS
575 THEN
576 p_yes_no := NULL;
577 p_error_msg := SQLERRM;
578
579 IF SQLCODE <> hr_utility.hr_error_number
580 THEN
581 debug_others (l_proc_name, l_proc_step);
582
583 IF g_debug
584 THEN
585 DEBUG ( 'Leaving: '
586 || l_proc_name, -999);
587 END IF;
588
589 fnd_message.raise_error;
590 ELSE
591 RAISE;
592 END IF;
593 --
594 END chk_multiple_membership;
595
596 --
597
598 -- ----------------------------------------------------------------------------
599 -- |----------------------------< get_pension_type_info >---------------------|
600 -- ----------------------------------------------------------------------------
601 FUNCTION get_pension_type_info (
602 p_business_group_id IN NUMBER
603 ,p_effective_date IN DATE
604 ,p_pension_type_id IN NUMBER
605 ,p_pens_type_info_rec OUT NOCOPY r_pension_types
606 ,p_error_msg OUT NOCOPY VARCHAR2
607 )
608 RETURN NUMBER
609 IS
610 --
611
612 l_proc_name VARCHAR2 (80)
613 := g_proc_name
614 || 'get_pension_type_info';
615 l_proc_step NUMBER;
616 l_pens_type_info_rec csr_get_pens_type_info%ROWTYPE;
617 --
618 BEGIN
619 --
620 IF g_debug
621 THEN
622 l_proc_step := 10;
623 DEBUG ( 'Entering: '
624 || l_proc_name, l_proc_step);
625 END IF;
626
627 -- Get the pension type information
628 OPEN csr_get_pens_type_info (
629 p_pension_type_id
630 ,p_business_group_id
631 ,p_effective_date
632 );
633 FETCH csr_get_pens_type_info INTO l_pens_type_info_rec;
634
635 IF csr_get_pens_type_info%NOTFOUND
636 THEN
637 CLOSE csr_get_pens_type_info;
638 fnd_message.set_name ('PQP', 'PQP_230934_PEN_TYPE_ID_INVALID');
639 p_error_msg := fnd_message.get;
640
641 IF g_debug
642 THEN
643 l_proc_step := 20;
644 DEBUG ( 'Leaving: '
645 || l_proc_name, l_proc_step);
646 END IF;
647
648 RETURN -1;
649 END IF; -- End if of pension type info not found check ...
650
651 CLOSE csr_get_pens_type_info;
652 p_pens_type_info_rec.minimum_age := l_pens_type_info_rec.minimum_age;
653 p_pens_type_info_rec.maximum_age := l_pens_type_info_rec.maximum_age;
654 p_pens_type_info_rec.pension_type_id :=
655 l_pens_type_info_rec.pension_type_id;
656 p_pens_type_info_rec.pension_type_name :=
657 l_pens_type_info_rec.pension_type_name;
658 p_pens_type_info_rec.pension_category :=
659 l_pens_type_info_rec.pension_category;
660 p_pens_type_info_rec.effective_start_date :=
661 l_pens_type_info_rec.effective_start_date;
662 p_pens_type_info_rec.effective_end_date :=
663 l_pens_type_info_rec.effective_end_date;
664
665 IF g_debug
666 THEN
667 l_proc_step := 30;
668 DEBUG ( 'Leaving: '
669 || l_proc_name, l_proc_step);
670 END IF;
671
672 RETURN 0;
673 --
674 EXCEPTION
675 WHEN OTHERS
676 THEN
677 p_error_msg := SQLERRM;
678
679 IF SQLCODE <> hr_utility.hr_error_number
680 THEN
681 debug_others (l_proc_name, l_proc_step);
682
683 IF g_debug
684 THEN
685 DEBUG ( 'Leaving: '
686 || l_proc_name, -999);
687 END IF;
688
689 fnd_message.raise_error;
690 ELSE
691 RAISE;
692 END IF;
693 END get_pension_type_info;
694
695 --
696
697 -- ----------------------------------------------------------------------------
698 -- |----------------------------< get_ele_pens_type_info >--------------------|
699 -- ----------------------------------------------------------------------------
700 FUNCTION get_ele_pens_type_info (
701 p_business_group_id IN NUMBER
702 ,p_effective_date IN DATE
703 ,p_element_type_id IN NUMBER
704 ,p_minimum_age OUT NOCOPY NUMBER
705 ,p_maximum_age OUT NOCOPY NUMBER
706 ,p_error_msg OUT NOCOPY VARCHAR2
707 )
708 RETURN NUMBER
709 IS
710 --
711
712 l_proc_name VARCHAR2 (80)
713 := g_proc_name
714 || 'get_ele_pens_type_info';
715 l_proc_step NUMBER;
716 l_pens_type_info_rec r_pension_types;
717 l_return NUMBER;
718 l_truncated_yes_no VARCHAR2 (1);
719 l_pension_type_id NUMBER;
720 l_tab_pension_types_info t_pension_types;
721 --
722 BEGIN
723 --
724 IF g_debug
725 THEN
726 l_proc_step := 10;
727 DEBUG ( 'Entering: '
728 || l_proc_name, l_proc_step);
729 END IF;
730
731 -- Get the pension type id from the EEIT for this
732 -- element type id
733 l_return :=
734 pqp_utilities.pqp_get_extra_element_info (
735 p_element_type_id => p_element_type_id
736 ,p_information_type => 'PQP_GB_PENSION_SCHEME_INFO'
737 ,p_segment_name => 'Pension Type'
738 ,p_value => l_pension_type_id
739 ,p_truncated_yes_no => l_truncated_yes_no
740 ,p_error_msg => p_error_msg
741 );
742
743 IF g_debug
744 THEN
745 DEBUG ( 'Pension Type ID: '
746 || TO_CHAR (l_pension_type_id));
747 END IF;
748
749 IF l_return = -1
750 THEN
751 -- An error has occurred return the error message
752 IF g_debug
753 THEN
754 l_proc_step := 20;
755 DEBUG ( 'Leaving: '
756 || l_proc_name, l_proc_step);
757 END IF;
758
759 RETURN -1;
760 END IF; -- End if of error occurred check ...
761
762 IF g_debug
763 THEN
764 l_proc_step := 30;
765 DEBUG (l_proc_name, l_proc_step);
766 END IF;
767
768 -- Get it from cache if its already there
769
770 l_tab_pension_types_info := g_tab_pension_types_info;
771
772 IF NOT l_tab_pension_types_info.EXISTS (l_pension_type_id)
773 OR -- Check the effectiveness as this is DT table
774 ( l_tab_pension_types_info.EXISTS (l_pension_type_id)
775 AND NOT (p_effective_date
776 BETWEEN l_tab_pension_types_info (l_pension_type_id).effective_start_date
777 AND l_tab_pension_types_info (l_pension_type_id).effective_end_date
778 )
779 )
780 THEN
781 -- Call other function to get the pension type information
782 l_return :=
783 get_pension_type_info (
784 p_business_group_id => p_business_group_id
785 ,p_effective_date => p_effective_date
786 ,p_pension_type_id => l_pension_type_id
787 ,p_pens_type_info_rec => l_pens_type_info_rec
788 ,p_error_msg => p_error_msg
789 );
790
791 IF l_return = -1
792 THEN
793 IF g_debug
794 THEN
795 l_proc_step := 40;
796 DEBUG ( 'Leaving: '
797 || l_proc_name, l_proc_step);
798 END IF;
799
800 RETURN -1;
801 END IF; -- End if of error occurred check ...
802
803 -- Store it in cache
804 l_tab_pension_types_info (l_pension_type_id).pension_type_id :=
805 l_pens_type_info_rec.pension_type_id;
806 l_tab_pension_types_info (l_pension_type_id).pension_type_name :=
807 l_pens_type_info_rec.pension_type_name;
808 l_tab_pension_types_info (l_pension_type_id).pension_category :=
809 l_pens_type_info_rec.pension_category;
810 l_tab_pension_types_info (l_pension_type_id).effective_start_date :=
811 l_pens_type_info_rec.effective_start_date;
812 l_tab_pension_types_info (l_pension_type_id).effective_end_date :=
813 l_pens_type_info_rec.effective_end_date;
814 l_tab_pension_types_info (l_pension_type_id).minimum_age :=
815 l_pens_type_info_rec.minimum_age;
816 l_tab_pension_types_info (l_pension_type_id).maximum_age :=
817 l_pens_type_info_rec.maximum_age;
818 -- Store it in global collection now
819 g_tab_pension_types_info := l_tab_pension_types_info;
820 ELSE -- in cache
821 l_pens_type_info_rec :=
822 l_tab_pension_types_info (l_pension_type_id);
823 END IF; -- End if of pension type info in cache check ...
824
825 p_minimum_age := l_pens_type_info_rec.minimum_age;
826 p_maximum_age := l_pens_type_info_rec.maximum_age;
827
828 IF g_debug
829 THEN
830 DEBUG (
831 'Minimum Age: '
832 || TO_CHAR (l_pens_type_info_rec.minimum_age)
833 );
834 DEBUG (
835 'Maximum Age: '
836 || TO_CHAR (l_pens_type_info_rec.maximum_age)
837 );
838 l_proc_step := 50;
839 DEBUG ( 'Leaving: '
840 || l_proc_name, l_proc_step);
841 END IF;
842
843 RETURN 0;
844 --
845 EXCEPTION
846 WHEN OTHERS
847 THEN
848 p_error_msg := SQLERRM;
849 p_minimum_age := NULL;
850 p_maximum_age := NULL;
851
852 IF SQLCODE <> hr_utility.hr_error_number
853 THEN
854 debug_others (l_proc_name, l_proc_step);
855
856 IF g_debug
857 THEN
858 DEBUG ( 'Leaving: '
859 || l_proc_name, -999);
860 END IF;
861
862 fnd_message.raise_error;
863 ELSE
864 RAISE;
865 END IF;
866 END get_ele_pens_type_info;
867
868 --
869
870 --
871 --==============================================================================
872 --|---------------------------< get_input_value_id >---------------------------|
873 --==============================================================================
874 FUNCTION get_input_value_id (
875 p_input_value_name IN VARCHAR2
876 ,p_element_type_id IN NUMBER
877 ,p_effective_date IN DATE
878 )
879 RETURN NUMBER
880 IS
881 --
882 -- Cursor to retrieve the input value information
883 CURSOR csr_get_ipv_info (c_element_type_id NUMBER)
884 IS
885 SELECT input_value_id
886 FROM pay_input_values_f
887 WHERE NAME = p_input_value_name
888 AND element_type_id = c_element_type_id
889 AND p_effective_date BETWEEN effective_start_date
890 AND effective_end_date;
891
892 l_proc_name VARCHAR2 (80)
893 := g_proc_name
894 || 'get_input_value_id';
895 l_input_value_id NUMBER;
896 l_proc_step NUMBER;
897 --
898 BEGIN
899 --
900 IF g_debug
901 THEN
902 l_proc_step := 10;
903 DEBUG ( 'Entering: '
904 || l_proc_name, l_proc_step);
905 END IF;
906
907 --
908 OPEN csr_get_ipv_info (p_element_type_id);
909 FETCH csr_get_ipv_info INTO l_input_value_id;
910
911 IF csr_get_ipv_info%NOTFOUND
912 THEN
913 CLOSE csr_get_ipv_info;
914 fnd_message.set_name ('PQP', 'PQP_230935_INPUT_VAL_NOTFOUND');
915 fnd_message.set_token ('INPUT_VALUE', p_input_value_name);
916 fnd_message.raise_error;
917 END IF; -- End if of csr row not found check ...
918
919 CLOSE csr_get_ipv_info;
920
921 IF g_debug
922 THEN
923 DEBUG ( 'Input Value ID: '
924 || TO_CHAR (l_input_value_id));
925 l_proc_step := 20;
926 DEBUG ( 'Leaving: '
927 || l_proc_name, l_proc_step);
928 END IF;
929
930 --
931 RETURN l_input_value_id;
932 END get_input_value_id;
933
934 --
935
936 -- This function returns the element entry value for a given element entry id
937 -- and input value id
938 --
939 -- ----------------------------------------------------------------------------
940 -- |-------------------------< get_ele_entry_value >--------------------------|
941 -- ----------------------------------------------------------------------------
942 FUNCTION get_ele_entry_value (
943 p_element_entry_id IN NUMBER
944 ,p_input_value_id IN NUMBER
945 ,p_effective_start_date IN DATE
946 ,p_effective_end_date IN DATE
947 )
948 RETURN VARCHAR2
949 IS
950
951 --
952
953 -- Cursor to get element entry value information
954
955 CURSOR csr_get_ele_entry_value
956 IS
957 SELECT screen_entry_value
958 FROM pay_element_entry_values_f
959 WHERE element_entry_id = p_element_entry_id
960 AND input_value_id = p_input_value_id
961 AND effective_start_date = p_effective_start_date
962 AND effective_end_date = p_effective_end_date;
963
964 l_proc_name VARCHAR2 (72)
965 := g_proc_name
966 || 'get_ele_entry_value';
967 l_ele_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
968 l_proc_step NUMBER;
969
970 --
971 BEGIN
972 --
973 IF g_debug
974 THEN
975 l_proc_step := 10;
976 DEBUG ( 'Entering: '
977 || l_proc_name, l_proc_step);
978 END IF;
979
980 OPEN csr_get_ele_entry_value;
981 FETCH csr_get_ele_entry_value INTO l_ele_entry_value;
982 CLOSE csr_get_ele_entry_value;
983
984 IF g_debug
985 THEN
986 DEBUG ( 'Element Entry ID: '
987 || TO_CHAR (p_element_entry_id));
988 DEBUG ( 'Input Value ID: '
989 || TO_CHAR (p_input_value_id));
990 DEBUG ( 'Entry Value: '
991 || l_ele_entry_value);
992 l_proc_step := 20;
993 DEBUG ( 'Leaving: '
994 || l_proc_name, l_proc_step);
995 END IF;
996
997 RETURN l_ele_entry_value;
998 END get_ele_entry_value;
999
1000
1001 -- ----------------------------------------------------------------------------
1002 -- |----------------------------< get_ele_opt_out_info >----------------------|
1003 -- ----------------------------------------------------------------------------
1004 FUNCTION get_ele_opt_out_info (
1005 p_element_type_id IN NUMBER
1006 ,p_element_entry_id IN NUMBER
1007 ,p_effective_start_date IN DATE
1008 ,p_effective_end_date IN DATE
1009 ,p_effective_date IN DATE
1010 )
1011 RETURN BOOLEAN
1012 IS
1013 --
1014
1015 l_proc_name VARCHAR2 (80)
1016 := g_proc_name
1017 || 'get_ele_opt_out_info';
1018 l_proc_step NUMBER;
1019 l_exists VARCHAR2 (1);
1020 l_return BOOLEAN;
1021 l_value VARCHAR2 (100);
1022 l_opt_out_date DATE;
1023 l_input_value_id NUMBER;
1024 --
1025 BEGIN
1026 --
1027 IF g_debug
1028 THEN
1029 l_proc_step := 10;
1030 DEBUG ( 'Entering: '
1031 || l_proc_name, l_proc_step);
1032 END IF;
1033
1034 l_return := FALSE;
1035 --
1036 -- Get the input value id for opt out date
1037 --
1038 l_input_value_id :=
1039 get_input_value_id (
1040 p_input_value_name => 'Opt Out Date'
1041 ,p_element_type_id => p_element_type_id
1042 ,p_effective_date => p_effective_date
1043 );
1044
1045 IF g_debug
1046 THEN
1047 l_proc_step := 20;
1048 DEBUG (l_proc_name, l_proc_step);
1049 END IF;
1050
1051 -- Get the screen entry value
1052
1053 l_value :=
1054 get_ele_entry_value (
1055 p_element_entry_id => p_element_entry_id
1056 ,p_input_value_id => l_input_value_id
1057 ,p_effective_start_date => p_effective_start_date
1058 ,p_effective_end_date => p_effective_end_date
1059 );
1060
1061 IF l_value IS NOT NULL
1062 THEN
1063 l_opt_out_date := fnd_date.canonical_to_date (l_value);
1064
1065 IF l_opt_out_date <= p_effective_date
1066 THEN
1067 -- Opted out
1068 l_return := TRUE;
1069 END IF; -- End if of opt out date < effective date check ...
1070 END IF; -- End if of value specified check ...
1071
1072 IF g_debug
1073 THEN
1074 l_proc_step := 30;
1075 DEBUG ( 'Leaving: '
1076 || l_proc_name, l_proc_step);
1077 END IF;
1078
1079 RETURN l_return;
1080 --
1081 END get_ele_opt_out_info;
1082
1083 --
1084
1085 -- ----------------------------------------------------------------------------
1086 -- |----------------------------< chk_ele_entry_exists >----------------------|
1087 -- ----------------------------------------------------------------------------
1088 FUNCTION chk_ele_entry_exists (
1089 p_assignment_id IN NUMBER
1090 ,p_business_group_id IN NUMBER
1091 ,p_effective_date IN DATE
1092 ,p_element_type_id IN NUMBER
1093 ,p_opt_out_dt_chk IN BOOLEAN
1094 ,p_yes_no OUT NOCOPY VARCHAR2
1095 ,p_effective_start_date OUT NOCOPY DATE
1096 ,p_effective_end_date OUT NOCOPY DATE
1097 ,p_error_msg OUT NOCOPY VARCHAR2
1098 )
1099 RETURN NUMBER
1100 IS
1101 --
1102
1103 l_proc_name VARCHAR2 (80)
1104 := g_proc_name
1105 || 'chk_ele_entry_exists';
1106 l_proc_step NUMBER;
1107 l_ele_entry_info csr_chk_ele_entry_exists%ROWTYPE;
1108 l_yes_no VARCHAR2 (1);
1109 l_element_link_id NUMBER;
1110 l_opt_out BOOLEAN;
1111 --
1112 BEGIN
1113 --
1114 IF g_debug
1115 THEN
1116 l_proc_step := 10;
1117 DEBUG ( 'Entering: '
1118 || l_proc_name, l_proc_step);
1119 END IF;
1120
1121 --
1122 l_yes_no := 'N';
1123 -- Comment out the following line of code as a fix
1124 -- for Bug 3637584
1125 -- Get the element link id for this element
1126 -- l_element_link_id :=
1127 -- get_ele_link_info (
1128 -- p_element_type_id => p_element_type_id
1129 -- ,p_business_group_id => p_business_group_id
1130 -- ,p_effective_date => p_effective_date
1131 -- );
1132 --
1133 -- -- Only check for entry if a link exists
1134 -- IF l_element_link_id IS NOT NULL
1135 -- THEN
1136 -- IF g_debug
1137 -- THEN
1138 -- l_proc_step := 20;
1139 -- DEBUG (l_proc_name, l_proc_step);
1140 -- END IF;
1141
1142 -- Added new params for bug fix 3637584
1143 -- Check whether the element entry exists for this assignment
1144 OPEN csr_chk_ele_entry_exists (
1145 p_assignment_id
1146 ,p_element_type_id
1147 ,p_business_group_id
1148 ,p_effective_date
1149 );
1150 FETCH csr_chk_ele_entry_exists INTO l_ele_entry_info;
1151
1152 IF csr_chk_ele_entry_exists%FOUND
1153 THEN
1154 l_yes_no := 'Y';
1155 p_effective_start_date :=
1156 l_ele_entry_info.effective_start_date;
1157 p_effective_end_date := l_ele_entry_info.effective_end_date;
1158
1159 -- Do opt out date check if specified
1160 IF p_opt_out_dt_chk
1161 THEN
1162 l_opt_out := FALSE;
1163 l_opt_out :=
1164 get_ele_opt_out_info (
1165 p_element_type_id => p_element_type_id
1166 ,p_element_entry_id => l_ele_entry_info.element_entry_id
1167 ,p_effective_start_date => l_ele_entry_info.effective_start_date
1168 ,p_effective_end_date => l_ele_entry_info.effective_end_date
1169 ,p_effective_date => p_effective_date
1170 );
1171
1172 IF l_opt_out
1173 THEN
1174 -- yes opted out
1175 l_yes_no := 'O';
1176 END IF; -- End if of opt out is yes check ...
1177 END IF; -- End if of opt out dt check ...
1178 END IF; -- End if of ele entry exists ...
1179
1180 CLOSE csr_chk_ele_entry_exists;
1181 -- END IF; -- End if of ele link id is not null check ...
1182
1183 IF g_debug
1184 THEN
1185 DEBUG ( 'Yes or NO: '
1186 || l_yes_no);
1187 l_proc_step := 30;
1188 DEBUG ( 'Leaving: '
1189 || l_proc_name, l_proc_step);
1190 END IF;
1191
1192 p_yes_no := l_yes_no;
1193 RETURN 0;
1194 --
1195 EXCEPTION
1196 WHEN OTHERS
1197 THEN
1198 p_yes_no := NULL;
1199 p_effective_start_date := NULL;
1200 p_effective_end_date := NULL;
1201 p_error_msg := SQLERRM;
1202
1203 IF SQLCODE <> hr_utility.hr_error_number
1204 THEN
1205 debug_others (l_proc_name, l_proc_step);
1206
1207 IF g_debug
1208 THEN
1209 DEBUG ( 'Leaving: '
1210 || l_proc_name, -999);
1211 END IF;
1212
1213 fnd_message.raise_error;
1214 ELSE
1215 RAISE;
1216 END IF;
1217 END chk_ele_entry_exists;
1218
1219
1220 --
1221
1222 -- ----------------------------------------------------------------------------
1223 -- |----------------------------< chk_element_entry_exists >------------------|
1224 -- ----------------------------------------------------------------------------
1225 FUNCTION chk_element_entry_exists (
1226 p_assignment_id IN NUMBER
1227 ,p_business_group_id IN NUMBER
1228 ,p_effective_date IN DATE
1229 ,p_element_type_id IN NUMBER
1230 ,p_yes_no OUT NOCOPY VARCHAR2
1231 ,p_error_msg OUT NOCOPY VARCHAR2
1232 )
1233 RETURN NUMBER
1234 IS
1235 --
1236
1237 CURSOR csr_pen_sch_exist
1238 IS
1239 SELECT employee_deduction_method
1240 FROM pqp_gb_pension_schemes_v
1241 WHERE element_type_id = p_element_type_id;
1242
1243 l_proc_name VARCHAR2 (80)
1244 := g_proc_name
1245 || 'chk_element_entry_exists';
1246 l_proc_step NUMBER;
1247 l_yes_no VARCHAR2 (1);
1248 l_element_type_id NUMBER;
1249 l_effective_start_date DATE;
1250 l_effective_end_date DATE;
1251 l_tab_element_types_info t_element_types;
1252 l_emp_deduction_mthd pay_element_type_extra_info.eei_information1%TYPE;
1253 --
1254 BEGIN
1255 --
1256 IF g_debug
1257 THEN
1258 l_proc_step := 10;
1259 DEBUG ( 'Entering: '
1260 || l_proc_name, l_proc_step);
1261 END IF;
1262
1263 --
1264 -- Check whether information exists in cache
1265 --
1266
1267 l_tab_element_types_info := g_tab_element_types_info;
1268
1269 IF l_tab_element_types_info.EXISTS (p_element_type_id)
1270 AND -- check the effectiveness and assignment details
1271 (p_effective_date
1272 BETWEEN NVL (
1273 l_tab_element_types_info (p_element_type_id).effective_start_date
1274 ,p_effective_date
1275 )
1276 AND NVL (
1277 l_tab_element_types_info (p_element_type_id).effective_end_date
1278 ,p_effective_date
1279 )
1280 )
1281 AND l_tab_element_types_info (p_element_type_id).assignment_id =
1282 p_assignment_id
1283 THEN
1284 l_yes_no :=
1285 l_tab_element_types_info (p_element_type_id).yes_no_opt;
1286 ELSE -- Information not in cache
1287 --
1288 -- Call function to check whether element entry exists
1289 --
1290
1291 IF chk_ele_entry_exists (
1292 p_assignment_id => p_assignment_id
1293 ,p_business_group_id => p_business_group_id
1294 ,p_effective_date => p_effective_date
1295 ,p_element_type_id => p_element_type_id
1296 ,p_opt_out_dt_chk => FALSE
1297 ,p_yes_no => l_yes_no
1298 ,p_effective_start_date => l_effective_start_date
1299 ,p_effective_end_date => l_effective_end_date
1300 ,p_error_msg => p_error_msg
1301 ) <> 0
1302 THEN
1303 IF g_debug
1304 THEN
1305 DEBUG ( 'Yes or NO: '
1306 || l_yes_no);
1307 l_proc_step := 15;
1308 DEBUG ( 'Leaving: '
1309 || l_proc_name, l_proc_step);
1310 END IF;
1311
1312 RETURN -1;
1313 END IF; -- End if of function in error check ...
1314
1315 IF g_debug
1316 THEN
1317 l_proc_step := 20;
1318 DEBUG (l_proc_name, l_proc_step);
1319 END IF;
1320
1321 IF l_yes_no = 'N'
1322 THEN
1323 -- Check whether this scheme supports both deduction methods
1324 l_emp_deduction_mthd := NULL;
1325 OPEN csr_pen_sch_exist;
1326 FETCH csr_pen_sch_exist INTO l_emp_deduction_mthd;
1327 CLOSE csr_pen_sch_exist;
1328 IF g_debug
1329 THEN
1330 l_proc_step := 25;
1331 DEBUG (l_proc_name, l_proc_step);
1332 DEBUG ('l_emp_deduction_mthd: '||l_emp_deduction_mthd);
1333 END IF;
1334 IF l_emp_deduction_mthd = 'PEFR' THEN
1335 -- Check whether there is any other base element
1336 -- for this pension scheme name
1337 OPEN csr_get_sch_oth_ele_id (
1338 p_element_type_id
1339 ,p_business_group_id
1340 );
1341 FETCH csr_get_sch_oth_ele_id INTO l_element_type_id;
1342
1343 IF csr_get_sch_oth_ele_id%FOUND
1344 THEN
1345 IF g_debug
1346 THEN
1347 l_proc_step := 30;
1348 DEBUG (l_proc_name, l_proc_step);
1349 END IF;
1350
1351 l_yes_no := NULL;
1352
1353 IF chk_ele_entry_exists (
1354 p_assignment_id => p_assignment_id
1355 ,p_business_group_id => p_business_group_id
1356 ,p_effective_date => p_effective_date
1357 ,p_element_type_id => l_element_type_id
1358 ,p_opt_out_dt_chk => TRUE
1359 ,p_yes_no => l_yes_no
1360 ,p_effective_start_date => l_effective_start_date
1361 ,p_effective_end_date => l_effective_end_date
1362 ,p_error_msg => p_error_msg
1363 ) <> 0
1364 THEN
1365 IF g_debug
1366 THEN
1367 DEBUG ( 'Yes or NO: '
1368 || l_yes_no);
1369 l_proc_step := 40;
1370 DEBUG ( 'Leaving: '
1371 || l_proc_name, l_proc_step);
1372 END IF;
1373
1374 CLOSE csr_get_sch_oth_ele_id;
1375 RETURN -1;
1376 END IF; -- End if of function in error check ...
1377 END IF; -- End if of row found check ...
1378
1379 CLOSE csr_get_sch_oth_ele_id;
1380 END IF; -- End if of pen scheme exists ...
1381 END IF; -- yes no is N check ...
1382
1383 -- Update the cache
1384 l_tab_element_types_info (p_element_type_id).element_type_id :=
1385 p_element_type_id;
1386 l_tab_element_types_info (p_element_type_id).assignment_id :=
1387 p_assignment_id;
1388 l_tab_element_types_info (p_element_type_id).effective_start_date :=
1389 l_effective_start_date;
1390 l_tab_element_types_info (p_element_type_id).effective_end_date :=
1391 l_effective_end_date;
1392 l_tab_element_types_info (p_element_type_id).yes_no_opt := l_yes_no;
1393 -- Store it in global collection
1394 g_tab_element_types_info := l_tab_element_types_info;
1395 END IF; -- End if of check whether information is in cache...
1396
1397 IF g_debug
1398 THEN
1399 DEBUG ( 'Yes or NO: '
1400 || l_yes_no);
1401 l_proc_step := 50;
1402 DEBUG ( 'Leaving: '
1403 || l_proc_name, l_proc_step);
1404 END IF;
1405
1406 p_yes_no := l_yes_no;
1407 RETURN 0;
1408 --
1409 EXCEPTION
1410 WHEN OTHERS
1411 THEN
1412 p_yes_no := NULL;
1413 p_error_msg := SQLERRM;
1414
1415 IF SQLCODE <> hr_utility.hr_error_number
1416 THEN
1417 debug_others (l_proc_name, l_proc_step);
1418
1419 IF g_debug
1420 THEN
1421 DEBUG ( 'Leaving: '
1422 || l_proc_name, -999);
1423 END IF;
1424
1425 fnd_message.raise_error;
1426 ELSE
1427 RAISE;
1428 END IF;
1429 END chk_element_entry_exists;
1430 --
1431
1432 --
1433 END pqp_gb_pension_functions;