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