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