1 PACKAGE body hr_us_w2_rep AS
2 /* $Header: pyusw2pg.pkb 120.9.12020000.3 2012/11/01 17:28:24 emunisek ship $ */
3
4 /*
5 +=====================================================================+
6 | Copyright (c) 1997 Orcale Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=====================================================================+
10 Name : pyusw2pg.pkb
11 Description : This package declares functions and procedures which are
12 used to return values for the W2 US Payroll reports.
13
14 Change List
15 -----------
16
17 Version Date Author ER/CR No. Description of Change
18 -------+---------+------------+---------+--------------------------
19 40.0 13-MAY-98 SSarma Date Created
20 40.1 18-AUG-98 ahanda modified packaged
21 40.2 18-AUG-98 ahanda added condition for 1099R
22 40.5 15-jan-99 ssarma Added logic for A_SPL_CITY_LOCAL_WAGES,
23 A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD
24 40.6 21-JAN-99 ahanda Removed the check for Jurisdiction Code
25 length.
26 40.8/ 22-JAN-99 achauhan Added logic to bypass gross for bouroughs
27 110.4 if the withheld is zero.
28 115.1 23-APR-99 scgrant Multi-radix changes.
29 115.6 10-may-99 iharding removed set serveroutput on
30 115.7 08-AUG-99 ssarma Added functions get_w2_tax_unit_item,
31 get_tax_unit_addr_line,get_tax_unit_bg,
32 get_per_item,get_state_item for eoy99.
33 115.9 16-Sep-99 skutteti Pre-tax enhancements
34 115.10 10-Aug-01 kthirmiy added a new function get_leav_reason to get the
35 termination reason meaning to fix the bug 1482168.
36 used fnd_lookup_values in the function
37 instead of fnd_common_lookups because
38 of release 115
39 115.15 07-SEP-01 ssarma Fix for 1977767.
40 115.18 16-SEP-01 ssarma Overloaded function get_w2_box_15
41 115.19 17-SEP-01 ssarma Removed default for effective date from
42 function get_w2_box_15.
43 115.20 29-NOV-01 meshah Fix for 2125750. adding
44 A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_
45 TO_TAX_PER_GRE_YTD
46 to the deduction calculation for A_WAGES.
47 115.21 30-NOV-01 meshah add dbdrv.
48 115.22 10-DEC-01 meshah adding
49 A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_
50 TO_TAX_PER_GRE_YTD
51 and A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD
52 to the earnings calculation for A_WAGES
53 not deductions.
54 115.23 17-DEC-02 fusman 2380518 Changed the hr_locations to hr_locations_all for
55 Employer address.
56 115.24 18-JUL-02 kthirmiy 2465183 Changed from p_per_item per_people_f.
57 middle_names%type
58 to per_people_f.first_name%type
59 for bug 2465183 because of UTF8 the length
60 has been increased
61 115.25 06-AUG-02 ppanda 2145804 Procedure get_county_tax_info added
62 2207317 Procedures fetches County Tax info for
63 tax computation
64
65 2287844 Currently SS Wages includes Tips which is
66 reported in Box-7 In order to report
67 correctly the SS Wages, SS Tips should be
68 subtracted from SS Wages.
69
70 2400545 For NY states State wages must be equal to
71 Federal wages
72 when a taxpapayer has state tax withholding
73 for any part of the tax year.
74
75 2505076 This is fix for Yonker City of NY state,
76 which requires City wages
77 to match with Fed wages when taxpayer
78 has yonker City tax withheld
79 115.26 10-SEP-2002 kthirmiy 1992073 Added a new procedure get_agent_tax_unit_id for
80 Agent reporting enhancement
81 Note that the message will take only
82 45 characters in the pyugen
83 process to display.
84 115.28 11-SEP-2002 kthirmiy Added Both in the error message
85 115.30 12-SEP-2002 kthirmiy Changed to 2678 Filer instead of Agent
86 in the error mesg
87 115.31 12-SEP-2002 ahanda Changed 2678 Filer to only pick up
88 non 1099R GREs
89 115.32 17-SEP-2002 kthirmiy Changed the Error mesg bug 2573499
90 115.35 18-SEP-2002 irgonzal 2577109 Modified get_agent_tax_unit_id procedure.
91 Added following conditions:
92 a) if only one 2678 Filer GRE is found,
93 only this GRE should be the W2
94 Transmitter. b) Only one 2678 Filer
95 GRE can exist within a BG
96 115.36 20-SEP-2002 irgonzal Modifed error message for bug 2577109.
97 115.37 20-SEP-2002 irgonzal Modified get_agent_tax_unit_id procedure.
98 Ensured error message does not exceed 100 chrs.
99 115.38 13-Nov-2002 fusman 2625264 Checked the optional reporting
100 parameter of fed wages in state wages
101 for NY
102 115.39 13-Nov-2002 fusman Moved the PL/SQL declaration to package header.
103
104 115.41 02-DEC-2002 asasthan nocopy changes for gscc compliance.
105 115.42 20-JAN-2003 jgoswami Modified the A_W2_GROSS_1099R code to
106 get correct gross for 1099r paper,1099r
107 register and view:PAY_US_WAGES_1099R_V.
108 115.45 12-AUG-2003 rsethupa 2631650 Rolled back the changes introduced in
109 version 115.44
110 115.46 26-AUG-2003 meshah Added in a new function
111 get_w2_box17_label. This function is
112 called from the pay_us_locality_w2_v.
113 115.47 07-JAN-2004 ahanda 3347942 Added 'A_FIT_3RD_PARTY_PER_GRE_YTD' to
114 get_w2_box_15
115 115.48 28-JUL-2004 rsethupa 3347948 Removed 'A_FIT_3RD_PARTY_PER_GRE_YTD'
116 from get_w2_box_15. Will use only
117 A_W2_TP_SICK_PAY_PER_GRE_YTD for Sick
118 Pay Indicator
119 115.49 13-Aug-2004 meshah 3725848 Now checking for 26-000-0690 (Kansas
120 City) jurisdiction code in
121 A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD
122 and A_SPL_CITY_LOCAL_WAGES.
123 pay_us_locality_w2_v will also change.
124
125 115.50 23-Jan-2006 sausingh 5748431 Added two extra conditions for checking
126 the box 13b in case of designated roth
127 contribution under section 401(k) plan
128 and under section 403(b) plan.
129 115.25 27-SEP-2007 sausingh 5517938 Added a new function get_last_deffer_year
130 to display first year of designated roth
131 contribution
132
133 115.26 08-jan-2008 psugumar 5855662 Added a new functions get_w2_location_cd
134 get_w2_worker_compensation
135 get_w2_employee_number
136 to display new information required for Bug #5855662
137 115.55 14-Sep-2009 kagangul 8353425 Added a new function get_w2_employee_name.
138 115.58 14-Nov-2011 emunisek 13326069 Made changes to function get_w2_arch_bal to calculate
139 FIT Wages using the Direct Balances approach.
140 115.59 25-Jan-2012 emunisek 13614766 Added change to check the Data Archived before using
141 Direct Balance approach
142 115.60 01-Nov-2012 emunisek 14385437 Added changes to check the value set for Profile
143 Option 'PAY_US_DIRECT_BALANCE_START_YEAR' to use
144 the Direct US Federal Balances approach.
145 =============================================================================
146
147 */
148
149 FUNCTION get_w2_bal_amt (w2_asg_act_id number,
150 w2_balance_name varchar2,
151 w2_tax_unit_id varchar2,
152 w2_jurisdiction_code varchar2,
153 w2_jurisdiction_level number) RETURN NUMBER
154 IS
155 l_user_entity_id number;
156 l_bal_amt number := 0;
157 l_tax_context_id number := 0;
158 l_jd_context_id number := 0;
159
160 BEGIN
161
162 --dbms_output.put_line('inside get_w2_bal_amt');
163
164 l_user_entity_id := get_user_entity_id(w2_balance_name);
165 l_jd_context_id := hr_us_w2_rep.get_context_id('JURISDICTION_CODE');
166 l_tax_context_id := hr_us_w2_rep.get_context_id('TAX_UNIT_ID');
167
168
169 if w2_tax_unit_id is not null then
170 if w2_jurisdiction_code <> '00-000-0000' then
171 --dbms_output.put_line('got jd and gre as not null ');
172 select nvl(fnd_number.canonical_to_number(fai.value),0) into l_bal_amt
173 from ff_archive_items fai,
174 ff_archive_item_contexts fic1,
175 ff_archive_item_contexts fic2
176 where fai.context1 = w2_asg_act_id
177 and fai.user_entity_id = l_user_entity_id
178 and fai.archive_item_id = fic1.archive_item_id
179 and fic1.context_id = l_tax_context_id
180 and ltrim(rtrim(fic1.context)) = w2_tax_unit_id
181 and fai.archive_item_id = fic2.archive_item_id
182 and fic2.context_id = l_jd_context_id
183 and substr(ltrim(rtrim(fic2.context)),1,w2_jurisdiction_level) = substr(w2_jurisdiction_code,1,w2_jurisdiction_level);
184 else
185 --dbms_output.put_line('got jd as null and gre as not null ');
186 select nvl(fnd_number.canonical_to_number(fai.value),0) into l_bal_amt
187 from ff_archive_items fai,
188 ff_archive_item_contexts fic
189 where fai.context1 = w2_asg_act_id
190 and fai.user_entity_id = l_user_entity_id
191 and fai.archive_item_id = fic.archive_item_id
192 and fic.context_id = l_tax_context_id
193 and ltrim(rtrim(fic.context)) = w2_tax_unit_id;
194 end if;
195 else
196 if w2_jurisdiction_code <> '00-000-0000' then
197 --dbms_output.put_line('got jd as not null and gre as null ');
198 select nvl(fnd_number.canonical_to_number(fai.value),0) into l_bal_amt
199 from ff_archive_items fai,
200 ff_archive_item_contexts fic
201 where fai.context1 = w2_asg_act_id
202 and fai.user_entity_id = l_user_entity_id
203 and fai.archive_item_id = fic.archive_item_id
204 and fic.context_id = l_jd_context_id
205 and substr(ltrim(rtrim(fic.context)),1,w2_jurisdiction_level) = substr(w2_jurisdiction_code,1,w2_jurisdiction_level);
206 else
207 --dbms_output.put_line('got jd and gre as null ');
208 select nvl(fnd_number.canonical_to_number(fai.value),0) into l_bal_amt
209 from ff_archive_items fai
210 where fai.context1 = w2_asg_act_id
211 and fai.user_entity_id = l_user_entity_id;
212 end if;
213 end if;
214
215 return(l_bal_amt);
216
217 EXCEPTION
218 when no_data_found then
219 return(0);
220
221 END get_w2_bal_amt;
222
223 FUNCTION get_user_entity_id (w2_balance_name in varchar2)
224 RETURN NUMBER
225 IS
226 l_user_entity_id number := 0;
227
228 BEGIN
229 select fdi.user_entity_id into l_user_entity_id
230 from ff_database_items fdi,
231 ff_user_entities fue
232 where user_name = w2_balance_name
233 and fdi.user_entity_id = fue.user_entity_id
234 and fue.legislation_code = 'US';
235
236 --dbms_output.put_line('got user_entity_id = ' || to_char(l_user_entity_id));
237 return (l_user_entity_id);
238
239 EXCEPTION
240 when no_data_found then
241 return(-1);
242
243 END get_user_entity_id;
244
245 FUNCTION get_context_id (w2_context_name in varchar2)
246 RETURN NUMBER
247 IS
248 l_context_id number := 0;
249 BEGIN
250 select context_id into l_context_id
251 from ff_contexts
252 where context_name = w2_context_name;
253
254 return (l_context_id);
255
256 EXCEPTION
257 WHEN NO_DATA_FOUND THEN
258 return(-1);
259
260 END get_context_id;
261
262 FUNCTION get_w2_arch_bal(w2_asg_act_id number,
263 w2_balance_name varchar2,
264 w2_tax_unit_id number ,
265 w2_jurisdiction_code varchar2 ,
266 w2_jurisdiction_level number) RETURN NUMBER IS
267
268 CURSOR get_reporting_year (p_assign_action_id NUMBER) IS
269 SELECT TO_CHAR(effective_date,'YYYY')
270 FROM pay_assignment_actions paa,pay_payroll_actions ppa
271 WHERE ppa.payroll_action_id = paa.payroll_action_id
272 AND paa.assignment_action_id = p_assign_action_id;
273
274 CURSOR check_archive_data (p_assign_action_id NUMBER) IS
275 SELECT 'Y'
276 FROM FF_ARCHIVE_ITEMS FAI,
277 FF_DATABASE_ITEMS FDI
278 WHERE p_assign_action_id = FAI.CONTEXT1
279 AND FAI.USER_ENTITY_ID = FDI.USER_ENTITY_ID
280 AND FDI.USER_NAME IN
281 ('A_FIT_SUBJ_WHABLE_PER_GRE_YTD',
282 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',
283 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD')
284 AND ROWNUM = 1;
285
286 TYPE numeric_table IS TABLE OF number(17,2)
287 INDEX BY BINARY_INTEGER;
288
289 TYPE text_table IS TABLE OF varchar2(2000)
290 INDEX BY BINARY_INTEGER;
291
292 g_user_name text_table;
293 g_element_value numeric_table;
294
295 l_jursd_tbl text_table;
296 l_count number := 0;
297 l_user_entity_id number :=0;
298 l_earnings number :=0;
299 l_deductions number :=0;
300 bal_amt number :=0;
301 l_amt number := 0;
302 l_withheld number := 0;
303 l_city_tax_withheld number := 0;
304 l_direct_fed_bal_call varchar2(2) := 'N';
305 l_archived_data varchar2(2) := 'N';
306 /* Added for Bug#14385437 */
307 l_year varchar2(4);
308 l_direct_bal_year varchar2(4);
309 /* End of changes for Bug#14385437 */
310
311
312 FUNCTION get_ny_fed_state_wage_match (p_w2_tax_unit_id in number)
313 RETURN varchar2
314
315 IS
316 cursor c_ny_st_match_fed (cp_tax_unit_id in number)
317 IS
318 select nvl(hoi.org_information1, 'Y')
319 from hr_organization_information hoi,
320 hr_organization_units hou
321 where hoi.organization_id = hou.business_group_id
322 and hou.organization_id = cp_tax_unit_id
323 and hoi.org_information_context = 'US State Tax Info';
324
325 l_ny_st_match_fed varchar2(1) := 'Y';
326 l_ny_bg_found boolean := FALSE;
327
328 l_index NUMBER;
329
330 BEGIN
331 if ltr_newyork_tax_table.count > 0 then
332 for j in ltr_newyork_tax_table.first .. ltr_newyork_tax_table.last loop
333
334 IF ltr_newyork_tax_table(j).tax_unit_id = w2_tax_unit_id THEN
335 l_ny_st_match_fed := ltr_newyork_tax_table(j).tax_value;
336 l_ny_bg_found := TRUE;
337 exit;
338 END IF;
339 end loop;
340 end if;
341
342 IF NOT l_ny_bg_found THEN --l_bg_found checking
343 OPEN c_ny_st_match_fed(p_w2_tax_unit_id);
344 FETCH c_ny_st_match_fed into l_ny_st_match_fed;
345 CLOSE c_ny_st_match_fed;
346
347 l_index := ltr_newyork_tax_table.count;
348 ltr_newyork_tax_table(l_index).tax_unit_id := w2_tax_unit_id;
349 ltr_newyork_tax_table(l_index).tax_value := l_ny_st_match_fed;
350 END IF;
351
352 return (l_ny_st_match_fed);
353 END get_ny_fed_state_wage_match;
354
355 BEGIN
356 /*Added for Bug#13326069*/
357 l_direct_fed_bal_call := nvl(fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES'),'N');
358
359 /*Added for Bug#13614766*/
360 if l_direct_fed_bal_call = 'Y' then
361
362 /* Added for Bug#14385437 */
363
364 open get_reporting_year(w2_asg_act_id);
365 fetch get_reporting_year INTO l_year;
366 close get_reporting_year;
367
368 hr_utility.trace('Year from Assignment Action ID: '||l_year);
369
370 l_direct_bal_year := NVL(fnd_profile.value('PAY_US_DIRECT_BALANCE_START_YEAR'),'0001');
371
372 hr_utility.trace('Starting Year for Direct US Federal Balances: '||l_direct_bal_year);
373
374 if l_direct_bal_year > l_year THEN
375
376 l_direct_fed_bal_call := 'N';
377
378 else
379
380 open check_archive_data(w2_asg_act_id);
381 fetch check_archive_data INTO l_archived_data;
382 close check_archive_data;
383
384 l_direct_fed_bal_call := l_archived_data;
385
386 end if;
387
388 end if;
389
390 for i in 1..50 loop
391 g_element_value(i) := 0;
392 end loop;
393
394 for i in 1..50 loop
395 l_jursd_tbl(i) := null;
396 end loop;
397
398 -- pay_us_balance_view_pkg.debug_msg('FUNCTION : get_w2_arch_bal ');
399 -- pay_us_balance_view_pkg.debug_msg('Assignment Action Id : '||to_char(w2_asg_act_id));
400 -- pay_us_balance_view_pkg.debug_msg('Balance Name : '||w2_balance_name);
401
402 if w2_balance_name = 'A_WAGES' then
403
404 /*Added for Bug#13326069*/
405 if l_direct_fed_bal_call = 'Y' then
406
407 g_user_name(1) := 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD';
408 g_user_name(2) := 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD';
409 g_user_name(3) := 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD';
410
411 for i in 1..3 loop
412 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
413 g_user_name(i),
414 to_char(w2_tax_unit_id),
415 '00-000-0000',
416 w2_jurisdiction_level);
417 end loop;
418
419 l_earnings := 0;
420 l_deductions := 0;
421
422 for i in 1..2 loop
423 l_earnings := l_earnings + g_element_value(i);
424 end loop;
425
426 for i in 3..3 loop
427 l_deductions := l_deductions + g_element_value(i);
428 end loop;
429
430 bal_amt := l_earnings - l_deductions;
431 /*End of changes for Bug#13326069*/
432 else
433
434 g_user_name(1) := 'A_REGULAR_EARNINGS_PER_GRE_YTD';
435 g_user_name(2) := 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD';
436 g_user_name(3) := 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD';
437 g_user_name(4) := 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD';
438 g_user_name(5) := 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD';
439 g_user_name(6) := 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD';
440
441 for i in 1..6 loop
442 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
443 g_user_name(i),
444 to_char(w2_tax_unit_id),
445 '00-000-0000',
446 w2_jurisdiction_level);
447 end loop;
448
449 l_earnings := 0;
450 l_deductions := 0;
451
452 for i in 1..5 loop
453 l_earnings := l_earnings + g_element_value(i);
454 end loop;
455
456 for i in 6..6 loop
457 l_deductions := l_deductions + g_element_value(i);
458 end loop;
459
460 bal_amt := l_earnings - l_deductions;
461
462 end if;
463
464 elsif w2_balance_name = 'A_W2_STATE_WAGES' then
465
466 g_user_name(1) := 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD';
467 g_user_name(2) := 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD';
468 g_user_name(3) := 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD';
469
470 for i in 1..3 loop
471 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
472 g_user_name(i),
473 to_char(w2_tax_unit_id),
474 w2_jurisdiction_code,
475 w2_jurisdiction_level);
476 end loop;
477
478 l_earnings := 0;
479 l_deductions := 0;
480
481 for i in 1..2 loop
482 l_earnings := l_earnings + g_element_value(i);
483 end loop;
484
485 for i in 3..3 loop
486 l_deductions := l_deductions + g_element_value(i);
487 end loop;
488
489 bal_amt := l_earnings - l_deductions;
490
491 --
492 -- This is to fix Bug # 2400545
493 -- Start for the Fix
494 -- For NY states State wages must be equal to Federal wages
495 -- when a taxpapayer has state tax withholding
496 -- for anypart of the tax year.
497 if substr(w2_jurisdictioN_code,1,2) = '33' then -- NY testing
498
499 if get_ny_fed_state_wage_match(w2_tax_unit_id) = 'Y' THEN
500 if bal_amt <> 0 then
501 bal_amt := hr_us_w2_rep.get_w2_arch_bal(
502 w2_asg_act_id,
503 'A_WAGES',
504 w2_tax_unit_id,
505 '00-000-0000',0);
506 end if;
507 end if;
508
509 end if; -- NY checking
510
511
512 elsif w2_balance_name = 'A_CITY_LOCAL_WAGES' then
513
514 g_user_name(1) := 'A_CITY_SUBJ_WHABLE_PER_JD_GRE_YTD';
515 g_user_name(2) := 'A_CITY_SUBJ_NWHABLE_PER_JD_GRE_YTD';
516 g_user_name(3) := 'A_CITY_PRE_TAX_REDNS_PER_JD_GRE_YTD';
517
518 for i in 1..3 loop
519 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
520 g_user_name(i),
521 to_char(w2_tax_unit_id),
522 w2_jurisdiction_code,
523 w2_jurisdiction_level);
524 end loop;
525
526 l_earnings := 0;
527 l_deductions := 0;
528
529 for i in 1..2 loop
530 l_earnings := l_earnings + g_element_value(i);
531 end loop;
532
533 for i in 3..3 loop
534 l_deductions := l_deductions + g_element_value(i);
535 end loop;
536
537 bal_amt := l_earnings - l_deductions;
538
539 -- This is fix for Bug # 2505076
540 -- Where for Yonker City of NY state requires City wages to
541 -- match with Fed wages when taxpayer has yonker City tax withheld
542 -- Start fix for Bug # 2505076
543 --
544 if w2_jurisdiction_code = '33-119-3230' then
545 -- When City jurisdiction is Yonkers derive city tax withheld
546 --
547 l_city_tax_withheld := hr_us_w2_rep.get_w2_arch_bal(
548 w2_asg_act_id,
549 'A_CITY_WITHHELD_PER_JD_GRE_YTD' ,
550 to_char(w2_tax_unit_id),
551 w2_jurisdiction_code,
552 w2_jurisdiction_level);
553 -- When City Tax withheld is Greater than Zero derive
554 -- Fed wages and assign to City Wages
555 --
556 if get_ny_fed_state_wage_match(w2_tax_unit_id) = 'Y' then
557 if l_city_tax_withheld > 0 then
558 bal_amt := hr_us_w2_rep.get_w2_arch_bal(
559 w2_asg_act_id,
560 'A_WAGES',
561 w2_tax_unit_id,
562 '00-000-0000',0);
563 end if;
564 end if;
565 end if;
566
567
568 elsif w2_balance_name = 'A_SPL_CITY_LOCAL_WAGES' then
569
570 if w2_jurisdiction_code = '33-000-2010' then
571
572 l_jursd_tbl(1) := '33-005-2010';
573 l_jursd_tbl(2) := '33-047-2010';
574 l_jursd_tbl(3) := '33-061-2010';
575 l_jursd_tbl(4) := '33-081-2010';
576 l_jursd_tbl(5) := '33-085-2010';
577
578 l_count := 5;
579
580 end if;
581
582 if w2_jurisdiction_code = '26-000-0690' then
583 /* Kansas Missouri */
584 l_jursd_tbl(1) := '26-047-0690';
585 l_jursd_tbl(2) := '26-037-0690';
586 l_jursd_tbl(3) := '26-095-0690';
587 l_jursd_tbl(4) := '26-165-0690';
588
589 l_count := 4;
590
591 end if;
592 g_user_name(1) := 'A_CITY_SUBJ_WHABLE_PER_JD_GRE_YTD';
593 g_user_name(2) := 'A_CITY_SUBJ_NWHABLE_PER_JD_GRE_YTD';
594 g_user_name(3) := 'A_CITY_PRE_TAX_REDNS_PER_JD_GRE_YTD';
595
596 l_earnings := 0;
597 l_deductions := 0;
598
599 for j in 1..l_count loop
600
601 for i in 1..3 loop
602
603 l_withheld := hr_us_w2_rep.get_w2_bal_amt(
604 w2_asg_act_id,
605 'A_CITY_WITHHELD_PER_JD_GRE_YTD',
606 to_char(w2_tax_unit_id),
607 l_jursd_tbl(j),
608 w2_jurisdiction_level);
609 if l_withheld <= 0 then
610 g_element_value(i) := 0;
611 else
612 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(
613 w2_asg_act_id,
614 g_user_name(i),
615 to_char(w2_tax_unit_id),
616 l_jursd_tbl(j),
617 w2_jurisdiction_level);
618 end if;
619 end loop;
620
621
622 for i in 1..2 loop
623 l_earnings := l_earnings + g_element_value(i);
624 end loop;
625
626 for i in 3..3 loop
627 l_deductions := l_deductions + g_element_value(i);
628 end loop;
629
630 end loop;
631
632 bal_amt := l_earnings - l_deductions;
633
634 elsif w2_balance_name = 'A_COUNTY_LOCAL_WAGES' then
635
636 g_user_name(1) := 'A_COUNTY_SUBJ_WHABLE_PER_JD_GRE_YTD';
637 g_user_name(2) := 'A_COUNTY_SUBJ_NWHABLE_PER_JD_GRE_YTD';
638 g_user_name(3) := 'A_COUNTY_PRE_TAX_REDNS_PER_JD_GRE_YTD';
639
640 for i in 1..3 loop
641 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
642 g_user_name(i),
643 to_char(w2_tax_unit_id),
644 w2_jurisdiction_code,
645 w2_jurisdiction_level);
646 end loop;
647
648 l_earnings := 0;
649 l_deductions := 0;
650
651 for i in 1..2 loop
652 l_earnings := l_earnings + g_element_value(i);
653 end loop;
654
655 for i in 3..3 loop
656 l_deductions := l_deductions + g_element_value(i);
657 end loop;
658
659 bal_amt := l_earnings - l_deductions;
660
661 elsif w2_balance_name = 'A_SCHOOL_LOCAL_WAGES' then
662
663 g_user_name(1) := 'A_SCHOOL_SUBJ_WHABLE_PER_JD_GRE_YTD';
664 g_user_name(2) := 'A_SCHOOL_SUBJ_NWHABLE_PER_JD_GRE_YTD';
665 g_user_name(3) := 'A_SCHOOL_PRE_TAX_REDNS_PER_JD_GRE_YTD';
666
667 for i in 1..3 loop
668 g_element_value(i) := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
669 g_user_name(i),
670 to_char(w2_tax_unit_id),
671 w2_jurisdiction_code,
672 w2_jurisdiction_level);
673 end loop;
674
675 l_earnings := 0;
676 l_deductions := 0;
677
678 for i in 1..2 loop
679 l_earnings := l_earnings + g_element_value(i);
680 end loop;
681
682 for i in 3..3 loop
683 l_deductions := l_deductions + g_element_value(i);
684 end loop;
685
686 bal_amt := l_earnings - l_deductions;
687
688 elsif w2_balance_name = 'A_W2_GROSS_1099R' then
689
690 g_user_name(1) := 'A_GROSS_EARNINGS_PER_GRE_YTD';
691
692 bal_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
693 g_user_name(1),
694 to_char(w2_tax_unit_id),
695 '00-000-0000',
696 w2_jurisdiction_level);
697
698
699 elsif w2_balance_name = 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD' then
700
701 if w2_jurisdiction_code = '33-000-2010' then
702
703 l_jursd_tbl(1) := '33-005-2010';
704 l_jursd_tbl(2) := '33-047-2010';
705 l_jursd_tbl(3) := '33-061-2010';
706 l_jursd_tbl(4) := '33-081-2010';
707 l_jursd_tbl(5) := '33-085-2010';
708
709 l_count := 5;
710
711 end if;
712
713 if w2_jurisdiction_code = '26-000-0690' then
714 /* Kansas Missouri city */
715 l_jursd_tbl(1) := '26-047-0690';
716 l_jursd_tbl(2) := '26-037-0690';
717 l_jursd_tbl(3) := '26-095-0690';
718 l_jursd_tbl(4) := '26-165-0690';
719
720 l_count := 4;
721
722 end if;
723 bal_amt := 0;
724
725 for j in 1..l_count loop
726
727 l_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
728 'A_CITY_WITHHELD_PER_JD_GRE_YTD',
729 to_char(w2_tax_unit_id),
730 l_jursd_tbl(j),
731 w2_jurisdiction_level);
732
733 bal_amt := bal_amt + l_amt;
734
735 end loop;
736 --
737 -- This is to Fix the Social Security Wages Bug 2287844
738 --
739 elsif w2_balance_name = 'A_SS_EE_TAXABLE_PER_GRE_YTD' then
740 bal_amt := /* Social Security Wages */
741 hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
742 w2_balance_name,
743 to_char(w2_tax_unit_id),
744 w2_jurisdiction_code,
745 w2_jurisdiction_level)
746 -
747 /* Social Security Tips - Box 7 */
748 hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
749 'A_W2_BOX_7_PER_GRE_YTD',
750 to_char(w2_tax_unit_id),
751 w2_jurisdiction_code,
752 w2_jurisdiction_level);
753
754 elsif w2_balance_name = 'A_W2_401K_PER_GRE_YTD' then
755 bal_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
756 w2_balance_name,
757 to_char(w2_tax_unit_id),
758 w2_jurisdiction_code,
759 w2_jurisdiction_level)
760 -
761 get_w2_userra_bal(w2_asg_act_id,
762 w2_tax_unit_id ,
763 w2_jurisdiction_code ,
764 w2_jurisdiction_level ,
765 '401K');
766 elsif w2_balance_name = 'A_W2_403B_PER_GRE_YTD' then
767 bal_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
768 w2_balance_name,
769 to_char(w2_tax_unit_id),
770 w2_jurisdiction_code,
771 w2_jurisdiction_level)
772 -
773 get_w2_userra_bal(w2_asg_act_id,
774 to_char(w2_tax_unit_id),
775 w2_jurisdiction_code ,
776 w2_jurisdiction_level ,
777 '403B');
778 elsif w2_balance_name = 'A_W2_457_PER_GRE_YTD' then
779 bal_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
780 w2_balance_name,
781 to_char(w2_tax_unit_id),
782 w2_jurisdiction_code,
783 w2_jurisdiction_level)
784 -
785 get_w2_userra_bal(w2_asg_act_id,
786 to_char(w2_tax_unit_id),
787 w2_jurisdiction_code ,
788 w2_jurisdiction_level ,
789 '457');
790
791 --
792 -- For all other Archived Balance not conditionally computed
793 -- above is derived with the following
794
795 else
796 bal_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
797 w2_balance_name,
798 to_char(w2_tax_unit_id),
799 w2_jurisdiction_code,
800 w2_jurisdiction_level);
801 end if;
802
803 return(bal_amt);
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 return(0);
808
809 END; /* FUNCTION get_w2_arch_bal */
810
811
812 FUNCTION get_w2_organization_id(w2_asg_id in number, w2_effective_date in date)
813 RETURN NUMBER IS
814 l_org_id number;
815
816 BEGIN
817 -- pay_us_balance_view_pkg.debug_msg('FUNCTION : get_w2_organization_id ');
818 -- pay_us_balance_view_pkg.debug_msg('Assignment Id : '||to_char(w2_asg_id));
819 -- pay_us_balance_view_pkg.debug_msg('Effective Date : '|| to_char(w2_effective_date,'DD-MM-YYYY'));
820
821 select paf.organization_id
822 into l_org_id
823 from per_assignments_f paf
824 where paf.assignment_id = w2_asg_id
825 and w2_effective_date between paf.effective_start_date
826 and paf.effective_end_date;
827 return(l_org_id);
828 EXCEPTION
829 WHEN NO_DATA_FOUND THEN
830 return(-99999);
831 WHEN OTHERS THEN
832 return(-99999);
833 END; /* FUNCTION get_w2_organization_id */
834
835
836 FUNCTION get_w2_location_id(w2_asg_id in number, w2_effective_date in date)
837 RETURN NUMBER IS
838 l_loc_id number;
839
840 BEGIN
841 -- pay_us_balance_view_pkg.debug_msg('FUNCTION : get_w2_location_id ');
842 -- pay_us_balance_view_pkg.debug_msg('Assignment Id : '||to_char(w2_asg_id));
843 -- pay_us_balance_view_pkg.debug_msg('Effective Date : '|| to_char(w2_effective_date,'DD-MM-YYYY'));
844
845 select paf.location_id
846 into l_loc_id
847 from per_assignments_f paf
848 where paf.assignment_id = w2_asg_id
849 and w2_effective_date between paf.effective_start_date
850 and paf.effective_end_date;
851 return(l_loc_id);
852 EXCEPTION
853 WHEN NO_DATA_FOUND THEN
854 return(-99999);
855 WHEN OTHERS THEN
856 return(-99999);
857 END; /* FUNCTION get_w2_organization_id */
858
859
860 FUNCTION get_w2_postal_code(w2_person_id in number, w2_effective_date in date)
861 RETURN VARCHAR2 IS
862
863 l_postal_code per_addresses.postal_code%type;
864
865 BEGIN
866 -- pay_us_balance_view_pkg.debug_msg('FUNCTION : get_w2_postal_code ');
867 -- pay_us_balance_view_pkg.debug_msg('Person Id : '||to_char(w2_person_id));
868 -- pay_us_balance_view_pkg.debug_msg('Effective Date : '|| to_char(w2_effective_date,'DD-MM-YYYY'));
869 select pa.postal_code
870 into l_postal_code
871 from per_addresses pa
872 where pa.person_id = w2_person_id
873 and pa.primary_flag = 'Y'
874 and w2_effective_date between pa.date_from
875 and nvl(pa.date_to,w2_effective_date);
876 return(l_postal_code);
877 EXCEPTION
878 WHEN NO_DATA_FOUND THEN
879 return(00000);
880 WHEN OTHERS THEN
881 return(000000);
882 END; /* FUNCTION get_w2_organization_id */
883
884 FUNCTION get_w2_employee_name(w2_person_id IN NUMBER, w2_effective_date IN DATE)
885 RETURN VARCHAR2 IS
886
887 CURSOR c_w2_emp_name IS
888 SELECT ppf.last_name|| ' ' || ppf.first_name || ' ' || substr(ppf.middle_names,1,1) emp_name
889 FROM per_all_people_f ppf
890 WHERE ppf.person_id = w2_person_id
891 AND w2_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
892
893 c_w2_emp_name_rec c_w2_emp_name%ROWTYPE;
894
895 BEGIN
896
897 OPEN c_w2_emp_name;
898 FETCH c_w2_emp_name INTO c_w2_emp_name_rec;
899 CLOSE c_w2_emp_name;
900
901 RETURN c_w2_emp_name_rec.emp_name;
902
903 END;
904
905
906 FUNCTION get_w2_state_ein ( w2_tax_unit_id in number,
907 w2_state_abbrev in varchar2)
908 RETURN varchar2 IS
909
910 CURSOR my_cursor IS
911 select ORG_INFORMATION3
912 from hr_organization_information
913 where organization_id = w2_tax_unit_id
914 and org_information_context = 'State Tax Rules'
915 and org_information1 = w2_state_abbrev;
916
917
918 state_id hr_organization_information.ORG_INFORMATION3%TYPE;
919
920 BEGIN
921 --
922 -- Get Employee State ID No for Box 16
923 --
924
925
926 OPEN my_cursor;
927 FETCH my_cursor INTO state_id;
928 CLOSE my_cursor;
929
930 return(state_id);
931
932 EXCEPTION WHEN NO_DATA_FOUND THEN
933 return('NO STATE EIN');
934
935 END; /* get_w2_state_ein */
936
937 FUNCTION get_w2_state_uin ( w2_tax_unit_id in number,
938 w2_state_abbrev in varchar2)
939 RETURN varchar2 IS
940 CURSOR UI_cursor IS
941 select nvl(ORG_INFORMATION2,'NO STATE UI#')
942 from hr_organization_information
943 where organization_id = w2_tax_unit_id
944 and org_information_context = 'State Tax Rules'
945 and org_information1 = w2_state_abbrev;
946
947
948 ui_id hr_organization_information.ORG_INFORMATION2%TYPE;
949
950 BEGIN
951
952 -- Get Employee State UI ID No for Box 16 - NJ
953 --
954
955 OPEN UI_cursor;
956 FETCH UI_cursor INTO ui_id;
957 CLOSE UI_cursor;
958
959 return(ui_id);
960
961 EXCEPTION WHEN NO_DATA_FOUND THEN
962 return('NO STATE UI#');
963 WHEN OTHERS THEN
964 return('NO STATE UI#');
965
966 END; /* get_w2_state_uin */
967
968
969
970 FUNCTION get_w2_high_comp_amt (w2_rownum in number,
971 w2_restrict in number,
972 w2_bal_amt in number)
973 RETURN number IS
974 l_return_value number :=0;
975
976 BEGIN
977 if ( w2_rownum * w2_restrict - (0.01 * (w2_rownum-1))) <= w2_bal_amt then
978 l_return_value := w2_restrict - (0.01 * (w2_rownum-1));
979 else
980 l_return_value := w2_bal_amt;
981 for i in 1 .. (w2_rownum - 1) LOOP
982 l_return_value := l_return_value - (w2_restrict - (0.01 * (i-1)));
983 end loop;
984 if l_return_value <= 0 then
985 l_return_value := 0;
986 end if;
987 end if;
988
989 return(l_return_value);
990 EXCEPTION
991 WHEN OTHERS THEN
992 return(0);
993 END; /* get_w2_high_comp_amt */
994
995
996 FUNCTION get_w2_box_15 (w2_asg_act_id number,
997 w2_balance_name varchar2,
998 w2_tax_unit_id number,
999 w2_jurisdiction_code varchar2,
1000 w2_jurisdiction_level number
1001 ) RETURN VARCHAR2 is
1002 BEGIN
1003
1004 return(hr_us_w2_rep.get_w2_box_15 (w2_asg_act_id,
1005 w2_balance_name,
1006 w2_tax_unit_id,
1007 w2_jurisdiction_code,
1008 w2_jurisdiction_level,
1009 null));
1010
1011 END get_w2_box_15;
1012
1013
1014 FUNCTION get_w2_box_15 (w2_asg_act_id number,
1015 w2_balance_name varchar2,
1016 w2_tax_unit_id number,
1017 w2_jurisdiction_code varchar2,
1018 w2_jurisdiction_level number,
1019 w2_effective_date date ) RETURN VARCHAR2 is
1020
1021 l_user_entity_id number;
1022 l_bal_amt number;
1023
1024 cursor c_sel is
1025 select decode(fai.value, 'Y', 1, 'D', 1, 0)
1026 from ff_archive_items fai
1027 where fai.context1 = w2_asg_act_id
1028 and fai.user_entity_id = l_user_entity_id;
1029
1030 BEGIN
1031
1032 if w2_balance_name = 'A_W2_PENSION_PLAN_PER_GRE_YTD' then
1033 -- Changed for EOY 2000
1034 if w2_effective_date is null then
1035 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1036 (w2_asg_act_id,
1037 'A_W2_BOX_13D_PER_GRE_YTD', /* EOY 2000 */
1038 to_char(w2_tax_unit_id),
1039 w2_jurisdiction_code,
1040 w2_jurisdiction_level);
1041
1042 if l_bal_amt <= 0 or l_bal_amt is null then
1043 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1044 (w2_asg_act_id,
1045 'A_W2_BOX_13E_PER_GRE_YTD',
1046 to_char(w2_tax_unit_id),
1047 w2_jurisdiction_code,
1048 w2_jurisdiction_level);
1049 end if;
1050
1051 if l_bal_amt <= 0 or l_bal_amt is null then
1052 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1053 (w2_asg_act_id,
1054 w2_balance_name,
1055 to_char(w2_tax_unit_id),
1056 w2_jurisdiction_code,
1057 w2_jurisdiction_level);
1058 end if;
1059 else
1060 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1061 (w2_asg_act_id,
1062 'A_W2_401K_PER_GRE_YTD', /* EOY 2001 */
1063 to_char(w2_tax_unit_id),
1064 w2_jurisdiction_code,
1065 w2_jurisdiction_level);
1066
1067 if l_bal_amt <= 0 or l_bal_amt is null then
1068 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1069 (w2_asg_act_id,
1070 'A_W2_403B_PER_GRE_YTD',
1071 to_char(w2_tax_unit_id),
1072 w2_jurisdiction_code,
1073 w2_jurisdiction_level);
1074 end if;
1075
1076 if l_bal_amt <= 0 or l_bal_amt is null then
1077 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1078 (w2_asg_act_id,
1079 'A_W2_408K_PER_GRE_YTD',
1080 to_char(w2_tax_unit_id),
1081 w2_jurisdiction_code,
1082 w2_jurisdiction_level);
1083 end if;
1084
1085 if l_bal_amt <= 0 or l_bal_amt is null then
1086 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1087 (w2_asg_act_id,
1088 'A_W2_501C_PER_GRE_YTD',
1089 to_char(w2_tax_unit_id),
1090 w2_jurisdiction_code,
1091 w2_jurisdiction_level);
1092 end if;
1093
1094 if l_bal_amt <= 0 or l_bal_amt is null then
1095 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1096 (w2_asg_act_id,
1097 w2_balance_name,
1098 to_char(w2_tax_unit_id),
1099 w2_jurisdiction_code,
1100 w2_jurisdiction_level);
1101 end if;
1102
1103 if l_bal_amt <= 0 or l_bal_amt is null then /* 5748431 */
1104 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1105 (w2_asg_act_id,
1106 'A_W2_ROTH_403B_PER_GRE_YTD',
1107 to_char(w2_tax_unit_id),
1108 w2_jurisdiction_code,
1109 w2_jurisdiction_level);
1110 end if;
1111
1112
1113
1114 if l_bal_amt <= 0 or l_bal_amt is null then /* 5748431 */
1115 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1116 (w2_asg_act_id,
1117 'A_W2_ROTH_401K_PER_GRE_YTD',
1118 to_char(w2_tax_unit_id),
1119 w2_jurisdiction_code,
1120 w2_jurisdiction_level);
1121 end if;
1122
1123 end if;
1124 elsif w2_balance_name = 'A_DEF_COMP_401K_PER_GRE_YTD' then
1125 if w2_effective_date is null then
1126 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1127 (w2_asg_act_id,
1128 'A_W2_BOX_13D_PER_GRE_YTD', /* EOY 2000 */
1129 to_char(w2_tax_unit_id),
1130 w2_jurisdiction_code,
1131 w2_jurisdiction_level);
1132 if l_bal_amt <= 0 or l_bal_amt is null then
1133 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1134 (w2_asg_act_id,
1135 'A_W2_BOX_13E_PER_GRE_YTD',
1136 to_char(w2_tax_unit_id),
1137 w2_jurisdiction_code,
1138 w2_jurisdiction_level);
1139 end if;
1140 if l_bal_amt <= 0 or l_bal_amt is null then
1141 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1142 (w2_asg_act_id,
1143 'A_W2_BOX_13G_PER_GRE_YTD',
1144 to_char(w2_tax_unit_id),
1145 w2_jurisdiction_code,
1146 w2_jurisdiction_level);
1147 end if;
1148 if l_bal_amt <= 0 or l_bal_amt is null then
1149 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1150 (w2_asg_act_id,
1151 w2_balance_name,
1152 to_char(w2_tax_unit_id),
1153 w2_jurisdiction_code,
1154 w2_jurisdiction_level);
1155 end if;
1156 else
1157 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1158 (w2_asg_act_id,
1159 'A_W2_401K_PER_GRE_YTD', /* EOY 2001 */
1160 to_char(w2_tax_unit_id),
1161 w2_jurisdiction_code,
1162 w2_jurisdiction_level);
1163 if l_bal_amt <= 0 or l_bal_amt is null then
1164 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1165 (w2_asg_act_id,
1166 'A_W2_403B_PER_GRE_YTD',
1167 to_char(w2_tax_unit_id),
1168 w2_jurisdiction_code,
1169 w2_jurisdiction_level);
1170 end if;
1171 if l_bal_amt <= 0 or l_bal_amt is null then
1172 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1173 (w2_asg_act_id,
1174 'A_W2_457_PER_GRE_YTD',
1175 to_char(w2_tax_unit_id),
1176 w2_jurisdiction_code,
1177 w2_jurisdiction_level);
1178 end if;
1179 if l_bal_amt <= 0 or l_bal_amt is null then
1180 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1181 (w2_asg_act_id,
1182 w2_balance_name,
1183 to_char(w2_tax_unit_id),
1184 w2_jurisdiction_code,
1185 w2_jurisdiction_level);
1186 end if;
1187
1188 end if;
1189 elsif w2_balance_name = 'A_W2_TP_SICK_PAY_PER_GRE_YTD' then
1190 /* For Sick Pay Indicator, will use only this balance */
1191 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt
1192 (w2_asg_act_id,
1193 w2_balance_name,
1194 to_char(w2_tax_unit_id),
1195 w2_jurisdiction_code,
1196 w2_jurisdiction_level);
1197 else
1198 l_user_entity_id := get_user_entity_id(w2_balance_name);
1199
1200 open c_sel;
1201 fetch c_sel into l_bal_amt;
1202 if c_sel%notfound then
1203 l_bal_amt := 0;
1204 end if;
1205 close c_sel;
1206
1207 end if;
1208
1209 if l_bal_amt > 0 then
1210 return('X');
1211 else
1212 return(' ');
1213 end if;
1214
1215 end get_w2_box_15;
1216
1217 FUNCTION get_w2_tax_unit_item (w2_tax_unit_id number,
1218 w2_payroll_action_id number,
1219 w2_tax_unit_item varchar2) RETURN VARCHAR2 is
1220
1221 CURSOR c_tax_unit_item IS
1222 select fai.value
1223 from ff_archive_item_contexts faic,
1224 ff_contexts fc,
1225 ff_archive_items fai,
1226 ff_database_items fdi
1227 where fdi.user_name = w2_tax_unit_item
1228 and fdi.user_entity_id = fai.user_entity_id
1229 and fai.context1 = w2_payroll_action_id
1230 and fc.context_name = 'TAX_UNIT_ID'
1231 and fai.archive_item_id = faic.archive_item_id
1232 and faic.context_id = fc.context_id
1233 and faic.context = to_char(w2_tax_unit_id);
1234
1235 p_tax_unit_item ff_archive_items.value%type;
1236
1237 BEGIN
1238 OPEN c_tax_unit_item;
1239 FETCH c_tax_unit_item INTO p_tax_unit_item;
1240 CLOSE c_tax_unit_item;
1241
1242 return(p_tax_unit_item);
1243
1244 EXCEPTION WHEN NO_DATA_FOUND THEN
1245 return(null);
1246 END; /* get_w2_tax_unit_item */
1247
1248 FUNCTION get_tax_unit_addr_line (w2_tax_unit_id number,
1249 w2_addr_item varchar2) RETURN VARCHAR2 is
1250
1251 cursor c_addr_line is
1252 select decode(w2_addr_item,
1253 'ADDR1' ,address_line_1,
1254 'ADDR2' ,address_line_2,
1255 'ADDR3' ,address_line_3,
1256 'CITY' ,town_or_city,
1257 'STATE' ,region_2,
1258 'COUNTRY',country,
1259 'ZIP' ,postal_code,null)
1260 from hr_locations_all hl, /*Bug:2380518 fix */
1261 hr_organization_units hou
1262 where hou.organization_id = w2_tax_unit_id
1263 and hou.location_id = hl.location_id;
1264
1265 addr_line hr_locations.address_line_1%type;
1266
1267 begin
1268 OPEN c_addr_line;
1269 FETCH c_addr_line INTO addr_line;
1270 CLOSE c_addr_line;
1271
1272 return(addr_line);
1273
1274 EXCEPTION when no_data_found then
1275 return(null);
1276
1277 end; /* get_tax_unit_addr_line */
1278
1279 FUNCTION get_tax_unit_bg (w2_tax_unit_id number)
1280 RETURN NUMBER is
1281
1282 cursor c_bg is
1283 select
1284 business_group_id
1285 from hr_organization_units hou
1286 where hou.organization_id = w2_tax_unit_id;
1287
1288 p_business_group_id hr_organization_units.business_group_id%type;
1289
1290 begin
1291 OPEN c_bg;
1292 FETCH c_bg INTO p_business_group_id;
1293 CLOSE c_bg;
1294
1295 return(p_business_group_id);
1296
1297 EXCEPTION when no_data_found then
1298 return(null);
1299
1300 end; /* get_tax_unit_bg */
1301
1302 FUNCTION get_per_item (w2_assignment_action_id number,
1303 w2_per_item varchar2)
1304 RETURN VARCHAR2 is
1305
1306 cursor c_per_item is
1307 select
1308 fai.value
1309 from ff_archive_items fai,
1310 ff_database_items fdi
1311 where fdi.user_name = w2_per_item
1312 and fdi.user_entity_id = fai.user_entity_id
1313 and fai.context1 = w2_assignment_action_id;
1314
1315 -- changed from
1316 -- p_per_item per_people_f.middle_names%type;
1317 -- for bug 2465183 because of UTF8 the length has been
1318 -- increased
1319
1320 p_per_item per_people_f.last_name%type;
1321
1322 begin
1323
1324 OPEN c_per_item;
1325 FETCH c_per_item INTO p_per_item;
1326 CLOSE c_per_item;
1327
1328 return(p_per_item);
1329
1330 EXCEPTION when no_data_found then
1331 return(null);
1332
1333 end; /* get_per_item */
1334
1335 FUNCTION get_state_item (w2_tax_unit_id number,
1336 w2_jurisdiction_code varchar2,
1337 w2_payroll_action_id number,
1338 w2_state_item varchar2)
1339 RETURN VARCHAR2 is
1340
1341 cursor c_state_item is
1342 select
1343 fai.value
1344 from
1345 ff_archive_item_contexts faic2,
1346 ff_archive_item_contexts faic1,
1347 ff_contexts fc2,
1348 ff_contexts fc1,
1349 ff_archive_items fai,
1350 ff_database_items fdi
1351 where fdi.user_name = w2_state_item
1352 and fdi.user_entity_id = fai.user_entity_id
1353 and fai.context1 = w2_payroll_action_id
1354 and fc2.context_name = 'TAX_UNIT_ID'
1355 and fc1.context_name = 'JURISDICTION_CODE'
1356 and fai.archive_item_id = faic2.archive_item_id
1357 and faic2.context_id = fc2.context_id
1358 and faic2.context = to_char(w2_tax_unit_id)
1359 and fai.archive_item_id = faic1.archive_item_id
1360 and faic1.context_id = fc1.context_id
1361 and faic1.context = w2_jurisdiction_code;
1362
1363 p_state_item varchar2(240);
1364
1365 begin
1366 OPEN c_state_item;
1367 FETCH c_state_item INTO p_state_item;
1368 CLOSE c_state_item;
1369
1370 return(p_state_item);
1371
1372 EXCEPTION when no_data_found then
1373 return(null);
1374
1375 end; /* get_state_item */
1376
1377 FUNCTION get_leav_reason (w2_leaving_reason varchar2)
1378 RETURN VARCHAR2 is
1379
1380 cursor c_leav_reason is
1381 select meaning
1382 from fnd_lookup_values
1383 where lookup_type='LEAV_REAS'
1384 and lookup_code= w2_leaving_reason ;
1385
1386 l_leav_reason VARCHAR2(80);
1387
1388 begin
1389 OPEN c_leav_reason;
1390 FETCH c_leav_reason INTO l_leav_reason;
1391 CLOSE c_leav_reason;
1392
1393 return(l_leav_reason);
1394
1395 EXCEPTION when no_data_found then
1396 return('ZZ');
1397
1398 when others then
1399 return('ZZ');
1400
1401 end; /* get_leav_reason */
1402
1403
1404 PROCEDURE GET_COUNTY_TAX_INFO
1405 ( p_jurisdiction_code IN Varchar2 ,
1406 p_tax_year IN NUMBER,
1407 p_tax_rate OUT NOCOPY NUMBER,
1408 P_mh_tax_rate OUT NOCOPY NUMBER,
1409 P_mh_tax_limit OUT NOCOPY NUMBER,
1410 P_occ_mh_tax_limit OUT NOCOPY NUMBER,
1411 P_occ_mh_wage_limit OUT NOCOPY NUMBER,
1412 P_mh_tax_wage_limit OUT NOCOPY NUMBER
1413 )
1414 IS
1415
1416 --
1417 -- Purpose: Procedure to fetch County Tax info from pay_us_county_tax_info_f table
1418 -- The return values used in W2 reports for computing Boone County Taxes
1419 -- like Occupatinal and Mental Health Taxes
1420 --
1421 --
1422 -- MODIFICATION HISTORY
1423 -- Person Date Comments
1424 -- --------- ------ -------------------------------------------
1425 -- ppanda 05-Aug-2002 Initial Version
1426 --
1427 -- Declaration of Local program variables
1428 --
1429 l_occ_tax_rate Varchar2(80) := '';
1430 l_mh_tax_rate Varchar2(80) := '';
1431 l_mh_tax_limit Varchar2(80) := '';
1432 l_occ_mh_tax_limit Varchar2(80) := '';
1433 l_occ_mh_wage_limit Varchar2(80) := '';
1434 l_mh_tax_wage_limit Varchar2(80) := '';
1435 --
1436 -- This Cursor fetches Tax info for the given jurisdiction and effective date
1437 --
1438 Cursor C_cnty_tax_info (C_jurisdiction_code Varchar2,
1439 C_tax_year NUMBER) IS
1440 select cnty_information2 occ_tax_rate,
1441 cnty_information3 mh_tax_rate,
1442 cnty_information4 mh_tax_limit,
1443 cnty_information5 occ_mh_tax_limit,
1444 cnty_information6 occ_mh_wage_limit,
1445 cnty_information7 mh_tax_wage_limit
1446 from PAY_US_COUNTY_TAX_INFO_F
1447 where jurisdiction_code = C_jurisdiction_code
1448 and (to_date('31-12-'||to_char(C_tax_year), 'DD-MM-YYYY')
1449 between effective_start_date and effective_end_date);
1450
1451 BEGIN
1452 OPEN C_cnty_tax_info(p_jurisdiction_code, P_tax_year);
1453 FETCH C_cnty_tax_info INTO l_occ_tax_rate,
1454 l_mh_tax_rate,
1455 l_mh_tax_limit,
1456 l_occ_mh_tax_limit,
1457 l_occ_mh_wage_limit,
1458 l_mh_tax_wage_limit;
1459 IF C_cnty_tax_info%FOUND then
1460 p_tax_rate := l_occ_tax_rate;
1461 P_mh_tax_rate := l_mh_tax_rate;
1462 P_mh_tax_limit := l_mh_tax_limit;
1463 P_occ_mh_tax_limit := l_occ_mh_tax_limit;
1464 P_occ_mh_wage_limit := l_occ_mh_wage_limit;
1465 P_mh_tax_wage_limit := l_mh_tax_wage_limit;
1466 END IF;
1467 CLOSE C_cnty_tax_info;
1468 EXCEPTION
1469 WHEN others THEN
1470 NULL;
1471 END; -- Procedure get_county_tax_info
1472
1473
1474 PROCEDURE get_agent_tax_unit_id ( p_business_group_id in number,
1475 p_year in number,
1476 p_agent_tax_unit_id out nocopy number,
1477 p_error_mesg out nocopy varchar2 )
1478
1479 IS
1480
1481 --
1482 -- Purpose: Procedure to get Agent Tax Unit Id. Called from Emp W2, ER W2 report
1483 -- and W2 Register report.
1484 -- Input parameter is business_group_id and
1485 -- Output parameter is p_agent_tax_unit_id and p_error_mesg
1486 -- The calling program has to check
1487 -- If p_error_mesg is not null then
1488 -- Error and write the this error mesg in the log file
1489 -- Else if p_agent_tax_unit_id is null then
1490 -- there is no change in the existing process ie incase of
1491 -- Emp W2 it has to take the w2 parameter GRE's name, address and EIN
1492 -- Else (p_agent_tax_unit_id is not null) then
1493 -- the report need to use the p_agent_tax_unit_id to retrieve the
1494 -- GRE's name, address and EIN
1495 --
1496
1497 l_agent_tax_unit_id number ;
1498 l_count number ;
1499 l_agent_tax_unit_name varchar2(240) := ' ';
1500 l_w2_tax_unit_id number ;
1501
1502
1503
1504 begin
1505
1506 l_agent_tax_unit_id := null ;
1507 l_w2_tax_unit_id := null;
1508
1509 begin
1510
1511 -- Get 2678 Filer
1512 select hou.organization_id,
1513 hou.name
1514 into l_agent_tax_unit_id,
1515 l_agent_tax_unit_name
1516 from hr_organization_information hoi,
1517 hr_organization_units hou
1518 where hoi.org_information_context = 'W2 Reporting Rules'
1519 and hou.organization_id = hoi.organization_id
1520 and hou.business_group_id = p_business_group_id
1521 and nvl(org_information8, 'N') = 'Y'
1522 and not exists (
1523 select 'Y'
1524 from hr_organization_information
1525 where organization_id = hou.organization_id
1526 and org_information_context = '1099R Magnetic Report Rules');
1527
1528 begin
1529 -- Get W2 Transmitter
1530 select hou.organization_id
1531 into l_w2_tax_unit_id
1532 from hr_organization_information hoi,
1533 hr_organization_units hou
1534 where hoi.org_information_context = 'W2 Reporting Rules'
1535 and hou.organization_id = hoi.organization_id
1536 and hou.business_group_id = p_business_group_id
1537 and nvl(org_information1, 'N') = 'Y' -- W2 Transmitter flag
1538 and not exists (
1539 select 'Y'
1540 from hr_organization_information
1541 where organization_id = hou.organization_id
1542 and org_information_context = '1099R Magnetic Report Rules');
1543
1544 if l_agent_tax_unit_id = l_w2_tax_unit_id then -- is the Filer defined as W2 transmitter ?
1545
1546 -- Now check whether this agent gre is archived or not
1547 --
1548 begin
1549
1550 select count(*)
1551 into l_count
1552 from pay_us_w2_tax_unit_v
1553 where tax_unit_id = l_agent_tax_unit_id
1554 and year = p_year ;
1555
1556 if l_count = 0 then
1557
1558 p_agent_tax_unit_id := null ;
1559 p_error_mesg := 'Error : 2678 Filer GRE ' || l_agent_tax_unit_name || 'for Year '
1560 || to_char(p_year) || ' is Not Archived ' ;
1561
1562 else
1563
1564 p_agent_tax_unit_id := l_agent_tax_unit_id ;
1565 p_error_mesg := null ;
1566
1567 end if;
1568
1569 end ; -- End gre is archived ?
1570 else
1571 p_agent_tax_unit_id := null ;
1572 p_error_mesg := 'Error: 2678 Filer GRE must be defined as W-2 Transmitter. ';
1573
1574 end if;
1575 exception -- W2 Transmitter check exception section
1576 when no_data_found then
1577 -- error: Filer found; but no W2-Transmitter found
1578 p_agent_tax_unit_id := null ;
1579 p_error_mesg := 'Error: 2678 Filer GRE must be defined as W-2 Transmitter. ';
1580
1581 when too_many_rows then
1582 -- error: Multiple W2-Transmitter found
1583 p_agent_tax_unit_id := null ;
1584 p_error_mesg := 'Error:GRE in the business group defined as a 2678 Filer but multiple GREs marked as W-2 transmitter.';
1585
1586
1587 when others then
1588
1589 p_agent_tax_unit_id := null ;
1590 p_error_mesg := substr(SQLERRM,1,45);
1591
1592 end; -- W2 Transmitter check
1593
1594 exception when too_many_rows then
1595
1596 -- error multiple Filer GREs found
1597
1598 p_agent_tax_unit_id := null ;
1599 p_error_mesg := 'Error: Only one 2678 Filer GRE can exist in a business group. ' ;
1600 --p_error_mesg := p_error_mesg || 'Cannot have more than one GRE with 2678 Filer and/or W-2 Transmitter defined.' ;
1601
1602 when no_data_found then
1603 -- Normal processing: no 2678 Filer GREs found.
1604
1605 p_agent_tax_unit_id := null;
1606 p_error_mesg := null;
1607
1608 when others then
1609
1610 p_agent_tax_unit_id := null ;
1611 p_error_mesg := substr(SQLERRM,1,45);
1612
1613 end ; -- 2678 Filer check
1614
1615 END get_agent_tax_unit_id;
1616
1617
1618 function get_w2_userra_bal(w2_asg_act_id number,
1619 w2_tax_unit_id number ,
1620 w2_jurisdiction_code varchar2 ,
1621 w2_jurisdiction_level number,
1622 p_userra_code varchar2
1623 ) return number is
1624
1625 l_userra_balance number := 0;
1626 l_bal_amt number := 0;
1627
1628 cursor c_userra_db_items (c_userra_code varchar2) is
1629 select user_name
1630 from ff_database_items
1631 where user_name like 'A_W2_USERRA_'||c_userra_code||'%_PER_GRE_YTD';
1632
1633 l_balance_name ff_database_items.user_name%type;
1634 begin
1635 for userra_db_items IN c_userra_db_items(p_userra_code)
1636 loop
1637 l_balance_name :=userra_db_items.user_name;
1638
1639 l_bal_amt := hr_us_w2_rep.get_w2_bal_amt(w2_asg_act_id,
1640 l_balance_name,
1641 w2_tax_unit_id,
1642 w2_jurisdiction_code,
1643 w2_jurisdiction_level);
1644 if l_bal_amt <> 0 then
1645 l_userra_balance := l_userra_balance + l_bal_amt;
1646 end if;
1647 end loop;
1648 return (l_userra_balance);
1649 end get_w2_userra_bal;
1650
1651 FUNCTION get_w2_box17_label (p_tax_unit_id in number,
1652 p_state_abbrev in varchar2)
1653 return varchar2 is
1654
1655 cursor c_get_value_gre is
1656
1657 select nvl(org_information18,'SDI')
1658 from hr_organization_information
1659 where organization_id = p_tax_unit_id
1660 and org_information_context = 'W2 Reporting Rules';
1661
1662 l_box17_label varchar2(5);
1663
1664 begin
1665 /* check if the state is 'CA'. If yes then we nned to check == first in the
1666 plsql table for the value else fetch the value and populate the table
1667 and return the value */
1668
1669 hr_utility.trace('TUID is : '|| to_char(p_tax_unit_id));
1670 hr_utility.trace('State Abbrev is : '|| p_state_abbrev);
1671
1672 if p_state_abbrev <> 'CA' then
1673
1674 return ('SDI');
1675
1676 else
1677
1678 if hr_us_w2_rep.ltr_box17.exists(p_tax_unit_id) then
1679
1680 hr_utility.trace('Value exists ');
1681 l_box17_label := hr_us_w2_rep.ltr_box17(p_tax_unit_id).value;
1682
1683 else
1684
1685 hr_utility.trace('Value does not exists ');
1686 open c_get_value_gre;
1687 fetch c_get_value_gre into l_box17_label;
1688 if c_get_value_gre%NOTFOUND then
1689 l_box17_label := 'SDI';
1690 end if;
1691 close c_get_value_gre;
1692
1693 hr_utility.trace('SQL Value is : '|| l_box17_label);
1694 hr_us_w2_rep.ltr_box17(p_tax_unit_id).state_abbrev := p_state_abbrev;
1695 hr_us_w2_rep.ltr_box17(p_tax_unit_id).value := l_box17_label;
1696
1697 end if;
1698
1699 hr_utility.trace('Return Value is : '|| l_box17_label);
1700 return (l_box17_label);
1701
1702 end if;
1703
1704 end get_w2_box17_label ;
1705
1706
1707
1708 FUNCTION get_last_deffer_year (p_ass_action_id in number)
1709 return varchar2 is
1710
1711 year varchar2(20);
1712
1713 CURSOR get_year( cp_action_id number) is
1714
1715 select fai.value designated_roth_contri
1716 from FF_USER_ENTITIES fue,
1717 FF_ARCHIVE_ITEMS fai
1718 where fai.context1 = cp_action_id
1719 AND fai.user_entity_id = fue.user_entity_id
1720 AND upper(fue.user_entity_name) = 'A_FIRST_YEAR_ROTH_CONTRIB' ;
1721
1722 Begin
1723
1724 OPEN get_year (p_ass_action_id) ;
1725 FETCH get_year INTO year ;
1726 CLOSE get_year ;
1727
1728 RETURN (year);
1729
1730 END get_last_deffer_year;
1731
1732 --New function added to get the employee number from active tables
1733
1734 FUNCTION get_w2_employee_number(w2_nat_ident in varchar2, w2_effective_date in date)
1735 RETURN varchar2 IS
1736 l_emp_number varchar2(30);
1737
1738 BEGIN
1739 SELECT peo.employee_number INTO l_emp_number
1740 FROM per_all_people_f peo
1741 WHERE peo.national_identifier = w2_nat_ident
1742 AND w2_effective_date BETWEEN peo.effective_start_date
1743 AND peo.effective_end_date;
1744 RETURN(l_emp_number);
1745 EXCEPTION
1746 WHEN NO_DATA_FOUND THEN
1747 return(' ');
1748 WHEN OTHERS THEN
1749 return(' ');
1750 END;
1751
1752 --New function added to get the workers compensation code from active tables
1753
1754 FUNCTION get_w2_worker_compensation(w2_asg_id in number, w2_effective_date in date)
1755 RETURN varchar2 IS
1756 l_emp_wc varchar2(30);
1757 l_emp_loc per_all_assignments_f.location_id%type;
1758 l_emp_job per_all_assignments_f.job_id%type;
1759 BEGIN
1760 SELECT job_id,location_id into l_emp_job,l_emp_loc
1761 FROM per_all_assignments_f
1762 WHERE assignment_id=w2_asg_id
1763 AND w2_effective_date BETWEEN effective_start_date AND effective_end_date;
1764 SELECT jwc.wc_code INTO l_emp_wc
1765 FROM pay_job_wc_code_usages jwc,
1766 hr_locations_all hl
1767 WHERE jwc.job_id = l_emp_job
1768 AND hl.location_id = l_emp_loc
1769 AND jwc.state_code = hl.region_2;
1770 return(l_emp_wc);
1771 EXCEPTION
1772 WHEN NO_DATA_FOUND THEN
1773 return(' ');
1774 WHEN OTHERS THEN
1775 return(' ');
1776 END;
1777
1778 --New function added to get the location code from active tables
1779
1780 FUNCTION get_w2_location_cd(w2_asg_id in number, w2_effective_date in date)
1781 RETURN varchar2 IS
1782 l_emp_loc varchar2(60);
1783
1784 BEGIN
1785 SELECT location_code into l_emp_loc
1786 FROM per_all_assignments_f paf,
1787 hr_locations_all hl
1788 WHERE assignment_id=w2_asg_id
1789 AND w2_effective_date BETWEEN effective_start_date AND effective_end_date
1790 AND paf.location_id=hl.location_id;
1791 RETURN(l_emp_loc);
1792 EXCEPTION
1793 WHEN NO_DATA_FOUND THEN
1794 return(' ');
1795 WHEN OTHERS THEN
1796 return(' ');
1797 END;
1798
1799
1800 end hr_us_w2_rep;
[Home] [Help]