[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W3C_RPT
Source
1 PACKAGE BODY pay_us_w3c_rpt AS
2 /* $Header: pyusw3cr.pkb 120.1 2006/11/16 13:03:40 saurgupt noship $*/
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_w3c_rpt
21 Description : This package is called by Employee W-2c Report
22
23 Change List
24 -----------
25 Date Name Vers Bug No Description
26 ---- ---- ------ ---------- -----------
27 24-FEB-2003 Asasthan 115.0 Created.
28 16-NOV-2006 Saurugpt 115.7 5562494 Change the box 14 value variables to varchar2.
29 This is needed as NJ Plan ID can be alphanumeric also.
30
31 ************************************************************
32 ** Local Package Variables
33 ************************************************************/
34 gv_title VARCHAR2(100) := 'W-2c Audit Information';
35 gv_title2 VARCHAR2(100) := '';
36 gc_csv_delimiter VARCHAR2(1) := ',';
37 gc_csv_data_delimiter VARCHAR2(1) := '"';
38
39 gv_package_name VARCHAR2(50) := 'pay_us_w3c_rpt';
40
41
42 /******************************************************************
43 ** Function Returns the formated input string based on the
44 ** Output format. If the format is CSV then the values are returned
45 ** seperated by comma (,).
46 ******************************************************************/
47
48 FUNCTION data_string
49 (p_input_string in varchar2
50 ,p_bold in varchar2 default 'N'
51 ,p_output_file_type in varchar2
52 )
53 RETURN VARCHAR2
54 IS
55
56 lv_format varchar2(32000);
57
58 BEGIN
59 hr_utility.set_location(gv_package_name || '.data_string', 10);
60
61 if p_output_file_type = 'CSV' then
62
63
64 hr_utility.set_location(gv_package_name || '.data_string', 20);
65
66 lv_format := gc_csv_data_delimiter || p_input_string ||
67 gc_csv_data_delimiter || gc_csv_delimiter;
68
69 end if;
70
71 hr_utility.set_location(gv_package_name || '.data_string', 60);
72
73 return lv_format;
74
75 END data_string;
76
77 /*****************************************************************
78 ** This procudure returns the Mandatory Static Labels and the
79 ** Other Additional Static columns.
80 *****************************************************************/
81
82 FUNCTION report_header(
83 p_output_file_type in varchar2,
84 p_header in varchar2
85 )
86 RETURN VARCHAR2
87 IS
88
89 lv_format1 varchar2(32000);
90 lv_format2 varchar2(32000);
91
92 BEGIN
93
94 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
95
96 IF p_header = 'Header1' THEN
97
98 lv_format1 := data_string (p_input_string => gv_title
99 ,p_bold => 'Y'
100 ,p_output_file_type => p_output_file_type);
101
102 ELSE
103
104 lv_format1 :=
105 data_string (p_input_string => 'GRE'
106 ,p_bold => 'Y'
107 ,p_output_file_type => p_output_file_type) ||
108 data_string (p_input_string => 'Employer''s Federal EIN '
109 ,p_bold => 'Y'
110 ,p_output_file_type => p_output_file_type) ||
111 data_string (p_input_string => 'Employee''s Name '
112 ,p_bold => 'Y'
113 ,p_output_file_type => p_output_file_type) ||
114 data_string (p_input_string => 'Incorrect Employee Name'
115 ,p_bold => 'Y'
116 ,p_output_file_type => p_output_file_type) ||
117 data_string (p_input_string => 'SSN '
118 ,p_bold => 'Y'
119 ,p_output_file_type => p_output_file_type) ||
120 data_string (p_input_string => 'Incorrect SSN'
121 ,p_bold => 'Y'
122 ,p_output_file_type => p_output_file_type) ||
123 data_string (p_input_string => 'Record Type'
124 ,p_bold => 'Y'
125 ,p_output_file_type => p_output_file_type) ||
126 data_string (p_input_string => 'Tax Year'
127 ,p_bold => 'Y'
128 ,p_output_file_type => p_output_file_type) ||
129 data_string (p_input_string => '''Wages, tips, other compensation (previous values)'''
130 ,p_bold => 'Y'
131 ,p_output_file_type => p_output_file_type) ||
132 data_string (p_input_string => '''Wages, tips, other compensation (corrected values)'''
133 ,p_bold => 'Y'
134 ,p_output_file_type => p_output_file_type) ||
135 data_string (p_input_string => 'Federal Income tax withheld (previous values)'
136 ,p_bold => 'Y'
137 ,p_output_file_type => p_output_file_type) ||
138 data_string (p_input_string => 'Federal Income tax withheld (corrected values)'
139 ,p_bold => 'Y'
140 ,p_output_file_type => p_output_file_type) ||
141 data_string (p_input_string => 'Social security wages (previous values)'
142 ,p_bold => 'Y'
143 ,p_output_file_type => p_output_file_type) ||
144 data_string (p_input_string => 'Social security wages (corrected values)'
145 ,p_bold => 'Y'
146 ,p_output_file_type => p_output_file_type) ||
147 data_string (p_input_string => 'Social security tax withheld (previous values)'
148 ,p_bold => 'Y'
149 ,p_output_file_type => p_output_file_type) ||
150 data_string (p_input_string => 'Social security tax withheld (corrected values)'
151 ,p_bold => 'Y'
152 ,p_output_file_type => p_output_file_type) ||
153 data_string (p_input_string => 'Medicare wages and tips (previous values)'
154 ,p_bold => 'Y'
155 ,p_output_file_type => p_output_file_type) ||
156 data_string (p_input_string => 'Medicare wages and tips (corrected values)'
157 ,p_bold => 'Y'
158 ,p_output_file_type => p_output_file_type) ||
159 data_string (p_input_string => 'Medicare tax withheld (previous values)'
160 ,p_bold => 'Y'
161 ,p_output_file_type => p_output_file_type) ||
162 data_string (p_input_string => 'Medicare tax withheld (corrected values)'
163 ,p_bold => 'Y'
164 ,p_output_file_type => p_output_file_type) ||
165 data_string (p_input_string => 'Social security tips (previous values)'
166 ,p_bold => 'Y'
167 ,p_output_file_type => p_output_file_type) ||
168 data_string (p_input_string => 'Social security tips (corrected values)'
169 ,p_bold => 'Y'
170 ,p_output_file_type => p_output_file_type) ||
171 data_string (p_input_string => 'Allocated tips (previous values)'
172 ,p_bold => 'Y'
173 ,p_output_file_type => p_output_file_type) ||
174 data_string (p_input_string => 'Allocated tips (corrected values)'
175 ,p_bold => 'Y'
176 ,p_output_file_type => p_output_file_type) ||
177 data_string (p_input_string => 'Advance EIC payments (previous values)'
178 ,p_bold => 'Y'
179 ,p_output_file_type => p_output_file_type) ||
180 data_string (p_input_string => 'Advance EIC payments (corrected values)'
181 ,p_bold => 'Y'
182 ,p_output_file_type => p_output_file_type) ||
183 data_string (p_input_string => 'Dependent care benefits (previous values)'
184 ,p_bold => 'Y'
185 ,p_output_file_type => p_output_file_type) ||
186 data_string (p_input_string => 'Dependent care benefits (corrected values)'
187 ,p_bold => 'Y'
188 ,p_output_file_type => p_output_file_type) ||
189 data_string (p_input_string => 'Nonqualified plans (previous values)'
190 ,p_bold => 'Y'
191 ,p_output_file_type => p_output_file_type) ||
192 data_string (p_input_string => 'Nonqualified plans (corrected values)'
193 ,p_bold => 'Y'
194 ,p_output_file_type => p_output_file_type) ||
195 data_string (p_input_string => 'Inc. tax W/H by 3rd party sick pay payer (previous values)'
196 ,p_bold => 'Y'
197 ,p_output_file_type => p_output_file_type) ||
198 data_string (p_input_string => 'Inc. tax W/H by 3rd party sick pay payer (corrected values)'
199 ,p_bold => 'Y'
200 ,p_output_file_type => p_output_file_type) ||
201 data_string (p_input_string => 'Box 12 Code'
202 ,p_bold => 'Y'
203 ,p_output_file_type => p_output_file_type) ||
204 data_string (p_input_string => 'Box 12 (previous values)'
205 ,p_bold => 'Y'
206 ,p_output_file_type => p_output_file_type) ||
207 data_string (p_input_string => 'Box 12 (corrected values)'
208 ,p_bold => 'Y'
209 ,p_output_file_type => p_output_file_type) ||
210 data_string (p_input_string => 'Box 14 Code'
211 ,p_bold => 'Y'
212 ,p_output_file_type => p_output_file_type) ||
213 data_string (p_input_string => 'Box 14 (previous values)'
214 ,p_bold => 'Y'
215 ,p_output_file_type => p_output_file_type) ||
216 data_string (p_input_string => 'Box 14 (corrected values)'
217 ,p_bold => 'Y'
218 ,p_output_file_type => p_output_file_type) ||
219 data_string (p_input_string => 'State'
220 ,p_bold => 'Y'
221 ,p_output_file_type => p_output_file_type) ||
222 data_string (p_input_string => 'State EIN'
223 ,p_bold => 'Y'
224 ,p_output_file_type => p_output_file_type) ||
225 data_string (p_input_string => 'State Incorrect EIN'
226 ,p_bold => 'Y'
227 ,p_output_file_type => p_output_file_type) ||
228 data_string (p_input_string => '''State wages, tips, etc. (previous values)'''
229 ,p_bold => 'Y'
230 ,p_output_file_type => p_output_file_type) ||
231 data_string (p_input_string => '''State wages, tips, etc. (corrected values)'''
232 ,p_bold => 'Y'
233 ,p_output_file_type => p_output_file_type) ||
234 data_string (p_input_string => 'State income tax (previous values)'
235 ,p_bold => 'Y'
236 ,p_output_file_type => p_output_file_type) ||
237 data_string (p_input_string => 'State income tax (corrected values)'
238 ,p_bold => 'Y'
239 ,p_output_file_type => p_output_file_type) ||
240 data_string (p_input_string => 'State-Locality'
241 ,p_bold => 'Y'
242 ,p_output_file_type => p_output_file_type) ||
243 data_string (p_input_string => '''Local wages, tips, etc. (previous values)'''
244 ,p_bold => 'Y'
245 ,p_output_file_type => p_output_file_type) ||
246 data_string (p_input_string => '''Local wages, tips, etc. (corrected values)'''
247 ,p_bold => 'Y'
248 ,p_output_file_type => p_output_file_type) ||
249 data_string (p_input_string => 'Local income tax (previous values)'
250 ,p_bold => 'Y'
251 ,p_output_file_type => p_output_file_type) ||
252 data_string (p_input_string => 'Local income tax (corrected values)'
253 ,p_bold => 'Y'
254 ,p_output_file_type => p_output_file_type) ;
255
256 END IF;
257
258 RETURN (lv_format1);
259 END;
260
261
262 FUNCTION report_data (
263 p_employer_name in varchar2,
264 p_federal_ein in varchar2,
265 p_employee_name in varchar2,
266 p_incorrect_name in varchar2,
267 p_ssn in varchar2,
268 p_incorrect_ssn in varchar2,
269 p_record_type in varchar2,
270 p_tax_year in varchar2,
271 p_wages_old in number,
272 p_wages_new in number,
273 p_fit_old in number,
274 p_fit_new in number,
275 p_ss_wages_old in number,
276 p_ss_wages_new in number,
277 p_ss_withheld_old in number,
278 p_ss_withheld_new in number,
279 p_medi_wages_old in number,
280 p_medi_wages_new in number,
281 p_medi_withheld_old in number,
282 p_medi_withheld_new in number,
283 p_ss_tips_old in number,
284 p_ss_tips_new in number,
285 p_alloc_tips_old in number,
286 p_alloc_tips_new in number,
287 p_eic_old in number,
288 p_eic_new in number,
289 p_depcare_old in number,
290 p_depcare_new in number,
291 p_nonqual_old in number,
292 p_nonqual_new in number,
293 p_third_party_sick_old in number,
294 p_third_party_sick_new in number,
295 p_box_12_code in varchar2,
296 p_box_12_value_old in number,
297 p_box_12_value_new in number,
298 p_box_14_code in varchar2,
299 p_box_14_value_old in varchar2, --number, Bug 5562494
300 p_box_14_value_new in varchar2, --number,
304 p_ss_wages_tips_old in number,
301 p_state_abbrev in varchar2,
302 p_state_id_number in varchar2,
303 p_state_inco_id_number in varchar2,
305 p_ss_wages_tips_new in number,
306 p_sit_old in number,
307 p_sit_new in number,
308 p_state_local_name in varchar2,
309 p_local_wages_old in number,
310 p_local_wages_new in number,
311 p_local_withheld_old in number,
312 p_local_withheld_new in number,
313 p_output_file_type in varchar2
314 )
315 RETURN VARCHAR2
316 IS
317
318 lv_format1 VARCHAR2(32000);
319 lv_format2 VARCHAR2(32000);
320
321
322 BEGIN
323
324 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
325 lv_format1 :=
326 data_string (p_input_string => p_employer_name
327 ,p_output_file_type => p_output_file_type)||
328 data_string (p_input_string => p_federal_ein
329 ,p_output_file_type => p_output_file_type)||
330 data_string (p_input_string => p_employee_name
331 ,p_output_file_type => p_output_file_type)||
332 data_string (p_input_string => p_incorrect_name
333 ,p_output_file_type => p_output_file_type)||
334 data_string (p_input_string => p_ssn
335 ,p_output_file_type => p_output_file_type)||
336 data_string (p_input_string => p_incorrect_ssn
337 ,p_output_file_type => p_output_file_type)||
338 data_string (p_input_string => p_record_type
339 ,p_output_file_type => p_output_file_type)||
340 data_string (p_input_string => p_tax_year
341 ,p_output_file_type => p_output_file_type)||
342 data_string (p_input_string => p_wages_old
343 ,p_output_file_type => p_output_file_type)||
344 data_string (p_input_string => p_wages_new
345 ,p_output_file_type => p_output_file_type)||
346 data_string (p_input_string => p_fit_old
347 ,p_output_file_type => p_output_file_type)||
348 data_string (p_input_string => p_fit_new
349 ,p_output_file_type => p_output_file_type)||
350 data_string (p_input_string => p_ss_wages_old
351 ,p_output_file_type => p_output_file_type)||
352 data_string (p_input_string => p_ss_wages_new
353 ,p_output_file_type => p_output_file_type)||
354 data_string (p_input_string => p_ss_withheld_old
355 ,p_output_file_type => p_output_file_type)||
356 data_string (p_input_string => p_ss_withheld_new
357 ,p_output_file_type => p_output_file_type)||
358 data_string (p_input_string => p_medi_wages_old
359 ,p_output_file_type => p_output_file_type)||
360 data_string (p_input_string => p_medi_wages_new
361 ,p_output_file_type => p_output_file_type)||
362 data_string (p_input_string => p_medi_withheld_old
363 ,p_output_file_type => p_output_file_type)||
364 data_string (p_input_string => p_medi_withheld_new
365 ,p_output_file_type => p_output_file_type)||
366 data_string (p_input_string => p_ss_tips_old
367 ,p_output_file_type => p_output_file_type)||
368 data_string (p_input_string => p_ss_tips_new
369 ,p_output_file_type => p_output_file_type)||
370 data_string (p_input_string => p_alloc_tips_old
371 ,p_output_file_type => p_output_file_type)||
372 data_string (p_input_string => p_alloc_tips_new
373 ,p_output_file_type => p_output_file_type)||
374 data_string (p_input_string => p_eic_old
375 ,p_output_file_type => p_output_file_type)||
376 data_string (p_input_string => p_eic_new
377 ,p_output_file_type => p_output_file_type)||
378 data_string (p_input_string => p_depcare_old
379 ,p_output_file_type => p_output_file_type)||
380 data_string (p_input_string => p_depcare_new
381 ,p_output_file_type => p_output_file_type)||
382 data_string (p_input_string => p_nonqual_old
383 ,p_output_file_type => p_output_file_type)||
384 data_string (p_input_string => p_nonqual_new
385 ,p_output_file_type => p_output_file_type)||
386 data_string (p_input_string => p_third_party_sick_old
387 ,p_output_file_type => p_output_file_type)||
388 data_string (p_input_string => p_third_party_sick_new
389 ,p_output_file_type => p_output_file_type)||
390 data_string (p_input_string => p_box_12_code
394 data_string (p_input_string => p_box_12_value_new
391 ,p_output_file_type => p_output_file_type)||
392 data_string (p_input_string => p_box_12_value_old
393 ,p_output_file_type => p_output_file_type)||
395 ,p_output_file_type => p_output_file_type)||
396 data_string (p_input_string => p_box_14_code
397 ,p_output_file_type => p_output_file_type)||
398 data_string (p_input_string => p_box_14_value_old
399 ,p_output_file_type => p_output_file_type)||
400 data_string (p_input_string => p_box_14_value_new
401 ,p_output_file_type => p_output_file_type)||
402 data_string (p_input_string => p_state_abbrev
403 ,p_output_file_type => p_output_file_type)||
404 data_string (p_input_string => p_state_id_number
405 ,p_output_file_type => p_output_file_type)||
406 data_string (p_input_string => p_state_inco_id_number
407 ,p_output_file_type => p_output_file_type)||
408 data_string (p_input_string => p_ss_wages_tips_old
409 ,p_output_file_type => p_output_file_type)||
410 data_string (p_input_string => p_ss_wages_tips_new
411 ,p_output_file_type => p_output_file_type)||
412 data_string (p_input_string => p_sit_old
413 ,p_output_file_type => p_output_file_type)||
414 data_string (p_input_string => p_sit_new
415 ,p_output_file_type => p_output_file_type)||
416 data_string (p_input_string => p_state_local_name
417 ,p_output_file_type => p_output_file_type)||
418 data_string (p_input_string => p_local_wages_old
419 ,p_output_file_type => p_output_file_type)||
420 data_string (p_input_string => p_local_wages_new
421 ,p_output_file_type => p_output_file_type)||
422 data_string (p_input_string => p_local_withheld_old
423 ,p_output_file_type => p_output_file_type)||
424 data_string (p_input_string => p_local_withheld_new
425 ,p_output_file_type => p_output_file_type);
426
427
428
429 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
430
431
432 hr_utility.trace('Static Data1 = ' || lv_format1);
433 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
434
435 return (lv_format1);
436 END;
437
438
439 PROCEDURE insert_w3c_dtls(errbuf OUT nocopy VARCHAR2,
440 retcode OUT nocopy NUMBER,
441 p_seq_num IN VARCHAR2) is
442
443 cursor c_get_data(c_seq_num in VARCHAR2) is
444 SELECT tax_unit_id tax_unit_id,
445 nvl(attribute6,' ') employer_name,
446 nvl(attribute7,' ') federal_ein,
447 nvl(attribute4,' ') employee_name,
448 nvl(attribute5,' ') ssn,
449 nvl(attribute3,' ') record_type,
450 nvl(attribute2,' ') tax_year,
451 nvl(value1,0) wages_old,
452 nvl(value2,0) wages_new,
453 nvl(value3,0) fit_old,
454 nvl(value4,0) fit_new,
455 nvl(value5,0) ss_wages_old,
456 nvl(value6,0) ss_wages_new,
457 nvl(value7,0) ss_withheld_old,
458 nvl(value8,0) ss_withheld_new,
459 nvl(value9,0) medi_wages_old,
460 nvl(value10,0) medi_wages_new,
461 nvl(value11,0) medi_withheld_old,
462 nvl(value12,0) medi_withheld_new,
463 nvl(value13,0) ss_tips_old,
464 nvl(value14,0) ss_tips_new,
465 nvl(value15,0) alloc_tips_old,
466 nvl(value16,0) alloc_tips_new,
467 nvl(value17,0) eic_old,
468 nvl(value18,0) eic_new,
469 nvl(value19,0) depcare_old,
470 nvl(value20,0) depcare_new,
471 nvl(value21,0) nonqual_old,
472 nvl(value22,0) nonqual_new,
473 nvl(value23,0) third_party_sick_old,
474 nvl(value24,0) third_party_sick_new,
475 nvl(attribute9,' ') box_12_code,
476 to_number(nvl(attribute10,0)) box_12_value_old,
477 to_number(nvl(attribute11,0)) box_12_value_new,
478 nvl(attribute12,' ') box_14_code ,
479 nvl(attribute13,0) box_14_value_old, -- Bug 5562494 to_number(nvl(attribute13,0))
480 nvl(attribute14,0) box_14_value_new, -- to_number(nvl(attribute14,0))
481 nvl(attribute15,' ') state_abbrev,
482 nvl(attribute16,' ') state_id_number,
483 nvl(attribute17,' ') state_inco_id_number,
484 to_number(nvl(attribute18,0)) ss_wages_tips_old,
488 nvl(attribute22,' ') state_local_name,
485 to_number(nvl(attribute19,0)) ss_wages_tips_new,
486 to_number(nvl(attribute20,0)) sit_old,
487 to_number(nvl(attribute21,0)) sit_new,
489 to_number(nvl(attribute23,0)) local_wages_old,
490 to_number(nvl(attribute24,0)) local_wages_new,
491 to_number(nvl(attribute25,0)) local_withheld_old,
492 to_number(nvl(attribute26,0)) local_withheld_new,
493 nvl(attribute27,' ') incorrect_name,
494 nvl(attribute28,' ') incorrect_ssn
495 FROM pay_us_rpt_totals
496 WHERE session_id = to_number(c_seq_num)
497 AND attribute3 in ('FEDERAL','BOX 12','BOX 14','STATE','LOCAL')
498 order by attribute4,
499 attribute5,
500 decode(attribute3,'FEDERAL','1',
501 'BOX 12','2',
502 'BOX 14','3',
503 'STATE','4',
504 'LOCAL','5','6') ;
505
506
507 TYPE numeric_table IS TABLE OF number(20,2)
508 INDEX BY BINARY_INTEGER;
509
510 TYPE text_table IS TABLE OF varchar2(2000)
511 INDEX BY BINARY_INTEGER;
512
513 l_tax_unit_id pay_us_rpt_totals.tax_unit_id%TYPE := 9999999;
514 l_wages_old pay_us_rpt_totals.value1%TYPE := 0;
515 l_wages_new pay_us_rpt_totals.value2%TYPE := 0;
516 l_fit_old pay_us_rpt_totals.value3%TYPE := 0;
517 l_fit_new pay_us_rpt_totals.value4%TYPE := 0;
518 l_ss_wages_old pay_us_rpt_totals.value5%TYPE := 0;
519 l_ss_wages_new pay_us_rpt_totals.value6%TYPE := 0;
520 l_ss_withheld_old pay_us_rpt_totals.value7%TYPE := 0;
521 l_ss_withheld_new pay_us_rpt_totals.value8%TYPE := 0;
522 l_medi_wages_old pay_us_rpt_totals.value9%TYPE := 0;
523 l_medi_wages_new pay_us_rpt_totals.value10%TYPE := 0;
524 l_medi_withheld_old pay_us_rpt_totals.value11%TYPE := 0;
525 l_medi_withheld_new pay_us_rpt_totals.value12%TYPE := 0;
526 l_ss_tips_old pay_us_rpt_totals.value13%TYPE := 0;
527 l_ss_tips_new pay_us_rpt_totals.value14%TYPE := 0;
528 l_alloc_tips_old pay_us_rpt_totals.value15%TYPE := 0;
529 l_alloc_tips_new pay_us_rpt_totals.value16%TYPE := 0;
530 l_eic_old pay_us_rpt_totals.value17%TYPE := 0;
531 l_eic_new pay_us_rpt_totals.value18%TYPE := 0;
532 l_depcare_old pay_us_rpt_totals.value19%TYPE := 0;
533 l_depcare_new pay_us_rpt_totals.value20%TYPE :=0 ;
534 l_nonqual_old pay_us_rpt_totals.value21%TYPE :=0 ;
535 l_nonqual_new pay_us_rpt_totals.value22%TYPE :=0 ;
536 l_third_party_sick_old pay_us_rpt_totals.value23%TYPE := 0;
537 l_third_party_sick_new pay_us_rpt_totals.value24%TYPE := 0;
538 l_report_type pay_us_rpt_totals.attribute1%TYPE := ' ' ;
539 l_tax_year pay_us_rpt_totals.attribute2%TYPE := ' ';
540 l_record_type pay_us_rpt_totals.attribute3%TYPE := ' ';
541 l_employee_name pay_us_rpt_totals.attribute4%TYPE := ' ';
542 l_ssn pay_us_rpt_totals.attribute5%TYPE := ' ';
543 l_employer_name pay_us_rpt_totals.attribute6%TYPE := ' ';
544 l_federal_ein pay_us_rpt_totals.attribute7%TYPE := ' ';
545 l_fed_ein pay_us_rpt_totals.attribute7%TYPE := ' ';
546 l_box_12_code pay_us_rpt_totals.attribute9%TYPE := ' ';
547 l_box_12_value_old pay_us_rpt_totals.attribute10%TYPE := ' ';
548 l_box_12_value_new pay_us_rpt_totals.attribute11%TYPE := ' ';
549 l_box_14_code pay_us_rpt_totals.attribute12%TYPE := ' ';
550 l_box_14_value_old pay_us_rpt_totals.attribute13%TYPE := ' ';
551 l_box_14_value_new pay_us_rpt_totals.attribute14%TYPE := ' ';
552 l_state_abbrev pay_us_rpt_totals.attribute15%TYPE := ' ';
553 l_state_id_number pay_us_rpt_totals.attribute16%TYPE := ' ';
554 l_state_id_sring pay_us_rpt_totals.attribute16%TYPE := ' ';
555 l_state_inco_id_number pay_us_rpt_totals.attribute17%TYPE := ' ';
556 l_state_inco_id_string pay_us_rpt_totals.attribute17%TYPE := ' ';
557 l_ss_wages_tips_old pay_us_rpt_totals.attribute18%TYPE := ' ';
558 l_ss_wages_tips_new pay_us_rpt_totals.attribute19%TYPE := ' ';
559 l_sit_old pay_us_rpt_totals.attribute20%TYPE := ' ';
560 l_sit_new pay_us_rpt_totals.attribute21%TYPE := ' ';
561 l_state_local_name pay_us_rpt_totals.attribute22%TYPE := ' ';
562 l_local_wages_old pay_us_rpt_totals.attribute23%TYPE := ' ';
563 l_local_wages_new pay_us_rpt_totals.attribute24%TYPE := ' ';
564 l_local_withheld_old pay_us_rpt_totals.attribute25%TYPE := ' ';
565 l_local_withheld_new pay_us_rpt_totals.attribute26%TYPE := ' ';
566 l_incorrect_name pay_us_rpt_totals.attribute27%TYPE := ' ';
567 l_incorrect_ssn pay_us_rpt_totals.attribute28%TYPE := ' ';
568 BEGIN
569
570 --hr_utility.trace_on(null, 'W3CAUDIT');
571 hr_utility.trace ('Entered Main package');
572 hr_utility.trace ('p_seq_num = '||p_seq_num);
573
574
575 open c_get_data(p_seq_num);
576
577 hr_utility.trace('Opened c_get_data cursor');
578
579 loop
580
581 l_tax_unit_id :=0;
582 l_employer_name := ' ';
586 l_record_type := ' ';
583 l_federal_ein := ' ';
584 l_employee_name := ' ';
585 l_ssn := ' ';
587 l_tax_year := ' ';
588 l_wages_old :=0;
589 l_wages_new :=0;
590 l_fit_old :=0;
591 l_fit_new :=0;
592 l_ss_wages_old :=0;
593 l_ss_wages_new :=0;
594 l_ss_withheld_old :=0;
595 l_ss_withheld_new :=0;
596 l_medi_wages_old :=0;
597 l_medi_wages_new :=0;
598 l_medi_withheld_old :=0;
599 l_medi_withheld_new :=0;
600 l_ss_tips_old :=0;
601 l_ss_tips_new :=0;
602 l_alloc_tips_old :=0;
603 l_alloc_tips_new :=0;
604 l_eic_old :=0;
605 l_eic_new :=0;
606 l_depcare_old :=0;
607 l_depcare_new :=0;
608 l_nonqual_old :=0;
609 l_nonqual_new :=0;
610 l_third_party_sick_old :=0;
611 l_third_party_sick_new :=0;
612 l_box_12_code :=' ';
613 l_box_12_value_old := 0;
614 l_box_12_value_new := 0;
615 l_box_14_code := ' ';
616 l_box_14_value_old := 0;
617 l_box_14_value_new := 0;
618 l_state_abbrev := ' ';
619 l_state_id_number := ' ';
620 l_state_inco_id_number := ' ';
621 l_ss_wages_tips_old := 0;
622 l_ss_wages_tips_new := 0;
623 l_sit_old := 0;
624 l_sit_new := 0;
625 l_state_local_name := ' ';
626 l_local_wages_old := 0;
627 l_local_wages_new := 0;
628 l_local_withheld_old := 0;
629 l_local_withheld_new := 0;
630 l_incorrect_name := ' ';
631 l_incorrect_ssn := ' ';
632
633 fetch c_get_data into l_tax_unit_id,
634 l_employer_name,
635 l_federal_ein,
636 l_employee_name,
637 l_ssn,
638 l_record_type,
639 l_tax_year,
640 l_wages_old,
641 l_wages_new,
642 l_fit_old,
643 l_fit_new,
644 l_ss_wages_old,
645 l_ss_wages_new,
646 l_ss_withheld_old,
647 l_ss_withheld_new,
648 l_medi_wages_old,
649 l_medi_wages_new,
650 l_medi_withheld_old,
651 l_medi_withheld_new,
652 l_ss_tips_old,
653 l_ss_tips_new,
654 l_alloc_tips_old,
655 l_alloc_tips_new,
656 l_eic_old,
657 l_eic_new,
658 l_depcare_old,
659 l_depcare_new,
660 l_nonqual_old,
661 l_nonqual_new,
662 l_third_party_sick_old,
663 l_third_party_sick_new,
664 l_box_12_code,
665 l_box_12_value_old,
666 l_box_12_value_new,
667 l_box_14_code,
668 l_box_14_value_old,
669 l_box_14_value_new,
670 l_state_abbrev,
671 l_state_id_number,
672 l_state_inco_id_number,
673 l_ss_wages_tips_old,
674 l_ss_wages_tips_new,
675 l_sit_old,
676 l_sit_new,
677 l_state_local_name,
678 l_local_wages_old,
679 l_local_wages_new,
680 l_local_withheld_old,
681 l_local_withheld_new,
682 l_incorrect_name,
683 l_incorrect_ssn
684 ;
685
686 if l_record_type = 'FEDERAL' then
687 l_fed_ein := ''''||l_federal_ein||'''';
688 else
689 l_fed_ein := l_federal_ein;
690 end if;
691
692 if l_record_type ='STATE' then
693 l_state_id_sring := ''''||l_state_id_number||'''';
694 l_state_inco_id_string := ''''||l_state_inco_id_number||'''';
695 else
696 l_state_id_sring := l_state_id_number;
697 l_state_inco_id_string := l_state_inco_id_number;
698 end if;
699
700
704
701 l_employer_name := replace(l_employer_name,',');
702 l_employee_name := replace(l_employee_name,',');
703
705 hr_utility.trace('l_employer_name = '||l_employer_name);
706 hr_utility.trace('l_employee_name = '||l_employee_name);
707
708 hr_utility.trace('l_record_type = '||l_record_type);
709 hr_utility.trace('l_federal_ein = '||l_federal_ein);
710 hr_utility.trace('l_fed_ein = '||l_fed_ein);
711 hr_utility.trace('l_employee_name = '||l_employee_name);
712 hr_utility.trace('l_ssn = '||l_ssn);
713 hr_utility.trace('l_record_type = '||l_record_type);
714 hr_utility.trace('l_tax_year = '||l_tax_year);
715 hr_utility.trace('l_wages_old = '||to_char(l_wages_old));
716 hr_utility.trace('l_wages_new = '||to_char(l_wages_new));
717 hr_utility.trace('l_fit_old = '||to_char(l_fit_old));
718 hr_utility.trace('l_fit_new = '||to_char(l_fit_new));
719 hr_utility.trace('l_ss_wages_old = '||to_char(l_ss_wages_old));
720 hr_utility.trace('l_ss_wages_new = '||to_char(l_ss_wages_new));
721 hr_utility.trace('l_ss_withheld_old = '||to_char(l_ss_withheld_old));
722 hr_utility.trace('l_ss_withheld_new = '||to_char(l_ss_withheld_new));
723 hr_utility.trace('l_medi_wages_old = '||to_char(l_medi_wages_old));
724 hr_utility.trace('l_medi_wages_new = '||to_char(l_medi_wages_new));
725 hr_utility.trace('l_medi_withheld_old = '||to_char(l_medi_withheld_old));
726 hr_utility.trace('l_medi_withheld_new = '||to_char(l_medi_withheld_new));
727 hr_utility.trace('l_ss_tips_old = '||to_char(l_ss_tips_old));
728 hr_utility.trace('l_ss_tips_new = '||to_char(l_ss_tips_new));
729 hr_utility.trace('l_alloc_tips_old = '||to_char(l_alloc_tips_old));
730 hr_utility.trace('l_alloc_tips_new = '||to_char(l_alloc_tips_new));
731 hr_utility.trace('l_eic_old = '||to_char(l_eic_old));
732 hr_utility.trace('l_eic_new = '||to_char(l_eic_new));
733 hr_utility.trace('l_depcare_old = '||to_char(l_depcare_old));
734 hr_utility.trace('l_depcare_new = '||to_char(l_depcare_new));
735 hr_utility.trace('l_nonqual_old = '||to_char(l_nonqual_old));
736 hr_utility.trace('l_nonqual_new = '||to_char(l_nonqual_new));
737 hr_utility.trace('l_third_party_sick_old = '||to_char(l_third_party_sick_old));
738 hr_utility.trace('l_third_party_sick_new = '||to_char(l_third_party_sick_new));
739 hr_utility.trace('l_box_12_code = '||l_box_12_code);
740 hr_utility.trace('l_box_12_value_old = '||l_box_12_value_old);
741 hr_utility.trace('l_box_12_value_new = '||l_box_12_value_new);
742 hr_utility.trace('l_box_14_code = '||l_box_14_code);
743 hr_utility.trace('l_box_14_value_old = '||l_box_14_value_old);
744 hr_utility.trace('l_box_14_value_new = '||l_box_14_value_new);
745 hr_utility.trace('l_state_abbrev = '||l_state_abbrev);
746 hr_utility.trace('l_state_id_number = '||l_state_id_number);
747 hr_utility.trace('l_state_id_sring = '||l_state_id_sring);
748 hr_utility.trace('l_state_inco_id_number = '||l_state_inco_id_number);
749 hr_utility.trace('l_state_inco_id_string = '||l_state_inco_id_string);
750 hr_utility.trace('l_ss_wages_tips_old = '||l_ss_wages_tips_old);
751 hr_utility.trace('l_ss_wages_tips_new = '||l_ss_wages_tips_new);
752 hr_utility.trace('l_sit_old = '||l_sit_old);
753 hr_utility.trace('l_sit_new = '||l_sit_new);
754 hr_utility.trace('l_state_local_name = '||l_state_local_name);
755 hr_utility.trace('l_local_wages_old = '||l_local_wages_old);
756 hr_utility.trace('l_local_wages_new = '||l_local_wages_new);
757 hr_utility.trace('l_local_withheld_old = '||l_local_withheld_old);
758 hr_utility.trace('l_local_withheld_new = '||l_local_withheld_new);
759 hr_utility.trace('l_incorrect_name = '||l_incorrect_name);
760 hr_utility.trace('l_incorrect_ssn = '||l_incorrect_ssn);
761
762 exit when c_get_data%NOTFOUND;
763
764 if c_get_data%ROWCOUNT =1 THEN
765
766
767 hr_utility.trace('row_count = 1');
768
769 fnd_file.put_line(fnd_file.output,report_header('CSV','Header1'));
770 fnd_file.put_line(fnd_file.output,report_header('CSV','Header2'));
771
772 end if;
773
774 hr_utility.trace ('Writing report data = ');
775
776 fnd_file.put_line(fnd_file.output,report_data(
777 l_employer_name,
778 l_fed_ein,
779 l_employee_name,
780 l_incorrect_name,
781 l_ssn,
782 l_incorrect_ssn,
783 l_record_type,
784 l_tax_year,
785 l_wages_old,
786 l_wages_new,
787 l_fit_old,
788 l_fit_new,
789 l_ss_wages_old,
790 l_ss_wages_new,
791 l_ss_withheld_old,
792 l_ss_withheld_new,
793 l_medi_wages_old,
794 l_medi_wages_new,
795 l_medi_withheld_old,
796 l_medi_withheld_new,
797 l_ss_tips_old,
798 l_ss_tips_new,
799 l_alloc_tips_old,
800 l_alloc_tips_new,
801 l_eic_old,
802 l_eic_new,
803 l_depcare_old,
804 l_depcare_new,
805 l_nonqual_old,
806 l_nonqual_new,
807 l_third_party_sick_old,
808 l_third_party_sick_new,
809 l_box_12_code,
810 l_box_12_value_old,
811 l_box_12_value_new,
812 l_box_14_code,
813 l_box_14_value_old,
814 l_box_14_value_new,
815 l_state_abbrev,
816 l_state_id_sring,
817 l_state_inco_id_string,
818 l_ss_wages_tips_old,
819 l_ss_wages_tips_new,
820 l_sit_old,
821 l_sit_new,
822 l_state_local_name,
823 l_local_wages_old,
824 l_local_wages_new,
825 l_local_withheld_old,
826 l_local_withheld_new,
827 'CSV'));
828
829 end loop;
830
831
832 hr_utility.trace ('out of loop = ');
833
834 DELETE FROM pay_us_rpt_totals
835 WHERE session_id = p_seq_num ;
836
837
838
839 close c_get_data;
840
841 end;
842 end pay_us_w3c_rpt;