1 Package body pay_ie_pensions as
2 /* $Header: pyiepenf.pkb 120.3 2012/01/19 09:43:30 rpahune ship $ */
3 FUNCTION IE_GET_MAX_PENSION_PERCENT(
4 p_assgt_id NUMBER,
5 p_date_earned DATE,
6 prsa2 NUMBER)
7 RETURN NUMBER
8 IS
9 l_age number;
10 l_percent number;
11 BEGIN
12 SELECT DISTINCT floor(months_between(p_date_earned,p.date_of_birth)/12) into l_age
13 FROM per_all_people_f p, per_all_assignments_f a
14 WHERE a.person_id=p.person_id AND a.assignment_id=p_assgt_id;
15 IF prsa2 = 0 THEN
16 SELECT i.value INTO l_percent
17 FROM pay_user_tables t, pay_user_columns c, pay_user_rows_f r, pay_user_column_instances_f i
18 WHERE t.user_table_name = 'IE PRSA Certificate Rates'
19 AND t.user_table_id=c.user_table_id
20 AND t.user_table_id=r.user_table_id
21 AND i.user_column_id=c.user_column_id
22 AND i.user_row_id=r.user_row_id
23 AND c.user_column_name = 'PRSA1'
24 AND l_age BETWEEN r.row_low_range_or_name AND r.row_high_range
25 AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
26 AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
27 ELSE
28 SELECT i.value INTO l_percent
29 FROM pay_user_tables t, pay_user_columns c, pay_user_rows_f r, pay_user_column_instances_f i
30 WHERE t.user_table_name = 'IE PRSA Certificate Rates'
31 AND t.user_table_id=c.user_table_id
32 AND t.user_table_id=r.user_table_id
33 AND i.user_column_id=c.user_column_id
34 AND i.user_row_id=r.user_row_id
35 AND c.user_column_name = 'PRSA2'
36 AND l_age BETWEEN r.row_low_range_or_name AND r.row_high_range
37 AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
38 AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
39 END IF;
40 RETURN l_percent;
41 END IE_GET_MAX_PENSION_PERCENT;
42
43 FUNCTION GET_EARNINGS_CAP
44 ( p_date_earned DATE)
45 RETURN NUMBER
46 IS
47 l_amount number;
48 BEGIN
49 SELECT global_value INTO l_amount
50 FROM ff_globals_f g
51 WHERE g.global_name = 'IE_PENSIONS_EARNINGS_CAP'
52 AND p_date_earned BETWEEN g.effective_start_date AND g.effective_end_date;
53 RETURN l_amount;
54 END GET_EARNINGS_CAP;
55
56 FUNCTION GET_PENSION_CONTRIBUTION
57 (p_date_earned DATE,
58 p_contribution_type VARCHAR2,
59 p_pension_type_id NUMBER,
60 p_pensionable_pay NUMBER)
61 RETURN NUMBER IS
62 l_amount number;
63 l_percent number;
64 BEGIN
65 l_amount := 0;
66 l_percent := 0;
67 IF p_contribution_type='EE' THEN
68 SELECT ee_contribution_percent, ee_annual_contribution
69 INTO l_percent, l_amount
70 FROM pqp_pension_types_f
71 WHERE pension_type_id = p_pension_type_id
72 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
73 IF l_amount = 0 or l_amount is null THEN
74 l_amount := p_pensionable_pay*nvl(l_percent,0)/100;
75 END IF;
76 ELSE
77 SELECT er_contribution_percent, er_annual_contribution
78 INTO l_percent, l_amount
79 FROM pqp_pension_types_f
80 WHERE pension_type_id = p_pension_type_id
81 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
82 IF l_amount = 0 or l_amount is null THEN
83 l_amount := p_pensionable_pay*nvl(l_percent,0)/100;
84 END IF;
85 END IF;
86 RETURN l_amount;
87 END GET_PENSION_CONTRIBUTION;
88 FUNCTION IE_GET_MAX_PENSION_PERCENT_AGE(
89 p_date_earned DATE,
90 p_age NUMBER,
91 prsa2 NUMBER)
92 RETURN NUMBER
93 IS
94 l_percent number;
95 BEGIN
96 IF prsa2 = 0 THEN
97 SELECT i.value INTO l_percent
98 FROM pay_user_tables t, pay_user_columns c, pay_user_rows_f r, pay_user_column_instances_f i
99 WHERE t.user_table_name = 'IE PRSA Certificate Rates'
100 AND t.user_table_id=c.user_table_id
101 AND t.user_table_id=r.user_table_id
102 AND i.user_column_id=c.user_column_id
103 AND i.user_row_id=r.user_row_id
104 AND c.user_column_name = 'PRSA1'
105 AND p_age BETWEEN r.row_low_range_or_name AND r.row_high_range
106 AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
107 AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
108 ELSE
109 SELECT i.value INTO l_percent
110 FROM pay_user_tables t, pay_user_columns c, pay_user_rows_f r, pay_user_column_instances_f i
111 WHERE t.user_table_name = 'IE PRSA Certificate Rates'
112 AND t.user_table_id=c.user_table_id
113 AND t.user_table_id=r.user_table_id
114 AND i.user_column_id=c.user_column_id
115 AND i.user_row_id=r.user_row_id
116 AND c.user_column_name = 'PRSA2'
117 AND p_age BETWEEN r.row_low_range_or_name AND r.row_high_range
118 AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
119 AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
120 END IF;
121 RETURN l_percent;
122 END IE_GET_MAX_PENSION_PERCENT_AGE;
123
124 END pay_ie_pensions;
[Home] [Help]