[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MMRF_W2_FORMAT_RECORD
Source
1 PACKAGE BODY pay_us_mmrf_w2_format_record AS
2 /* $Header: pyusw2fr.pkb 120.47.12010000.4 2008/11/06 06:47:29 svannian ship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_mmrf_w2_format_record
12
13 Purpose
14 The purpose of this package is to format reacord to support the
15 generation of W2 magnetic tape for US legilsative requirements.
16
17 Notes
18 Referenced By: Package pay_us_reporting_utils_pkg
19
20 History
21
22 14-Jul-03 ppanda 115.0 Created
23 18-Sep-03 ppanda 115.2 3150687 STATE W2 MAG RS RECORDS ARE ONLY 487 POSITIONS
24 RS record formatting function uses a local variable
25 l_last_field which is 25 spaces. This field was not
26 initialized. This was causing the RS record length as 487.
27 30-OCT-03 ppanda 115.3 3135857 Reporting Period for SC no longer required. Position
28 197-202 on RS record blank filled.
29 01-NOV-03 ppanda 115.5 Function set_req_field_length modified for W-2c Mag requirement
30
31 08-NOV-03 tmehra 115.6 2084851 Maryland State Pickup Pos 298-307 change
32 20-NOV-03 ppanda 115.7 3130999 RA positions 12 - 216 NR blank fill
33 274 - 295 NR blank fill
34 356 - 512 NR blank fill
35 RE position 7 NR blank fill
36 17 - 39 NR blank fill
37 97 - 118 NR blank fill
38 179 - 512 NR blank fill
39 RS position 73 - 94 NR blank fill
40 150 - 194 NR blank fill
41 274 - 275 NR blank fill
42 22-NOV-03 sodhingr 115.8 Removed show_errors to fix GSCC failure
43 25-NOV-03 ppanda 115.9 3067494 Kansas City, MO Local RS reocrd formating changed
44 for few fields
45 02-DEC-03 ppanda 115.10 3293083 Lousiana state W-2 is not reporting submitter
46 Delivery address, City, State, Zip and Zip code
47 extension.
48 08-NOV-03 tmehra 115.11 2084851 Zero filled the Maryland State Pickup Pos 298-307
49 08-DEC-03 ppanda 115.9 3299126 For LA state W2 RE record : 97- 118(Employer Address)
50 Not required
51 26-DEC-03 ppanda 115.10 3337295 For PR RO, RS and RT record specification changed
52 Refer Bug Descriotion for the details of record changes.
53 20-AUG-04 tmehra 115.11 3534769 Added support for the Maryland state pickup.
54
55 27-OCT-04 rsethupa 115.15 3895206 NJ DIPP Plan ID - Only last 14 characters are
56 required
57 RS Positions 339-352
58 3936924 For State of AL
59 RE positions 170-173 - zip code extension
60 401-410 - Alabama withholding tax acct number
61 (Right justify zero fill)
62 RS positions 248-257 - State ER In Number
63 258-268 - Federal Employer Id Number
64 393-396 - Payment year
65 28-OCT-04 rsethupa 115.16 3936924 Removed redundant local variables. Added comments.
66
67 02-NOV-04 rsethupa 115.17 3180532 IN EIC changes
68 RS positions 193 - 203 Box 9 - Adv Fed EIC
69 204 - 273 Blank fill
70 341 - 352 Box 19b - State Adv EIC
71 353 - 357 Box 20b - Adv EIC ID "INADV"
72 358 - 512 Blank fill
73 04-NOV-04 rsethupa 115.18 3186636 WV requires MMREF format from 2004. Formatting for
74 RE, RW and RS records done as per specifications.
75 3292989 ST Louis MO Local W-2 Mag
76 RA positions 12 - 28 blank fill
77 29 - 29 blank fill
78 30 - 35 blank fill
79 36 - 37 blank fill
80 3680056 New fields ER Contrib to a Health Savings Account has been
81 added to RW and RT records
82 RW positions 364-374
83 RT positions 250-264
84 18-NOV-04 ppanda 115.19 3180532 IN EIC changes
85 RS positions 341 - 352 Box 19b - State Adv EIC
86 353 - 357 Box 20b - Adv EIC ID "INADV"
87 These two values were lpaded with blanks. This was
88 causing the value to appear at position 396 instead
89 of 341.
90 18-NOV-04 ppanda 115.20 4016439 Dayton, OH Local Tape RA record changed
91 for preferred problem notification method.
92 24-NOV-04 rsethupa 115.21 3180532 Added local variable l_fl_field_17_20 for RS record
93 24-NOV-04 rsethupa 115.22 4022086 RS record for state of MS
94 positions
95 258-266 Federal Employer ID number
96 267 - blank
97 298-307 Federal Tax withheld
98 338-348 1099 Income
99 349-392 blanks
100 393-396 Payment Year
101 397-512 blanks
102 25-NOV-04 rsethupa 115.23 4014356 RS Record for NJ
103 Positions
104 308-310 Medical Malpractice
105 Insurance Premium Assistance
106 Assessment (MIF)
107 311-330 blanks
108 29-Nov-04 ppanda 115.24 4012469 RS record changed for
109 positions 3-4 STate Code
110 - now require numeric code "20"
111 positions 274-275 state code -
112 - now require numeric code "20"
113 positions 338-348 Employee
114 Contributions to Kansas Public
115 EE's Retirement System (KPERS,
116 KPF or Judges)
117 03-DEC-04 rsethupa 115.25 4045592 OH_CCAAA RS record
118 positions 338-412 is now blank
119 07-DEC-04 rsethupa 115.26 4052268 For WV RE record
120 positions
121 7 : Agent Indicator code
122 17-25 : Agent for EIN
123 31-39 : Other EIN
124 23-DEC-04 rsethupa 115.27 4084765 Added logic to display NJ DIPP Plan ID
125 even if it is less than 14 characters
126 04-APR-05 meshah 115.28 4279809 changed the r_input_34 for AL in
127 the RS record.
128 07-Nov-05 sudedas 115.29 4391218 RA,RW,RO,RT and RU W2 Format changed.
129 09-Nov-05 saurgupt 115.30 4720007 Postion 341-351 : RS record for IN moves by one
130 position.
131 09-Nov-05 kvsankar 115.31 4502738 RE, RW, RT record added for MO_KNSAS
132 Removed Code that was commented.
133 11-Nov-05 kvsankar 115.32 4730413 OH RS record SD has incorrect positioning
134 for SD code. It should be right justified and
135 blank filled in positions 331-337.
136 15-Nov-05 kvsankar 115.33 4728539 For RS 368-369 should be filled with ZERO's
137 for Maryland
138 16-Nov-05 kvsankar 115.34 4739790 Modified RE, RW and RS record for MO_KNSAS
139 29-Nov-05 sudedas 115.35 4665713 Modified RW, RO, RS and RT Record for Puerto Rico W2
140 4859212 Modified RW Pos: 342 - 352 and RT Pos: 220 - 234
141 17-Aug-06 sudedas 115.36 5256745 Modified RW Pos: 441 - 451 , Pos: 452 - 462
142 RT Pos: 370 - 384 , Pos: 385 - 399
143 RO Pos: 266 - 274.
144 4736977 Modified RS Record Pos: 368 - 369 for MD. Should now
145 report W4 Withholding Allowances for Maryland.
146 26-Oct-06 vmkulkar 115.37 5513076 Modified RS pos 73-94 , pos : 95-116 and pos : 357-359
147 for the state of INDIANA
148 03-Nov-06 vmkulkar 115.38 5513076 Modified RS pos 331-343 , pos : 344-354 and pos : 355-359
149 5637673 and 360-512 blanks for INDIANA
150 10-Nov-06 sausingh 115.39 5651314 Modified RS pos 298-307 for GEORGIA
151 14-Nov-06 sausingh 115.40 5651314 Removed (115.38=120.10) from
152 line 5 to remove GSCC compliencnce
153 14-Nov-06 sausingh 115.40 5651314 Modified RS pos 298-307 for GEORGIA
154 22-Nov-06 sudedas 115.41 5640748 Modified RE Record for State of Maryland.
155 28-Nov-06 vmkulkar 115.42 5668970 Modified
156 RO pos 265 civil status
157 RS 274-275
158 RU pos 55-69 zero for Puerto Rico(PR).
159 29-Nov-06 sudedas 115.43 5686164 Modified RE Record for Maryland , added nvl.
160 04-Dec-06 sudedas 115.44 5693183 RS Rec Pos: 248 - 267 Blank for GA.
161 14-dec-06 djoshi 115.45 5717304 RS record position 307 fixed for
162 KS and NJ
163
164 14-dec-06 djoshi 115.46 5717304 RS record position 307 fixed for
165 KS and NJ
166 15-Dec-06 sudedas 115.47 5717438 RS Record Pos : 298 - 307 Modified
167 for ELSE section.
168 (Earlier code only for GA)
169 28-Dec-06 alikhar 115.49 5696443 RS Record for GA modified. Two new fields.
170 Pos 338-412 (FEIN) and Pos 413-487 (SIT).
171 03-Jan-07 sudedas 115.50 5739737 Position 341 - 343 Modified for Indiana RS Record.
172 13-Jan-07 sudedas 115.51 5760355 Maryland RE Record Position: 273 - 294 should
173 5759976 report Address Line 1 (Employer Delivery Address)
174 16-Feb-07 vaprakas 115.52 5876054 Fixed mutilple issue in RS and RT records for PR
175 16-Feb-07 sudedas 115.53 5886247 City Name to be displayed for Tax Withheld in Ohio-RITA
176 26-Oct-07 svannian 115.55 6330489 RA RECORD Modified,
177 Position: 20-23 ,Software Vendor code(1334)
178 Position: 24-28 ,blank
179 RO RECORD Modified ,
180 Position: 265,blank
181 changes for the Federal and ALL STATES
182 02-Nov-07 svannian 115.56 5155648 For Transfer State FED and State PR, position 275-278 of
183 RO record and 408-418 of RW Record made to Zero.
184 22-Nov-07 sjawid 115.57 6641801 For State SC RS record Pos 197-202 modified to
185 115.58 populate Reporting period.
186 07-Dec-07 vmkulkar 115.59 6644795 Added function format_W2_RV_record
187 6648007
188 07-Dec-07 svannian 115.61 6648064 430-512 of Rw record made to blank fill for indiana
189 07-Dec-07 vmkulkar 115.64 PR RS Rec 338-347 Blank fill
190 10-Dec-07 svannian 115.65 6644795 For PR - RU RECORD 10 - 24 zero fill
191 40 - 29 zero fill
192 RO RECORD
193 34-99 zero fill
194 26-Dec-07 svannian 115.70 6684920 RA pos 172-512 made blank
195 RE pos 219 made blank
196 02-Jan-08 vmkulkar 115.73 6720630 RS 274-275 MI made blank
197 09-Jan-08 vmkulkar 115.75 6650931 Reverted back the CCA changes made in the last version.
198 21-apr-08 svannian 115.77 6855543 GA pos 340 - 482 changed as stated in bug 6855543
199 */
200
201 -- This function determines the required length for fields
202 -- in various data record. This function is being referenced
203 -- from DAT_VALIDATION function in package pay_us_reporting_utils_pkg
204 --
205 FUNCTION set_req_field_length (p_report_type IN varchar2,
206 p_format IN varchar2,
207 p_report_qualifier IN varchar2,
208 p_record_name IN varchar2,
209 p_input_1 IN varchar2,
210 p_input_2 IN varchar2,
211 p_input_3 IN varchar2,
212 p_input_4 IN varchar2,
213 p_input_5 IN varchar2
214 ) return NUMBER
215 IS
216 l_length number(10);
217 BEGIN
218 IF p_record_name in ('RU','RT','RF','RCU','RCT','RCF') THEN
219 --{
220 l_length := 15;
221 IF ( (p_record_name = 'RF' ) AND
222 (p_report_qualifier = 'CT') ) THEN
223 l_length := 16;
224 END IF;
225 --}
226 ELSIF p_record_name in ('RS','RO','RW','RCW','RCO') THEN
227 --{
228 l_length := 11;
229 IF ((p_record_name = 'RS') AND
230 (p_input_3 = 'Other State Data')
231 ) THEN
232 l_length := 10;
233 hr_utility.trace('RS. Other State Data.l_length = '||to_char(l_length));
234 ELSIF p_record_name = 'RS' THEN
235 --{
236 IF p_report_qualifier = 'NJ' THEN
237 IF ((p_input_3 = 'SDI Withheld') OR
238 (p_input_3 = 'Unemployment Insurance Tax')) THEN
239 l_length := 5;
240 ELSIF p_input_3 = 'Deferred Comp' THEN
241 l_length := 9;
242 ELSIF p_input_3 = 'MIF' THEN -- Bug 4014356
243 l_length := 3;
244 END IF;
245 END IF; --NJ
246 --}
247 END IF; --RS Record
248
249 /* Bug 5640748 */
250 /*
251 ELSIF p_record_name = 'RE' THEN
252 IF p_report_qualifier = 'MD' THEN
253 IF (( p_input_3 = 'Tot Withheld Tax Reported') OR
254 ( p_input_3 = 'SIT Withheld') OR
255 ( p_input_3 = 'MW508 ER Credits') OR
256 ( p_input_3 = 'MW508 ER Amt Tax Due') OR
257 ( p_input_3 = 'MW508 ER Amt Balance Due') OR
258 ( p_input_3 = 'MW508 ER Amt Overpayment') OR
259 ( p_input_3 = 'MW508 ER Amt Credit') OR
260 ( p_input_3 = 'MW508 ER Amt Refunded') OR
261 ( p_input_3 = 'State taxable Wages') OR
262 ( p_input_3 = 'Other State Data')
263 ) THEN
264 l_length := 12 ;
265 END IF ; -- p_input_3
266 END IF ; -- MD
267 */
268 --}
269 END IF; -- p_record_name
270 return l_length;
271 END set_req_field_length; -- End of set_req_field_length
272 --
273
274 PROCEDURE format_w2_contact_prsn_info (
275 p_report_qualifier IN varchar2,
276 p_record_name IN varchar2,
277 p_validate IN varchar2,
278 p_exclude_from_output IN OUT nocopy varchar2,
279 p_contact_prsn_name IN OUT nocopy varchar2,
280 p_contact_prsn_phone IN OUT nocopy varchar2,
281 p_contact_prsn_extension IN OUT nocopy varchar2,
282 p_contact_prsn_email IN OUT nocopy varchar2,
283 p_contact_prsn_fax IN OUT nocopy varchar2,
284 p_contact_prsn_first_name IN OUT nocopy varchar2,
285 p_contact_prsn_middle_name IN OUT nocopy varchar2,
286 p_contact_prsn_last_name IN OUT nocopy varchar2)
287 IS
288 BEGIN
289 IF p_contact_prsn_name is NULL THEN
290 --{
291 p_contact_prsn_name := lpad(' ',27);
292 p_contact_prsn_phone := lpad(' ',15);
293 p_contact_prsn_extension := lpad(' ',5);
294 p_contact_prsn_email := lpad(' ',40);
295 p_contact_prsn_fax := lpad(' ',10);
296 --}
297 ELSE
298 --{
299 p_contact_prsn_name :=rpad(substr(nvl(upper(p_contact_prsn_name),' '),1,27),27);
300 p_contact_prsn_email :=rpad(substr(nvl(upper(p_contact_prsn_email),' '),1,40),40);
301 p_contact_prsn_fax :=
302 rpad(substr(nvl(replace(p_contact_prsn_fax,' '),' '),1,10),10);
303 --}
304 END IF;
305 END format_w2_contact_prsn_info;
306 -- End of famatting Contact Person info used for reportin W2
307 --
308
309 --
310 -- Procedure to Format Employee and Employer Address
311 -- This procedure is being called from function GET_EE_ADDRESS
312 -- and GET_ER_ADDRESS
313 --
314 PROCEDURE format_mmref_address(
315 p_name IN varchar2,
316 p_locality_company_id IN varchar2,
317 p_emp_number IN varchar2,
318 p_address_line_1 IN varchar2,
319 p_address_line_2 IN varchar2,
320 p_address_line_3 IN varchar2,
321 p_town_or_city IN varchar2,
322 p_state IN varchar2,
323 p_postal_code IN varchar2,
324 p_country IN varchar2,
325 p_region_1 IN varchar2,
326 p_region_2 IN varchar2,
327 p_valid_address IN varchar2,
328 p_item_name IN varchar2,
329 p_report_type IN varchar2,
330 p_record_name IN varchar2,
331 p_validate IN varchar2,
332 p_local_code IN varchar2,
333 p_exclude_from_output OUT nocopy varchar2,
334 sp_out_1 IN OUT nocopy varchar2,
335 sp_out_2 IN OUT nocopy varchar2,
336 sp_out_3 IN OUT nocopy varchar2,
337 sp_out_4 IN OUT nocopy varchar2,
338 sp_out_5 IN OUT nocopy varchar2,
339 sp_out_6 IN OUT nocopy varchar2,
340 sp_out_7 IN OUT nocopy varchar2,
341 sp_out_8 IN OUT nocopy varchar2,
342 sp_out_9 IN OUT nocopy varchar2,
343 sp_out_10 IN OUT nocopy varchar2 ) IS
344 --
345 TYPE message_columns IS RECORD(
346 p_mesg_description varchar2(100),
347 p_mesg_value varchar2(100),
348 p_output_value varchar2(100));
349 message_parameter_rec message_columns;
350 TYPE message_parameter_record IS TABLE OF message_parameter_rec%TYPE
351 INDEX BY BINARY_INTEGER;
352 message_record message_parameter_record;
353
354 l_level varchar2(1);
355 l_mesg_name varchar2(50);
356 l_name_or_number varchar2(50);
357 l_err boolean := FALSE;
358 l_hyphen_position number(10);
359 c_item_name varchar2(100);
360 l_name varchar2(100);
361 l_location_addr varchar2(100);
362 l_delivery_addr varchar2(100);
363 l_State varchar2(100);
364 l_city varchar2(100);
365
366 BEGIN
367 c_item_name := p_item_name;
368 l_name := rpad(upper(substr(nvl(p_name,lpad(' ',57)),1,57)),57);
369 l_location_addr := nvl(rpad(replace(replace(upper(substr(ltrim
370 (p_address_line_2 ||' '||p_address_line_3), 1, 22))
371 ,',','_'),''''),22) ,lpad(' ',22));
372 l_delivery_addr := nvl(rpad(replace(replace(upper(substr(ltrim(
373 p_address_line_1),1,22)),',','_'),''''),22),lpad(' ',22));
374 l_State := upper(rpad(substr(p_state,1,2),2));
375 l_city := nvl(upper(rpad(substr(p_town_or_city, 1, 22), 22)),
376 lpad(' ',22));
377 -- Format for Valid Address
378 IF p_valid_address = 'Y' THEN
379 --{
380 hr_utility.trace('Valid Address found ');
381 hr_utility.trace('Location address '||l_location_addr);
382 hr_utility.trace('Delivery address '||l_delivery_addr);
383 hr_utility.trace('town_or_city '||l_city);
384 hr_utility.trace('postal_code '||p_postal_code);
385 hr_utility.trace('State '||l_state);
386 hr_utility.trace('p_country '||p_country);
387
388 IF c_item_name = 'EE_ADDRESS' THEN
389 l_level := 'A';
390 l_mesg_name := 'PAY_INVALID_EE_FORMAT';
391 l_name_or_number := p_emp_number;
392 ELSIF c_item_name = 'ER_ADDRESS' THEN
393 l_level := 'P';
394 l_mesg_name := 'PAY_INVALID_ER_FORMAT';
395 l_name_or_number := substr(p_name,1,50);
396 END IF;
397
398 message_record(1).p_mesg_description:='Invalid address.Address Line1 is null';
399 message_record(2).p_mesg_description:='Invalid address.City is null';
400 message_record(3).p_mesg_description:='Invalid address.State is null';
401 message_record(4).p_mesg_description:='Invalid address.Zip is null';
402 message_record(1).p_mesg_value:= l_delivery_addr;
403 message_record(2).p_mesg_value:= l_city;
404 message_record(3).p_mesg_value:= l_state;
405 message_record(4).p_mesg_value:= p_postal_code;
406
407 FOR i in 1..4 LOOP
408 IF message_record(i).p_mesg_value IS NULL THEN
409 pay_core_utils.push_message(801,l_mesg_name,l_level);
410 pay_core_utils.push_token('record_name', p_record_name);
411 pay_core_utils.push_token('name_or_number', l_name_or_number);
412 pay_core_utils.push_token('description',
413 message_record(i).p_mesg_description);
414 l_err:=TRUE;
415 END IF;
416 END LOOP;
417
418 sp_out_1 := l_location_addr;
419 sp_out_2 := l_delivery_addr;
420 sp_out_3 := l_city;
421
422 IF (p_country = 'US' OR p_country IS NULL )THEN
423 sp_out_9:= lpad(' ',2);
424 IF p_region_2 IS NOT NULL THEN
425 sp_out_4 := l_state; --State abbreviation
426 sp_out_7 := lpad(' ',23); --foreign state/province
427 ELSE --The region is null.
428 sp_out_4 := lpad(' ',2);
429 sp_out_7 := lpad(' ',23);
430 END IF;
431 ELSE -- country is not US
432 sp_out_4 := lpad(' ',2);
433 /* Bug:2133985 foreign state/province*/
434 sp_out_7 := upper(rpad(substr(nvl(p_region_1,' '),1,23),23));
435 sp_out_9:= upper(rpad(substr(p_country,1,2),2));
436 END IF;
437
438 /* See if the zip code has a zip code extension ie. contains a hyphen */
439
440 IF p_postal_code IS NOT NULL THEN
441 --{
442 l_hyphen_position := instr(p_postal_code, '-');
443
444 /* sp_out_5: zip code Len: 5
445 sp_out_6: zip code extension Len: 4
446 sp_out_8: foreign postal_code Len: 15 */
447
448 IF ( (p_country = 'US') OR ( p_country IS NULL ) ) THEN
449 IF l_hyphen_position = 0 THEN
450 sp_out_5:= upper(rpad(substr(p_postal_code,1,5),5));
451 sp_out_6 := lpad(' ', 4);
452 ELSE
453 sp_out_5:= upper(rpad(substr(substr
454 (p_postal_code,1,l_hyphen_position-1),1,5),5));
455 sp_out_6 := upper(rpad(substr(
456 p_postal_code,l_hyphen_position+1,4),4));
457 END IF;
458 sp_out_8:= lpad(' ',15);
459 ELSE /* ( (l_country = 'US') OR ( l_country IS NULL ) ) */
460 sp_out_5:= lpad(' ',5); --zip
461 sp_out_6:= lpad(' ', 4); --extension
462 sp_out_8:= upper(rpad(substr(p_postal_code,1,15),15)); --foreign zip
463 END IF;
464 --}
465 ELSE /* l_postal_code IS NULL.*/
466 --{
467 sp_out_5:= lpad(' ',5); --zip
468 sp_out_8:= lpad(' ',15); -- foreign zip
469 sp_out_6:= lpad(' ', 4); --extension
470 hr_utility.trace('Zip or Postal Code is null');
471 --}
472 END IF;
473 IF ((p_item_name = 'ER_ADDRESS') OR ( p_item_name = 'CR_ADDRESS')) THEN
474 sp_out_10:= p_name;
475 hr_utility.trace('Organization Name = '||p_name);
476 /* Locality_Company_Id */
477 IF ((p_item_name = 'ER_ADDRESS') and (p_local_code = 'PHILA')) THEN
478 sp_out_8:= lpad(NVL(p_locality_company_id,' '),15);
479 END IF;
480 ELSIF p_item_name = 'EE_ADDRESS' THEN
481 sp_out_10:= pay_us_reporting_utils_pkg.Character_check(p_emp_number);
482 END IF;
483 --}
484 --
485 -- when address is Invalid
486 --
487 ELSE
488 --{
489 IF p_item_name IN ('EE_ADDRESS',
490 'ER_ADDRESS',
491 'CR_ADDRESS') THEN
492 sp_out_1:=lpad(' ',22);
493 sp_out_2:=lpad(' ',22);
494 sp_out_3:=lpad(' ',22);
495 sp_out_4:=lpad(' ',2);
496 sp_out_5:=lpad(' ',5);
497 sp_out_6:=lpad(' ',9);
498 sp_out_7:=lpad(' ',23);
499 sp_out_8:=lpad(' ',15);
500 sp_out_9:=lpad(' ',2);
501 sp_out_10:=lpad(' ',57);
502 END IF;
503 IF ( (p_item_name = 'ER_ADDRESS')OR
504 (p_item_name = 'EE_ADDRESS')
505 ) THEN
506 l_err :=TRUE;
507 END IF;
508 --}
509 END IF; --p_valid_address
510 hr_utility.trace('location address '||sp_out_1);
511 hr_utility.trace('delivery address '||sp_out_2);
512 hr_utility.trace('City '||sp_out_3);
513 hr_utility.trace('State '||sp_out_4);
514 hr_utility.trace('Zip '||sp_out_5);
515 hr_utility.trace('Zip Code Extension '||sp_out_6);
516 hr_utility.trace('Foreign State/Province '||sp_out_7);
517 hr_utility.trace('Foreign Zip '||sp_out_8);
518 hr_utility.trace('Country '||sp_out_9);
519 IF (p_item_name = 'ER_ADDRESS') THEN
520 hr_utility.trace('Organization Name '||sp_out_10);
521 ELSE
522 hr_utility.trace('Employee Number '||sp_out_10);
523 END IF;
524 --
525 -- Check to include or exclude record on the basis of validity of address
526 --
527 IF p_validate = 'Y' THEN
528 IF l_err THEN
529 p_exclude_from_output := 'Y';
530 hr_utility.trace('p_validate is Y .error '||p_exclude_from_output);
531 END IF;
532 END IF;
533 IF p_exclude_from_output IS NULL THEN
534 p_exclude_from_output := 'N';
535 END IF;
536 END format_mmref_address; --End of Procedure Validate_address
537
538 -- Formatting RA record for W2 reporting
539 --
540 /*--------------------- Parameter mapping Starts. ----------------------
541 Record Identifier, --> p_input_1
542 Submitter''s Employer Identification Number (EIN), --> p_input_2
543 Personal Identification Number (PIN) --> p_input_3,
544 Resub Indicator --> p_input_4,
545 -- This is fix for bug # 2510920 to rename TLCN to WFID
546 Resub WFID --> p_input_5,
547 Software Code --> p_input_6,
548 Company Name --> p_input_7,
549 Location Address --> p_input_8,
550 Delivery Address --> p_input_9,
551 City --> p_input_10,
552 State Abbreviation --> p_input_11,
553 Zip Code --> p_input_12,
554 Zip Code Extension --> p_input_13,
555 Foreign State / Province --> p_input_14,
556 Foreign Postal Code --> p_input_15,
557 Country Code --> p_input_16,
558 Submitter Name --> p_input_17,
559 Location Address --> p_input_18,
560 Delivery Address --> p_input_19,
561 City --> p_input_20,
562 State Abbreviation --> p_input_21,
563 Zip Code --> p_input_22,
564 Zip Code Extension --> p_input_23,
565 Foreign State / Province --> p_input_24,
566 Foreing Postal Code --> p_input_25,
567 Country Code --> p_input_26,
568 Contact Name --> p_input_27,
569 Contact Phone Number --> p_input_28,
570 Contact Phone Extension --> p_input_29,
571 Contact E-Mail --> p_input_30,
572 Blank,Contact FAX --> p_input_31,
573 Preferred Method Of Problem Notification Code --> p_input_32,
574 Preparer Code --> p_input_33,
575 ------------------------ Parameter mapping Ends. -------------------------
576 */
577
578 FUNCTION format_W2_RA_record(
579 p_effective_date IN varchar2,
580 p_report_type IN varchar2,
581 p_format IN varchar2,
582 p_report_qualifier IN varchar2,
583 p_record_name IN varchar2,
584 p_input_1 IN varchar2,
585 p_input_2 IN varchar2,
586 p_input_3 IN varchar2,
587 p_input_4 IN varchar2,
588 p_input_5 IN varchar2,
589 p_input_6 IN varchar2,
590 p_input_7 IN varchar2,
591 p_input_8 IN varchar2,
592 p_input_9 IN varchar2,
593 p_input_10 IN varchar2,
594 p_input_11 IN varchar2,
595 p_input_12 IN varchar2,
596 p_input_13 IN varchar2,
597 p_input_14 IN varchar2,
598 p_input_15 IN varchar2,
599 p_input_16 IN varchar2,
600 p_input_17 IN varchar2,
601 p_input_18 IN varchar2,
602 p_input_19 IN varchar2,
603 p_input_20 IN varchar2,
604 p_input_21 IN varchar2,
605 p_input_22 IN varchar2,
606 p_input_23 IN varchar2,
607 p_input_24 IN varchar2,
608 p_input_25 IN varchar2,
609 p_input_26 IN varchar2,
610 p_input_27 IN varchar2,
611 p_input_28 IN varchar2,
612 p_input_29 IN varchar2,
613 p_input_30 IN varchar2,
614 p_input_31 IN varchar2,
615 p_input_32 IN varchar2,
616 p_input_33 IN varchar2,
617 p_input_34 IN varchar2,
618 p_input_35 IN varchar2,
619 p_input_36 IN varchar2,
620 p_input_37 IN varchar2,
621 p_input_38 IN varchar2,
622 p_input_39 IN varchar2,
623 p_input_40 IN varchar2,
624 p_validate IN varchar2,
625 p_exclude_from_output OUT nocopy varchar2,
626 sp_out_1 OUT nocopy varchar2,
627 sp_out_2 OUT nocopy varchar2,
628 sp_out_3 OUT nocopy varchar2,
629 sp_out_4 OUT nocopy varchar2,
630 sp_out_5 OUT nocopy varchar2,
631 ret_str_len OUT nocopy number,
632 p_error OUT nocopy boolean
633 ) RETURN VARCHAR2
634 IS
635 l_agent_indicator varchar2(1);
636 l_emp_ein varchar2(100);
637 l_agent_ein varchar2(100);
638 l_other_ein varchar2(100);
639 l_term_indicator varchar2(1);
640 l_exclude_from_output_chk boolean;
641 l_input_8 varchar2(50);
642 l_bus_tax_acct_number varchar2(50);
643 l_rep_qtr varchar2(300);
644 l_rep_prd varchar2(300);
645 l_end_of_rec varchar2(20);
646 return_value varchar2(32767);
647 l_pin varchar2(50);
648 l_pblm_code varchar2(1);
649 l_preparer_code varchar2(1);
650 p_end_of_rec varchar2(20) :=
651 fnd_global.local_chr(13)||fnd_global.local_chr(10);
652 r_input_1 varchar2(300);
653 r_input_2 varchar2(300);
654 r_input_3 varchar2(300);
655 r_input_4 varchar2(300);
656 r_input_5 varchar2(300);
657 r_input_6 varchar2(300);
658 r_input_7 varchar2(300);
659 r_input_8 varchar2(300);
660 r_input_9 varchar2(300);
661 r_input_10 varchar2(300);
662 r_input_11 varchar2(300);
663 r_input_12 varchar2(300);
664 r_input_13 varchar2(300);
665 r_input_14 varchar2(300);
666 r_input_15 varchar2(300);
667 r_input_16 varchar2(300);
668 r_input_17 varchar2(300);
669 r_input_18 varchar2(300);
670 r_input_19 varchar2(300);
671 r_input_20 varchar2(300);
672 r_input_21 varchar2(300);
673 r_input_22 varchar2(300);
674 r_input_23 varchar2(300);
675 r_input_24 varchar2(300);
676 r_input_25 varchar2(300);
677 r_input_26 varchar2(300);
678 r_input_27 varchar2(300);
679 r_input_28 varchar2(300);
680 r_input_29 varchar2(300);
681 r_input_30 varchar2(300);
682 r_input_31 varchar2(300);
683 r_input_32 varchar2(300);
684 r_input_33 varchar2(300);
685 r_input_34 varchar2(300);
686 r_input_35 varchar2(300);
687 r_input_36 varchar2(300);
688 r_input_37 varchar2(300);
689 r_input_38 varchar2(300);
690 r_input_39 varchar2(300);
691
692 BEGIN
693 hr_utility.trace('RA Record Formatting started ');
694 hr_utility.trace(' Format_W2_RA_Record Begin for Company '|| p_input_7);
695 -- Initializing local variables with parameter value
696 --{
697 r_input_2 := p_input_2;
698 r_input_3 := p_input_3;
699 r_input_4 := p_input_4;
700 r_input_5 := p_input_5;
701 r_input_6 := p_input_6;
702 r_input_7 := p_input_7;
703 r_input_8 := p_input_8;
704 r_input_9 := p_input_9;
705 r_input_10 := p_input_10;
706 r_input_11 := p_input_11;
707 r_input_12 := p_input_12;
708 r_input_13 := p_input_13;
709 r_input_14 := p_input_14;
710 r_input_15 := p_input_15;
711 r_input_16 := p_input_16;
712 r_input_17 := p_input_17;
713 r_input_18 := p_input_18;
714 r_input_19 := p_input_19;
715 r_input_20 := p_input_20;
716 r_input_21 := p_input_21;
717 r_input_22 := p_input_22;
718 r_input_23 := p_input_23;
719 r_input_24 := p_input_24;
720 r_input_25 := p_input_25;
721 r_input_26 := p_input_26;
722 r_input_27 := p_input_27;
723 r_input_28 := p_input_28;
724 r_input_29 := p_input_29;
725 r_input_30 := p_input_30;
726 r_input_31 := p_input_31;
727 r_input_32 := p_input_32;
728 r_input_33 := p_input_33;
729 r_input_34 := p_input_34;
730 r_input_35 := p_input_35;
731 r_input_36 := p_input_36;
732 r_input_37 := p_input_37;
733 r_input_38 := p_input_38;
734 r_input_39 := p_input_39;
735 --}
736 -- Validation Starts
737 -- EIN Validation
738 IF p_input_40 = 'FLAT' THEN
739 l_emp_ein :=
740 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
741 p_report_type,
742 p_format,
743 p_report_qualifier,
744 p_record_name,
745 'EIN',
746 p_input_2,
747 'Submitters EIN',
748 p_input_17,
749 null,
750 p_validate,
751 p_exclude_from_output,
752 sp_out_1,
753 sp_out_2);
754
755 IF p_exclude_from_output = 'Y' THEN
756 l_exclude_from_output_chk := TRUE;
757 END IF;
758 hr_utility.trace('Valid EIN '||l_emp_ein);
759 -- Validation Ends
760 --
761 -- Formatiing Starts
762
763 /* Pos:12 - 216 blank for PA. */
764 IF ((p_report_qualifier = 'PA') OR
765 (p_report_qualifier = 'LA'))
766 THEN
767 --{
768 l_pin := lpad(' ',17);
769 r_input_4 := lpad(' ',1);
770 r_input_5 := lpad(' ',6);
771 r_input_6 := lpad(' ',2);
772 r_input_7 := lpad(' ',57);
773 r_input_8 := lpad(' ',22);
774 r_input_9 := lpad(' ',22);
775 r_input_10 := lpad(' ',22);
776 r_input_11 := lpad(' ',2);
777 r_input_12 := lpad(' ',5);
778 r_input_13 := lpad(' ',4);
779 r_input_14 := lpad(' ',23);
780 r_input_15 := lpad(' ',15);
781 r_input_16 := lpad(' ',2);
782 r_input_24 := lpad(' ',23);
783 r_input_25 := lpad(' ',15);
784 r_input_26 := lpad(' ',2);
785 --}
786 ELSIF p_report_qualifier = 'MA' THEN
787 --{
788 l_pin := lpad(' ',17);
789 r_input_5 := lpad(' ',6);
790 r_input_6 := lpad(' ',2);
791 r_input_14 := lpad(' ',23);
792 r_input_15 := lpad(' ',15);
793 r_input_16 := lpad(' ',2);
794 r_input_24 := lpad(' ',23);
795 r_input_25 := lpad(' ',15);
796 r_input_26 := lpad(' ',2);
797 --}
798 ELSIF p_report_qualifier = 'CT' THEN
799 --{
800 l_pin := lpad(' ',17);
801 r_input_5 := lpad(' ',6);
802 r_input_6 := lpad(' ',2);
803 r_input_4 := lpad(' ',1);/* Bug: 2145032 */
804 --}
805 ELSIF (( p_report_qualifier = 'PA_PHILA') OR
806 ( p_report_qualifier = 'MO_STLOU')) THEN
807
808 l_pin := lpad(' ',17) ; -- Bug 4391218
809
810 ELSE
811 --{
812 /* Bug 4391218 Pos: 12 - 19 (8 characters) */
813 l_pin := rpad(substr(nvl(p_input_3,' '),1,8),17);
814 hr_utility.trace(' l_pin = '||l_pin);
815 --}
816 END IF;
817 /*6330489*/
818 IF p_report_qualifier not in ('MO_KNSAS','MO_STLOU','OH_CCAAA','OH_DAYTO','OH_RTCCA','PA_PHILA')
819 THEN
820 l_pin := rpad((rpad(substr(nvl(p_input_3 ,' '),1,8),8,' ') || '1334'),17);
821 END IF ;
822
823 IF p_report_qualifier = 'IN' THEN
824 /* 6684920 */
825 r_input_14 := lpad(' ',23);
826 r_input_15 := lpad(' ',15);
827 r_input_16 := lpad(' ',2);
828 r_input_17 := lpad(' ',57);
829 r_input_18 := lpad(' ',22);
830 r_input_19 := lpad(' ',22);
831 r_input_20 := lpad(' ',22);
832 r_input_21 := lpad(' ',2);
833 r_input_22 := lpad(' ',5);
834 r_input_23 := lpad(' ',5);
835 r_input_24 := lpad(' ',23);
836 r_input_25 := lpad(' ',15);
837 r_input_26 := lpad(' ',2);
838 r_input_27 := lpad(' ',27);
839 r_input_28 := lpad(' ',15);
840 r_input_29 := lpad(' ',5);
841 r_input_30 := lpad(' ',40);
842 r_input_31 := lpad(' ',10);
843 r_input_32 := lpad(' ',1);
844 r_input_33 := lpad(' ',1);
845 END IF;
846
847 /* Checking for preferred method of problem notification code which is
848 1=email, 2=postal service */
849 IF ((p_input_32 = '1' ) OR
850 (p_input_32 = '2' )
851 )
852 THEN
853 hr_utility.trace('Preferred method of code is correct. it is '||p_input_32);
854 l_pblm_code:= p_input_32;
855 ELSE
856 hr_utility.trace('Preferred method of code is incorrect. it is '||p_input_32);
857 l_pblm_code:= lpad('2',1);
858 END IF;
859 -- Bug # 2682428
860 --
861 -- commented to fix bug # 4016439
862 /*IF (p_report_qualifier = 'OH_DAYTO') THEN
863 l_pblm_code:= '2';
864 END IF;*/
865 -- Bug # 2736928
866 --
867 IF (p_report_qualifier = 'PR') THEN
868 l_pblm_code:= '2';
869 END IF;
870
871 If( (p_input_33 = 'A' )OR
872 (p_input_33 = 'S' )OR
873 (p_input_33 = 'L' )OR
874 (p_input_33 = 'P' )OR
875 (p_input_33 = 'O' )) THEN
876 l_preparer_code:= p_input_33;
877 hr_utility.trace('l_preparer_code is correct. it is '||p_input_33);
878 ELSE
879 l_preparer_code:= lpad(' ',1);
880 hr_utility.trace('l_preparer_code is incorrect. it is '||p_input_33);
881 END IF;
882
883 /* fix for bug # 2680189 */
884 IF (p_report_qualifier = 'PA_PHILA') THEN
885 r_input_3 := ' ';
886 r_input_4 := ' ';
887 r_input_5 := ' ';
888 r_input_6 := ' ';
889 r_input_7 := ' ';
890 END IF;
891
892 /* Fix for Bug # 2680070 and 2680189 */
893 IF ((p_report_qualifier = 'MO_STLOU') OR
894 (p_report_qualifier = 'PA_PHILA')) THEN
895 --{
896 r_input_8 := ' ';
897 r_input_9 := ' ';
898 r_input_10 := ' ';
899 r_input_11 := ' ';
900 r_input_12 := ' ';
901 r_input_13 := ' ';
902 r_input_14 := ' ';
903 r_input_15 := ' ';
904 r_input_16 := ' ';
905 --}
906 END IF;
907
908 /* Fix for Bug # 2680070 and #3130999 and # 3292989*/
909 IF (p_report_qualifier = 'MO_STLOU') THEN
910 l_pin := lpad(' ',17);
911 r_input_4 := ' ';
912 r_input_5 := ' ';
913 r_input_6 := ' ';
914 r_input_18 := ' ';
915 r_input_19 := ' ';
916 r_input_20 := ' ';
917 r_input_21 := ' ';
918 r_input_22 := ' ';
919 r_input_23 := ' ';
920 r_input_24 := ' ';
921 r_input_25 := ' ';
922 r_input_26 := ' ';
923 r_input_27 := ' ';
924 r_input_28 := ' ';
925 r_input_29 := ' ';
926 r_input_30 := ' ';
927 r_input_31 := ' ';
928 END IF;
929 -- Bug # 3293083
930 IF (p_report_qualifier = 'LA') THEN
931 r_input_18 := ' ';
932 r_input_24 := ' ';
933 r_input_25 := ' ';
934 r_input_26 := ' ';
935 r_input_27 := ' ';
936 r_input_28 := ' ';
937 r_input_29 := ' ';
938 r_input_30 := ' ';
939 r_input_31 := ' ';
940 END IF;
941
942 /* Fix for Bug # 2680070 */
943 IF p_report_qualifier = 'MO_STLOU' THEN
944 r_input_17 := ' ';
945 END IF;
946
947 IF ((p_report_qualifier = 'MA') OR
948 (p_report_qualifier = 'PA') OR
949 (p_report_qualifier = 'PA_PHILA') OR -- fix for bug # 2680189
950 (p_report_qualifier = 'CT') OR
951 (p_report_qualifier = 'LA') OR -- fix for bug # 3109990
952 (p_report_qualifier = 'MO_STLOU')OR
953 (p_report_qualifier = 'IN')) THEN /* 6684920 */
954 --{
955 l_pblm_code := lpad(' ',1);
956 l_preparer_code := lpad(' ',1);
957 --}
958 END IF;
959
960 -- Formatiing Ends
961 --
962
963 -- RA Record of Flat Type
964 --
965 return_value:='RA'
966 ||l_emp_ein||l_pin
967 ||rpad(substr(nvl(r_input_4,'0'),1,1),1)
968 ||rpad(substr(nvl(r_input_5,' '),1,6),6)
969 ||rpad(substr(nvl(r_input_6,' '),1,2),2)
970 ||rpad(substr(nvl(r_input_7,' '),1,57),57)
971 ||rpad(substr(nvl(r_input_8,' '),1,22),22)
972 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
973 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
974 ||rpad(substr(nvl(r_input_11,' '),1,2),2)
975 ||rpad(substr(nvl(r_input_12,' '),1,5),5)
976 ||rpad(substr(nvl(r_input_13,' '),1,4),9)
977 ||rpad(substr(nvl(r_input_14,' '),1,23),23)
978 ||rpad(substr(nvl(r_input_15,' '),1,15),15)
979 ||rpad(substr(nvl(r_input_16,' '),1,2),2)
980 ||rpad(substr(nvl(upper(r_input_17),' '),1,57),57)
981 ||rpad(substr(nvl(r_input_18,' '),1,22),22)
982 ||rpad(substr(nvl(r_input_19,' '),1,22),22)
983 ||rpad(substr(nvl(r_input_20,' '),1,22),22)
984 ||rpad(substr(nvl(r_input_21,' '),1,2),2)
985 ||rpad(substr(nvl(r_input_22,' '),1,5),5)
986 ||rpad(substr(nvl(r_input_23,' '),1,4),9)
987 ||rpad(substr(nvl(r_input_24,' '),1,23),23)
988 ||rpad(substr(nvl(r_input_25,' '),1,15),15)
989 ||rpad(substr(nvl(r_input_26,' '),1,2),2)
990 ||rpad(substr(nvl(r_input_27,' '),1,27),27)
991 ||rpad(substr(nvl(r_input_28,' '),1,15),15)
992 ||rpad(substr(nvl(r_input_29,' '),1,5),8)
993 ||rpad(substr(nvl(r_input_30,' '),1,43),43)
994 ||rpad(substr(nvl(r_input_31,' '),1,10),10)
995 ||l_pblm_code
996 ||l_preparer_code
997 ||lpad(' ',12)
998 ||l_end_of_rec ;
999 --
1000 hr_utility.trace('RA Record of FLAT Type -----');
1001 ret_str_len:=length(return_value);
1002
1003 ELSIF p_input_40 = 'CSV' THEN
1004 hr_utility.trace('RA Record of CSV Type formatting Starts ----');
1005 return_value:='RA'||','||p_input_2||','||p_input_3||','||lpad(' ',9) -- Bug# 4391218
1006 ||','||rpad(substr(nvl(p_input_4,'0'),1,1),1)
1007 ||','||rpad(substr(nvl(p_input_5,' '),1,6),6)
1008 ||','||rpad(substr(nvl(p_input_6,' '),1,2),2)
1009 ||','||rpad(substr(nvl(p_input_7,' '),1,57),57)
1010 ||','||rpad(substr(nvl(p_input_8,' '),1,22),22)
1011 ||','||rpad(substr(nvl(p_input_9,' '),1,22),22)
1012 ||','||rpad(substr(nvl(p_input_10,' '),1,22),22)
1013 ||','||rpad(substr(nvl(p_input_11,' '),1,2),2)
1014 ||','||rpad(substr(nvl(p_input_12,' '),1,5),5)
1015 ||','||rpad(substr(nvl(p_input_13,' '),1,4),4)
1016 ||','||lpad(' ',5)
1017 ||','||rpad(substr(nvl(p_input_14,' '),1,23),23)
1018 ||','||rpad(substr(nvl(p_input_15,' '),1,15),15)
1019 ||','||rpad(substr(nvl(p_input_16,' '),1,2),2)
1020 ||','||rpad(substr(upper(p_input_17),1,57),57)
1021 ||','||rpad(substr(nvl(p_input_18,' '),1,22),22)
1022 ||','||rpad(substr(nvl(p_input_19,' '),1,22),22)
1023 ||','||rpad(substr(nvl(p_input_20,' '),1,22),22)
1024 ||','||rpad(substr(nvl(p_input_21,' '),1,2),2)
1025 ||','||rpad(substr(nvl(p_input_22,' '),1,5),5)
1026 ||','||rpad(substr(nvl(p_input_23,' '),1,4),4)
1027 ||','||lpad(' ',5)
1028 ||','||rpad(substr(nvl(p_input_24,' '),1,23),23)
1029 ||','||rpad(substr(nvl(p_input_25,' '),1,15),15)
1030 ||','||rpad(substr(nvl(p_input_26,' '),1,2),2)
1031 ||','||rpad(substr(nvl(p_input_27,' '),1,27),27)
1032 ||','||rpad(substr(nvl(p_input_28,' '),1,15),15)
1033 ||','||rpad(substr(nvl(p_input_29,' '),1,5),5)
1034 ||','||lpad(' ',3)
1035 ||','||rpad(substr(nvl(p_input_30,' '),1,40),40)
1036 ||','||lpad(' ',3)
1037 ||','||rpad(substr(nvl(p_input_31,' '),1,10),10)
1038 ||','||p_input_32
1039 ||','||p_input_33
1040 ||','||lpad(' ',12);
1041 hr_utility.trace(' RA Record of CSV Type formatting Ends----');
1042 hr_utility.trace(return_value);
1043 END IF;
1044 p_error := l_exclude_from_output_chk;
1045 ret_str_len:=length(return_value);
1046 return return_value;
1047 END format_W2_RA_record; -- End of formatting W2 RA Record
1048
1049 --vmkulkar
1050 -- Formatting of RV Record Start
1051 FUNCTION format_W2_RV_record(
1052 p_effective_date IN varchar2,
1053 p_report_type IN varchar2,
1054 p_format IN varchar2,
1055 p_report_qualifier IN varchar2,
1056 p_record_name IN varchar2,
1057 p_input_1 IN varchar2,
1058 p_input_2 IN varchar2,
1059 p_input_3 IN varchar2,
1060 p_input_4 IN varchar2,
1061 p_input_5 IN varchar2,
1062 p_input_6 IN varchar2,
1063 p_input_7 IN varchar2,
1064 p_input_8 IN varchar2,
1065 p_input_9 IN varchar2,
1066 p_input_10 IN varchar2,
1067 p_input_11 IN varchar2,
1068 p_input_12 IN varchar2,
1069 p_input_13 IN varchar2,
1070 p_input_14 IN varchar2,
1071 p_input_15 IN varchar2,
1072 p_input_16 IN varchar2,
1073 p_input_17 IN varchar2,
1074 p_input_18 IN varchar2,
1075 p_input_19 IN varchar2,
1076 p_input_20 IN varchar2,
1077 p_input_21 IN varchar2,
1078 p_input_22 IN varchar2,
1079 p_input_23 IN varchar2,
1080 p_input_24 IN varchar2,
1081 p_input_25 IN varchar2,
1082 p_input_26 IN varchar2,
1083 p_input_27 IN varchar2,
1084 p_input_28 IN varchar2,
1085 p_input_29 IN varchar2,
1086 p_input_30 IN varchar2,
1087 p_input_31 IN varchar2,
1088 p_input_32 IN varchar2,
1089 p_input_33 IN varchar2,
1090 p_input_34 IN varchar2,
1091 p_input_35 IN varchar2,
1092 p_input_36 IN varchar2,
1093 p_input_37 IN varchar2,
1094 p_input_38 IN varchar2,
1095 p_input_39 IN varchar2,
1096 p_input_40 IN varchar2,
1097 p_validate IN varchar2,
1098 p_exclude_from_output OUT nocopy varchar2,
1099 sp_out_1 OUT nocopy varchar2,
1100 sp_out_2 OUT nocopy varchar2,
1101 sp_out_3 OUT nocopy varchar2,
1102 sp_out_4 OUT nocopy varchar2,
1103 sp_out_5 OUT nocopy varchar2,
1104 ret_str_len OUT nocopy number,
1105 p_error OUT nocopy boolean
1106 ) RETURN VARCHAR2
1107 IS
1108
1109
1110 /* Specifications for PR
1111
1112 1-2 Record Identifier --> p_input_1,
1113 3-12 Employer phone number --> p_input_2,
1114 13-17 Access Code --> p_input_3,
1115 18-32 Cost of Pension or Annuity --> p_input_4,
1116 33-47 Reimbursed Expenses --> p_input_5,
1117 48-62 Contributions to Qualified Lanns --> p_input_6,
1118 63-77 Salaries under act 324 of 2004 --> p_input_7,
1119 78-92 Uncollected Social Security Tax on Tips --> p_input_8,
1120 93-107 Uncollected Medicare Tax on Tips --> p_input_9,
1121 108-512 Blank */
1122
1123 /* Specifications for MD
1124
1125 1-2 Record identifier --> p_input_1
1126 3-4 State Code(24) --> p_input_2
1127 5-9 State record('MW508') --> p_input_3
1128 10-13 MW508 ER - Tax year --> p_input_4
1129 14-22 MW508 ER Id number --> p_input_5
1130 23-30 MW508 Central Registration --> p_input_6
1131 number(Tax Withholding Acct Number)
1132 31-87 MW508 ER Name --> p_input_7
1133 88-109 MW508 ER St Address --> p_input_8
1134 110-131 MW508 City --> p_input_9
1135 132-133 MW508 State --> p_input_10
1136 134-138 MW508 Zipcode --> p_input_11
1137 139-142 MW508 Zip Extension --> p_input_12
1138 143-148 MW508 ER Number of W2s --> p_input_13
1139 149-160 MW508 Total amt of taxes --> p_input_14
1140 161-172 MW508 ER Total Tax --> p_input_15
1141 173-187 MW508 ER Credits --> p_input_16
1142 185-196 MW508 ER amt tax due --> p_input_17
1143 total w/h amt due after credits
1144 197-208 MW508 ER amt balance due --> p_input_18
1145 209-220 MW508 ER amt overpayment --> p_input_19
1146 221-232 MW508 ER amt of overpayment --> p_input_20
1147 233-244 MW508 amt overpayment refunded --> p_input_21
1148 245-256 MW508 gross payroll --> p_input_22
1149 257-268 MW508 ER State pickup amt --> p_input_23
1150 269-296 ER Rep name --> p_input_24
1151 297-311 ER Rep title --> p_input_25
1152 312-319 ER Rep Date --> p_input_26
1153 320-329 ER Rep phone number --> p_input_27
1154 330-330 ER Total File Indicator "Y" --> p_input_28
1155 331-512 Blank */
1156
1157
1158
1159 r_input_1 varchar2(300);
1160 r_input_2 varchar2(300);
1161 r_input_3 varchar2(300);
1162 r_input_4 varchar2(300);
1163 r_input_5 varchar2(300);
1164 r_input_6 varchar2(300);
1165 r_input_7 varchar2(300);
1166 r_input_8 varchar2(300);
1167 r_input_9 varchar2(300);
1168 r_input_10 varchar2(300);
1169 r_input_11 varchar2(300);
1170 r_input_12 varchar2(300);
1171 r_input_13 varchar2(300);
1172 r_input_14 varchar2(300);
1173 r_input_15 varchar2(300);
1174 r_input_16 varchar2(300);
1175 r_input_17 varchar2(300);
1176 r_input_18 varchar2(300);
1177 r_input_19 varchar2(300);
1178 r_input_20 varchar2(300);
1179 r_input_21 varchar2(300);
1180 r_input_22 varchar2(300);
1181 r_input_23 varchar2(300);
1182 r_input_24 varchar2(300);
1183 r_input_25 varchar2(300);
1184 r_input_26 varchar2(300);
1185 r_input_27 varchar2(300);
1186 r_input_28 varchar2(300);
1187 r_input_29 varchar2(300);
1188 r_input_30 varchar2(300);
1189 r_input_31 varchar2(300);
1190 r_input_32 varchar2(300);
1191 r_input_33 varchar2(300);
1192 r_input_34 varchar2(300);
1193 r_input_35 varchar2(300);
1194 r_input_36 varchar2(300);
1195 r_input_37 varchar2(300);
1196 r_input_38 varchar2(300);
1197 r_input_39 varchar2(300);
1198 l_exclude_from_output_chk boolean;
1199 return_value varchar2(32767);
1200
1201
1202 BEGIN
1203 hr_utility.trace('RV Record Formatting started ');
1204 -- Initializing local variables with parameter value
1205 r_input_2 := p_input_2;
1206 r_input_3 := p_input_3;
1207 r_input_4 := p_input_4;
1208 r_input_5 := p_input_5;
1209 r_input_6 := p_input_6;
1210 r_input_7 := p_input_7;
1211 r_input_8 := p_input_8;
1212 r_input_9 := p_input_9;
1213 r_input_10 := p_input_10;
1214 r_input_11 := p_input_11;
1215 r_input_12 := p_input_12;
1216 r_input_13 := p_input_13;
1217 r_input_14 := p_input_14;
1218 r_input_15 := p_input_15;
1219 r_input_16 := p_input_16;
1220 r_input_17 := p_input_17;
1221 r_input_18 := p_input_18;
1222 r_input_19 := p_input_19;
1223 r_input_20 := p_input_20;
1224 r_input_21 := p_input_21;
1225 r_input_22 := p_input_22;
1226 r_input_23 := p_input_23;
1227 r_input_24 := p_input_24;
1228 r_input_25 := p_input_25;
1229 r_input_26 := p_input_26;
1230 r_input_27 := p_input_27;
1231 r_input_28 := p_input_28;
1232 r_input_29 := p_input_29;
1233 r_input_30 := p_input_30;
1234 r_input_31 := p_input_31;
1235 r_input_32 := p_input_32;
1236 r_input_33 := p_input_33;
1237 r_input_34 := p_input_34;
1238 r_input_35 := p_input_35;
1239 r_input_36 := p_input_36;
1240 r_input_37 := p_input_37;
1241 r_input_38 := p_input_38;
1242 r_input_39 := p_input_39;
1243
1244
1245 -- Use separate IF conditions for each report_qualifier.
1246
1247 IF p_report_qualifier = 'PR' THEN
1248
1249 r_input_2 := lpad(nvl(r_input_2,' '),10,' ');
1250 r_input_3 := lpad(nvl(substr(replace(r_input_3,' '),1,5),' '),5);
1251 r_input_4 := lpad(nvl(r_input_4,' '),15,'0');
1252 r_input_5 := lpad(nvl(r_input_5,' '),15,'0');
1253 r_input_6 := lpad(nvl(r_input_6,' '),15,'0');
1254 r_input_7 := lpad(nvl(r_input_7,' '),15,'0');
1255 r_input_8 := lpad(nvl(r_input_8,' '),15,'0');
1256 r_input_9 := lpad(nvl(r_input_9,' '),15,'0');
1257
1258 END IF;
1259
1260 IF p_report_qualifier = 'MD' THEN
1261
1262 r_input_4 := rpad(substr(nvl(r_input_4,' '),1,4),4);
1263 r_input_5 := rpad(substr(replace(replace(nvl(replace(p_input_5,' '),' '),'-'),'/'),1,9),9);
1264 r_input_6 := lpad(replace(replace(nvl(replace(p_input_6,' '),' '),'-'),'/'),8,'0');
1265
1266 r_input_7 := rpad(substr(nvl(upper(r_input_7),' '),1,57),57,' '); --ER Name
1267
1268 /* ER Address */
1269 r_input_8 := rpad(substr(nvl(r_input_8,' '),1,22),22,' ');
1270 r_input_9 := rpad(substr(nvl(r_input_9, ' '),1,22),22,' ');
1271 r_input_10 := rpad(substr(nvl(r_input_10,' '),1,2),2,' ');
1272 r_input_11 := rpad(substr(nvl(r_input_11,' '),1,5),5,' ');
1273 r_input_12 := rpad(substr(nvl(r_input_12,' '),1,4),4,' ');
1274
1275 r_input_13 := lpad(nvl(r_input_13,'0'),6,'0'); -- Number of RS Records
1276
1277 r_input_14 := lpad(nvl(r_input_14,'0'),12,'0');
1278 r_input_15 := lpad(nvl(r_input_15,'0'),12,'0');
1279 r_input_16 := lpad(nvl(r_input_16,'0'),12,'0');
1280 r_input_17 := lpad(nvl(r_input_17,'0'),12,'0');
1281 r_input_18 := lpad(nvl(r_input_18,'0'),12,'0');
1282 r_input_19 := lpad(nvl(r_input_19,'0'),12,'0');
1283 r_input_20 := lpad(nvl(r_input_20,'0'),12,'0');
1284 r_input_21 := lpad(nvl(r_input_21,'0'),12,'0');
1285 r_input_22 := lpad(nvl(r_input_22,'0'),12,'0');
1286 r_input_23 := lpad(nvl(r_input_23,'0'),12,'0');
1287
1288 r_input_24 := rpad(substr(nvl(upper(r_input_24),' '),1,28),28,' '); --Contact
1289 r_input_25 := rpad(nvl(r_input_25,' '),15,' '); -- Title
1290
1291 /* Date in YYYYMMDD format */
1292 r_input_26 := substr(nvl(r_input_26,' '),5,4)||substr(nvl(r_input_26,' '),1,2)||substr(nvl(r_input_26,' '),3,2);
1293 r_input_26 := rpad(nvl(r_input_26,' '),8,' '); -- Date
1294
1295 r_input_27 := rpad(nvl(r_input_27,' '),10,' '); -- Phone
1296 END IF ;
1297
1298
1299
1300 IF p_report_qualifier = 'PR' THEN
1301
1302 return_value :='RV'
1303 ||r_input_2
1304 ||r_input_3
1305 ||r_input_4
1306 ||r_input_5
1307 ||r_input_6
1308 ||r_input_7
1309 ||r_input_8
1310 ||r_input_9
1311 ||lpad(' ',405);
1312
1313 END IF;
1314
1315 IF p_report_qualifier = 'MD' THEN
1316
1317 return_value :='RV'
1318 ||r_input_2
1319 ||r_input_3
1320 ||r_input_4
1321 ||r_input_5
1322 ||r_input_6
1323 ||r_input_7
1324 ||r_input_8
1325 ||r_input_9
1326 ||r_input_10
1327 ||r_input_11
1328 ||r_input_12
1329 ||r_input_13
1330 ||r_input_14
1331 ||r_input_15
1332 ||r_input_16
1333 ||r_input_17
1334 ||r_input_18
1335 ||r_input_19
1336 ||r_input_20
1337 ||r_input_21
1338 ||r_input_22
1339 ||r_input_23
1340 ||r_input_24
1341 ||r_input_25
1342 ||r_input_26
1343 ||r_input_27
1344 ||r_input_28
1345 ||lpad(' ',182);
1346
1347 END IF;
1348
1349 IF p_exclude_from_output = 'Y' THEN
1350 l_exclude_from_output_chk := TRUE;
1351 END IF;
1352
1353 p_error := l_exclude_from_output_chk;
1354 ret_str_len:=length(return_value);
1355 return return_value;
1356 END format_W2_RV_record;
1357 -- vmkulkar
1358 -- End of Function Formatting RV record
1359
1360
1361 --
1362 -- Formatting RE Record for W2 reporting
1363 --
1364 FUNCTION format_W2_RE_record(
1365 p_effective_date IN varchar2,
1366 p_report_type IN varchar2,
1367 p_format IN varchar2,
1368 p_report_qualifier IN varchar2,
1369 p_record_name IN varchar2,
1370 p_input_1 IN varchar2,
1371 p_input_2 IN varchar2,
1372 p_input_3 IN varchar2,
1373 p_input_4 IN varchar2,
1374 p_input_5 IN varchar2,
1375 p_input_6 IN varchar2,
1376 p_input_7 IN varchar2,
1377 p_input_8 IN varchar2,
1378 p_input_9 IN varchar2,
1379 p_input_10 IN varchar2,
1380 p_input_11 IN varchar2,
1381 p_input_12 IN varchar2,
1382 p_input_13 IN varchar2,
1383 p_input_14 IN varchar2,
1384 p_input_15 IN varchar2,
1385 p_input_16 IN varchar2,
1386 p_input_17 IN varchar2,
1387 p_input_18 IN varchar2,
1388 p_input_19 IN varchar2,
1389 p_input_20 IN varchar2,
1390 p_input_21 IN varchar2,
1391 p_input_22 IN varchar2,
1392 p_input_23 IN varchar2,
1393 p_input_24 IN varchar2,
1394 p_input_25 IN varchar2,
1395 p_input_26 IN varchar2,
1396 p_input_27 IN varchar2,
1397 p_input_28 IN varchar2,
1398 p_input_29 IN varchar2,
1399 p_input_30 IN varchar2,
1400 p_input_31 IN varchar2,
1401 p_input_32 IN varchar2,
1402 p_input_33 IN varchar2,
1403 p_input_34 IN varchar2,
1404 p_input_35 IN varchar2,
1405 p_input_36 IN varchar2,
1406 p_input_37 IN varchar2,
1407 p_input_38 IN varchar2,
1408 p_input_39 IN varchar2,
1409 p_input_40 IN varchar2,
1410 p_validate IN varchar2,
1411 p_exclude_from_output OUT nocopy varchar2,
1412 sp_out_1 OUT nocopy varchar2,
1413 sp_out_2 OUT nocopy varchar2,
1414 sp_out_3 OUT nocopy varchar2,
1415 sp_out_4 OUT nocopy varchar2,
1416 sp_out_5 OUT nocopy varchar2,
1417 ret_str_len OUT nocopy number,
1418 p_error OUT nocopy boolean
1419 ) RETURN VARCHAR2
1420 IS
1421 /* Record Identifier --> p_input_1,
1422 Tax Year --> p_input_2,
1423 Agent Indicator Code --> p_input_3,
1424 Employer / Agent Employer Identification Number (EIN) -->p_input_4,
1425 Agent For EIN --> p_input_5,
1426 Terminating Business Indicator --> p_input_6,
1427 Establishment Number --> p_input_7,
1428 Other EIN --> p_input_8,
1429 Employer Name --> p_input_9,
1430 Location Address --> p_input_10,
1431 Delivery Address --> p_input_11,
1432 City --> p_input_12,
1433 State Abbreviation --> p_input_13,
1434 Zip Code --> p_input_14,
1435 Zip Code Extension --> p_input_15,
1436 Blank,
1437 Foreign State / Provinc --> p_input_16
1438 Foreign Postal Code --> p_input_17,
1439 Country Code --> p_input_18,
1440 Employment Code --> p_input_19,
1441 Tax Jurisdiction Code --> p_input_20,
1442 Third Party Sick Pay Indicator --> p_input_21,
1443 Alabama Withholding Tax Acct Number (Only for AL) --> p_input_23
1444 Tax Withholding Acct No. (MD) --> p_input_24
1445 Total No. of RS Record for this Employer / GRE (MD) --> p_input_25
1446 Total Withheld Tax Reported for MD --> p_input_26
1447 Total Withheld Tax Shown on W2 (MD) --> p_input_27
1448 MW508 ER Credits (MD) --> p_input_28
1449 MW508 Amt Tax Due (MD) --> p_input_29
1450 MW508 Amt Balance Due (MD) --> p_input_30
1451 MW508 ER Amt OverPayment (MD) --> p_input_31
1452 MW508 ER Amt Credit (MD) --> p_input_32
1453 MW508 ER Amt Refunded (MD) --> p_input_33
1454 Total Gross MD Payroll for Year --> p_input_34
1455 Total State Pickup Amt (MD) --> p_input_35
1456 ER Reporting Name (MD) --> p_input_36
1457 ER Reporting Date (MD) --> p_input_37
1458 ER Reporting Phone Number (MD) --> p_input_38
1459 */
1460
1461 l_agent_indicator varchar2(1);
1462 l_emp_ein varchar2(100);
1463 l_agent_ein varchar2(100);
1464 l_other_ein varchar2(100);
1465 l_term_indicator varchar2(1);
1466 l_exclude_from_output_chk boolean;
1467 l_input_8 varchar2(50);
1468 l_bus_tax_acct_number varchar2(50);
1469 l_rep_qtr varchar2(300);
1470 l_rep_prd varchar2(300);
1471 l_end_of_rec varchar2(20);
1472 return_value varchar2(32767);
1473 /* Bug 3936924 */
1474 l_al_wh_tax_acct_no varchar2(300);
1475
1476 r_input_1 varchar2(300);
1477 r_input_2 varchar2(300);
1478 r_input_3 varchar2(300);
1479 r_input_4 varchar2(300);
1480 r_input_5 varchar2(300);
1481 r_input_6 varchar2(300);
1482 r_input_7 varchar2(300);
1483 r_input_8 varchar2(300);
1484 r_input_9 varchar2(300);
1485 r_input_10 varchar2(300);
1486 r_input_11 varchar2(300);
1487 r_input_12 varchar2(300);
1488 r_input_13 varchar2(300);
1489 r_input_14 varchar2(300);
1490 r_input_15 varchar2(300);
1491 r_input_16 varchar2(300);
1492 r_input_17 varchar2(300);
1493 r_input_18 varchar2(300);
1494 r_input_19 varchar2(300);
1495 r_input_20 varchar2(300);
1496 r_input_21 varchar2(300);
1497 r_input_22 varchar2(300);
1498 r_input_23 varchar2(300);
1499 r_input_24 varchar2(300);
1500 r_input_25 varchar2(300);
1501 r_input_26 varchar2(300);
1502 r_input_27 varchar2(300);
1503 r_input_28 varchar2(300);
1504 r_input_29 varchar2(300);
1505 r_input_30 varchar2(300);
1506 r_input_31 varchar2(300);
1507 r_input_32 varchar2(300);
1508 r_input_33 varchar2(300);
1509 r_input_34 varchar2(300);
1510 r_input_35 varchar2(300);
1511 r_input_36 varchar2(300);
1512 r_input_37 varchar2(300);
1513 r_input_38 varchar2(300);
1514 r_input_39 varchar2(300);
1515
1516 BEGIN
1517 hr_utility.trace('RE Record Formatting started ');
1518 -- Initializing local variables with parameter value
1519 --{
1520 r_input_2 := p_input_2;
1521 r_input_3 := p_input_3;
1522 r_input_4 := p_input_4;
1523 r_input_5 := p_input_5;
1524 r_input_6 := p_input_6;
1525 r_input_7 := p_input_7;
1526 r_input_8 := p_input_8;
1527 r_input_9 := p_input_9;
1528 r_input_10 := p_input_10;
1529 r_input_11 := p_input_11;
1530 r_input_12 := p_input_12;
1531 r_input_13 := p_input_13;
1532 r_input_14 := p_input_14;
1533 r_input_15 := p_input_15;
1534 r_input_16 := p_input_16;
1535 r_input_17 := p_input_17;
1536 r_input_18 := p_input_18;
1537 r_input_19 := p_input_19;
1538 r_input_20 := p_input_20;
1539 r_input_21 := p_input_21;
1540 r_input_22 := p_input_22;
1541 r_input_23 := p_input_23;
1542 r_input_24 := p_input_24;
1543 r_input_25 := p_input_25;
1544 r_input_26 := p_input_26;
1545 r_input_27 := p_input_27;
1546 r_input_28 := p_input_28;
1547 r_input_29 := p_input_29;
1548 r_input_30 := p_input_30;
1549 r_input_31 := p_input_31;
1550 r_input_32 := p_input_32;
1551 r_input_33 := p_input_33;
1552 r_input_34 := p_input_34;
1553 r_input_35 := p_input_35;
1554 r_input_36 := p_input_36;
1555 r_input_37 := p_input_37;
1556 r_input_38 := p_input_38;
1557 r_input_39 := p_input_39;
1558 --}
1559
1560 IF p_record_name = 'RE' THEN -- p_record_name
1561 --{
1562 /* Check for agent indicator code p_input_3 */
1563 IF p_input_3 = 'Y' THEN
1564 hr_utility.trace('agent indicator is Y');
1565 l_agent_indicator := '1';
1566 l_emp_ein := p_input_5;
1567 l_agent_ein := p_input_4;
1568 ELSE
1569 l_agent_indicator:=lpad(' ','1');
1570 hr_utility.trace('agent indicator is not 1');
1571 l_emp_ein := p_input_4;
1572 l_agent_ein := lpad(' ',9);
1573 END IF;
1574
1575 IF p_input_6 = 'Y' THEN
1576 l_term_indicator:='1';
1577 ELSE
1578 l_term_indicator:='0';
1579 END IF;
1580
1581 IF p_report_qualifier = 'WV' THEN
1582 l_term_indicator := lpad(' ',1);
1583 END IF;
1584
1585
1586 IF ((p_report_qualifier = 'AL') OR --AL ,CT,NC,PA and OH RITA
1587 (p_report_qualifier = 'PA') OR
1588 (p_report_qualifier = 'NC') OR
1589 (p_report_qualifier = 'CT') OR
1590 (p_report_qualifier = 'LA') OR -- Bug # 3130999
1591 (p_report_qualifier = 'MO') OR -- Added to fix bug # 2149507
1592 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
1593 (p_report_qualifier = 'OH_RTCCA') OR
1594 (p_report_qualifier = 'OH_CCAAA') OR
1595 (p_report_qualifier = 'PA_PHILA') OR
1596 (p_report_qualifier = 'MO_STLOU')) THEN
1597 --{
1598 l_agent_indicator := lpad(' ',1);
1599 l_other_ein := lpad(' ',9);
1600 l_agent_ein := lpad(' ',9);
1601 l_term_indicator := lpad(' ',1);
1602 l_emp_ein := p_input_4;
1603 r_input_7 := lpad(' ',4);
1604 r_input_8 := l_other_ein;
1605 --}
1606 END IF;
1607
1608 /* OH RITA Other EIN. */
1609 If ((p_report_qualifier = 'OH_RTCCA') OR
1610 (p_report_qualifier = 'OH_CCAAA')) THEN
1611 --{
1612 r_input_8 := p_input_8;
1613 --}
1614 END IF;
1615 /* Bug 5640748 */
1616 IF (p_report_qualifier = 'MD') THEN
1617 l_term_indicator := '0' ;
1618 r_input_8 := lpad(' ',9,' ') ;
1619 END IF ;
1620
1621 -- Validation for RE Record starts
1622 -- These validation are used only for mf file only.
1623 -- not for any of the audit report
1624 --
1625 IF p_input_40 = 'FLAT' THEN
1626 --{
1627 hr_utility.trace('before data_validation of ein');
1628 l_emp_ein :=
1629 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1630 p_report_type,
1631 p_format,
1632 p_report_qualifier,
1633 p_record_name,
1634 'EIN',
1635 l_emp_ein,
1636 'Employer EIN',
1637 p_input_9,
1638 null,
1639 p_validate,
1640 p_exclude_from_output,
1641 sp_out_1,
1642 sp_out_2);
1643 IF p_exclude_from_output = 'Y' THEN
1644 l_exclude_from_output_chk := TRUE;
1645 END IF;
1646
1647 IF l_agent_indicator = '1' THEN
1648 --{
1649 l_agent_ein := pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1650 p_report_type,
1651 p_format,
1652 p_report_qualifier,
1653 p_record_name,
1654 'EIN',
1655 l_agent_ein,
1656 'Agent EIN',
1657 p_input_9,
1658 null,
1659 p_validate,
1660 p_exclude_from_output,
1661 sp_out_1,
1662 sp_out_2);
1663 IF p_exclude_from_output = 'Y' THEN
1664 l_exclude_from_output_chk := TRUE;
1665 END IF;
1666 --}
1667 END IF; -- agent_indicator
1668
1669 hr_utility.trace('after data_validation of EIN');
1670 l_input_8 := replace(r_input_8,' ');
1671
1672 IF l_input_8 IS NOT NULL THEN --Checking Other EIN for validation
1673 --{
1674 hr_utility.trace('before data_validation of other EIN');
1675 l_other_ein:= pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1676 p_report_type,
1677 p_format,
1678 p_report_qualifier,
1679 p_record_name,
1680 'EIN',
1681 p_input_8,
1682 'Other EIN',
1683 p_input_9,
1684 null,
1685 p_validate,
1686 p_exclude_from_output,
1687 sp_out_1,
1688 sp_out_2);
1689 hr_utility.trace('after data_validation of l_other_ein');
1690 IF p_exclude_from_output = 'Y' THEN
1691 l_exclude_from_output_chk := TRUE;
1692 END IF;
1693 --}
1694 ELSE
1695 l_other_ein:= lpad(' ',9);
1696 END IF; --Checking Other EIN for validation
1697 --
1698 -- Validation for RE record ends here
1699
1700 -- Formatting for mf file
1701 --
1702 /* Pos:27 - 30 Establishment number is blank for MA */
1703
1704 IF p_report_qualifier = 'MA' THEN
1705 r_input_7 := lpad(' ',4);
1706 END IF;
1707
1708 /* Pos:119-140 Delivery address blank for NC.Bug:2124630 */
1709 /* Pos:119-140 Delivery Address shoul not be blanked out for NC.
1710 This is to revert back the chenges made to fix
1711 the bug 2124630. Delivery address should be
1712 reported for NC in the Pos 119-140 Code written
1713 to fix bug 2124630 will be commented to fix bug
1714 # 2198547
1715 */
1716 /*
1717 IF p_report_qualifier = 'NC' THEN
1718 r_input_10 := r_input_11; -- Bug:2124630
1719 r_input_11 := lpad(' ',22);
1720 END IF;
1721 */
1722 /* Employer Address is blank out for the Saint Louis Local of MO state */
1723 IF (p_report_qualifier = 'MO_STLOU')THEN
1724 r_input_10 := lpad(' ',22);
1725 r_input_12 := lpad(' ',22);
1726 r_input_13 := lpad(' ',2);
1727 r_input_14 := lpad(' ',5);
1728 r_input_15 := lpad(' ',4);
1729 END IF;
1730
1731 /* Delivery Address is blank out for MO and Saint Louis local */
1732 -- Bug 4739790
1733 -- Removed the MO_KNSAS condition as we require Delivery address for that
1734 IF ((p_report_qualifier = 'MO') OR
1735 (p_report_qualifier = 'MO_STLOU')) THEN
1736 r_input_11 := lpad(' ',22);
1737 END IF;
1738
1739 IF p_report_qualifier = 'AL' THEN
1740 r_input_10 := lpad(' ',22);
1741 /* Bug 3936924
1742 Zip Code Extn reqd for AL now
1743 401-410 - Alabama withholding tax acct number*/
1744 l_al_wh_tax_acct_no := lpad(replace(replace(nvl(replace(r_input_23,' '),' ')
1745 ,'-'),'/'),10,'0');
1746 r_input_23 := ' ';
1747 ELSE
1748 l_al_wh_tax_acct_no := lpad(' ',10,' ');
1749 END IF; --AL zip ext blank
1750
1751 /* AL,PA,MA,NC and OH RITA/CCA foreign adrs,emp_code,
1752 third_party,tax_jurisdiction testing.
1753 Saint Louis, Missouri, Philadelphia added for local mag*/
1754 IF ((p_report_qualifier = 'AL') OR
1755 (p_report_qualifier = 'PA') OR
1756 (p_report_qualifier = 'LA') OR -- Bug # 3130999
1757 (p_report_qualifier = 'NC') OR
1758 (p_report_qualifier = 'MO') OR -- Added for fixing bug # 2149507
1759 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
1760 (p_report_qualifier = 'OH_RTCCA') OR
1761 (p_report_qualifier = 'OH_CCAAA') OR
1762 (p_report_qualifier = 'MO_STLOU') OR
1763 (p_report_qualifier = 'PA_PHILA') OR
1764 (p_report_qualifier = 'MA') OR
1765 (p_report_qualifier = 'WV') OR -- Bug # 3186636
1766 (p_report_qualifier = 'MD')) THEN -- Bug# 5640748
1767 --{
1768 r_input_16 := lpad(' ',23);
1769 r_input_17 := lpad(' ',15);
1770 r_input_18 := lpad(' ',2);
1771 r_input_20 := lpad(' ',1);
1772 r_input_21 := lpad(' ',1);
1773
1774 IF ((p_report_qualifier = 'PA') OR -- AL,PA,NC emp_code blank
1775 (p_report_qualifier = 'NC') OR
1776 (p_report_qualifier = 'LA') OR -- Bug # 3130999
1777 (p_report_qualifier = 'OH_RTCCA') OR
1778 (p_report_qualifier = 'OH_CCAAA') OR
1779 (p_report_qualifier = 'MO_STLOU') OR
1780 (p_report_qualifier = 'PA_PHILA') OR
1781 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
1782 (p_report_qualifier = 'AL') OR
1783 (p_report_qualifier = 'MO') OR -- Added for fixing bug # 2149507
1784 (p_report_qualifier = 'WV') OR -- Bug # 3186636
1785 (p_report_qualifier = 'MD') -- Bug# 5640748
1786 ) THEN
1787 r_input_19 := lpad(' ',1);
1788 END IF;-- AL,PA emp_code blank
1789 --}
1790 END IF; --AL,PA,MA checking
1791
1792 /* Pos:219 - 221 blank for CT */
1793 IF (p_report_qualifier = 'CT') THEN
1794 --{
1795 r_input_19 := lpad(' ',1);
1796 r_input_20 := lpad(' ',1);
1797 r_input_21 := lpad(' ',1);
1798 --}
1799 END IF;
1800
1801 /* Bug:2116807 Pos:221 blank for IN */
1802 IF p_report_qualifier = 'IN' THEN
1803 r_input_21 := lpad(' ',1);
1804 r_input_19 := lpad(' ',1); /* 6684920 */
1805 END IF;
1806 /* Bug# 5640748 */
1807 IF p_report_qualifier = 'MD' THEN
1808 r_input_21 := lpad('0',1,'0') ;
1809 END IF ;
1810
1811 IF p_report_qualifier = 'PA' THEN /* Bug:2159881 */
1812 -- Formatting for 8 digit PA account
1813 r_input_30 := rpad(replace(replace(nvl
1814 (replace(r_input_22,' '),' '),'/'),'-'),8);
1815 ELSE
1816 r_input_30 := lpad(' ',8);
1817 END IF;
1818
1819 IF p_report_qualifier = 'PA_PHILA' THEN
1820 --{
1821 hr_utility.trace('CHK before Formating Locality Comapny ID ');
1822 l_bus_tax_acct_number := rpad(replace(replace(nvl
1823 (replace(p_input_17,' '),' '),'/'),'-'),7);
1824 hr_utility.trace('CHK Formatted Locality Comapny ID '
1825 ||l_bus_tax_acct_number);
1826 r_input_22 := substr(l_bus_tax_acct_number,1,2);
1827 r_input_23 := rpad(substr(l_bus_tax_acct_number,3,7),6);
1828 r_input_24 := lpad(' ',2);
1829 r_input_25 := lpad(' ',12);
1830 r_input_26 := lpad(' ',1);
1831 r_input_27 := lpad(' ',1);
1832 r_input_28 := lpad(' ',1);
1833 r_input_29 := lpad(' ',1);
1834 --}
1835 ELSE
1836 --{
1837 r_input_22 := lpad(' ',2);
1838 r_input_23 := lpad(' ',6);
1839 r_input_24 := lpad(' ',2);
1840 r_input_25 := lpad(' ',12);
1841 r_input_26 := lpad(' ',1);
1842 r_input_27 := lpad(' ',1);
1843 r_input_28 := lpad(' ',1);
1844 r_input_29 := lpad(' ',1);
1845
1846 END IF;
1847
1848 -- Bug # 3299126
1849 IF p_report_qualifier = 'LA' THEN --LA Delivery address should be blank
1850 r_input_10 := lpad(' ',22);
1851 END IF;
1852
1853 -- Bug# 5640748
1854 IF p_report_qualifier = 'MD' THEN
1855 r_input_22 := rpad(substr(nvl(r_input_2,' '),1,4),4) ;
1856 r_input_23 := l_emp_ein ;
1857 r_input_24 := lpad(replace(replace(nvl(replace(p_input_24,' '),' ')
1858 ,'-'),'/'),8,'0');
1859 r_input_24 := r_input_24 || rpad(substr(nvl(upper(r_input_9), ' '), 1, 30), 30, ' ') ;
1860 r_input_24 := r_input_24 || rpad(substr(nvl(r_input_11, ' '),1, 22), 22, ' ') ; /* Bug# 5759976 Changed r_input_10 to r_input_11 */
1861 r_input_24 := r_input_24 || rpad(substr(nvl(r_input_12, ' '),1, 20), 20, ' ') ;
1862 r_input_24 := r_input_24 || rpad(substr(nvl(r_input_13, ' '),1, 2), 2, ' ') ;
1863 r_input_24 := r_input_24 || rpad(substr(nvl(r_input_14, ' '), 1, 5), 5, ' ') ;
1864 r_input_24 := r_input_24 || rpad(substr(nvl(r_input_15, ' '),1, 4), 4 , ' ') ;
1865
1866 r_input_25 := lpad(p_input_25, 5 , '0') ;
1867
1868 r_input_26 := lpad(nvl(p_input_26, '0'), 12, '0') ;
1869 r_input_27 := lpad(nvl(p_input_27, '0'), 12, '0') ;
1870 r_input_28 := lpad(nvl(p_input_28, '0'), 12, '0') ;
1871 r_input_29 := lpad(nvl(p_input_29, '0'), 12, '0') ;
1872 r_input_30 := lpad(nvl(p_input_30, '0'), 12, '0') ;
1873 r_input_31 := lpad(nvl(p_input_31, '0'), 12, '0') ;
1874 r_input_32 := lpad(nvl(p_input_32, '0'), 12, '0') ;
1875 r_input_33 := lpad(nvl(p_input_33, '0'), 12, '0') ;
1876 r_input_34 := lpad(nvl(p_input_34, '0'), 12, '0') ;
1877 r_input_35 := lpad(nvl(p_input_35, '0'), 12, '0') ;
1878
1879 r_input_36 := rpad(nvl(p_input_36, ' '), 43, ' ') ;
1880 r_input_37 := rpad(p_input_37, 8, ' ') ;
1881 r_input_38 := rpad(nvl(p_input_38,' '), 10, ' ') ;
1882 END IF ;
1883 --{
1884 IF p_report_qualifier = 'MD' THEN /* 6648007 */
1885 r_input_22 := lpad(replace(replace(nvl(replace(p_input_24,' '),' ')
1886 ,'-'),'/'),8,'0'); --Central Registration Number
1887
1888 END IF ;
1889
1890 IF p_report_qualifier = 'MD' THEN
1891 return_value := 'RE'
1892 ||rpad(substr(nvl(r_input_2,' '),1,4),4)
1893 ||l_agent_indicator
1894 ||l_emp_ein||l_agent_ein
1895 ||l_term_indicator
1896 ||rpad(substr(nvl(r_input_7,' '),1,4),4)
1897 ||l_other_ein
1898 ||rpad(substr(nvl(upper(r_input_9),' '),1,57),57)
1899 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
1900 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
1901 ||rpad(substr(nvl(r_input_12,' '),1,22),22)
1902 ||rpad(substr(nvl(r_input_13,' '),1,2),2)
1903 ||rpad(substr(nvl(r_input_14,' '),1,5),5)
1904 ||rpad(substr(nvl(r_input_15,' '),1,4),9)
1905 ||rpad(substr(nvl(r_input_16,' '),1,23),23)
1906 ||rpad(substr(nvl(r_input_17,' '),1,15),15)
1907 ||rpad(substr(nvl(r_input_18,' '),1,2),2)
1908 ||rpad(nvl(r_input_19,'R'),1)
1909 ||rpad(nvl(r_input_20,' '),1)
1910 ||rpad(nvl(r_input_21,'0'),1)
1911 ||r_input_22
1912 /* ||r_input_23
1913 ||r_input_24
1914 ||r_input_25
1915 ||r_input_26
1916 ||r_input_27
1917 ||r_input_28
1918 ||r_input_29
1919 ||r_input_30
1920 ||r_input_31
1921 ||r_input_32
1922 ||r_input_33
1923 ||r_input_34
1924 ||r_input_35
1925 ||r_input_36
1926 ||r_input_37
1927 ||r_input_38
1928 ||'Y' */
1929 || LPAD(' ',283) /* 6648007 - Moved to RV record */
1930 ||l_end_of_rec ;
1931 ELSE
1932 return_value := 'RE'
1933 ||rpad(substr(nvl(r_input_2,' '),1,4),4)
1934 ||l_agent_indicator
1935 ||l_emp_ein||l_agent_ein
1936 ||l_term_indicator
1937 ||rpad(substr(nvl(r_input_7,' '),1,4),4)
1938 ||l_other_ein
1939 ||rpad(substr(nvl(upper(r_input_9),' '),1,57),57)
1940 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
1941 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
1942 ||rpad(substr(nvl(r_input_12,' '),1,22),22)
1943 ||rpad(substr(nvl(r_input_13,' '),1,2),2)
1944 ||rpad(substr(nvl(r_input_14,' '),1,5),5)
1945 ||rpad(substr(nvl(r_input_15,' '),1,4),9)
1946 ||rpad(substr(nvl(r_input_16,' '),1,23),23)
1947 ||rpad(substr(nvl(r_input_17,' '),1,15),15)
1948 ||rpad(substr(nvl(r_input_18,' '),1,2),2)
1949 ||rpad(nvl(r_input_19,'R'),1)
1950 ||rpad(nvl(r_input_20,' '),1)
1951 ||rpad(nvl(r_input_21,'0'),1)
1952 ||r_input_22
1953 ||r_input_23
1954 ||rpad(r_input_24,5)
1955 ||rpad(r_input_25,15)
1956 ||r_input_26
1957 ||r_input_27
1958 ||r_input_28
1959 ||rpad(r_input_29,148)
1960 ||rpad(l_al_wh_tax_acct_no,104,' ') -- Positions 401-410 for AL state
1961 ||r_input_30
1962 ||l_end_of_rec;
1963 --}
1964 END IF ;
1965 ret_str_len:=length(return_value);
1966 --}
1967 ELSIF p_input_40 = 'CSV' THEN
1968 --{
1969 IF p_report_qualifier = 'MD' THEN
1970 return_value:= 'RE'
1971 ||','||rpad(substr(p_input_2,1,4),4)
1972 ||','||l_agent_indicator
1973 ||','||l_emp_ein
1974 ||','||l_agent_ein
1975 ||','||l_term_indicator
1976 ||','||rpad(substr(nvl(p_input_7,' '),1,4),4)
1977 ||','||p_input_8
1978 ||','||rpad(substr(nvl(upper(p_input_9),' '),1,57),57)
1979 ||','||rpad(substr(nvl(p_input_10,' '),1,22),22)
1980 ||','||rpad(substr(nvl(p_input_11,' '),1,22),22)
1981 ||','||rpad(substr(nvl(p_input_12,' '),1,22),22)
1982 ||','||rpad(substr(nvl(p_input_13,' '),1,2),2)
1983 ||','||rpad(substr(nvl(p_input_14,' '),1,5),5)
1984 ||','||rpad(substr(nvl(p_input_15,' '),1,4),4)
1985 ||','||lpad(' ',5)
1986 ||','||rpad(substr(nvl(p_input_16,' '),1,23),23)
1987 ||','||rpad(substr(nvl(p_input_17,' '),1,15),15)
1988 ||','||rpad(substr(nvl(p_input_18,' '),1,2),2)
1989 ||','||rpad(nvl(p_input_19,'R'),1)
1990 ||','||rpad(nvl(p_input_20,' '),1)
1991 ||','||rpad(nvl(p_input_21,'0'),1)
1992 ||','||r_input_22
1993 ||','||r_input_23
1994 ||','||r_input_24
1995 ||','||r_input_9
1996 ||','||r_input_10
1997 ||','||r_input_12
1998 ||','||r_input_13
1999 ||','||r_input_14
2000 ||','||r_input_15
2001 ||','||p_input_25
2002 ||','||p_input_26
2003 ||','||p_input_27
2004 ||','||p_input_28
2005 ||','||p_input_29
2006 ||','||p_input_30
2007 ||','||p_input_31
2008 ||','||p_input_32
2009 ||','||p_input_33
2010 ||','||p_input_34
2011 ||','||p_input_35
2012 ||','||p_input_36
2013 ||','||p_input_37
2014 ||','||p_input_38
2015 ||','||'Y' ;
2016 ELSE
2017 return_value:= 'RE'
2018 ||','||rpad(substr(p_input_2,1,4),4)
2019 ||','||l_agent_indicator
2020 ||','||l_emp_ein
2021 ||','||l_agent_ein
2022 ||','||l_term_indicator
2023 ||','||rpad(substr(nvl(p_input_7,' '),1,4),4)
2024 ||','||p_input_8
2025 ||','||rpad(substr(nvl(upper(p_input_9),' '),1,57),57)
2026 ||','||rpad(substr(nvl(p_input_10,' '),1,22),22)
2027 ||','||rpad(substr(nvl(p_input_11,' '),1,22),22)
2028 ||','||rpad(substr(nvl(p_input_12,' '),1,22),22)
2029 ||','||rpad(substr(nvl(p_input_13,' '),1,2),2)
2030 ||','||rpad(substr(nvl(p_input_14,' '),1,5),5)
2031 ||','||rpad(substr(nvl(p_input_15,' '),1,4),4)
2032 ||','||lpad(' ',5)
2033 ||','||rpad(substr(nvl(p_input_16,' '),1,23),23)
2034 ||','||rpad(substr(nvl(p_input_17,' '),1,15),15)
2035 ||','||rpad(substr(nvl(p_input_18,' '),1,2),2)
2036 ||','||rpad(nvl(p_input_19,'R'),1)
2037 ||','||rpad(nvl(p_input_20,' '),1)
2038 ||','||rpad(nvl(p_input_21,'0'),1)
2039 ||','||p_input_22
2040 ||','||p_input_23
2041 ||','||p_input_24
2042 ||','||lpad(' ',3)
2043 ||','||p_input_25
2044 ||','||lpad(' ',3)
2045 ||','||p_input_26
2046 ||','||p_input_27
2047 ||','||p_input_28
2048 ||','||p_input_29
2049 ||','||p_input_30;
2050
2051 END IF ; -- p_report_qulaifier
2052 --}
2053 END IF; -- p_input_40
2054 END IF;
2055 p_error := l_exclude_from_output_chk;
2056 ret_str_len:=length(return_value);
2057 return return_value;
2058 END format_W2_RE_record; -- End of Function Formatting RE record
2059 --
2060 --
2061
2062 -- Formatting RW record for W2 reporting
2063 -- Parameter description
2064
2065 /*Record Identifier, --> p_input_1
2066 Social Security Number, --> p_input_2
2067 Employee First Name, --> p_input_3
2068 Employee Middle Name or Initial, --> p_input_4
2069 Employee Last Name, --> p_input_5
2070 Suffix, --> p_input_6
2071 Location Address, --> p_input_7
2072 Delivery Address, --> p_input_8
2073 City, --> p_input_9
2074 State Abbreviation, --> p_input_10
2075 Zip Code, --> p_input_11
2076 Zip Code Extension, --> p_input_12
2077 Blank,
2078 Foreign State / Province, --> p_input_13
2079 Foreign Postal Code, --> p_input_14
2080 Country Code, --> p_input_15
2081 Wages, Tips And Other Compensation, --> p_input_16
2082 Federal Income Tax Withheld, --> p_input_17
2083 Social Security Wages, --> p_input_18
2084 Social Security Tax Withheld, --> p_input_19
2085 Medicare Wages And Tips, --> p_input_20
2086 Medicare Tax Withheld, --> p_input_21
2087 Social Security Tips, --> p_input_22
2088 Advance Earned Income Credit, --> p_input_23
2089 Dependent Care Benefits, --> p_input_24
2090 Deferred Compensation Contributions to Section 401(k), --> p_input_25
2091 Deferred Compensation Contributions to Section 403(b), --> p_input_26
2092 Deferred Compensation Contributions to Section 408(k)(6), --> p_input_27
2093 Deferred Compensation Contributions to Section 457(b), --> p_input_28
2094 Deferred Compensation Contributions to Section 501(c)(18)(D), --> p_input_29
2095 Military EE''s Basic Quarters, Subsistence and Combat Pay, --> p_input_30
2096 Non-Qual. plan Sec.457 Distributions or Contributions, --> p_input_31
2097 Non-Qual. plan NOT Section 457 Distributions or Contributions,--> p_input_32
2098 Blank,
2099 Employer Cost of Premiums for GTL> $50k, --> p_input_33
2100 Income From Exercise of Nonqualified Stock Options,, --> p_input_34
2101 Blank,, --> p_input_31
2102 Statutory Employee Indicator,, --> p_input_35
2103 Blank,
2104 Retirement Plan Indicator,, --> p_input_36
2105 Third-Party Sick Pay Indicator, --> p_input_37
2106 Employer Contributions to a Health Savings Account --> p_input_38
2107 Non Taxable Combat Pay --> p_input_41
2108 Deferrals Und a Sec 409A Non-Qualified Deferred Comp Plan --> p_input_42
2109 Designated Roth Contributions to a section 401(k) Plan --> p_input_43
2110 Designated Roth Contributions Under a section 403(b) Salaray --> p_input_44
2111 */
2112
2113 FUNCTION format_W2_RW_record(
2114 p_effective_date IN varchar2,
2115 p_report_type IN varchar2,
2116 p_format IN varchar2,
2117 p_report_qualifier IN varchar2,
2118 p_record_name IN varchar2,
2119 p_input_1 IN varchar2,
2120 p_input_2 IN varchar2,
2121 p_input_3 IN varchar2,
2122 p_input_4 IN varchar2,
2123 p_input_5 IN varchar2,
2124 p_input_6 IN varchar2,
2125 p_input_7 IN varchar2,
2126 p_input_8 IN varchar2,
2127 p_input_9 IN varchar2,
2128 p_input_10 IN varchar2,
2129 p_input_11 IN varchar2,
2130 p_input_12 IN varchar2,
2131 p_input_13 IN varchar2,
2132 p_input_14 IN varchar2,
2133 p_input_15 IN varchar2,
2134 p_input_16 IN varchar2,
2135 p_input_17 IN varchar2,
2136 p_input_18 IN varchar2,
2137 p_input_19 IN varchar2,
2138 p_input_20 IN varchar2,
2139 p_input_21 IN varchar2,
2140 p_input_22 IN varchar2,
2141 p_input_23 IN varchar2,
2142 p_input_24 IN varchar2,
2143 p_input_25 IN varchar2,
2144 p_input_26 IN varchar2,
2145 p_input_27 IN varchar2,
2146 p_input_28 IN varchar2,
2147 p_input_29 IN varchar2,
2148 p_input_30 IN varchar2,
2149 p_input_31 IN varchar2,
2150 p_input_32 IN varchar2,
2151 p_input_33 IN varchar2,
2152 p_input_34 IN varchar2,
2153 p_input_35 IN varchar2,
2154 p_input_36 IN varchar2,
2155 p_input_37 IN varchar2,
2156 p_input_38 IN varchar2,
2157 p_input_39 IN varchar2,
2158 p_input_40 IN varchar2,
2159 p_validate IN varchar2,
2160 p_exclude_from_output OUT nocopy varchar2,
2161 sp_out_1 OUT nocopy varchar2,
2162 sp_out_2 OUT nocopy varchar2,
2163 sp_out_3 OUT nocopy varchar2,
2164 sp_out_4 OUT nocopy varchar2,
2165 sp_out_5 OUT nocopy varchar2,
2166 ret_str_len OUT nocopy number,
2167 p_error OUT nocopy boolean,
2168 p_input_41 IN varchar2 default null,
2169 p_input_42 IN varchar2 default null,
2170 p_input_43 IN varchar2 default null,
2171 p_input_44 IN varchar2 default null
2172
2173 ) RETURN VARCHAR2
2174 IS
2175
2176 l_full_name varchar2(100);
2177 l_emp_name_or_number varchar2(50);
2178 l_emp_number varchar2(50);
2179 l_first_name varchar2(150);
2180 l_middle_name varchar2(100);
2181 l_last_name varchar2(150);
2182 l_suffix varchar2(100);
2183 l_ssn varchar2(100);
2184 l_message varchar2(2000);
2185 l_description varchar2(50);
2186 l_field_description varchar2(50);
2187 l_ss_count number(10);
2188 l_amount number(10);
2189 return_value varchar2(32767);
2190 l_err boolean;
2191 l_exclude_from_output_chk boolean;
2192 l_ss_tax_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
2193 l_ss_wage_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
2194
2195 TYPE function_columns IS RECORD(
2196 p_parameter_name varchar2(100),
2197 p_parameter_value varchar2(100),
2198 p_output_value varchar2(100)
2199 );
2200 function_parameter_rec function_columns;
2201 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
2202 INDEX BY BINARY_INTEGER;
2203 parameter_record input_parameter_record;
2204
2205 r_input_1 varchar2(300);
2206 r_input_2 varchar2(300);
2207 r_input_3 varchar2(300);
2208 r_input_4 varchar2(300);
2209 r_input_5 varchar2(300);
2210 r_input_6 varchar2(300);
2211 r_input_7 varchar2(300);
2212 r_input_8 varchar2(300);
2213 r_input_9 varchar2(300);
2214 r_input_10 varchar2(300);
2215 r_input_11 varchar2(300);
2216 r_input_12 varchar2(300);
2217 r_input_13 varchar2(300);
2218 r_input_14 varchar2(300);
2219 r_input_15 varchar2(300);
2220 r_input_16 varchar2(300);
2221 r_input_17 varchar2(300);
2222 r_input_18 varchar2(300);
2223 r_input_19 varchar2(300);
2224 r_input_20 varchar2(300);
2225 r_input_21 varchar2(300);
2226 r_input_22 varchar2(300);
2227 r_input_23 varchar2(300);
2228 r_input_24 varchar2(300);
2229 r_input_25 varchar2(300);
2230 r_input_26 varchar2(300);
2231 r_input_27 varchar2(300);
2232 r_input_28 varchar2(300);
2233 r_input_29 varchar2(300);
2234 r_input_30 varchar2(300);
2235 r_input_31 varchar2(300);
2236 r_input_32 varchar2(300);
2237 r_input_33 varchar2(300);
2238 r_input_34 varchar2(300);
2239 r_input_35 varchar2(300);
2240 r_input_36 varchar2(300);
2241 r_input_37 varchar2(300);
2242 r_input_38 varchar2(300);
2243 r_input_39 varchar2(300);
2244 /* Bug # 4391218 */
2245 r_input_40 varchar2(300) ;
2246 r_input_41 varchar2(300) ;
2247 /* Bug # 5256745 */
2248 r_input_42 varchar2(300) ;
2249 r_input_43 varchar2(300) ;
2250
2251 CURSOR GET_SS_LIMIT(c_date varchar2)
2252 IS
2253 SELECT SS_EE_WAGE_LIMIT*100,
2254 (SS_EE_WAGE_LIMIT*SS_EE_RATE)*100 tax
2255 FROM PAY_US_FEDERAL_TAX_INFO_F
2256 WHERE TO_DATE(C_DATE,'DD-MM-YYYY') BETWEEN EFFECTIVE_START_DATE
2257 AND EFFECTIVE_END_DATE
2258 AND FED_INFORMATION_CATEGORY = '401K LIMITS';
2259
2260 BEGIN
2261 hr_utility.trace('Formatting RW record');
2262 hr_utility.trace('Formatting Mode = '||p_input_40);
2263 -- Initializing local variables with parameter value
2264 --{
2265 r_input_2 := p_input_2;
2266 r_input_3 := p_input_3;
2267 r_input_4 := p_input_4;
2268 r_input_5 := p_input_5;
2269 r_input_6 := p_input_6;
2270 r_input_7 := p_input_7;
2271 r_input_8 := p_input_8;
2272 r_input_9 := p_input_9;
2273 r_input_10 := p_input_10;
2274 r_input_11 := p_input_11;
2275 r_input_12 := p_input_12;
2276 r_input_13 := p_input_13;
2277 r_input_14 := p_input_14;
2278 r_input_15 := p_input_15;
2279 r_input_16 := p_input_16;
2280 r_input_17 := p_input_17;
2281 r_input_18 := p_input_18;
2282 r_input_19 := p_input_19;
2283 r_input_20 := p_input_20;
2284 r_input_21 := p_input_21;
2285 r_input_22 := p_input_22;
2286 r_input_23 := p_input_23;
2287 r_input_24 := p_input_24;
2288 r_input_25 := p_input_25;
2289 r_input_26 := p_input_26;
2290 r_input_27 := p_input_27;
2291 r_input_28 := p_input_28;
2292 r_input_29 := p_input_29;
2293 r_input_30 := p_input_30;
2294 r_input_31 := p_input_31;
2295 r_input_32 := p_input_32;
2296 r_input_33 := p_input_33;
2297 r_input_34 := p_input_34;
2298 r_input_35 := p_input_35;
2299 r_input_36 := p_input_36;
2300 r_input_37 := p_input_37;
2301 r_input_38 := p_input_38;
2302 r_input_39 := p_input_39;
2303 r_input_40 := p_input_41 ;
2304 r_input_41 := p_input_42 ; -- Bug # 4391218
2305 r_input_42 := p_input_43 ;
2306 r_input_43 := p_input_44 ; -- Bug # 5256745
2307 --}
2308
2309 --
2310 IF p_input_40 = 'FLAT' THEN
2311
2312 -- Following Formattings are required for data validation
2313 --
2314 --{
2315
2316 /*5155648*/
2317 /* commented for bug 7109106 */
2318 /* IF (p_report_qualifier = 'FED' and
2319 r_input_10 = 'PR') THEN
2320 r_input_33 := '0';
2321
2322 END IF; */
2323
2324
2325 /* For fixing bug # 2645739 instead of input parameters, local variables were formatted
2326 and used for Flat reocrd */
2327 IF ((p_report_qualifier = 'NC') OR
2328 (p_report_qualifier = 'MO_STLOU')) THEN
2329 --{
2330 r_input_18 := '0';
2331 r_input_19 := '0';
2332 r_input_20 := '0';
2333 r_input_21 := '0';
2334 r_input_22 := '0';
2335 r_input_23 := '0';
2336 r_input_24 := '0';
2337 r_input_25 := '0';
2338 r_input_26 := '0';
2339 r_input_27 := '0';
2340 r_input_28 := '0';
2341 r_input_29 := '0';
2342 r_input_30 := '0';
2343 r_input_31 := '0';
2344 r_input_32 := '0';
2345 r_input_33 := '0';
2346 r_input_34 := '0';
2347 --}
2348 END IF;
2349
2350 IF p_report_qualifier = 'MO_STLOU' THEN
2351 --{
2352 r_input_17 := '0';
2353 r_input_25 := p_input_25;
2354 r_input_26 := p_input_26;
2355 r_input_27 := p_input_27;
2356 r_input_28 := p_input_28;
2357 r_input_29 := p_input_29;
2358 --}
2359 END IF;
2360
2361 IF p_report_qualifier = 'PR' THEN
2362 --{
2363 r_input_16 := '0';
2364 r_input_17 := '0';
2365 r_input_23 := '0';
2366 r_input_24 := '0';
2367 r_input_25 := '0';
2368 r_input_26 := '0';
2369 r_input_27 := '0';
2370 r_input_28 := '0';
2371 r_input_29 := '0';
2372 r_input_30 := '0';
2373 r_input_31 := '0';
2374 r_input_32 := '0';
2375 r_input_33 := '0';
2376 r_input_38 := '0'; /* Bug 3680056 NR-Zero fill for PR */
2377 --}
2378 END IF;
2379
2380 -- Fix for Bug# 4502738
2381 IF p_report_qualifier = 'MO_KNSAS' THEN
2382 --{
2383 r_input_17 := '0'; -- Federal Income Tax Withheld
2384 r_input_18 := '0'; -- Social Security Wages
2385 r_input_19 := '0'; -- Social Security Tax Withheld
2386 r_input_22 := '0'; -- Social Security Tips
2387 r_input_23 := '0'; -- Advance Earned Income Credit
2388 r_input_24 := '0'; -- Dependent Care Benefits
2389 r_input_25 := '0'; -- Deferred Compensation Contributions to Section 401(k)
2390 r_input_26 := '0'; -- Deferred Compensation Contributions to Section 403(b)
2391 r_input_27 := '0'; -- Deferred Compensation Contributions to Section 408(k)(6)
2392 r_input_28 := '0'; -- Deferred Compensation Contributions to Section 457(b)
2393 r_input_29 := '0'; -- Deferred Compensation Contributions to Section 501(c)(18)(D)
2394 r_input_30 := lpad(' ',11); -- Blank
2395 r_input_31 := '0'; -- Non-Qual. plan Sec.457 Distributions or Contributions
2396 r_input_32 := '0'; -- Non-Qual. plan NOT Section 457 Distributions or Contributions
2397 r_input_33 := '0'; -- Employer Cost of Premiums for GTL> $50k
2398 r_input_34 := '0'; -- Income from nonqualified stock option
2399 r_input_38 := '0'; -- Employer Contributions to Health Savings Account
2400 r_input_41 := '0'; -- Deferrals Under Sec 409A Non-Qual Def Comp Plan
2401 --}
2402 END IF;
2403
2404
2405 /* Bug # 3186636 */
2406 --{
2407 IF p_report_qualifier = 'WV' THEN
2408
2409 r_input_7 := ' ';
2410 r_input_13 := ' ';
2411 r_input_14 := ' ';
2412 r_input_15 := ' ';
2413 r_input_18 := '0';
2414 r_input_19 := '0';
2415 r_input_20 := '0';
2416 r_input_21 := '0';
2417 r_input_22 := '0';
2418 r_input_23 := '0';
2419 r_input_24 := '0';
2420 r_input_25 := '0';
2421 r_input_26 := '0';
2422 r_input_27 := '0';
2423 r_input_28 := '0';
2424 r_input_29 := '0';
2425 r_input_30 := '0';
2426 r_input_31 := '0';
2427 r_input_32 := '0';
2428 r_input_33 := '0';
2429 r_input_34 := '0';
2430 r_input_35 := ' ';
2431 r_input_36 := ' ';
2432 r_input_37 := ' ';
2433
2434 END IF;
2435 --}
2436 --
2437 -- Formatting completes before data validation
2438 --
2439 parameter_record(1).p_parameter_name:= ' Wages,Tips And Other Compensation';
2440 parameter_record(1).p_parameter_value:=r_input_16;
2441
2442 parameter_record(2).p_parameter_name:= ' Federal Income Tax Withheld';
2443 parameter_record(2).p_parameter_value:=r_input_17;
2444
2445 parameter_record(3).p_parameter_name:= 'SS Wages';
2446 parameter_record(3).p_parameter_value:=r_input_18;
2447
2448 parameter_record(4).p_parameter_name:= ' Social Security Tax Withheld';
2449 parameter_record(4).p_parameter_value:=r_input_19;
2450
2451 parameter_record(5).p_parameter_name:= 'Medicare Wages And Tips';
2452 parameter_record(5).p_parameter_value:=r_input_20;
2453
2454 parameter_record(6).p_parameter_name:= 'Medicare Tax Withheld';
2455 parameter_record(6).p_parameter_value:=r_input_21;
2456
2457 parameter_record(7).p_parameter_name:= 'SS Tips';
2458 parameter_record(7).p_parameter_value:=r_input_22;
2459
2460 parameter_record(8).p_parameter_name:= 'Advance Earned Income Credit';
2461 parameter_record(8).p_parameter_value:=r_input_23;
2462
2463 parameter_record(9).p_parameter_name:= 'Dependent Care Benefits';
2464 parameter_record(9).p_parameter_value:=r_input_24;
2465
2466 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
2467 parameter_record(10).p_parameter_value:=r_input_25;
2468
2469 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
2470 parameter_record(11).p_parameter_value:=r_input_26;
2471
2472 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
2473 parameter_record(12).p_parameter_value:=r_input_27;
2474
2475 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
2476 parameter_record(13).p_parameter_value:=r_input_28;
2477
2478 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
2479 parameter_record(14).p_parameter_value:=r_input_29;
2480 /* As A_W2_MILITARY_HOUSING_Q is disabled this field will be blank filled
2481 no standard numeric validation would be performed
2482 parameter_record(15).p_parameter_name:= 'Military Combat Pay';
2483 parameter_record(15).p_parameter_value:=p_input_30;
2484 */
2485 parameter_record(15).p_parameter_name:= 'Non-Qual. plan Sec 457';
2486 parameter_record(15).p_parameter_value:=r_input_31;
2487
2488 parameter_record(16).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
2489 parameter_record(16).p_parameter_value:=r_input_32;
2490
2491 parameter_record(17).p_parameter_name:= 'Employer cost of premiun';
2492 parameter_record(17).p_parameter_value:=r_input_33;
2493
2494 parameter_record(18).p_parameter_name:= 'Income from nonqualified stock option';
2495 parameter_record(18).p_parameter_value:=r_input_34;
2496
2497 /* Bug 3680056 New field */
2498 parameter_record(19).p_parameter_name:= 'Employer Contributions to Health Savings Account';
2499 parameter_record(19).p_parameter_value:=r_input_38;
2500
2501 parameter_record(20).p_parameter_name:= 'Non-Taxable Combat Pay';
2502 parameter_record(20).p_parameter_value:=r_input_40 ; -- Bug # 4391218
2503
2504 parameter_record(21).p_parameter_name:= 'Deferrals Under Sec 409A Non-Qual Def Comp Plan';
2505 parameter_record(21).p_parameter_value:=r_input_41 ; -- Bug # 4391218
2506 /* Bug 5256745 */
2507 parameter_record(22).p_parameter_name:= 'Roth Contributions Und Sec 401(k) Plan';
2508 parameter_record(22).p_parameter_value:=r_input_42 ; -- Bug # 5256745
2509
2510 parameter_record(23).p_parameter_name:= 'Roth Contributions Und Sec 403(b) Plan';
2511 parameter_record(23).p_parameter_value:=r_input_43 ; -- Bug # 5256745
2512
2513 l_first_name := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
2514 substr(p_input_3,1,15),' '),15));
2515 l_middle_name := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
2516 substr(p_input_4,1,15),' '),15));
2517 l_last_name := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
2518 substr(p_input_5,1,20),' '),20));
2519 l_suffix := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(
2520 substr(p_input_6,1,4),' '),4));
2521 l_full_name := substr(ltrim(rtrim(p_input_3)||' '||
2522 rtrim(p_input_5)),1,50);
2523
2524 l_emp_number := replace(p_input_39,' ');
2525
2526 IF l_emp_number IS NULL THEN
2527 l_emp_name_or_number := l_full_name;
2528 hr_utility.trace('l_emp_name_or_number = '||l_emp_name_or_number);
2529 ELSE
2530 l_emp_name_or_number:= l_emp_number;
2531 hr_utility.trace('l_emp_name_or_number = '||l_emp_name_or_number);
2532 END IF;
2533 --
2534 -- Validation for RW Record Start
2535 --
2536 -- SSN Validation
2537 l_ssn :=
2538 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
2539 p_report_type,
2540 p_format,
2541 p_report_qualifier,
2542 p_record_name,
2543 'SSN',
2544 p_input_2,
2545 'Social Security',
2546 l_emp_name_or_number,
2547 null,
2548 p_validate,
2549 p_exclude_from_output,
2550 sp_out_1,
2551 sp_out_2);
2552 IF p_exclude_from_output = 'Y' THEN
2553 l_exclude_from_output_chk := TRUE;
2554 END IF;
2555
2556 -- Various Amount Validation for for Neg value. If value is found negative record
2557 -- is marked for exclusion
2558 FOR i in 1..23
2559 LOOP
2560 parameter_record(i).p_output_value :=
2561 pay_us_reporting_utils_pkg.data_validation(
2562 p_effective_date,
2563 p_report_type,
2564 p_format,
2565 p_report_qualifier,
2566 p_record_name,
2567 'NEG_CHECK',
2568 parameter_record(i).p_parameter_value,
2569 parameter_record(i).p_parameter_name,
2570 l_emp_name_or_number, --EE number for mesg
2571 null,
2572 p_validate,
2573 p_exclude_from_output,
2574 sp_out_1,
2575 sp_out_2);
2576
2577 IF p_exclude_from_output = 'Y' THEN
2578 l_exclude_from_output_chk := TRUE;
2579 END IF;
2580 hr_utility.trace(parameter_record(i).p_parameter_name||' = '||
2581 parameter_record(i).p_output_value);
2582 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
2583
2584 END LOOP;
2585
2586 hr_utility.trace('SS Wage and Tax limit Checking begins.' );
2587 OPEN get_ss_limit(p_effective_date);
2588 LOOP
2589 FETCH get_ss_limit INTO l_ss_wage_limit,
2590 l_ss_tax_limit;
2591 hr_utility.trace('SS Wage Limit '||to_char(l_ss_wage_limit));
2592 l_ss_count:= get_ss_limit%ROWCOUNT;
2593 EXIT WHEN get_ss_limit%NOTFOUND ;
2594 END LOOP;
2595 CLOSE get_ss_limit;
2596 hr_utility.trace('No. rows exist for limit '||to_char(l_ss_count));
2597
2598 IF l_ss_count = 0 THEN
2599 hr_utility.trace('No data found on PAY_US_FEDERAL_TAX_INFO_F '||
2600 'for Social security wage limits.');
2601 ELSIF l_ss_count >1 THEN
2602 hr_utility.trace('Too many rows on PAY_US_FEDERAL_TAX_INFO_F '||
2603 'for Social security wage limits.');
2604 ELSIF l_ss_count=1 THEN
2605 --{
2606 IF (to_number(parameter_record(3).p_output_value) > 0 OR
2607 to_number(parameter_record(4).p_output_value) > 0 OR
2608 to_number(parameter_record(7).p_output_value) > 0 )
2609 THEN
2610 --{
2611 hr_utility.trace('SS Tax w/h, SS Tips, SS Wages are >0 ');
2612 IF (to_number(parameter_record(3).p_output_value)+
2613 to_number(parameter_record(7).p_output_value))
2614 > l_ss_wage_limit
2615 THEN
2616 hr_utility.trace('ss_tips+ss_wages is > '||
2617 to_char(l_ss_wage_limit));
2618 l_field_description:='the sum of '||
2619 parameter_record(3).p_parameter_name
2620 ||' and '||
2621 parameter_record(7).p_parameter_name;
2622 l_amount:=l_ss_wage_limit/100;
2623 l_description:=' It is greater than '||to_char(l_amount);
2624 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
2625 pay_core_utils.push_token('record_name',p_record_name);
2626 pay_core_utils.push_token('name_or_number',
2627 substr(l_emp_name_or_number,1,50));
2628 pay_core_utils.push_token('field_name',l_field_description);
2629 pay_core_utils.push_token('description',
2630 substr(l_description,1,50));
2631 l_err := TRUE;
2632 END IF;
2633
2634 IF to_number(parameter_record(4).p_output_value)>l_ss_tax_limit
2635 THEN
2636 --{
2637 hr_utility.trace('SS Tax w/h is > '||
2638 to_char(l_ss_tax_limit));
2639 l_err := TRUE;
2640 l_amount:=l_ss_tax_limit/100;
2641 l_description:=' It is greater than '||to_char(l_amount);
2642 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
2643 pay_core_utils.push_token('record_name',p_record_name);
2644 pay_core_utils.push_token('name_or_number',
2645 substr(l_emp_name_or_number,1,50));
2646 pay_core_utils.push_token('field_name',parameter_record(4).p_parameter_name);
2647 pay_core_utils.push_token('description',l_description);
2648 /* Sample message for SS Wage/Tax limit
2649 Error in RW record for Employee 1234 in Social Security Tax Withheld. It is greater than 498480 */
2650 --}
2651 END IF; --l_ss_tax_limit
2652 --}
2653 END IF; -- negative check
2654 --}
2655 END IF; --l_ss_count
2656
2657 hr_utility.trace('After SS Wage/ Tax limit checking ');
2658
2659 IF l_err THEN
2660 IF p_validate = 'Y' THEN
2661 p_exclude_from_output := 'Y';
2662 END IF;
2663 END IF;
2664
2665 IF p_exclude_from_output = 'Y' THEN
2666 l_exclude_from_output_chk := TRUE;
2667 END IF;
2668 --
2669 -- Validation for RW record Ends here
2670 --
2671
2672 -- Formatting feields for Geo specific requirements
2673 --
2674
2675
2676 /* Fix for Bug # 2767254 */
2677 IF p_report_qualifier = 'PR' THEN
2678 r_input_30 := rpad('0',11,'0');
2679 r_input_31 := rpad('0',11,'0');
2680 r_input_32 := rpad('0',22,'0');
2681 ELSE
2682 r_input_30 := lpad('0',11,'0'); /* Bug 4859212 */
2683 r_input_31 := lpad(' ',11);
2684 r_input_32 := lpad(' ',22);
2685 END IF;
2686
2687 IF (p_report_qualifier = 'MO_STLOU') THEN
2688 l_first_name := lpad(' ',15);
2689 l_middle_name := lpad(' ',15);
2690 l_last_name := lpad(' ',20);
2691 END IF;
2692
2693 /* For State of NC, suffix is blank filled Bug # 2645739 */
2694 IF(( p_report_qualifier = 'NC') OR
2695 (p_report_qualifier = 'MO_STLOU')) THEN
2696 l_suffix := lpad(' ',4);
2697 END IF;
2698
2699 IF ((p_report_qualifier = 'NC') OR
2700 (p_report_qualifier = 'MO_STLOU')) THEN
2701 --{
2702 r_input_7 := ' ';
2703 r_input_8 := ' ';
2704 r_input_9 := ' ';
2705 r_input_10 := ' ';
2706 r_input_11 := ' ';
2707 r_input_12 := ' ';
2708 r_input_13 := ' ';
2709 r_input_14 := ' ';
2710 r_input_15 := ' ';
2711 r_input_35 := ' ';
2712 r_input_36 := ' ';
2713 r_input_37 := ' ';
2714 END IF;
2715
2716 -- Fix for Bug# 4502738
2717 -- Bug 4739790
2718 -- Removed the blanking of fields r_input_8 and r_input_9
2719 -- as they are required for MO_KNSAS
2720 IF (p_report_qualifier = 'MO_KNSAS') THEN
2721 --{
2722 r_input_13 := ' ';
2723 r_input_14 := ' ';
2724 r_input_15 := ' ';
2725 r_input_35 := ' ';
2726 r_input_36 := ' ';
2727 r_input_37 := ' ';
2728 END IF;
2729 -- Fix for Bug# 4391218
2730
2731 r_input_40 := parameter_record(20).p_output_value ;
2732
2733 IF ( p_report_qualifier = 'MO_KNSAS') THEN
2734 r_input_40 := lpad('0',22,'0') ;
2735 r_input_30 := lpad(' ',11) ; -- Keep it as it is in spec not changed for #4859212
2736 END IF ;
2737
2738
2739 -- End of Fix for Bug# 4391218
2740
2741 hr_utility.trace('Before formatting and returning RW record for the flat file');
2742
2743 -- Formatting Wage Record for .mf reporting file
2744 --
2745 IF p_report_qualifier = 'IN' THEN
2746 return_value := 'RW'||l_ssn||l_first_name
2747 ||l_middle_name
2748 ||l_last_name||l_suffix
2749 ||rpad(substr(nvl(r_input_7,' '),1,22),22)
2750 ||rpad(substr(nvl(r_input_8,' '),1,22),22)
2751 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
2752 ||rpad(substr(nvl(r_input_10,' '),1,2),2)
2753 ||rpad(substr(nvl(r_input_11,' '),1,5),5)
2754 ||rpad(substr(nvl(r_input_12,' '),1,4),9)
2755 ||rpad(substr(nvl(r_input_13,' '),1,23),23)
2756 ||rpad(substr(nvl(r_input_14,' '),1,15),15)
2757 ||rpad(substr(nvl(r_input_15,' '),1,2),2)
2758 ||parameter_record(1).p_output_value
2759 ||parameter_record(2).p_output_value
2760 ||parameter_record(3).p_output_value
2761 ||parameter_record(4).p_output_value
2762 ||parameter_record(5).p_output_value
2763 ||parameter_record(6).p_output_value
2764 ||parameter_record(7).p_output_value
2765 ||parameter_record(8).p_output_value
2766 ||parameter_record(9).p_output_value
2767 ||parameter_record(10).p_output_value
2768 ||parameter_record(11).p_output_value
2769 ||parameter_record(12).p_output_value
2770 ||parameter_record(13).p_output_value
2771 ||parameter_record(14).p_output_value
2772 -- ||parameter_record(15).p_output_value
2773 --lpad(' ',11)
2774 ||r_input_30
2775 --||rpad(parameter_record(15).p_output_value,22)
2776 ||parameter_record(15).p_output_value
2777 -- Bug 3680056 New Field 364-374
2778 ||parameter_record(19).p_output_value
2779 --||rpad(parameter_record(16).p_output_value,33)
2780 ||parameter_record(16).p_output_value
2781 --||r_input_32
2782 ||rpad(r_input_40,22,' ')
2783 ||parameter_record(17).p_output_value
2784 --||rpad(parameter_record(18).p_output_value,67)
2785 ||parameter_record(18).p_output_value
2786 --||rpad(parameter_record(21).p_output_value,56,' ')
2787 /* Bug 5256745 */
2788 /* ||parameter_record(21).p_output_value
2789 ||parameter_record(22).p_output_value
2790 ||rpad(parameter_record(23).p_output_value,34,' ')
2791 ||rpad(substr(nvl(r_input_35,'0'),1,1),2)
2792 ||rpad(substr(nvl(r_input_36,'0'),1,1),1)
2793 ||rpad(substr(nvl(r_input_37,'0'),1,1),24)*/
2794 || rpad(' ',83); /* 6648064 */
2795 ELSE
2796 return_value := 'RW'||l_ssn||l_first_name
2797 ||l_middle_name
2798 ||l_last_name||l_suffix
2799 ||rpad(substr(nvl(r_input_7,' '),1,22),22)
2800 ||rpad(substr(nvl(r_input_8,' '),1,22),22)
2801 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
2802 ||rpad(substr(nvl(r_input_10,' '),1,2),2)
2803 ||rpad(substr(nvl(r_input_11,' '),1,5),5)
2804 ||rpad(substr(nvl(r_input_12,' '),1,4),9)
2805 ||rpad(substr(nvl(r_input_13,' '),1,23),23)
2806 ||rpad(substr(nvl(r_input_14,' '),1,15),15)
2807 ||rpad(substr(nvl(r_input_15,' '),1,2),2)
2808 ||parameter_record(1).p_output_value
2809 ||parameter_record(2).p_output_value
2810 ||parameter_record(3).p_output_value
2811 ||parameter_record(4).p_output_value
2812 ||parameter_record(5).p_output_value
2813 ||parameter_record(6).p_output_value
2814 ||parameter_record(7).p_output_value
2815 ||parameter_record(8).p_output_value
2816 ||parameter_record(9).p_output_value
2817 ||parameter_record(10).p_output_value
2818 ||parameter_record(11).p_output_value
2819 ||parameter_record(12).p_output_value
2820 ||parameter_record(13).p_output_value
2821 ||parameter_record(14).p_output_value
2822 -- ||parameter_record(15).p_output_value
2823 --lpad(' ',11)
2824 ||r_input_30
2825 --||rpad(parameter_record(15).p_output_value,22)
2826 ||parameter_record(15).p_output_value
2827 -- Bug 3680056 New Field 364-374
2828 ||parameter_record(19).p_output_value
2829 --||rpad(parameter_record(16).p_output_value,33)
2830 ||parameter_record(16).p_output_value
2831 --||r_input_32
2832 ||rpad(r_input_40,22,' ')
2833 ||parameter_record(17).p_output_value
2834 --||rpad(parameter_record(18).p_output_value,67)
2835 ||parameter_record(18).p_output_value
2836 --||rpad(parameter_record(21).p_output_value,56,' ')
2837 /* Bug 5256745 */
2838 ||parameter_record(21).p_output_value
2839 ||parameter_record(22).p_output_value
2840 ||rpad(parameter_record(23).p_output_value,34,' ')
2841 ||rpad(substr(nvl(r_input_35,'0'),1,1),2)
2842 ||rpad(substr(nvl(r_input_36,'0'),1,1),1)
2843 ||rpad(substr(nvl(r_input_37,'0'),1,1),24);
2844 END IF ;
2845 ret_str_len:=length(return_value);
2846 hr_utility.trace('ret_str_len = '||to_char(ret_str_len));
2847 --}
2848 ELSIF p_input_40 = 'CSV' THEN
2849 --{
2850 hr_utility.trace('CSV');
2851 return_value :=
2852 'RW'||','||replace(p_input_2,',')
2853 ||','||replace(p_input_3,',',' ')||','||replace(p_input_4,',',' ')
2854 ||','||replace(p_input_5,',',' ')||','||replace(p_input_6,',',' ')
2855 ||','||rpad(substr(nvl(p_input_7,' '),1,22),22)
2856 ||','||rpad(substr(nvl(p_input_8,' '),1,22),22)
2857 ||','||rpad(substr(nvl(p_input_9,' '),1,22),22)
2858 ||','||rpad(substr(nvl(p_input_10,' '),1,2),2)
2859 ||','||rpad(substr(nvl(p_input_11,' '),1,5),5)
2860 ||','||rpad(substr(nvl(p_input_12,' '),1,4),4)
2861 ||','||lpad(' ',5)
2862 ||','||rpad(substr(nvl(p_input_13,' '),1,23),23)
2863 ||','||rpad(substr(nvl(p_input_14,' '),1,15),15)
2864 ||','||rpad(substr(nvl(p_input_15,' '),1,2),2)
2865 ||','||p_input_16
2866 ||','||p_input_17
2867 ||','||p_input_18
2868 ||','||p_input_19
2869 ||','||p_input_20
2870 ||','||p_input_21
2871 ||','||p_input_22
2872 ||','||p_input_23
2873 ||','||p_input_24
2874 ||','||p_input_25
2875 ||','||p_input_26
2876 ||','||p_input_27
2877 ||','||p_input_28
2878 ||','||p_input_29
2879 -- commented to fix bug # 2297587 ||','||p_input_30
2880 ||','||lpad('0',11,'0') /* Bug 4859212 */
2881 ||','||p_input_31
2882 ||','||p_input_38 -- Bug 3680056 ER Contrib to HSA
2883 ||','||p_input_32
2884 ||','||p_input_41 -- Bug # 4391218
2885 ||','||lpad(' ',11)
2886 ||','||p_input_33
2887 ||','||p_input_34
2888 ||','||p_input_42 -- Bug # 4391218
2889 /* Bug 5256745 */
2890 ||','||p_input_43
2891 ||','||p_input_44
2892 ||','||lpad(' ',23)
2893 ||','||rpad(nvl(p_input_35,'0'),1)
2894 ||','||lpad(' ',1)
2895 ||','||rpad(nvl(p_input_36,'0'),1)
2896 ||','||rpad(nvl(p_input_37,'0'),1)
2897 ||','||lpad(' ',23);
2898 --}
2899 ELSIF p_input_40 = 'BLANK' THEN
2900 --{
2901 hr_utility.trace('Formatting BLANK RW Record ');
2902 return_value :=
2903 ' '||','||' '
2904 ||','||' '||','||' '
2905 ||','||' '||','||' '
2906 ||','||' '
2907 ||','||' '
2908 ||','||' '
2909 ||','||' '
2910 ||','||' '
2911 ||','||' '
2912 ||','||lpad(' ',5)
2913 ||','||' '
2914 ||','||' '
2915 ||','||' '
2916 ||','||' '
2917 ||','||' '
2918 ||','||' '
2919 ||','||' '
2920 ||','||' '
2921 ||','||' '
2922 ||','||' '
2923 ||','||' '
2924 ||','||' '
2925 ||','||' '
2926 ||','||' '
2927 ||','||' '
2928 ||','||' '
2929 ||','||' '
2930 ||','||' '
2931 ||','||' '
2932 ||','||' ' -- Bug 3680056 ER Contrib to HSA
2933 ||','||' '
2934 ||','||' ' -- Bug # 4391218
2935 ||','||lpad(' ',11)
2936 ||','||' '
2937 ||','||' '
2938 ||','||' '
2939 /* Bug 5256745 */
2940 ||','||' '
2941 ||','||' '
2942 ||','||lpad(' ',23)
2943 ||','||' '
2944 ||','||lpad(' ',1)
2945 ||','||' '
2946 ||','||' '
2947 ||','||' ';
2948 hr_utility.trace(return_value);
2949 --}
2950 END IF;
2951 p_error := l_exclude_from_output_chk;
2952 ret_str_len:=length(return_value);
2953 return return_value;
2954
2955 END format_W2_RW_record;
2956 -- End of Formatting RW Record for W2 reporting
2957 --
2958 -- Formatting RO record for W2 reporting
2959 --
2960 /*-------------------------- Parameter Description ------------------------
2961 Record Identifier, --> p_input_1
2962 Allocated Tips, --> p_input_2
2963 Uncollected Employee Tax on Tips, --> p_input_3
2964 Medical Savings Account, --> p_input_4
2965 Simple Retirement Account, --> p_input_5
2966 Qualified Adoption Expenses, --> p_input_6
2967 Uncollected Social Security or RRTA Tax on GTL, --> p_input_7
2968 Uncollected Medicare Tax on GTL, --> p_input_8
2969 Civil Status, --> p_input_9
2970 Spouse''s Social Security Number (SSN), --> p_input_10
2971 Wages Subject to Puerto Rico Tax, --> p_input_11
2972 Commissions Subject to Puerto Rico Tax, --> p_input_12
2973 Allowances Subject to Puerto Rico Tax, --> p_input_13
2974 Tips Subject to Puerto Rico Tax, --> p_input_14
2975 Total Wages, Commissions, Tips, and Allow Sub. to PRTax, --> p_input_15
2976 Puerto Rico Tax Withheld, --> p_input_16
2977 Retirement Fund Annual Contributions, --> p_input_17
2978 Total Wages, Tips and other Compensation Subject to
2979 Virgin Islands, or Guam, or American Samoa, or Northern
2980 Mariana Islands Income Tax, --> p_input_18
2981 Virgin Islands, or Guam, or AS, or MP Income Tax Withheld --> p_input_19
2982 Marital Status S Single M Married etc --> p_input_20
2983 Employee SSN for Philadelphia use only --> p_input_21
2984 Income Under Section 409A on a Non-Qual Deferred Comp Plan --> p_input_22
2985 */
2986 FUNCTION format_W2_RO_record(
2987 p_effective_date IN varchar2,
2988 p_report_type IN varchar2,
2989 p_format IN varchar2,
2990 p_report_qualifier IN varchar2,
2991 p_record_name IN varchar2,
2992 p_input_1 IN varchar2,
2993 p_input_2 IN varchar2,
2994 p_input_3 IN varchar2,
2995 p_input_4 IN varchar2,
2996 p_input_5 IN varchar2,
2997 p_input_6 IN varchar2,
2998 p_input_7 IN varchar2,
2999 p_input_8 IN varchar2,
3000 p_input_9 IN varchar2,
3001 p_input_10 IN varchar2,
3002 p_input_11 IN varchar2,
3003 p_input_12 IN varchar2,
3004 p_input_13 IN varchar2,
3005 p_input_14 IN varchar2,
3006 p_input_15 IN varchar2,
3007 p_input_16 IN varchar2,
3008 p_input_17 IN varchar2,
3009 p_input_18 IN varchar2,
3010 p_input_19 IN varchar2,
3011 p_input_20 IN varchar2,
3012 p_input_21 IN varchar2,
3013 p_input_22 IN varchar2,
3014 p_input_23 IN varchar2,
3015 p_input_24 IN varchar2,
3016 p_input_25 IN varchar2,
3017 p_input_26 IN varchar2,
3018 p_input_27 IN varchar2,
3019 p_input_28 IN varchar2,
3020 p_input_29 IN varchar2,
3021 p_input_30 IN varchar2,
3022 p_input_31 IN varchar2,
3023 p_input_32 IN varchar2,
3024 p_input_33 IN varchar2,
3025 p_input_34 IN varchar2,
3026 p_input_35 IN varchar2,
3027 p_input_36 IN varchar2,
3028 p_input_37 IN varchar2,
3029 p_input_38 IN varchar2,
3030 p_input_39 IN varchar2,
3031 p_input_40 IN varchar2,
3032 p_validate IN varchar2,
3033 p_exclude_from_output OUT nocopy varchar2,
3034 sp_out_1 OUT nocopy varchar2,
3035 sp_out_2 OUT nocopy varchar2,
3036 sp_out_3 OUT nocopy varchar2,
3037 sp_out_4 OUT nocopy varchar2,
3038 sp_out_5 OUT nocopy varchar2,
3039 ret_str_len OUT nocopy number,
3040 p_error OUT nocopy boolean
3041 ) RETURN VARCHAR2
3042 IS
3043 l_full_name varchar2(100);
3044 l_emp_name_or_number varchar2(50);
3045 l_emp_number varchar2(50);
3046 l_first_name varchar2(150);
3047 l_middle_name varchar2(100);
3048 l_last_name varchar2(150);
3049 l_suffix varchar2(100);
3050 l_ssn varchar2(100);
3051 l_emp_ssn varchar2(100);
3052 l_message varchar2(2000);
3053 l_description varchar2(50);
3054 l_field_description varchar2(50);
3055 l_ss_count number(10);
3056 l_amount number(10);
3057 return_value varchar2(32767);
3058 l_err boolean;
3059 l_exclude_from_output_chk boolean;
3060 l_ss_tax_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
3061 l_ss_wage_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
3062
3063 TYPE function_columns IS RECORD(
3064 p_parameter_name varchar2(100),
3065 p_parameter_value varchar2(100),
3066 p_output_value varchar2(100)
3067 );
3068 function_parameter_rec function_columns;
3069 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
3070 INDEX BY BINARY_INTEGER;
3071 parameter_record input_parameter_record;
3072
3073 r_input_1 varchar2(300);
3074 r_input_2 varchar2(300);
3075 r_input_3 varchar2(300);
3076 r_input_4 varchar2(300);
3077 r_input_5 varchar2(300);
3078 r_input_6 varchar2(300);
3079 r_input_7 varchar2(300);
3080 r_input_8 varchar2(300);
3081 r_input_9 varchar2(300);
3082 r_input_10 varchar2(300);
3083 r_input_11 varchar2(300);
3084 r_input_12 varchar2(300);
3085 r_input_13 varchar2(300);
3086 r_input_14 varchar2(300);
3087 r_input_15 varchar2(300);
3088 r_input_16 varchar2(300);
3089 r_input_17 varchar2(300);
3090 r_input_18 varchar2(300);
3091 r_input_19 varchar2(300);
3092 r_input_20 varchar2(300);
3093 r_input_21 varchar2(300);
3094 r_input_22 varchar2(300);
3095 r_input_23 varchar2(300);
3096 r_input_24 varchar2(300);
3097 r_input_25 varchar2(300);
3098 r_input_26 varchar2(300);
3099 r_input_27 varchar2(300);
3100 r_input_28 varchar2(300);
3101 r_input_29 varchar2(300);
3102 r_input_30 varchar2(300);
3103 r_input_31 varchar2(300);
3104 r_input_32 varchar2(300);
3105 r_input_33 varchar2(300);
3106 r_input_34 varchar2(300);
3107 r_input_35 varchar2(300);
3108 r_input_36 varchar2(300);
3109 r_input_37 varchar2(300);
3110 r_input_38 varchar2(300);
3111 r_input_39 varchar2(300);
3112
3113 BEGIN
3114 hr_utility.trace('Formatting RO Record');
3115 hr_utility.trace('Format Mode p_input_40 '||p_input_40);
3116 -- Initializing local variables with parameter value
3117 r_input_2 := p_input_2;
3118 r_input_3 := p_input_3;
3119 r_input_4 := p_input_4;
3120 r_input_5 := p_input_5;
3121 r_input_6 := p_input_6;
3122 r_input_7 := p_input_7;
3123 r_input_8 := p_input_8;
3124 r_input_9 := p_input_9;
3125 r_input_10 := p_input_10;
3126 r_input_11 := p_input_11;
3127 r_input_12 := p_input_12;
3128 r_input_13 := p_input_13;
3129 r_input_14 := p_input_14;
3130 r_input_15 := p_input_15;
3131 r_input_16 := p_input_16;
3132 r_input_17 := p_input_17;
3133 r_input_18 := p_input_18;
3134 r_input_19 := p_input_19;
3135 r_input_20 := p_input_20;
3136 r_input_21 := p_input_21;
3137 r_input_22 := p_input_22;
3138 r_input_23 := p_input_23;
3139 r_input_24 := p_input_24;
3140 r_input_25 := p_input_25;
3141 r_input_26 := p_input_26;
3142 r_input_27 := p_input_27;
3143 r_input_28 := p_input_28;
3144 r_input_29 := p_input_29;
3145 r_input_30 := p_input_30;
3146 r_input_31 := p_input_31;
3147 r_input_32 := p_input_32;
3148 r_input_33 := p_input_33;
3149 r_input_34 := p_input_34;
3150 r_input_35 := p_input_35;
3151 r_input_36 := p_input_36;
3152 r_input_37 := p_input_37;
3153 r_input_38 := p_input_38;
3154 r_input_39 := p_input_39;
3155
3156 IF p_input_40 = 'FLAT' THEN
3157 -- Validation Starts
3158 --{
3159 parameter_record(1).p_parameter_name:= ' Allocated Tips';
3160 parameter_record(1).p_parameter_value:=p_input_2;
3161
3162 parameter_record(2).p_parameter_name:= 'Uncollected employee tax on tips';
3163 parameter_record(2).p_parameter_value:=p_input_3;
3164
3165 parameter_record(3).p_parameter_name:= 'medical savings a/c';
3166 parameter_record(3).p_parameter_value:=p_input_4;
3167
3168 parameter_record(4).p_parameter_name:= 'simple retirement a/c';
3169 parameter_record(4).p_parameter_value:=p_input_5;
3170
3171 parameter_record(5).p_parameter_name:= 'qualified adopted expenses';
3172 parameter_record(5).p_parameter_value:=p_input_6;
3173
3174 parameter_record(6).p_parameter_name:= 'Uncollected ss or RRTA tax';
3175 parameter_record(6).p_parameter_value:=p_input_7;
3176
3177 parameter_record(7).p_parameter_name:= 'Uncollected medicare tax';
3178 parameter_record(7).p_parameter_value:=p_input_8;
3179
3180 parameter_record(8).p_parameter_name:= 'wages sub. to PR tax';
3181 parameter_record(8).p_parameter_value:=p_input_11;
3182
3183 parameter_record(9).p_parameter_name:= 'Commissions sub.to PR tax';
3184 parameter_record(9).p_parameter_value:=p_input_12;
3185
3186 parameter_record(10).p_parameter_name:= 'Allowances sub. to PR tax';
3187 parameter_record(10).p_parameter_value:=p_input_13;
3188
3189 parameter_record(11).p_parameter_name:= 'Tips sub to PR tax';
3190 parameter_record(11).p_parameter_value:=p_input_14;
3191
3192 parameter_record(12).p_parameter_name:= 'Total wages sub to PR tax';
3193 parameter_record(12).p_parameter_value:=p_input_15;
3194
3195 parameter_record(13).p_parameter_name:= 'PR tax withheld';
3196 parameter_record(13).p_parameter_value:=p_input_16;
3197
3198 parameter_record(14).p_parameter_name:= 'Retirement fund ann. contributions';
3199 parameter_record(14).p_parameter_value:=p_input_17;
3200
3201 parameter_record(15).p_parameter_name:= 'Total wages sub to VI,GU,AS and MP islands';
3202 parameter_record(15).p_parameter_value:=p_input_18;
3203
3204 parameter_record(16).p_parameter_name:= 'VI,GU or MP Islands income tax wh';
3205 parameter_record(16).p_parameter_value:=p_input_19;
3206
3207 parameter_record(17).p_parameter_name:= 'Income Under Sec 409A on a Non-Qual Def Comp Plan';
3208 parameter_record(17).p_parameter_value := p_input_22;
3209
3210 hr_utility.trace('Before the data validation loop.');
3211 --
3212 -- This loop used to validation above 17 input values
3213 --
3214 FOR i in 1..17
3215 LOOP
3216 --{
3217 hr_utility.trace('Value of loop counter i is : ' || to_char(i));
3218 hr_utility.trace('Input '||parameter_record(i).p_parameter_name||' = '
3219 ||parameter_record(i).p_parameter_value);
3220
3221 parameter_record(i).p_output_value :=
3222 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
3223 p_report_type,
3224 p_format,
3225 p_report_qualifier,
3226 p_record_name,
3227 'NEG_CHECK',
3228 parameter_record(i).p_parameter_value,
3229 parameter_record(i).p_parameter_name,
3230 p_input_39,
3231 null,
3232 p_validate,
3233 p_exclude_from_output,
3234 sp_out_1,
3235 sp_out_2);
3236
3237 IF p_exclude_from_output = 'Y' THEN
3238 l_exclude_from_output_chk := TRUE;
3239 END IF;
3240
3241 hr_utility.trace('Value of i is : ' || to_char(i));
3242 hr_utility.trace('Output ' || parameter_record(i).p_parameter_name||' = '
3243 ||parameter_record(i).p_output_value);
3244 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
3245 --}
3246 END LOOP; -- End of various Amount validation loop
3247 hr_utility.trace('After Amount data validation ');
3248 -- Spouse SSN validation
3249 IF p_input_20 = 'PR_M' THEN
3250 l_ssn:= replace(replace(p_input_10,' '),'I');
3251 IF l_ssn IS NULL THEN
3252 l_ssn:='000000000';
3253 ELSE
3254 l_ssn :=
3255 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
3256 p_report_type,
3257 p_format,
3258 p_report_qualifier,
3259 p_record_name,
3260 'SSN',
3261 p_input_10,
3262 'Spouse Social Security',
3263 p_input_39,
3264 null,
3265 p_validate,
3266 p_exclude_from_output,
3267 sp_out_1,
3268 sp_out_2);
3269 IF p_exclude_from_output = 'Y' THEN
3270 l_exclude_from_output_chk := TRUE;
3271 END IF;
3272 END IF;
3273 ELSE
3274 l_ssn:=lpad(' ',9);
3275 END IF;
3276 hr_utility.trace('After Spouse SSN validation. SSN = '||l_ssn);
3277 -- This is added for Philadelphia locality
3278 --
3279 IF (p_report_qualifier = 'PA_PHILA') THEN
3280 IF p_input_21 IS NULL THEN
3281 l_emp_ssn:=lpad(' ',9);
3282 ELSE
3283 l_emp_ssn :=
3284 pay_us_reporting_utils_pkg.data_validation(
3285 p_effective_date,
3286 p_report_type,
3287 p_format,
3288 p_report_qualifier,
3289 p_record_name,
3290 'SSN',
3291 p_input_21,
3292 'Employee Social Security',
3293 p_input_39,
3294 null,
3295 p_validate,
3296 p_exclude_from_output,
3297 sp_out_1,
3298 sp_out_2);
3299 hr_utility.trace('For Philadelphia SSN after validation. = '||l_emp_ssn);
3300 IF p_exclude_from_output = 'Y' THEN
3301 l_exclude_from_output_chk := TRUE;
3302 END IF;
3303 END IF;
3304 END IF;
3305
3306 -- Validation Ends
3307 --
3308 -- Formatting Starts
3309
3310
3311 IF (p_report_qualifier = 'PA_PHILA') THEN
3312 r_input_9 := ' ';
3313 l_ssn := lpad(' ',9);
3314 r_input_11 := lpad('0',11,'0');
3315 r_input_12 := lpad('0',11,'0');
3316 r_input_13 := lpad('0',11,'0');
3317 r_input_14 := lpad('0',11,'0');
3318 r_input_15 := lpad('0',11,'0');
3319 r_input_16 := lpad('0',11,'0');
3320 r_input_17 := lpad('0',11,'0');
3321 r_input_18 := lpad('0',11,'0');
3322 r_input_19 := lpad('0',11,'0');
3323 ELSIF (p_report_qualifier = 'PR') THEN
3324 l_ssn := lpad(' ',9); /* Bug 4665713 */
3325 r_input_2 := lpad('0',11,'0');
3326 r_input_3 := parameter_record(2).p_output_value;
3327 r_input_4 := lpad('0',11,'0');
3328 r_input_5 := lpad('0',11,'0');
3329 r_input_6 := lpad('0',11,'0');
3330 r_input_7 := parameter_record(6).p_output_value;
3331 --r_input_8 := rpad(parameter_record(7).p_output_value,187);
3332 r_input_8 := parameter_record(7).p_output_value ;
3333 r_input_9 := ' '; -- Bug # 5668970
3334 r_input_22 := rpad(parameter_record(17).p_output_value,176) ;
3335 r_input_11 := parameter_record(8).p_output_value;
3336 r_input_12 := parameter_record(9).p_output_value;
3337 r_input_13 := parameter_record(10).p_output_value;
3338 r_input_14 := parameter_record(11).p_output_value;
3339 r_input_15 := parameter_record(12).p_output_value;
3340 r_input_16 := parameter_record(13).p_output_value;
3341 r_input_17 := parameter_record(14).p_output_value;
3342 ELSE
3343 l_emp_ssn := lpad(' ',9);
3344 r_input_11 := parameter_record(8).p_output_value;
3345 r_input_12 := parameter_record(9).p_output_value;
3346 r_input_13 := parameter_record(10).p_output_value;
3347 r_input_14 := parameter_record(11).p_output_value;
3348 r_input_15 := parameter_record(12).p_output_value;
3349 r_input_16 := parameter_record(13).p_output_value;
3350 r_input_17 := parameter_record(14).p_output_value;
3351 r_input_18 := parameter_record(15).p_output_value;
3352 r_input_19 := parameter_record(16).p_output_value;
3353 END IF;
3354 /* 6806139 */
3355 /* 5155648 */
3356 /* IF ( p_report_qualifier = 'FED' AND UPPER(p_input_23) = 'P') THEN
3357 r_input_11 := lpad('0',11,'0');
3358 END IF ; */
3359
3360
3361 /* This changes made for PR for fixing Bug # 2736928 */
3362 /* Bug # 3337295 fixed RO record fields for PR.
3363 These changes gone with YE 2003 Phase III
3364 position 352-362 - should now contain blanks
3365 363-384 - should contain zeroes
3366 385-512 - should now contain blanks
3367 */
3368 -- Formatting Ends
3369 IF (p_report_qualifier = 'PR') THEN
3370 --{
3371 return_value:='RO'
3372 ||lpad(' ',9)
3373 ||r_input_2
3374 ||r_input_3
3375 ||r_input_4
3376 ||r_input_5
3377 ||r_input_6
3378 /* ||r_input_7
3379 ||r_input_8 */
3380 ||lpad('0',22,'0') /* 6644795 */
3381 ||r_input_22
3382 -- ||rpad(nvl(r_input_9,' '),1) /* 6330489 */
3383 ||rpad(' ',1)/* 6330489 */
3384 --||l_ssn
3385 /* Bug 5256745 */
3386 ||lpad(' ',9)
3387 ||r_input_11
3388 ||r_input_12
3389 ||r_input_13
3390 ||r_input_14
3391 ||r_input_15
3392 ||r_input_16
3393 ||r_input_17
3394 ||lpad(' ',11)
3395 ||lpad('0',22,'0')
3396 ||lpad(' ',128);
3397 --}
3398 ELSIF p_report_qualifier NOT IN ('MO_KNSAS','MO_STLOU','OH_CCAAA','OH_DAYTO','OH_RTCCA','PA_PHILA') THEN
3399 --{
3400 return_value:='RO'
3401 ||lpad(l_emp_ssn,9) -- Added for Philadelphia locality
3402 ||lpad(parameter_record(1).p_output_value,11)
3403 ||parameter_record(2).p_output_value
3404 ||parameter_record(3).p_output_value
3405 ||parameter_record(4).p_output_value
3406 ||parameter_record(5).p_output_value
3407 ||parameter_record(6).p_output_value
3408 --||rpad(parameter_record(7).p_output_value,187)
3409 ||parameter_record(7).p_output_value
3410 ||rpad(parameter_record(17).p_output_value,176)
3411 -- ||rpad(nvl(r_input_9,' '),1) /* 6330489 */
3412 ||rpad(' ',1) /* 6330489 */
3413 --||l_ssn
3414 /* Bug 5256745 */
3415 ||lpad(' ',9)
3416 ||r_input_11
3417 ||r_input_12
3418 ||r_input_13
3419 ||r_input_14
3420 ||r_input_15
3421 ||r_input_16
3422 ||rpad(r_input_17,22)
3423 ||r_input_18
3424 ||rpad(r_input_19,139);
3425
3426 --}
3427 ELSE
3428
3429 return_value:='RO'
3430 ||lpad(l_emp_ssn,9) -- Added for Philadelphia locality
3431 ||lpad(parameter_record(1).p_output_value,11)
3432 ||parameter_record(2).p_output_value
3433 ||parameter_record(3).p_output_value
3434 ||parameter_record(4).p_output_value
3435 ||parameter_record(5).p_output_value
3436 ||parameter_record(6).p_output_value
3437 --||rpad(parameter_record(7).p_output_value,187)
3438 ||parameter_record(7).p_output_value
3439 ||rpad(parameter_record(17).p_output_value,176)
3440 ||rpad(nvl(r_input_9,' '),1)
3441 --||l_ssn
3442 /* Bug 5256745 */
3443 ||lpad(' ',9)
3444 ||r_input_11
3445 ||r_input_12
3446 ||r_input_13
3447 ||r_input_14
3448 ||r_input_15
3449 ||r_input_16
3450 ||rpad(r_input_17,22)
3451 ||r_input_18
3452 ||rpad(r_input_19,139);
3453 END IF;
3454
3455 ret_str_len:=length(return_value);
3456 hr_utility.trace('length of return_Value RO = '||to_char(ret_str_len));
3457 --}
3458 ELSIF p_input_40 = 'CSV' THEN
3459 --{
3460 -- Format Mode CSV
3461 --
3462 IF (p_report_qualifier = 'PR') THEN
3463 --{
3464 return_value:=','|| -- As RO_Status column removed from RO record header coma is now prefixed
3465 p_input_1
3466 ||','||lpad(' ',9)
3467 ||','||p_input_2
3468 ||','||p_input_3
3469 ||','||p_input_4
3470 ||','||p_input_5
3471 ||','||p_input_6
3472 ||','||p_input_7
3473 ||','||p_input_8
3474 ||','||p_input_22
3475 ||','||lpad(' ',165)
3476 ||','||rpad(nvl(p_input_9,' '),1)
3477 ||','||p_input_10
3478 ||','||p_input_11
3479 ||','||p_input_12
3480 ||','||p_input_13
3481 ||','||p_input_14
3482 ||','||p_input_15
3483 ||','||p_input_16
3484 ||','||p_input_17
3485 ||','||lpad(' ',11)
3486 ||','||lpad('0',11,'0')
3487 ||','||lpad('0',11,'0')
3488 ||','||lpad(' ',128);
3489 --}
3490 ELSE
3491 --{
3492 return_value:=','|| -- As RO_Status column removed from RO record header coma is now prefixed
3493 p_input_1
3494 ||','||lpad(' ',9)
3495 ||','||p_input_2
3496 ||','||p_input_3
3497 ||','||p_input_4
3498 ||','||p_input_5
3499 ||','||p_input_6
3500 ||','||p_input_7
3501 ||','||p_input_8
3502 ||','||p_input_22
3503 ||','||lpad(' ',165)
3504 ||','||rpad(nvl(p_input_9,' '),1)
3505 ||','||p_input_10
3506 ||','||p_input_11
3507 ||','||p_input_12
3508 ||','||p_input_13
3509 ||','||p_input_14
3510 ||','||p_input_15
3511 ||','||p_input_16
3512 ||','||p_input_17
3513 ||','||lpad(' ',11)
3514 ||','||p_input_18
3515 ||','||p_input_19
3516 ||','||lpad(' ',128);
3517 --}
3518
3519 END IF;
3520 ELSIF p_input_40 = 'BLANK' THEN
3521 --{
3522 -- Format Mode BLANK (used for Formatting Blank RO Record)
3523 --
3524 IF (p_report_qualifier = 'PR') THEN
3525 return_value:=--','|| As RO_Status column removed from RO record header coma is now prefixed
3526 ' '
3527 ||','||lpad(' ',9)
3528 ||','||' '
3529 ||','||' '
3530 ||','||' '
3531 ||','||' '
3532 ||','||' '
3533 ||','||' '
3534 ||','||' '
3535 ||','||' '
3536 ||','||lpad(' ',165)
3537 ||','||' '
3538 ||','||' '
3539 ||','||' '
3540 ||','||' '
3541 ||','||' '
3542 ||','||' '
3543 ||','||' '
3544 ||','||' '
3545 ||','||' '
3546 ||','||lpad(' ',11)
3547 ||','||' '
3548 ||','||' '
3549 ||','||lpad(' ',128);
3550 --}
3551 ELSE
3552 return_value:= ' '
3553 ||','||lpad(' ',9)
3554 ||','||' '
3555 ||','||' '
3556 ||','||' '
3557 ||','||' '
3558 ||','||' '
3559 ||','||' '
3560 ||','||' '
3561 ||','||' '
3562 ||','||lpad(' ',165)
3563 ||','||' '
3564 ||','||' '
3565 ||','||' '
3566 ||','||' '
3567 ||','||' '
3568 ||','||' '
3569 ||','||' '
3570 ||','||' '
3571 ||','||' '
3572 ||','||lpad(' ',11)
3573 ||','||' '
3574 ||','||' '
3575 ||','||lpad(' ',128);
3576 END IF ;
3577 END IF;
3578 p_error := l_exclude_from_output_chk;
3579 ret_str_len:=length(return_value);
3580 return return_value;
3581
3582 END format_W2_RO_record;
3583 -- End of formatting RO record for W2 reporting
3584 --
3585
3586 /* ---------------------- Parameter Mapping -----------------------
3587 Record Identifier --> p_input_1
3588 State Code --> p_input_2
3589 Taxing Entity Code --> p_input_3
3590 Social Security Number (SSN) --> p_input_4
3591 Employee First Name --> p_input_5
3592 Employee Middle Name or Initial --> p_input_6
3593 Employee Last Name --> p_input_7
3594 Suffix --> p_input_8
3595 Location Address --> p_input_9
3596 Delivery Address --> p_input_10
3597 City --> p_input_11
3598 State Abbreviation --> p_input_12
3599 Zip Code --> p_input_13
3600 Zip Code Extension --> p_input_14
3601 Foreign State / Province --> p_input_15
3602 Foreign Postal Code --> p_input_16
3603 Country Code --> p_input_17
3604 Optional Code --> p_input_18
3605 Reporting Period --> p_input_19
3606 State Quarterly Unemployment Insurance Total Wages --> p_input_20
3607 State Quarterly Unemployment Total Taxable Wages --> p_input_21
3608 Number of Weeks Worked --> p_input_22
3609 Date First Employed --> p_input_23
3610 Date of Separation --> p_input_24
3611 State Employer Account Number --> p_input_25
3612 State Code --> p_input_26
3613 State Taxable Wages --> p_input_27
3614 SIT Withheld --> p_input_28
3615 Other State Data --> p_input_29
3616 Tax Type Code --> p_input_30
3617 Local Taxable Wages --> p_input_31
3618 Local Income Tax Withheld --> p_input_32
3619 State Control Number --> p_input_33
3620 Supplemental Data 1 --> p_input_34
3621 Supplemental Data 2 --> p_input_35
3622
3623 Fed Wages Tips and other comp (for state MA) |
3624 Contrib to Qual Plans (for PR) |----> p_input_36
3625 FIT Withheld (for state MD) |
3626 Tax credit amt for KY indus revit act (state KY) |
3627 Federal Employer Account Number (for state AL) |
3628
3629 Supplemental Data for KS state --> p_input_34
3630 used for KS state EE contributions to public EE's retirement System
3631 (KPERS, KPF or Judges)
3632
3633 Malpractice Insurance Fund (MIF) for NJ State --> p_input_36
3634
3635 Cost Reimbursement (for PR) |----> p_input_37
3636 Tax credit amt for KY indus dev act (state KY) |
3637 Employee Withholding Allowance provided on W4 |
3638 as of 31st December,YYYY (for Maryland) |
3639
3640 Serial Number (for PR) --> p_input_38
3641 EE number used for trace messg purpose --> p_input_39
3642
3643 */
3644
3645 FUNCTION format_W2_RS_record(
3646 p_effective_date IN varchar2,
3647 p_report_type IN varchar2,
3648 p_format IN varchar2,
3649 p_report_qualifier IN varchar2,
3650 p_record_name IN varchar2,
3651 p_input_1 IN varchar2,
3652 p_input_2 IN varchar2,
3653 p_input_3 IN varchar2,
3654 p_input_4 IN varchar2,
3655 p_input_5 IN varchar2,
3656 p_input_6 IN varchar2,
3657 p_input_7 IN varchar2,
3658 p_input_8 IN varchar2,
3659 p_input_9 IN varchar2,
3660 p_input_10 IN varchar2,
3661 p_input_11 IN varchar2,
3662 p_input_12 IN varchar2,
3663 p_input_13 IN varchar2,
3664 p_input_14 IN varchar2,
3665 p_input_15 IN varchar2,
3666 p_input_16 IN varchar2,
3667 p_input_17 IN varchar2,
3668 p_input_18 IN varchar2,
3669 p_input_19 IN varchar2,
3670 p_input_20 IN varchar2,
3671 p_input_21 IN varchar2,
3672 p_input_22 IN varchar2,
3673 p_input_23 IN varchar2,
3674 p_input_24 IN varchar2,
3675 p_input_25 IN varchar2,
3676 p_input_26 IN varchar2,
3677 p_input_27 IN varchar2,
3678 p_input_28 IN varchar2,
3679 p_input_29 IN varchar2,
3680 p_input_30 IN varchar2,
3681 p_input_31 IN varchar2,
3682 p_input_32 IN varchar2,
3683 p_input_33 IN varchar2,
3684 p_input_34 IN varchar2,
3685 p_input_35 IN varchar2,
3686 p_input_36 IN varchar2,
3687 p_input_37 IN varchar2,
3688 p_input_38 IN varchar2,
3689 p_input_39 IN varchar2,
3690 p_input_40 IN varchar2,
3691 p_validate IN varchar2,
3692 p_exclude_from_output OUT nocopy varchar2,
3693 sp_out_1 OUT nocopy varchar2,
3694 sp_out_2 OUT nocopy varchar2,
3695 sp_out_3 OUT nocopy varchar2,
3696 sp_out_4 OUT nocopy varchar2,
3697 sp_out_5 OUT nocopy varchar2,
3698 ret_str_len OUT nocopy number,
3699 p_error OUT nocopy boolean
3700 ) RETURN VARCHAR2
3701 IS
3702 return_value varchar2(32767);
3703 l_s_hyphen_position number := 0;
3704 l_pblm_code varchar2(1);
3705 l_preparer_code varchar2(1);
3706 l_input_1 varchar2(100);
3707 l_records number(10);
3708 l_input_2 varchar2(100);
3709 l_record_identifier varchar2(2);
3710 l_tax_year date;
3711 l_agent_indicator varchar2(1);
3712 l_emp_ein varchar2(100);
3713 l_term_indicator varchar2(1);
3714 l_agent_ein varchar2(100);
3715 l_other_ein varchar2(100);
3716 l_input_8 varchar2(50);
3717 l_check varchar2(1);
3718 l_employment_code varchar2(1);
3719 p_exc varchar2(10);
3720 main_return_string varchar2(300);
3721 l_resub_tlcn varchar2(100);
3722 l_pin varchar2(50);
3723 l_ssn varchar2(100);
3724 l_wages_tips varchar2(100);
3725 l_full_name varchar2(100);
3726 l_emp_name_or_number varchar2(50);
3727 l_emp_number varchar2(50);
3728 l_first_name varchar2(150);
3729 l_middle_name varchar2(100);
3730 l_last_name varchar2(150);
3731 l_suffix varchar2(100);
3732 l_mif varchar2(100);
3733 l_err boolean;
3734 l_exclude_from_output_chk boolean;
3735 l_message varchar2(2000);
3736 l_ss_tax_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
3737 l_description varchar2(50);
3738 l_field_description varchar2(50);
3739 l_ss_wage_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
3740 l_ss_count number(10);
3741 l_amount number(10);
3742 l_tax_ct_job_dev varchar2(30);
3743 l_tax_ct_ind_revit varchar2(30);
3744 l_tax_ct_ind_dev varchar2(30);
3745 l_tax_ct_rural varchar2(30);
3746 l_fit_wh varchar2(30);
3747 l_total_records varchar2(50);
3748 l_wages varchar2(100);
3749 l_taxes varchar2(100);
3750 l_deferred_comp varchar2(100);
3751 l_sdi_wh varchar2(100);
3752 l_state_length number(10);
3753 l_unemp_insurance varchar2(100);
3754 l_fica_mcr_wh varchar2(100);
3755 l_bus_tax_acct_number varchar2(50);
3756 l_w2_govt_ee_contrib varchar2(100);
3757 l_w2_fed_wages varchar2(100);
3758 l_wa_sqwl_outstring varchar2(200);
3759
3760 /* PuertoRico W2 related variables Bug # 2736928 */
3761 l_contact_person_phone_no varchar2(100); -- mapped to r_input_34
3762 l_pension_annuity varchar2(100); -- mapped to r_input_35
3763 l_contribution_plan varchar2(100); -- mapped to r_input_36
3764 l_cost_reimbursement varchar2(100); -- mapped to r_input_37
3765 l_uncollected_ss_tax_on_tips varchar2(100); -- mapped to r_input_31
3766 l_uncollected_med_tax_on_tips varchar2(100); -- mapped to r_input_32
3767 l_rt_end_of_rec varchar2(200);
3768 l_end_of_rec varchar2(20);
3769 /* Bug 2789523 */
3770 l_last_field varchar2(100);
3771 /* Bug 3180532 - local variable for audit record (CSV/BLANK) */
3772 l_audit_field_17 varchar2(100);
3773 l_fl_field_17_20 varchar2(100);
3774 /* EE contributions to public EE's retirement System */
3775 l_ee_contrib_pub_retire_system varchar2(100);
3776 /* Bug 4084765 */
3777 l_nj_dipp_plan_id varchar2(14);
3778
3779 /* Bug # 5513076 and 5637673 */
3780 swap_street_location_indiana varchar2(300);
3781 IN_state_adv_EIC varchar2(100);
3782
3783 r_input_1 varchar2(300);
3784 r_input_2 varchar2(300);
3785 r_input_3 varchar2(300);
3786 r_input_4 varchar2(300);
3787 r_input_5 varchar2(300);
3788 r_input_6 varchar2(300);
3789 r_input_7 varchar2(300);
3790 r_input_8 varchar2(300);
3791 r_input_9 varchar2(300);
3792 r_input_10 varchar2(300);
3793 r_input_11 varchar2(300);
3794 r_input_12 varchar2(300);
3795 r_input_13 varchar2(300);
3796 r_input_14 varchar2(300);
3797 r_input_15 varchar2(300);
3798 r_input_16 varchar2(300);
3799 r_input_17 varchar2(300);
3800 r_input_18 varchar2(300);
3801 r_input_19 varchar2(300);
3802 r_input_20 varchar2(300);
3803 r_input_21 varchar2(300);
3804 r_input_22 varchar2(300);
3805 r_input_23 varchar2(300);
3806 r_input_24 varchar2(300);
3807 r_input_25 varchar2(300);
3808 r_input_26 varchar2(300);
3809 r_input_27 varchar2(300);
3810 r_input_28 varchar2(300);
3811 r_input_29 varchar2(300);
3812 r_input_30 varchar2(300);
3813 r_input_31 varchar2(300);
3814 r_input_32 varchar2(300);
3815 r_input_33 varchar2(300);
3816 r_input_34 varchar2(500); /* Increased for Puerto Rico */
3817 r_input_35 varchar2(300);
3818 r_input_36 varchar2(300);
3819 r_input_37 varchar2(300);
3820 r_input_38 varchar2(300);
3821 r_input_39 varchar2(300);
3822
3823 BEGIN
3824 hr_utility.trace('Formatting RS record for W2 reporting');
3825 hr_utility.trace('p_report_qualifier = '||p_report_qualifier);
3826 -- Initializing local variables with parameter value
3827 --{
3828 r_input_2 := p_input_2;
3829 r_input_3 := p_input_3;
3830 r_input_4 := p_input_4;
3831 r_input_5 := p_input_5;
3832 r_input_6 := p_input_6;
3833 r_input_7 := p_input_7;
3834 r_input_8 := p_input_8;
3835 r_input_9 := p_input_9;
3836 r_input_10 := p_input_10;
3837 r_input_11 := p_input_11;
3838 r_input_12 := p_input_12;
3839 r_input_13 := p_input_13;
3840 r_input_14 := p_input_14;
3841 r_input_15 := p_input_15;
3842 r_input_16 := p_input_16;
3843 r_input_17 := p_input_17;
3844 r_input_18 := p_input_18;
3845 r_input_19 := p_input_19;
3846 r_input_20 := p_input_20;
3847 r_input_21 := p_input_21;
3848 r_input_22 := p_input_22;
3849 r_input_23 := p_input_23;
3850 r_input_24 := p_input_24;
3851 r_input_25 := p_input_25;
3852 r_input_26 := p_input_26;
3853 r_input_27 := p_input_27;
3854 r_input_28 := p_input_28;
3855 r_input_29 := p_input_29;
3856 r_input_30 := p_input_30;
3857 r_input_31 := p_input_31;
3858 r_input_32 := p_input_32;
3859 r_input_33 := p_input_33;
3860 r_input_34 := p_input_34;
3861 r_input_35 := p_input_35;
3862 r_input_36 := p_input_36;
3863 r_input_37 := p_input_37;
3864 r_input_38 := p_input_38;
3865 r_input_39 := p_input_39;
3866 --}
3867 l_state_length := length(replace(p_input_2,' ')); /* Fix for State code.*/
3868 IF l_state_length < 2 THEN
3869 r_input_2 := lpad(replace(p_input_2,' '),2,0);
3870 r_input_26 := lpad(replace(p_input_26,' '),2,0);
3871 hr_utility.trace('l_state_length = '||to_char(l_state_length));
3872 hr_utility.trace('l_state_length < 2.State code r_input_2 = '||r_input_2);
3873 hr_utility.trace('State code r_input_26 = '||r_input_26);
3874 END IF;
3875
3876 /* Pos:3 - 4 State Code Blank for AL,MA,MD */
3877 IF (/*(p_report_qualifier = 'MA') OR 6720319 */
3878 (p_report_qualifier = 'MD') OR
3879 (p_report_qualifier = 'MO_STLOU') OR
3880 (p_report_qualifier = 'OH_CCAAA') OR
3881 (p_report_qualifier = 'MO_KNSAS') OR
3882 -- (p_report_qualifier = 'KS') OR -- Added for Bug # 2644092 commented to fix bug # 4012469
3883 (p_report_qualifier = 'AL')) THEN
3884 r_input_2 := lpad(' ',2);
3885 ELSE
3886 r_input_2 := rpad(nvl(r_input_2,' '),2);
3887 END IF;
3888
3889 /* Pos:5 - 9 Taxing entity code blank for these states and local */
3890
3891 IF p_report_qualifier = 'OH_DAYTO' THEN
3892 r_input_3 := lpad(nvl(p_input_29,' '),5);
3893 ELSIF p_report_qualifier = 'PR' THEN /* For bug # 2736928 */
3894 r_input_3 := lpad('0',5,'0');
3895 ELSE
3896 r_input_3 := lpad(' ',5);
3897 END IF;
3898
3899 /* Pos:10 - 18 Social security number */
3900
3901 IF p_input_40 = 'FLAT' THEN
3902 l_ssn := pay_us_reporting_utils_pkg.data_validation(
3903 p_effective_date,
3904 p_report_type,
3905 p_format,
3906 p_report_qualifier,
3907 p_record_name,
3908 'SSN',
3909 r_input_4,
3910 'Social Security',
3911 p_input_39, --EE number for messg purpose.
3912 null,
3913 p_validate,
3914 p_exclude_from_output,
3915 sp_out_1,
3916 sp_out_2);
3917 IF p_exclude_from_output = 'Y' THEN
3918 l_exclude_from_output_chk := TRUE;
3919 END IF;
3920 ELSE
3921 l_ssn := replace(replace(r_input_4,'-'),',');
3922 END IF;
3923
3924 hr_utility.trace('SSN after Validation and Formatting = '||l_ssn);
3925
3926 /* Pos:19 - 33 First name
3927 Pos:34 - 48 Middle name
3928 Pos:49 - 68 Last name
3929 Pos:69 - 72 suffix */
3930
3931 l_first_name := pay_us_reporting_utils_pkg.Character_check(rpad(
3932 nvl(substr(r_input_5,1,15),' '),15));
3933 l_middle_name := pay_us_reporting_utils_pkg.Character_check(rpad(
3934 nvl(substr(r_input_6,1,15),' '),15));
3935 l_last_name := pay_us_reporting_utils_pkg.Character_check(rpad(
3936 nvl(substr(r_input_7,1,20),' '),20));
3937 l_suffix := pay_us_reporting_utils_pkg.Character_check(rpad(
3938 nvl(substr(r_input_8,1,4),' '),4));
3939 hr_utility.trace('l_suffix = '||l_suffix);
3940
3941 /* Suffix blank for MD,OH_RC,MD_SQWL,MN_SQWL,OH_SQWL */
3942 IF ((p_report_qualifier = 'MD') OR
3943 (p_report_qualifier = 'OH_CCAAA') OR
3944 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
3945 (p_report_qualifier = 'OH_RTCCA')) THEN
3946 l_suffix := lpad(' ',4);
3947 END IF;
3948
3949 /* Pos:69 - 149 blank for AL,NC,MO,WV */
3950 IF ((p_report_qualifier = 'AL') OR
3951 (p_report_qualifier = 'NC') OR
3952 (p_report_qualifier = 'MO') OR -- Fix for bug # 2149507
3953 (p_report_qualifier = 'WV') -- Bug # 3186636
3954 ) THEN
3955 --{
3956 hr_utility.trace('Pos:69 - 149 blank for state '||p_report_qualifier);
3957 l_suffix := lpad(' ',4);
3958 r_input_9 := lpad(' ',22);
3959 r_input_10 := lpad(' ',22);
3960 r_input_11 := lpad(' ',22);
3961 r_input_12 := lpad(' ',2);
3962 r_input_13 := lpad(' ',5);
3963 r_input_14 := lpad(' ',4);
3964 --}
3965 END IF;
3966
3967 IF (p_report_qualifier = 'LA') -- Bug # 3130999
3968 THEN
3969 r_input_9 := lpad(' ',22);
3970 END IF;
3971
3972
3973 /* Zip ext is null for MT and ME */
3974
3975 IF ((p_report_qualifier = 'MT') OR
3976 (p_report_qualifier = 'MO_STLOU') OR
3977 (p_report_qualifier = 'ME')) THEN
3978 r_input_14 := lpad(' ',4);
3979 END IF;
3980
3981 /* Pos:155 - 177 178 - 192 193 - 194
3982 Foreign State / Province --> r_input_15
3983 Foreign Postal Code --> r_input_16
3984 Country Code --> r_input_17
3985 Foreign address set to blank for following states and locals */
3986
3987 IF ((p_report_qualifier = 'AL') OR
3988 (p_report_qualifier = 'AR') OR
3989 (p_report_qualifier = 'GA') OR
3990 (p_report_qualifier = 'ID') OR
3991 (p_report_qualifier = 'KS') OR
3992 (p_report_qualifier = 'MD') OR
3993 (p_report_qualifier = 'DE') OR
3994 (p_report_qualifier = 'SC') OR
3995 (p_report_qualifier = 'LA') OR -- Bug # 3130999
3996 (p_report_qualifier = 'NE') OR
3997 (p_report_qualifier = 'ME') OR
3998 (p_report_qualifier = 'MA') OR
3999 (p_report_qualifier = 'PA') OR
4000 (p_report_qualifier = 'WV') OR -- Bug 3186636
4001 (p_report_qualifier = 'OH_RTCCA') OR
4002 (p_report_qualifier = 'OH_CCAAA') OR
4003 (p_report_qualifier = 'NC') OR
4004 (p_report_qualifier = 'MO') OR -- Fix for Bug # 2149507
4005 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
4006 (p_report_qualifier = 'MO_STLOU') OR
4007 -- (p_report_qualifier = 'OH_DAYTO') OR
4008 (p_report_qualifier = 'PA_PHILA') ) THEN
4009 --{
4010 r_input_15 := lpad(' ',23);
4011 r_input_16 := lpad(' ',15);
4012 r_input_17 := lpad(' ',2);
4013
4014 IF p_report_qualifier = 'ME' THEN --ME Country value is not null
4015 r_input_17 := rpad(nvl(p_input_17,' '),2);
4016 END IF;
4017 --}
4018 ELSE
4019 r_input_15 := rpad(nvl(r_input_15,' '),23);
4020 r_input_16 := rpad(nvl(r_input_16,' '),15);
4021 r_input_17 := rpad(nvl(r_input_17,' '),2);
4022 END IF;
4023
4024 -- Bug 4739790
4025 -- Removed blanking of r_input_10 as we require Deliver Address for MO_KNSAS
4026 IF (p_report_qualifier = 'MO_KNSAS') -- Fix for Bug # 3067494
4027 THEN
4028 r_input_14 := lpad(' ',4);
4029 END IF;
4030
4031 /* Pos:195 - 242 Optional code - Date of separation blanks */
4032 /* Pos:203-226 State quarterly unemployments details should be Zero filled
4033 for MO, OH and PA
4034 This is to fix bug # 2627606
4035 This is also true with many states for W2, so changes made for W2
4036 */
4037 --{
4038 r_input_18 := lpad(' ',2);
4039 r_input_19 := lpad(' ',6);
4040 r_input_20 := lpad('0',11,'0');
4041 r_input_21 := lpad('0',11,'0');
4042 r_input_22 := lpad('0',2,'0');
4043 r_input_23 := lpad(' ',8);
4044 r_input_24 := lpad(' ',8);
4045 /* Pos:197-202 Reporting period for SC,ME and MT */
4046 -- (p_report_qualifier = 'SC') OR
4047 -- Bug # 3134857 fixed to blank out 197-202 for State of SC
4048 --
4049 IF ((p_report_qualifier = 'ME') OR
4050 (p_report_qualifier = 'MT') OR
4051 (p_report_qualifier = 'SC')) THEN /* bug 6641801 */
4052 r_input_19 := rpad(nvl(p_input_19,' '),6);
4053 END IF;
4054 /* As per bug # 2668099 this should be zero filled
4055 IF p_report_qualifier = 'ME' THEN
4056 r_input_21 := lpad(' ',11);
4057 END IF;
4058 */
4059 /* Bug #2736928 Puertorico Bug Fix */
4060 IF (p_report_qualifier = 'PR') THEN
4061 r_input_15 := lpad(' ',23);
4062 r_input_16 := lpad(' ',15);
4063 r_input_17 := lpad(' ',2);
4064 r_input_18 := lpad('0',2,'0');
4065 r_input_19 := rpad('0',6,'0');
4066 END IF;
4067 --}
4068
4069 /* Pos:248-267 State Employer Account number is blank for ME,MA,MD,IN,PA and OH RITA */
4070
4071 --{
4072 IF ((p_report_qualifier = 'IN') OR
4073 (p_report_qualifier = 'ME') OR
4074 -- Bug# 5693183
4075 -- (p_report_qualifier = 'GA') OR
4076 (p_report_qualifier = 'MA') OR
4077 (p_report_qualifier = 'MD') OR
4078 (p_report_qualifier = 'PR') OR
4079 (p_report_qualifier = 'OH_RTCCA') OR
4080 (p_report_qualifier = 'OH_CCAAA') OR
4081 (p_report_qualifier = 'MO_STLOU') OR
4082 (p_report_qualifier = 'MO_KNSAS') OR -- Bug # 3067494
4083 (p_report_qualifier = 'PA_PHILA') OR
4084 (p_report_qualifier = 'OH_DAYTO') OR
4085 (p_report_qualifier = 'PA')) THEN
4086
4087 r_input_25 := lpad(' ',20);
4088 -- Bug # 2673612
4089 ELSIF (p_report_qualifier = 'ID') THEN
4090 r_input_25 := lpad(replace(replace(nvl(replace(r_input_25,' '),' ')
4091 ,'-'),'/'),20,'0');
4092 -- Bug 3936924
4093 ELSIF (p_report_qualifier = 'AL') THEN
4094 r_input_25 := lpad(replace(replace(nvl(replace(r_input_25,' '),' ')
4095 ,'-'),'/'),10,'0') ||
4096 rpad(lpad(replace(replace(nvl(replace(r_input_36,' '),' ')
4097 ,'-'),'/'),9,'0'),10,' ');
4098 -- Bug 4022086
4099 ELSIF (p_report_qualifier = 'MS') THEN
4100 r_input_25 := rpad(replace(replace(nvl(replace(r_input_25,' '),' ')
4101 ,'-'),'/'),10,'0');
4102 r_input_36 := rpad(rpad(replace(replace(nvl(replace(r_input_36,' '),' ')
4103 ,'-'),'/'),9),10);
4104 IF p_input_40 = 'FLAT' THEN
4105 r_input_25 := r_input_25 || r_input_36;
4106 ELSE
4107 r_input_25 := r_input_25 || ',' || r_input_36;
4108 END IF;
4109 -- Bug# 5693183
4110 ELSIF (p_report_qualifier = 'GA') THEN
4111 r_input_25 := lpad('0', 20, '0') ;
4112 ELSE
4113 /* Bug:2159881 */
4114 r_input_25 := rpad(replace(replace(nvl(replace(r_input_25,' '),' ')
4115 ,'-'),'/'),20);
4116 END IF;
4117 --}
4118
4119 /* Pos:274 - 275 State code blank for PA,NJ,OH RITA ,NC and SQWL */
4120 IF ((p_report_qualifier = 'PA') OR
4121 (p_report_qualifier = 'NJ') OR
4122 (p_report_qualifier = 'MI') OR
4123 (p_report_qualifier = 'LA') OR -- Bug # 3130999
4124 (p_report_qualifier = 'OH_RTCCA') OR
4125 (p_report_qualifier = 'OH_CCAAA') OR
4126 (p_report_qualifier = 'MO_STLOU') OR
4127 (p_report_qualifier = 'PA_PHILA') OR
4128 (p_report_qualifier = 'NC') OR
4129 -- (p_report_qualifier = 'KS') OR -- commented to fix bug # 4012469 Fix for bug # 2644092
4130 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
4131 (p_report_qualifier = 'MO') -- Fix for Bug 2149507
4132 ) THEN
4133 r_input_26 := lpad(' ',2);
4134 ELSE
4135 r_input_26 := rpad(nvl(r_input_26,' '),2);
4136 END IF;
4137
4138 /* Pos:276 - 286 State taxable wages.
4139 Pos:287 - 297 SIT withheld. */
4140
4141 IF p_input_40 = 'FLAT' THEN
4142 r_input_27:=
4143 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4144 p_report_type,
4145 p_format,
4146 p_report_qualifier,
4147 p_record_name,
4148 'NEG_CHECK',
4149 r_input_27,
4150 'State taxable Wages',
4151 p_input_39,
4152 null,
4153 p_validate,
4154 p_exclude_from_output,
4155 sp_out_1,
4156 sp_out_2);
4157 IF p_exclude_from_output = 'Y' THEN
4158 l_exclude_from_output_chk := TRUE;
4159 END IF;
4160 r_input_28 :=
4161 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
4162 p_report_type,
4163 p_format,
4164 p_report_qualifier,
4165 p_record_name,
4166 'NEG_CHECK',
4167 r_input_28,
4168 'SIT Withheld',
4169 p_input_39,
4170 null,
4171 p_validate,
4172 p_exclude_from_output,
4173 sp_out_1,
4174 sp_out_2);
4175 IF p_exclude_from_output = 'Y' THEN
4176 l_exclude_from_output_chk := TRUE;
4177 END IF;
4178 END IF;
4179
4180 /* SIT withheld and wages are zero fill for OH RITA. */
4181 IF (p_report_qualifier = 'OH_CCAAA')
4182 THEN
4183 r_input_27 := rpad(0,11,0);
4184 r_input_28 := rpad(0,11,0);
4185 END IF;
4186
4187 /* SIT withheld are zero fill for MO KANSAS */
4188 IF (p_report_qualifier = 'MO_KNSAS')
4189 THEN
4190 r_input_28 := rpad(0,11,0);
4191 END IF;
4192
4193 /* SIT withheld for St Louis, MO Local is zero filled */
4194 IF p_report_qualifier = 'MO_STLOU' THEN
4195 r_input_28 := rpad(0,11,0);
4196 hr_utility.trace('SIT Withheld = '||r_input_28);
4197 END IF;
4198
4199 /* Pos:298 - 307 Other state data AL,MD,OH*/
4200 /* Pos:298-307 State Excess Wages for LA_SQWL. */
4201 /* Added by tmehra for bug 2084851 */
4202 IF ((p_report_qualifier = 'AL') OR
4203 (p_report_qualifier = 'OH') OR
4204 (p_report_qualifier = 'MD') OR
4205 (p_report_qualifier = 'MS')) THEN
4206 --{
4207 If p_input_40 = 'FLAT' THEN
4208 hr_utility.trace(' Other state data AL, OH, MD, MS ');
4209 r_input_29 :=
4210 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4211 p_report_type,
4212 p_format,
4213 p_report_qualifier,
4214 p_record_name,
4215 'NEG_CHECK',
4216 r_input_29,
4217 'Other State Data',
4218 p_input_39,
4219 null,
4220 p_validate,
4221 p_exclude_from_output,
4222 sp_out_1,
4223 sp_out_2);
4224 IF p_exclude_from_output = 'Y' THEN
4225 l_exclude_from_output_chk := TRUE;
4226 END IF;
4227 END IF;
4228 --}
4229 /* ELSIF (p_report_qualifier = 'GA') THEN
4230 r_input_29 := r_input_29 ;*/
4231 /* Modified only for GA for Bug# 5717438 */
4232 ELSIF (p_report_qualifier = 'GA') THEN
4233 r_input_29 := lpad(NVL(r_input_29,' '),10); -- Bug 5651314
4234 ELSE
4235 r_input_29 := lpad(' ',10);
4236 END IF;
4237
4238 /* BUG 5717304 and Bug 5717384 */
4239 IF (p_report_qualifier = 'KY' OR
4240 p_report_qualifier = 'NJ' )
4241 THEN
4242 r_input_29 := lpad(NVL(' ',' '),10);
4243 END IF;
4244
4245 /* Pos:308 - 308 Tax type code IN and OH */
4246 IF p_report_qualifier = 'OH' THEN
4247 r_input_30 := rpad(nvl(r_input_30,' '),1);
4248 ELSIF p_report_qualifier = 'IN' THEN /*Bug:2128995 */
4249 r_input_29 := lpad(' ',9);
4250 r_input_30 := lpad(nvl(r_input_30,' '),2,'0');
4251 ELSIF p_report_qualifier = 'PR' THEN /*Bug:2736928 */
4252 -- r_input_30 := 'F';
4253 r_input_30 := ' '; -- Bug # 3337295
4254 ELSE
4255 r_input_30 := lpad(' ', 1);
4256 END IF;
4257
4258 /* Pos:309 - 319 Local taxable wages OH ,IN and OH RITA
4259 Pos:320 - 330 Local Income tax withheld */
4260
4261 IF ((p_report_qualifier = 'OH') OR
4262 (p_report_qualifier = 'OH_RTCCA') OR
4263 (p_report_qualifier = 'OH_CCAAA') OR
4264 (p_report_qualifier = 'MO_STLOU') OR
4265 (p_report_qualifier = 'MO_KNSAS') OR -- Fix for Bug # 3067494
4266 (p_report_qualifier = 'PA_PHILA') OR
4267 (p_report_qualifier = 'OH_DAYTO') OR
4268 (p_report_qualifier = 'IN') ) THEN
4269 --{
4270 IF p_input_40 = 'FLAT' THEN
4271 -- Validating Local Taxable Wages
4272 r_input_31 :=
4273 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4274 p_report_type,
4275 p_format,
4276 p_report_qualifier,
4277 p_record_name,
4278 'NEG_CHECK',
4279 r_input_31,
4280 'Local taxable Wages',
4281 p_input_39,
4282 null,
4283 p_validate,
4284 p_exclude_from_output,
4285 sp_out_1,
4286 sp_out_2);
4287 IF p_exclude_from_output = 'Y' THEN
4288 l_exclude_from_output_chk := TRUE;
4289 END IF;
4290 -- Validating Local Income Tax withheld
4291 r_input_32 := pay_us_reporting_utils_pkg.data_validation( p_effective_date,
4292 p_report_type,
4293 p_format,
4294 p_report_qualifier,
4295 p_record_name,
4296 'NEG_CHECK',
4297 r_input_32,
4298 'Local Income tax withheld',
4299 p_input_39,
4300 null,
4301 p_validate,
4302 p_exclude_from_output,
4303 sp_out_1,
4304 sp_out_2);
4305 IF p_exclude_from_output = 'Y' THEN
4306 l_exclude_from_output_chk := TRUE;
4307 END IF;
4308 hr_utility.trace('Local Taxable Wages after validation n Formatting '
4309 ||r_input_31);
4310 hr_utility.trace('Local Tax Withhel after validation n Formatting '
4311 ||r_input_32);
4312 END IF;
4313 --}
4314 ELSE /* Zero Fill for other States. */
4315 hr_utility.trace('Zero fill for SQWLs. p_report_qualifier = '||p_report_qualifier);
4316 r_input_31 := rpad(0,11,0);
4317 r_input_32 := rpad(0,11,0);
4318 hr_utility.trace('r_input_31 '||r_input_31);
4319 hr_utility.trace('r_input_32 '||r_input_32);
4320 END IF;
4321
4322 /* Pos:309-330 Blank for SC_SQWL. State wages and SIT withheld. */
4323 IF (p_report_qualifier = 'AR') -- To fix bug # 2668250
4324 THEN
4325
4326 r_input_31 := lpad(' ',11);
4327 r_input_32 := lpad(' ',11);
4328 END IF;
4329
4330 /* Pos:331 - 337 State Control number OH,IN,KY,NJ and GA
4331 Pos:338 - 412 Supplemental data1
4332 Pos:413 - 487 Supplemental data2 */
4333 r_input_33 := replace(replace(replace(p_input_33,'-'),' '),'/');
4334
4335 IF p_report_qualifier = 'OH' THEN
4336 -- Bug 4730413
4337 r_input_33 := lpad(nvl(r_input_33,' '),7);
4338 r_input_34 := lpad(' ',75);
4339 r_input_35 := lpad(' ',75);
4340 /* Bug 3180532
4341 Pos: 193-203 Federal Advanced EIC
4342 Pos: 204 - 273 Blank fill
4343 Pos: 341 - 352 Box 19b - State Adv EIC
4344 Pos: 353 - 357 Box 20b - Adv EIC ID "INADV"
4345 Pos: 358 - 512 Blank fill */
4346 ELSIF p_report_qualifier = 'IN' THEN
4347 IF p_input_40 = 'FLAT' THEN
4348 r_input_17:=
4349 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4350 p_report_type,
4351 p_format,
4352 p_report_qualifier,
4353 p_record_name,
4354 'NEG_CHECK',
4355 p_input_17,
4356 'Federal Advanced EIC',
4357 p_input_39,
4358 null,
4359 p_validate,
4360 p_exclude_from_output,
4361 sp_out_1,
4362 sp_out_2);
4363 IF p_exclude_from_output = 'Y' THEN
4364 l_exclude_from_output_chk := TRUE;
4365 END IF;
4366
4367 r_input_34:=
4368 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4369 p_report_type,
4370 p_format,
4371 p_report_qualifier,
4372 p_record_name,
4373 'NEG_CHECK',
4374 r_input_34,
4375 'State Advanced EIC',
4376 p_input_39,
4377 null,
4378 p_validate,
4379 p_exclude_from_output,
4380 sp_out_1,
4381 sp_out_2);
4382 IF p_exclude_from_output = 'Y' THEN
4383 l_exclude_from_output_chk := TRUE;
4384 END IF;
4385 hr_utility.trace('Federal Advanced EIC after validation n Formatting '
4386 ||r_input_17);
4387 hr_utility.trace('State Advanced EIC after validation n Formatting '
4388 ||r_input_34);
4389
4390 r_input_21 := lpad(' ',11);
4391 r_input_22 := lpad(' ',2);
4392 r_input_33 := rpad(nvl(r_input_33,' '),10,0); -- Bug 5513076,5637673
4393 r_input_34 := rpad(lpad(r_input_34,11,0) || r_input_35,72); -- Bug 4720007
4394 r_input_35 := lpad(' ',75);
4395
4396 END IF;
4397 r_input_17 := lpad(r_input_17,11,0) ;
4398 -- Bug 3936924
4399 ELSIF p_report_qualifier = 'AL' THEN
4400 r_input_33 := lpad(' ',7);
4401 -- for bug 4279809
4402 r_input_34 := rpad((rpad(rpad('0',11,'0'),55,' ')||r_input_34),75,' ');
4403 --r_input_34 := rpad(lpad(r_input_34,59,' '),75,' ');
4404 r_input_35 := lpad(' ',75);
4405 IF p_input_40 = 'CSV' THEN
4406 r_input_25 := p_input_25 || ',' || p_input_36;
4407 END IF;
4408 ELSIF p_report_qualifier = 'GA' THEN /* 6855543 */
4409
4410 r_input_33 := rpad(nvl(r_input_33,' '),9);
4411 r_input_34 := rpad(substr(nvl(upper(p_input_30),' '),1,57),57)
4412 ||rpad(substr(nvl(p_input_31,' '),1,22),22)
4413 ||rpad(substr(nvl(p_input_32,' '),1,22),22)
4414 ||rpad(substr(nvl(p_input_34,' '),1,22),22)
4415 ||rpad(substr(nvl(p_input_35,' '),1,2),2)
4416 ||rpad(substr(nvl(p_input_36,' '),1,5),5)
4417 ||rpad(substr(nvl(p_input_37,' '),1,4),4)
4418 ||rpad(substr(replace(replace(nvl(replace(p_input_38,' '),' '),'-'),'/'),1,9),9);
4419 r_input_35 := lpad(' ',5);
4420 ELSIF p_report_qualifier = 'ME' THEN
4421 r_input_33 := lpad(' ',7);
4422 r_input_34 := rpad(rpad(nvl(replace(replace(r_input_34,'-'),' '),' '),11),75);
4423 r_input_35 := lpad(' ',75);
4424 ELSIF p_report_qualifier = 'MA' THEN
4425 r_input_33 := lpad(' ',7);
4426 IF p_input_40 = 'FLAT' THEN
4427 l_fica_mcr_wh :=
4428 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4429 p_report_type,
4430 p_format,
4431 p_report_qualifier,
4432 p_record_name,
4433 'NEG_CHECK',
4434 r_input_34,
4435 'FICA_MCR_WH',
4436 p_input_39,
4437 null,
4438 p_validate,
4439 p_exclude_from_output,
4440 sp_out_1,
4441 sp_out_2);
4442 l_w2_govt_ee_contrib :=
4443 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4444 p_report_type,
4445 p_format,
4446 p_report_qualifier,
4447 p_record_name,
4448 'NEG_CHECK',
4449 r_input_35,
4450 'W2_GOVT_EE_CONTRIB',
4451 p_input_39,
4452 null,
4453 p_validate,
4454 p_exclude_from_output,
4455 sp_out_1,
4456 sp_out_2);
4457 l_w2_fed_wages :=
4458 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4459 p_report_type,
4460 p_format,
4461 p_report_qualifier,
4462 p_record_name,
4463 'NEG_CHECK',
4464 r_input_36,
4465 'FED_WAGES',
4466 p_input_39,
4467 null,
4468 p_validate,
4469 p_exclude_from_output,
4470 sp_out_1,
4471 sp_out_2);
4472 /* r_input_34 := l_fica_mcr_wh||l_w2_govt_ee_contrib||l_w2_fed_wages
4473 ||lpad(' ',42); */
4474 r_input_34 := lpad(' ',75); /* 6720319 */
4475 r_input_35 := lpad(' ',75);
4476 ELSE
4477 r_input_34 := r_input_34;
4478 r_input_35 := 'Federal Railroad MA and Local Govt '||
4479 'retirement contribution '||
4480 p_input_35||' | '||
4481 'Fed Wages Tips and other comp '||p_input_36;
4482 END IF;
4483
4484 ELSIF p_report_qualifier = 'NJ' THEN
4485 r_input_33 := lpad(' ',7);
4486 IF p_input_40 = 'FLAT' THEN
4487
4488 l_mif :=
4489 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4490 p_report_type,
4491 p_format,
4492 p_report_qualifier,
4493 p_record_name,
4494 'NEG_CHECK',
4495 p_input_36,
4496 'MIF',
4497 p_input_39,
4498 null,
4499 p_validate,
4500 p_exclude_from_output,
4501 sp_out_1,
4502 sp_out_2);
4503 IF p_exclude_from_output = 'Y' THEN
4504 l_exclude_from_output_chk := TRUE;
4505 END IF;
4506
4507 l_unemp_insurance :=
4508 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4509 p_report_type,
4510 p_format,
4511 p_report_qualifier,
4512 p_record_name,
4513 'NEG_CHECK',
4514 p_input_31,
4515 'Unemployment Insurance Tax',
4516 p_input_39,
4517 null,
4518 p_validate,
4519 p_exclude_from_output,
4520 sp_out_1,
4521 sp_out_2);
4522 l_sdi_wh :=
4523 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
4524 p_report_type,
4525 p_format,
4526 p_report_qualifier,
4527 p_record_name,
4528 'NEG_CHECK',
4529 p_input_32,
4530 'SDI Withheld',
4531 p_input_39,
4532 null,
4533 p_validate,
4534 p_exclude_from_output,
4535 sp_out_1,
4536 sp_out_2);
4537 l_deferred_comp :=
4538 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4539 p_report_type,
4540 p_format,
4541 p_report_qualifier,
4542 p_record_name,
4543 'NEG_CHECK',
4544 p_input_35,
4545 'Deferred Comp',
4546 p_input_39,
4547 null,
4548 p_validate,
4549 p_exclude_from_output,
4550 sp_out_1,
4551 sp_out_2);
4552
4553 /* Bug 4014356*/
4554 r_input_30 := lpad(l_mif,3,0);
4555 r_input_31 := lpad(' ',9);
4556 r_input_32 := lpad(' ',11);
4557 r_input_33 := lpad(' ',7);
4558
4559 -- Bug 3895206 - Last 14 characters of NJ DIPP ID only reqd
4560 -- Bug 4084765 - Added logic for displaying DIPP plan ID whose length is less than 14
4561 IF length(p_input_30) >= 14 THEN
4562 l_nj_dipp_plan_id := rpad(substr(p_input_30,-14),14);
4563 ELSE
4564 l_nj_dipp_plan_id := substr(rpad(nvl(p_input_30,' '),14),-14);
4565 END IF;
4566
4567 r_input_34 := rpad(nvl(p_input_29,' '),1)|| l_nj_dipp_plan_id
4568 ||l_unemp_insurance ||l_sdi_wh||rpad(
4569 nvl(p_input_33,' '),1)||rpad(nvl(p_input_34,' '),1)
4570 ||l_deferred_comp;
4571 r_input_35 := lpad(' ',114);
4572
4573 ELSE
4574 r_input_30 := p_input_36;
4575 r_input_34 := r_input_29||','||p_input_30||','||p_input_31||','||
4576 p_input_32||','||p_input_33||','||p_input_34||','||
4577 p_input_35;
4578 r_input_35 := lpad(' ',10);
4579 END IF;
4580 ELSIF p_report_qualifier = 'KY' THEN
4581 r_input_33 := rpad(nvl(r_input_33,' '),7);
4582 IF p_input_40 = 'FLAT' THEN
4583 l_tax_ct_rural :=
4584 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4585 p_report_type,
4586 p_format,
4587 p_report_qualifier,
4588 p_record_name,
4589 'NEG_CHECK',
4590 r_input_34,
4591 'Tax credit amount for KY rural asst.',
4592 p_input_39,
4593 null,
4594 p_validate,
4595 p_exclude_from_output,
4596 sp_out_1,
4597 sp_out_2);
4598 l_tax_ct_job_dev :=
4599 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4600 p_report_type,
4601 p_format,
4602 p_report_qualifier,
4603 p_record_name,
4604 'NEG_CHECK',
4605 r_input_35,
4606 'Tax credit amount for KY job dev act',
4607 p_input_39,
4608 null,
4609 p_validate,
4610 p_exclude_from_output,
4611 sp_out_1,
4612 sp_out_2);
4613 l_tax_ct_ind_revit :=
4614 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4615 p_report_type,
4616 p_format,
4617 p_report_qualifier,
4618 p_record_name,
4619 'NEG_CHECK',
4620 r_input_36,
4621 'Tax credit amt for KY indus revit act',
4622 p_input_39,
4623 null,
4624 p_validate,
4625 p_exclude_from_output,
4626 sp_out_1,
4627 sp_out_2);
4628 l_tax_ct_ind_dev :=
4629 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4630 p_report_type,
4631 p_format,
4632 p_report_qualifier,
4633 p_record_name,
4634 'NEG_CHECK',
4635 r_input_37,
4636 'Tax credit amt for KY indus dev act',
4637 p_input_39,
4638 null,
4639 p_validate,
4640 p_exclude_from_output,
4641 sp_out_1,
4642 sp_out_2);
4643 r_input_34 := l_tax_ct_rural||l_tax_ct_job_dev||l_tax_ct_ind_revit
4644 ||l_tax_ct_ind_dev||lpad(' ',31);
4645 r_input_35 := lpad(' ',75);
4646 ELSE
4647 r_input_34 := r_input_34||','||r_input_35||','||r_input_36||','||r_input_37;
4648 r_input_35 := lpad(' ',75);
4649 END IF;
4650 ELSIF p_report_qualifier = 'MD' THEN
4651 --{
4652 r_input_33 := lpad(' ',7);
4653 IF p_input_40 = 'FLAT' THEN
4654 l_wages_tips :=
4655 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4656 p_report_type,
4657 p_format,
4658 p_report_qualifier,
4659 p_record_name,
4660 'NEG_CHECK',
4661 p_input_35,
4662 'Wages Tips',
4663 p_input_39,
4664 null,
4665 p_validate,
4666 p_exclude_from_output,
4667 sp_out_1,
4668 sp_out_2);
4669 l_fit_wh :=
4670 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4671 p_report_type,
4672 p_format,
4673 p_report_qualifier,
4674 p_record_name,
4675 'NEG_CHECK',
4676 r_input_36,
4677 'FIT Withheld',
4678 p_input_39,
4679 null,
4680 p_validate,
4681 p_exclude_from_output,
4682 sp_out_1,
4683 sp_out_2);
4684
4685 -- Bug 4728539
4686 -- 368-369 should be filled with ZERO's
4687 -- Bug# 4736977
4688 -- 368-369 should report Employee Withholding Allowances
4689 r_input_34 := rpad(nvl(r_input_34,' '),8)||l_wages_tips||l_fit_wh||lpad(r_input_37,2,'0')||lpad(' ',43);
4690 r_input_35 := lpad(' ',75);
4691 ELSE
4692 r_input_34 := r_input_34||','||r_input_35||','||r_input_36;
4693 r_input_35 := lpad(' ',8);
4694 END IF;
4695 --}
4696 /* Start of PuertoRico Supplemental Data formating */
4697 ELSIF p_report_qualifier = 'PR' THEN
4698 --{
4699 /* These changes added for Bug # 3337295 */
4700 --{
4701
4702 r_input_23 := lpad(0,8,0);
4703 r_input_24 := lpad(0,8,0);
4704 --r_input_26 := lpad(' ',2);
4705 r_input_26 := '00'; -- Bug # 5668970
4706 r_input_27 := rpad(0,11,0);
4707 r_input_28 := rpad(0,11,0);
4708 r_input_29 := lpad(0,10,0);
4709
4710 --}
4711
4712 r_input_33 := lpad(' ',7);
4713 IF p_input_40 = 'FLAT' THEN
4714 l_pension_annuity :=
4715 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4716 p_report_type,
4717 p_format,
4718 p_report_qualifier,
4719 p_record_name,
4720 'NEG_CHECK',
4721 p_input_35,
4722 'Cost of Pension Annuity',
4723 p_input_39,
4724 null,
4725 p_validate,
4726 p_exclude_from_output,
4727 sp_out_1,
4728 sp_out_2);
4729 l_contribution_plan :=
4730 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4731 p_report_type,
4732 p_format,
4733 p_report_qualifier,
4734 p_record_name,
4735 'NEG_CHECK',
4736 p_input_36,
4737 'Contrib to Qual Plans',
4738 p_input_39,
4739 null,
4740 p_validate,
4741 p_exclude_from_output,
4742 sp_out_1,
4743 sp_out_2);
4744 l_cost_reimbursement :=
4745 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4746 p_report_type,
4747 p_format,
4748 p_report_qualifier,
4749 p_record_name,
4750 'NEG_CHECK',
4751 p_input_37,
4752 'Cost Reimbursement',
4753 p_input_39,
4754 null,
4755 p_validate,
4756 p_exclude_from_output,
4757 sp_out_1,
4758 sp_out_2);
4759 l_uncollected_ss_tax_on_tips :=
4760 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4761 p_report_type,
4762 p_format,
4763 p_report_qualifier,
4764 p_record_name,
4765 'NEG_CHECK',
4766 p_input_31,
4767 'Uncollected SS Tax on Tips',
4768 p_input_39,
4769 null,
4770 p_validate,
4771 p_exclude_from_output,
4772 sp_out_1,
4773 sp_out_2);
4774 l_uncollected_med_tax_on_tips :=
4775 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4776 p_report_type,
4777 p_format,
4778 p_report_qualifier,
4779 p_record_name,
4780 'NEG_CHECK',
4781 p_input_32,
4782 'Uncollected Med Tax on Tips',
4783 p_input_39,
4784 null,
4785 p_validate,
4786 p_exclude_from_output,
4787 sp_out_1,
4788 sp_out_2);
4789 r_input_34 := rpad(nvl(' ',' '),10)||-- Contact Person Phone
4790 -- lpad(' ',8)|| -- Operations Closing Date 5876054
4791 lpad('0',8,'0')||
4792 lpad(nvl(replace(p_input_38,' '),' '),9,'0')|| -- Serial Number
4793 lpad(nvl(l_pension_annuity,'0'),11,'0')|| -- Cost of Pension Annuity
4794 lpad(nvl(l_contribution_plan,'0'),11,'0')|| -- Contributions to qualified Plans
4795 lpad(nvl(l_cost_reimbursement,'0'),11,'0')|| -- Cost Reimbursement
4796 lpad(' ',1)|| -- Amendment Indicator
4797 /* lpad(nvl(substr(replace(p_input_29,' '),1,5),' '),5)|| */ -- Access code
4798 lpad(' ',5)|| /* 6644795 */
4799 lpad(nvl(l_uncollected_ss_tax_on_tips,'0'),11,'0')|| -- Uncollected SS tax on Tips
4800 lpad(nvl(l_uncollected_med_tax_on_tips,'0'),11,'0'); -- Uncollected Med. Tax on Tips
4801 r_input_35 := lpad('0',5,'0') || lpad('0',11,'0') || lpad('0',8,'0') || lpad(' ',38); --Bug 4665713 Bug 5876054
4802 ELSE
4803 r_input_34 := 'Contact Person Phone '||p_input_34 || '|' ||
4804 'Operations Closing Date '|| ' |' ||
4805 'Serial Number '||p_input_38|| ' |' ||
4806 'Cost of Pension or annuity '||p_input_35|| '|' ||
4807 'Contrib to Qual Plans '||p_input_36|| '|' ||
4808 'Cost Reimbursement '||p_input_37|| '|' ||
4809 'Amendment Indicator '|| ' |' ||
4810 'Access Code '||p_input_29|| ' |' ||
4811 'Uncollected SS Tax on Tips '||p_input_31|| '|' ||
4812 'Uncollected Med Tax on Tips '||p_input_32 || '|' ||
4813 'Specialist Register Number '||lpad(' ',5)|| '|' || /* Bug 4665713 */
4814 'Salaries Und Act 324 of 2004 '|| '0' ;
4815
4816 r_input_35 := lpad(' ',46); /* Bug 4665713 */
4817 END IF;
4818 /* End of Puertorico Supplemental Data */
4819
4820 /* Bug # 3186636 - Formatting for WV */
4821 ELSIF p_report_qualifier = 'WV' THEN
4822 r_input_23 := lpad(0,8,0);
4823 r_input_24 := lpad(' ',8);
4824 r_input_30 := ' ';
4825 r_input_31 := lpad(0,11,0);
4826 r_input_33 := lpad(0,7,0);
4827 r_input_34 := lpad(' ',75);
4828 r_input_35 := lpad(' ',75);
4829
4830 /* Bug 4022086 - Formatting for MS */
4831 ELSIF p_report_qualifier = 'MS' THEN
4832 r_input_33 := lpad(' ',7);
4833 IF p_input_40 = 'FLAT' THEN
4834 r_input_34 := rpad(rpad(lpad(0,11,0),55,' ') || r_input_35,75,' ');
4835 r_input_35 := lpad(' ',75);
4836 ELSE
4837 r_input_34 := p_input_34 || ',' || p_input_35;
4838 r_input_35 := ' ';
4839 END IF;
4840 ELSIF p_report_qualifier = 'KS' THEN
4841 r_input_2 := '20';
4842 r_input_26 := '20';
4843 r_input_33 := lpad(' ',7);
4844 l_ee_contrib_pub_retire_system :=
4845 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
4846 p_report_type,
4847 p_format,
4848 p_report_qualifier,
4849 p_record_name,
4850 'NEG_CHECK',
4851 p_input_34,
4852 'EE contrib to retirement systems',
4853 p_input_39,
4854 null,
4855 p_validate,
4856 p_exclude_from_output,
4857 sp_out_1,
4858 sp_out_2);
4859 IF p_exclude_from_output = 'Y' THEN
4860 l_exclude_from_output_chk := TRUE;
4861 END IF;
4862 IF p_input_40 = 'FLAT' THEN
4863 r_input_34 := rpad(lpad(l_ee_contrib_pub_retire_system,11,0),75,' ');
4864 r_input_35 := lpad(' ',75);
4865 ELSE
4866 r_input_34 := p_input_34;
4867 r_input_35 := ' ';
4868 END IF;
4869 /* Bug 5696443 - New fields for AR */
4870 ELSIF p_report_qualifier = 'AR' THEN
4871 r_input_33 := rpad(' ',7);
4872 r_input_34 := rpad(lpad(replace(replace(nvl(replace(p_input_34,' '),' '),'-'),'/'),9,'0'),75,' ');
4873 r_input_35 := rpad(replace(nvl(p_input_35,' '),'-'),75,' ');
4874 ELSE
4875 -- Supplemental data for other states filled with blank
4876 --
4877 r_input_33 := lpad(' ',7);
4878 r_input_34 := lpad(' ',75);
4879 r_input_35 := lpad(' ',75);
4880 END IF; /* W2 States. */
4881
4882
4883 /* OH RITA 298-304 blanks. 305-307 City code.
4884 Pos:308 C=Emp.City R=Res.City */
4885 IF (p_report_qualifier = 'OH_RTCCA')
4886 THEN
4887 /*r_input_29 := lpad(rpad(nvl(replace(p_input_29,' '),'000'),3),10); */
4888 r_input_3 := 'RO' || rpad(nvl(replace(p_input_29,' '),'000'),3); /* 6716247 */
4889 r_input_29 := rpad(' ',10);
4890 r_input_30 := rpad(nvl(p_input_30,' '),1);
4891 /* Bug 4045592 - Commented the following code as blank is reqd from 338-412
4892 r_input_34 := rpad(rpad(nvl(replace(upper(p_input_34),' '),' '),22),75); */
4893
4894 END IF;
4895
4896 IF (p_report_qualifier = 'OH_CCAAA')
4897 THEN
4898 r_input_29 := lpad(rpad(nvl(replace(p_input_29,' '),'000'),3),10);
4899 r_input_30 := rpad(nvl(p_input_30,' '),1);
4900 /* Bug 4045592 - Commented the following code as blank is reqd from 338-412
4901 r_input_34 := rpad(rpad(nvl(replace(upper(p_input_34),' '),' '),22),75); */
4902 END IF;
4903
4904
4905 IF ((p_report_qualifier = 'MO_STLOU') OR
4906 (p_report_qualifier = 'PA_PHILA') OR
4907 (p_report_qualifier = 'OH_DAYTO')) THEN
4908 r_input_30 := rpad(nvl(p_input_30,' '),1);
4909 END IF;
4910
4911 IF (p_report_qualifier = 'MO_KNSAS') THEN
4912 r_input_30 := rpad(' ',1);
4913 END IF;
4914
4915 IF p_report_qualifier = 'PA_PHILA' THEN
4916 hr_utility.trace('Company Locality Id '||p_input_33);
4917 r_input_33 := lpad(substr(NVL(p_input_33,' '),1,7),7);
4918 END IF;
4919
4920 IF p_report_qualifier = 'OH_DAYTO' THEN
4921 r_input_34 := rpad(lpad(nvl(r_input_31,'0'),12,'0'),75);
4922 r_input_35 := rpad(lpad(nvl(r_input_32,'0'),12,'0'),75);
4923 END IF;
4924 /* City Name should be displayed for OH RITA for Taxes Withheld */
4925 /* Bug 5886247 */
4926 IF (p_report_qualifier = 'OH_RTCCA') THEN /* 6716247 */
4927 r_input_34 := lpad(' ',75);
4928 /* IF to_number(nvl(r_input_32,'0')) > 0 THEN
4929 r_input_34 := rpad(rpad(nvl(p_input_34,' '),22,' '),75, ' ') ;
4930 END IF ; */
4931 END IF ;
4932
4933 /* Fix for Bug # 2680070 */
4934 hr_utility.trace('Value of Stock Option Amount '||p_input_35);
4935 IF (p_report_qualifier = 'MO_STLOU') THEN
4936 IF (to_number(NVL(p_input_35,'0')) > 0) THEN
4937 r_input_34 := 'STK'||lpad(NVL(p_input_35,'0'),11,'0')||lpad(' ',61);
4938 r_input_35 := lpad(' ',75);
4939 ELSE
4940 r_input_34 := lpad(' ',3)||rpad('0',11,'0')||lpad(' ',61);
4941 r_input_35 := lpad(' ',75);
4942 END IF;
4943 END IF;
4944 hr_utility.trace('Value of r_input_34 '||r_input_34);
4945
4946 l_last_field := lpad(' ',25);
4947
4948 IF p_input_40 = 'FLAT' THEN
4949 --{ Start of formatting FLAT type RS Record
4950 --
4951 IF p_report_qualifier = 'IN' THEN
4952 l_fl_field_17_20 := r_input_17 || lpad(' ',10);
4953
4954 /* Bug # 5513076 */
4955 swap_street_location_indiana := r_input_9;
4956 r_input_9 := r_input_10; /*pos 73-94 Street Address pos 95-116 Location Address */
4957 r_input_10 := swap_street_location_indiana;
4958
4959 -- Bug 5513076,5637673
4960 IN_state_adv_EIC := substr(r_input_34,1,11);
4961 r_input_34 := /*'000' Commenting for Bug# 5739737*/
4962 lpad(nvl(substr(p_input_33, -3),'0'),3,'0')||
4963 IN_state_adv_EIC||'INADV'||substr(r_input_34,20,53);
4964
4965
4966
4967
4968 ELSE
4969 l_fl_field_17_20 := r_input_17 || r_input_18 || r_input_19 || r_input_20 ;
4970 END IF;
4971
4972 IF p_report_qualifier = 'MD' THEN /* 6648007 */
4973 /* r_input_32 := lpad(' ', 8) || rpad(substr(nvl(p_input_38,' '),1,9),9) ; */
4974 r_input_32 := lpad(' ', 8) || rpad(substr(replace(replace(nvl(p_input_38,' '),'-'),'/'),1,9),9);
4975 r_input_33 := ' ' ;
4976 END IF;
4977
4978 return_value:= 'RS'||r_input_2
4979 ||r_input_3
4980 ||l_ssn
4981 ||l_first_name
4982 ||l_middle_name
4983 ||l_last_name
4984 ||l_suffix
4985 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
4986 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
4987 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
4988 ||rpad(substr(nvl(r_input_12,' '),1,2),2)
4989 ||rpad(substr(nvl(r_input_13,' '),1,5),5)
4990 ||rpad(substr(nvl(r_input_14,' '),1,4),9)
4991 ||r_input_15
4992 ||r_input_16
4993 ||l_fl_field_17_20
4994 ||r_input_21
4995 ||r_input_22
4996 ||r_input_23
4997 ||r_input_24
4998 ||lpad(' ',5)
4999 ||r_input_25
5000 ||lpad(' ',6)
5001 ||r_input_26
5002 ||r_input_27
5003 ||r_input_28
5004 ||r_input_29
5005 ||r_input_30
5006 ||r_input_31
5007 ||r_input_32
5008 ||r_input_33
5009 ||r_input_34
5010 ||r_input_35
5011 ||l_last_field
5012 ||l_end_of_rec;
5013
5014 hr_utility.trace('Length of return value = '||to_char(length(return_value)));
5015 --} End of formatting FLAT Type RS Record
5016 ELSIF p_input_40 = 'CSV' THEN
5017 --{ Start of formatting RS record in CSV format
5018 /* Bug 3180532
5019 IN does not require fields Country Code, Optional code and Reporting Period */
5020 IF p_report_qualifier = 'IN' THEN
5021 l_audit_field_17 := p_input_17;
5022 ELSE
5023 l_audit_field_17 := r_input_17 ||','|| r_input_18 ||','|| r_input_19 ;
5024 END IF;
5025
5026 return_value := 'RS'
5027 ||','||r_input_2
5028 ||','||r_input_3
5029 ||','||l_ssn
5030 ||','||l_first_name
5031 ||','||l_middle_name
5032 ||','||l_last_name
5033 ||','||l_suffix
5034 ||','||rpad(substr(nvl(r_input_9,' '),1,22),22)
5035 ||','||rpad(substr(nvl(r_input_10,' '),1,22),22)
5036 ||','||rpad(substr(nvl(r_input_11,' '),1,22),22)
5037 ||','||rpad(substr(nvl(r_input_12,' '),1,2),2)
5038 ||','||rpad(substr(nvl(r_input_13,' '),1,5),5)
5039 ||','||rpad(substr(nvl(r_input_14,' '),1,4),4)
5040 ||','||lpad(' ',5)
5041 ||','||r_input_15
5042 ||','||r_input_16
5043 ||','||l_audit_field_17
5044 ||','||r_input_20
5045 ||','||r_input_21
5046 ||','||r_input_22
5047 ||','||r_input_23
5048 ||','||r_input_24
5049 ||','||lpad(' ',6)
5050 ||','||r_input_25
5051 ||','||lpad(' ',6)
5052 ||','||r_input_26
5053 ||','||r_input_27
5054 ||','||r_input_28
5055 ||','||r_input_29
5056 ||','||r_input_30
5057 ||','||r_input_31
5058 ||','||r_input_32
5059 ||','||r_input_33
5060 ||','||r_input_34
5061 ||','||r_input_35
5062 ||','||lpad(' ',5);
5063 --} End of formatting RS record in CSV format
5064 --
5065 ELSIF p_input_40 = 'BLANK' THEN
5066 --{ Start of formatting BALNK RS record used for audit report
5067 --
5068 /* Bug 3180532 - IN does not require fields Country Code,
5069 Optional code and Reporting Period */
5070 IF p_report_qualifier = 'IN' THEN
5071 l_audit_field_17 := ' ';
5072 ELSE
5073 l_audit_field_17 := ' '||','||' '||','||' ';
5074 END IF;
5075
5076 return_value := ''
5077 ||','||' '
5078 ||','||' '
5079 ||','||' '
5080 ||','||' '
5081 ||','||' '
5082 ||','||' '
5083 ||','||' '
5084 ||','||' '
5085 ||','||' '
5086 ||','||' '
5087 ||','||' '
5088 ||','||' '
5089 ||','||' '
5090 ||','||lpad(' ',5)
5091 ||','||' '
5092 ||','||' '
5093 ||','|| l_audit_field_17
5094 ||','||' '
5095 ||','||' '
5096 ||','||' '
5097 ||','||' '
5098 ||','||' '
5099 ||','||lpad(' ',6)
5100 ||','||' '
5101 ||','||lpad(' ',6)
5102 ||','||' '
5103 ||','||' '
5104 ||','||' '
5105 ||','||' '
5106 ||','||' '
5107 ||','||' '
5108 ||','||' '
5109 ||','||' '
5110 ||','||' '
5111 ||','||' '
5112 ||','||lpad(' ',5);
5113 --} End of formatting BLANK RS record used for audit report
5114 --
5115 END IF; -- p_input_40
5116 p_error := l_exclude_from_output_chk;
5117 ret_str_len:=length(return_value);
5118 return return_value;
5119 END format_W2_RS_record;
5120 -- End of Formatting RS Record for W2 Reporting
5121
5122 -- Formatting RT record for W2 reporting
5123 --
5124 /*
5125 Record Identifier --> p_input_1
5126 Number of RW Records --> p_input_2
5127 Wages, Tips and other Compensation --> p_input_3
5128 Federal Income Tax Withheld --> p_input_4
5129 Social Security Wages --> p_input_5
5130 Social Security Tax Withheld --> p_input_6
5131 Medicare Wages And Tips --> p_input_7
5132 Medicare Tax Withheld --> p_input_8
5133 Social Security Tips --> p_input_9
5134 Advance Earned Income Credit --> p_input_10
5135 Dependent Care Benefits --> p_input_11
5136 Deferred Compensation Contributions to Section 401(k) --> p_input_12
5137 Deferred Compensation Contributions to Section 403(b) --> p_input_13
5138 Deferred Compensation Contributions to Section 408(k)(6) --> p_input_14
5139 Deferred Compensation Contributions to Section 457(b) --> p_input_15
5140 Deferred Compensation Contributions to Section 501(c)(18)(D) --> p_input_16
5141 Military EE''s Basic Quarters, Subsistence And Combat Pay --> p_input_17
5142 Non-Qual. Plan Sec.457 Distributions or Contributions --> p_input_18
5143 Non-Qual. Plan NOT Section 457 Distributions or Contributions --> p_input_19
5144 Employer Cost of Premiums for GTL> $50k --> p_input_20
5145 Income Tax Withheld by Third-Party Payer --> p_input_21
5146 Income from the Exercise of Nonqualified Stock Options --> p_input_22
5147
5148 For Massachussets following input parameters were used
5149 W2 Govt EE Contributions --> p_input_23
5150 Total Fed Wages --> p_input_24
5151
5152 For PuertoRico following input parameters were used
5153 Cost of Pension or Annuity --> p_input_23
5154 Contributions to Qualified Plans --> p_input_24
5155 Cost Reimbursement --> p_input_25
5156 Employer Contributions to a Health Savings Account --> p_input_26
5157 Non-Taxable Combat Pay --> p_input_27
5158 Deferrals Under a Section 409A Non-Qualified Deferred Comp Plan--> p_input_28
5159 Designated Roth Contributions to a section 401(k) Plan --> p_input_31
5160 Designated Roth Contributions Under a section 403(b) Plan --> p_input_32
5161 */
5162 FUNCTION format_W2_RT_record(
5163 p_effective_date IN varchar2,
5164 p_report_type IN varchar2,
5165 p_format IN varchar2,
5166 p_report_qualifier IN varchar2,
5167 p_record_name IN varchar2,
5168 p_input_1 IN varchar2,
5169 p_input_2 IN varchar2,
5170 p_input_3 IN varchar2,
5171 p_input_4 IN varchar2,
5172 p_input_5 IN varchar2,
5173 p_input_6 IN varchar2,
5174 p_input_7 IN varchar2,
5175 p_input_8 IN varchar2,
5176 p_input_9 IN varchar2,
5177 p_input_10 IN varchar2,
5178 p_input_11 IN varchar2,
5179 p_input_12 IN varchar2,
5180 p_input_13 IN varchar2,
5181 p_input_14 IN varchar2,
5182 p_input_15 IN varchar2,
5183 p_input_16 IN varchar2,
5184 p_input_17 IN varchar2,
5185 p_input_18 IN varchar2,
5186 p_input_19 IN varchar2,
5187 p_input_20 IN varchar2,
5188 p_input_21 IN varchar2,
5189 p_input_22 IN varchar2,
5190 p_input_23 IN varchar2,
5191 p_input_24 IN varchar2,
5192 p_input_25 IN varchar2,
5193 p_input_26 IN varchar2,
5194 p_input_27 IN varchar2,
5195 p_input_28 IN varchar2,
5196 p_input_29 IN varchar2,
5197 p_input_30 IN varchar2,
5198 p_input_31 IN varchar2,
5199 p_input_32 IN varchar2,
5200 p_input_33 IN varchar2,
5201 p_input_34 IN varchar2,
5202 p_input_35 IN varchar2,
5203 p_input_36 IN varchar2,
5204 p_input_37 IN varchar2,
5205 p_input_38 IN varchar2,
5206 p_input_39 IN varchar2,
5207 p_input_40 IN varchar2,
5208 p_validate IN varchar2,
5209 p_exclude_from_output OUT nocopy varchar2,
5210 sp_out_1 OUT nocopy varchar2,
5211 sp_out_2 OUT nocopy varchar2,
5212 sp_out_3 OUT nocopy varchar2,
5213 sp_out_4 OUT nocopy varchar2,
5214 sp_out_5 OUT nocopy varchar2,
5215 ret_str_len OUT nocopy number,
5216 p_error OUT nocopy boolean
5217 ) RETURN VARCHAR2
5218 IS
5219 l_input_2 varchar2(100);
5220 return_value varchar2(32767);
5221 l_exclude_from_output_chk boolean;
5222 l_rt_end_of_rec varchar2(200);
5223 l_records number(10);
5224 l_end_of_rec varchar2(20);
5225 p_end_of_rec varchar2(20) :=
5226 fnd_global.local_chr(13)||fnd_global.local_chr(10);
5227
5228 TYPE function_columns IS RECORD(
5229 p_parameter_name varchar2(100),
5230 p_parameter_value varchar2(100),
5231 p_output_value varchar2(100)
5232 );
5233 function_parameter_rec function_columns;
5234 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
5235 INDEX BY BINARY_INTEGER;
5236 parameter_record input_parameter_record;
5237
5238 r_input_1 varchar2(300);
5239 r_input_2 varchar2(300);
5240 r_input_3 varchar2(300);
5241 r_input_4 varchar2(300);
5242 r_input_5 varchar2(300);
5243 r_input_6 varchar2(300);
5244 r_input_7 varchar2(300);
5245 r_input_8 varchar2(300);
5246 r_input_9 varchar2(300);
5247 r_input_10 varchar2(300);
5248 r_input_11 varchar2(300);
5249 r_input_12 varchar2(300);
5250 r_input_13 varchar2(300);
5251 r_input_14 varchar2(300);
5252 r_input_15 varchar2(300);
5253 r_input_16 varchar2(300);
5254 r_input_17 varchar2(300);
5255 r_input_18 varchar2(300);
5256 r_input_19 varchar2(300);
5257 r_input_20 varchar2(300);
5258 r_input_21 varchar2(300);
5259 r_input_22 varchar2(300);
5260 r_input_23 varchar2(300);
5261 r_input_24 varchar2(300);
5262 r_input_25 varchar2(300);
5263 r_input_26 varchar2(300);
5264 r_input_27 varchar2(300);
5265 r_input_28 varchar2(300);
5266 r_input_29 varchar2(300);
5267 r_input_30 varchar2(300);
5268 r_input_31 varchar2(300);
5269 r_input_32 varchar2(300);
5270 r_input_33 varchar2(300);
5271 r_input_34 varchar2(300);
5272 r_input_35 varchar2(300);
5273 r_input_36 varchar2(300);
5274 r_input_37 varchar2(300);
5275 r_input_38 varchar2(300);
5276 r_input_39 varchar2(300);
5277
5278 BEGIN
5279 -- hr_utility.trace_on(null,'RI_W2') ;
5280 hr_utility.trace('Formatting RT record for W2 reporting');
5281 hr_utility.trace('p_report_qualifier = '||p_report_qualifier);
5282 -- Initializing local variables with parameter value
5283 --{
5284 r_input_2 := p_input_2;
5285 r_input_3 := p_input_3;
5286 r_input_4 := p_input_4;
5287 r_input_5 := p_input_5;
5288 r_input_6 := p_input_6;
5289 r_input_7 := p_input_7;
5290 r_input_8 := p_input_8;
5291 r_input_9 := p_input_9;
5292 r_input_10 := p_input_10;
5293 r_input_11 := p_input_11;
5294 r_input_12 := p_input_12;
5295 r_input_13 := p_input_13;
5296 r_input_14 := p_input_14;
5297 r_input_15 := p_input_15;
5298 r_input_16 := p_input_16;
5299 r_input_17 := p_input_17;
5300 r_input_18 := p_input_18;
5301 r_input_19 := p_input_19;
5302 r_input_20 := p_input_20;
5303 r_input_21 := p_input_21;
5304 r_input_22 := p_input_22;
5305 r_input_23 := p_input_23;
5306 r_input_24 := p_input_24;
5307 r_input_25 := p_input_25;
5308 r_input_26 := p_input_26;
5309 r_input_27 := p_input_27;
5310 r_input_28 := p_input_28;
5311 r_input_29 := p_input_29;
5312 r_input_30 := p_input_30;
5313 r_input_31 := p_input_31;
5314 r_input_32 := p_input_32;
5315 r_input_33 := p_input_33;
5316 r_input_34 := p_input_34;
5317 r_input_35 := p_input_35;
5318 r_input_36 := p_input_36;
5319 r_input_37 := p_input_37;
5320 r_input_38 := p_input_38;
5321 r_input_39 := p_input_39;
5322 --}
5323 -- Validation Starts
5324 If p_input_40='FLAT' THEN
5325 --{
5326 IF p_report_qualifier = 'MA' THEN
5327 --{
5328 parameter_record(1).p_parameter_name:= 'State taxable Wages';
5329 parameter_record(1).p_parameter_value:=p_input_3;
5330
5331 parameter_record(2).p_parameter_name:= 'SIT withheld';
5332 parameter_record(2).p_parameter_value:=p_input_4;
5333
5334 parameter_record(3).p_parameter_name:= 'FICA and Medicare withheld';
5335 parameter_record(3).p_parameter_value:=p_input_5;
5336
5337 parameter_record(4).p_parameter_name:= 'W2 Govt EE Contributions';
5338 parameter_record(4).p_parameter_value:=p_input_23;
5339
5340 parameter_record(5).p_parameter_name:= 'Total Fed Wages';
5341 parameter_record(5).p_parameter_value:=p_input_24;
5342 l_records :=5;
5343 --}
5344 ELSIF p_report_qualifier = 'CT' THEN
5345 --{
5346 parameter_record(1).p_parameter_name:= 'State taxable Wages';
5347 parameter_record(1).p_parameter_value:=p_input_3;
5348
5349 parameter_record(2).p_parameter_name:= 'SIT withheld';
5350 parameter_record(2).p_parameter_value:=p_input_4;
5351
5352 l_records :=2;
5353 --}
5354 ELSIF p_report_qualifier = 'PA' THEN
5355 --{
5356 parameter_record(1).p_parameter_name:= 'State taxable Wages';
5357 parameter_record(1).p_parameter_value:=p_input_3;
5358
5359 parameter_record(2).p_parameter_name:= 'SIT withheld';
5360 parameter_record(2).p_parameter_value:=p_input_4;
5361
5362 /* Bug 3680056 New field */
5363 parameter_record(3).p_parameter_name:= 'Employer Contributions to Health Savings Account';
5364 parameter_record(3).p_parameter_value:=p_input_26;
5365
5366 parameter_record(4).p_parameter_name:= 'Non-Taxable Combat Pay';
5367 parameter_record(4).p_parameter_value := p_input_27 ;
5368
5369 l_records := 4 ;
5370 -- l_records :=3;
5371 --}
5372 ELSIF p_report_qualifier = 'RI' THEN
5373 --{
5374 parameter_record(1).p_parameter_name:= 'Wages,Tips And Other Compensation';
5375 parameter_record(1).p_parameter_value:=p_input_3;
5376
5377 parameter_record(2).p_parameter_name:= 'Federal Income Tax Withheld';
5378 parameter_record(2).p_parameter_value:=p_input_4;
5379
5380 parameter_record(3).p_parameter_name:= 'State Taxable Wages';
5381 parameter_record(3).p_parameter_value:=p_input_5;
5382
5383 parameter_record(4).p_parameter_name:= 'Medicare Wages And Tips';
5384 parameter_record(4).p_parameter_value:=p_input_6;
5385
5386 parameter_record(5).p_parameter_name:= 'Medicare Tax Withheld';
5387 parameter_record(5).p_parameter_value:=p_input_7;
5388
5389 parameter_record(6).p_parameter_name:= 'SIT withheld';
5390 parameter_record(6).p_parameter_value:=p_input_8;
5391
5392 /* Bug 3680056 New field */
5393 parameter_record(7).p_parameter_name:= 'Employer Contributions to Health Savings Account';
5394 parameter_record(7).p_parameter_value:=p_input_26;
5395
5396 parameter_record(8).p_parameter_name:= 'Non-Taxable Combat Pay';
5397 parameter_record(8).p_parameter_value := p_input_27 ;
5398
5399 parameter_record(9).p_parameter_name:= 'Deferrals Under a Sec 409A Non-Qual Def Comp Plan';
5400 parameter_record(9).p_parameter_value := p_input_28 ;
5401
5402 l_records := 9 ;
5403
5404 -- l_records :=7;
5405 --}
5406 ELSIF p_report_qualifier = 'FL_SQWL' THEN
5407 --{
5408 parameter_record(1).p_parameter_name:= 'Wages,Tips And Other Compensation';
5409 parameter_record(1).p_parameter_value:=p_input_3;
5410
5411 parameter_record(2).p_parameter_name:= 'Total Tax Due';
5412 parameter_record(2).p_parameter_value:=p_input_22;
5413
5414 parameter_record(3).p_parameter_name:= 'Corrected Worker Ist Month';
5415 parameter_record(3).p_parameter_value:=p_input_23;
5416
5417 parameter_record(4).p_parameter_name:= 'Corrected Worker IInd Month';
5418 parameter_record(4).p_parameter_value:=p_input_24;
5419
5420 parameter_record(5).p_parameter_name:= 'Corrected Worker IIIrd Month';
5421 parameter_record(5).p_parameter_value:=p_input_25;
5422
5423 l_records := 5 ;
5424 l_end_of_rec := p_end_of_rec;
5425 --}
5426 ELSIF p_report_qualifier = 'MO_KNSAS' THEN
5427 -- Fix for Bug# 4502738
5428 --{
5429 parameter_record(1).p_parameter_name:= 'Wages,Tips And Other Compensation';
5430 parameter_record(1).p_parameter_value:=p_input_3;
5431
5432 parameter_record(2).p_parameter_name:= 'Medicare Wages And Tips';
5433 parameter_record(2).p_parameter_value:=p_input_7;
5434
5435 parameter_record(3).p_parameter_name:= 'Medicare Tax Withheld';
5436 parameter_record(3).p_parameter_value:=p_input_8;
5437
5438 l_records := 3 ;
5439 l_end_of_rec := p_end_of_rec;
5440 --}
5441 ELSE
5442 --{
5443 parameter_record(1).p_parameter_name:= ' Wages,Tips And Other Compensation';
5444 parameter_record(1).p_parameter_value:=p_input_3;
5445
5446 parameter_record(2).p_parameter_name:= ' Federal Income Tax Withheld';
5447 parameter_record(2).p_parameter_value:=p_input_4;
5448
5449 parameter_record(3).p_parameter_name:= 'Social Security Wages';
5450 parameter_record(3).p_parameter_value:=p_input_5;
5451
5452 parameter_record(4).p_parameter_name:= ' Social Security Tax Withheld';
5453 parameter_record(4).p_parameter_value:=p_input_6;
5454
5455 parameter_record(5).p_parameter_name:= 'Medicare Wages And Tips';
5456 parameter_record(5).p_parameter_value:=p_input_7;
5457
5458 parameter_record(6).p_parameter_name:= 'Medicare Tax Withheld';
5459 parameter_record(6).p_parameter_value:=p_input_8;
5460
5461 parameter_record(7).p_parameter_name:= 'Social Security Tips';
5462 parameter_record(7).p_parameter_value:=p_input_9;
5463
5464 parameter_record(8).p_parameter_name:= 'Advance Earned Income Credit';
5465 parameter_record(8).p_parameter_value:=p_input_10;
5466
5467 parameter_record(9).p_parameter_name:= 'Dependent Care Benefits';
5468 parameter_record(9).p_parameter_value:=p_input_11;
5469
5470 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
5471 parameter_record(10).p_parameter_value:=p_input_12;
5472
5473 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
5474 parameter_record(11).p_parameter_value:=p_input_13;
5475
5476 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
5477 parameter_record(12).p_parameter_value:=p_input_14;
5478
5479 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
5480 parameter_record(13).p_parameter_value:=p_input_15;
5481
5482 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
5483 parameter_record(14).p_parameter_value:=p_input_16;
5484
5485 /* Following field is commented to fix bug # 2297587
5486 parameter_record(15).p_parameter_name:= 'Military Combat Pay';
5487 parameter_record(15).p_parameter_value:=p_input_17;
5488 */
5489 parameter_record(15).p_parameter_name:= 'Non-Qual. plan Sec 457';
5490 parameter_record(15).p_parameter_value:=p_input_18;
5491
5492 parameter_record(16).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
5493 parameter_record(16).p_parameter_value:=p_input_19;
5494
5495 parameter_record(17).p_parameter_name:= 'Employer cost of premiun';
5496 parameter_record(17).p_parameter_value:=p_input_20;
5497
5498 parameter_record(18).p_parameter_name:= 'Income tax withheld by 3rd party payer';
5499 parameter_record(18).p_parameter_value:=p_input_21;
5500
5501 parameter_record(19).p_parameter_name:= 'Income from nonqualified stock option';
5502 parameter_record(19).p_parameter_value:=p_input_22;
5503
5504 /* Bug 3680056 New field */
5505 parameter_record(20).p_parameter_name:= 'Employer Contributions to Health Savings Account';
5506 parameter_record(20).p_parameter_value:=p_input_26;
5507
5508 parameter_record(21).p_parameter_name:= 'Non-Taxable Combat Pay';
5509 parameter_record(21).p_parameter_value := p_input_27 ;
5510
5511 parameter_record(22).p_parameter_name:= 'Deferrals Under a Sec 409A Non-Qual Def Comp Plan';
5512 parameter_record(22).p_parameter_value := p_input_28 ;
5513 /* Bug 5256745 */
5514
5515 parameter_record(23).p_parameter_name:= 'Roth Contributions Und Sec 401(k) Plan';
5516 parameter_record(23).p_parameter_value := p_input_31 ;
5517
5518 parameter_record(24).p_parameter_name:= 'Roth Contributions Und Sec 403(b) Plan';
5519 parameter_record(24).p_parameter_value := p_input_32 ;
5520
5521 l_records := 24 ;
5522
5523 END IF;
5524
5525 /* These Values validated specifically for PuertoRico */
5526 /* Bug 3680056 - Since New field is added as parameter 20, the foll. 3 parameters for PR
5527 have been shifted by one each. i.e from 20-22 to 21-23 */
5528 IF p_report_qualifier = 'PR' THEN
5529
5530 parameter_record(21).p_parameter_name:= 'Cost of Pension Annuity';
5531 parameter_record(21).p_parameter_value:=p_input_23;
5532
5533 parameter_record(22).p_parameter_name:= 'Contribution to Qual. Plans';
5534 parameter_record(22).p_parameter_value:=p_input_24;
5535
5536 parameter_record(23).p_parameter_name:= 'Cost Reimbursement';
5537 parameter_record(23).p_parameter_value:=p_input_25;
5538
5539 parameter_record(24).p_parameter_name:= 'Non-Taxable Combat Pay';
5540 parameter_record(24).p_parameter_value := p_input_27 ;
5541
5542 parameter_record(25).p_parameter_name:= 'Deferrals Under a Sec 409A Non-Qual Def Comp Plan';
5543 parameter_record(25).p_parameter_value := p_input_28 ;
5544
5545 parameter_record(26).p_parameter_name:= 'Uncollected SS Tax on Tips';
5546 parameter_record(26).p_parameter_value := p_input_29 ;
5547
5548 parameter_record(27).p_parameter_name:= 'Uncollected Medicare Tax on Tips';
5549 parameter_record(27).p_parameter_value := p_input_30 ;
5550
5551 l_records := 27 ;
5552
5553 END IF;
5554
5555 -- Validating above data based on the report_qualifier and number entries
5556 --
5557 FOR i in 1..l_records
5558 LOOP
5559 parameter_record(i).p_output_value :=
5560 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
5561 p_report_type,
5562 p_format,
5563 p_report_qualifier,
5564 p_record_name,
5565 'NEG_CHECK',
5566 parameter_record(i).p_parameter_value,
5567 parameter_record(i).p_parameter_name,
5568 p_input_39,
5569 null,
5570 p_validate,
5571 p_exclude_from_output,
5572 sp_out_1,
5573 sp_out_2);
5574 IF p_exclude_from_output = 'Y' THEN
5575 l_exclude_from_output_chk := TRUE;
5576 END IF;
5577 hr_utility.trace(parameter_record(i).p_parameter_name||' = '
5578 ||parameter_record(i).p_output_value);
5579 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
5580 END LOOP;
5581
5582 -- Validation Ends here
5583 --
5584 -- Formatting RT Record depending on report_qualifier
5585 --
5586 -- Formatting No of Employee Wage/State Record
5587 l_input_2:= lpad(substr(nvl(p_input_2,'0'),1,7),7,0);
5588
5589 IF p_report_qualifier = 'MA' THEN
5590 --{
5591 return_value := 'RT'||l_input_2
5592 ||parameter_record(1).p_output_value
5593 ||parameter_record(2).p_output_value
5594 ||lpad(' ',45)
5595 /* ||parameter_record(3).p_output_value
5596 ||parameter_record(4).p_output_value -- Railroad, MA and Local Govt Reqtirement Contrib
5597 ||parameter_record(5).p_output_value -- Fed Wages (Box 1) */
5598 ||lpad(' ',45) /* 6720319 */
5599 ||lpad(' ',383); -- 383 Spaces
5600 --}
5601 ELSIF p_report_qualifier = 'CT' THEN
5602 --{
5603 --
5604 -- space between wage and withhed removed to fix bug # 2640052
5605 --
5606 return_value := 'RT'||l_input_2
5607 ||parameter_record(1).p_output_value
5608 -- ||lpad(' ',1)
5609 ||parameter_record(2).p_output_value
5610 ||lpad(' ',473);
5611
5612 --}
5613 ELSIF p_report_qualifier = 'PA' THEN
5614 --{
5615 return_value := 'RT'||lpad(' ',247) -- changed from 249
5616 ||parameter_record(3).p_output_value -- Bug 3680056 - New Field 250-264
5617 --||lpad(' ',209)
5618 ||lpad(' ',15)
5619 ||rpad(parameter_record(4).p_output_value,196)
5620 ||l_input_2
5621 ||parameter_record(1).p_output_value
5622 ||parameter_record(2).p_output_value;
5623 --}
5624 ELSIF p_report_qualifier = 'RI' THEN
5625 --{
5626 hr_utility.trace('Within RI') ;
5627 return_value := 'RT'||l_input_2
5628 ||parameter_record(1).p_output_value
5629 ||parameter_record(2).p_output_value
5630 ||parameter_record(3).p_output_value
5631 ||lpad(' ',15)
5632 ||parameter_record(4).p_output_value
5633 ||parameter_record(5).p_output_value
5634 ||parameter_record(6).p_output_value
5635 ||lpad(' ',135) /* Bug 3680056 - New field from 250-264 */
5636 ||rpad(parameter_record(7).p_output_value,30)
5637 ||rpad(parameter_record(8).p_output_value,30)
5638 ||lpad('0',45,'0')
5639 ||rpad(parameter_record(9).p_output_value,158) ;
5640 hr_utility.trace('Exiting RI') ;
5641 --}
5642 ELSIF p_report_qualifier = 'SC_SQWL' THEN /*2274381.*/
5643 return_value := 'RT'||l_input_2
5644 ||lpad(' ',503);
5645 ELSIF p_report_qualifier = 'FL_SQWL' THEN
5646 --{
5647 return_value := 'RT'||lpad(' ',7)
5648 ||lpad(substr(nvl(parameter_record(1).p_output_value,'0'),1,15),15)
5649 ||lpad(' ',456)
5650 ||lpad(substr(nvl(parameter_record(2).p_output_value,'0'),1,11),11)
5651 ||lpad(substr(nvl(parameter_record(3).p_output_value,'0'),1,7),7)
5652 ||lpad(substr(nvl(parameter_record(4).p_output_value,'0'),1,7),7)
5653 ||lpad(substr(nvl(parameter_record(5).p_output_value,'0'),1,7),7)
5654 ||l_end_of_rec;
5655 --}
5656 ELSIF p_report_qualifier = 'PA_PHILA' THEN /* Bug # 2680189 */
5657 return_value := 'RT'||lpad(' ',510);
5658 ELSIF p_report_qualifier = 'PR' THEN
5659 --{
5660 return_value := 'RT'||l_input_2
5661 ||parameter_record(1).p_output_value
5662 ||parameter_record(2).p_output_value
5663 ||parameter_record(3).p_output_value
5664 ||parameter_record(4).p_output_value
5665 ||parameter_record(5).p_output_value
5666 ||parameter_record(6).p_output_value
5667 ||parameter_record(7).p_output_value
5668 ||parameter_record(8).p_output_value
5669 ||parameter_record(9).p_output_value
5670 /* Bug 5876054
5671 ||parameter_record(10).p_output_value
5672 ||parameter_record(11).p_output_value
5673 ||parameter_record(12).p_output_value
5674 ||parameter_record(13).p_output_value
5675 ||parameter_record(14).p_output_value
5676 ||lpad(' ',15)
5677 ||rpad(parameter_record(15).p_output_value,15)
5678 */
5679 ||lpad('0',15,'0')
5680 ||lpad('0',15,'0')
5681 ||lpad('0',15,'0')
5682 ||lpad('0',15,'0')
5683 ||lpad('0',15,'0')
5684 ||lpad('0',15,'0')
5685 ||lpad('0',15,'0')
5686 -- Bug 3680056 - New Field 250-264
5687 ||parameter_record(20).p_output_value
5688 ||parameter_record(16).p_output_value
5689 ||rpad(parameter_record(24).p_output_value,30)
5690 ||parameter_record(17).p_output_value
5691 ||parameter_record(18).p_output_value
5692 ||parameter_record(19).p_output_value
5693 -- 5876054 ||parameter_record(25).p_output_value /* Changed from here : Bug 4665713 */
5694 ||lpad('0',15,'0')
5695 ||lpad('0',30,'0')
5696 ||lpad(' ',113) /* 6644795 - The Values have been Moved to the RV record */
5697 /* ||lpad(' ',22)
5698 ||parameter_record(21).p_output_value
5699 ||parameter_record(22).p_output_value
5700 ||parameter_record(23).p_output_value
5701 ||lpad('0',15,'0')
5702 ||parameter_record(26).p_output_value
5703 ||parameter_record(27).p_output_value
5704 ||lpad(' ',1) */;
5705 --}
5706 ELSIF p_report_qualifier = 'MO_KNSAS' THEN
5707 --{
5708 -- Fix for Bug# 4502738
5709 return_value := 'RT'||l_input_2
5710 ||parameter_record(1).p_output_value
5711 ||rpad('0',45,'0' )
5712 ||parameter_record(2).p_output_value
5713 ||parameter_record(3).p_output_value
5714 ||rpad('0',120,'0' )
5715 ||rpad(' ',15,' ' )
5716 ||rpad('0',60,'0' )
5717 ||rpad(' ',15,' ' )
5718 ||rpad('0',60,'0' )
5719 ||rpad(' ',143,' ' );
5720 --}
5721 ELSE
5722 --{
5723 return_value := 'RT'||l_input_2
5724 ||parameter_record(1).p_output_value
5725 ||parameter_record(2).p_output_value
5726 ||parameter_record(3).p_output_value
5727 ||parameter_record(4).p_output_value
5728 ||parameter_record(5).p_output_value
5729 ||parameter_record(6).p_output_value
5730 ||parameter_record(7).p_output_value
5731 ||parameter_record(8).p_output_value
5732 ||parameter_record(9).p_output_value
5733 ||parameter_record(10).p_output_value
5734 ||parameter_record(11).p_output_value
5735 ||parameter_record(12).p_output_value
5736 ||parameter_record(13).p_output_value
5737 ||parameter_record(14).p_output_value
5738 -- commented to fix bug # 2297587 ||parameter_record(15).p_output_value
5739 ||lpad('0',15,'0') /* Bug 4859212 */
5740 ||rpad(parameter_record(15).p_output_value,15)
5741 ||rpad(parameter_record(20).p_output_value,15) -- Bug 3680056 Pos 250-264
5742 --||rpad(parameter_record(16).p_output_value,45)
5743 ||parameter_record(16).p_output_value
5744 ||rpad(parameter_record(21).p_output_value,30)
5745 ||parameter_record(17).p_output_value
5746 ||parameter_record(18).p_output_value
5747 --||rpad(parameter_record(19).p_output_value,173);
5748 ||parameter_record(19).p_output_value
5749 --||rpad(parameter_record(22).p_output_value,158)
5750 /* Bug 5256745 */
5751 ||parameter_record(22).p_output_value
5752 ||parameter_record(23).p_output_value
5753 ||rpad(parameter_record(24).p_output_value,128) ;
5754
5755 --}
5756 END IF;
5757 hr_utility.trace('B4 Calculating length') ;
5758 ret_str_len:=length(return_value);
5759 --}
5760 ELSIF p_input_40 = 'CSV' THEN
5761 --{
5762 /* for PuertoRico following condition added */
5763 IF p_report_qualifier = 'PR' THEN
5764 /* Bug 4665713 */
5765 l_rt_end_of_rec := ','||p_input_28
5766 ||lpad(' ',52)||'|'||
5767 'Cost of Pension Annuity '||p_input_23||'|'||
5768 'Contrib to Qual. Plans ' ||p_input_24||'|'||
5769 'Cost Reimbursement ' ||p_input_25||'|'||
5770 'Salaries under Act No. 324 of 2004 ' ||'0'||'|'||
5771 'Uncollected SS Tax on Tips '||p_input_29||'|'||
5772 'Uncollected Medicare Tax on Tips '||p_input_30 ;
5773
5774 ELSE
5775 l_rt_end_of_rec := ','||p_input_28
5776 ||','||p_input_31
5777 ||','||p_input_32
5778 ||','||lpad(' ',113) ;
5779 END IF;
5780
5781 return_value := 'RT'||','||l_input_2
5782 ||','||p_input_3
5783 ||','||p_input_4
5784 ||','||p_input_5
5785 ||','||p_input_6
5786 ||','||p_input_7
5787 ||','||p_input_8
5788 ||','||p_input_9
5789 ||','||p_input_10
5790 ||','||p_input_11
5791 ||','||p_input_12
5792 ||','||p_input_13
5793 ||','||p_input_14
5794 ||','||p_input_15
5795 ||','||p_input_16
5796 -- commented to fix bug # 2297587 ||','||p_input_17
5797 ||','||lpad('0',15,'0') /* Bug 4859212 */
5798 ||','||p_input_18
5799 ||','||p_input_26 -- Bug 3680056 ER Contrib to HSA
5800 ||','||p_input_19
5801 ||','||p_input_27
5802 ||','||lpad(' ',15)
5803 ||','||p_input_20
5804 ||','||p_input_21
5805 ||','||p_input_22
5806 ||l_rt_end_of_rec;
5807 --}
5808 END IF; -- p_input_40 (i.e. FLAT, CSV)
5809 p_error := l_exclude_from_output_chk;
5810 ret_str_len:=length(return_value);
5811 hr_utility.trace('Exiting RT') ;
5812 return return_value;
5813 END format_W2_RT_record; -- End of Formatting RT record
5814 --
5815 -- Formatting RW record for W2 reporting
5816 --
5817 /*
5818 Record Identifier --> p_input_1
5819 Number of RO Records --> p_input_2
5820 Allocated Tips --> p_input_3
5821 Uncollected Employee Tax on Tips --> p_input_4
5822 Medical Savings Account --> p_input_5
5823 Simple Retirement Account --> p_input_6
5824 Qualified Adoption Expenses --> p_input_7
5825 Uncollected Social Security Tax on GTL --> p_input_8
5826 Uncollected Medicare Tax On GTL --> p_input_9
5827 Wages Subject to Puerto Rico Tax --> p_input_10
5828 Commissions Subject to Puerto Rico Tax --> p_input_11
5829 Allowances Subject to Puerto Rico Tax --> p_input_12
5830 Tips Subject to Puerto Rico Tax --> p_input_13
5831 Total Wages, Commissions, Tips, And Allow Sub to PR Tax --> p_input_14
5832 Puerto Rico Tax Withheld --> p_input_15
5833 Retirement Fund Annual Contributions --> p_input_16
5834 Total Wages, Tips And Other Comp Sub to Virgin Islands,
5835 or Guam, or American Samoa, or Northern Mariana
5836 Islands Income Tax --> p_input_17
5837 Virgin Islands, or Guam, Or American Samoa, or Northern
5838 Mariana Islands Income Tax Withheld --> p_input_18
5839 Income Under Section 409A on Non-Qualified Def Comp Plan --> p_input_20
5840 Employee Number --> p_input_39
5841 Blank;
5842 */
5843 FUNCTION format_W2_RU_record(
5844 p_effective_date IN varchar2,
5845 p_report_type IN varchar2,
5846 p_format IN varchar2,
5847 p_report_qualifier IN varchar2,
5848 p_record_name IN varchar2,
5849 p_input_1 IN varchar2,
5850 p_input_2 IN varchar2,
5851 p_input_3 IN varchar2,
5852 p_input_4 IN varchar2,
5853 p_input_5 IN varchar2,
5854 p_input_6 IN varchar2,
5855 p_input_7 IN varchar2,
5856 p_input_8 IN varchar2,
5857 p_input_9 IN varchar2,
5858 p_input_10 IN varchar2,
5859 p_input_11 IN varchar2,
5860 p_input_12 IN varchar2,
5861 p_input_13 IN varchar2,
5862 p_input_14 IN varchar2,
5863 p_input_15 IN varchar2,
5864 p_input_16 IN varchar2,
5865 p_input_17 IN varchar2,
5866 p_input_18 IN varchar2,
5867 p_input_19 IN varchar2,
5868 p_input_20 IN varchar2,
5869 p_input_21 IN varchar2,
5870 p_input_22 IN varchar2,
5871 p_input_23 IN varchar2,
5872 p_input_24 IN varchar2,
5873 p_input_25 IN varchar2,
5874 p_input_26 IN varchar2,
5875 p_input_27 IN varchar2,
5876 p_input_28 IN varchar2,
5877 p_input_29 IN varchar2,
5878 p_input_30 IN varchar2,
5879 p_input_31 IN varchar2,
5880 p_input_32 IN varchar2,
5881 p_input_33 IN varchar2,
5882 p_input_34 IN varchar2,
5883 p_input_35 IN varchar2,
5884 p_input_36 IN varchar2,
5885 p_input_37 IN varchar2,
5886 p_input_38 IN varchar2,
5887 p_input_39 IN varchar2,
5888 p_input_40 IN varchar2,
5889 p_validate IN varchar2,
5890 p_exclude_from_output OUT nocopy varchar2,
5891 sp_out_1 OUT nocopy varchar2,
5892 sp_out_2 OUT nocopy varchar2,
5893 sp_out_3 OUT nocopy varchar2,
5894 sp_out_4 OUT nocopy varchar2,
5895 sp_out_5 OUT nocopy varchar2,
5896 ret_str_len OUT nocopy number,
5897 p_error OUT nocopy boolean
5898 ) RETURN VARCHAR2
5899 IS
5900 l_input_2 varchar2(100);
5901 return_value varchar2(32767);
5902 l_exclude_from_output_chk boolean;
5903 r_input_6 varchar2(100);
5904
5905 TYPE function_columns IS RECORD(
5906 p_parameter_name varchar2(100),
5907 p_parameter_value varchar2(100),
5908 p_output_value varchar2(100)
5909 );
5910 function_parameter_rec function_columns;
5911 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
5912 INDEX BY BINARY_INTEGER;
5913 parameter_record input_parameter_record;
5914
5915 BEGIN
5916 hr_utility.trace('Formatting RU Record');
5917 hr_utility.trace('Format Mode p_input_40 '||p_input_40);
5918 IF p_input_40='FLAT' THEN
5919 --{
5920 IF p_report_qualifier = 'PR' THEN -- Bug # 5668970
5921 r_input_6 := rpad('0',15,'0');
5922 END IF;
5923
5924 parameter_record(1).p_parameter_name:= ' Allocated Tips';
5925 parameter_record(1).p_parameter_value:=p_input_3;
5926
5927 parameter_record(2).p_parameter_name:= 'Uncollected employee tax on tips';
5928 parameter_record(2).p_parameter_value:=p_input_4;
5929
5930 parameter_record(3).p_parameter_name:= 'medical savings a/c';
5931 parameter_record(3).p_parameter_value:=p_input_5;
5932
5933 parameter_record(4).p_parameter_name:= 'simple retirement a/c';
5934 parameter_record(4).p_parameter_value:=p_input_6;
5935
5936 parameter_record(5).p_parameter_name:= 'qualified adoption expenses';
5937 parameter_record(5).p_parameter_value:=p_input_7;
5938
5939 parameter_record(6).p_parameter_name:= 'Uncollected SS tax';
5940 parameter_record(6).p_parameter_value:=p_input_8;
5941
5942 parameter_record(7).p_parameter_name:= 'Uncollected medicare tax';
5943 parameter_record(7).p_parameter_value:=p_input_9;
5944
5945 parameter_record(8).p_parameter_name:= 'wages sub. to PR tax';
5946 parameter_record(8).p_parameter_value:=p_input_10;
5947
5948 parameter_record(9).p_parameter_name:= 'Commissions sub.to PR tax';
5949 parameter_record(9).p_parameter_value:=p_input_11;
5950
5951 parameter_record(10).p_parameter_name:= 'Allowances sub. to PR tax';
5952 parameter_record(10).p_parameter_value:=p_input_12;
5953
5954 parameter_record(11).p_parameter_name:= 'Tips sub to PR tax';
5955 parameter_record(11).p_parameter_value:=p_input_13;
5956
5957 parameter_record(12).p_parameter_name:= 'Total wages sub to PR tax';
5958 parameter_record(12).p_parameter_value:=p_input_14;
5959
5960 parameter_record(13).p_parameter_name:= 'PR tax withheld';
5961 parameter_record(13).p_parameter_value:=p_input_15;
5962
5963 parameter_record(14).p_parameter_name:= 'Retirement fund ann. contri';
5964 parameter_record(14).p_parameter_value:=p_input_16;
5965
5966 parameter_record(15).p_parameter_name:= 'Total wages sub to VI,GU,AS and MP islands';
5967 parameter_record(15).p_parameter_value:=p_input_17;
5968
5969 parameter_record(16).p_parameter_name:= 'VI,GU or MP Islands income tax wh';
5970 parameter_record(16).p_parameter_value:=p_input_18;
5971
5972 --Added New Field in Pos: 115 - 129
5973
5974 parameter_record(17).p_parameter_name:= 'Income Und Sec 409A on Non-Qual Def Comp Plan';
5975 parameter_record(17).p_parameter_value := p_input_20;
5976
5977 --
5978 -- Validation and Formatting for above fields done in this loop
5979 FOR i in 1..17
5980 LOOP
5981 parameter_record(i).p_output_value :=
5982 pay_us_reporting_utils_pkg.data_validation(
5983 p_effective_date,
5984 p_report_type,
5985 p_format,
5986 p_report_qualifier,
5987 p_record_name,
5988 'NEG_CHECK',
5989 parameter_record(i).p_parameter_value,
5990 parameter_record(i).p_parameter_name,
5991 p_input_39,
5992 null,
5993 p_validate,
5994 p_exclude_from_output,
5995 sp_out_1,
5996 sp_out_2);
5997 IF p_exclude_from_output = 'Y' THEN
5998 l_exclude_from_output_chk := TRUE;
5999 END IF;
6000
6001 hr_utility.trace(parameter_record(i).p_parameter_name||' = '
6002 ||parameter_record(i).p_output_value);
6003 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
6004 END LOOP;
6005 -- Formatting RU record for format mode FLAT
6006 -- formatting Total no of record
6007 l_input_2 := lpad(substr(nvl(p_input_2,'0'),1,7),7,0);
6008 -- Formatting RU record for W2 reporting
6009 IF p_report_qualifier = 'PR' THEN
6010 return_value:='RU'||l_input_2
6011 /* ||parameter_record(1).p_output_value */
6012 ||rpad('0',15,'0') /* 6644795 */
6013 ||parameter_record(2).p_output_value
6014 /* ||parameter_record(3).p_output_value
6015 ||parameter_record(4).p_output_value
6016 ||parameter_record(5).p_output_value
6017 ||parameter_record(6).p_output_value
6018 --||rpad(parameter_record(7).p_output_value,255)
6019 ||parameter_record(7).p_output_value */
6020 ||rpad('0',75,'0')/* 6644795 */
6021 ||rpad(parameter_record(17).p_output_value,240)
6022 ||parameter_record(8).p_output_value
6023 ||parameter_record(9).p_output_value
6024 ||parameter_record(10).p_output_value
6025 ||parameter_record(11).p_output_value
6026 ||parameter_record(12).p_output_value
6027 ||parameter_record(13).p_output_value
6028 ||parameter_record(14).p_output_value
6029 ||parameter_record(15).p_output_value
6030 ||rpad(parameter_record(16).p_output_value,38);
6031 ELSE
6032 return_value:='RU'||l_input_2
6033 ||parameter_record(1).p_output_value
6034 ||parameter_record(2).p_output_value
6035 ||parameter_record(3).p_output_value
6036 ||parameter_record(4).p_output_value
6037 ||parameter_record(5).p_output_value
6038 ||parameter_record(6).p_output_value
6039 --||rpad(parameter_record(7).p_output_value,255)
6040 ||parameter_record(7).p_output_value
6041 ||rpad(parameter_record(17).p_output_value,240)
6042 ||parameter_record(8).p_output_value
6043 ||parameter_record(9).p_output_value
6044 ||parameter_record(10).p_output_value
6045 ||parameter_record(11).p_output_value
6046 ||parameter_record(12).p_output_value
6047 ||parameter_record(13).p_output_value
6048 ||parameter_record(14).p_output_value
6049 ||parameter_record(15).p_output_value
6050 ||rpad(parameter_record(16).p_output_value,38);
6051 END IF ;
6052 ret_str_len:=length(return_value);
6053 --}
6054 ELSIF p_input_40 = 'CSV' THEN
6055 -- Formatting RU record for W2 audit reports
6056 return_value:='RU'||','||l_input_2
6057 ||','||p_input_3
6058 ||','||p_input_4
6059 ||','||p_input_5
6060 ||','||p_input_6
6061 ||','||p_input_7
6062 ||','||p_input_8
6063 ||','||p_input_9
6064 ||','||p_input_20
6065 ||','||lpad(' ',225)
6066 ||','||p_input_10
6067 ||','||p_input_11
6068 ||','||p_input_12
6069 ||','||p_input_13
6070 ||','||p_input_14
6071 ||','||p_input_15
6072 ||','||p_input_16
6073 ||','||p_input_17
6074 ||','||p_input_18
6075 ||','||lpad(' ',23);
6076
6077 END IF; -- p_input_40
6078 ret_str_len:=length(return_value);
6079 p_error := l_exclude_from_output_chk;
6080 return return_value;
6081 END format_W2_RU_record; -- End of Formatting RU Record
6082
6083 -- Formatting RF record for W2 reporting
6084 --
6085 /*
6086 Effective Date --> p_effective_date
6087 Report Type (i.e.W2 ) --> p_report_type
6088 Report Format --> p_format
6089 Report Qualifier --> p_report_qualifier
6090 Record Name (i.e. RF) --> p_record_name
6091 Record Identifier --> p_record_identifier
6092 Number of RW Records --> p_total_no_of_record
6093 Wages, Tips and other Compensation --> p_total_wages
6094 Federal Income Tax Withheld --> p_total_taxes
6095 Report Format Mode (FLAT, CSV) --> p_format_mode
6096 Validation Flag --> p_validate
6097 Exclude Record from mag file --> p_exclude_from_output
6098 Return Record Length --> ret_str_len
6099 Validation Error Flag --> p_error
6100 */
6101 FUNCTION format_W2_RF_record(
6102 p_effective_date IN varchar2,
6103 p_report_type IN varchar2,
6104 p_format IN varchar2,
6105 p_report_qualifier IN varchar2,
6106 p_record_name IN varchar2,
6107 p_record_identifier IN varchar2,
6108 p_total_no_of_record IN varchar2,
6109 p_total_wages IN varchar2,
6110 p_total_taxes IN varchar2,
6111 p_format_mode IN varchar2,
6112 p_validate IN varchar2,
6113 p_exclude_from_output OUT nocopy varchar2,
6114 ret_str_len OUT nocopy number,
6115 p_error OUT nocopy boolean
6116 ) RETURN VARCHAR2
6117 IS
6118 return_value varchar2(32767);
6119 l_exclude_from_output_chk boolean;
6120 l_total_records varchar2(50);
6121 l_wages varchar2(100);
6122 l_taxes varchar2(100);
6123 sp_out_1 varchar2(100);
6124 sp_out_2 varchar2(100);
6125 BEGIN
6126 hr_utility.trace('Formatting RF Record');
6127 hr_utility.trace('Format Mode p_input_40 '||p_format_mode);
6128 hr_utility.trace('Report Qualifier before Formatting RF Record '
6129 ||p_report_qualifier);
6130 IF ((p_report_qualifier = 'PA_PHILA') OR -- Bug # 2680189
6131 (p_report_qualifier = 'CO')) THEN -- Bug # 2813555
6132 --{
6133 l_total_records := lpad(nvl(p_total_no_of_record,'0'),9,0);
6134 return_value := 'RF'
6135 ||lpad(' ',5)
6136 ||l_total_records
6137 ||lpad(' ',496);
6138 --}
6139 ELSIF p_report_qualifier IN ('PA', 'CT') THEN
6140
6141 --{
6142 -- Validating Total Wage for Negative Value
6143 --
6144 l_wages :=
6145 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
6146 p_report_type,
6147 p_format,
6148 p_report_qualifier,
6149 p_record_name,
6150 'NEG_CHECK',
6151 p_total_wages,
6152 'State taxable Wages',
6153 null,
6154 null,
6155 p_validate,
6156 p_exclude_from_output,
6157 sp_out_1,
6158 sp_out_2);
6159
6160 IF p_exclude_from_output = 'Y' THEN
6161 l_exclude_from_output_chk := TRUE;
6162 END IF;
6163 -- Validating Total Tax for Negative Value
6164 --
6165 l_taxes :=
6166 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
6167 p_report_type,
6168 p_format,
6169 p_report_qualifier,
6170 p_record_name,
6171 'NEG_CHECK',
6172 p_total_taxes,
6173 'SIT Withheld',
6174 null,
6175 null,
6176 p_validate,
6177 p_exclude_from_output,
6178 sp_out_1,
6179 sp_out_2);
6180
6181 IF p_exclude_from_output = 'Y' THEN
6182 l_exclude_from_output_chk := TRUE;
6183 END IF;
6184 -- Formatting RF Record for state PA
6185 IF p_report_qualifier = 'PA' THEN
6186 --{
6187 l_total_records := lpad(substr(nvl(p_total_no_of_record,' '),1,7),7,0);
6188 return_value := 'RF'||lpad(' ',473)
6189 ||l_total_records
6190 ||l_wages
6191 ||l_taxes;
6192 --}
6193 -- Formatting RF Record for state CT
6194 ELSIF p_report_qualifier = 'CT' THEN
6195 --{
6196 --
6197 -- This is to fix bug # 2640074
6198 -- column positions of State Wages/Withheld changed
6199 -- return_value := 'RF'||rpad(l_total_records,10)||rpad(l_wages,17)
6200 -- ||rpad(l_taxes,483);
6201 --
6202 l_total_records := lpad(substr(nvl(p_total_no_of_record,' '),1,9),9,0);
6203 return_value := 'RF'||rpad(l_total_records,9)
6204 ||rpad(l_wages,16)
6205 ||rpad(l_taxes,483);
6206 --}
6207 END IF;
6208 --}
6209 ELSE
6210
6211 return_value:= 'RF'
6212 ||lpad(lpad(substr(nvl(p_total_no_of_record,' '),1,9)
6213 ,9,0),14)
6214 ||rpad(' ',496);
6215
6216 END IF;
6217 p_error := l_exclude_from_output_chk;
6218 ret_str_len:=length(return_value);
6219 return return_value;
6220 END format_W2_RF_record; -- End of Formatting RF Record for W2 Reporting
6221
6222 END pay_us_mmrf_w2_format_record; -- End of Package Body