[Home] [Help]
PACKAGE BODY: APPS.PQP_US_SRS_FUNCTIONS
Source
1 Package Body PQP_US_SRS_Functions AS
2 /* $Header: pqussrfn.pkb 115.6 2003/07/31 13:37:12 rpinjala noship $ */
3
4 g_business_group_id per_business_groups.business_group_id%TYPE;
5 ----------------------------------------------------------------------------+
6 -- FUNCTION GET_SRS_LIMIT
7 ----------------------------------------------------------------------------+
8 FUNCTION get_srs_limit(p_payroll_action_id IN NUMBER
9 ,p_limit IN VARCHAR2)
10 RETURN NUMBER IS
11 --+
12 CURSOR c_limit (p_effective_date IN DATE) IS
13 SELECT fed_information1, -- DBP Payback Limit
14 fed_information2, -- DCP Combined EE ER Limit
15 fed_information3, -- Includable Annual Comp Limit
16 fed_information4, -- Includable Annual Comp GF Limit
17 fed_information5, -- DCP EE Contribution Limit
18 fed_information6 -- DBP Contribution Limit (%age)
19 FROM pay_us_federal_tax_info_f
20 WHERE p_effective_date BETWEEN effective_start_date
21 AND effective_end_date
22 AND fed_information_category = 'SRS LIMITS';
23 --+
24 l_limit_val NUMBER;
25 l_string VARCHAR2(1000);
26
27 dbp_payback_limit NUMBER;
28 dcp_combined_ee_er_limit NUMBER;
29 includable_annual_comp_limit NUMBER;
30 incld_annual_comp_gf_limit NUMBER;
31 dcp_ee_contribution_limit NUMBER;
32 dbp_contribution_limit NUMBER;
33
34 --+
35 l_effective_date DATE;
36
37 BEGIN
38
39 l_effective_date := TRUNC(pqp_us_srs_functions.get_date_paid
40 (p_payroll_action_id));
41
42
43 FOR c_rec IN c_limit(l_effective_date)
44 LOOP
45 dbp_payback_limit := to_number(c_rec.fed_information1); -- DBP Payback Limit
46 dcp_combined_ee_er_limit := to_number(c_rec.fed_information2); -- DCP Combined EE ER Limit
47 includable_annual_comp_limit := to_number(c_rec.fed_information3); -- Includable Annual Comp Limit
48 incld_annual_comp_gf_limit := to_number(c_rec.fed_information4); -- Includable Annual Comp GF Limit
49 dcp_ee_contribution_limit := to_number(c_rec.fed_information5); -- DCP EE Contribution Limit
50 dbp_contribution_limit := to_number(c_rec.fed_information6); -- DBP Contribution Limit (%age)
51 END LOOP;
52
53 IF p_limit = 'DBP' THEN
54 RETURN NVL(dbp_contribution_limit,0);
55 ELSIF p_limit = 'DCP' THEN
56 RETURN NVL(dcp_ee_contribution_limit,0);
57 ELSIF p_limit = 'ER_LIMIT_DCP' THEN
58 RETURN NVL(dcp_combined_ee_er_limit,0);
59 ELSIF p_limit = 'BuyBack_DCP' THEN
60 RETURN NVL(dcp_ee_contribution_limit,0);
61 ELSIF p_limit = 'BuyBack_DBP' THEN
62 RETURN NVL(dbp_contribution_limit,0);
63 ELSIF p_limit = 'ER_LIMIT_DBP' THEN
64 RETURN NVL(dbp_payback_limit,0);
65 ELSIF p_limit = 'COMP_LIMIT' THEN
66 RETURN NVL(includable_annual_comp_limit,0);
67 ELSIF p_limit = 'GF_COMP_LIMIT' THEN
68 RETURN NVL(incld_annual_comp_gf_limit,0);
69 ELSE
70 RETURN 0;
71 END IF;
72
73 END;
74 ----------------------------------------------------------------------------+
75 -- FUNCTION get_date_paid
76 ----------------------------------------------------------------------------+
77 FUNCTION get_date_paid (p_payroll_action_id IN NUMBER)
78
79 RETURN DATE AS
80 --+
81 CURSOR c_date_paid IS
82 SELECT effective_date, business_group_id
83 FROM pay_payroll_actions
84 WHERE payroll_action_id= p_payroll_action_id;
85 --+
86 l_date_paid date;
87
88 BEGIN
89
90 l_date_paid := null;
91
92 FOR c_rec IN c_date_paid
93 LOOP
94 l_date_paid := c_rec.effective_date;
95 g_business_group_id := c_rec.business_group_id;
96 END LOOP;
97
98 RETURN (l_date_paid);
99
100 END;
101 ----------------------------------------------------------------------------+
102 -- FUNCTION get_srs_plan_type
103 ----------------------------------------------------------------------------+
104 FUNCTION get_srs_plan_type (p_element_type_id IN NUMBER)
105 RETURN VARCHAR2 IS
106 --+
107
108 CURSOR srs_c1 (c_element_type_id In NUMBER
109 ,c_information_type In VARCHAR2
110 ) Is
111 SELECT EEI_INFORMATION4
112 FROM PAY_ELEMENT_TYPE_EXTRA_INFO
113 WHERE element_type_id = c_element_type_id
114 AND information_type = c_information_type ;
115
116 l_eei_information4 PAY_ELEMENT_TYPE_EXTRA_INFO.EEI_INFORMATION4%TYPE;
117
118 --+
119
120 BEGIN
121
122 OPEN srs_c1 ( c_element_type_id => p_element_type_id
123 ,c_information_type => 'PQP_US_SRS_DEDUCTIONS'
124 );
125 FETCH srs_c1 Into l_eei_information4;
126 IF srs_c1%NOTFOUND THEN
127 l_eei_information4 := 'N'; -- Valid values Benefit(B), Contribution(C) or None(N)
128 END IF;
129
130 CLOSE srs_c1;
131
132 RETURN l_eei_information4;
133
134 END;
135
136 ----------------------------------------------------------------------------+
137 -- FUNCTION check_srs_enrolled
138 -- Function to Check if the person is enrolled for
139 -- SRS plan. This function also checks if the person
140 -- is eligible for Grandfathering or 10 Year Rule.
141 ----------------------------------------------------------------------------+
142 FUNCTION check_srs_enrollment (p_element_type_id IN NUMBER
143 ,p_assignment_id IN NUMBER
144 ,p_payroll_action_id IN NUMBER
145 ,p_enrollment_type IN VARCHAR2
146 )
147 RETURN VARCHAR2 IS
148 CURSOR srs_c1 (c_element_type_id IN NUMBER
149 ,c_assignment_id IN NUMBER
150 ,c_effective_date IN DATE
151 ) Is
152 SELECT aei_information5,
153 aei_information6
154 FROM per_assignment_extra_info paei,
155 pay_element_types_f pet
156 WHERE assignment_id = c_assignment_id
157 AND information_type = 'PQP_US_SRS_PLAN_ASG_INFO'
158 AND aei_information4 = pet.element_name
159 AND element_type_id = c_element_type_id
160 AND c_effective_date
161 BETWEEN NVL(TO_DATE(SUBSTR(aei_information1,1,10),'yyyy/mm/dd')
162 ,c_effective_date)
163 AND NVL(TO_DATE(SUBSTR(aei_information2,1,10),'yyyy/mm/dd')
164 ,c_effective_date)
165 AND c_effective_date BETWEEN pet.effective_start_date
166 AND pet.effective_end_date;
167
168 Cursor csr_ele (c_element_name in varchar2
169 ,c_effective_date in date
170 ,c_business_group_id in number) Is
171 Select pet.element_type_id
172 from pay_element_types_f pet
173 Where pet.element_name = c_element_name
174 and pet.business_group_id = c_business_group_id
175 and c_effective_date between pet.effective_start_date
176 and pet.effective_end_date;
177
178 CURSOR srs_c2 (c_assignment_id IN NUMBER
179 ) Is
180 SELECT aei_information4,
181 aei_information5,
182 aei_information6
183 FROM per_assignment_extra_info paei
184 WHERE assignment_id = c_assignment_id
185 AND information_type = 'PQP_US_SRS_PLAN_ASG_INFO';
186
187 l_aei_information4 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION4%TYPE;
188 l_aei_information5 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION5%TYPE;
189 l_aei_information6 PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION6%TYPE;
190 l_effective_date DATE;
191 l_srs_enrolled VARCHAR2(1) := 'N';
192 l_ele_name pay_element_types_f.element_name%TYPE;
193 l_base_ele_id pay_element_types_f.element_type_id%TYPE;
194 l_buyback_ele_id pay_element_types_f.element_type_id%TYPE;
195 l_return_value VARCHAR2(2) := 'N';
196 BEGIN
197 l_effective_date := TRUNC(pqp_us_srs_functions.get_date_paid
198 (p_payroll_action_id)
199 );
200
201 IF p_enrollment_type = 'SRS' THEN
202 OPEN srs_c1 ( c_element_type_id => p_element_type_id
203 ,c_assignment_id => p_assignment_id
204 ,c_effective_date => l_effective_date
205 );
206 FETCH srs_c1 INTO l_aei_information5,
207 l_aei_information6;
208 IF srs_c1%NOTFOUND THEN
209 l_srs_enrolled := 'N'; -- Person is not enrolled for SRS Plan
210 ELSE
211 l_srs_enrolled := 'Y'; -- Person is enrolled for SRS Plan
212 END IF;
213 CLOSE srs_c1;
214 RETURN NVL(l_srs_enrolled, 'N');
215 ELSE
216 l_return_value := 'N';
217 For asg_ext In srs_c2 (c_assignment_id => p_assignment_id)
218 Loop
219 l_ele_name := asg_ext.aei_information4;
220 Open csr_ele (c_element_name => l_ele_name
221 ,c_effective_date => l_effective_date
222 ,c_business_group_id => g_business_group_id);
223 Fetch csr_ele Into l_base_ele_id;
224 Close csr_ele;
225 l_ele_name := asg_ext.aei_information4||' Buy Back';
226 Open csr_ele (c_element_name => l_ele_name
227 ,c_effective_date => l_effective_date
228 ,c_business_group_id => g_business_group_id);
229 Fetch csr_ele Into l_buyback_ele_id;
230 Close csr_ele;
231 If p_element_type_id = l_base_ele_id Or
232 p_element_type_id = l_buyback_ele_id Then
233 IF p_enrollment_type = 'TEN_YEAR' THEN
234 l_return_value := NVL(asg_ext.aei_information5,'N');
235 ELSIF p_enrollment_type = 'GRAND_FATHERING' THEN
236 l_return_value := NVL(asg_ext.aei_information6,'N');
237 END IF;
238 End If;
239 End Loop;
240 Return l_return_value;
241 END IF;
242 END check_srs_enrollment;
243
244 END pqp_us_srs_functions;