[Home] [Help]
PACKAGE BODY: APPS.PAY_US_PR_W2
Source
1 PACKAGE BODY pay_us_pr_w2 AS
2 /* $Header: pyusprw2.pkb 120.13.12020000.3 2012/11/26 09:01:07 pkoduri ship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_pr_w2
21 Description : This package is called by the Puerto Rico W2 Totals and
22 Exceptions Report
23
24 - CSV
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ---- ---- ------ ------- -----------
30 22-AUG-2002 Fusman 115.0 Created.
31 27-AUG-2002 Fusman 115.1 Changed the seq number.
32 16-SEP-2002 ahanda 115.2 Changed report titles to use gv_title
33 and gv_title2.
34 19-SEP-2002 Fusman 115.3 Fix for bug:2585617. Added sum to the totals.
35 20-SEP-2002 Fusman 115.4 Fix for bug:2585617. Changed header for SS Tips.
36 22-SEP-2002 JGoswami 115.5
37 14-AUG-2003 Jgoswami 115.6 2778370 Added code for
38 Puerto Rico W2 Register Report
39 Modified code for Total Report.
40 15-AUG-2003 Jgoswami 115.7 Added Pension to the Total Report.
41 25-AUG-2003 JGoswami 115.8 Changed the Total Report format
42 from csv to html.
43 Removed employer header and
44 employer data functions.
45 03-sep-2003 JGoswami 115.9 3097463 added r_type to store datatype of r_value.
46 3125120 added ername, ein and year to register and exception report.
47 3122224 modified to show the correct totals for
48 Medicare Wages & Tips ,Medicare Taxes Withheld and
49 Social Security Tips.
50 09-sep-2003 JGoswami 115.10 3125120 Modified Format for Number to 999,999,999,999,990.00
51 12-JAN-2003 JGoswami 115.11 3347535 Modified the package to report Employeer's and Employee Address on Register, Exception and Totals Report
52 07-JAN-2008 PSUGUMAR 115.12 5855662 Modified the package to include employee number,compensation code,location
53 11-Jan-2008 vmkulkar 115.15 Added escape symbol before nbsp
54 30-Jan-2008 psugumar 115.17 6782741 Uncommented all the fields to display the missing fields
55 30-Jan-2008 jgoswami 115.18 6782741 Modified package to remove extra SSN
56 13-Nov-2008 skpatil 115.19 7566756 Modified employee_data() to format Employer ID#
57 03-Jan-2011 skchalla 115.21 10287981 Modified employee_data(),employee_header() and insert_pr_w2_totals()
58 to Include New field Hire act wages (Y/N)
59 10435466 Modified employee_data(),employee_header() and insert_pr_w2_totals()
60 to Include New field to dsplay the Hire act wages
61 11-Jan-2011 skchalla 115.23 10435466 Modified employee_data() and resolved issue with the EE address line 3
62 03-Jan-2011 sgotlasw 115.24 13462230 Modified employee_data(),employee_header() and insert_pr_w2_totals()
63 to Include PR W2 Charitable Contributions, PR W2 Contr To Save and Double Money,
64 W2 Health Coverage.
65 21-Nov-2012 Pkoduri 115.25 15874201 Corrected the Employee Address line 1 and 2 to display 40 Chars on register.
66
67 */
68
69
70 /************************************************************
71 ** Local Package Variables
72 ************************************************************/
73 gv_title VARCHAR2(100) := 'W-3 PR Transmittal of Withholding Statements';
74 gv_title2 VARCHAR2(100) := 'Puerto Rico W2 Exceptions Report ';
75 gv_title3 VARCHAR2(100) := 'Puerto Rico W2 Register Report ';
76 gc_csv_delimiter VARCHAR2(1) := ',';
77 gc_csv_data_delimiter VARCHAR2(1) := '"';
78
79 gv_html_start_data VARCHAR2(50) := '<td>' ;
80 gv_html_end_data VARCHAR2(5) := '</td>' ;
81
82 gv_package_name VARCHAR2(50) := 'pay_us_pr_w2';
83
84 total_rec_tab tab_rec_total;
85
86
87
88 /******************************************************************
89 ** Function Returns the formated input string based on the
90 ** Output format. If the format is CSV then the values are returned
91 ** seperated by comma (,).
92 ******************************************************************/
93
94 FUNCTION formated_data_string
95 (p_input_string in varchar2
96 ,p_bold in varchar2 default 'N'
97 ,p_type in varchar2
98 ,p_output_file_type in varchar2
99 )
100 RETURN VARCHAR2
101 IS
102
103 lv_format varchar2(32000);
104
105 BEGIN
106 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
107
108 if p_output_file_type = 'CSV' then
109
110
111 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
112
113 lv_format := gc_csv_data_delimiter || p_input_string ||
114 gc_csv_data_delimiter || gc_csv_delimiter;
115
116 elsif p_output_file_type = 'HTML' then
117
118 if p_type = 'C' then
119 gv_html_start_data := '<td align="left">';
120 elsif p_type = 'N' then
121 gv_html_start_data := '<td align="right">';
122
123 else
124 gv_html_start_data := '<td align="left">';
125
126 end if;
127
128 if p_input_string is null then
129 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
130 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
131 else
132 if p_bold = 'Y' then
133 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
134 lv_format := gv_html_start_data || '<b> ' || p_input_string
135 || '</b>' || gv_html_end_data;
136 else
137 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
138 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
139 end if;
140 end if;
141
142 end if;
143
144 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
145
146 return lv_format;
147
148 END formated_data_string;
149
150 /************************************************************
151 ** Function returns the string with the HTML Header tags
152 ************************************************************/
153 FUNCTION formated_header_string
154 (p_input_string in varchar2
155 ,p_output_file_type in varchar2
156 )
157 RETURN VARCHAR2
158 IS
159
160 lv_format varchar2(1000);
161
162 BEGIN
163 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
164 if p_output_file_type = 'CSV' then
165 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
166 lv_format := p_input_string;
167 elsif p_output_file_type = 'HTML' then
168 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
169 lv_format := '<HTML><HEAD> <H1> <B>' || p_input_string ||
170 '</B></H1></HEAD>';
171 end if;
172
173
174 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
175 return lv_format;
176
177 END formated_header_string;
178
179 /*****************************************************************
180 ** This procudure returns the Mandatory Static Labels and the
181 ** Other Additional Static columns.
182 *****************************************************************/
183
184 /* This function is used to write the headers for Puerto Rico W2 Exception */
185
186 FUNCTION employee_header(
187 p_output_file_type in varchar2,
188 p_header in varchar2,
189 p_report_type in varchar2
190 )
191 RETURN VARCHAR2
192 IS
193
194 lv_format1 varchar2(32000);
195 lv_format2 varchar2(32000);
196
197 BEGIN
198
199 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
200 IF p_header = 'Header1' THEN
201
202
203 if p_report_type = 'PRW2EXCEPTION' then
204 lv_format1 := formated_data_string (p_input_string => gv_title2
205 ,p_bold => 'Y'
206 ,p_type => 'C'
207 ,p_output_file_type => p_output_file_type);
208
209 elsif p_report_type = 'PRW2REGISTER' then
210 lv_format1 := formated_data_string (p_input_string => gv_title3
211 ,p_bold => 'Y'
212 ,p_type => 'C'
213 ,p_output_file_type => p_output_file_type);
214
215 end if;
216
217 ELSE
218 --5855662 Changed for employee file header format
219 lv_format1 :=
220 formated_data_string (p_input_string => 'Social Security Number'
221 ,p_bold => 'Y'
222 ,p_type => 'C'
223 ,p_output_file_type => p_output_file_type) ||
224
225 /*
226 formated_data_string (p_input_string => 'Social Security Number'
227 ,p_bold => 'Y'
228 ,p_type => 'C'
229 ,p_output_file_type => p_output_file_type) ||
230
231 */
232 formated_data_string (p_input_string => 'Employee Number'
233 ,p_bold => 'Y'
234 ,p_type => 'C'
235 ,p_output_file_type => p_output_file_type) ||
236 formated_data_string (p_input_string => 'Inactive'
237 ,p_bold => 'Y'
238 ,p_type => 'C'
239 ,p_output_file_type => p_output_file_type) ||
240 formated_data_string (p_input_string => 'First Name'
241 ,p_bold => 'Y'
242 ,p_type => 'C'
243 ,p_output_file_type => p_output_file_type) ||
244 formated_data_string (p_input_string => 'Last Name'
245 ,p_bold => 'Y'
246 ,p_type => 'C'
247 ,p_output_file_type => p_output_file_type) ||
248 formated_data_string (p_input_string => 'Address Line1'
249 ,p_bold => 'Y'
250 ,p_type => 'C'
251 ,p_output_file_type => p_output_file_type) ||
252 formated_data_string (p_input_string => 'Address Line2'
253 ,p_bold => 'Y'
254 ,p_type => 'C'
255 ,p_output_file_type => p_output_file_type) ||
256 formated_data_string (p_input_string => 'Address Line3'
257 ,p_bold => 'Y'
258 ,p_type => 'C'
259 ,p_output_file_type => p_output_file_type) ||
260 formated_data_string (p_input_string => 'City'
261 ,p_bold => 'Y'
262 ,p_type => 'C'
263 ,p_output_file_type => p_output_file_type) ||
264 formated_data_string (p_input_string => 'State'
265 ,p_bold => 'Y'
266 ,p_type => 'C'
267 ,p_output_file_type => p_output_file_type) ||
268 formated_data_string (p_input_string => 'ZIP'
269 ,p_bold => 'Y'
270 ,p_type => 'C'
274 ,p_type => 'C'
271 ,p_output_file_type => p_output_file_type) ||
272 formated_data_string (p_input_string => 'Country'
273 ,p_bold => 'Y'
275 ,p_output_file_type => p_output_file_type) ||
276 formated_data_string (p_input_string => 'Marital Status'
277 ,p_bold => 'Y'
278 ,p_type => 'C'
279 ,p_output_file_type => p_output_file_type) ||
280 formated_data_string (p_input_string => 'Chauffeur Insurance'
281 ,p_bold => 'Y'
282 ,p_type => 'C'
283 ,p_output_file_type => p_output_file_type) ||
284 formated_data_string (p_input_string => 'Household Employee'
285 ,p_bold => 'Y'
286 ,p_type => 'C'
287 ,p_output_file_type => p_output_file_type) ||
288 formated_data_string (p_input_string => 'Driver''s License'
289 ,p_bold => 'Y'
290 ,p_type => 'C'
291 ,p_output_file_type => p_output_file_type) ||
292 formated_data_string (p_input_string => 'Blank'
293 ,p_bold => 'Y'
294 ,p_type => 'C'
295 ,p_output_file_type => p_output_file_type) ||
296 formated_data_string (p_input_string => 'Prior Retirement'
297 ,p_bold => 'Y'
298 ,p_type => 'C'
299 ,p_output_file_type => p_output_file_type) ||
300 formated_data_string (p_input_string => 'Workmen''s Compensation Code'
301 ,p_bold => 'Y'
302 ,p_type => 'C'
303 ,p_output_file_type => p_output_file_type) ||
304 formated_data_string (p_input_string => 'Agricultural'
305 ,p_bold => 'Y'
306 ,p_type => 'C'
307 ,p_output_file_type => p_output_file_type) ||
308 formated_data_string (p_input_string => 'Blank'
309 ,p_bold => 'Y'
310 ,p_type => 'C'
311 ,p_output_file_type => p_output_file_type) ||
312 formated_data_string (p_input_string => 'Location'
313 ,p_bold => 'Y'
314 ,p_type => 'C'
315 ,p_output_file_type => p_output_file_type) ||
316 formated_data_string (p_input_string => 'Whole Name'
317 ,p_bold => 'Y'
318 ,p_type => 'C'
319 ,p_output_file_type => p_output_file_type) ||
320 formated_data_string (p_input_string => 'Pension Date'
321 ,p_bold => 'Y'
322 ,p_type => 'C'
323 ,p_output_file_type => p_output_file_type) ||
324 formated_data_string (p_input_string => 'Employer Name'
325 ,p_bold => 'Y'
326 ,p_type => 'C'
327 ,p_output_file_type => p_output_file_type) ||
328 formated_data_string (p_input_string => 'Employer''s Identification Number'
329 ,p_bold => 'Y'
330 ,p_type => 'C'
331 ,p_output_file_type => p_output_file_type) ||
332 formated_data_string (p_input_string => 'Employer''s Address Line1'
333 ,p_bold => 'Y'
334 ,p_type => 'C'
335 ,p_output_file_type => p_output_file_type) ||
339 ,p_output_file_type => p_output_file_type) ||
336 formated_data_string (p_input_string => 'Employer''s Address Line2'
337 ,p_bold => 'Y'
338 ,p_type => 'C'
340 formated_data_string (p_input_string => 'Employer''s Address Line3'
341 ,p_bold => 'Y'
342 ,p_type => 'C'
343 ,p_output_file_type => p_output_file_type) ||
344 formated_data_string (p_input_string => 'Employer''s City'
345 ,p_bold => 'Y'
346 ,p_type => 'C'
347 ,p_output_file_type => p_output_file_type) ||
348 formated_data_string (p_input_string => 'Employer''s State'
349 ,p_bold => 'Y'
350 ,p_type => 'C'
351 ,p_output_file_type => p_output_file_type) ||
352 formated_data_string (p_input_string => 'Employer''s ZIP'
353 ,p_bold => 'Y'
354 ,p_type => 'C'
355 ,p_output_file_type => p_output_file_type) ||
356 formated_data_string (p_input_string => 'Employer''s Phone'
357 ,p_bold => 'Y'
358 ,p_type => 'C'
359 ,p_output_file_type => p_output_file_type) ||
360 formated_data_string (p_input_string => 'Year'
361 ,p_bold => 'Y'
362 ,p_type => 'C'
363 ,p_output_file_type => p_output_file_type) ||
364
365 formated_data_string (p_input_string => 'Surnames'
366 ,p_bold => 'Y'
367 ,p_type => 'C'
368 ,p_output_file_type => p_output_file_type) ||
369 formated_data_string (p_input_string => 'Civil Status'
370 ,p_bold => 'Y'
371 ,p_type => 'C'
372 ,p_output_file_type => p_output_file_type) ||
373 formated_data_string (p_input_string => 'Spouse''s Social Security Number'
374 ,p_bold => 'Y'
375 ,p_type => 'C'
376 ,p_output_file_type => p_output_file_type) ||
377 formated_data_string (p_input_string => 'Pension'
378 ,p_bold => 'Y'
379 ,p_type => 'C'
380 ,p_output_file_type => p_output_file_type) ||
381 formated_data_string (p_input_string => 'Wages'
382 ,p_bold => 'Y'
383 ,p_type => 'C'
384 ,p_output_file_type => p_output_file_type) ||
385 formated_data_string (p_input_string => 'Commissions'
386 ,p_bold => 'Y'
387 ,p_type => 'C'
388 ,p_output_file_type => p_output_file_type) ||
389 formated_data_string (p_input_string => 'Allowances'
390 ,p_bold => 'Y'
391 ,p_type => 'C'
392 ,p_output_file_type => p_output_file_type) ||
393 formated_data_string (p_input_string => 'Tips'
394 ,p_bold => 'Y'
395 ,p_type => 'C'
396 ,p_output_file_type => p_output_file_type) ||
397 formated_data_string (p_input_string => 'Total'
398 ,p_bold => 'Y'
399 ,p_type => 'C'
400 ,p_output_file_type => p_output_file_type) ||
401 formated_data_string (p_input_string => 'Reimbursed Expenses'
402 ,p_bold => 'Y'
403 ,p_type => 'C'
404 ,p_output_file_type => p_output_file_type) ||
405 formated_data_string (p_input_string => 'Tax Withheld '
406 ,p_bold => 'Y'
407 ,p_type => 'C'
408 ,p_output_file_type => p_output_file_type) ||
409 formated_data_string (p_input_string => 'Retirement Fund'
410 ,p_bold => 'Y'
411 ,p_type => 'C'
412 ,p_output_file_type => p_output_file_type) ||
413 formated_data_string (p_input_string => 'Contributions to CODA PLANS'
414 ,p_bold => 'Y'
415 ,p_type => 'C'
416 ,p_output_file_type => p_output_file_type) ||
417 formated_data_string (p_input_string => 'Soc.Security Wages'
418 ,p_bold => 'Y'
419 ,p_type => 'C'
420 ,p_output_file_type => p_output_file_type) ||
421 formated_data_string (p_input_string => 'Soc. Sec Tax Withheld'
422 ,p_bold => 'Y'
423 ,p_type => 'C'
424 ,p_output_file_type => p_output_file_type) ||
428 ,p_output_file_type => p_output_file_type) ||
425 formated_data_string (p_input_string => 'Medicare Wages and Tips'
426 ,p_bold => 'Y'
427 ,p_type => 'C'
429 formated_data_string (p_input_string => 'Medicare Tax Withheld'
430 ,p_bold => 'Y'
431 ,p_type => 'C'
432 ,p_output_file_type => p_output_file_type) ||
433 formated_data_string (p_input_string => 'Social Security Tips'
434 ,p_bold => 'Y'
435 ,p_type => 'C'
436 ,p_output_file_type => p_output_file_type) ||
437 formated_data_string (p_input_string => 'Soc. Sec. Tax on Tips'
438 ,p_bold => 'Y'
439 ,p_type => 'C'
440 ,p_output_file_type => p_output_file_type) ||
441 formated_data_string (p_input_string => 'Medicare Tax on Tips'
442 ,p_bold => 'Y'
443 ,p_type => 'C'
444 ,p_output_file_type => p_output_file_type) ||
445 formated_data_string (p_input_string => 'HIRE Act'
446 ,p_bold => 'Y'
447 ,p_type => 'C'
448 ,p_output_file_type => p_output_file_type) || --10287981
449 formated_data_string (p_input_string => 'HIRE Act Wages'
450 ,p_bold => 'Y'
451 ,p_type => 'C'
452 ,p_output_file_type => p_output_file_type) || --10435466
453 formated_data_string (p_input_string => 'W2 Charitable Contributions'
454 ,p_bold => 'Y'
455 ,p_type => 'C'
456 ,p_output_file_type => p_output_file_type) || --13462230
457 formated_data_string (p_input_string => 'W2 Contr To Save and Double Money'
458 ,p_bold => 'Y'
459 ,p_type => 'C'
460 ,p_output_file_type => p_output_file_type) || --13462230
461 formated_data_string (p_input_string => 'W2 Health Coverage'
462 ,p_bold => 'Y'
463 ,p_type => 'C'
464 ,p_output_file_type => p_output_file_type); --13462230
465
466 END IF;
467
468
469 RETURN (lv_format1);
470 END;
471
472 FUNCTION get_blanks(p_length in number)
473 RETURN VARCHAR2
474 IS
475 BEGIN
476 return(lpad(' ',p_length,' '));
477 END;
478 FUNCTION format_data(p_data in varchar2,p_length in number)
479 RETURN VARCHAR2
480 IS
481 BEGIN
482 return(lpad(substr(p_data,1,p_length),p_length,' '));
483 END;
484
485 FUNCTION employee_data (
486 p_tax_unit_name in varchar2
490 ,p_er_addr_line_3 in varchar2
487 ,p_ein in varchar2
488 ,p_er_addr_line_1 in varchar2
489 ,p_er_addr_line_2 in varchar2
491 ,p_er_addr_city in varchar2
492 ,p_er_addr_state in varchar2
493 ,p_er_addr_zip in varchar2
494 ,p_er_phone in varchar2
495 ,p_year in varchar2
496 ,p_first_name in varchar2
497 ,p_surname in varchar2
498 ,p_ee_addr_line_1 in varchar2
499 ,p_ee_addr_line_2 in varchar2
500 ,p_ee_addr_line_3 in varchar2
501 ,p_ee_addr_city in varchar2
502 ,p_ee_addr_state in varchar2
503 ,p_ee_addr_zip in varchar2
504 ,p_ee_addr_country in varchar2
505 ,p_een in varchar2
506 ,p_eewc in varchar2
507 ,p_eeloc in varchar2
508 ,p_ee_hire_act in varchar2--10287981
509 ,p_ssn in varchar2
510 ,p_status in varchar2
511 ,p_spouse_ssn in varchar2
515 ,p_Allowances in number
512 ,p_Pension in number
513 ,p_Wages in number
514 ,p_Commissions in number
516 ,p_Tips in number
517 ,p_Total in number
518 ,p_Reimb_exp in number
519 ,p_Tax_wh in number
520 ,p_Retir_fund in number
521 ,p_Coda_plan in number
522 ,p_SS_Wages in number
523 ,p_SS_tax in number
524 ,p_Med_wages in number
525 ,p_Med_tax in number
526 ,p_SS_Tips in number
527 ,p_SS_Tax_on_tips in number
528 ,p_Med_Tax_on_tips in number
529 ,p_ee_hire_act_wages in number --10435466
530 ,p_ee_charitable_contributions in number --13462230
531 ,p_ee_save_and_double_money in number --13462230
532 ,p_ee_health_coverage in number --13462230
533 ,p_output_file_type in varchar2 )
534
535 RETURN VARCHAR2
536 IS
537
538 lv_format1 VARCHAR2(32000);
539 lv_format2 VARCHAR2(32000);
540
541
542 BEGIN
543
544 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
545 hr_utility.trace('ER Name = '||p_tax_unit_name);
546 hr_utility.trace('EIN = '||p_ein);
547 hr_utility.trace('Year = '||p_year);
548 hr_utility.trace('EE Name = '||p_first_name);
549 --5855662 Changed for employee file format
550 --7566756 Formatted p_ein
551 lv_format1 :=
552 formated_data_string (p_input_string => format_data(replace(p_ssn,'-',''),9)
553 ,p_type => 'C'
554 ,p_output_file_type => p_output_file_type)||
555 /*
556 formated_data_string (p_input_string => format_data(p_ssn,9)
557 ,p_type => 'C'
558 ,p_output_file_type => p_output_file_type)||
559 */
560 formated_data_string (p_input_string => format_data(p_een,30)
561 ,p_type => 'C'
562 ,p_output_file_type => p_output_file_type)||
563 formated_data_string (p_input_string => get_blanks(1)
564 ,p_type => 'C'
565 ,p_output_file_type => p_output_file_type)||
566 formated_data_string (p_input_string => format_data(p_first_name,14)
567 ,p_type => 'C'
568 ,p_output_file_type => p_output_file_type) ||
569 formated_data_string (p_input_string => format_data(p_surname,20)
570 ,p_type => 'C'
571 ,p_output_file_type => p_output_file_type) ||
572 formated_data_string (p_input_string => format_data(p_ee_addr_line_1,40) -- Bug 15874201
573 ,p_type => 'N'
574 ,p_output_file_type => p_output_file_type)||
575 formated_data_string (p_input_string => format_data(p_ee_addr_line_2,40) -- Bug 15874201
576 ,p_type => 'N'
577 ,p_output_file_type => p_output_file_type)||
578 formated_data_string (p_input_string => p_ee_addr_line_3--For the Bug#10435466
579 ,p_type => 'N'
580 ,p_output_file_type => p_output_file_type)||
581 formated_data_string (p_input_string => format_data(p_ee_addr_city,24)
582 ,p_type => 'N'
583 ,p_output_file_type => p_output_file_type)||
584 formated_data_string (p_input_string => format_data(p_ee_addr_state,2)
585 ,p_type => 'N'
586 ,p_output_file_type => p_output_file_type)||
587 formated_data_string (p_input_string => format_data(p_ee_addr_zip,10)
588 ,p_type => 'N'
589 ,p_output_file_type => p_output_file_type)||
590 formated_data_string (p_input_string => p_ee_addr_country
591 ,p_type => 'N'
592 ,p_output_file_type => p_output_file_type)||
593 formated_data_string (p_input_string => p_status
594 ,p_type => 'C'
595 ,p_output_file_type => p_output_file_type)||
596 formated_data_string (p_input_string => get_blanks(1)
597 ,p_type => 'C'
598 ,p_output_file_type => p_output_file_type)||
599 formated_data_string (p_input_string => get_blanks(1)
600 ,p_type => 'C'
604 ,p_output_file_type => p_output_file_type)||
601 ,p_output_file_type => p_output_file_type)||
602 formated_data_string (p_input_string => get_blanks(10)
603 ,p_type => 'C'
605 formated_data_string (p_input_string => get_blanks(1)
606 ,p_type => 'C'
607 ,p_output_file_type => p_output_file_type)||
608 formated_data_string (p_input_string => get_blanks(9)
609 ,p_type => 'N'
610 ,p_output_file_type => p_output_file_type)||
611 formated_data_string (p_input_string => format_data(p_eewc,4)
612 ,p_type => 'C'
613 ,p_output_file_type => p_output_file_type)||
614 formated_data_string (p_input_string => get_blanks(1)
615 ,p_type => 'C'
616 ,p_output_file_type => p_output_file_type)||
617 formated_data_string (p_input_string => get_blanks(1)
618 ,p_type => 'C'
619 ,p_output_file_type => p_output_file_type)||
620 formated_data_string (p_input_string => format_data(p_eeloc,20)
621 ,p_type => 'C'
622 ,p_output_file_type => p_output_file_type)||
623 formated_data_string (p_input_string => format_data(p_first_name||','||p_surname,35)
624 ,p_type => 'C'
625 ,p_output_file_type => p_output_file_type)||
626 formated_data_string (p_input_string => format_data(' ',8)
627 ,p_type => 'C'
628 ,p_output_file_type => p_output_file_type)||
629 formated_data_string (p_input_string => p_tax_unit_name
630 ,p_type => 'C'
631 ,p_output_file_type => p_output_file_type)||
632 -- 7566756 formatted employer id # to 9 characters with no hyphen
633 formated_data_string (p_input_string => format_data(replace(p_ein,'-',''),9)
634 ,p_type => 'N'
635 ,p_output_file_type => p_output_file_type)||
636 formated_data_string (p_input_string => p_er_addr_line_1
637 ,p_type => 'N'
638 ,p_output_file_type => p_output_file_type)||
639 formated_data_string (p_input_string => p_er_addr_line_2
640 ,p_type => 'N'
641 ,p_output_file_type => p_output_file_type)||
642 formated_data_string (p_input_string => p_er_addr_line_3
643 ,p_type => 'N'
644 ,p_output_file_type => p_output_file_type)||
645 formated_data_string (p_input_string => p_er_addr_city
646 ,p_type => 'N'
647 ,p_output_file_type => p_output_file_type)||
648 formated_data_string (p_input_string => p_er_addr_state
649 ,p_type => 'N'
650 ,p_output_file_type => p_output_file_type)||
651 formated_data_string (p_input_string => p_er_addr_zip
652 ,p_type => 'N'
653 ,p_output_file_type => p_output_file_type)||
654 formated_data_string (p_input_string => p_er_phone
655 ,p_type => 'N'
656 ,p_output_file_type => p_output_file_type)||
657 formated_data_string (p_input_string => p_year
658 ,p_type => 'N'
659 ,p_output_file_type => p_output_file_type)||
660 formated_data_string (p_input_string => p_surname
661 ,p_type => 'C'
662 ,p_output_file_type => p_output_file_type)||
663 formated_data_string (p_input_string => p_status
664 ,p_type => 'C'
665 ,p_output_file_type => p_output_file_type) ||
666 formated_data_string (p_input_string => p_spouse_ssn
667 ,p_type => 'C'
668 ,p_output_file_type => p_output_file_type) ||
669 formated_data_string (p_input_string => to_char(p_pension,'999,999,999,999,990.00')
670 ,p_type => 'N'
671 ,p_output_file_type => p_output_file_type) ||
672 formated_data_string (p_input_string => to_char(p_Wages,'999,999,999,999,990.00')
673 ,p_type => 'N'
674 ,p_output_file_type => p_output_file_type) ||
675 formated_data_string (p_input_string => to_char(p_Commissions,'999,999,999,999,990.00')
676 ,p_type => 'N'
677 ,p_output_file_type => p_output_file_type) ||
678 formated_data_string (p_input_string => to_char(p_Allowances,'999,999,999,999,990.00')
679 ,p_type => 'N'
683 ,p_output_file_type => p_output_file_type) ||
680 ,p_output_file_type => p_output_file_type) ||
681 formated_data_string (p_input_string => to_char(p_Tips,'999,999,999,999,990.00')
682 ,p_type => 'N'
684 formated_data_string (p_input_string => to_char(p_Total,'999,999,999,999,990.00')
685 ,p_type => 'N'
686 ,p_output_file_type => p_output_file_type) ||
687 formated_data_string (p_input_string => to_char(p_Reimb_exp,'999,999,999,999,990.00')
688 ,p_type => 'N'
689 ,p_output_file_type => p_output_file_type) ||
690 formated_data_string (p_input_string => to_char(p_Tax_wh,'999,999,999,999,990.00')
691 ,p_type => 'N'
692 ,p_output_file_type => p_output_file_type) ||
693 formated_data_string (p_input_string => to_char(p_Retir_fund,'999,999,999,999,990.00')
694 ,p_type => 'N'
695 ,p_output_file_type => p_output_file_type)||
699 formated_data_string (p_input_string => to_char(p_SS_Wages ,'999,999,999,999,990.00')
696 formated_data_string (p_input_string => to_char(p_Coda_plan,'999,999,999,999,990.00')
697 ,p_type => 'N'
698 ,p_output_file_type => p_output_file_type) ||
700 ,p_type => 'N'
701 ,p_output_file_type => p_output_file_type) ||
702 formated_data_string (p_input_string => to_char(p_SS_tax,'999,999,999,999,990.00')
703 ,p_type => 'N'
704 ,p_output_file_type => p_output_file_type) ||
705 formated_data_string (p_input_string => to_char(p_Med_wages,'999,999,999,999,990.00')
706 ,p_type => 'N'
707 ,p_output_file_type => p_output_file_type) ||
708 formated_data_string (p_input_string => to_char(p_Med_tax ,'999,999,999,999,990.00')
709 ,p_type => 'N'
710 ,p_output_file_type => p_output_file_type) ||
711 formated_data_string (p_input_string => to_char(p_SS_Tips ,'999,999,999,999,990.00')
712 ,p_type => 'N'
713 ,p_output_file_type => p_output_file_type) ||
714 formated_data_string (p_input_string => to_char(p_SS_Tax_on_tips,'999,999,999,999,990.00')
715 ,p_type => 'N'
716 ,p_output_file_type => p_output_file_type) ||
717 formated_data_string (p_input_string => to_char(p_Med_Tax_on_tips,'999,999,999,999,990.00')
718 ,p_type => 'N'
719 ,p_output_file_type => p_output_file_type) ||
720 formated_data_string (p_input_string => p_ee_hire_act
721 ,p_type => 'C'
722 ,p_output_file_type => p_output_file_type) || --for the Bug 10287981
723 formated_data_string (p_input_string => to_char(p_ee_hire_act_wages,'999,999,999,999,990.00')
724 ,p_type => 'N'
725 ,p_output_file_type => p_output_file_type) || -- 10435466
726 formated_data_string (p_input_string => to_char(p_ee_charitable_contributions,'999,999,999,999,990.00')
727 ,p_type => 'N'
728 ,p_output_file_type => p_output_file_type) || -- 13462230
729 formated_data_string (p_input_string => to_char(p_ee_save_and_double_money,'999,999,999,999,990.00')
733 ,p_type => 'N'
730 ,p_type => 'N'
731 ,p_output_file_type => p_output_file_type) || -- 13462230
732 formated_data_string (p_input_string => to_char(p_ee_health_coverage,'999,999,999,999,990.00')
734 ,p_output_file_type => p_output_file_type); -- 13462230
735
736 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
737
738
739 hr_utility.trace('Static Data1 = ' || lv_format1);
740 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
741
742 return (lv_format1);
743 END;
744
745
746 PROCEDURE insert_pr_w2_totals(errbuf OUT nocopy VARCHAR2,
747 retcode OUT nocopy NUMBER,
748 p_seq_num IN VARCHAR2,
749 p_report_type IN VARCHAR2) is
750
751 cursor c_er_total (c_seq_num in VARCHAR2,p_report_type in VARCHAR2) is
752 SELECT
753 attribute8 er_name,
754 attribute9 ein,
755 attribute11 er_addr_line_1,
756 attribute12 er_addr_line_2,
757 attribute13 er_addr_line_3,
758 attribute14 er_addr_city,
759 attribute15 er_addr_state,
760 attribute16 er_addr_zip,
761 attribute17 er_phone,
762 attribute10 year,
763 sum(value1) pension,
764 sum(value2) wages,
765 sum(value3) commissions,
766 sum(value4) allowances,
767 sum(value5) tips,
768 sum(value6) total,
769 sum(value7) reimb,
770 sum(value8) tax,
771 sum(value9) ret_fund,
772 sum(value10) coda,
773 sum(value11) ss_wages,
774 sum(value12) ss_tax,
775 sum(value13) med_wages,
776 sum(value14) med_tax,
777 sum(value15) ss_tips,
778 sum(value16) ss_tax_on_tips,
779 sum(value17) med_tax_on_tips,
780 sum(value18) hire_act_wages, --10435466
781 sum(value19) w2_charitable_contributions, --13462230
782 sum(value20) w2_save_and_double_money, --13462230
783 sum(value21) w2_health_coverage --13462230
784 FROM pay_us_rpt_totals
785 WHERE attribute2 = p_report_type
786 AND session_id = to_number(p_seq_num)
787 GROUP BY attribute8,attribute9, attribute11,attribute12,attribute13,attribute14,attribute15,attribute16,attribute17,attribute10;
788
789
790 cursor c_ee_exception (c_seq_num in VARCHAR2,p_report_type in VARCHAR2) is
791 SELECT
792 attribute8 er_name,
793 attribute9 ein,
794 attribute11 er_addr_line_1,
795 attribute12 er_addr_line_2,
796 attribute13 er_addr_line_3,
797 attribute14 er_addr_city,
798 attribute15 er_addr_state,
799 attribute16 er_addr_zip,
800 attribute17 er_phone,
801 attribute10 year,
802 attribute3 ee_name,
803 attribute4 ee_last_name,
804 attribute18 ee_addr_line_1,
805 attribute19 ee_addr_line_2,
806 attribute20 ee_addr_line_3,
807 attribute21 ee_addr_city,
808 attribute22 ee_addr_state,
809 attribute23 ee_addr_zip,
810 attribute24 ee_addr_country,
811 attribute25 ee_number,
812 attribute26 ee_wc,
813 attribute27 ee_loc,
814 attribute28 ee_hire_act,--10287981
815 attribute5 ssn, --Change 5855662
816 attribute6 status,
817 attribute7 spouse_ssn,
818 value1 pension,
819 value2 wages,
820 value3 commissions,
821 value4 allowances,
822 value5 tips,
823 value6 total,
824 value7 reimb,
825 value8 tax,
829 value12 ss_tax,
826 value9 ret_fund,
827 value10 coda,
828 value11 ss_wages,
830 value13 med_wages,
831 value14 med_tax,
832 value15 ss_tips,
833 value16 ss_tax_on_tips,
834 value17 med_tax_on_tips,
835 value18 hire_act_wages, --10435466
836 value19 w2_charitable_contributions, --13462230
837 value20 w2_save_and_double_money, --13462230
838 value21 w2_health_coverage --13462230
839 FROM pay_us_rpt_totals
840 WHERE attribute2 = p_report_type
841 AND session_id = to_number(p_seq_num);
842
843 TYPE numeric_table IS TABLE OF number(20,2)
844 INDEX BY BINARY_INTEGER;
845
846 TYPE text_table IS TABLE OF varchar2(2000)
847 INDEX BY BINARY_INTEGER;
848
849 ee_first_name varchar2(240);
850 ee_last_name varchar2(240);
851 ee_ssn varchar2(240);
852 ee_n varchar2(240);
853 ee_status varchar2(240);
854 ee_spouse_ssn varchar2(240);
855 ee_wages number(22,2) := 0.00;
856 ee_pension number(22,2) := 0.00;
857 ee_commissions number(22,2) := 0.00;
858 ee_allowances number(22,2) := 0.00;
859 ee_tips number(22,2) := 0.00;
860 ee_total number(22,2) := 0.00;
861 ee_reimb number(22,2) := 0.00;
862 ee_tax number(22,2) := 0.00;
863 ee_ret_fund number(22,2) := 0.00;
864 ee_coda number(22,2) := 0.00;
865 ee_ss_wages number(22,2) := 0.00;
866 ee_ss_Tax number(22,2) := 0.00;
867 ee_ss_tips number(22,2) := 0.00;
868 ee_med_Wages number(22,2) := 0.00;
869 ee_med_tax number(22,2) := 0.00;
870 ee_ss_tax_on_tips number(22,2) := 0.00;
871 ee_med_tax_on_tips number(22,2) := 0.00;
872 ee_hire_act_wages number(22,2) := 0.00; --10435466
873 ee_charitable_contributions number(22,2) := 0.00;--13462230
874 ee_save_and_double_money number(22,2) := 0.00;--13462230
875 ee_health_coverage number(22,2) := 0.00;--13462230
876
877 er_name varchar2(240);
878 er_ein varchar2(240);
879 year varchar2(240);
880 er_addr_line_1 varchar2(50);
881 er_addr_line_2 varchar2(50);
882 er_addr_line_3 varchar2(50);
883 er_addr_city varchar2(50);
884 er_addr_state varchar2(50);
885 er_addr_zip varchar2(50);
886 er_phone varchar2(50);
887 ee_addr_line_1 varchar2(50);
888 ee_addr_line_2 varchar2(50);
889 ee_addr_line_3 varchar2(50);
890 ee_addr_city varchar2(50);
891 ee_addr_state varchar2(50);
892 ee_addr_zip varchar2(50);
893 ee_addr_country varchar2(50);
894 ee_wc varchar2(20);
895 ee_loc varchar2(60);
896 ee_hire_act varchar2(10);--10287981
897
898
899 l_output_file_type varchar2(10);
900 i number(2);
901 j number(2);
902 ln_count number(2);
903
904 BEGIN
905
906 -- hr_utility.trace_on(null, 'PRW2');
907 hr_utility.trace('Entered Main package');
908 hr_utility.trace('p_seq_num = '||p_seq_num);
909 hr_utility.trace('p_report_type = '||p_report_type);
910
911 /* report_type
912 PRW2REGISTER - Register Report
913 PRW2TOTAL - Total Report
914 PRW2EXCEPTION - Exception Report
915 */
916
917 If p_report_type = 'PRW2TOTAL' then
918
919 l_output_file_type := 'HTML';
920
921 /* Modified report to insert records with
922 report_type of 'PRW2REGISTER' for employee
923 detail and we can have the Total as a SUM
924 from the detail records. So now we are not
925 inserting seperate SUM and hence passing the
926 report type of 'PRW2REGISTER'
927 */
928
929 i := 0;
930
931 open c_er_total(p_seq_num,'PRW2REGISTER');
932 hr_utility.trace ('Opened c_er_total cursor');
933
934 fetch c_er_total into er_name,
935 er_ein,
936 er_addr_line_1,
937 er_addr_line_2,
938 er_addr_line_3,
939 er_addr_city,
940 er_addr_state,
941 er_addr_zip,
942 er_phone,
943 year,
944 ee_pension,
945 ee_wages,
946 ee_commissions,
947 ee_allowances,
948 ee_tips,
949 ee_total,
950 ee_reimb,
951 ee_tax,
952 ee_ret_fund,
953 ee_coda,
954 ee_ss_wages,
955 ee_ss_Tax,
956 ee_med_Wages,
957 ee_med_tax,
958 ee_ss_tips,
959 ee_ss_tax_on_tips,
960 ee_med_tax_on_tips,
961 ee_hire_act_wages,--10435466
965
962 ee_charitable_contributions,--13462230
963 ee_save_and_double_money,--13462230
964 ee_health_coverage;--13462230
966 if c_er_total%NOTFOUND then
967
968 hr_utility.trace('No total information found for seq num = '||p_seq_num);
969
970 else
971
972 er_name := replace(er_name,',');
973
974 hr_utility.trace('wages ='||ee_wages);
975 hr_utility.trace('commissions ='||ee_commissions);
976 hr_utility.trace('allowances ='||ee_allowances);
977 hr_utility.trace('tips ='||ee_tips);
978 hr_utility.trace('total ='||ee_total);
979 hr_utility.trace('reimb ='||ee_reimb);
980 hr_utility.trace('tax ='||ee_tax);
981 hr_utility.trace('ret_fund ='||ee_ret_fund);
982 hr_utility.trace('coda ='||ee_coda);
983 hr_utility.trace('ss_wages ='||ee_ss_wages);
984 hr_utility.trace('ss_Tax ='||ee_ss_Tax);
985 hr_utility.trace('ss_tips ='||ee_ss_tips);
986 hr_utility.trace('med_Wages ='||ee_med_Wages);
990 hr_utility.trace('hire_act_wages='||ee_hire_act_wages); --10435466
987 hr_utility.trace('med_tax ='||ee_med_tax);
988 hr_utility.trace('ss_tax_on_tips ='||ee_ss_tax_on_tips);
989 hr_utility.trace('med_tax_on_tips='||ee_med_tax_on_tips);
991 hr_utility.trace('PR W2 Charitable Contributions='||ee_charitable_contributions); --13462230
992 hr_utility.trace('PR W2 Contr To Save and Double Money='||ee_save_and_double_money); --13462230
993 hr_utility.trace('W2 Health Coverage='||ee_health_coverage); --13462230
994
995
996 i := i+1;
997 total_rec_tab(i).r_label := 'Employer Name';
998 total_rec_tab(i).r_value := er_name;
999 total_rec_tab(i).r_type := 'C';
1000 i := i+1;
1001 total_rec_tab(i).r_label := 'Employer''s Address Line1';
1002 total_rec_tab(i).r_value := er_addr_line_1;
1003 total_rec_tab(i).r_type := 'C';
1004 i := i+1;
1005 total_rec_tab(i).r_label := 'Employer''s Address Line2';
1006 total_rec_tab(i).r_value := er_addr_line_2;
1007 total_rec_tab(i).r_type := 'C';
1008 i := i+1;
1009 total_rec_tab(i).r_label := 'Employer''s Address Line3';
1010 total_rec_tab(i).r_value := er_addr_line_3;
1011 total_rec_tab(i).r_type := 'C';
1012 i := i+1;
1013 total_rec_tab(i).r_label := 'Employer''s City';
1014 total_rec_tab(i).r_value := er_addr_city;
1015 total_rec_tab(i).r_type := 'C';
1016 i := i+1;
1017 total_rec_tab(i).r_label := 'Employer''s State';
1018 total_rec_tab(i).r_value := er_addr_state;
1019 total_rec_tab(i).r_type := 'C';
1020 i := i+1;
1021 total_rec_tab(i).r_label := 'Employer''s ZIP';
1022 total_rec_tab(i).r_value := er_addr_zip;
1023 total_rec_tab(i).r_type := 'C';
1024 i := i+1;
1025 total_rec_tab(i).r_label := 'Employer''s Phone';
1026 total_rec_tab(i).r_value := er_phone;
1027 total_rec_tab(i).r_type := 'C';
1028 i := i+1;
1029 total_rec_tab(i).r_label := 'Year';
1030 total_rec_tab(i).r_value := year;
1031 total_rec_tab(i).r_type := 'N';
1032 i := i+1;
1033 total_rec_tab(i).r_label := 'Employer''s Identification Number';
1034 total_rec_tab(i).r_value := er_ein;
1035 total_rec_tab(i).r_type := 'N';
1036 i := i+1;
1037 total_rec_tab(i).r_label := 'Pension';
1038 total_rec_tab(i).r_value := to_char(ee_pension,'999,999,999,999,990.00');
1039 total_rec_tab(i).r_type := 'N';
1040 i := i+1;
1041 total_rec_tab(i).r_label := 'Wages';
1042 total_rec_tab(i).r_value := to_char(ee_wages,'999,999,999,999,990.00');
1043 total_rec_tab(i).r_type := 'N';
1044 i := i+1;
1045 total_rec_tab(i).r_label := 'Commissions';
1046 total_rec_tab(i).r_value := to_char(ee_commissions,'999,999,999,999,990.00');
1047 total_rec_tab(i).r_type := 'N';
1048 i := i+1;
1049 total_rec_tab(i).r_label := 'Allowances';
1050 total_rec_tab(i).r_value := to_char(ee_allowances,'999,999,999,999,990.00');
1051 total_rec_tab(i).r_type := 'N';
1052 i := i+1;
1053 total_rec_tab(i).r_label := 'Tips';
1054 total_rec_tab(i).r_value := to_char(ee_tips,'999,999,999,999,990.00');
1055 total_rec_tab(i).r_type := 'N';
1056 i := i+1;
1057 total_rec_tab(i).r_label := 'Total';
1061 total_rec_tab(i).r_label := 'Reimbursed Expenses';
1058 total_rec_tab(i).r_value := to_char(ee_total,'999,999,999,999,990.00');
1059 total_rec_tab(i).r_type := 'N';
1060 i := i+1;
1062 total_rec_tab(i).r_value := to_char(ee_reimb,'999,999,999,999,990.00');
1063 total_rec_tab(i).r_type := 'N';
1064 i := i+1;
1065 total_rec_tab(i).r_label := 'Tax Withheld';
1066 total_rec_tab(i).r_value := to_char(ee_tax,'999,999,999,999,990.00');
1067 total_rec_tab(i).r_type := 'N';
1068 i := i+1;
1069 total_rec_tab(i).r_label := 'Retirement Fund';
1070 total_rec_tab(i).r_value := to_char(ee_ret_fund,'999,999,999,999,990.00');
1071 total_rec_tab(i).r_type := 'N';
1072 i := i+1;
1073 total_rec_tab(i).r_label := 'Contributions to CODA PLANS';
1074 total_rec_tab(i).r_value := to_char(ee_coda,'999,999,999,999,990.00');
1075 total_rec_tab(i).r_type := 'N';
1076 i := i+1;
1077 total_rec_tab(i).r_label := 'Soc.Security Wages';
1078 total_rec_tab(i).r_value := to_char(ee_ss_wages,'999,999,999,999,990.00');
1079 total_rec_tab(i).r_type := 'N';
1080 i := i+1;
1081 total_rec_tab(i).r_label := 'Soc. Sec Tax Withheld';
1082 total_rec_tab(i).r_value := to_char(ee_ss_Tax,'999,999,999,999,990.00');
1083 total_rec_tab(i).r_type := 'N';
1084 i := i+1;
1085 total_rec_tab(i).r_label := 'Medicare Wages and Tips';
1086 total_rec_tab(i).r_value := to_char(ee_med_Wages,'999,999,999,999,990.00');
1087 total_rec_tab(i).r_type := 'N';
1088 i := i+1;
1089 total_rec_tab(i).r_label := 'Medicare Tax Withheld';
1090 total_rec_tab(i).r_value := to_char(ee_med_tax,'999,999,999,999,990.00');
1091 total_rec_tab(i).r_type := 'N';
1092 i := i+1;
1093 total_rec_tab(i).r_label := 'Social Security Tips';
1094 total_rec_tab(i).r_value := to_char(ee_ss_tips,'999,999,999,999,990.00');
1095 total_rec_tab(i).r_type := 'N';
1096 i := i+1;
1097 total_rec_tab(i).r_label := 'Soc. Sec. Tax on Tips';
1098 total_rec_tab(i).r_value := to_char(ee_ss_tax_on_tips,'999,999,999,999,990.00');
1099 total_rec_tab(i).r_type := 'N';
1100 i := i+1;
1101 total_rec_tab(i).r_label := 'Medicare Tax on Tips';
1102 total_rec_tab(i).r_value := to_char(ee_med_tax_on_tips,'999,999,999,999,990.00');
1103 total_rec_tab(i).r_type := 'N';
1104 i := i+1; --10435466
1105 total_rec_tab(i).r_label := 'Hire Act Wages';
1106 total_rec_tab(i).r_value := to_char(ee_hire_act_wages,'999,999,999,999,990.00');
1110 total_rec_tab(i).r_value := to_char(ee_charitable_contributions,'999,999,999,999,990.00');
1107 total_rec_tab(i).r_type := 'N';
1108 i := i+1; --13462230
1109 total_rec_tab(i).r_label := 'W2 Charitable Contributions';
1111 total_rec_tab(i).r_type := 'N';
1112 i := i+1; --13462230
1113 total_rec_tab(i).r_label := 'W2 Contr To Save and Double Money';
1114 total_rec_tab(i).r_value := to_char(ee_save_and_double_money,'999,999,999,999,990.00');
1115 total_rec_tab(i).r_type := 'N';
1116 i := i+1; --13462230
1117 total_rec_tab(i).r_label := 'W2 Health Coverage';
1118 total_rec_tab(i).r_value := to_char(ee_health_coverage,'999,999,999,999,990.00');
1119 total_rec_tab(i).r_type := 'N';
1120
1121
1122
1123
1124
1125 /*
1126 for j in total_rec_tab.first .. total_rec_tab.last loop
1127 hr_utility.trace( 'J = '|| j );
1128 hr_utility.trace( 'label = '||total_rec_tab(j).r_label);
1129 hr_utility.trace( 'value = '||total_rec_tab(j).r_value);
1130 hr_utility.trace( 'type = '||total_rec_tab(j).r_type);
1131 end loop;
1132 */
1133
1134 hr_utility.trace( 'B4 formated header string ');
1135 fnd_file.put_line(fnd_file.output,
1136 formated_header_string(gv_title,'HTML'));
1137 fnd_file.new_line(fnd_file.output,1);
1138 hr_utility.trace( 'A4 formated header string new line ');
1139 /****************************************************************
1140 ** Print the Header Information. If the format is HTML then open
1141 ** the body and table before printing the header info, otherwise
1142 ** just print the header information.
1143 ****************************************************************/
1144 if l_output_file_type ='HTML' then
1145
1149 for i in total_rec_tab.first .. total_rec_tab.last loop
1146 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1147 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1148
1150
1151
1152 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1153 fnd_file.put_line(fnd_file.output
1154 ,formated_data_string(p_input_string => total_rec_tab(i).r_label
1155 ,p_bold => 'Y'
1156 ,p_type => 'C'
1157 ,p_output_file_type => l_output_file_type));
1158
1159 fnd_file.put_line(fnd_file.output
1160 ,formated_data_string(p_input_string => total_rec_tab(i).r_value
1161 ,p_bold => 'N'
1162 ,p_type => total_rec_tab(i).r_type
1163 ,p_output_file_type => l_output_file_type));
1164
1165 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1166 hr_utility.trace( 'label 0 = '||total_rec_tab(i).r_label);
1167 hr_utility.trace( 'value 0 = '||total_rec_tab(i).r_value);
1168 hr_utility.trace( 'value 0 = '||total_rec_tab(i).r_type);
1169 end loop ;
1170
1171 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1172 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</body>');
1173 end if;
1174
1175 /*
1176 select count(*)
1177 into ln_count
1178 FROM pay_us_rpt_totals
1179 WHERE attribute2 = 'PRW2REGISTER'
1180 AND session_id = to_number(p_seq_num);
1181
1182 hr_utility.trace('Total# of Records ='||to_char(ln_count));
1183 */
1184 DELETE from pay_us_rpt_totals
1185 WHERE attribute2 = 'PRW2REGISTER'
1186 AND session_id = to_number(p_seq_num);
1187
1188 end if;
1189
1190
1191 close c_er_total;
1192
1193 Else
1194 /* Employer Totals ends and Exception employees begin. */
1195
1196 OPEN c_ee_exception(p_seq_num,p_report_type);
1197
1198 LOOP
1199
1200 ee_pension := 0.00;
1201 ee_wages := 0.00;
1202 ee_commissions := 0.00;
1203 ee_allowances := 0.00;
1204 ee_tips := 0.00;
1205 ee_total := 0.00;
1206 ee_reimb := 0.00;
1207 ee_tax := 0.00;
1208 ee_ret_fund := 0.00;
1209 ee_coda := 0.00;
1210 ee_ss_wages := 0.00;
1211 ee_ss_Tax := 0.00;
1212 ee_ss_tips := 0.00;
1213 ee_med_Wages := 0.00;
1214 ee_med_tax := 0.00;
1215 ee_ss_tax_on_tips := 0.00;
1216 ee_med_tax_on_tips := 0.00;
1217 ee_hire_act_wages := 0.00;--10435466
1218 ee_charitable_contributions := 0.00;--13462230
1219 ee_save_and_double_money := 0.00;--13462230
1220 ee_health_coverage := 0.00;--13462230
1221
1222 ee_first_name := 0.00;
1223 ee_last_name := 0.00;
1224 ee_wc := 0.00;
1225 ee_ssn := 0.00;
1226 ee_loc := 0.00;
1227 ee_status := 0.00;
1228 ee_spouse_ssn := 0.00;
1229 ee_n := 0.00;
1230 er_addr_line_1 := 0.00;
1231 er_addr_line_2 := 0.00;
1232 er_addr_line_3 := 0.00;
1233 er_addr_city := 0.00;
1234 er_addr_state := 0.00;
1235 er_addr_zip := 0.00;
1236 er_phone := 0.00;
1237 ee_addr_line_1 := 0.00;
1238 ee_addr_line_2 := 0.00;
1239 ee_addr_line_3 := 0.00;
1240 ee_addr_city := 0.00;
1241 ee_addr_state := 0.00;
1242 ee_addr_zip := 0.00;
1243 ee_addr_country := 0.00;
1244
1245
1246
1247 FETCH c_ee_exception INTO
1248 er_name,
1249 er_ein,
1250 er_addr_line_1,
1251 er_addr_line_2,
1252 er_addr_line_3,
1253 er_addr_city,
1254 er_addr_state,
1255 er_addr_zip,
1256 er_phone,
1257 year,
1258 ee_first_name,
1259 ee_last_name,
1260 ee_addr_line_1,
1261 ee_addr_line_2,
1262 ee_addr_line_3,
1263 ee_addr_city,
1264 ee_addr_state,
1265 ee_addr_zip,
1266 ee_addr_country,
1267 ee_n,
1268 ee_wc,
1269 ee_loc,
1273 ee_spouse_ssn,
1270 ee_hire_act,--10287981
1271 ee_ssn,
1272 ee_status,
1274 ee_pension,
1275 ee_wages,
1276 ee_commissions,
1277 ee_allowances,
1278 ee_tips,
1279 ee_total,
1280 ee_reimb,
1281 ee_tax,
1282 ee_ret_fund,
1283 ee_coda,
1284 ee_ss_wages,
1285 ee_ss_Tax,
1286 ee_ss_tips,
1287 ee_med_Wages,
1288 ee_med_tax,
1289 ee_ss_tax_on_tips,
1290 ee_med_tax_on_tips,
1291 ee_hire_act_wages,--10435466
1292 ee_charitable_contributions,--13462230
1293 ee_save_and_double_money,--13462230
1294 ee_health_coverage;--13462230
1295
1296
1297 EXIT WHEN c_ee_exception%notfound;
1298
1299 hr_utility.trace('Exception value found for '||ee_last_name);
1300
1301 IF c_ee_exception%ROWCOUNT =1 THEN
1302
1303 fnd_file.put_line(fnd_file.output
1304 ,employee_header('CSV','Header1',p_report_type));
1305
1306 fnd_file.new_line(fnd_file.output,1);
1307
1308 fnd_file.put_line(fnd_file.output
1309 ,employee_header('CSV','Header2',p_report_type));
1310
1311 END IF;
1312
1313 fnd_file.put_line(fnd_file.output
1314 ,employee_data (
1315 er_name,
1316 er_ein,
1317 er_addr_line_1,
1318 er_addr_line_2,
1319 er_addr_line_3,
1320 er_addr_city,
1321 er_addr_state,
1322 er_addr_zip,
1323 er_phone,
1324 year,
1325 ee_first_name,
1326 ee_last_name,
1327 ee_addr_line_1,
1328 ee_addr_line_2,
1329 ee_addr_line_3,
1330 ee_addr_city,
1331 ee_addr_state,
1332 ee_addr_zip,
1333 ee_addr_country,
1334 ee_n,
1335 ee_wc,
1336 ee_loc,
1337 ee_hire_act,--10287981
1338 ee_ssn,
1339 ee_status,
1340 ee_spouse_ssn,
1341 ee_pension,
1342 ee_wages,
1343 ee_commissions,
1344 ee_allowances,
1345 ee_tips,
1346 ee_total,
1347 ee_reimb,
1348 ee_tax,
1349 ee_ret_fund,
1350 ee_coda,
1351 ee_ss_wages,
1352 ee_ss_Tax,
1353 ee_ss_tips,
1354 ee_med_Wages,
1355 ee_med_tax,
1356 ee_ss_tax_on_tips,
1357 ee_med_tax_on_tips,
1358 ee_hire_act_wages,--10435466
1359 ee_charitable_contributions,--13462230
1360 ee_save_and_double_money,--13462230
1361 ee_health_coverage,--13462230
1362 'CSV'));
1363
1364
1365 END LOOP;
1366
1367 if p_report_type = 'PRW2EXCEPTION' then
1368
1369 /*
1370 select count(*)
1371 into ln_count
1372 FROM pay_us_rpt_totals
1373 WHERE attribute2 = p_report_type
1374 AND session_id = to_number(p_seq_num);
1375
1376 hr_utility.trace('Total# of Records ='||to_char(ln_count));
1377 */
1378 DELETE FROM pay_us_rpt_totals
1379 WHERE attribute2 = p_report_type
1380 AND session_id = to_number(p_seq_num);
1381
1382 end if;
1383
1384 CLOSE c_ee_exception;
1385 End If; -- End of Report Type
1386 end;
1387 end pay_us_pr_w2;