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