[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_EARNINGS_HISTORY
Source
1 PACKAGE BODY PQP_GB_PSI_EARNINGS_HISTORY
2 -- /* $Header: pqpgbpsiern.pkb 120.16.12010000.4 2008/08/05 14:07:40 ubhat ship $ */
3 AS
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------------< debug >---------------------------------|
6 -- ----------------------------------------------------------------------------
7
8 PROCEDURE DEBUG(p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
9 IS
10 --
11 BEGIN
12 --
13
14 pqp_utilities.DEBUG(
15 p_trace_message => p_trace_message
16 ,p_trace_location => p_trace_location
17 );
18 --
19 END DEBUG;
20
21 -- This procedure is used for debug purposes
22 -- debug_enter checks the debug flag and sets the trace on/off
23 --
24 -- ----------------------------------------------------------------------------
25 -- |----------------------------< debug_enter >-------------------------------|
26 -- ----------------------------------------------------------------------------
27
28 PROCEDURE debug_enter(p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
29 IS
30 BEGIN
31 --
32 IF pqp_utilities.g_nested_level = 0
33 THEN
34 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
35 END IF;
36
37 -- g_nested_level := g_nested_level + 1;
38 -- debug('Entering: ' || NVL(p_proc_name, g_proc_name)
39 -- ,g_nested_level * 100);
40
41 pqp_utilities.debug_enter(p_proc_name => p_proc_name
42 ,p_trace_on => p_trace_on);
43 --
44 END debug_enter;
45
46 -- This procedure is used for debug purposes
47 --
48 -- ----------------------------------------------------------------------------
49 -- |----------------------------< debug_exit >--------------------------------|
50 -- ----------------------------------------------------------------------------
51
52 PROCEDURE debug_exit(p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
53 IS
54 BEGIN
55 --
56 -- DEBUG (
57 -- 'Leaving: '
58 -- || NVL (p_proc_name, g_proc_name),
59 -- -g_nested_level * 100
60 -- );
61 -- g_nested_level := g_nested_level
62 -- - 1;
63 pqp_utilities.debug_exit(p_proc_name => p_proc_name
64 ,p_trace_off => p_trace_off);
65
66 -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
67 -- so we must turn it off for the same condition
68 -- Also turn off tracing when the override flag of p_trace_off has been passed as Y
69 IF pqp_utilities.g_nested_level = 0
70 THEN
71 hr_utility.trace_off;
72 END IF; -- (g_nested_level = 0
73
74 --
75 END debug_exit;
76
77 -- This procedure is used for debug purposes
78 --
79 -- ----------------------------------------------------------------------------
80 -- |----------------------------< debug_others >------------------------------|
81 -- ----------------------------------------------------------------------------
82
83 PROCEDURE debug_others(p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
84 IS
85 BEGIN
86 --
87 pqp_utilities.debug_others(p_proc_name => p_proc_name
88 ,p_proc_step => p_proc_step);
89 --
90 END debug_others;
91
92 -- This procedure is used to clear all cached global variables
93 --
94 -- ----------------------------------------------------------------------------
95 -- |----------------------------< clear_cache >-------------------------------|
96 -- ----------------------------------------------------------------------------
97 PROCEDURE clear_cache
98 IS
99 --
100 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
101 l_proc_step PLS_INTEGER;
102 --
103 BEGIN
104 --
105 IF g_debug
106 THEN
107 l_proc_step := 10;
108 debug_enter(l_proc_name);
109 END IF;
110
111 -- Clear all global variables first
112 g_business_group_id := NULL;
113 g_effective_date := NULL;
114 g_extract_type := NULL;
115 g_paypoint := NULL;
116 g_cutover_date := NULL;
117 g_ext_dfn_id := NULL;
118 g_ni_ele_type_id := NULL;
119 g_ni_category_iv_id := NULL;
120 g_ni_pension_iv_id := NULL;
121 g_ni_euel_bal_type_id := NULL;
122 g_ni_euel_ptd_bal_id := NULL;
123 g_ni_eet_bal_type_id := NULL;
124 g_ni_eet_ptd_bal_id := NULL;
125 -- Commenting the below variables as not used
126 /* g_tot_byb_cont_bal_id := NULL;
127 g_tot_byb_ptd_bal_id := NULL; */
128
129 g_tot_ayr_cont_bal_id := NULL;
130 g_tot_ayr_ptd_bal_id := NULL;
131 -- For 115.29
132 g_tot_ayr_ytd_bal_id := NULL;
133
134 g_tot_ayr_fb_cont_bal_id := NULL;
135 g_tot_ayr_fb_ptd_bal_id := NULL;
136 -- For 115.29
137 g_tot_ayr_fb_ytd_bal_id := NULL;
138
139 /* BEGIN Nuvos changes */
140 g_tot_apavc_cont_bal_id := NULL;
141 g_tot_apavc_ptd_bal_id := NULL;
142 -- For 115.29
143 g_tot_apavc_ytd_bal_id := NULL;
144
145 g_tot_apavcm_cont_bal_id := NULL;
146 g_tot_apavcm_ptd_bal_id := NULL;
147 -- For 115.29
148 g_tot_apavcm_ytd_bal_id := NULL;
149
150 /* END Nuvos Changes */
151
152 g_effective_start_date := NULL;
153 g_effective_end_date := NULL;
154 g_procptd_dimension_id := NULL;
155 -- For 115.29
156 g_penytd_dimension_id := NULL;
157 g_tdptd_dimension_id := NULL;
158 g_ayfwd_bal_conts := NULL;
159 -- Clear all global collections
160 g_tab_clas_pen_bal_dtls.DELETE;
161 g_tab_clap_pen_bal_dtls.DELETE;
162 g_tab_prem_pen_bal_dtls.DELETE;
163 g_tab_part_pen_bal_dtls.DELETE;
164 g_tab_pen_sch_map_cv.DELETE;
165 g_tab_pen_ele_ids.DELETE;
166 g_tab_prs_dfn_cv.DELETE;
167 g_tab_eei_info.DELETE;
168 g_tab_avc_pen_bal_dtls.DELETE;
169 g_tab_ni_cont_out_bals.DELETE;
170
171 g_tab_nuvos_pen_bal_dtls.DELETE; -- For Nuvos
172
173 IF g_debug
174 THEN
175 debug_exit(l_proc_name);
176 END IF;
177 EXCEPTION
178 WHEN OTHERS
179 THEN
180 IF SQLCODE <> hr_utility.hr_error_number
181 THEN
182 debug_others(l_proc_name, l_proc_step);
183
184 IF g_debug
185 THEN
186 DEBUG('Leaving: ' || l_proc_name, -999);
187 END IF;
188
189 fnd_message.raise_error;
190 ELSE
191 RAISE;
192 END IF;
193 END clear_cache;
194
195 -- This procedure is used to clear all cached assignment variables
196 --
197 -- ----------------------------------------------------------------------------
198 -- |----------------------------< clear_asg_cache >---------------------------|
199 -- ----------------------------------------------------------------------------
200 PROCEDURE clear_asg_cache
201 IS
202 --
203 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_asg_cache';
204 l_proc_step PLS_INTEGER;
205 --
206 BEGIN
207 --
208 IF g_debug
209 THEN
210 l_proc_step := 10;
211 debug_enter(l_proc_name);
212 END IF;
213
214 g_assignment_id := NULL;
215 g_ni_ele_ent_details := NULL;
216 g_ni_e_cat_exists := NULL;
217 g_member := 'N';
218
219 IF g_debug
220 THEN
221 debug_exit(l_proc_name);
222 END IF;
223 EXCEPTION
224 WHEN OTHERS
225 THEN
226 IF SQLCODE <> hr_utility.hr_error_number
227 THEN
228 debug_others(l_proc_name, l_proc_step);
229
230 IF g_debug
231 THEN
232 DEBUG('Leaving: ' || l_proc_name, -999);
233 END IF;
234
235 fnd_message.raise_error;
236 ELSE
237 RAISE;
238 END IF;
239 END clear_asg_cache;
240
241 -- This function returns the element name for a given element type id
242 -- and effective date
243 -- ----------------------------------------------------------------------------
244 -- |----------------------------< get_element_name >--------------------------|
245 -- ----------------------------------------------------------------------------
246 FUNCTION get_element_name(
247 p_element_type_id IN NUMBER
248 ,p_effective_date IN DATE
249 )
250 RETURN VARCHAR2
251 IS
252 --
253 -- Cursor to get element name
254 CURSOR csr_get_element_name
255 IS
256 SELECT petl.element_name
257 FROM pay_element_types_f pet, pay_element_types_f_tl petl
258 WHERE petl.element_type_id = pet.element_type_id
259 AND petl.LANGUAGE = USERENV('LANG')
260 AND pet.element_type_id = p_element_type_id
261 AND p_effective_date BETWEEN pet.effective_start_date
262 AND pet.effective_end_date;
263
264 l_proc_name VARCHAR2(80) := g_proc_name || 'get_element_name';
265 l_proc_step PLS_INTEGER;
266 l_element_name pay_element_types_f.element_name%TYPE;
267 --
268 BEGIN
269 --
270 IF g_debug
271 THEN
272 l_proc_step := 10;
273 debug_enter(l_proc_name);
274 DEBUG('p_element_type_id: ' || p_element_type_id);
275 DEBUG('p_effective_date: '
276 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
277 END IF;
278
279 OPEN csr_get_element_name;
280 FETCH csr_get_element_name INTO l_element_name;
281 CLOSE csr_get_element_name;
282
283 IF g_debug
284 THEN
285 l_proc_step := 20;
286 DEBUG('l_element_name: ' || l_element_name);
287 debug_exit(l_proc_name);
288 END IF;
289
290 RETURN l_element_name;
291 EXCEPTION
292 WHEN OTHERS
293 THEN
294 clear_cache;
295
296 IF SQLCODE <> hr_utility.hr_error_number
297 THEN
298 debug_others(l_proc_name, l_proc_step);
299
300 IF g_debug
301 THEN
302 DEBUG('Leaving: ' || l_proc_name, -999);
303 END IF;
304
305 fnd_message.raise_error;
306 ELSE
307 RAISE;
308 END IF;
309 END get_element_name;
310
311 -- This function returns input value id for a given element type id
312 -- and input value name
313 -- ----------------------------------------------------------------------------
314 -- |----------------------------< get_input_value_id >------------------------|
315 -- ----------------------------------------------------------------------------
316 FUNCTION get_input_value_id(
317 p_element_type_id IN NUMBER
318 ,p_effective_date IN DATE
319 ,p_input_value_name IN VARCHAR2
320 ,p_element_name IN VARCHAR2
321 )
322 RETURN NUMBER
323 IS
324 --
325 -- Cursor to get input value id
326 CURSOR csr_get_iv_id
327 IS
328 SELECT input_value_id
329 FROM pay_input_values_f
330 WHERE element_type_id = p_element_type_id
331 AND NAME = p_input_value_name
332 AND p_effective_date BETWEEN effective_start_date
333 AND effective_end_date;
334
335 l_proc_name VARCHAR2(80) := g_proc_name || 'get_input_value_id';
336 l_proc_step PLS_INTEGER;
337 l_input_value_id NUMBER;
338 --
339 BEGIN
340 --
341 IF g_debug
342 THEN
343 l_proc_step := 10;
344 debug_enter(l_proc_name);
345 DEBUG('p_element_type_id: ' || p_element_type_id);
346 DEBUG('p_effective_date: '
347 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
348 DEBUG('p_input_value_name: ' || p_input_value_name);
349 DEBUG('p_element_name: ' || p_element_name);
350 END IF;
351
352 OPEN csr_get_iv_id;
353 FETCH csr_get_iv_id INTO l_input_value_id;
354
355 IF csr_get_iv_id%NOTFOUND
356 THEN
357 -- Raise setup error
358 pqp_gb_psi_functions.store_extract_exceptions(
359 p_extract_type => 'EARNINGS_HISTORY'
360 ,p_error_number => 92493
361 ,p_error_text => 'BEN_92493_EXT_PSI_NO_INPUT_VAL'
362 ,p_token1 => p_element_name
363 ,p_token2 => p_input_value_name
364 ,p_token3 => fnd_date.date_to_displaydt(p_effective_date)
365 ,p_error_warning_flag => 'E'
366 );
367 END IF; -- End if of row not found check ...
368
369 CLOSE csr_get_iv_id;
370
371 IF g_debug
372 THEN
373 l_proc_step := 20;
374 DEBUG('l_input_value_id: ' || l_input_value_id);
375 debug_exit(l_proc_name);
376 END IF;
377
378 RETURN l_input_value_id;
379 EXCEPTION
380 WHEN OTHERS
381 THEN
382 clear_cache;
383
384 IF SQLCODE <> hr_utility.hr_error_number
385 THEN
386 debug_others(l_proc_name, l_proc_step);
387
388 IF g_debug
389 THEN
390 DEBUG('Leaving: ' || l_proc_name, -999);
391 END IF;
392
393 fnd_message.raise_error;
394 ELSE
395 RAISE;
396 END IF;
397 END get_input_value_id;
398
399 -- This function returns template id for a given template name
400 -- and business group id
401 -- ----------------------------------------------------------------------------
402 -- |----------------------------< get_template_id >---------------------------|
403 -- ----------------------------------------------------------------------------
404 FUNCTION get_template_id(
405 p_template_name IN VARCHAR2
406 ,p_business_group_id IN NUMBER
407 ,p_template_type IN VARCHAR2
408 )
409 RETURN NUMBER
410 IS
411 --
412 -- Cursor to get template id
413 CURSOR csr_get_template_id
414 IS
415 SELECT template_id
416 FROM pay_element_templates
417 WHERE template_name = p_template_name
418 AND template_type = p_template_type
419 AND (
420 (
421 p_business_group_id IS NOT NULL
422 AND business_group_id = p_business_group_id
423 )
424 OR (
425 business_group_id IS NULL AND p_business_group_id IS NULL
426 )
427 );
428
429 l_proc_name VARCHAR2(80) := g_proc_name || 'get_template_id';
430 l_proc_step PLS_INTEGER;
431 l_template_id NUMBER;
432 --
433 BEGIN
434 --
435 IF g_debug
436 THEN
437 l_proc_step := 10;
438 debug_enter(l_proc_name);
439 DEBUG('p_template_name: ' || p_template_name);
440 DEBUG('p_business_group_id: ' || p_business_group_id);
441 DEBUG('p_template_type: ' || p_template_type);
442 END IF;
443
444 OPEN csr_get_template_id;
445 FETCH csr_get_template_id INTO l_template_id;
446 CLOSE csr_get_template_id;
447
448 IF g_debug
449 THEN
450 l_proc_step := 20;
451 DEBUG('l_template_id: ' || l_template_id);
452 debug_exit(l_proc_name);
453 END IF;
454
455 RETURN l_template_id;
456 EXCEPTION
457 WHEN OTHERS
458 THEN
459 clear_cache;
460
461 IF SQLCODE <> hr_utility.hr_error_number
462 THEN
463 debug_others(l_proc_name, l_proc_step);
464
465 IF g_debug
466 THEN
467 DEBUG('Leaving: ' || l_proc_name, -999);
468 END IF;
469
470 fnd_message.raise_error;
471 ELSE
472 RAISE;
473 END IF;
474 END get_template_id;
475
476 -- This procedure gets element extra information for a given element type
477 -- and information type
478 -- ----------------------------------------------------------------------------
479 -- |----------------------------< get_eeit_info >-----------------------------|
480 -- ----------------------------------------------------------------------------
481 PROCEDURE get_eeit_info(
482 p_element_type_id IN NUMBER
483 ,p_information_type IN VARCHAR2
484 ,p_rec_eeit_info OUT NOCOPY pay_element_type_extra_info%ROWTYPE
485 )
486 IS
487 --
488 -- Cursor to get eei information
489 CURSOR csr_get_eei_info
490 IS
491 SELECT *
492 FROM pay_element_type_extra_info
493 WHERE element_type_id = p_element_type_id
494 AND information_type = p_information_type;
495
496 l_proc_name VARCHAR2(80) := g_proc_name || 'get_eeit_info';
497 l_proc_step PLS_INTEGER;
498 l_rec_eeit_info pay_element_type_extra_info%ROWTYPE;
499 --
500 BEGIN
501 --
502 IF g_debug
503 THEN
504 l_proc_step := 10;
505 debug_enter(l_proc_name);
506 DEBUG('p_element_type_id: ' || p_element_type_id);
507 DEBUG('p_information_type: ' || p_information_type);
508 END IF;
509
510 OPEN csr_get_eei_info;
511 FETCH csr_get_eei_info INTO l_rec_eeit_info;
512
513 IF csr_get_eei_info%NOTFOUND
514 THEN
515 -- Raise setup error
516 pqp_gb_psi_functions.store_extract_exceptions(
517 p_extract_type => 'EARNINGS_HISTORY'
518 ,p_error_number => 92583
519 ,p_error_text => 'BEN_92583_EXT_PSI_NO_EEI_INFO'
520 ,p_token1 => g_tab_pen_ele_ids(p_element_type_id).element_name
521 ,p_token2 => p_information_type
522 ,p_error_warning_flag => 'E'
523 );
524 END IF; -- End if of row not found check ...
525
526 CLOSE csr_get_eei_info;
527 p_rec_eeit_info := l_rec_eeit_info;
528
529 IF g_debug
530 THEN
531 l_proc_step := 20;
532 DEBUG(
533 'l_rec_eeit_info.eei_information1: '
534 || l_rec_eeit_info.eei_information1
535 );
536 DEBUG(
537 'l_rec_eeit_info.eei_information2: '
538 || l_rec_eeit_info.eei_information2
539 );
540 DEBUG(
541 'l_rec_eeit_info.eei_information3: '
542 || l_rec_eeit_info.eei_information3
543 );
544 DEBUG(
545 'l_rec_eeit_info.eei_information4: '
546 || l_rec_eeit_info.eei_information4
547 );
548 DEBUG(
549 'l_rec_eeit_info.eei_information5: '
550 || l_rec_eeit_info.eei_information5
551 );
552 DEBUG(
553 'l_rec_eeit_info.eei_information6: '
554 || l_rec_eeit_info.eei_information6
555 );
556 DEBUG(
557 'l_rec_eeit_info.eei_information7: '
558 || l_rec_eeit_info.eei_information7
559 );
560 DEBUG(
561 'l_rec_eeit_info.eei_information8: '
562 || l_rec_eeit_info.eei_information8
563 );
564 DEBUG(
565 'l_rec_eeit_info.eei_information9: '
566 || l_rec_eeit_info.eei_information9
567 );
568 DEBUG(
569 'l_rec_eeit_info.eei_information10: '
570 || l_rec_eeit_info.eei_information10
571 );
572 DEBUG(
573 'l_rec_eeit_info.eei_information11: '
574 || l_rec_eeit_info.eei_information11
575 );
576 DEBUG(
577 'l_rec_eeit_info.eei_information12: '
578 || l_rec_eeit_info.eei_information12
579 );
580 DEBUG(
581 'l_rec_eeit_info.eei_information13: '
582 || l_rec_eeit_info.eei_information13
583 );
584 DEBUG(
585 'l_rec_eeit_info.eei_information14: '
586 || l_rec_eeit_info.eei_information14
587 );
588 DEBUG(
589 'l_rec_eeit_info.eei_information15: '
590 || l_rec_eeit_info.eei_information15
591 );
592 DEBUG(
593 'l_rec_eeit_info.eei_information16: '
594 || l_rec_eeit_info.eei_information16
595 );
596 DEBUG(
597 'l_rec_eeit_info.eei_information17: '
598 || l_rec_eeit_info.eei_information17
599 );
600 DEBUG(
601 'l_rec_eeit_info.eei_information18: '
602 || l_rec_eeit_info.eei_information18
603 );
604 DEBUG(
605 'l_rec_eeit_info.eei_information19: '
606 || l_rec_eeit_info.eei_information19
607 );
608 DEBUG(
609 'l_rec_eeit_info.eei_information20: '
610 || l_rec_eeit_info.eei_information20
611 );
612 debug_exit(l_proc_name);
613 END IF;
614 EXCEPTION
615 WHEN OTHERS
616 THEN
617 clear_cache;
618
619 IF SQLCODE <> hr_utility.hr_error_number
620 THEN
621 debug_others(l_proc_name, l_proc_step);
622
623 IF g_debug
624 THEN
625 DEBUG('Leaving: ' || l_proc_name, -999);
626 END IF;
627
628 fnd_message.raise_error;
629 ELSE
630 RAISE;
631 END IF;
632 END get_eeit_info;
633 /* BEGIN Nuvos Change */
634 -- This procedure gets assignment extra information for a given assignment
635 -- ----------------------------------------------------------------------------
636 -- |----------------------------< get_asg_eit_info >-----------------------------|
637 -- ----------------------------------------------------------------------------
638 FUNCTION get_asg_eit_info(
639 p_assignment_id IN NUMBER
640 ,p_information_type IN VARCHAR2
641 )
642 RETURN VARCHAR2
643
644 IS
645 --
646 -- Cursor to get eei information
647 CURSOR csr_get_aei_info
648 IS
649 SELECT aei_information1
650 FROM per_assignment_extra_info
651 WHERE assignment_id = p_assignment_id
652 AND information_type = p_information_type;
653
654 l_proc_name VARCHAR2(80) := g_proc_name || 'get_asg_eit_info';
655 l_proc_step PLS_INTEGER;
656 l_svpn_no per_assignment_extra_info.aei_information1%TYPE;
657 l_value NUMBER;
658 --
659 BEGIN
660 --
661 IF g_debug
662 THEN
663 l_proc_step := 10;
664 debug_enter(l_proc_name);
665 DEBUG('p_assignment_id: ' || p_assignment_id);
666 DEBUG('p_information_type: ' || p_information_type);
667 END IF;
668
669 OPEN csr_get_aei_info;
670 FETCH csr_get_aei_info INTO l_svpn_no;
671
672 IF csr_get_aei_info%NOTFOUND
673 THEN
674 l_svpn_no := '01';
675 -- l_value := pqp_gb_psi_functions.raise_extract_warning(p_error_text => 'SPN not found');
676 /* -- Raise setup error
677 pqp_gb_psi_functions.store_extract_exceptions(
678 p_extract_type => 'EARNINGS_HISTORY'
679 ,p_error_number => 92583
680 ,p_error_text => 'BEN_92583_EXT_PSI_NO_EEI_INFO'
681 ,p_token1 => p_assignment_id
682 ,p_token2 => p_information_type
683 ,p_error_warning_flag => 'W'
684 );*/
685 END IF; -- End if of row not found check ...
686
687 CLOSE csr_get_aei_info;
688
689 RETURN l_svpn_no;
690
691 IF g_debug
692 THEN
693 l_proc_step := 20;
694 DEBUG(
695 'l_svpn_no: '
696 || l_svpn_no
697 );
698
699 debug_exit(l_proc_name);
700 END IF;
701 EXCEPTION
702 WHEN OTHERS
703 THEN
704 clear_cache;
705
706 IF SQLCODE <> hr_utility.hr_error_number
707 THEN
708 debug_others(l_proc_name, l_proc_step);
709
710 IF g_debug
711 THEN
712 DEBUG('Leaving: ' || l_proc_name, -999);
713 END IF;
714
715 fnd_message.raise_error;
716 ELSE
717 RAISE;
718 END IF;
719 END get_asg_eit_info;
720
721 /* END NUvos Change */
722
723 -- This function returns ptd balance value over a date range
724 -- for a given assignment id, effective date range and balance dimension
725 -- ----------------------------------------------------------------------------
726 -- |----------------------------< get_total_ptd_bal_value >-------------------|
727 -- ----------------------------------------------------------------------------
728 FUNCTION get_total_ptd_bal_value(
729 p_assignment_id IN NUMBER
730 ,p_defined_balance_id IN NUMBER
731 ,p_effective_start_date IN DATE
732 ,p_effective_end_date IN DATE
733 )
734 RETURN NUMBER
735 IS
736 --
737 -- Cursor to fetch end date from time period
738 CURSOR csr_get_end_date
739 IS
740 SELECT /*+ leading(paa) */
741 DISTINCT (ptp.end_date) end_date
742 FROM pay_assignment_actions paa
743 ,pay_payroll_actions ppa
744 ,per_time_periods ptp
745 WHERE ptp.time_period_id = ppa.time_period_id
746 AND ppa.payroll_action_id = paa.payroll_action_id
747 AND ppa.effective_date BETWEEN p_effective_start_date AND p_effective_end_date
748 AND ppa.action_type IN('R', 'Q', 'I', 'V', 'B')
749 AND NVL(ppa.business_group_id, g_business_group_id) = g_business_group_id
750 AND paa.assignment_id = p_assignment_id
751 ORDER BY ptp.end_date;
752
753 l_proc_name VARCHAR2(80)
754 := g_proc_name || 'get_balance_type_id';
755 l_proc_step PLS_INTEGER;
756 l_balance_value NUMBER;
757 l_total_balance_value NUMBER;
758 l_effective_date DATE;
759
760 TYPE t_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
761 l_ptpenddt t_date;
762
763 l_ptpenddt1 t_date;
764 --
765 BEGIN
766 --
767 IF g_debug
768 THEN
769 l_proc_step := 10;
770 debug_enter(l_proc_name);
771 DEBUG('p_assignment_id: ' || p_assignment_id);
772 DEBUG('p_defined_balance_id: ' || p_defined_balance_id);
773 DEBUG(
774 'p_effective_start_date: '
775 || TO_CHAR(p_effective_start_date, 'DD/MON/YYYY')
776 );
777 DEBUG(
778 'p_effective_end_date: '
779 || TO_CHAR(p_effective_end_date, 'DD/MON/YYYY')
780 );
781 END IF;
782
783 l_total_balance_value := 0;
784
785 l_ptpenddt := l_ptpenddt1;
786
787 OPEN csr_get_end_date;
788 FETCH csr_get_end_date BULK COLLECT INTO l_ptpenddt;
789 CLOSE csr_get_end_date;
790
791 DEBUG('lptpenddt.count: ' || l_ptpenddt.count);
792
793 IF l_ptpenddt.count > 0
794 THEN
795 FOR ptpi in l_ptpenddt.first..l_ptpenddt.last
796 LOOP
797
798 l_balance_value := 0;
799
800 IF g_debug
801 THEN
802 l_proc_step := 20;
803 DEBUG(l_proc_name, l_proc_step);
804 DEBUG('Before calling function pay_balance_pkg.get_value');
805 DEBUG( 'l_ptpenddt: ' || TO_CHAR(l_ptpenddt(ptpi), 'DD/MON/YYYY'));
806 END IF;
807
808 BEGIN
809 l_balance_value :=
810 pay_balance_pkg.get_value(
811 p_defined_balance_id => p_defined_balance_id
812 ,p_assignment_id => p_assignment_id
813 ,p_virtual_date => l_ptpenddt(ptpi)
814 );
815 EXCEPTION
816 WHEN NO_DATA_FOUND
817 THEN
818 IF g_debug
819 THEN
820 DEBUG('Exception: No data found');
821 END IF;
822
823 l_balance_value := 0;
824 END;
825
826 IF g_debug
827 THEN
828 DEBUG('Balance Value: ' || TO_CHAR(l_balance_value));
829 END IF;
830
831 l_total_balance_value := l_total_balance_value + l_balance_value;
832
833 END LOOP;
834 END IF;
835
836
837 /* LOOP --Commented as bul collect logic is used
838 FETCH csr_get_end_date INTO l_effective_date;
839 EXIT WHEN csr_get_end_date%NOTFOUND;
840 l_balance_value := 0;
841
842 IF g_debug
843 THEN
844 l_proc_step := 20;
845 DEBUG(l_proc_name, l_proc_step);
846 DEBUG('Before calling function pay_balance_pkg.get_value');
847 DEBUG(
848 'l_effective_date: '
849 || TO_CHAR(l_effective_date, 'DD/MON/YYYY')
850 );
851 END IF;
852
853 BEGIN
854 l_balance_value :=
855 pay_balance_pkg.get_value(
856 p_defined_balance_id => p_defined_balance_id
857 ,p_assignment_id => p_assignment_id
858 ,p_virtual_date => l_effective_date
859 );
860 EXCEPTION
861 WHEN NO_DATA_FOUND
862 THEN
863 IF g_debug
864 THEN
865 DEBUG('Exception: No data found');
866 END IF;
867
868 l_balance_value := 0;
869 END;
870
871 IF g_debug
872 THEN
873 DEBUG('Balance Value: ' || TO_CHAR(l_balance_value));
874 END IF;
875
876 l_total_balance_value := l_total_balance_value + l_balance_value;
877 END LOOP;
878
879 CLOSE csr_get_end_date; */
880
881 IF g_debug
882 THEN
883 l_proc_step := 30;
884 DEBUG('l_total_balance_value: ' || l_total_balance_value);
885 debug_exit(l_proc_name);
886 END IF;
887
888 RETURN l_total_balance_value;
889 EXCEPTION
890 WHEN OTHERS
891 THEN
892 clear_cache;
893
894 IF SQLCODE <> hr_utility.hr_error_number
895 THEN
896 debug_others(l_proc_name, l_proc_step);
897
898 IF g_debug
899 THEN
900 DEBUG('Leaving: ' || l_proc_name, -999);
901 END IF;
902
903 fnd_message.raise_error;
904 ELSE
905 RAISE;
906 END IF;
907 END get_total_ptd_bal_value;
908
909 -- This function returns balance type id for a given balance name
910 -- and business group id and legislation code
911 -- ----------------------------------------------------------------------------
912 -- |----------------------------< get_balance_type_id >------------------------|
913 -- ----------------------------------------------------------------------------
914 FUNCTION get_balance_type_id(
915 p_balance_name IN VARCHAR2
916 ,p_business_group_id IN NUMBER
917 ,p_legislation_code IN VARCHAR2
918 )
919 RETURN NUMBER
920 IS
921 --
922 -- Cursor to get balance type id
923 CURSOR csr_get_bal_id
924 IS
925 SELECT balance_type_id
926 FROM pay_balance_types
927 WHERE balance_name = p_balance_name
928 AND (
929 (business_group_id = p_business_group_id)
930 OR (
931 business_group_id IS NULL
932 AND (
933 legislation_code IS NULL
934 OR legislation_code = p_legislation_code
935 )
936 )
937 );
938
939 l_proc_name VARCHAR2(80) := g_proc_name || 'get_balance_type_id';
940 l_proc_step PLS_INTEGER;
941 l_balance_type_id NUMBER;
942 --
943 BEGIN
944 --
945 IF g_debug
946 THEN
947 l_proc_step := 10;
948 debug_enter(l_proc_name);
949 DEBUG('p_balance_name: ' || p_balance_name);
950 DEBUG('p_business_group_id: ' || p_business_group_id);
951 DEBUG('p_legislation_code: ' || p_legislation_code);
952 END IF;
953
954 OPEN csr_get_bal_id;
955 FETCH csr_get_bal_id INTO l_balance_type_id;
956
957 -- Added For Bug 6082532
958 IF csr_get_bal_id%NOTFOUND
959 AND NOT (p_balance_name LIKE '%Buy Back FWC Contribution'
960 or
961 p_balance_name LIKE '%Added Years Family Benefit')
962 THEN
963 -- Raise setup error
964 pqp_gb_psi_functions.store_extract_exceptions(
965 p_extract_type => 'EARNINGS_HISTORY'
966 ,p_error_number => 92642
967 ,p_error_text => 'BEN_92642_EXT_PSI_BAL_NOTFOUND'
968 ,p_token1 => p_balance_name
969 ,p_token2 => NULL
970 ,p_error_warning_flag => 'E'
971 );
972 END IF; -- End if of row not found check ...
973
974 CLOSE csr_get_bal_id;
975
976 IF g_debug
977 THEN
978 l_proc_step := 20;
979 DEBUG('l_balance_type_id: ' || l_balance_type_id);
980 debug_exit(l_proc_name);
981 END IF;
982
983 RETURN l_balance_type_id;
984 EXCEPTION
985 WHEN OTHERS
986 THEN
987 clear_cache;
988
989 IF SQLCODE <> hr_utility.hr_error_number
990 THEN
991 debug_others(l_proc_name, l_proc_step);
992
993 IF g_debug
994 THEN
995 DEBUG('Leaving: ' || l_proc_name, -999);
996 END IF;
997
998 fnd_message.raise_error;
999 ELSE
1000 RAISE;
1001 END IF;
1002 END get_balance_type_id;
1003
1004 -- This function returns balance dimension id for a given dimension name
1005 -- and legislation code, business group
1006 -- ----------------------------------------------------------------------------
1007 -- |---------------------------< get_bal_dimension_id >-----------------------|
1008 -- ----------------------------------------------------------------------------
1009 FUNCTION get_bal_dimension_id(
1010 p_dimension_name IN VARCHAR2
1011 ,p_business_group_id IN NUMBER
1012 ,p_legislation_code IN VARCHAR2
1013 )
1014 RETURN NUMBER
1015 IS
1016 --
1017 -- Cursor to get bal dimension id
1018 CURSOR csr_get_bal_dimension_id
1019 IS
1020 SELECT balance_dimension_id
1021 FROM pay_balance_dimensions
1022 WHERE dimension_name = p_dimension_name
1023 AND (
1024 (business_group_id = p_business_group_id)
1025 OR (
1026 business_group_id IS NULL
1027 AND (
1028 legislation_code IS NULL
1029 OR legislation_code = p_legislation_code
1030 )
1031 )
1032 );
1033
1034 l_proc_name VARCHAR2(80)
1035 := g_proc_name || 'get_bal_dimension_id';
1036 l_proc_step PLS_INTEGER;
1037 l_bal_dimension_id NUMBER;
1038 --
1039 BEGIN
1040 --
1041 IF g_debug
1042 THEN
1043 l_proc_step := 10;
1044 debug_enter(l_proc_name);
1045 DEBUG('p_dimension_name: ' || p_dimension_name);
1046 DEBUG('p_business_group_id: ' || p_business_group_id);
1047 DEBUG('p_legislation_code: ' || p_legislation_code);
1048 END IF;
1049
1050 OPEN csr_get_bal_dimension_id;
1051 FETCH csr_get_bal_dimension_id INTO l_bal_dimension_id;
1052
1053 IF csr_get_bal_dimension_id%NOTFOUND
1054 THEN
1055 -- Raise setup error
1056 pqp_gb_psi_functions.store_extract_exceptions(
1057 p_extract_type => 'EARNINGS_HISTORY'
1058 ,p_error_number => 92766
1059 ,p_error_text => 'BEN_92766_EXT_PSI_NO_BAL_DIM'
1060 ,p_token1 => p_dimension_name
1061 ,p_token2 => NULL
1062 ,p_error_warning_flag => 'E'
1063 );
1064 END IF;
1065
1066 CLOSE csr_get_bal_dimension_id;
1067
1068 IF g_debug
1069 THEN
1070 l_proc_step := 20;
1071 DEBUG('l_bal_dimension_id: ' || l_bal_dimension_id);
1072 debug_exit(l_proc_name);
1073 END IF;
1074
1075 RETURN l_bal_dimension_id;
1076 EXCEPTION
1077 WHEN OTHERS
1078 THEN
1079 clear_cache;
1080
1081 IF SQLCODE <> hr_utility.hr_error_number
1082 THEN
1083 debug_others(l_proc_name, l_proc_step);
1084
1085 IF g_debug
1086 THEN
1087 DEBUG('Leaving: ' || l_proc_name, -999);
1088 END IF;
1089
1090 fnd_message.raise_error;
1091 ELSE
1092 RAISE;
1093 END IF;
1094 END get_bal_dimension_id;
1095
1096 -- This function returns defined balance for a given balance type
1097 -- and dimension
1098 -- ----------------------------------------------------------------------------
1099 -- |---------------------------< get_defined_balance >------------------------|
1100 -- ----------------------------------------------------------------------------
1101 FUNCTION get_defined_balance(
1102 p_balance_type_id IN NUMBER
1103 ,p_balance_dimension_id IN NUMBER
1104 )
1105 RETURN NUMBER
1106 IS
1107 --
1108 -- Cursor to get defined balance id
1109 CURSOR csr_get_def_bal_id
1110 IS
1111 SELECT defined_balance_id
1112 FROM pay_defined_balances
1113 WHERE balance_type_id = p_balance_type_id
1114 AND balance_dimension_id = p_balance_dimension_id;
1115
1116 l_proc_name VARCHAR2(80) := g_proc_name || 'get_defined_balance';
1117 l_proc_step PLS_INTEGER;
1118 l_def_balance_id NUMBER;
1119 --
1120 BEGIN
1121 --
1122 IF g_debug
1123 THEN
1124 l_proc_step := 10;
1125 debug_enter(l_proc_name);
1126 DEBUG('p_balance_type_id: ' || p_balance_type_id);
1127 DEBUG('p_balance_dimension_id: ' || p_balance_dimension_id);
1128 END IF;
1129
1130 OPEN csr_get_def_bal_id;
1131 FETCH csr_get_def_bal_id INTO l_def_balance_id;
1132
1133 IF csr_get_def_bal_id%NOTFOUND
1134 THEN
1135 -- Raise setup error
1136 pqp_gb_psi_functions.store_extract_exceptions(
1137 p_extract_type => 'EARNINGS_HISTORY'
1138 ,p_error_number => 92780
1139 ,p_error_text => 'BEN_92780_EXT_PSI_NO_DEF_BAL'
1140 ,p_token1 => p_balance_type_id
1141 ,p_token2 => p_balance_dimension_id
1142 ,p_error_warning_flag => 'E'
1143 );
1144 END IF; -- End if of row not found check ...
1145
1146 CLOSE csr_get_def_bal_id;
1147
1148 IF g_debug
1149 THEN
1150 l_proc_step := 20;
1151 DEBUG('l_def_balance_id: ' || l_def_balance_id);
1152 debug_exit(l_proc_name);
1153 END IF;
1154
1155 RETURN l_def_balance_id;
1156 EXCEPTION
1157 WHEN OTHERS
1158 THEN
1159 clear_cache;
1160
1161 IF SQLCODE <> hr_utility.hr_error_number
1162 THEN
1163 debug_others(l_proc_name, l_proc_step);
1164
1165 IF g_debug
1166 THEN
1167 DEBUG('Leaving: ' || l_proc_name, -999);
1168 END IF;
1169
1170 fnd_message.raise_error;
1171 ELSE
1172 RAISE;
1173 END IF;
1174 END get_defined_balance;
1175
1176 -- This function returns screen entry value for a given element entry id
1177 -- ----------------------------------------------------------------------------
1178 -- |----------------------------< get_screen_entry_value >--------------------|
1179 -- ----------------------------------------------------------------------------
1180 FUNCTION get_screen_entry_value(
1181 p_element_entry_id IN NUMBER
1182 ,p_effective_start_date IN DATE
1183 ,p_effective_end_date IN DATE
1184 ,p_input_value_id IN NUMBER
1185 )
1186 RETURN VARCHAR2
1187 IS
1188 --
1189 -- Cursor to fetch screen entry value
1190 CURSOR csr_get_screen_ent_val
1191 IS
1192 SELECT screen_entry_value
1193 FROM pay_element_entry_values_f
1194 WHERE element_entry_id = p_element_entry_id
1195 AND effective_start_date = p_effective_start_date
1196 AND effective_end_date = p_effective_end_date
1197 AND input_value_id = p_input_value_id;
1198
1199 l_proc_name VARCHAR2(80)
1200 := g_proc_name || 'get_screen_entry_value';
1201 l_proc_step PLS_INTEGER;
1202 l_screen_ent_value pay_element_entry_values_f.screen_entry_value%TYPE;
1203 --
1204 BEGIN
1205 --
1206 IF g_debug
1207 THEN
1208 l_proc_step := 10;
1209 debug_enter(l_proc_name);
1210 DEBUG('p_element_entry_id: ' || p_element_entry_id);
1211 DEBUG(
1212 'p_effective_start_date: '
1213 || TO_CHAR(p_effective_start_date, 'DD/MON/YYYY')
1214 );
1215 DEBUG(
1216 'p_effective_end_date: '
1217 || TO_CHAR(p_effective_end_date, 'DD/MON/YYYY')
1218 );
1219 DEBUG('p_input_value_id: ' || p_input_value_id);
1220 END IF;
1221
1222 OPEN csr_get_screen_ent_val;
1223 FETCH csr_get_screen_ent_val INTO l_screen_ent_value;
1224 CLOSE csr_get_screen_ent_val;
1225
1226 IF g_debug
1227 THEN
1228 l_proc_step := 20;
1229 DEBUG('l_screen_ent_value: ' || l_screen_ent_value);
1230 debug_exit(l_proc_name);
1231 END IF;
1232
1233 RETURN l_screen_ent_value;
1234 EXCEPTION
1235 WHEN OTHERS
1236 THEN
1237 clear_cache;
1238
1239 IF SQLCODE <> hr_utility.hr_error_number
1240 THEN
1241 debug_others(l_proc_name, l_proc_step);
1242
1243 IF g_debug
1244 THEN
1245 DEBUG('Leaving: ' || l_proc_name, -999);
1246 END IF;
1247
1248 fnd_message.raise_error;
1249 ELSE
1250 RAISE;
1251 END IF;
1252 END get_screen_entry_value;
1253
1254 -- Ths function returns a yes or no flag to identify whether a value
1255 -- is in the collection or not
1256 -- ----------------------------------------------------------------------------
1257 -- |---------------------< chk_value_in_collection >--------------------------|
1258 -- ----------------------------------------------------------------------------
1259 FUNCTION chk_value_in_collection(
1260 p_collection_name IN t_number
1261 ,p_value IN NUMBER
1262 ,p_index OUT NOCOPY NUMBER
1263 )
1264 RETURN VARCHAR2
1265 IS
1266 --
1267 l_proc_name VARCHAR2(80) := g_proc_name || 'chk_value_in_collection';
1268 l_proc_step PLS_INTEGER;
1269 i NUMBER;
1270 l_return VARCHAR2(10);
1271 l_index NUMBER;
1272 --
1273 BEGIN
1274 --
1275 IF g_debug
1276 THEN
1277 l_proc_step := 10;
1278 debug_enter(l_proc_name);
1279 DEBUG('p_value: ' || p_value);
1280 END IF;
1281
1282 i := p_collection_name.FIRST;
1283 l_return := 'N';
1284 l_index := NULL;
1285
1286 WHILE i IS NOT NULL
1287 LOOP
1288 IF g_debug
1289 THEN
1290 l_proc_step := 20;
1291 DEBUG(l_proc_name, l_proc_step);
1292 DEBUG('i: ' || i);
1293 DEBUG('p_collection_name(i): ' || p_collection_name(i));
1294 END IF;
1295
1296 IF p_collection_name(i) = p_value
1297 THEN
1298 l_return := 'Y';
1299 l_index := i;
1300 EXIT;
1301 END IF;
1302
1303 i := p_collection_name.NEXT(i);
1304 END LOOP;
1305
1306 IF g_debug
1307 THEN
1308 l_proc_step := 30;
1309 DEBUG('l_return: ' || l_return);
1310 debug_exit(l_proc_name);
1311 END IF;
1312
1313 p_index := l_index;
1314 RETURN l_return;
1315 EXCEPTION
1316 WHEN OTHERS
1317 THEN
1318 clear_cache;
1319
1320 IF SQLCODE <> hr_utility.hr_error_number
1321 THEN
1322 debug_others(l_proc_name, l_proc_step);
1323
1324 IF g_debug
1325 THEN
1326 DEBUG('Leaving: ' || l_proc_name, -999);
1327 END IF;
1328
1329 fnd_message.raise_error;
1330 ELSE
1331 RAISE;
1332 END IF;
1333 END chk_value_in_collection;
1334
1335 -- Ths function returns a yes or no flag to identify whether a value
1336 -- is in the collection or not
1337 -- ----------------------------------------------------------------------------
1338 -- |---------------------< chk_value_in_collection >--------------------------|
1339 -- ----------------------------------------------------------------------------
1340 FUNCTION chk_value_in_collection(
1341 p_collection_name IN t_varchar2
1342 ,p_value IN VARCHAR2
1343 ,p_index OUT NOCOPY NUMBER
1344 )
1345 RETURN VARCHAR2
1346 IS
1347 --
1348 l_proc_name VARCHAR2(80) := g_proc_name || 'chk_value_in_collection';
1349 l_proc_step PLS_INTEGER;
1350 i NUMBER;
1351 l_return VARCHAR2(10);
1352 l_index NUMBER;
1353 --
1354 BEGIN
1355 --
1356 IF g_debug
1357 THEN
1358 l_proc_step := 10;
1359 debug_enter(l_proc_name);
1360 DEBUG('p_value: ' || p_value);
1361 END IF;
1362
1363 i := p_collection_name.FIRST;
1364 l_return := 'N';
1365 l_index := NULL;
1366
1367 WHILE i IS NOT NULL
1368 LOOP
1369 IF g_debug
1370 THEN
1371 l_proc_step := 20;
1372 DEBUG(l_proc_name, l_proc_step);
1373 DEBUG('i: ' || i);
1374 DEBUG('p_collection_name(i): ' || p_collection_name(i));
1375 END IF;
1376
1377 IF p_collection_name(i) = p_value
1378 THEN
1379 l_return := 'Y';
1380 l_index := i;
1381 EXIT;
1382 END IF;
1383
1384 i := p_collection_name.NEXT(i);
1385 END LOOP;
1386
1387 IF g_debug
1388 THEN
1389 l_proc_step := 30;
1390 DEBUG('l_return: ' || l_return);
1391 debug_exit(l_proc_name);
1392 END IF;
1393
1394 p_index := l_index;
1395 RETURN l_return;
1396 EXCEPTION
1397 WHEN OTHERS
1398 THEN
1399 clear_cache;
1400
1401 IF SQLCODE <> hr_utility.hr_error_number
1402 THEN
1403 debug_others(l_proc_name, l_proc_step);
1404
1405 IF g_debug
1406 THEN
1407 DEBUG('Leaving: ' || l_proc_name, -999);
1408 END IF;
1409
1410 fnd_message.raise_error;
1411 ELSE
1412 RAISE;
1413 END IF;
1414 END chk_value_in_collection;
1415
1416 -- This procedures fetches the process definition configuration
1417 -- for penserver
1418 -- ----------------------------------------------------------------------------
1419 -- |----------------------------< fetch_process_defn_cv >---------------------|
1420 -- ----------------------------------------------------------------------------
1421 PROCEDURE fetch_process_defn_cv(p_business_group_id IN NUMBER)
1422 IS
1423 --
1424 l_proc_name VARCHAR2(80)
1425 := g_proc_name || 'fetch_process_defn_cv';
1426 l_proc_step PLS_INTEGER;
1427 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
1428 l_tab_config_values pqp_utilities.t_config_values;
1429 i NUMBER;
1430 --
1431 BEGIN
1432 --
1433 IF g_debug
1434 THEN
1435 l_proc_step := 10;
1436 debug_enter(l_proc_name);
1437 END IF;
1438
1439 -- Call configuration value function to retrieve all data
1440 -- for a configuration type
1441 IF g_debug
1442 THEN
1443 l_proc_step := 20;
1444 DEBUG(l_proc_name, l_proc_step);
1445 DEBUG('p_business_group_id: ' || p_business_group_id);
1446 END IF;
1447
1448 l_configuration_type := 'PQP_GB_PENSERVER_DEFINITION';
1449 pqp_utilities.get_config_type_values(
1450 p_configuration_type => l_configuration_type
1451 ,p_business_group_id => p_business_group_id
1452 ,p_legislation_code => g_legislation_code
1453 ,p_tab_config_values => l_tab_config_values
1454 );
1455
1456 IF g_debug
1457 THEN
1458 l_proc_step := 30;
1459 DEBUG(l_proc_name, l_proc_step);
1460 DEBUG('l_configuration_type: ' || l_configuration_type);
1461 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
1462 END IF;
1463
1464 -- Store the config values in the global collection
1465 -- for event map
1466 g_tab_prs_dfn_cv := l_tab_config_values;
1467
1468 -- Debug PCV_INFORMATION1
1469 IF g_debug
1470 THEN
1471 l_proc_step := 40;
1472 DEBUG(l_proc_name, l_proc_step);
1473 END IF;
1474
1475 i := g_tab_prs_dfn_cv.FIRST;
1476
1477 WHILE i IS NOT NULL
1478 LOOP
1479 IF g_debug
1480 THEN
1481 DEBUG('Debug: ' || l_tab_config_values(i).pcv_information1);
1482 END IF;
1483
1484 i := g_tab_prs_dfn_cv.NEXT(i);
1485 END LOOP;
1486
1487 IF g_debug
1488 THEN
1489 l_proc_step := 50;
1490 debug_exit(l_proc_name);
1491 END IF;
1492 EXCEPTION
1493 WHEN OTHERS
1494 THEN
1495 clear_cache;
1496
1497 IF SQLCODE <> hr_utility.hr_error_number
1498 THEN
1499 debug_others(l_proc_name, l_proc_step);
1500
1501 IF g_debug
1502 THEN
1503 DEBUG('Leaving: ' || l_proc_name, -999);
1504 END IF;
1505
1506 fnd_message.raise_error;
1507 ELSE
1508 RAISE;
1509 END IF;
1510 END fetch_process_defn_cv;
1511
1512 -- This procedure fetches elements mapped to civil service pension schemes
1513 -- ----------------------------------------------------------------------------
1514 -- |----------------------------< fetch_pension_scheme_map_cv >---------------|
1515 -- ----------------------------------------------------------------------------
1516 PROCEDURE fetch_pension_scheme_map_cv(
1517 p_business_group_id IN NUMBER
1518 ,p_tab_pen_sch_map_cv OUT NOCOPY pqp_utilities.t_config_values
1519 )
1520 IS
1521 --
1522 l_proc_name VARCHAR2(80)
1523 := g_proc_name || 'fetch_pension_scheme_map_cv';
1524 l_proc_step PLS_INTEGER;
1525 l_element_type_id NUMBER;
1526 l_configuration_type pqp_configuration_types.configuration_type%TYPE;
1527 l_tab_config_values pqp_utilities.t_config_values;
1528 i NUMBER;
1529 --
1530 BEGIN
1531 --
1532 IF g_debug
1533 THEN
1534 l_proc_step := 10;
1535 debug_enter(l_proc_name);
1536 END IF;
1537
1538 --
1539 -- Call configuration value function to retrieve all data
1540 -- for a configuration type
1541
1542 IF g_debug
1543 THEN
1544 l_proc_step := 20;
1545 DEBUG(l_proc_name, l_proc_step);
1546 DEBUG('p_business_group_id: ' || p_business_group_id);
1547 END IF;
1548
1549 l_configuration_type := 'PQP_GB_PENSERV_SCHEME_MAP_INFO';
1550
1551 IF pqp_gb_psi_functions.g_pension_scheme_mapping.COUNT = 0
1552 THEN
1553 pqp_utilities.get_config_type_values(
1554 p_configuration_type => l_configuration_type
1555 ,p_business_group_id => p_business_group_id
1556 ,p_legislation_code => g_legislation_code
1557 ,p_tab_config_values => l_tab_config_values
1558 );
1559 ELSE -- get it from cached collection
1560 l_tab_config_values :=
1561 pqp_gb_psi_functions.g_pension_scheme_mapping;
1562 END IF;
1563
1564 IF g_debug
1565 THEN
1566 l_proc_step := 30;
1567 DEBUG(l_proc_name, l_proc_step);
1568 DEBUG('l_configuration_type: ' || l_configuration_type);
1569 DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
1570 END IF;
1571
1572 -- Return the
1573 -- collection for pension scheme elements
1574 p_tab_pen_sch_map_cv := l_tab_config_values;
1575 -- Penserver Pension Scheme PCV_INFORMATION2
1576 -- Template Pension Scheme PCV_INFORMATION1
1577
1578 i := l_tab_config_values.FIRST;
1579
1580 IF g_debug
1581 THEN
1582 l_proc_step := 40;
1583 DEBUG(l_proc_name, l_proc_step);
1584 END IF;
1585
1586 WHILE i IS NOT NULL
1587 LOOP
1588 IF g_debug
1589 THEN
1590 DEBUG(
1591 'Penserver Pension Scheme: '
1592 || l_tab_config_values(i).pcv_information2
1593 );
1594 DEBUG(
1595 'Template Pension Scheme: '
1596 || l_tab_config_values(i).pcv_information1
1597 );
1598 END IF;
1599
1600 i := l_tab_config_values.NEXT(i);
1601 END LOOP;
1602
1603 IF l_tab_config_values.COUNT = 0
1604 THEN
1605 -- Raise setup error
1606 pqp_gb_psi_functions.store_extract_exceptions(
1607 p_extract_type => 'EARNINGS_HISTORY'
1608 ,p_error_number => 94268
1609 ,p_error_text => 'BEN_92799_EXT_PSI_NO_CONFIG'
1610 ,p_token1 => 'Penserver Interface'
1611 ,p_token2 => 'Pension Scheme Mapping'
1612 ,p_error_warning_flag => 'E'
1613 );
1614 END IF;
1615
1616 IF g_debug
1617 THEN
1618 l_proc_step := 50;
1619 debug_exit(l_proc_name);
1620 END IF;
1621 EXCEPTION
1622 WHEN OTHERS
1623 THEN
1624 clear_cache;
1625
1626 IF SQLCODE <> hr_utility.hr_error_number
1627 THEN
1628 debug_others(l_proc_name, l_proc_step);
1629
1630 IF g_debug
1631 THEN
1632 DEBUG('Leaving: ' || l_proc_name, -999);
1633 END IF;
1634
1635 fnd_message.raise_error;
1636 ELSE
1637 RAISE;
1638 END IF;
1639 END fetch_pension_scheme_map_cv;
1640
1641 -- This function determines whether an extract is a periodic interface or
1642 -- cutover interface based on the data_typ_cd
1643 -- ----------------------------------------------------------------------------
1644 -- |----------------------------< get_extract_type >--------------------------|
1645 -- ----------------------------------------------------------------------------
1646 FUNCTION get_extract_type(p_ext_dfn_id IN NUMBER)
1647 RETURN VARCHAR2
1648 IS
1649 --
1650 -- F -> Full Profile
1651 -- C -> Changes Only
1652 CURSOR csr_get_ext_type
1653 IS
1654 SELECT DECODE(data_typ_cd, 'F', 'CUTOVER', 'C', 'PERIODIC')
1655 FROM ben_ext_dfn
1656 WHERE ext_dfn_id = p_ext_dfn_id;
1657
1658 l_proc_name VARCHAR2(80) := g_proc_name || 'get_extract_type';
1659 l_proc_step PLS_INTEGER;
1660 l_extract_type VARCHAR2(50);
1661 --
1662 BEGIN
1663 --
1664 IF g_debug
1665 THEN
1666 l_proc_step := 10;
1667 debug_enter(l_proc_name);
1668 DEBUG('p_ext_dfn_id: ' || p_ext_dfn_id);
1669 END IF;
1670
1671 OPEN csr_get_ext_type;
1672 FETCH csr_get_ext_type INTO l_extract_type;
1673 CLOSE csr_get_ext_type;
1674
1675 IF g_debug
1676 THEN
1677 l_proc_step := 20;
1678 DEBUG('l_extract_type: ' || l_extract_type);
1679 debug_exit(l_proc_name);
1680 END IF;
1681
1682 RETURN l_extract_type;
1683 EXCEPTION
1684 WHEN OTHERS
1685 THEN
1686 clear_cache;
1687
1688 IF SQLCODE <> hr_utility.hr_error_number
1689 THEN
1690 debug_others(l_proc_name, l_proc_step);
1691
1692 IF g_debug
1693 THEN
1694 DEBUG('Leaving: ' || l_proc_name, -999);
1695 END IF;
1696
1697 fnd_message.raise_error;
1698 ELSE
1699 RAISE;
1700 END IF;
1701 END get_extract_type;
1702
1703 -- This function gets the element entry details for a given element type
1704 -- ----------------------------------------------------------------------------
1705 -- |----------------------------< get_ele_ent_details >-----------------------|
1706 -- ----------------------------------------------------------------------------
1707 FUNCTION get_ele_ent_details(
1708 p_assignment_id IN NUMBER
1709 ,p_effective_start_date IN DATE
1710 ,p_effective_end_date IN DATE
1711 ,p_element_type_id IN NUMBER
1712 ,p_rec_ele_ent_details OUT NOCOPY r_ele_ent_details
1713 )
1714 RETURN VARCHAR2
1715 IS
1716 --
1717 -- Cursor to get pension scheme element details
1718 -- for this person
1719 CURSOR csr_get_ele_ent_details(c_element_type_id NUMBER)
1720 IS
1721 SELECT pee.element_entry_id,
1722 pee.effective_start_date,
1723 pee.effective_end_date,
1724 -- pel.element_type_id
1725 pee.element_type_id
1726 FROM pay_element_entries_f pee
1727 --pay_element_links_f pel
1728 WHERE pee.assignment_id = p_assignment_id
1729 AND pee.entry_type = 'E'
1730 -- AND pee.element_link_id = pel.element_link_id
1731 AND (
1732 p_effective_start_date BETWEEN pee.effective_start_date
1733 AND pee.effective_end_date
1734 OR p_effective_end_date BETWEEN pee.effective_start_date
1735 AND pee.effective_end_date
1736 OR pee.effective_start_date BETWEEN p_effective_start_date
1737 AND p_effective_end_date
1738 OR pee.effective_end_date BETWEEN p_effective_start_date
1739 AND p_effective_end_date
1740 )
1741 AND pee.element_type_id = c_element_type_id
1742 /*AND pel.element_type_id = c_element_type_id
1743 AND (
1744 p_effective_start_date BETWEEN pel.effective_start_date
1745 AND pel.effective_end_date
1746 OR p_effective_end_date BETWEEN pel.effective_start_date
1747 AND pel.effective_end_date
1748 OR pel.effective_start_date BETWEEN p_effective_start_date
1749 AND p_effective_end_date
1750 OR pel.effective_end_date BETWEEN p_effective_start_date
1751 AND p_effective_end_date
1752 )*/
1753 ORDER BY pee.effective_start_date DESC;
1754
1755 l_proc_name VARCHAR2(80)
1756 := g_proc_name || 'get_ele_ent_details';
1757 l_proc_step PLS_INTEGER;
1758 l_rec_ele_ent_details r_ele_ent_details;
1759 l_return VARCHAR2(10);
1760 --
1761 BEGIN
1762 --
1763 IF g_debug
1764 THEN
1765 l_proc_step := 10;
1766 debug_enter(l_proc_name);
1767 DEBUG('p_assignment_id: ' || p_assignment_id);
1768 DEBUG(
1769 'p_effective_start_date: '
1770 || TO_CHAR(p_effective_start_date, 'DD/MON/YYYY')
1771 );
1772 DEBUG(
1773 'p_effective_end_date: '
1774 || TO_CHAR(p_effective_end_date, 'DD/MON/YYYY')
1775 );
1776 DEBUG('p_element_type_id: ' || p_element_type_id);
1777 END IF;
1778
1779 l_return := 'N';
1780 OPEN csr_get_ele_ent_details(p_element_type_id);
1781 FETCH csr_get_ele_ent_details INTO l_rec_ele_ent_details;
1782
1783 IF csr_get_ele_ent_details%FOUND
1784 THEN
1785 l_return := 'Y';
1786
1787 IF g_debug
1788 THEN
1789 l_proc_step := 30;
1790 DEBUG(l_proc_name, l_proc_step);
1791 END IF;
1792 END IF; -- cursor found check ...
1793
1794 CLOSE csr_get_ele_ent_details;
1795 p_rec_ele_ent_details := l_rec_ele_ent_details;
1796
1797 IF g_debug
1798 THEN
1799 l_proc_step := 40;
1800 DEBUG('l_return: ' || l_return);
1801 DEBUG(
1802 'l_rec_ele_ent_details.element_entry_id: '
1803 || l_rec_ele_ent_details.element_entry_id
1804 );
1805 DEBUG(
1806 'l_rec_ele_ent_details.effective_start_date: '
1807 || l_rec_ele_ent_details.effective_start_date
1808 );
1809 DEBUG(
1810 'l_rec_ele_ent_details.effective_end_date: '
1811 || l_rec_ele_ent_details.effective_end_date
1812 );
1813 debug_exit(l_proc_name);
1814 END IF;
1815
1816 RETURN l_return;
1817 EXCEPTION
1818 WHEN OTHERS
1819 THEN
1820 clear_cache;
1821
1822 IF SQLCODE <> hr_utility.hr_error_number
1823 THEN
1824 debug_others(l_proc_name, l_proc_step);
1825
1826 IF g_debug
1827 THEN
1828 DEBUG('Leaving: ' || l_proc_name, -999);
1829 END IF;
1830
1831 fnd_message.raise_error;
1832 ELSE
1833 RAISE;
1834 END IF;
1835 END get_ele_ent_details;
1836
1837 -- This function returns the pension scheme membership details at a given date
1838 -- ----------------------------------------------------------------------------
1839 -- |----------------------------< get_pen_scheme_memb >-----------------------|
1840 -- ----------------------------------------------------------------------------
1841 FUNCTION get_pen_scheme_memb(
1842 p_assignment_id IN NUMBER
1843 ,p_effective_start_date IN DATE
1844 ,p_effective_end_date IN DATE
1845 ,p_tab_pen_sch_map_cv IN pqp_utilities.t_config_values
1846 ,p_rec_ele_ent_details OUT NOCOPY r_ele_ent_details
1847 )
1848 RETURN VARCHAR2
1849 IS
1850 --
1851 l_proc_name VARCHAR2(80)
1852 := g_proc_name || 'get_pen_scheme_memb';
1853 l_proc_step PLS_INTEGER;
1854 l_rec_ele_ent_details r_ele_ent_details;
1855 l_element_type_id NUMBER;
1856 i NUMBER;
1857 l_return VARCHAR2(10);
1858 --
1859 BEGIN
1860 --
1861 IF g_debug
1862 THEN
1863 l_proc_step := 10;
1864 debug_enter(l_proc_name);
1865 DEBUG('p_assignment_id: ' || p_assignment_id);
1866 DEBUG(
1867 'p_effective_start_date: '
1868 || TO_CHAR(p_effective_start_date, 'DD/MON/YYYY')
1869 );
1870 DEBUG(
1871 'p_effective_end_date: '
1872 || TO_CHAR(p_effective_end_date, 'DD/MON/YYYY')
1873 );
1874 END IF;
1875
1876 i := g_tab_pen_sch_map_cv.FIRST;
1877
1878 WHILE i IS NOT NULL
1879 LOOP
1880 l_element_type_id :=
1881 fnd_number.canonical_to_number(p_tab_pen_sch_map_cv(i).pcv_information1);
1882
1883 IF g_debug
1884 THEN
1885 l_proc_step := 20;
1886 DEBUG(l_proc_name, l_proc_step);
1887 DEBUG('l_element_type_id: ' || l_element_type_id);
1888 END IF;
1889
1890 l_return :=
1891 get_ele_ent_details(
1892 p_assignment_id => p_assignment_id
1893 ,p_effective_start_date => p_effective_start_date
1894 ,p_effective_end_date => p_effective_end_date
1895 ,p_element_type_id => l_element_type_id
1896 ,p_rec_ele_ent_details => l_rec_ele_ent_details
1897 );
1898
1899 -- We are only interested in the latest pension scheme
1900 -- membership details
1901 IF l_return = 'Y'
1902 THEN
1903 IF g_debug
1904 THEN
1905 l_proc_step := 30;
1906 DEBUG(
1907 'l_rec_ele_ent_details.element_entry_id: '
1908 || l_rec_ele_ent_details.element_entry_id
1909 );
1910 DEBUG(
1911 'l_rec_ele_ent_details.effective_start_date: '
1912 || l_rec_ele_ent_details.effective_start_date
1913 );
1914 DEBUG(
1915 'l_rec_ele_ent_details.effective_end_date: '
1916 || l_rec_ele_ent_details.effective_end_date
1917 );
1918 DEBUG(l_proc_name, l_proc_step);
1919 END IF;
1920
1921 EXIT;
1922 END IF; -- element entry details exist ...
1923
1924 i := p_tab_pen_sch_map_cv.NEXT(i);
1925 END LOOP;
1926
1927 IF g_debug
1928 THEN
1929 l_proc_step := 40;
1930 DEBUG('l_return: ' || l_return);
1931 DEBUG('l_element_type_id: ' || l_element_type_id);
1932 debug_exit(l_proc_name);
1933 END IF;
1934
1935 p_rec_ele_ent_details := l_rec_ele_ent_details;
1936 RETURN l_return;
1937 EXCEPTION
1938 WHEN OTHERS
1939 THEN
1940 clear_cache;
1941
1942 IF SQLCODE <> hr_utility.hr_error_number
1943 THEN
1944 debug_others(l_proc_name, l_proc_step);
1945
1946 IF g_debug
1947 THEN
1948 DEBUG('Leaving: ' || l_proc_name, -999);
1949 END IF;
1950
1951 fnd_message.raise_error;
1952 ELSE
1953 RAISE;
1954 END IF;
1955 END get_pen_scheme_memb;
1956
1957 -- This procedure gets all the relevant pension scheme balances for
1958 -- reporting purposes
1959 -- ----------------------------------------------------------------------------
1960 -- |----------------------------< get_pen_balance_details >-------------------|
1961 -- ----------------------------------------------------------------------------
1962 PROCEDURE get_pen_balance_details(
1963 p_element_type_id IN NUMBER
1964 ,p_base_name IN VARCHAR2
1965 ,p_pension_category IN VARCHAR2
1966 ,p_psi_pens_category IN VARCHAR2
1967 ,p_rec_pen_bal_dtls OUT NOCOPY r_pen_bal_dtls
1968 )
1969 IS
1970 --
1971 l_proc_name VARCHAR2(80)
1972 := g_proc_name || 'get_pen_balance_details';
1973 l_proc_step PLS_INTEGER;
1974 i NUMBER;
1975 l_tab_bal_name t_varchar2;
1976 l_ees_bal_name pay_balance_types.balance_name%TYPE;
1977 l_ees_bal_type_id NUMBER;
1978 l_ees_ptd_bal_id NUMBER;
1979 -- For 115.29
1980 l_ees_ytd_bal_id NUMBER;
1981 l_ers_bal_name pay_balance_types.balance_name%TYPE;
1982 l_ers_bal_type_id NUMBER;
1983 l_ers_ptd_bal_id NUMBER;
1984 -- For 115.29
1985 l_ers_ytd_bal_id NUMBER;
1986 -- Commenting the below variables as they are not used
1987 /* l_add_bal_name pay_balance_types.balance_name%TYPE;
1988 l_add_bal_type_id NUMBER;
1989 l_add_ptd_bal_id NUMBER;
1990 l_ayr_bal_name pay_balance_types.balance_name%TYPE;
1991 l_ayr_bal_type_id NUMBER;
1992 l_ayr_ptd_bal_id NUMBER;
1993 l_fwd_bal_name pay_balance_types.balance_name%TYPE;
1994 l_fwd_bal_type_id NUMBER;
1995 l_fwd_ptd_bal_id NUMBER; */
1996 l_ayfwd_bal_name pay_balance_types.balance_name%TYPE;
1997 l_ayfwd_bal_type_id NUMBER;
1998 l_ayfwd_ptd_bal_id NUMBER;
1999 -- For 115.29
2000 l_ayfwd_ytd_bal_id NUMBER;
2001
2002 /* l_ayfb_bal_name pay_balance_types.balance_name%TYPE; -- For Bug 6082532
2003 l_ayfb_bal_type_id NUMBER;
2004 l_ayfb_ptd_bal_id NUMBER; */
2005
2006 l_nuvos_sa_bal_name pay_balance_types.balance_name%TYPE; -- For Nuvos
2007 l_nuvos_sa_bal_type_id NUMBER;
2008 l_nuvos_sa_ptd_bal_id NUMBER;
2009 -- For 115.29
2010 l_nuvos_sa_ytd_bal_id NUMBER;
2011
2012 l_balance_type_id NUMBER;
2013 l_defined_bal_id NUMBER;
2014 -- For 115.29
2015 l_pen_defined_bal_id NUMBER;
2016
2017 l_rec_pen_bal_dtls r_pen_bal_dtls;
2018 --
2019 BEGIN
2020 --
2021 IF g_debug
2022 THEN
2023 l_proc_step := 10;
2024 debug_enter(l_proc_name);
2025 DEBUG('p_element_type_id: ' || p_element_type_id);
2026 DEBUG('p_base_name: ' || p_base_name);
2027 DEBUG('p_pension_category: ' || p_pension_category);
2028 DEBUG('p_psi_pens_category: ' || p_psi_pens_category);
2029 -- debug('p_template_id: '||p_template_id);
2030 END IF;
2031
2032 -- Only proceed if eeit information exists
2033 IF g_tab_eei_info.EXISTS(p_element_type_id)
2034 THEN
2035 i := 1;
2036 l_tab_bal_name(i) :=
2037 p_base_name || ' ' || p_pension_category
2038 || ' EES Contribution';
2039 l_ees_bal_name := l_tab_bal_name(i);
2040
2041 -- For Bug 6082532 (Added years Family Benefit balance)
2042 /* i := i + 1;
2043 l_tab_bal_name(i) :=
2044 p_base_name|| ' Added Years Family Benefit';
2045 l_ayfb_bal_name := l_tab_bal_name(i); */
2046
2047 -- ERS Contribution balance
2048 IF g_tab_eei_info(p_element_type_id).eei_information7 IS NOT NULL
2049 THEN
2050 i := i + 1;
2051 l_tab_bal_name(i) :=
2052 p_base_name || ' ' || p_pension_category
2053 || ' ERS Contribution';
2054 l_ers_bal_name := l_tab_bal_name(i);
2055 END IF; -- End if of eer deduction method check ...
2056
2057 IF p_pension_category = 'OCP'
2058 THEN
2059 IF g_debug
2060 THEN
2061 l_proc_step := 20;
2062 DEBUG(l_proc_name, l_proc_step);
2063 END IF;
2064
2065 -- Commenting the below code as they are not used to retrieve the balances
2066 -- Look for other balances
2067 -- Additional Contribution balance
2068 /* IF g_tab_eei_info(p_element_type_id).eei_information13 = 'Y'
2069 THEN
2070 i := i + 1;
2071 l_tab_bal_name(i) :=
2072 p_base_name
2073 || ' Additional Contribution';
2074 l_add_bal_name := l_tab_bal_name(i);
2075 ELSIF g_tab_eei_info(p_element_type_id).eei_information14 IS NOT NULL -- For BUG 6082532
2076 THEN
2077 -- Added Years Contribution
2078 i := i + 1;
2079 l_tab_bal_name(i) :=
2080 p_base_name
2081 || ' Added Years Contribution';
2082 l_ayr_bal_name := l_tab_bal_name(i);
2083 ELSIF g_tab_eei_info(p_element_type_id).eei_information15 = 'Y'
2084 THEN
2085 -- Family or Widower Benefit Contribution
2086 i := i + 1;
2087 l_tab_bal_name(i) :=
2088 p_base_name
2089 || ' Family Widower Contribution';
2090 l_fwd_bal_name := l_tab_bal_name(i);
2091 END IF; */
2092
2093 IF p_psi_pens_category = 'CLASSIC'
2094 THEN
2095 -- Added Years for FW contribution
2096 i := i + 1;
2097 l_tab_bal_name(i) :=
2098 p_base_name
2099 || ' Buy Back FWC Contribution';
2100 l_ayfwd_bal_name := l_tab_bal_name(i);
2101
2102 ELSIF p_psi_pens_category = 'NUVOS'
2103 THEN
2104 -- Added Years for Nuvos contributions
2105 i := i + 1;
2106 l_tab_bal_name(i) :=
2107 p_base_name
2108 || ' Superannuable Salary';
2109 l_nuvos_sa_bal_name := l_tab_bal_name(i);
2110
2111 END IF; -- End if of psi pension category is classic check ...
2112 END IF; -- End if of pension category is OCP check ...
2113 END IF; -- End if of eeit information exists
2114 -- for this element type check ...
2115
2116 -- Get the balance information
2117
2118 i := l_tab_bal_name.FIRST;
2119
2120 WHILE i IS NOT NULL
2121 LOOP
2122 l_balance_type_id :=
2123 get_balance_type_id(
2124 p_balance_name => l_tab_bal_name(i)
2125 ,p_business_group_id => g_business_group_id
2126 ,p_legislation_code => NULL
2127 );
2128 l_defined_bal_id := NULL;
2129
2130 IF l_balance_type_id IS NOT NULL
2131 THEN
2132 l_defined_bal_id :=
2133 get_defined_balance(
2134 p_balance_type_id => l_balance_type_id
2135 ,p_balance_dimension_id => g_procptd_dimension_id
2136 );
2137 END IF;
2138
2139 -- For 115.29
2140 l_pen_defined_bal_id := NULL;
2141
2142 IF l_balance_type_id IS NOT NULL
2143 THEN
2144 l_pen_defined_bal_id :=
2145 get_defined_balance(
2146 p_balance_type_id => l_balance_type_id
2147 ,p_balance_dimension_id => g_penytd_dimension_id
2148 );
2149 END IF;
2150
2151 IF g_debug
2152 THEN
2153 l_proc_step := 30;
2154 DEBUG(l_proc_name, l_proc_step);
2155 DEBUG('l_tab_bal_name(i): ' || l_tab_bal_name(i));
2156 DEBUG('l_balance_type_id: ' || l_balance_type_id);
2157 DEBUG('l_defined_bal_id: ' || l_defined_bal_id);
2158 DEBUG('l_pen_defined_bal_id: ' || l_pen_defined_bal_id);
2159 END IF;
2160
2161 -- Check the balance names and store it against the
2162 -- relevant variables
2163
2164 IF l_tab_bal_name(i) = l_ees_bal_name
2165 THEN
2166 l_ees_bal_type_id := l_balance_type_id;
2167 l_ees_ptd_bal_id := l_defined_bal_id;
2168 -- For 115.29
2169 l_ees_ytd_bal_id := l_pen_defined_bal_id;
2170 ELSIF l_tab_bal_name(i) = l_ers_bal_name
2171 THEN
2172 l_ers_bal_type_id := l_balance_type_id;
2173 l_ers_ptd_bal_id := l_defined_bal_id;
2174 -- For 115.29
2175 l_ers_ytd_bal_id := l_pen_defined_bal_id;
2176 -- Commenting the below code as they are not used
2177 /* ELSIF l_tab_bal_name(i) = l_add_bal_name
2178 THEN
2179 l_add_bal_type_id := l_balance_type_id;
2180 l_add_ptd_bal_id := l_defined_bal_id;
2181 ELSIF l_tab_bal_name(i) = l_ayr_bal_name
2182 THEN
2183 l_ayr_bal_type_id := l_balance_type_id;
2184 l_ayr_ptd_bal_id := l_defined_bal_id;
2185 ELSIF l_tab_bal_name(i) = l_fwd_bal_name
2186 THEN
2187 l_fwd_bal_type_id := l_balance_type_id;
2188 l_fwd_ptd_bal_id := l_defined_bal_id; */
2189 ELSIF l_tab_bal_name(i) = l_ayfwd_bal_name
2190 THEN
2191 l_ayfwd_bal_type_id := l_balance_type_id;
2192 l_ayfwd_ptd_bal_id := l_defined_bal_id;
2193 -- For 115.29
2194 l_ayfwd_ytd_bal_id := l_pen_defined_bal_id;
2195 /* ELSIF l_tab_bal_name(i) = l_ayfb_bal_name -- For Bug 6082532
2196 THEN
2197 l_ayfb_bal_type_id := l_balance_type_id;
2198 l_ayfb_ptd_bal_id := l_defined_bal_id; */
2199
2200 ELSIF l_tab_bal_name(i) = l_nuvos_sa_bal_name -- For Nuvos
2201 THEN
2202 l_nuvos_sa_bal_type_id := l_balance_type_id;
2203 l_nuvos_sa_ptd_bal_id := l_defined_bal_id;
2204 -- For 115.29
2205 l_nuvos_sa_ytd_bal_id := l_pen_defined_bal_id;
2206
2207 END IF; -- End if of balance name check ...
2208
2209 i := l_tab_bal_name.NEXT(i);
2210 END LOOP;
2211
2212 l_rec_pen_bal_dtls.element_type_id := p_element_type_id;
2213 l_rec_pen_bal_dtls.ees_balance_name := l_ees_bal_name;
2214 l_rec_pen_bal_dtls.ees_bal_type_id := l_ees_bal_type_id;
2215 l_rec_pen_bal_dtls.ees_ptd_bal_id := l_ees_ptd_bal_id;
2216 -- For 115.29
2217 l_rec_pen_bal_dtls.ees_ytd_bal_id := l_ees_ytd_bal_id;
2218 l_rec_pen_bal_dtls.ers_balance_name := l_ers_bal_name;
2219 l_rec_pen_bal_dtls.ers_bal_type_id := l_ers_bal_type_id;
2220 l_rec_pen_bal_dtls.ers_ptd_bal_id := l_ers_ptd_bal_id;
2221 -- For 115.29
2222 l_rec_pen_bal_dtls.ers_ytd_bal_id := l_ers_ytd_bal_id;
2223 -- Commenting the below codes as they are not used
2224 /* l_rec_pen_bal_dtls.add_balance_name := l_add_bal_name;
2225 l_rec_pen_bal_dtls.add_bal_type_id := l_add_bal_type_id;
2226 l_rec_pen_bal_dtls.add_ptd_bal_id := l_add_ptd_bal_id;
2227 l_rec_pen_bal_dtls.ayr_balance_name := l_ayr_bal_name;
2228 l_rec_pen_bal_dtls.ayr_bal_type_id := l_ayr_bal_type_id;
2229 l_rec_pen_bal_dtls.ayr_ptd_bal_id := l_ayr_ptd_bal_id;
2230 l_rec_pen_bal_dtls.fwd_balance_name := l_fwd_bal_name;
2231 l_rec_pen_bal_dtls.fwd_bal_type_id := l_fwd_bal_type_id;
2232 l_rec_pen_bal_dtls.fwd_ptd_bal_id := l_fwd_ptd_bal_id; */
2233 l_rec_pen_bal_dtls.ayfwd_balance_name := l_ayfwd_bal_name;
2234 l_rec_pen_bal_dtls.ayfwd_bal_type_id := l_ayfwd_bal_type_id;
2235 l_rec_pen_bal_dtls.ayfwd_ptd_bal_id := l_ayfwd_ptd_bal_id;
2236 -- For 115.29
2237 l_rec_pen_bal_dtls.ayfwd_ytd_bal_id := l_ayfwd_ytd_bal_id;
2238 /* l_rec_pen_bal_dtls.ayfb_balance_name := l_ayfb_bal_name; -- For Bug 6082532
2239 l_rec_pen_bal_dtls.ayfb_bal_type_id := l_ayfb_bal_type_id;
2240 l_rec_pen_bal_dtls.ayfb_ptd_bal_id := l_ayfb_ptd_bal_id; */
2241
2242 l_rec_pen_bal_dtls.nuvos_sa_balance_name := l_nuvos_sa_bal_name; -- For Bug 6082532
2243 l_rec_pen_bal_dtls.nuvos_sa_bal_type_id := l_nuvos_sa_bal_type_id;
2244 l_rec_pen_bal_dtls.nuvos_sa_ptd_bal_id := l_nuvos_sa_ptd_bal_id;
2245 -- For 115.29
2246 l_rec_pen_bal_dtls.nuvos_sa_ytd_bal_id := l_nuvos_sa_ytd_bal_id;
2247
2248
2249 p_rec_pen_bal_dtls := l_rec_pen_bal_dtls;
2250
2251 IF g_debug
2252 THEN
2253 l_proc_step := 40;
2254 DEBUG('l_ees_bal_name: ' || l_ees_bal_name);
2255 DEBUG('l_ees_bal_type_id: ' || l_ees_bal_type_id);
2256 DEBUG('l_ees_ptd_bal_id: ' || l_ees_ptd_bal_id);
2257 -- For 115.29
2258 DEBUG('l_ees_ytd_bal_id: ' || l_ees_ytd_bal_id);
2259 DEBUG('l_ers_bal_name: ' || l_ers_bal_name);
2260 DEBUG('l_ers_bal_type_id: ' || l_ers_bal_type_id);
2261 DEBUG('l_ers_ptd_bal_id: ' || l_ers_ptd_bal_id);
2262 -- For 115.29
2263 DEBUG('l_ers_ytd_bal_id: ' || l_ers_ytd_bal_id);
2264 -- Commenting the below codes
2265 /* DEBUG('l_add_bal_name: ' || l_add_bal_name);
2266 DEBUG('l_add_bal_type_id: ' || l_add_bal_type_id);
2267 DEBUG('l_add_ptd_bal_id: ' || l_add_ptd_bal_id);
2268 DEBUG('l_ayr_bal_name: ' || l_ayr_bal_name);
2269 DEBUG('l_ayr_bal_type_id: ' || l_ayr_bal_type_id);
2270 DEBUG('l_ayr_ptd_bal_id: ' || l_ayr_ptd_bal_id);
2271 DEBUG('l_fwd_bal_type_id: ' || l_fwd_bal_type_id);
2272 DEBUG('l_fwd_ptd_bal_id: ' || l_fwd_ptd_bal_id); */
2273 DEBUG('l_ayfwd_bal_type_id: ' || l_ayfwd_bal_type_id);
2274 DEBUG('l_ayfwd_ptd_bal_id: ' || l_ayfwd_ptd_bal_id);
2275 -- For 115.29
2276 DEBUG('l_ayfwd_ytd_bal_id: ' || l_ayfwd_ytd_bal_id);
2277 /* DEBUG('l_ayfb_bal_type_id: ' || l_ayfb_bal_type_id); -- For Bug 6082532
2278 DEBUG('l_ayfb_ptd_bal_id: ' || l_ayfb_ptd_bal_id); */
2279 DEBUG('l_nuvos_sa_bal_type_id: ' || l_nuvos_sa_bal_type_id); -- For Nuvos
2280 DEBUG('l_nuvos_sa_ptd_bal_id: ' || l_nuvos_sa_ptd_bal_id);
2281 -- For 115.29
2282 DEBUG('l_nuvos_sa_ytd_bal_id: ' || l_nuvos_sa_ytd_bal_id);
2283
2284 debug_exit(l_proc_name);
2285 END IF;
2286 EXCEPTION
2287 WHEN OTHERS
2288 THEN
2289 clear_cache;
2290
2291 IF SQLCODE <> hr_utility.hr_error_number
2292 THEN
2293 debug_others(l_proc_name, l_proc_step);
2294
2295 IF g_debug
2296 THEN
2297 DEBUG('Leaving: ' || l_proc_name, -999);
2298 END IF;
2299
2300 fnd_message.raise_error;
2301 ELSE
2302 RAISE;
2303 END IF;
2304 END get_pen_balance_details;
2305
2306 -- This procedure gets all the avc details that has an associated COMP
2307 -- OCP
2308 -- ----------------------------------------------------------------------------
2309 -- |----------------------------< get_avc_pen_balance_details >---------------|
2310 -- ----------------------------------------------------------------------------
2311 PROCEDURE get_avc_pen_balance_details(
2312 p_associated_ocp_ele_id IN NUMBER
2313 ,p_information_type IN VARCHAR2
2314 ,p_tab_avc_pen_bal_dtls IN OUT NOCOPY t_ele_bal_dtls
2315 )
2316 IS
2317 --
2318 -- Cursor to fetch eei information
2319 CURSOR csr_get_avc_eei_info
2320 IS
2321 SELECT *
2322 FROM pay_element_type_extra_info
2323 WHERE information_type = p_information_type
2324 AND eei_information16 = p_associated_ocp_ele_id
2325 AND eei_information12 IS NULL;
2326
2327 CURSOR csr_chk_classification (
2328 p_element_type_id NUMBER
2329 )
2330 IS
2331 SELECT pec.classification_name
2332 FROM pay_element_types_f petf, pay_element_classifications pec
2333 WHERE petf.element_type_id = p_element_type_id
2334 AND pec.classification_id = petf.classification_id;
2335
2336
2337
2338 l_proc_name VARCHAR2(80)
2339 := g_proc_name || 'get_avc_pen_balance_details';
2340 l_proc_step PLS_INTEGER;
2341 l_rec_avc_eei_info pay_element_type_extra_info%ROWTYPE;
2342 l_ele_classification pay_element_classifications.classification_name%TYPE;
2343 l_element_type_id NUMBER;
2344 l_scheme_prefix pay_element_type_extra_info.eei_information18%TYPE;
2345 l_balance_name pay_balance_types.balance_name%TYPE;
2346 l_balance_type_id NUMBER;
2347 l_defined_bal_id NUMBER;
2348 -- For 115.29
2349 l_pen_defined_bal_id NUMBER;
2350 l_tab_avc_pen_bal_dtls t_ele_bal_dtls;
2351 l_value NUMBER;
2352 --
2353 BEGIN
2354 --
2355
2356 l_element_type_id := NULL;
2357 l_scheme_prefix := NULL;
2358 l_balance_name := NULL;
2359 l_defined_bal_id := NULL;
2360 -- For 115.29
2361 l_pen_defined_bal_id := NULL;
2362 l_tab_avc_pen_bal_dtls := p_tab_avc_pen_bal_dtls;
2363 OPEN csr_get_avc_eei_info;
2364
2365 LOOP
2366 FETCH csr_get_avc_eei_info INTO l_rec_avc_eei_info;
2367 EXIT WHEN csr_get_avc_eei_info%NOTFOUND;
2368
2369 IF g_debug
2370 THEN
2371 l_proc_step := 10;
2372 debug_enter(l_proc_name);
2373 DEBUG('p_associated_ocp_ele_id: ' || p_associated_ocp_ele_id);
2374 DEBUG('p_information_type: ' || p_information_type);
2375 END IF;
2376
2377 IF g_debug
2378 THEN
2379 l_proc_step := 20;
2380 DEBUG(l_proc_name, l_proc_step);
2381 END IF;
2382
2383 -- If this AVC is of COMP type, pick up details
2384 IF l_rec_avc_eei_info.eei_information8 = 'COMP'
2385 THEN
2386
2387 -- Get the scheme prefix
2388 l_element_type_id :=
2389 l_rec_avc_eei_info.element_type_id;
2390 l_scheme_prefix :=
2391 l_rec_avc_eei_info.eei_information18;
2392 l_balance_name :=
2393 l_scheme_prefix
2394 || ' AVC EES Contribution';
2395 l_balance_type_id :=
2396 get_balance_type_id(
2397 p_balance_name => l_balance_name
2398 ,p_business_group_id => g_business_group_id
2399 ,p_legislation_code => NULL
2400 );
2401 l_defined_bal_id :=
2402 get_defined_balance(
2403 p_balance_type_id => l_balance_type_id
2404 ,p_balance_dimension_id => g_procptd_dimension_id
2405 );
2406 -- For 115.29
2407 l_pen_defined_bal_id :=
2408 get_defined_balance(
2409 p_balance_type_id => l_balance_type_id
2410 ,p_balance_dimension_id => g_penytd_dimension_id
2411 );
2412
2413 l_tab_avc_pen_bal_dtls(l_element_type_id).balance_name :=
2414 l_balance_name;
2415 l_tab_avc_pen_bal_dtls(l_element_type_id).balance_type_id :=
2416 l_balance_type_id;
2417 l_tab_avc_pen_bal_dtls(l_element_type_id).defined_balance_id :=
2418 l_defined_bal_id;
2419 -- For 115.29
2420 l_tab_avc_pen_bal_dtls(l_element_type_id).pen_defined_balance_id :=
2421 l_pen_defined_bal_id;
2422
2423 IF g_debug
2424 THEN
2425 l_proc_step := 30;
2426 DEBUG(l_proc_name, l_proc_step);
2427 DEBUG('l_element_type_id: ' || l_element_type_id);
2428 DEBUG('l_scheme_prefix: ' || l_scheme_prefix);
2429 DEBUG('l_balance_name: ' || l_balance_name);
2430 DEBUG('l_balance_type_id: ' || l_balance_type_id);
2431 DEBUG('l_defined_bal_id: ' || l_defined_bal_id);
2432 -- For 115.29
2433 DEBUG('l_pen_defined_bal_id: ' || l_pen_defined_bal_id);
2434
2435 END IF;
2436
2437 ELSIF l_rec_avc_eei_info.eei_information8 IS NULL -- AVC not of COMP type
2438 THEN
2439
2440 -- now check if this AVC element is 'Pre Tax Deductions'
2441 -- or 'Voluntary Deductions' type
2442 OPEN csr_chk_classification (l_rec_avc_eei_info.element_type_id) ;
2443 FETCH csr_chk_classification INTO l_ele_classification;
2444 CLOSE csr_chk_classification;
2445
2446 -- if 'Pre Tax Deductions' then it should not be null
2447 -- should be COMP or COSR, raise warning
2448 IF l_ele_classification = 'Pre Tax Deductions'
2449 THEN
2450
2451 l_value :=
2452 pqp_gb_psi_functions.raise_extract_warning(
2453 p_error_number => 94892
2454 ,p_error_text => 'BEN_94892_NO_AVC_CLASSIFIC'
2455 ,p_token1 => l_rec_avc_eei_info.eei_information1
2456 );
2457 END IF;
2458 END IF; -- IF l_rec_avc_eei_info.eei_information8 = 'COMP'
2459
2460
2461 l_element_type_id :=
2462 NULL;
2463 l_scheme_prefix :=
2464 NULL;
2465 l_balance_name :=
2466 NULL;
2467 l_defined_bal_id :=
2468 NULL;
2469 l_pen_defined_bal_id :=
2470 NULL;
2471 END LOOP;
2472
2473 CLOSE csr_get_avc_eei_info;
2474 p_tab_avc_pen_bal_dtls := l_tab_avc_pen_bal_dtls;
2475
2476 IF g_debug
2477 THEN
2478 l_proc_step := 40;
2479 DEBUG(l_proc_name, l_proc_step);
2480 debug_exit(l_proc_name);
2481 END IF;
2482 EXCEPTION
2483 WHEN OTHERS
2484 THEN
2485 clear_cache;
2486
2487 IF SQLCODE <> hr_utility.hr_error_number
2488 THEN
2489 debug_others(l_proc_name, l_proc_step);
2490
2491 IF g_debug
2492 THEN
2493 DEBUG('Leaving: ' || l_proc_name, -999);
2494 END IF;
2495
2496 fnd_message.raise_error;
2497 ELSE
2498 RAISE;
2499 END IF;
2500 END get_avc_pen_balance_details;
2501
2502 -- This procedure is used to set any globals needed for this extract
2503 --
2504 -- ----------------------------------------------------------------------------
2505 -- |----------------------------< set_earnings_history_globals >--------------|
2506 -- ----------------------------------------------------------------------------
2507 PROCEDURE set_earnings_history_globals(
2508 p_business_group_id IN NUMBER
2509 ,p_effective_date IN DATE
2510 )
2511 IS
2512 --
2513 l_proc_name VARCHAR2(80)
2514 := g_proc_name || 'set_earnings_history_globals';
2515 l_proc_step PLS_INTEGER;
2516 l_input_value_name pay_input_values_f.NAME%TYPE;
2517 l_input_value_id NUMBER;
2518 l_element_type_id NUMBER;
2519 l_tab_config_values pqp_utilities.t_config_values;
2520 i NUMBER;
2521 l_error_code NUMBER;
2522 l_error_message VARCHAR2(2400);
2523 l_year VARCHAR2(10);
2524 l_scheme_prefix pay_element_type_extra_info.eei_information18%TYPE;
2525 l_tab_avc_pen_bal_dtls t_ele_bal_dtls;
2526 l_psi_pens_category pqp_configuration_values.pcv_information1%TYPE;
2527 l_template_pens_category pay_element_type_extra_info.eei_information4%TYPE;
2528 l_rec_pen_bal_dtls r_pen_bal_dtls;
2529 l_rec_eeit_info pay_element_type_extra_info%ROWTYPE;
2530 l_element_name pay_element_types_f.element_name%TYPE;
2531 --
2532 BEGIN
2533 --
2534 IF g_debug
2535 THEN
2536 l_proc_step := 10;
2537 debug_enter(l_proc_name);
2538 DEBUG('p_business_group_id: ' || p_business_group_id);
2539 DEBUG('p_effective_date: '
2540 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
2541 END IF;
2542
2543 -- set global variables
2544 g_business_group_id := p_business_group_id;
2545 g_extract_type :=
2546 get_extract_type(p_ext_dfn_id => g_ext_dfn_id);
2547 g_effective_date := p_effective_date;
2548
2549 -- IF g_extract_type = 'CUTOVER' THEN
2550 -- g_effective_date := g_cutover_date;
2551 -- ELSIF g_extract_type = 'PERIODIC' THEN
2552 -- g_effective_date := p_effective_date;
2553 -- END IF; -- End if of p_extract_type is cutover check ...
2554
2555 IF g_debug
2556 THEN
2557 l_proc_step := 20;
2558 DEBUG(l_proc_name, l_proc_step);
2559 END IF;
2560
2561 -- set effective end date
2562 IF TO_NUMBER(TO_CHAR(g_effective_date, 'MM')) < 4
2563 THEN
2564 -- subtract a year
2565 l_year := TO_CHAR(ADD_MONTHS(g_effective_date, -12), 'YYYY');
2566 ELSE
2567 l_year := TO_CHAR(g_effective_date, 'YYYY');
2568 END IF;
2569
2570 g_effective_start_date := TO_DATE('01/04/' || l_year, 'DD/MM/YYYY');
2571 g_effective_end_date := LAST_DAY(g_effective_date);
2572
2573 IF g_debug
2574 THEN
2575 l_proc_step := 30;
2576 DEBUG(l_proc_name, l_proc_step);
2577 DEBUG(
2578 'g_effective_start_date: '
2579 || TO_CHAR(g_effective_start_date, 'DD/MM/YYYY')
2580 );
2581 DEBUG(
2582 'g_effective_end_date: '
2583 || TO_CHAR(g_effective_end_date, 'DD/MM/YYYY')
2584 );
2585 END IF;
2586
2587 -- Get the assignment status type id for
2588 -- active assignments
2589 -- get_asg_status_type
2590 -- (p_per_system_status => 'ACTIVE_ASSIGN'
2591 -- ,p_rec_asg_sts_dtls => l_rec_asg_sts_dtls
2592 -- );
2593 -- g_active_asg_sts_id := l_rec_asg_sts_dtls.assignment_status_type_id;
2594 --
2595 -- IF g_debug
2596 -- THEN
2597 -- l_proc_step := 40;
2598 -- debug(l_proc_name, l_proc_step);
2599 -- END IF;
2600 --
2601 -- -- Get the assignment status type id for
2602 -- -- terminations
2603 -- get_asg_status_type
2604 -- (p_per_system_status => 'TERM_ASSIGN'
2605 -- ,p_rec_asg_sts_dtls => l_rec_asg_sts_dtls
2606 -- );
2607 -- g_terminate_asg_sts_id := l_rec_asg_sts_dtls.assignment_status_type_id;
2608
2609 -- IF g_debug
2610 -- THEN
2611 -- l_proc_step := 50;
2612 -- debug(l_proc_name, l_proc_step);
2613 -- END IF;
2614
2615 -- fetch_empl_type_map_cv;
2616
2617 -- Get the bal dimension id for dimension _ASG_PROC_PTD
2618 g_procptd_dimension_id :=
2619 get_bal_dimension_id(
2620 p_dimension_name => '_ASG_PROC_PTD'
2621 ,p_business_group_id => NULL
2622 ,p_legislation_code => g_legislation_code
2623 );
2624
2625 -- For 115.29
2626 -- Get the bal dimension id for dimension _ASG_PEN_YTD
2627 g_penytd_dimension_id :=
2628 get_bal_dimension_id(
2629 p_dimension_name => '_ASG_PEN_YTD'
2630 ,p_business_group_id => NULL
2631 ,p_legislation_code => g_legislation_code
2632 );
2633
2634
2635 -- Get the bal dimension id for dimension _ASG_TRANSFER_PTD
2636 -- g_tdptd_dimension_id :=
2637 -- get_bal_dimension_id(
2638 -- p_dimension_name => '_ASG_TRANSFER_PTD'
2639 -- ,p_business_group_id => NULL
2640 -- ,p_legislation_code => g_legislation_code
2641 -- );
2642
2643 -- Fetch data from configuration values and store in a
2644 -- global collection
2645 IF g_debug
2646 THEN
2647 l_proc_step := 60;
2648 DEBUG(l_proc_name, l_proc_step);
2649 DEBUG('g_procptd_dimension_id: ' || g_procptd_dimension_id);
2650 DEBUG('g_penytd_dimension_id ' || g_penytd_dimension_id); -- For 115.29
2651 END IF;
2652
2653 -- Fetch pension scheme configuration values
2654 fetch_pension_scheme_map_cv(
2655 p_business_group_id => p_business_group_id
2656 ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv
2657 );
2658 i := g_tab_pen_sch_map_cv.FIRST;
2659
2660 -- l_input_value_name := 'Opt Out Date';
2661 IF g_debug
2662 THEN
2663 l_proc_step := 70;
2664 DEBUG(l_proc_name, l_proc_step);
2665 END IF;
2666
2667 WHILE i IS NOT NULL
2668 LOOP
2669 l_element_type_id :=
2670 fnd_number.canonical_to_number(g_tab_pen_sch_map_cv(i).pcv_information1);
2671 l_psi_pens_category :=
2672 g_tab_pen_sch_map_cv(i).pcv_information2;
2673 -- l_input_value_id := get_input_value_id(p_element_type_id => l_element_type_id
2674 -- ,p_effective_date => g_effective_date
2675 -- ,p_input_value_name => l_input_value_name
2676 -- );
2677 l_element_name :=
2678 get_element_name(
2679 p_element_type_id => l_element_type_id
2680 ,p_effective_date => g_effective_date
2681 );
2682 g_tab_pen_ele_ids(l_element_type_id).element_type_id :=
2683 l_element_type_id;
2684 g_tab_pen_ele_ids(l_element_type_id).element_name :=
2685 l_element_name;
2686 -- g_tab_pen_ele_ids(l_element_type_id).input_value_name := l_input_value_name;
2687 -- g_tab_pen_ele_ids(l_element_type_id).input_value_id := l_input_value_id;
2688
2689 get_eeit_info(
2690 p_element_type_id => l_element_type_id
2691 ,p_information_type => 'PQP_GB_PENSION_SCHEME_INFO'
2692 ,p_rec_eeit_info => l_rec_eeit_info
2693 );
2694 l_scheme_prefix :=
2695 l_rec_eeit_info.eei_information18;
2696 l_template_pens_category :=
2697 l_rec_eeit_info.eei_information4;
2698 g_tab_eei_info(l_element_type_id) :=
2699 l_rec_eeit_info;
2700
2701 IF g_debug
2702 THEN
2703 l_proc_step := 80;
2704 DEBUG(l_proc_name, l_proc_step);
2705 END IF;
2706
2707 -- l_template_id := get_template_id
2708 -- (p_template_name => 'GB Pensions'
2709 -- ,p_business_group_id => g_business_group_id
2710 -- ,p_template_type => 'U'
2711 -- );
2712
2713 get_pen_balance_details(
2714 p_element_type_id => l_element_type_id
2715 ,p_base_name => l_scheme_prefix
2716 ,p_pension_category => l_template_pens_category
2717 ,p_psi_pens_category => l_psi_pens_category
2718 ,p_rec_pen_bal_dtls => l_rec_pen_bal_dtls
2719 );
2720
2721 IF l_psi_pens_category = 'CLASSIC'
2722 THEN
2723 -- Classic Scheme store the pension balance information
2724 g_tab_clas_pen_bal_dtls(l_element_type_id) :=
2725 l_rec_pen_bal_dtls;
2726 ELSIF l_psi_pens_category = 'PREMIUM'
2727 THEN
2728 -- Premium scheme store the pension balance information
2729 g_tab_prem_pen_bal_dtls(l_element_type_id) :=
2730 l_rec_pen_bal_dtls;
2731 ELSIF l_psi_pens_category = 'CLASSPLUS'
2732 THEN
2733 -- Classic Plus scheme store the pension balance information
2734 g_tab_clap_pen_bal_dtls(l_element_type_id) :=
2735 l_rec_pen_bal_dtls;
2736 ELSIF l_psi_pens_category = 'PARTNER'
2737 THEN
2738 -- Partnership scheme store the pension balance information
2739 g_tab_part_pen_bal_dtls(l_element_type_id) :=
2740 l_rec_pen_bal_dtls;
2741 /* For Nuvos */
2742 ELSIF l_psi_pens_category = 'NUVOS'
2743 THEN
2744 -- Nuvos scheme store the pension balance information
2745 g_tab_nuvos_pen_bal_dtls(l_element_type_id) :=
2746 l_rec_pen_bal_dtls;
2747 END IF; -- End if of pension category check ...
2748
2749 IF g_debug
2750 THEN
2751 l_proc_step := 90;
2752 DEBUG(l_proc_name, l_proc_step);
2753 END IF;
2754
2755 IF g_tab_eei_info.EXISTS(l_element_type_id)
2756 -- AND g_tab_eei_info(l_element_type_id).eei_information8 = 'COMP'
2757 -- Above line is commented as we dont check for COMP at OCP level,
2758 -- pick all OCPs, check COMP at AVC level
2759 AND l_template_pens_category = 'OCP'
2760 THEN
2761 -- This is a money purchase scheme OCP
2762 -- Get all the AVCs associated with it
2763 get_avc_pen_balance_details(
2764 p_associated_ocp_ele_id => l_element_type_id
2765 ,p_information_type => 'PQP_GB_PENSION_SCHEME_INFO'
2766 ,p_tab_avc_pen_bal_dtls => l_tab_avc_pen_bal_dtls
2767 );
2768 g_tab_avc_pen_bal_dtls := l_tab_avc_pen_bal_dtls;
2769 END IF;
2770
2771 IF g_debug
2772 THEN
2773 DEBUG(
2774 'Penserver Pension Scheme: '
2775 || g_tab_pen_sch_map_cv(i).pcv_information2
2776 );
2777 DEBUG(
2778 'Template Pension Scheme: '
2779 || g_tab_pen_sch_map_cv(i).pcv_information1
2780 );
2781 DEBUG('Element Type ID: ' || l_element_type_id);
2782 DEBUG('Input Value Name: ' || l_input_value_name);
2783 DEBUG('Input Value ID: ' || l_input_value_id);
2784 DEBUG('Scheme Prefix: ' || l_scheme_prefix);
2785 DEBUG('PSI Pension Category: ' || l_psi_pens_category);
2786 END IF;
2787
2788 i :=
2789 g_tab_pen_sch_map_cv.NEXT(i);
2790 END LOOP;
2791
2792 -- Get NI element type ID and category input value ID
2793 IF g_debug
2794 THEN
2795 l_proc_step := 100;
2796 DEBUG(l_proc_name, l_proc_step);
2797 END IF;
2798
2799 g_ni_ele_type_id :=
2800 pqp_utilities.pqp_get_element_type_id(
2801 p_business_group_id => NULL -- look for seeded
2802 ,p_legislation_code => g_legislation_code
2803 ,p_effective_date => g_effective_date
2804 ,p_element_type_name => 'NI'
2805 ,p_error_code => l_error_code
2806 ,p_message => l_error_message
2807 );
2808
2809 IF g_debug
2810 THEN
2811 l_proc_step := 110;
2812 DEBUG(l_proc_name, l_proc_step);
2813 DEBUG('g_ni_ele_type_id: ' || g_ni_ele_type_id);
2814 END IF;
2815
2816 IF g_ni_ele_type_id IS NOT NULL
2817 THEN
2818 -- Get the input value id as well
2819 g_ni_category_iv_id :=
2820 get_input_value_id(
2821 p_element_type_id => g_ni_ele_type_id
2822 ,p_effective_date => g_effective_date
2823 ,p_input_value_name => 'Category'
2824 ,p_element_name => 'NI'
2825 );
2826
2827 IF g_debug
2828 THEN
2829 l_proc_step := 100;
2830 DEBUG(l_proc_name, l_proc_step);
2831 DEBUG('g_ni_category_iv_id: ' || g_ni_category_iv_id);
2832 END IF;
2833
2834 g_ni_pension_iv_id :=
2835 get_input_value_id(
2836 p_element_type_id => g_ni_ele_type_id
2837 ,p_effective_date => g_effective_date
2838 ,p_input_value_name => 'Pension'
2839 ,p_element_name => 'NI'
2840 );
2841
2842 IF g_debug
2843 THEN
2844 l_proc_step := 110;
2845 DEBUG(l_proc_name, l_proc_step);
2846 DEBUG('g_ni_pension_iv_id: ' || g_ni_pension_iv_id);
2847 END IF;
2848 END IF; -- End if of g_ni_ele_type_id is not null check ...
2849
2850 -- Get the NI E UEL and ET defined balance ids
2851
2852 g_ni_euel_bal_type_id :=
2853 get_balance_type_id(
2854 p_balance_name => 'NI E Able UEL'
2855 ,p_business_group_id => NULL
2856 ,p_legislation_code => g_legislation_code
2857 );
2858
2859 IF g_ni_euel_bal_type_id IS NOT NULL
2860 THEN
2861 g_ni_euel_ptd_bal_id :=
2862 get_defined_balance(
2863 p_balance_type_id => g_ni_euel_bal_type_id
2864 ,p_balance_dimension_id => g_procptd_dimension_id
2865 );
2866 END IF; -- End if of g_ni_euel_bal_type_id is not null check ...
2867
2868 g_ni_eet_bal_type_id :=
2869 get_balance_type_id(
2870 p_balance_name => 'NI E Able ET'
2871 ,p_business_group_id => NULL
2872 ,p_legislation_code => g_legislation_code
2873 );
2874
2875 IF g_ni_eet_bal_type_id IS NOT NULL
2876 THEN
2877 g_ni_eet_ptd_bal_id :=
2878 get_defined_balance(
2879 p_balance_type_id => g_ni_eet_bal_type_id
2880 ,p_balance_dimension_id => g_procptd_dimension_id
2881 );
2882 END IF; -- End if of g_ni_eet_bal_type_id is not null check ...
2883
2884 IF g_debug
2885 THEN
2886 l_proc_step := 120;
2887 DEBUG(l_proc_name, l_proc_step);
2888 DEBUG('g_ni_euel_bal_type_id: ' || g_ni_euel_bal_type_id);
2889 DEBUG('g_ni_euel_ptd_bal_id: ' || g_ni_euel_ptd_bal_id);
2890 DEBUG('g_ni_eet_bal_type_id: ' || g_ni_eet_bal_type_id);
2891 DEBUG('g_ni_eet_ptd_bal_id: ' || g_ni_eet_ptd_bal_id);
2892 END IF;
2893
2894 -- Get the balance type ids for generic balance
2895 -- Total BuyBack Contributions
2896 -- Commenting the below code as not used
2897 /* g_tot_byb_cont_bal_id :=
2898 get_balance_type_id(
2899 p_balance_name => 'Total BuyBack Contributions'
2900 ,p_business_group_id => NULL
2901 ,p_legislation_code => g_legislation_code
2902 );
2903
2904 IF g_tot_byb_cont_bal_id IS NOT NULL
2905 THEN
2906 g_tot_byb_ptd_bal_id :=
2907 get_defined_balance(
2908 p_balance_type_id => g_tot_byb_cont_bal_id
2909 ,p_balance_dimension_id => g_procptd_dimension_id
2910 );
2911 END IF; -- End if of g_tot_byb_cont_bal_id is not null check ...
2912
2913 IF g_debug
2914 THEN
2915 l_proc_step := 130;
2916 DEBUG(l_proc_name, l_proc_step);
2917 DEBUG('g_tot_byb_cont_bal_id: ' || g_tot_byb_cont_bal_id);
2918 DEBUG('g_tot_byb_ptd_bal_id: ' || g_tot_byb_ptd_bal_id);
2919 END IF; */
2920
2921 -- Get the balance type ids for generic balance
2922 -- Pensrv Added Years Contribution
2923
2924 g_tot_ayr_cont_bal_id :=
2925 get_balance_type_id(
2926 p_balance_name => 'Pensrv Added Years Contribution'
2927 ,p_business_group_id => NULL
2928 ,p_legislation_code => g_legislation_code
2929 );
2930
2931 IF g_tot_ayr_cont_bal_id IS NOT NULL
2932 THEN
2933 g_tot_ayr_ptd_bal_id :=
2934 get_defined_balance(
2935 p_balance_type_id => g_tot_ayr_cont_bal_id
2936 ,p_balance_dimension_id => g_procptd_dimension_id
2937 );
2938 -- For 115.29
2939 g_tot_ayr_ytd_bal_id :=
2940 get_defined_balance(
2941 p_balance_type_id => g_tot_ayr_cont_bal_id
2942 ,p_balance_dimension_id => g_penytd_dimension_id
2943 );
2944
2945 END IF; -- End if of g_tot_byb_cont_bal_id is not null check ...
2946
2947 IF g_debug
2948 THEN
2949 l_proc_step := 131;
2950 DEBUG(l_proc_name, l_proc_step);
2951 DEBUG('g_tot_ayr_cont_bal_id: ' || g_tot_ayr_cont_bal_id);
2952 DEBUG('g_tot_ayr_ptd_bal_id: ' || g_tot_ayr_ptd_bal_id);
2953 -- For 115.29
2954 DEBUG('g_tot_ayr_ytd_bal_id: ' || g_tot_ayr_ytd_bal_id);
2955 END IF;
2956
2957
2958 -- Get the balance type ids for generic balance
2959 -- Pensrv Added Years Family Benefit Contribution
2960
2961 g_tot_ayr_fb_cont_bal_id :=
2962 get_balance_type_id(
2963 p_balance_name => 'Pensrv Added Years Family Benefit Contribution'
2964 ,p_business_group_id => NULL
2965 ,p_legislation_code => g_legislation_code
2966 );
2967
2968 IF g_tot_ayr_fb_cont_bal_id IS NOT NULL
2969 THEN
2970 g_tot_ayr_fb_ptd_bal_id :=
2971 get_defined_balance(
2972 p_balance_type_id => g_tot_ayr_fb_cont_bal_id
2973 ,p_balance_dimension_id => g_procptd_dimension_id
2974 );
2975 -- For 115.29
2976 g_tot_ayr_fb_ytd_bal_id :=
2977 get_defined_balance(
2978 p_balance_type_id => g_tot_ayr_fb_cont_bal_id
2979 ,p_balance_dimension_id => g_penytd_dimension_id
2980 );
2981
2982 END IF; -- End if of g_tot_byb_cont_bal_id is not null check ...
2983
2984 IF g_debug
2985 THEN
2986 l_proc_step := 132;
2987 DEBUG(l_proc_name, l_proc_step);
2988 DEBUG('g_tot_ayr_fb_cont_bal_id: ' || g_tot_ayr_fb_cont_bal_id);
2989 DEBUG('g_tot_ayr_fb_ptd_bal_id: ' || g_tot_ayr_fb_ptd_bal_id);
2990 DEBUG('g_tot_ayr_fb_ytd_bal_id: ' || g_tot_ayr_fb_ytd_bal_id);
2991 END IF;
2992
2993 /* Begin For Nuvos Change */
2994 -- Get the balance type ids for generic balance
2995 -- Pensrv APAVC Contribution
2996
2997 g_tot_apavc_cont_bal_id :=
2998 get_balance_type_id(
2999 p_balance_name => 'Pensrv APAVC Contribution'
3000 ,p_business_group_id => NULL
3001 ,p_legislation_code => g_legislation_code
3002 );
3003
3004 IF g_tot_ayr_cont_bal_id IS NOT NULL
3005 THEN
3006 g_tot_apavc_ptd_bal_id :=
3007 get_defined_balance(
3008 p_balance_type_id => g_tot_apavc_cont_bal_id
3009 ,p_balance_dimension_id => g_procptd_dimension_id
3010 );
3011 -- For 115.29
3012 g_tot_apavc_ytd_bal_id :=
3013 get_defined_balance(
3014 p_balance_type_id => g_tot_apavc_cont_bal_id
3015 ,p_balance_dimension_id => g_penytd_dimension_id
3016 );
3017 END IF; -- End if of g_tot_byb_cont_bal_id is not null check ...
3018
3019 IF g_debug
3020 THEN
3021 l_proc_step := 133;
3022 DEBUG(l_proc_name, l_proc_step);
3023 DEBUG('g_tot_apavc_cont_bal_id: ' || g_tot_apavc_cont_bal_id);
3024 DEBUG('g_tot_apavc_ptd_bal_id: ' || g_tot_apavc_ptd_bal_id);
3025 -- For 115.29
3026 DEBUG('g_tot_apavc_ytd_bal_id: ' || g_tot_apavc_ytd_bal_id);
3027 END IF;
3028
3029 -- Get the balance type ids for generic balance
3030 -- Pensrv APAVC Contribution
3031
3032 g_tot_apavcm_cont_bal_id :=
3033 get_balance_type_id(
3034 p_balance_name => 'Pensrv APAVCM Contribution'
3035 ,p_business_group_id => NULL
3036 ,p_legislation_code => g_legislation_code
3037 );
3038
3039 IF g_tot_apavcm_cont_bal_id IS NOT NULL
3040 THEN
3041 g_tot_apavcm_ptd_bal_id :=
3042 get_defined_balance(
3043 p_balance_type_id => g_tot_apavcm_cont_bal_id
3044 ,p_balance_dimension_id => g_procptd_dimension_id
3045 );
3046 -- For 115.29
3047 g_tot_apavcm_ytd_bal_id :=
3048 get_defined_balance(
3049 p_balance_type_id => g_tot_apavcm_cont_bal_id
3050 ,p_balance_dimension_id => g_penytd_dimension_id
3051 );
3052 END IF; -- End if of g_tot_byb_cont_bal_id is not null check ...
3053
3054 IF g_debug
3055 THEN
3056 l_proc_step := 133;
3057 DEBUG(l_proc_name, l_proc_step);
3058 DEBUG('g_tot_apavcm_cont_bal_id: ' || g_tot_apavcm_cont_bal_id);
3059 DEBUG('g_tot_apavcm_ptd_bal_id: ' || g_tot_apavcm_ptd_bal_id);
3060 -- For 115.29
3061 DEBUG('g_tot_apavcm_ytd_bal_id: ' || g_tot_apavcm_ytd_bal_id);
3062 END IF;
3063
3064 /* END For Nuvos Change */
3065
3066 -- IF g_extract_type = 'PERIODIC' THEN
3067 -- IF g_debug
3068 -- THEN
3069 -- l_proc_step := 80;
3070 -- debug(l_proc_name, l_proc_step);
3071 -- END IF;
3072 -- -- populated dated table ids
3073 -- set_dated_table_collection;
3074 --
3075 -- -- populate event group colleciton
3076 -- IF g_debug
3077 -- THEN
3078 -- l_proc_step := 90;
3079 -- debug(l_proc_name, l_proc_step);
3080 -- END IF;
3081 -- set_event_group_collection;
3082 -- END IF; -- End if of extract type = periodic check ...
3083
3084
3085 IF g_debug
3086 THEN
3087 l_proc_step := 140;
3088 DEBUG('g_business_group_id: ' || g_business_group_id);
3089 DEBUG('g_effective_date: '
3090 || TO_CHAR(g_effective_date, 'DD/MON/YYYY'));
3091 DEBUG('g_extract_type: ' || g_extract_type);
3092 -- DEBUG('g_active_asg_sts_id: '||g_active_asg_sts_id);
3093 -- DEBUG('g_terminate_asg_sts_id: '||g_terminate_asg_sts_id);
3094 debug_exit(l_proc_name);
3095 END IF;
3096 EXCEPTION
3097 WHEN OTHERS
3098 THEN
3099 clear_cache;
3100
3101 IF SQLCODE <> hr_utility.hr_error_number
3102 THEN
3103 debug_others(l_proc_name, l_proc_step);
3104
3105 IF g_debug
3106 THEN
3107 DEBUG('Leaving: ' || l_proc_name, -999);
3108 END IF;
3109
3110 fnd_message.raise_error;
3111 ELSE
3112 RAISE;
3113 END IF;
3114 END set_earnings_history_globals;
3115
3116 -- This function is used to evaluate assignments that
3117 -- qualify for penserver earnings history interface
3118 -- ----------------------------------------------------------------------------
3119 -- |---------------------< chk_earnings_history_criteria >--------------------|
3120 -- ----------------------------------------------------------------------------
3121 FUNCTION chk_earnings_history_criteria(
3122 p_business_group_id IN NUMBER
3123 ,p_effective_date IN DATE
3124 ,p_assignment_id IN NUMBER
3125 )
3126 RETURN VARCHAR2
3127 IS
3128 --
3129 l_proc_name VARCHAR2(80)
3130 := g_proc_name || 'chk_earnings_history_criteria';
3131 l_proc_step PLS_INTEGER;
3132 l_include_flag VARCHAR2(10);
3133 l_debug VARCHAR2(10);
3134 i NUMBER;
3135 l_effective_end_date DATE;
3136 l_return VARCHAR2(10);
3137 l_rec_ele_ent_details r_ele_ent_details;
3138 l_value NUMBER;
3139 --
3140 BEGIN
3141 --
3142
3143 IF g_business_group_id IS NULL
3144 THEN
3145 -- Always clear cache before proceeding to set globals
3146 clear_cache;
3147 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
3148 -- -- set g_debug based on process definition configuration
3149 -- IF g_tab_prs_dfn_cv.COUNT = 0
3150 -- THEN
3151 -- fetch_process_defn_cv(p_business_group_id => p_business_group_id);
3152 -- i := g_tab_prs_dfn_cv.FIRST;
3153 --
3154 -- WHILE i IS NOT NULL
3155 -- LOOP
3156 -- l_debug := g_tab_prs_dfn_cv(i).pcv_information1;
3157 -- i := g_tab_prs_dfn_cv.NEXT(i);
3158 -- END LOOP;
3159 --
3160 -- IF l_debug = 'Y'
3161 -- THEN
3162 -- g_debug := TRUE;
3163 -- END IF;
3164 -- END IF; -- End if of prs dfn collection count is zero check ...
3165 END IF; -- End if of g_business_group_id is NULL check ...
3166
3167 IF g_debug
3168 THEN
3169 l_proc_step := 10;
3170 debug_enter(l_proc_name);
3171 DEBUG('p_business_group_id: ' || p_business_group_id);
3172 DEBUG('p_effective_date: '
3173 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
3174 DEBUG('p_assignment_id: ' || p_assignment_id);
3175 DEBUG(
3176 'ben_ext_person.g_effective_date: '
3177 || TO_CHAR(ben_ext_person.g_effective_date, 'DD/MON/YYYY')
3178 );
3179 DEBUG(
3180 'ben_ext_person.g_person_ext_dt: '
3181 || TO_CHAR(ben_ext_person.g_person_ext_dt, 'DD/MON/YYYY')
3182 );
3183 END IF;
3184
3185 l_include_flag := 'N';
3186
3187 IF g_business_group_id IS NULL
3188 THEN
3189 -- Call clear cache function to clear cached variables
3190 IF g_debug
3191 THEN
3192 DEBUG('g_business_group_id: ' || g_business_group_id);
3193 l_proc_step := 20;
3194 DEBUG(l_proc_name, l_proc_step);
3195 END IF;
3196
3197 -- set shared globals
3198 pqp_gb_psi_functions.set_shared_globals(
3199 p_business_group_id => p_business_group_id
3200 ,p_paypoint => g_paypoint
3201 ,p_cutover_date => g_cutover_date
3202 ,p_ext_dfn_id => g_ext_dfn_id
3203 );
3204
3205 IF g_debug
3206 THEN
3207 l_proc_step := 30;
3208 DEBUG(l_proc_name, l_proc_step);
3209 DEBUG('g_paypoint: ' || g_paypoint);
3210 DEBUG('g_cutover_date: '
3211 || TO_CHAR(g_cutover_date, 'DD/MON/YYYY'));
3212 DEBUG('g_ext_dfn_id: ' || g_ext_dfn_id);
3213 END IF;
3214
3215 -- set extract global variables
3216 set_earnings_history_globals(
3217 p_business_group_id => p_business_group_id
3218 ,p_effective_date => ben_ext_person.g_effective_date
3219 );
3220
3221 IF g_debug
3222 THEN
3223 l_proc_step := 40;
3224 DEBUG(l_proc_name, l_proc_step);
3225 END IF;
3226
3227 -- Raise Extract Exceptions
3228 pqp_gb_psi_functions.raise_extract_exceptions('S');
3229 END IF; -- End if of business group id is null check ...
3230
3231 -- IF g_extract_type = 'PERIODIC' THEN
3232 -- g_effective_date := p_effective_date;
3233 -- IF g_debug
3234 -- THEN
3235 -- debug('g_effective_date: '||TO_CHAR(g_effective_date, 'DD/MON/YYYY'));
3236 -- END IF;
3237 -- END IF;
3238
3239 IF p_effective_date BETWEEN g_effective_start_date AND g_effective_end_date
3240 THEN
3241 l_effective_end_date := g_effective_end_date;
3242
3243 IF g_effective_date <> p_effective_date
3244 THEN
3245 l_effective_end_date :=
3246 LEAST(g_effective_end_date, p_effective_date);
3247 END IF;
3248
3249 -- Check penserver basic criteria
3250 IF g_debug
3251 THEN
3252 l_proc_step := 50;
3253 DEBUG(l_proc_name, l_proc_step);
3254 DEBUG(
3255 'l_effective_end_date: '
3256 || TO_CHAR(l_effective_end_date, 'DD/MM/YYYY')
3257 );
3258 END IF;
3259
3260 g_person_dtl := NULL;
3261 g_assignment_dtl := NULL;
3262 l_include_flag :=
3263 pqp_gb_psi_functions.chk_penserver_basic_criteria(
3264 p_business_group_id => g_business_group_id
3265 ,p_effective_date => l_effective_end_date
3266 ,p_assignment_id => p_assignment_id
3267 ,p_person_dtl => g_person_dtl
3268 ,p_assignment_dtl => g_assignment_dtl
3269 );
3270
3271 IF NVL(g_assignment_id, hr_api.g_number) <> p_assignment_id
3272 THEN
3273 clear_asg_cache;
3274 g_assignment_id := p_assignment_id;
3275 END IF;
3276
3277 IF g_debug
3278 THEN
3279 l_proc_step := 60;
3280 DEBUG(l_proc_name, l_proc_step);
3281 DEBUG('l_include_flag: ' || l_include_flag);
3282 DEBUG('g_extract_type: ' || g_extract_type);
3283 DEBUG('Person ID: ' || g_person_dtl.person_id);
3284 DEBUG('Full Name: ' || g_person_dtl.full_name);
3285 DEBUG('Assignment Number: ' || g_assignment_dtl.assignment_number);
3286 END IF;
3287
3288 IF l_include_flag = 'Y'
3289 THEN
3290 -- Check earnings history criteria
3291 l_return :=
3292 get_pen_scheme_memb(
3293 p_assignment_id => p_assignment_id
3294 ,p_effective_start_date => g_effective_start_date
3295 ,p_effective_end_date => l_effective_end_date
3296 ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv
3297 ,p_rec_ele_ent_details => l_rec_ele_ent_details
3298 );
3299 g_member := l_return;
3300
3301 IF l_return = 'N'
3302 THEN
3303 IF g_debug
3304 THEN
3305 l_proc_step := 75;
3306 DEBUG(l_proc_name, l_proc_step);
3307 END IF;
3308
3309 l_value :=
3310 pqp_gb_psi_functions.raise_extract_warning(
3311 p_error_number => 93775
3312 ,p_error_text => 'BEN_93775_EXT_PSI_NOT_PEN_MEMB'
3313 ,p_token1 => p_assignment_id
3314 ,p_token2 => fnd_date.date_to_displaydt(g_effective_date)
3315 );
3316 END IF;
3317 END IF; -- End if of l_include_flag is Y check ...
3318
3319 -- ELSIF g_extract_type = 'PERIODIC' THEN
3320 --
3321 -- IF g_debug
3322 -- THEN
3323 -- l_proc_step := 80;
3324 -- debug(l_proc_name, l_proc_step);
3325 --
3326 -- END IF;
3327 --
3328 -- l_include_flag := chk_ern_periodic_criteria
3329 -- (p_assignment_id => p_assignment_id);
3330 -- IF l_include_flag = 'Y' THEN
3331 -- NULL;
3332 -- END IF;
3333 -- END IF; -- End if of g_extract_type = 'CUTOVER' check ...
3334 END IF; -- termination date within extract run dates check ...
3335
3336 IF g_debug
3337 THEN
3338 l_proc_step := 80;
3339 DEBUG('l_return: ' || l_return);
3340 DEBUG('l_include_flag: ' || l_include_flag);
3341 debug_exit(l_proc_name);
3342 END IF;
3343
3344 RETURN l_include_flag;
3345 EXCEPTION
3346 WHEN OTHERS
3347 THEN
3348 clear_cache;
3349
3350 IF SQLCODE <> hr_utility.hr_error_number
3351 THEN
3352 debug_others(l_proc_name, l_proc_step);
3353
3354 IF g_debug
3355 THEN
3356 DEBUG('Leaving: ' || l_proc_name, -999);
3357 END IF;
3358
3359 fnd_message.raise_error;
3360 ELSE
3361 RAISE;
3362 END IF;
3363 END chk_earnings_history_criteria;
3364
3365 -- This function returns the current NI category
3366 -- for an assignment
3367 -- ----------------------------------------------------------------------------
3368 -- |------------------------------< get_ni_category >-------------------------|
3369 -- ----------------------------------------------------------------------------
3370 FUNCTION get_ni_category(p_assignment_id IN NUMBER)
3371 RETURN VARCHAR2
3372 IS
3373 --
3374 l_proc_name VARCHAR2(80)
3375 := g_proc_name || 'get_ni_category';
3376 l_proc_step PLS_INTEGER;
3377 l_ni_category VARCHAR2(10);
3378 l_return VARCHAR2(10);
3379 l_rec_ele_ent_details r_ele_ent_details;
3380 l_value NUMBER;
3381 l_effective_end_date DATE;
3382 --
3383 BEGIN
3384 --
3385 IF g_debug
3386 THEN
3387 l_proc_step := 10;
3388 debug_enter(l_proc_name);
3389 DEBUG('p_assignment_id: ' || p_assignment_id);
3390 END IF;
3391
3392 l_ni_category := NULL;
3393 l_effective_end_date := g_effective_end_date;
3394
3395 IF g_effective_date <> ben_ext_person.g_person_ext_dt
3396 THEN
3397 l_effective_end_date :=
3398 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
3399 END IF;
3400
3401 IF g_debug
3402 THEN
3403 DEBUG(
3404 'l_effective_end_date: '
3405 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
3406 );
3407 END IF;
3408
3409 -- Get the NI element entry details for this assignment
3410 l_return :=
3411 get_ele_ent_details(
3412 p_assignment_id => p_assignment_id
3413 ,p_effective_start_date => g_effective_start_date
3414 ,p_effective_end_date => l_effective_end_date
3415 ,p_element_type_id => g_ni_ele_type_id
3416 ,p_rec_ele_ent_details => l_rec_ele_ent_details
3417 );
3418
3419 IF l_return = 'Y'
3420 THEN
3421 -- NI element exists
3422 -- Find the NI category
3423 IF g_debug
3424 THEN
3425 l_proc_step := 20;
3426 DEBUG(l_proc_name, l_proc_step);
3427 END IF;
3428
3429 l_ni_category :=
3430 get_screen_entry_value(
3431 p_element_entry_id => l_rec_ele_ent_details.element_entry_id
3432 ,p_effective_start_date => l_rec_ele_ent_details.effective_start_date
3433 ,p_effective_end_date => l_rec_ele_ent_details.effective_end_date
3434 ,p_input_value_id => g_ni_category_iv_id
3435 );
3436 g_ni_ele_ent_details := l_rec_ele_ent_details;
3437 ELSE -- raise person data warning
3438 l_value :=
3439 pqp_gb_psi_functions.raise_extract_warning(
3440 p_error_number => 94480
3441 ,p_error_text => 'BEN_94480_EXT_PSI_NO_NI_ELEMT'
3442 ,p_token1 => p_assignment_id
3443 ,p_token2 => fnd_date.date_to_displaydt(g_effective_date)
3444 );
3445 END IF; -- End if of ni element entry exists check ...
3446
3447 IF g_debug
3448 THEN
3449 l_proc_step := 30;
3450 DEBUG(l_proc_name, l_proc_step);
3451 DEBUG('l_ni_category: ' || l_ni_category);
3452 debug_exit(l_proc_name);
3453 END IF;
3454
3455 RETURN l_ni_category;
3456 EXCEPTION
3457 WHEN OTHERS
3458 THEN
3459 clear_cache;
3460
3461 IF SQLCODE <> hr_utility.hr_error_number
3462 THEN
3463 debug_others(l_proc_name, l_proc_step);
3464
3465 IF g_debug
3466 THEN
3467 DEBUG('Leaving: ' || l_proc_name, -999);
3468 END IF;
3469
3470 fnd_message.raise_error;
3471 ELSE
3472 RAISE;
3473 END IF;
3474 END get_ni_category;
3475
3476 -- This function returns the contracted out earnings figure
3477 -- for an assignment
3478 -- ----------------------------------------------------------------------------
3479 -- |--------------------------< get_contracted_out_earnings >-----------------|
3480 -- ----------------------------------------------------------------------------
3481 FUNCTION get_contracted_out_earnings(p_assignment_id IN NUMBER
3482 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE) -- For Bug 5941475
3483 RETURN NUMBER
3484 IS
3485 --
3486 CURSOR csr_get_screen_ent_val(
3487 c_element_entry_id NUMBER
3488 ,c_input_value_id NUMBER
3489 ,c_effective_start_date DATE
3490 ,c_effective_end_date DATE
3491 )
3492 IS
3493 SELECT screen_entry_value, effective_start_date, effective_end_date
3494 FROM pay_element_entry_values_f
3495 WHERE element_entry_id = c_element_entry_id
3496 AND (
3497 effective_start_date BETWEEN c_effective_start_date
3498 AND c_effective_end_date
3499 OR effective_end_date BETWEEN c_effective_start_date
3500 AND c_effective_end_date
3501 OR c_effective_start_date BETWEEN effective_start_date
3502 AND effective_end_date
3503 OR c_effective_end_date BETWEEN effective_start_date
3504 AND effective_end_date
3505 )
3506 AND input_value_id = c_input_value_id;
3507
3508 l_proc_name VARCHAR2(80)
3509 := g_proc_name || 'get_contracted_out_earnings';
3510 l_proc_step PLS_INTEGER;
3511 l_rec_screen_ent_val csr_get_screen_ent_val%ROWTYPE;
3512 l_tab_ni_cont_out_bals t_varchar2;
3513 l_effective_end_date DATE;
3514 l_ni_category pay_element_entry_values_f.screen_entry_value%TYPE;
3515 l_ni_pension pay_element_entry_values_f.screen_entry_value%TYPE;
3516 i NUMBER;
3517 l_ni_bal_name t_varchar2;
3518 l_return VARCHAR2(10);
3519 l_index NUMBER;
3520 l_balance_type_id NUMBER;
3521 l_defined_balance_id NUMBER;
3522 l_ni_cont_out_earn NUMBER;
3523 l_total_ni_cont_out_earn NUMBER;
3524 l_element_entry_id NUMBER;
3525 --
3526 BEGIN
3527 --
3528 IF g_debug
3529 THEN
3530 l_proc_step := 10;
3531 debug_enter(l_proc_name);
3532 DEBUG('p_assignment_id: ' || p_assignment_id);
3533 END IF;
3534
3535 l_effective_end_date := g_effective_end_date;
3536
3537 IF g_effective_date <> ben_ext_person.g_person_ext_dt
3538 THEN
3539 l_effective_end_date :=
3540 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
3541 END IF;
3542
3543 IF g_debug
3544 THEN
3545 DEBUG(
3546 'l_effective_end_date: '
3547 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
3548 );
3549 END IF;
3550
3551 l_element_entry_id := g_ni_ele_ent_details.element_entry_id;
3552 l_total_ni_cont_out_earn := 0;
3553 l_ni_cont_out_earn := 0;
3554
3555 IF l_element_entry_id IS NOT NULL
3556 THEN
3557 IF g_debug
3558 THEN
3559 l_proc_step := 20;
3560 DEBUG(l_proc_name, l_proc_step);
3561 DEBUG('l_element_entry_id: ' || l_element_entry_id);
3562 END IF;
3563
3564 -- Get all the category / pension details for this NI element
3565 -- entry id
3566 OPEN csr_get_screen_ent_val(
3567 l_element_entry_id
3568 ,g_ni_category_iv_id
3569 ,g_effective_start_date
3570 ,l_effective_end_date
3571 );
3572
3573 LOOP
3574 FETCH csr_get_screen_ent_val INTO l_rec_screen_ent_val;
3575 EXIT WHEN csr_get_screen_ent_val%NOTFOUND;
3576
3577 IF g_debug
3578 THEN
3579 l_proc_step := 30;
3580 DEBUG(l_proc_name, l_proc_step);
3581 DEBUG(
3582 'l_rec_screen_ent_val.screen_entry_value: '
3583 || l_rec_screen_ent_val.screen_entry_value
3584 );
3585 DEBUG(
3586 'l_rec_screen_ent_val.effective_start_date: '
3587 || TO_CHAR(
3588 l_rec_screen_ent_val.effective_start_date
3589 ,'DD/MM/YYYY'
3590 )
3591 );
3592 DEBUG(
3593 'l_rec_screen_ent_val.effective_end_date: '
3594 || TO_CHAR(l_rec_screen_ent_val.effective_end_date
3595 ,'DD/MM/YYYY')
3596 );
3597 END IF;
3598
3599 l_ni_category := l_rec_screen_ent_val.screen_entry_value;
3600
3601 IF l_ni_category = 'E'
3602 THEN
3603 -- NI E category exists for this assignment
3604 -- for this extract period
3605 g_ni_e_cat_exists := 'Y';
3606 END IF;
3607
3608 -- Get the NI pension info
3609 l_ni_pension :=
3610 get_screen_entry_value(
3611 p_element_entry_id => g_ni_ele_ent_details.element_entry_id
3612 ,p_effective_start_date => l_rec_screen_ent_val.effective_start_date
3613 ,p_effective_end_date => l_rec_screen_ent_val.effective_end_date
3614 ,p_input_value_id => g_ni_pension_iv_id
3615 );
3616
3617 IF g_debug
3618 THEN
3619 l_proc_step := 40;
3620 DEBUG(l_proc_name, l_proc_step);
3621 DEBUG('l_ni_pension: ' || l_ni_pension);
3622 END IF;
3623
3624 IF l_ni_pension IN('C', 'M') -- contracted out
3625 THEN
3626 -- We are interested in this category only
3627 -- if it is contracted out
3628 l_ni_category := l_rec_screen_ent_val.screen_entry_value;
3629 -- Get the balance information
3630 i := 1;
3631 l_ni_bal_name(i) := 'NI ' || l_ni_category || ' Able UEL';
3632 i := i + 1;
3633 l_ni_bal_name(i) := 'NI ' || l_ni_category || ' Able ET';
3634 i := l_ni_bal_name.FIRST;
3635
3636 WHILE i IS NOT NULL
3637 LOOP
3638 l_return :=
3639 chk_value_in_collection(
3640 p_collection_name => l_tab_ni_cont_out_bals
3641 ,p_value => l_ni_bal_name(i)
3642 ,p_index => l_index
3643 );
3644
3645 IF l_return = 'N'
3646 THEN
3647 l_return :=
3648 chk_value_in_collection(
3649 p_collection_name => g_tab_ni_cont_out_bals
3650 ,p_value => l_ni_bal_name(i)
3651 ,p_index => l_index
3652 );
3653
3654 IF l_return = 'Y'
3655 THEN
3656 l_defined_balance_id := l_index;
3657 ELSE
3658 l_balance_type_id :=
3659 get_balance_type_id(
3660 p_balance_name => l_ni_bal_name(i)
3661 ,p_business_group_id => NULL
3662 ,p_legislation_code => g_legislation_code
3663 );
3664 l_defined_balance_id :=
3665 get_defined_balance(
3666 p_balance_type_id => l_balance_type_id
3667 ,p_balance_dimension_id => g_procptd_dimension_id
3668 );
3669
3670 IF g_debug
3671 THEN
3672 l_proc_step := 50;
3673 DEBUG(l_proc_name, l_proc_step);
3674 DEBUG(
3675 'l_ni_bal_name( ' || i || ')'
3676 || l_ni_bal_name(i)
3677 );
3678 DEBUG('l_defined_balance_id: '
3679 || l_defined_balance_id);
3680 DEBUG('l_balance_type_id: ' || l_balance_type_id);
3681 END IF;
3682
3683 -- Store it in the NI contracted out global collection
3684 IF l_defined_balance_id IS NOT NULL
3685 THEN
3686 g_tab_ni_cont_out_bals(l_defined_balance_id) :=
3687 l_ni_bal_name(i);
3688 END IF;
3689 END IF; -- End if of l_return = 'Y' check ...
3690
3691 IF g_debug
3692 THEN
3693 l_proc_step := 60;
3694 DEBUG(l_proc_name, l_proc_step);
3695 DEBUG('l_defined_balance_id: '
3696 || l_defined_balance_id);
3697 END IF;
3698
3699 IF l_defined_balance_id IS NOT NULL
3700 THEN
3701 l_tab_ni_cont_out_bals(l_defined_balance_id) :=
3702 l_ni_bal_name(i);
3703 END IF;
3704 END IF; -- if not already in local collection check ...
3705
3706 i := l_ni_bal_name.NEXT(i);
3707 END LOOP;
3708 END IF; -- End if of ni pension is C check ...
3709 END LOOP;
3710
3711 CLOSE csr_get_screen_ent_val;
3712 END IF; -- End if of element entry id is not null check ...
3713
3714 i := l_tab_ni_cont_out_bals.FIRST;
3715
3716 WHILE i IS NOT NULL
3717 LOOP
3718 IF g_debug
3719 THEN
3720 l_proc_step := 70;
3721 DEBUG(l_proc_name, l_proc_step);
3722 DEBUG('l_defined_balance_id: ' || i);
3723 DEBUG('l_balance_name: ' || l_tab_ni_cont_out_bals(i));
3724 END IF;
3725
3726 l_ni_cont_out_earn := 0;
3727 -- BEGIN
3728
3729 -- For Bug 5941475
3730 IF NOT p_ptd_balance
3731 THEN
3732 l_ni_cont_out_earn :=
3733 get_total_ptd_bal_value(
3734 p_assignment_id => p_assignment_id
3735 ,p_defined_balance_id => i
3736 ,p_effective_start_date => g_effective_start_date
3737 ,p_effective_end_date => l_effective_end_date
3738 );
3739 ELSE
3740 l_ni_cont_out_earn :=
3741 pay_balance_pkg.get_value(
3742 p_defined_balance_id => i
3743 ,p_assignment_id => p_assignment_id
3744 ,p_virtual_date => l_effective_end_date);
3745
3746 END IF; -- IF NOT p_ptd_balance
3747
3748 -- pay_balance_pkg.get_value(
3749 -- p_defined_balance_id => i
3750 -- ,p_assignment_id => p_assignment_id
3751 -- ,p_virtual_date => l_effective_end_date);
3752 -- EXCEPTION
3753 -- WHEN NO_DATA_FOUND
3754 -- THEN
3755 -- IF g_debug
3756 -- THEN
3757 -- DEBUG('No data found exception: ');
3758 -- END IF;
3759 --
3760 -- l_ni_cont_out_earn := 0;
3761 -- END;
3762
3763 l_total_ni_cont_out_earn :=
3764 l_total_ni_cont_out_earn
3765 + l_ni_cont_out_earn;
3766
3767 IF g_debug
3768 THEN
3769 -- For Bug 5941475
3770 IF NOT p_ptd_balance
3771 THEN
3772 l_proc_step := 80;
3773 DEBUG(l_proc_name, l_proc_step);
3774 DEBUG('l_ni_cont_out_earn: ' || l_ni_cont_out_earn);
3775 DEBUG('l_total_ni_cont_out_earn: ' || l_total_ni_cont_out_earn);
3776 END IF;
3777
3778 END IF;
3779
3780 i := l_tab_ni_cont_out_bals.NEXT(i);
3781 END LOOP;
3782
3783 IF g_debug
3784 THEN
3785 -- For Bug 5941475
3786 IF NOT p_ptd_balance
3787 THEN
3788 l_proc_step := 90;
3789 DEBUG(l_proc_name, l_proc_step);
3790 DEBUG('l_total_ni_cont_out_earn: ' || l_total_ni_cont_out_earn);
3791 debug_exit(l_proc_name);
3792 END IF;
3793
3794 END IF;
3795
3796 RETURN l_total_ni_cont_out_earn;
3797 EXCEPTION
3798 WHEN OTHERS
3799 THEN
3800 clear_cache;
3801
3802 IF SQLCODE <> hr_utility.hr_error_number
3803 THEN
3804 debug_others(l_proc_name, l_proc_step);
3805
3806 IF g_debug
3807 THEN
3808 DEBUG('Leaving: ' || l_proc_name, -999);
3809 END IF;
3810
3811 fnd_message.raise_error;
3812 ELSE
3813 RAISE;
3814 END IF;
3815 END get_contracted_out_earnings;
3816
3817 -- This function returns the WPS contributions for an
3818 -- assignment
3819 -- ----------------------------------------------------------------------------
3820 -- |---------------------< get_WPS_contributions >----------------------------|
3821 -- ----------------------------------------------------------------------------
3822 FUNCTION get_wps_contributions(p_assignment_id IN NUMBER
3823 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE) -- For Bug 5941475
3824 RETURN NUMBER
3825 IS
3826 --
3827 l_proc_name VARCHAR2(80)
3828 := g_proc_name || 'get_wps_contributions';
3829 l_proc_step PLS_INTEGER;
3830 l_wps_contribution NUMBER;
3831 l_total_wps_contribution NUMBER;
3832 l_effective_end_date DATE;
3833 l_defined_balance_id t_number;
3834 -- For 115.29
3835 l_pen_defined_balance_id t_number;
3836 i NUMBER;
3837 j NUMBER;
3838 l_return VARCHAR2(10);
3839 l_rec_ele_ent_details r_ele_ent_details;
3840 --
3841 BEGIN
3842 --
3843 IF g_debug
3844 THEN
3845 l_proc_step := 10;
3846 debug_enter(l_proc_name);
3847 DEBUG('p_assignment_id: ' || p_assignment_id);
3848 END IF;
3849
3850 -- WPS contributions has feeds from
3851 -- Classic OCP EES contribution balance and
3852 -- Classic Buy Back FWD contribution
3853
3854 l_wps_contribution := 0;
3855 l_total_wps_contribution := 0;
3856 l_effective_end_date := g_effective_end_date;
3857 --Reset to 0
3858 IF NOT p_ptd_balance
3859 THEN
3860 g_ayfwd_bal_conts := 0;
3861 END IF;
3862
3863 IF g_effective_date <> ben_ext_person.g_person_ext_dt
3864 THEN
3865 l_effective_end_date :=
3866 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
3867 END IF;
3868
3869 IF g_debug
3870 THEN
3871 DEBUG(
3872 'l_effective_end_date: '
3873 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
3874 );
3875 END IF;
3876
3877 -- Get the balance information for classic
3878 -- ocp ees contribution balance
3879
3880 j := g_tab_clas_pen_bal_dtls.FIRST;
3881
3882 WHILE j IS NOT NULL
3883 LOOP
3884 i := 0;
3885
3886 -- For 115.29
3887 IF NOT p_ptd_balance
3888 THEN -- get the YTD defined balance id
3889 IF g_tab_clas_pen_bal_dtls(j).ees_ytd_bal_id IS NOT NULL
3890 THEN
3891 i := i + 1;
3892 l_defined_balance_id(i) :=
3893 g_tab_clas_pen_bal_dtls(j).ees_ytd_bal_id;
3894 END IF;
3895 -- Get the balance information for classic
3896 -- ayr fwd contribution balance
3897 IF g_tab_clas_pen_bal_dtls(j).ayfwd_ytd_bal_id IS NOT NULL
3898 THEN
3899 i := i + 1;
3900 l_defined_balance_id(i) :=
3901 g_tab_clas_pen_bal_dtls(j).ayfwd_ytd_bal_id;
3902 END IF;
3903 ELSE -- get the PTD defined balance id
3904 IF g_tab_clas_pen_bal_dtls(j).ees_ptd_bal_id IS NOT NULL
3905 THEN
3906 i := i + 1;
3907 l_defined_balance_id(i) :=
3908 g_tab_clas_pen_bal_dtls(j).ees_ptd_bal_id;
3909 END IF;
3910 -- Get the balance information for classic
3911 -- ayr fwd contribution balance
3912 IF g_tab_clas_pen_bal_dtls(j).ayfwd_ptd_bal_id IS NOT NULL
3913 THEN
3914 i := i + 1;
3915 l_defined_balance_id(i) :=
3916 g_tab_clas_pen_bal_dtls(j).ayfwd_ptd_bal_id;
3917 END IF;
3918 END IF;
3919
3920 IF g_debug
3921 THEN
3922 l_proc_step := 20;
3923 DEBUG(l_proc_name, l_proc_step);
3924 END IF;
3925
3926 -- Check whether this person is enrolled into classic scheme
3927
3928 l_return :=
3929 get_ele_ent_details(
3930 p_assignment_id => p_assignment_id
3931 ,p_effective_start_date => g_effective_start_date
3932 ,p_effective_end_date => l_effective_end_date
3933 ,p_element_type_id => j
3934 ,p_rec_ele_ent_details => l_rec_ele_ent_details
3935 );
3936 i := l_defined_balance_id.FIRST;
3937
3938 WHILE i IS NOT NULL
3939 LOOP
3940 IF g_debug
3941 THEN
3942 l_proc_step := 30;
3943 DEBUG(l_proc_name, l_proc_step);
3944 DEBUG('i: ' || i);
3945 DEBUG('j: ' || j);
3946 DEBUG('l_defined_balance_id: ' || l_defined_balance_id(i));
3947 END IF;
3948
3949 l_wps_contribution := 0;
3950
3951 IF l_return = 'Y'
3952 THEN
3953 -- BEGIN
3954
3955 -- For Bug 5941475
3956 IF NOT p_ptd_balance
3957 THEN
3958 l_wps_contribution :=
3959 -- For 115.29
3960 pay_balance_pkg.get_value(
3961 p_defined_balance_id => l_defined_balance_id(i)
3962 ,p_assignment_id => p_assignment_id
3963 ,p_virtual_date => l_effective_end_date);
3964 ELSE
3965 l_wps_contribution :=
3966 pay_balance_pkg.get_value(
3967 p_defined_balance_id => l_defined_balance_id(i)
3968 ,p_assignment_id => p_assignment_id
3969 ,p_virtual_date => l_effective_end_date);
3970
3971 END IF; --End if Not p_ptd_balance
3972
3973 -- pay_balance_pkg.get_value(
3974 -- p_defined_balance_id => l_defined_balance_id(i)
3975 -- ,p_assignment_id => p_assignment_id
3976 -- ,p_virtual_date => l_effective_end_date);
3977 -- EXCEPTION
3978 -- WHEN NO_DATA_FOUND
3979 -- THEN
3980 -- IF g_debug
3981 -- THEN
3982 -- DEBUG('No data found exception: ');
3983 -- END IF;
3984 --
3985 -- l_wps_contribution := 0;
3986 -- END;
3987 -- Commented the below codes as not used
3988 /* IF l_defined_balance_id(i) =
3989 g_tab_clas_pen_bal_dtls(j).ayfwd_ptd_bal_id
3990 THEN
3991 -- For Bug 5941475
3992 IF NOT p_ptd_balance
3993 THEN
3994 g_ayfwd_bal_conts :=
3995 nvl(g_ayfwd_bal_conts,0)
3996 + l_wps_contribution;
3997 END IF;
3998
3999 END IF; */
4000
4001 IF g_debug
4002 THEN
4003 l_proc_step := 40;
4004 DEBUG(l_proc_name, l_proc_step);
4005 DEBUG('l_wps_contribution: ' || l_wps_contribution);
4006 END IF;
4007 END IF; -- End if of l_return = 'Y' check ...
4008
4009 l_total_wps_contribution :=
4010 l_total_wps_contribution
4011 + l_wps_contribution;
4012 i := l_defined_balance_id.NEXT(i);
4013 END LOOP; -- collection loop
4014
4015 j := g_tab_clas_pen_bal_dtls.NEXT(j);
4016 END LOOP; -- Balance collection loop
4017
4018 IF g_debug
4019 THEN
4020 -- For Bug 5941475
4021 IF NOT p_ptd_balance
4022 THEN
4023 l_proc_step := 50;
4024 DEBUG(l_proc_name, l_proc_step);
4025 DEBUG('l_total_wps_contribution: ' || l_total_wps_contribution);
4026 debug_exit(l_proc_name);
4027
4028 END IF;
4029 END IF;
4030
4031 RETURN l_total_wps_contribution;
4032 EXCEPTION
4033 WHEN OTHERS
4034 THEN
4035 clear_cache;
4036
4037 IF SQLCODE <> hr_utility.hr_error_number
4038 THEN
4039 debug_others(l_proc_name, l_proc_step);
4040
4041 IF g_debug
4042 THEN
4043 DEBUG('Leaving: ' || l_proc_name, -999);
4044 END IF;
4045
4046 fnd_message.raise_error;
4047 ELSE
4048 RAISE;
4049 END IF;
4050 END get_wps_contributions;
4051 -- BEGIN For BUG 6082532
4052 -- This function returns the Added Years contributions
4053 -- for an assignment as of the effective date
4054 -- ----------------------------------------------------------------------------
4055 -- |---------------------< get_added_years_conts >-------------------------|
4056 -- ----------------------------------------------------------------------------
4057
4058 /*FUNCTION get_added_years_conts(p_assignment_id IN NUMBER
4059 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE) -- For Bug 5941475
4060 RETURN NUMBER
4061 IS
4062 --
4063 l_proc_name VARCHAR2(80)
4064 := g_proc_name || 'get_added_years_conts';
4065 l_proc_step PLS_INTEGER;
4066 l_add_yrs_contributions NUMBER;
4067 l_total_add_yrs_contributions NUMBER;
4068 l_effective_end_date DATE;
4069 l_defined_balance_id t_number;
4070 i NUMBER;
4071 j NUMBER;
4072 l_return VARCHAR2(10);
4073 l_rec_ele_ent_details r_ele_ent_details;
4074 --
4075 BEGIN
4076 --
4077 IF g_debug
4078 THEN
4079 l_proc_step := 10;
4080 debug_enter(l_proc_name);
4081 DEBUG('p_assignment_id: ' || p_assignment_id);
4082 END IF;
4083
4084 -- Added Yeras contributions has feeds from
4085 -- Classic Added Years contribution balance and
4086 -- Classic Added Years Family benefit contribution
4087
4088 l_add_yrs_contributions := 0;
4089 l_total_add_yrs_contributions := 0;
4090 l_effective_end_date := g_effective_end_date;
4091
4092
4093 IF g_effective_date <> ben_ext_person.g_person_ext_dt
4094 THEN
4095 l_effective_end_date :=
4096 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
4097 END IF;
4098
4099 IF g_debug
4100 THEN
4101 DEBUG(
4102 'l_effective_end_date: '
4103 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
4104 );
4105 END IF;
4106
4107 -- Get the balance information for classic
4108 -- Added yeras and classic Added Years Family Benefit contribution balance
4109
4110 j := g_tab_clas_pen_bal_dtls.FIRST;
4111
4112 WHILE j IS NOT NULL
4113 LOOP
4114 i := 0;
4115
4116 IF g_tab_clas_pen_bal_dtls(j).ayr_ptd_bal_id IS NOT NULL
4117 THEN
4118 i := i + 1;
4119 l_defined_balance_id(i) :=
4120 g_tab_clas_pen_bal_dtls(j).ayr_ptd_bal_id;
4121 END IF;
4122
4123 -- Get the balance information for classic
4124 -- ayr family benefit contribution balance
4125 IF g_tab_clas_pen_bal_dtls(j).ayfb_ptd_bal_id IS NOT NULL
4126 THEN
4127 i := i + 1;
4128 l_defined_balance_id(i) :=
4129 g_tab_clas_pen_bal_dtls(j).ayfb_ptd_bal_id;
4130 END IF;
4131
4132 IF g_debug
4133 THEN
4134 l_proc_step := 20;
4135 DEBUG(l_proc_name, l_proc_step);
4136 END IF;
4137
4138 -- Check whether this person is enrolled into classic scheme
4139
4140 l_return :=
4141 get_ele_ent_details(
4142 p_assignment_id => p_assignment_id
4143 ,p_effective_start_date => g_effective_start_date
4144 ,p_effective_end_date => l_effective_end_date
4145 ,p_element_type_id => j
4146 ,p_rec_ele_ent_details => l_rec_ele_ent_details
4147 );
4148 i := l_defined_balance_id.FIRST;
4149
4150 WHILE i IS NOT NULL
4151 LOOP
4152 IF g_debug
4153 THEN
4154 l_proc_step := 30;
4155 DEBUG(l_proc_name, l_proc_step);
4156 DEBUG('i: ' || i);
4157 DEBUG('j: ' || j);
4158 DEBUG('l_defined_balance_id: ' || l_defined_balance_id(i));
4159 END IF;
4160
4161 l_add_yrs_contributions := 0;
4162
4163 IF l_return = 'Y'
4164 THEN
4165 -- BEGIN
4166
4167 -- For Bug 5941475
4168 IF NOT p_ptd_balance
4169 THEN
4170 l_add_yrs_contributions :=
4171 get_total_ptd_bal_value(
4172 p_assignment_id => p_assignment_id
4173 ,p_defined_balance_id => l_defined_balance_id(i)
4174 ,p_effective_start_date => g_effective_start_date
4175 ,p_effective_end_date => l_effective_end_date
4176 );
4177 ELSE
4178 l_add_yrs_contributions :=
4179 pay_balance_pkg.get_value(
4180 p_defined_balance_id => l_defined_balance_id(i)
4181 ,p_assignment_id => p_assignment_id
4182 ,p_virtual_date => l_effective_end_date);
4183
4184 END IF; --End if Not p_ptd_balance
4185
4186 IF l_defined_balance_id(i) =
4187 g_tab_clas_pen_bal_dtls(j).ayfb_ptd_bal_id
4188 THEN
4189 -- For Bug 5941475
4190 IF NOT p_ptd_balance
4191 THEN
4192 g_ayfb_bal_conts :=
4193 nvl(g_ayfb_bal_conts,0)
4194 + l_add_yrs_contributions;
4195 END IF;
4196
4197 END IF;
4198
4199 IF g_debug
4200 THEN
4201 -- For Bug 5941475
4202 IF NOT p_ptd_balance
4203 THEN
4204 l_proc_step := 40;
4205 DEBUG(l_proc_name, l_proc_step);
4206 DEBUG('l_add_yrs_contributions: ' || l_add_yrs_contributions);
4207 DEBUG('g_ayfb_bal_conts: ' || g_ayfb_bal_conts);
4208
4209 END IF;
4210 END IF;
4211 END IF; -- End if of l_return = 'Y' check ...
4212
4213 l_total_add_yrs_contributions :=
4214 l_total_add_yrs_contributions
4215 + l_add_yrs_contributions;
4216 i := l_defined_balance_id.NEXT(i);
4217 END LOOP; -- collection loop
4218
4219 j := g_tab_clas_pen_bal_dtls.NEXT(j);
4220 END LOOP; -- Balance collection loop
4221
4222 IF g_debug
4223 THEN
4224 -- For Bug 5941475
4225 IF NOT p_ptd_balance
4226 THEN
4227 l_proc_step := 50;
4228 DEBUG(l_proc_name, l_proc_step);
4229 DEBUG('l_total_add_yrs_contributions: ' || l_total_add_yrs_contributions);
4230 debug_exit(l_proc_name);
4231
4232 END IF;
4233 END IF;
4234
4235 RETURN l_total_add_yrs_contributions;
4236 EXCEPTION
4237 WHEN OTHERS
4238 THEN
4239 clear_cache;
4240
4241 IF SQLCODE <> hr_utility.hr_error_number
4242 THEN
4243 debug_others(l_proc_name, l_proc_step);
4244
4245 IF g_debug
4246 THEN
4247 DEBUG('Leaving: ' || l_proc_name, -999);
4248 END IF;
4249
4250 fnd_message.raise_error;
4251 ELSE
4252 RAISE;
4253 END IF;
4254 END get_added_years_conts; */
4255
4256 -- This function returns the money purchase AVC contributions
4257 -- for an assignment as of the effective date
4258 -- ----------------------------------------------------------------------------
4259 -- |---------------------< get_moneypurchase_conts >-------------------------|
4260 -- ----------------------------------------------------------------------------
4261 FUNCTION get_moneypurchase_conts(p_assignment_id IN NUMBER
4262 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE) -- For Bug 5941475
4263 RETURN NUMBER
4264 IS
4265 --
4266 l_proc_name VARCHAR2(80)
4267 := g_proc_name || 'get_moneypurchase_conts';
4268 l_proc_step PLS_INTEGER;
4269 l_effective_end_date DATE;
4270 l_mp_contributions NUMBER;
4271 l_total_mp_contributions NUMBER;
4272 i NUMBER;
4273 l_defined_balance_id NUMBER;
4274 -- For 115.29
4275 l_pen_defined_balance_id NUMBER;
4276 l_return VARCHAR2(10);
4277 l_rec_ele_ent_details r_ele_ent_details;
4278 --
4279 BEGIN
4280 --
4281 IF g_debug
4282 THEN
4283 l_proc_step := 10;
4284 debug_enter(l_proc_name);
4285 DEBUG('p_assignment_id: ' || p_assignment_id);
4286 END IF;
4287
4288 l_effective_end_date := g_effective_end_date;
4289
4290 IF g_effective_date <> ben_ext_person.g_person_ext_dt
4291 THEN
4292 l_effective_end_date :=
4293 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
4294 END IF;
4295
4296 IF g_debug
4297 THEN
4298 DEBUG(
4299 'l_effective_end_date: '
4300 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
4301 );
4302 END IF;
4303
4304 l_mp_contributions := 0;
4305 l_total_mp_contributions := 0;
4306 -- Loop through the AVC balance collection
4307 -- fetch the balance value only if an avc element entry
4308 -- exists for this assignment
4309
4310 i := g_tab_avc_pen_bal_dtls.FIRST;
4311
4312 WHILE i IS NOT NULL
4313 LOOP
4314 -- For 115.29
4315 IF NOT p_ptd_balance
4316 THEN
4317 l_defined_balance_id :=
4318 g_tab_avc_pen_bal_dtls(i).pen_defined_balance_id;
4319 ELSE
4320 l_defined_balance_id :=
4321 g_tab_avc_pen_bal_dtls(i).defined_balance_id;
4322 END IF;
4323
4324 l_mp_contributions := 0;
4325
4326 IF g_debug
4327 THEN
4328 l_proc_step := 20;
4329 DEBUG(l_proc_name, l_proc_step);
4330 DEBUG('i: ' || i);
4331 DEBUG(
4332 'g_tab_avc_pen_bal_dtls(i).balance_name: '
4333 || g_tab_avc_pen_bal_dtls(i).balance_name
4334 );
4335 DEBUG('l_defined_balance_id ' || l_defined_balance_id);
4336 END IF;
4337
4338 -- Check whether this person is enrolled into this scheme
4339
4340 l_return :=
4341 get_ele_ent_details(
4342 p_assignment_id => p_assignment_id
4343 ,p_effective_start_date => g_effective_start_date
4344 ,p_effective_end_date => l_effective_end_date
4345 ,p_element_type_id => i
4346 ,p_rec_ele_ent_details => l_rec_ele_ent_details
4347 );
4348
4349 IF l_return = 'Y'
4350 THEN
4351 -- For Bug 5941475
4352 IF NOT p_ptd_balance
4353 THEN
4354 l_mp_contributions :=
4355 -- For 115.29
4356 pay_balance_pkg.get_value(
4357 p_defined_balance_id => l_defined_balance_id
4358 ,p_assignment_id => p_assignment_id
4359 ,p_virtual_date => l_effective_end_date);
4360 /* get_total_ptd_bal_value(
4361 p_assignment_id => p_assignment_id
4362 ,p_defined_balance_id => l_defined_balance_id
4363 ,p_effective_start_date => g_effective_start_date
4364 ,p_effective_end_date => l_effective_end_date
4365 ); */
4366
4367 ELSE
4368 l_mp_contributions :=
4369 pay_balance_pkg.get_value(
4370 p_defined_balance_id => l_defined_balance_id
4371 ,p_assignment_id => p_assignment_id
4372 ,p_virtual_date => l_effective_end_date);
4373
4374 END IF; --End if Not p_ptd_balance
4375 -- pay_balance_pkg.get_value(
4376 -- p_defined_balance_id => l_defined_balance_id
4377 -- ,p_assignment_id => p_assignment_id
4378 -- ,p_virtual_date => l_effective_end_date);
4379 l_total_mp_contributions :=
4380 l_total_mp_contributions
4381 + l_mp_contributions;
4382 END IF; -- End if of element entry exists check ...
4383
4384 IF g_debug
4385 THEN
4386 -- For Bug 5941475
4387 IF NOT p_ptd_balance
4388 THEN
4389 l_proc_step := 30;
4390 DEBUG(l_proc_name, l_proc_step);
4391 DEBUG('l_mp_contributions: ' || l_mp_contributions);
4392 DEBUG('l_total_mp_contributions: ' || l_total_mp_contributions);
4393 END IF;
4394
4395 END IF;
4396
4397 i := g_tab_avc_pen_bal_dtls.NEXT(i);
4398 END LOOP;
4399
4400 IF g_debug
4401 THEN
4402 -- For Bug 5941475
4403 IF NOT p_ptd_balance
4404 THEN
4405 l_proc_step := 40;
4406 DEBUG(l_proc_name, l_proc_step);
4407 DEBUG('l_total_mp_contributions: ' || l_total_mp_contributions);
4408 debug_exit(l_proc_name);
4409 END IF;
4410
4411 END IF;
4412
4413 RETURN l_total_mp_contributions;
4414 EXCEPTION
4415 WHEN OTHERS
4416 THEN
4417 clear_cache;
4418
4419 IF SQLCODE <> hr_utility.hr_error_number
4420 THEN
4421 debug_others(l_proc_name, l_proc_step);
4422
4423 IF g_debug
4424 THEN
4425 DEBUG('Leaving: ' || l_proc_name, -999);
4426 END IF;
4427
4428 fnd_message.raise_error;
4429 ELSE
4430 RAISE;
4431 END IF;
4432 END get_moneypurchase_conts;
4433
4434 -- This function returns the contribution amount
4435 -- for an assignment and collection as of the effective date
4436 -- ----------------------------------------------------------------------------
4437 -- |---------------------< get_contribution_amount >-------------------------|
4438 -- ----------------------------------------------------------------------------
4439 FUNCTION get_contribution_amount(
4440 p_assignment_id IN NUMBER
4441 ,p_tab_pen_bal_dtls IN t_pen_bal_dtls
4442 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE -- For Bug 5941475
4443 ,p_employer_only IN BOOLEAN DEFAULT FALSE
4444 )
4445 RETURN NUMBER
4446 IS
4447 --
4448 l_proc_name VARCHAR2(80)
4449 := g_proc_name || 'get_contribution_amount';
4450 l_proc_step PLS_INTEGER;
4451 l_effective_end_date DATE;
4452 l_contributions NUMBER;
4453 l_total_contributions NUMBER;
4454 i NUMBER;
4455 l_defined_balance_id NUMBER;
4456 l_return VARCHAR2(10);
4457 l_rec_ele_ent_details r_ele_ent_details;
4458 --
4459 BEGIN
4460 --
4461 IF g_debug
4462 THEN
4463 l_proc_step := 10;
4464 debug_enter(l_proc_name);
4465 DEBUG('p_assignment_id: ' || p_assignment_id);
4466 END IF;
4467
4468 l_effective_end_date := g_effective_end_date;
4469
4470 IF g_effective_date <> ben_ext_person.g_person_ext_dt
4471 THEN
4472 l_effective_end_date :=
4473 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
4474 END IF;
4475
4476 IF g_debug
4477 THEN
4478 DEBUG(
4479 'l_effective_end_date: '
4480 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
4481 );
4482 END IF;
4483
4484 l_contributions := 0;
4485 l_total_contributions := 0;
4486 -- Loop through the balance collection
4487 -- fetch the balance value only if an element entry
4488 -- exists for this assignment and element type
4489
4490 i := p_tab_pen_bal_dtls.FIRST;
4491
4492 WHILE i IS NOT NULL
4493 LOOP
4494 IF NOT p_employer_only
4495 THEN
4496 -- For 115.29
4497 IF NOT p_ptd_balance
4498 THEN
4499 l_defined_balance_id := p_tab_pen_bal_dtls(i).ees_ytd_bal_id;
4500 ELSE
4501 l_defined_balance_id := p_tab_pen_bal_dtls(i).ees_ptd_bal_id;
4502 END IF;
4503 ELSE
4504 IF NOT p_ptd_balance
4505 THEN
4506 l_defined_balance_id := p_tab_pen_bal_dtls(i).ers_ytd_bal_id;
4507 ELSE
4508 l_defined_balance_id := p_tab_pen_bal_dtls(i).ers_ptd_bal_id;
4509 END IF;
4510 END IF;
4511
4512 l_contributions := 0;
4513
4514 IF g_debug
4515 THEN
4516 l_proc_step := 20;
4517 DEBUG(l_proc_name, l_proc_step);
4518 DEBUG('i: ' || i);
4519 DEBUG(
4520 'p_tab_pen_bal_dtls(i).ees_balance_name: '
4521 || p_tab_pen_bal_dtls(i).ees_balance_name
4522 );
4523 DEBUG(
4524 'p_tab_pen_bal_dtls(i).ers_balance_name: '
4525 || p_tab_pen_bal_dtls(i).ers_balance_name
4526 );
4527 DEBUG('l_defined_balance_id ' || l_defined_balance_id);
4528 END IF;
4529
4530 -- Check whether this person is enrolled into this scheme
4531
4532 l_return :=
4533 get_ele_ent_details(
4534 p_assignment_id => p_assignment_id
4535 ,p_effective_start_date => g_effective_start_date
4536 ,p_effective_end_date => l_effective_end_date
4537 ,p_element_type_id => i
4538 ,p_rec_ele_ent_details => l_rec_ele_ent_details
4539 );
4540
4541 IF l_return = 'Y'
4542 THEN
4543 -- For Bug 5941475
4544 IF NOT p_ptd_balance
4545 THEN
4546 l_contributions :=
4547 -- For 115.29
4548 pay_balance_pkg.get_value(
4549 p_defined_balance_id => l_defined_balance_id
4550 ,p_assignment_id => p_assignment_id
4551 ,p_virtual_date => l_effective_end_date);
4552
4553 /* get_total_ptd_bal_value(
4554 p_assignment_id => p_assignment_id
4555 ,p_defined_balance_id => l_defined_balance_id
4556 ,p_effective_start_date => g_effective_start_date
4557 ,p_effective_end_date => l_effective_end_date
4558 ); */
4559 ELSE
4560 l_contributions :=
4561 pay_balance_pkg.get_value(
4562 p_defined_balance_id => l_defined_balance_id
4563 ,p_assignment_id => p_assignment_id
4564 ,p_virtual_date => l_effective_end_date);
4565
4566 END IF; --End if Not p_ptd_balance
4567
4568 -- pay_balance_pkg.get_value(
4569 -- p_defined_balance_id => l_defined_balance_id
4570 -- ,p_assignment_id => p_assignment_id
4571 -- ,p_virtual_date => l_effective_end_date);
4572 l_total_contributions :=
4573 l_total_contributions
4574 + l_contributions;
4575 END IF; -- End if of element entry exists check ...
4576
4577 IF g_debug
4578 THEN
4579 -- For Bug 5941475
4580 IF NOT p_ptd_balance
4581 THEN
4582 l_proc_step := 30;
4583 DEBUG(l_proc_name, l_proc_step);
4584 DEBUG('l_contributions: ' || l_contributions);
4585 DEBUG('l_total_contributions: ' || l_total_contributions);
4586 END IF;
4587
4588 END IF;
4589
4590 i := p_tab_pen_bal_dtls.NEXT(i);
4591 END LOOP;
4592
4593 IF g_debug
4594 THEN
4595 -- For Bug 5941475
4596 IF NOT p_ptd_balance
4597 THEN
4598 l_proc_step := 40;
4599 DEBUG(l_proc_name, l_proc_step);
4600 DEBUG('l_total_contributions: ' || l_total_contributions);
4601 debug_exit(l_proc_name);
4602 END IF;
4603 END IF;
4604
4605 RETURN l_total_contributions;
4606 EXCEPTION
4607 WHEN OTHERS
4608 THEN
4609 clear_cache;
4610
4611 IF SQLCODE <> hr_utility.hr_error_number
4612 THEN
4613 debug_others(l_proc_name, l_proc_step);
4614
4615 IF g_debug
4616 THEN
4617 DEBUG('Leaving: ' || l_proc_name, -999);
4618 END IF;
4619
4620 fnd_message.raise_error;
4621 ELSE
4622 RAISE;
4623 END IF;
4624 END get_contribution_amount;
4625
4626 /* For Nuvos */
4627 -- This function returns the Nuvos contribution amount
4628 -- for an assignment and collection as of the effective date
4629 -- ----------------------------------------------------------------------------
4630 -- |---------------------< get_nuvos_contribution_amount >-------------------------|
4631 -- ----------------------------------------------------------------------------
4632 FUNCTION get_nuvos_contribution_amount(
4633 p_assignment_id IN NUMBER
4634 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE ) -- For Bug 5941475
4635
4636 RETURN NUMBER
4637 IS
4638 --
4639 l_proc_name VARCHAR2(80)
4640 := g_proc_name || 'get_nuvos_contribution_amount';
4641 l_proc_step PLS_INTEGER;
4642 l_effective_end_date DATE;
4643 l_contributions NUMBER;
4644 l_total_contributions NUMBER;
4645 i NUMBER;
4646 l_defined_balance_id NUMBER;
4647 l_return VARCHAR2(10);
4648 l_rec_ele_ent_details r_ele_ent_details;
4649 --
4650 BEGIN
4651 --
4652 IF g_debug
4653 THEN
4654 l_proc_step := 10;
4655 debug_enter(l_proc_name);
4656 DEBUG('p_assignment_id: ' || p_assignment_id);
4657 END IF;
4658
4659 l_effective_end_date := g_effective_end_date;
4660
4661 IF g_effective_date <> ben_ext_person.g_person_ext_dt
4662 THEN
4663 l_effective_end_date :=
4664 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
4665 END IF;
4666
4667 IF g_debug
4668 THEN
4669 DEBUG(
4670 'l_effective_end_date: '
4671 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
4672 );
4673 END IF;
4674
4675 l_contributions := 0;
4676 l_total_contributions := 0;
4677 -- Loop through the balance collection
4678 -- fetch the balance value only if an element entry
4679 -- exists for this assignment and element type
4680
4681 i := g_tab_nuvos_pen_bal_dtls.FIRST;
4682
4683 WHILE i IS NOT NULL
4684 LOOP
4685 IF NOT p_ptd_balance -- For bug 5941475
4686 THEN
4687 l_defined_balance_id := g_tab_nuvos_pen_bal_dtls(i).nuvos_sa_ytd_bal_id;
4688 ELSE
4689 l_defined_balance_id := g_tab_nuvos_pen_bal_dtls(i).nuvos_sa_ptd_bal_id;
4690 END IF;
4691
4692 l_contributions := 0;
4693
4694 IF g_debug
4695 THEN
4696 l_proc_step := 20;
4697 DEBUG(l_proc_name, l_proc_step);
4698 DEBUG('i: ' || i);
4699 DEBUG(
4700 'g_tab_nuvos_pen_bal_dtls(i).nuvos_sa_balance_name: '
4701 || g_tab_nuvos_pen_bal_dtls(i).nuvos_sa_balance_name
4702 );
4703 DEBUG(
4704 'g_tab_nuvos_pen_bal_dtls(i).nuvos_sa_balance_name: '
4705 || g_tab_nuvos_pen_bal_dtls(i).nuvos_sa_balance_name
4706 );
4707 DEBUG('l_defined_balance_id ' || l_defined_balance_id);
4708 END IF;
4709
4710 -- Check whether this person is enrolled into this scheme
4711
4712 l_return :=
4713 get_ele_ent_details(
4714 p_assignment_id => p_assignment_id
4715 ,p_effective_start_date => g_effective_start_date
4716 ,p_effective_end_date => l_effective_end_date
4717 ,p_element_type_id => i
4718 ,p_rec_ele_ent_details => l_rec_ele_ent_details
4719 );
4720
4721 IF l_return = 'Y'
4722 THEN
4723 IF NOT p_ptd_balance -- For bug 5941475
4724 THEN
4725 l_contributions :=
4726 -- For 115.29
4727 pay_balance_pkg.get_value(
4728 p_defined_balance_id => l_defined_balance_id
4729 ,p_assignment_id => p_assignment_id
4730 ,p_virtual_date => l_effective_end_date);
4731
4732 /* get_total_ptd_bal_value(
4733 p_assignment_id => p_assignment_id
4734 ,p_defined_balance_id => l_defined_balance_id
4735 ,p_effective_start_date => g_effective_start_date
4736 ,p_effective_end_date => l_effective_end_date
4737 ); */
4738 ELSE
4739 l_contributions :=
4740 pay_balance_pkg.get_value(
4741 p_defined_balance_id => l_defined_balance_id
4742 ,p_assignment_id => p_assignment_id
4743 ,p_virtual_date => l_effective_end_date);
4744
4745 END IF; --End if Not p_ptd_balance
4746
4747 l_total_contributions :=
4748 l_total_contributions
4749 + l_contributions;
4750 EXIT;
4751
4752 END IF; -- End if of element entry exists check ...
4753
4754 IF g_debug
4755 THEN
4756 l_proc_step := 30;
4757 DEBUG(l_proc_name, l_proc_step);
4758 DEBUG('l_contributions: ' || l_contributions);
4759 DEBUG('l_total_contributions: ' || l_total_contributions);
4760 END IF;
4761
4762 i := g_tab_nuvos_pen_bal_dtls.NEXT(i);
4763 END LOOP;
4764
4765 IF g_debug
4766 THEN
4767 l_proc_step := 40;
4768 DEBUG(l_proc_name, l_proc_step);
4769 DEBUG('l_total_contributions: ' || l_total_contributions);
4770 debug_exit(l_proc_name);
4771 END IF;
4772
4773 RETURN l_total_contributions;
4774 EXCEPTION
4775 WHEN OTHERS
4776 THEN
4777 clear_cache;
4778
4779 IF SQLCODE <> hr_utility.hr_error_number
4780 THEN
4781 debug_others(l_proc_name, l_proc_step);
4782
4783 IF g_debug
4784 THEN
4785 DEBUG('Leaving: ' || l_proc_name, -999);
4786 END IF;
4787
4788 fnd_message.raise_error;
4789 ELSE
4790 RAISE;
4791 END IF;
4792 END get_nuvos_contribution_amount;
4793 /*For Nuvos END*/
4794
4795 -- This function returns the contracted out E earnings figure
4796 -- for a given assignment as of an effective date
4797 -- ----------------------------------------------------------------------------
4798 -- |---------------------< get_contracted_out_E_earnings >---------------------|
4799 -- ----------------------------------------------------------------------------
4800 FUNCTION get_contracted_out_e_earnings(p_assignment_id IN NUMBER
4801 ,p_ptd_balance IN BOOLEAN DEFAULT FALSE) -- For Bug 5941475
4802 RETURN VARCHAR2
4803 IS
4804 --
4805 l_proc_name VARCHAR2(80)
4806 := g_proc_name || 'get_contracted_out_E_earnings';
4807 l_proc_step PLS_INTEGER;
4808 l_effective_end_date DATE;
4809 l_ni_e_earnings NUMBER;
4810 l_total_ni_e_earnings NUMBER;
4811 i NUMBER;
4812 l_ni_e_def_bal_id t_number;
4813 l_defined_balance_id NUMBER;
4814 --
4815 BEGIN
4816 --
4817 IF g_debug
4818 THEN
4819 l_proc_step := 10;
4820 debug_enter(l_proc_name);
4821 DEBUG('p_assignment_id: ' || p_assignment_id);
4822 END IF;
4823
4824 l_effective_end_date := g_effective_end_date;
4825
4826 IF g_effective_date <> ben_ext_person.g_person_ext_dt
4827 THEN
4828 l_effective_end_date :=
4829 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
4830 END IF;
4831
4832 IF g_debug
4833 THEN
4834 DEBUG(
4835 'l_effective_end_date: '
4836 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
4837 );
4838 END IF;
4839
4840 l_ni_e_earnings := 0;
4841 l_total_ni_e_earnings := 0;
4842
4843 -- Only proceed with there is a NI E category
4844 -- for this assignment within the extract period
4845
4846 IF g_ni_e_cat_exists = 'Y'
4847 THEN
4848 i := 0;
4849
4850 IF g_ni_euel_ptd_bal_id IS NOT NULL
4851 THEN
4852 i := i + 1;
4853 l_ni_e_def_bal_id(i) := g_ni_euel_ptd_bal_id;
4854 END IF;
4855
4856 IF g_ni_eet_ptd_bal_id IS NOT NULL
4857 THEN
4858 i := i + 1;
4859 l_ni_e_def_bal_id(i) := g_ni_eet_ptd_bal_id;
4860 END IF;
4861 END IF; -- End if of NI E category exists check ...
4862
4863 i := l_ni_e_def_bal_id.FIRST;
4864
4865 WHILE i IS NOT NULL
4866 LOOP
4867 l_defined_balance_id := l_ni_e_def_bal_id(i);
4868
4869 IF g_debug
4870 THEN
4871 l_proc_step := 20;
4872 DEBUG(l_proc_name, l_proc_step);
4873 DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
4874 END IF;
4875
4876 l_ni_e_earnings := 0;
4877 -- BEGIN
4878
4879 -- For Bug 5941475
4880 IF NOT p_ptd_balance
4881 THEN
4882 l_ni_e_earnings :=
4883 get_total_ptd_bal_value(
4884 p_assignment_id => p_assignment_id
4885 ,p_defined_balance_id => l_defined_balance_id
4886 ,p_effective_start_date => g_effective_start_date
4887 ,p_effective_end_date => l_effective_end_date
4888 );
4889 ELSE
4890 l_ni_e_earnings :=
4891 pay_balance_pkg.get_value(
4892 p_defined_balance_id => l_defined_balance_id
4893 ,p_assignment_id => p_assignment_id
4894 ,p_virtual_date => l_effective_end_date);
4895
4896 END IF; --End if Not p_ptd_balance
4897 -- pay_balance_pkg.get_value(
4898 -- p_defined_balance_id => l_defined_balance_id
4899 -- ,p_assignment_id => p_assignment_id
4900 -- ,p_virtual_date => l_effective_end_date);
4901 -- EXCEPTION
4902 -- WHEN NO_DATA_FOUND
4903 -- THEN
4904 -- IF g_debug
4905 -- THEN
4906 -- DEBUG('No data found exception: ');
4907 -- END IF;
4908 --
4909 -- l_ni_e_earnings := 0;
4910 -- END;
4911
4912 l_total_ni_e_earnings := l_total_ni_e_earnings + l_ni_e_earnings;
4913
4914 IF g_debug
4915 THEN
4916 -- For Bug 5941475
4917 IF NOT p_ptd_balance
4918 THEN
4919 l_proc_step := 30;
4920 DEBUG(l_proc_name, l_proc_step);
4921 DEBUG('l_ni_e_earnings: ' || l_ni_e_earnings);
4922 DEBUG('l_total_ni_e_earnings: ' || l_total_ni_e_earnings);
4923 END IF;
4924
4925 END IF;
4926
4927 i := l_ni_e_def_bal_id.NEXT(i);
4928 END LOOP;
4929
4930 IF g_debug
4931 THEN
4932 -- For Bug 5941475
4933 IF NOT p_ptd_balance
4934 THEN
4935 l_proc_step := 40;
4936 DEBUG(l_proc_name, l_proc_step);
4937 DEBUG('l_total_ni_e_earnings: ' || l_total_ni_e_earnings);
4938 debug_exit(l_proc_name);
4939 END IF;
4940
4941 END IF;
4942
4943 RETURN l_total_ni_e_earnings;
4944 EXCEPTION
4945 WHEN OTHERS
4946 THEN
4947 clear_cache;
4948
4949 IF SQLCODE <> hr_utility.hr_error_number
4950 THEN
4951 debug_others(l_proc_name, l_proc_step);
4952
4953 IF g_debug
4954 THEN
4955 DEBUG('Leaving: ' || l_proc_name, -999);
4956 END IF;
4957
4958 fnd_message.raise_error;
4959 ELSE
4960 RAISE;
4961 END IF;
4962 END get_contracted_out_e_earnings;
4963
4964 -- This function is used to get earnings history data
4965 -- for an assignment
4966 -- ----------------------------------------------------------------------------
4967 -- |---------------------< get_earnings_history_data >-------------------------|
4968 -- ----------------------------------------------------------------------------
4969 FUNCTION get_earnings_history_data(
4970 p_business_group_id IN NUMBER
4971 ,p_effective_date IN DATE
4972 ,p_assignment_id IN NUMBER
4973 ,p_rule_parameter IN VARCHAR2
4974 )
4975 RETURN VARCHAR2
4976 IS
4977 --
4978
4979 -- Cursor to get actual termination date
4980 CURSOR csr_get_actual_term_dt(c_effective_date DATE)
4981 IS
4982 SELECT actual_termination_date
4983 FROM per_periods_of_service pps
4984 WHERE pps.person_id = g_person_dtl.person_id
4985 AND pps.date_start =
4986 (SELECT MAX(pps1.date_start) -- this gets most recent
4987 FROM per_periods_of_service pps1
4988 WHERE pps1.person_id = g_person_dtl.person_id
4989 AND pps1.date_start <= c_effective_date)
4990 AND pps.actual_termination_date <=
4991 last_day(add_months(g_effective_end_date, -1)); -- Bug: 6801704
4992
4993 l_proc_name VARCHAR2(80)
4994 := g_proc_name || 'get_earnings_history_data';
4995 l_proc_step PLS_INTEGER;
4996 l_return_value VARCHAR2(150);
4997 l_earnings NUMBER;
4998 l_effective_end_date DATE;
4999 l_defined_balance_id NUMBER;
5000 -- For 115.29
5001 l_pen_defined_balance_id NUMBER;
5002 l_field_name VARCHAR2(240);
5003 l_value NUMBER;
5004 l_actual_term_date DATE;
5005
5006 -- For Bug 5941475
5007 l_current_earnings NUMBER;
5008 l_check_current_balance VARCHAR2(10) := 'N';
5009
5010 --
5011 BEGIN
5012 --
5013 IF g_debug
5014 THEN
5015 l_proc_step := 10;
5016 debug_enter(l_proc_name);
5017 DEBUG('p_business_group_id: ' || p_business_group_id);
5018 DEBUG('p_effective_date: '
5019 || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
5020 DEBUG('p_assignment_id: ' || p_assignment_id);
5021 DEBUG('p_rule_parameter: ' || p_rule_parameter);
5022 END IF;
5023
5024 l_effective_end_date := g_effective_end_date;
5025
5026 IF g_effective_date <> ben_ext_person.g_person_ext_dt
5027 THEN
5028 l_effective_end_date :=
5029 LEAST(g_effective_end_date, ben_ext_person.g_person_ext_dt);
5030 END IF;
5031
5032 IF g_debug
5033 THEN
5034 DEBUG(
5035 'l_effective_end_date: '
5036 || TO_CHAR(l_effective_end_date, 'DD/MON/YYYY')
5037 );
5038 END IF;
5039
5040 l_earnings := 0;
5041
5042 -- For Bug 5941475
5043 l_current_earnings := 0;
5044
5045 OPEN csr_get_actual_term_dt(l_effective_end_date);
5046 FETCH csr_get_actual_term_dt INTO l_actual_term_date;
5047 CLOSE csr_get_actual_term_dt;
5048
5049 IF l_actual_term_date IS NOT NULL
5050 THEN
5051 l_check_current_balance := 'Y';
5052 END IF;
5053
5054 -- Call local functions based on rule_parameter value
5055 IF g_debug
5056 THEN
5057 l_proc_step := 20;
5058 DEBUG(l_proc_name, l_proc_step);
5059 END IF;
5060
5061 IF p_rule_parameter = 'StartDate'
5062 THEN
5063 l_return_value :=
5064 fnd_date.date_to_canonical(g_effective_start_date);
5065 ELSIF p_rule_parameter = 'EndDate'
5066 THEN
5067
5068 /*
5069 OPEN csr_get_actual_term_dt(l_effective_end_date);
5070 FETCH csr_get_actual_term_dt INTO l_actual_term_date;
5071 CLOSE csr_get_actual_term_dt;
5072
5073 IF l_actual_term_date IS NULL
5074 THEN
5075 l_actual_term_date := l_effective_end_date;
5076 END IF;
5077
5078 l_return_value := fnd_date.date_to_canonical(l_actual_term_date);
5079 */
5080
5081 -- bugfix : 5948932
5082 -- The above code was for reporting the actual termination date as the end date.
5083 -- From this version (115.16) onwards, reverting back to reporting the end date
5084 -- with the value of the period end date.
5085 l_return_value := fnd_date.date_to_canonical(g_effective_end_date);
5086
5087 ELSIF p_rule_parameter = 'NICategory'
5088 THEN
5089 l_return_value :=
5090 get_ni_category(p_assignment_id => p_assignment_id);
5091 l_return_value := RPAD(NVL(l_return_value, ' '), 1, ' ');
5092 ELSIF p_rule_parameter = 'ContractedOut'
5093 THEN
5094 l_field_name := 'Contracted Out Earnings';
5095 l_earnings :=
5096 get_contracted_out_earnings(p_assignment_id => p_assignment_id);
5097
5098 -- For Bug 5941475
5099 IF l_check_current_balance = 'Y'
5100 THEN
5101 l_current_earnings :=
5102 get_contracted_out_earnings(p_assignment_id => p_assignment_id,
5103 p_ptd_balance => TRUE);
5104
5105 IF l_current_earnings <> 0
5106 THEN
5107 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5108 END IF;
5109
5110 END IF; --l_check_current_balance = 'Y'
5111
5112 IF l_earnings > 0
5113 THEN
5114 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5115 ELSIF l_earnings < 0
5116 THEN
5117 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5118 ELSE
5119 l_return_value := RPAD(' ', 12, ' ');
5120 END IF;
5121 ELSIF p_rule_parameter = 'WPSContributions'
5122 THEN
5123 l_field_name := 'Classic Scheme WPS Contributions';
5124 g_ayfwd_bal_conts := 0;
5125
5126 IF g_member = 'Y'
5127 THEN
5128 l_earnings :=
5129 get_wps_contributions(p_assignment_id => p_assignment_id);
5130
5131 -- For Bug 5941475
5132 IF l_check_current_balance = 'Y'
5133 THEN
5134 l_current_earnings :=
5135 get_wps_contributions(p_assignment_id => p_assignment_id,
5136 p_ptd_balance => TRUE);
5137
5138 IF l_current_earnings <> 0
5139 THEN
5140 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5141 END IF;
5142
5143 END IF; --End if l_check_current_balance = 'Y'
5144
5145 END IF; -- End if of is member check ...
5146
5147 IF l_earnings > 0
5148 THEN
5149 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5150 ELSIF l_earnings < 0
5151 THEN
5152 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5153 ELSE
5154 l_return_value := RPAD(' ', 12, ' ');
5155 END IF;
5156 ELSIF p_rule_parameter = 'AddedYearsContributions'
5157 THEN
5158 l_field_name := 'Added Years Contributions';
5159 g_ayfb_bal_conts := 0;
5160
5161 l_defined_balance_id := g_tot_ayr_ptd_bal_id;
5162 -- For 115.29
5163 l_pen_defined_balance_id := g_tot_ayr_ytd_bal_id;
5164
5165 IF g_debug
5166 THEN
5167 DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5168 END IF;
5169
5170 IF l_defined_balance_id IS NOT NULL
5171 -- For 115.29
5172 AND l_pen_defined_balance_id is NOT NULL
5173 AND g_member = 'Y'
5174 THEN
5175 -- BEGIN
5176 l_earnings :=
5177 -- For 115.29
5178 pay_balance_pkg.get_value(
5179 p_defined_balance_id => l_pen_defined_balance_id
5180 ,p_assignment_id => p_assignment_id
5181 ,p_virtual_date => l_effective_end_date);
5182
5183 /* get_total_ptd_bal_value(
5184 p_assignment_id => p_assignment_id
5185 ,p_defined_balance_id => l_defined_balance_id
5186 ,p_effective_start_date => g_effective_start_date
5187 ,p_effective_end_date => l_effective_end_date
5188 ); */
5189
5190 -- For Bug 5941475
5191 IF l_check_current_balance = 'Y'
5192 THEN
5193 l_current_earnings :=
5194 pay_balance_pkg.get_value(
5195 p_defined_balance_id => l_defined_balance_id
5196 ,p_assignment_id => p_assignment_id
5197 ,p_virtual_date => l_effective_end_date);
5198
5199 IF l_current_earnings <> 0
5200 THEN
5201 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5202 END IF;
5203
5204 END IF; --End if l_check_current_balance = 'Y'
5205
5206 -- pay_balance_pkg.get_value(
5207 -- p_defined_balance_id => l_defined_balance_id
5208 -- ,p_assignment_id => p_assignment_id
5209 -- ,p_virtual_date => l_effective_end_date);
5210 -- EXCEPTION
5211 -- WHEN NO_DATA_FOUND
5212 -- THEN
5213 -- IF g_debug
5214 -- THEN
5215 -- DEBUG('No data found exception: ');
5216 -- END IF;
5217 --
5218 -- l_earnings := 0;
5219 -- END;
5220 END IF; -- End if of defined balance id is not null check ..
5221 /* -- BEGIN For BUG 6082532
5222 IF g_member = 'Y'
5223 THEN
5224 l_earnings :=
5225 get_added_years_conts(p_assignment_id => p_assignment_id);
5226
5227 -- For Bug 5941475
5228 IF l_check_current_balance = 'Y'
5229 THEN
5230 l_current_earnings :=
5231 get_added_years_conts(p_assignment_id => p_assignment_id,
5232 p_ptd_balance => TRUE);
5233
5234 IF l_current_earnings <> 0
5235 THEN
5236 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5237 END IF;
5238
5239 END IF; --End if l_check_current_balance = 'Y'
5240
5241 END IF; -- End if of is member check ...*/
5242 -- END For BUG 6082532
5243
5244 IF l_earnings > 0
5245 THEN
5246 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5247 ELSIF l_earnings < 0
5248 THEN
5249 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5250 ELSE
5251 l_return_value := RPAD(' ', 12, ' ');
5252 END IF;
5253 ELSIF p_rule_parameter = 'MoneyPurchase'
5254 THEN
5255 l_field_name := 'Money Purchase Contributions';
5256
5257 IF g_member = 'Y'
5258 THEN
5259 l_earnings :=
5260 get_moneypurchase_conts(p_assignment_id => p_assignment_id);
5261
5262 -- For Bug 5941475
5263 IF l_check_current_balance = 'Y'
5264 THEN
5265 l_current_earnings :=
5266 get_moneypurchase_conts(p_assignment_id => p_assignment_id
5267 ,p_ptd_balance => TRUE);
5268
5269 IF l_current_earnings <> 0
5270 THEN
5271 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5272 END IF;
5273
5274 END IF; --End if l_check_current_balance = 'Y'
5275
5276 END IF; -- End if of is member check ...
5277
5278 IF l_earnings > 0
5279 THEN
5280 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5281 ELSIF l_earnings < 0
5282 THEN
5283 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5284 ELSE
5285 l_return_value := RPAD(' ', 12, ' ');
5286 END IF;
5287 ELSIF p_rule_parameter = 'ContractedOutE'
5288 THEN
5289 l_field_name := 'Contracted Out E Earnings';
5290 l_earnings :=
5291 get_contracted_out_e_earnings(p_assignment_id => p_assignment_id);
5292
5293 -- For Bug 5941475
5294 IF l_check_current_balance = 'Y'
5295 THEN
5296 l_current_earnings :=
5297 get_contracted_out_e_earnings(p_assignment_id => p_assignment_id,
5298 p_ptd_balance => TRUE);
5299
5300 IF l_current_earnings <> 0
5301 THEN
5302 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5303 END IF;
5304
5305 END IF; --l_check_current_balance = 'Y'
5306
5307 IF l_earnings > 0
5308 THEN
5309 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5310 ELSIF l_earnings < 0
5311 THEN
5312 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5313 ELSE
5314 l_return_value := RPAD(' ', 12, ' ');
5315 END IF;
5316 ELSIF p_rule_parameter = 'AddedYearsFamilyBenefit'
5317 THEN
5318 l_field_name := 'Added Years Family Benefit Contributions';
5319
5320 l_defined_balance_id := g_tot_ayr_fb_ptd_bal_id;
5321 -- For 115.29
5322 l_pen_defined_balance_id := g_tot_ayr_fb_ytd_bal_id;
5323
5324 IF g_debug
5325 THEN
5326 DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5327 -- For 115.29
5328 DEBUG('l_pen_defined_balance_id: ' || l_defined_balance_id);
5329 END IF;
5330
5331 IF l_defined_balance_id IS NOT NULL
5332 -- For 115.29
5333 AND l_pen_defined_balance_id IS NOT NULL
5334 AND g_member = 'Y'
5335 THEN
5336 -- BEGIN
5337 l_earnings :=
5338 -- For 115.29
5339 pay_balance_pkg.get_value(
5340 p_defined_balance_id => l_pen_defined_balance_id
5341 ,p_assignment_id => p_assignment_id
5342 ,p_virtual_date => l_effective_end_date);
5343 /* get_total_ptd_bal_value(
5344 p_assignment_id => p_assignment_id
5345 ,p_defined_balance_id => l_defined_balance_id
5346 ,p_effective_start_date => g_effective_start_date
5347 ,p_effective_end_date => l_effective_end_date
5348 ); */
5349
5350 -- For Bug 5941475
5351 IF l_check_current_balance = 'Y'
5352 THEN
5353 l_current_earnings :=
5354 pay_balance_pkg.get_value(
5355 p_defined_balance_id => l_defined_balance_id
5356 ,p_assignment_id => p_assignment_id
5357 ,p_virtual_date => l_effective_end_date);
5358
5359 IF l_current_earnings <> 0
5360 THEN
5361 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5362 END IF;
5363
5364 END IF; --End if l_check_current_balance = 'Y'
5365
5366 -- pay_balance_pkg.get_value(
5367 -- p_defined_balance_id => l_defined_balance_id
5368 -- ,p_assignment_id => p_assignment_id
5369 -- ,p_virtual_date => l_effective_end_date);
5370 -- EXCEPTION
5371 -- WHEN NO_DATA_FOUND
5372 -- THEN
5373 -- IF g_debug
5374 -- THEN
5375 -- DEBUG('No data found exception: ');
5376 -- END IF;
5377 --
5378 -- l_earnings := 0;
5379 -- END;
5380 END IF; -- End if of defined balance id is not null check .
5381
5382 IF l_earnings > 0
5383 THEN
5384 l_return_value :=
5385 TRIM(TO_CHAR(l_earnings, '099999999.99'));
5386 ELSIF l_earnings < 0
5387 THEN
5388 l_return_value :=
5389 TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5390 ELSE
5391 l_return_value := RPAD(' ', 12, ' ');
5392 END IF;
5393
5394 ELSIF p_rule_parameter = 'SchemeContributions'
5395 THEN
5396 l_field_name := 'Premium and Classic Plus Scheme Contributions';
5397 IF g_member = 'Y'
5398 THEN
5399 -- Get premium scheme contributions
5400 l_earnings :=
5401 get_contribution_amount(
5402 p_assignment_id => p_assignment_id
5403 ,p_tab_pen_bal_dtls => g_tab_prem_pen_bal_dtls
5404 );
5405 -- l_defined_balance_id := g_prem_pen_bal_dtls.ees_ptd_bal_id;
5406 --
5407 -- IF g_debug
5408 -- THEN
5409 -- DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5410 -- END IF;
5411 --
5412 -- IF l_defined_balance_id IS NOT NULL AND g_member = 'Y'
5413 -- THEN
5414 -- -- BEGIN
5415 -- l_earnings :=
5416 -- get_total_ptd_bal_value(
5417 -- p_assignment_id => p_assignment_id
5418 -- ,p_defined_balance_id => l_defined_balance_id
5419 -- ,p_effective_start_date => g_effective_start_date
5420 -- ,p_effective_end_date => l_effective_end_date
5421 -- );
5422 -- pay_balance_pkg.get_value(
5423 -- p_defined_balance_id => l_defined_balance_id
5424 -- ,p_assignment_id => p_assignment_id
5425 -- ,p_virtual_date => l_effective_end_date);
5426 -- EXCEPTION
5427 -- WHEN NO_DATA_FOUND
5428 -- THEN
5429 -- IF g_debug
5430 -- THEN
5431 -- DEBUG('No data found exception: ');
5432 -- END IF;
5433 --
5434 -- l_earnings := 0;
5435 -- END;
5436 -- END IF; -- End if of defined balance id is not null check ...
5437
5438 -- Get Classic Plus scheme contributions
5439 l_earnings :=
5440 l_earnings
5441 + get_contribution_amount(
5442 p_assignment_id => p_assignment_id
5443 ,p_tab_pen_bal_dtls => g_tab_clap_pen_bal_dtls
5444 );
5445
5446 -- For Bug: 6788647
5447 -- Get Nuvos scheme contributions
5448 l_earnings :=
5449 l_earnings
5450 + get_contribution_amount(
5451 p_assignment_id => p_assignment_id
5452 ,p_tab_pen_bal_dtls => g_tab_nuvos_pen_bal_dtls
5453 );
5454
5455 -- For Bug 5941475
5456 IF l_check_current_balance = 'Y'
5457 THEN
5458 -- Get premium scheme contributions
5459 l_current_earnings :=
5460 get_contribution_amount(
5461 p_assignment_id => p_assignment_id
5462 ,p_tab_pen_bal_dtls => g_tab_prem_pen_bal_dtls
5463 ,p_ptd_balance => TRUE
5464 );
5465
5466 l_current_earnings :=
5467 l_current_earnings
5468 + get_contribution_amount(
5469 p_assignment_id => p_assignment_id
5470 ,p_tab_pen_bal_dtls => g_tab_clap_pen_bal_dtls
5471 ,p_ptd_balance => TRUE
5472 );
5473
5474 -- For Bug: 6788647
5475 l_current_earnings :=
5476 l_current_earnings
5477 + get_contribution_amount(
5478 p_assignment_id => p_assignment_id
5479 ,p_tab_pen_bal_dtls => g_tab_nuvos_pen_bal_dtls
5480 ,p_ptd_balance => TRUE
5481 );
5482
5483 IF l_current_earnings <> 0
5484 THEN
5485 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5486 END IF;
5487
5488 END IF; --l_check_current_balance = 'Y'
5489
5490 END IF; -- End if of g_member = 'Y' check ...
5491
5492 -- l_defined_balance_id := g_clap_pen_bal_dtls.ees_ptd_bal_id;
5493 --
5494 -- IF g_debug
5495 -- THEN
5496 -- DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5497 -- END IF;
5498 --
5499 -- IF l_defined_balance_id IS NOT NULL AND g_member = 'Y'
5500 -- THEN
5501 -- -- BEGIN
5502 -- l_earnings :=
5503 -- l_earnings
5504 -- + get_total_ptd_bal_value(
5505 -- p_assignment_id => p_assignment_id
5506 -- ,p_defined_balance_id => l_defined_balance_id
5507 -- ,p_effective_start_date => g_effective_start_date
5508 -- ,p_effective_end_date => l_effective_end_date
5509 -- );
5510 -- pay_balance_pkg.get_value(
5511 -- p_defined_balance_id => l_defined_balance_id
5512 -- ,p_assignment_id => p_assignment_id
5513 -- ,p_virtual_date => l_effective_end_date);
5514 -- EXCEPTION
5515 -- WHEN NO_DATA_FOUND
5516 -- THEN
5517 -- IF g_debug
5518 -- THEN
5519 -- DEBUG('No data found exception: ');
5520 -- END IF;
5521 -- END;
5522 -- END IF; -- End if of defined balance id is not null check ...
5523
5524 IF l_earnings > 0
5525 THEN
5526 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5527 ELSIF l_earnings < 0
5528 THEN
5529 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5530 ELSE
5531 l_return_value := RPAD(' ', 12, ' ');
5532 END IF;
5533 ELSIF p_rule_parameter = 'EmployeeContributions'
5534 THEN
5535 l_field_name := 'Partnership Scheme Employee Contributions';
5536 IF g_member = 'Y'
5537 THEN
5538 l_earnings :=
5539 get_contribution_amount(
5540 p_assignment_id => p_assignment_id
5541 ,p_tab_pen_bal_dtls => g_tab_part_pen_bal_dtls
5542 );
5543
5544 -- For Bug 5941475
5545 IF l_check_current_balance = 'Y'
5546 THEN
5547 l_current_earnings :=
5548 get_contribution_amount(
5549 p_assignment_id => p_assignment_id
5550 ,p_tab_pen_bal_dtls => g_tab_part_pen_bal_dtls
5551 ,p_ptd_balance => TRUE
5552 );
5553
5554 IF l_current_earnings <> 0
5555 THEN
5556 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5557 END IF;
5558
5559 END IF; --l_check_current_balance = 'Y'
5560
5561 END IF; -- End if of g_member = 'Y' check ...
5562
5563 -- l_defined_balance_id := g_part_pen_bal_dtls.ees_ptd_bal_id;
5564 --
5565 -- IF g_debug
5566 -- THEN
5567 -- DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5568 -- END IF;
5569 --
5570 -- IF l_defined_balance_id IS NOT NULL AND g_member = 'Y'
5571 -- THEN
5572 -- -- BEGIN
5573 -- l_earnings :=
5574 -- get_total_ptd_bal_value(
5575 -- p_assignment_id => p_assignment_id
5576 -- ,p_defined_balance_id => l_defined_balance_id
5577 -- ,p_effective_start_date => g_effective_start_date
5578 -- ,p_effective_end_date => l_effective_end_date
5579 -- );
5580 -- pay_balance_pkg.get_value(
5581 -- p_defined_balance_id => l_defined_balance_id
5582 -- ,p_assignment_id => p_assignment_id
5583 -- ,p_virtual_date => l_effective_end_date);
5584 -- EXCEPTION
5585 -- WHEN NO_DATA_FOUND
5586 -- THEN
5587 -- IF g_debug
5588 -- THEN
5589 -- DEBUG('No data found exception: ');
5590 -- END IF;
5591 --
5592 -- l_earnings := 0;
5593 -- END;
5594 -- END IF; -- End if of defined balance id is not null check ...
5595
5596 IF l_earnings > 0
5597 THEN
5598 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5599 ELSIF l_earnings < 0
5600 THEN
5601 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5602 ELSE
5603 l_return_value := RPAD(' ', 12, ' ');
5604 END IF;
5605 ELSIF p_rule_parameter = 'EmployerContributions' AND g_member = 'Y'
5606 THEN
5607 l_field_name := 'Partnership Scheme Employer Contributions';
5608 l_earnings :=
5609 get_contribution_amount(
5610 p_assignment_id => p_assignment_id
5611 ,p_tab_pen_bal_dtls => g_tab_part_pen_bal_dtls
5612 ,p_employer_only => TRUE
5613 );
5614
5615 -- For Bug 5941475
5616 IF l_check_current_balance = 'Y'
5617 THEN
5618 l_current_earnings :=
5619 get_contribution_amount(
5620 p_assignment_id => p_assignment_id
5621 ,p_tab_pen_bal_dtls => g_tab_part_pen_bal_dtls
5622 ,p_ptd_balance => TRUE
5623 ,p_employer_only => TRUE
5624 );
5625
5626 IF l_current_earnings <> 0
5627 THEN
5628 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5629 END IF;
5630
5631 END IF; --l_check_current_balance = 'Y'
5632
5633
5634 -- l_defined_balance_id := g_part_pen_bal_dtls.ers_ptd_bal_id;
5635 --
5636 -- IF g_debug
5637 -- THEN
5638 -- DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5639 -- END IF;
5640 --
5641 -- IF l_defined_balance_id IS NOT NULL
5642 -- THEN
5643 -- -- BEGIN
5644 -- l_earnings :=
5645 -- get_total_ptd_bal_value(
5646 -- p_assignment_id => p_assignment_id
5647 -- ,p_defined_balance_id => l_defined_balance_id
5648 -- ,p_effective_start_date => g_effective_start_date
5649 -- ,p_effective_end_date => l_effective_end_date
5650 -- );
5651 -- pay_balance_pkg.get_value(
5652 -- p_defined_balance_id => l_defined_balance_id
5653 -- ,p_assignment_id => p_assignment_id
5654 -- ,p_virtual_date => l_effective_end_date);
5655 -- EXCEPTION
5656 -- WHEN NO_DATA_FOUND
5657 -- THEN
5658 -- IF g_debug
5659 -- THEN
5660 -- DEBUG('No data found exception: ');
5661 -- END IF;
5662 --
5663 -- l_earnings := 0;
5664 -- END;
5665 -- END IF; -- End if of defined balance id is not null check ...
5666
5667 IF l_earnings > 0
5668 THEN
5669 l_return_value := TRIM(TO_CHAR(l_earnings, '099999999.99'));
5670 ELSIF l_earnings < 0
5671 THEN
5672 l_return_value := TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5673 ELSE
5674 l_return_value := RPAD(' ', 12, ' ');
5675 END IF;
5676 ELSIF p_rule_parameter = 'Scheme' THEN
5677 l_return_value := TRIM(RPAD(pqp_gb_psi_functions.g_pension_scheme,4,' '));
5678
5679 /* BEGIN For Nuvos */
5680
5681 ELSIF p_rule_parameter = 'APAVC'
5682 THEN
5683 l_field_name := 'APAVC Contribtuions';
5684
5685 l_defined_balance_id := g_tot_apavc_ptd_bal_id;
5686 -- For 115.29
5687 l_pen_defined_balance_id := g_tot_apavc_ytd_bal_id;
5688
5689 IF g_debug
5690 THEN
5691 DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5692 -- For 115.29
5693 DEBUG('l_pen_defined_balance_id: ' || l_pen_defined_balance_id);
5694 END IF;
5695
5696 IF l_defined_balance_id IS NOT NULL
5697 AND l_pen_defined_balance_id IS NOT NULL
5698 AND g_member = 'Y'
5699 THEN
5700 -- BEGIN
5701 l_earnings :=
5702 -- For 115.29
5703 pay_balance_pkg.get_value(
5704 p_defined_balance_id => l_pen_defined_balance_id
5705 ,p_assignment_id => p_assignment_id
5706 ,p_virtual_date => l_effective_end_date);
5707
5708 /* get_total_ptd_bal_value(
5709 p_assignment_id => p_assignment_id
5710 ,p_defined_balance_id => l_defined_balance_id
5711 ,p_effective_start_date => g_effective_start_date
5712 ,p_effective_end_date => l_effective_end_date
5713 ); */
5714
5715 -- For Bug 5941475
5716 IF l_check_current_balance = 'Y'
5717 THEN
5718 l_current_earnings :=
5719 pay_balance_pkg.get_value(
5720 p_defined_balance_id => l_defined_balance_id
5721 ,p_assignment_id => p_assignment_id
5722 ,p_virtual_date => l_effective_end_date);
5723
5724 IF l_current_earnings <> 0
5725 THEN
5726 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5727 END IF;
5728
5729 END IF;
5730
5731 END IF; -- End if of defined balance id is not null check .
5732
5733 IF l_earnings > 0
5734 THEN
5735 l_return_value :=
5736 TRIM(TO_CHAR(l_earnings, '099999999.99'));
5737 ELSIF l_earnings < 0
5738 THEN
5739 l_return_value :=
5740 TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5741 ELSE
5742 l_return_value := RPAD(' ', 12, ' ');
5743 END IF;
5744
5745 -- APAVCM Contributions */
5746 ELSIF p_rule_parameter = 'APAVCM'
5747 THEN
5748 l_field_name := 'APAVCM Contribtuions';
5749
5750 l_defined_balance_id := g_tot_apavcm_ptd_bal_id;
5751 -- For 115.29
5752 l_pen_defined_balance_id := g_tot_apavcm_ytd_bal_id;
5753
5754 IF g_debug
5755 THEN
5756 DEBUG('l_defined_balance_id: ' || l_defined_balance_id);
5757 DEBUG('l_pen_defined_balance_id: ' || l_pen_defined_balance_id);
5758 END IF;
5759
5760 IF l_defined_balance_id IS NOT NULL
5761 AND l_pen_defined_balance_id IS NOT NULL
5762 AND g_member = 'Y'
5763 THEN
5764 -- BEGIN
5765 l_earnings :=
5766 -- For 115.29
5767 pay_balance_pkg.get_value(
5768 p_defined_balance_id => l_pen_defined_balance_id
5769 ,p_assignment_id => p_assignment_id
5770 ,p_virtual_date => l_effective_end_date);
5771
5772 /* get_total_ptd_bal_value(
5773 p_assignment_id => p_assignment_id
5774 ,p_defined_balance_id => l_defined_balance_id
5775 ,p_effective_start_date => g_effective_start_date
5776 ,p_effective_end_date => l_effective_end_date
5777 ); */
5778
5779 -- For Bug 5941475
5780 IF l_check_current_balance = 'Y'
5781 THEN
5782 l_current_earnings :=
5783 pay_balance_pkg.get_value(
5784 p_defined_balance_id => l_defined_balance_id
5785 ,p_assignment_id => p_assignment_id
5786 ,p_virtual_date => l_effective_end_date);
5787
5788 IF l_current_earnings <> 0
5789 THEN
5790 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5791 END IF;
5792
5793 END IF;
5794
5795 END IF; -- End if of defined balance id is not null check .
5796
5797 IF l_earnings > 0
5798 THEN
5799 l_return_value :=
5800 TRIM(TO_CHAR(l_earnings, '099999999.99'));
5801 ELSIF l_earnings < 0
5802 THEN
5803 l_return_value :=
5804 TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5805 ELSE
5806 l_return_value := RPAD(' ', 12, ' ');
5807 END IF;
5808
5809 -- SPN
5810
5811 ELSIF p_rule_parameter = 'SPN'
5812 THEN
5813 l_field_name := 'Service Period Number';
5814
5815 l_return_value := get_asg_eit_info(p_assignment_id => p_assignment_id
5816 ,p_information_type => 'PQP_GB_PENSERV_SVPN'
5817 );
5818
5819
5820 ELSIF p_rule_parameter = 'EARNINGS'
5821 THEN
5822 l_field_name := 'Pensionable Earnings for Nuvos Members';
5823
5824 l_earnings := get_nuvos_contribution_amount(p_assignment_id => p_assignment_id);
5825
5826 -- For bug: 5941475
5827 IF l_check_current_balance = 'Y'
5828 THEN
5829 l_current_earnings :=
5830 get_nuvos_contribution_amount(
5831 p_assignment_id => p_assignment_id
5832 ,p_ptd_balance => TRUE
5833 );
5834
5835 IF l_current_earnings <> 0
5836 THEN
5837 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'N';
5838 END IF;
5839
5840 END IF; --l_check_current_balance = 'Y'
5841
5842 IF l_earnings >= 0
5843 THEN
5844 l_return_value :=
5845 TRIM(TO_CHAR(l_earnings, '099999999.99'));
5846 ELSIF l_earnings < 0
5847 THEN
5848 l_return_value :=
5849 TRIM(TO_CHAR(l_earnings, 'S09999999.99'));
5850 ELSE
5851 l_return_value := RPAD(' ', 12, ' ');
5852 END IF;
5853
5854 /* END For Nuvos */
5855
5856 -- For Bug 5941475
5857 ELSIF p_rule_parameter = 'Check' THEN
5858
5859 IF pqp_gb_psi_earnings_history.g_ern_term_exclude_flag = 'Y' and l_check_current_balance = 'Y'
5860 THEN
5861 l_return_value := NULL;
5862 ELSE
5863 l_return_value := 'INCLUDE';
5864 END IF;
5865
5866 pqp_gb_psi_earnings_history.g_ern_term_exclude_flag := 'Y';
5867
5868 END IF; -- End if of rule parameter check ...
5869
5870 IF NOT l_earnings BETWEEN -99999999.99 AND 999999999.99
5871 THEN
5872 IF g_debug
5873 THEN
5874 DEBUG('Maximum length error');
5875 END IF;
5876
5877 l_value :=
5878 pqp_gb_psi_functions.raise_extract_error(
5879 p_error_number => 94589
5880 ,p_error_text => 'BEN_94589_EXT_MAX_LENGTH_ERROR'
5881 ,p_token1 => l_field_name || ' '
5882 || TO_CHAR(l_earnings)
5883 ,p_token2 => '999999999.99'
5884 );
5885 END IF; -- End if of earnings value check ...
5886
5887 IF g_debug
5888 THEN
5889 l_proc_step := 30;
5890 DEBUG('l_field_name: ' || l_field_name);
5891 DEBUG('l_return_value: ' || l_return_value);
5892 debug_exit(l_proc_name);
5893 END IF;
5894
5895 RETURN l_return_value;
5896 EXCEPTION
5897 WHEN OTHERS
5898 THEN
5899 clear_cache;
5900
5901 IF SQLCODE <> hr_utility.hr_error_number
5902 THEN
5903 debug_others(l_proc_name, l_proc_step);
5904
5905 IF g_debug
5906 THEN
5907 DEBUG('Leaving: ' || l_proc_name, -999);
5908 END IF;
5909
5910 fnd_message.raise_error;
5911 ELSE
5912 RAISE;
5913 END IF;
5914 END get_earnings_history_data;
5915
5916 -- This function is used for post processing in earnings history interface
5917 -- ----------------------------------------------------------------------------
5918 -- |---------------------< earnings_history_post_process >---------------------|
5919 -- ----------------------------------------------------------------------------
5920 FUNCTION earnings_history_post_process(p_ext_rslt_id IN NUMBER)
5921 RETURN VARCHAR2
5922 IS
5923 --
5924 l_proc_name VARCHAR2(80)
5925 := g_proc_name || 'earnings_history_post_process';
5926 l_proc_step PLS_INTEGER;
5927 l_return_value VARCHAR2(100);
5928 --
5929 BEGIN
5930 --
5931 IF g_debug
5932 THEN
5933 l_proc_step := 10;
5934 debug_enter(l_proc_name);
5935 END IF;
5936
5937 pqp_gb_psi_functions.raise_extract_exceptions('S');
5938 pqp_gb_psi_functions.common_post_process(p_business_group_id => g_business_group_id);
5939
5940 IF g_debug
5941 THEN
5942 l_proc_step := 20;
5943 debug_exit(l_proc_name);
5944 END IF;
5945
5946 RETURN l_return_value;
5947 EXCEPTION
5948 WHEN OTHERS
5949 THEN
5950 clear_cache;
5951
5952 IF SQLCODE <> hr_utility.hr_error_number
5953 THEN
5954 debug_others(l_proc_name, l_proc_step);
5955
5956 IF g_debug
5957 THEN
5958 DEBUG('Leaving: ' || l_proc_name, -999);
5959 END IF;
5960
5961 fnd_message.raise_error;
5962 ELSE
5963 RAISE;
5964 END IF;
5965 END earnings_history_post_process;
5966 END pqp_gb_psi_earnings_history;