[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2C_IN_MMREF2_FORMAT
Source
1 PACKAGE BODY pay_us_w2c_in_mmref2_format AS
2 /* $Header: payusw2cinmmref2.pkb 120.6.12020000.4 2013/04/10 15:11:03 skchalla ship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 pay_us_w2c_in_mmref2_format
11
12 File Name:
13 payusw2cinmmref2.pkb
14
15 Purpose
16 The purpose of this package is to support the generation of magnetic tape W-2c
17 reports in MMREF-2 format for US legilsative requirements.
18
19 Notes
20
21 Parameters: The following parameters are used in all the functions.
22 p_effective_date -
23 This parameter indicates the year for the function.
24 p_report_type -
25 This parameter will have the type of the report.
26 eg: 'W2C'
27 p_format -
28 This parameter will have the format to be printed
29 on W2C. eg:'MMREF2'
30 p_report_qualifier -
31 This will support currently only FED as W2c doesn't
32 support State
33 p_record_name -
34 This parameter will have the particular
35 record name. eg: RCA,RCF,RCE,RCT etc.
36 p_validate -
37 This parameter will check whether it wants to
38 validate the error condition or override the checking.
39 'N'- Override
40 'Y'- Check
41 p_exclude_from_output -
42 This out parameter gives the information on
43 whether the record has to be printed or not.
44 'Y'- Do not print.
45 'N'- Print.
46
47 Change List
48 -----------
49 Date Name Vers Bug No Description
50 ----------- -------- ------ ------- --------------------------
51 24-OCT-2003 ppanda 115.0 created
52 10-DEC-2003 ppanda RCW and RCO record formatting changed
53 11-DEC-2003 ppanda 115. 3313413 RCU reocod formatting changed for all
54 amounts
55 18-OCT-2004 meshah 115.6 3769733 RCW has been changed to use
56 A_W2_TP_SICK_PAY_PER_GRE_YTD(wages)
57 instead of
58 A_FIT_3RD_PARTY_PER_GRE_YTD(withheld).
59 26-OCT-2004 meshah 115.6 3650105 added parameter_record(21) in
60 format_w2c_total_record for ER Health
61 Savings account. using p_output_43 and
62 p_output_44 to store old and new values.
63 GET_ARCHIVED_VALUES has also been changed
64 to fetch the archived values for ER HSA.
65 09-NOV-2004 meshah 115.7 3996391 changed format_w2c_total_record function.
66 changed the p_parameter_name for 15 and
67 16.
68 16-NOV-2004 meshah 115.8 now checking for l_rco_neg_flag and
69 l_rcw_neg_flag before adding the values
70 for the total record.
71 30-DEC-2004 rsethupa now checking for l_rcw_neg_flag also
72 before incrementing the count for valid
73 RCU records.
74 03-Jan-2005 sodhingr 115.9 4398606 RCW changed to report combatpay and 409
75 deferrals.
76 RCO changed to report 409 income and
77 also changed RCU and RCT record to report
78 totals.
79 RCA changed to restrict the trns_pin to
80 8 chars.
81 10-Jan-2007 sausingh 115.11 5358272 Changed GET_ARCHIVED_VALUES,
82 sudedas pay_us_w2c_RCW_record,format_w2c_total_record,
83 print_w2c_record_header for Roth 401k/403b
84 12-Nov-2010 skchalla 115.12 9783383 Changed GET_ARCHIVED_VALUES,
85 pay_us_w2c_RCW_record,format_w2c_total_record,
86 print_w2c_record_header for Hire exempt wages and tips.
87 02-Nov-2011 skchalla 115.13 13073358 Modified the function format_w2c_record for .a02 file.
88 23-Nov-2011 emunisek 115.14 13326069 Made changes to function get_archived_balances to
89 calculate FIT Wages using the Direct Balances approach.
90 27-Dec-2011 skchalla 115.15 13255427 Modified to report two new balances, W2 Health Coverage
91 and W2 Roth 457b
92 25-Jan-2012 emunisek 115.16 13614766 Added change to check the Data Archived before using
93 Direct Balance approach
94 10-Apr-2013 skchalla 115.17 16545887 Modified for the box 13 Retirement Plan.
95 10-Apr-2013 skchalla 115.18 16545887 Removed the W2 Roth 457b check while deriving the box 13 Retirement Plan,
96 as it should not be checked.
97 ============================================================================*/
98 -- Global Variable
99
100 g_number NUMBER;
101 l_return varchar2(100);
102 end_date date := to_date('31/12/4712','DD/MM/YYYY');
103
104 /******************************************************************
105 ** Package Local Variables
106 ******************************************************************/
107 gv_package varchar2(50) := 'pay_us_w2c_in_mmref2_format';
108
109 /* -------------------------------------------------------------
110 Function Name : print_record_header
111 Purpose : Function will return the String for header
112 or title line for the Table or table heading
113 related to record for printing in audit files
114
115 Error checking
116
117 Special Note :
118
119 -------------------------------------------------------------- */
120
121 FUNCTION print_w2c_record_header(
122 p_effective_date IN varchar2,
123 p_report_type IN varchar2,
124 p_format IN varchar2,
125 p_report_qualifier IN varchar2,
126 p_record_name IN varchar2,
127 p_input_1 IN varchar2,
128 p_input_2 IN varchar2,
129 p_input_3 IN varchar2,
130 p_input_4 IN varchar2,
131 p_input_5 IN varchar2,
132 p_validate IN varchar2,
133 p_exclude_from_output OUT nocopy varchar2,
134 sp_out_1 OUT nocopy varchar2,
135 sp_out_2 OUT nocopy varchar2,
136 sp_out_3 OUT nocopy varchar2,
137 sp_out_4 OUT nocopy varchar2,
138 sp_out_5 OUT nocopy varchar2,
139 sp_out_6 OUT nocopy varchar2,
140 sp_out_7 OUT nocopy varchar2,
141 sp_out_8 OUT nocopy varchar2,
142 sp_out_9 OUT nocopy varchar2,
143 sp_out_10 OUT nocopy varchar2
144 ) RETURN VARCHAR2
145 IS
146
147 header_string varchar2(3000); --13255427
148 return_header_string varchar2(3000); --13255427
149
150 l_header_2 varchar2(900);
151 l_header_3 varchar2(900);
152 l_header_4 varchar2(900);
153 l_header_5 varchar2(900);
154 l_header_8 varchar2(900);
155 l_header_9 varchar2(900);
156
157 l_header_20 varchar2(900);
158 l_header_21 varchar2(900);
159 l_report_format varchar2(15);
160 l_header_29 varchar2(900);
161 l_header_34 varchar2(900);
162 l_name_header varchar2(900);
163 l_records varchar2(900);
164
165
166 BEGIN
167 --hr_utility.trace_on (null, 'san2');
168 l_report_format := p_input_1;
169 hr_utility.trace('Begin Function'|| gv_package ||'.print_w2c_record_header ');
170 --hr_utility.trace_oFF();
171 IF p_format = 'MMREF2' THEN
172
173 IF p_record_name = 'RCW' THEN
174 hr_utility.trace('Formating RCW Record in'|| gv_package ||'.print_w2c_record_header ');
175 header_string :=
176 pay_us_mmrf_print_rec_header.mmrf2_format_rcw_record_header(
177 p_report_type,
178 p_format,
179 p_report_qualifier,
180 p_record_name
181 );
182 ELSIF p_record_name = 'RCO' THEN
183 hr_utility.trace('Formating RCO Record in'|| gv_package ||'.print_w2c_record_header ');
184 header_string:=
185 pay_us_mmrf_print_rec_header.mmrf2_format_rco_record_header(
186 p_report_type,
187 p_format,
188 p_report_qualifier,
189 p_record_name
190 );
191 END IF; /* p_record_name */
192 END IF; /* p_format */
193 hr_utility.trace('splitting the header string ');
194 return_header_string := substr(header_string,1,200);
195 sp_out_1:=substr(header_string,201,250);
196 sp_out_2:=substr(header_string,451,250);
197 sp_out_3:=substr(header_string,701,250);
198 sp_out_4:=substr(header_string,951,250);
199 sp_out_5:=substr(header_string,1201,250);
200 sp_out_6:=substr(header_string,1451,250);
201 sp_out_7:=substr(header_string,1701,250);
202 sp_out_8:=substr(header_string,1951,250);
203 sp_out_9:=substr(header_string,2201,250);
204 sp_out_10:=substr(header_string,2451);
205
206 p_exclude_from_output:='N';
207 hr_utility.trace('Length of return_header_string := ' || length(return_header_string)) ;
208 hr_utility.trace('return_header_string = '||return_header_string);
209 hr_utility.trace('sp_out_1:='||sp_out_1);
210 hr_utility.trace('sp_out_2:='||sp_out_2);
211 hr_utility.trace('sp_out_3:='||sp_out_3);
212 hr_utility.trace('sp_out_4:='||sp_out_4);
213 hr_utility.trace('sp_out_5:='||sp_out_5);
214 hr_utility.trace('sp_out_6:='||sp_out_6);
215 hr_utility.trace('sp_out_7:='||sp_out_7);
216 hr_utility.trace('sp_out_8:='||sp_out_8);
217 hr_utility.trace('sp_out_9:='||sp_out_9);
218 hr_utility.trace('sp_out_10:='||sp_out_10);
219 hr_utility.trace('Recod Header Formating completed in'|| gv_package
220 ||'.print_w2c_record_header ');
221 RETURN return_header_string;
222 END print_w2c_record_header;
223
224 /* ---------------------------------------------------------------
225 Function Name : format_w2c_record
226 Purpose : This is a geralised function which can be used
227 in W-2c MAg Formula to format Variaous records
228 Error checking
229
230 Special Note :
231
232 parameters :
233
234 -------------------------------------------------------------------- */
235
236 FUNCTION format_w2c_record(
237 p_effective_date IN varchar2,
238 p_report_type IN varchar2,
239 p_format IN varchar2,
240 p_report_qualifier IN varchar2,
241 p_record_name IN varchar2,
242 p_input_1 IN varchar2,
243 p_input_2 IN varchar2,
244 p_input_3 IN varchar2,
245 p_input_4 IN varchar2,
246 p_input_5 IN varchar2,
247 p_input_6 IN varchar2,
248 p_input_7 IN varchar2,
249 p_input_8 IN varchar2,
250 p_input_9 IN varchar2,
251 p_input_10 IN varchar2,
252 p_input_11 IN varchar2,
253 p_input_12 IN varchar2,
254 p_input_13 IN varchar2,
255 p_input_14 IN varchar2,
256 p_input_15 IN varchar2,
257 p_input_16 IN varchar2,
258 p_input_17 IN varchar2,
259 p_input_18 IN varchar2,
260 p_input_19 IN varchar2,
261 p_input_20 IN varchar2,
262 p_input_21 IN varchar2,
263 p_input_22 IN varchar2,
264 p_input_23 IN varchar2,
265 p_input_24 IN varchar2,
266 p_input_25 IN varchar2,
267 p_input_26 IN varchar2,
268 p_input_27 IN varchar2,
269 p_input_28 IN varchar2,
270 p_input_29 IN varchar2,
271 p_input_30 IN varchar2,
272 p_input_31 IN varchar2,
273 p_input_32 IN varchar2,
274 p_input_33 IN varchar2,
275 p_input_34 IN varchar2,
276 p_input_35 IN varchar2,
277 p_input_36 IN varchar2,
278 p_input_37 IN varchar2,
279 p_input_38 IN varchar2,
280 p_input_39 IN varchar2,
281 p_input_40 IN varchar2,
282 p_validate IN varchar2,
283 p_exclude_from_output OUT nocopy varchar2,
284 sp_out_1 OUT nocopy varchar2,
285 sp_out_2 OUT nocopy varchar2,
286 sp_out_3 OUT nocopy varchar2,
287 sp_out_4 OUT nocopy varchar2,
288 sp_out_5 OUT nocopy varchar2,
289 ret_str_len OUT nocopy number
290 ) RETURN VARCHAR2
291 IS
292
293 return_value varchar2(32767);
294 l_exclude_from_output_chk boolean;
295 main_return_string varchar2(300);
296 l_total_rcw_records number := 0;
297 ln_return_value number := 0;
298 BEGIN
299 --hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');
300 --hr_utility.trace_on;
301
302 --hr_utility.trace_on (null, 'DEBUG');
303
304 hr_utility.trace(' p_report_qualifier = '||p_report_qualifier);
305 hr_utility.trace(' p_record_name = ' ||p_record_name);
306 hr_utility.trace(' p_input_2 = ' ||p_input_2);
307 hr_utility.trace(' p_input_2 = ' ||p_input_2);
308 hr_utility.trace(' p_input_3 = ' ||p_input_3);
309 hr_utility.trace(' p_input_4 = ' ||p_input_4);
310 hr_utility.trace(' p_input_5 = ' ||p_input_5);
311 hr_utility.trace(' p_input_6 = ' ||p_input_6);
312 hr_utility.trace(' p_input_7 = ' ||p_input_7);
313 hr_utility.trace(' p_input_8 = ' ||p_input_8);
314 hr_utility.trace(' p_input_9 = ' ||p_input_9);
315 hr_utility.trace(' p_input_10 = ' ||p_input_10);
316 hr_utility.trace(' p_input_11 = ' ||p_input_11);
317 hr_utility.trace(' p_input_12 = ' ||p_input_12);
318 hr_utility.trace(' p_input_13 = ' ||p_input_13);
319 hr_utility.trace(' p_input_14 = ' ||p_input_14);
320 hr_utility.trace(' p_input_15 = ' ||p_input_15);
321 hr_utility.trace(' p_input_16 = ' ||p_input_16);
322 hr_utility.trace(' p_input_17 = ' ||p_input_17);
323 hr_utility.trace(' p_input_18 = ' ||p_input_18);
324 hr_utility.trace(' p_input_19 = ' ||p_input_19);
325 hr_utility.trace(' p_input_20 = ' ||p_input_20);
326 hr_utility.trace(' p_input_21 = ' ||p_input_21);
327 hr_utility.trace(' p_input_22 = ' ||p_input_22);
328 hr_utility.trace(' p_input_23 = ' ||p_input_23);
329 hr_utility.trace(' p_input_24 = ' ||p_input_24);
330 hr_utility.trace(' p_input_25 = ' ||p_input_25);
331 hr_utility.trace(' p_input_26 = ' ||p_input_26);
332 hr_utility.trace(' p_input_27 = ' ||p_input_27);
333 hr_utility.trace(' p_input_28 = ' ||p_input_28);
334 hr_utility.trace(' p_input_29 = ' ||p_input_29);
335 hr_utility.trace(' p_input_30 = ' ||p_input_30);
336 hr_utility.trace(' p_input_31 = ' ||p_input_31);
337 hr_utility.trace(' p_input_32 = ' ||p_input_32);
338 hr_utility.trace(' p_input_33 = ' ||p_input_33);
339 hr_utility.trace(' p_input_34 = ' ||p_input_34);
340 hr_utility.trace(' p_input_35 = ' ||p_input_35);
341 hr_utility.trace(' p_input_36 = ' ||p_input_36);
342 hr_utility.trace(' p_input_37 = ' ||p_input_37);
343 hr_utility.trace(' p_input_38 = ' ||p_input_38);
344 hr_utility.trace(' p_input_39 = ' ||p_input_39);
345 hr_utility.trace(' p_input_40 = ' ||p_input_40);
346
347 IF p_format = 'MMREF2' THEN -- p_format
348 --{
349 IF (p_report_type = 'W2C') THEN
350 --{
351 IF p_record_name = 'RCA' THEN -- p_record_name
352 hr_utility.set_location( gv_package || '.format_w2c_record',10);
353 return_value := pay_us_mmrf2_w2c_format_record.format_W2C_RCA_record(
354 p_effective_date,
355 p_report_type,
356 p_format,
357 p_report_qualifier,
358 p_record_name,
359 p_input_1,
360 p_input_2,
361 p_input_3,
362 p_input_4,
363 p_input_5,
364 p_input_6,
365 p_input_7,
366 p_input_8,
367 p_input_9 ,
368 p_input_10,
369 p_input_11,
370 p_input_12,
371 p_input_13,
372 p_input_14,
373 p_input_15,
374 p_input_16,
375 p_input_17,
376 p_input_18,
377 p_input_19,
378 p_input_20,
379 p_input_21,
380 p_input_22,
381 p_input_23,
382 p_input_24,
383 p_input_25,
384 p_input_26,
385 p_input_27,
386 p_input_28,
387 p_input_29,
388 p_input_30,
389 p_input_31,
390 p_input_32,
391 p_input_33,
392 p_input_34,
393 p_input_35,
394 p_input_36,
395 p_input_37,
396 p_input_38,
397 p_input_39,
398 p_input_40,
399 p_validate,
400 p_exclude_from_output,
401 sp_out_1,
402 sp_out_2,
403 sp_out_3,
404 sp_out_4,
405 sp_out_5,
406 ret_str_len,
407 l_exclude_from_output_chk
408 );
409 hr_utility.set_location( gv_package || '.format_w2c_record',20);
410 ELSIF p_record_name = 'RCE' THEN
411 hr_utility.set_location( gv_package || '.format_w2c_record',30);
412 --
413 -- Initialize GRE level Totals globally defined
414 --
415 ln_return_value := Initialize_GRE_Level_total;
416 hr_utility.set_location( gv_package || '.format_w2c_record',40);
417 --
418 -- Format RCE Record for the GRE
419 --
420 return_value :=
421 pay_us_mmrf2_w2c_format_record.format_W2C_RCE_record(
422 p_effective_date,
423 p_report_type,
424 p_format,
425 p_report_qualifier,
426 p_record_name,
427 p_input_1,
428 p_input_2,
429 p_input_3,
430 p_input_4,
431 p_input_5,
432 p_input_6,
433 p_input_7,
434 p_input_8,
435 p_input_9 ,
436 p_input_10,
437 p_input_11,
438 p_input_12,
439 p_input_13,
440 p_input_14,
441 p_input_15,
442 p_input_16,
443 p_input_17,
444 p_input_18,
445 p_input_19,
446 p_input_20,
447 p_input_21,
448 p_input_22,
449 p_input_23,
450 p_input_24,
451 p_input_25,
452 p_input_26,
453 p_input_27,
454 p_input_28,
455 p_input_29,
456 p_input_30,
457 p_input_31,
458 p_input_32,
459 p_input_33,
460 p_input_34,
461 p_input_35,
462 p_input_36,
463 p_input_37,
464 p_input_38,
465 p_input_39,
466 p_input_40,
467 p_validate,
468 p_exclude_from_output,
469 sp_out_1,
470 sp_out_2,
471 sp_out_3,
472 sp_out_4,
473 sp_out_5,
474 ret_str_len,
475 l_exclude_from_output_chk
476 );
477 hr_utility.set_location( gv_package || '.format_w2c_record',50);
478 ELSIF p_record_name = 'RCF' THEN
479 hr_utility.set_location( gv_package || '.format_w2c_record',60);
480 ln_return_value := Initialize_GRE_Level_total;
481 l_total_rcw_records :=
482 NVL(pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rcf,0);
483 hr_utility.trace('Total No of RCW processed for File Total ' ||to_char(l_total_rcw_records) );
484 hr_utility.trace('Formula feed Total No of RCW processed ' ||p_input_2 );
485 return_value :=
486 pay_us_mmrf2_w2c_format_record.format_W2C_RCF_record(
487 p_effective_date,
488 p_report_type,
489 p_format,
490 p_report_qualifier,
491 p_record_name,
492 p_input_1,
493 l_total_rcw_records,
494 p_input_3,
495 p_input_4,
496 p_input_40,
497 p_validate,
498 p_exclude_from_output,
499 ret_str_len,
500 l_exclude_from_output_chk
501 );
502 hr_utility.set_location( gv_package || '.format_w2c_record',70);
503
504 ELSIF p_record_name = 'RCW' then
505 hr_utility.set_location( gv_package || '.format_w2c_record',80);
506 if p_input_40 = 'FLAT' then
507 --{
508 return_value := pay_us_w2c_in_mmref2_format.pay_us_w2c_RCW_record (
509 p_effective_date,
510 p_report_type,
511 p_format,
512 p_report_qualifier,
513 p_record_name,
514 p_input_18, -- Tax_unit_ud
515 p_input_1, -- Record Identifier
516 p_input_2, --ssn,
517 p_input_3, --first_name,
518 p_input_4, --middle_name,
519 p_input_5, --last_name,
520 p_input_6, --sufix,
521 p_input_7, --location_address,
522 p_input_8, --delivery_address,
523 p_input_9, --city,
524 p_input_10, --state,
525 p_input_11, --zip,
526 p_input_12, --zip_extension,
527 p_input_13, --foreign_state,
528 p_input_14, --foreign_postal_code
529 p_input_15, --country_code,
530 p_input_16, --old_asgn_action_id,
531 p_input_17, --new_asgn_action_id,
532 p_input_39, --employee_number
533 p_input_40, --format_type (FLAT,CSV,BLANK)
534 p_validate,
535 p_exclude_from_output,
536 sp_out_1,
537 sp_out_2,
538 sp_out_3,
539 sp_out_4,
540 sp_out_5,
541 ret_str_len,
542 l_exclude_from_output_chk
543 );
544 hr_utility.set_location( gv_package || '.format_w2c_record',90);
545
546 --Added for Bug 13073358. If any RCW error then employee need to be
547 -- reported on .a02
548
549 if pay_us_w2c_in_mmref2_format.rcw_exclude_flag = 'Y' then
550 l_exclude_from_output_chk := TRUE;
551 else
552 l_exclude_from_output_chk := FALSE;
553 end if;
554
555 elsif p_input_40 = 'CSV' then
556 --
557 -- When RCW record is formatted for FLAT format, it also formats CSV and stores
558 -- the value when required it would use the CSV format record for audit purpose
559 --
560 hr_utility.set_location( gv_package || '.format_w2c_record',100);
561 return_value := pay_us_w2c_in_mmref2_format.rcw_csv_record;
562 elsif p_input_40 = 'BLANK' then
563 --
564 -- When RCW record is formatted for FLAT format, it also formats
565 -- Blank CSV of RCW for audit purpose only. The blank RCW would
566 -- be used for reporting Error on RCO.
567 --
568 hr_utility.set_location( gv_package || '.format_w2c_record',110);
569 return_value := pay_us_w2c_in_mmref2_format.rcw_blank_csv_record;
570 end if;
571 ELSIF p_record_name = 'RCO' then
572 --{
573 hr_utility.set_location( gv_package || '.format_w2c_record',150);
574 if p_input_40 = 'FLAT' then
575 --{
576
577 hr_utility.trace('Formating RCO for mf file ');
578 hr_utility.trace('RCO Exclude from output Flag '||
579 pay_us_w2c_in_mmref2_format.rco_exclude_flag);
580
581 return_value := pay_us_w2c_in_mmref2_format.rco_mf_record;
582
583 if pay_us_w2c_in_mmref2_format.rco_exclude_flag = 'Y'
584 then
585 l_exclude_from_output_chk := TRUE;
586 else
587 l_exclude_from_output_chk := FALSE;
588 end if;
589 hr_utility.set_location( gv_package || '.format_w2c_record',160);
590 --}
591 elsif p_input_40 = 'CSV' then
592 --
593 -- When RCW record is formatted for FLAT format, it also formats CSV and stores
594 -- the value when required it would use the CSV format record for audit purpose
595 --
596 hr_utility.trace('Formating RCO in CSV format for Audit file ');
597 hr_utility.set_location( gv_package || '.format_w2c_record',170);
598 return_value := pay_us_w2c_in_mmref2_format.rco_csv_record;
599 elsif p_input_40 = 'BLANK' then
600 --
601 -- When RCW record is formatted for FLAT format, it also formats
602 -- Blank CSV of RCW for audit purpose only. The blank RCW would
603 -- be used for reporting Error on RCO.
604 --
605 hr_utility.trace('Formating BLANK RCO in CSV format for Audit file ');
606 hr_utility.set_location( gv_package || '.format_w2c_record',180);
607 return_value := pay_us_w2c_in_mmref2_format.rco_blank_csv_record;
608 ret_str_len := pay_us_w2c_in_mmref2_format.rco_number_of_correction;
609 hr_utility.trace('No of Correction on RCO record '|| to_char(ret_str_len));
610 end if;
611 --}
612 END IF; --p_record_name
613 --}
614 END IF; --p_report_type
615 --}
616 END IF; -- p_format
617 return_value:=upper(return_value);
618 --
619 -- As formula function out parameter value can't exceed 200 characters
620 -- multiple out prameters are used to return a long varchar2
621 --
622 hr_utility.set_location( gv_package || '.format_w2_record',190);
623 main_return_string := substr(return_value,1,200);
624 sp_out_1:=substr(return_value,201,200);
625 sp_out_2:=substr(return_value,401,200);
626 sp_out_3:=substr(return_value,601,200);
627 sp_out_4:=substr(return_value,801,200);
628 sp_out_5:=substr(return_value,1001,200);
629
630 IF l_exclude_from_output_chk THEN
631 p_exclude_from_output := 'Y';
632 ELSE
633 p_exclude_from_output := 'N';
634 END IF;
635 hr_utility.set_location( gv_package || '.format_w2_record',200);
636 hr_utility.trace('main_return_string = '||main_return_string);
637 hr_utility.trace(' length of main_return_string = '||to_char(length(main_return_string)));
638 hr_utility.trace('sp_out_1 = '||sp_out_1);
639 hr_utility.trace(' length of sp_out_1 = '||to_char(length(sp_out_1)));
640 hr_utility.trace('sp_out_2 = '||sp_out_2);
641 hr_utility.trace(' length of sp_out_2 = '||to_char(length(sp_out_2)));
642 hr_utility.trace('sp_out_3 = '||sp_out_3);
643 hr_utility.trace(' length of sp_out_3 = '||to_char(length(sp_out_3)));
644 hr_utility.trace('sp_out_4 = '||sp_out_4);
645 hr_utility.trace(' length of sp_out_4 = '||to_char(length(sp_out_4)));
646 hr_utility.trace('sp_out_5 = '||sp_out_5);
647 hr_utility.trace(' length of sp_out_5 = '||to_char(length(sp_out_5)));
648 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
649 hr_utility.set_location( gv_package || '.format_w2_record',210);
650
651 RETURN main_return_string;
652 END Format_W2C_Record;
653 -- End of formatting record for W2C reporting
654 --
655
656 /*NEW*/
657
658 /* ---------------------------------------------------------------
659 Function Name : format_w2c_total_record
660 Purpose : This is a generalised function which can be used
661 in W-2c MAg Formula to format RCT and RCU recods
662 Error checking
663
664 Special Note :
665
666 parameters :
667
668 -------------------------------------------------------------------- */
669
670 FUNCTION format_w2c_total_record(
671 p_effective_date IN varchar2,
672 p_report_type IN varchar2,
673 p_format IN varchar2,
674 p_report_qualifier IN varchar2,
675 p_record_name IN varchar2,
676 p_input_1 IN varchar2,
677 p_input_2 IN varchar2,
678 p_input_3 IN varchar2,
679 p_input_4 IN varchar2,
680 p_input_5 IN varchar2,
681 p_output_1 OUT nocopy varchar2,
682 p_output_2 OUT nocopy varchar2,
683 p_output_3 OUT nocopy varchar2,
684 p_output_4 OUT nocopy varchar2,
685 p_output_5 OUT nocopy varchar2,
686 p_output_6 OUT nocopy varchar2,
687 p_output_7 OUT nocopy varchar2,
688 p_output_8 OUT nocopy varchar2,
689 p_output_9 OUT nocopy varchar2,
690 p_output_10 OUT nocopy varchar2,
691 p_output_11 OUT nocopy varchar2,
692 p_output_12 OUT nocopy varchar2,
693 p_output_13 OUT nocopy varchar2,
694 p_output_14 OUT nocopy varchar2,
695 p_output_15 OUT nocopy varchar2,
696 p_output_16 OUT nocopy varchar2,
697 p_output_17 OUT nocopy varchar2,
698 p_output_18 OUT nocopy varchar2,
699 p_output_19 OUT nocopy varchar2,
700 p_output_20 OUT nocopy varchar2,
701 p_output_21 OUT nocopy varchar2,
702 p_output_22 OUT nocopy varchar2,
703 p_output_23 OUT nocopy varchar2,
704 p_output_24 OUT nocopy varchar2,
705 p_output_25 OUT nocopy varchar2,
706 p_output_26 OUT nocopy varchar2,
707 p_output_27 OUT nocopy varchar2,
708 p_output_28 OUT nocopy varchar2,
709 p_output_29 OUT nocopy varchar2,
710 p_output_30 OUT nocopy varchar2,
711 p_output_31 OUT nocopy varchar2,
712 p_output_32 OUT nocopy varchar2,
713 p_output_33 OUT nocopy varchar2,
714 p_output_34 OUT nocopy varchar2,
715 p_output_35 OUT nocopy varchar2,
716 p_output_36 OUT nocopy varchar2,
717 p_output_37 OUT nocopy varchar2,
718 p_output_38 OUT nocopy varchar2,
719 p_output_39 OUT nocopy varchar2,
720 p_output_40 OUT nocopy varchar2,
721 p_output_41 OUT nocopy varchar2,
722 p_output_42 OUT nocopy varchar2,
723 p_output_43 OUT nocopy varchar2,
724 p_output_44 OUT nocopy varchar2,
725 p_output_45 OUT nocopy varchar2,
726 p_output_46 OUT nocopy varchar2,
727 p_output_51 OUT nocopy varchar2, /* saurabh */
728 p_output_52 OUT nocopy varchar2, /* saurabh */
729 p_output_53 OUT nocopy varchar2, /* saurabh */
730 p_output_54 OUT nocopy varchar2, /* saurabh */
731 p_validate IN varchar2,
732 p_exclude_from_output OUT nocopy varchar2,
733 sp_out_1 OUT nocopy varchar2,
734 sp_out_2 OUT nocopy varchar2,
735 sp_out_3 OUT nocopy varchar2,
736 sp_out_4 OUT nocopy varchar2,
737 sp_out_5 OUT nocopy varchar2,
738 ret_str_len OUT nocopy varchar2,
739 p_output_47 OUT nocopy varchar2,
740 p_output_48 OUT nocopy varchar2,
741 p_output_49 OUT nocopy varchar2,
742 p_output_50 OUT nocopy varchar2
743 ) RETURN VARCHAR2
744 IS
745
746 return_value varchar2(32767);
747 l_exclude_from_output_chk boolean;
748 main_return_string varchar2(300);
749 ln_no_of_rcw_wages number := 23;
750 ln_no_of_rco_wages number := 8;
751 TYPE function_columns IS RECORD(
752 p_parameter_name varchar2(100)
753 );
754 function_parameter_rec function_columns;
755 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
756 INDEX BY BINARY_INTEGER;
757 parameter_record input_parameter_record;
758 lb_exclude_from_output_chk boolean := FALSE;
759 lv_wage_value_in_cents varchar2(100) := ' ';
760 BEGIN
761
762 hr_utility.set_location( gv_package || '.format_w2c_total_record',10);
763 hr_utility.trace(' p_report_qualifier = '||p_report_qualifier);
764 hr_utility.trace(' p_record_name = ' ||p_record_name);
765
766 IF p_format = 'MMREF2' THEN -- p_format
767 --{
768 IF (p_report_type = 'W2C') THEN
769 --{
770 IF p_record_name = 'RCT' THEN -- p_record_name
771 --{
772 hr_utility.set_location( gv_package || '.format_w2c_total_record',20);
773 parameter_record.delete;
774 parameter_record(1).p_parameter_name := 'Wages,Tips And Other Compensation';
775 parameter_record(2).p_parameter_name := 'Federal Income Tax Withheld';
776 parameter_record(3).p_parameter_name := 'Social Security Wages';
777 parameter_record(4).p_parameter_name := 'Social Security Tax Withheld';
778 parameter_record(5).p_parameter_name := 'Medicare Wages And Tips';
779 parameter_record(6).p_parameter_name := 'Medicare Tax Withheld';
780 parameter_record(7).p_parameter_name := 'Social Security Tips';
781 parameter_record(8).p_parameter_name := 'Advance Earned Income Credit';
782 parameter_record(9).p_parameter_name := 'Dependent Care Benefits';
783 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
784 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
785 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
786 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
787 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
788 parameter_record(15).p_parameter_name:= 'Deferred Compensation Contribution';
789 parameter_record(16).p_parameter_name:= 'Military Combat Pay';
790 parameter_record(17).p_parameter_name:= 'Non-Qual. plan Sec 457';
791 parameter_record(18).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
792 parameter_record(19).p_parameter_name:= 'Employer cost of premiun';
793 parameter_record(20).p_parameter_name:= 'Income from nonqualified stock option';
794 parameter_record(21).p_parameter_name:= 'ER Contribution to HSA';
795 parameter_record(22).p_parameter_name:= 'Nontaxable Combat Pay';
796 parameter_record(23).p_parameter_name:= 'Nonqual 409A Deferral Amount';
797 parameter_record(24).p_parameter_name:= 'Designated Roth Contr. to 401k Plan'; /* saurabh */
798 parameter_record(25).p_parameter_name:= 'Designated Roth Contr. to 403b Plan'; /* saurabh */
799 parameter_record(26).p_parameter_name:= 'Cost of Employer-Sponsored Health Coverage'; --13255427
800
801 ln_no_of_rcw_wages := 26; --13255427 /* saurabh */
802
803
804 hr_utility.set_location( gv_package || '.format_w2c_total_record',30);
805
806 FOR i IN 1..ln_no_of_rcw_wages
807 LOOP
808 if (NVL(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0) <>
809 NVL(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0)) then
810 --{
811 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_identical_flag := 'N';
812
813 /* Negative Value check For Originally Reported Value on RCT */
814 lv_wage_value_in_cents :=
815 to_char(nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0 ));
816
817 hr_utility.set_location( gv_package || '.format_w2c_total_record',40);
818
819 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old_formated :=
820 pay_us_reporting_utils_pkg.data_validation(
821 p_effective_date,
822 p_report_type,
823 p_format,
824 p_report_qualifier,
825 p_record_name,
826 'NEG_CHECK',
827 lv_wage_value_in_cents,
828 parameter_record(i).p_parameter_name||'(Old)',
829 p_input_1,
830 null,
831 p_validate,
832 p_exclude_from_output,
833 sp_out_1,
834 sp_out_2);
835
836 IF p_exclude_from_output = 'Y' THEN
837 lb_exclude_from_output_chk := TRUE;
838 END IF;
839
840 hr_utility.trace(parameter_record(i).p_parameter_name||'(Old) = '
841 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old);
842
843 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
844
845 hr_utility.set_location( gv_package || '.format_w2c_total_record',50);
846 /* Negative Value check For Corrected Value to be reported on RCT */
847
848 lv_wage_value_in_cents :=
849 to_char(nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0));
850
851 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new_formated :=
852 pay_us_reporting_utils_pkg.data_validation(
853 p_effective_date,
854 p_report_type,
855 p_format,
856 p_report_qualifier,
857 p_record_name,
858 'NEG_CHECK',
859 lv_wage_value_in_cents,
860 parameter_record(i).p_parameter_name||'(New)',
861 p_input_1,
862 null,
863 p_validate,
864 p_exclude_from_output,
865 sp_out_1,
866 sp_out_2);
867
868 IF p_exclude_from_output = 'Y' THEN
869 lb_exclude_from_output_chk := TRUE;
870 END IF;
871
872 hr_utility.set_location( gv_package || '.format_w2c_total_record',60);
873 hr_utility.trace(parameter_record(i).p_parameter_name||'(New) = '
874 ||pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new);
875 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
876
877 /* Set output parameters when RCT Originally reported value and Corrected values
878 are not identical */
879 --{
880 if i = 1 then
881 p_output_1 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
882 p_output_21 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
883 elsif i = 2 then
884 p_output_2 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
885 p_output_22 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
886 elsif i = 3 then
887 p_output_3 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
888 p_output_23 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
889 elsif i = 4 then
890 p_output_4 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
891 p_output_24 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
892 elsif i = 5 then
893 p_output_5 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
894 p_output_25 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
895 elsif i = 6 then
896 p_output_6 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
897 p_output_26 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
898 elsif i = 7 then
899 p_output_7 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
900 p_output_27 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
901 elsif i = 8 then
902 p_output_8 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
903 p_output_28 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
904 elsif i = 9 then
905 p_output_9 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
906 p_output_29 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
907 elsif i = 10 then
908 p_output_10 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
909 p_output_30 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
910 elsif i = 11 then
911 p_output_11 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
912 p_output_31 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
913 elsif i = 12 then
914 p_output_12 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
915 p_output_32 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
916 elsif i = 13 then
917 p_output_13 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
918 p_output_33 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
919 elsif i = 14 then
920 p_output_14 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
921 p_output_34 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
922 elsif i = 15 then
923 p_output_15 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
924 p_output_35 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
925 elsif i = 16 then
926 p_output_16 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
927 p_output_36 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
928 elsif i = 17 then
929 p_output_17 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
930 p_output_37 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
931 elsif i = 18 then
932 p_output_18 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
933 p_output_38 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
934 elsif i = 19 then
935 p_output_19 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
936 p_output_39 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
937 elsif i = 20 then
938 p_output_20 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
939 p_output_40 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
940 elsif i = 21 then
941 p_output_43 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
942 p_output_44 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
943 elsif i = 22 then
944 p_output_47 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
945 p_output_48 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
946 elsif i = 23 then
947 p_output_45 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
948 p_output_46 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
949 elsif i = 24 then /* saurabh */
950 p_output_51 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
951 p_output_53 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
952 elsif i = 25 then /* saurabh */
953 p_output_52 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
954 p_output_54 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
955 elsif i = 26 then --13255427
956 p_output_49 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old,0);
957 p_output_50 := nvl(pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new,0);
958 end if;
959
960 hr_utility.set_location( gv_package || '.format_w2c_total_record',70);
961 --}
962 --}
963 else
964 --{
965 hr_utility.set_location( gv_package || '.format_w2c_total_record',80);
966
967 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_identical_flag := 'Y';
968 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old_formated :=
969 lpad(' ',15);
970 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new_formated :=
971 lpad(' ',15);
972 /* Set output parameters as 0 when RCT Originally reported value and Corrected values
973 are identical */
974 --{
975 if i = 1 then
976 p_output_1 := '0';
977 p_output_21 := '0';
978 elsif i = 2 then
979 p_output_2 := '0';
980 p_output_22 := '0';
981 elsif i = 3 then
982 p_output_3 := '0';
983 p_output_23 := '0';
984 elsif i = 4 then
985 p_output_4 := '0';
986 p_output_24 := '0';
987 elsif i = 5 then
988 p_output_5 := '0';
989 p_output_25 := '0';
990 elsif i = 6 then
991 p_output_6 := '0';
992 p_output_26 := '0';
993 elsif i = 7 then
994 p_output_7 := '0';
995 p_output_27 := '0';
996 elsif i = 8 then
997 p_output_8 := '0';
998 p_output_28 := '0';
999 elsif i = 9 then
1000 p_output_9 := '0';
1001 p_output_29 := '0';
1002 elsif i = 10 then
1003 p_output_10 := '0';
1004 p_output_30 := '0';
1005 elsif i = 11 then
1006 p_output_11 := '0';
1007 p_output_31 := '0';
1008 elsif i = 12 then
1009 p_output_12 := '0';
1010 p_output_32 := '0';
1011 elsif i = 13 then
1012 p_output_13 := '0';
1013 p_output_33 := '0';
1014 elsif i = 14 then
1015 p_output_14 := '0';
1016 p_output_34 := '0';
1017 elsif i = 15 then
1018 p_output_15 := '0';
1019 p_output_35 := '0';
1020 elsif i = 16 then
1021 p_output_16 := '0';
1022 p_output_36 := '0';
1023 elsif i = 17 then
1024 p_output_17 := '0';
1025 p_output_37 := '0';
1026 elsif i = 18 then
1027 p_output_18 := '0';
1028 p_output_38 := '0';
1029 elsif i = 19 then
1030 p_output_19 := '0';
1031 p_output_39 := '0';
1032 elsif i = 20 then
1033 p_output_20 := '0';
1034 p_output_40 := '0';
1035 elsif i = 21 then
1036 p_output_43 := '0';
1037 p_output_44 := '0';
1038 elsif i = 22 then
1039 p_output_47 := '0';
1040 p_output_48 := '0';
1041 elsif i = 23 then
1042 p_output_45 := '0';
1043 p_output_46 := '0';
1044 elsif i = 24 then /* saurabh */
1045 p_output_51 := '0';
1046 p_output_53 := '0';
1047 elsif i = 25 then /* saurabh */
1048 p_output_52 := '0';
1049 p_output_54 := '0';
1050 elsif i = 26 then --13255427
1051 p_output_49 := '0';
1052 p_output_50 := '0';
1053 end if;
1054
1055 --}
1056 hr_utility.set_location( gv_package || '.format_w2c_total_record',90);
1057 --}
1058 end if;
1059
1060 END LOOP;
1061
1062 hr_utility.trace('p_output_47 '||p_output_47);
1063 hr_utility.trace('p_output_48 '||p_output_48);
1064
1065 p_output_41 := lpad(NVL(pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct,0),7,'0');
1066 p_output_42 := lpad(NVL(pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct,0),7,'0');
1067
1068 hr_utility.set_location( gv_package || '.format_w2c_total_record',100);
1069
1070 return_value :=
1071 pay_us_mmrf2_w2c_format_record.format_W2C_RCT_record(
1072 p_effective_date,
1073 p_report_type,
1074 p_format,
1075 p_report_qualifier,
1076 p_record_name,
1077 p_input_1,
1078 p_input_2,
1079 p_input_3,
1080 p_input_4,
1081 p_output_41,
1082 p_validate,
1083 p_exclude_from_output,
1084 ret_str_len,
1085 lb_exclude_from_output_chk
1086 );
1087
1088 hr_utility.set_location( gv_package || '.format_w2c_total_record',110);
1089 -- End of Formating RCT Record
1090 --
1091 --}
1092 ELSIF p_record_name = 'RCU' THEN
1093 --{
1094 hr_utility.set_location( gv_package || '.format_w2c_total_record',120);
1095 parameter_record.delete;
1096 ln_no_of_rco_wages := 10; --Increased for the Bug 13255427
1097 parameter_record(1).p_parameter_name:= ' Allocated Tips';
1098 parameter_record(2).p_parameter_name:= 'Uncollected employee tax on tips';
1099 parameter_record(3).p_parameter_name:= 'medical savings a/c';
1100 parameter_record(4).p_parameter_name:= 'simple retirement a/c';
1101 parameter_record(5).p_parameter_name:= 'qualified adoption expenses';
1102 parameter_record(6).p_parameter_name:= 'Uncollected SS tax';
1103 parameter_record(7).p_parameter_name:= 'Uncollected medicaroe tax';
1104 parameter_record(8).p_parameter_name:= 'income under 409A';
1105 parameter_record(9).p_parameter_name:= 'Hire Exempt Wages and Tips';--9783383
1106 parameter_record(10).p_parameter_name:= 'Desig. Roth Contributions Under 457b Plan'; --13255427
1107 --
1108 -- Compare RCO Wage total for formatting RCU Total Wage Record
1109 --
1110 hr_utility.set_location( gv_package || '.format_w2c_total_record',130);
1111 FOR i IN 1..ln_no_of_rco_wages
1112 LOOP
1113 if (NVL(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0) <>
1114 NVL(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0)) then
1115 --{
1116 hr_utility.set_location( gv_package || '.format_w2c_total_record',140);
1117 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_identical_flag := 'N';
1118
1119 /* Negative Value check For Originally Reported Value on RCT */
1120 lv_wage_value_in_cents :=
1121 to_char(nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0));
1122 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old_formated :=
1123 pay_us_reporting_utils_pkg.data_validation(
1124 p_effective_date,
1125 p_report_type,
1126 p_format,
1127 p_report_qualifier,
1128 p_record_name,
1129 'NEG_CHECK',
1130 lv_wage_value_in_cents,
1131 parameter_record(i).p_parameter_name||'(Old)',
1132 p_input_1,
1133 null,
1134 p_validate,
1135 p_exclude_from_output,
1136 sp_out_1,
1137 sp_out_2);
1138 IF p_exclude_from_output = 'Y' THEN
1139 lb_exclude_from_output_chk := TRUE;
1140 END IF;
1141 hr_utility.set_location( gv_package || '.format_w2c_total_record',150);
1142 hr_utility.trace(parameter_record(i).p_parameter_name||'(Old) = '
1143 ||lv_wage_value_in_cents);
1144 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1145
1146 /* Negative Value check For Corrected Value to be reported on RCU */
1147 hr_utility.set_location( gv_package || '.format_w2c_total_record',160);
1148 lv_wage_value_in_cents :=
1149 to_char(nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0));
1150 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new_formated :=
1151 pay_us_reporting_utils_pkg.data_validation(
1152 p_effective_date,
1153 p_report_type,
1154 p_format,
1155 p_report_qualifier,
1156 p_record_name,
1157 'NEG_CHECK',
1158 lv_wage_value_in_cents,
1159 parameter_record(i).p_parameter_name||'(New)',
1160 p_input_1,
1161 null,
1162 p_validate,
1163 p_exclude_from_output,
1164 sp_out_1,
1165 sp_out_2);
1166 IF p_exclude_from_output = 'Y' THEN
1167 lb_exclude_from_output_chk := TRUE;
1168 END IF;
1169 hr_utility.set_location( gv_package || '.format_w2c_total_record',170);
1170 hr_utility.trace(parameter_record(i).p_parameter_name||'(New) = '
1171 ||lv_wage_value_in_cents);
1172 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1173 /* Set output parameters when RCT Originally reported value and Corrected values
1174 are not identical ch9783383*/
1175 --{
1176 if i = 1 then
1177 p_output_1 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1178 p_output_21 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1179 elsif i = 2 then
1180 p_output_2 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1181 p_output_22 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1182 elsif i = 3 then
1183 p_output_3 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1184 p_output_23 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1185 elsif i = 4 then
1186 p_output_4 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1187 p_output_24 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1188 elsif i = 5 then
1189 p_output_5 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1190 p_output_25 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1191 elsif i = 6 then
1192 p_output_6 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1193 p_output_26 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1194 elsif i = 7 then
1195 p_output_7 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1196 p_output_27 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1197 elsif i = 8 then
1198 p_output_8 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1199 p_output_28 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1200 elsif i = 9 then--9783383
1201 p_output_9 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1202 p_output_29 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1203 elsif i = 10 then--13255427
1204 p_output_10 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old,0);
1205 p_output_30 := nvl(pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new,0);
1206 end if;
1207 --hr_utility.trace('p_output_9 '||p_output_9);
1208 --hr_utility.trace('p_output_29 '||p_output_29);
1209
1210 hr_utility.set_location( gv_package || '.format_w2c_total_record',180);
1211 --}
1212 --}
1213 else
1214 --{
1215 hr_utility.set_location( gv_package || '.format_w2c_total_record',190);
1216 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_identical_flag := 'Y';
1217 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old_formated :=
1218 lpad(' ',15);
1219 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new_formated :=
1220 lpad(' ',15);
1221 if i = 1 then
1222 p_output_1 := '0';
1223 p_output_21 := '0';
1224 elsif i = 2 then
1225 p_output_2 := '0';
1226 p_output_22 := '0';
1227 elsif i = 3 then
1228 p_output_3 :='0';
1229 p_output_23 := '0';
1230 elsif i = 4 then
1231 p_output_4 := '0';
1232 p_output_24 := '0';
1233 elsif i = 5 then
1234 p_output_5 := '0';
1235 p_output_25 := '0';
1236 elsif i = 6 then
1237 p_output_6 := '0';
1238 p_output_26 := '0';
1239 elsif i = 7 then
1240 p_output_7 := '0';
1241 p_output_27 := '0';
1242 elsif i = 8 then
1243 p_output_8 := '0';
1244 p_output_28 := '0';
1245 elsif i = 9 then --9783383
1246 p_output_9 := '0';
1247 p_output_29 := '0';
1248 elsif i = 10 then --13255427
1249 p_output_10 := '0';
1250 p_output_30 := '0';
1251 end if;
1252 --}
1253 end if;
1254 END LOOP;
1255 hr_utility.set_location( gv_package || '.format_w2c_total_record',200);
1256 p_output_41 := lpad(NVL(pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu,0),7,'0');
1257 p_output_42 := lpad(NVL(pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu,0),7,'0');
1258 return_value :=
1259 pay_us_mmrf2_w2c_format_record.format_W2C_RCU_record(
1260 p_effective_date,
1261 p_report_type,
1262 p_format,
1263 p_report_qualifier,
1264 p_record_name,
1265 p_input_1,
1266 p_input_2,
1267 p_input_3,
1268 p_input_4,
1269 p_output_41,
1270 p_validate,
1271 p_exclude_from_output,
1272 ret_str_len,
1273 lb_exclude_from_output_chk
1274 );
1275 hr_utility.set_location( gv_package || '.format_w2c_total_record',210);
1276 --}
1277 END IF; --p_record_name
1278 --}
1279 END IF; --p_report_type
1280 --}
1281 END IF; -- p_format
1282 return_value:=upper(return_value);
1283 --
1284 -- As formula function out parameter value can't exceed 200 characters
1285 -- multiple out prameters are used to return a long varchar2
1286 --
1287 main_return_string := substr(return_value,1,200);
1288 sp_out_1:=substr(return_value,201,200);
1289 sp_out_2:=substr(return_value,401,200);
1290 sp_out_3:=substr(return_value,601,200);
1291 sp_out_4:=substr(return_value,801,200);
1292 sp_out_5:=substr(return_value,1001,200);
1293
1294 IF l_exclude_from_output_chk THEN
1295 p_exclude_from_output := 'Y';
1296 ELSE
1297 p_exclude_from_output := 'N';
1298 END IF;
1299 hr_utility.trace('main_return_string = '||main_return_string);
1300 hr_utility.trace(' length of main_return_string = '||to_char(length(main_return_string)));
1301 hr_utility.trace('sp_out_1 = '||sp_out_1);
1302 hr_utility.trace(' length of sp_out_1 = '||to_char(length(sp_out_1)));
1303 hr_utility.trace('sp_out_2 = '||sp_out_2);
1304 hr_utility.trace(' length of sp_out_2 = '||to_char(length(sp_out_2)));
1305 hr_utility.trace('sp_out_3 = '||sp_out_3);
1306 hr_utility.trace(' length of sp_out_3 = '||to_char(length(sp_out_3)));
1307 hr_utility.trace('sp_out_4 = '||sp_out_4);
1308 hr_utility.trace(' length of sp_out_4 = '||to_char(length(sp_out_4)));
1309 hr_utility.trace('sp_out_5 = '||sp_out_5);
1310 hr_utility.trace(' length of sp_out_5 = '||to_char(length(sp_out_5)));
1311 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1312 hr_utility.set_location( gv_package || '.format_w2c_total_record',220);
1313
1314 RETURN main_return_string;
1315 END Format_W2C_total_Record;
1316 /* NEW*/
1317
1318 --
1319 -- This function is used to initialize all the GRE level data used
1320 -- for Reporting in RCT and RCU records. This function is called
1321 -- when RCE record is being formatted
1322 -- There is no parameter for this function
1323 Function Initialize_GRE_Level_total return number
1324 IS
1325 ln_no_of_rcw_wages number := 26; --Bug 13255427 /* saurabh */
1326 ln_no_of_rco_wages number := 10; --13255427
1327 BEGIN
1328 hr_utility.set_location( gv_package || '.Initialize_GRE_Level_total',10);
1329 hr_utility.trace('Entered in pay_us_w2c_in_mmref2_format.Initialize_GRE_Level_total');
1330 hr_utility.trace('Initializing GRE Level Totals');
1331 if pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct > 0 then
1332 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rcf :=
1333 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rcf +
1334 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct ;
1335 end if;
1336
1337 -- Number of Valid RCW Record to be reported in RCT
1338 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct := 0; --3;
1339
1340 if pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct > 0 then
1341 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf :=
1342 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf +
1343 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct ;
1344 end if;
1345 -- Number of Error RCW Record to be reported in RCT
1346 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct := 0;
1347
1348 if pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu > 0 then
1349 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcf :=
1350 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcf +
1351 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu ;
1352 end if;
1353 -- Number of Valid RCO Record to be reported in RCU
1354 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu :=0; -- 3;
1355
1356 if pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu > 0 then
1357 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcf :=
1358 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcf +
1359 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu ;
1360 end if;
1361 -- Number of Error RCO Record to be reported in RCU
1362 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu := 0;
1363
1364 -- Initialize all the PL/SQL table defined for RCT Wages
1365 ln_no_of_rcw_wages := 26; --13255427 /* saurabh */
1366 pay_us_w2c_in_mmref2_format.ltr_rct_info.delete;
1367 FOR i IN 1..ln_no_of_rcw_wages
1368 LOOP
1369 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old := 0; --100000;
1370 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new := 0; --200000;
1371 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_old_formated := ' ';
1372 pay_us_w2c_in_mmref2_format.ltr_rct_info(i).rct_wage_new_formated := ' ';
1373 END LOOP;
1374 -- Initialize all the PL/SQL table defined for RCU Wages
1375 hr_utility.set_location( gv_package || '.Initialize_GRE_Level_total',20);
1376 pay_us_w2c_in_mmref2_format.ltr_rcu_info.delete;
1377 ln_no_of_rco_wages := 10;--13255427
1378
1379 FOR i IN 1..ln_no_of_rco_wages
1380 LOOP
1381 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old := 0; --100000;
1382 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new := 0; --200000;
1383 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_old_formated := ' ';
1384 pay_us_w2c_in_mmref2_format.ltr_rcu_info(i).rcu_wage_new_formated := ' ';
1385 END LOOP;
1386 hr_utility.set_location( gv_package || '.Initialize_GRE_Level_total',30);
1387 hr_utility.trace('Leaving pay_us_w2c_in_mmref2_format.Initialize_GRE_Level_total');
1388 return(0);
1389 EXCEPTION
1390 when others then
1391 return(1);
1392 END Initialize_GRE_Level_total;
1393
1394
1395 --
1396 -- This procedure would be used for Fetching the archived values of RCW or RCO record
1397 --
1398 PROCEDURE GET_ARCHIVED_VALUES ( p_action_type varchar2 -- O Originally Reported, C Corrected
1399 ,p_record_type varchar2 -- RCW, RCO
1400 ,p_assignment_action_id number
1401 ,p_tax_unit_id number)
1402 IS
1403
1404 CURSOR get_reporting_year (p_assign_action_id NUMBER) IS
1405 SELECT TO_CHAR(effective_date,'YYYY')
1406 FROM pay_assignment_actions paa,pay_payroll_actions ppa
1407 WHERE ppa.payroll_action_id = paa.payroll_action_id
1408 AND paa.assignment_action_id = p_assign_action_id;
1409
1410 i number := 0;
1411 j number := 0;
1412 ln_gross_wages number := 0;
1413 ln_non_qual_not457 number := 0;
1414 lv_statutory_employee varchar2(200) :='';
1415 ln_401k_contribution number := 0;
1416 ln_403b_contribution number := 0;
1417 ln_408k_contribution number := 0;
1418 ln_457_contribution number := 0;
1419 ln_501c_contribution number := 0;
1420 ln_total_contribution number := 0;
1421 ln_nonqual_457 number := 0;
1422 ln_nonqual_not457 number := 0;
1423 ln_nonqual_plan number := 0;
1424 ln_3rd_party number := 0;
1425 ln_no_of_rcw_wages number := 25; /* saurabh */
1426 ln_no_of_rco_wages number := 8;
1427 ln_roth_401k_contribution number := 0; /* saurabh */
1428 ln_roth_403b_contribution number := 0; /* saurabh */
1429 --a number(14,2) := 0;
1430 --tmp varchar2(500);
1431 ln_direct_fed_bal_call varchar2(2) := 'N';
1432 l_year varchar2(4);
1433
1434 l_w2_pension number :=0;
1435 ln_roth_457b_contribution number :=0;
1436
1437 BEGIN
1438 --
1439 -- In PL/SQL table 1st record would be Originally reported arhived value for an Assignment_Action_Id
1440 -- and 2nd record would be Corrected values
1441 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',10);
1442 if p_action_type = 'O'
1443 then
1444 i := 1;
1445 elsif p_action_type = 'C' then
1446 i := 2;
1447 end if;
1448
1449 if p_record_type = 'RCW' then
1450 --{
1451 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',20);
1452 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).SSN := '';
1453 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).first_name := '';
1454 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).middle_name := '';
1455 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).last_name := '';
1456 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information1 := 0; -- wages, tips and other compensation
1457 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information2 := 0; -- FIT withheld
1458 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information3 := 0; -- SS Wages
1459 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information4 := 0; -- SS Tax withheld
1460 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information5 := 0; -- Medicare Wages/Tips
1461 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information6 := 0; -- Medicare Tax withheld
1462 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information7 := 0; -- Social Security Tips
1463 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information8 := 0; -- Advanced EIC
1464 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information9 := 0; -- Dependent Care benefits
1465 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information10 := 0; -- deferred compensation contributions to section 401(K)
1466 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information11 := 0; -- deferred compensation contributions to section 403(b)
1467 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information12 := 0; -- deferred compensation contributions to section 408(K)(6)
1468 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information13 := 0; -- deferred compensation contributions to section 457(b)
1469 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information14 := 0; -- deferred compensation contributions to section 501(c)(18)(D)
1470 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information15 := 0; -- Deferred compensation contributions
1471 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information16 := 0; -- Military employees basic quarters, subsistence and combat pay
1472 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information17 := 0; -- nonqualified plan section 457 distributions or contributions
1473 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information18 := 0; -- nonqualified plan not section 457 distributions or contributions
1474 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information19 := 0; -- employer cost of premiums for GTL over $50000
1475 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information20 := 0; -- income from the exercise of nonstatutory stock options
1476 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information21 := 0; -- ER contribution to Health Savings Account
1477 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information22 := 0; -- Nontax Combat Pay
1478 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information23 := 0; -- 409A Deferrals
1479 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information24 := 0; -- deferred compensation contributions to section roth 401(K) /* saurabh */
1480 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information25 := 0; -- deferred compensation contributions to section roth 403(b) /* saurabh */
1481 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information26 := 0; -- Cost of Employer-Sponsored Health Coverage, Bug 13255427
1482
1483 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).statutory_emp_indicator := '';
1484 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).retirement_plan_indicator := '';
1485 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).sick_pay_indicator := '';
1486 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',30);
1487 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).SSN :=
1488 hr_us_w2_rep.get_per_item(p_assignment_action_id,'A_PER_NATIONAL_IDENTIFIER') ;
1489 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).first_name :=
1490 hr_us_w2_rep.get_per_item(p_assignment_action_id,'A_PER_FIRST_NAME') ;
1491 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).middle_name :=
1492 hr_us_w2_rep.get_per_item(p_assignment_action_id, 'A_PER_MIDDLE_NAMES') ;
1493 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).last_name :=
1494 hr_us_w2_rep.get_per_item(p_assignment_action_id, 'A_PER_LAST_NAME') ;
1495
1496 -- wages, tips and other compensation
1497 ln_gross_wages := 0;
1498
1499 /*Added for Bug#13326069*/
1500 ln_direct_fed_bal_call := nvl(fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES'),'N');
1501
1502 /*Added for Bug#13614766*/
1503 IF ln_direct_fed_bal_call = 'Y' THEN
1504
1505 OPEN get_reporting_year(p_assignment_action_id);
1506 FETCH get_reporting_year INTO l_year;
1507 CLOSE get_reporting_year;
1508
1509 ln_direct_fed_bal_call := hr_us_ff_udfs.direct_fed_data_archived(l_year);
1510
1511 END IF;
1512
1513 IF ln_direct_fed_bal_call = 'Y' THEN
1514
1515 ln_gross_wages := hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id
1516 ,'A_FIT_SUBJ_WHABLE_PER_GRE_YTD'
1517 ,p_tax_unit_id
1518 ,'00-000-0000'
1519 , 0) +
1520 hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id,
1521 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD'
1522 ,p_tax_unit_id
1523 ,'00-000-0000'
1524 ,0) -
1525 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1526 ,'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD'
1527 ,p_tax_unit_id
1528 ,'00-000-0000'
1529 ,0);
1530 /*End of changes for Bug#13326069*/
1531 ELSE
1532
1533 ln_gross_wages := hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id
1534 ,'A_REGULAR_EARNINGS_PER_GRE_YTD'
1535 ,p_tax_unit_id
1536 ,'00-000-0000'
1537 , 0) +
1538 hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id,
1539 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD'
1540 ,p_tax_unit_id
1541 ,'00-000-0000'
1542 ,0) +
1543 hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id,
1544 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'
1545 ,p_tax_unit_id
1546 ,'00-000-0000'
1547 ,0) -
1548 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1549 ,'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD'
1550 ,p_tax_unit_id
1551 ,'00-000-0000'
1552 ,0)+
1553 hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id,
1554 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD'
1555 ,p_tax_unit_id
1556 ,'00-000-0000'
1557 ,0)+
1558 hr_us_w2_rep.get_w2_arch_bal( p_assignment_action_id,
1559 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'
1560 ,p_tax_unit_id
1561 ,'00-000-0000'
1562 ,0);
1563
1564 END IF;
1565
1566 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information1 := ln_gross_wages;
1567
1568 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',40);
1569 -- FIT withheld
1570 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information2 :=
1571 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1572 ,'A_FIT_WITHHELD_PER_GRE_YTD'
1573 ,p_tax_unit_id
1574 ,'00-000-0000'
1575 ,0);
1576 -- SS Wages
1577 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information3 :=
1578 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1579 ,'A_SS_EE_TAXABLE_PER_GRE_YTD'
1580 ,p_tax_unit_id
1581 ,'00-000-0000'
1582 ,0);
1583 -- SS Tax withheld
1584 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information4 :=
1585 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1586 ,'A_SS_EE_WITHHELD_PER_GRE_YTD'
1587 ,p_tax_unit_id
1588 ,'00-000-0000'
1589 ,0);
1590 -- Medicare Wages/Tips
1591 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information5 :=
1592 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1593 ,'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD'
1594 ,p_tax_unit_id
1595 ,'00-000-0000'
1596 ,0);
1597 -- Medicare Tax withheld
1598 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information6 :=
1599 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1600 ,'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD'
1601 ,p_tax_unit_id
1602 ,'00-000-0000'
1603 ,0);
1604 -- Social Security Tips
1605 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information7 :=
1606 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1607 ,'A_W2_BOX_7_PER_GRE_YTD'
1608 ,p_tax_unit_id
1609 ,'00-000-0000'
1610 ,0);
1611 -- Advanced EIC
1612 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information8 :=
1613 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1614 ,'A_EIC_ADVANCE_PER_GRE_YTD'
1615 ,p_tax_unit_id
1616 ,'00-000-0000'
1617 ,0);
1618 -- Dependent Care benefits
1619 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information9 :=
1620 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1621 ,'A_W2_DEPENDENT_CARE_PER_GRE_YTD'
1622 ,p_tax_unit_id
1623 ,'00-000-0000'
1624 ,0);
1625 -- deferred compensation contributions to section 401(K)
1626 ln_401k_contribution :=
1627 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1628 ,'A_W2_401K_PER_GRE_YTD'
1629 ,p_tax_unit_id
1630 ,'00-000-0000'
1631 ,0);
1632 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information10 := ln_401k_contribution;
1633 -- deferred compensation contributions to section 403(b)
1634 ln_403b_contribution :=
1635 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1636 ,'A_W2_403B_PER_GRE_YTD'
1637 ,p_tax_unit_id
1638 ,'00-000-0000'
1639 ,0);
1640
1641 -- 'Designated Roth Contr. to 401k Plan' /* saurabh */
1642 ln_roth_401k_contribution :=
1643 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1644 ,'A_W2_ROTH_401K_PER_GRE_YTD'
1645 ,p_tax_unit_id
1646 ,'00-000-0000'
1647 ,0);
1648 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information24 := ln_roth_401k_contribution;
1649 -- 'Designated Roth Contr. to 403b Plan' /* saurabh */
1650 ln_roth_403b_contribution :=
1651 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1652 ,'A_W2_ROTH_403B_PER_GRE_YTD'
1653 ,p_tax_unit_id
1654 ,'00-000-0000'
1655 ,0);
1656
1657 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information25 := ln_roth_403b_contribution;
1658 -- deferred compensation contributions to section 408(K)(6)
1659 ln_408k_contribution :=
1660 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1661 ,'A_W2_408K_PER_GRE_YTD'
1662 ,p_tax_unit_id
1663 ,'00-000-0000'
1664 ,0);
1665 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information12 := ln_408k_contribution;
1666
1667 /*--ln_roth_457b_contribution, Added for the Bug 16545887
1668 hr_utility.trace(' Getting A_W2_ROTH_457B_PER_GRE_YTD ' );
1669 ln_roth_457b_contribution :=
1670 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1671 ,'A_W2_ROTH_457B_PER_GRE_YTD'
1672 ,p_tax_unit_id
1673 ,'00-000-0000'
1674 ,0);*/
1675
1676 -- deferred compensation contributions to section 457(b)
1677 ln_457_contribution :=
1678 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1679 ,'A_W2_457_PER_GRE_YTD'
1680 ,p_tax_unit_id
1681 ,'00-000-0000'
1682 ,0);
1683 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information13 := ln_457_contribution;
1684 -- deferred compensation contributions to section 501(c)(18)(D)
1685 ln_501c_contribution :=
1686 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1687 ,'A_W2_501C_PER_GRE_YTD'
1688 ,p_tax_unit_id
1689 ,'00-000-0000'
1690 ,0);
1691 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information14 :=
1692 ln_501c_contribution;
1693 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',50);
1694 -- Deferred compensation contributions
1695 -- This need to be clarified
1696 ln_total_contribution := ln_401k_contribution +
1697 ln_403b_contribution +
1698 ln_408k_contribution +
1699 ln_457_contribution +
1700 ln_501c_contribution;
1701 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information15 :=
1702 ln_total_contribution;
1703 --
1704 -- Military employees basic quarters, subsistence and combat pay
1705 -- This field is not report in FED W2 That is why field is initialized with 0
1706 --
1707 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information16 := 0;
1708 -- nonqualified plan section 457 distributions or contributions
1709 ln_nonqual_457 := hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1710 ,'A_W2_NONQUAL_457_PER_GRE_YTD'
1711 ,p_tax_unit_id
1712 ,'00-000-0000'
1713 ,0);
1714 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information17 :=
1715 ln_nonqual_457;
1716
1717 -- nonqualified plan not section 457 distributions or contributions
1718 ln_nonqual_plan :=
1719 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1720 ,'A_W2_NONQUAL_PLAN_PER_GRE_YTD'
1721 ,p_tax_unit_id
1722 ,'00-000-0000'
1723 ,0);
1724 if ln_nonqual_plan > ln_nonqual_457 then
1725 ln_non_qual_not457 := ln_nonqual_plan - ln_nonqual_457;
1726 end if;
1727 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information18 :=
1728 ln_non_qual_not457;
1729 -- employer cost of premiums for GTL over $50000
1730 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information19 :=
1731 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1732 ,'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD'
1733 ,p_tax_unit_id
1734 ,'00-000-0000'
1735 ,0);
1736 -- income from the exercise of nonstatutory stock options
1737 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information20 :=
1738 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1739 ,'A_W2_NONQUAL_STOCK_PER_GRE_YTD'
1740 ,p_tax_unit_id
1741 ,'00-000-0000'
1742 ,0);
1743
1744 -- ER contribution to Health Savings Account
1745 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information21 :=
1746 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1747 ,'A_W2_HSA_PER_GRE_YTD'
1748 ,p_tax_unit_id
1749 ,'00-000-0000'
1750 ,0);
1751 -- Non Combat Pay , for bug 4398606
1752 hr_utility.trace(' Getting Non combat pay ' );
1753 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information22 :=
1754 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1755 ,'A_W2_NONTAX_COMBAT_PER_GRE_YTD'
1756 ,p_tax_unit_id
1757 ,'00-000-0000'
1758 ,0);
1759 hr_utility.trace('Nontax Combat ' || pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information22);
1760
1761
1762 -- 409A Deferrals , for bug 4398606
1763 hr_utility.trace(' Getting NonQual Def Comp ' );
1764 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information23 :=
1765 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1766 ,'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD'
1767 ,p_tax_unit_id
1768 ,'00-000-0000'
1769 ,0);
1770 hr_utility.trace('NonQual Def Comp ' || pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information23);
1771
1772 --Added for the Bug 16545887
1773 -- A_W2_PENSION_PLAN_PER_GRE_YTD
1774 hr_utility.trace(' Getting A_W2_PENSION_PLAN_PER_GRE_YTD ' );
1775 l_w2_pension :=
1776 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1777 ,'A_W2_PENSION_PLAN_PER_GRE_YTD'
1778 ,p_tax_unit_id
1779 ,'00-000-0000'
1780 ,0);
1781 hr_utility.trace('A_W2_PENSION_PLAN_PER_GRE_YTD ' || l_w2_pension);
1782
1783 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',60);
1784 -- Statutory Employee Indicator
1785 lv_statutory_employee :=
1786 hr_us_w2_rep.get_per_item(p_assignment_action_id,'A_W2_ASG_STATUTORY_EMPLOYEE');
1787 if lv_statutory_employee = 'Y' then
1788 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).statutory_emp_indicator := '1';
1789 else
1790 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).statutory_emp_indicator := '0';
1791 end if;
1792 hr_utility.set_location( gv_package || '.GET_ARCHIVED_VALUES',70);
1793 --
1794 -- Retirement Plan Indicator
1795 -- If any of the contribution is > 0 then retirement plan indicator is set to 1
1796 -- otherwise 0
1797 -- Mofified for the Bug 16545887
1798
1799 if ((ln_401k_contribution > 0) OR
1800 (ln_403b_contribution > 0) OR
1801 (ln_408k_contribution > 0) OR
1802 (ln_501c_contribution > 0) OR
1803 (l_w2_pension > 0) OR
1804 (ln_roth_401k_contribution >0) OR
1805 (ln_roth_403b_contribution >0))
1806 then
1807 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).retirement_plan_indicator := '1';
1808 else
1809 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).retirement_plan_indicator := '0';
1810 end if;
1811 -- Third Party Sick Pay Indicator
1812 ln_3rd_party :=
1813 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1814 ,'A_W2_TP_SICK_PAY_PER_GRE_YTD'
1815 ,p_tax_unit_id
1816 ,'00-000-0000'
1817 ,0);
1818 /* We should be using the Wages for Third Party Sick Pay Indicator because
1819 an employee can have wages and be marked for exempt ie no taxes withheld.
1820 If there are wages we should be reporting the employee */
1821 /*
1822 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1823 ,'A_FIT_3RD_PARTY_PER_GRE_YTD'
1824 ,p_tax_unit_id
1825 ,'00-000-0000'
1826 ,0);
1827 */
1828 if ln_3rd_party > 0 then
1829 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).sick_pay_indicator := '1';
1830 else
1831 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).sick_pay_indicator := '0';
1832 end if;
1833 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',80);
1834
1835 -- Cost of Employer-Sponsored Health Coverage, Bug 13255427
1836 hr_utility.trace(' Getting Cost of Employer-Sponsored Health Coverage ' );
1837 pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information26 :=
1838 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1839 ,'A_W2_HEALTH_COVERAGE_PER_GRE_YTD'
1840 ,p_tax_unit_id
1841 ,'00-000-0000'
1842 ,0);
1843 hr_utility.trace('Cost of Employer-Sponsored Health Coverage ' || pay_us_w2c_in_mmref2_format.ltr_rcw_info(i).action_information26);
1844
1845 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',90);
1846
1847 ELSIF p_record_type = 'RCO' then
1848 --{
1849 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',100);
1850 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information1 := 0; -- allocated tips
1851 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information2 := 0; -- uncollected employee tax on tips
1852 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information3 := 0; -- Medical Savings Account
1853 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information4 := 0; -- Simple Retirement Account
1854 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information5 := 0; -- Qualified adoption expenses
1855 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information6 := 0; -- uncollected social security or RRTA tax on GTL insurance over $50000
1856 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information7 := 0; -- uncollected medicare tax on GTL insurance over $50,000
1857 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information8 := 0; -- uncollected medicare tax on GTL insurance over $50,000
1858 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information9 := 0; -- Hire Exempt Wages amd Tips --9783383
1859 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information10 := 0; -- Design. Roth Contributions Under 457b Plan, bug 13255427
1860 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',105);
1861
1862 -- allocated tips
1863 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information1 :=
1864 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1865 ,'A_W2_BOX_8_PER_GRE_YTD'
1866 ,p_tax_unit_id
1867 ,'00-000-0000'
1868 ,0);
1869 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',110);
1870 -- uncollected employee tax on tips
1871 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information2 :=
1872 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1873 ,'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD'
1874 ,p_tax_unit_id
1875 ,'00-000-0000'
1876 ,0) +
1877 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1878 ,'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD'
1879 ,p_tax_unit_id
1880 ,'00-000-0000'
1881 ,0);
1882 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',120);
1883 -- Medical Savings Account
1884 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information3 :=
1885 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1886 ,'A_W2_MSA_PER_GRE_YTD'
1887 ,p_tax_unit_id
1888 ,'00-000-0000'
1889 ,0);
1890 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',130);
1891 -- Simple Retirement Account
1892 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information4 :=
1893 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1894 ,'A_W2_408P_PER_GRE_YTD'
1895 ,p_tax_unit_id
1896 ,'00-000-0000'
1897 ,0);
1898 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',140);
1899 -- Qualified adoption expenses
1900 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information5 :=
1901 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1902 ,'A_W2_ADOPTION_PER_GRE_YTD'
1903 ,p_tax_unit_id
1904 ,'00-000-0000'
1905 ,0);
1906 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',150);
1907 -- uncollected social security or RRTA tax on GTL insurance over $50000
1908 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information6 :=
1909 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1910 ,'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD'
1911 ,p_tax_unit_id
1912 ,'00-000-0000'
1913 ,0);
1914 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',160);
1915 -- uncollected medicare tax on GTL insurance over $50,000
1916 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information7 :=
1917 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1918 ,'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD'
1919 ,p_tax_unit_id
1920 ,'00-000-0000'
1921 ,0);
1922 -- 409A Income , for bug 4398606
1923 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information8 :=
1924 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1925 ,'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD'
1926 ,p_tax_unit_id
1927 ,'00-000-0000'
1928 ,0);
1929 hr_utility.trace('getting 409A Income for RCO record ' ||pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information8);
1930
1931 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',170);
1932
1933 --Hire Exempt Wages and Tips 9783383
1934 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information9 :=
1935 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1936 ,'A_SS_ER_W11_TAXABLE_PER_GRE_YTD'
1937 ,p_tax_unit_id
1938 ,'00-000-0000'
1939 ,0);
1940
1941 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',180);
1942
1943 --Design. Roth Contributions Under 457b Plan, bug 13255427
1944 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information10 :=
1945 hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1946 ,'A_W2_ROTH_457B_PER_GRE_YTD'
1947 ,p_tax_unit_id
1948 ,'00-000-0000'
1949 ,0);
1950
1951 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',190);
1952
1953 /*hr_utility.trace('calling with normal variable');
1954 a:= hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id
1955 ,'A_SS_ER_W11_TAXABLE_PER_GRE_YTD'
1956 ,p_tax_unit_id
1957 ,'00-000-0000'
1958 ,0);
1959 hr_utility.trace('Action Type '||p_action_type);
1960 hr_utility.trace('Action Type '||p_assignment_action_id);
1961 hr_utility.trace('Action Type '||p_tax_unit_id);
1962 begin
1963 pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information9 :=to_char(a);
1964 EXCEPTION
1965 WHEN OTHERS then
1966 --dbms_output.PUT_LINE('got it!1');
1967
1968 hr_utility.trace('Error!');
1969 hr_utility.trace('Error encountered in '||gv_package || '.pay_us_w2c_RCW_record');
1970 tmp:=sqlerrm;
1971 hr_utility.trace('Error: '||tmp);
1972 commit;
1973 --hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',190);
1974 END;
1975 --pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information9 :=21640;
1976 hr_utility.trace('Hire exmpt value in variable '||a);
1977 hr_utility.trace('GRE ID '||p_tax_unit_id); */
1978
1979 hr_utility.trace('getting Hire exempt wages for RCO record '||pay_us_w2c_in_mmref2_format.ltr_rco_info(i).action_information9);
1980 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',180);
1981 -- End of fetching Archived values RCO
1982 --}
1983 END IF; -- p_record_type check
1984 --
1985 EXCEPTION
1986 WHEN OTHERS then
1987 --dbms_output.PUT_LINE('got it!1');
1988
1989 hr_utility.trace('Error!');
1990 hr_utility.trace('Error encountered in '||gv_package || '.pay_us_w2c_RCW_record');
1991 hr_utility.trace('Error: '||sqlerrm);
1992 hr_utility.set_location(gv_package || '.GET_ARCHIVED_VALUES',190);
1993 END GET_ARCHIVED_VALUES;
1994 -- End of Get_Archived_Values function
1995 --
1996
1997 -- Following Function used to compare Originally Reported values and Corrected
1998 -- values on RCW or RCO record and format the record
1999 FUNCTION pay_us_w2c_RCW_record ( p_effective_date IN varchar2,
2000 p_report_type IN varchar2,
2001 p_format IN varchar2,
2002 p_report_qualifier IN varchar2,
2003 p_record_name IN varchar2,
2004 p_tax_unit_id IN varchar2,
2005 p_record_identifier IN varchar2,
2006 p_ssn IN varchar2,
2007 p_first_name IN varchar2,
2008 p_middle_name IN varchar2,
2009 p_last_name IN varchar2,
2010 p_sufix IN varchar2,
2011 p_location_address IN varchar2,
2012 p_delivery_address IN varchar2,
2013 p_city IN varchar2,
2014 p_state IN varchar2,
2015 p_zip IN varchar2,
2016 p_zip_extension IN varchar2,
2017 p_foreign_state IN varchar2,
2018 p_foreign_postal_code IN varchar2,
2019 p_country_code IN varchar2,
2020 p_orig_assignment_actid IN varchar2,
2021 p_correct_assignment_actid IN varchar2,
2022 p_employee_number IN varchar2,
2023 p_format_type IN varchar2,
2024 p_validate IN varchar2,
2025 p_exclude_from_output OUT nocopy varchar2,
2026 sp_out_1 OUT nocopy varchar2,
2027 sp_out_2 OUT nocopy varchar2,
2028 sp_out_3 OUT nocopy varchar2,
2029 sp_out_4 OUT nocopy varchar2,
2030 sp_out_5 OUT nocopy varchar2,
2031 ret_str_len OUT nocopy varchar2,
2032 p_error OUT nocopy boolean
2033 ) return varchar2
2034 IS
2035
2036 lv_action_type varchar2(10) := '';
2037 lv_record_type varchar2(10) := '';
2038 ln_num_corrections number := 0;
2039 ln_num_corrections_rcw number := 0;
2040 ln_num_corrections_rco number := 0;
2041 ln_num_corrections_rct number := 0;
2042 ln_num_corrections_rcu number := 0;
2043 lv_old_ssn varchar2(100) := '';
2044 lv_new_ssn varchar2(100) := '';
2045 lv_ssn_identical_flag varchar2(10) := 'Y';
2046 lv_first_name_old varchar2(200) := ' ';
2047 lv_middle_name_old varchar2(200) := ' ';
2048 lv_last_name_old varchar2(200) := ' ';
2049 lv_statutory_emp_indicator_old varchar2(10) := ' ';
2050 lv_statutory_emp_indicator_new varchar2(10) := ' ';
2051 lv_retire_plan_indicator_old varchar2(10) := ' ';
2052 lv_retire_plan_indicator_new varchar2(10) := ' ';
2053 lv_sickpay_indicator_old varchar2(10) := ' ';
2054 lv_sickpay_indicator_new varchar2(10) := ' ';
2055 rcw_return_value varchar2(32467) := lpad(' ',1024);
2056 rco_return_value varchar2(32467) := lpad(' ',1024);
2057 lb_rco_error boolean := FALSE;
2058 lv_rco_ret_str_len varchar2(100) := '0';
2059 lv_rco_exclude_from_output varchar2(100) := 'N';
2060 return_value_blank varchar2(32767);
2061 TYPE wage_value IS RECORD( p_parameter_name varchar2(100),
2062 p_parameter_value_old number,
2063 p_parameter_value_new number
2064 );
2065 function_parameter_rec wage_value;
2066 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
2067 INDEX BY BINARY_INTEGER;
2068 parameter_record input_parameter_record;
2069
2070 rcw_compared_rec pay_us_w2c_in_mmref2_format.table_wage_record;
2071 rco_compared_rec pay_us_w2c_in_mmref2_format.table_wage_record;
2072
2073 l_rcw_neg_flag boolean := FALSE;
2074 l_rco_neg_flag boolean := FALSE;
2075
2076 BEGIN
2077 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 10);
2078 ln_num_corrections_rcw := 0;
2079 ln_num_corrections_rco := 0;
2080 -- Employee Level Global Variables are initialized here
2081
2082 pay_us_w2c_in_mmref2_format.rcw_exclude_flag := 'N';
2083 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'N';
2084 pay_us_w2c_in_mmref2_format.rcw_mf_record := '';
2085 pay_us_w2c_in_mmref2_format.rcw_csv_record := '';
2086 pay_us_w2c_in_mmref2_format.rcw_blank_csv_record := '';
2087 pay_us_w2c_in_mmref2_format.rco_mf_record := '';
2088 pay_us_w2c_in_mmref2_format.rco_csv_record := '';
2089 pay_us_w2c_in_mmref2_format.rco_blank_csv_record := '';
2090 pay_us_w2c_in_mmref2_format.rcw_number_of_correction := 0;
2091 pay_us_w2c_in_mmref2_format.rco_number_of_correction := 0;
2092 --
2093 -- Set Originally Reported RCW values
2094 -- This procedure call will set the value in PL/SQL session table
2095 -- pay_us_w2c_in_mmref2_format.ltr_rcw_info. Orginally reported value
2096 -- would be stored in 1st row of the Pl/SQL table
2097 lv_action_type := 'O';
2098 lv_record_type := 'RCW';
2099 pay_us_w2c_in_mmref2_format.ltr_rcw_info.delete;
2100 hr_utility.trace('Calling Procedure pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES to set RCW record ');
2101 pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES ( lv_action_type
2102 ,lv_record_type
2103 ,p_orig_assignment_actid
2104 ,p_tax_unit_id
2105 );
2106 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 20);
2107 --
2108 -- Set Corrected RCW values in PL/SQL table pay_us_w2c_in_mmref2_format.ltr_rcw_info
2109 -- Corrected value would be stored in 2nd row of the Pl/SQL table
2110 --
2111 lv_action_type := 'C';
2112 pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES ( lv_action_type
2113 ,p_record_name
2114 ,p_correct_assignment_actid
2115 ,p_tax_unit_id
2116 );
2117 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 30);
2118 --
2119 -- Compare Orginally Reported and Corrected values to decide whether RCW record
2120 -- would be written to mf file or moved to .a02.
2121 --
2122
2123 -- Compare Orignally Reported and Corrected <SSN>
2124 lv_old_ssn := replace(replace(replace(replace(
2125 replace(pay_us_reporting_utils_pkg.character_check(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).SSN),
2126 ' '),'I'),'-'),'.'),'''');
2127 lv_new_ssn := replace(replace(replace(replace(
2128 replace(pay_us_reporting_utils_pkg.character_check(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).SSN),
2129 ' '),'I'),'-'),'.'),'''');
2130 if NVL(lv_old_ssn,'ZZ') <> NVL(lv_new_ssn,'ZZ')
2131 then
2132 ln_num_corrections := ln_num_corrections + 1;
2133 lv_ssn_identical_flag := 'N';
2134 else
2135 lv_ssn_identical_flag := 'Y';
2136 lv_old_ssn := lpad(' ',9);
2137 end if;
2138 hr_utility.trace('SSN Comparision completed ');
2139 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 40);
2140 --
2141 -- Compare Orignally Reported and Corrected <First Name>
2142 --
2143 lv_first_name_old := ' ';
2144 if (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).first_name is not null
2145 and pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).first_name is null)
2146 then
2147 ln_num_corrections := ln_num_corrections + 1;
2148 lv_first_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).first_name;
2149 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).first_name is not null
2150 and pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).first_name is null) then
2151 ln_num_corrections := ln_num_corrections + 1;
2152 lv_first_name_old := ' ';
2153 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).first_name <>
2154 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).first_name) then
2155 ln_num_corrections := ln_num_corrections + 1;
2156 lv_first_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).first_name;
2157 end if;
2158 hr_utility.trace('First Name Comparision completed ');
2159 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 50);
2160 --
2161 -- Compare Orignally Reported and Corrected <Middle Name>
2162 --
2163 lv_middle_name_old := ' ';
2164 if (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name is not null
2165 and pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).middle_name is null)
2166 then
2167 ln_num_corrections := ln_num_corrections + 1;
2168 lv_middle_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name;
2169 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).middle_name is not null
2170 and pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name is null) then
2171 ln_num_corrections := ln_num_corrections + 1;
2172 elsif (substr(pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).middle_name,1,1) <>
2173 substr(pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name,1,1)) then
2174 ln_num_corrections := ln_num_corrections + 1;
2175 lv_middle_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name;
2176 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).middle_name <>
2177 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name) then
2178 ln_num_corrections := ln_num_corrections + 1;
2179 lv_middle_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name;
2180 end if;
2181 hr_utility.trace('Middle Name Comparision completed ');
2182 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 60);
2183 --
2184 -- Compare Orignally Reported and Corrected <Last Name>
2185 --
2186 lv_last_name_old := ' ';
2187 if (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).last_name is not null
2188 and pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).last_name is null)
2189 then
2190 ln_num_corrections := ln_num_corrections + 1;
2191 lv_last_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).last_name;
2192 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).last_name is not null
2193 and pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).last_name is null) then
2194 ln_num_corrections := ln_num_corrections + 1;
2195 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).last_name <>
2196 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).last_name) then
2197 ln_num_corrections := ln_num_corrections + 1;
2198 lv_last_name_old := pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).last_name;
2199 end if;
2200 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 70);
2201 hr_utility.trace('Last Name Comparision completed ');
2202 --
2203 -- Compare Orignally Reported and Corrected <Statutory Employee Indicator>
2204 --
2205 if (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).statutory_emp_indicator = '0' and
2206 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).statutory_emp_indicator = '0') then
2207 --{
2208 lv_statutory_emp_indicator_old := ' ';
2209 lv_statutory_emp_indicator_new := ' ';
2210 --}
2211 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).statutory_emp_indicator <>
2212 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).statutory_emp_indicator) then
2213 --{
2214 hr_utility.trace(' Statutory Employee Indicator Values are not equal ');
2215 ln_num_corrections := ln_num_corrections + 1;
2216 lv_statutory_emp_indicator_old :=
2217 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).statutory_emp_indicator;
2218 lv_statutory_emp_indicator_new :=
2219 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).statutory_emp_indicator;
2220 --}
2221 else
2222 lv_statutory_emp_indicator_old := ' ';
2223 lv_statutory_emp_indicator_new := ' ';
2224 end if;
2225
2226 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 80);
2227
2228 -- Compare Orignally Reported and Corrected <Retirement Plan Indicator>
2229 if (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).retirement_plan_indicator ='0' and
2230 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).retirement_plan_indicator ='0') then
2231 --{
2232 lv_retire_plan_indicator_old := ' ';
2233 lv_retire_plan_indicator_new := ' ';
2234 --}
2235 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).retirement_plan_indicator <>
2236 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).retirement_plan_indicator) then
2237 --{
2238 hr_utility.trace(' Retirement Plan Indicator Values are not equal ');
2239 ln_num_corrections := ln_num_corrections + 1;
2240 lv_retire_plan_indicator_old :=
2241 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).retirement_plan_indicator;
2242 lv_retire_plan_indicator_new :=
2243 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).retirement_plan_indicator;
2244 --}
2245 else
2246 lv_retire_plan_indicator_old := ' ';
2247 lv_retire_plan_indicator_new := ' ';
2248 end if;
2249 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 90);
2250
2251 -- Compare Orignally Reported and Corrected < Third Part SickPay Indicator>
2252 if (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).sick_pay_indicator = '0' and
2253 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).sick_pay_indicator ='0' ) then
2254 --{
2255 lv_sickpay_indicator_old := ' ';
2256 lv_sickpay_indicator_new := ' ';
2257 --}
2258 elsif (pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).sick_pay_indicator <>
2259 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).sick_pay_indicator) then
2260 --{
2261 hr_utility.trace(' Third Party Sick Pay Indicator Values are not equal ');
2262 ln_num_corrections := ln_num_corrections + 1;
2263 lv_sickpay_indicator_old :=
2264 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).sick_pay_indicator;
2265 lv_sickpay_indicator_new :=
2266 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).sick_pay_indicator;
2267 --}
2268 else
2269 lv_sickpay_indicator_old := ' ';
2270 lv_sickpay_indicator_new := ' ';
2271 end if;
2272 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 100);
2273 --
2274 -- Following section is to compare all Wage values of RCW Record
2275 --
2276 parameter_record.delete;
2277
2278 parameter_record(1).p_parameter_name:= ' Wages,Tips And Other Compensation';
2279 parameter_record(1).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information1;
2280 parameter_record(1).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information1;
2281
2282 parameter_record(2).p_parameter_name:= ' Federal Income Tax Withheld';
2283 parameter_record(2).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information2;
2284 parameter_record(2).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information2;
2285
2286 parameter_record(3).p_parameter_name:= 'SS Wages';
2287 parameter_record(3).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information3;
2288 parameter_record(3).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information3;
2289
2290 parameter_record(4).p_parameter_name:= ' Social Security Tax Withheld';
2291 parameter_record(4).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information4;
2292 parameter_record(4).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information4;
2293
2294 parameter_record(5).p_parameter_name:= 'Medicare Wages And Tips';
2295 parameter_record(5).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information5;
2296 parameter_record(5).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information5;
2297
2298 parameter_record(6).p_parameter_name:= 'Medicare Tax Withheld';
2299 parameter_record(6).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information6;
2300 parameter_record(6).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information6;
2301
2302 parameter_record(7).p_parameter_name:= 'SS Tips';
2303 parameter_record(7).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information7;
2304 parameter_record(7).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information7;
2305
2306 parameter_record(8).p_parameter_name:= 'Advance Earned Income Credit';
2307 parameter_record(8).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information8;
2308 parameter_record(8).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information8;
2309
2310 parameter_record(9).p_parameter_name:= 'Dependent Care Benefits';
2311 parameter_record(9).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information9;
2312 parameter_record(9).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information9;
2313
2314 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
2315 parameter_record(10).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information10;
2316 parameter_record(10).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information10;
2317
2318 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
2319 parameter_record(11).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information11;
2320 parameter_record(11).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information11;
2321
2322 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
2323 parameter_record(12).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information12;
2324 parameter_record(12).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information12;
2325
2326 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
2327 parameter_record(13).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information13;
2328 parameter_record(13).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information13;
2329
2330 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
2331 parameter_record(14).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information14;
2332 parameter_record(14).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information14;
2333
2334 parameter_record(15).p_parameter_name:= 'Deferred Compensation Contribution ';
2335 parameter_record(15).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information15;
2336 parameter_record(15).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information15;
2337
2338 parameter_record(16).p_parameter_name:= 'Military Combat Pay';
2339 parameter_record(16).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information16;
2340 parameter_record(16).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information16;
2341
2342 parameter_record(17).p_parameter_name:= 'Non-Qual. plan Sec 457';
2343 parameter_record(17).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information17;
2344 parameter_record(17).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information17;
2345
2346 parameter_record(18).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
2347 parameter_record(18).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information18;
2348 parameter_record(18).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information18;
2349
2350 parameter_record(19).p_parameter_name:= 'Employer cost of premiun';
2351 parameter_record(19).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information19;
2352 parameter_record(19).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information19;
2353
2354 parameter_record(20).p_parameter_name:= 'Income from nonqualified stock option';
2355 parameter_record(20).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information20;
2356 parameter_record(20).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information20;
2357
2358 parameter_record(21).p_parameter_name:= 'ER Contribution to HSA';
2359 parameter_record(21).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information21;
2360 parameter_record(21).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information21;
2361
2362 parameter_record(22).p_parameter_name:= 'Nontaxable Combat Pay';
2363 parameter_record(22).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information22;
2364 parameter_record(22).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information22;
2365
2366 parameter_record(23).p_parameter_name:= 'Nonqual 409A Deferral Amount';
2367 parameter_record(23).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information23;
2368 parameter_record(23).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information23;
2369
2370 parameter_record(24).p_parameter_name:= 'Designed Roth Contr. to 401k Plan'; /* saurabh */
2371 parameter_record(24).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information24;
2372 parameter_record(24).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information24;
2373
2374 parameter_record(25).p_parameter_name:= 'Designed Roth Contr. to 403b Plan'; /* saurabh */
2375 parameter_record(25).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information25;
2376 parameter_record(25).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information25;
2377 -- hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 110);
2378
2379 --Changes for the Bug 13255427, added W2 health Coverage
2380
2381 parameter_record(26).p_parameter_name:= 'Cost of Employer-Sponsored Health Coverage';
2382 parameter_record(26).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).action_information26;
2383 parameter_record(26).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).action_information26;
2384
2385 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 110);
2386
2387 -- Delete all value from PL/SQL table used for storing RCW record wage values
2388 -- after comparision. This table normally stores 20 wage values
2389
2390 rcw_compared_rec.delete;
2391
2392 l_rcw_neg_flag := FALSE;
2393
2394 for m in 1..26 loop --Bug 13255427 /* saurabh */
2395
2396 if ((parameter_record(m).p_parameter_value_old < 0 ) OR
2397 (parameter_record(m).p_parameter_value_new < 0 )) then
2398
2399 l_rcw_neg_flag := TRUE;
2400 exit;
2401
2402 end if;
2403
2404 end loop;
2405
2406 --
2407 -- This loop will be used to compare all the wage data associated with RCW
2408 --
2409 FOR k in 1..26 --Bug 13255427 /* saurabh */
2410 LOOP
2411 /*
2412 if (parameter_record(k).p_parameter_value_old = 0 and
2413 parameter_record(k).p_parameter_value_new = 0) then
2414 */
2415
2416 if (parameter_record(k).p_parameter_value_old =
2417 parameter_record(k).p_parameter_value_new ) then
2418 --{
2419 rcw_compared_rec(k).identical_flag := 'Y';
2420 rcw_compared_rec(k).wage_old_value := 0;
2421 rcw_compared_rec(k).wage_new_value := 0;
2422 rcw_compared_rec(k).wage_old_value_formated := lpad(' ',11);
2423 rcw_compared_rec(k).wage_new_value_formated := lpad(' ',11);
2424 --}
2425 elsif parameter_record(k).p_parameter_value_old <>
2426 parameter_record(k).p_parameter_value_new
2427 then
2428 --{
2429 hr_utility.trace(to_char(k)||'. '||parameter_record(k).p_parameter_name ||'Value are not equal ');
2430
2431 ln_num_corrections := ln_num_corrections + 1;
2432
2433 if NOT l_rcw_neg_flag then
2434
2435 -- These values will be used for formating RCT record
2436
2437 if ( parameter_record(k).p_parameter_value_old > 0 AND
2438 parameter_record(k).p_parameter_value_new >= 0 ) then
2439
2440 pay_us_w2c_in_mmref2_format.ltr_rct_info(k).rct_wage_old :=
2441 pay_us_w2c_in_mmref2_format.ltr_rct_info(k).rct_wage_old +
2442 (parameter_record(k).p_parameter_value_old*100);
2443 end if;
2444
2445 if parameter_record(k).p_parameter_value_new > 0 then
2446
2447 pay_us_w2c_in_mmref2_format.ltr_rct_info(k).rct_wage_new :=
2448 pay_us_w2c_in_mmref2_format.ltr_rct_info(k).rct_wage_new +
2449 (parameter_record(k).p_parameter_value_new*100);
2450 end if;
2451
2452 end if; /* l_rcw_neg_flag */
2453
2454
2455 -- These values will be used for formatting RCW record
2456 rcw_compared_rec(k).wage_old_value :=
2457 to_char(trunc(parameter_record(k).p_parameter_value_old*100));
2458 rcw_compared_rec(k).wage_new_value :=
2459 to_char(trunc(parameter_record(k).p_parameter_value_new*100));
2460 rcw_compared_rec(k).identical_flag := 'N';
2461 --}
2462
2463 /*
2464 else
2465 -- These values will be used for formatting RCW record
2466 rcw_compared_rec(k).identical_flag := 'Y';
2467 rcw_compared_rec(k).wage_old_value := 0;
2468 rcw_compared_rec(k).wage_new_value := 0;
2469 rcw_compared_rec(k).wage_old_value_formated := lpad(' ',11);
2470 rcw_compared_rec(k).wage_new_value_formated := lpad(' ',11);
2471 */
2472
2473 end if;
2474 END LOOP;
2475 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 120);
2476 ln_num_corrections_rcw := ln_num_corrections;
2477
2478 --
2479 -- Set Originally Reported RCO values
2480 -- This procedure call will set the value in PL/SQL session table
2481 -- pay_us_w2c_in_mmref2_format.ltr_rco_info. Orginally reported value
2482 -- would be stored in 1st row of the Pl/SQL table
2483
2484 lv_action_type := 'O';
2485 lv_record_type := 'RCO';
2486
2487 pay_us_w2c_in_mmref2_format.ltr_rco_info.delete;
2488
2489 hr_utility.trace('Calling pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES'
2490 || 'to set Originally reported RCO record values');
2491
2492 pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES ( lv_action_type
2493 ,lv_record_type
2494 ,p_orig_assignment_actid
2495 ,p_tax_unit_id
2496 );
2497 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 130);
2498 -- Set Corrected RCO values in PL/SQL table
2499 -- pay_us_w2c_in_mmref2_format.ltr_rco_info
2500 -- Corrected value would be stored in 2nd row of the Pl/SQL table
2501 --
2502
2503 lv_action_type := 'C';
2504 hr_utility.trace('Calling pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES'
2505 || 'to set Corrected RCO record values');
2506
2507 pay_us_w2c_in_mmref2_format.GET_ARCHIVED_VALUES ( lv_action_type
2508 ,lv_record_type
2509 ,p_correct_assignment_actid
2510 ,p_tax_unit_id
2511 );
2512 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 140);
2513
2514 --
2515 -- Following section is to compare all Wage values of RCO Record
2516 --
2517 -- Delete the Temp parameter table
2518
2519 parameter_record.delete;
2520
2521 -- Initialize temp prameter table for comparing RCO Wages
2522
2523 parameter_record(1).p_parameter_name:= ' allocated tips';
2524 parameter_record(1).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information1;
2525 parameter_record(1).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information1;
2526
2527 parameter_record(2).p_parameter_name:= ' uncollected employee tax on tips';
2528 parameter_record(2).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information2;
2529 parameter_record(2).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information2;
2530
2531 parameter_record(3).p_parameter_name:= ' Medical Savings Account';
2532 parameter_record(3).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information3;
2533 parameter_record(3).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information3;
2534
2535 parameter_record(4).p_parameter_name:= ' Simple Retirement Account';
2536 parameter_record(4).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information4;
2537 parameter_record(4).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information4;
2538
2539 parameter_record(5).p_parameter_name:= ' Qualified adoption expenses';
2540 parameter_record(5).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information5;
2541 parameter_record(5).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information5;
2542
2543 parameter_record(6).p_parameter_name:= 'uncollected social security or RRTA tax on GTL insurance over $50000';
2544 parameter_record(6).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information6;
2545 parameter_record(6).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information6;
2546
2547 parameter_record(7).p_parameter_name:= 'uncollected medicare tax on GTL insurance over $50,000';
2548 parameter_record(7).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information7;
2549 parameter_record(7).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information7;
2550
2551 parameter_record(8).p_parameter_name:= 'income under 409A';
2552 parameter_record(8).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information8;
2553 parameter_record(8).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information8;
2554
2555 hr_utility.trace('Old value of income under 409A '||parameter_record(8).p_parameter_value_old);
2556 hr_utility.trace('New value of income under 409A '||parameter_record(8).p_parameter_value_new);
2557
2558 --9783383
2559 parameter_record(9).p_parameter_name:= 'Hire Exempt Wages and Tips';
2560 parameter_record(9).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information9;
2561 parameter_record(9).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information9;
2562
2563
2564 -- Changes for Bug 13255427, Added W2 Roth 457b
2565
2566 parameter_record(10).p_parameter_name:= 'Desig. Roth Contributions Under 457b Plan';
2567 parameter_record(10).p_parameter_value_old:=pay_us_w2c_in_mmref2_format.ltr_rco_info(1).action_information10;
2568 parameter_record(10).p_parameter_value_new:=pay_us_w2c_in_mmref2_format.ltr_rco_info(2).action_information10;
2569
2570 hr_utility.trace('Old Value of Hire exempt'||parameter_record(9).p_parameter_value_old||' '||lv_action_type);
2571 hr_utility.trace('New Value of Hire exempt'||parameter_record(9).p_parameter_value_new||' '||lv_action_type);
2572 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record', 150);
2573
2574 -- Delete all value from PL/SQL table used for storing RCO record wage values
2575 -- after comparision. This table normally stores 7 wage values
2576
2577 rco_compared_rec.delete;
2578
2579 l_rco_neg_flag := FALSE;
2580
2581 for m in 1..10 loop --Bug 13255427 -- Increased from 8 to 9 --9783383
2582
2583 if ((parameter_record(m).p_parameter_value_old < 0 ) OR
2584 (parameter_record(m).p_parameter_value_new < 0 )) then
2585
2586 l_rco_neg_flag := TRUE;
2587 exit;
2588
2589 end if;
2590
2591 end loop;
2592
2593 --
2594 -- This loop will be used to compare all the wage data associated with RCO
2595 --
2596 hr_utility.trace('Comparing RCO Values of Originally reported and Corrected W2');
2597
2598 FOR k in 1..10 LOOP --Bug 13255427 -- Increased from 8 to 9 --9783383
2599
2600 if (parameter_record(k).p_parameter_value_old =
2601 parameter_record(k).p_parameter_value_new ) then
2602 --{
2603 rco_compared_rec(k).identical_flag := 'Y';
2604 rco_compared_rec(k).wage_old_value := 0;
2605 rco_compared_rec(k).wage_new_value := 0;
2606 rco_compared_rec(k).wage_old_value_formated := lpad(' ', 11);
2607 rco_compared_rec(k).wage_new_value_formated := lpad(' ', 11);
2608 --}
2609 elsif parameter_record(k).p_parameter_value_old <>
2610 parameter_record(k).p_parameter_value_new
2611 then
2612 --{
2613 hr_utility.trace(to_char(k)||'. '||parameter_record(k).p_parameter_name ||'Value are not equal ');
2614
2615 ln_num_corrections := ln_num_corrections + 1;
2616
2617 if NOT l_rco_neg_flag then
2618
2619 -- These values will be used for formating RCU record
2620 if (parameter_record(k).p_parameter_value_old > 0 AND
2621 parameter_record(k).p_parameter_value_new >= 0 )then
2622
2623 pay_us_w2c_in_mmref2_format.ltr_rcu_info(k).rcu_wage_old :=
2624 pay_us_w2c_in_mmref2_format.ltr_rcu_info(k).rcu_wage_old +
2625 (parameter_record(k).p_parameter_value_old*100);
2626
2627 end if;
2628
2629 if parameter_record(k).p_parameter_value_new > 0 then
2630
2631 pay_us_w2c_in_mmref2_format.ltr_rcu_info(k).rcu_wage_new :=
2632 pay_us_w2c_in_mmref2_format.ltr_rcu_info(k).rcu_wage_new +
2633 (parameter_record(k).p_parameter_value_new*100);
2634
2635 end if;
2636
2637 end if; /* l_rco_neg_flag */
2638
2639 -- These values will be used for formatting RCO record
2640 rco_compared_rec(k).wage_old_value :=
2641 to_char(trunc(parameter_record(k).p_parameter_value_old*100));
2642 rco_compared_rec(k).wage_new_value :=
2643 to_char(trunc(parameter_record(k).p_parameter_value_new*100));
2644 rco_compared_rec(k).identical_flag := 'N';
2645 --}
2646
2647 /*
2648 else
2649 -- These values will be used for formatting RCO record
2650 rco_compared_rec(k).identical_flag := 'Y';
2651 rco_compared_rec(k).wage_old_value := 0;
2652 rco_compared_rec(k).wage_new_value := 0;
2653 rco_compared_rec(k).wage_old_value_formated := lpad(' ', 11);
2654 rco_compared_rec(k).wage_new_value_formated := lpad(' ', 11);
2655 */
2656
2657 end if;
2658 END LOOP;
2659
2660 hr_utility.trace('Comparision for RCO values completed ');
2661 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',160);
2662
2663 ln_num_corrections_rco := ln_num_corrections -ln_num_corrections_rcw;
2664
2665 pay_us_w2c_in_mmref2_format.rcw_number_of_correction := ln_num_corrections_rcw;
2666
2667 pay_us_w2c_in_mmref2_format.rco_number_of_correction := ln_num_corrections_rco;
2668
2669 hr_utility.trace('RCW No of Corrections '||to_char(ln_num_corrections_rcw));
2670 hr_utility.trace('RCO No of Corrections '||to_char(ln_num_corrections_rco));
2671 hr_utility.trace('Before RCW Format and Validation p_exclude_from_output '||p_exclude_from_output);
2672
2673 pay_us_w2c_in_mmref2_format.rcw_number_of_correction := ln_num_corrections_rcw;
2674
2675 pay_us_w2c_in_mmref2_format.rco_number_of_correction := ln_num_corrections_rco;
2676
2677 -- This section Validates and Formats RCW Record
2678 -- Call to format_W2C_RCW_record to format RCW record
2679 --
2680 rcw_return_value := pay_us_mmrf2_w2c_format_record.format_W2C_RCW_record (
2681 p_effective_date,
2682 p_report_type,
2683 p_format,
2684 p_report_qualifier,
2685 p_record_name,
2686 p_tax_unit_id,
2687 p_record_identifier,
2688 lv_old_ssn,
2689 lv_new_ssn,
2690 lv_first_name_old,
2691 lv_middle_name_old,
2692 lv_last_name_old,
2693 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).first_name,
2694 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).middle_name,
2695 pay_us_w2c_in_mmref2_format.ltr_rcw_info(1).last_name,
2696 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).first_name,
2697 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).middle_name,
2698 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).last_name,
2699 p_location_address,
2700 p_delivery_address,
2701 p_city,
2702 p_state,
2703 p_zip,
2704 p_zip_extension,
2705 p_foreign_state,
2706 p_foreign_postal_code,
2707 p_country_code,
2708 lv_statutory_emp_indicator_old,
2709 lv_statutory_emp_indicator_new,
2710 lv_retire_plan_indicator_old,
2711 lv_retire_plan_indicator_new,
2712 lv_sickpay_indicator_old,
2713 lv_sickpay_indicator_new,
2714 p_orig_assignment_actid,
2715 p_correct_assignment_actid,
2716 p_employee_number,
2717 rcw_compared_rec,
2718 p_format_type,
2719 p_validate,
2720 p_exclude_from_output,
2721 ret_str_len,
2722 p_error
2723 );
2724 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',165);
2725
2726 if p_error then
2727 hr_utility.trace('RCW Format and Validation resulted ERROR with p_exclude_from_output '||p_exclude_from_output);
2728 else
2729 hr_utility.trace('RCW Format and Validation resulted NO Error with p_exclude_from_output '||p_exclude_from_output);
2730 end if;
2731 hr_utility.trace('Before RCO Format and Validation p_exclude_from_output '||lv_rco_exclude_from_output);
2732
2733 -- This section Validates and Formats RCO Record
2734 -- Call to format_W2C_RCO_record to set Originally reported formattted values for reporting
2735 if ln_num_corrections_rco > 0 then
2736 rco_return_value := pay_us_mmrf2_w2c_format_record.format_W2C_RCO_record
2737 (
2738 p_effective_date,
2739 p_report_type,
2740 p_format,
2741 p_report_qualifier,
2742 'RCO',
2743 p_tax_unit_id,
2744 'RCO',
2745 lv_new_ssn,
2746 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).first_name,
2747 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).middle_name,
2748 pay_us_w2c_in_mmref2_format.ltr_rcw_info(2).last_name,
2749 p_orig_assignment_actid,
2750 p_correct_assignment_actid,
2751 p_employee_number,
2752 rco_compared_rec,
2753 p_format_type,
2754 p_validate,
2755 lv_rco_exclude_from_output,
2756 lv_rco_ret_str_len,
2757 lb_rco_error
2758 );
2759
2760
2761 if lb_rco_error then
2762 hr_utility.trace('RCO Format and Validation resulted ERROR with p_exclude_from_output '||lv_rco_exclude_from_output);
2763 else
2764 hr_utility.trace('RCO Format and Validation resulted NO Error with p_exclude_from_output '||lv_rco_exclude_from_output);
2765 end if;
2766 else
2767 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',168);
2768 -- Format Blank RCO record in MMREF-2 format
2769 -- This Blank record would be used when RCO record is moved to .a02 for error
2770 --{
2771
2772 hr_utility.trace('Formatting BLANK RCO Record ');
2773 return_value_blank := ','
2774 ||' '
2775 ||','||lpad(' ',9)
2776 ||','||' '
2777 ||','||' '
2778 ||','||' '
2779 ||','||' '
2780 ||','||' '
2781 ||','||' '
2782 ||','||' '
2783 ||','||' '
2784 ||','||' '
2785 ||','||' '
2786 ||','||' '
2787 ||','||' '
2788 ||','||' '
2789 ||','||' '
2790 ||','||' '
2791 ||','||' '
2792 ||','||' '
2793 ||','||' ';
2794 pay_us_w2c_in_mmref2_format.rco_blank_csv_record := return_value_blank;
2795 --}
2796 lb_rco_error := FALSE;
2797 lv_rco_exclude_from_output := 'Y';
2798 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'Y';
2799 end if;
2800
2801 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',170);
2802 if NOT p_error then
2803 --{
2804 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',180);
2805 hr_utility.trace('RCW formating and Validation was successful');
2806 --
2807 -- This section increments the counter to be reported in RCT record for Valid
2808 -- or Error Record
2809 -- Valid condtion is atleast one value of RCW is corrected for given employee
2810 -- Error condition is not even a single value is corrected for the employee
2811 --
2812 if ln_num_corrections_rcw > 0 then
2813 -- RCT Valid Record Total
2814 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct :=
2815 pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rct + 1;
2816 pay_us_w2c_in_mmref2_format.rcw_exclude_flag := 'N';
2817 -- RCF Valid Record Total
2818 -- pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rcf :=
2819 -- pay_us_w2c_in_mmref2_format.number_of_valid_rcw_rcf + 1;
2820
2821 else
2822 -- RCT Error Record Total
2823 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct :=
2824 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct + 1;
2825 pay_us_w2c_in_mmref2_format.rcw_exclude_flag := 'Y';
2826 -- RCF Error Record Total
2827 -- pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf :=
2828 -- pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf + 1;
2829
2830 end if;
2831 --}
2832 else
2833 --{
2834 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',190);
2835 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct :=
2836 pay_us_w2c_in_mmref2_format.number_of_error_rcw_rct + 1;
2837 pay_us_w2c_in_mmref2_format.rcw_exclude_flag := 'Y';
2838 -- RCF Error Record Total
2839 -- pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf :=
2840 -- pay_us_w2c_in_mmref2_format.number_of_error_rcw_rcf + 1;
2841 --}
2842 end if;
2843
2844 if NOT lb_rco_error and NOT l_rcw_neg_flag then
2845 --{
2846 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',200);
2847 hr_utility.trace('RCO formating and Validation was successful');
2848 --
2849 -- This section increments the counter to be reported in RCU record for Valid
2850 -- or Error Record
2851 -- Valid condtion is atleast one value of RCO is corrected for given employee
2852 -- Error condition is not even a single value is corrected for the employee
2853 --
2854 if ln_num_corrections_rco > 0 then
2855 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',205);
2856 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu :=
2857 pay_us_w2c_in_mmref2_format.number_of_valid_rco_rcu + 1;
2858 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'N';
2859 else
2860 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',206);
2861 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu :=
2862 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu + 1;
2863 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'Y';
2864 end if;
2865 --}
2866 else
2867 --{
2868 hr_utility.set_location(gv_package || '.pay_us_w2c_RCW_record',210);
2869 hr_utility.trace('RCO formating and Validation ERROR out');
2870 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu :=
2871 pay_us_w2c_in_mmref2_format.number_of_error_rco_rcu + 1;
2872 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'Y';
2873 --}
2874 end if;
2875 if lv_rco_exclude_from_output = 'Y'
2876 then
2877 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'Y';
2878 else
2879 pay_us_w2c_in_mmref2_format.rco_exclude_flag := 'N';
2880 end if;
2881 -- return Flat format RCW record
2882 hr_utility.trace('RCW Exlcude from output Flag '||pay_us_w2c_in_mmref2_format.rcw_exclude_flag);
2883 hr_utility.trace('RCO Exlcude from output Flag '||pay_us_w2c_in_mmref2_format.rco_exclude_flag);
2884 hr_utility.trace('Number of Values corrected '||to_char(ln_num_corrections));
2885 hr_utility.trace(gv_package || '.pay_us_w2c_RCW_record'||' successfully completed');
2886 return (rcw_return_value);
2887 Exception
2888 WHEN OTHERS THEN
2889 hr_utility.trace('Error encountered in '||gv_package || '.pay_us_w2c_RCW_record');
2890 hr_utility.trace('Error: '||sqlerrm);
2891 END pay_us_w2c_RCW_record;
2892
2893 --
2894 -- End of Procedure to get the Archived values of RCW/RCO record
2895 --
2896 --BEGIN
2897 --hr_utility.trace_on(null,'W2CGAV');
2898 END pay_us_w2c_in_mmref2_format;