[Home] [Help]
PACKAGE BODY: APPS.PAY_US_CUSTOM_SQWL_FORMAT_REC
Source
1 PACKAGE BODY pay_us_custom_sqwl_format_rec AS
2 /* $Header: pyuscussqfr.pkb 120.3.12000000.1 2007/01/18 02:14:23 appldev noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_custom_sqwl_format_rec
12
13 Purpose
14 The purpose of this package is to format reacord to support the
15 generation of SQWL magnetic tape for US legilsative requirements.
16
17 Notes
18 Referenced By: Package pay_us_reporting_utils_pkg
19
20 History
21
22 24-FEB-2004 jgoswami 115.0 3334497 Created
23 30-May-2006 sackumar 115.1 5089997 Modified format_SQWL_CUSTOM_EMPLOYER
24 and format_SQWL_CUSTOM_EMPLOYEE procedures for NM and AK
25
26 14-JUN-2006 sackumar 115.2 5089997 Modified format_SQWL_CUSTOM_EMPLOYER for NM
27 */
28
29 --
30 -- Formatting Submitter record for SQWL reporting
31 -- For Future Use
32
33 --
34 -- Formatting Custom Employer record for SQWL reporting
35 --
36 /* Record Identifier --> p_input_1,
37 Employer Account Number --> p_input_2,
38 Tax Year --> p_input_3,
39 Quarter --> p_input_4,
40 Number of Wage Items --> p_input_5,
41 Total Wages --> p_input_6,
42 Source Code --> p_input_7,
43 Filler --> p_input_8,
44 Batch Number --> p_input_9,
45 Batch Item --> p_input_10,
46 */
47 --
48 FUNCTION format_SQWL_CUSTOM_EMPLOYER(
49 p_effective_date IN varchar2,
50 p_report_type IN varchar2,
51 p_format IN varchar2,
52 p_report_qualifier IN varchar2,
53 p_record_name IN varchar2,
54 p_input_1 IN varchar2,
55 p_input_2 IN varchar2,
56 p_input_3 IN varchar2,
57 p_input_4 IN varchar2,
58 p_input_5 IN varchar2,
59 p_input_6 IN varchar2,
60 p_input_7 IN varchar2,
61 p_input_8 IN varchar2,
62 p_input_9 IN varchar2,
63 p_input_10 IN varchar2,
64 p_input_11 IN varchar2,
65 p_input_12 IN varchar2,
66 p_input_13 IN varchar2,
67 p_input_14 IN varchar2,
68 p_input_15 IN varchar2,
69 p_input_16 IN varchar2,
70 p_input_17 IN varchar2,
71 p_input_18 IN varchar2,
72 p_input_19 IN varchar2,
73 p_input_20 IN varchar2,
74 p_input_21 IN varchar2,
75 p_input_22 IN varchar2,
76 p_input_23 IN varchar2,
77 p_input_24 IN varchar2,
78 p_input_25 IN varchar2,
79 p_input_26 IN varchar2,
80 p_input_27 IN varchar2,
81 p_input_28 IN varchar2,
82 p_input_29 IN varchar2,
83 p_input_30 IN varchar2,
84 p_input_31 IN varchar2,
85 p_input_32 IN varchar2,
86 p_input_33 IN varchar2,
87 p_input_34 IN varchar2,
88 p_input_35 IN varchar2,
89 p_input_36 IN varchar2,
90 p_input_37 IN varchar2,
91 p_input_38 IN varchar2,
92 p_input_39 IN varchar2,
93 p_input_40 IN varchar2,
94 p_validate IN varchar2,
95 p_exclude_from_output OUT nocopy varchar2,
96 sp_out_1 OUT nocopy varchar2,
97 sp_out_2 OUT nocopy varchar2,
98 sp_out_3 OUT nocopy varchar2,
99 sp_out_4 OUT nocopy varchar2,
100 sp_out_5 OUT nocopy varchar2,
101 ret_str_len OUT nocopy number,
102 p_error OUT nocopy boolean
103 ) RETURN VARCHAR2
104 IS
105 l_agent_indicator varchar2(1);
106 l_emp_ein varchar2(100);
107 l_agent_ein varchar2(100);
108 l_term_indicator varchar2(1);
109 l_other_ein varchar2(100);
110 l_exclude_from_output_chk boolean;
111 l_input_8 varchar2(50);
112 l_bus_tax_acct_number varchar2(50);
113 l_rep_qtr varchar2(300);
114 l_rep_prd varchar2(300);
115 l_end_of_rec varchar2(20);
116 l_transaction_code varchar2(1);
117 l_emp_account_num varchar2(100);
118 return_value varchar2(32767);
119
120 r_input_1 varchar2(300);
121 r_input_2 varchar2(300);
122 r_input_3 varchar2(300);
123 r_input_4 varchar2(300);
124 r_input_5 varchar2(300);
125 r_input_6 varchar2(300);
126 r_input_7 varchar2(300);
127 r_input_8 varchar2(300);
128 r_input_9 varchar2(300);
129 r_input_10 varchar2(300);
130 r_input_11 varchar2(300);
131 r_input_12 varchar2(300);
132 r_input_13 varchar2(300);
133 r_input_14 varchar2(300);
134 r_input_15 varchar2(300);
135 r_input_16 varchar2(300);
136 r_input_17 varchar2(300);
137 r_input_18 varchar2(300);
138 r_input_19 varchar2(300);
139 r_input_20 varchar2(300);
140 r_input_21 varchar2(300);
141 r_input_22 varchar2(300);
142 r_input_23 varchar2(300);
143 r_input_24 varchar2(300);
144 r_input_25 varchar2(300);
145 r_input_26 varchar2(300);
146 r_input_27 varchar2(300);
147 r_input_28 varchar2(300);
148 r_input_29 varchar2(300);
149 r_input_30 varchar2(300);
150 r_input_31 varchar2(300);
151 r_input_32 varchar2(300);
152 r_input_33 varchar2(300);
153 r_input_34 varchar2(300);
154 r_input_35 varchar2(300);
155 r_input_36 varchar2(300);
156 r_input_37 varchar2(300);
157 r_input_38 varchar2(300);
158 r_input_39 varchar2(300);
159
160 p_end_of_rec varchar2(20) := fnd_global.local_chr(13)||fnd_global.local_chr(10);
161 BEGIN
162 hr_utility.trace('Custom Employer Record Formatting started ');
163 -- Initializing local variables with parameter value
164 --{
165 r_input_2 := p_input_2;
166 r_input_3 := p_input_3;
167 r_input_4 := p_input_4;
168 r_input_5 := p_input_5;
169 r_input_6 := p_input_6;
170 r_input_7 := p_input_7;
171 r_input_8 := p_input_8;
172 r_input_9 := p_input_9;
173 r_input_10 := p_input_10;
174 r_input_11 := p_input_11;
175 r_input_12 := p_input_12;
176 r_input_13 := p_input_13;
177 r_input_14 := p_input_14;
178 r_input_15 := p_input_15;
179 r_input_16 := p_input_16;
180 r_input_17 := p_input_17;
181 r_input_18 := p_input_18;
182 r_input_19 := p_input_19;
183 r_input_20 := p_input_20;
184 r_input_21 := p_input_21;
185 r_input_22 := p_input_22;
186 r_input_23 := p_input_23;
187 r_input_24 := p_input_24;
188 r_input_25 := p_input_25;
189 r_input_26 := p_input_26;
190 r_input_27 := p_input_27;
191 r_input_28 := p_input_28;
192 r_input_29 := p_input_29;
193 r_input_30 := p_input_30;
194 r_input_31 := p_input_31;
195 r_input_32 := p_input_32;
196 r_input_33 := p_input_33;
197 r_input_34 := p_input_34;
198 r_input_35 := p_input_35;
199 r_input_36 := p_input_36;
200 r_input_37 := p_input_37;
201 r_input_38 := p_input_38;
202 r_input_39 := p_input_39;
203 --}
204
205 IF p_record_name = 'H' THEN -- p_record_name
206 --{
207 IF p_report_qualifier = 'AK_SQWL' THEN
208 --{
209 /* Pos: 1 Len: 1 Transaction Code. */
210 l_transaction_code := 'H';
211
212 /* Pos: 2 Len: 10 Desc: State Employer Account Number */
213 /* Ask Legislative Analyst to check for Special Characters */
214 /* r_input_2 := rpad(substr(replace( pay_us_reporting_utils_pkg.character_check(nvl(r_input_2,'0')),'-'),1,8),8,0);
215 */
216
217 l_emp_account_num := upper(lpad(substr(replace(r_input_2,'-'), 1, 8), 8, '0'));
218
219 /* Pos: 14 Len: 1 Desc: Quarter */
220 IF substr(p_input_4,1,2) = '03' THEN
221 l_rep_prd := 1;
222 ELSIF substr(p_input_4,1,2) = '06' THEN
223 l_rep_prd := 2;
224 ELSIF substr(p_input_4,1,2) = '09' THEN
225 l_rep_prd := 3;
226 ELSIF substr(p_input_4,1,2) = '12' THEN
227 l_rep_prd := 4;
228 END IF;
229
230 IF p_input_40 = 'FLAT' THEN
231 -- Formatting for mf file
232
233 return_value := l_transaction_code
234 ||l_emp_account_num
235 ||rpad(substr(nvl(r_input_3,' '),1,4),4)
236 ||l_rep_prd
237 ||lpad(substr(nvl(r_input_5,'0'),1,6),6,'0')
238 ||lpad(substr(nvl(r_input_6,'0'),1,11),11,'0')
239 ||r_input_7
240 ||lpad(nvl(r_input_8,'0'),73,'0')
241 ||lpad(nvl(r_input_9,'0'),8,'0')
242 ||lpad(nvl(r_input_10,'0'),4,'0');
243
244 ret_str_len:=length(return_value);
245 --}
246 ELSIF p_input_40 = 'CSV' THEN
247 --{
248 return_value:= l_transaction_code
249 ||','||l_emp_account_num
250 ||','||rpad(substr(nvl(r_input_3,' '),1,4),4)
251 ||','||l_rep_prd
252 ||','||lpad(substr(nvl(r_input_5,'0'),1,6),6,'0')
253 ||','||lpad(substr(nvl(r_input_6,'0'),1,11),11,'0')
254 ||','||r_input_7
255 ||','||lpad(nvl(r_input_8,'0'),73,'0')
256 ||','||lpad(nvl(r_input_9,'0'),8,'0')
257 ||','||lpad(nvl(r_input_10,'0'),4,'0');
258 --}
259 END IF;
260 ELSIF p_report_qualifier = 'NM_SQWL' THEN
261 r_input_3 := 'ES_903_Employer';
262
263 IF p_input_40 = 'FLAT' THEN
264 return_value:= r_input_2
265 || r_input_3
266 || ',' || r_input_4
267 || ',' || r_input_5
268 || ',' || r_input_6
269 || ',' || r_input_7
270 || ',' || r_input_8
271 || ',' || r_input_9
272 || ',' || r_input_10
273 || ',' || r_input_11
274 || ',' || r_input_12
275 || ',' || r_input_13
276 || ',' || r_input_14
277 || ',' || r_input_15
278 || ',' || r_input_16
279 || ',' || r_input_17
280 || ',' || r_input_18
281 || ',' || r_input_19
282 || ',' || r_input_20
283 || ',' || r_input_21
284 || ',' || r_input_22
285 || ',' || r_input_23
286 || ',' || r_input_24 ;
287
288 ret_str_len:=length(return_value);
289
290 ELSIF p_input_40 = 'CSV' THEN
291 return_value:= r_input_4
292 || ',' || r_input_5
293 || ',' || r_input_6
294 || ',' || r_input_7
295 || ',' || r_input_8
296 || ',' || r_input_9
297 || ',' || r_input_10
298 || ',' || r_input_11
299 || ',' || r_input_12
300 || ',' || r_input_13
301 || ',' || r_input_14
302 || ',' || r_input_15
303 || ',' || r_input_16
304 || ',' || r_input_17
305 || ',' || r_input_18
306 || ',' || r_input_19
307 || ',' || r_input_20
308 || ',' || r_input_21
309 || ',' || r_input_22
310 || ',' || r_input_23
311 || ',' || r_input_24;
312 END IF;
313 END IF;-- p_report_qualifier
314 END IF;
315 p_error := l_exclude_from_output_chk;
316 ret_str_len:=length(return_value);
317 hr_utility.trace('Length of return value = '||to_char(length(return_value)));
318 return return_value;
319 END format_SQWL_CUSTOM_EMPLOYER; -- End of Function Formatting Custom Employer record
320
321 /* ------------ Parameter mapping for SQWL Custom Employee Record -------------
322 --{
323 Record Identifier --> p_input_1
324 Employer Account Number --> p_input_2
325 Tax Year --> p_input_3
326 Quarter --> p_input_4
327 Social Security Number (SSN) --> p_input_5
328 Employee Last Name --> p_input_6
329 Employee First Name --> p_input_7
330 Employee Middle Name or Initial --> p_input_8
331 State Quarterly Unemployment Insurance Total Wages --> p_input_9
332 Project Code --> p_input_10
333 Hourly Rate --> p_input_11
334 Occupational Code or Title --> p_input_12
335 Area Code --> p_input_13
336 Batch Number --> p_input_14
337 Batch Item --> p_input_15
338
339 -- Not Currently Used for AK SQWL
340 State Quarterly Unemployment Total Taxable Wages --> p_input_16
341 State Taxable Wages --> p_input_17
342 SIT Withheld --> p_input_18
343 --}
344 */
345
346 FUNCTION format_SQWL_CUSTOM_EMPLOYEE (
347 p_effective_date IN varchar2,
348 p_report_type IN varchar2,
349 p_format IN varchar2,
350 p_report_qualifier IN varchar2,
351 p_record_name IN varchar2,
352 p_input_1 IN varchar2,
356 p_input_5 IN varchar2,
353 p_input_2 IN varchar2,
354 p_input_3 IN varchar2,
355 p_input_4 IN varchar2,
357 p_input_6 IN varchar2,
358 p_input_7 IN varchar2,
359 p_input_8 IN varchar2,
360 p_input_9 IN varchar2,
361 p_input_10 IN varchar2,
362 p_input_11 IN varchar2,
363 p_input_12 IN varchar2,
364 p_input_13 IN varchar2,
365 p_input_14 IN varchar2,
366 p_input_15 IN varchar2,
367 p_input_16 IN varchar2,
368 p_input_17 IN varchar2,
369 p_input_18 IN varchar2,
370 p_input_19 IN varchar2,
371 p_input_20 IN varchar2,
372 p_input_21 IN varchar2,
373 p_input_22 IN varchar2,
374 p_input_23 IN varchar2,
375 p_input_24 IN varchar2,
376 p_input_25 IN varchar2,
377 p_input_26 IN varchar2,
378 p_input_27 IN varchar2,
379 p_input_28 IN varchar2,
380 p_input_29 IN varchar2,
381 p_input_30 IN varchar2,
382 p_input_31 IN varchar2,
383 p_input_32 IN varchar2,
384 p_input_33 IN varchar2,
385 p_input_34 IN varchar2,
386 p_input_35 IN varchar2,
387 p_input_36 IN varchar2,
388 p_input_37 IN varchar2,
389 p_input_38 IN varchar2,
390 p_input_39 IN varchar2,
391 p_input_40 IN varchar2,
392 p_validate IN varchar2,
393 p_exclude_from_output OUT nocopy varchar2,
394 sp_out_1 OUT nocopy varchar2,
395 sp_out_2 OUT nocopy varchar2,
396 sp_out_3 OUT nocopy varchar2,
397 sp_out_4 OUT nocopy varchar2,
398 sp_out_5 OUT nocopy varchar2,
399 ret_str_len OUT nocopy number,
400 p_error OUT nocopy boolean
401 ) RETURN VARCHAR2
402 IS
403 return_value varchar2(32767);
404 l_s_hyphen_position number := 0;
405 l_pblm_code varchar2(1);
406 l_preparer_code varchar2(1);
407 l_input_1 varchar2(100);
408 l_records number(10);
409 l_input_2 varchar2(100);
410 l_record_identifier varchar2(2);
411 l_tax_year date;
412 l_agent_indicator varchar2(1);
413 l_emp_ein varchar2(100);
414 l_term_indicator varchar2(1);
415 l_agent_ein varchar2(100);
416 l_other_ein varchar2(100);
417 l_input_8 varchar2(50);
418 l_check varchar2(1);
419 l_employment_code varchar2(1);
420 p_exc varchar2(10);
421 main_return_string varchar2(300);
422 l_resub_tlcn varchar2(100);
423 l_pin varchar2(50);
424 l_ssn varchar2(100);
425 l_wages_tips varchar2(100);
426 l_full_name varchar2(100);
427 l_emp_name_or_number varchar2(50);
428 l_emp_number varchar2(50);
429 l_first_name varchar2(150);
430 l_middle_name varchar2(100);
431 l_last_name varchar2(150);
432 l_suffix varchar2(100);
433 l_err boolean;
434 l_exclude_from_output_chk boolean;
435 l_message varchar2(2000);
436 l_ss_tax_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
437 l_description varchar2(50);
438 l_field_description varchar2(50);
439 l_ss_wage_limit pay_us_federal_tax_info_f.ss_ee_wage_limit%TYPE;
440 l_ss_count number(10);
441 l_amount number(10);
442 l_tax_ct_job_dev varchar2(30);
443 l_tax_ct_ind_revit varchar2(30);
444 l_tax_ct_ind_dev varchar2(30);
445 l_tax_ct_rural varchar2(30);
446 l_fit_wh varchar2(30);
447 l_total_records varchar2(50);
448 l_wages varchar2(100);
449 l_taxes varchar2(100);
450 l_deferred_comp varchar2(100);
451 l_sdi_wh varchar2(100);
452 l_state_length number(10);
453 l_unemp_insurance varchar2(100);
454 l_fica_mcr_wh varchar2(100);
455 l_bus_tax_acct_number varchar2(50);
456 l_w2_govt_ee_contrib varchar2(100);
460 l_rep_prd varchar2(300);
457 l_w2_fed_wages varchar2(100);
458 l_wa_sqwl_outstring varchar2(200);
459 l_hours_worked number(10);
461 l_transaction_code varchar2(1);
462 l_emp_account_num varchar2(100);
463 l_end_of_rec varchar2(20);
464 p_end_of_rec varchar2(20) :=
465 fnd_global.local_chr(13)||fnd_global.local_chr(10);
466 /* PuertoRico W2 related variables Bug # 2736928 */
467 l_contact_person_phone_no varchar2(100); -- mapped to r_input_34
468 l_pension_annuity varchar2(100); -- mapped to r_input_35
469 l_contribution_plan varchar2(100); -- mapped to r_input_36
470 l_cost_reimbursement varchar2(100); -- mapped to r_input_37
471 l_uncollected_ss_tax_on_tips varchar2(100); -- mapped to r_input_31
472 l_uncollected_med_tax_on_tips varchar2(100); -- mapped to r_input_32
473
474 l_rt_end_of_rec varchar2(200);
475
476 /* Bug 2789523 */
477 l_last_field varchar2(100);
478
479 r_input_1 varchar2(300);
480 r_input_2 varchar2(300);
481 r_input_3 varchar2(300);
482 r_input_4 varchar2(300);
483 r_input_5 varchar2(300);
484 r_input_6 varchar2(300);
485 r_input_7 varchar2(300);
486 r_input_8 varchar2(300);
487 r_input_9 varchar2(300);
488 r_input_10 varchar2(300);
489 r_input_11 varchar2(300);
490 r_input_12 varchar2(300);
491 r_input_13 varchar2(300);
492 r_input_14 varchar2(300);
493 r_input_15 varchar2(300);
494 r_input_16 varchar2(300);
495 r_input_17 varchar2(300);
496 r_input_18 varchar2(300);
497 r_input_19 varchar2(300);
498 r_input_20 varchar2(300);
499 r_input_21 varchar2(300);
500 r_input_22 varchar2(300);
501 r_input_23 varchar2(300);
502 r_input_24 varchar2(300);
503 r_input_25 varchar2(300);
504 r_input_26 varchar2(300);
505 r_input_27 varchar2(300);
506 r_input_28 varchar2(300);
507 r_input_29 varchar2(300);
508 r_input_30 varchar2(300);
509 r_input_31 varchar2(300);
510 r_input_32 varchar2(300);
511 r_input_33 varchar2(300);
512 r_input_34 varchar2(300);
513 r_input_35 varchar2(300);
514 r_input_36 varchar2(300);
515 r_input_37 varchar2(300);
516 r_input_38 varchar2(300);
517 r_input_39 varchar2(300);
518
519 BEGIN
520 hr_utility.trace('Formatting Custom Employee record for SQWL ');
521 hr_utility.trace('p_report_qualifier = '||p_report_qualifier);
522 -- Initializing local variables with parameter value
523 --{
524 r_input_2 := p_input_2;
525 r_input_3 := p_input_3;
526 r_input_4 := p_input_4;
527 r_input_5 := p_input_5;
528 r_input_6 := p_input_6;
529 r_input_7 := p_input_7;
530 r_input_8 := p_input_8;
531 r_input_9 := p_input_9;
532 r_input_10 := p_input_10;
533 r_input_11 := p_input_11;
534 r_input_12 := p_input_12;
535 r_input_13 := p_input_13;
536 r_input_14 := p_input_14;
537 r_input_15 := p_input_15;
538 r_input_16 := p_input_16;
539 r_input_17 := p_input_17;
540 r_input_18 := p_input_18;
541 r_input_19 := p_input_19;
542 r_input_20 := p_input_20;
543 r_input_21 := p_input_21;
544 r_input_22 := p_input_22;
545 r_input_23 := p_input_23;
546 r_input_24 := p_input_24;
547 r_input_25 := p_input_25;
548 r_input_26 := p_input_26;
549 r_input_27 := p_input_27;
550 r_input_28 := p_input_28;
551 r_input_29 := p_input_29;
552 r_input_30 := p_input_30;
553 r_input_31 := p_input_31;
554 r_input_32 := p_input_32;
555 r_input_33 := p_input_33;
556 r_input_34 := p_input_34;
557 r_input_35 := p_input_35;
558 r_input_36 := p_input_36;
559 r_input_37 := p_input_37;
560 r_input_38 := p_input_38;
561 r_input_39 := p_input_39;
562 --}
563
564 IF p_record_name = 'D' THEN -- p_record_name
565 --{
566 IF p_report_qualifier = 'AK_SQWL' THEN
567 --{
568 /* Pos: 1 Len: 1 Transaction Code. */
569 /* Bug 4554387 l_transaction_code := 'D'; */
570
571 /* Pos: 2 Len: 10 Desc: State Employer Account Number */
572 /* Ask Legislative Analyst to check for Special Characters */
573 /* r_input_2 := rpad(substr(replace( pay_us_reporting_utils_pkg.character_check(nvl(r_input_2,'0')),'-'),1,8),8,0);
574 r_input_2 := lpad(replace(replace(nvl(replace(r_input_2,' '),' ') ,'-'),'/'),8,0);
575 */
576
577 l_emp_account_num := upper(lpad(substr(replace(r_input_2,'-'), 1, 8), 8, '0'));
578
579 /* Pos: 14 Len: 1 Desc: Quarter */
583 l_rep_prd := 2;
580 IF substr(p_input_4,1,2) = '03' THEN
581 l_rep_prd := 1;
582 ELSIF substr(p_input_4,1,2) = '06' THEN
584 ELSIF substr(p_input_4,1,2) = '09' THEN
585 l_rep_prd := 3;
586 ELSIF substr(p_input_4,1,2) = '12' THEN
587 l_rep_prd := 4;
588 END IF;
589
590 /* Pos:15 Len: 9 Desc: Social security number */
591
592 IF p_input_40 = 'FLAT' THEN
593
594 l_ssn := pay_us_reporting_utils_pkg.data_validation(
595 p_effective_date,
596 p_report_type,
597 p_format,
598 p_report_qualifier,
599 p_record_name,
600 'SSN',
601 r_input_5,
602 'Social Security',
603 p_input_39, --EE number for messg purpose.
604 null,
605 p_validate,
606 p_exclude_from_output,
607 sp_out_1,
608 sp_out_2);
609 IF p_exclude_from_output = 'Y' THEN
610 l_exclude_from_output_chk := TRUE;
611 END IF;
612 sp_out_5 := l_ssn;
613 ELSE
614 l_ssn := replace(replace(r_input_5,'-'),',');
615 END IF;
616
617 hr_utility.trace('SSN after Validation and Formatting = '||l_ssn);
618
619 /*Pos:24 - 48 Last name
620 Pos:49 - 63 First name
621 Pos:64 - 64 Middle name
622 */
623
624 l_last_name := pay_us_reporting_utils_pkg.Character_check(
625 nvl(substr(r_input_6,1,25),''));
626 l_first_name := pay_us_reporting_utils_pkg.Character_check(
627 nvl(substr(r_input_7,1,15),''));
628 l_middle_name := ltrim(pay_us_reporting_utils_pkg.Character_check(
629 nvl(substr(r_input_8,1,1),'')));
630
631 /* Pos:65-75 SUI Insurance Wages. */
632 IF p_input_40 = 'FLAT' THEN
633
634 r_input_9 := pay_us_reporting_utils_pkg.data_validation(p_effective_date,
635 p_report_type,
636 p_format,
637 p_report_qualifier,
638 p_record_name,
639 'NEG_CHECK',
640 r_input_9,
641 'SUI Insurance Wages',
642 p_input_39,
643 null,
644 p_validate,
645 p_exclude_from_output,
646 sp_out_1,
647 sp_out_2);
648
649 /*Bug# 4554387*/
650 r_input_9 := ltrim(r_input_9,'0');
651
652 IF p_exclude_from_output = 'Y' THEN
653 l_exclude_from_output_chk := TRUE;
654 END IF;
655
656 END IF;
657 /* Pos:76 Project Code */
658 /* Bug 4554387 r_input_10 := lpad(nvl(r_input_10,'0'),1,'0'); */
659 r_input_10 := '';
660
661 /* Pos:77-81 Hourly Rate */
662 /* Bug 4554387 r_input_11 := lpad(nvl(r_input_11,'0'),5,'0'); */
663 r_input_11 := '';
664
665 /* Pos: 82-103 Desc: Occupational Code or Title */
666 r_input_12 := ltrim(upper(substr(r_input_12,1,10)));
667
668 /* Pos: 104-105 Desc: Area Code */
669 r_input_13 := ltrim(upper(substr(r_input_13,1,2)));
670
671 /* Pos: 106-113 Desc: Batch Number */
672 /*Bug # 4554387 r_input_14 := lpad(nvl(r_input_14,'0'),8,'0');*/
673
674 /* Pos: 114-117 Desc: Batch Number */
675 /*Bug # 4554387 r_input_15 := lpad(nvl(r_input_15,'0'),4,'0');*/
676
677 /* Check with Legislative Analyst that do we want to report
678 an employee to a02 for other -ve wages or taxes
679 */
680
681 /*Bug # 4554387 */
682 /*
683 IF p_input_40 = 'FLAT' THEN
684 -{ Start of formatting FLAT type Custom Employee Record
685
686 return_value:= l_transaction_code
687 ||l_emp_account_num
688 ||rpad(substr(nvl(r_input_3,' '),1,4),4)
689 ||l_rep_prd
690 ||l_ssn
691 ||l_last_name
692 ||l_first_name
693 ||l_middle_name
694 ||r_input_9
695 ||r_input_10
696 ||r_input_11
700 ||r_input_15
697 ||r_input_12
698 ||r_input_13
699 ||r_input_14
701 |l_end_of_rec;
702
703 hr_utility.trace('Length of return value = '||to_char(length(return_value)));
704 --} End of formatting FLAT Type RS Record
705 ELS
706 */
707 /* end of Bug # 4554387*/
708
709 IF p_input_40 = 'CSV'
710 or p_input_40 = 'FLAT' THEN /* Bug # 4554387 */
711
712 --{ Start of formatting Custom Employee record in CSV format
713 return_value := /* Bug # 4554387 l_transaction_code
714 ||','||*/l_emp_account_num
715 ||','||rpad(substr(nvl(r_input_3,' '),1,4),4)
716 ||','||l_rep_prd
717 ||','||l_ssn
718 ||','||l_last_name
719 ||','||l_first_name
720 ||','||l_middle_name
721 ||','||r_input_9
722 ||',' /*Bug# 4554387 ||r_input_10 */
723 ||',' /*Bug# 4554387 ||r_input_11 */
724 ||','||r_input_12
725 ||','||r_input_13;
726 /* Bug # 4554387
727 ||','||r_input_14
728 ||','||r_input_15
729 ||','||lpad(' ',5);
730 */
731
732 --} End of formatting RS record in CSV format
733
734 ELSIF p_input_40 = 'BLANK' THEN
735 --{ Start of formatting BALNK Custom Employee record used for audit report
736
737 return_value := ''
738 ||','||' '
739 ||','||' '
740 ||','||' '
741 ||','||' '
742 ||','||' '
743 ||','||' '
744 ||','||' '
745 ||','||' '
746 ||','||' '
747 ||','||' '
748 ||','||' '
749 ||','||' '
750 ||','||' ';
751 /* Bug# 4554387
752 ||','||' '
753 ||','||lpad(' ',5);
754 */
755 --} End of formatting BLANK Custom Employee record used for audit report
756 END IF; -- p_input_40
757 ELSIF p_report_qualifier = 'NM_SQWL' THEN
758 r_input_2 := 'ES_903_Employees';
759
760 IF p_input_40 = 'FLAT' THEN
761 return_value := r_input_2
762 || ',' || r_input_3
763 || ',' || r_input_4
764 || ',' || r_input_5
765 || ',' || r_input_6
766 || ',' || r_input_7
767 || ',' || r_input_8;
768
769 ELSIF p_input_40 = 'CSV' THEN
770 return_value := r_input_3
771 /* || ',' || r_input_4 Employee Name */
772 || ',' || r_input_5
773 || ',' || r_input_6
774 || ',' || r_input_7
775 || ',' || r_input_8;
776 END IF;
777 END IF;-- p_report_qualifier
778 END IF; -- p_record_name
779 p_error := l_exclude_from_output_chk;
780 ret_str_len:=length(return_value);
781 hr_utility.trace('Length of return value = '||to_char(length(return_value)));
782 return return_value;
783 END format_SQWL_CUSTOM_EMPLOYEE;
784 -- End of Formatting Custom Employee Record for SQWL Reporting
785
786 END pay_us_custom_sqwl_format_rec; -- End of Package Body