[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MMRF_SQWL_FORMAT_RECORD
Source
1 PACKAGE BODY pay_us_mmrf_sqwl_format_record AS
2 /* $Header: pyussqfr.pkb 120.21.12020000.19 2013/02/25 14:55:06 nvelaga ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8
9 Name
10 pay_us_mmrf_sqwl_format_record
11
12 Purpose
13 The purpose of this package is to format reacord to support the
14 generation of SQWL magnetic tape for US legilsative requirements.
15
16 Notes
17 Referenced By: Package pay_us_reporting_utils_pkg
18
19 History
20 25-Feb-2013 nvelaga 115.50 16100022 Modified the format_SQWL_RE_record to dispaly X for LA_SQWL
21 Location Address, when it is blank.
22 10-Jan-2013 ybudamal 115.49 16091833 Modified format_SQWL_RS_record to format RS record in CSV format
23 by removing the extra space for New Mexico(NM) SQWL.
24 10-Jan-2013 ybudamal 115.48 16091833 Modified format_SQWL_RS_record for New Mexico(NM) SQWL to remove
25 the extra space present in the RS record. Now RS record for NM SQWL
26 displays with 512 characters and the last character(512th) is the
27 End of Line - Identified Carriage Return.
28 28-Nov-2012 ybudamal 115.47 4959283 Modified format_SQWL_RS_record for Minnesota(MN) SQWL
29 from positions 256-259 as per the new specifications for Employer
30 Unit Number. This file has been modified for Q4 2012 MN SQWL changes.
31 29-Sep-2012 ybudamal 115.46 14356614 Modified format_SQWL_RS_record to display the r_input_25
32 input value correctly when special characters present in
33 r_input_25.
34 26-Sep-2012 ybudamal 115.45 14356614 Modified format_SQWL_RS_record to display the correct
35 NM Income Tax Withheld value for the CSV format.
36 26-Sep-2012 ybudamal 115.44 14356614 Modified format_SQWL_RS_record to check negative values for
37 r_input_20, r_input_27, r_input_28 and r_input_32 for Q3 2012
38 New Mexico(NM) SQWL changes.
39 23-Sep-2012 ybudamal 115.43 14356614 Modified format_SQWL_RS_record to format RS record in CSV format
40 for Q3 2012 New Mexico(NM) SQWL changes.
41 17-Sep-2012 ybudamal 115.42 14356614 Modified format_SQWL_RS_record to format r_input_28
42 for New Mexico(NM) SQWL as per the new NM EFW2 format for Q3 2012.
43 06-Sep-2012 ybudamal 115.41 14356614 Modified format_SQWL_RS_record to remove the Multiple Style
44 comments on single line.
45 06-Sep-2012 ybudamal 115.40 14356614 Modified format_SQWL_RS_record for New Mexico(NM) SQWL
46 as per the new NM EFW2 format for Q3 2012.
47 19-Jun-2012 nvelaga 115.39 14086658 Commented r_input_7 in RA record for SC SQWL.
48 18-Jun-2012 nvelaga 115.38 14099390 Corrected the validation on r_input_38 inorder not to
49 override the validation on r_input_37.
50 05-Jun-2012 nvelaga 115.37 14099390 Corrected the input description as 'Educational Wages'.
51 04-Jun-2012 nvelaga 115.36 14099390 Modified format_SQWL_RS_record for FL. Added data
52 validation on r_input_38.
53 25-May-2012 nvelaga 115.35 14086658 Modified the file to remove double multiline comments
54 in sinlge line for check_patch errors.
55 24-May-2012 nvelaga 115.34 14086658 Modified RA, RE and RS records for South Carolina SQWL.
56 24-May-2012 nvelaga 115.33 13998882 Modified RE record for Louisiana SQWL.
57 22-Nov-2011 nvelaga 115.32 13377871 Modified RE and RS Records for Virginia SQWL.
58 03-Sep-2010 sjawid 115.27 9913439 Utah SQWL efw2 format changes for Q3 2010
59 02-Sep-2010 emunisek 115.26 9959504 Added negative check on the Actual SUI Taxable
60 wages in procedure format_SQWL_RS_record
61 25-Mar-2010 emunisek 115.25 9356178 Added new field to the Florida SQWL Record to
62 display the Actual Florida SUI Taxable
63 05-09-2008 pannapur 115.24 7335939 Modified 139th position for AL_SQWL to display 'AL'
64 27-Jul-2007 sjawid 115.23 6277029 Modified RA, RE Records for Kansas.
65 18-Jun-2007 sjawid 115.20 6036926 Kansas(KS) sqwl fomat changed to MMREF from ICESA.
66 23-May-2007 sjawid 115.19 6057156 Added RT record for VA State.
67 29-Nov-2006 sackumar 115.18 5686173 Modified RA, RE and RS Records for AL State.
68 18-Nov-2006 sackumar 115.17 5657141 Modified RA Records for AL State
69 02-Nov-2006 sackumar 115.16 5637654 Modified (POS 36-37)RA Records for Texas
70 30-May-2006 sackumar 115.15 5092657 Modified RS Records for Texas SQWL
71 Modified RE, RS Record for Nebraska.
72 17-Feb-2006 sudedas 115.14 4559359 Modified RA, RE, RS Records for Texas SQWL
73 4867893 Modified RT Record for Alabama and Florida SQWL
74 18-Nov-2005 saikrish 115.13 3835632 Added code to format Tips Wages.
75 11-Nov-2005 sudedas 115.12 4552131 Modified RE,RS and RT Record for AL_SQWL
76 1236643 Modified RS Record for MN_SQWL
77 25-MAY-2005 sudedas 115.10 4170713 Modified RS record for MN_SQWL
78 07-SEP-2004 jgoswami 115.9 3871022 Modified RA record for NV_SQWL
79 02-SEP-2004 jgoswami 115.8 3871022 Modified RA record for NV_SQWL
80 28-AUG-2004 jgoswami 115.7 3830050 Added code for NV_SQWL
81 08-AUG-2004 jgoswami 115.6 3527986 Removed record delimiters for NY_SQWL
82 01-MAR-2004 jgoswami 115.5 3334497 Added code for AK_SQWL
83 19-DEC-2003 jgoswami 115.4 3319454 Added EIN fro WA_SQWL for a02,a03
84 27-Oct-2003 fusman 115.1 Bug:3175230 Added End of rec for NY_SQWL
85 14-Jul-2003 ppanda 115.0 Created
86 */
87
88 -- This function determines the required length for fields
89 -- in various data record. This function is being referenced
90 -- from DAT_VALIDATION function in package pay_us_reporting_utils_pkg
91 --
92 FUNCTION set_req_field_length (p_report_type IN varchar2,
93 p_format IN varchar2,
94 p_report_qualifier IN varchar2,
95 p_record_name IN varchar2,
96 p_input_1 IN varchar2,
97 p_input_2 IN varchar2,
98 p_input_3 IN varchar2,
99 p_input_4 IN varchar2,
100 p_input_5 IN varchar2
101 ) return NUMBER
102 IS
103 l_length number(10);
104 BEGIN
105 IF p_record_name in ('RU','RT','RF') THEN
106 --{
107 l_length := 15;
108 IF (p_record_name = 'RT') and (p_report_qualifier = 'FL_SQWL') THEN
109 IF (p_input_3 = 'Total Tax Due') THEN
110 l_length := 11;
111 ELSIF (p_input_3 like '%Month%') THEN
112 l_length := 7;
113 END IF;
114 END IF;
115 --}
116 ELSIF p_record_name in ('RS','RO','RW') THEN
117 --{
118 l_length := 11;
119 IF ((p_record_name = 'RS') AND
120 (p_input_3 = 'Other State Data')
121 ) THEN
122 l_length := 10;
123 hr_utility.trace('RS. Other State Data.l_length = '||to_char(l_length));
124 ELSIF p_record_name = 'RS' THEN
125 --{
126 IF p_report_qualifier = 'GA_SQWL' THEN
127 IF p_input_3 = 'SUI Insurance Wages' THEN
128 l_length :=9;
129 END IF;
130 END IF; --GA_SQWL
131 --}
132 END IF; --RS Record
133 --}
134 ELSIF p_record_name in ('RSSUMM') THEN
135 --{
136 l_length := 14;
137 --}
138 ELSIF (p_record_name = 'D') and (p_report_qualifier = 'AK_SQWL') THEN
139 l_length := 11;
140 END IF; -- p_record_name
141 return l_length;
142 END set_req_field_length; -- End of set_req_field_length
143 --
144
145 PROCEDURE format_sqwl_contact_prsn_info (
146 p_report_qualifier IN varchar2,
147 p_record_name IN varchar2,
148 p_validate IN varchar2,
149 p_exclude_from_output IN OUT nocopy varchar2,
150 p_contact_prsn_name IN OUT nocopy varchar2,
151 p_contact_prsn_phone IN OUT nocopy varchar2,
152 p_contact_prsn_extension IN OUT nocopy varchar2,
153 p_contact_prsn_email IN OUT nocopy varchar2,
154 p_contact_prsn_fax IN OUT nocopy varchar2,
155 p_contact_prsn_first_name IN OUT nocopy varchar2,
156 p_contact_prsn_middle_name IN OUT nocopy varchar2,
157 p_contact_prsn_last_name IN OUT nocopy varchar2)
158 IS
159 BEGIN
160 IF p_contact_prsn_name is NULL THEN
161 --{
162 p_contact_prsn_name :=lpad(' ',27);
163 p_contact_prsn_phone :=lpad(' ',15);
164 p_contact_prsn_extension :=lpad(' ',5);
165 p_contact_prsn_email :=lpad(' ',40);
166 IF ((p_report_qualifier = 'NY_SQWL') AND
167 (p_record_name = 'RA')) THEN
168 p_contact_prsn_fax := lpad(' ',24); --Fax
169 ELSE
170 p_contact_prsn_fax := lpad(' ',10); --Fax
171 END IF;
172 --}
173 ELSE
174 --{
175 p_contact_prsn_name :=rpad(substr(nvl(upper(p_contact_prsn_name),' '),1,27),27);
176 p_contact_prsn_email :=rpad(substr(nvl(upper(p_contact_prsn_email),' '),1,40),40);
177 IF p_report_qualifier = 'NY_SQWL' and
178 p_record_name = 'RA' THEN
179 p_contact_prsn_fax :=
180 rpad(substr(nvl(replace(p_contact_prsn_fax,' '),' '),1,24),24);
181 ELSE
182 p_contact_prsn_fax :=
183 rpad(substr(nvl(replace(p_contact_prsn_fax,' '),' '),1,10),10);
184 END IF;
185 --}
186 END IF;
187 END format_sqwl_contact_prsn_info;
188
189 -- Formatting RA record for SQWL reporting
190 --
191 /*----------------------------- Parameter mapping. -----------------------------
192 Record Identifier, --> p_input_1
193 Submitter''s Employer Identification Number (EIN), --> p_input_2
194 Personal Identification Number (PIN) --> p_input_3,
195 Resub Indicator --> p_input_4,
196 -- This is fix for bug # 2510920 to rename TLCN to WFID
197 Resub WFID --> p_input_5,
198 Software Code --> p_input_6,
199 Company Name --> p_input_7,
200 Location Address --> p_input_8,
201 Delivery Address --> p_input_9,
202 City --> p_input_10,
203 State Abbreviation --> p_input_11,
204 Zip Code --> p_input_12,
205 Zip Code Extension --> p_input_13,
206 Foreign State / Province --> p_input_14,
207 Foreign Postal Code --> p_input_15,
208 Country Code --> p_input_16,
209 Submitter Name --> p_input_17,
210 Location Address --> p_input_18,
211 Delivery Address --> p_input_19,
212 City --> p_input_20,
213 State Abbreviation --> p_input_21,
214 Zip Code --> p_input_22,
215 Zip Code Extension --> p_input_23,
216 Foreign State / Province --> p_input_24,
217 Foreing Postal Code --> p_input_25,
218 Country Code --> p_input_26,
219 Contact Name --> p_input_27,
220 Contact Phone Number --> p_input_28,
221 Contact Phone Extension --> p_input_29,
222 Contact E-Mail --> p_input_30,
223 Blank,Contact FAX --> p_input_31,
224 Preferred Method Of Problem Notification Code --> p_input_32,
225 Preparer Code --> p_input_33,
226 */
227
228 FUNCTION format_SQWL_RA_record(
229 p_effective_date IN varchar2,
230 p_report_type IN varchar2,
231 p_format IN varchar2,
232 p_report_qualifier IN varchar2,
233 p_record_name IN varchar2,
234 p_input_1 IN varchar2,
235 p_input_2 IN varchar2,
236 p_input_3 IN varchar2,
237 p_input_4 IN varchar2,
238 p_input_5 IN varchar2,
239 p_input_6 IN varchar2,
240 p_input_7 IN varchar2,
241 p_input_8 IN varchar2,
242 p_input_9 IN varchar2,
243 p_input_10 IN varchar2,
244 p_input_11 IN varchar2,
245 p_input_12 IN varchar2,
246 p_input_13 IN varchar2,
247 p_input_14 IN varchar2,
248 p_input_15 IN varchar2,
249 p_input_16 IN varchar2,
250 p_input_17 IN varchar2,
251 p_input_18 IN varchar2,
252 p_input_19 IN varchar2,
253 p_input_20 IN varchar2,
254 p_input_21 IN varchar2,
255 p_input_22 IN varchar2,
256 p_input_23 IN varchar2,
257 p_input_24 IN varchar2,
258 p_input_25 IN varchar2,
259 p_input_26 IN varchar2,
260 p_input_27 IN varchar2,
261 p_input_28 IN varchar2,
262 p_input_29 IN varchar2,
263 p_input_30 IN varchar2,
264 p_input_31 IN varchar2,
265 p_input_32 IN varchar2,
266 p_input_33 IN varchar2,
267 p_input_34 IN varchar2,
268 p_input_35 IN varchar2,
269 p_input_36 IN varchar2,
270 p_input_37 IN varchar2,
271 p_input_38 IN varchar2,
272 p_input_39 IN varchar2,
273 p_input_40 IN varchar2,
274 p_validate IN varchar2,
275 p_exclude_from_output OUT nocopy varchar2,
276 sp_out_1 OUT nocopy varchar2,
277 sp_out_2 OUT nocopy varchar2,
278 sp_out_3 OUT nocopy varchar2,
279 sp_out_4 OUT nocopy varchar2,
280 sp_out_5 OUT nocopy varchar2,
281 ret_str_len OUT nocopy number,
282 p_error OUT nocopy boolean
283 ) RETURN VARCHAR2
284 IS
285 l_agent_indicator varchar2(1);
286 l_emp_ein varchar2(100);
287 l_agent_ein varchar2(100);
288 l_other_ein varchar2(100);
289 l_term_indicator varchar2(1);
290 l_exclude_from_output_chk boolean;
291 l_input_8 varchar2(50);
292 l_bus_tax_acct_number varchar2(50);
293 l_rep_qtr varchar2(300);
294 l_rep_prd varchar2(300);
295 l_end_of_rec varchar2(20);
296 return_value varchar2(32767);
297 l_pin varchar2(50);
298 l_pblm_code varchar2(1);
299 l_preparer_code varchar2(1);
300 p_end_of_rec varchar2(20);
301 r_input_1 varchar2(300);
302 r_input_2 varchar2(300);
303 r_input_3 varchar2(300);
304 r_input_4 varchar2(300);
305 r_input_5 varchar2(300);
306 r_input_6 varchar2(300);
307 r_input_7 varchar2(300);
308 r_input_8 varchar2(300);
309 r_input_9 varchar2(300);
310 r_input_10 varchar2(300);
311 r_input_11 varchar2(300);
312 r_input_12 varchar2(300);
313 r_input_13 varchar2(300);
314 r_input_14 varchar2(300);
315 r_input_15 varchar2(300);
316 r_input_16 varchar2(300);
317 r_input_17 varchar2(300);
318 r_input_18 varchar2(300);
319 r_input_19 varchar2(300);
320 r_input_20 varchar2(300);
321 r_input_21 varchar2(300);
322 r_input_22 varchar2(300);
323 r_input_23 varchar2(300);
324 r_input_24 varchar2(300);
325 r_input_25 varchar2(300);
326 r_input_26 varchar2(300);
327 r_input_27 varchar2(300);
328 r_input_28 varchar2(300);
329 r_input_29 varchar2(300);
330 r_input_30 varchar2(300);
331 r_input_31 varchar2(300);
332 r_input_32 varchar2(300);
333 r_input_33 varchar2(300);
334 r_input_34 varchar2(300);
335 r_input_35 varchar2(300);
336 r_input_36 varchar2(300);
337 r_input_37 varchar2(300);
338 r_input_38 varchar2(300);
339 r_input_39 varchar2(300);
340
341 BEGIN
342 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
343 hr_utility.trace('RA Record Formatting started ');
344 hr_utility.trace('Format_SQWL_RA_Record Begin for Company '|| p_input_7);
345 -- Initializing local variables with parameter value
346 --{
347 r_input_2 := p_input_2;
348 r_input_3 := p_input_3;
349 r_input_4 := p_input_4;
350 r_input_5 := p_input_5;
351 r_input_6 := p_input_6;
352 r_input_7 := p_input_7;
353 r_input_8 := p_input_8;
354 r_input_9 := p_input_9;
355 r_input_10 := p_input_10;
356 r_input_11 := p_input_11;
357 r_input_12 := p_input_12;
358 r_input_13 := p_input_13;
359 r_input_14 := p_input_14;
360 r_input_15 := p_input_15;
361 r_input_16 := p_input_16;
362 r_input_17 := p_input_17;
363 r_input_18 := p_input_18;
364 r_input_19 := p_input_19;
365 r_input_20 := p_input_20;
366 r_input_21 := p_input_21;
367 r_input_22 := p_input_22;
368 r_input_23 := p_input_23;
369 r_input_24 := p_input_24;
370 r_input_25 := p_input_25;
371 r_input_26 := p_input_26;
372 r_input_27 := p_input_27;
373 r_input_28 := p_input_28;
374 r_input_29 := p_input_29;
375 r_input_30 := p_input_30;
376 r_input_31 := p_input_31;
377 r_input_32 := p_input_32;
378 r_input_33 := p_input_33;
379 r_input_34 := p_input_34;
380 r_input_35 := p_input_35;
381 r_input_36 := p_input_36;
382 r_input_37 := p_input_37;
383 r_input_38 := p_input_38;
384 r_input_39 := p_input_39;
385 --}
386
387 -- Validation Starts
388 -- EIN Validation
389 IF p_input_40 = 'FLAT' THEN
390 l_emp_ein :=
391 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
392 p_report_type,
393 p_format,
394 p_report_qualifier,
395 p_record_name,
396 'EIN',
397 p_input_2,
398 'Submitters EIN',
399 p_input_17,
400 null,
401 p_validate,
402 p_exclude_from_output,
403 sp_out_1,
404 sp_out_2);
405
406 IF p_exclude_from_output = 'Y' THEN
407 l_exclude_from_output_chk := TRUE;
408 END IF;
409 hr_utility.trace('Valid EIN '||l_emp_ein);
410 -- Validation Ends
411 --
412 -- Formatiing Starts
413 /*Pos:3-11 EIN blank for LA,NH SQWL. */
414
415 IF ((p_report_qualifier = 'LA_SQWL')OR
416 (p_report_qualifier = 'SC_SQWL') OR /* Added for Bug# 14086658 */
417 (p_report_qualifier = 'NH_SQWL')) THEN
418 l_emp_ein := lpad(' ',9);
419 END IF;
420
421 /* Pos:12 - 216 blank for PA. */
422
423 IF ((p_report_qualifier = 'LA_SQWL')OR
424 (p_report_qualifier = 'NH_SQWL')OR
425 (p_report_qualifier = 'SC_SQWL')OR
426 (p_report_qualifier = 'NY_SQWL') OR
427 (p_report_qualifier = 'NV_SQWL')OR
428 (p_report_qualifier = 'VA_SQWL')) THEN
429 --{
430 l_pin := lpad(' ',17);
431 r_input_4 := lpad(' ',1);
432 r_input_5 := lpad(' ',6);
433 r_input_6 := lpad(' ',2);
434 r_input_7 := lpad(' ',57);
435 r_input_8 := lpad(' ',22);
436 r_input_9 := lpad(' ',22);
437 r_input_10 := lpad(' ',22);
438 r_input_11 := lpad(' ',2);
439 r_input_12 := lpad(' ',5);
440 r_input_13 := lpad(' ',4);
441 r_input_14 := lpad(' ',23);
442 r_input_15 := lpad(' ',15);
443 r_input_16 := lpad(' ',2);
444 r_input_24 := lpad(' ',23);
445 r_input_25 := lpad(' ',15);
446 r_input_26 := lpad(' ',2);
447
448 IF ((p_report_qualifier = 'NH_SQWL') /* OR
449 (p_report_qualifier = 'SC_SQWL') */ ) THEN -- Commented SC_SQWL for Bug# 14086658
450 --{
451 r_input_17 := lpad(' ',57);
452 r_input_18 := lpad(' ',22);
453 r_input_19 := lpad(' ',22);
454 r_input_20 := lpad(' ',22);
455 r_input_21 := lpad(' ',2);
456 r_input_22 := lpad(' ',5);
457 r_input_23 := lpad(' ',4);
458 --}
459 END IF;
460
461 IF (p_report_qualifier = 'NY_SQWL') THEN
462 l_pin := lpad(' ',15);
463 r_input_23 := lpad(' ',4);
464 ELSIF (p_report_qualifier = 'NV_SQWL') THEN
465 l_pin := '0';
466 l_pin := rpad(substr(l_pin,1,17),17,'0');
467 ELSIF (p_report_qualifier = 'SC_SQWL') THEN /* Added for Bug# 14086658 */
468 r_input_6 := lpad('99', 2);
469 r_input_23 := lpad(' ',4);
470 END IF;
471 --}
472 ELSE
473 l_pin := rpad(substr(nvl(p_input_3,' '),1,17),17);
474 hr_utility.trace(' l_pin = '||l_pin);
475 --}
476 END IF;
477
478 IF ( p_report_qualifier = 'AL_SQWL' ) THEN
479 r_input_4 := lpad(' ', 1) ;
480 r_input_5 := lpad(' ', 6) ;
481 r_input_6 := lpad('99', 2) ;
482 END IF;
483
484 /* Position : 12 - 35, 438-442, 446-485, 489-498 Blank For Texas (Bug# 4559359) */
485 IF ( p_report_qualifier = 'TX_SQWL' ) THEN
486 l_pin := lpad(' ', 17) ;
487 r_input_4 := lpad(' ', 1) ;
488 r_input_5 := lpad(' ', 6) ;
489 r_input_6 := lpad('99', 2) ;
490 r_input_29 := lpad(' ', 5) ;
491 r_input_30 := lpad(' ', 40) ;
492 r_input_31 := lpad(' ', 10) ;
493 END IF ;
494
495 /* Pos:38-94 Company name. Pos:489-498 Fax blank for SC_SQWL. */
496 IF p_report_qualifier = 'SC_SQWL' THEN
497 -- r_input_7 := p_input_7; /* Bug#14086658 */
498 r_input_31 := lpad(' ',10);
499 END IF;
500
501 /* Pos:489-498 Fax,Pos:446-485 Email blank for LA_SQWL. */
502 IF p_report_qualifier = 'LA_SQWL' THEN
503 r_input_30 := lpad(' ',40);
504 r_input_31 := lpad(' ',10);
505 END IF;
506
507 /* Checking for preferred method of problem notification code which is
508 1=email, 2=fax, 3=postal service, 4=OWRS */
509 IF ((p_input_32 = '1' ) OR
510 (p_input_32 = '2' ) OR
511 (p_input_32 = '3' ) OR
512 (p_input_32 = '4' ) ) THEN
513 hr_utility.trace('Preferred method of code is correct. it is '||p_input_32);
514 l_pblm_code:= p_input_32;
515 ELSE
516 hr_utility.trace('Preferred method of code is incorrect. it is '||p_input_32);
517 l_pblm_code:= lpad(' ',1);
518 END IF;
519
520 If( (p_input_33 = 'A' )OR
521 (p_input_33 = 'S' )OR
522 (p_input_33 = 'L' )OR
523 (p_input_33 = 'P' )OR
524 (p_input_33 = 'O' )) THEN
525 l_preparer_code:= p_input_33;
526 hr_utility.trace('l_preparer_code is correct. it is '||p_input_33);
527 ELSE
528 IF (p_report_qualifier = 'AL_SQWL') THEN
529 l_preparer_code := lpad('O',1);
530 ELSE
531 l_preparer_code:= lpad(' ',1);
532 END IF;
533 hr_utility.trace('l_preparer_code is incorrect. it is '||p_input_33);
534 END IF;
535
536 IF ((p_report_qualifier = 'LA_SQWL')OR
537 (p_report_qualifier = 'NH_SQWL')OR
538 /* (p_report_qualifier = 'SC_SQWL')OR */ -- Commented for Bug# 14086658
539 (p_report_qualifier = 'TX_SQWL') /* Bug# 4559359 */
540 )
541 THEN
542 l_pblm_code:= lpad(' ',1);
543 l_preparer_code:= lpad(' ',1);
544 ELSIF ((p_report_qualifier = 'NV_SQWL') OR
545 (p_report_qualifier = 'SC_SQWL')) THEN /* Added SC_SQWL for Bug# 14086658 */
546 l_pblm_code:= lpad(' ',1);
547 l_preparer_code:= lpad('O',1);
548 END IF;
549
550 IF (p_report_qualifier = 'FL_SQWL') THEN
551 l_pin := rpad(substr(nvl(p_input_3,' '),1,8),17);
552 r_input_4 := lpad(' ',1);
553 r_input_5 := lpad(' ',6);
554 r_input_6 := lpad(' ',2);
555 r_input_8 := lpad(' ',22);
556 r_input_9 := lpad(' ',22);
557 r_input_10 := lpad(' ',22);
558 r_input_11 := lpad(' ',2);
559 r_input_12 := lpad(' ',5);
560 r_input_13 := lpad(' ',4);
561 r_input_14 := lpad(' ',23);
562 r_input_15 := lpad(' ',15);
563 r_input_16 := lpad(' ',2);
564 r_input_17 := lpad(' ',57);
565 r_input_18 := lpad(' ',22);
566 r_input_19 := lpad(' ',22);
567 r_input_20 := lpad(' ',22);
568 r_input_21 := lpad(' ',2);
569 r_input_22 := lpad(' ',5);
570 r_input_23 := lpad(' ',4);
571 r_input_24 := lpad(' ',23);
572 r_input_25 := lpad(' ',15);
573 r_input_26 := lpad(' ',2);
574 l_pblm_code:= lpad(' ',1);
575 l_preparer_code:= lpad(' ',1);
576 l_end_of_rec := p_end_of_rec;
577 ELSIF (p_report_qualifier = 'VA_SQWL') THEN
578 r_input_7 := p_input_7;
579 r_input_18 := lpad(' ',22);
580 r_input_29 := lpad(' ',5);
581 r_input_30 := lpad(' ',40);
582 r_input_31 := lpad(' ',10);
583
584 ELSIF (p_report_qualifier = 'KS_SQWL') THEN /*6036926 */
585 r_input_4 := lpad(' ',1);
586 r_input_5 := lpad(' ',6);
587 r_input_6 := lpad(' ',2);
588 r_input_8 := lpad(' ',22);
589 /* r_input_11 := lpad(' ',2);
590 r_input_12 := lpad(' ',5); */ /*Bug 6277029*/
591 r_input_13 := lpad(' ',4);
592 r_input_14 := lpad(' ',23);
593 r_input_15 := lpad(' ',15);
594 r_input_16 := lpad(' ',2);
595 r_input_18 := lpad(' ',22);
596 /* r_input_21 := lpad(' ',2);
597 r_input_22 := lpad(' ',5); */ /*Bug 6277029*/
598 r_input_23 := lpad(' ',4);
599 r_input_24 := lpad(' ',23);
600 r_input_25 := lpad(' ',15);
601 r_input_26 := lpad(' ',2);
602 r_input_30 := lpad(' ',40);
603 r_input_31 := lpad(' ',10);
604 l_pblm_code:= lpad(' ',1);
605 l_preparer_code:= lpad(' ',1);
606 END IF;
607
608 hr_utility.trace('p_input_40 = '||p_input_40);
609 IF (p_report_qualifier = 'NY_SQWL') THEN
610 /* Bug 3527986 Removed Delimiters fro NY */
611 --l_end_of_rec := p_end_of_rec;
612 return_value:='RA'
613 ||l_emp_ein||l_pin
614 ||rpad(substr(nvl(r_input_4,'0'),1,1),1)
615 ||rpad(substr(nvl(r_input_5,' '),1,6),6)
616 ||rpad(substr(nvl(r_input_6,' '),1,2),2)
617 ||rpad(substr(nvl(r_input_7,' '),1,57),57)
618 ||rpad(substr(nvl(r_input_8,' '),1,22),22)
619 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
620 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
621 ||rpad(substr(nvl(r_input_11,' '),1,2),2)
622 ||rpad(substr(nvl(r_input_12,' '),1,5),5)
623 ||rpad(substr(nvl(r_input_13,' '),1,4),9)
624 ||rpad(substr(nvl(r_input_14,' '),1,23),23)
625 ||rpad(substr(nvl(r_input_15,' '),1,15),15)
626 ||rpad(substr(nvl(r_input_16,' '),1,2),2)
627 ||rpad(substr(nvl(upper(r_input_17),' '),1,57),57)
628 ||rpad(substr(nvl(r_input_18,' '),1,22),22)
629 ||rpad(substr(nvl(r_input_19,' '),1,22),22)
630 ||rpad(substr(nvl(r_input_20,' '),1,22),22)
631 ||rpad(substr(nvl(r_input_21,' '),1,2),2)
632 ||rpad(substr(nvl(r_input_22,' '),1,5),5)
633 ||rpad(substr(nvl(r_input_23,' '),1,4),9)
634 ||rpad(substr(nvl(r_input_24,' '),1,23),23)
635 ||rpad(substr(nvl(r_input_25,' '),1,15),15)
636 ||rpad(substr(nvl(r_input_26,' '),1,2),2)
637 ||rpad(substr(nvl(r_input_27,' '),1,27),27)
638 ||rpad(substr(nvl(r_input_28,' '),1,15),15)
639 ||rpad(substr(nvl(r_input_29,' '),1,5),8)
640 ||rpad(substr(nvl(r_input_30,' '),1,43),43)
641 ||rpad(substr(nvl(r_input_31,' '),1,24),24)
642 ||l_end_of_rec;
643 ELSE
644 return_value:='RA'
645 ||l_emp_ein||l_pin
646 ||rpad(substr(nvl(r_input_4,'0'),1,1),1)
647 ||rpad(substr(nvl(r_input_5,' '),1,6),6)
648 ||rpad(substr(nvl(r_input_6,' '),1,2),2)
649 ||rpad(substr(nvl(r_input_7,' '),1,57),57)
650 ||rpad(substr(nvl(r_input_8,' '),1,22),22)
651 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
652 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
653 ||rpad(substr(nvl(r_input_11,' '),1,2),2)
654 ||rpad(substr(nvl(r_input_12,' '),1,5),5)
655 ||rpad(substr(nvl(r_input_13,' '),1,4),9)
656 ||rpad(substr(nvl(r_input_14,' '),1,23),23)
657 ||rpad(substr(nvl(r_input_15,' '),1,15),15)
658 ||rpad(substr(nvl(r_input_16,' '),1,2),2)
659 ||rpad(substr(nvl(upper(r_input_17),' '),1,57),57)
660 ||rpad(substr(nvl(r_input_18,' '),1,22),22)
661 ||rpad(substr(nvl(r_input_19,' '),1,22),22)
662 ||rpad(substr(nvl(r_input_20,' '),1,22),22)
663 ||rpad(substr(nvl(r_input_21,' '),1,2),2)
664 ||rpad(substr(nvl(r_input_22,' '),1,5),5)
665 ||rpad(substr(nvl(r_input_23,' '),1,4),9)
666 ||rpad(substr(nvl(r_input_24,' '),1,23),23)
667 ||rpad(substr(nvl(r_input_25,' '),1,15),15)
668 ||rpad(substr(nvl(r_input_26,' '),1,2),2)
669 ||rpad(substr(nvl(r_input_27,' '),1,27),27)
670 ||rpad(substr(nvl(r_input_28,' '),1,15),15)
671 ||rpad(substr(nvl(r_input_29,' '),1,5),8)
672 ||rpad(substr(nvl(r_input_30,' '),1,43),43)
673 ||rpad(substr(nvl(r_input_31,' '),1,10),10)
674 ||l_pblm_code
675 ||l_preparer_code
676 ||lpad(' ',12)
677 ||l_end_of_rec ;
678 ret_str_len:=length(return_value);
679 hr_utility.trace('---------------------FLAT----------------------------');
680 END IF;
681 ELSIF p_input_40 = 'CSV' THEN
682 hr_utility.trace('---------------------CSV----------------------------');
683 return_value:='RA'||','||p_input_2||','||p_input_3
684 ||','||rpad(substr(nvl(p_input_4,'0'),1,1),1)
685 ||','||rpad(substr(nvl(p_input_5,' '),1,6),6)
686 ||','||rpad(substr(nvl(p_input_6,' '),1,2),2)
687 ||','||rpad(substr(nvl(p_input_7,' '),1,57),57)
688 ||','||rpad(substr(nvl(p_input_8,' '),1,22),22)
689 ||','||rpad(substr(nvl(p_input_9,' '),1,22),22)
690 ||','||rpad(substr(nvl(p_input_10,' '),1,22),22)
691 ||','||rpad(substr(nvl(p_input_11,' '),1,2),2)
692 ||','||rpad(substr(nvl(p_input_12,' '),1,5),5)
693 ||','||rpad(substr(nvl(p_input_13,' '),1,4),4)
694 ||','||lpad(' ',5)
695 ||','||rpad(substr(nvl(p_input_14,' '),1,23),23)
696 ||','||rpad(substr(nvl(p_input_15,' '),1,15),15)
697 ||','||rpad(substr(nvl(p_input_16,' '),1,2),2)
698 ||','||rpad(substr(upper(p_input_17),1,57),57)
699 ||','||rpad(substr(nvl(p_input_18,' '),1,22),22)
700 ||','||rpad(substr(nvl(p_input_19,' '),1,22),22)
701 ||','||rpad(substr(nvl(p_input_20,' '),1,22),22)
702 ||','||rpad(substr(nvl(p_input_21,' '),1,2),2)
703 ||','||rpad(substr(nvl(p_input_22,' '),1,5),5)
704 ||','||rpad(substr(nvl(p_input_23,' '),1,4),4)
705 ||','||lpad(' ',5)
706 ||','||rpad(substr(nvl(p_input_24,' '),1,23),23)
707 ||','||rpad(substr(nvl(p_input_25,' '),1,15),15)
708 ||','||rpad(substr(nvl(p_input_26,' '),1,2),2)
709 ||','||rpad(substr(nvl(p_input_27,' '),1,27),27)
710 ||','||rpad(substr(nvl(p_input_28,' '),1,15),15)
711 ||','||rpad(substr(nvl(p_input_29,' '),1,5),5)
712 ||','||lpad(' ',3)
713 ||','||rpad(substr(nvl(p_input_30,' '),1,40),40)
714 ||','||lpad(' ',3)
715 ||','||rpad(substr(nvl(p_input_31,' '),1,10),10)
716 ||','||p_input_32
717 ||','||p_input_33
718 ||','||lpad(' ',12);
719 hr_utility.trace('---------------------CSV----------------------------');
720 hr_utility.trace(return_value);
721 END IF;
722 p_error := l_exclude_from_output_chk;
723 ret_str_len:=length(return_value);
724 return return_value;
725 END format_SQWL_RA_record; -- End of formatting SQWL RA Record
726 --
727 -- Formatting RE record for SQWL reporting
728 --
729 /* Record Identifier --> p_input_1,
730 Tax Year --> p_input_2,
731 Agent Indicator Code --> p_input_3,
732 Employer / Agent Employer Identification Number (EIN) -->p_input_4,
733 Agent For EIN --> p_input_5,
734 Terminating Business Indicator --> p_input_6,
735 Establishment Number --> p_input_7,
736 Other EIN --> p_input_8,
737 Employer Name --> p_input_9,
738 Location Address --> p_input_10,
739 Delivery Address --> p_input_11,
740 City --> p_input_12,
741 State Abbreviation --> p_input_13,
742 Zip Code --> p_input_14,
743 Zip Code Extension --> p_input_15,
744 Blank,
745 Foreign State / Provinc --> p_input_16
746 Foreign Postal Code --> p_input_17,
747 Country Code --> p_input_18,
748 Employment Code --> p_input_19,
749 Tax Jurisdiction Code --> p_input_20,
750 Third Party Sick Pay Indicator --> p_input_21,
751 Texas Specific Fields :
752 TWC Account Number(SUI ER Acct)--> p_input_30,
753 Qtr and Yr MMYYYY --> p_input_31,
754 Tax Rate Decimal Point --> p_input_32,
755 NAICS Code --> p_input_33
756 */
757 --
758 FUNCTION format_SQWL_RE_record(
759 p_effective_date IN varchar2,
760 p_report_type IN varchar2,
761 p_format IN varchar2,
762 p_report_qualifier IN varchar2,
763 p_record_name IN varchar2,
764 p_input_1 IN varchar2,
765 p_input_2 IN varchar2,
766 p_input_3 IN varchar2,
767 p_input_4 IN varchar2,
768 p_input_5 IN varchar2,
769 p_input_6 IN varchar2,
770 p_input_7 IN varchar2,
771 p_input_8 IN varchar2,
772 p_input_9 IN varchar2,
773 p_input_10 IN varchar2,
774 p_input_11 IN varchar2,
775 p_input_12 IN varchar2,
776 p_input_13 IN varchar2,
777 p_input_14 IN varchar2,
778 p_input_15 IN varchar2,
779 p_input_16 IN varchar2,
780 p_input_17 IN varchar2,
781 p_input_18 IN varchar2,
782 p_input_19 IN varchar2,
783 p_input_20 IN varchar2,
784 p_input_21 IN varchar2,
785 p_input_22 IN varchar2,
786 p_input_23 IN varchar2,
787 p_input_24 IN varchar2,
788 p_input_25 IN varchar2,
789 p_input_26 IN varchar2,
790 p_input_27 IN varchar2,
791 p_input_28 IN varchar2,
792 p_input_29 IN varchar2,
793 p_input_30 IN varchar2,
794 p_input_31 IN varchar2,
795 p_input_32 IN varchar2,
796 p_input_33 IN varchar2,
797 p_input_34 IN varchar2,
798 p_input_35 IN varchar2,
799 p_input_36 IN varchar2,
800 p_input_37 IN varchar2,
801 p_input_38 IN varchar2,
802 p_input_39 IN varchar2,
803 p_input_40 IN varchar2,
804 p_validate IN varchar2,
805 p_exclude_from_output OUT nocopy varchar2,
806 sp_out_1 OUT nocopy varchar2,
807 sp_out_2 OUT nocopy varchar2,
808 sp_out_3 OUT nocopy varchar2,
809 sp_out_4 OUT nocopy varchar2,
810 sp_out_5 OUT nocopy varchar2,
811 ret_str_len OUT nocopy number,
812 p_error OUT nocopy boolean
813 ) RETURN VARCHAR2
814 IS
815 l_agent_indicator varchar2(1);
816 l_emp_ein varchar2(100);
817 l_agent_ein varchar2(100);
818 l_term_indicator varchar2(1);
819 l_other_ein varchar2(100);
820 l_exclude_from_output_chk boolean;
821 l_input_8 varchar2(50);
822 l_bus_tax_acct_number varchar2(50);
823 l_rep_qtr varchar2(300);
824 l_rep_prd varchar2(300);
825 l_end_of_rec varchar2(20);
826 return_value varchar2(32767);
827
828 r_input_1 varchar2(300);
829 r_input_2 varchar2(300);
830 r_input_3 varchar2(300);
831 r_input_4 varchar2(300);
832 r_input_5 varchar2(300);
833 r_input_6 varchar2(300);
834 r_input_7 varchar2(300);
835 r_input_8 varchar2(300);
836 r_input_9 varchar2(300);
837 r_input_10 varchar2(300);
838 r_input_11 varchar2(300);
839 r_input_12 varchar2(300);
840 r_input_13 varchar2(300);
841 r_input_14 varchar2(300);
842 r_input_15 varchar2(300);
843 r_input_16 varchar2(300);
844 r_input_17 varchar2(300);
845 r_input_18 varchar2(300);
846 r_input_19 varchar2(300);
847 r_input_20 varchar2(300);
848 r_input_21 varchar2(300);
849 r_input_22 varchar2(300);
850 r_input_23 varchar2(300);
851 r_input_24 varchar2(300);
852 r_input_25 varchar2(300);
853 r_input_26 varchar2(300);
854 r_input_27 varchar2(300);
855 r_input_28 varchar2(300);
856 r_input_29 varchar2(300);
857 r_input_30 varchar2(300);
858 r_input_31 varchar2(300);
859 r_input_32 varchar2(300);
860 r_input_33 varchar2(300);
861 r_input_34 varchar2(300);
862 r_input_35 varchar2(300);
863 r_input_36 varchar2(300);
864 r_input_37 varchar2(300);
865 r_input_38 varchar2(300);
866 r_input_39 varchar2(300);
867
868 p_end_of_rec varchar2(20);
869 BEGIN
870 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
871 hr_utility.trace('RE Record Formatting started ');
872 -- Initializing local variables with parameter value
873 --{
874 r_input_2 := p_input_2;
875 r_input_3 := p_input_3;
876 r_input_4 := p_input_4;
877 r_input_5 := p_input_5;
878 r_input_6 := p_input_6;
879 r_input_7 := p_input_7;
880 r_input_8 := p_input_8;
881 r_input_9 := p_input_9;
882 r_input_10 := p_input_10;
883 r_input_11 := p_input_11;
884 r_input_12 := p_input_12;
885 r_input_13 := p_input_13;
886 r_input_14 := p_input_14;
887 r_input_15 := p_input_15;
888 r_input_16 := p_input_16;
889 r_input_17 := p_input_17;
890 r_input_18 := p_input_18;
891 r_input_19 := p_input_19;
892 r_input_20 := p_input_20;
893 r_input_21 := p_input_21;
894 r_input_22 := p_input_22;
895 r_input_23 := p_input_23;
896 r_input_24 := p_input_24;
897 r_input_25 := p_input_25;
898 r_input_26 := p_input_26;
899 r_input_27 := p_input_27;
900 r_input_28 := p_input_28;
901 r_input_29 := p_input_29;
902 r_input_30 := p_input_30;
903 r_input_31 := p_input_31;
904 r_input_32 := p_input_32;
905 r_input_33 := p_input_33;
906 r_input_34 := p_input_34;
907 r_input_35 := p_input_35;
908 r_input_36 := p_input_36;
909 r_input_37 := p_input_37;
910 r_input_38 := p_input_38;
911 r_input_39 := p_input_39;
912 --}
913
914 IF p_record_name = 'RE' THEN -- p_record_name
915 --{
916 /* Check for agent indicator code p_input_3 */
917 IF p_input_3 = 'Y' THEN
918 hr_utility.trace('agent indicator is Y');
919 l_agent_indicator := '1';
920 l_emp_ein := p_input_5;
921 l_agent_ein := p_input_4;
922 ELSE
923 l_agent_indicator:=lpad(' ','1');
924 hr_utility.trace('agent indicator is not 1');
925 l_emp_ein := p_input_4;
926 l_agent_ein := lpad(' ',9);
927 END IF;
928
929 IF p_input_6 = 'Y' THEN
930 l_term_indicator:='1';
931 ELSE
932 l_term_indicator:='0';
933 END IF;
934
935 IF ((p_report_qualifier = 'LA_SQWL') OR /*Added SQWL. */
936 (p_report_qualifier = 'MD_SQWL') OR
937 (p_report_qualifier = 'NH_SQWL') OR
938 (p_report_qualifier = 'SC_SQWL') OR
939 (p_report_qualifier = 'OR_SQWL') OR
940 (p_report_qualifier = 'CA_SQWL') OR
941 (p_report_qualifier = 'NY_SQWL') OR
942 (p_report_qualifier = 'NC_SQWL') OR
943 (p_report_qualifier = 'FL_SQWL') OR
944 (p_report_qualifier = 'NE_SQWL') OR /*Bug 5189831*/
945 (p_report_qualifier = 'KS_SQWL') OR /*Bug 6036926*/
946 (p_report_qualifier = 'UT_SQWL') OR /*Bug 9913439 */
947 (p_report_qualifier = 'AL_SQWL')) THEN /* Bug 4552131 */
948 --{
949 l_agent_indicator := lpad(' ',1);
950 l_other_ein := lpad(' ',9);
951 l_agent_ein := lpad(' ',9);
952 l_term_indicator := lpad(' ',1);
953 l_emp_ein := p_input_4;
954 r_input_7 := lpad(' ',4);
955 r_input_8 := l_other_ein;
956 --}
957 END IF;
958
959 IF ((p_report_qualifier = 'MD_SQWL') OR /*Added SQWL. */
960 (p_report_qualifier = 'OR_SQWL') OR
961 (p_report_qualifier = 'NH_SQWL') OR
962 (p_report_qualifier = 'NC_SQWL') OR
963 (p_report_qualifier = 'SC_SQWL') OR
964 (p_report_qualifier = 'CA_SQWL')) THEN
965 --{
966 l_emp_ein := lpad(' ',9);
967 --}
968 END IF;
969
970 /* Pos:3-6 blank for OR_SQWL */
971
972 IF (p_report_qualifier = 'OR_SQWL' OR
973 p_report_qualifier = 'CA_SQWL' OR
974 p_report_qualifier = 'NY_SQWL' OR
975 p_report_qualifier = 'NC_SQWL' OR
976 p_report_qualifier = 'KS_SQWL' OR /* 6036926 */
977 p_report_qualifier = 'VA_SQWL' OR /* Bug# 13377871 */
978 p_report_qualifier = 'FL_SQWL') THEN
979 --{
980 r_input_2 := lpad(' ',4);
981 --}
982 END IF;
983
984 /* OH RITA Other EIN. */
985 If (p_report_qualifier = 'NH_SQWL') THEN
986
987 r_input_8 := p_input_8;
988
989 END IF;
990
991 IF p_input_40 = 'FLAT' THEN
992 --{
993 -- Validation for RE Record starts
994 -- These validation are used only for mf file only.
995 -- not for any of the audit report
996 --
997 hr_utility.trace('before data_validation of ein');
998 l_emp_ein :=
999 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1000 p_report_type,
1001 p_format,
1002 p_report_qualifier,
1003 p_record_name,
1004 'EIN',
1005 l_emp_ein,
1006 'Employer EIN',
1007 p_input_9,
1008 null,
1009 p_validate,
1010 p_exclude_from_output,
1011 sp_out_1,
1012 sp_out_2);
1013 IF p_exclude_from_output = 'Y' THEN
1014 l_exclude_from_output_chk := TRUE;
1015 END IF;
1016
1017 IF l_agent_indicator = '1' THEN
1018 --{
1019 l_agent_ein :=
1020 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1021 p_report_type,
1022 p_format,
1023 p_report_qualifier,
1024 p_record_name,
1025 'EIN',
1026 l_agent_ein,
1027 'Agent EIN',
1028 p_input_9,
1029 null,
1030 p_validate,
1031 p_exclude_from_output,
1032 sp_out_1,
1033 sp_out_2);
1034 IF p_exclude_from_output = 'Y' THEN
1035 l_exclude_from_output_chk := TRUE;
1036 END IF;
1037 --}
1038 END IF; -- agent_indicator
1039
1040 hr_utility.trace('after data_validation of EIN');
1041 l_input_8 := replace(r_input_8,' ');
1042
1043 IF l_input_8 IS NOT NULL THEN --Checking Other EIN for validation
1044 --{
1045 hr_utility.trace('before data_validation of other EIN');
1046 l_other_ein:=
1047 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1048 p_report_type,
1049 p_format,
1050 p_report_qualifier,
1051 p_record_name,
1052 'EIN',
1053 p_input_8,
1054 'Other EIN',
1055 p_input_9,
1056 null,
1057 p_validate,
1058 p_exclude_from_output,
1059 sp_out_1,
1060 sp_out_2
1061 );
1062 hr_utility.trace('after data_validation of Other EIN');
1063 IF p_exclude_from_output = 'Y' THEN
1064 l_exclude_from_output_chk := TRUE;
1065 END IF;
1066 --}
1067 ELSE
1068 l_other_ein:= lpad(' ',9);
1069 END IF; --Checking Other EIN for validation
1070 --
1071 -- Validation for RE record ends here
1072
1073 -- Formatting for mf file
1074 --
1075 IF ( p_report_qualifier = 'TX_SQWL' ) THEN /* Bug# 4559359 */
1076 r_input_7 := lpad(' ', 4) ;
1077 r_input_16 := lpad(' ', 23) ;
1078 r_input_17 := lpad(' ', 15) ;
1079 r_input_18 := lpad(' ', 2) ;
1080 r_input_19 := lpad(' ', 1) ;
1081 r_input_20 := lpad(' ', 1) ;
1082 r_input_21 := lpad(' ', 1) ;
1083 END IF ;
1084
1085 IF p_report_qualifier = 'NY_SQWL' THEN
1086 l_agent_ein := lpad(' ',7);
1087 r_input_15 := lpad(' ',4);
1088 r_input_19 := lpad(' ',1);
1089 END IF;
1090
1091 /* Pos:40 - 173 Blank for MD,OR SQWL. */
1092
1093 IF ((p_report_qualifier = 'MD_SQWL') OR
1094 (p_report_qualifier = 'OR_SQWL') OR
1095 (p_report_qualifier = 'NE_SQWL') /*Bug 5189831*/
1096 ) THEN
1097 --{
1098 r_input_9 := lpad(' ',57);
1099 r_input_10 := lpad(' ',22);
1100 r_input_11 := lpad(' ',22);
1101 r_input_12 := lpad(' ',22);
1102 r_input_13 := lpad(' ',2);
1103 r_input_14 := lpad(' ',5);
1104 r_input_15 := lpad(' ',4);
1105 --}
1106 END IF;
1107
1108 /* Pos:97-108 Location address blank for NC SQWL. */
1109
1110 IF p_report_qualifier = 'NC_SQWL' OR
1111 p_report_qualifier = 'KS_SQWL'THEN /* Bug 6036926 */
1112 r_input_10 := lpad(' ',22);
1113 END IF;
1114
1115 IF ((p_report_qualifier = 'FL_SQWL') OR
1116 (p_report_qualifier = 'VA_SQWL')) THEN
1117 r_input_10 := lpad(' ',22);
1118 END IF;
1119
1120 /* Added for Bug#16100022 */
1121 IF ((p_report_qualifier = 'LA_SQWL') AND
1122 (r_input_10 = lpad(' ', 22))) THEN
1123 r_input_10 := rpad('X', 22, ' ');
1124 END IF;
1125
1126 /* MD_SQWL Pos:3-6 Tax year blank and Pos:163-164 not blank. */
1127 IF p_report_qualifier = 'MD_SQWL' THEN
1128 r_input_2 := lpad(' ',4);
1129 r_input_13 := rpad(substr(nvl(p_input_13,' '),1,2),2);
1130 END IF;
1131
1132 /* Pos:163-164 not blank. */
1133 IF p_report_qualifier = 'AL_SQWL' THEN
1134 r_input_13 := 'AL';
1135 END IF;
1136 /* Pos:163-173 blank for 'KS' Bug 6036926 */
1137 IF (p_report_qualifier = 'KS_SQWL') THEN
1138 /* r_input_13 := lpad(' ',2);
1139 r_input_14 := lpad(' ',5); */ /*6277029*/
1140 r_input_15 := lpad(' ',4);
1141 END IF;
1142
1143 /* foreign adrs,emp_code, third_party,tax_jurisdiction formatting */
1144 IF ((p_report_qualifier = 'MD_SQWL') OR
1145 (p_report_qualifier = 'LA_SQWL') OR
1146 (p_report_qualifier = 'SC_SQWL') OR
1147 (p_report_qualifier = 'OR_SQWL') OR
1148 (p_report_qualifier = 'CA_SQWL') OR
1149 (p_report_qualifier = 'NY_SQWL') OR
1150 (p_report_qualifier = 'FL_SQWL') OR
1151 (p_report_qualifier = 'NE_SQWL') OR /* Bug# 5189831*/
1152 (p_report_qualifier = 'KS_SQWL') OR /* Bug# 6036926*/
1153 (p_report_qualifier = 'UT_SQWL') OR /* Bug# 9913439 */
1154 (p_report_qualifier = 'VA_SQWL')) THEN
1155 --{
1156 r_input_16 := lpad(' ',23);
1157 r_input_17 := lpad(' ',15);
1158 r_input_18 := lpad(' ',2);
1159 r_input_20 := lpad(' ',1);
1160 r_input_21 := lpad(' ',1);
1161
1162 IF ((p_report_qualifier = 'MD_SQWL') OR
1163 (p_report_qualifier = 'LA_SQWL') OR
1164 /* (p_report_qualifier = 'SC_SQWL') OR */ -- Commented for Bug# 14086658
1165 (p_report_qualifier = 'OR_SQWL') OR
1166 (p_report_qualifier = 'NE_SQWL') OR /* Bug# 5189831*/
1167 (p_report_qualifier = 'KS_SQWL') OR /* Bug# 6036926*/
1168 (p_report_qualifier = 'UT_SQWL') OR /* Bug# 9913439 */
1169 (p_report_qualifier = 'VA_SQWL') OR /* Bug# 13377871 */
1170 (p_report_qualifier = 'CA_SQWL')) THEN
1171
1172 r_input_19 := lpad(' ',1);
1173
1174 END IF;-- AL,PA emp_code blank
1175 --}
1176 END IF;
1177
1178 /* Pos:217-218 Country code blank for NH_SQWL. */
1179 IF p_report_qualifier = 'NH_SQWL' OR
1180 p_report_qualifier = 'KS_SQWL' OR /* Bug# 6036926*/
1181 p_report_qualifier = 'UT_SQWL' THEN /* Bug# 9913439 */
1182 r_input_18 := lpad(' ',2);
1183 END IF;
1184
1185 /* Pos:219 - 221 blank for NH and NC SQWL */
1186 IF ((p_report_qualifier = 'NH_SQWL') OR
1187 (p_report_qualifier = 'NV_SQWL') OR
1188 (p_report_qualifier = 'NC_SQWL') OR
1189 (p_report_qualifier = 'KS_SQWL') OR /* Bug# 6036926 */
1190 (p_report_qualifier = 'UT_SQWL') OR /* Bug# 9913439 */
1191 (p_report_qualifier = 'AL_SQWL')) THEN /* Bug# 4552131 */
1192 --{
1193 r_input_19 := lpad(' ',1);
1194 r_input_20 := lpad(' ',1);
1195 r_input_21 := lpad(' ',1);
1196 --}
1197 END IF;
1198
1199 IF p_report_qualifier = 'LA_SQWL' THEN
1200 --{
1201 r_input_21 := p_input_21;
1202 r_input_22 := rpad(nvl(r_input_22,' '),2);
1203 r_input_23 := rpad(nvl(r_input_23,' '),6);
1204 r_input_24 := lpad(' ',2);
1205 /*Bug: 2324869 */
1206 /* Commented for Bug# 13998882 */
1207 /* r_input_25 := rpad(substr(replace(
1208 pay_us_reporting_utils_pkg.character_check(nvl(r_input_25,
1209 ' ')),'-'),1,6),12,0); */
1210
1211 /* Added for Bug# 13998882 */
1212 r_input_25 := rpad(substr(replace(
1213 pay_us_reporting_utils_pkg.character_check(nvl(r_input_25,
1214 ' ')),'-'),1,7),12, ' ');
1215
1216 /*Pos:250 - 253 Not supported. Zero Fill. */
1217 r_input_26 := '0';
1218 r_input_27 := '0';
1219 r_input_28 := '0';
1220 r_input_29 := '0';
1221 --}
1222 ELSIF p_report_qualifier = 'UT_SQWL' THEN
1223 r_input_22 := lpad(substr(p_input_23,1,2),2);
1224 r_input_25 := lpad(replace(replace(nvl(replace(p_input_25,' ')
1225 ,' '),'-'),'/'),10);
1226 r_input_23 := lpad(' ',6);
1227 r_input_24 := lpad(' ',2);
1228 --r_input_25 := lpad(' ',12);
1229 r_input_26 := lpad(' ',1);
1230 r_input_27 := lpad(' ',1);
1231 r_input_28 := lpad(' ',1);
1232 r_input_29 := lpad(' ',1);
1233 ELSE
1234 --{
1235 r_input_22 := lpad(' ',2);
1236 r_input_23 := lpad(' ',6);
1237 r_input_24 := lpad(' ',2);
1238 r_input_25 := lpad(' ',12);
1239 r_input_26 := lpad(' ',1);
1240 r_input_27 := lpad(' ',1);
1241 r_input_28 := lpad(' ',1);
1242 r_input_29 := lpad(' ',1);
1243
1244 IF p_report_qualifier = 'NY_SQWL' THEN
1245 IF substr(p_input_23,1,2) = '03' THEN
1246 l_rep_prd := 1;
1247 ELSIF substr(p_input_23,1,2) = '06' THEN
1248 l_rep_prd := 2;
1249 ELSIF substr(p_input_23,1,2) = '09' THEN
1250 l_rep_prd := 3;
1251 ELSIF substr(p_input_23,1,2) = '12' THEN
1252 l_rep_prd := 4;
1253 END IF;
1254 l_rep_qtr := rpad(l_rep_prd,1) ||rpad(nvl(substr(p_input_23,3,4),' '),4);
1255 ELSE
1256 l_rep_qtr := ' ';
1257 l_rep_prd := ' ';
1258 END IF;
1259 --}
1260 END IF;
1261
1262 IF (p_report_qualifier = 'VA_SQWL') THEN
1263 l_term_indicator := lpad(' ',1);
1264 l_agent_indicator := lpad(' ',1); /* Bug# 13377871 */
1265 r_input_11 := lpad(' ',22); /* Bug# 13377871 */
1266 r_input_12 := lpad(' ',22); /* Bug# 13377871 */
1267 r_input_13 := lpad(' ',2); /* Bug# 13377871 */
1268 r_input_14 := lpad(' ',5); /* Bug# 13377871 */
1269 r_input_15 := lpad(' ',4); /* Bug# 13377871 */
1270 END IF;
1271 /* Bug 3527986 */
1272 /*
1273 IF ((p_report_qualifier = 'FL_SQWL') OR
1274 (p_report_qualifier = 'NY_SQWL')) THEN
1275 l_end_of_rec := p_end_of_rec;
1276 END IF;
1277 */
1278
1279 IF (p_report_qualifier = 'FL_SQWL') THEN
1280 l_end_of_rec := p_end_of_rec;
1281 END IF;
1282
1283 IF ( p_report_qualifier = 'TX_SQWL' ) THEN /* Bug# 4559359 */
1284 r_input_29 := r_input_29 ||
1285 lpad(' ', 46) ||
1286 rpad(substr(nvl(r_input_30,' '),1,9),9) ||
1287 rpad(substr(nvl(r_input_31,' '),1,6),6) ||
1288 rpad(' ',1) ||
1289 lpad(substr(nvl(r_input_32,'0'),1,5),5,'0') ||
1290 rpad(' ',1) ||
1291 lpad(' ',6) ; /* NAICS Code */
1292 END IF ;
1293
1294 r_input_30 := lpad(' ',8);
1295
1296
1297 IF p_report_qualifier = 'NY_SQWL' THEN
1298 --{
1299 return_value := 'RE'
1300 ||rpad(substr(nvl(r_input_2,' '),1,4),4)
1301 ||l_agent_indicator
1302 ||l_emp_ein||l_agent_ein
1303 ||l_term_indicator
1304 ||rpad(substr(nvl(r_input_7,' '),1,4),4)
1305 ||l_other_ein
1306 ||rpad(substr(nvl(upper(r_input_9),' '),1,79),79)
1307 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
1308 ||rpad(substr(nvl(r_input_12,' '),1,22),22)
1309 ||rpad(substr(nvl(r_input_13,' '),1,2),2)
1310 ||rpad(substr(nvl(r_input_14,' '),1,5),5)
1311 ||rpad(substr(nvl(r_input_15,' '),1,4),4)
1312 ||rpad(substr(nvl(l_rep_qtr,' '),1,5),5)
1313 ||rpad(substr(nvl(r_input_16,' '),1,23),23)
1314 ||rpad(substr(nvl(r_input_17,' '),1,15),15)
1315 ||rpad(substr(nvl(r_input_18,' '),1,2),2)
1316 ||rpad(nvl(r_input_19,' '),1)
1317 ||rpad(nvl(r_input_20,' '),1)
1318 ||rpad(nvl(r_input_21,' '),1)
1319 ||r_input_22
1320 ||r_input_23
1321 ||rpad(r_input_24,5)
1322 ||rpad(r_input_25,15)
1323 ||r_input_26
1324 ||r_input_27
1325 ||r_input_28
1326 ||rpad(r_input_29,252)
1327 ||r_input_30
1328 ||l_end_of_rec;
1329 --}
1330
1331 ELSIF p_report_qualifier = 'UT_SQWL' THEN
1332 --{
1333 return_value := 'RE'
1334 ||rpad(substr(nvl(r_input_2,' '),1,4),4)
1335 ||l_agent_indicator
1336 ||l_emp_ein||l_agent_ein
1337 ||l_term_indicator
1338 ||rpad(substr(nvl(r_input_7,' '),1,4),4)
1339 ||l_other_ein
1340 ||rpad(substr(nvl(upper(r_input_9),' '),1,57),57)
1341 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
1342 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
1343 ||rpad(substr(nvl(r_input_12,' '),1,22),22)
1344 ||rpad(substr(nvl(r_input_13,' '),1,2),2)
1345 ||rpad(substr(nvl(r_input_14,' '),1,5),5)
1346 ||rpad(substr(nvl(r_input_15,' '),1,4),4)
1347 ||rpad(substr(nvl(l_rep_qtr,' '),1,5),5)
1348 ||rpad(substr(nvl(r_input_16,' '),1,23),23)
1349 ||rpad(substr(nvl(r_input_17,' '),1,15),15)
1350 ||rpad(substr(nvl(r_input_18,' '),1,2),2)
1351 ||rpad(nvl(r_input_19,' '),1)
1352 ||rpad(nvl(r_input_20,' '),1)
1353 ||rpad(nvl(r_input_21,' '),1)
1354 ||r_input_22
1355 ||rpad(r_input_25,15)
1356 ||r_input_23
1357 ||rpad(r_input_24,5)
1358 --||rpad(r_input_25,15)
1359 ||r_input_26
1360 ||r_input_27
1361 ||r_input_28
1362 ||rpad(r_input_29,252)
1363 ||r_input_30
1364 ||l_end_of_rec;
1365 --}
1366
1367 ELSE
1368 --{
1369 return_value := 'RE'
1370 ||rpad(substr(nvl(r_input_2,' '),1,4),4)
1371 ||l_agent_indicator
1372 ||l_emp_ein||l_agent_ein
1373 ||l_term_indicator
1374 ||rpad(substr(nvl(r_input_7,' '),1,4),4)
1375 ||l_other_ein
1376 ||rpad(substr(nvl(upper(r_input_9),' '),1,57),57)
1377 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
1378 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
1379 ||rpad(substr(nvl(r_input_12,' '),1,22),22)
1380 ||rpad(substr(nvl(r_input_13,' '),1,2),2)
1381 ||rpad(substr(nvl(r_input_14,' '),1,5),5)
1382 ||rpad(substr(nvl(r_input_15,' '),1,4),9)
1383 ||rpad(substr(nvl(r_input_16,' '),1,23),23)
1384 ||rpad(substr(nvl(r_input_17,' '),1,15),15)
1385 ||rpad(substr(nvl(r_input_18,' '),1,2),2)
1386 ||rpad(nvl(r_input_19,'R'),1)
1387 ||rpad(nvl(r_input_20,' '),1)
1388 ||rpad(nvl(r_input_21,'0'),1)
1389 ||r_input_22
1390 ||r_input_23
1391 ||rpad(r_input_24,5)
1392 ||rpad(r_input_25,15)
1393 ||r_input_26
1394 ||r_input_27
1395 ||r_input_28
1396 ||rpad(r_input_29,252)
1397 ||r_input_30
1398 ||l_end_of_rec;
1399 --}
1400 END IF;
1401 ret_str_len:=length(return_value);
1402 --}
1403 ELSIF p_input_40 = 'CSV' THEN
1404 --{
1405 return_value:= 'RE'
1406 ||','||rpad(substr(p_input_2,1,4),4)
1407 ||','||l_agent_indicator
1408 ||','||l_emp_ein
1409 ||','||l_agent_ein
1410 ||','||l_term_indicator
1411 ||','||rpad(substr(nvl(p_input_7,' '),1,4),4)
1412 ||','||p_input_8
1413 ||','||rpad(substr(nvl(upper(p_input_9),' '),1,57),57)
1414 ||','||rpad(substr(nvl(p_input_10,' '),1,22),22)
1415 ||','||rpad(substr(nvl(p_input_11,' '),1,22),22)
1416 ||','||rpad(substr(nvl(p_input_12,' '),1,22),22)
1417 ||','||rpad(substr(nvl(p_input_13,' '),1,2),2)
1418 ||','||rpad(substr(nvl(p_input_14,' '),1,5),5)
1419 ||','||rpad(substr(nvl(p_input_15,' '),1,4),4)
1420 ||','||lpad(' ',5)
1421 ||','||rpad(substr(nvl(p_input_16,' '),1,23),23)
1422 ||','||rpad(substr(nvl(p_input_17,' '),1,15),15)
1423 ||','||rpad(substr(nvl(p_input_18,' '),1,2),2)
1424 ||','||rpad(nvl(p_input_19,'R'),1)
1425 ||','||rpad(nvl(p_input_20,' '),1)
1426 ||','||rpad(nvl(p_input_21,'0'),1)
1427 ||','||p_input_22
1428 ||','||p_input_23
1429 ||','||p_input_24
1430 ||','||lpad(' ',3)
1431 ||','||p_input_25
1432 ||','||lpad(' ',3)
1433 ||','||p_input_26
1434 ||','||p_input_27
1435 ||','||p_input_28
1436 ||','||p_input_29
1437 ||','||p_input_30;
1438 --}
1439 END IF;
1440 END IF;
1441 p_error := l_exclude_from_output_chk;
1442 ret_str_len:=length(return_value);
1443 return return_value;
1444 END format_SQWL_RE_record; -- End of Function Formatting RE record
1445
1446 /* ------------ Parameter mapping for SQWL RS Record -------------
1447 --{
1448 Record Identifier --> p_input_1
1449 State Code --> p_input_2
1450 Taxing Entity Code --> p_input_3
1451 Social Security Number (SSN) --> p_input_4
1452 Employee First Name --> p_input_5
1453 Employee Middle Name or Initial --> p_input_6
1454 Employee Last Name --> p_input_7
1455 Suffix --> p_input_8
1456 Location Address --> p_input_0
1457 Delivery Address --> p_input_10
1458 City --> p_input_11
1459 State Abbreviation --> p_input_12
1460 Zip Code --> p_input_13
1461 Zip Code Extension --> p_input_14
1462 Foreign State / Province --> p_input_15
1463 Foreign Postal Code --> p_input_16
1464 Country Code --> p_input_17
1465 Optional Code --> p_input_18
1466 Reporting Period --> p_input_19
1467 State Quarterly Unemployment Insurance Total Wages --> p_input_20
1468 State Quarterly Unemployment Total Taxable Wages --> p_input_21
1469 Number of Weeks Worked --> p_input_22
1470 Date First Employed --> p_input_23
1471 Date of Separation --> p_input_24
1472 State Employer Account Number --> p_input_25
1473 State Code --> p_input_26
1474 State Taxable Wages --> p_input_27
1475 SIT Withheld --> p_input_28
1476 Other State Data --> p_input_29
1477 Tax Type Code --> p_input_30
1478 Local Taxable Wages --> p_input_31
1479 Local Income Tax Withheld --> p_input_32
1480 State Control Number --> p_input_33
1481 Supplemental Data 1 --> p_input_34
1482 Supplemental Data 2 --> p_input_35
1483 --}
1484 */
1485
1486 FUNCTION format_SQWL_RS_record(
1487 p_effective_date IN varchar2,
1488 p_report_type IN varchar2,
1489 p_format IN varchar2,
1490 p_report_qualifier IN varchar2,
1491 p_record_name IN varchar2,
1492 p_input_1 IN varchar2,
1493 p_input_2 IN varchar2,
1494 p_input_3 IN varchar2,
1495 p_input_4 IN varchar2,
1496 p_input_5 IN varchar2,
1497 p_input_6 IN varchar2,
1498 p_input_7 IN varchar2,
1499 p_input_8 IN varchar2,
1500 p_input_9 IN varchar2,
1501 p_input_10 IN varchar2,
1502 p_input_11 IN varchar2,
1503 p_input_12 IN varchar2,
1504 p_input_13 IN varchar2,
1505 p_input_14 IN varchar2,
1506 p_input_15 IN varchar2,
1507 p_input_16 IN varchar2,
1508 p_input_17 IN varchar2,
1509 p_input_18 IN varchar2,
1510 p_input_19 IN varchar2,
1511 p_input_20 IN varchar2,
1512 p_input_21 IN varchar2,
1513 p_input_22 IN varchar2,
1514 p_input_23 IN varchar2,
1515 p_input_24 IN varchar2,
1516 p_input_25 IN varchar2,
1517 p_input_26 IN varchar2,
1518 p_input_27 IN varchar2,
1519 p_input_28 IN varchar2,
1520 p_input_29 IN varchar2,
1521 p_input_30 IN varchar2,
1522 p_input_31 IN varchar2,
1523 p_input_32 IN varchar2,
1524 p_input_33 IN varchar2,
1525 p_input_34 IN varchar2,
1526 p_input_35 IN varchar2,
1527 p_input_36 IN varchar2,
1528 p_input_37 IN varchar2,
1529 p_input_38 IN varchar2,
1530 p_input_39 IN varchar2,
1531 p_input_40 IN varchar2,
1532 p_validate IN varchar2,
1533 p_exclude_from_output OUT nocopy varchar2,
1534 sp_out_1 OUT nocopy varchar2,
1535 sp_out_2 OUT nocopy varchar2,
1536 sp_out_3 OUT nocopy varchar2,
1537 sp_out_4 OUT nocopy varchar2,
1538 sp_out_5 OUT nocopy varchar2,
1539 ret_str_len OUT nocopy number,
1540 p_error OUT nocopy boolean
1541 ) RETURN VARCHAR2
1542 IS
1543 return_value varchar2(32767);
1544 l_s_hyphen_position number := 0;
1545 l_carate_1_position number := 0 ;
1546 l_pblm_code varchar2(1);
1547 l_preparer_code varchar2(1);
1548 l_input_1 varchar2(100);
1549 l_records number(10);
1550 l_input_2 varchar2(100);
1551 l_record_identifier varchar2(2);
1552 l_tax_year date;
1553 l_agent_indicator varchar2(1);
1554 l_emp_ein varchar2(100);
1555 l_term_indicator varchar2(1);
1556 l_agent_ein varchar2(100);
1557 l_other_ein varchar2(100);
1558 l_input_8 varchar2(50);
1559 l_check varchar2(1);
1560 l_employment_code varchar2(1);
1561 p_exc varchar2(10);
1562 main_return_string varchar2(300);
1563 l_resub_tlcn varchar2(100);
1564 l_pin varchar2(50);
1565 l_ssn varchar2(100);
1566 l_wages_tips varchar2(100);
1567 l_full_name varchar2(100);
1568 l_emp_name_or_number varchar2(50);
1569 l_emp_number varchar2(50);
1570 l_first_name varchar2(150);
1571 l_middle_name varchar2(100);
1572 l_last_name varchar2(150);
1573 l_suffix varchar2(100);
1574 l_err boolean;
1575 l_exclude_from_output_chk boolean;
1576 l_message varchar2(2000);
1577 l_ss_tax_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
1578 l_description varchar2(50);
1579 l_field_description varchar2(50);
1580 l_ss_wage_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
1581 l_ss_count number(10);
1582 l_amount number(10);
1583 l_tax_ct_job_dev varchar2(30);
1584 l_tax_ct_ind_revit varchar2(30);
1585 l_tax_ct_ind_dev varchar2(30);
1586 l_tax_ct_rural varchar2(30);
1587 l_fit_wh varchar2(30);
1588 l_total_records varchar2(50);
1589 l_wages varchar2(100);
1590 l_taxes varchar2(100);
1591 l_deferred_comp varchar2(100);
1592 l_sdi_wh varchar2(100);
1593 l_state_length number(10);
1594 l_unemp_insurance varchar2(100);
1595 l_fica_mcr_wh varchar2(100);
1596 l_bus_tax_acct_number varchar2(50);
1597 l_w2_govt_ee_contrib varchar2(100);
1598 l_w2_fed_wages varchar2(100);
1599 l_wa_sqwl_outstring varchar2(200);
1600 l_hours_worked number(10);
1601 l_end_of_rec varchar2(20);
1602 p_end_of_rec varchar2(20);
1603 /* PuertoRico W2 related variables Bug # 2736928 */
1604 l_contact_person_phone_no varchar2(100); -- mapped to r_input_34
1605 l_pension_annuity varchar2(100); -- mapped to r_input_35
1606 l_contribution_plan varchar2(100); -- mapped to r_input_36
1607 l_cost_reimbursement varchar2(100); -- mapped to r_input_37
1608 l_uncollected_ss_tax_on_tips varchar2(100); -- mapped to r_input_31
1609 l_uncollected_med_tax_on_tips varchar2(100); -- mapped to r_input_32
1610 l_rt_end_of_rec varchar2(200);
1611
1612 /* Bug 2789523 */
1613 l_last_field varchar2(100);
1614
1615 /* Bug 4170713 */
1616 l_pos_260 varchar2(1) ;
1617 l_pos_261 varchar2(1) ;
1618 l_pos_262 varchar2(1) ;
1619
1620 /* Bug# 1236643 */
1621 l_st_er_acct_no varchar2(300) ;
1622 l_er_unit_no varchar2(300) ;
1623
1624 r_input_1 varchar2(300);
1625 r_input_2 varchar2(300);
1626 r_input_3 varchar2(300);
1627 r_input_4 varchar2(300);
1628 r_input_5 varchar2(300);
1629 r_input_6 varchar2(300);
1630 r_input_7 varchar2(300);
1631 r_input_8 varchar2(300);
1632 r_input_9 varchar2(300);
1633 r_input_10 varchar2(300);
1634 r_input_11 varchar2(300);
1635 r_input_12 varchar2(300);
1636 r_input_13 varchar2(300);
1637 r_input_14 varchar2(300);
1638 r_input_15 varchar2(300);
1639 r_input_16 varchar2(300);
1640 r_input_17 varchar2(300);
1641 r_input_18 varchar2(300);
1642 r_input_19 varchar2(300);
1643 r_input_20 varchar2(300);
1644 r_input_21 varchar2(300);
1645 r_input_22 varchar2(300);
1646 r_input_23 varchar2(300);
1647 r_input_24 varchar2(300);
1648 r_input_25 varchar2(300);
1649 r_input_26 varchar2(300);
1650 r_input_27 varchar2(300);
1651 r_input_28 varchar2(300);
1652 r_input_29 varchar2(300);
1653 r_input_30 varchar2(300);
1654 r_input_31 varchar2(300);
1655 r_input_32 varchar2(300);
1656 r_input_33 varchar2(300);
1657 r_input_34 varchar2(300);
1658 r_input_35 varchar2(300);
1659 r_input_36 varchar2(300);
1660 r_input_37 varchar2(300);
1661 r_input_38 varchar2(300);
1662 r_input_39 varchar2(300);
1663
1664 BEGIN
1665 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1666 hr_utility.trace('Formatting RS record for SQWL ');
1667 hr_utility.trace('p_report_qualifier = '||p_report_qualifier);
1668 -- Initializing local variables with parameter value
1669 --{
1670 r_input_2 := p_input_2;
1671 r_input_3 := p_input_3;
1672 r_input_4 := p_input_4;
1673 r_input_5 := p_input_5;
1674 r_input_6 := p_input_6;
1675 r_input_7 := p_input_7;
1676 r_input_8 := p_input_8;
1677 r_input_9 := p_input_9;
1678 r_input_10 := p_input_10;
1679 r_input_11 := p_input_11;
1680 r_input_12 := p_input_12;
1681 r_input_13 := p_input_13;
1682 r_input_14 := p_input_14;
1683 r_input_15 := p_input_15;
1684 r_input_16 := p_input_16;
1685 r_input_17 := p_input_17;
1686 r_input_18 := p_input_18;
1687 r_input_19 := p_input_19;
1688 r_input_20 := p_input_20;
1689 r_input_21 := p_input_21;
1690 r_input_22 := p_input_22;
1691 r_input_23 := p_input_23;
1692 r_input_24 := p_input_24;
1693 r_input_25 := p_input_25;
1694 r_input_26 := p_input_26;
1695 r_input_27 := p_input_27;
1696 r_input_28 := p_input_28;
1697 r_input_29 := p_input_29;
1698 r_input_30 := p_input_30;
1699 r_input_31 := p_input_31;
1700 r_input_32 := p_input_32;
1701 r_input_33 := p_input_33;
1702 r_input_34 := p_input_34;
1703 r_input_35 := p_input_35;
1704 r_input_36 := p_input_36;
1705 r_input_37 := p_input_37;
1706 r_input_38 := p_input_38;
1707 r_input_39 := p_input_39;
1708 --}
1709
1710 l_state_length := length(replace(p_input_2,' ')); /* Fix for State code.*/
1711
1712 IF l_state_length < 2 THEN
1713
1714 r_input_2 := lpad(replace(p_input_2,' '),2,0);
1715 r_input_26 := lpad(replace(p_input_26,' '),2,0);
1716
1717 hr_utility.trace('l_state_length = '||to_char(l_state_length));
1718 hr_utility.trace('l_state_length < 2.State code r_input_2 = '||r_input_2);
1719 hr_utility.trace('State code r_input_26 = '||r_input_26);
1720 END IF;
1721
1722 /* Pos:3 - 4 State Code Blank for GA SQWL, State Abbreviation for AL SQWL */
1723 IF (p_report_qualifier = 'GA_SQWL') OR
1724 (p_report_qualifier = 'KS_SQWL') THEN /* Bug 6036926 */
1725 r_input_2 := lpad(' ',2);
1726 ELSIF ( p_report_qualifier = 'AL_SQWL') THEN /* Bug# 4552131 */
1727 r_input_2 := 'AL' ;
1728 ELSE
1729 r_input_2 := rpad(nvl(r_input_2,' '),2);
1730 END IF;
1731
1732 /* Pos:5 - 9 Taxing entity code blank for all the states except AZ_SQWL/TX_SQWL. */
1733
1734 IF ( p_report_qualifier = 'AZ_SQWL' ) THEN
1735 r_input_3 := rpad(substr(nvl(r_input_3,' '),1,5),5);
1736 ELSIF p_report_qualifier = 'TX_SQWL' THEN /* Bug# 4559359 */
1737 r_input_3 := rpad('UTAX',5);
1738 ELSIF p_report_qualifier = 'VA_SQWL' THEN
1739 r_input_3 := rpad('B',5);
1740 ELSIF p_report_qualifier = 'NV_SQWL' THEN
1741 r_input_3 := rpad('WAGE',5);
1742 ELSIF p_report_qualifier = 'FL_SQWL' THEN
1743 r_input_3 := rpad(substr(nvl(r_input_3,' '),1,2),5);
1744 l_end_of_rec := p_end_of_rec;
1745 ELSE
1746 r_input_3 := lpad(' ',5);
1747 END IF;
1748
1749 /* Pos:10 - 18 Social security number */
1750
1751 IF p_input_40 = 'FLAT' THEN
1752 l_ssn := pay_us_reporting_utils_pkg.data_validation(
1753 p_effective_date,
1754 p_report_type,
1755 p_format,
1756 p_report_qualifier,
1757 p_record_name,
1758 'SSN',
1759 r_input_4,
1760 'Social Security',
1761 p_input_39, --EE number for messg purpose.
1762 null,
1763 p_validate,
1764 p_exclude_from_output,
1765 sp_out_1,
1766 sp_out_2);
1767 IF p_exclude_from_output = 'Y' THEN
1768 l_exclude_from_output_chk := TRUE;
1769 END IF;
1770 sp_out_5 := l_ssn;
1771 ELSE
1772 l_ssn := replace(replace(r_input_4,'-'),',');
1773 END IF;
1774
1775 hr_utility.trace('SSN after Validation and Formatting = '||l_ssn);
1776
1777
1778 IF p_report_qualifier = 'LA_SQWL' THEN
1779 --{
1780 /* Pos:19 - 38 Last name
1781 Pos:39 - 53 First name
1782 Pos:54 - 68 Middle name
1783 Pos:69 - 72 suffix Blank */
1784
1785 /* Last name. p_input_7 */
1786 l_first_name := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(substr(r_input_7,1,20),' '),20));
1787
1788 /* Middle Name p_input_5 */
1789 l_middle_name := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(substr(r_input_5,1,15),' '),15));
1790
1791 /* First Name. p_input_6 */
1792 l_last_name := pay_us_reporting_utils_pkg.Character_check(rpad(nvl(substr(r_input_6,1,15),' '),15));
1793 l_suffix := lpad(' ',4);
1794 --}
1795 ELSE
1796 /* Pos:19 - 33 First name
1797 Pos:34 - 48 Middle name
1798 Pos:49 - 68 Last name
1799 Pos:69 - 72 suffix */
1800
1801 l_first_name := pay_us_reporting_utils_pkg.Character_check(rpad(
1802 nvl(substr(r_input_5,1,15),' '),15));
1803 l_middle_name := pay_us_reporting_utils_pkg.Character_check(rpad(
1804 nvl(substr(r_input_6,1,15),' '),15));
1805 l_last_name := pay_us_reporting_utils_pkg.Character_check(rpad(
1806 nvl(substr(r_input_7,1,20),' '),20));
1807 l_suffix := pay_us_reporting_utils_pkg.Character_check(rpad(
1808 nvl(substr(r_input_8,1,4),' '),4));
1809 END IF;
1810 hr_utility.trace('l_suffix = '||l_suffix);
1811
1812 /* Middle Name blank for MD_SQWL. */
1813 IF p_report_qualifier = 'MD_SQWL' THEN
1814 l_middle_name := lpad(' ',15);
1815 END IF;
1816
1817 /* Pos:69 - 149 blank for following state SQWL */
1818 IF ( (p_report_qualifier = 'MD_SQWL') OR
1819 (p_report_qualifier = 'OR_SQWL') OR
1820 (p_report_qualifier = 'MN_SQWL') OR
1821 (p_report_qualifier = 'MS_SQWL') OR
1822 (p_report_qualifier = 'GA_SQWL') OR
1823 (p_report_qualifier = 'MO_SQWL') OR
1824 (p_report_qualifier = 'SC_SQWL') OR
1825 (p_report_qualifier = 'NC_SQWL') OR
1826 (p_report_qualifier = 'NH_SQWL') OR
1827 (p_report_qualifier = 'NV_SQWL') OR
1828 (p_report_qualifier = 'LA_SQWL') OR
1829 (p_report_qualifier = 'CA_SQWL') OR
1830 (p_report_qualifier = 'NY_SQWL') OR
1831 (p_report_qualifier = 'NE_SQWL') OR /* Bug# 5189831*/
1832 (p_report_qualifier = 'KS_SQWL') OR /* Bug 6036926 */
1833 (p_report_qualifier = 'UT_SQWL') OR /* Bug 9913439 */
1834 (p_report_qualifier = 'FL_SQWL') OR
1835 (p_report_qualifier = 'NM_SQWL')) THEN /* Bug# 14356614*/
1836 --{
1837 hr_utility.trace('Pos:69 - 149 blank for AL and NC');
1838 l_suffix := lpad(' ',4);
1839 r_input_9 := lpad(' ',22);
1840 r_input_10 := lpad(' ',22);
1841 r_input_11 := lpad(' ',22);
1842 r_input_12 := lpad(' ',2);
1843 r_input_13 := lpad(' ',5);
1844 r_input_14 := lpad(' ',4);
1845 --}
1846 END IF;
1847
1848 /* Pos:69-72 Suffix not blank for SC_SQWL. */
1849
1850 IF ((p_report_qualifier = 'SC_SQWL') OR
1851 (p_report_qualifier = 'NY_SQWL') OR
1852 (p_report_qualifier = 'NV_SQWL') OR
1853 (p_report_qualifier = 'FL_SQWL')) THEN
1854 l_suffix := pay_us_reporting_utils_pkg.Character_check(rpad(
1855 nvl(substr(r_input_8,1,4),' '),4));
1856 END IF;
1857
1858 /* Pos: 73 - 149 Blank for Alabama SQWL Ref Bug# 4552131*/
1859
1860 IF ( p_report_qualifier = 'TX_SQWL' ) THEN /* Bug# 4559359 */
1861
1862 r_input_9 := lpad(' ',22);
1863 r_input_10 := lpad(' ',22);
1864 r_input_11 := lpad(' ',22);
1865 r_input_12 := lpad(' ',2);
1866 r_input_13 := lpad(' ',5);
1867 r_input_14 := lpad(' ',4);
1868
1869 END IF ;
1870 /* Pos: 139 'AL' for Alabama SQWL Ref Bug# 7335939*/
1871 IF ( p_report_qualifier = 'AL_SQWL') THEN /* Bug# 7335939 */
1872
1873 r_input_9 := lpad(' ',22);
1874 r_input_10 := lpad(' ',22);
1875 r_input_11 := lpad(' ',22);
1876 r_input_12 := lpad('AL',2);
1877 r_input_13 := lpad(' ',5);
1878 r_input_14 := lpad(' ',4);
1879
1880 END IF ;
1881
1882 /* Pos:155 - 177 178 - 192 193 - 194
1883 Foreign State / Province --> r_input_15
1884 Foreign Postal Code --> r_input_16
1885 Country Code --> r_input_17
1886 Foreign address set to blank for following states and locals */
1887
1888 IF ((p_report_qualifier = 'MD_SQWL') OR
1889 (p_report_qualifier = 'OR_SQWL') OR
1890 (p_report_qualifier = 'MN_SQWL') OR
1891 (p_report_qualifier = 'MO_SQWL') OR
1892 (p_report_qualifier = 'MS_SQWL') OR
1893 (p_report_qualifier = 'GA_SQWL') OR
1894 (p_report_qualifier = 'SC_SQWL') OR
1895 (p_report_qualifier = 'LA_SQWL') OR
1896 (p_report_qualifier = 'AZ_SQWL') OR
1897 (p_report_qualifier = 'NC_SQWL') OR
1898 (p_report_qualifier = 'NH_SQWL') OR
1899 (p_report_qualifier = 'NV_SQWL') OR
1900 (p_report_qualifier = 'CA_SQWL') OR
1901 (p_report_qualifier = 'NY_SQWL') OR
1902 (p_report_qualifier = 'NE_SQWL') OR /* Bug # 5189831*/
1903 (p_report_qualifier = 'AL_SQWL') OR /* AL is added for Bug# 4552131 */
1904 (p_report_qualifier = 'KS_SQWL') OR /* 6036926 */
1905 (p_report_qualifier = 'UT_SQWL') OR /* 9913439 */
1906 (p_report_qualifier = 'VA_SQWL') OR /* Bug# 13377871 */
1907 (p_report_qualifier = 'TX_SQWL') OR /* TX is added for Bug# 4559359 */
1908 (p_report_qualifier = 'NM_SQWL')) THEN /* Bug# 14356614*/
1909 --{
1910 r_input_15 := lpad(' ',23);
1911 r_input_16 := lpad(' ',15);
1912 r_input_17 := lpad(' ',2);
1913 --}
1914 ELSE
1915 --{
1916 r_input_15 := rpad(nvl(r_input_15,' '),23);
1917 r_input_16 := rpad(nvl(r_input_16,' '),15);
1918 r_input_17 := rpad(nvl(r_input_17,' '),2);
1919 --}
1920 END IF;
1921
1922
1923 /* Pos:195 - 242 Optional code - Date of separation blanks */
1924 /* Pos:203-226 State quarterly unemployments details should be Zero filled
1925 for MO, OH and PA
1926 This is to fix bug # 2627606
1927 This is also true with many states for W2, so changes made for W2
1928 */
1929 --{
1930 /* Pos:195-196 Optional code .
1931 Pos:197-202 Reporting period. */
1932 r_input_18 := lpad(' ',2);
1933
1934 r_input_19 := rpad(nvl(p_input_19,' '),6);
1935
1936 /* GA_SQWL expects state code in Pos:195-196. */
1937 IF p_report_qualifier = 'GA_SQWL' THEN
1938 r_input_18 := lpad(replace(p_input_2,' '),2);
1939 ELSIF p_report_qualifier = 'MO_SQWL' THEN
1940 r_input_18 := '00';
1941 ELSIF p_report_qualifier = 'NC_SQWL' THEN
1942 r_input_18 := replace(nvl(p_input_18,'N'),'Y','S')||' ';
1943 hr_utility.trace('Seasonal worker for NC is '||p_input_18);
1944 ELSIF (p_report_qualifier = 'CA_SQWL') THEN
1945 r_input_18 := p_input_18||' ';
1946 ELSIF (p_report_qualifier = 'NY_SQWL') THEN
1947 r_input_18 := lpad(' ',2);
1948 r_input_19 := lpad(' ',6);
1949 ELSIF (p_report_qualifier = 'NM_SQWL') THEN /* Bug# 14356614*/
1950 /* Pos:197-202 Reporting period. for NM_SQWL */
1951 IF substr(p_input_19,1,2)='03' THEN
1952 r_input_19 := substr(p_input_19,3,4)||'01';
1953 ELSIF substr(p_input_19,1,2)='06' THEN
1954 r_input_19 := substr(p_input_19,3,4)||'02';
1955 ELSIF substr(p_input_19,1,2)='09' THEN
1956 r_input_19 := substr(p_input_19,3,4)||'03';
1957 ELSIF substr(p_input_19,1,2)='12' THEN
1958 r_input_19 := substr(p_input_19,3,4)||'04';
1959 END IF;
1960 END IF;
1961
1962 /* Pos:203-213 SUI Insurance Wages. */
1963 IF p_input_40 = 'FLAT' THEN
1964 r_input_20 :=
1965 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
1966 p_report_type,
1967 p_format,
1968 p_report_qualifier,
1969 p_record_name,
1970 'NEG_CHECK',
1971 r_input_20,
1972 'SUI Insurance Wages',
1973 p_input_39,
1974 null,
1975 p_validate,
1976 p_exclude_from_output,
1977 sp_out_1,
1978 sp_out_2);
1979
1980 IF p_exclude_from_output = 'Y' THEN
1981 l_exclude_from_output_chk := TRUE;
1982 END IF;
1983
1984 /* Pos:214-224 SUI Taxable Wages. */
1985 IF ((p_report_qualifier = 'AZ_SQWL') OR
1986 (p_report_qualifier = 'SC_SQWL') OR
1987 (p_report_qualifier = 'FL_SQWL') OR
1988 (p_report_qualifier = 'KS_SQWL') OR /* Bug 6036926 */
1989 (p_report_qualifier = 'UT_SQWL') OR /* Bug 9913439 */
1990 (p_report_qualifier = 'TX_SQWL')) THEN /* #5919391 */
1991 --{
1992 r_input_21 :=
1993 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
1994 p_report_type,
1995 p_format,
1996 p_report_qualifier,
1997 p_record_name,
1998 'NEG_CHECK',
1999 r_input_21,
2000 'SUI Taxable Wages',
2001 p_input_39,
2002 null,
2003 p_validate,
2004 p_exclude_from_output,
2005 sp_out_1,
2006 sp_out_2);
2007 /*Added for Bug#9959504*/
2008 IF p_report_qualifier = 'FL_SQWL' THEN
2009
2010 r_input_37 :=
2011 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
2012 p_report_type,
2013 p_format,
2014 p_report_qualifier,
2015 p_record_name,
2016 'NEG_CHECK',
2017 r_input_37,
2018 'SUI Taxable Wages',
2019 p_input_39,
2020 null,
2021 p_validate,
2022 p_exclude_from_output,
2023 sp_out_1,
2024 sp_out_2);
2025
2026 END IF;
2027 /*End of changes for Bug#9959504*/
2028 IF p_exclude_from_output = 'Y' THEN
2029 l_exclude_from_output_chk := TRUE;
2030 END IF;
2031 END IF; /* AZ,NH,SC_SQWL */
2032
2033 /* Bug#14099390 */
2034 IF p_report_qualifier = 'FL_SQWL' THEN
2035 r_input_38 :=
2036 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
2037 p_report_type,
2038 p_format,
2039 p_report_qualifier,
2040 p_record_name,
2041 'NEG_CHECK',
2042 r_input_38,
2043 'Educational Wages',
2044 p_input_39,
2045 null,
2046 p_validate,
2047 p_exclude_from_output,
2048 sp_out_1,
2049 sp_out_2);
2050
2051 IF p_exclude_from_output = 'Y' THEN
2052 l_exclude_from_output_chk := TRUE;
2053 END IF;
2054 END IF;
2055
2056 END IF; /* p_input_40 FLAT */
2057
2058 IF ((p_report_qualifier = 'MD_SQWL') OR
2059 (p_report_qualifier = 'MN_SQWL') OR
2060 (p_report_qualifier = 'MS_SQWL') OR
2061 (p_report_qualifier = 'LA_SQWL') OR
2062 (p_report_qualifier = 'NH_SQWL') OR
2063 (p_report_qualifier = 'NV_SQWL') OR
2064 (p_report_qualifier = 'NC_SQWL') OR
2065 (p_report_qualifier = 'OR_SQWL') OR
2066 (p_report_qualifier = 'MO_SQWL') OR
2067 (p_report_qualifier = 'AL_SQWL')) THEN /* AL is added for Bug# 4552131 */
2068
2069 r_input_21 := rpad(0,11,0);
2070
2071 END IF;
2072
2073 IF ( p_report_qualifier = 'TX_SQWL' ) THEN
2074 r_input_21 := lpad(r_input_21, 11, '0') ;
2075 END IF ;
2076
2077 IF ((p_report_qualifier = 'CA_SQWL') OR
2078 (p_report_qualifier = 'NY_SQWL') OR
2079 (p_report_qualifier = 'VA_SQWL')) THEN
2080
2081 r_input_21 := lpad(' ',11);
2082 END IF;
2083
2084 IF p_report_qualifier = 'GA_SQWL' THEN
2085 r_input_20 := lpad(r_input_20,11,0);
2086 r_input_21 := lpad(' ',11);
2087 END IF;
2088
2089 IF p_report_qualifier = 'NE_SQWL' THEN/* Bug# 5189831*/
2090 r_input_20 := lpad(r_input_20,11,0);
2091 r_input_21 := lpad('0',11,'0');
2092 END IF;
2093
2094 /* Pos:203-213 SUI Insurance Wages.
2095 Pos:214-224 SUI Taxable Wages. for NM_SQWL*/
2096 IF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2097
2098 IF r_input_20 < 0 THEN
2099 r_input_20 := substr(r_input_20,1,1)||lpad(substr(r_input_20,2,11),11,'0');
2100 ELSE
2101 r_input_20 := lpad(r_input_20,11,'0');
2102 END IF;
2103
2104 r_input_21 := lpad(' ',11);
2105 END IF;
2106
2107 /* Bug:2286329 Negative hours check. */
2108 IF ((p_report_qualifier = 'MN_SQWL') OR
2109 (p_report_qualifier = 'OR_SQWL')) THEN
2110
2111 l_hours_worked := to_number(replace(r_input_34,' '));
2112 hr_utility.trace('l_hours_worked = '||to_char(l_hours_worked));
2113
2114 IF l_hours_worked <0 THEN
2115 hr_utility.trace('Error in RS record for Employee '||p_input_1||
2116 ' Negative hours');
2117 l_exclude_from_output_chk := TRUE;
2118 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
2119 pay_core_utils.push_token('record_name',p_record_name);
2120 pay_core_utils.push_token('name_or_number',substr(p_input_39,1,50));
2121 pay_core_utils.push_token('description','Negative hours');
2122 END IF;
2123
2124 END IF; /*Hours check for OR,MN SQWL */
2125
2126 /*Pos:225-226 No. of weeks worked. */
2127 r_input_22 := rpad(0,2,0);
2128
2129 /*Pos:225-226 No. of weeks worked.Blank for SC_SQWL. */
2130 IF ((p_report_qualifier = 'SC_SQWL') OR
2131 (p_report_qualifier = 'LA_SQWL') OR
2132 (p_report_qualifier = 'GA_SQWL') OR
2133 (p_report_qualifier = 'CA_SQWL') OR
2134 ( p_report_qualifier = 'NY_SQWL') OR
2135 ( p_report_qualifier = 'NE_SQWL') OR /* Bug# 5189831*/
2136 ( p_report_qualifier = 'VA_SQWL') OR /* Fix.*/
2137 ( p_report_qualifier = 'KS_SQWL') OR /* 6036926 */
2138 ( p_report_qualifier = 'UT_SQWL') OR /* 9913439 */
2139 ( p_report_qualifier = 'TX_SQWL' ) OR /* Bug# 4559359 */
2140 (p_report_qualifier = 'NM_SQWL')) THEN /* Bug# 14356614*/
2141
2142 r_input_22 := lpad(' ',2);
2143 END IF;
2144
2145 /* Pos:227-234:Date First Employed,Pos:
2146 235-242:Date of Separation. */
2147 IF ( p_report_qualifier = 'TX_SQWL' ) THEN /* Bug# 4559359 */
2148 r_input_23 := lpad(substr(nvl(r_input_23,' '),1,8),8) ;
2149
2150 if r_input_24 <> '01010001' then
2151 r_input_24 := lpad(substr(nvl(r_input_24,' '),1,8),8) ;
2152 else
2153 r_input_24 := lpad(' ',8);
2154 end if;
2155
2156 ELSE
2157 r_input_23 := lpad(' ',8);
2158 r_input_24 := lpad(' ',8);
2159 END IF ;
2160
2161 /* Pos:243-247 Unit Number for NM_SQWL*/
2162 IF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2163 r_input_24 := r_input_24||lpad(substr(p_input_24,9,5),5,'0');
2164 END IF;
2165
2166 /* Pos:248-267 State Employer Account number */
2167 --{
2168 IF ((p_report_qualifier = 'NH_SQWL') ) THEN /* Bug:2901849 */
2169 r_input_25 := lpad(replace(replace(nvl(replace(r_input_25,' '),' ')
2170 ,'-'),'/'),20,0);
2171 ELSIF p_report_qualifier = 'NC_SQWL' THEN
2172 r_input_25 := rpad(lpad(replace(replace(nvl(replace(r_input_25,' ')
2173 ,' '),'-'),'/'),7),20);
2174 ELSIF p_report_qualifier = 'OR_SQWL' THEN
2175 l_s_hyphen_position := nvl(instr(substr(r_input_25,1,7),'-'),1) - 1 ;
2176 IF l_s_hyphen_position = -1 or l_s_hyphen_position > 7 then
2177 l_s_hyphen_position := 7;
2178 End IF;
2179 r_input_25 := lpad(nvl(substr(r_input_25,1,l_s_hyphen_position),0),7,'0')
2180 || lpad(' ',13);
2181 ELSIF p_report_qualifier = 'NV_SQWL' THEN
2182
2183 l_s_hyphen_position := nvl(instr(substr(r_input_25,1,9),'-'),1) - 1 ;
2184 IF l_s_hyphen_position = -1 or l_s_hyphen_position > 9 then
2185 l_s_hyphen_position := 9;
2186 End IF;
2187
2188 r_input_25 := rpad(lpad(replace(nvl(replace(substr(r_input_25,1,l_s_hyphen_position)
2189 ,' '),' '),'.'),9,'0'),20);
2190
2191 ELSIF p_report_qualifier = 'NE_SQWL' THEN /* Bug# 5189831*/
2192
2193 l_s_hyphen_position := nvl(instr(substr(r_input_25,1,10),'-'),1) - 1 ;
2194 IF l_s_hyphen_position = -1 or l_s_hyphen_position > 10 then
2195 l_s_hyphen_position := 10;
2196 End IF;
2197
2198 r_input_25 := lpad(lpad(replace(nvl(replace(substr(r_input_25,1,l_s_hyphen_position)
2199 ,' '),' '),'.'),10,'0'),20);
2200
2201 ELSIF p_report_qualifier = 'MO_SQWL' THEN
2202
2203 r_input_25 := rpad(replace(replace(nvl(replace(r_input_25,' '),' ')
2204 ,'-'),'/'),15)
2205 || rpad( ' ',5) ;
2206 ELSIF p_report_qualifier = 'GA_SQWL' THEN
2207
2208 r_input_25 := lpad(' ',12) || rpad(replace(replace(nvl(
2209 replace(r_input_25,' '),' '),'-'),'/'),8);
2210 ELSIF p_report_qualifier = 'SC_SQWL' THEN
2211
2212 l_s_hyphen_position := nvl(instr(substr(r_input_25,1,6),'-'),1) - 1 ;
2213 IF l_s_hyphen_position = -1 or l_s_hyphen_position > 6 then
2214 l_s_hyphen_position := 6;
2215 End IF;
2216
2217 /* r_input_25 := lpad(nvl(replace(substr(r_input_25,1,l_s_hyphen_position)
2218 ,'/'),0),20,'0'); */
2219 r_input_25 := lpad(nvl(replace(substr(r_input_25,1,l_s_hyphen_position)
2220 ,'/'),0),6,'0') || rpad(' ', 14, ' ') ; /* Modified for Bug# 14086658 */
2221
2222 ELSIF (p_report_qualifier = 'MD_SQWL') THEN
2223
2224 r_input_25 := lpad(lpad(replace(replace(substr(nvl(replace(r_input_25,
2225 ' '),' '),1,10),'-'),'/'),10),20);
2226 ELSIF p_report_qualifier = 'MS_SQWL' THEN
2227
2228 r_input_25 := rpad(replace(replace(nvl(replace(r_input_25,' '),' ')
2229 ,'-'),'/'),20);
2230 ELSIF ((p_report_qualifier = 'LA_SQWL') OR
2231 ( p_report_qualifier = 'NY_SQWL')) THEN
2232
2233 r_input_25 := lpad(' ',20);
2234 ELSIF p_report_qualifier = 'AZ_SQWL' THEN
2235 r_input_25 := rpad(replace(replace(substr(nvl(replace(r_input_25,' ')
2236 ,' '),1,7),'-'),'/'),7)||lpad(substr(nvl(
2237 replace(r_input_26,' '),' '),1,3),3,0)||lpad(' ',10);
2238
2239
2240 /* Bug :4170713 */
2241 /* Position 260 : Month1 Employment
2242 Position 261 : Month2 Employment
2243 Position 262 : Month3 Employment
2244 Position 263-267 : Blank
2245 */
2246
2247 ELSIF p_report_qualifier = 'MN_SQWL' THEN /* Bug:2334393 */
2248 /*
2249 l_s_hyphen_position :=
2250 nvl(instr(substr(substr(r_input_25,1,instr(r_input_25,'^',1,1) - 1),1,8),'-'),1) - 1 ;
2251
2252 IF l_s_hyphen_position = -1 or l_s_hyphen_position > 8 then
2253 l_s_hyphen_position := 8;
2254 End IF;
2255 */
2256 /* Bug# 1236643 Pos: 256 - 259 Employer Unit Number */
2257 l_s_hyphen_position :=
2258 nvl(instr(substr(r_input_25,1,instr(r_input_25,'^',1,1) - 1),'-'),1) - 1 ;
2259
2260 l_carate_1_position := instr(r_input_25,'^',1,1) ;
2261
2262 IF l_s_hyphen_position = -1 THEN
2263 l_st_er_acct_no := lpad(replace(nvl(substr(r_input_25,1,8),'0'),'/'),8,'0') ;
2264 ELSE
2265 l_st_er_acct_no := lpad(replace(nvl(substr(substr(r_input_25,1,l_s_hyphen_position),1,8),'0'),'/'),8,'0') ;
2266 End IF;
2267
2268 l_er_unit_no := substr(r_input_26,1,4); /* Bug#4959283 */
2269
2270 If substr(r_input_25,instr(r_input_25,'^',1,1) + 1,instr(r_input_25,'^',1,2) - instr(r_input_25,'^',1,1) - 1) = '0' Then
2271 l_pos_260 := '0' ;
2272 Else
2273 l_pos_260 := '1' ;
2274 End If ;
2275
2276 If substr(r_input_25,instr(r_input_25,'^',1,2) + 1,instr(r_input_25,'^',1,3) - instr(r_input_25,'^',1,2) - 1) = '0' Then
2277 l_pos_261 := '0' ;
2278 Else
2279 l_pos_261 := '1' ;
2280 End If ;
2281
2282 If substr(r_input_25,instr(r_input_25,'^',1,3) + 1) = '0' Then
2283 l_pos_262 := '0' ;
2284 Else
2285 l_pos_262 := '1' ;
2286 End If ;
2287
2288 r_input_25 := l_st_er_acct_no || l_er_unit_no || l_pos_260 || l_pos_261 || l_pos_262 || rpad(' ',5,' ') ;
2289
2290 ELSIF p_report_qualifier = 'CA_SQWL' THEN
2291 r_input_25 := rpad(replace(r_input_25, '-'), 11, '0')||lpad(' ',9);
2292 ELSIF p_report_qualifier = 'VA_SQWL' THEN
2293 /* Modified for bug# 13377871 */
2294 r_input_25 := rpad(' ',10,' ') || lpad(substr(nvl(replace(replace(r_input_25, '-'), ' '),'0'),1,10),10,'0');
2295 -- r_input_25 := lpad(substr(nvl(replace(replace(r_input_25, '-'), ' '),'0'),1,10),20,'0');
2296 ELSIF p_report_qualifier = 'FL_SQWL' THEN
2297 r_input_25 := rpad(replace(nvl(substr(r_input_25,1,20),' '),'-'),20,' ');
2298 ELSIF p_report_qualifier = 'AL_SQWL' THEN
2299 r_input_25 := rpad(nvl(substr(r_input_25,1,10),' '),20,' ') ;
2300 ELSIF p_report_qualifier = 'TX_SQWL' OR /* Bug# 4559359 */
2301 p_report_qualifier = 'KS_SQWL' THEN /* Bug# 6036926 */
2302 r_input_25 := rpad(nvl(substr(replace(r_input_25,'-' ),1,9),' '),20,' ') ;
2303 ELSIF p_report_qualifier = 'UT_SQWL' THEN /*9913439*/
2304 r_input_25 := rpad(lpad(replace(replace(nvl(replace(r_input_25,' ')
2305 ,' '),'-'),'/'),10),20);
2306 ELSIF p_report_qualifier = 'NM_SQWL' THEN /* Bug#14356614 */
2307
2308 /* Pos: 248 - 267 State Employer Account Number */
2309 l_st_er_acct_no := lpad (replace (substr (r_input_25,1,instr (r_input_25,'^',1,1) - 1),'-'),8,'0');
2310 l_er_unit_no := rpad(' ',4,' ') ;
2311
2312 If substr(r_input_25,instr(r_input_25,'^',1,1) + 1,instr(r_input_25,'^',1,2) - instr(r_input_25,'^',1,1) - 1) = '0' Then
2313 l_pos_260 := '0' ;
2314 Else
2315 l_pos_260 := '1' ;
2316 End If ;
2317
2318 If substr(r_input_25,instr(r_input_25,'^',1,2) + 1,instr(r_input_25,'^',1,3) - instr(r_input_25,'^',1,2) - 1) = '0' Then
2319 l_pos_261 := '0' ;
2320 Else
2321 l_pos_261 := '1' ;
2322 End If ;
2323
2324 If substr(r_input_25,instr(r_input_25,'^',1,3) + 1) = '0' Then
2325 l_pos_262 := '0' ;
2326 Else
2327 l_pos_262 := '1' ;
2328 End If ;
2329
2330 r_input_25 := l_st_er_acct_no || l_er_unit_no || l_pos_260 || l_pos_261 || l_pos_262 || rpad(' ',5,' ') ;
2331 END IF;
2332 --}
2333
2334 /* Pos:274 - 275 State code blank for PA,NJ,OH RITA ,NC and SQWL */
2335 IF (p_report_qualifier = 'MN_SQWL') THEN
2336 r_input_26 := '00';
2337 ELSE
2338 r_input_26 := lpad(' ',2);
2339 END IF;
2340
2341 /* Pos:276 - 286 State taxable wages.
2342 Pos:287 - 297 SIT withheld. */
2343
2344 IF ((p_report_qualifier = 'CA_SQWL') OR
2345 (p_report_qualifier = 'NY_SQWL' and
2346 substr(p_input_19,1,2) = '12' ) OR
2347 (p_report_qualifier = 'NM_SQWL')) THEN /* Bug#14356614 */
2348 --{
2349 IF p_input_40 = 'FLAT' THEN
2350 r_input_27:=
2351 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
2352 p_report_type,
2353 p_format,
2354 p_report_qualifier,
2355 p_record_name,
2356 'NEG_CHECK',
2357 r_input_27,
2358 'State taxable Wages',
2359 p_input_39,
2360 null,
2361 p_validate,
2362 p_exclude_from_output,
2363 sp_out_1,
2364 sp_out_2);
2365 IF p_exclude_from_output = 'Y' THEN
2366 l_exclude_from_output_chk := TRUE;
2367 END IF;
2368 r_input_28 :=
2369 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
2370 p_report_type,
2371 p_format,
2372 p_report_qualifier,
2373 p_record_name,
2374 'NEG_CHECK',
2375 r_input_28,
2376 'SIT Withheld',
2377 p_input_39,
2378 null,
2379 p_validate,
2380 p_exclude_from_output,
2381 sp_out_1,
2382 sp_out_2);
2383 IF p_exclude_from_output = 'Y' THEN
2384 l_exclude_from_output_chk := TRUE;
2385 END IF;
2386
2387 END IF;
2388
2389 IF (p_report_qualifier = 'NM_SQWL') THEN /* Bug#14356614 */
2390
2391 r_input_28 := r_input_28*100;
2392
2393 IF r_input_27 < 0 THEN
2394 r_input_27 := substr(r_input_27,1,1)||lpad(substr(r_input_27,2,11),11,'0');
2395 ELSE
2396 r_input_27 := lpad(r_input_27,11,'0');
2397 END IF;
2398
2399 IF r_input_28 < 0 THEN
2400 r_input_28 := substr(r_input_28,1,1)||lpad(substr(r_input_28,2,11),11,'0');
2401 ELSE
2402 r_input_28 := lpad(r_input_28,11,'0');
2403 END IF;
2404
2405 END IF;
2406 --}
2407 ELSE
2408 r_input_27 := rpad(0,11,0);
2409 r_input_28 := rpad(0,11,0);
2410 END IF;
2411
2412 /* Pos:276-297 Blank for SC_SQWL. State wages and SIT withheld. */
2413 IF ((p_report_qualifier = 'SC_SQWL') OR
2414 (p_report_qualifier = 'GA_SQWL') OR
2415 (p_report_qualifier = 'FL_SQWL') OR
2416 (p_report_qualifier = 'VA_SQWL') OR
2417 (p_report_qualifier = 'AZ_SQWL') OR /* Fix.*/
2418 (p_report_qualifier = 'KS_SQWL') OR /* Bug 6036926 */
2419 (p_report_qualifier = 'UT_SQWL') OR /* Bug 9913439 */
2420 (p_report_qualifier = 'TX_SQWL')) /* Bug# 4559359 */ THEN
2421 r_input_27 := lpad(' ',11);
2422 r_input_28 := lpad(' ',11);
2423 END IF;
2424
2425 /* Pos:298-307 State Excess Wages for LA_SQWL. */
2426 IF (p_report_qualifier = 'LA_SQWL') THEN
2427 IF p_input_40 = 'FLAT' THEN
2428 hr_utility.trace(' Other state data AL,MD,OH ');
2429 r_input_29 :=
2430 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
2431 p_report_type,
2432 p_format,
2433 p_report_qualifier,
2434 p_record_name,
2435 'NEG_CHECK',
2436 r_input_29,
2437 'Other State Data',
2438 p_input_39,
2439 null,
2440 p_validate,
2441 p_exclude_from_output,
2442 sp_out_1,
2443 sp_out_2);
2444 IF p_exclude_from_output = 'Y' THEN
2445 l_exclude_from_output_chk := TRUE;
2446 END IF;
2447 END IF;
2448 ELSE
2449 r_input_29 := lpad(' ',10);
2450 END IF;
2451
2452 IF p_report_qualifier = 'NC_SQWL' THEN
2453 r_input_29 := rpad(upper(rpad(p_input_29, 6, ' ')),10);
2454 hr_utility.trace('State Control No for NC is '||r_input_29);
2455 END IF;
2456
2457 /* Pos:308 - 308 Tax type code formatting */
2458 /* Modified for Bug#13377871 */
2459 r_input_30 := lpad(' ', 1);
2460 -- IF p_report_qualifier = 'VA_SQWL' THEN
2461 -- r_input_30 := 'B' ; /*Unemployement Tax */
2462 -- ELSE
2463 -- r_input_30 := lpad(' ', 1);
2464 -- END IF;
2465
2466 /* Pos:309 - 319 Local taxable wages filled with zeros
2467 Pos:320 - 330 Local Income tax withheld filled with zeros */
2468
2469 hr_utility.trace('Zero fill for SQWLs. p_report_qualifier = '||p_report_qualifier);
2470 r_input_31 := rpad(0,11,0);
2471 r_input_32 := rpad(0,11,0);
2472 hr_utility.trace('r_input_31 '||r_input_31);
2473 hr_utility.trace('r_input_32 '||r_input_32);
2474
2475 /* Pos:309-330 Blank for SC_SQWL. State wages and SIT withheld. */
2476 IF ((p_report_qualifier = 'SC_SQWL') OR
2477 (p_report_qualifier = 'GA_SQWL') OR
2478 (p_report_qualifier = 'LA_SQWL') OR
2479 (p_report_qualifier = 'CA_SQWL') OR
2480 (p_report_qualifier = 'NY_SQWL') OR
2481 (p_report_qualifier = 'FL_SQWL') OR
2482 (p_report_qualifier = 'VA_SQWL') OR
2483 (p_report_qualifier = 'AZ_SQWL') OR /* Fix.*/
2484 (p_report_qualifier = 'KS_SQWL') OR /* Bug 6036926 */
2485 (p_report_qualifier = 'UT_SQWL') OR /* Bug 9913439 */
2486 (p_report_qualifier = 'TX_SQWL')) THEN /* Bug# 4559359 */
2487 --{
2488 r_input_31 := lpad(' ',11);
2489 r_input_32 := lpad(' ',11);
2490 --}
2491 END IF;
2492
2493 /* Pos:309-319 local taxable wages
2494 Pos:320-330 NM income tax withheld for NM_SQWL*/
2495 IF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2496 r_input_31 := lpad(' ',11);
2497 r_input_32 := p_input_32;
2498 IF p_input_40 = 'FLAT' THEN
2499 r_input_32:=
2500 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
2501 p_report_type,
2502 p_format,
2503 p_report_qualifier,
2504 p_record_name,
2505 'NEG_CHECK',
2506 p_input_32,
2507 'NM Income Tax Withheld',
2508 p_input_39,
2509 null,
2510 p_validate,
2511 p_exclude_from_output,
2512 sp_out_1,
2513 sp_out_2);
2514 IF p_exclude_from_output = 'Y' THEN
2515 l_exclude_from_output_chk := TRUE;
2516 END IF;
2517 END IF;
2518 r_input_32 := r_input_32*100;
2519
2520 IF r_input_32 < 0 THEN
2521 r_input_32 := substr(r_input_32,1,1)||lpad(substr(r_input_32,2,11),11,'0');
2522 ELSE
2523 r_input_32 := lpad(r_input_32,11,'0');
2524 END IF;
2525 END IF;
2526
2527 /* Pos:331 - 337 State Control number filled with blank for SQWL
2528 Pos:338 - 412 Supplemental data1
2529 Pos:413 - 487 Supplemental data2 */
2530 r_input_33 := replace(replace(replace(p_input_33,'-'),' '),'/');
2531 --{
2532 -- Formatting Supplemental Data
2533 --
2534 r_input_33 := lpad(' ',7);
2535 IF p_report_qualifier = 'NE_SQWL' THEN
2536 r_input_33 := lpad('0',7,'0');
2537 END IF;
2538
2539
2540 IF p_report_qualifier = 'LA_SQWL' THEN
2541
2542 r_input_34 := lpad(rpad(rpad(nvl(r_input_34,' '),1) ||
2543 rpad(nvl(r_input_35,' '),1) ||
2544 rpad(nvl(r_input_36,' '),1),56),75);
2545 r_input_35 := lpad(' ',75);
2546
2547 ELSIF p_report_qualifier = 'KS_SQWL' THEN
2548 r_input_34 := rpad(rpad(nvl(r_input_34,' '),1) ||
2549 rpad(nvl(r_input_35,' '),1) ||
2550 rpad(nvl(r_input_36,' '),1),75); /* 6036926 */
2551 r_input_35 := lpad(' ',75);
2552
2553 ELSIF p_report_qualifier = 'MN_SQWL' THEN
2554 r_input_34 := rpad(lpad(nvl(r_input_34,' '),3,0)||/*3011801*/
2555 rpad(nvl(r_input_35,' '),1),75);
2556 r_input_35 := lpad(' ',75);
2557 ELSIF p_report_qualifier = 'OR_SQWL' THEN
2558 r_input_34 := rpad(lpad(nvl(r_input_34,' '),3,0),75); /*2286335*/
2559 r_input_35 := lpad(' ',75);
2560 ELSIF p_report_qualifier = 'NV_SQWL' THEN
2561 -- r_input_34 := 0; /* Will be supported in Q1 2005 with new Tips Wages Balance*/
2562 -- Added for bug 3835632 .
2563 r_input_34 := rpad(lpad(nvl(r_input_34,' '),9,0),75);
2564 r_input_35 := lpad(' ',75);
2565 /* 4559359 */
2566 /* ELSIF p_report_qualifier = 'TX_SQWL' THEN
2567 hr_utility.trace(' r_input_34 := '|| r_input_34 ) ;
2568 r_input_34 := lpad(nvl(r_input_34,' '),3,'0') ||
2569 lpad(substr(nvl(r_input_35,' '),1,3),3) || /* County Code * /
2570 lpad(' ',1) ||
2571 lpad(' ',6) || /* NAICS Code * /
2572 lpad(' ',1) ||
2573 lpad(' ',10) || /* Establishment ID - Not Supported * /
2574 lpad(' ',1) ||
2575 lpad('0',5,'0') || /* Unit Number - Zero Fill * /
2576 lpad(' ',45) ;
2577 */
2578 ELSIF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2579 /* Pos:338-340 Hours Worked fill with blank
2580 Pos:341-341 Officer Code
2581 Pos:342-412 fill with blank
2582 Pos:413-487 fill with blank for NM_SQWL*/
2583 r_input_34 := lpad(' ',3) || lpad(p_input_34,1) || lpad(' ',71);
2584 r_input_35 := lpad(' ',75);
2585 ELSE
2586 r_input_34 := lpad(' ',75);
2587 r_input_35 := lpad(' ',75);
2588 END IF;
2589 --}
2590
2591 /* Fix for Bug # 2789523 */
2592 IF p_report_qualifier = 'MN_SQWL' THEN
2593 l_last_field := lpad('X',25);
2594 /* Pos:488-511 fill with blank for NM_SQWL*/
2595 ELSIF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 16091833*/
2596 l_last_field := lpad(' ',24);
2597 ELSE
2598 l_last_field := lpad(' ',25);
2599 END IF;
2600
2601
2602 /* Pos:512-512 End of line identified carriage return for NM_SQWL*/
2603 IF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2604 l_end_of_rec := p_end_of_rec;
2605 END IF;
2606
2607 IF p_report_qualifier = 'WA_SQWL' THEN
2608 hr_utility.trace('WA_SQWL');
2609 r_input_4 :=
2610 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
2611 p_report_type,
2612 p_format,
2613 p_report_qualifier,
2614 p_record_name,
2615 'NEG_CHECK',
2616 p_input_4,
2617 'Quarterly Gross Wages',
2618 p_input_2,
2619 null,
2620 p_validate,
2621 p_exclude_from_output,
2622 sp_out_1,
2623 sp_out_2);
2624 IF p_exclude_from_output = 'Y' THEN
2625 l_exclude_from_output_chk := TRUE;
2626 END IF;
2627 l_hours_worked := to_number(replace(p_input_3,' '));
2628 hr_utility.trace('l_hours_worked = '||to_char(l_hours_worked));
2629 IF l_hours_worked <0 THEN
2630 hr_utility.trace('Error in RS record for Employee '||p_input_2||
2631 ' Negative hours');
2632 l_exclude_from_output_chk := TRUE;
2633 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
2634 pay_core_utils.push_token('record_name',p_record_name);
2635 pay_core_utils.push_token('name_or_number',substr(p_input_2,1,50));
2636 pay_core_utils.push_token('description','Negative hours');
2637 END IF;
2638 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
2639 -- Validation Starts
2640 -- EIN Validation
2641 r_input_5 :=
2642 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
2643 p_report_type,
2644 p_format,
2645 p_report_qualifier,
2646 p_record_name,
2647 'EIN',
2648 p_input_5,
2649 'EIN',
2650 p_input_2,
2651 null,
2652 p_validate,
2653 p_exclude_from_output,
2654 sp_out_1,
2655 sp_out_2);
2656
2657 IF p_exclude_from_output = 'Y' THEN
2658 l_exclude_from_output_chk := TRUE;
2659 END IF;
2660 hr_utility.trace('Valid EIN '||r_input_5);
2661 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
2662 -- Validation Ends
2663 r_input_2 := replace(p_input_2,',');
2664 r_input_4 := to_char(to_number(r_input_4/100),'000000.00');
2665 /*
2666 l_wa_sqwl_outstring := p_input_1||','||r_input_2||','||p_input_3||','||r_input_4;
2667 */
2668 l_wa_sqwl_outstring := r_input_5||','||p_input_1||','||r_input_2||','||p_input_3||','||r_input_4;
2669 hr_utility.trace('l_wa_sqwl_outstring = '||l_wa_sqwl_outstring);
2670 END IF;
2671
2672 IF p_input_40 = 'FLAT' THEN
2673 --{ Start of formatting FLAT type RS Record
2674 --
2675 IF (p_report_qualifier = 'NY_SQWL') THEN
2676
2677 /* Bug 3527986 Removed Delimiters fro NY */
2678 --l_end_of_rec := p_end_of_rec;
2679
2680 return_value:= 'RS'||r_input_2
2681 ||r_input_3
2682 ||l_ssn
2683 ||l_first_name
2684 ||l_middle_name
2685 ||l_last_name
2686 ||l_suffix
2687 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
2688 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
2689 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
2690 ||rpad(substr(nvl(r_input_12,' '),1,2),2)
2691 ||rpad(substr(nvl(r_input_13,' '),1,5),5)
2692 ||rpad(substr(nvl(r_input_14,' '),1,4),9)
2693 ||r_input_15
2694 ||r_input_16
2695 ||r_input_17
2696 ||r_input_18
2697 ||r_input_19
2698 ||r_input_20
2699 ||r_input_21
2700 ||r_input_22
2701 ||r_input_23
2702 ||r_input_24
2703 ||'W'
2704 ||lpad(' ',4)
2705 ||r_input_25
2706 ||lpad(' ',6)
2707 ||r_input_26
2708 ||r_input_27
2709 ||r_input_28
2710 ||r_input_29
2711 ||r_input_30
2712 ||r_input_31
2713 ||r_input_32
2714 ||r_input_33
2715 ||r_input_34
2716 ||r_input_35
2717 ||lpad(' ',25)
2718 ||l_end_of_rec;
2719 ELSIF p_report_qualifier = 'WA_SQWL' THEN
2720 return_value:= l_wa_sqwl_outstring;
2721 ELSIF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2722
2723 return_value:= 'RS'||r_input_2
2724 ||r_input_3
2725 ||l_ssn
2726 ||l_first_name
2727 ||l_middle_name
2728 ||l_last_name
2729 ||l_suffix
2730 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
2731 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
2732 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
2733 ||rpad(substr(nvl(r_input_12,' '),1,2),2)
2734 ||rpad(substr(nvl(r_input_13,' '),1,5),5)
2735 ||rpad(substr(nvl(r_input_14,' '),1,4),9)
2736 ||r_input_15
2737 ||r_input_16
2738 ||r_input_17
2739 ||r_input_18
2740 ||r_input_19
2741 ||r_input_20
2742 ||r_input_21
2743 ||r_input_22
2744 ||r_input_23
2745 ||r_input_24
2746 ||r_input_25
2747 ||lpad(' ',6)
2748 ||r_input_26
2749 ||r_input_27
2750 ||r_input_28
2751 ||r_input_29
2752 ||r_input_30
2753 ||r_input_31
2754 ||r_input_32
2755 ||r_input_33
2756 ||r_input_34
2757 ||r_input_35
2758 ||l_last_field
2759 ||l_end_of_rec;
2760 ELSE
2761 return_value:= 'RS'||r_input_2
2762 ||r_input_3
2763 ||l_ssn
2764 ||l_first_name
2765 ||l_middle_name
2766 ||l_last_name
2767 ||l_suffix
2768 ||rpad(substr(nvl(r_input_9,' '),1,22),22)
2769 ||rpad(substr(nvl(r_input_10,' '),1,22),22)
2770 ||rpad(substr(nvl(r_input_11,' '),1,22),22)
2771 ||rpad(substr(nvl(r_input_12,' '),1,2),2)
2772 ||rpad(substr(nvl(r_input_13,' '),1,5),5)
2773 ||rpad(substr(nvl(r_input_14,' '),1,4),9)
2774 ||r_input_15
2775 ||r_input_16
2776 ||r_input_17
2777 ||r_input_18
2778 ||r_input_19
2779 ||r_input_20
2780 ||r_input_21
2781 ||r_input_22
2782 ||r_input_23
2783 ||r_input_24
2784 ||lpad(' ',5)
2785 ||r_input_25
2786 ||lpad(' ',6)
2787 ||r_input_26
2788 ||r_input_27
2789 ||r_input_28
2790 ||r_input_29
2791 ||r_input_30
2792 ||r_input_31
2793 ||r_input_32
2794 ||r_input_33
2795 ||r_input_34
2796 ||r_input_35
2797 ||l_last_field
2798 ||l_end_of_rec;
2799 END IF;
2800 hr_utility.trace('Length of return value = '||to_char(length(return_value)));
2801 --} End of formatting FLAT Type RS Record
2802 ELSIF p_input_40 = 'CSV' THEN
2803 --{ Start of formatting RS record in CSV format
2804
2805 IF p_report_qualifier = 'FL_SQWL' THEN ----Added for Bug#9356178
2806
2807 return_value := 'RS'
2808 ||','||r_input_2
2809 ||','||r_input_3
2810 ||','||l_ssn
2811 ||','||l_first_name
2812 ||','||l_middle_name
2813 ||','||l_last_name
2814 ||','||l_suffix
2815 ||','||rpad(substr(nvl(r_input_9,' '),1,22),22)
2816 ||','||rpad(substr(nvl(r_input_10,' '),1,22),22)
2817 ||','||rpad(substr(nvl(r_input_11,' '),1,22),22)
2818 ||','||rpad(substr(nvl(r_input_12,' '),1,2),2)
2819 ||','||rpad(substr(nvl(r_input_13,' '),1,5),5)
2820 ||','||rpad(substr(nvl(r_input_14,' '),1,4),4)
2821 ||','||lpad(' ',5)
2822 ||','||r_input_15
2823 ||','||r_input_16
2824 ||','||r_input_17
2825 ||','||r_input_18
2826 ||','||r_input_19
2827 ||','||r_input_20
2828 ||','||r_input_21
2829 ||','||r_input_22
2830 ||','||r_input_23
2831 ||','||r_input_24
2832 ||','||lpad(' ',6)
2833 ||','||r_input_25
2834 ||','||lpad(' ',6)
2835 ||','||r_input_26
2836 ||','||r_input_27
2837 ||','||r_input_28
2838 ||','||r_input_29
2839 ||','||r_input_30
2840 ||','||r_input_31
2841 ||','||r_input_32
2842 ||','||r_input_33
2843 ||','||r_input_34
2844 ||','||r_input_35
2845 ||','||lpad(' ',5)
2846 ||','||r_input_37
2847 ||','||r_input_38; /* Bug#14099390 */
2848
2849 ELSIF p_report_qualifier = 'NM_SQWL' THEN /* Bug# 14356614*/
2850
2851 return_value := 'RS'
2852 ||','||r_input_2
2853 ||','||r_input_3
2854 ||','||l_ssn
2855 ||','||l_first_name
2856 ||','||l_middle_name
2857 ||','||l_last_name
2858 ||','||l_suffix
2859 ||','||rpad(substr(nvl(r_input_9,' '),1,22),22)
2860 ||','||rpad(substr(nvl(r_input_10,' '),1,22),22)
2861 ||','||rpad(substr(nvl(r_input_11,' '),1,22),22)
2862 ||','||rpad(substr(nvl(r_input_12,' '),1,2),2)
2863 ||','||rpad(substr(nvl(r_input_13,' '),1,5),5)
2864 ||','||rpad(substr(nvl(r_input_14,' '),1,4),4)
2865 ||','||lpad(' ',5)
2866 ||','||r_input_15
2867 ||','||r_input_16
2868 ||','||r_input_17
2869 ||','||r_input_18
2870 ||','||r_input_19
2871 ||','||r_input_20
2872 ||','||r_input_21
2873 ||','||r_input_22
2874 ||','||r_input_23
2875 ||','||substr(r_input_24,1,8)
2876 ||','||substr(r_input_24,9,5)
2877 ||','||substr(r_input_25,1,8)
2878 ||','||substr(r_input_25,9,4)
2879 ||','||substr(r_input_25,13,1)
2880 ||','||substr(r_input_25,14,1)
2881 ||','||substr(r_input_25,15,1)
2882 ||','||substr(r_input_25,16,1)
2883 ||','||substr(r_input_25,17,4)||lpad(' ',6)
2884 ||','||r_input_26
2885 ||','||r_input_27
2886 ||','||r_input_28
2887 ||','||r_input_29
2888 ||','||r_input_30
2889 ||','||r_input_31
2890 ||','||r_input_32
2891 ||','||r_input_33
2892 ||','||substr(r_input_34,1,3)
2893 ||','||substr(r_input_34,4,1)
2894 ||','||lpad(' ',170); /* Bug# 16091833*/
2895
2896 ELSE
2897
2898 return_value := 'RS'
2899 ||','||r_input_2
2900 ||','||r_input_3
2901 ||','||l_ssn
2902 ||','||l_first_name
2903 ||','||l_middle_name
2904 ||','||l_last_name
2905 ||','||l_suffix
2906 ||','||rpad(substr(nvl(r_input_9,' '),1,22),22)
2907 ||','||rpad(substr(nvl(r_input_10,' '),1,22),22)
2908 ||','||rpad(substr(nvl(r_input_11,' '),1,22),22)
2909 ||','||rpad(substr(nvl(r_input_12,' '),1,2),2)
2910 ||','||rpad(substr(nvl(r_input_13,' '),1,5),5)
2911 ||','||rpad(substr(nvl(r_input_14,' '),1,4),4)
2912 ||','||lpad(' ',5)
2913 ||','||r_input_15
2914 ||','||r_input_16
2915 ||','||r_input_17
2916 ||','||r_input_18
2917 ||','||r_input_19
2918 ||','||r_input_20
2919 ||','||r_input_21
2920 ||','||r_input_22
2921 ||','||r_input_23
2922 ||','||r_input_24
2923 ||','||lpad(' ',6)
2924 ||','||r_input_25
2925 ||','||lpad(' ',6)
2926 ||','||r_input_26
2927 ||','||r_input_27
2928 ||','||r_input_28
2929 ||','||r_input_29
2930 ||','||r_input_30
2931 ||','||r_input_31
2932 ||','||r_input_32
2933 ||','||r_input_33
2934 ||','||r_input_34
2935 ||','||r_input_35
2936 ||','||lpad(' ',5);
2937
2938 END IF;
2939 --} End of formatting RS record in CSV format
2940 --
2941 ELSIF p_input_40 = 'BLANK' THEN
2942 --{ Start of formatting BALNK RS record used for audit report
2943 --
2944 return_value := ''
2945 ||','||' '
2946 ||','||' '
2947 ||','||' '
2948 ||','||' '
2949 ||','||' '
2950 ||','||' '
2951 ||','||' '
2952 ||','||' '
2953 ||','||' '
2954 ||','||' '
2955 ||','||' '
2956 ||','||' '
2957 ||','||' '
2958 ||','||lpad(' ',5)
2959 ||','||' '
2960 ||','||' '
2961 ||','||' '
2962 ||','||' '
2963 ||','||' '
2964 ||','||' '
2965 ||','||' '
2966 ||','||' '
2967 ||','||' '
2968 ||','||' '
2969 ||','||lpad(' ',6)
2970 ||','||' '
2971 ||','||lpad(' ',6)
2972 ||','||' '
2973 ||','||' '
2974 ||','||' '
2975 ||','||' '
2976 ||','||' '
2977 ||','||' '
2978 ||','||' '
2979 ||','||' '
2980 ||','||' '
2981 ||','||' '
2982 ||','||lpad(' ',5);
2983 --} End of formatting BLANK RS record used for audit report
2984 --
2985 END IF; -- p_input_40
2986 p_error := l_exclude_from_output_chk;
2987 ret_str_len:=length(return_value);
2988 return return_value;
2989 END format_SQWL_RS_record;
2990 -- End of Formatting RS Record for W2 Reporting
2991
2992 -- Formatting RT record for SQWL reporting
2993 --
2994 /*
2995 Record Identifier --> p_input_1
2996 Number of RW Records --> p_input_2
2997 Wages, Tips and other Compensation --> p_input_3
2998 Federal Income Tax Withheld --> p_input_4
2999 Social Security Wages --> p_input_5
3000 Social Security Tax Withheld --> p_input_6
3001 Medicare Wages And Tips --> p_input_7
3002 Medicare Tax Withheld --> p_input_8
3003 Social Security Tips --> p_input_9
3004 Advance Earned Income Credit --> p_input_10
3005 Dependent Care Benefits --> p_input_11
3006 Deferred Compensation Contributions to Section 401(k) --> p_input_12
3007 Deferred Compensation Contributions to Section 403(b) --> p_input_13
3008 Deferred Compensation Contributions to Section 408(k)(6) --> p_input_14
3009 Deferred Compensation Contributions to Section 457(b) --> p_input_15
3010 Deferred Compensation Contributions to Section 501(c)(18)(D) --> p_input_16
3011 Military EE''s Basic Quarters, Subsistence And Combat Pay --> p_input_17
3012 Non-Qual. Plan Sec.457 Distributions or Contributions --> p_input_18
3013 Non-Qual. Plan NOT Section 457 Distributions or Contributions --> p_input_19
3014 Employer Cost of Premiums for GTL> $50k --> p_input_20
3015 Income Tax Withheld by Third-Party Payer --> p_input_21
3016 Income from the Exercise of Nonqualified Stock Options --> p_input_22
3017
3018 For PuertoRico following input parameters were used
3019 W2 Govt EE Contributions --> p_input_23
3020 Total Fed Wages --> p_input_24
3021
3022 For PuertoRico following input parameters were used
3023 Cost of Pension or Annuity --> p_input_23
3024 Contributions to Qualified Plans --> p_input_24
3025 Cost Reimbursement --> p_input_25
3026 */
3027 FUNCTION format_SQWL_RT_record(
3028 p_effective_date IN varchar2,
3029 p_report_type IN varchar2,
3030 p_format IN varchar2,
3031 p_report_qualifier IN varchar2,
3032 p_record_name IN varchar2,
3033 p_input_1 IN varchar2,
3034 p_input_2 IN varchar2,
3035 p_input_3 IN varchar2,
3036 p_input_4 IN varchar2,
3037 p_input_5 IN varchar2,
3038 p_input_6 IN varchar2,
3039 p_input_7 IN varchar2,
3040 p_input_8 IN varchar2,
3041 p_input_9 IN varchar2,
3042 p_input_10 IN varchar2,
3043 p_input_11 IN varchar2,
3044 p_input_12 IN varchar2,
3045 p_input_13 IN varchar2,
3046 p_input_14 IN varchar2,
3047 p_input_15 IN varchar2,
3048 p_input_16 IN varchar2,
3049 p_input_17 IN varchar2,
3050 p_input_18 IN varchar2,
3051 p_input_19 IN varchar2,
3052 p_input_20 IN varchar2,
3053 p_input_21 IN varchar2,
3054 p_input_22 IN varchar2,
3055 p_input_23 IN varchar2,
3056 p_input_24 IN varchar2,
3057 p_input_25 IN varchar2,
3058 p_input_26 IN varchar2,
3059 p_input_27 IN varchar2,
3060 p_input_28 IN varchar2,
3061 p_input_29 IN varchar2,
3062 p_input_30 IN varchar2,
3063 p_input_31 IN varchar2,
3064 p_input_32 IN varchar2,
3065 p_input_33 IN varchar2,
3066 p_input_34 IN varchar2,
3067 p_input_35 IN varchar2,
3068 p_input_36 IN varchar2,
3069 p_input_37 IN varchar2,
3070 p_input_38 IN varchar2,
3071 p_input_39 IN varchar2,
3072 p_input_40 IN varchar2,
3073 p_validate IN varchar2,
3074 p_exclude_from_output OUT nocopy varchar2,
3075 sp_out_1 OUT nocopy varchar2,
3076 sp_out_2 OUT nocopy varchar2,
3077 sp_out_3 OUT nocopy varchar2,
3078 sp_out_4 OUT nocopy varchar2,
3079 sp_out_5 OUT nocopy varchar2,
3080 ret_str_len OUT nocopy number,
3081 p_error OUT nocopy boolean
3082 ) RETURN VARCHAR2
3083 IS
3084 l_input_2 varchar2(100);
3085 return_value varchar2(32767);
3086 l_exclude_from_output_chk boolean;
3087 l_rt_end_of_rec varchar2(200);
3088 l_records number(10);
3089 l_end_of_rec varchar2(20);
3090 p_end_of_rec varchar2(20);
3091
3092 /*bug 9913439 */
3093 l_sui_total_taxable_wages number;
3094 l_sui_tax_rate number;
3095 l_sui_tax_due number;
3096
3097 TYPE function_columns IS RECORD(
3098 p_parameter_name varchar2(100),
3099 p_parameter_value varchar2(100),
3100 p_output_value varchar2(100)
3101 );
3102 function_parameter_rec function_columns;
3103 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
3104 INDEX BY BINARY_INTEGER;
3105 parameter_record input_parameter_record;
3106
3107 BEGIN
3108 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3109 hr_utility.trace('RT Record Formatting started for SQWL reporting ');
3110
3111 -- Validation Starts
3112 If p_input_40='FLAT' THEN
3113 --{
3114 IF p_report_qualifier = 'FL_SQWL' THEN
3115 --{
3116 parameter_record(1).p_parameter_name:= 'Wages,Tips And Other Compensation';
3117 parameter_record(1).p_parameter_value:=p_input_3;
3118
3119 parameter_record(2).p_parameter_name:= 'Total Tax Due';
3120 parameter_record(2).p_parameter_value:=p_input_22;
3121
3122 parameter_record(3).p_parameter_name:= 'Corrected Worker Ist Month';
3123 parameter_record(3).p_parameter_value:=p_input_23;
3124
3125 parameter_record(4).p_parameter_name:= 'Corrected Worker IInd Month';
3126 parameter_record(4).p_parameter_value:=p_input_24;
3127
3128 parameter_record(5).p_parameter_name:= 'Corrected Worker IIIrd Month';
3129 parameter_record(5).p_parameter_value:=p_input_25;
3130
3131 l_records :=5;
3132 l_end_of_rec := p_end_of_rec;
3133
3134 ELSIF p_report_qualifier = 'UT_SQWL' THEN /*bug 9913439 */
3135 parameter_record(1).p_parameter_name:= 'Wages,Tips And Other Compensation';
3136 parameter_record(1).p_parameter_value:=p_input_3;
3137
3138 parameter_record(2).p_parameter_name:= 'Total Excess Wages';
3139 parameter_record(2).p_parameter_value:=p_input_22;
3140
3141 parameter_record(3).p_parameter_name:= 'Total Taxable Wages';
3142 parameter_record(3).p_parameter_value:=p_input_23;
3143
3144 parameter_record(4).p_parameter_name:= 'Total Month1 Count';
3145 parameter_record(4).p_parameter_value:=p_input_24;
3146
3147 parameter_record(5).p_parameter_name:= 'Total Month2 Count';
3148 parameter_record(5).p_parameter_value:=p_input_25;
3149
3150 parameter_record(6).p_parameter_name:= 'Total Month3 Count';
3151 parameter_record(6).p_parameter_value:=p_input_26;
3152
3153
3154 l_sui_total_taxable_wages :=to_number(p_input_23)/100;
3155 l_sui_tax_rate := trunc(to_number(nvl(p_input_27,'0')),1);
3156 l_sui_tax_due := l_sui_total_taxable_wages * (l_sui_tax_rate/100);
3157
3158 parameter_record(7).p_parameter_name:= 'UT Tax Rate';
3159 parameter_record(7).p_parameter_value:=lpad(to_char(l_sui_tax_rate*10),5,'0');
3160
3161 hr_utility.trace('parameter_record(7).p_parameter_value '||parameter_record(7).p_parameter_value);
3162
3163 parameter_record(8).p_parameter_name:= 'UT Tax Due';
3164 parameter_record(8).p_parameter_value:= lpad(to_char(l_sui_tax_due*100),9,'0');
3165
3166 hr_utility.trace('parameter_record(8).p_parameter_value '||parameter_record(8).p_parameter_value);
3167 --}
3168 l_records :=8;
3169 l_end_of_rec := p_end_of_rec;
3170 --}
3171 ELSE
3172 --{
3173 parameter_record(1).p_parameter_name:= ' Wages,Tips And Other Compensation';
3174 parameter_record(1).p_parameter_value:=p_input_3;
3175
3176 parameter_record(2).p_parameter_name:= ' Federal Income Tax Withheld';
3177 parameter_record(2).p_parameter_value:=p_input_4;
3178
3179 parameter_record(3).p_parameter_name:= 'Social Security Wages';
3180 parameter_record(3).p_parameter_value:=p_input_5;
3181
3182 parameter_record(4).p_parameter_name:= ' Social Security Tax Withheld';
3183 parameter_record(4).p_parameter_value:=p_input_6;
3184
3185 parameter_record(5).p_parameter_name:= 'Medicare Wages And Tips';
3186 parameter_record(5).p_parameter_value:=p_input_7;
3187
3188 parameter_record(6).p_parameter_name:= 'Medicare Tax Withheld';
3189 parameter_record(6).p_parameter_value:=p_input_8;
3190
3191 parameter_record(7).p_parameter_name:= 'Social Security Tips';
3192 parameter_record(7).p_parameter_value:=p_input_9;
3193
3194 parameter_record(8).p_parameter_name:= 'Advance Earned Income Credit';
3195 parameter_record(8).p_parameter_value:=p_input_10;
3196
3197 parameter_record(9).p_parameter_name:= 'Dependent Care Benefits';
3198 parameter_record(9).p_parameter_value:=p_input_11;
3199
3200 parameter_record(10).p_parameter_name:= 'Deferred Comp Contr. to Sec 401(k)';
3201 parameter_record(10).p_parameter_value:=p_input_12;
3202
3203 parameter_record(11).p_parameter_name:= 'Deferred Comp Contr. to Sec 403(b)';
3204 parameter_record(11).p_parameter_value:=p_input_13;
3205
3206 parameter_record(12).p_parameter_name:= 'Deferred Comp Contr. to Sec 408(k)(6)';
3207 parameter_record(12).p_parameter_value:=p_input_14;
3208
3209 parameter_record(13).p_parameter_name:= 'Deferred Comp Contr. to Sec 457(b)';
3210 parameter_record(13).p_parameter_value:=p_input_15;
3211
3212 parameter_record(14).p_parameter_name:= 'Deferred Comp Contr. to Sec 501(c)';
3213 parameter_record(14).p_parameter_value:=p_input_16;
3214
3215 /* Following field is commented to fix bug # 2297587
3216 parameter_record(15).p_parameter_name:= 'Military Combat Pay';
3217 parameter_record(15).p_parameter_value:=p_input_17;
3218 */
3219 parameter_record(15).p_parameter_name:= 'Non-Qual. plan Sec 457';
3220 parameter_record(15).p_parameter_value:=p_input_18;
3221
3222 parameter_record(16).p_parameter_name:= 'Non-Qual. plan NOT Sec 457';
3223 parameter_record(16).p_parameter_value:=p_input_19;
3224
3225 parameter_record(17).p_parameter_name:= 'Employer cost of premiun';
3226 parameter_record(17).p_parameter_value:=p_input_20;
3227
3228 parameter_record(18).p_parameter_name:= 'Income tax withheld by 3rd party payer';
3229 parameter_record(18).p_parameter_value:=p_input_21;
3230
3231 parameter_record(19).p_parameter_name:= 'Income from nonqualified stock option';
3232 parameter_record(19).p_parameter_value:=p_input_22;
3233 l_records := 19;
3234
3235 END IF;
3236 -- Validating above data based on the report_qualifier and number entries
3237 --
3238 FOR i in 1..l_records
3239 LOOP
3240 parameter_record(i).p_output_value :=
3241 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
3242 p_report_type,
3243 p_format,
3244 p_report_qualifier,
3245 p_record_name,
3246 'NEG_CHECK',
3247 parameter_record(i).p_parameter_value,
3248 parameter_record(i).p_parameter_name,
3249 p_input_39,
3250 null,
3251 p_validate,
3252 p_exclude_from_output,
3253 sp_out_1,
3254 sp_out_2);
3255 IF p_exclude_from_output = 'Y' THEN
3256 l_exclude_from_output_chk := TRUE;
3257 END IF;
3258 hr_utility.trace(parameter_record(i).p_parameter_name||' = '
3259 ||parameter_record(i).p_output_value);
3260 hr_utility.trace('After validating the parameter for -ve Value p_exclude_from_output = '||p_exclude_from_output);
3261 END LOOP;
3262 -- Validation Ends here
3263
3264 -- For Alabama, Zero Fill Pos : 250 - 264 /* Employer Contributions to Health Savings Account */
3265 -- Zero Fill Pos : 280 - 294
3266 -- Zero Fill Pos : 355 - 369
3267 IF p_report_qualifier = 'AL_SQWL' THEN
3268 parameter_record(15).p_output_value := rpad(parameter_record(15).p_output_value,30,'0') ;
3269 parameter_record(16).p_output_value := rpad(parameter_record(16).p_output_value,30,'0') ;
3270 parameter_record(19).p_output_value := rpad(parameter_record(19).p_output_value,30,'0') ;
3271 END IF ;
3272 --
3273 -- Formatting RT Record depending on report_qualifier
3274 --
3275 -- Formatting No of Employee Wage/State Record
3276 l_input_2:= lpad(substr(nvl(p_input_2,'0'),1,7),7,0);
3277
3278 IF p_report_qualifier = 'SC_SQWL' THEN /*2274381.*/
3279 --{
3280 return_value := 'RT'||l_input_2
3281 ||lpad(' ',503);
3282 --}
3283 ELSIF p_report_qualifier = 'FL_SQWL' THEN
3284 --{
3285 return_value := 'RT'||lpad(' ',7)
3286 ||lpad(substr(nvl(parameter_record(1).p_output_value,'0'),1,15),15)
3287 --||lpad(' ',456)
3288 ||lpad(' ',195) /* Bug# 4867893 */
3289 ||lpad('0',15,'0')
3290 ||lpad(' ',246)
3291 ||lpad(substr(nvl(parameter_record(2).p_output_value,'0'),1,11),11)
3292 ||lpad(substr(nvl(parameter_record(3).p_output_value,'0'),1,7),7)
3293 ||lpad(substr(nvl(parameter_record(4).p_output_value,'0'),1,7),7)
3294 ||lpad(substr(nvl(parameter_record(5).p_output_value,'0'),1,7),7)
3295 ||l_end_of_rec;
3296
3297 ELSIF p_report_qualifier = 'UT_SQWL' THEN /*bug 9913439 */
3298 --{
3299 hr_utility.trace('parameter_record7.p_output_value '||parameter_record(7).p_output_value);
3300 return_value := 'RT'||l_input_2
3301 ||lpad(parameter_record(1).p_parameter_value,15,'0')
3302 --||lpad(' ',456)
3303 ||lpad(' ',375)
3304 ||lpad(nvl(parameter_record(2).p_output_value,'0'),15,'0')
3305 ||lpad(nvl(parameter_record(3).p_output_value,'0'),15,'0')
3306 ||lpad(parameter_record(7).p_parameter_value,5,'0')
3307 ||lpad(parameter_record(8).p_parameter_value,9,'0')
3308 ||lpad(substr(nvl(parameter_record(4).p_parameter_value,'0'),1,5),5,'0')
3309 ||lpad(substr(nvl(parameter_record(5).p_parameter_value,'0'),1,5),5,'0')
3310 ||lpad(substr(nvl(parameter_record(6).p_parameter_value,'0'),1,5),5,'0')
3311 ||lpad(' ',54);
3312
3313 ELSIF p_report_qualifier = 'VA_SQWL' THEN /* Bug# 6057156 */
3314 --{
3315 return_value := 'RT'||lpad(' ',510);
3316 --}
3317 --}
3318 ELSE
3319 --{
3320 return_value := 'RT'||l_input_2
3321 ||parameter_record(1).p_output_value
3322 ||parameter_record(2).p_output_value
3323 ||parameter_record(3).p_output_value
3324 ||parameter_record(4).p_output_value
3325 ||parameter_record(5).p_output_value
3326 ||parameter_record(6).p_output_value
3327 ||parameter_record(7).p_output_value
3328 ||parameter_record(8).p_output_value
3329 ||parameter_record(9).p_output_value
3330 ||parameter_record(10).p_output_value
3331 ||parameter_record(11).p_output_value
3332 ||parameter_record(12).p_output_value
3333 ||parameter_record(13).p_output_value
3334 ||parameter_record(14).p_output_value
3335 -- commented to fix bug # 2297587 ||parameter_record(15).p_output_value
3336 ||lpad('0',15,'0') /* Bug# 4867893 */
3337 ||rpad(parameter_record(15).p_output_value,30)
3338 ||rpad(parameter_record(16).p_output_value,45)
3339 ||parameter_record(17).p_output_value
3340 ||parameter_record(18).p_output_value
3341 ||rpad(parameter_record(19).p_output_value,173);
3342 --}
3343 END IF;
3344 ret_str_len:=length(return_value);
3345 --}
3346 ELSIF p_input_40 = 'CSV' THEN
3347 --{
3348 l_rt_end_of_rec := lpad(' ',158);
3349 return_value := 'RT'||','||l_input_2
3350 ||','||p_input_3
3351 ||','||p_input_4
3352 ||','||p_input_5
3353 ||','||p_input_6
3354 ||','||p_input_7
3355 ||','||p_input_8
3356 ||','||p_input_9
3357 ||','||p_input_10
3358 ||','||p_input_11
3359 ||','||p_input_12
3360 ||','||p_input_13
3361 ||','||p_input_14
3362 ||','||p_input_15
3363 ||','||p_input_16
3364 -- commented to fix bug # 2297587 ||','||p_input_17
3365 ||','||lpad(' ',15)
3366 ||','||p_input_18
3367 ||','||lpad(' ',15)
3368 ||','||p_input_19
3369 ||','||lpad(' ',30)
3370 ||','||p_input_20
3371 ||','||p_input_21
3372 ||','||p_input_22
3373 ||','||l_rt_end_of_rec;
3374 --}
3375 END IF; -- p_input_40 (i.e. FLAT, CSV)
3376 p_error := l_exclude_from_output_chk;
3377 ret_str_len:=length(return_value);
3378 return return_value;
3379 END format_SQWL_RT_record; -- End of Formatting SQWL RT record
3380
3381 -- Formatting RSSUMM record for SQWL reporting
3382 --
3383 /*
3384 Record Identifier --> p_input_1
3385 State Code --> p_input_2
3386 Taxing Entity Code/State Rec Type --> p_input_3
3387 Federal Tax ID Number --> p_input_4
3388 State Employer Acct Number --> p_input_5
3389 Reporting Period --> p_input_6
3390 Total Number of Workers on Report --> p_input_7
3391 Total Wages Paid This Quarter (Including Tips) --> p_input_8
3392 Nontaxable Wage --> p_input_9
3393 Taxable Wages Paid This Quarter --> p_input_10
3394 Month(1) employment for employer --> p_input_11
3395 Month(2) employment for employer --> p_input_12
3396 Month(3) employment for employer --> p_input_13
3397 No Workers/No Wages Indicator --> p_input_14
3398 Employer Name --> p_input_15
3399
3400 */
3401 FUNCTION format_SQWL_RSSUMM_record(
3402 p_effective_date IN varchar2,
3403 p_report_type IN varchar2,
3404 p_format IN varchar2,
3405 p_report_qualifier IN varchar2,
3406 p_record_name IN varchar2,
3407 p_input_1 IN varchar2,
3408 p_input_2 IN varchar2,
3409 p_input_3 IN varchar2,
3410 p_input_4 IN varchar2,
3411 p_input_5 IN varchar2,
3412 p_input_6 IN varchar2,
3413 p_input_7 IN varchar2,
3414 p_input_8 IN varchar2,
3415 p_input_9 IN varchar2,
3416 p_input_10 IN varchar2,
3417 p_input_11 IN varchar2,
3418 p_input_12 IN varchar2,
3419 p_input_13 IN varchar2,
3420 p_input_14 IN varchar2,
3421 p_input_15 IN varchar2,
3422 p_input_16 IN varchar2,
3423 p_input_17 IN varchar2,
3424 p_input_18 IN varchar2,
3425 p_input_19 IN varchar2,
3426 p_input_20 IN varchar2,
3427 p_input_21 IN varchar2,
3428 p_input_22 IN varchar2,
3429 p_input_23 IN varchar2,
3430 p_input_24 IN varchar2,
3431 p_input_25 IN varchar2,
3432 p_input_26 IN varchar2,
3433 p_input_27 IN varchar2,
3434 p_input_28 IN varchar2,
3435 p_input_29 IN varchar2,
3436 p_input_30 IN varchar2,
3437 p_input_31 IN varchar2,
3438 p_input_32 IN varchar2,
3439 p_input_33 IN varchar2,
3440 p_input_34 IN varchar2,
3441 p_input_35 IN varchar2,
3442 p_input_36 IN varchar2,
3443 p_input_37 IN varchar2,
3444 p_input_38 IN varchar2,
3445 p_input_39 IN varchar2,
3446 p_input_40 IN varchar2,
3447 p_validate IN varchar2,
3448 p_exclude_from_output OUT nocopy varchar2,
3449 sp_out_1 OUT nocopy varchar2,
3450 sp_out_2 OUT nocopy varchar2,
3451 sp_out_3 OUT nocopy varchar2,
3452 sp_out_4 OUT nocopy varchar2,
3453 sp_out_5 OUT nocopy varchar2,
3454 ret_str_len OUT nocopy number,
3455 p_error OUT nocopy boolean
3456 ) RETURN VARCHAR2
3457 IS
3458 l_input_2 varchar2(100);
3459 l_input_3 varchar2(100);
3460 l_input_6 varchar2(100);
3461 l_input_7 varchar2(100);
3462 l_input_11 varchar2(100);
3463 l_input_12 varchar2(100);
3464 l_input_13 varchar2(100);
3465 l_noworker_nowage_ind varchar2(100);
3466 return_value varchar2(32767);
3467 l_exclude_from_output_chk boolean;
3468 l_rt_end_of_rec varchar2(200);
3469 l_records number(10);
3470 l_end_of_rec varchar2(20);
3471 l_emp_ein varchar2(100);
3472 l_other_ein varchar2(100);
3473 p_end_of_rec varchar2(20);
3474
3475 TYPE function_columns IS RECORD(
3476 p_parameter_name varchar2(100),
3477 p_parameter_value varchar2(100),
3478 p_output_value varchar2(100)
3479 );
3480 function_parameter_rec function_columns;
3481 TYPE input_parameter_record IS TABLE OF function_parameter_rec%TYPE
3482 INDEX BY BINARY_INTEGER;
3483 parameter_record input_parameter_record;
3484
3485 BEGIN
3486 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3487 hr_utility.trace('RSSUMM Record Formatting started for SQWL reporting ');
3488
3489 l_emp_ein := p_input_4;
3490 l_other_ein := p_input_5;
3491 hr_utility.trace('Before Valaditing Values are ...');
3492 hr_utility.trace('p_input_1 = '||p_input_1);
3493 hr_utility.trace('p_input_2 = '||p_input_2);
3494 hr_utility.trace('p_input_3 = '||p_input_3);
3495 hr_utility.trace('p_input_4 = '||p_input_4);
3496 hr_utility.trace('p_input_5 = '||p_input_5);
3497 hr_utility.trace('p_input_6 = '||p_input_6);
3498 hr_utility.trace('p_input_7 = '||p_input_7);
3499 hr_utility.trace('p_input_8 = '||p_input_8);
3500 hr_utility.trace('p_input_9 = '||p_input_9);
3501 hr_utility.trace('p_input_10 = '||p_input_10);
3502 hr_utility.trace('p_input_11 = '||p_input_11);
3503 hr_utility.trace('p_input_12 = '||p_input_12);
3504 hr_utility.trace('p_input_13 = '||p_input_13);
3505 hr_utility.trace('p_input_14 = '||p_input_14);
3506 hr_utility.trace('p_input_15 = '||p_input_15);
3507 -- Validation Starts
3508 If p_input_40='FLAT' THEN
3509 --{
3510 -- These validation are used only for mf file only.
3511 -- not for any of the audit report
3512 --
3513 hr_utility.trace('before data_validation of ein');
3514 l_emp_ein :=
3515 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
3516 p_report_type,
3517 p_format,
3518 p_report_qualifier,
3519 p_record_name,
3520 'EIN',
3521 l_emp_ein,
3522 'Employer EIN',
3523 p_input_15,
3524 null,
3525 p_validate,
3526 p_exclude_from_output,
3527 sp_out_1,
3528 sp_out_2);
3529 IF p_exclude_from_output = 'Y' THEN
3530 l_exclude_from_output_chk := TRUE;
3531 END IF;
3532
3533 IF l_other_ein is not null then
3534 --{
3535 hr_utility.trace('before data_validation of Other EIN');
3536 l_other_ein :=
3537 pay_us_reporting_utils_pkg.data_validation(p_effective_date,
3538 p_report_type,
3539 p_format,
3540 p_report_qualifier,
3541 p_record_name,
3542 'EIN',
3543 l_other_ein,
3544 'Other EIN',
3545 p_input_15,
3546 null,
3547 p_validate,
3548 p_exclude_from_output,
3549 sp_out_1,
3550 sp_out_2);
3551 IF p_exclude_from_output = 'Y' THEN
3552 l_exclude_from_output_chk := TRUE;
3553 END IF;
3554 --}
3555 END IF;
3556
3557 IF p_report_qualifier = 'NV_SQWL' THEN
3558 --{
3559 parameter_record(1).p_parameter_name:= 'Wages,Tips And Other Compensation';
3560 parameter_record(1).p_parameter_value:=p_input_8;
3561
3562 parameter_record(2).p_parameter_name:= ' Nontaxable Wage';
3563 parameter_record(2).p_parameter_value:=p_input_9;
3564
3565 parameter_record(3).p_parameter_name:= 'Taxable Wages';
3566 parameter_record(3).p_parameter_value:=p_input_10;
3567
3568 l_records := 3;
3569 --}
3570 END IF;
3571 -- Validating above data based on the report_qualifier and number entries
3572 --
3573 FOR i in 1..l_records
3574 LOOP
3575 hr_utility.trace(parameter_record(i).p_parameter_name||' = '
3576 ||parameter_record(i).p_parameter_value);
3577
3578 hr_utility.trace('value of(i)|| = '|| to_char(i));
3579
3580 parameter_record(i).p_output_value :=
3581 pay_us_reporting_utils_pkg.data_validation( p_effective_date,
3582 p_report_type,
3583 p_format,
3584 p_report_qualifier,
3585 p_record_name,
3586 'NEG_CHECK',
3587 parameter_record(i).p_parameter_value,
3588 parameter_record(i).p_parameter_name,
3589 p_input_39,
3590 null,
3591 p_validate,
3592 p_exclude_from_output,
3593 sp_out_1,
3594 sp_out_2);
3595 IF p_exclude_from_output = 'Y' THEN
3596 l_exclude_from_output_chk := TRUE;
3597 END IF;
3598 hr_utility.trace(parameter_record(i).p_parameter_name||' = '
3599 ||parameter_record(i).p_output_value);
3600 hr_utility.trace('After validating the parameter for -ve Value p_exclude_from_output = '||p_exclude_from_output);
3601 END LOOP;
3602 -- Validation Ends here
3603 --
3604 -- Formatting RSSUMM Record depending on report_qualifier
3605 --
3606 -- Formatting No of Employee Wage/State Record
3607 l_input_2:= lpad(substr(nvl(p_input_2,'0'),1,2),2,0);
3608
3609 -- Taxing Entity Code/State Rec Type
3610 l_input_3:= lpad(substr(nvl(p_input_3,'SUMM'),1,4),4);
3611
3612 -- No Workers/No Wages Indicator
3613 if p_input_8 <> 0 AND (p_input_7 <> 0 OR p_input_8 <> 0)then
3614 l_noworker_nowage_ind := ' ';
3615 else
3616 l_noworker_nowage_ind := 'Y';
3617 end if;
3618 l_noworker_nowage_ind := lpad(substr(l_noworker_nowage_ind,1,1),1);
3619
3620 -- Len: 6 Desc: Reporting Period
3621 l_input_6:= lpad(substr(nvl(p_input_6,'0'),1,6),6,0);
3622
3623 -- Len: 7 Desc: Total Number of Workers on Report
3624 l_input_7:= lpad(substr(nvl(p_input_7,'0'),1,7),7,0);
3625
3626 -- Len: 7 Desc: Month(1) employment for employer
3627 l_input_11:= lpad(substr(nvl(p_input_11,'0'),1,7),7,0);
3628
3629 -- Len: 7 Desc: Month(2) employment for employer
3630 l_input_12:= lpad(substr(nvl(p_input_12,'0'),1,7),7,0);
3631
3632 -- Len: 7 Desc: Month(3) employment for employer
3633 l_input_13:= lpad(substr(nvl(p_input_13,'0'),1,7),7,0);
3634
3635
3636
3637 IF p_report_qualifier = 'NV_SQWL' THEN
3638 --{
3639 return_value := 'RS'||l_input_2
3640 ||l_input_3
3641 ||lpad(' ',2)
3642 ||l_emp_ein
3643 ||lpad(' ',1)
3644 ||l_other_ein
3645 ||lpad(' ',1)
3646 ||l_input_6
3647 ||lpad(' ',1)
3648 ||l_input_7
3649 ||lpad(' ',1)
3650 ||parameter_record(1).p_output_value
3651 ||lpad(' ',1)
3652 ||parameter_record(2).p_output_value
3653 ||lpad(' ',1)
3654 ||parameter_record(3).p_output_value
3655 ||lpad(' ',1)
3656 ||lpad(' ',13)
3657 ||l_input_11
3658 ||lpad(' ',1)
3659 ||l_input_12
3660 ||lpad(' ',1)
3661 ||l_input_13
3662 ||lpad(' ',1)
3663 ||l_noworker_nowage_ind
3664 ||lpad(' ',384);
3665 --}
3666 END IF;
3667 ret_str_len:=length(return_value);
3668 hr_utility.trace(' Length of ret_str_len = ' || to_char(ret_str_len));
3669 --}
3670 ELSIF p_input_40 = 'CSV' THEN
3671 --{
3672 l_rt_end_of_rec := lpad(' ',158);
3673 --{
3674 return_value := 'RS'||','||l_input_2
3675 ||','||l_input_3
3676 ||','||lpad(' ',2)
3677 ||','||l_emp_ein
3678 ||','||lpad(' ',1)
3679 ||','||l_other_ein
3680 ||','||lpad(' ',1)
3681 ||','||l_input_6
3682 ||','||lpad(' ',1)
3683 ||','||l_input_7
3684 ||','||lpad(' ',1)
3685 ||','||parameter_record(1).p_output_value
3686 ||','||lpad(' ',1)
3687 ||','||parameter_record(2).p_output_value
3688 ||','||lpad(' ',1)
3689 ||','||parameter_record(3).p_output_value
3690 ||','||lpad(' ',1)
3691 ||','||lpad(' ',13)
3692 ||','||l_input_11
3693 ||','||lpad(' ',1)
3694 ||','||l_input_12
3695 ||','||lpad(' ',1)
3696 ||','||l_input_13
3697 ||','||lpad(' ',1)
3698 ||','||l_noworker_nowage_ind
3699 ||','||lpad(' ',384)
3700 ||','||l_rt_end_of_rec;
3701 --}
3702 END IF; -- p_input_40 (i.e. FLAT, CSV)
3703 p_error := l_exclude_from_output_chk;
3704 ret_str_len:=length(return_value);
3705 return return_value;
3706 END format_SQWL_RSSUMM_record; -- End of Formatting SQWL RSSUMM record
3707
3708 --
3709 -- Formatting RST record for SQWL reporting
3710 --
3711 /*
3712 Effective Date --> p_effective_date
3713 Report Type (i.e.W2 ) --> p_report_type
3714 Report Format --> p_format
3715 Report Qualifier --> p_report_qualifier
3716 Record Name (i.e. RST) --> p_record_name
3717 Record Identifier --> p_record_identifier
3718 Number of Employee --> p_total_no_employee
3719 SIT Wages --> p_sit_wages
3720 SIT Taxes --> p_sit_taxes
3721 1st Month employed no of employee --> p_month1_no_employee
3722 2nd Month employed no of employee --> p_month1_no_employee
3723 3rd Month employed no of employee --> p_month1_no_employee
3724 Report Format Mode (FLAT, CSV) --> p_format_mode
3725 Validation Flag --> p_validate
3726 Exclude Record from mag file --> p_exclude_from_output
3727 Return Record Length --> ret_str_len
3728 */
3729
3730 FUNCTION format_SQWL_RST_record(
3731 p_effective_date IN varchar2,
3732 p_report_type IN varchar2,
3733 p_format IN varchar2,
3734 p_report_qualifier IN varchar2,
3735 p_record_name IN varchar2,
3736 p_record_identifier IN varchar2,
3737 p_total_no_of_employee IN varchar2,
3738 p_state_code IN varchar2,
3739 p_sui_reduction_wages IN varchar2,
3740 p_sit_wages IN varchar2,
3741 p_sit_tax IN varchar2,
3742 p_month1_no_employee IN varchar2,
3743 p_month2_no_employee IN varchar2,
3744 p_month3_no_employee IN varchar2,
3745 p_format_mode IN varchar2,
3746 p_validate IN varchar2,
3747 p_exclude_from_output OUT nocopy varchar2,
3748 ret_str_len OUT nocopy number
3749 ) RETURN VARCHAR2
3750 IS
3751 return_value varchar2(32767);
3752 r_total_no_of_employee varchar2(300); -- r_input_2
3753 r_state_code varchar2(300); -- r_input_3
3754 r_sui_reduction_wages varchar2(300); -- r_input_4
3755 r_sit_wages varchar2(300); -- r_input_5
3756 r_sit_tax varchar2(300); -- r_input_6
3757 r_month1_no_employee varchar2(300); -- r_input_7
3758 r_month2_no_employee varchar2(300); -- r_input_8
3759 r_month3_no_employee varchar2(300); -- r_input_9
3760 l_state_length number(10);
3761
3762 BEGIN
3763 IF p_record_name = 'RST' THEN
3764 -- Assigning parameter values to local variables
3765 --
3766 r_total_no_of_employee := p_total_no_of_employee;
3767 r_state_code := p_state_code;
3768 r_sui_reduction_wages := p_sui_reduction_wages;
3769 r_sit_wages := p_sit_wages;
3770 r_sit_tax := p_sit_tax;
3771 r_month1_no_employee := p_month1_no_employee;
3772 r_month2_no_employee := p_month2_no_employee;
3773 r_month3_no_employee := p_month3_no_employee;
3774 hr_utility.trace('Report Qualifier before Formatting RST Record '
3775 ||p_report_qualifier);
3776 /*r_input_2 := lpad(' ',7);*/
3777 r_total_no_of_employee := lpad(substr(nvl(p_total_no_of_employee,'0'),1,7),7,0);
3778 r_sui_reduction_wages := lpad(nvl(r_sui_reduction_wages,'0'), 14, '0');
3779 r_sit_wages := lpad(nvl(r_sit_wages,'0'), 14, '0');
3780 l_state_length := length(replace(p_state_code,' ')); /* Fix for State code.*/
3781 IF l_state_length < 2 THEN
3782 r_state_code := lpad(replace(nvl(p_state_code,' '),' '),2,0);
3783 r_sit_tax := lpad(nvl(r_sit_tax,'0'), 14, '0');
3784 r_month1_no_employee := lpad(nvl(r_month1_no_employee,'0'), 7, '0');
3785 r_month2_no_employee := lpad(nvl(r_month2_no_employee,'0'), 7, '0');
3786 r_month3_no_employee := lpad(nvl(r_month3_no_employee,'0'), 7, '0');
3787 END IF;
3788 return_value:= 'RST'
3789 ||r_total_no_of_employee --r_input_2
3790 ||' '
3791 ||r_state_code
3792 ||' '
3793 ||r_sui_reduction_wages
3794 ||' '
3795 ||r_sit_wages
3796 ||' '
3797 ||r_sit_tax
3798 ||' '
3799 ||r_month1_no_employee
3800 ||' '
3801 ||r_month2_no_employee
3802 ||' '
3803 ||r_month3_no_employee
3804 ||lpad(' ',430);
3805 END IF; --p_record_name
3806 ret_str_len:=length(return_value);
3807 return return_value;
3808 END format_SQWL_RST_record;
3809 -- End of formatting RST record used for SQWL reporting
3810 --
3811
3812
3813 --
3814 -- Formatting RU record for SQWL reporting
3815 --
3816 /*
3817 Effective Date --> p_effective_date
3818 Report Type (i.e.W2 ) --> p_report_type
3819 Report Format --> p_format
3820 Report Qualifier --> p_report_qualifier
3821 Record Name (i.e. RU) --> p_record_name
3822 Record Identifier --> p_record_identifier
3823 Number of RS Records --> p_number_of_RS_record
3824 Record Format Mode (FLAT, CSV etc.) --> p_record_format_mode
3825 Exclude RU record from .mf file --> p_exclude_from_output
3826
3827 */
3828 FUNCTION format_SQWL_RU_record(
3829 p_effective_date IN varchar2,
3830 p_report_type IN varchar2,
3831 p_format IN varchar2,
3832 p_report_qualifier IN varchar2,
3833 p_record_name IN varchar2,
3834 p_record_identifier IN varchar2,
3835 p_number_of_RS_record IN varchar2,
3836 p_record_format_mode IN varchar2,
3837 p_exclude_from_output OUT nocopy varchar2,
3838 ret_str_len OUT nocopy number
3839 ) RETURN VARCHAR2
3840 IS
3841 return_value varchar2(32767);
3842 l_no_of_rs_record varchar2(300);
3843 p_end_of_rec varchar2(20);
3844 l_end_of_rec varchar2(20) ;
3845
3846 BEGIN
3847 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3848 hr_utility.trace('Formatting RU Record');
3849 hr_utility.trace('RU Record Format Mode '||p_record_format_mode);
3850 l_no_of_rs_record := lpad(substr(nvl(p_number_of_RS_record,'0'),1,7),7,0);
3851
3852 /* Bug 3527986 Removed Delimiters fro NY */
3853 /*
3854 IF ((p_report_qualifier = 'NY_SQWL') and
3855 (p_record_format_mode = 'FLAT' )) THEN
3856 return_value:='RU'||l_no_of_rs_record||lpad(' ',503)||p_end_of_rec;
3857 ELSIF ((p_report_qualifier = 'NY_SQWL') and
3858 (p_record_format_mode = 'CSV' )) THEN
3859 return_value:='RU'||','||l_no_of_rs_record||lpad(' ',503);
3860 END IF; -- p_report_qualifier
3861 */
3862 IF ((p_report_qualifier = 'NY_SQWL') and
3863 (p_record_format_mode = 'FLAT' )) THEN
3864 return_value:='RU'||l_no_of_rs_record||lpad(' ',503)||l_end_of_rec;
3865 ELSIF ((p_report_qualifier = 'NY_SQWL') and
3866 (p_record_format_mode = 'CSV' )) THEN
3867 return_value:='RU'||','||l_no_of_rs_record||lpad(' ',503);
3868 END IF; -- p_report_qualifier
3869
3870 ret_str_len:=length(return_value);
3871 return return_value;
3872 END format_SQWL_RU_record; -- End of Formatting RU Record
3873
3874 -- Formatting RF record for SQWL reporting
3875 --
3876 /*
3877 Effective Date --> p_effective_date
3878 Report Type (i.e. SQWL) --> p_report_type
3879 Report Format --> p_format
3880 Report Qualifier --> p_report_qualifier
3881 Record Name (i.e. RF) --> p_record_name
3882 Record Identifier --> p_record_identifier
3883 Number of RW Records --> p_total_no_of_record
3884 Wages, Tips and other Compensation --> p_total_wages
3885 Federal Income Tax Withheld --> p_tal_taxes
3886 Report Format Mode (FLAT, CSV) --> p_format_mode
3887 Validation Flag --> p_validate
3888 Exclude Record from mag file --> p_exclude_from_output
3889 Return Record Length --> ret_str_len
3890 */
3891 FUNCTION format_SQWL_RF_record(
3892 p_effective_date IN varchar2,
3893 p_report_type IN varchar2,
3894 p_format IN varchar2,
3895 p_report_qualifier IN varchar2,
3896 p_record_name IN varchar2,
3897 p_record_identifier IN varchar2,
3898 p_total_no_of_record IN varchar2,
3899 p_total_wages IN varchar2,
3900 p_total_taxes IN varchar2,
3901 p_format_mode IN varchar2,
3902 p_validate IN varchar2,
3903 p_exclude_from_output OUT nocopy varchar2,
3904 ret_str_len OUT nocopy number
3905 ) RETURN VARCHAR2
3906 IS
3907 return_value varchar2(32767);
3908 l_exclude_from_output_chk boolean;
3909 l_total_records varchar2(50);
3910 p_end_of_rec varchar2(20);
3911 l_end_of_rec varchar2(20) ;
3912
3913 BEGIN
3914 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3915 -- Formatting RF Record
3916 hr_utility.trace('Report Qualifier before Formatting RF Record '
3917 ||p_report_qualifier);
3918
3919 /* Bug:2259849. */
3920 IF ((p_report_qualifier = 'LA_SQWL' ) OR
3921 (p_report_qualifier = 'NV_SQWL')) THEN
3922
3923 return_value := 'RF'||lpad(' ',510);
3924
3925 ELSIF p_report_qualifier = 'NY_SQWL' THEN
3926
3927 return_value := 'RF'||lpad(' ',510)||l_end_of_rec;
3928
3929 ELSIF p_report_qualifier = 'AZ_SQWL' THEN /* Bug:2259849. */
3930
3931 return_value := 'RF'||lpad(' ',510);
3932
3933 ELSIF ((p_report_qualifier = 'NH_SQWL') OR
3934 (p_report_qualifier = 'VA_SQWL')) THEN
3935
3936 return_value := 'RF'||rpad(' ',5)
3937 ||rpad(0,9,0)
3938 ||lpad(' ',496);
3939
3940 ELSIF p_report_qualifier = 'SC_SQWL' THEN
3941
3942 l_total_records := lpad(substr(nvl(p_total_no_of_record,' '),1,7),7,0);
3943 return_value := 'RF'||l_total_records
3944 ||lpad(' ',7)
3945 ||lpad(' ',496);
3946 ELSE
3947 return_value:=
3948 'RF'
3949 || lpad(lpad(substr(nvl(p_total_no_of_record,' '),1,9),9,0),14)
3950 || rpad(' ',496);
3951 END IF;
3952 ret_str_len:=length(return_value);
3953 return return_value;
3954 END format_SQWL_RF_record; -- End of Formatting RF Record for SQWL Reporting
3955 --
3956 --BEGIN
3957
3958 --hr_utility.trace_on(null, 'NVSQWL');
3959
3960 END pay_us_mmrf_sqwl_format_record; -- End of Package Body