[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MULTI_MMRF
Source
1 PACKAGE BODY pay_us_multi_mmrf AS
2 /* $Header: payusmultimmrf.pkb 120.2.12020000.2 2012/07/04 20:03:56 amnaraya ship $ */
3 FUNCTION get_w2_er_arch_bal(
4 w2_balance_name in varchar2,
5 w2_tax_unit_id in varchar2,
6 w2_jurisdiction_code in varchar2,
7 w2_jurisdiction_level in varchar2,
8 w2_year in varchar2,
9 a1 OUT NOCOPY varchar2,
10 a2 OUT NOCOPY varchar2,
11 a3 OUT NOCOPY varchar2,
12 a4 OUT NOCOPY varchar2,
13 a5 OUT NOCOPY varchar2,
14 a6 OUT NOCOPY varchar2,
15 a7 OUT NOCOPY varchar2,
16 a8 OUT NOCOPY varchar2,
17 a9 OUT NOCOPY varchar2,
18 a10 OUT NOCOPY varchar2,
19 a11 OUT NOCOPY varchar2,
20 a12 OUT NOCOPY varchar2,
21 a13 OUT NOCOPY varchar2,
22 a14 OUT NOCOPY varchar2,
23 a15 OUT NOCOPY varchar2,
24 a16 OUT NOCOPY varchar2,
25 a17 OUT NOCOPY varchar2,
26 a18 OUT NOCOPY varchar2,
27 a19 OUT NOCOPY varchar2,
28 a20 OUT NOCOPY varchar2,
29 a21 OUT NOCOPY varchar2,
30 a22 OUT NOCOPY varchar2,
31 a23 OUT NOCOPY varchar2,
32 a24 OUT NOCOPY varchar2,
33 a25 OUT NOCOPY varchar2,
34 a26 OUT NOCOPY varchar2,
35 a27 OUT NOCOPY varchar2,
36 a28 OUT NOCOPY varchar2,
37 a29 OUT NOCOPY varchar2,
38 a30 OUT NOCOPY varchar2,
39 a31 OUT NOCOPY varchar2,
40 a32 OUT NOCOPY varchar2,
41 a33 OUT NOCOPY varchar2,
42 a34 OUT NOCOPY varchar2,
43 a35 OUT NOCOPY varchar2,
44 a36 OUT NOCOPY varchar2,
45 a37 OUT NOCOPY varchar2,
46 a38 OUT NOCOPY varchar2,
47 a39 OUT NOCOPY varchar2,
48 a40 OUT NOCOPY varchar2,
49 a41 OUT NOCOPY varchar2,
50 a42 OUT NOCOPY varchar2,
51 a43 OUT NOCOPY varchar2,
52 a44 OUT NOCOPY varchar2,
53 a45 OUT NOCOPY varchar2,
54 a46 OUT NOCOPY varchar2,
55 a47 OUT NOCOPY varchar2
56 )
57 RETURN varchar2 IS
58
59 CURSOR C_EMP_count(cp_tax_unit_id number) IS
60 select count(*)
61 from pay_payroll_actions ppa,
62 pay_assignment_actions paa
63 where ppa.report_type = 'W2'
64 and report_qualifier = 'FED'
65 and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
66 and ppa.payroll_action_id = paa.payroll_action_id
67 and paa.action_status = 'C'
68 and paa.tax_unit_id = cp_tax_unit_id;
69
70 CURSOR C_ER_SUM ( P_TAX_UNIT_ID number) IS
71 SELECT user_entity_name,
72 DECODE(fue.user_entity_name,
73 'A_REGULAR_EARNINGS_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
74 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
75 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
76 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
77 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
78 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
79 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD',nvl(sum(value),0) * 100,
80 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',nvl(sum(value),0) * 100,
81 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD',nvl(sum(value),0) * 100,
82 'A_FIT_WITHHELD_PER_GRE_YTD',nvl(sum(value),0) * 100,
83 'A_SS_EE_TAXABLE_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
84 'A_SS_EE_WITHHELD_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
85 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
86 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
87 'A_W2_BOX_7_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
88 'A_EIC_ADVANCE_PER_GRE_YTD',nvl(sum(value),0) * 100,
89 'A_W2_DEPENDENT_CARE_PER_GRE_YTD',nvl(sum(value),0) * 100 ,
90 'A_W2_401K_PER_GRE_YTD',nvl(sum(value),0) * 100,
91 'A_W2_403B_PER_GRE_YTD',nvl(sum(value),0) * 100,
92 'A_W2_408K_PER_GRE_YTD', nvl(sum(value),0) * 100,
93 'A_W2_457_PER_GRE_YTD', nvl(sum(value),0) * 100,
94 'A_W2_501C_PER_GRE_YTD', nvl(sum(value),0) * 100,
95 'A_W2_MILITARY_HOUSING_PER_GRE_YTD', nvl(sum(value),0) * 100,
96 'A_W2_NONQUAL_PLAN_PER_GRE_YTD', nvl(sum(value),0) * 100,
97 'A_W2_NONQUAL_457_PER_GRE_YTD',nvl(sum(value),0) * 100,
98 'A_W2_BOX_11_PER_GRE_YTD', nvl(sum(value),0) * 100,
99 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD', nvl(sum(value),0) * 100,
100 'A_FIT_3RD_PARTY_PER_GRE_YTD', nvl(sum(value),0) * 100,
101 'A_W2_NONQUAL_STOCK_PER_GRE_YTD', nvl(sum(value),0) * 100,
102 'A_W2_HSA_PER_GRE_YTD', nvl(sum(value),0) * 100,
103 'A_W2_NONTAX_COMBAT_PER_GRE_YTD', nvl(sum(value),0) * 100,
104 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD',nvl(sum(value),0) * 100,
105 'A_W2_BOX_8_PER_GRE_YTD', nvl(sum(value),0) * 100,
106 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD', nvl(sum(value),0) * 100,
107 'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD', nvl(sum(value),0) * 100,
108 'A_W2_MSA_PER_GRE_YTD', nvl(sum(value),0) * 100,
109 'A_W2_408P_PER_GRE_YTD', nvl(sum(value),0) * 100,
110 'A_W2_ADOPTION_PER_GRE_YTD', nvl(sum(value),0) * 100,
111 'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD', nvl(sum(value),0) * 100,
112 'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD', nvl(sum(value),0) * 100,
113 'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD', nvl(sum(value),0) * 100,
114 'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD', nvl(sum(value),0) * 100,
115 'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD', nvl(sum(value),0) * 100,
116 'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD', nvl(sum(value),0) * 100,
117 'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD', nvl(sum(value),0) * 100
118 , 'A_W2_ROTH_401K_PER_GRE_YTD', nvl(sum(value),0) * 100
119 , 'A_W2_ROTH_403B_PER_GRE_YTD', nvl(sum(value),0) * 100
120 ) val
121 FROM ff_archive_items fai,
122 pay_action_interlocks pai,
123 pay_payroll_actions ppa,
124 pay_assignment_actions paa,
125 ff_user_entities fue
126 where ppa.report_type = 'W2'
127 and ppa.report_qualifier = 'FED'
128 and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
129 and ppa.payroll_action_id = paa.payroll_action_id
130 and paa.tax_unit_id = p_tax_unit_id
131 and paa.action_status = 'C'
132 and paa.assignment_action_id = pai.locking_action_id
133 and fai.context1 = pai.locked_action_id
134 and fai.user_entity_id = fue.user_entity_id
135 and fue.user_entity_name IN
136 (
137 'A_REGULAR_EARNINGS_PER_GRE_YTD' ,
138 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,
139 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,
140 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,
141 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' ,
142 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' ,
143 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD',
144 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',
145 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD',
146 'A_FIT_WITHHELD_PER_GRE_YTD',
147 'A_SS_EE_TAXABLE_PER_GRE_YTD',
148 'A_SS_EE_WITHHELD_PER_GRE_YTD',
149 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD',
150 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD',
151 'A_W2_BOX_7_PER_GRE_YTD',
152 'A_EIC_ADVANCE_PER_GRE_YTD',
153 'A_W2_DEPENDENT_CARE_PER_GRE_YTD',
154 'A_W2_401K_PER_GRE_YTD',
155 'A_W2_403B_PER_GRE_YTD',
156 'A_W2_408K_PER_GRE_YTD',
157 'A_W2_457_PER_GRE_YTD',
158 'A_W2_501C_PER_GRE_YTD',
159 'A_W2_MILITARY_HOUSING_PER_GRE_YTD',
160 'A_W2_NONQUAL_PLAN_PER_GRE_YTD',
161 'A_W2_NONQUAL_457_PER_GRE_YTD',
162 'A_W2_BOX_11_PER_GRE_YTD',
163 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD',
164 'A_FIT_3RD_PARTY_PER_GRE_YTD',
165 'A_W2_NONQUAL_STOCK_PER_GRE_YTD',
166 'A_W2_HSA_PER_GRE_YTD',
167 'A_W2_NONTAX_COMBAT_PER_GRE_YTD',
168 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD',
169 'A_W2_BOX_8_PER_GRE_YTD',
170 /* Sum of */
171 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD',
175 'A_W2_ADOPTION_PER_GRE_YTD',
172 'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD',
173 'A_W2_MSA_PER_GRE_YTD',
174 'A_W2_408P_PER_GRE_YTD',
176 'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD',
177 'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD',
178 'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD',
179 'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD',
180 'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD',
181 'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD',
182 'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD'
183 , 'A_W2_ROTH_401K_PER_GRE_YTD'
184 , 'A_W2_ROTH_403B_PER_GRE_YTD'
185 )
186 group by fue.user_entity_name;
187
188 CURSOR c_ter(cp_tax_unit_id number) IS
189 SELECT
190 fue.user_entity_name,decode(fue.user_entity_name,
191 'A_SIT_WITHHELD_PER_JD_GRE_YTD',nvl(sum(value),0) * 100,
192 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',nvl(sum(value),0) * 100,
193 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',nvl(sum(value),0) * 100,
194 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD',nvl(sum(value),0) * 100
195 ) val
196 FROM ff_archive_item_contexts faic
197 ,ff_archive_items fai
198 ,pay_assignment_actions paa
199 ,pay_payroll_actions ppa
200 ,pay_action_interlocks pai
201 ,ff_user_entities fue
202 WHERE
203 ppa.report_type = 'W2'
204 and ppa.report_qualifier = 'FED'
205 and ppa.effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
206 and paa.payroll_action_id = ppa.payroll_action_id
207 and paa.assignment_action_id = pai.locking_action_id
208 and fai.context1 = pai.locked_action_id
209 and context = '72-000-0000'
210 and fai.archive_item_id = faic.archive_item_id
211 and fai.user_entity_id = fue.user_entity_id
212 and paa.tax_unit_id = cp_tax_unit_id
213 and fue.user_entity_name in ( 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
214 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
215 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
216 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD')
217 and paa.action_status = 'C'
218 group by fue.user_entity_name;
219
220
221 CURSOR c_ro_count ( cp_tax_unit_id number) IS
222 select count(*)
223 from pay_payroll_actions ppa
224 ,pay_assignment_actions paa
225 ,ff_archive_items fai
226 where ppa.report_type = 'W2'
227 and ppa.report_qualifier = 'FED'
228 and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
229 and ppa.payroll_action_id = paa.payroll_action_id
230 and paa.assignment_action_id = fai.context1
231 and name is not null
232 and name like 'TRANSFER_RO_TOTAL'
233 and paa.tax_unit_id = cp_tax_unit_id
234 group by tax_unit_id;
235
236 l_date date;
237 l_tax_unit_id varchar2(10);
238 l_total_emp number;
239 l_er_sum er_sum_table;
240 l_fit_with varchar2(20);
241 l_ss_ee_taxable varchar2(20);
242 l_ro_count number;
243 l_direct_fed_bal_call VARCHAR2(2) := 'N';
244
245 BEGIN
246
247 a1 := '0';
248 a2 := '0';
249 a3 := '0';
250 a4 := '0';
251 a5 := '0';
252 a6 := '0';
253 a7 := '0';
254 a8 := '0';
255 a9 := '0';
256 a10:= '0';
257 a11 := '0';
258 a12 := '0';
259 a13 := '0';
260 a14 := '0';
261 a15 := '0';
262 a16 := '0';
263 a17 := '0';
264 a18 := '0';
265 a19 := '0';
266 a20 := '0';
267 a21 := '0';
268 a22 := '0';
269 a23 := '0';
270 a24 := '0';
271 a25 := '0';
272 a26 := '0';
273 a27 := '0';
274 a28 := '0';
275 a29 := '0';
276 a30 := '0';
277 a31 := '0';
278 a32 := '0';
279 a33 := '0';
280 a34 := '0';
281 a35 := '0';
282 a36 := '0';
283 a37 := '0';
284 a38 := '0';
285 a39 := '0';
286 a40 := '0';
287 a41 := '0';
288 a42 := '0';
289 a43 := '0';
290 a44 := '0';
291 a45 := '0';
292 a46 := '0';
293 a47 := '0';
294
295
296 OPEN C_EMP_COUNT(to_number(W2_tax_unit_id));
300 END IF;
297 FETCH C_EMP_COUNT INTO a1;
298 IF C_EMP_COUNT%NOTFOUND THEN
299 a1 := 0;
301 CLOSE C_EMP_COUNT;
302
303 l_direct_fed_bal_call := nvl(fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES'),'N'); /*Added for Bug#13326069*/
304 /*Added for Bug#13614766*/
305 IF l_direct_fed_bal_call = 'Y' THEN
306
307 l_direct_fed_bal_call := hr_us_ff_udfs.direct_fed_data_archived(w2_year);
308
309 END IF;
310
311 /*For Bug#13326069, Made changes to calculate Federal Gross Wages based on Direct US Federal Balances Profile Option*/
312
313 FOR I IN C_ER_SUM(to_number(W2_TAX_UNIT_ID)) LOOP
314
315 if I.user_entity_name = 'A_REGULAR_EARNINGS_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
316 a2 := a2 + i.val;
317 ELSIF I.user_entity_name = 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
318 a2 := a2 + i.val;
319 ELSIF I.user_entity_name = 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
320 a2 := a2 + i.val;
324 a2 := a2 + i.val;
321 ELSIF I.user_entity_name = 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
322 a2 := a2 + i.val;
323 ELSIF I.user_entity_name = 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
325 ELSIF I.user_entity_name = 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
326 a2 := a2 - i.val;
327 ELSIF I.user_entity_name = 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD' AND l_direct_fed_bal_call='Y' THEN
328 a2 := a2 + i.val;
329 ELSIF I.user_entity_name = 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD' AND l_direct_fed_bal_call='Y' THEN
330 a2 := a2 + i.val;
331 ELSIF I.user_entity_name = 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD' AND l_direct_fed_bal_call='Y' THEN
332 a2 := a2 - i.val;
333 ELSIF I.user_entity_name = 'A_FIT_WITHHELD_PER_GRE_YTD'THEN
334 a3 := i.val;
335 ELSIF i.user_entity_name = 'A_SS_EE_TAXABLE_PER_GRE_YTD'THEN
336 a4 := i.val;
337 ELSIF i.user_entity_name ='A_SS_EE_WITHHELD_PER_GRE_YTD' THEN
338 a5 := i.val;
339 ELSIF i.user_entity_name = 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD' THEN
340 a6 := i.val;
341 ELSIF i.user_entity_name = 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD' THEN
342 a7 := i.val;
343 ELSIF i.user_entity_name = 'A_W2_BOX_7_PER_GRE_YTD' THEN
344 a8 := i.val;
345 ELSIF i.user_entity_name = 'A_EIC_ADVANCE_PER_GRE_YTD' THEN
346 a9 := i.val;
347 ELSIF i.user_entity_name = 'A_W2_DEPENDENT_CARE_PER_GRE_YTD' THEN
348 a10 := i.val;
349 ELSIF i.user_entity_name = 'A_W2_401K_PER_GRE_YTD' THEN
350 a11 := i.val;
351 ELSIF i.user_entity_name = 'A_W2_403B_PER_GRE_YTD' THEN
352 a12 := i.val;
353 ELSIF i.user_entity_name = 'A_W2_408K_PER_GRE_YTD' THEN
354 a13 := i.val;
355 ELSIF i.user_entity_name = 'A_W2_457_PER_GRE_YTD' THEN
356 a14 := i.val;
357 ELSIF i.user_entity_name = 'A_W2_501C_PER_GRE_YTD' THEN
358 a15 := i.val;
359 ELSIF i.user_entity_name = 'A_W2_MILITARY_HOUSING_PER_GRE_YTD' THEN
360 a16 := i.val;
361 ELSIF i.user_entity_name = 'A_W2_NONQUAL_457_PER_GRE_YTD' THEN
362 a17:= i.val;
363 ELSIF i.user_entity_name = 'A_W2_BOX_11_PER_GRE_YTD' THEN
364 a18 := i.val;
365 ELSIF i.user_entity_name = 'A_W2_HSA_PER_GRE_YTD' THEN
366 a19 := i.val;
367 ELSIF i.user_entity_name = 'A_W2_NONQUAL_PLAN_PER_GRE_YTD' THEN
368 a20 := i.val;
369 ELSIF i.user_entity_name = 'A_W2_NONTAX_COMBAT_PER_GRE_YTD' THEN
370 a21 := i.val;
371 ELSIF i.user_entity_name = 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' THEN
372 a22:= i.val;
373 ELSIF i.user_entity_name = 'A_FIT_3RD_PARTY_PER_GRE_YTD' THEN
374 a23 := i.val;
375 ELSIF i.user_entity_name = 'A_W2_NONQUAL_STOCK_PER_GRE_YTD' THEN
376 a24 := i.val;
377 ELSIF i.user_entity_name = 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD' THEN
378 a25 := i.val;
379 ELSIF i.user_entity_name ='A_W2_BOX_8_PER_GRE_YTD' THEN
380 a26 := i.val;
381 ELSIF i.user_entity_name = 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD' THEN
382 a27 := a27 + i.val;
383 ELSIF i.user_entity_name ='A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD' THEN
384 a27 := a27 + i.val;
385 ELSIF i.user_entity_name ='A_W2_MSA_PER_GRE_YTD' THEN
386 a28 := i.val;
387 ELSIF i.user_entity_name ='A_W2_408P_PER_GRE_YTD' THEN
388 a29 := i.val;
389 ELSIF i.user_entity_name ='A_W2_ADOPTION_PER_GRE_YTD' THEN
390 a30 := i.val;
391 ELSIF i.user_entity_name ='A_W2_UNCOLL_SS_GTL_PER_GRE_YTD' THEN
392 a31 := i.val;
393 ELSIF i.user_entity_name ='A_W2_UNCOLL_MED_GTL_PER_GRE_YTD' THEN
394 a32 := i.val;
395 ELSIF i.user_entity_name ='A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD' THEN
396 a33 := i.val;
397 ELSIF i.user_entity_name ='A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD' THEN
398 a34 := i.val;
399 ELSIF i.user_entity_name ='A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD' THEN
400 a35 := i.val;
401 ELSIF i.user_entity_name ='A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD' THEN
402 a36 := i.val;
403 ELSIF i.user_entity_name ='A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD' THEN
404 a37 := i.val;
405 ELSIF i.user_entity_name ='A_W2_ROTH_401K_PER_GRE_YTD' THEN
406 a46 := i.val;
407 ELSIF i.user_entity_name ='A_W2_ROTH_403B_PER_GRE_YTD' THEN
408 a47 := i.val;
409 END IF;
410
411 IF i.user_entity_name = 'A_W2_NONQUAL_457_PER_GRE_YTD' THEN
412 a20 := a20 - i.val;
413 END IF;
414 END LOOP;
415
416 OPEN c_ro_count(to_number(W2_TAX_UNIT_ID));
417 FETCH c_ro_count INTO l_ro_count;
418 CLOSE c_ro_count;
419 a38 := to_char(nvl(l_ro_count,0));
420
421 IF l_ro_count > 0 THEN
422
423 FOR J IN c_ter(to_number(W2_TAX_UNIT_ID)) LOOP
424 if J.user_entity_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' THEN
425 a39 := a39 + J.val;
426 ELSIF J.user_entity_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD' THEN
427 a39 := a39 + J.val;
431 a40 := J.val;
428 ELSIF J.user_entity_name = 'A_SIT_PRE_TAX_REDNS_PER_GRE_YTD' THEN
429 a39 := a39 - J.val;
430 ELSIF J.user_entity_name = 'A_SIT_WITHHELD_PER_JD_GRE_YTD' THEN
432 END IF;
433 END LOOP;
434 END IF ;
435
436 a41 := to_number(a39) - to_number(a37) - to_number(a35) - to_number(a36);
437
438 return '0' ;
439 END get_w2_er_arch_bal;
440 end pay_us_multi_mmrf;