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