[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MMRF_PRINT_REC_HEADER
Source
1 PACKAGE BODY pay_us_mmrf_print_rec_header AS
2 /* $Header: pyusprhd.pkb 120.6 2007/01/10 12:44:15 sudedas noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_mmrf_print_rec_header_pkg
12
13 Purpose
14 The purpose of this package is to format reacord header
15 to support the generation of magnetic tape W2 / SQWL reports
16 for US legilsative requirements. These record headers are
17 mainly used for CSV
18
19 Notes
20 Referenced By: Package pay_us_reporting_utils_pkg
21
22 Notes
23
24 History
25
26 10-Jan-2007 sausingh 115.20 5358272 Added Headers for Roth 401k/403b
27 sudedas Changed mmrf2_format_rcw_record_header
28 22-Nov-2006 sudedas 115.19 5640748 Modified mmrf_format_re_record_header
29 for State of Maryland.
30 17-Aug-2006 sudedas 115.18 5256745 Header Changed for RW and RT
31 Records due to MMREF-1 spec change.
32 30-May-2006 sackumar 115.17 (5089997, 4554387) Changed header for CUSTOM H and D Records.
33 03-Dec-2005 sodhingr 115.16 4398606 Changed header for RCO and RCW
34 05-Nov-2005 sudedas 115.15 4391218 Header Changed for RA, RW, RO, RT, RO
35 Records due to MMREF-1 spec change.
36 25-NOV-2004 rsethupa 115.14 4014356 RS Record header for NJ
37 Added 'MIF'
38 25-NOV-2004 rsethupa 115.13 4022086 RS Record header for MS
39 Added 'Federal ER Account Number',
40 '1099 Income' and 'Payment Yeat'
41 05-NOV-2004 rsethupa 115.12 3680056 RW and RT records header for
42 ER Contrib to Health Savings Acct
43 02-NOV-2004 rsethupa 115.11 3180532 RS Record header for IN
44 Replaced columns 'Country Code',
45 'Optional Code' and 'Reporting Period'
46 with 'Federal Advanced EIC'.
47 Added 'State Advanced EIC' and
48 'State Advanced EIC ID'
49 27-OCT-2004 rsethupa 115.10 3936924 RS record header for AL
50 Added 'Federal Employer Account Number',
51 'Payment Year'
52 26-OCT-2004 meshah 115.9 3650105 changed mmrf2_format_rcw_record_header
53 to add ER contribution to HSA Account.
54 28-AUG-2004 jgoswami 115.8 3830050 added mmrf_format_rssumm_rec_header
55 01-MAR-2004 jgoswami 115.7 3334497 Added Columns for AK_SQWL header string.
56 19-DEC-03 jgoswami 115.6 3319454 Added EIN for WA_SQWL header string.
57 08-DEC-03 ppanda 115.4 3300354 Column heading on a02 and a03 corrected
58 02-Dec-03 ppanda 115.3 3277954 Column heading changed for RCW and RCO record
59 10-Nov-03 ppanda 115.2 2587381 RCW and RCO record header formating function
60 added
61 14-Oct-03 fusman 115.1 2682247 Separate header for NY in RS record.
62 3220001 Parameter order was changed.
63 14-Jul-03 ppanda 115.0 Created
64 */
65 -- Global Variable
66 g_number NUMBER;
67 l_return varchar2(100);
68 end_date date := to_date('31/12/4712','DD/MM/YYYY');
69
70 --
71 -- This function format submitter Record (i.e. RA) record header
72 --
73 FUNCTION mmrf_format_ra_record_header(
74 p_report_type IN varchar2,
75 p_format IN varchar2,
76 p_report_qualifier IN varchar2,
77 p_record_name IN varchar2
78 ) RETURN VARCHAR2
79 IS
80 -- Local Variables
81 header_string varchar2(3000);
82 BEGIN
83 hr_utility.trace('Formatting RA record header');
84 -- This is fix for bug # 2510920
85 -- Column heading which used to be 'Resub TLCN' now its going to be 'Resub WFID'
86 --
87 header_string := 'Record Identifier'
88 ||','||'Submitters Employer Identification Number (EIN)'
89 ||','||'Personal Identification Number (PIN)'
90 ||','||'Blank' -- Bug# 4391218
91 ||','||'Resub Indicator'
92 ||','||'Resub WFID'
93 ||','||'Software Code'
94 ||','||'Company Name'
95 ||','||'Location Address'
96 ||','||'Delivery Address'
97 ||','||'City'
98 ||','||'State Abbreviation'
99 ||','||'Zip Code'
100 ||','||'Zip Code Extension'
101 ||','||'Blank'
102 ||','||'Foreign State / Province'
103 ||','||'Foreign Postal Code'
104 ||','||'Country Code'
105 ||','||'Submitter Name'
106 ||','||'Location Address'
107 ||','||'Delivery Address'
108 ||','||'City'
109 ||','||'State Abbreviation'
110 ||','||'Zip Code'
111 ||','||'Zip Code Extension'
112 ||','||'Blank'
113 ||','||'Foreign State / Province'
114 ||','||'Foreing Postal Code'
115 ||','||'Country Code'
116 ||','||'Contact Name'
117 ||','||'Contact Phone Number'
118 ||','||'Contact Phone Extension'
119 ||','||'Blank'
120 ||','||'Contact E-Mail'
121 ||','||'Blank'
122 ||','||'Contact FAX'
123 ||','||'Preferred Method Of Problem Notification Code'
124 ||','||'Preparer Code'
125 ||','||'Blank';
126 hr_utility.trace('value of header string = '||header_string);
127 return header_String;
128 END mmrf_format_ra_record_header;
129
130 --
131 -- This function format Employer Record (i.e. RE) record header
132 --
133 FUNCTION mmrf_format_re_record_header(
134 p_report_type IN varchar2,
135 p_format IN varchar2,
136 p_report_qualifier IN varchar2,
137 p_record_name IN varchar2
138 ) RETURN VARCHAR2
139 IS
140 -- Local Variables
141 header_string varchar2(6000);
142 l_header_9 varchar2(3000);
143 BEGIN
144 hr_utility.trace('Formatting RE record header');
145 If ((p_report_qualifier = 'PA') OR
146 (p_report_qualifier = 'PA_PHILA')) THEN
147 l_header_9 := 'Third Party Sick Pay Indicator'
148 ||','||'PA 8-digit Account Number';
149 Elsif (p_report_qualifier = 'MD') Then
150 l_header_9 := 'Third Party Sick Pay Indicator'
151 || ',' || 'MW508 ER - Tax Year'
152 || ',' || 'MW508 ER ID'
153 || ',' || 'MW508 Central Registration Number'
154 || ',' || 'MW508 - ER Name'
155 || ',' || 'MW508 ER St Address'
156 || ',' || 'MW508 City'
157 || ',' || 'MW508 State'
158 || ',' || 'MW508 Zipcode'
159 || ',' || 'MW508 Zip Extension'
160 || ',' || 'MW508 ER Number of W2s'
161 || ',' || 'MW508 Total amt of taxes w/h'
162 || ',' || 'MW508 ER Total Tax w/h'
163 || ',' || 'MW508 ER Credits'
164 || ',' || 'MW508 ER Amt Tax Due'
165 || ',' || 'MW508 ER Amt Balance Due'
166 || ',' || 'MW508 ER Amt Overpayment'
167 || ',' || 'MW508 ER Amt Credit'
168 || ',' || 'MW508 ER Amt Refunded'
169 || ',' || 'MW508 Gross Payroll'
170 || ',' || 'ER State Pickup Amt'
171 || ',' || 'ER Rep Name'
172 || ',' || 'ER Rep Title'
173 || ',' || 'ER Rep Date'
174 || ',' || 'ER Rep Phone Number'
175 || ',' || 'ER Total File Indicator' ;
176 Else
177 l_header_9 := 'Third Party Sick Pay Indicator'
178 ||','||'Blank';
179 End if;
180 header_string :=
181 'Record Identifier'
182 ||','||'Tax Year'
183 ||','||'Agent Indicator Code'
184 ||','||'Employer / Agent Employer Identification Number (EIN)'
185 ||','||'Agent For EIN'
186 ||','||'Terminating Business Indicator'
187 ||','||'Establishment Number'
188 ||','||'Other EIN'
189 ||','||'Employer Name'
190 ||','||'Location Address'
191 ||','||'Delivery Address'
192 ||','||'City'
193 ||','||'State Abbreviation'
194 ||','||'Zip Code'
195 ||','||'Zip Code Extension'
196 ||','||'Blank'
197 ||','||'Foreign State / Province'
198 ||','||'Foreign Postal Code'
199 ||','||'Country Code'
200 ||','||'Employment Code'
201 ||','||'Tax Jurisdiction Code'
202 ||','||l_header_9;
203 hr_utility.trace('value of header string = '||header_string);
204 return header_string;
205 END mmrf_format_re_record_header;
206
207 --
208 -- This function formats Wage Record (i.e. RW) record header
209 --
210 FUNCTION mmrf_format_rw_record_header(
211 p_report_type IN varchar2,
212 p_format IN varchar2,
213 p_report_qualifier IN varchar2,
214 p_record_name IN varchar2
215 ) RETURN VARCHAR2
216 IS
217 -- Local Variables
218 header_string varchar2(3000);
219 BEGIN
220 hr_utility.trace('Formatting RW record header');
221 header_string :=
222 'Record Identifier'
223 ||','||'Social Security Number'
224 ||','||'Employee First Name'
225 ||','||'Employee Middle Name or Initial'
226 ||','||'Employee Last Name'
227 ||','||'Suffix'
228 ||','||'Location Address'
229 ||','||'Delivery Address'
230 ||','||'City'
231 ||','||'State Abbreviation'
232 ||','||'Zip Code'
233 ||','||'Zip Code Extension'
234 ||','||'Blank'
235 ||','||'Foreign State / Province'
236 ||','||'Foreign Postal Code'
237 ||','||'Country Code'
238 ||','||'Wages Tips And Other Compensation'
239 ||','||'Federal Income Tax Withheld'
240 ||','||'Social Security Wages'
241 ||','||'Social Security Tax Withheld'
242 ||','||'Medicare Wages And Tips'
243 ||','||'Medicare Tax Withheld'
244 ||','||'Social Security Tips'
245 ||','||'Advance Earned Income Credit'
246 ||','||'Dependent Care Benefits'
247 ||','||'Deferred Compensation Contributions to Section 401(k)'
248 ||','||'Deferred Compensation Contributions to Section 403(b)'
249 ||','||'Deferred Compensation Contributions to Section 408(k)(6)'
250 ||','||'Deferred Compensation Contributions to Section 457(b)'
251 ||','||'Deferred Compensation Contributions to Section 501(c)(18)(D)'
252 --||','||'Military EE''s Basic Quarters Subsistence and Combat Pay'
253 ||','||'Blank'
254 ||','||'Non-Qual. plan Sec.457 Distributions or Contributions'
255 -- Bug 3680056 - New field
256 ||','||'Employer Contributions to Health Savings Account'
257 ||','||'Non-Qual. plan NOT Section 457 Distributions or Contributions'
258 -- Bug 4391218
259 ||','||'Non-Taxable Combat Pay'
260 ||','||'Blank'
261 ||','||'Employer Cost of Premiums for GTL> $50k'
262 ||','||'Income From Exercise of Nonqualified Stock Options'
263 -- Bug 4391218
264 ||','||'Deferrals Under a Section 409A Non-Qualified Deferred Comp Plan'
265 -- Bug 5256745
266 ||','||'Designated Roth Contrib. To Sec. 401(k) Plan'
267 ||','||'Designated Roth Contrib. Und Sec. 403(b) Salary Reduction Agreement'
268 ||','||'Blank'
269 ||','||'Statutory Employee Indicator'
270 ||','||'Blank'
271 ||','||'Retirement Plan Indicator'
272 ||','||'Third-Party Sick Pay Indicator'
273 ||','||'Blank';
274 hr_utility.trace('value of formatted header string = '||header_string);
275 return header_string;
276 END mmrf_format_rw_record_header;
277
278 --
279 -- This function formats Puertorico based Wage Record (i.e. RO) record header
280 --
281 FUNCTION mmrf_format_ro_record_header(
282 p_report_type IN varchar2,
283 p_format IN varchar2,
284 p_report_qualifier IN varchar2,
285 p_record_name IN varchar2
286 ) RETURN VARCHAR2
287 IS
288 -- Local Variables
289 header_string varchar2(3000);
290 BEGIN
291 hr_utility.trace('Formatting RO record header');
292 header_string :=
293 'Record Identifier'
294 ||','||'Blank'
295 ||','||'Allocated Tips'
296 ||','||'Uncollected Employee Tax on Tips'
297 ||','||'Medical Savings Account'
298 ||','||'Simple Retirement Account'
299 ||','||'Qualified Adoption Expenses'
300 ||','||'Uncollected Social Security or RRTA Tax on GTL'
301 ||','||'Uncollected Medicare Tax on GTL'
302 -- Bug 4391218
303 ||','||'Income Under Section 409A on a Non-Qualified Deferred Comp Plan'
304 ||','||'Blank'
305 ||','||'Civil Status'
306 ||','||'Spouse''s Social Security Number (SSN)'
307 ||','||'Wages Subject to Puerto Rico Tax'
308 ||','||'Commissions Subject to Puerto Rico Tax'
309 ||','||'Allowances Subject to Puerto Rico Tax'
310 ||','||'Tips Subject to Puerto Rico Tax'
311 ||','||'Total Wages Commissions Tips and Allowances Subject to Puerto Rico Tax'
312 ||','||'Puerto Rico Tax Withheld'
313 ||','||'Retirement Fund Annual Contributions'
314 ||','||'Blank'
315 ||','||'Total Wages Tips and other Compensation Subject to Virgin Islands or Guam or American Samoa or Northern Mariana Islands Income Tax'
316 ||','||'Virgin Islands or Guam or American Samoa or Northern Mariana Islands Income Tax Withheld'
317 ||','||'Blank';
318 hr_utility.trace('value of formatted header string = '||header_string);
319 return header_string;
320 END mmrf_format_ro_record_header;
321
322 --
323 -- This function formats State Wage Record (i.e. RS) record header
324 --
325 FUNCTION mmrf_format_w2_rs_rec_header(
326 p_report_type IN varchar2,
327 p_format IN varchar2,
328 p_report_qualifier IN varchar2,
329 p_record_name IN varchar2,
330 p_input_1 IN varchar2
331 ) RETURN VARCHAR2
332 IS
333 -- Local Variables
337 l_header_21 varchar2(900);
334 header_string varchar2(3000);
335
336 l_header_20 varchar2(900);
338 -- Bug 3936924
339 l_header_25 varchar2(900);
340 l_report_format varchar2(15);
341 l_header_29 varchar2(900);
342 -- Bug 4014356
343 l_header_30 varchar2(900);
344 l_header_34 varchar2(900);
345 l_name_header varchar2(900);
346 --Bug 3180532
347 l_header_17 varchar2(900);
348
349 BEGIN
350 hr_utility.trace('Formatting W2 RS record header');
351 l_report_format := p_input_1;
352 l_name_header := 'Employee First Name'
353 ||','||'Employee Middle Name or Initial'
354 ||','||'Employee Last Name';
355 l_header_20 := 'State Quarterly Unemployment Insurance Total Wages';
356 l_header_21 := 'State Quarterly Unemployment Total Taxable Wages';
357 l_header_25 := 'State Employer Account Number';
358
359 /* Bug 3180532
360 l_header_17
361 For Indiana, only 'Federal Advanced EIC' is reqd
362 'Country Code', 'Optional Code' and 'Reporting Period' not reqd
363 For other states these 3 strings are combined in l_header_17 */
364 IF p_report_qualifier = 'IN' THEN
365 l_header_17 := 'Federal Advanced EIC';
366 ELSE
367 l_header_17 := 'Country Code'
368 ||','||'Optional Code'
369 ||','||'Reporting Period';
370 END IF;
371
372 IF p_report_qualifier = 'AL' THEN
373 l_header_29 := 'FIT withheld';
374 -- Bug 3936924 - New columns headers
375 l_header_25 := l_header_25 || ',' ||
376 'Federal Employer Account Number';
377 ELSIF p_report_qualifier = 'MD' THEN
378 l_header_29 := 'Maryland state pickup for MD state retirement system';
379 ELSIF p_report_qualifier = 'OH' THEN
380 l_header_29 := 'Total Wages Tips and Other Compenstaion';
381 ELSE
382 l_header_29 := 'Other State Data';
383 END IF;
384
385 IF p_report_qualifier = 'MS' THEN
386 l_header_25 := l_header_25 || ',' ||
387 'Federal Employer Account Number';
388 END IF;
389
390 -- Bug 4014356
391 IF p_report_qualifier = 'NJ' THEN
392 l_header_30 := 'MIF';
393 ELSE
394 l_header_30 := 'Tax Type Code';
395 END IF;
396
397 IF p_report_qualifier = 'KY' THEN
398 l_header_34 :=
399 'Amount of tax credit for KY rural economic dev. asst.'
400 ||','||'Amount of tax credit for KY jobs dev. act'
401 ||','||
402 'Amount of tax credit for KY industrial revitalization authority'
403 ||','||'Amount of tax credit for KY industrial devep. authority'
404 ||','||'Blank';
405 ELSIF p_report_qualifier = 'NJ' THEN
406 l_header_34 := 'Disability Plan Type Code'
407 ||','||'Private Disability Plan'
408 ||','||'Unemployment Insurance Tax'
409 ||','||'SDI Withheld'
410 ||','||'Pension Plan Indicator'
411 ||','||'Deferred Comp. Indicator '
412 ||','||'Deferred Compensation'
413 ||','||'Blank';
414 ELSIF p_report_qualifier = 'MD' THEN
415 l_header_34 := 'MD Central Registration Number'
416 ||','||'Wages Tips and Other Compensation'
417 ||','||'FIT w/h'
418 ||','||'Blank';
419 ELSIF p_report_qualifier = 'ME' THEN
420 l_header_34 := 'State Withheld a/c Number'
421 ||','||'Blank';
422 ELSIF p_report_qualifier = 'MA' THEN
423 l_header_34 := 'FICA + MCR w/h '
424 ||','||'Blank';
425 -- Bug 3936924
426 ELSIF p_report_qualifier = 'AL' THEN
427 l_header_34 := 'Payment Year'
428 ||','||'Blank';
429 -- Bug 3180532 : For Indiana 'State Advanced EIC' and 'State Advanced EIC ID'
430 ELSIF p_report_qualifier = 'IN' THEN
431 l_header_34 := 'State Advanced EIC'
432 ||','||'State Advanced EIC ID';
433 ELSIF p_report_qualifier = 'MS' THEN
434 l_header_34 := '1099 Income'
435 ||','||'Payment Year';
436 END IF;
437
438 header_string:=
439 'Record Identifier'
440 ||','||'State Code'
441 ||','||'Taxing Entity Code'
442 ||','||'Social Security Number (SSN)'
443 ||','||l_name_header
444 ||','||'Suffix'
445 ||','||'Location Address'
446 ||','||'Delivery Address'
447 ||','||'City'
448 ||','||'State Abbreviation'
449 ||','||'Zip Code'
450 ||','||'Zip Code Extension'
451 ||','||'Blank'
452 ||','||'Foreign State / Province'
453 ||','||'Foreign Postal Code'
454 ||','|| l_header_17
455 ||','|| l_header_20
456 ||','|| l_header_21
457 ||','||'Number of Weeks Worked'
458 ||','||'Date First Employed'
459 ||','||'Date of Separation'
460 ||','||'Blank'
461 ||','|| l_header_25
462 ||','||'Blank'
463 ||','||'State Code'
464 ||','||'State Taxable Wages'
465 ||','||'SIT Withheld'
466 ||','||l_header_29
467 ||','||l_header_30
471 ||','||l_header_34;
468 ||','||'Local Taxable Wages'
469 ||','||'Local Income Tax Withheld'
470 ||','||'State Control Number'
472 hr_utility.trace('value of formatted W2 RS Record header string = '||header_string);
473 return header_string;
474 END mmrf_format_w2_rs_rec_header;
475
476 --
477 -- This function formats State Wage Record (i.e. RO) record header
478 --
479 FUNCTION mmrf_format_sqwl_rs_rec_header(
480 p_report_type IN varchar2,
481 p_format IN varchar2,
482 p_report_qualifier IN varchar2,
483 p_record_name IN varchar2,
484 p_input_1 IN varchar2
485 ) RETURN VARCHAR2
486 IS
487 -- Local Variables
488 header_string varchar2(3000);
489 l_report_format varchar2(15);
490 l_header_20 varchar2(900);
491 l_header_21 varchar2(900);
492 l_header_28 varchar2(900);
493 l_header_29 varchar2(900);
494 l_header_34 varchar2(900);
495 l_name_header varchar2(900);
496
497 BEGIN
498 hr_utility.trace('Formatting SQWL RS record header');
499 hr_utility.trace('p_report_qualifier = '|| p_report_qualifier);
500 l_report_format := p_input_1;
501 IF p_report_qualifier in ( 'LA_SQWL','AK_SQWL') THEN /*Bug:2337613 */
502 l_name_header := 'Employee Last Name'||','||'Employee First Name'
503 ||','||'Employee Middle Name or Initial';
504 ELSE
505 l_name_header := 'Employee First Name'
506 ||','||'Employee Middle Name or Initial'
507 ||','||'Employee Last Name';
508 END IF;
509
510 If p_report_qualifier ='CA_SQWL' Then
511 If l_report_format is not null and
512 l_report_format = 'RTM' Then
513 l_header_20 := 'SDI/SUI Total Wages';
514 l_header_21 := 'SUI Total Taxable Wages';
515 Else
516 l_header_20 := 'State Quarterly Unemployment Insurance Total Wages';
517 l_header_21 := 'State Quarterly Unemployment Total Taxable Wages';
518 End If;
519 Else
520 l_header_20 := 'State Quarterly Unemployment Insurance Total Wages';
521 l_header_21 := 'State Quarterly Unemployment Total Taxable Wages';
522 End If;
523
524 l_header_29 := 'Other State Data';
525
526 l_header_34 := 'Supplemental Data 1'
527 ||','||'Supplemental Data 2'
528 ||','||'Blank';
529
530 IF p_report_qualifier = 'NY_SQWL' THEN /*2682247*/
531
532 l_header_28 := 'Q4 only YTD FIT Wages'
533 ||','||'Q4 only YTD SIT and CIT';
534 ELSE
535
536 l_header_28 := 'State Taxable Wages'
537 ||','||'SIT Withheld';
538
539 END IF;
540
541 hr_utility.trace('l_header_28 = '|| l_header_28);
542
543 IF p_report_qualifier = 'WA_SQWL' THEN
544 header_string:=
545 'Employer Identification Number(EIN)'
546 ||','||'Social Security Number (SSN)'
547 ||','||'Name'
548 ||','||'Quarterly Hours'
549 ||','||'Quarterly Gross Wages';
550 hr_utility.trace(header_string);
551 ELSIF p_report_qualifier = 'AK_SQWL' THEN
552 header_string:=
553 'Transaction Code'
554 ||','||'Employer Account Number'
555 ||','||'Year'
556 ||','||'Quarter'
557 ||','||'Social Security Number (SSN)'
558 ||','||l_name_header
559 ||','||l_header_20
560 ||','||'Project Code'
561 ||','||'Hourly Rate'
562 ||','||'Occupational Code or Title'
563 ||','||'Area Code' ;
564 /* Bug # 4554387
565 ||','||'Batch Number'
566 ||','||'Batch Item';
567 */
568 hr_utility.trace(header_string);
569 ELSIF p_report_qualifier = 'NM_SQWL' THEN
570 header_string:=
571 'Social Security Number (SSN)'
572 ||','||'Quater Gross Wages'
573 ||','||'Quarter Excess Wages'
574 ||','||'Withholding'
575 ||','||'WorkersComp' ;
576
577 hr_utility.trace(header_string);
578 ELSE
579 header_string:=
580 'Record Identifier'
581 ||','||'State Code'
582 ||','||'Taxing Entity Code'
583 ||','||'Social Security Number (SSN)'
584 ||','||l_name_header
585 ||','||'Suffix'
586 ||','||'Location Address'
587 ||','||'Delivery Address'
588 ||','||'City'
589 ||','||'State Abbreviation'
590 ||','||'Zip Code'
591 ||','||'Zip Code Extension'
592 ||','||'Blank'
593 ||','||'Foreign State / Province'
594 ||','||'Foreign Postal Code'
595 ||','||'Country Code'
596 ||','||'Optional Code'
597 ||','||'Reporting Period'
598 ||','|| l_header_20
599 ||','|| l_header_21
600 ||','||'Number of Weeks Worked'
604 ||','||'State Employer Account Number'
601 ||','||'Date First Employed'
602 ||','||'Date of Separation'
603 ||','||'Blank'
605 ||','||'Blank'
606 ||','||'State Code'
607 ||','|| l_header_28
608 ||','||l_header_29
609 ||','||'Tax Type Code'
610 ||','||'Local Taxable Wages'
611 ||','||'Local Income Tax Withheld'
612 ||','||'State Control Number'
613 ||','||l_header_34;
614 END IF;
615 hr_utility.trace('value of formatted SQWL RS Record header string = '||header_string);
616 return header_string;
617 END mmrf_format_sqwl_rs_rec_header;
618
619
620 --
621 -- This function formats Total Wage Record (i.e. RSSSUM) record header
622 --
623 FUNCTION mmrf_format_rssumm_rec_header(
624 p_report_type IN varchar2,
625 p_format IN varchar2,
626 p_report_qualifier IN varchar2,
627 p_record_name IN varchar2
628 ) RETURN VARCHAR2
629 IS
630 -- Local Variables
631 header_string varchar2(3000);
632 l_report_format varchar2(15);
633 l_header_3 varchar2(900);
634 l_header_4 varchar2(900);
635 l_header_5 varchar2(900);
636 l_header_8 varchar2(900);
637 l_header_9 varchar2(900);
638 l_records varchar2(900);
639 l_name_header varchar2(900);
640
641 BEGIN
642 hr_utility.trace('Formatting RSSSUMM record header');
643
644 IF (p_report_qualifier = 'NV') THEN
645 l_records := 'Number of RS Records';
646 l_header_3 := 'State Taxable Wages';
647 l_header_4 := 'State Income Tax Withheld';
648 ELSE
649 l_records := 'Number of RW Records';
650 l_header_3 := 'Wages Tips and other Compensation';
651 l_header_4 := 'Federal Income Tax Withheld';
652 END IF;
653
654 IF p_report_qualifier = 'RI' THEN
655 l_records:= 'Number of RS Records';
656 l_header_5 := 'State Taxable Wages';
657 l_header_9 := 'State Income Tax Withheld';
658 ELSE
659 l_header_5 := 'Social Security Wages';
660 l_header_9 := 'Social Security Tips';
661 END IF;
662
663 IF p_report_qualifier = 'MA' THEN
664 l_header_8 := 'Total FICA + MRC withheld';
665 ELSE
666 l_header_8 := 'Medicare Tax Withheld';
667 END IF;
668
669 header_string:=
670 'Record Identifier'
671 ||','||'State Code'
672 ||','||'Taxing Entity Code/State Rec Type'
673 ||','||'Blank'
674 ||','||'Federal Tax ID Number'
675 ||','||'Blank'
676 ||','||'State Employer Acct Number'
677 ||','||'Blank'
678 ||','||'Reporting Period'
679 ||','||'Blank'
680 ||','||'Total Number of Workers on Report'
681 ||','||'Blank'
682 ||','||'Total Wages Paid This Quarter (Including Tips)'
683 ||','||'Blank'
684 ||','||'Nontaxable Wages'
685 ||','||'Blank'
686 ||','||'Taxable Wages Paid This Quarter'
687 ||','||'Blank'
688 ||','||'Blank'
689 ||','||'Month(1) employment for employer'
690 ||','||'Blank'
691 ||','||'Month(2) employment for employer'
692 ||','||'Blank'
693 ||','||'Month(3) employment for employer'
694 ||','||'Blank'
695 ||','||'No Workers/No Wages Indicator'
696 ||','||'Blank';
697 return header_String;
698 hr_utility.trace('value of header string = '||header_string);
699 END mmrf_format_rssumm_rec_header;
700
701 --
702 -- This function formats Total Wage Record (i.e. RT) record header
703 --
704 FUNCTION mmrf_format_rt_record_header(
705 p_report_type IN varchar2,
706 p_format IN varchar2,
707 p_report_qualifier IN varchar2,
708 p_record_name IN varchar2
709 ) RETURN VARCHAR2
710 IS
711 -- Local Variables
712 header_string varchar2(3000);
713 l_report_format varchar2(15);
714 l_header_3 varchar2(900);
715 l_header_4 varchar2(900);
716 l_header_5 varchar2(900);
717 l_header_8 varchar2(900);
718 l_header_9 varchar2(900);
719 l_records varchar2(900);
720 l_name_header varchar2(900);
721
722 BEGIN
723 hr_utility.trace('Formatting RT record header');
724
725 IF ( (p_report_qualifier = 'CT') OR
726 (p_report_qualifier = 'MA') ) THEN
727 l_records := 'Number of RS Records';
728 l_header_3 := 'State Taxable Wages';
729 l_header_4 := 'State Income Tax Withheld';
730 ELSE
731 l_records := 'Number of RW Records';
732 l_header_3 := 'Wages Tips and other Compensation';
733 l_header_4 := 'Federal Income Tax Withheld';
734 END IF;
735
736 IF p_report_qualifier = 'RI' THEN
737 l_records:= 'Number of RS Records';
738 l_header_5 := 'State Taxable Wages';
739 l_header_9 := 'State Income Tax Withheld';
740 ELSE
744
741 l_header_5 := 'Social Security Wages';
742 l_header_9 := 'Social Security Tips';
743 END IF;
745 IF p_report_qualifier = 'MA' THEN
746 l_header_8 := 'Total FICA + MRC withheld';
747 ELSE
748 l_header_8 := 'Medicare Tax Withheld';
749 END IF;
750
751 header_string:=
752 'Record Identifier'
753 ||','||l_records
754 ||','||l_header_3
755 ||','||l_header_4
756 ||','||l_header_5
757 ||','||'Social Security Tax Withheld'
758 ||','||'Medicare Wages And Tips'
759 ||','||l_header_8
760 ||','||l_header_9
761 ||','||'Advance Earned Income Credit'
762 ||','||'Dependent Care Benefits'
763 ||','||'Deferred Compensation Contributions to Section 401(k)'
764 ||','||'Deferred Compensation Contributions to Section 403(b)'
765 ||','||'Deferred Compensation Contributions to Section 408(k)(6)'
766 ||','||'Deferred Compensation Contributions to Section 457(b)'
767 ||','||'Deferred Compensation Contributions to Section 501(c)(18)(D)'
768 --||','||'Military EE''s Basic Quarters Subsistence And Combat Pay'
769 ||','||'Blank'
770 ||','||'Non-Qual. Plan Sec.457 Distributions or Contributions'
771 -- Bug 3680056 - New field
772 ||','||'Employer Contributions to Health Savings Account'
773 ||','||'Non-Qual. Plan NOT Section 457 Distributions or Contributions'
774 -- Bug 4391218
775 ||','||'Non-Taxable Combat Pay'
776 ||','||'Blank'
777 ||','||'Employer Cost of Premiums for GTL> $50k'
778 ||','||'Income Tax Withheld by Third-Party Payer'
779 ||','||'Income from the Exercise of Nonqualified Stock Options'
780 -- Bug 4391218
781 ||','||'Deferrals Under a Section 409A Non-Qualified Deferred Plan Comp'
782 -- Bug 5256745
783 ||','||'Designated Roth Contrib. To Sec. 401(k) Plan'
784 ||','||'Designated Roth Contrib. Und Sec. 403(b) Salary Reduction Agreement'
785 ||','||'Blank';
786 return header_String;
787 hr_utility.trace('value of header string = '||header_string);
788 END mmrf_format_rt_record_header;
789
790 --
791 -- This function formats Total Puertorico Wage Record (i.e. RU) record header
792 --
793 FUNCTION mmrf_format_ru_record_header(
794 p_report_type IN varchar2,
795 p_format IN varchar2,
796 p_report_qualifier IN varchar2,
797 p_record_name IN varchar2
798 ) RETURN VARCHAR2
799 IS
800 -- Local Variables
801 header_string varchar2(3000);
802
803 BEGIN
804 hr_utility.trace('Formatting RU record header');
805 header_string:=
806 'Record Identifier'
807 ||','||'Number of RO Records'
808 ||','||'Allocated Tips'
809 ||','||'Uncollected Employee Tax on Tips'
810 ||','||'Medical Savings Account'
811 ||','||'Simple Retirement Account'
812 ||','||'Qualified Adoption Expenses'
813 ||','||'Uncollected Social Security Tax on GTL'
814 ||','||'Uncollected Medicare Tax On GTL'
815 -- Bug 4391218
816 ||','||'Income Under Section 409A on a Non-Qualified Deferred Comp Plan'
817 ||','||'Blank'
818 ||','||'Wages Subject to Puerto Rico Tax'
819 ||','||'Commissions Subject to Puerto Rico Tax'
820 ||','||'Allowances Subject to Puerto Rico Tax'
821 ||','||'Tips Subject to Puerto Rico Tax'
822 ||','||'Total Wages Commissions Tips And Allowances Subject to Puerto Rico Tax'
823 ||','||'Puerto Rico Tax Withheld'
824 ||','||'Retirement Fund Annual Contributions'
825 ||','||'Total Wages Tips And Other Compensation Subject to Virgin Islands or Guam or American Samoa or Northern Mariana Islands Income Tax'
826 ||','||'Virgin Islands or Guam Or American Samoa or Northern Mariana Islands Income Tax Withheld'
827 ||','||'Blank';
828 hr_utility.trace('value of formatted header string = '||header_string);
829 return header_string;
830 END mmrf_format_ru_record_header;
831
832 --
833 -- This function formats File Total Record (i.e. RF) record header
834 --
835 FUNCTION mmrf_format_rf_record_header(
836 p_report_type IN varchar2,
837 p_format IN varchar2,
838 p_report_qualifier IN varchar2,
839 p_record_name IN varchar2
840 ) RETURN VARCHAR2
841 IS
842 -- Local Variables
843 l_records varchar2(900);
844 header_string varchar2(3000);
845
846 BEGIN
847 hr_utility.trace('Formatting RF record header');
848 IF p_report_qualifier = 'PA' THEN
849 l_records := 'Blank'
850 ||','||'Number of RS Records'
851 ||','||'PA Taxable Wages'
852 ||','||'PA tax withheld';
853
854 ELSIF p_report_qualifier = 'CT' THEN
855 l_records := 'Number of RS Records'
856 ||','||'Blank'
857 ||','||'State Taxable Wages'
858 ||','||'Blank'
859 ||','||'SIT withheld'
860 ||','||'Blank';
861 ELSIF ((p_report_qualifier = 'IN') OR
865 l_records := 'Blank'
862 (p_report_qualifier = 'MA') OR
863 (p_report_qualifier = 'RI') OR
864 (p_report_qualifier = 'VA')) THEN
866 ||','||'Number of RS Records'
867 ||','||'Blank';
868 ELSE
869 l_records := 'Blank'
870 ||','||'Number of RW Records'
871 ||','||'Blank';
872 END IF;
873 header_string:= 'Record Identifier'
874 ||','||l_records;
875
876 hr_utility.trace('value of header string = '||header_string);
877 return header_string;
878 END mmrf_format_rf_record_header;
879
880 --
881 -- This function formats Wage Record (i.e. RCW) record header
882 --
883 FUNCTION mmrf2_format_rcw_record_header(
884 p_report_type IN varchar2,
885 p_format IN varchar2,
886 p_report_qualifier IN varchar2,
887 p_record_name IN varchar2
888 ) RETURN VARCHAR2
889 IS
890 -- Local Variables
891 header_string varchar2(2500);
892 BEGIN
893
894 hr_utility.trace('Formatting RCW record header');
895 header_string :=
896 'Record Identifier'
897 ||','||'(Orig) SSN'
898 ||','||'(Correct) SSN'
899 ||','||'(Orig) First Name'
900 ||','||'(Orig) Middle Name or Initial'
901 ||','||'(Orig) Last Name'
902 ||','||'(Correct) First Name'
903 ||','||'(Correct) Middle Name or Initial'
904 ||','||'(Correct) Last Name'
905 ||','||'Location Address'
906 ||','||'Delivery Address'
907 ||','||'City'
908 ||','||'State Abbreviation'
909 ||','||'Zip Code'
910 ||','||'Zip Code Extension'
911 ||','||'Blank'
912 ||','||'Foreign State / Province'
913 ||','||'Foreign Postal Code'
914 ||','||'Country Code'
915 ||','||'(Orig) Wages Tips And Other Compensation'
916 ||','||'(Correct) Wages Tips And Other Compensation'
917 ||','||'(Orig) FIT Withheld'
918 ||','||'(Correct) FIT Withheld'
919 ||','||'(Orig) SS Wages'
920 ||','||'(Correct) SS Wages'
921 ||','||'(Orig) SS Tax Withheld'
922 ||','||'(Correct) SS Tax Withheld'
923 ||','||'(Orig) Medicare Wages And Tips'
924 ||','||'(Correct) Medicare Wages And Tips'
925 ||','||'(Orig) Medicare Tax Withheld'
926 ||','||'(Correct) Medicare Tax Withheld'
927 ||','||'(Orig) SS Tips'
928 ||','||'(Correct) SS Tips'
929 ||','||'(Orig) Advance EIC'
930 ||','||'(Correct) Advance EIC'
931 ||','||'(Orig) Dependent Care Benefits'
932 ||','||'(Correct) Dependent Care Benefits'
933 ||','||'(Orig) Def. CompContrib to Sec 401(k)'
934 ||','||'(Correct) Def. CompContrib to Sec 401(k)'
935 ||','||'(Orig) Def. CompContrib to Sec 403(b)'
936 ||','||'(Correct) Def. CompContrib to Sec 403(b)'
937 ||','||'(Orig) Def. CompContrib to Sec 408(k)(6)'
938 ||','||'(Correct) Def. CompContrib to Sec 408(k)(6)'
939 ||','||'(Orig) Def. CompContrib to Sec 457(b)'
940 ||','||'(Correct) Def. CompContrib to Sec 457(b)'
941 ||','||'(Orig) Def. CompContrib to Sec (501)(c)(18)(D)'
942 ||','||'(Correct) Def. CompContrib to Sec (501)(c)(18)(D)'
943 ||','||'(Orig) Total Def. CompContrib '
944 ||','||'(Correct) Total Def. CompContrib '
945 ||','||'(Orig) Military EE''s Basic Qtrs Subsistence and Combat Pay'
946 ||','||'(Correct) Military EE''s Basic Qtrs Subsistence and Combat Pay'
947 ||','||'(Orig) Non-Qual. plan Sec.457 Dist or Contrib'
948 ||','||'(Correct) Non-Qual. plan Sec.457 Dist or Contrib'
949 ||','||'(Orig) ER Contribution to HSA'
950 ||','||'(Correct) ER Contribution to HSA'
951 ||','||'(Orig) Non-Qual. plan NOT Section 457 Dist or Contrib'
952 ||','||'(Correct) Non-Qual. plan NOT Section 457 Dist or Contrib'
953 ||','||'(Orig) Nontaxable Combat Pay'
954 ||','||'(Correct) Nontaxable Combat Pay'
955 --||','||'Blank'
956 ||','||'(Orig) Employer Cost of Premiums for GTL> $50k'
957 ||','||'(Correct) Employer Cost of Premiums for GTL> $50k'
958 ||','||'(Orig) Income From Exercise of Nonqual Stock Options'
959 ||','||'(Correct) Income From Exercise of Nonqual Stock Options'
960 ||','||'(Orig) Deferrals Under Section 409A'
961 ||','||'(Correct) Deferrals Under Section 409A'
962 ||','||'(Orig) Designated Roth Contr. to 401k Plan' /* 5358272 */
963 ||','||'(Correct) Designated Roth Contr. to 401k Plan'
964 ||','||'(Orig) Designated Roth Contr. to 403b Plan'
965 ||','||'(Correct) Designated Roth Contr. to 403b Plan'
966 --||','||'Blank'
967 ||','||'(Orig) Statutory Employee Indicator'
968 ||','||'(Correct) Statutory Employee Indicator'
969 ||','||'(Orig) Retirement Plan Indicator'
970 ||','||'(Correct) Retirement Plan Indicator'
971 ||','||'(Orig) Third-Party Sick Pay Indicator'
972 ||','||'(Correct) Third-Party Sick Pay Indicator'
973 ||','||'Blank';
974 hr_utility.trace('length of formatted header string = '|| to_char(length(header_string)) );
975 --hr_utility.trace('value of formatted header string = '||header_string);
976 return header_string;
977 END mmrf2_format_rcw_record_header;
978
979
980 --
981 -- This function formats Puertorico based Wage Record (i.e. RCO) record header
982 --
983 FUNCTION mmrf2_format_rco_record_header(
984 p_report_type IN varchar2,
985 p_format IN varchar2,
986 p_report_qualifier IN varchar2,
987 p_record_name IN varchar2
988 ) RETURN VARCHAR2
989 IS
990 -- Local Variables
991 header_string varchar2(3000);
992 BEGIN
993 hr_utility.trace('Formatting RCO record header');
994 header_string :=
995 'Record Identifier'
996 ||','||'Blank'
997 ||','||'(Orig) Allocated Tips'
998 ||','||'(Correct) Allocated Tips'
999 ||','||'(Orig) Uncollected Employee Tax on Tips'
1000 ||','||'(Correct) Uncollected Employee Tax on Tips'
1001 ||','||'(Orig) Medical Savings Account'
1002 ||','||'(Correct) Medical Savings Account'
1003 ||','||'(Orig) Simple Retirement Account'
1004 ||','||'(Correct) Simple Retirement Account'
1005 ||','||'(Orig) Qualified Adoption Expenses'
1006 ||','||'(Correct) Qualified Adoption Expenses'
1007 ||','||'(Orig) Uncollected Social Security or RRTA Tax on GTL > $50k'
1008 ||','||'(Correct) Uncollected Social Security or RRTA Tax on GTL > $50k'
1009 ||','||'(Orig) Uncollected Medicare Tax on GTL > $50k'
1010 ||','||'(Correct) Uncollected Medicare Tax on GTL > $50k'
1011 ||','||'(Orig) Income Under 409A'
1012 ||','||'(Correct) Income Under 409A';
1013 -- ||','||'Blank';
1014 --hr_utility.trace('value of formatted header string = '||header_string);
1015 return header_string;
1016 END mmrf2_format_rco_record_header;
1017
1018 --
1019 --BEGIN
1020 --hr_utility.trace_on(null,'PRINTHEADER');
1021 END pay_us_mmrf_print_rec_header;
1022 --End of Package Body