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