[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MMRF2_W2C_FORMAT_RECORD
Source
1 PACKAGE BODY PAY_US_MMRF2_W2C_FORMAT_RECORD AS
2 /* $Header: payusw2cmagfreco.pkb 120.2 2007/01/10 13:14:52 sausingh 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_mmrf2_w2c_format_record
12
13 File Name:
14 payusw2cmagfreco.pkb
15
16 Purpose
17 The purpose of this package is to format reacord to support the
18 generation of W-2c magnetic tape for US legilsative requirements.
19
20 Notes
21 Refers By: Package pay_us_w2c_in_mmref2_format
22
23 History
24 10-JAN-07 sausingh 115.11 5358272 added parameter_record(24) and parameter_record(25)
25 for RCT and RCW records for bug number 5358272
26 06-JAN-04 rsethupa 115.11 4097321 To display the RCO record in a03 and a02,
27 the global variable
28 pay_us_w2c_in_mmref2_format.rco_csv_record
29 is assigned values - return_value_csv and
30 return_value_mf depending on whether RCO has
31 errored out or it is correct.
32
33 15-NOV-04 meshah 115.10 4005679 changed for RCO record also.
34 15-NOV-04 meshah 115.9 4005679 now checking for
35 l_exclude_from_output_chk flag before
36 creating the csv record. if the mf is
37 fine then we use the exact copy of
38 mf to be displayed for a03 however
39 where there is an error in mf we use
40 a different plsql table to display the
41 values in the a02 file.
42 09-NOV-04 meshah 115.7 3996391 changed format_W2C_RCT_record
43 function. changed the p_parameter_name
44 for 15 and 16. Also changed the
45 format sequence.
46 26-Oct-04 meshah 115.8 3650105 added parameter 21 in
47 format_W2C_RCT_record
48 and format_W2C_RCW_record for
49 ER health savings account.
50 07-JAN-03 ppanda 115.7 3358901 negative balance on RCW and RCO record were
51 not errorning. When negative balance found on
52 RCW or RCO record employee is moved to a02
53 26-DEC-03 ppanda 115.5 3315951 RCO record was not having prefixed comma
54 which was causing RCO columns moved by one column left
55 and disallign all the fields in a02 and a03
56 10-DEC-03 ppanda 115.4 3311278 RCW and RCO formating changed
57 08-DEC-03 ppanda 115.3 3298890 On the A03 file, the columns (Orig) First Name,
58 (Orig) Middle Name, and (Orig) Last Name are being
59 populated even though no name change was made.
60 03-DEC-03 ppanda 115.2 3292976 RCA record format changed for position 166-171
61 14-OCT-03 ppanda 115.0 Created
62
63 */
64 /******************************************************************
65 ** Package Local Variables
66 ******************************************************************/
67 gv_package varchar2(50) := 'pay_us_mmrf2_w2c_format_record';
68
69 --
70 --
71 -- Formatting RCA record for W2c reporting in MMREF-2 format
72 --
73 /*--------------------- Parameter mapping Starts. ----------------------
74 Record Identifier, --> p_input_1
75 Submitter''s Employer Identification Number (EIN), --> p_input_2
76 Personal Identification Number (PIN) --> p_input_3,
77 Software Code --> p_input_4,
78 Company Name --> p_input_5,
79 Location Address --> p_input_6,
80 Delivery Address --> p_input_7,
81 City --> p_input_8 ,
82 State Abbreviation --> p_input_9 ,
83 Zip Code --> p_input_10,
84 Zip Code Extension --> p_input_11,
85 Foreign State / Province --> p_input_12,
86 Foreign Postal Code --> p_input_13,
87 Country Code --> p_input_14,
88 Contact Name --> p_input_15,
89 Contact Phone Number --> p_input_16
90 Contact Phone Extension --> p_input_17,
91 Contact E-Mail --> p_input_18,
92 Contact FAX --> p_input_19,
93 Preferred Method Of Problem Notification Code --> p_input_20,
94 Preparer Code --> p_input_21,
95 Resub inidicator --> p_input_22
96 resub WFID --> p_input_23
97
98 ------------------------ Parameter mapping Ends. -------------------------
99 */
100
101 FUNCTION format_W2C_RCA_record(
102 p_effective_date IN varchar2,
103 p_report_type IN varchar2,
104 p_format IN varchar2,
105 p_report_qualifier IN varchar2,
106 p_record_name IN varchar2,
107 p_input_1 IN varchar2,
108 p_input_2 IN varchar2,
109 p_input_3 IN varchar2,
110 p_input_4 IN varchar2,
111 p_input_5 IN varchar2,
112 p_input_6 IN varchar2,
113 p_input_7 IN varchar2,
114 p_input_8 IN varchar2,
115 p_input_9 IN varchar2,
116 p_input_10 IN varchar2,
117 p_input_11 IN varchar2,
118 p_input_12 IN varchar2,
119 p_input_13 IN varchar2,
120 p_input_14 IN varchar2,
121 p_input_15 IN varchar2,
122 p_input_16 IN varchar2,
123 p_input_17 IN varchar2,
124 p_input_18 IN varchar2,
125 p_input_19 IN varchar2,
126 p_input_20 IN varchar2,
127 p_input_21 IN varchar2,
128 p_input_22 IN varchar2,
129 p_input_23 IN varchar2,
130 p_input_24 IN varchar2,
131 p_input_25 IN varchar2,
132 p_input_26 IN varchar2,
133 p_input_27 IN varchar2,
134 p_input_28 IN varchar2,
135 p_input_29 IN varchar2,
136 p_input_30 IN varchar2,
137 p_input_31 IN varchar2,
138 p_input_32 IN varchar2,
139 p_input_33 IN varchar2,
140 p_input_34 IN varchar2,
141 p_input_35 IN varchar2,
142 p_input_36 IN varchar2,
143 p_input_37 IN varchar2,
144 p_input_38 IN varchar2,
145 p_input_39 IN varchar2,
146 p_input_40 IN varchar2,
147 p_validate IN varchar2,
148 p_exclude_from_output OUT nocopy varchar2,
149 sp_out_1 OUT nocopy varchar2,
150 sp_out_2 OUT nocopy varchar2,
151 sp_out_3 OUT nocopy varchar2,
152 sp_out_4 OUT nocopy varchar2,
153 sp_out_5 OUT nocopy varchar2,
154 ret_str_len OUT nocopy number,
155 p_error OUT nocopy boolean
156 ) RETURN VARCHAR2
157 IS
158 l_agent_indicator varchar2(1);
159 l_emp_ein varchar2(100);
160 l_agent_ein varchar2(100);
161 l_other_ein varchar2(100);
162 l_term_indicator varchar2(1);
163 l_exclude_from_output_chk boolean;
164 l_input_8 varchar2(50);
165 l_bus_tax_acct_number varchar2(50);
166 l_rep_qtr varchar2(300);
167 l_rep_prd varchar2(300);
168 l_end_of_rec varchar2(20);
169 return_value varchar2(32767);
170 l_pin varchar2(50);
171 l_pblm_code varchar2(1);
172 l_preparer_code varchar2(1);
173 p_end_of_rec varchar2(20) :=
174 fnd_global.local_chr(13)||fnd_global.local_chr(10);
175 r_input_1 varchar2(300);
176 r_input_2 varchar2(300);
177 r_input_3 varchar2(300);
178 r_input_4 varchar2(300);
179 r_input_5 varchar2(300);
180 r_input_6 varchar2(300);
181 r_input_7 varchar2(300);
182 r_input_8 varchar2(300);
183 r_input_9 varchar2(300);
184 r_input_10 varchar2(300);
185 r_input_11 varchar2(300);
186 r_input_12 varchar2(300);
187 r_input_13 varchar2(300);
188 r_input_14 varchar2(300);
189 r_input_15 varchar2(300);
190 r_input_16 varchar2(300);
191 r_input_17 varchar2(300);
192 r_input_18 varchar2(300);
193 r_input_19 varchar2(300);
194 r_input_20 varchar2(300);
195 r_input_21 varchar2(300);
196 r_input_22 varchar2(300);
197 r_input_23 varchar2(300);
198 r_input_24 varchar2(300);
199 r_input_25 varchar2(300);
200 r_input_26 varchar2(300);
201 r_input_27 varchar2(300);
202 r_input_28 varchar2(300);
203 r_input_29 varchar2(300);
204 r_input_30 varchar2(300);
205 r_input_31 varchar2(300);
206 r_input_32 varchar2(300);
207 r_input_33 varchar2(300);
208 r_input_34 varchar2(300);
209 r_input_35 varchar2(300);
210 r_input_36 varchar2(300);
211 r_input_37 varchar2(300);
212 r_input_38 varchar2(300);
213 r_input_39 varchar2(300);
214
215 BEGIN
216 hr_utility.set_location(gv_package || '.format_W2C_RCA_record', 10);
217 hr_utility.trace('RCA Record Formatting started for W-2c');
218 hr_utility.trace(' Format_W2_RA_Record Begin for Company '|| p_input_5);
219 -- Initializing local variables with parameter value
220 --{
221 r_input_2 := p_input_2;
222 r_input_3 := p_input_3;
223 r_input_4 := p_input_4;
224 r_input_5 := p_input_5;
225 r_input_6 := p_input_6;
226 r_input_7 := p_input_7;
227 r_input_8 := p_input_8;
228 r_input_9 := p_input_9;
229 r_input_10 := p_input_10;
230 r_input_11 := p_input_11;
231 r_input_12 := p_input_12;
232 r_input_13 := p_input_13;
233 r_input_14 := p_input_14;
234 r_input_15 := p_input_15;
235 r_input_16 := p_input_16;
236 r_input_17 := p_input_17;
237 r_input_18 := p_input_18;
238 r_input_19 := p_input_19;
239 r_input_20 := p_input_20;
240 r_input_21 := p_input_21;
241 r_input_22 := p_input_22;
242 r_input_23 := p_input_23;
243 r_input_24 := p_input_24;
244 r_input_25 := p_input_25;
245 r_input_26 := p_input_26;
246 r_input_27 := p_input_27;
247 r_input_28 := p_input_28;
248 r_input_29 := p_input_29;
249 r_input_30 := p_input_30;
250 r_input_31 := p_input_31;
251 r_input_32 := p_input_32;
252 r_input_33 := p_input_33;
253 r_input_34 := p_input_34;
254 r_input_35 := p_input_35;
255 r_input_36 := p_input_36;
256 r_input_37 := p_input_37;
257 r_input_38 := p_input_38;
258 r_input_39 := p_input_39;
259 --}
260 -- Validation Starts
261 IF p_input_40 = 'FLAT' THEN
262 -- EIN Validation and format
263 hr_utility.set_location(gv_package || '.format_W2C_RCA_record', 20);
264 l_emp_ein :=
265 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
266 p_report_type,
267 p_format,
268 p_report_qualifier,
269 p_record_name,
270 'EIN',
271 p_input_2,
272 'Submitters EIN',
273 p_input_17,
274 null,
275 p_validate,
276 p_exclude_from_output,
277 sp_out_1,
278 sp_out_2);
279
280 IF p_exclude_from_output = 'Y' THEN
281 l_exclude_from_output_chk := TRUE;
282 END IF;
283 l_emp_ein := rpad(substr(l_emp_ein,1,9),9);
284 hr_utility.trace('After Validation formatted EIN '||l_emp_ein);
285 -- EIN Validation Ends
286 --
287 hr_utility.set_location(gv_package || '.format_W2C_RCA_record', 30);
288
289 -- Formatiing Starts
290
291 -- Formating PIN which is of 17 char long
292
293 l_pin := rpad(substr(nvl(p_input_3,' '),1,17),17);
294 hr_utility.trace('Formatted PIN = '||l_pin);
295
296 /* Checking for preferred method of problem notification code which is
297 1=email, 2=USPS */
298 IF ((p_input_20 = '1' ) OR
299 (p_input_20 = '2' ) ) THEN
300 hr_utility.trace('Preferred method of code is correct. it is '||p_input_20);
301 l_pblm_code:= lpad(p_input_20,1);
302 ELSE
303 hr_utility.trace('Preferred method of code is incorrect. it is '||p_input_20);
304 l_pblm_code:= lpad(' ',1);
305 END IF;
306
307 If( (p_input_21 = 'A' )OR
308 (p_input_21 = 'S' )OR
309 (p_input_21 = 'L' )OR
310 (p_input_21 = 'P' )OR
311 (p_input_21 = 'O' )) THEN
312 l_preparer_code:= lpad(p_input_21,1);
313 hr_utility.trace('l_preparer_code is correct. it is '||p_input_21);
314 ELSE
315 l_preparer_code:= lpad(' ',1);
316 hr_utility.trace('l_preparer_code is incorrect. it is '||p_input_21);
317 END IF;
318
319 -- Formatiing Ends
320 --
321 hr_utility.set_location(gv_package || '.format_W2C_RCA_record', 40);
322
323 -- RA Record of Flat Type
324 --
325 return_value:='RCA'
326 ||l_emp_ein
327 ||l_pin
328 ||rpad(substr(nvl(r_input_4,' '),1,2),2)
329 ||rpad(substr(nvl(r_input_5,' '),1,57),57)
330 ||rpad(substr(nvl(r_input_6,' '),1,22),22)
331 ||rpad(substr(nvl(r_input_7,' '),1,22),22)
332 ||rpad(substr(nvl(r_input_8,' '),1,22),22)
333 ||rpad(substr(nvl(r_input_9,' '),1,2),2)
334 ||rpad(substr(nvl(r_input_10,' '),1,5),5)
335 ||rpad(substr(nvl(r_input_11,' '),1,4),10)
336 ||rpad(substr(nvl(r_input_12,' '),1,23),23)
337 ||rpad(substr(nvl(r_input_13,' '),1,15),15)
338 ||rpad(substr(nvl(r_input_14,' '),1,2),2)
339 ||rpad(substr(nvl(r_input_15,' '),1,27),27)
340 ||rpad(substr(nvl(r_input_16,' '),1,15),15)
341 ||rpad(substr(nvl(r_input_17,' '),1,5),8)
342 ||rpad(substr(nvl(r_input_18,' '),1,43),43)
343 ||rpad(substr(nvl(r_input_19,' '),1,10),10)
344 ||l_pblm_code
345 ||l_preparer_code
346 ||rpad(substr(nvl(r_input_22,'0'),1,1),1)
347 ||rpad(substr(nvl(r_input_23,' '),1,6),6)
348 ||lpad(' ',701);
349 -- These Variables are initialized to derive the file total
350 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rcf := 0;
351 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcf := 0;
352 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf := 0;
353 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcf := 0;
354
355 --
356 hr_utility.set_location(gv_package || '.format_W2C_RCA_record', 50);
357 hr_utility.trace('RCA Record of FLAT Type -----');
358 ret_str_len:=length(return_value);
359 END IF;
360 p_error := l_exclude_from_output_chk;
361 ret_str_len:=length(return_value);
362 return return_value;
363 END format_W2C_RCA_record; -- End of formatting W2c RCA Record
364
365 --
366 -- Formatting RCE Record for W2c reporting in MMREF-2 format
367 --
368 FUNCTION format_W2C_RCE_record(
369 p_effective_date IN varchar2,
370 p_report_type IN varchar2,
371 p_format IN varchar2,
372 p_report_qualifier IN varchar2,
373 p_record_name IN varchar2,
374 p_input_1 IN varchar2,
375 p_input_2 IN varchar2,
376 p_input_3 IN varchar2,
377 p_input_4 IN varchar2,
378 p_input_5 IN varchar2,
379 p_input_6 IN varchar2,
380 p_input_7 IN varchar2,
381 p_input_8 IN varchar2,
382 p_input_9 IN varchar2,
383 p_input_10 IN varchar2,
384 p_input_11 IN varchar2,
385 p_input_12 IN varchar2,
386 p_input_13 IN varchar2,
387 p_input_14 IN varchar2,
388 p_input_15 IN varchar2,
389 p_input_16 IN varchar2,
390 p_input_17 IN varchar2,
391 p_input_18 IN varchar2,
392 p_input_19 IN varchar2,
393 p_input_20 IN varchar2,
394 p_input_21 IN varchar2,
395 p_input_22 IN varchar2,
396 p_input_23 IN varchar2,
397 p_input_24 IN varchar2,
398 p_input_25 IN varchar2,
399 p_input_26 IN varchar2,
400 p_input_27 IN varchar2,
401 p_input_28 IN varchar2,
402 p_input_29 IN varchar2,
403 p_input_30 IN varchar2,
404 p_input_31 IN varchar2,
405 p_input_32 IN varchar2,
406 p_input_33 IN varchar2,
407 p_input_34 IN varchar2,
408 p_input_35 IN varchar2,
409 p_input_36 IN varchar2,
410 p_input_37 IN varchar2,
411 p_input_38 IN varchar2,
412 p_input_39 IN varchar2,
413 p_input_40 IN varchar2,
414 p_validate IN varchar2,
415 p_exclude_from_output OUT nocopy varchar2,
416 sp_out_1 OUT nocopy varchar2,
417 sp_out_2 OUT nocopy varchar2,
418 sp_out_3 OUT nocopy varchar2,
419 sp_out_4 OUT nocopy varchar2,
420 sp_out_5 OUT nocopy varchar2,
421 ret_str_len OUT nocopy number,
422 p_error OUT nocopy boolean
423 ) RETURN VARCHAR2
424 IS
425 /* Record Identifier --> p_input_1,
426 Tax Year --> p_input_2,
427 Employer / Agent Employer Identification Number (EIN) --> p_input_3,
428 */
429
430 l_agent_indicator varchar2(1);
431 l_emp_ein varchar2(100);
432 l_agent_ein varchar2(100);
433 l_other_ein varchar2(100);
434 l_term_indicator varchar2(1);
435 l_exclude_from_output_chk boolean;
436 l_input_8 varchar2(50);
437 l_bus_tax_acct_number varchar2(50);
438 l_rep_qtr varchar2(300);
439 l_rep_prd varchar2(300);
440 l_end_of_rec varchar2(20);
441 return_value varchar2(32767);
442
443 r_input_1 varchar2(300);
444 r_input_2 varchar2(300);
445 r_input_3 varchar2(300);
446 r_input_4 varchar2(300);
447 r_input_5 varchar2(300);
448 r_input_6 varchar2(300);
449 r_input_7 varchar2(300);
450 r_input_8 varchar2(300);
451 r_input_9 varchar2(300);
452 r_input_10 varchar2(300);
453 r_input_11 varchar2(300);
454 r_input_12 varchar2(300);
455 r_input_13 varchar2(300);
456 r_input_14 varchar2(300);
457 r_input_15 varchar2(300);
458 r_input_16 varchar2(300);
459 r_input_17 varchar2(300);
460 r_input_18 varchar2(300);
461 r_input_19 varchar2(300);
462 r_input_20 varchar2(300);
463 r_input_21 varchar2(300);
464 r_input_22 varchar2(300);
465 r_input_23 varchar2(300);
466 r_input_24 varchar2(300);
467 r_input_25 varchar2(300);
468 r_input_26 varchar2(300);
469 r_input_27 varchar2(300);
470 r_input_28 varchar2(300);
471 r_input_29 varchar2(300);
472 r_input_30 varchar2(300);
473 r_input_31 varchar2(300);
474 r_input_32 varchar2(300);
475 r_input_33 varchar2(300);
476 r_input_34 varchar2(300);
477 r_input_35 varchar2(300);
478 r_input_36 varchar2(300);
479 r_input_37 varchar2(300);
480 r_input_38 varchar2(300);
481 r_input_39 varchar2(300);
482
483 BEGIN
484 hr_utility.set_location(gv_package || '.format_W2C_RCE_record', 10);
485 hr_utility.trace('RCE Record Formatting started ');
486 hr_utility.trace('Format Record Type '|| p_input_40);
487 -- Initializing local variables with parameter value
488 --{
489 r_input_2 := p_input_2;
490 r_input_3 := p_input_3;
491 r_input_4 := p_input_4;
492 r_input_5 := p_input_5;
493 r_input_6 := p_input_6;
494 r_input_7 := p_input_7;
495 r_input_8 := p_input_8;
496 r_input_9 := p_input_9;
497 r_input_10 := p_input_10;
498 r_input_11 := p_input_11;
499 r_input_12 := p_input_12;
500 r_input_13 := p_input_13;
501 r_input_14 := p_input_14;
502 r_input_15 := p_input_15;
503 r_input_16 := p_input_16;
504 r_input_17 := p_input_17;
505 r_input_18 := p_input_18;
506 r_input_19 := p_input_19;
507 r_input_20 := p_input_20;
508 r_input_21 := p_input_21;
509 r_input_22 := p_input_22;
510 r_input_23 := p_input_23;
511 r_input_24 := p_input_24;
512 r_input_25 := p_input_25;
513 r_input_26 := p_input_26;
514 r_input_27 := p_input_27;
515 r_input_28 := p_input_28;
516 r_input_29 := p_input_29;
517 r_input_30 := p_input_30;
518 r_input_31 := p_input_31;
519 r_input_32 := p_input_32;
520 r_input_33 := p_input_33;
521 r_input_34 := p_input_34;
522 r_input_35 := p_input_35;
523 r_input_36 := p_input_36;
524 r_input_37 := p_input_37;
525 r_input_38 := p_input_38;
526 r_input_39 := p_input_39;
527 --}
528 hr_utility.set_location(gv_package || '.format_W2C_RCE_record', 20);
529
530 -- Validation for RCE Record starts
531 -- These validation are used only for mf file only.
532 -- not for any of the audit report
533 --
534 IF p_input_40 = 'FLAT' THEN
535 --{
536 hr_utility.set_location(gv_package || '.format_W2C_RCE_record', 30);
537 hr_utility.trace('before data_validation of EIN is '||p_input_3);
538 l_emp_ein :=
539 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
540 p_report_type,
541 p_format,
542 p_report_qualifier,
543 p_record_name,
544 'EIN',
545 p_input_3,
546 'Employer EIN',
547 p_input_9,
548 null,
549 p_validate,
550 p_exclude_from_output,
551 sp_out_1,
552 sp_out_2);
553 IF p_exclude_from_output = 'Y' THEN
554 l_exclude_from_output_chk := TRUE;
555 END IF;
556 hr_utility.trace('after data_validation of EIN value is '||l_emp_ein);
557 hr_utility.trace('Exclude from output '||p_exclude_from_output);
558 return_value := 'RCE'
559 ||rpad(substr(nvl(r_input_2,' '),1,4),13)
560 ||l_emp_ein
561 ||lpad(' ',197)
562 ||lpad(NVL(r_input_19,'R'),1)
563 ||lpad(' ',801);
564 --}
565 END IF;
566 p_error := l_exclude_from_output_chk;
567 ret_str_len:=length(return_value);
568 return return_value;
569 end format_W2C_RCE_record;
570 -- End of Formatting RCE record for W-2c reporting
571 --
572
573
574 --
575 -- This function is used for formatting RCF Record in MMREF-2 format
576 --
577 /*
578 Effective Date --> p_effective_date
579 Report Type (i.e.W2 ) --> p_report_type
580 Report Format --> p_format
581 Report Qualifier --> p_report_qualifier
582 Record Name (i.e. RF) --> p_record_name
583 Record Identifier --> p_record_identifier
584 Number of RW Records --> p_total_no_of_record
585 Wages, Tips and other Compensation --> p_total_wages
586 Federal Income Tax Withheld --> p_total_taxes
587 Report Format Mode (FLAT, CSV) --> p_format_mode
588 Validation Flag --> p_validate
589 Exclude Record from mag file --> p_exclude_from_output
590 Return Record Length --> ret_str_len
591 Validation Error Flag --> p_error
592 */
593 FUNCTION format_W2C_RCF_record(
594 p_effective_date IN varchar2,
595 p_report_type IN varchar2,
596 p_format IN varchar2,
597 p_report_qualifier IN varchar2,
598 p_record_name IN varchar2,
599 p_record_identifier IN varchar2,
600 p_total_no_of_record IN varchar2,
601 p_total_wages IN varchar2,
602 p_total_taxes IN varchar2,
603 p_format_mode IN varchar2,
604 p_validate IN varchar2,
605 p_exclude_from_output OUT nocopy varchar2,
606 ret_str_len OUT nocopy number,
607 p_error OUT nocopy boolean
608 ) RETURN VARCHAR2
609 IS
610 return_value varchar2(32767);
611 l_exclude_from_output_chk boolean := FALSE;
612 l_total_rcw_records varchar2(50);
613 l_wages varchar2(100);
614 l_taxes varchar2(100);
615 sp_out_1 varchar2(100);
616 sp_out_2 varchar2(100);
617 BEGIN
618 hr_utility.trace('Formatting RCF Record');
619 l_total_rcw_records := lpad(nvl(p_total_no_of_record,'0'),9,0);
620 return_value := 'RCF'
621 ||l_total_rcw_records
622 ||lpad(' ',1012);
623 p_error := l_exclude_from_output_chk;
624 ret_str_len:=length(return_value);
625 return return_value;
626 END format_W2C_RCF_record; -- End of Formatting RF Record for W2 Reporting
627
628 --
629 -- This function is used for formatting RCT Record in MMREF-2 format
630 --
631 /*
632 Effective Date --> p_effective_date
633 Report Type (i.e.W2C ) --> p_report_type
634 Report Format --> p_format
635 Report Qualifier --> p_report_qualifier
636 Record Name (i.e. RCT) --> p_record_name
637 Record Identifier --> p_record_identifier
638 Number of RW Records --> p_total_no_of_record
639 Wages, Tips and other Compensation --> p_total_wages
640 Federal Income Tax Withheld --> p_total_taxes
641 Report Format Mode (FLAT, CSV) --> p_format_mode
642 Validation Flag --> p_validate
643 Exclude Record from mag file --> p_exclude_from_output
644 Return Record Length --> ret_str_len
645 Validation Error Flag --> p_error
646 */
647 FUNCTION format_W2C_RCT_record(
648 p_effective_date IN varchar2,
649 p_report_type IN varchar2,
650 p_format IN varchar2,
651 p_report_qualifier IN varchar2,
652 p_record_name IN varchar2,
653 p_record_identifier IN varchar2,
654 p_total_no_of_record IN varchar2,
655 p_total_wages IN varchar2,
656 p_total_taxes IN varchar2,
657 p_format_mode IN varchar2,
658 p_validate IN varchar2,
659 p_exclude_from_output OUT nocopy varchar2,
660 ret_str_len OUT nocopy number,
661 p_error OUT nocopy boolean
662 ) RETURN VARCHAR2
663 IS
664 return_value varchar2(32767);
665 l_exclude_from_output_chk boolean := FALSE;
666 l_total_rcw_records varchar2(50);
667 l_wages varchar2(100);
668 l_taxes varchar2(100);
669 sp_out_1 varchar2(100);
670 sp_out_2 varchar2(100);
671 TYPE function_columns IS RECORD(
672 p_parameter_name varchar2(100)
673 );
674 function_parameter_rec function_columns;
675 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
676 INDEX BY BINARY_INTEGER;
677 parameter_record input_parameter_record;
678 ln_no_of_rcw_wages number := 20;
679 lv_no_of_rcw_records varchar2(10) := ' ';
680
681 BEGIN
682 hr_utility.set_location(gv_package || '.format_W2C_RCT_record', 10);
683 hr_utility.trace('Formatting RCT Record');
684
685 parameter_record.delete;
686 parameter_record(1).p_parameter_name := 'Wages,Tips And Other Compensation';
687 parameter_record(2).p_parameter_name := 'Federal Income Tax Withheld';
688 parameter_record(3).p_parameter_name := 'Social Security Wages';
689 parameter_record(4).p_parameter_name := 'Social Security Tax Withheld';
690 parameter_record(5).p_parameter_name := 'Medicare Wages And Tips';
691 parameter_record(6).p_parameter_name := 'Medicare Tax Withheld';
692 parameter_record(7).p_parameter_name := 'Social Security Tips';
693 parameter_record(8).p_parameter_name := 'Advance Earned Income Credit';
694 parameter_record(9).p_parameter_name := 'Dependent Care Benefits';
695 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
696 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
697 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
698 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
699 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
700 parameter_record(15).p_parameter_name:= 'Deferred Compensation Contribution';
701 parameter_record(16).p_parameter_name:= 'Military Combat Pay';
702 parameter_record(17).p_parameter_name:= 'Non-Qual. plan Sec 457';
703 parameter_record(18).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
704 parameter_record(19).p_parameter_name:= 'Employer cost of premiun';
705 parameter_record(20).p_parameter_name:= 'Income from nonqualified stock option';
706 parameter_record(21).p_parameter_name:= 'ER Contribution to HSA';
707 parameter_record(22).p_parameter_name:= 'Nontaxable Combat Pay';
708 parameter_record(23).p_parameter_name:= 'Nonqual 409A Deferral Amount';
709 parameter_record(24).p_parameter_name:= 'Designated Roth Contr. to 401k Plan'; /* 5358272 */
710 parameter_record(25).p_parameter_name:= 'Designated Roth Contr. to 403b Plan'; /* 5358272 */
711
712
713
714
715 hr_utility.set_location(gv_package || '.format_W2C_RCT_record', 20);
716 ln_no_of_rcw_wages := 25; /* 5358272 */
717
718 FOR i IN 1..ln_no_of_rcw_wages
719 LOOP
720 hr_utility.trace(parameter_record(i).p_parameter_name||' OLD-> '
721 || pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old_formated
722 || ' NEW-> '|| pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new_formated);
723 END LOOP;
724
725 hr_utility.set_location(gv_package || '.format_W2C_RCT_record', 30);
726
727 lv_no_of_rcw_records := lpad(pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct,7,0);
728 return_value := 'RCT'
729 ||lv_no_of_rcw_records
730 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(1).rct_wage_old_formated
731 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(1).rct_wage_new_formated
732 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(2).rct_wage_old_formated
733 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(2).rct_wage_new_formated
734 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(3).rct_wage_old_formated
735 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(3).rct_wage_new_formated
736 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(4).rct_wage_old_formated
737 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(4).rct_wage_new_formated
738 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(5).rct_wage_old_formated
739 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(5).rct_wage_new_formated
740 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(6).rct_wage_old_formated
741 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(6).rct_wage_new_formated
742 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(7).rct_wage_old_formated
743 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(7).rct_wage_new_formated
744 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(8).rct_wage_old_formated
745 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(8).rct_wage_new_formated
746 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(9).rct_wage_old_formated
747 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(9).rct_wage_new_formated
748 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(10).rct_wage_old_formated
749 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(10).rct_wage_new_formated
750 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(11).rct_wage_old_formated
751 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(11).rct_wage_new_formated
752 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(12).rct_wage_old_formated
753 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(12).rct_wage_new_formated
754 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(13).rct_wage_old_formated
755 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(13).rct_wage_new_formated
756 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(14).rct_wage_old_formated
757 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(14).rct_wage_new_formated
758 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(15).rct_wage_old_formated
759 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(15).rct_wage_new_formated
760 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(16).rct_wage_old_formated
761 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(16).rct_wage_new_formated
762 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(17).rct_wage_old_formated
763 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(17).rct_wage_new_formated
764 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(21).rct_wage_old_formated
765 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(21).rct_wage_new_formated
766 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(18).rct_wage_old_formated
767 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(18).rct_wage_new_formated
768 --||lpad(' ',60)
769 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(22).rct_wage_old_formated
770 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(22).rct_wage_new_formated
771 ||lpad(' ',30)
772 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(19).rct_wage_old_formated
773 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(19).rct_wage_new_formated
774 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(20).rct_wage_old_formated
775 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(20).rct_wage_new_formated
776 --||lpad(' ',324)
777 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(23).rct_wage_old_formated
778 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(23).rct_wage_new_formated
779 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(24).rct_wage_old_formated /* 5358272 */
780 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(24).rct_wage_new_formated
781 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(25).rct_wage_old_formated
782 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(25).rct_wage_new_formated
783 ||lpad(' ',234);
784
785 hr_utility.set_location(gv_package || '.format_W2C_RCT_record', 40);
786 p_error := l_exclude_from_output_chk;
787 ret_str_len:=length(return_value);
788 return return_value;
789 END format_W2C_RCT_record; -- End of Formatting RCT Record for W-2c Reporting
790
791
792 --
793 -- This function is used for formatting RCU Record in MMREF-2 format
794 --
795 /*
796 Effective Date --> p_effective_date
797 Report Type (i.e.W2C ) --> p_report_type
798 Report Format --> p_format
799 Report Qualifier --> p_report_qualifier
800 Record Name (i.e. RCU) --> p_record_name
801 Record Identifier --> p_record_identifier
802 Number of RW Records --> p_total_no_of_record
803 Wages, Tips and other Compensation --> p_total_wages
804 Federal Income Tax Withheld --> p_total_taxes
805 Report Format Mode (FLAT, CSV) --> p_format_mode
806 Validation Flag --> p_validate
807 Exclude Record from mag file --> p_exclude_from_output
808 Return Record Length --> ret_str_len
809 Validation Error Flag --> p_error
810 */
811 FUNCTION format_W2C_RCU_record(
812 p_effective_date IN varchar2,
813 p_report_type IN varchar2,
814 p_format IN varchar2,
815 p_report_qualifier IN varchar2,
816 p_record_name IN varchar2,
817 p_record_identifier IN varchar2,
818 p_total_no_of_record IN varchar2,
819 p_total_wages IN varchar2,
820 p_total_taxes IN varchar2,
821 p_format_mode IN varchar2,
822 p_validate IN varchar2,
823 p_exclude_from_output OUT nocopy varchar2,
824 ret_str_len OUT nocopy number,
825 p_error OUT nocopy boolean
826 ) RETURN VARCHAR2
827 IS
828 return_value varchar2(32767);
829 l_exclude_from_output_chk boolean := FALSE;
830 l_total_rcu_records varchar2(50);
831 l_wages varchar2(100);
832 l_taxes varchar2(100);
833 sp_out_1 varchar2(100);
834 sp_out_2 varchar2(100);
835 TYPE function_columns IS RECORD(
836 p_parameter_name varchar2(100)
837 );
838 function_parameter_rec function_columns;
839 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
840 INDEX BY BINARY_INTEGER;
841 parameter_record input_parameter_record;
842 ln_no_of_rco_wages number := 8;
843 lv_no_of_rco_records varchar2(10) := ' ';
844
845 BEGIN
846 hr_utility.set_location(gv_package || '.format_W2C_RCU_record', 10);
847 hr_utility.trace('Formatting RCU Record');
848 parameter_record.delete;
849 parameter_record(1).p_parameter_name:= ' Allocated Tips';
850 parameter_record(2).p_parameter_name:= 'Uncollected employee tax on tips';
851 parameter_record(3).p_parameter_name:= 'medical savings a/c';
852 parameter_record(4).p_parameter_name:= 'simple retirement a/c';
853 parameter_record(5).p_parameter_name:= 'qualified adoption expenses';
854 parameter_record(6).p_parameter_name:= 'Uncollected SS tax';
855 parameter_record(7).p_parameter_name:= 'Uncollected medicare tax';
856 parameter_record(8).p_parameter_name:= 'Income under 409A';
857
858 hr_utility.set_location(gv_package || '.format_W2C_RCU_record', 20);
859 ln_no_of_rco_wages := 8;
860 FOR i IN 1..ln_no_of_rco_wages
861 LOOP
862 hr_utility.trace(parameter_record(i).p_parameter_name||' OLD-> '
863 || pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old_formated
864 || ' NEW-> '|| pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new_formated);
865 END LOOP;
866 hr_utility.set_location(gv_package || '.format_W2C_RCU_record', 30);
867 lv_no_of_rco_records := lpad(pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu,7,0);
868 return_value := 'RCU'
869 ||lv_no_of_rco_records
870 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(1).rcu_wage_old_formated
871 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(1).rcu_wage_new_formated
872 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(2).rcu_wage_old_formated
873 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(2).rcu_wage_new_formated
874 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(3).rcu_wage_old_formated
875 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(3).rcu_wage_new_formated
876 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(4).rcu_wage_old_formated
877 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(4).rcu_wage_new_formated
878 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(5).rcu_wage_old_formated
879 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(5).rcu_wage_new_formated
880 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(6).rcu_wage_old_formated
881 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(6).rcu_wage_new_formated
882 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(7).rcu_wage_old_formated
883 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(7).rcu_wage_new_formated
884 --||lpad(' ',804);
885 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(8).rcu_wage_old_formated
886 ||pay_us_w2c_in_mmref2_format.ltr_rcu_info(8).rcu_wage_new_formated
887 ||lpad(' ',774);
888
889 hr_utility.set_location(gv_package || '.format_W2C_RCU_record', 40);
890 p_error := l_exclude_from_output_chk;
891 ret_str_len:=length(return_value);
892 return return_value;
893 END format_W2C_RCU_record; -- End of Formatting RCU Record for W-2c Reporting
894 --
895 -- This function is used for formatting RCU Record in MMREF-2 format
896 --
897 FUNCTION format_W2C_RCW_record ( p_effective_date IN varchar2,
898 p_report_type IN varchar2,
899 p_format IN varchar2,
900 p_report_qualifier IN varchar2,
901 p_record_name IN varchar2,
902 p_tax_unit_id IN varchar2,
903 p_record_identifier IN varchar2,
904 p_ssn_old IN varchar2,
905 p_ssn_new IN varchar2,
906 p_first_name_old IN varchar2,
907 p_middle_name_old IN varchar2,
908 p_last_name_old IN varchar2,
909 p_first_name_old_raw IN varchar2,
910 p_middle_name_old_raw IN varchar2,
911 p_last_name_old_raw IN varchar2,
912 p_first_name_new IN varchar2,
913 p_middle_name_new IN varchar2,
914 p_last_name_new IN varchar2,
915 p_location_address IN varchar2,
916 p_delivery_address IN varchar2,
917 p_city IN varchar2,
918 p_state IN varchar2,
919 p_zip IN varchar2,
920 p_zip_extension IN varchar2,
921 p_foreign_state IN varchar2,
922 p_foreign_postal_code IN varchar2,
923 p_country_code IN varchar2,
924 p_statutory_emp_indicator_old IN varchar2,
925 p_statutory_emp_indicator_new IN varchar2,
926 p_retire_plan_indicator_old IN varchar2,
927 p_retire_plan_indicator_new IN varchar2,
928 p_sickpay_indicator_old IN varchar2,
929 p_sickpay_indicator_new IN varchar2,
930 p_orig_assignment_actid IN varchar2,
931 p_correct_assignment_actid IN varchar2,
932 p_employee_number IN varchar2,
933 rcw_wage_rec IN OUT nocopy pay_us_w2c_in_mmref2_format.table_wage_record,
934 p_format_type IN varchar2,
935 p_validate IN varchar2,
936 p_exclude_from_output OUT nocopy varchar2,
937 ret_str_len OUT nocopy varchar2,
938 p_error OUT nocopy boolean
939 )
940 return varchar2
941 IS
942 l_emp_name_or_number varchar2(50);
943 l_emp_number varchar2(50);
944 l_first_name_old varchar2(150);
945 l_middle_name_old varchar2(100);
946 l_last_name_old varchar2(150);
947 l_first_name_new varchar2(150);
948 l_middle_name_new varchar2(100);
949 l_last_name_new varchar2(150);
950 l_full_name_old varchar2(100);
951 l_full_name_new varchar2(100);
952 l_suffix varchar2(100);
953 l_ssn_old varchar2(100);
954 l_ssn_new varchar2(100);
955 l_message varchar2(2000);
956 l_description varchar2(50);
957 l_field_description varchar2(50);
958 l_ss_count number(10);
959 l_amount number(10);
960 return_value_mf varchar2(32767);
961 return_value_csv varchar2(32767);
962 return_value_blank varchar2(32767);
963 l_err boolean;
964 l_exclude_from_output_chk boolean;
965 l_ss_tax_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
966 l_ss_wage_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
967 sp_out_1 varchar2(100);
968 sp_out_2 varchar2(100);
969
970 CURSOR GET_SS_LIMIT(c_date varchar2)
971 IS
972 SELECT SS_EE_WAGE_LIMIT*100,
973 (SS_EE_WAGE_LIMIT*SS_EE_RATE)*100 tax
974 FROM PAY_US_FEDERAL_TAX_INFO_F
975 WHERE TO_DATE(C_DATE,'DD-MM-YYYY') BETWEEN EFFECTIVE_START_DATE
976 AND EFFECTIVE_END_DATE
977 AND FED_INFORMATION_CATEGORY = '401K LIMITS';
978
979
980 TYPE function_columns IS RECORD(
981 p_parameter_name varchar2(100)
982 );
983 function_parameter_rec function_columns;
984 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
985 INDEX BY BINARY_INTEGER;
986 parameter_record input_parameter_record;
987 BEGIN
988 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 10);
989 --{
990
991 l_first_name_old := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
992 substr(p_first_name_old,1,15),' '),15));
993 l_middle_name_old := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
994 substr(p_middle_name_old,1,15),' '),15));
995 l_last_name_old := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
996 substr(p_last_name_old,1,20),' '),20));
997 l_full_name_old := substr(pay_us_reporting_utils_pkg.Character_check(ltrim(rtrim(p_first_name_old)||' '||
998 rtrim(p_last_name_old))),1,50);
999
1000 l_first_name_new := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
1001 substr(p_first_name_new,1,15),' '),15));
1002 l_middle_name_new := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
1003 substr(p_middle_name_new,1,15),' '),15));
1004 l_last_name_new := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
1005 substr(p_last_name_new,1,20),' '),20));
1006 l_full_name_new := substr(pay_us_reporting_utils_pkg.Character_check(ltrim(rtrim(p_first_name_new)||' '||
1007 rtrim(p_last_name_new))),1,50);
1008
1009 l_emp_number := replace(p_employee_number,' ');
1010
1011 IF l_emp_number IS NULL THEN
1012 l_emp_name_or_number := l_full_name_new;
1013 hr_utility.trace('Employee Name or Number = '||l_emp_name_or_number);
1014 ELSE
1015 l_emp_name_or_number:= l_emp_number;
1016 hr_utility.trace('Employee Name or Number = '||l_emp_name_or_number);
1017 END IF;
1018 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 20);
1019 --
1020 -- Validation for RCW Record Start
1021 --
1022 -- Originally reported SSN validation and formatting
1023 l_ssn_old := lpad(' ',9);
1024 IF rtrim(p_ssn_old) IS not NULL then
1025 l_ssn_old :=
1026 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
1027 p_report_type,
1028 p_format,
1029 p_report_qualifier,
1030 p_record_name,
1031 'SSN',
1032 p_ssn_old,
1033 'Social Security',
1034 l_emp_name_or_number,
1035 null,
1036 p_validate,
1037 p_exclude_from_output,
1038 sp_out_1,
1039 sp_out_2);
1040 IF p_exclude_from_output = 'Y' THEN
1041 l_exclude_from_output_chk := TRUE;
1042 END IF;
1043 END IF;
1044 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 30);
1045 -- Corrected SSN validation and formatting
1046 l_ssn_new := lpad(' ',9);
1047 l_ssn_new :=
1048 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
1049 p_report_type,
1050 p_format,
1051 p_report_qualifier,
1052 p_record_name,
1053 'SSN',
1054 p_ssn_new,
1055 'Social Security',
1056 l_emp_name_or_number,
1057 null,
1058 p_validate,
1059 p_exclude_from_output,
1060 sp_out_1,
1061 sp_out_2);
1062 IF p_exclude_from_output = 'Y' THEN
1063 l_exclude_from_output_chk := TRUE;
1064 END IF;
1065 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 40);
1066
1067 parameter_record(1).p_parameter_name := 'Wages,Tips And Other Compensation';
1068 parameter_record(2).p_parameter_name := 'Federal Income Tax Withheld';
1069 parameter_record(3).p_parameter_name := 'Social Security Wages';
1070 parameter_record(4).p_parameter_name := 'Social Security Tax Withheld';
1071 parameter_record(5).p_parameter_name := 'Medicare Wages And Tips';
1072 parameter_record(6).p_parameter_name := 'Medicare Tax Withheld';
1073 parameter_record(7).p_parameter_name := 'Social Security Tips';
1074 parameter_record(8).p_parameter_name := 'Advance Earned Income Credit';
1075 parameter_record(9).p_parameter_name := 'Dependent Care Benefits';
1076 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
1077 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
1078 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
1079 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
1080 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
1081 parameter_record(15).p_parameter_name:= 'Deferred Compensation Contribution ';
1082 parameter_record(16).p_parameter_name:= 'Military Combat Pay';
1083 parameter_record(17).p_parameter_name:= 'Non-Qual. plan Sec 457';
1084 parameter_record(18).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
1085 parameter_record(19).p_parameter_name:= 'Employer cost of premiun';
1086 parameter_record(20).p_parameter_name:= 'Income from nonqualified stock option';
1087 parameter_record(21).p_parameter_name:= 'ER Contribution for HSA';
1088 parameter_record(22).p_parameter_name:= 'Nontaxable Combat Pay';
1089 parameter_record(23).p_parameter_name:= 'Nonqual 409A Deferral Amount';
1090 parameter_record(24).p_parameter_name:= 'Designated Roth Contr. to 401k Plan'; /* 5358272 */
1091 parameter_record(25).p_parameter_name:= 'Designated Roth Contr. to 403b Plan'; /* 5358272 */
1092
1093 -- Various Amount Validation for for Neg value. If value is found negative record
1094 -- is marked for exclusion
1095
1096 FOR i in 1..25 /* 5358272 */
1097 LOOP
1098 -- Negative Value validation for Originally reported wages
1099 -- if Originally reported and coreected values are identical validation and
1100 -- formating are avoided with this check
1101 --
1102 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 50);
1103 if (rcw_wage_rec(i).identical_flag = 'N') then
1104 --{
1105 if rcw_wage_rec(i).wage_old_value <> 0 then
1106 --{
1107 rcw_wage_rec(i).wage_old_value_formated :=
1108 pay_us_reporting_utils_pkg.data_validation(
1109 p_effective_date,
1110 p_report_type,
1111 p_format,
1112 p_report_qualifier,
1113 p_record_name,
1114 'NEG_CHECK',
1115 rcw_wage_rec(i).wage_old_value,
1116 parameter_record(i).p_parameter_name||'(Old)',
1117 l_emp_name_or_number, --EE number or Full name for mesg
1118 null,
1119 p_validate,
1120 p_exclude_from_output,
1121 sp_out_1,
1122 sp_out_2);
1123
1124 IF p_exclude_from_output = 'Y' THEN
1125 l_exclude_from_output_chk := TRUE;
1126 l_err := TRUE;
1127 END IF;
1128 hr_utility.trace(parameter_record(i).p_parameter_name||'Old = '||
1129 rcw_wage_rec(i).wage_old_value_formated);
1130 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1131 --}
1132 else
1133 rcw_wage_rec(i).wage_old_value_formated := lpad('0',11,'0');
1134 end if;
1135 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 60);
1136 -- Validation and formating of Corrected Value
1137 --
1138 if rcw_wage_rec(i).wage_new_value <> 0 then
1139 --{
1140 rcw_wage_rec(i).wage_new_value_formated :=
1141 pay_us_reporting_utils_pkg.data_validation(
1142 p_effective_date,
1143 p_report_type,
1144 p_format,
1145 p_report_qualifier,
1146 p_record_name,
1147 'NEG_CHECK',
1148 rcw_wage_rec(i).wage_new_value,
1149 parameter_record(i).p_parameter_name||'(New)',
1150 l_emp_name_or_number, --EE number or Full Name for mesg
1151 null,
1152 p_validate,
1153 p_exclude_from_output,
1154 sp_out_1,
1155 sp_out_2);
1156
1157 IF p_exclude_from_output = 'Y' THEN
1158 l_exclude_from_output_chk := TRUE;
1159 l_err := TRUE;
1160 END IF;
1161 hr_utility.trace(parameter_record(i).p_parameter_name||'New = '||
1162 rcw_wage_rec(i).wage_new_value_formated);
1163 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1164 --}
1165 else
1166 rcw_wage_rec(i).wage_new_value_formated := lpad('0',11,'0');
1167 end if;
1168 --}
1169 end if;
1170 END LOOP;
1171 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 70);
1172 hr_utility.trace('SS Wage and Tax limit Checking begins.' );
1173 OPEN get_ss_limit(p_effective_date);
1174 LOOP
1175 FETCH get_ss_limit INTO l_ss_wage_limit,
1176 l_ss_tax_limit;
1177 hr_utility.trace('SS Wage Limit '||to_char(l_ss_wage_limit));
1178 l_ss_count:= get_ss_limit%ROWCOUNT;
1179 EXIT WHEN get_ss_limit%NOTFOUND ;
1180 END LOOP;
1181 CLOSE get_ss_limit;
1182 hr_utility.trace('No. rows exist for limit '||to_char(l_ss_count));
1183
1184 IF l_ss_count = 0 THEN
1185 hr_utility.trace('No data found on PAY_US_FEDERAL_TAX_INFO_F '||
1186 'for Social security wage limits.');
1187 ELSIF l_ss_count >1 THEN
1188 hr_utility.trace('Too many rows on PAY_US_FEDERAL_TAX_INFO_F '||
1189 'for Social security wage limits.');
1190 ELSIF l_ss_count=1 THEN
1191 --{
1192 hr_utility.trace('SS Wage (Box-3 '||to_char(rcw_wage_rec(3).wage_new_value));
1193 hr_utility.trace('SS Tax w/h (Box-4) '||to_char(rcw_wage_rec(4).wage_new_value));
1194 hr_utility.trace('SS Tips (Box-7) '||to_char(rcw_wage_rec(7).wage_new_value));
1195
1196 IF (rcw_wage_rec(3).wage_new_value > 0 OR
1197 rcw_wage_rec(4).wage_new_value > 0 OR
1198 rcw_wage_rec(7).wage_new_value > 0 )
1199 THEN
1200 --{
1201 hr_utility.trace('SS Tax w/h, SS Tips, SS Wages are >0 ');
1202 IF (rcw_wage_rec(3).wage_new_value+
1203 rcw_wage_rec(7).wage_new_value) > l_ss_wage_limit
1204 THEN
1205 hr_utility.trace('Sum of SS_Tips and SS_Wages is > '||
1206 to_char(l_ss_wage_limit));
1207 l_field_description:='the sum of '||
1208 parameter_record(3).p_parameter_name
1209 ||' and '||
1210 parameter_record(7).p_parameter_name;
1211 l_amount:=l_ss_wage_limit/100;
1212 l_description:=' It is greater than '||to_char(l_amount);
1213 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
1214 pay_core_utils.push_token('record_name',p_record_name);
1215 pay_core_utils.push_token('name_or_number',
1216 substr(l_emp_name_or_number,1,50));
1217 pay_core_utils.push_token('field_name',l_field_description);
1218 pay_core_utils.push_token('description',
1219 substr(l_description,1,50));
1220 l_err := TRUE;
1221 END IF;
1222 IF rcw_wage_rec(4).wage_new_value > l_ss_tax_limit
1223 THEN
1224 --{
1225 hr_utility.trace('SS Tax w/h is > '||
1226 to_char(l_ss_tax_limit));
1227 l_err := TRUE;
1228 l_amount:=l_ss_tax_limit/100;
1229 l_description:=' It is greater than '||to_char(l_amount);
1230 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
1231 pay_core_utils.push_token('record_name',p_record_name);
1232 pay_core_utils.push_token('name_or_number',
1233 substr(l_emp_name_or_number,1,50));
1234 pay_core_utils.push_token('field_name',parameter_record(4).p_parameter_name);
1235 pay_core_utils.push_token('description',l_description);
1236 /* Sample message for SS Wage/Tax limit
1237 Error in RW record for Employee 1234 in Social Security Tax Withheld. It is greater than 498480 */
1238 --}
1239 END IF; --l_ss_tax_limit
1240 --}
1241 END IF; -- negative check
1242 --}
1243 END IF; --l_ss_count
1244 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 80);
1245 hr_utility.trace('After SS Wage/ Tax limit checking ');
1246 IF l_err THEN
1247 IF p_validate = 'Y' THEN
1248 p_exclude_from_output := 'Y';
1249 END IF;
1250 END IF;
1251
1252 IF p_exclude_from_output = 'Y' THEN
1253 l_exclude_from_output_chk := TRUE;
1254 ELSE
1255 l_exclude_from_output_chk := FALSE;
1256 END IF;
1257 --
1258 -- Validation for RCW record Ends here
1259 --
1260 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 90);
1261 hr_utility.trace('Formating RCW record in MMREF-2 format ');
1262 -- Formatting Wage Record (RCW) for .mf reporting file
1263 --
1264 return_value_mf := 'RCW'
1265 ||l_ssn_old
1266 ||l_ssn_new
1267 ||rpad(l_first_name_old,15)
1268 ||rpad(l_middle_name_old,15)
1269 ||rpad(l_last_name_old,20)
1270 ||rpad(l_first_name_new,15)
1271 ||rpad(l_middle_name_new,15)
1272 ||rpad(l_last_name_new,20)
1273 ||rpad(substr(nvl(p_location_address,' '),1,22),22)
1274 ||rpad(substr(nvl(p_delivery_address,' '),1,22),22)
1275 ||rpad(substr(nvl(p_city,' '),1,22),22)
1276 ||rpad(substr(nvl(p_state,' '),1,2),2)
1277 ||rpad(substr(nvl(p_zip,' '),1,5),5)
1278 ||rpad(substr(nvl(p_zip_extension,' '),1,4),9)
1279 ||rpad(substr(nvl(p_foreign_state,' '),1,23),23)
1280 ||rpad(substr(nvl(p_foreign_postal_code,' '),1,15),15)
1281 ||rpad(substr(nvl(p_country_code,' '),1,2),2)
1282 ||rcw_wage_rec(1).wage_old_value_formated
1283 ||rcw_wage_rec(1).wage_new_value_formated
1284 ||rcw_wage_rec(2).wage_old_value_formated
1285 ||rcw_wage_rec(2).wage_new_value_formated
1286 ||rcw_wage_rec(3).wage_old_value_formated
1287 ||rcw_wage_rec(3).wage_new_value_formated
1288 ||rcw_wage_rec(4).wage_old_value_formated
1289 ||rcw_wage_rec(4).wage_new_value_formated
1290 ||rcw_wage_rec(5).wage_old_value_formated
1291 ||rcw_wage_rec(5).wage_new_value_formated
1292 ||rcw_wage_rec(6).wage_old_value_formated
1293 ||rcw_wage_rec(6).wage_new_value_formated
1294 ||rcw_wage_rec(7).wage_old_value_formated
1295 ||rcw_wage_rec(7).wage_new_value_formated
1296 ||rcw_wage_rec(8).wage_old_value_formated
1297 ||rcw_wage_rec(8).wage_new_value_formated
1298 ||rcw_wage_rec(9).wage_old_value_formated
1299 ||rcw_wage_rec(9).wage_new_value_formated
1300 ||rcw_wage_rec(10).wage_old_value_formated
1301 ||rcw_wage_rec(10).wage_new_value_formated
1302 ||rcw_wage_rec(11).wage_old_value_formated
1303 ||rcw_wage_rec(11).wage_new_value_formated
1304 ||rcw_wage_rec(12).wage_old_value_formated
1305 ||rcw_wage_rec(12).wage_new_value_formated
1306 ||rcw_wage_rec(13).wage_old_value_formated
1307 ||rcw_wage_rec(13).wage_new_value_formated
1308 ||rcw_wage_rec(14).wage_old_value_formated
1309 ||rcw_wage_rec(14).wage_new_value_formated
1310 ||rcw_wage_rec(15).wage_old_value_formated
1311 ||rcw_wage_rec(15).wage_new_value_formated
1312 ||rcw_wage_rec(16).wage_old_value_formated
1313 ||rcw_wage_rec(16).wage_new_value_formated
1314 ||rcw_wage_rec(17).wage_old_value_formated
1315 ||rcw_wage_rec(17).wage_new_value_formated
1316 ||rcw_wage_rec(21).wage_old_value_formated
1317 ||rcw_wage_rec(21).wage_new_value_formated
1318 --||lpad(' ',22)
1319 ||rcw_wage_rec(18).wage_old_value_formated
1320 ||rcw_wage_rec(18).wage_new_value_formated
1321 /* commented for bug 4398606 ||lpad(' ',44) */
1322 ||rcw_wage_rec(22).wage_old_value_formated --noncombatpay
1323 ||rcw_wage_rec(22).wage_new_value_formated
1324 ||lpad(' ',22)
1325 /* done changes for bug 4398606 */
1326 ||rcw_wage_rec(19).wage_old_value_formated
1327 ||rcw_wage_rec(19).wage_new_value_formated
1328 ||rcw_wage_rec(20).wage_old_value_formated
1329 ||rcw_wage_rec(20).wage_new_value_formated
1330 /* commented for bug 4398606 ||lpad(' ',253)*/
1331 ||rcw_wage_rec(23).wage_old_value_formated
1332 ||rcw_wage_rec(23).wage_new_value_formated
1333 ||rcw_wage_rec(24).wage_old_value_formated
1334 ||rcw_wage_rec(24).wage_new_value_formated
1335 ||rcw_wage_rec(25).wage_old_value_formated
1336 ||rcw_wage_rec(25).wage_new_value_formated
1337 --||lpad(' ',231)
1338 ||lpad(' ',187)
1339 ||lpad(p_statutory_emp_indicator_old,1)
1340 ||lpad(p_statutory_emp_indicator_new,1)
1341 ||lpad(p_retire_plan_indicator_old,1)
1342 ||lpad(p_retire_plan_indicator_new,1)
1343 ||lpad(p_sickpay_indicator_old,1)
1344 ||lpad(p_sickpay_indicator_new,1)
1345 ||lpad(' ',16);
1346 pay_us_w2c_in_mmref2_format.rcw_mf_record := return_value_mf;
1347 hr_utility.trace('Formating RCW record completed ');
1348 hr_utility.trace('mf Format RCW Record '||pay_us_w2c_in_mmref2_format.rcw_mf_record);
1349 hr_utility.trace('Length of FLAT Format RCW Record '||to_char(length(pay_us_w2c_in_mmref2_format.rcw_mf_record)));
1350 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 100);
1351 --}
1352 -- Formatting CSV format of RCW record
1353 --{
1354 hr_utility.trace('Formating RCW record in CSV format');
1355
1356 if l_exclude_from_output_chk then
1357 -- {
1358 -- This will be used for a02 record because this gets the actual values
1359 -- from balance calls. In a02 we will show all the values irrespective
1360 -- if it is changed or not.
1361
1362 return_value_csv :=
1363 'RCW'
1364 ||','||l_ssn_old
1365 ||','||l_ssn_new
1366 ||','||rpad(l_first_name_old,15)
1367 ||','||rpad(l_middle_name_old,15)
1368 ||','||rpad(l_last_name_old,20)
1369 ||','||rpad(l_first_name_new,15)
1370 ||','||rpad(l_middle_name_new,15)
1371 ||','||rpad(l_last_name_new,20)
1372 ||','||rpad(substr(nvl(p_location_address,' '),1,22),22)
1373 ||','||rpad(substr(nvl(p_delivery_address,' '),1,22),22)
1374 ||','||rpad(substr(nvl(p_city,' '),1,22),22)
1375 ||','||rpad(substr(nvl(p_state,' '),1,2),2)
1376 ||','||rpad(substr(nvl(p_zip,' '),1,5),5)
1377 ||','||rpad(substr(nvl(p_zip_extension,' '),1,4),9)
1378 ||','||' '
1379 ||','||rpad(substr(nvl(p_foreign_state,' '),1,23),23)
1380 ||','||rpad(substr(nvl(p_foreign_postal_code,' '),1,15),15)
1381 ||','||rpad(substr(nvl(p_country_code,' '),1,2),2)
1382 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information1*100)
1383 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information1*100)
1384 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information2*100)
1385 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information2*100)
1386 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information3*100)
1387 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information3*100)
1388 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information4*100)
1389 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information4*100)
1390 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information5*100)
1391 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information5*100)
1392 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information6*100)
1393 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information6*100)
1394 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information7*100)
1395 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information7*100)
1396 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information8*100)
1397 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information8*100)
1398 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information9*100)
1399 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information9*100)
1400 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information10*100)
1401 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information10*100)
1402 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information11*100)
1403 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information11*100)
1404 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information12*100)
1405 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information12*100)
1406 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information13*100)
1407 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information13*100)
1408 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information14*100)
1409 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information14*100)
1410 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information15*100)
1411 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information15*100)
1412 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information16*100)
1413 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information16*100)
1414 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information17*100)
1415 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information17*100)
1416 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information21*100)
1417 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information21*100)
1418 --||','||lpad(' ',22)
1419 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information18*100)
1420 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information18*100)
1421 ||','||lpad(' ',44)
1422 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information19*100)
1423 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information19*100)
1424 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information20*100)
1425 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information20*100)
1426 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information23*100)
1427 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information23*100)
1428 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information24*100)
1429 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information24*100)
1430 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information25*100)
1431 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information25*100)
1432 ||','||lpad(' ',187)
1433 ||','||lpad(p_statutory_emp_indicator_old,1)
1434 ||','||lpad(p_statutory_emp_indicator_new,1)
1435 ||','||lpad(p_retire_plan_indicator_old,1)
1436 ||','||lpad(p_retire_plan_indicator_new,1)
1437 ||','||lpad(p_sickpay_indicator_old,1)
1438 ||','||lpad(p_sickpay_indicator_new,1)
1439 ||','||lpad(' ',16);
1440
1441 else
1442
1443 return_value_csv := 'RCW'
1444 ||','||l_ssn_old
1445 ||','||l_ssn_new
1446 ||','||rpad(l_first_name_old,15)
1447 ||','||rpad(l_middle_name_old,15)
1448 ||','||rpad(l_last_name_old,20)
1449 ||','||rpad(l_first_name_new,15)
1450 ||','||rpad(l_middle_name_new,15)
1451 ||','||rpad(l_last_name_new,20)
1452 ||','||rpad(substr(nvl(p_location_address,' '),1,22),22)
1453 ||','||rpad(substr(nvl(p_delivery_address,' '),1,22),22)
1454 ||','||rpad(substr(nvl(p_city,' '),1,22),22)
1455 ||','||rpad(substr(nvl(p_state,' '),1,2),2)
1456 ||','||rpad(substr(nvl(p_zip,' '),1,5),5)
1457 ||','||rpad(substr(nvl(p_zip_extension,' '),1,4),9)
1458 ||','||' '
1459 ||','||rpad(substr(nvl(p_foreign_state,' '),1,23),23)
1460 ||','||rpad(substr(nvl(p_foreign_postal_code,' '),1,15),15)
1461 ||','||rpad(substr(nvl(p_country_code,' '),1,2),2)
1462 ||','||rcw_wage_rec(1).wage_old_value_formated
1463 ||','||rcw_wage_rec(1).wage_new_value_formated
1464 ||','||rcw_wage_rec(2).wage_old_value_formated
1465 ||','||rcw_wage_rec(2).wage_new_value_formated
1466 ||','||rcw_wage_rec(3).wage_old_value_formated
1467 ||','||rcw_wage_rec(3).wage_new_value_formated
1468 ||','||rcw_wage_rec(4).wage_old_value_formated
1469 ||','||rcw_wage_rec(4).wage_new_value_formated
1470 ||','||rcw_wage_rec(5).wage_old_value_formated
1471 ||','||rcw_wage_rec(5).wage_new_value_formated
1472 ||','||rcw_wage_rec(6).wage_old_value_formated
1473 ||','||rcw_wage_rec(6).wage_new_value_formated
1474 ||','||rcw_wage_rec(7).wage_old_value_formated
1475 ||','||rcw_wage_rec(7).wage_new_value_formated
1476 ||','||rcw_wage_rec(8).wage_old_value_formated
1477 ||','||rcw_wage_rec(8).wage_new_value_formated
1478 ||','||rcw_wage_rec(9).wage_old_value_formated
1479 ||','||rcw_wage_rec(9).wage_new_value_formated
1480 ||','||rcw_wage_rec(10).wage_old_value_formated
1481 ||','||rcw_wage_rec(10).wage_new_value_formated
1482 ||','||rcw_wage_rec(11).wage_old_value_formated
1483 ||','||rcw_wage_rec(11).wage_new_value_formated
1484 ||','||rcw_wage_rec(12).wage_old_value_formated
1485 ||','||rcw_wage_rec(12).wage_new_value_formated
1486 ||','||rcw_wage_rec(13).wage_old_value_formated
1487 ||','||rcw_wage_rec(13).wage_new_value_formated
1488 ||','||rcw_wage_rec(14).wage_old_value_formated
1489 ||','||rcw_wage_rec(14).wage_new_value_formated
1490 ||','||rcw_wage_rec(15).wage_old_value_formated
1491 ||','||rcw_wage_rec(15).wage_new_value_formated
1492 ||','||rcw_wage_rec(16).wage_old_value_formated
1493 ||','||rcw_wage_rec(16).wage_new_value_formated
1494 ||','||rcw_wage_rec(17).wage_old_value_formated
1495 ||','||rcw_wage_rec(17).wage_new_value_formated
1496 ||','||rcw_wage_rec(21).wage_old_value_formated
1497 ||','||rcw_wage_rec(21).wage_new_value_formated
1498 ||','||rcw_wage_rec(18).wage_old_value_formated
1499 ||','||rcw_wage_rec(18).wage_new_value_formated
1500 ||','||lpad(' ',44)
1501 ||','||rcw_wage_rec(19).wage_old_value_formated /* 5358272 */
1502 ||','||rcw_wage_rec(19).wage_new_value_formated
1503 ||','||rcw_wage_rec(20).wage_old_value_formated
1504 ||','||rcw_wage_rec(20).wage_new_value_formated
1505 ||','||rcw_wage_rec(23).wage_old_value_formated
1506 ||','||rcw_wage_rec(23).wage_new_value_formated
1507 ||','||rcw_wage_rec(24).wage_old_value_formated
1508 ||','||rcw_wage_rec(24).wage_new_value_formated
1509 ||','||rcw_wage_rec(25).wage_old_value_formated
1510 ||','||rcw_wage_rec(25).wage_new_value_formated
1511 ||','||lpad(' ',187)
1512 ||','||lpad(p_statutory_emp_indicator_old,1)
1513 ||','||lpad(p_statutory_emp_indicator_new,1)
1514 ||','||lpad(p_retire_plan_indicator_old,1)
1515 ||','||lpad(p_retire_plan_indicator_new,1)
1516 ||','||lpad(p_sickpay_indicator_old,1)
1517 ||','||lpad(p_sickpay_indicator_new,1)
1518 ||','||lpad(' ',16);
1519
1520 end if; /* l_exclude_from_output_chk */
1521
1522 pay_us_w2c_in_mmref2_format.rcw_csv_record := return_value_csv;
1523 hr_utility.trace('CSV Format RCW Record '||pay_us_w2c_in_mmref2_format.rcw_csv_record);
1524 hr_utility.trace('Length of CSV Format RCW Record '||to_char(length(pay_us_w2c_in_mmref2_format.rcw_csv_record)));
1525 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 110);
1526 --}
1527 -- Format Blank RCW record in MMREF-2 format
1528 -- This Blank record would be used when RCO record is moved to .a02 for error
1529 --{
1530
1531 hr_utility.trace('Formatting BLANK RCW Record ');
1532 return_value_blank :=
1533 ' '
1534 ||','||' '
1535 ||','||' '
1536 ||','||' '
1537 ||','||' '
1538 ||','||' '
1539 ||','||' '
1540 ||','||' '
1541 ||','||' '
1542 ||','||' '
1543 ||','||' '
1544 ||','||' '
1545 ||','||' '
1546 ||','||' '
1547 ||','||' '
1548 ||','||' '
1549 ||','||' '
1550 ||','||' '
1551 ||','||' '
1552 ||','||' '
1553 ||','||' '
1554 ||','||' '
1555 ||','||' '
1556 ||','||' '
1557 ||','||' '
1558 ||','||' '
1559 ||','||' '
1560 ||','||' '
1561 ||','||' '
1562 ||','||' '
1563 ||','||' '
1564 ||','||' '
1565 ||','||' '
1566 ||','||' '
1567 ||','||' '
1568 ||','||' '
1569 ||','||' '
1570 ||','||' '
1571 ||','||' '
1572 ||','||' '
1573 ||','||' '
1574 ||','||' '
1575 ||','||' '
1576 ||','||' '
1577 ||','||' '
1578 ||','||' '
1579 ||','||' '
1580 ||','||' '
1581 ||','||' '
1582 ||','||' '
1583 ||','||' '
1584 ||','||' '
1585 ||','||' '
1586 ||','||' '
1587 ||','||' '
1588 ||','||' '
1589 ||','||' '
1590 ||','||' '
1591 ||','||' '
1592 ||','||' '
1593 ||','||' '
1594 ||','||' '
1595 ||','||' '
1596 ||','||' '
1597 ||','||' '
1598 ||','||' '
1599 ||','||' '
1600 ||','||' '
1601 ||','||' '
1602 ||','||' ';
1603 pay_us_w2c_in_mmref2_format.rcw_blank_csv_record := return_value_blank;
1604 hr_utility.trace('CSV Blank Format RCW Record '||pay_us_w2c_in_mmref2_format.rcw_blank_csv_record);
1605 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 120);
1606 --}
1607
1608 hr_utility.trace('p_exclude_from_output ->'||p_exclude_from_output);
1609 p_error := l_exclude_from_output_chk;
1610 ret_str_len:=length(return_value_mf);
1611 return return_value_mf;
1612 END format_W2C_RCW_record;
1613 -- End of Formatting RCW in MMREF-2 format
1614
1615 -- This function is used for formatting RCO Record in MMREF-2 format
1616 --
1617 FUNCTION format_W2C_RCO_record ( p_effective_date IN varchar2,
1618 p_report_type IN varchar2,
1619 p_format IN varchar2,
1620 p_report_qualifier IN varchar2,
1621 p_record_name IN varchar2,
1622 p_tax_unit_id IN varchar2,
1623 p_record_identifier IN varchar2,
1624 p_ssn_new IN varchar2,
1625 p_first_name_new IN varchar2,
1626 p_middle_name_new IN varchar2,
1627 p_last_name_new IN varchar2,
1628 p_orig_assignment_actid IN varchar2,
1629 p_correct_assignment_actid IN varchar2,
1630 p_employee_number IN varchar2,
1631 rco_wage_rec IN OUT nocopy pay_us_w2c_in_mmref2_format.table_wage_record,
1632 p_format_type IN varchar2,
1633 p_validate IN varchar2,
1634 p_exclude_from_output OUT nocopy varchar2,
1635 ret_str_len OUT nocopy varchar2,
1636 p_error OUT nocopy boolean
1637 )
1638 return varchar2
1639 IS
1640 l_emp_name_or_number varchar2(50);
1641 l_emp_number varchar2(50);
1642 l_full_name_new varchar2(100);
1643 l_message varchar2(2000);
1644 l_description varchar2(50);
1645 l_field_description varchar2(50);
1646 l_ss_count number(10);
1647 l_amount number(10);
1648 return_value_mf varchar2(32767);
1649 return_value_csv varchar2(32767);
1650 return_value_blank varchar2(32767);
1651 l_err boolean;
1652 l_exclude_from_output_chk boolean;
1653 sp_out_1 varchar2(100);
1654 sp_out_2 varchar2(100);
1655
1656 TYPE function_columns IS RECORD(
1657 p_parameter_name varchar2(100)
1658 );
1659 function_parameter_rec function_columns;
1660 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
1661 INDEX BY BINARY_INTEGER;
1662 parameter_record input_parameter_record;
1663
1664 BEGIN
1665 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 10);
1666 --{
1667
1668 l_full_name_new := substr(pay_us_reporting_utils_pkg.Character_check(ltrim(rtrim(p_first_name_new)||' '||
1669 rtrim(p_last_name_new))),1,50);
1670
1671 l_emp_number := replace(p_employee_number,' ');
1672
1673 IF l_emp_number IS NULL THEN
1674 l_emp_name_or_number := l_full_name_new;
1675 hr_utility.trace('Employee Name or Number = '||l_emp_name_or_number);
1676 ELSE
1677 l_emp_name_or_number:= l_emp_number;
1678 hr_utility.trace('Employee Name or Number = '||l_emp_name_or_number);
1679 END IF;
1680 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 20);
1681 --
1682 -- Validation for RCO Record Start
1683 --
1684 parameter_record(1).p_parameter_name:= ' allocated tips';
1685 parameter_record(2).p_parameter_name:= ' uncollected employee tax on tips';
1686 parameter_record(3).p_parameter_name:= ' Medical Savings Account';
1687 parameter_record(4).p_parameter_name:= ' Simple Retirement Account';
1688 parameter_record(5).p_parameter_name:= ' Qualified adoption expenses';
1689 parameter_record(6).p_parameter_name:= 'uncollected social security or RRTA tax on GTL insurance over $50000';
1690 parameter_record(7).p_parameter_name:= 'uncollected medicare tax on GTL insurance over $50,000';
1691 parameter_record(8).p_parameter_name:= 'income under 409A';
1692 -- Various Amount Validation for for Neg value. If value is found negative record
1693 -- is marked for exclusion
1694 FOR i in 1..8
1695 LOOP
1696 -- Negative Value validation for Originally reported wages
1697 -- if Originally reported and coreected values are identical validation and
1698 -- formating are avoided with this check
1699 --
1700 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 30);
1701 if (rco_wage_rec(i).identical_flag = 'N') then
1702 --{
1703 if rco_wage_rec(i).wage_old_value <> 0 then
1704 --{
1705 rco_wage_rec(i).wage_old_value_formated :=
1706 pay_us_reporting_utils_pkg.data_validation(
1707 p_effective_date,
1708 p_report_type,
1709 p_format,
1710 p_report_qualifier,
1711 p_record_name,
1712 'NEG_CHECK',
1713 rco_wage_rec(i).wage_old_value,
1714 parameter_record(i).p_parameter_name||'(Old)',
1715 l_emp_name_or_number, --EE number or Full name for mesg
1716 null,
1717 p_validate,
1718 p_exclude_from_output,
1719 sp_out_1,
1720 sp_out_2);
1721
1722 IF p_exclude_from_output = 'Y' THEN
1723 l_exclude_from_output_chk := TRUE;
1724 l_err := TRUE;
1725 END IF;
1726 hr_utility.trace(parameter_record(i).p_parameter_name||'Old = '||
1727 rco_wage_rec(i).wage_old_value_formated);
1728 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1729 --}
1730 else
1731 rco_wage_rec(i).wage_old_value_formated := lpad('0',11,'0');
1732 end if;
1733 hr_utility.set_location(gv_package || '.format_W2C_RCW_record', 40);
1734 -- Validation and formating of Corrected Value
1735 --
1736 if rco_wage_rec(i).wage_new_value <> 0 then
1737 --{
1738 rco_wage_rec(i).wage_new_value_formated :=
1739 pay_us_reporting_utils_pkg.data_validation(
1740 p_effective_date,
1741 p_report_type,
1742 p_format,
1743 p_report_qualifier,
1744 p_record_name,
1745 'NEG_CHECK',
1746 rco_wage_rec(i).wage_new_value,
1747 parameter_record(i).p_parameter_name||'(New)',
1748 l_emp_name_or_number, --EE number or Full Name for mesg
1749 null,
1750 p_validate,
1751 p_exclude_from_output,
1752 sp_out_1,
1753 sp_out_2);
1754
1755 IF p_exclude_from_output = 'Y' THEN
1756 l_exclude_from_output_chk := TRUE;
1757 l_err := TRUE;
1758 END IF;
1759 hr_utility.trace(parameter_record(i).p_parameter_name||'New = '||
1760 rco_wage_rec(i).wage_new_value_formated);
1761 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1762 --}
1763 else
1764 rco_wage_rec(i).wage_new_value_formated := lpad('0',11,'0');
1765 end if;
1766 --}
1767 end if;
1768 END LOOP;
1769 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 50);
1770 if l_err then
1771 p_exclude_from_output := 'Y';
1772 end if;
1773
1774 IF p_exclude_from_output = 'Y' THEN
1775 l_exclude_from_output_chk := TRUE;
1776 ELSE
1777 l_exclude_from_output_chk := FALSE;
1778 END IF;
1779 --
1780 -- Validation for RCO record Ends here
1781 --
1782 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 60);
1783 hr_utility.trace('Formating RCO record in MMREF-2 format ');
1784 -- Formatting Wage Record (RCO) for .mf reporting file
1785 --
1786 return_value_mf := 'RCO'
1787 ||lpad(' ',9)
1788 ||rco_wage_rec(1).wage_old_value_formated
1789 ||rco_wage_rec(1).wage_new_value_formated
1790 ||rco_wage_rec(2).wage_old_value_formated
1791 ||rco_wage_rec(2).wage_new_value_formated
1792 ||rco_wage_rec(3).wage_old_value_formated
1793 ||rco_wage_rec(3).wage_new_value_formated
1794 ||rco_wage_rec(4).wage_old_value_formated
1795 ||rco_wage_rec(4).wage_new_value_formated
1796 ||rco_wage_rec(5).wage_old_value_formated
1797 ||rco_wage_rec(5).wage_new_value_formated
1798 ||rco_wage_rec(6).wage_old_value_formated
1799 ||rco_wage_rec(6).wage_new_value_formated
1800 ||rco_wage_rec(7).wage_old_value_formated
1801 ||rco_wage_rec(7).wage_new_value_formated
1802 ||rco_wage_rec(8).wage_old_value_formated
1803 ||rco_wage_rec(8).wage_new_value_formated
1804 ||lpad(' ',836);
1805 pay_us_w2c_in_mmref2_format.rco_mf_record := return_value_mf;
1806 hr_utility.trace('Formating RCO record completed ');
1807 hr_utility.trace('mf Format RCO Record '||pay_us_w2c_in_mmref2_format.rco_mf_record);
1808 hr_utility.trace('Length of FLAT Format RCO Record '||to_char(length(pay_us_w2c_in_mmref2_format.rco_mf_record)));
1809 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 70);
1810 --}
1811 -- Formatting CSV format of RCO record
1812 --{
1813 hr_utility.trace('Formating RCO record in CSV format');
1814
1815 if l_exclude_from_output_chk then
1816 -- {
1817 -- This will be used for a02 record because this gets the actual values
1818 -- from balance calls. In a02 we will show all the values irrespective
1819 -- if it is changed or not.
1820
1821 return_value_csv := ',' ||
1822 'RCO'
1823 ||','||lpad(' ',9)
1824 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information1*100)
1825 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information1*100)
1826 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information2*100)
1827 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information2*100)
1828 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information3*100)
1829 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information3*100)
1830 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information4*100)
1831 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information4*100)
1832 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information5*100)
1833 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information5*100)
1834 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information6*100)
1835 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information6*100)
1836 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information7*100)
1837 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information7*100)
1838 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information8*100)
1839 ||','||to_char(pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information8*100)
1840 ||lpad(' ',836);
1841 --||lpad(' ',858);
1842 /* Bug 4097321 - Assign return_value_csv to global variable*/
1843 pay_us_w2c_in_mmref2_format.rco_csv_record := return_value_csv;
1844 --}
1845 else
1846
1847 return_value_mf := 'RCO'
1848 ||','||lpad(' ',9)
1849 ||','||rco_wage_rec(1).wage_old_value_formated
1850 ||','||rco_wage_rec(1).wage_new_value_formated
1851 ||','||rco_wage_rec(2).wage_old_value_formated
1852 ||','||rco_wage_rec(2).wage_new_value_formated
1853 ||','||rco_wage_rec(3).wage_old_value_formated
1854 ||','||rco_wage_rec(3).wage_new_value_formated
1855 ||','||rco_wage_rec(4).wage_old_value_formated
1856 ||','||rco_wage_rec(4).wage_new_value_formated
1857 ||','||rco_wage_rec(5).wage_old_value_formated
1858 ||','||rco_wage_rec(5).wage_new_value_formated
1859 ||','||rco_wage_rec(6).wage_old_value_formated
1860 ||','||rco_wage_rec(6).wage_new_value_formated
1861 ||','||rco_wage_rec(7).wage_old_value_formated
1862 ||','||rco_wage_rec(7).wage_new_value_formated
1863 ||','||rco_wage_rec(8).wage_old_value_formated
1864 ||','||rco_wage_rec(8).wage_new_value_formated
1865 ||lpad(' ',836);
1866
1867 --||','||lpad(' ',858);
1868 /* Bug 4097321 - Assign return_value_mf to global variable
1869 This is needed to print RCO in a03 if it is correct */
1870 pay_us_w2c_in_mmref2_format.rco_csv_record := ',' || return_value_mf;
1871
1872 end if;
1873
1874 /* Bug 4097321 - Commented following code. The Assignment is done inside the IF..ELSE above
1875 pay_us_w2c_in_mmref2_format.rco_csv_record := return_value_csv; */
1876 hr_utility.trace('CSV Format RCO Record '||pay_us_w2c_in_mmref2_format.rco_csv_record);
1877 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 80);
1878 --}
1879 -- Format Blank RCO record in MMREF-2 format
1880 -- This Blank record would be used when RCO record is moved to .a02 for error
1881 --{
1882
1883 hr_utility.trace('Formatting BLANK RCO Record ');
1884 return_value_blank := ','
1885 ||' '
1886 ||','||lpad(' ',9)
1887 ||','||' '
1888 ||','||' '
1889 ||','||' '
1890 ||','||' '
1891 ||','||' '
1892 ||','||' '
1893 ||','||' '
1894 ||','||' '
1895 ||','||' '
1896 ||','||' '
1897 ||','||' '
1898 ||','||' '
1899 ||','||' '
1900 ||','||' '
1901 ||','||' ';
1902 pay_us_w2c_in_mmref2_format.rco_blank_csv_record := return_value_blank;
1903 hr_utility.trace('CSV Blank Format RCO Record '||pay_us_w2c_in_mmref2_format.rcw_blank_csv_record);
1904 hr_utility.set_location(gv_package || '.format_W2C_RCO_record', 90);
1905 --}
1906
1907 hr_utility.trace('p_exclude_from_output ->'||p_exclude_from_output);
1908 p_error := l_exclude_from_output_chk;
1909 ret_str_len:=length(return_value_mf);
1910 hr_utility.trace('format W2C RCO return_value_mf ->'||return_value_mf);
1911 return return_value_mf;
1912 END format_W2C_RCO_record;
1913 -- End of Formatting RCW in MMREF-2 format
1914
1915 --BEGIN
1916 --hr_utility.trace_on(null,'W2CFMTREC');
1917 END pay_us_mmrf2_w2c_format_record; -- End of Package Body