[Home] [Help]
PACKAGE BODY: APPS.PER_NEW_HIRE_PKG
Source
1 package body per_new_hire_pkg as
2 /* $Header: pernhire.pkb 120.16.12020000.8 2012/09/03 09:23:49 agarai ship $ */
3 /*
4 REM +======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation |
6 REM | Redwood Shores, California, USA |
7 REM | All rights reserved. |
8 REM +======================================================================+
9 REM Package Body Name : per_new_hire_pkg
10 REM Package File Name : pernhire.pkb
11 REM Description : This package defines procedures/functions that
12 REM are used for new hire electrical file
13 REM
14 REM Change List:
15 REM ------------
16 REM
17 REM Name Date Version Bug Text
18 REM ----------- ---------- ------- ------- -------------------------------
19 REM ynegoro 12-Dec-2002 115.0 1057968 Created.
20 REM ynegoro 13-Dec-2002 115.1 Fixed GSCC Compliance
21 REM ynegoro 19-Dec-2002 115.2 Added get_new_hire_contact
22 REM ynegoro 20-Dec-2002 115.3 Changed get_location_address
23 REM ynegoro 15-Jan-2003 115.4 2753923 Changed get_employee_address
24 REM get_location_address
25 REM to get address_line3
26 REM ynegoro 22-Jan-2003 115.5 2753923 Changed a03_fl_new_hire_record
27 REM 'YYYYMMDD' to 'MMDDYYYY'
28 REM ynegoro 28-Jan-2003 115.6 Support Canadian Province
29 REM ynegoro 29-Jan-2003 115.7 2775157 Omit hyphens in CA SIT
30 REM ynegoro 30-Jan-2003 115.8 Replace comman to space for a03
31 REM ynegoro 11-Mar-2003 115.9 Modified ny_1t_record and
32 REM ny_1f_record from 118 to 119
33 REM for blank field
34 REM vbanner 30-Dec-2003 115.10 3316519 Modified get_new_hire_contact
35 REM to restrict contact title
36 REM out parameter to 60 characters
37 REM ynegoro 03-Nov-2004 115.11 2919553 Changed c_new_hire_record cursor
38 REM to pikc up latest person name
39 REM ynegoro 23-DEC-2004 4095015 Changed tx_new_hire_record
40 REM ynegoro 30-DEC-2004 Added tx_r4_record
41 REM ynegoro 23-JUN-2005 115.12 Added p_state_of_hire to a03_tx_record
42 REM and tx_new_hire_record
43 REM ynegoro 20-JUL-2005 115.13 4504074 Added nvl to v_ssn
44 REM ynegoro 25-JUL-2005 115.15 3954955 Changed a format CCYY to YYYY for TX
45 REM trugless 12-JAN-2006 115.16 4912696 Modified cursor c_new_hire_record
46 REM to use tables instead of views
47 REM to reduce shared memory use.
48 REM ssouresr 20-APR-2006 115.17 5169671 Modified the fnc il_new_hire_record
49 REM so that a missing middle name does not
50 REM invalidate record positions in file
51 REM rpasumar 08-MAR-2007 115.18 5893234 Changed the version number from 1:00 to 1.00
52 REM in the procedure, fl_new_hire_record.
53 REM jdevasah 09-JUL-2007 115.19 6155091 Modified address procedures in order to
54 REM handle US International address style
55 REM swamukhe 20-JUN-2005 115.20 5069465 Modified the l_buffer for CA and NY states to
56 REM insert a line feed.
57 REM swamukhe 26-MAY-2008 115.25 Modified the date format for TX from 'YYYYMMDD' to
58 REM 'MMDDYYYY'
59 REM lbodired 21-AUG-2009 115.26 Modified the 'fl_new_hire_record' function to
60 REM insert carriage return
61 REM nkjaladi 26-JUL-2011 115.27 10014495 Modified 'ny_1h_record' and
62 REM 'a03_ny_new_hire_record' functions as per
63 REM requirement of New York dependent health
64 REM benefits available.
65 REM (Made changes to package header also)
66 REM nkjaladi 26-JUL-2011 115.28 10014495 Modified 'a03_ny_new_hire_header' and
67 REM 'a03_ny_new_hire_record' functions as per
68 REM requirement of New York dependent health
69 REM benefits available.
70 REM (Made changes to package header also)
71 REM ybudamal 11-NOV-2011 115.29 13082528 Modified 'fl_new_hire_record', 'a03_fl_new_hire_header'
72 REM and 'a03_fl_new_hire_record' functions to add the parameter
73 REM 'p_medical_insr_avail' and modified output as per the
74 REM Florida New Hires Electronic Reporting Specifications
75 REM nkjaladi 18-MAY-2012 115.30 10387522 Added new functions for SC, MI, PA,
76 REM OH, AR, MD and LA states.
77 REM agarai 31-MAY-2012 115.31 10387522 Added nocopy to OUT parameters
78 REM to remove GSCC errors.
79 REM agarai 01-JUN-2012 115.32 10387522 Changed substr parameters in
80 REM a03_ny_new_hire_record to remove
81 REM errors.
82 REM nkjaladi 07-JUN-2012 115.33 10387522 Corrected typos to resolve the
83 REM issue of columns wrongly getting
84 REM ordered.
85 REM nkjaladi 07-JUN-2012 115.34 10387522 In A03 PA record corrected the
86 REM date format
87 REM agarai 31-AUG-2012 115.35 14532612 Modified 'a03_ny_new_hire_record'
88 REM 14470601 and 'ny_1h_record' to add
89 REM 'p_new_hire_indicator' parameter,
90 REM to correct date formats of hire
91 REM date and Dep. health insurance
92 REM start date and to display the
93 REM eligibility indicator properly when
94 REM p_medical_avail is 'E'.
95 REM ========================================================================
96
97 */
98 --
99 -- Global variables and Constants.
100 --
101 g_character_set varchar2(80);
102 g_package_name constant varchar2(30) := 'per_new_hire_pkg';
103
104 g_e4_code varchar2(2) := 'E4';
105 g_w4_code varchar2(2) := 'W4';
106 g_t4_code varchar2(2) := 'T4';
107 g_eol varchar2(1) := fnd_global.local_chr(10) ;
108 g_delimiter varchar2(1) := ',';
109
110 /****************************************************************
111 * This procedure sets character_set to out put file *
112 * *
113 * *
114 ****************************************************************/
115 procedure char_set_init
116 (
117 p_character_set in varchar2
118 )
119 is
120 l_api_name varchar2(61) := g_package_name || '.char_set_init';
121 l_package_name varchar2(30) := g_package_name;
122 begin
123 --
124 --hr_utility.trace_on(null,'NEWHIRE');
125 hr_utility.set_location('Entering:' ||l_api_name,10);
126 --
127 -- Check mandatory argument.
128 --
129 hr_api.mandatory_arg_error(
130 p_api_name => l_api_name,
131 p_argument => 'p_character_set',
132 p_argument_value => p_character_set);
133 --
134 -- Initialize global variables.
135 --
136 g_character_set := p_character_set;
137 --
138 hr_utility.set_location(l_api_name,20);
139 --
140 hr_utility.set_location('Leaving.... :' || l_api_name,50);
141 end char_set_init;
142
143 /****************************************************************
144 * This procedure formats California E4 record *
145 * *
146 * *
147 ****************************************************************/
148 function ca_e4_record
149 (
150 p_record_identifier in varchar2
151 ,p_federal_id in varchar2
152 ,p_sit_company_state_id in varchar2
153 ,p_branch_code in varchar2
154 ,p_tax_unit_name in varchar2
155 ,p_street_address in varchar2
156 ,p_city in varchar2
157 ,p_state in varchar2
158 ,p_zip in varchar2
159 ,p_zip_extension in varchar2 default null
160 ) return varchar2
161 is
162 l_api_name varchar2(61) := g_package_name || '.ca_e4_record';
163 l_buffer varchar2(2000);
164 v_sit varchar2(40);
165 p_end_of_rec varchar2(2000);
166 begin
167
168 hr_utility.set_location('Entering:' ||l_api_name,10);
169
170 --
171 -- Check mandatory argument.
172 --
173 hr_api.mandatory_arg_error(
174 p_api_name => l_api_name,
175 p_argument => 'p_record_identifier',
176 p_argument_value => p_record_identifier);
177
178 hr_utility.set_location(l_api_name,11);
179 hr_utility.trace('p_federal_id= ' || p_federal_id);
180 hr_utility.trace('p_sit_company_state_id= ' || p_sit_company_state_id);
181
182 v_sit := replace(p_sit_company_state_id,'-',null);
183
184 hr_utility.trace('v_sit_company_state_id= ' || v_sit);
185
186 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
187 hr_utility.trace('p_street_address= ' || p_street_address);
188 hr_utility.trace('p_city= ' || p_city);
189 hr_utility.trace('p_state= ' || p_state);
190 hr_utility.trace('p_zip= ' || p_zip);
191 hr_utility.trace('p_zip_extension= ' || p_zip_extension);
192
193 hr_utility.set_location(l_api_name,30);
194
195 --
196 -- format new hire rocord
197 --
198
199 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
200
201 l_buffer := upper(p_record_identifier ||
202 rpad(p_federal_id,9,' ') ||
203 rpad(nvl(v_sit,'0'),8,'0') ||
204 rpad(nvl(p_branch_code,' '),3,' ') ||
205 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
206 rpad(p_street_address,40,' ') ||
207 rpad(p_city,25,' ') ||
208 rpad(p_state,2,' ') ||
209 rpad(p_zip,5,' ') ||
210 rpad(nvl(p_zip_extension,' '),4,' ') ||
211 lpad(' ',32,' '))||p_end_of_rec ;
212
213
214 hr_utility.set_location('Leaving.... :' || l_api_name,40);
215
216 return convert(l_buffer,g_character_set);
217 exception
218 when others then
219 hr_utility.set_location('Leaving.... :' || l_api_name,70);
220 -- fnd_message.set_name('PER','HR_ERROR');
221 -- fnd_message.set_token('SQL',g_ca_e4_sql);
222 -- fnd_message.raise_error;
223 raise;
224 end ca_e4_record;
225
226 /****************************************************************
227 * This procedure formats California W4 record *
228 * *
229 * *
230 ****************************************************************/
231 function ca_w4_record
232 (
233 p_record_identifier in varchar2
234 ,p_national_identifier in varchar2
235 ,p_first_name in varchar2
236 ,p_middle_name in varchar2
237 ,p_last_name in varchar2
238 ,p_street_address in varchar2
239 ,p_city in varchar2
240 ,p_state in varchar2
241 ,p_zip in varchar2
242 ,p_zip_extension in varchar2
243 ,p_date_of_hire in date
244 ) return varchar2
245 is
246 l_api_name varchar2(61) := g_package_name || '.ca_w4_record';
247 l_buffer varchar2(2000);
248 v_ssn varchar(9);
249 p_end_of_rec varchar2(2000);
250 begin
251 hr_utility.set_location('Entering:' ||l_api_name,10);
252
253 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
254 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
255
256 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
257
258 hr_utility.trace('v_ssn= ' || v_ssn);
259
260 hr_utility.trace('p_first_name= ' || p_first_name);
261
262 hr_utility.trace('p_middle_name= ' || p_middle_name);
263 hr_utility.trace('p_last_name= ' || p_last_name);
264
265 hr_utility.trace('p_street_address= ' || p_street_address);
266 hr_utility.trace('p_city= ' || p_city);
267 hr_utility.trace('p_state= ' || p_state);
268 hr_utility.trace('p_zip= ' || p_zip);
269 hr_utility.trace('p_zip_extension= ' || p_zip_extension);
270 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
271
272 --
273 -- format new hire rocord
274 --
275
276 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
277
278 l_buffer := upper(
279 p_record_identifier ||
280 rpad(nvl(v_ssn,' '),9,' ') || -- BUG4504074
281 rpad(nvl(p_first_name,' '),16,' ') ||
282 rpad(nvl(p_middle_name,' '),1,' ') ||
283 rpad(p_last_name,30,' ') ||
284 rpad(nvl(p_street_address,' '),40,' ') ||
285 rpad(nvl(p_city,' '),25,' ') ||
286 rpad(nvl(p_state,' '),2,' ') ||
287 rpad(nvl(p_zip,' '),5,' ') ||
288 rpad(nvl(p_zip_extension,' '),4,' ') ||
289 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
290 lpad(' ',33,' '))||p_end_of_rec;
291
292 hr_utility.trace('l_buffer = ' || l_buffer);
293
294
295 hr_utility.set_location('Leaving.... :' || l_api_name,60);
296
297 return convert(l_buffer,g_character_set);
298 exception
299 when others then
300 hr_utility.set_location('Leaving.... :' || l_api_name,70);
301 raise;
302 end ca_w4_record;
303 --
304 /****************************************************************
305 * This procedure formats California T4 record *
306 * *
307 * *
308 ****************************************************************/
309 FUNCTION ca_t4_record
310 (
311 p_record_identifier in varchar2
312 ,p_number_of_employee in number
313 ) return varchar2
314 IS
315 l_api_name varchar2(61) := g_package_name || '.ca_t4_record';
316 l_buffer varchar2(2000);
317 p_end_of_rec varchar2(2000);
318 begin
319 --
320 hr_utility.set_location('Entering:' ||l_api_name,10);
321
322 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
323 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
324
325 --
326 -- format new hire rocord
327 --
328
329 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
330
331 l_buffer := upper(
332 p_record_identifier ||
333 lpad(to_char(p_number_of_employee),11,'0') ||
334 lpad(' ',162,' '))||p_end_of_rec;
335
336 hr_utility.set_location(l_api_name,20);
337
338 return convert(l_buffer,g_character_set);
339 exception
340 when others then
341 hr_utility.set_location('Leaving.... :' || l_api_name,30);
342 raise;
343 end ca_t4_record;
344 --
345 /****************************************************************
346 * This procedure formats NEW YORK 1A record for Transmitter *
347 * *
348 * *
349 ****************************************************************/
350 function ny_1a_record
351 (
352 p_record_identifier in varchar2
353 ,p_creation_date in varchar2
354 ,p_federal_id in varchar2
355 ,p_tax_unit_name in varchar2
356 ,p_street_address in varchar2
357 ,p_city in varchar2
358 ,p_state in varchar2
359 ,p_zip in varchar2
360 ) return varchar2
361 is
362 l_api_name varchar2(61) := g_package_name || '.ny_1a_record';
363 l_buffer varchar2(2000);
364 p_end_of_rec varchar2(2000);
365 begin
366
367 hr_utility.set_location('Entering...:' ||l_api_name,10);
368
369 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
370 hr_utility.trace('p_federal_id= ' || p_federal_id);
371 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
372 hr_utility.trace('p_street_address= ' || p_street_address);
373 hr_utility.trace('p_city= ' || p_city);
374 hr_utility.trace('p_state= ' || p_state);
375 hr_utility.trace('p_zip= ' || p_zip);
376
377 hr_utility.set_location(l_api_name,30);
378
379 --
380 -- format new hire rocord
381 --
382
383 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
384
385 l_buffer := upper(p_record_identifier ||
386 p_creation_date ||
387 rpad(nvl(p_federal_id,' '),11,' ') ||
388 rpad(nvl(p_tax_unit_name,' '),40,' ') ||
389 rpad(nvl(p_street_address,' '),30,' ') ||
390 rpad(nvl(p_city,' '),18,' ') ||
391 rpad(nvl(p_state,' '),2,' ') ||
392 rpad(nvl(p_zip,' '),9,' ') ||
393 lpad(' ',10,' '))||p_end_of_rec;
394
395
396 hr_utility.set_location('Leaving.... :' || l_api_name,40);
397
398 return convert(l_buffer,g_character_set);
399 exception
400 when others then
401 hr_utility.set_location('Leaving.... :' || l_api_name,70);
402 raise;
403 end ny_1a_record;
404 --
405 /****************************************************************
406 * This procedure formats NEW YORK 1E record for Employer *
407 * *
408 * *
409 ****************************************************************/
410 function ny_1e_record
411 (
412 p_record_identifier in varchar2
413 ,p_federal_id in varchar2
414 ,p_tax_unit_name in varchar2
415 ,p_street_address in varchar2
416 ,p_city in varchar2
417 ,p_state in varchar2
418 ,p_zip in varchar2
419 ) return varchar2
420 is
421 l_api_name varchar2(61) := g_package_name || '.ny_1e_record';
422 l_buffer varchar2(2000);
423 p_end_of_rec varchar2(2000);
424 begin
425
426 hr_utility.set_location('Entering:' ||l_api_name,10);
427
428 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
429 hr_utility.trace('p_federal_id= ' || p_federal_id);
430 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
431 hr_utility.trace('p_street_address= ' || p_street_address);
432 hr_utility.trace('p_city= ' || p_city);
433 hr_utility.trace('p_state= ' || p_state);
434 hr_utility.trace('p_zip= ' || p_zip);
435
436 hr_utility.set_location(l_api_name,30);
437
438 --
439 -- format new hire rocord
440 --
441
442 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
443
444 l_buffer := upper(p_record_identifier ||
445 lpad(' ',4,' ') ||
446 rpad(nvl(p_federal_id,' '),11,' ') ||
447 ' ' || -- blank 1
448 rpad(nvl(p_tax_unit_name,' '),40,' ') ||
449 ' ' || -- blank 1
450 rpad(nvl(p_street_address,' '),30,' ') ||
451 rpad(nvl(p_city,' '),18,' ') ||
452 rpad(nvl(p_state,' '),2,' ') ||
453 rpad(nvl(p_zip,' '),9,' ') ||
454 lpad(' ',10,' '))||p_end_of_rec;
455
456
457 hr_utility.set_location('Leaving.... :' || l_api_name,40);
458
459 return convert(l_buffer,g_character_set);
460 exception
461 when others then
462 hr_utility.set_location('Leaving.... :' || l_api_name,70);
463 raise;
464 end ny_1e_record;
465 --
466 --
467 /****************************************************************
468 * This procedure formats NEW YORK 1H record for Employee *
469 * *
470 * *
471 ****************************************************************/
472 function ny_1h_record
473 (
474 p_record_identifier in varchar2
475 ,p_national_identifier in varchar2
476 ,p_first_name in varchar2
477 ,p_middle_name in varchar2
478 ,p_last_name in varchar2
479 ,p_street_address in varchar2
480 ,p_city in varchar2
481 ,p_state in varchar2
482 ,p_zip in varchar2
483 ,p_date_of_hire in date
484 ,p_medical_avail in varchar2 --#10014495
485 ,p_health_ins_date in date --#10014495
486 ,p_new_hire_indicator in varchar2 --#14532612
487 ) return varchar2
488 is
489 l_api_name varchar2(61) := g_package_name || '.ny_1h_record';
490 l_buffer varchar2(2000);
491 v_ssn varchar(9);
492 p_end_of_rec varchar2(2000);
493 l_medical_avail NUMBER; --#10014495
494 begin
495 hr_utility.set_location('Entering:' ||l_api_name,10);
496
497 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
498 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
499
500 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
501
502 hr_utility.trace('v_ssn= ' || v_ssn);
503 hr_utility.trace('p_first_name= ' || p_first_name);
504 hr_utility.trace('p_middle_name= ' || p_middle_name);
505 hr_utility.trace('p_last_name= ' || p_last_name);
506
507 hr_utility.trace('p_street_address= ' || p_street_address);
508 hr_utility.trace('p_city= ' || p_city);
509 hr_utility.trace('p_state= ' || p_state);
510 hr_utility.trace('p_zip= ' || p_zip);
511 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
512 --#10014495 start
513 hr_utility.trace('p_medical_avail= ' || p_medical_avail);
514 hr_utility.trace('p_health_ins_date= ' || to_char(p_health_ins_date));
515 hr_utility.trace('p_new_hire_indicator= ' || p_new_hire_indicator); --#14532612
516
517 IF p_medical_avail = 'Y' THEN
518 l_medical_avail := 1;
519 --start of bug 14532612
520 ELSIF p_medical_avail = 'E' THEN
521 IF p_new_hire_indicator = 'INCL' THEN
522 l_medical_avail := 1;
523 ELSE
524 l_medical_avail := 2;
525 END IF;
526 --end of bug 14532612
527 ELSE
528 l_medical_avail := 2;
529 END IF;
530 --#10014495 end
531 --
532 -- format new hire rocord
533 --
534
535 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
536
537 l_buffer := upper(
538 p_record_identifier ||
539 rpad(nvl(v_ssn,' '),9,' ') || -- BUG4504074
540 rpad((p_last_name || ',' || nvl(p_first_name,'') || ' ' ||
541 nvl(substr(p_middle_name,1,1),'')),28,' ') ||
542 rpad(nvl(p_street_address,' '),30,' ') ||
543 rpad(nvl(p_city,' '),18,' ') ||
544 rpad(nvl(p_state,' '),2,' ') ||
545 rpad(nvl(p_zip,' '),5,' ') ||
546 ' ' || -- blank 1
547 rpad(nvl(to_char(p_date_of_hire,'MMDDRR'),' '),6,' ') ||
548 to_char(l_medical_avail)||
549 rpad(nvl(to_char(p_health_ins_date,'MMDDRRRR'),' '),8,' ')||
550 lpad(' ',18,' '))||p_end_of_rec; --#10014495 Added l_medical_avail and p_health_ins_date
551
552 hr_utility.trace('l_buffer = ' || l_buffer);
553
554
555 hr_utility.set_location('Leaving.... :' || l_api_name,60);
556
557 return convert(l_buffer,g_character_set);
558 exception
559 when others then
560 hr_utility.set_location('Leaving.... :' || l_api_name,70);
561 raise;
562 end ny_1h_record;
563 --
564 /****************************************************************
565 * This procedure formats NEW YORK 1T record *
566 * *
567 * *
568 ****************************************************************/
569 FUNCTION ny_1t_record
570 (
571 p_record_identifier in varchar2
572 ,p_number_of_employee in number
573 ) return varchar2
574 IS
575 l_api_name varchar2(61) := g_package_name || '.ny_1t_record';
576 l_buffer varchar2(2000);
577 p_end_of_rec varchar2(2000);
578 begin
579 --
580 hr_utility.set_location('Entering:' ||l_api_name,10);
581
582 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
583
584 --
585 -- format new hire rocord
586 --
587
588 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
589
590 l_buffer := upper(
591 p_record_identifier ||
592 lpad(to_char(p_number_of_employee),7,' ') ||
593 lpad(' ',119,' '))||p_end_of_rec;
594
595 hr_utility.set_location(l_api_name,20);
596
597 return convert(l_buffer,g_character_set);
598 exception
599 when others then
600 hr_utility.set_location('Leaving.... :' || l_api_name,30);
601 raise;
602 end ny_1t_record;
603 --
604 /****************************************************************
605 * This procedure formats NEW YORK 1F record *
606 * *
607 * *
608 ****************************************************************/
609 FUNCTION ny_1f_record
610 (
611 p_record_identifier in varchar2
612 ,p_number_of_employer in number
613 ) return varchar2
614 IS
615 l_api_name varchar2(61) := g_package_name || '.ny_1f_record';
616 l_buffer varchar2(2000);
617 p_end_of_rec varchar2(2000);
618 begin
619 --
620 hr_utility.set_location('Entering:' ||l_api_name,10);
621
622 hr_utility.trace('p_number_of_emplyer = ' || p_number_of_employer);
623
624 --
625 -- format new hire rocord
626 --
627
628 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
629
630 l_buffer := upper(
631 p_record_identifier ||
632 lpad(to_char(p_number_of_employer),7,' ') ||
633 lpad(' ',119,' '))||p_end_of_rec;
634
635 hr_utility.set_location(l_api_name,20);
636
637 return convert(l_buffer,g_character_set);
638 exception
639 when others then
640 hr_utility.set_location('Leaving.... :' || l_api_name,30);
641 raise;
642 end ny_1f_record;
643 --
644 /****************************************************************
645 * This procedure formats FLORIDA New Hire record *
646 * *
647 * *
648 ****************************************************************/
649 function fl_new_hire_record
650 (
651 p_record_identifier in varchar2
652 ,p_first_name in varchar2
653 ,p_middle_name in varchar2
654 ,p_last_name in varchar2
655 ,p_national_identifier in varchar2
656 ,p_emp_address_line1 in varchar2
657 ,p_emp_address_line2 in varchar2
658 ,p_emp_address_line3 in varchar2
659 ,p_emp_city in varchar2
660 ,p_emp_state in varchar2
661 ,p_emp_zip in varchar2
662 ,p_emp_zip_extension in varchar2
663 ,p_emp_country_code in varchar2
664 ,p_date_of_birth in date
665 ,p_date_of_hire in date
666 ,p_state_of_hire in varchar2
667 ,p_medical_insr_avail in varchar2 --#13082528
668 ,p_federal_id in varchar2
669 ,p_sit_company_state_id in varchar2
670 ,p_tax_unit_name in varchar2
671 ,p_loc_address_line1 in varchar2
672 ,p_loc_address_line2 in varchar2
673 ,p_loc_address_line3 in varchar2
674 ,p_loc_city in varchar2
675 ,p_loc_state in varchar2
676 ,p_loc_zip in varchar2
677 ,p_loc_zip_extension in varchar2
678 ,p_loc_country_code in varchar2
679 ,p_loc_phone in varchar2
680 ,p_loc_phone_extension in varchar2
681 ,p_loc_contact in varchar2
682 ,p_opt_address_line1 in varchar2
683 ,p_opt_address_line2 in varchar2
684 ,p_opt_address_line3 in varchar2
685 ,p_opt_city in varchar2
686 ,p_opt_state in varchar2
687 ,p_opt_zip in varchar2
688 ,p_opt_zip_extension in varchar2
689 ,p_opt_country_code in varchar2
690 ,p_opt_phone in varchar2
691 ,p_opt_phone_extension in varchar2
692 ,p_opt_contact in varchar2
693 ,p_multi_state in varchar2
694 ) return varchar2
695 is
696 l_api_name varchar2(61) := g_package_name || '.fl_new_hire_record';
697 l_buffer varchar2(2000);
698 v_ssn varchar(9);
699 l_phone varchar(10);
700 l_opt_phone varchar(10);
701 --bug#8791766
702 p_end_of_rec varchar2(2000);
703 begin
704
705 hr_utility.set_location('Entering...: ' || l_api_name,10);
706
707 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
708
709 hr_utility.trace('p_first_name= ' || p_first_name);
710 hr_utility.trace('p_middle_name= ' || p_middle_name);
711 hr_utility.trace('p_last_name= ' || p_last_name);
712
713 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
714
715 hr_utility.trace('v_ssn= ' || v_ssn);
716 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
717 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
718 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line3);
719 hr_utility.trace('p_emp_city= ' || p_emp_city);
720 hr_utility.trace('p_emp_state= ' || p_emp_state);
721 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
722 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
723 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
724 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
725 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
726 hr_utility.trace('p_medical_insr_avail= ' || p_medical_insr_avail); --#13082528
727 hr_utility.trace('p_federal_id= ' || p_federal_id);
728 hr_utility.trace('p_sit_company_state_id=' || p_sit_company_state_id);
729 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
730 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
731 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
732 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
733 hr_utility.trace('p_loc_city= ' || p_loc_city);
734 hr_utility.trace('p_loc_state= ' || p_loc_state);
735 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
736 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
737 hr_utility.trace('p_loc_country_code= ' || p_loc_country_code);
738 hr_utility.trace('p_loc_phone= ' || p_loc_phone);
739
740 l_phone := replace(replace(replace(replace(replace(nvl(p_loc_phone,' '),'(',null),'-',null),'.',null),')',null),' ',null);
741
742 hr_utility.trace('l_phone= ' || l_phone);
743 hr_utility.trace('p_loc_contact= ' || p_loc_contact);
744
745 l_opt_phone := replace(replace(replace(replace(replace(nvl(p_opt_phone,' '),'(',null),'-',null),'.',null),')',null),' ',null);
746
747 hr_utility.trace('l_opt_phone= ' || l_opt_phone);
748 hr_utility.trace('p_multi_state= ' || p_multi_state);
749 hr_utility.set_location(l_api_name,30);
750 --bug#8791766
751 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
752 --
753 -- format new hire rocord
754 --
755 l_buffer := p_record_identifier || -- 'FL Newhire Record'
756 '1.00' ||
757 rpad(nvl(p_first_name,' '),16,' ') ||
758 rpad(nvl(p_middle_name,' '),16,' ') ||
759 rpad(nvl(p_last_name,' '),30,' ') ||
760 rpad(nvl(v_ssn,' '),9,' ') ||
761 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
762 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
763 rpad(nvl(p_emp_address_line3,' '),40,' ') ||
764 rpad(nvl(p_emp_city,' '),25,' ') ||
765 rpad(nvl(p_emp_state,' '),2,' ') ||
766 rpad(nvl(p_emp_zip,' '),20,' ') ||
767 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
768 rpad(nvl(p_emp_country_code,' '),2,' ') ||
769 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') ||
770 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') ||
771 ' ' || -- rpad(nvl(p_state_of_hire,' '),2,' ') ||
772 nvl(p_medical_insr_avail,' ') || --#13082528
773 ' ' || -- filler blank 2 --#13082528
774 rpad(nvl(p_federal_id,' '),9,' ') ||
775 rpad(nvl(p_sit_company_state_id,' '),12,' ') ||
776 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
777 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
778 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
779 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
780 rpad(nvl(p_loc_city,' '),25,' ') ||
781 rpad(nvl(p_loc_state,' '),2,' ') ||
782 rpad(nvl(p_loc_zip,' '),20,' ') ||
783 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
784 rpad(nvl(p_loc_country_code,' '),2,' ') ||
785 rpad(nvl(l_phone,' '),10,' ') ||
786 rpad(nvl(p_loc_phone_extension,' '),6,' ') ||
787 rpad(nvl(p_loc_contact,' '),20,' ') ||
788 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
789 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
790 rpad(nvl(p_opt_address_line3,' '),40,' ') ||
791 rpad(nvl(p_opt_city,' '),25,' ') ||
792 rpad(nvl(p_opt_state,' '),2,' ') ||
793 rpad(nvl(p_opt_zip,' '),20,' ') ||
794 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
795 rpad(nvl(p_opt_country_code,' '),2,' ') ||
796 rpad(nvl(l_opt_phone,' '),10,' ') ||
797 rpad(nvl(p_opt_phone_extension,' '),6,' ') ||
798 rpad(nvl(p_opt_contact,' '),20,' ') ||
799 ' ' || -- filler blank 1
800 rpad(nvl(p_multi_state,' '),1,' ') ||
801 --bug#8791766
802 rpad(' ',30,' ')||p_end_of_rec; -- filler blank 30
803
804
805 hr_utility.set_location('Leaving.... :' || l_api_name,40);
806
807 return convert(l_buffer,g_character_set);
808 exception
809 when others then
810 hr_utility.set_location('Leaving.... with ERROR :' || l_api_name,200);
811 raise;
812 end fl_new_hire_record;
813 --
814 /****************************************************************
815 * This procedure formats ILLINOIS New Hire record *
816 * *
817 * *
818 ****************************************************************/
819 function il_new_hire_record
820 (
821 p_record_identifier in varchar2
822 ,p_national_identifier in varchar2
823 ,p_first_name in varchar2
824 ,p_middle_name in varchar2
825 ,p_last_name in varchar2
826 ,p_emp_address_line1 in varchar2
827 ,p_emp_address_line2 in varchar2
828 ,p_emp_city in varchar2
829 ,p_emp_state in varchar2
830 ,p_emp_zip in varchar2
831 ,p_emp_zip_extension in varchar2
832 ,p_date_of_hire in date
833 ,p_federal_id in varchar2
834 ,p_tax_unit_name in varchar2
835 ,p_loc_address_line1 in varchar2
836 ,p_loc_address_line2 in varchar2
837 ,p_loc_city in varchar2
838 ,p_loc_state in varchar2
839 ,p_loc_zip in varchar2
840 ,p_loc_zip_extension in varchar2
841 ,p_opt_address_line1 in varchar2
842 ,p_opt_address_line2 in varchar2
843 ,p_opt_city in varchar2
844 ,p_opt_state in varchar2
845 ,p_opt_zip in varchar2
846 ,p_opt_zip_extension in varchar2
847 ) return varchar2
848 is
849 l_api_name varchar2(61) := g_package_name || '.il_new_hire_record';
850 l_buffer varchar2(2000);
851 v_ssn varchar(9);
852 begin
853
854 hr_utility.set_location('Entering...: ' ||l_api_name,10);
855
856 hr_utility.trace('p_record_identifier= ' ||p_record_identifier);
857 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
858
859 hr_utility.trace('v_ssn= ' ||v_ssn);
860
861 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
862 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
863 hr_utility.trace('p_emp_city= ' || p_emp_city);
864 hr_utility.trace('p_emp_state= ' || p_emp_state);
865 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
866 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
867 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
868
869 hr_utility.trace('p_federal_id= ' || p_federal_id);
870 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
871 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
872 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
873 hr_utility.trace('p_loc_city= ' || p_loc_city);
874 hr_utility.trace('p_loc_state= ' || p_loc_state);
875 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
876 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
877
878 hr_utility.set_location(l_api_name,30);
879 --
880 -- format new hire rocord
881 --
882 l_buffer := p_record_identifier || -- 'W4'
883 rpad(nvl(v_ssn,' '),9,' ') ||
884 rpad(nvl(p_first_name,' '),16,' ') ||
885 rpad(nvl(p_middle_name,' '),16,' ') ||
886 rpad(nvl(p_last_name,' '),30,' ') ||
887 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
888 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
889 rpad(' ',40,' ') ||
890 rpad(nvl(p_emp_city,' '),25,' ') ||
891 rpad(nvl(p_emp_state,' '),2,' ') ||
892 rpad(nvl(p_emp_zip,' '),5,' ') ||
893 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
894 rpad(' ',42,' ') ||
895 rpad(' ',8,' ') ||
896 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
897 ' ' || -- 2 blank
898 rpad(nvl(p_federal_id,' '),9,' ') ||
899 rpad(' ',12,' ') ||
900 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
901 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
902 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
903 rpad(' ',40,' ') ||
904 rpad(nvl(p_loc_city,' '),25,' ') ||
905 rpad(nvl(p_loc_state,' '),2,' ') ||
906 rpad(nvl(p_loc_zip,' '),5,' ') ||
907 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
908 rpad(' ',42,' ') ||
909 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
910 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
911 rpad(' ',40,' ') ||
912 rpad(nvl(p_opt_city,' '),25,' ') ||
913 rpad(nvl(p_opt_state,' '),2,' ') ||
914 rpad(nvl(p_opt_zip,' '),5,' ') ||
915 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
916 rpad(' ',42,' ') ||
917 rpad(' ',50,' ') ;
918
919
920
921 hr_utility.set_location('Leaving.... :' || l_api_name,100);
922
923 return convert(l_buffer,g_character_set);
924 exception
925 when others then
926 hr_utility.set_location('Leaving.... :' || l_api_name,200);
927 raise;
928 end il_new_hire_record;
929
930 -- sjawid al
931
932 function al_new_hire_record
933 (
934 p_national_identifier in varchar2
935 ,p_dir_acc_number in varchar2
936 ,p_date_of_hire in date
937 ,p_indicator in varchar2
938 ,p_first_name in varchar2
939 ,p_middle_name in varchar2
940 ,p_last_name in varchar2
941 ,p_emp_address_line1 in varchar2
942 ,p_emp_address_line2 in varchar2
943 ,p_emp_address_line3 in varchar2
944 ,p_emp_city in varchar2
945 ,p_emp_state in varchar2
946 ,p_emp_zip in varchar2
947 ,p_emp_zip_extension in varchar2
948 ,p_federal_id in varchar2
949 ,p_tax_unit_name in varchar2
950 ,p_loc_address_line1 in varchar2
951 ,p_loc_address_line2 in varchar2
952 ,p_loc_address_line3 in varchar2
953 ,p_loc_city in varchar2
954 ,p_loc_state in varchar2
955 ,p_loc_zip in varchar2
956 ,p_blanks in varchar2
957 ) return varchar2
958 is
959 l_api_name varchar2(61) := g_package_name || '.al_new_hire_record';
960 l_buffer varchar2(2000);
961 v_ssn varchar(9);
962 function format_text(p_text in varchar2,p_length in number) return varchar2
963 is
964 begin
965 return (rpad(nvl(p_text,' '),p_length,' ') );
966 end format_text;
967 begin
968 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
969
970 l_buffer := format_text(v_ssn,9)|| --Social Security Number
971 -- format_text(p_dir_acc_number,10)||
972 lpad(nvl(p_dir_acc_number,' '),10,'0')|| --Account Number**
973 format_text(to_char(p_date_of_hire,'MMDDYY'),6)|| --Activity Date
974 format_text(p_indicator,'1')|| --Indicator
975 format_text(p_last_name||'/'||p_first_name||'/'||p_middle_name||' '||substr(p_middle_name,1,1),27)|| --Employee's Name
976 format_text(p_emp_address_line1||' '||p_emp_address_line2||' '||p_emp_address_line3,30)|| --Employee's Street Address
977 format_text(p_emp_city,20)|| --Employee's City Name
978 format_text(p_emp_state,2)|| --Employee's State Name
979 format_text(p_emp_zip||p_emp_zip_extension,9)||--Employee's ZIP + 4 ZIP Code
980 format_text(p_federal_id,9)|| --Employer's FEIN
981 format_text(p_tax_unit_name,20)|| --Employer's name
982 format_text(p_loc_address_line1||' '||p_loc_address_line2||' '||p_loc_address_line3,14)|| --employers address
983 format_text(p_loc_city,11)|| --employers city
984 format_text(p_loc_state,2)|| --employers state
985 format_text(p_loc_zip,5)|| --employers zip
986 format_text(' ',25);
987
988 return l_buffer;
989 end al_new_hire_record;
990 -- sjawid end al
991 --
992 /****************************************************************
993 * This procedure formats Texas T4 record *
994 * *
995 * *
996 ****************************************************************/
997 FUNCTION tx_t4_record
998 (
999 p_record_identifier in varchar2
1000 ,p_number_of_employee in number
1001 ) return varchar2
1002 IS
1003 l_api_name varchar2(61) := g_package_name || '.tx_t4_record';
1004 l_buffer varchar2(2000);
1005 begin
1006 --
1007 hr_utility.set_location('Entering:' ||l_api_name,10);
1008
1009 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1010 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
1011
1012 --
1013 -- format new hire rocord
1014 --
1015 l_buffer := upper(
1016 p_record_identifier ||
1017 lpad(to_char(p_number_of_employee),11,'0') ||
1018 lpad(' ',788,' '));
1019
1020 hr_utility.set_location(l_api_name,20);
1021
1022 return convert(l_buffer,g_character_set);
1023 exception
1024 when others then
1025 hr_utility.set_location('Leaving.... :' || l_api_name,30);
1026 raise;
1027 end tx_t4_record;
1028 --
1029 /****************************************************************
1030 * This procedure formats TEXAS New Hire record *
1031 * *
1032 * *
1033 ****************************************************************/
1034 function tx_new_hire_record
1035 (
1036 p_record_identifier in varchar2
1037 ,p_national_identifier in varchar2
1038 ,p_first_name in varchar2
1039 ,p_middle_name in varchar2
1040 ,p_last_name in varchar2
1041 ,p_emp_address_line1 in varchar2
1042 ,p_emp_address_line2 in varchar2
1043 ,p_emp_city in varchar2
1044 ,p_emp_state in varchar2
1045 ,p_emp_zip in varchar2
1046 ,p_emp_zip_extension in varchar2
1047 ,p_emp_country_code in varchar2
1048 ,p_emp_country_name in varchar2
1049 ,p_emp_country_zip in varchar2
1050 ,p_date_of_birth in date
1051 ,p_date_of_hire in date
1052 ,p_state_of_hire in varchar2
1053 ,p_federal_id in varchar2
1054 ,p_state_ein in varchar2
1055 ,p_tax_unit_name in varchar2
1056 ,p_loc_address_line1 in varchar2
1057 ,p_loc_address_line2 in varchar2
1058 ,p_loc_address_line3 in varchar2
1059 ,p_loc_city in varchar2
1060 ,p_loc_state in varchar2
1061 ,p_loc_zip in varchar2
1062 ,p_loc_zip_extension in varchar2
1063 ,p_loc_country_code in varchar2
1064 ,p_loc_country_name in varchar2
1065 ,p_loc_country_zip in varchar2
1066 ,p_opt_address_line1 in varchar2
1067 ,p_opt_address_line2 in varchar2
1068 ,p_opt_address_line3 in varchar2
1069 ,p_opt_city in varchar2
1070 ,p_opt_state in varchar2
1071 ,p_opt_zip in varchar2
1072 ,p_opt_zip_extension in varchar2
1073 ,p_opt_country_code in varchar2
1074 ,p_opt_country_name in varchar2
1075 ,p_opt_country_zip in varchar2
1076 ,p_salary in varchar2
1077 ,p_frequency in varchar2
1078 ) return varchar2
1079 is
1080 l_api_name varchar2(61) := g_package_name || '.tx_new_hire_record';
1081 l_buffer varchar2(2000);
1082 v_ssn varchar(9);
1083 begin
1084
1085 hr_utility.set_location('Entering...: ' || l_api_name,10);
1086
1087 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1088 hr_utility.trace('p_federal_id= ' || p_federal_id);
1089 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1090 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1091
1092 hr_utility.trace('v_ssn= ' || v_ssn);
1093 hr_utility.trace('p_first_name = ' || p_first_name);
1094 hr_utility.trace('p_middle_name = ' || p_middle_name);
1095 hr_utility.trace('p_last_name = ' || p_last_name);
1096 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1097 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1098 hr_utility.trace('p_emp_city= ' || p_emp_city);
1099 hr_utility.trace('p_emp_state= ' || p_emp_state);
1100 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1101 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1102 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1103 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1104 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
1105
1106 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1107 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1108 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
1109 hr_utility.trace('p_loc_city= ' || p_loc_city);
1110 hr_utility.trace('p_loc_state= ' || p_loc_state);
1111 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1112 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1113
1114 hr_utility.trace('p_salary= ' || p_salary);
1115 hr_utility.trace('p_frequency= ' || p_frequency);
1116
1117 hr_utility.set_location(l_api_name,30);
1118
1119 --
1120 -- format new hire rocord
1121 --
1122 l_buffer := upper(
1123 p_record_identifier || -- 'W4'
1124 rpad(nvl(v_ssn,' '),9,' ') || -- BUG4504074
1125 rpad(nvl(p_first_name,' '),16,' ') ||
1126 rpad(nvl(p_middle_name,' '),16,' ') ||
1127 rpad(nvl(p_last_name,' '),30,' ') ||
1128 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
1129 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
1130 rpad(' ',40,' ') ||
1131 rpad(nvl(p_emp_city,' '),25,' ') ||
1132 rpad(nvl(p_emp_state,' '),2,' ') ||
1133 rpad(nvl(p_emp_zip,' '),5,' ') ||
1134 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
1135 rpad(nvl(p_emp_country_code,' '),2,' ') ||
1136 rpad(nvl(p_emp_country_name,' '),25,' ') ||
1137 rpad(nvl(p_emp_country_zip,' '),15,' ') ||
1138 -- nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || -- BUG3954955
1139 -- nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || -- BUG3954955
1140 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || -- BUG 8515583
1141 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || -- BUG 8515583
1142 rpad(nvl(p_state_of_hire,' '),2,' ') ||
1143 rpad(nvl(p_federal_id,' '),9,' ') ||
1144 rpad(nvl(p_state_ein,' '),12,' ') ||
1145 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
1146 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
1147 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
1148 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
1149 rpad(nvl(p_loc_city,' '),25,' ') ||
1150 rpad(nvl(p_loc_state,' '),2,' ') ||
1151 rpad(nvl(p_loc_zip,' '),5,' ') ||
1152 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
1153 rpad(nvl(p_loc_country_code,' '),2,' ') ||
1154 rpad(nvl(p_loc_country_name,' '),25,' ') ||
1155 rpad(nvl(p_loc_country_zip,' '),15,' ') ||
1156 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
1157 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
1158 rpad(nvl(p_opt_address_line3,' '),40,' ') ||
1159 rpad(nvl(p_opt_city,' '),25,' ') ||
1160 rpad(nvl(p_opt_state,' '),2,' ') ||
1161 rpad(nvl(p_opt_zip,' '),5,' ') ||
1162 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
1163 rpad(nvl(p_opt_country_code,' '),2,' ') ||
1164 rpad(nvl(p_opt_country_name,' '),25,' ') ||
1165 rpad(nvl(p_opt_country_zip,' '),15,' ') ||
1166 rpad(' ',10,' ') ||
1167 rpad(' ',9) || -- rpad(nvl(p_salary,' '),9,'0') ||
1168 ' ' || -- rpad(nvl(p_frequency,' '),1,' ') ||
1169 rpad(' ',30,' ')) ;
1170
1171
1172 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1173
1174
1175 return convert(l_buffer,g_character_set);
1176 exception
1177 when others then
1178 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1179 raise;
1180 end tx_new_hire_record;
1181 --
1182 -- #10387522 start
1183 --
1184 /****************************************************************
1185 * This procedure formats SC New Hire Header Record *
1186 * *
1187 * *
1188 ****************************************************************/
1189 function sc_header_record
1190 (
1191 p_federal_id in varchar2
1192 ,p_tax_unit_name in varchar2
1193 ,p_add_line1 in varchar2
1194 ,p_add_line2 in varchar2
1195 ,p_city in varchar2
1196 ,p_state in varchar2
1197 ,p_zip in varchar2
1198 ,p_phone_no in varchar2
1199 ,p_record_identifier in varchar2
1200 ) return varchar2
1201 is
1202 l_api_name varchar2(61) := g_package_name || '.sc_header_record';
1203 l_buffer varchar2(2000);
1204 p_end_of_rec varchar2(2000);
1205 begin
1206
1207 hr_utility.set_location('Entering:' ||l_api_name,10);
1208
1209 hr_utility.trace('p_federal_id= ' || p_federal_id);
1210 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1211 hr_utility.trace('p_add_line1= ' || p_add_line1);
1212 hr_utility.trace('p_add_line2= ' || p_add_line2);
1213 hr_utility.trace('p_city= ' || p_city);
1214 hr_utility.trace('p_state= ' || p_state);
1215 hr_utility.trace('p_zip= ' || p_zip);
1216 hr_utility.trace('p_phone_no= ' || p_phone_no);
1217 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1218
1219 hr_utility.set_location(l_api_name,30);
1220
1221 --
1222 -- format new hire rocord
1223 --
1224
1225 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1226
1227 l_buffer := upper(
1228 rpad(nvl(p_federal_id,' '),9,' ') ||
1229 rpad(nvl(p_tax_unit_name,' '),30,' ') ||
1230 rpad(nvl(p_add_line1,' '),25,' ') ||
1231 rpad(nvl(p_add_line2,' '),25,' ') ||
1232 rpad(nvl(p_city,' '),22,' ') ||
1233 rpad(nvl(p_state,' '),2,' ') ||
1234 rpad(nvl(p_zip,' '),9,' ') ||
1235 rpad(nvl(p_phone_no,' '),10,' ') ||
1236 lpad(' ',17,' ')|| --blank 17
1237 rpad(nvl(p_record_identifier,' '),1,' '))||p_end_of_rec;
1238
1239
1240 hr_utility.set_location('Leaving.... :' || l_api_name,40);
1241
1242 return convert(l_buffer,g_character_set);
1243 exception
1244 when others then
1245 hr_utility.set_location('Leaving.... :' || l_api_name,70);
1246 raise;
1247 end sc_header_record;
1248 --
1249 /****************************************************************
1250 * This procedure formats SC New Hire Record *
1251 * *
1252 * *
1253 ****************************************************************/
1254 --
1255 function sc_new_hire_record
1256 (
1257 p_national_identifier in varchar2
1258 ,p_first_name in varchar2
1259 ,p_middle_name in varchar2
1260 ,p_last_name in varchar2
1261 ,p_add_line1 in varchar2
1262 ,p_add_line2 in varchar2
1263 ,p_city in varchar2
1264 ,p_state in varchar2
1265 ,p_zip in varchar2
1266 ,p_date_of_birth in date
1267 ,p_date_of_hire in date
1268 ,p_record_identifier in varchar2
1269 ) return varchar2
1270 is
1271 l_api_name varchar2(61) := g_package_name || '.sc_new_hire_record';
1272 l_buffer varchar2(2000);
1273 v_ssn varchar(9);
1274 p_end_of_rec varchar2(2000);
1275 begin
1276 hr_utility.set_location('Entering:' ||l_api_name,10);
1277
1278 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
1279
1280 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
1281 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1282 else
1283 v_ssn := p_national_identifier;
1284 end if;
1285 hr_utility.trace('v_ssn= ' || v_ssn);
1286 hr_utility.trace('p_first_name= ' || p_first_name);
1287 hr_utility.trace('p_middle_name= ' || p_middle_name);
1288 hr_utility.trace('p_last_name= ' || p_last_name);
1289 hr_utility.trace('p_add_line1= ' || p_add_line1);
1290 hr_utility.trace('p_add_line2= ' || p_add_line2);
1291 hr_utility.trace('p_city= ' || p_city);
1292 hr_utility.trace('p_state= ' || p_state);
1293 hr_utility.trace('p_zip= ' || p_zip);
1294 hr_utility.trace('p_date_of_birth= ' || to_char(p_date_of_birth));
1295 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
1296 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1297
1298 --
1299 -- format new hire rocord
1300 --
1301
1302 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1303
1304 l_buffer := upper(
1305 rpad(nvl(v_ssn,' '),9,' ') ||
1306 rpad(nvl(p_last_name,' '),15,' ')||
1307 rpad(nvl(p_first_name,' '),20,' ')||
1308 rpad(nvl(substr(p_middle_name,1,1),' '),1,' ')||
1309 rpad(nvl(p_add_line1,' '),25,' ')||
1310 rpad(nvl(p_add_line2,' '),25,' ')||
1311 rpad(nvl(p_city,' '),22,' ')||
1312 rpad(nvl(p_state,' '),2,' ') ||
1313 rpad(nvl(p_zip,' '),9,' ') ||
1314 rpad(nvl(to_char(p_date_of_birth,'YYYYMMDD'),' '),8,' ') ||
1315 rpad(nvl(to_char(p_date_of_hire,'YYYYMMDD'),' '),8,' ') ||
1316 lpad(' ',5,' '))||
1317 p_record_identifier ||p_end_of_rec;
1318
1319 hr_utility.trace('l_buffer = ' || l_buffer);
1320
1321
1322 hr_utility.set_location('Leaving.... :' || l_api_name,60);
1323
1324 return convert(l_buffer,g_character_set);
1325 exception
1326 when others then
1327 hr_utility.set_location('Leaving.... :' || l_api_name,70);
1328 raise;
1329 end sc_new_hire_record;
1330 --
1331 /****************************************************************
1332 * This procedure formats MI New Hire Record *
1333 * *
1334 * *
1335 ****************************************************************/
1336 --
1337 function mi_new_hire_record
1338 (
1339 p_national_identifier in varchar2
1340 ,p_first_name in varchar2
1341 ,p_middle_name in varchar2
1342 ,p_last_name in varchar2
1343 ,p_tax_unit_name in varchar2
1344 ,p_federal_id in varchar2
1345 ,p_loc_address_line1 in varchar2
1346 ,p_loc_address_line2 in varchar2
1347 ,p_loc_city in varchar2
1348 ,p_loc_state in varchar2
1349 ,p_loc_zip in varchar2
1350 ,p_loc_zip_extension in varchar2
1351 ,p_emp_address_line1 in varchar2
1352 ,p_emp_address_line2 in varchar2
1353 ,p_emp_city in varchar2
1354 ,p_emp_state in varchar2
1355 ,p_emp_zip in varchar2
1356 ,p_emp_zip_extension in varchar2
1357 ,p_date_of_birth in date
1358 ,p_date_of_hire in date
1359 ,p_dl_number in varchar2
1360 ) return varchar2
1361 is
1362 l_api_name varchar2(61) := g_package_name || '.mi_new_hire_record';
1363 l_buffer varchar2(2000);
1364 v_ssn varchar(9);
1365 p_end_of_rec varchar2(2000);
1366 begin
1367 hr_utility.set_location('Entering:' ||l_api_name,10);
1368
1369 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
1370
1371 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
1372 hr_utility.set_location('National identifier has hyphen' ,12);
1373 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1374 else
1375 hr_utility.set_location('National identifier has no hyphen' ,14);
1376 v_ssn := p_national_identifier;
1377 end if;
1378 hr_utility.trace('v_ssn= ' || v_ssn);
1379 hr_utility.trace('p_first_name= ' || p_first_name);
1380 hr_utility.trace('p_middle_name= ' || p_middle_name);
1381 hr_utility.trace('p_last_name= ' || p_last_name);
1382 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1383 hr_utility.trace('p_federal_id= ' || p_federal_id);
1384 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1385 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1386 hr_utility.trace('p_loc_city= ' || p_loc_city);
1387 hr_utility.trace('p_loc_state= ' || p_loc_state);
1388 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1389 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1390 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1391 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1392 hr_utility.trace('p_emp_city= ' || p_emp_city);
1393 hr_utility.trace('p_emp_state= ' || p_emp_state);
1394 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1395 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1396 hr_utility.trace('p_date_of_birth= ' || to_char(p_date_of_birth));
1397 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
1398 hr_utility.trace('p_dl_number= ' || p_dl_number);
1399
1400 --
1401 -- format new hire rocord
1402 --
1403
1404 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1405
1406 l_buffer := upper(
1407 rpad(nvl(v_ssn,' '),9,' ') ||
1408 rpad(nvl(p_first_name,' '),16,' ')||
1409 rpad(nvl(substr(p_middle_name,1,1),' '),1,' ')||
1410 rpad(nvl(p_last_name,' '),20,' ')||
1411 rpad(nvl(p_tax_unit_name,' '),40,' ')||
1412 rpad(nvl(p_federal_id,' '),9,' ')||
1413 rpad(nvl(p_loc_address_line1,' '),35,' ')||
1414 rpad(nvl(p_loc_address_line2,' '),35,' ')||
1415 rpad(nvl(p_loc_city,' '),25,' ')||
1416 rpad(nvl(p_loc_state,' '),2,' ') ||
1417 rpad(nvl(p_loc_zip,' '),5,' ') ||
1418 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
1419 rpad(nvl(p_emp_address_line1,' '),35,' ')||
1420 rpad(nvl(p_emp_address_line2,' '),35,' ')||
1421 rpad(nvl(p_emp_city,' '),25,' ')||
1422 rpad(nvl(p_emp_state,' '),2,' ') ||
1423 rpad(nvl(p_emp_zip,' '),5,' ') ||
1424 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
1425 lpad(' ',2,' ')||
1426 rpad(nvl(to_char(p_date_of_birth,'MMDDYYYY'),' '),8,' ') ||
1427 rpad(nvl(to_char(p_date_of_hire,'MMDDYYYY'),' '),8,' ') ||
1428 rpad(nvl(p_dl_number,' '),16,' '))||
1429 p_end_of_rec;
1430
1431 hr_utility.trace('l_buffer = ' || l_buffer);
1432
1433
1434 hr_utility.set_location('Leaving.... :' || l_api_name,60);
1435
1436 return convert(l_buffer,g_character_set);
1437 exception
1438 when others then
1439 hr_utility.set_location('Leaving.... :' || l_api_name,70);
1440 raise;
1441 end mi_new_hire_record;
1442 --
1443 /****************************************************************
1444 * This procedure formats Ohio Header record *
1445 * *
1446 * *
1447 ****************************************************************/
1448 --
1449 function oh_header_record
1450 (
1451 p_record_identifier in varchar2
1452 ,p_report_date in date
1453 ,p_number_of_employee in number
1454 ) return varchar2
1455 is
1456 l_api_name varchar2(61) := g_package_name || '.oh_header_record';
1457 l_buffer varchar2(2000);
1458 l_oh_delimiter varchar2(1) := ',';
1459 begin
1460 --
1461 hr_utility.set_location('Entering:' ||l_api_name,10);
1462
1463 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1464 hr_utility.trace('p_report_date = ' || to_char(p_report_date));
1465 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
1466
1467 --
1468 -- format new hire rocord
1469 --
1470 l_buffer := upper(
1471 p_record_identifier || l_oh_delimiter ||
1472 to_char(p_report_date,'CCYYMMDD') || l_oh_delimiter ||
1473 to_char(p_number_of_employee) || l_oh_delimiter ||
1474 ' ')||g_eol;
1475
1476 hr_utility.set_location(l_api_name,20);
1477
1478 return convert(l_buffer,g_character_set);
1479 exception
1480 when others then
1481 hr_utility.set_location('Leaving.... :' || l_api_name,30);
1482 raise;
1483 end oh_header_record;
1484 /****************************************************************
1485 * This procedure formats OH New Hire Record *
1486 * *
1487 * *
1488 ****************************************************************/
1489 --
1490 function oh_new_hire_record
1491 (
1492 p_record_identifier in varchar2
1493 ,p_national_identifier in varchar2
1494 ,p_first_name in varchar2
1495 ,p_middle_name in varchar2
1496 ,p_last_name in varchar2
1497 ,p_emp_address_line1 in varchar2
1498 ,p_emp_address_line2 in varchar2
1499 ,p_emp_address_line3 in varchar2
1500 ,p_emp_city in varchar2
1501 ,p_emp_state in varchar2
1502 ,p_emp_zip in varchar2
1503 ,p_emp_zip_extension in varchar2
1504 ,p_emp_country_code in varchar2
1505 ,p_emp_country_name in varchar2
1506 ,p_emp_country_zip in varchar2
1507 ,p_date_of_birth in date
1508 ,p_date_of_hire in date
1509 ,p_state_of_hire in varchar2
1510 ,p_gender in varchar2
1511 ,p_left_work in varchar2
1512 ,p_federal_id in varchar2
1513 ,p_inc_tax_credit in varchar2
1514 ,p_state_ein in varchar2
1515 ,p_tax_unit_name in varchar2
1516 ,p_loc_address_line1 in varchar2
1517 ,p_loc_address_line2 in varchar2
1518 ,p_loc_address_line3 in varchar2
1519 ,p_loc_city in varchar2
1520 ,p_loc_state in varchar2
1521 ,p_loc_zip in varchar2
1522 ,p_loc_zip_extension in varchar2
1523 ,p_loc_country_code in varchar2
1524 ,p_loc_country_name in varchar2
1525 ,p_loc_country_zip in varchar2
1526 ,p_opt_address_line1 in varchar2
1527 ,p_opt_address_line2 in varchar2
1528 ,p_opt_address_line3 in varchar2
1529 ,p_opt_city in varchar2
1530 ,p_opt_state in varchar2
1531 ,p_opt_zip in varchar2
1532 ,p_opt_zip_extension in varchar2
1533 ,p_opt_country_code in varchar2
1534 ,p_opt_country_name in varchar2
1535 ,p_opt_country_zip in varchar2
1536 ,p_emp_ic in varchar2
1537 ,p_ic_pay_date in date
1538 ,p_ic_length in number
1539 ) return varchar2
1540 is
1541 l_api_name varchar2(61) := g_package_name || '.oh_new_hire_record';
1542 l_buffer varchar2(2000);
1543 v_ssn varchar2(9);
1544 l_oh_delimiter varchar2(1) := ',';
1545 begin
1546
1547 hr_utility.set_location('Entering...: ' || l_api_name,10);
1548
1549 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1550 hr_utility.trace('p_federal_id= ' || p_federal_id);
1551 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1552
1553 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
1554 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1555 else
1556 v_ssn := p_national_identifier;
1557 end if;
1558
1559 hr_utility.trace('v_ssn= ' || v_ssn);
1560 hr_utility.trace('p_first_name = ' || p_first_name);
1561 hr_utility.trace('p_middle_name = ' || p_middle_name);
1562 hr_utility.trace('p_last_name = ' || p_last_name);
1563 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1564 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1565 hr_utility.trace('p_emp_city= ' || p_emp_city);
1566 hr_utility.trace('p_emp_state= ' || p_emp_state);
1567 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1568 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1569 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
1570 hr_utility.trace('p_emp_country_name= ' || p_emp_country_name);
1571 hr_utility.trace('p_emp_country_zip= ' || p_emp_country_zip);
1572 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1573 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1574 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
1575 hr_utility.trace('p_gender= ' || p_gender);
1576 hr_utility.trace('p_left_work= ' || p_left_work);
1577 hr_utility.trace('p_federal_id= ' || p_federal_id);
1578 hr_utility.trace('p_inc_tax_credit= ' || p_inc_tax_credit);
1579 hr_utility.trace('p_state_ein= ' || p_state_ein);
1580 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1581 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1582 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1583 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
1584 hr_utility.trace('p_loc_city= ' || p_loc_city);
1585 hr_utility.trace('p_loc_state= ' || p_loc_state);
1586 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1587 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1588
1589
1590
1591 hr_utility.set_location(l_api_name,30);
1592
1593 --
1594 -- format new hire rocord
1595 --
1596 l_buffer := upper( p_record_identifier || l_oh_delimiter ||
1597 v_ssn || l_oh_delimiter ||
1598 replace(nvl(p_first_name,' '),l_oh_delimiter,' ')||l_oh_delimiter||
1599 replace(nvl(p_middle_name,' '),l_oh_delimiter,' ')||l_oh_delimiter||
1600 replace(p_last_name,l_oh_delimiter,' ') || l_oh_delimiter ||
1601 replace(nvl(p_emp_address_line1,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1602 replace(nvl(p_emp_address_line2,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1603 replace(nvl(p_emp_address_line3,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1604 replace(nvl(p_emp_city,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1605 nvl(p_emp_state,' ') || l_oh_delimiter ||
1606 nvl(p_emp_zip,' ') || l_oh_delimiter ||
1607 nvl(p_emp_zip_extension,' ') || l_oh_delimiter ||
1608 nvl(p_emp_country_code,' ') || l_oh_delimiter ||
1609 nvl(p_emp_country_name,' ') || l_oh_delimiter ||
1610 nvl(p_emp_country_zip,' ') || l_oh_delimiter ||
1611 nvl(to_char(p_date_of_birth,'CCYYMMDD'),' ') || l_oh_delimiter ||
1612 nvl(to_char(p_date_of_hire,'CCYYMMDD'),' ') || l_oh_delimiter ||
1613 nvl(p_state_of_hire,' ') || l_oh_delimiter ||
1614 nvl(p_gender,' ') || l_oh_delimiter ||
1615 nvl(p_left_work,' ') || l_oh_delimiter ||
1616 nvl(p_federal_id,' ') || l_oh_delimiter ||
1617 nvl(p_inc_tax_credit,' ') || l_oh_delimiter ||
1618 nvl(p_state_ein,' ') || l_oh_delimiter ||
1619 replace(nvl(p_tax_unit_name,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1620 replace(nvl(p_loc_address_line1,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1621 replace(nvl(p_loc_address_line2,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1622 replace(nvl(p_loc_address_line3,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1623 replace(nvl(p_loc_city,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1624 nvl(p_loc_state,' ') || l_oh_delimiter ||
1625 nvl(p_loc_zip,' ') || l_oh_delimiter ||
1626 nvl(p_loc_zip_extension,' ') || l_oh_delimiter ||
1627 nvl(p_loc_country_code,' ') || l_oh_delimiter ||
1628 replace(nvl(p_loc_country_name,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1629 nvl(p_loc_country_zip,' ') || l_oh_delimiter ||
1630 replace(nvl(p_opt_address_line1,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1631 replace(nvl(p_opt_address_line2,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1632 replace(nvl(p_opt_address_line3,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1633 replace(nvl(p_opt_city,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1634 replace(nvl(p_opt_state,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1635 nvl(p_opt_zip,' ') || l_oh_delimiter ||
1636 nvl(p_opt_zip_extension,' ') || l_oh_delimiter ||
1637 nvl(p_opt_country_code,' ') || l_oh_delimiter ||
1638 replace(nvl(p_opt_country_name,' '),l_oh_delimiter,' ') || l_oh_delimiter ||
1639 nvl(p_opt_country_zip,' ') || l_oh_delimiter ||
1640 nvl(p_emp_ic,' ') || l_oh_delimiter ||
1641 nvl(to_char(p_ic_pay_date,'CCYYMMDD'),' ') || l_oh_delimiter ||
1642 nvl(to_char(p_ic_length) ,' ')) || l_oh_delimiter ||
1643 g_eol;
1644
1645
1646 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1647
1648
1649 return convert(l_buffer,g_character_set);
1650 exception
1651 when others then
1652 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1653 raise;
1654 end oh_new_hire_record;
1655 --
1656 /****************************************************************
1657 * This procedure formats PA new hire report *
1658 * *
1659 * *
1660 ****************************************************************/
1661 --
1662 function pa_new_hire_record
1663 (
1664 p_record_identifier in varchar2
1665 ,p_federal_id in varchar2
1666 ,p_tax_unit_name in varchar2
1667 ,p_loc_address_line1 in varchar2
1668 ,p_loc_address_line2 in varchar2
1669 ,p_loc_address_line3 in varchar2
1670 ,p_loc_city in varchar2
1671 ,p_loc_state in varchar2
1672 ,p_loc_zip in varchar2
1673 ,p_loc_zip_extension in varchar2
1674 ,p_contact_first_name in varchar2
1675 ,p_contact_last_name in varchar2
1676 ,p_contact_ph_no in varchar2
1677 ,p_contact_ph_no_ext in number
1678 ,p_national_identifier in varchar2
1679 ,p_first_name in varchar2
1680 ,p_middle_name in varchar2
1681 ,p_last_name in varchar2
1682 ,p_date_of_birth in date
1683 ,p_state_of_hire in varchar2
1684 ,p_date_of_hire in date
1685 ,p_emp_address_line1 in varchar2
1686 ,p_emp_address_line2 in varchar2
1687 ,p_emp_address_line3 in varchar2
1688 ,p_emp_city in varchar2
1689 ,p_emp_state in varchar2
1690 ,p_emp_zip in varchar2
1691 ,p_emp_zip_extension in varchar2
1692 ) return varchar2
1693 is
1694 l_api_name varchar2(61) := g_package_name || '.pa_new_hire_record';
1695 l_buffer varchar2(2000);
1696 v_ssn varchar2(9);
1697 l_pa_delimiter varchar2(1) := fnd_global.local_chr(9);
1698 l_phone varchar(20);
1699 begin
1700
1701 hr_utility.set_location('Entering...: ' || l_api_name,10);
1702
1703 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1704 hr_utility.trace('p_federal_id= ' || p_federal_id);
1705 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1706
1707 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
1708 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1709 else
1710 v_ssn := p_national_identifier;
1711 end if;
1712
1713 l_phone := replace(replace(replace(nvl(p_contact_ph_no,' '),'-',NULL),'(',NULL),')',NULL);
1714
1715 hr_utility.trace('v_ssn= ' || v_ssn);
1716 hr_utility.trace('p_first_name = ' || p_first_name);
1717 hr_utility.trace('p_middle_name = ' || p_middle_name);
1718 hr_utility.trace('p_last_name = ' || p_last_name);
1719 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1720 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1721 hr_utility.trace('p_emp_city= ' || p_emp_city);
1722 hr_utility.trace('p_emp_state= ' || p_emp_state);
1723 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1724 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1725 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1726 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1727 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
1728 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1729 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1730 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
1731 hr_utility.trace('p_loc_city= ' || p_loc_city);
1732 hr_utility.trace('p_loc_state= ' || p_loc_state);
1733 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1734 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1735 hr_utility.trace('p_contact_last_name= ' || p_contact_last_name);
1736 hr_utility.trace('p_contact_first_name=' || p_contact_first_name);
1737 hr_utility.trace('p_contact_ph_no = ' || p_contact_ph_no );
1738 hr_utility.trace('p_contact_ph_no_ext =' || p_contact_ph_no_ext );
1739
1740
1741
1742 hr_utility.set_location(l_api_name,30);
1743
1744 --
1745 -- format new hire rocord
1746 --
1747 l_buffer := upper( rpad(p_record_identifier,2,' ') || l_pa_delimiter ||
1748 p_federal_id || l_pa_delimiter ||
1749 rpad(replace(nvl(p_tax_unit_name,' '),l_pa_delimiter,' '),45,' ') || l_pa_delimiter ||
1750 rpad(replace(nvl(p_loc_address_line1,' '),l_pa_delimiter,' '),40,' ') || l_pa_delimiter ||
1751 rpad(replace(nvl(p_loc_address_line2,' '),l_pa_delimiter,' '),40,' ') || l_pa_delimiter ||
1752 rpad(replace(nvl(p_loc_address_line3,' '),l_pa_delimiter,' '),40,' ') || l_pa_delimiter ||
1753 rpad(replace(nvl(p_loc_city,' '),l_pa_delimiter,' '),25,' ') || l_pa_delimiter ||
1754 rpad(nvl(p_loc_state,' '),2,' ') || l_pa_delimiter ||
1755 rpad(nvl(p_loc_zip,' '),5,' ') || l_pa_delimiter ||
1756 rpad(nvl(p_loc_zip_extension,' '),4,' ') || l_pa_delimiter ||
1757 rpad(replace(nvl(p_contact_first_name,' '),l_pa_delimiter,' '),16,' ') || l_pa_delimiter ||
1758 rpad(replace(nvl(p_contact_last_name,' '),l_pa_delimiter,' '),30,' ') || l_pa_delimiter ||
1759 rpad(nvl(l_phone,''),10,' ') || l_pa_delimiter ||
1760 rpad(nvl(p_contact_ph_no_ext,''),5,' ') || l_pa_delimiter ||
1761 v_ssn || l_pa_delimiter ||
1762 rpad(replace(nvl(p_first_name,' '),l_pa_delimiter,' '),16,' ')||l_pa_delimiter||
1763 rpad(replace(nvl(p_middle_name,' '),l_pa_delimiter,' '),16,' ')||l_pa_delimiter||
1764 rpad(replace(p_last_name,l_pa_delimiter,' '),30,' ') || l_pa_delimiter ||
1765 nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || l_pa_delimiter ||
1766 nvl(p_state_of_hire,' ') || l_pa_delimiter ||
1767 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || l_pa_delimiter ||
1768 rpad(replace(nvl(p_emp_address_line1,' '),l_pa_delimiter,' '),40,' ') || l_pa_delimiter ||
1769 rpad(replace(nvl(p_emp_address_line2,' '),l_pa_delimiter,' '),40,' ') || l_pa_delimiter ||
1770 rpad(replace(nvl(p_emp_address_line3,' '),l_pa_delimiter,' '),40,' ') || l_pa_delimiter ||
1771 rpad(replace(nvl(p_emp_city,' '),l_pa_delimiter,' '),25,' ') || l_pa_delimiter ||
1772 nvl(p_emp_state,' ') || l_pa_delimiter ||
1773 rpad(nvl(p_emp_zip,' '),5,' ') || l_pa_delimiter ||
1774 rpad(nvl(p_emp_zip_extension,' '),4,' ')) || l_pa_delimiter||
1775 rpad(' ',4,' ')|| l_pa_delimiter||
1776 rpad(' ',15,' ')|| l_pa_delimiter||
1777 g_eol;
1778
1779
1780 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1781
1782
1783 return convert(l_buffer,g_character_set);
1784 exception
1785 when others then
1786 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1787 raise;
1788 end pa_new_hire_record;
1789 --
1790 /****************************************************************
1791 * This procedure formats AR new hire record *
1792 * *
1793 * *
1794 ****************************************************************/
1795 function ar_new_hire_record
1796 (
1797 p_record_identifier in varchar2
1798 ,p_first_name in varchar2
1799 ,p_middle_name in varchar2
1800 ,p_last_name in varchar2
1801 ,p_national_identifier in varchar2
1802 ,p_emp_address_line1 in varchar2
1803 ,p_emp_address_line2 in varchar2
1804 ,p_emp_address_line3 in varchar2
1805 ,p_emp_city in varchar2
1806 ,p_emp_state in varchar2
1807 ,p_emp_zip in varchar2
1808 ,p_emp_zip_extension in varchar2
1809 ,p_emp_country_code in varchar2
1810 ,p_date_of_birth in date
1811 ,p_date_of_hire in date
1812 ,p_state_of_hire in varchar2
1813 ,p_federal_id in varchar2
1814 ,p_sit_company_state_id in varchar2
1815 ,p_tax_unit_name in varchar2
1816 ,p_loc_address_line1 in varchar2
1817 ,p_loc_address_line2 in varchar2
1818 ,p_loc_address_line3 in varchar2
1819 ,p_loc_city in varchar2
1820 ,p_loc_state in varchar2
1821 ,p_loc_zip in varchar2
1822 ,p_loc_zip_extension in varchar2
1823 ,p_loc_country_code in varchar2
1824 ,p_loc_phone in varchar2
1825 ,p_loc_phone_extension in varchar2
1826 ,p_loc_contact in varchar2
1827 ,p_opt_address_line1 in varchar2
1828 ,p_opt_address_line2 in varchar2
1829 ,p_opt_address_line3 in varchar2
1830 ,p_opt_city in varchar2
1831 ,p_opt_state in varchar2
1832 ,p_opt_zip in varchar2
1833 ,p_opt_zip_extension in varchar2
1834 ,p_opt_country_code in varchar2
1835 ,p_opt_phone in varchar2
1836 ,p_opt_phone_extension in varchar2
1837 ,p_opt_contact in varchar2
1838 ) return varchar2
1839 is
1840 l_api_name varchar2(61) := g_package_name || '.ar_new_hire_record';
1841 l_buffer varchar2(2000);
1842 v_ssn varchar(9);
1843 l_phone varchar(20);
1844 p_end_of_rec varchar2(2000);
1845 l_first_name varchar2(3000);
1846 l_middle_name varchar2(3000);
1847 l_last_name varchar2(3000);
1848 l_emp_city varchar2(3000);
1849 l_federal_id varchar2(10);
1850
1851 begin
1852
1853 hr_utility.set_location('Entering...: ' || l_api_name,10);
1854
1855 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1856
1857 hr_utility.trace('p_first_name= ' || p_first_name);
1858 hr_utility.trace('p_middle_name= ' || p_middle_name);
1859 hr_utility.trace('p_last_name= ' || p_last_name);
1860
1861 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
1862 hr_utility.set_location('National identifier has hyphen' ,12);
1863 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1864 else
1865 hr_utility.set_location('National identifier has no hyphen' ,14);
1866 v_ssn := p_national_identifier;
1867 end if;
1868
1869 hr_utility.trace('v_ssn= ' || v_ssn);
1870 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1871 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1872 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line3);
1873 hr_utility.trace('p_emp_city= ' || p_emp_city);
1874 hr_utility.trace('p_emp_state= ' || p_emp_state);
1875 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1876 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1877 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
1878 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1879 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1880
1881 hr_utility.trace('p_federal_id= ' || p_federal_id);
1882 hr_utility.trace('p_sit_company_state_id=' || p_sit_company_state_id);
1883 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1884 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1885 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1886 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
1887 hr_utility.trace('p_loc_city= ' || p_loc_city);
1888 hr_utility.trace('p_loc_state= ' || p_loc_state);
1889 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1890 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1891 hr_utility.trace('p_loc_country_code= ' || p_loc_country_code);
1892 hr_utility.trace('p_loc_phone= ' || p_loc_phone);
1893
1894 l_phone := replace(replace(replace(nvl(p_loc_phone,' '),'-',NULL),'(',NULL),')',NULL);
1895
1896 hr_utility.trace('l_phone= ' || l_phone);
1897 hr_utility.trace('p_loc_contact= ' || p_loc_contact);
1898
1899
1900 hr_utility.set_location(l_api_name,30);
1901 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1902 l_first_name := remove_special_characters(p_first_name);
1903 l_middle_name := remove_special_characters(p_middle_name);
1904 l_last_name := remove_special_characters(p_last_name,'-');
1905 l_emp_city := remove_special_characters(p_emp_city,'-');
1906
1907 l_federal_id := replace(nvl(p_federal_id,' '),'-',NULL);
1908
1909 check_min_length(l_first_name,1,'First Name','AR');
1910
1911 if l_middle_name IS NOT NULL
1912 then
1913 check_min_length(l_middle_name,1,'Midlle Name','AR');
1914 end if;
1915
1916 check_min_length(l_last_name,1,'Last Name','AR');
1917 check_min_length(p_emp_address_line1,2,'Employee Address Line1','AR');
1918 check_min_length(l_emp_city,2,'Employee City','AR');
1919
1920 check_min_length(p_tax_unit_name,2,'Employer Name','AR');
1921 check_min_length(p_loc_address_line1,2,'Employer Address Line1','AR');
1922 check_min_length(p_loc_city,2,'Employer City','AR');
1923
1924 hr_utility.set_location(l_api_name,40);
1925 --
1926 -- format new hire rocord
1927 --
1928 l_buffer := p_record_identifier || -- 'AR Newhire Record'
1929 '1.00' ||
1930 rpad(nvl(l_first_name,' '),16,' ') ||
1931 rpad(nvl(l_middle_name,' '),16,' ') ||
1932 rpad(nvl(l_last_name,' '),30,' ') ||
1933 rpad(nvl(v_ssn,' '),9,' ') ||
1934 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
1935 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
1936 rpad(nvl(p_emp_address_line3,' '),40,' ') ||
1937 rpad(nvl(l_emp_city,' '),25,' ') ||
1938 rpad(nvl(p_emp_state,' '),2,' ') ||
1939 rpad(nvl(p_emp_zip,' '),20,' ') ||
1940 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
1941 rpad(nvl(p_emp_country_code,' '),2,' ') ||
1942 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') ||
1943 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') ||
1944 rpad(nvl(p_state_of_hire,' '),2,' ') ||
1945 ' '||-- filler blank
1946 rpad(nvl(l_federal_id,' '),9,' ') ||
1947 rpad(nvl(p_sit_company_state_id,' '),12,' ') ||
1948 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
1949 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
1950 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
1951 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
1952 rpad(nvl(p_loc_city,' '),25,' ') ||
1953 rpad(nvl(p_loc_state,' '),2,' ') ||
1954 rpad(nvl(p_loc_zip,' '),20,' ') ||
1955 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
1956 rpad(nvl(p_loc_country_code,' '),2,' ') ||
1957 rpad(nvl(l_phone,' '),10,' ') ||
1958 rpad(nvl(p_loc_phone_extension,' '),6,' ') ||
1959 rpad(nvl(p_loc_contact,' '),20,' ') ||
1960 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
1961 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
1962 rpad(nvl(p_opt_address_line3,' '),40,' ') ||
1963 rpad(nvl(p_opt_city,' '),25,' ') ||
1964 rpad(nvl(p_opt_state,' '),2,' ') ||
1965 rpad(nvl(p_opt_zip,' '),20,' ') ||
1966 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
1967 rpad(nvl(p_opt_country_code,' '),2,' ') ||
1968 rpad(nvl(p_opt_phone,' '),10,' ') ||
1969 rpad(nvl(p_opt_phone_extension,' '),6,' ') ||
1970 rpad(nvl(p_opt_contact,' '),20,' ') ||
1971 rpad(' ',32,' ')||p_end_of_rec;
1972
1973 hr_utility.set_location(l_api_name,50);
1974 hr_utility.trace('l_buffer= ' || l_buffer);
1975 hr_utility.set_location('Leaving.... :' || l_api_name,40);
1976
1977 return convert(l_buffer,g_character_set);
1978 exception
1979 when others then
1980 hr_utility.set_location(l_api_name,60);
1981 hr_utility.set_location('Leaving.... with ERROR :' || l_api_name,200);
1982 raise;
1983 end ar_new_hire_record;
1984 --
1985 /****************************************************************
1986 * This procedure formats MD new hire record *
1987 * *
1988 * *
1989 ****************************************************************/
1990 function md_new_hire_record
1991 (
1992 p_record_identifier in varchar2
1993 ,p_first_name in varchar2
1994 ,p_middle_name in varchar2
1995 ,p_last_name in varchar2
1996 ,p_national_identifier in varchar2
1997 ,p_emp_address_line1 in varchar2
1998 ,p_emp_address_line2 in varchar2
1999 ,p_emp_address_line3 in varchar2
2000 ,p_emp_city in varchar2
2001 ,p_emp_state in varchar2
2002 ,p_emp_zip in varchar2
2003 ,p_emp_zip_extension in varchar2
2004 ,p_emp_country_code in varchar2
2005 ,p_date_of_birth in date
2006 ,p_date_of_hire in date
2007 ,p_state_of_hire in varchar2
2008 ,p_medical_insr_avail in varchar2
2009 ,p_federal_id in varchar2
2010 ,p_suin_company_state_id in varchar2
2011 ,p_tax_unit_name in varchar2
2012 ,p_loc_address_line1 in varchar2
2013 ,p_loc_address_line2 in varchar2
2014 ,p_loc_address_line3 in varchar2
2015 ,p_loc_city in varchar2
2016 ,p_loc_state in varchar2
2017 ,p_loc_zip in varchar2
2018 ,p_loc_zip_extension in varchar2
2019 ,p_loc_country_code in varchar2
2020 ,p_loc_phone in varchar2
2021 ,p_loc_phone_extension in varchar2
2022 ,p_loc_contact in varchar2
2023 ,p_gender in varchar2
2024 ,p_loc_fax in varchar2
2025 ,p_loc_email in varchar2
2026 ,p_salary in varchar2
2027 ,p_sal_frequency in varchar2
2028 ) return varchar2
2029 is
2030 l_api_name varchar2(61) := g_package_name || '.md_new_hire_record';
2031 l_buffer varchar2(3000);
2032 v_ssn varchar(9);
2033 l_phone varchar2(20);
2034 l_opt_phone varchar(10);
2035 l_federal_id varchar2(30);
2036 p_end_of_rec varchar2(2000);
2037 l_first_name varchar2(3000);
2038 l_middle_name varchar2(3000);
2039 l_last_name varchar2(3000);
2040 l_emp_city varchar2(3000);
2041
2042 begin
2043
2044 hr_utility.set_location('Entering...: ' || l_api_name,10);
2045
2046 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
2047
2048 hr_utility.trace('p_first_name= ' || p_first_name);
2049 hr_utility.trace('p_middle_name= ' || p_middle_name);
2050 hr_utility.trace('p_last_name= ' || p_last_name);
2051
2052 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
2053 hr_utility.set_location('National identifier has hyphen' ,12);
2054 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
2055 else
2056 hr_utility.set_location('National identifier has no hyphen' ,14);
2057 v_ssn := p_national_identifier;
2058 end if;
2059
2060 hr_utility.trace('v_ssn= ' || v_ssn);
2061 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
2062 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
2063 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line3);
2064 hr_utility.trace('p_emp_city= ' || p_emp_city);
2065 hr_utility.trace('p_emp_state= ' || p_emp_state);
2066 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
2067 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
2068 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
2069 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
2070 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
2071
2072 hr_utility.trace('p_federal_id= ' || p_federal_id);
2073 hr_utility.trace('p_sit_company_state_id=' || p_suin_company_state_id);
2074 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
2075 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
2076 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
2077 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
2078 hr_utility.trace('p_loc_city= ' || p_loc_city);
2079 hr_utility.trace('p_loc_state= ' || p_loc_state);
2080 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
2081 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
2082 hr_utility.trace('p_loc_country_code= ' || p_loc_country_code);
2083 hr_utility.trace('p_loc_phone= ' || p_loc_phone);
2084 hr_utility.trace('p_salary = ' || p_salary );
2085 hr_utility.trace('p_sal_frequency= ' || p_sal_frequency );
2086 hr_utility.trace('p_gender = ' || p_gender );
2087
2088 l_phone := replace(replace(replace(nvl(p_loc_phone,' '),'-',NULL),'(',NULL),')',NULL);
2089
2090 hr_utility.trace('l_phone= ' || l_phone);
2091
2092 hr_utility.set_location(l_api_name,30);
2093 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
2094 l_first_name := remove_special_characters(p_first_name);
2095 l_middle_name := remove_special_characters(p_middle_name);
2096 l_last_name := remove_special_characters(p_last_name,'-');
2097 l_emp_city := remove_special_characters(p_emp_city,'-');
2098 l_federal_id := replace(nvl(p_federal_id,' '),'-',NULL);
2099
2100 check_min_length(l_first_name,1,'First Name','MD');
2101
2102 if l_middle_name IS NOT NULL
2103 then
2104 check_min_length(l_middle_name,1,'Midlle Name','MD');
2105 end if;
2106
2107 check_min_length(l_last_name,1,'Last Name','MD');
2108 check_min_length(p_emp_address_line1,2,'Employee Address Line1','MD');
2109 check_min_length(l_emp_city,2,'Employee City','MD');
2110
2111 check_min_length(p_tax_unit_name,2,'Employer Name','MD');
2112 check_min_length(p_loc_address_line1,2,'Employer Address Line1','MD');
2113 check_min_length(p_loc_city,2,'Employer City','MD');
2114
2115 hr_utility.set_location(l_api_name,40);
2116 --
2117 -- format new hire rocord
2118 --
2119 l_buffer := p_record_identifier || -- 'MD Newhire Record'
2120 '2.00' ||
2121 rpad(nvl(l_first_name,' '),16,' ') ||
2122 rpad(nvl(l_middle_name,' '),16,' ') ||
2123 rpad(nvl(l_last_name,' '),30,' ') ||
2124 rpad(nvl(v_ssn,' '),9,' ') ||
2125 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
2126 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
2127 rpad(nvl(p_emp_address_line3,' '),40,' ') ||
2128 rpad(nvl(l_emp_city,' '),25,' ') ||
2129 rpad(nvl(p_emp_state,' '),2,' ') ||
2130 rpad(nvl(p_emp_zip,' '),20,' ') ||
2131 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
2132 rpad(nvl(p_emp_country_code,' '),2,' ') ||
2133 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') ||
2134 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') ||
2135 rpad(nvl(p_state_of_hire,' '),2,' ') ||
2136 nvl(p_medical_insr_avail,' ')||
2137 ' '|| -- filler blank
2138 rpad(nvl(l_federal_id,' '),9,' ') ||
2139 rpad(nvl(p_suin_company_state_id,'APPLIEDFOR'),10,' ') ||
2140 ' '|| --filler blank
2141 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
2142 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
2143 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
2144 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
2145 rpad(nvl(p_loc_city,' '),25,' ') ||
2146 rpad(nvl(p_loc_state,' '),2,' ') ||
2147 rpad(nvl(p_loc_zip,' '),20,' ') ||
2148 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
2149 rpad(nvl(p_loc_country_code,' '),2,' ') ||
2150 rpad(nvl(l_phone,' '),10,' ') ||
2151 rpad(nvl(p_loc_phone_extension,' '),6,' ') ||
2152 rpad(nvl(p_loc_contact,' '),20,' ') ||
2153 rpad(' ',211,' ')|| --filler blank
2154 rpad(nvl(p_gender,' '),1,' ') ||
2155 rpad(' ',5,' ')|| --filler blank
2156 rpad(nvl(p_loc_fax,' '),10,' ')||
2157 rpad(nvl(p_loc_email,' '),50,' ') ||
2158 rpad(nvl(p_salary,' '),10,' ') ||
2159 rpad(nvl(p_sal_frequency,' '),1,' ') ||
2160 rpad(' ',12,' ')|| -- filler blank
2161 p_end_of_rec;
2162
2163 hr_utility.set_location(l_api_name,50);
2164 hr_utility.trace('l_buffer= ' || l_buffer);
2165 hr_utility.set_location('Leaving.... :' || l_api_name,41);
2166
2167 return convert(l_buffer,g_character_set);
2168 exception
2169 when others then
2170 hr_utility.set_location(l_api_name,60);
2171 hr_utility.set_location('Leaving.... with ERROR :' || l_api_name,200);
2172 raise;
2173 end md_new_hire_record;
2174
2175 /****************************************************************
2176 * This procedure formats LA new hire record *
2177 * *
2178 * *
2179 ****************************************************************/
2180 function la_new_hire_record
2181 (
2182 p_first_name in varchar2
2183 ,p_middle_name in varchar2
2184 ,p_last_name in varchar2
2185 ,p_suffix in varchar2
2186 ,p_national_identifier in varchar2
2187 ,p_emp_address_line1 in varchar2
2188 ,p_emp_address_line2 in varchar2
2189 ,p_emp_address_line3 in varchar2
2190 ,p_emp_city in varchar2
2191 ,p_emp_state in varchar2
2192 ,p_emp_zip in varchar2
2193 ,p_emp_zip_extension in varchar2
2194 ,p_emp_country_code in varchar2
2195 ,p_emp_country_name in varchar2
2196 ,p_emp_country_zip in varchar2
2197 ,p_emp_marital_status in varchar2
2198 ,p_date_of_hire in date
2199 ,p_tax_unit_name in varchar2
2200 ,p_loc_address_line1 in varchar2
2201 ,p_loc_address_line2 in varchar2
2202 ,p_loc_address_line3 in varchar2
2203 ,p_loc_city in varchar2
2204 ,p_loc_state in varchar2
2205 ,p_loc_zip in varchar2
2206 ,p_loc_zip_extension in varchar2
2207 ,p_loc_country_code in varchar2
2208 ,p_loc_country_name in varchar2
2209 ,p_loc_country_zip in varchar2
2210 ,p_federal_id in varchar2
2211 ,p_sit_company_state_id in varchar2
2212 ,p_job in varchar2
2213 ,p_salary in varchar2
2214 ,p_sal_frequency in varchar2
2215 ,p_state_of_hire in varchar2
2216 ,p_date_of_birth in date
2217 ,p_emp_insr_ind in varchar2
2218 ,p_serv_address_line1 in varchar2
2219 ,p_serv_address_line2 in varchar2
2220 ,p_serv_address_line3 in varchar2
2221 ,p_serv_city in varchar2
2222 ,p_serv_state in varchar2
2223 ,p_serv_zip in varchar2
2224 ,p_serv_zip_extension in varchar2
2225 ,p_serv_country_code in varchar2
2226 ,p_serv_country_name in varchar2
2227 ,p_serv_country_zip in varchar2
2228 ,p_loc_contact_first_name in varchar2
2229 ,p_loc_contact_last_name in varchar2
2230 ,p_loc_phone in varchar2
2231 ) return varchar2
2232 is
2233 l_api_name varchar2(61) := g_package_name || '.la_new_hire_record';
2234 l_buffer varchar2(2000);
2235 v_ssn varchar(9);
2236 l_phone varchar(10);
2237 l_federal_id varchar2(10);
2238 p_end_of_rec varchar2(2000);
2239 l_first_name varchar2(3000);
2240 l_middle_name varchar2(3000);
2241 l_last_name varchar2(3000);
2242 l_emp_city varchar2(3000);
2243 l_loc_city varchar2(3000);
2244
2245 begin
2246
2247 hr_utility.set_location('Entering...: ' || l_api_name,10);
2248
2249
2250 hr_utility.trace('p_first_name= ' || p_first_name);
2251 hr_utility.trace('p_middle_name= ' || p_middle_name);
2252 hr_utility.trace('p_last_name= ' || p_last_name);
2253
2254 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
2255 hr_utility.set_location('National identifier has hyphen' ,12);
2256 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
2257 else
2258 hr_utility.set_location('National identifier has no hyphen' ,14);
2259 v_ssn := p_national_identifier;
2260 end if;
2261
2262 hr_utility.trace('v_ssn= ' || v_ssn);
2263 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
2264 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
2265 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line3);
2266 hr_utility.trace('p_emp_city= ' || p_emp_city);
2267 hr_utility.trace('p_emp_state= ' || p_emp_state);
2268 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
2269 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
2270 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
2271 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
2272 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
2273
2274 hr_utility.trace('p_federal_id= ' || p_federal_id);
2275 hr_utility.trace('p_sit_company_state_id=' || p_sit_company_state_id);
2276 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
2277 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
2278 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
2279 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
2280 hr_utility.trace('p_loc_city= ' || p_loc_city);
2281 hr_utility.trace('p_loc_state= ' || p_loc_state);
2282 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
2283 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
2284 hr_utility.trace('p_loc_country_code= ' || p_loc_country_code);
2285 hr_utility.trace('p_loc_phone= ' || p_loc_phone);
2286
2287 l_phone := replace(nvl(p_loc_phone,' '),'-',NULL);
2288
2289 hr_utility.trace('l_phone= ' || l_phone);
2290
2291 hr_utility.set_location(l_api_name,30);
2292 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
2293 l_first_name := remove_special_characters(p_first_name);
2294 l_middle_name := remove_special_characters(p_middle_name);
2295 l_last_name := remove_special_characters(p_last_name);
2296 l_emp_city := remove_special_characters(p_emp_city);
2297 l_loc_city := remove_special_characters(p_loc_city);
2298
2299
2300 check_min_length(l_first_name,1,'First Name','LA');
2301
2302 if l_middle_name IS NOT NULL
2303 then
2304 check_min_length(l_middle_name,1,'Midlle Name','LA');
2305 end if;
2306
2307 check_min_length(l_last_name,1,'Last Name','LA');
2308 check_min_length(l_emp_city,2,'Employee City','LA');
2309
2310 check_min_length(p_tax_unit_name,2,'Employer Name','LA');
2311 check_min_length(p_loc_address_line1,1,'Employer Address Line1','LA');
2312 check_min_length(p_loc_city,2,'Employer City','LA');
2313
2314 hr_utility.set_location(l_api_name,40);
2315 --
2316 -- format new hire rocord
2317 --
2318 l_buffer := rpad(nvl(l_first_name,' '),16,' ') ||
2319 rpad(nvl(l_middle_name,' '),16,' ') ||
2320 rpad(nvl(l_last_name,' '),30,' ') ||
2321 rpad(nvl(p_suffix,' '),3,' ') ||
2322 rpad(nvl(v_ssn,' '),9,' ') ||
2323 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
2324 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
2325 rpad(nvl(p_emp_address_line3,' '),40,' ') ||
2326 rpad(nvl(l_emp_city,' '),25,' ') ||
2327 rpad(nvl(p_emp_state,' '),2,' ') ||
2328 rpad(nvl(p_emp_zip,' '),5,' ') ||
2329 rpad(nvl(p_emp_zip_extension,'0000'),4,' ') ||
2330 rpad(nvl(p_emp_country_code,' '),2,' ') ||
2331 rpad(nvl(p_emp_country_name,' '),25,' ') ||
2332 rpad(nvl(p_emp_country_zip,' '),15,' ') ||
2333 rpad(nvl(p_emp_marital_status,' '),1,' ') ||
2334 nvl(to_char(p_date_of_hire,'CCYYMMDD'),'00000000') ||
2335 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
2336 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
2337 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
2338 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
2339 rpad(nvl(l_loc_city,' '),25,' ') ||
2340 rpad(nvl(p_loc_state,' '),2,' ') ||
2341 rpad(nvl(p_loc_zip,' '),5,' ') ||
2342 rpad(nvl(p_loc_zip_extension,'0000'),4,' ') ||
2343 rpad(nvl(p_loc_country_code,' '),2,' ') ||
2344 rpad(nvl(p_loc_country_name,' '),25,' ') ||
2345 rpad(nvl(p_loc_country_zip,' '),15,' ') ||
2346 rpad(nvl(p_federal_id,' '),9,' ') ||
2347 rpad(nvl(p_sit_company_state_id,' '),12,' ') ||
2348 rpad(nvl(p_job,' '),20,' ') ||
2349 rpad(nvl(p_salary,' '),9,' ') ||
2350 rpad(nvl(p_sal_frequency,' '),2,' ') ||
2351 rpad(nvl(p_state_of_hire,' '),2,' ') ||
2352 nvl(to_char(p_date_of_birth,'CCYYMMDD'),'00000000') ||
2353 rpad(nvl(p_emp_insr_ind,' '),1,' ') ||
2354 rpad(nvl(p_serv_address_line1,' '),40,' ') ||
2355 rpad(nvl(p_serv_address_line2,' '),40,' ') ||
2356 rpad(nvl(p_serv_address_line3,' '),40,' ') ||
2357 rpad(nvl(p_serv_city,' '),25,' ') ||
2358 rpad(nvl(p_serv_state,' '),2,' ') ||
2359 rpad(nvl(p_serv_zip,' '),5,' ') ||
2360 rpad(nvl(p_serv_zip_extension,' '),4,' ') ||
2361 rpad(nvl(p_serv_country_code,' '),2,' ') ||
2362 rpad(nvl(p_serv_country_name,' '),25,' ') ||
2363 rpad(nvl(p_serv_country_zip,' '),15,' ') ||
2364 rpad(nvl(p_loc_contact_first_name,' '),16,' ') ||
2365 rpad(nvl(p_loc_contact_last_name,' '),21,' ') ||
2366 rpad(nvl(l_phone,' '),10,' ') ||
2367 rpad(' ',300,' ')||p_end_of_rec;
2368
2369
2370 hr_utility.set_location(l_api_name,50);
2371 hr_utility.trace('l_buffer= ' || l_buffer);
2372 hr_utility.set_location('Leaving.... :' || l_api_name,40);
2373
2374 return convert(l_buffer,g_character_set);
2375 exception
2376 when others then
2377 hr_utility.set_location(l_api_name,60);
2378 hr_utility.set_location('Leaving.... with ERROR :' || l_api_name,200);
2379 raise;
2380 end la_new_hire_record;
2381 -- #10387522 end
2382 --
2383 /****************************************************************
2384 * This procedure formats a03 FL new hire audit report *
2385 * *
2386 * *
2387 ****************************************************************/
2388 function a03_fl_new_hire_header
2389 return varchar2
2390 is
2391 l_api_name varchar2(61) := g_package_name || '.a03_fl_new_hire_header';
2392 l_buffer varchar2(2000);
2393 begin
2394 --
2395 hr_utility.set_location('Entering...: ' || l_api_name,10);
2396 l_buffer := 'First Name' || g_delimiter ||
2397 'Middle Name' || g_delimiter ||
2398 'Last Name' || g_delimiter ||
2399 'Social Security Number' || g_delimiter ||
2400 'Employee Address Line 1' || g_delimiter ||
2401 'Employee Address Line 2' || g_delimiter ||
2402 'Employee Address Line 3' || g_delimiter ||
2403 'Employee City' || g_delimiter ||
2404 'Employee State' || g_delimiter ||
2405 'Employee Zip' || g_delimiter ||
2406 'Employee Zip Extension' || g_delimiter ||
2407 'Employee Country Code' || g_delimiter ||
2408 'Date of Birth' || g_delimiter ||
2409 'Date of Hire' || g_delimiter ||
2410 'State of Hire' || g_delimiter ||
2411 'Medical Insurance Available' || g_delimiter || --#13082528
2412 'FEIN' || g_delimiter ||
2413 'State SUI' || g_delimiter ||
2414 'Employer Name' || g_delimiter ||
2415 'Employer Address Line 1' || g_delimiter ||
2416 'Employer Address Line 2' || g_delimiter ||
2417 'Employer Address Line 3' || g_delimiter ||
2418 'Employer City' || g_delimiter ||
2419 'Employer State' || g_delimiter ||
2420 'Employer Zip' || g_delimiter ||
2421 'Employer Zip Extension' || g_delimiter ||
2422 'Employer Country Code' || g_delimiter ||
2423 'Employer Phone' || g_delimiter ||
2424 'Employer Contact' || g_delimiter ||
2425 'Multi-State Indicator' ||
2426 g_eol;
2427
2428 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2429
2430 return l_buffer;
2431 exception
2432 when others then
2433 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2434 raise;
2435 end a03_fl_new_hire_header;
2436 --
2437 /****************************************************************
2438 * This procedure formats a03 Florida new hire audit report *
2439 * *
2440 * *
2441 ****************************************************************/
2442 function a03_fl_new_hire_record
2443 (
2444 p_national_identifier in varchar2
2445 ,p_first_name in varchar2
2446 ,p_middle_name in varchar2
2447 ,p_last_name in varchar2
2448 ,p_emp_address_line1 in varchar2
2449 ,p_emp_address_line2 in varchar2
2450 ,p_emp_address_line3 in varchar2
2451 ,p_emp_city in varchar2
2452 ,p_emp_state in varchar2
2453 ,p_emp_zip in varchar2
2454 ,p_emp_zip_extension in varchar2
2455 ,p_emp_country_code in varchar2
2456 ,p_date_of_birth in date
2457 ,p_date_of_hire in date
2458 ,p_state_of_hire in varchar2
2459 ,p_medical_insr_avail in varchar2 --#13082528
2460 ,p_federal_id in varchar2
2461 ,p_state_ein in varchar2
2462 ,p_tax_unit_name in varchar2
2463 ,p_loc_address_line1 in varchar2
2464 ,p_loc_address_line2 in varchar2
2465 ,p_loc_address_line3 in varchar2
2466 ,p_loc_city in varchar2
2467 ,p_loc_state in varchar2
2468 ,p_loc_zip in varchar2
2469 ,p_loc_zip_extension in varchar2
2470 ,p_loc_country_code in varchar2
2471 ,p_contact_phone in varchar2
2472 ,p_contact_phone_ext in varchar2
2473 ,p_contact_name in varchar2
2474 ,p_multi_state in varchar2
2475 ) return varchar2
2476 is
2477 l_api_name varchar2(61) := g_package_name || '.a03_fl_new_hire_record';
2478 l_buffer varchar2(2000);
2479 v_ssn varchar(9);
2480 begin
2481
2482 hr_utility.set_location('Entering...: ' || l_api_name,10);
2483
2484 hr_utility.trace('p_federal_id= ' || p_federal_id);
2485 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
2486 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
2487
2488 hr_utility.trace('v_ssn= ' || v_ssn);
2489 hr_utility.trace('p_first_name= ' || p_first_name);
2490 hr_utility.trace('p_middle_name= ' || p_middle_name);
2491 hr_utility.trace('p_last_name= ' || p_last_name);
2492 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
2493 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
2494 hr_utility.trace('p_emp_city= ' || p_emp_city);
2495 hr_utility.trace('p_emp_state= ' || p_emp_state);
2496 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
2497 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
2498 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
2499 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
2500 hr_utility.trace('p_medical_insr_avail= ' || p_medical_insr_avail); --#13082528
2501 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
2502 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
2503 hr_utility.trace('p_loc_city= ' || p_loc_city);
2504 hr_utility.trace('p_loc_state= ' || p_loc_state);
2505 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
2506 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
2507 hr_utility.trace('p_contact_phone = ' || p_contact_phone);
2508 hr_utility.trace('p_contact_name = ' || p_contact_name);
2509
2510
2511 hr_utility.set_location(l_api_name,30);
2512
2513 --
2514 -- format new hire rocord
2515 --
2516 l_buffer := replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
2517 replace(nvl(p_middle_name,''),',',' ') || g_delimiter ||
2518 replace(p_last_name,',',' ') || g_delimiter ||
2519 v_ssn || g_delimiter ||
2520 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
2521 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
2522 replace(nvl(p_emp_address_line3,' '),',',' ') || g_delimiter ||
2523 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
2524 nvl(p_emp_state,' ') || g_delimiter ||
2525 nvl(p_emp_zip,' ') || g_delimiter ||
2526 nvl(p_emp_zip_extension,' ') || g_delimiter ||
2527 nvl(p_emp_country_code,' ') || g_delimiter ||
2528 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
2529 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
2530 nvl(p_state_of_hire,' ') || g_delimiter ||
2531 nvl(p_medical_insr_avail,' ') || g_delimiter || --#13082528
2532 nvl(p_federal_id,' ') || g_delimiter ||
2533 nvl(p_state_ein,' ') || g_delimiter ||
2534 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
2535 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
2536 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
2537 replace(nvl(p_loc_address_line3,' '),',',' ') || g_delimiter ||
2538 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
2539 nvl(p_loc_state,' ') || g_delimiter ||
2540 nvl(p_loc_zip,' ') || g_delimiter ||
2541 nvl(p_loc_zip_extension,' ') || g_delimiter ||
2542 nvl(p_loc_country_code,' ') || g_delimiter ||
2543 nvl(p_contact_phone,' ') || g_delimiter ||
2544 replace(nvl(p_contact_name,' '),',',' ') || g_delimiter ||
2545 nvl(p_multi_state,' ') ||
2546 g_eol;
2547
2548 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2549
2550 return l_buffer;
2551 exception
2552 when others then
2553 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2554 raise;
2555 end a03_fl_new_hire_record;
2556 --
2557
2558 -- al audit report
2559
2560 function a03_al_new_hire_header
2561 return varchar2
2562 is
2563 l_api_name varchar2(61) := g_package_name || '.a03_al_new_hire_header';
2564 l_buffer varchar2(2000);
2565 begin
2566 --
2567 hr_utility.set_location('Entering...: ' || l_api_name,10);
2568 l_buffer := 'First Name' || g_delimiter ||
2569 'Middle Name' || g_delimiter ||
2570 'Last Name' || g_delimiter ||
2571 'Social Security Number' || g_delimiter ||
2572 'Employee Address Line 1' || g_delimiter ||
2573 'Employee Address Line 2' || g_delimiter ||
2574 'Employee Address Line 3' || g_delimiter ||
2575 'Employee City' || g_delimiter ||
2576 'Employee State' || g_delimiter ||
2577 'Employee Zip' || g_delimiter ||
2578 'Employee Zip Extension' || g_delimiter ||
2579 'Employee Country Code' || g_delimiter ||
2580 'Date of Birth' || g_delimiter ||
2581 'Date of Hire' || g_delimiter ||
2582 'State of Hire' || g_delimiter ||
2583 'FEIN' || g_delimiter ||
2584 'State SUI' || g_delimiter ||
2585 'Employer Name' || g_delimiter ||
2586 'Employer Address Line 1' || g_delimiter ||
2587 'Employer Address Line 2' || g_delimiter ||
2588 'Employer Address Line 3' || g_delimiter ||
2589 'Employer City' || g_delimiter ||
2590 'Employer State' || g_delimiter ||
2591 'Employer Zip' || g_delimiter ||
2592 'Employer Zip Extension' || g_delimiter ||
2593 'Employer Country Code' || g_delimiter ||
2594 'Employer Phone' || g_delimiter ||
2595 'Employer Contact' || g_delimiter ||
2596 'Multi-State Indicator' ||
2597 g_eol;
2598
2599 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2600
2601 return l_buffer;
2602 exception
2603 when others then
2604 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2605 raise;
2606 end a03_al_new_hire_header;
2607
2608
2609 function a03_al_new_hire_record
2610 (
2611 p_national_identifier in varchar2
2612 ,p_first_name in varchar2
2613 ,p_middle_name in varchar2
2614 ,p_last_name in varchar2
2615 ,p_emp_address_line1 in varchar2
2616 ,p_emp_address_line2 in varchar2
2617 ,p_emp_address_line3 in varchar2
2618 ,p_emp_city in varchar2
2619 ,p_emp_state in varchar2
2620 ,p_emp_zip in varchar2
2621 ,p_emp_zip_extension in varchar2
2622 ,p_emp_country_code in varchar2
2623 ,p_date_of_birth in date
2624 ,p_date_of_hire in date
2625 ,p_state_of_hire in varchar2
2626 ,p_federal_id in varchar2
2627 ,p_state_ein in varchar2
2628 ,p_tax_unit_name in varchar2
2629 ,p_loc_address_line1 in varchar2
2630 ,p_loc_address_line2 in varchar2
2631 ,p_loc_address_line3 in varchar2
2632 ,p_loc_city in varchar2
2633 ,p_loc_state in varchar2
2634 ,p_loc_zip in varchar2
2635 ,p_loc_zip_extension in varchar2
2636 ,p_loc_country_code in varchar2
2637 ,p_contact_phone in varchar2
2638 ,p_contact_phone_ext in varchar2
2639 ,p_contact_name in varchar2
2640 ,p_multi_state in varchar2
2641 ) return varchar2
2642 is
2643 l_api_name varchar2(61) := g_package_name || '.a03_al_new_hire_record';
2644 l_buffer varchar2(2000);
2645 v_ssn varchar(9);
2646 begin
2647
2648 hr_utility.set_location('Entering...: ' || l_api_name,10);
2649
2650 hr_utility.trace('p_federal_id= ' || p_federal_id);
2651 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
2652 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
2653
2654 hr_utility.trace('v_ssn= ' || v_ssn);
2655 hr_utility.trace('p_first_name= ' || p_first_name);
2656 hr_utility.trace('p_middle_name= ' || p_middle_name);
2657 hr_utility.trace('p_last_name= ' || p_last_name);
2658 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
2659 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
2660 hr_utility.trace('p_emp_city= ' || p_emp_city);
2661 hr_utility.trace('p_emp_state= ' || p_emp_state);
2662 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
2663 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
2664 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
2665 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
2666
2667 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
2668 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
2669 hr_utility.trace('p_loc_city= ' || p_loc_city);
2670 hr_utility.trace('p_loc_state= ' || p_loc_state);
2671 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
2672 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
2673 hr_utility.trace('p_contact_phone = ' || p_contact_phone);
2674 hr_utility.trace('p_contact_name = ' || p_contact_name);
2675
2676
2677 hr_utility.set_location(l_api_name,30);
2678
2679 --
2680 -- format new hire rocord
2681 --
2682 l_buffer := replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
2683 replace(nvl(p_middle_name,''),',',' ') || g_delimiter ||
2684 replace(p_last_name,',',' ') || g_delimiter ||
2685 v_ssn || g_delimiter ||
2686 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
2687 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
2688 replace(nvl(p_emp_address_line3,' '),',',' ') || g_delimiter ||
2689 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
2690 nvl(p_emp_state,' ') || g_delimiter ||
2691 nvl(p_emp_zip,' ') || g_delimiter ||
2692 nvl(p_emp_zip_extension,' ') || g_delimiter ||
2693 nvl(p_emp_country_code,' ') || g_delimiter ||
2694 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
2695 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
2696 nvl(p_state_of_hire,' ') || g_delimiter ||
2697 nvl(p_federal_id,' ') || g_delimiter ||
2698 nvl(p_state_ein,' ') || g_delimiter ||
2699 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
2700 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
2701 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
2702 replace(nvl(p_loc_address_line3,' '),',',' ') || g_delimiter ||
2703 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
2704 nvl(p_loc_state,' ') || g_delimiter ||
2705 nvl(p_loc_zip,' ') || g_delimiter ||
2706 nvl(p_loc_zip_extension,' ') || g_delimiter ||
2707 nvl(p_loc_country_code,' ') || g_delimiter ||
2708 nvl(p_contact_phone,' ') || g_delimiter ||
2709 replace(nvl(p_contact_name,' '),',',' ') || g_delimiter ||
2710 nvl(p_multi_state,' ') ||
2711 g_eol;
2712
2713 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2714
2715 return l_buffer;
2716 exception
2717 when others then
2718 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2719 raise;
2720 end a03_al_new_hire_record;
2721 --
2722
2723 /****************************************************************
2724 * This procedure formats a03 IL new hire audit report *
2725 * *
2726 * *
2727 ****************************************************************/
2728 function a03_il_new_hire_header
2729 return varchar2
2730 is
2731 l_api_name varchar2(61) := g_package_name || '.a03_il_new_hire_header';
2732 l_buffer varchar2(2000);
2733 begin
2734 --
2735 hr_utility.set_location('Entering...: ' || l_api_name,10);
2736 l_buffer := 'Social Security Number' || g_delimiter ||
2737 'First Name' || g_delimiter ||
2738 'Middle Name' || g_delimiter ||
2739 'Last Name' || g_delimiter ||
2740 'Employee Address Line 1' || g_delimiter ||
2741 'Employee Address Line 2' || g_delimiter ||
2742 'Employee City' || g_delimiter ||
2743 'Employee State' || g_delimiter ||
2744 'Employee Zip' || g_delimiter ||
2745 'Employee Zip Extension' || g_delimiter ||
2746 'Date of Hire' || g_delimiter ||
2747 'FEIN' || g_delimiter ||
2748 'Employer Name' || g_delimiter ||
2749 'Employer Address Line 1' || g_delimiter ||
2750 'Employer Address Line 2' || g_delimiter ||
2751 'Employer City' || g_delimiter ||
2752 'Employer State' || g_delimiter ||
2753 'Employer Zip' || g_delimiter ||
2754 'Employer Zip Extension' || g_delimiter ||
2755 g_eol;
2756
2757 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2758
2759 return l_buffer;
2760 exception
2761 when others then
2762 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2763 raise;
2764 end a03_il_new_hire_header;
2765 --
2766 /****************************************************************
2767 * This procedure formats a03 IL new hire audit report *
2768 * *
2769 * *
2770 ****************************************************************/
2771 function a03_il_new_hire_record
2772 (
2773 p_national_identifier in varchar2
2774 ,p_first_name in varchar2
2775 ,p_middle_name in varchar2
2776 ,p_last_name in varchar2
2777 ,p_emp_address_line1 in varchar2
2778 ,p_emp_address_line2 in varchar2
2779 ,p_emp_city in varchar2
2780 ,p_emp_state in varchar2
2781 ,p_emp_zip in varchar2
2782 ,p_emp_zip_extension in varchar2
2783 ,p_date_of_hire in date
2784 ,p_federal_id in varchar2
2785 ,p_tax_unit_name in varchar2
2786 ,p_loc_address_line1 in varchar2
2787 ,p_loc_address_line2 in varchar2
2788 ,p_loc_city in varchar2
2789 ,p_loc_state in varchar2
2790 ,p_loc_zip in varchar2
2791 ,p_loc_zip_extension in varchar2
2792 ) return varchar2
2793 is
2794 l_api_name varchar2(61) := g_package_name || '.a03_il_new_hire_record';
2795 l_buffer varchar2(2000);
2796 v_ssn varchar(9);
2797 begin
2798
2799 hr_utility.set_location('Entering...: ' || l_api_name,10);
2800
2801 hr_utility.trace('p_federal_id= ' || p_federal_id);
2802 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
2803 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
2804
2805 hr_utility.trace('v_ssn= ' || v_ssn);
2806 hr_utility.trace('p_first_name= ' || p_first_name);
2807 hr_utility.trace('p_middle_name= ' || p_middle_name);
2808 hr_utility.trace('p_last_name= ' || p_last_name);
2809 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
2810 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
2811 hr_utility.trace('p_emp_city= ' || p_emp_city);
2812 hr_utility.trace('p_emp_state= ' || p_emp_state);
2813 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
2814 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
2815 hr_utility.trace('p_date_of_birth= ' || p_date_of_hire);
2816
2817 hr_utility.set_location(l_api_name,30);
2818
2819 --
2820 -- format new hire rocord
2821 --
2822 l_buffer := v_ssn || g_delimiter ||
2823 replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
2824 replace(nvl(p_middle_name,''),',',' ') || g_delimiter ||
2825 replace(p_last_name,',',' ') || g_delimiter ||
2826 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
2827 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
2828 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
2829 nvl(p_emp_state,' ') || g_delimiter ||
2830 nvl(p_emp_zip,' ') || g_delimiter ||
2831 nvl(p_emp_zip_extension,' ') || g_delimiter ||
2832 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || g_delimiter ||
2833 nvl(p_federal_id,' ') || g_delimiter ||
2834 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
2835 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
2836 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
2837 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
2838 nvl(p_loc_state,' ') || g_delimiter ||
2839 nvl(p_loc_zip,' ') || g_delimiter ||
2840 nvl(p_loc_zip_extension,' ') ||
2841 g_eol;
2842
2843 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2844
2845 return l_buffer;
2846 exception
2847 when others then
2848 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2849 raise;
2850 end a03_il_new_hire_record;
2851 --
2852 --
2853 /****************************************************************
2854 * This procedure formats a03 TX new hire audit report *
2855 * *
2856 * *
2857 ****************************************************************/
2858 function a03_tx_new_hire_header
2859 return varchar2
2860 is
2861 l_api_name varchar2(61) := g_package_name || '.a03_tx_new_hire_header';
2862 l_buffer varchar2(2000);
2863 begin
2864 --
2865 hr_utility.set_location('Entering...: ' || l_api_name,10);
2866 l_buffer := upper(
2867 'Social Security Number' || g_delimiter ||
2868 'First Name' || g_delimiter ||
2869 'Middle Name' || g_delimiter ||
2870 'Last Name' || g_delimiter ||
2871 'Employee Address Line 1' || g_delimiter ||
2872 'Employee Address Line 2' || g_delimiter ||
2873 'Employee Address Line 3' || g_delimiter ||
2874 'Employee City' || g_delimiter ||
2875 'Employee State' || g_delimiter ||
2876 'Employee Zip' || g_delimiter ||
2877 'Employee Zip Extension' || g_delimiter ||
2878 'Employee Foreign Country Code' || g_delimiter ||
2879 -- 'Employee Foreign Country Name' || g_delimiter ||
2880 'Employee Foreign Zip' || g_delimiter ||
2881 'Date of Birth' || g_delimiter ||
2882 'Date of Hire' || g_delimiter ||
2883 'State of Hire' || g_delimiter ||
2884 'FEIN' || g_delimiter ||
2885 'State EIN' || g_delimiter ||
2886 'Employer Name' || g_delimiter ||
2887 'Employer Address Line 1' || g_delimiter ||
2888 'Employer Address Line 2' || g_delimiter ||
2889 'Employer Address Line 3' || g_delimiter ||
2890 'Employer City' || g_delimiter ||
2891 'Employer State' || g_delimiter ||
2892 'Employer Zip' || g_delimiter ||
2893 'Employer Zip Extension' || g_delimiter ||
2894 -- 'Employer Foreign Country Code' || g_delimiter ||
2895 -- 'Employer Foreign Country Name' || g_delimiter ||
2896 -- 'Employer Foreign Country Zip' ||
2897 g_eol);
2898
2899 hr_utility.set_location('Leaving.... :' || l_api_name,100);
2900
2901 return l_buffer;
2902 exception
2903 when others then
2904 hr_utility.set_location('Leaving.... :' || l_api_name,200);
2905 raise;
2906 end a03_tx_new_hire_header;
2907 --
2908 /****************************************************************
2909 * This procedure formats a03 TX new hire audit report *
2910 * *
2911 * *
2912 ****************************************************************/
2913 function a03_tx_new_hire_record
2914 (
2915 p_national_identifier in varchar2
2916 ,p_first_name in varchar2
2917 ,p_middle_name in varchar2
2918 ,p_last_name in varchar2
2919 ,p_emp_address_line1 in varchar2
2920 ,p_emp_address_line2 in varchar2
2921 ,p_emp_address_line3 in varchar2
2922 ,p_emp_city in varchar2
2923 ,p_emp_state in varchar2
2924 ,p_emp_zip in varchar2
2925 ,p_emp_zip_extension in varchar2
2926 ,p_emp_country_code in varchar2
2927 ,p_emp_country_name in varchar2
2928 ,p_emp_country_zip in varchar2
2929 ,p_date_of_birth in date
2930 ,p_date_of_hire in date
2931 ,p_state_of_hire in varchar2
2932 ,p_federal_id in varchar2
2933 ,p_state_ein in varchar2
2934 ,p_tax_unit_name in varchar2
2935 ,p_loc_address_line1 in varchar2
2936 ,p_loc_address_line2 in varchar2
2937 ,p_loc_address_line3 in varchar2
2938 ,p_loc_city in varchar2
2939 ,p_loc_state in varchar2
2940 ,p_loc_zip in varchar2
2941 ,p_loc_zip_extension in varchar2
2942 ,p_loc_country_code in varchar2
2943 ,p_loc_country_name in varchar2
2944 ,p_loc_country_zip in varchar2
2945 ) return varchar2
2946 is
2947 l_api_name varchar2(61) := g_package_name || '.a03_tx_new_hire_record';
2948 l_buffer varchar2(2000);
2949 v_ssn varchar(9);
2950 begin
2951
2952 hr_utility.set_location('Entering...: ' || l_api_name,10);
2953
2954 hr_utility.trace('p_federal_id= ' || p_federal_id);
2955 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
2956 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
2957
2958 hr_utility.trace('v_ssn= ' || v_ssn);
2959 hr_utility.trace('p_first_name= ' || p_first_name);
2960 hr_utility.trace('p_middle_name= ' || p_middle_name);
2961 hr_utility.trace('p_last_name= ' || p_last_name);
2962 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
2963 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
2964 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line2);
2965 hr_utility.trace('p_emp_city= ' || p_emp_city);
2966 hr_utility.trace('p_emp_state= ' || p_emp_state);
2967 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
2968 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
2969 hr_utility.trace('p_date_of_birth= ' || p_date_of_hire);
2970 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
2971 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
2972
2973 hr_utility.set_location(l_api_name,30);
2974
2975 --
2976 -- format new hire rocord
2977 --
2978 l_buffer := upper(
2979 v_ssn || g_delimiter ||
2980 replace(nvl(p_first_name,' '),',',' ') || g_delimiter ||
2981 replace(nvl(p_middle_name,' '),',',' ') || g_delimiter ||
2982 replace(p_last_name,',',' ') || g_delimiter ||
2983 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
2984 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
2985 replace(nvl(p_emp_address_line3,' '),',',' ') || g_delimiter ||
2986 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
2987 nvl(p_emp_state,' ') || g_delimiter ||
2988 nvl(p_emp_zip,' ') || g_delimiter ||
2989 nvl(p_emp_zip_extension,' ') || g_delimiter ||
2990 nvl(p_emp_country_code,' ') || g_delimiter ||
2991 -- nvl(p_emp_country_name,' ') || g_delimiter ||
2992 nvl(p_emp_country_zip,' ') || g_delimiter ||
2993 -- nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || g_delimiter || -- BUG3954955
2994 -- nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || g_delimiter || -- BUG3954955
2995 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter || -- BUG 8515583
2996 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter || -- BUG 8515583
2997 nvl(p_state_of_hire,' ') || g_delimiter ||
2998 nvl(p_federal_id,' ') || g_delimiter ||
2999 nvl(p_state_ein,' ') || g_delimiter ||
3000 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
3001 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
3002 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
3003 replace(nvl(p_loc_address_line3,' '),',',' ') || g_delimiter ||
3004 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
3005 nvl(p_loc_state,' ') || g_delimiter ||
3006 nvl(p_loc_zip,' ') || g_delimiter ||
3007 nvl(p_loc_zip_extension,' ') || g_delimiter ||
3008 g_eol);
3009
3010 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3011
3012 return l_buffer;
3013 exception
3014 when others then
3015 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3016 raise;
3017 end a03_tx_new_hire_record;
3018
3019
3020 /****************************************************************
3021 * This procedure formats a03 new hire CA audit report *
3022 * *
3023 * *
3024 ****************************************************************/
3025 function a03_ca_new_hire_header
3026 return varchar2
3027 is
3028 l_api_name varchar2(61) := g_package_name || '.a03_ca_new_hire_header';
3029 l_buffer varchar2(2000);
3030 begin
3031 --
3032 hr_utility.set_location('Entering...: ' || l_api_name,10);
3033 l_buffer := 'SOCIAL SECURITY NUMBER' || g_delimiter ||
3034 'FIRST NAME' || g_delimiter ||
3035 'MIDDLE INITIAL' || g_delimiter ||
3036 'LAST NAME' || g_delimiter ||
3037 'EMPLOYEE STREET ADDRESS' || g_delimiter ||
3038 'EMPLOYEE CITY' || g_delimiter ||
3039 'EMPLOYEE STATE' || g_delimiter ||
3040 'EMPLOYEE ZIP' || g_delimiter ||
3041 'EMPLOYEE ZIP EXTENSION' || g_delimiter ||
3042 'DATE OF HIRE' ||
3043 g_eol;
3044
3045 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3046
3047 return l_buffer;
3048 exception
3049 when others then
3050 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3051 raise;
3052 end a03_ca_new_hire_header;
3053 --
3054 /****************************************************************
3055 * This procedure formats a03 CA new hire audit report *
3056 * *
3057 * *
3058 ****************************************************************/
3059 function a03_ca_new_hire_record
3060 (
3061 p_national_identifier in varchar2
3062 ,p_first_name in varchar2
3063 ,p_middle_name in varchar2
3064 ,p_last_name in varchar2
3065 ,p_emp_address_line in varchar2
3066 ,p_emp_city in varchar2
3067 ,p_emp_state in varchar2
3068 ,p_emp_zip in varchar2
3069 ,p_emp_zip_extension in varchar2
3070 ,p_date_of_hire in date
3071 ) return varchar2
3072 is
3073 l_api_name varchar2(61) := g_package_name || '.a03_ca_new_hire_record';
3074 l_buffer varchar2(2000);
3075 v_ssn varchar(9);
3076 begin
3077
3078 hr_utility.set_location('Entering...: ' || l_api_name,10);
3079
3080 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
3081
3082 hr_utility.trace('v_ssn= ' || v_ssn);
3083 hr_utility.trace('p_first_name= ' || p_first_name);
3084 hr_utility.trace('p_middle_name= ' || p_middle_name);
3085 hr_utility.trace('p_last_name= ' || p_last_name);
3086 hr_utility.trace('p_emp_address_line = ' || p_emp_address_line);
3087 hr_utility.trace('p_emp_city= ' || p_emp_city);
3088 hr_utility.trace('p_emp_state= ' || p_emp_state);
3089 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
3090 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
3091 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
3092
3093 hr_utility.set_location(l_api_name,30);
3094
3095 --
3096 -- format new hire rocord
3097 --
3098 l_buffer := upper(
3099 v_ssn || g_delimiter ||
3100 replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
3101 replace(nvl(substr(p_middle_name,1,1),''),',',' ') || g_delimiter ||
3102 replace(p_last_name,',',' ') || g_delimiter ||
3103 replace(nvl(p_emp_address_line,' '),',',' ') || g_delimiter ||
3104 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
3105 nvl(p_emp_state,' ') || g_delimiter ||
3106 nvl(p_emp_zip,' ') || g_delimiter ||
3107 nvl(p_emp_zip_extension,' ') || g_delimiter ||
3108 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
3109 g_eol
3110 );
3111
3112 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3113
3114 return l_buffer;
3115 exception
3116 when others then
3117 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3118 raise;
3119 end a03_ca_new_hire_record;
3120 --
3121 /****************************************************************
3122 * This procedure formats a03 NY new hire audit report *
3123 * *
3124 * *
3125 ****************************************************************/
3126 function a03_ny_new_hire_header
3127 return varchar2
3128 is
3129 l_api_name varchar2(61) := g_package_name || '.a03_ny_new_hire_header';
3130 l_buffer varchar2(2000);
3131 begin
3132 --
3133 hr_utility.set_location('Entering...: ' || l_api_name,10);
3134 l_buffer := upper(
3135 'Social Security Number' || g_delimiter ||
3136 'Last Name' || g_delimiter ||
3137 'First Name' || g_delimiter ||
3138 'Middle Initial' || g_delimiter ||
3139 'Employee Street Address' || g_delimiter ||
3140 'Employee City' || g_delimiter ||
3141 'Employee State' || g_delimiter ||
3142 'Employee Zip' || g_delimiter ||
3143 'Hire Date' || g_delimiter ||
3144 'Eligibility Indicator' || g_delimiter ||
3145 'Date Employee eligible for coverage' || -- removed g_delimiter , bug 14532612
3146 g_eol); --#10014495 Added columns Elig. Ind. and Date Emp. Elig.
3147
3148 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3149
3150 return l_buffer;
3151 exception
3152 when others then
3153 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3154 raise;
3155 end a03_ny_new_hire_header;
3156 --
3157 /****************************************************************
3158 * This procedure formats a03 NY new hire audit report *
3159 * *
3160 * *
3161 ****************************************************************/
3162 function a03_ny_new_hire_record
3163 (
3164 p_national_identifier in varchar2
3165 ,p_first_name in varchar2
3166 ,p_middle_name in varchar2
3167 ,p_last_name in varchar2
3168 ,p_emp_address_line in varchar2
3169 ,p_emp_city in varchar2
3170 ,p_emp_state in varchar2
3171 ,p_emp_zip in varchar2
3172 ,p_date_of_hire in date
3173 ,p_medical_avail in varchar2 --#10014495
3174 ,p_health_ins_date in date --#10014495
3175 ,p_new_hire_indicator in varchar2 --#14532612
3176 ) return varchar2
3177 is
3178 l_api_name varchar2(61) := g_package_name || '.a03_ny_new_hire_record';
3179 l_buffer varchar2(2000);
3180 v_ssn varchar(9);
3181 l_medical_avail VARCHAR2(1); --bug 14532612
3182 begin
3183
3184 hr_utility.set_location('Entering...: ' || l_api_name,10);
3185
3186 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
3187 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
3188 else
3189 v_ssn := p_national_identifier;
3190 end if;
3191
3192 hr_utility.trace('v_ssn= ' || v_ssn);
3193 hr_utility.trace('p_first_name= ' || p_first_name);
3194 hr_utility.trace('p_middle_name= ' || p_middle_name);
3195 hr_utility.trace('p_last_name= ' || p_last_name);
3196 hr_utility.trace('p_emp_address_line = ' || p_emp_address_line);
3197 hr_utility.trace('p_emp_city= ' || p_emp_city);
3198 hr_utility.trace('p_emp_state= ' || p_emp_state);
3199 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
3200 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
3201 --#10014495 start
3202 hr_utility.trace('p_medical_avail= ' || p_medical_avail);
3203 hr_utility.trace('p_health_ins_date= ' || to_char(p_health_ins_date));
3204 --#10014495 end
3205 hr_utility.trace('p_new_hire_indicator= ' || p_new_hire_indicator); --#14532612
3206
3207 hr_utility.set_location(l_api_name,30);
3208
3209 --Start of bug 14532612
3210 l_medical_avail := p_medical_avail;
3211 IF p_medical_avail = 'E' THEN
3212 IF p_new_hire_indicator = 'INCL' THEN
3213 l_medical_avail := 'Y';
3214 ELSE
3215 l_medical_avail := 'N';
3216 END IF;
3217 END IF;
3218 --End of bug 14532612
3219
3220 --
3221 -- format new hire rocord
3222 --
3223 l_buffer := upper(
3224 v_ssn || g_delimiter ||
3225 replace(p_last_name,',',' ') || g_delimiter ||
3226 replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
3227 replace(nvl(substr(p_middle_name,1,1),''),',',' ') || g_delimiter ||
3228 replace(nvl(p_emp_address_line,' '),',',' ') || g_delimiter ||
3229 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
3230 nvl(p_emp_state,' ') || g_delimiter ||
3231 nvl(p_emp_zip,' ') || g_delimiter ||
3232 nvl(to_char(p_date_of_hire,'MMDDRR'),' ') || g_delimiter || --corrected date format,#14470601
3233 nvl(to_char(l_medical_avail),' ')|| g_delimiter || --replaced p_medical_avail by l_medical_avail, bug 14532612
3234 nvl(to_char(p_health_ins_date,'MMDDRRRR'),' ')|| --corrected date format,#14470601
3235 g_eol); --#10014495 Added p_medical_avail and p_health_ins_date
3236
3237 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3238
3239 return l_buffer;
3240 exception
3241 when others then
3242 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3243 raise;
3244 end a03_ny_new_hire_record;
3245 --
3246 -- #10387522 start
3247 /****************************************************************
3248 * This procedure formats a03 SC new hire audit report *
3249 * *
3250 * *
3251 ****************************************************************/
3252 function a03_sc_new_hire_header
3253 return varchar2
3254 is
3255 l_api_name varchar2(61) := g_package_name || '.a03_sc_new_hire_header';
3256 l_buffer varchar2(2000);
3257 begin
3258 --
3259 hr_utility.set_location('Entering...: ' || l_api_name,10);
3260 l_buffer := upper(
3261 'Social Security Number' || g_delimiter ||
3262 'Last Name' || g_delimiter ||
3263 'First Name' || g_delimiter ||
3264 'Middle Initial' || g_delimiter ||
3265 'Employee Address Line 1' || g_delimiter ||
3266 'Employee Address Line 2' || g_delimiter ||
3267 'Employee City' || g_delimiter ||
3268 'Employee State' || g_delimiter ||
3269 'Employee Zip' || g_delimiter ||
3270 'Date of Birth' || g_delimiter ||
3271 'Hire Date' ||
3272 g_eol);
3273
3274 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3275
3276 return l_buffer;
3277 exception
3278 when others then
3279 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3280 raise;
3281 end a03_sc_new_hire_header;
3282 --
3283 /****************************************************************
3284 * This procedure formats a03 SC new hire audit report *
3285 * *
3286 * *
3287 ****************************************************************/
3288 function a03_sc_new_hire_record
3289 (
3290 p_national_identifier in varchar2
3291 ,p_first_name in varchar2
3292 ,p_middle_name in varchar2
3293 ,p_last_name in varchar2
3294 ,p_emp_add_line1 in varchar2
3295 ,p_emp_add_line2 in varchar2
3296 ,p_emp_city in varchar2
3297 ,p_emp_state in varchar2
3298 ,p_emp_zip in varchar2
3299 ,p_date_of_birth in date
3300 ,p_date_of_hire in date
3301 ) return varchar2
3302 is
3303 l_api_name varchar2(61) := g_package_name || '.a03_sc_new_hire_record';
3304 l_buffer varchar2(2000);
3305 v_ssn varchar(9);
3306 begin
3307
3308 hr_utility.set_location('Entering...: ' || l_api_name,10);
3309
3310 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
3311 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
3312 else
3313 v_ssn := p_national_identifier;
3314 end if;
3315
3316 hr_utility.trace('v_ssn= ' || v_ssn);
3317 hr_utility.trace('p_first_name= ' || p_first_name);
3318 hr_utility.trace('p_middle_name= ' || p_middle_name);
3319 hr_utility.trace('p_last_name= ' || p_last_name);
3320 hr_utility.trace('p_emp_add_line1 = ' || p_emp_add_line1);
3321 hr_utility.trace('p_emp_add_line2 = ' || p_emp_add_line2);
3322 hr_utility.trace('p_emp_city= ' || p_emp_city);
3323 hr_utility.trace('p_emp_state= ' || p_emp_state);
3324 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
3325 hr_utility.trace('p_date_of_birth= ' || to_char(p_date_of_birth));
3326 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
3327
3328 hr_utility.set_location(l_api_name,30);
3329
3330 --
3331 -- format new hire rocord
3332 --
3333 l_buffer := upper(
3334 v_ssn || g_delimiter ||
3335 replace(p_last_name,g_delimiter,' ') || g_delimiter ||
3336 replace(nvl(p_first_name,''),g_delimiter,' ') || g_delimiter ||
3337 replace(nvl(substr(p_middle_name,1,1),''),g_delimiter,' ') || g_delimiter ||
3338 replace(nvl(p_emp_add_line1,' '),g_delimiter,' ') || g_delimiter ||
3339 replace(nvl(p_emp_add_line2,' '),g_delimiter,' ') || g_delimiter ||
3340 replace(nvl(p_emp_city,' '),g_delimiter,' ') || g_delimiter ||
3341 nvl(p_emp_state,' ') || g_delimiter ||
3342 nvl(p_emp_zip,' ') || g_delimiter ||
3343 nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || g_delimiter ||
3344 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
3345 g_eol);
3346
3347 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3348
3349 return l_buffer;
3350 exception
3351 when others then
3352 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3353 raise;
3354 end a03_sc_new_hire_record;
3355 --
3356 /****************************************************************
3357 * This procedure formats a03 MI new hire audit report *
3358 * *
3359 * *
3360 ****************************************************************/
3361 function a03_mi_new_hire_header
3362 return varchar2
3363 is
3364 l_api_name varchar2(61) := g_package_name || '.a03_mi_new_hire_header';
3365 l_buffer varchar2(2000);
3366 begin
3367 --
3368 hr_utility.set_location('Entering...: ' || l_api_name,10);
3369 l_buffer := upper(
3370 'Social Security Number' || g_delimiter ||
3371 'First Name' || g_delimiter ||
3372 'Middle Initial' || g_delimiter ||
3373 'Last Name' || g_delimiter ||
3374 'Employer Name' || g_delimiter ||
3375 'FEIN' || g_delimiter ||
3376 'Employer Address Line 1' || g_delimiter ||
3377 'Employer Address Line 2' || g_delimiter ||
3378 'Employer City' || g_delimiter ||
3379 'Employer State' || g_delimiter ||
3380 'Employer Zip' || g_delimiter ||
3381 'Employer Zip Extn' || g_delimiter ||
3382 'Employee Address Line 1' || g_delimiter ||
3383 'Employee Address Line 2' || g_delimiter ||
3384 'Employee City' || g_delimiter ||
3385 'Employee State' || g_delimiter ||
3386 'Employee Zip' || g_delimiter ||
3387 'Employee Zip Extn' || g_delimiter ||
3388 'Date of Birth' || g_delimiter ||
3389 'Hire Date' || g_delimiter ||
3390 'Driver License Number' ||
3391 g_eol);
3392
3393 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3394
3395 return l_buffer;
3396 exception
3397 when others then
3398 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3399 raise;
3400 end a03_mi_new_hire_header;
3401 --
3402 /****************************************************************
3403 * This procedure formats a03 MI new hire audit report *
3404 * *
3405 * *
3406 ****************************************************************/
3407 function a03_mi_new_hire_record
3408 (
3409 p_national_identifier in varchar2
3410 ,p_first_name in varchar2
3411 ,p_middle_name in varchar2
3412 ,p_last_name in varchar2
3413 ,p_tax_unit_name in varchar2
3414 ,p_federal_id in varchar2
3415 ,p_loc_address_line1 in varchar2
3416 ,p_loc_address_line2 in varchar2
3417 ,p_loc_city in varchar2
3418 ,p_loc_state in varchar2
3419 ,p_loc_zip in varchar2
3420 ,p_loc_zip_extension in varchar2
3421 ,p_emp_address_line1 in varchar2
3422 ,p_emp_address_line2 in varchar2
3423 ,p_emp_city in varchar2
3424 ,p_emp_state in varchar2
3425 ,p_emp_zip in varchar2
3426 ,p_emp_zip_extension in varchar2
3427 ,p_date_of_birth in date
3428 ,p_date_of_hire in date
3429 ,p_dl_number in varchar2
3430 ) return varchar2
3431 is
3432 l_api_name varchar2(61) := g_package_name || '.a03_mi_new_hire_record';
3433 l_buffer varchar2(2000);
3434 v_ssn varchar(9);
3435 begin
3436
3437 hr_utility.set_location('Entering...: ' || l_api_name,10);
3438
3439 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
3440
3441 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
3442
3443 hr_utility.trace('v_ssn= ' || v_ssn);
3444 hr_utility.trace('p_first_name= ' || p_first_name);
3445 hr_utility.trace('p_middle_name= ' || p_middle_name);
3446 hr_utility.trace('p_last_name= ' || p_last_name);
3447 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
3448 hr_utility.trace('p_federal_id= ' || p_federal_id);
3449 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
3450 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
3451 hr_utility.trace('p_loc_city= ' || p_loc_city);
3452 hr_utility.trace('p_loc_state= ' || p_loc_state);
3453 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
3454 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
3455 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
3456 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
3457 hr_utility.trace('p_emp_city= ' || p_emp_city);
3458 hr_utility.trace('p_emp_state= ' || p_emp_state);
3459 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
3460 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
3461 hr_utility.trace('p_date_of_birth= ' || to_char(p_date_of_birth));
3462 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
3463 hr_utility.trace('p_dl_number= ' || p_dl_number);
3464
3465
3466 hr_utility.set_location(l_api_name,30);
3467
3468 --
3469 -- format new hire rocord
3470 --
3471 l_buffer := upper(
3472 v_ssn || g_delimiter ||
3473 replace(nvl(p_first_name,''),g_delimiter,' ') || g_delimiter ||
3474 replace(nvl(substr(p_middle_name,1,1),''),g_delimiter,' ') || g_delimiter ||
3475 replace(p_last_name,g_delimiter,' ') || g_delimiter ||
3476 replace(nvl(p_tax_unit_name,' '),g_delimiter,' ') || g_delimiter ||
3477 replace(nvl(p_federal_id,' '),g_delimiter,' ') || g_delimiter ||
3478 replace(nvl(p_loc_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3479 replace(nvl(p_loc_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3480 replace(nvl(p_loc_city,' '),g_delimiter,' ') || g_delimiter ||
3481 replace(nvl(p_loc_state,' '),g_delimiter,' ') || g_delimiter ||
3482 replace(nvl(p_loc_zip,' '),g_delimiter,' ') || g_delimiter ||
3483 replace(nvl(p_loc_zip_extension,' '),g_delimiter,' ') || g_delimiter ||
3484 replace(nvl(p_emp_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3485 replace(nvl(p_emp_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3486 replace(nvl(p_emp_city,' '),g_delimiter,' ') || g_delimiter ||
3487 replace(nvl(p_emp_state,' '),g_delimiter,' ') || g_delimiter ||
3488 replace(nvl(p_emp_zip,' '),g_delimiter,' ') || g_delimiter ||
3489 replace(nvl(p_emp_zip_extension,' '),g_delimiter,' ') || g_delimiter ||
3490 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
3491 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
3492 replace(nvl(p_dl_number,' '),g_delimiter,' ') ||
3493 g_eol);
3494
3495 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3496
3497 return l_buffer;
3498 exception
3499 when others then
3500 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3501 raise;
3502 end a03_mi_new_hire_record;
3503 --
3504 --
3505 /****************************************************************
3506 * This procedure formats a03 OH new hire audit report *
3507 * *
3508 * *
3509 ****************************************************************/
3510 function a03_oh_new_hire_header
3511 return varchar2
3512 is
3513 l_api_name varchar2(61) := g_package_name || '.a03_oh_new_hire_header';
3514 l_buffer varchar2(2000);
3515 begin
3516 --
3517 hr_utility.set_location('Entering...: ' || l_api_name,10);
3518 l_buffer := upper(
3519 'Social Security Number' || g_delimiter ||
3520 'First Name' || g_delimiter ||
3521 'Middle Name' || g_delimiter ||
3522 'Last Name' || g_delimiter ||
3523 'Employee Address Line 1' || g_delimiter ||
3524 'Employee Address Line 2' || g_delimiter ||
3525 'Employee Address Line 3' || g_delimiter ||
3526 'Employee City' || g_delimiter ||
3527 'Employee State' || g_delimiter ||
3528 'Employee Zip' || g_delimiter ||
3529 'Employee Zip Extension' || g_delimiter ||
3530 'Employee Foreign Country Code' || g_delimiter ||
3531 'Employee Foreign Country Name' || g_delimiter ||
3532 'Employee Foreign Zip' || g_delimiter ||
3533 'Date of Birth' || g_delimiter ||
3534 'Date of Hire' || g_delimiter ||
3535 'State of Hire' || g_delimiter ||
3536 'Employee Gender' || g_delimiter ||
3537 'Employee left work' || g_delimiter ||
3538 'FEIN' || g_delimiter ||
3539 'Employer Income Tax Credit' || g_delimiter ||
3540 'State EIN' || g_delimiter ||
3541 'Employer Name' || g_delimiter ||
3542 'Employer Address Line 1' || g_delimiter ||
3543 'Employer Address Line 2' || g_delimiter ||
3544 'Employer Address Line 3' || g_delimiter ||
3545 'Employer City' || g_delimiter ||
3546 'Employer State' || g_delimiter ||
3547 'Employer Zip' || g_delimiter ||
3548 'Employer Zip Extension' || g_delimiter ||
3549 'Employer Foreign Country Code' || g_delimiter ||
3550 'Employer Foreign Country Name' || g_delimiter ||
3551 'Employer Foreign Country Zip' || g_delimiter ||
3552 'Employer Optional Address Line 1' || g_delimiter ||
3553 'Employer Optional Address Line 2' || g_delimiter ||
3554 'Employer Optional Address Line 3' || g_delimiter ||
3555 'Employer Optional City' || g_delimiter ||
3556 'Employer Optional State' || g_delimiter ||
3557 'Employer Optional Zip' || g_delimiter ||
3558 'Employer Optional Zip Extension' || g_delimiter ||
3559 'Employer Optional Foreign Country Code' || g_delimiter ||
3560 'Employer Optional Foreign Country Name' || g_delimiter ||
3561 'Employer Optional Foreign Country Zip' || g_delimiter ||
3562 'Employee Independent Contractor' || g_delimiter ||
3563 'Employee IC Payment Begin Date' || g_delimiter ||
3564 'Employee IC Length of Contract' ||
3565 g_eol);
3566
3567 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3568
3569 return l_buffer;
3570 exception
3571 when others then
3572 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3573 raise;
3574 end a03_oh_new_hire_header;
3575 --
3576 /****************************************************************
3577 * This procedure formats a03 OH new hire audit report *
3578 * *
3579 * *
3580 ****************************************************************/
3581 --
3582 function a03_oh_new_hire_record
3583 (
3584 p_national_identifier in varchar2
3585 ,p_first_name in varchar2
3586 ,p_middle_name in varchar2
3587 ,p_last_name in varchar2
3588 ,p_emp_address_line1 in varchar2
3589 ,p_emp_address_line2 in varchar2
3590 ,p_emp_address_line3 in varchar2
3591 ,p_emp_city in varchar2
3592 ,p_emp_state in varchar2
3593 ,p_emp_zip in varchar2
3594 ,p_emp_zip_extension in varchar2
3595 ,p_emp_country_code in varchar2
3596 ,p_emp_country_name in varchar2
3597 ,p_emp_country_zip in varchar2
3598 ,p_date_of_birth in date
3599 ,p_date_of_hire in date
3600 ,p_state_of_hire in varchar2
3601 ,p_gender in varchar2
3602 ,p_left_work in varchar2
3603 ,p_federal_id in varchar2
3604 ,p_inc_tax_credit in varchar2
3605 ,p_state_ein in varchar2
3606 ,p_tax_unit_name in varchar2
3607 ,p_loc_address_line1 in varchar2
3608 ,p_loc_address_line2 in varchar2
3609 ,p_loc_address_line3 in varchar2
3610 ,p_loc_city in varchar2
3611 ,p_loc_state in varchar2
3612 ,p_loc_zip in varchar2
3613 ,p_loc_zip_extension in varchar2
3614 ,p_loc_country_code in varchar2
3615 ,p_loc_country_name in varchar2
3616 ,p_loc_country_zip in varchar2
3617 ,p_opt_address_line1 in varchar2
3618 ,p_opt_address_line2 in varchar2
3619 ,p_opt_address_line3 in varchar2
3620 ,p_opt_city in varchar2
3621 ,p_opt_state in varchar2
3622 ,p_opt_zip in varchar2
3623 ,p_opt_zip_extension in varchar2
3624 ,p_opt_country_code in varchar2
3625 ,p_opt_country_name in varchar2
3626 ,p_opt_country_zip in varchar2
3627 ,p_emp_ic in varchar2
3628 ,p_ic_pay_date in date
3629 ,p_ic_length in number
3630 ) return varchar2
3631 is
3632 l_api_name varchar2(61) := g_package_name || '.a03_oh_new_hire_record';
3633 l_buffer varchar2(2000);
3634 v_ssn varchar2(9);
3635 l_oh_delimiter varchar2(1) := ',';
3636 begin
3637
3638 hr_utility.set_location('Entering...: ' || l_api_name,10);
3639
3640 hr_utility.trace('p_federal_id= ' || p_federal_id);
3641 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
3642
3643 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
3644 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
3645 else
3646 v_ssn := p_national_identifier;
3647 end if;
3648
3649 hr_utility.trace('v_ssn= ' || v_ssn);
3650 hr_utility.trace('p_first_name = ' || p_first_name);
3651 hr_utility.trace('p_middle_name = ' || p_middle_name);
3652 hr_utility.trace('p_last_name = ' || p_last_name);
3653 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
3654 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
3655 hr_utility.trace('p_emp_city= ' || p_emp_city);
3656 hr_utility.trace('p_emp_state= ' || p_emp_state);
3657 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
3658 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
3659 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
3660 hr_utility.trace('p_emp_country_name= ' || p_emp_country_name);
3661 hr_utility.trace('p_emp_country_zip= ' || p_emp_country_zip);
3662 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
3663 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
3664 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
3665 hr_utility.trace('p_gender= ' || p_gender);
3666 hr_utility.trace('p_left_work= ' || p_left_work);
3667 hr_utility.trace('p_federal_id= ' || p_federal_id);
3668 hr_utility.trace('p_inc_tax_credit= ' || p_inc_tax_credit);
3669 hr_utility.trace('p_state_ein= ' || p_state_ein);
3670 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
3671 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
3672 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
3673 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
3674 hr_utility.trace('p_loc_city= ' || p_loc_city);
3675 hr_utility.trace('p_loc_state= ' || p_loc_state);
3676 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
3677 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
3678
3679
3680
3681 hr_utility.set_location(l_api_name,30);
3682
3683 --
3684 -- format new hire rocord
3685 --
3686 l_buffer := upper(
3687 v_ssn || g_delimiter ||
3688 replace(nvl(p_first_name,' '),g_delimiter,' ')||g_delimiter||
3689 replace(nvl(p_middle_name,' '),g_delimiter,' ')||g_delimiter||
3690 replace(p_last_name,g_delimiter,' ') || g_delimiter ||
3691 replace(nvl(p_emp_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3692 replace(nvl(p_emp_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3693 replace(nvl(p_emp_address_line3,' '),g_delimiter,' ') || g_delimiter ||
3694 replace(nvl(p_emp_city,' '),g_delimiter,' ') || g_delimiter ||
3695 nvl(p_emp_state,' ') || g_delimiter ||
3696 nvl(p_emp_zip,' ') || g_delimiter ||
3697 nvl(p_emp_zip_extension,' ') || g_delimiter ||
3698 nvl(p_emp_country_code,' ') || g_delimiter ||
3699 nvl(p_emp_country_name,' ') || g_delimiter ||
3700 nvl(p_emp_country_zip,' ') || g_delimiter ||
3701 nvl(to_char(p_date_of_birth,'CCYYMMDD'),' ') || g_delimiter ||
3702 nvl(to_char(p_date_of_hire,'CCYYMMDD'),' ') || g_delimiter ||
3703 nvl(p_state_of_hire,' ') || g_delimiter ||
3704 nvl(p_gender,' ') || g_delimiter ||
3705 nvl(p_left_work,' ') || g_delimiter ||
3706 nvl(p_federal_id,' ') || g_delimiter ||
3707 nvl(p_inc_tax_credit,' ') || g_delimiter ||
3708 nvl(p_state_ein,' ') || g_delimiter ||
3709 replace(nvl(p_tax_unit_name,' '),g_delimiter,' ') || g_delimiter ||
3710 replace(nvl(p_loc_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3711 replace(nvl(p_loc_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3712 replace(nvl(p_loc_address_line3,' '),g_delimiter,' ') || g_delimiter ||
3713 replace(nvl(p_loc_city,' '),g_delimiter,' ') || g_delimiter ||
3714 nvl(p_loc_state,' ') || g_delimiter ||
3715 nvl(p_loc_zip,' ') || g_delimiter ||
3716 nvl(p_loc_zip_extension,' ') || g_delimiter ||
3717 nvl(p_loc_country_code,' ') || g_delimiter ||
3718 replace(nvl(p_loc_country_name,' '),g_delimiter,' ') || g_delimiter ||
3719 nvl(p_loc_country_zip,' ') || g_delimiter ||
3720 replace(nvl(p_opt_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3721 replace(nvl(p_opt_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3722 replace(nvl(p_opt_address_line3,' '),g_delimiter,' ') || g_delimiter ||
3723 replace(nvl(p_opt_city,' '),g_delimiter,' ') || g_delimiter ||
3724 replace(nvl(p_opt_state,' '),g_delimiter,' ') || g_delimiter ||
3725 nvl(p_opt_zip,' ') || g_delimiter ||
3726 nvl(p_opt_zip_extension,' ') || g_delimiter ||
3727 nvl(p_opt_country_code,' ') || g_delimiter ||
3728 replace(nvl(p_opt_country_name,' '),g_delimiter,' ') || g_delimiter ||
3729 nvl(p_opt_country_zip,' ') || g_delimiter ||
3730 nvl(p_emp_ic,' ') || g_delimiter ||
3731 nvl(to_char(p_ic_pay_date,'CCYYMMDD'),' ') || g_delimiter ||
3732 nvl(to_char(p_ic_length) ,' ')) ||
3733 g_eol;
3734
3735
3736 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3737
3738
3739 return convert(l_buffer,g_character_set);
3740 exception
3741 when others then
3742 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3743 raise;
3744 end a03_oh_new_hire_record;
3745 --
3746 --
3747 /****************************************************************
3748 * This procedure formats a03 OH new hire audit report *
3749 * *
3750 * *
3751 ****************************************************************/
3752 function a03_pa_new_hire_header
3753 return varchar2
3754 is
3755 l_api_name varchar2(61) := g_package_name || '.a03_pa_new_hire_header';
3756 l_buffer varchar2(2000);
3757 begin
3758 --
3759 hr_utility.set_location('Entering...: ' || l_api_name,10);
3760 l_buffer := upper(
3761 'FEIN' || g_delimiter ||
3762 'Employer Name' || g_delimiter ||
3763 'Employer Address Line 1' || g_delimiter ||
3764 'Employer Address Line 2' || g_delimiter ||
3765 'Employer Address Line 3' || g_delimiter ||
3766 'Employer City' || g_delimiter ||
3767 'Employer State' || g_delimiter ||
3768 'Employer Zip' || g_delimiter ||
3769 'Employer Zip Extension' || g_delimiter ||
3770 'Contact First Name' || g_delimiter ||
3771 'Contact Last Name' || g_delimiter ||
3772 'Contact Person Phone No' || g_delimiter ||
3773 'Contact Person Extn.' || g_delimiter ||
3774 'Social Security Number' || g_delimiter ||
3775 'First Name' || g_delimiter ||
3776 'Middle Name' || g_delimiter ||
3777 'Last Name' || g_delimiter ||
3778 'Date of Birth' || g_delimiter ||
3779 'State of Hire' || g_delimiter ||
3780 'Date of Hire' || g_delimiter ||
3781 'Employee Address Line 1' || g_delimiter ||
3782 'Employee Address Line 2' || g_delimiter ||
3783 'Employee Address Line 3' || g_delimiter ||
3784 'Employee City' || g_delimiter ||
3785 'Employee State' || g_delimiter ||
3786 'Employee Zip' || g_delimiter ||
3787 'Employee Zip Extension' ||
3788 g_eol);
3789
3790 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3791
3792 return l_buffer;
3793 exception
3794 when others then
3795 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3796 raise;
3797 end a03_pa_new_hire_header;
3798 --
3799 /****************************************************************
3800 * This procedure formats a03 PA new hire audit report *
3801 * *
3802 * *
3803 ****************************************************************/
3804 --
3805 function a03_pa_new_hire_record
3806 (
3807 p_federal_id in varchar2
3808 ,p_tax_unit_name in varchar2
3809 ,p_loc_address_line1 in varchar2
3810 ,p_loc_address_line2 in varchar2
3811 ,p_loc_address_line3 in varchar2
3812 ,p_loc_city in varchar2
3813 ,p_loc_state in varchar2
3814 ,p_loc_zip in varchar2
3815 ,p_loc_zip_extension in varchar2
3816 ,p_contact_first_name in varchar2
3817 ,p_contact_last_name in varchar2
3818 ,p_contact_ph_no in varchar2
3819 ,p_contact_ph_no_ext in number
3820 ,p_national_identifier in varchar2
3821 ,p_first_name in varchar2
3822 ,p_middle_name in varchar2
3823 ,p_last_name in varchar2
3824 ,p_date_of_birth in date
3825 ,p_state_of_hire in varchar2
3826 ,p_date_of_hire in date
3827 ,p_emp_address_line1 in varchar2
3828 ,p_emp_address_line2 in varchar2
3829 ,p_emp_address_line3 in varchar2
3830 ,p_emp_city in varchar2
3831 ,p_emp_state in varchar2
3832 ,p_emp_zip in varchar2
3833 ,p_emp_zip_extension in varchar2
3834 ) return varchar2
3835 is
3836 l_api_name varchar2(61) := g_package_name || '.a03_pa_new_hire_record';
3837 l_buffer varchar2(2000);
3838 v_ssn varchar2(9);
3839 l_phone varchar(20);
3840 begin
3841
3842 hr_utility.set_location('Entering...: ' || l_api_name,10);
3843
3844 hr_utility.trace('p_federal_id= ' || p_federal_id);
3845 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
3846
3847 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
3848 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
3849 else
3850 v_ssn := p_national_identifier;
3851 end if;
3852
3853 l_phone := replace(replace(replace(nvl(p_contact_ph_no,' '),'-',NULL),'(',NULL),')',NULL);
3854
3855 hr_utility.trace('v_ssn= ' || v_ssn);
3856 hr_utility.trace('p_first_name = ' || p_first_name);
3857 hr_utility.trace('p_middle_name = ' || p_middle_name);
3858 hr_utility.trace('p_last_name = ' || p_last_name);
3859 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
3860 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
3861 hr_utility.trace('p_emp_city= ' || p_emp_city);
3862 hr_utility.trace('p_emp_state= ' || p_emp_state);
3863 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
3864 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
3865 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
3866 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
3867 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
3868 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
3869 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
3870 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
3871 hr_utility.trace('p_loc_city= ' || p_loc_city);
3872 hr_utility.trace('p_loc_state= ' || p_loc_state);
3873 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
3874 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
3875 hr_utility.trace('p_contact_last_name= ' || p_contact_last_name);
3876 hr_utility.trace('p_contact_first_name=' || p_contact_first_name);
3877 hr_utility.trace('p_contact_ph_no = ' || p_contact_ph_no );
3878 hr_utility.trace('p_contact_ph_no_ext =' || p_contact_ph_no_ext );
3879
3880
3881
3882 hr_utility.set_location(l_api_name,30);
3883
3884 --
3885 -- format new hire rocord
3886 --
3887 l_buffer := upper(
3888 p_federal_id || g_delimiter ||
3889 replace(nvl(p_tax_unit_name,' '),g_delimiter,' ') || g_delimiter ||
3890 replace(nvl(p_loc_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3891 replace(nvl(p_loc_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3892 replace(nvl(p_loc_address_line3,' '),g_delimiter,' ') || g_delimiter ||
3893 replace(nvl(p_loc_city,' '),g_delimiter,' ') || g_delimiter ||
3894 nvl(p_loc_state,' ') || g_delimiter ||
3895 nvl(p_loc_zip,' ') || g_delimiter ||
3896 nvl(p_loc_zip_extension,' ') || g_delimiter ||
3897 replace(nvl(p_contact_first_name,' '),g_delimiter,' ') || g_delimiter ||
3898 replace(nvl(p_contact_last_name,' '),g_delimiter,' ') || g_delimiter ||
3899 nvl(l_phone,'') || g_delimiter ||
3900 nvl(p_contact_ph_no_ext,'') || g_delimiter ||
3901 v_ssn || g_delimiter ||
3902 replace(nvl(p_first_name,' '),g_delimiter,' ')||g_delimiter||
3903 replace(nvl(p_middle_name,' '),g_delimiter,' ')||g_delimiter||
3904 replace(p_last_name,g_delimiter,' ') || g_delimiter ||
3905 nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || g_delimiter ||
3906 nvl(p_state_of_hire,' ') || g_delimiter ||
3907 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || g_delimiter ||
3908 replace(nvl(p_emp_address_line1,' '),g_delimiter,' ') || g_delimiter ||
3909 replace(nvl(p_emp_address_line2,' '),g_delimiter,' ') || g_delimiter ||
3910 replace(nvl(p_emp_address_line3,' '),g_delimiter,' ') || g_delimiter ||
3911 replace(nvl(p_emp_city,' '),g_delimiter,' ') || g_delimiter ||
3912 nvl(p_emp_state,' ') || g_delimiter ||
3913 nvl(p_emp_zip,' ') || g_delimiter ||
3914 nvl(p_emp_zip_extension,' ')) ||
3915 g_eol;
3916
3917
3918 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3919
3920
3921 return convert(l_buffer,g_character_set);
3922 exception
3923 when others then
3924 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3925 raise;
3926 end a03_pa_new_hire_record;
3927 --
3928 /****************************************************************
3929 * This procedure formats a03 AR new hire audit report header *
3930 * *
3931 * *
3932 ****************************************************************/
3933 function a03_ar_new_hire_header
3934 return varchar2
3935 is
3936 l_api_name varchar2(61) := g_package_name || '.a03_ar_new_hire_header';
3937 l_buffer varchar2(2000);
3938 begin
3939 --
3940 hr_utility.set_location('Entering...: ' || l_api_name,10);
3941 l_buffer := 'First Name' || g_delimiter ||
3942 'Middle Name' || g_delimiter ||
3943 'Last Name' || g_delimiter ||
3944 'Social Security Number' || g_delimiter ||
3945 'Employee Address Line 1' || g_delimiter ||
3946 'Employee Address Line 2' || g_delimiter ||
3947 'Employee Address Line 3' || g_delimiter ||
3948 'Employee City' || g_delimiter ||
3949 'Employee State' || g_delimiter ||
3950 'Employee Zip' || g_delimiter ||
3951 'Employee Zip Extension' || g_delimiter ||
3952 'Employee Country Code' || g_delimiter ||
3953 'Date of Birth' || g_delimiter ||
3954 'Date of Hire' || g_delimiter ||
3955 'State of Hire' || g_delimiter ||
3956 'FEIN' || g_delimiter ||
3957 'State EIN' || g_delimiter ||
3958 'Employer Name' || g_delimiter ||
3959 'Employer Address Line 1' || g_delimiter ||
3960 'Employer Address Line 2' || g_delimiter ||
3961 'Employer Address Line 3' || g_delimiter ||
3962 'Employer City' || g_delimiter ||
3963 'Employer State' || g_delimiter ||
3964 'Employer Zip' || g_delimiter ||
3965 'Employer Zip Extension' || g_delimiter ||
3966 'Employer Country Code' || g_delimiter ||
3967 'Employer Phone' || g_delimiter ||
3968 'Employer Phone Extension' || g_delimiter ||
3969 'Employer Contact' ||
3970 g_eol;
3971
3972 hr_utility.set_location('Leaving.... :' || l_api_name,100);
3973
3974 return l_buffer;
3975 exception
3976 when others then
3977 hr_utility.set_location('Leaving.... :' || l_api_name,200);
3978 raise;
3979 end a03_ar_new_hire_header;
3980 /****************************************************************
3981 * This procedure formats a03 Arkansas new hire audit report *
3982 * *
3983 * *
3984 ****************************************************************/
3985 function a03_ar_new_hire_record
3986 (
3987 p_national_identifier in varchar2
3988 ,p_first_name in varchar2
3989 ,p_middle_name in varchar2
3990 ,p_last_name in varchar2
3991 ,p_emp_address_line1 in varchar2
3992 ,p_emp_address_line2 in varchar2
3993 ,p_emp_address_line3 in varchar2
3994 ,p_emp_city in varchar2
3995 ,p_emp_state in varchar2
3996 ,p_emp_zip in varchar2
3997 ,p_emp_zip_extension in varchar2
3998 ,p_emp_country_code in varchar2
3999 ,p_date_of_birth in date
4000 ,p_date_of_hire in date
4001 ,p_state_of_hire in varchar2
4002 ,p_federal_id in varchar2
4003 ,p_state_ein in varchar2
4004 ,p_tax_unit_name in varchar2
4005 ,p_loc_address_line1 in varchar2
4006 ,p_loc_address_line2 in varchar2
4007 ,p_loc_address_line3 in varchar2
4008 ,p_loc_city in varchar2
4009 ,p_loc_state in varchar2
4010 ,p_loc_zip in varchar2
4011 ,p_loc_zip_extension in varchar2
4012 ,p_loc_country_code in varchar2
4013 ,p_contact_phone in varchar2
4014 ,p_contact_phone_ext in varchar2
4015 ,p_contact_name in varchar2
4016 ) return varchar2
4017 is
4018 l_api_name varchar2(61) := g_package_name || '.a03_ar_new_hire_record';
4019 l_buffer varchar2(2000);
4020 v_ssn varchar(9);
4021 l_first_name varchar2(3000);
4022 l_middle_name varchar2(3000);
4023 l_last_name varchar2(3000);
4024 l_emp_city varchar2(3000);
4025 l_phone varchar(20);
4026 begin
4027
4028 hr_utility.set_location('Entering...: ' || l_api_name,10);
4029
4030 hr_utility.trace('p_federal_id= ' || p_federal_id);
4031 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
4032
4033 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
4034 hr_utility.set_location('National identifier has hyphen' ,12);
4035 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
4036 else
4037 hr_utility.set_location('National identifier has no hyphen' ,14);
4038 v_ssn := p_national_identifier;
4039 end if;
4040
4041 hr_utility.trace('v_ssn= ' || v_ssn);
4042 hr_utility.trace('p_first_name= ' || p_first_name);
4043 hr_utility.trace('p_middle_name= ' || p_middle_name);
4044 hr_utility.trace('p_last_name= ' || p_last_name);
4045 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
4046 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
4047 hr_utility.trace('p_emp_city= ' || p_emp_city);
4048 hr_utility.trace('p_emp_state= ' || p_emp_state);
4049 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
4050 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
4051 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
4052 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
4053
4054 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
4055 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
4056 hr_utility.trace('p_loc_city= ' || p_loc_city);
4057 hr_utility.trace('p_loc_state= ' || p_loc_state);
4058 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
4059 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
4060 hr_utility.trace('p_contact_phone = ' || p_contact_phone);
4061 hr_utility.trace('p_contact_name = ' || p_contact_name);
4062
4063
4064 hr_utility.set_location(l_api_name,30);
4065 l_first_name := remove_special_characters(p_first_name);
4066 l_middle_name := remove_special_characters(p_middle_name);
4067 l_last_name := remove_special_characters(p_last_name,'-');
4068 l_emp_city := remove_special_characters(p_emp_city,'-');
4069 l_phone := replace(replace(replace(nvl(p_contact_phone,' '),'-',NULL),'(',NULL),')',NULL);
4070
4071 --
4072 -- format new hire rocord
4073 --
4074 l_buffer := nvl(l_first_name,' ') || g_delimiter ||
4075 nvl(l_middle_name,' ') || g_delimiter ||
4076 nvl(l_last_name,' ') || g_delimiter ||
4077 v_ssn || g_delimiter ||
4078 nvl(p_emp_address_line1,' ')|| g_delimiter ||
4079 nvl(p_emp_address_line2,' ') || g_delimiter ||
4080 nvl(p_emp_address_line3,' ') || g_delimiter ||
4081 nvl(l_emp_city,' ') || g_delimiter ||
4082 nvl(p_emp_state,' ') || g_delimiter ||
4083 nvl(p_emp_zip,' ') || g_delimiter ||
4084 nvl(p_emp_zip_extension,' ') || g_delimiter ||
4085 nvl(p_emp_country_code,' ') || g_delimiter ||
4086 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
4087 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
4088 nvl(p_state_of_hire,' ') || g_delimiter ||
4089 nvl(p_federal_id,' ') || g_delimiter ||
4090 nvl(p_state_ein,' ') || g_delimiter ||
4091 nvl(p_tax_unit_name,' ') || g_delimiter ||
4092 nvl(p_loc_address_line1,' ') || g_delimiter ||
4093 nvl(p_loc_address_line2,' ') || g_delimiter ||
4094 nvl(p_loc_address_line3,' ') || g_delimiter ||
4095 nvl(p_loc_city,' ')|| g_delimiter ||
4096 nvl(p_loc_state,' ') || g_delimiter ||
4097 nvl(p_loc_zip,' ') || g_delimiter ||
4098 nvl(p_loc_zip_extension,' ') || g_delimiter ||
4099 nvl(p_loc_country_code,' ') || g_delimiter ||
4100 nvl(l_phone,' ') || g_delimiter ||
4101 nvl(p_contact_phone_ext,' ')||g_delimiter ||
4102 nvl(p_contact_name,' ') ||
4103 g_eol;
4104
4105 hr_utility.set_location('Leaving.... :' || l_api_name,100);
4106
4107 return l_buffer;
4108 exception
4109 when others then
4110 hr_utility.set_location('Leaving.... :' || l_api_name,200);
4111 raise;
4112 end a03_ar_new_hire_record;
4113 /****************************************************************
4114 * This procedure formats a03 MD new hire audit report header *
4115 * *
4116 * *
4117 ****************************************************************/
4118 function a03_md_new_hire_header
4119 return varchar2
4120 is
4121 l_api_name varchar2(61) := g_package_name || '.a03_md_new_hire_header';
4122 l_buffer varchar2(2000);
4123 begin
4124 --
4125 hr_utility.set_location('Entering...: ' || l_api_name,10);
4126 l_buffer := 'First Name' || g_delimiter ||
4127 'Middle Name' || g_delimiter ||
4128 'Last Name' || g_delimiter ||
4129 'Social Security Number' || g_delimiter ||
4130 'Employee Address Line 1' || g_delimiter ||
4131 'Employee Address Line 2' || g_delimiter ||
4132 'Employee Address Line 3' || g_delimiter ||
4133 'Employee City' || g_delimiter ||
4134 'Employee State' || g_delimiter ||
4135 'Employee Zip' || g_delimiter ||
4136 'Employee Zip Extension' || g_delimiter ||
4137 'Employee Country Code' || g_delimiter ||
4138 'Date of Birth' || g_delimiter ||
4139 'Date of Hire' || g_delimiter ||
4140 'State of Hire' || g_delimiter ||
4141 'Medical Insurance Available' || g_delimiter ||
4142 'FEIN' || g_delimiter ||
4143 'State SUI' || g_delimiter ||
4144 'Employer Name' || g_delimiter ||
4145 'Employer Address Line 1' || g_delimiter ||
4146 'Employer Address Line 2' || g_delimiter ||
4147 'Employer Address Line 3' || g_delimiter ||
4148 'Employer City' || g_delimiter ||
4149 'Employer State' || g_delimiter ||
4150 'Employer Zip' || g_delimiter ||
4151 'Employer Zip Extension' || g_delimiter ||
4152 'Employer Country Code' || g_delimiter ||
4153 'Employer Phone' || g_delimiter ||
4154 'Employer Phone Extension' || g_delimiter ||
4155 'Employer Contact' || g_delimiter ||
4156 'Employee Gender' || g_delimiter ||
4157 'Employer Fax Number' || g_delimiter ||
4158 'Employer Contact Email' || g_delimiter ||
4159 'Employee Salary' || g_delimiter ||
4160 'Employee Salary Frequency'||
4161 g_eol;
4162
4163 hr_utility.set_location('Leaving.... :' || l_api_name,100);
4164
4165 return l_buffer;
4166 exception
4167 when others then
4168 hr_utility.set_location('Leaving.... :' || l_api_name,200);
4169 raise;
4170 end a03_md_new_hire_header;
4171 --
4172 /****************************************************************
4173 * This procedure formats a03 Maryland new hire audit report *
4174 * *
4175 * *
4176 ****************************************************************/
4177 function a03_md_new_hire_record
4178 (
4179 p_national_identifier in varchar2
4180 ,p_first_name in varchar2
4181 ,p_middle_name in varchar2
4182 ,p_last_name in varchar2
4183 ,p_emp_address_line1 in varchar2
4184 ,p_emp_address_line2 in varchar2
4185 ,p_emp_address_line3 in varchar2
4186 ,p_emp_city in varchar2
4187 ,p_emp_state in varchar2
4188 ,p_emp_zip in varchar2
4189 ,p_emp_zip_extension in varchar2
4190 ,p_emp_country_code in varchar2
4191 ,p_date_of_birth in date
4192 ,p_date_of_hire in date
4193 ,p_state_of_hire in varchar2
4194 ,p_medical_insr_avail in varchar2
4195 ,p_federal_id in varchar2
4196 ,p_suin_company_state_id in varchar2
4197 ,p_tax_unit_name in varchar2
4198 ,p_loc_address_line1 in varchar2
4199 ,p_loc_address_line2 in varchar2
4200 ,p_loc_address_line3 in varchar2
4201 ,p_loc_city in varchar2
4202 ,p_loc_state in varchar2
4203 ,p_loc_zip in varchar2
4204 ,p_loc_zip_extension in varchar2
4205 ,p_loc_country_code in varchar2
4206 ,p_loc_phone in varchar2
4207 ,p_loc_phone_extension in varchar2
4208 ,p_loc_contact in varchar2
4209 ,p_gender in varchar2
4210 ,p_loc_fax in varchar2
4211 ,p_loc_email in varchar2
4212 ,p_salary in varchar2
4213 ,p_sal_frequency in varchar2
4214 ) return varchar2
4215 is
4216 l_api_name varchar2(61) := g_package_name || '.a03_md_new_hire_record';
4217 l_buffer varchar2(2000);
4218 v_ssn varchar(9);
4219 l_first_name varchar2(3000);
4220 l_middle_name varchar2(3000);
4221 l_last_name varchar2(3000);
4222 l_emp_city varchar2(3000);
4223 l_federal_id varchar2(30);
4224 l_phone varchar2(20);
4225 begin
4226
4227 hr_utility.set_location('Entering...: ' || l_api_name,10);
4228
4229 hr_utility.trace('p_federal_id= ' || p_federal_id);
4230 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
4231
4232 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
4233 hr_utility.set_location('National identifier has hyphen' ,12);
4234 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
4235 else
4236 hr_utility.set_location('National identifier has no hyphen' ,14);
4237 v_ssn := p_national_identifier;
4238 end if;
4239
4240 hr_utility.trace('v_ssn= ' || v_ssn);
4241 hr_utility.trace('p_first_name= ' || p_first_name);
4242 hr_utility.trace('p_middle_name= ' || p_middle_name);
4243 hr_utility.trace('p_last_name= ' || p_last_name);
4244 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
4245 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
4246 hr_utility.trace('p_emp_city= ' || p_emp_city);
4247 hr_utility.trace('p_emp_state= ' || p_emp_state);
4248 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
4249 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
4250 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
4251 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
4252
4253 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
4254 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
4255 hr_utility.trace('p_loc_city= ' || p_loc_city);
4256 hr_utility.trace('p_loc_state= ' || p_loc_state);
4257 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
4258 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
4259 hr_utility.trace('p_salary = ' || p_salary );
4260 hr_utility.trace('p_sal_frequency= ' || p_sal_frequency );
4261 hr_utility.trace('p_gender = ' || p_gender );
4262
4263
4264
4265 hr_utility.set_location(l_api_name,30);
4266 l_first_name := remove_special_characters(p_first_name);
4267 l_middle_name := remove_special_characters(p_middle_name);
4268 l_last_name := remove_special_characters(p_last_name,'-');
4269 l_emp_city := remove_special_characters(p_emp_city,'-');
4270 l_federal_id := replace(nvl(p_federal_id,' '),'-',NULL);
4271 l_phone := replace(replace(replace(nvl(p_loc_phone,' '),'-',NULL),'(',NULL),')',NULL);
4272
4273 --
4274 -- format new hire record
4275 --
4276 l_buffer := nvl(l_first_name,' ') || g_delimiter ||
4277 nvl(l_middle_name,' ') || g_delimiter ||
4278 nvl(l_last_name,' ') || g_delimiter ||
4279 v_ssn || g_delimiter ||
4280 nvl(p_emp_address_line1,' ') || g_delimiter ||
4281 nvl(p_emp_address_line2,' ') || g_delimiter ||
4282 nvl(p_emp_address_line3,' ') || g_delimiter ||
4283 nvl(l_emp_city,' ') || g_delimiter ||
4284 nvl(p_emp_state,' ') || g_delimiter ||
4285 nvl(p_emp_zip,' ' ) || g_delimiter ||
4286 nvl(p_emp_zip_extension,' ') || g_delimiter ||
4287 nvl(p_emp_country_code,' ') || g_delimiter ||
4288 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
4289 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
4290 nvl(p_state_of_hire,' ') || g_delimiter ||
4291 nvl(p_medical_insr_avail,' ') || g_delimiter ||
4292 nvl(l_federal_id,' ') || g_delimiter ||
4293 nvl(p_suin_company_state_id ,'APPLIEDFOR') || g_delimiter ||
4294 nvl(p_tax_unit_name,' ') || g_delimiter ||
4295 nvl(p_loc_address_line1,' ') || g_delimiter ||
4296 nvl(p_loc_address_line2,' ') || g_delimiter ||
4297 nvl(p_loc_address_line3,' ') || g_delimiter ||
4298 nvl(p_loc_city,' ') || g_delimiter ||
4299 nvl(p_loc_state,' ') || g_delimiter ||
4300 nvl(p_loc_zip,' ') || g_delimiter ||
4301 nvl(p_loc_zip_extension,' ') || g_delimiter ||
4302 nvl(p_loc_country_code,' ') || g_delimiter ||
4303 nvl(l_phone,' ') || g_delimiter ||
4304 nvl(p_loc_phone_extension,' ') || g_delimiter ||
4305 nvl(p_loc_contact,' ') || g_delimiter ||
4306 nvl(p_gender,' ') || g_delimiter ||
4307 nvl(p_loc_fax,' ') || g_delimiter ||
4308 nvl(p_loc_email,' ') || g_delimiter ||
4309 nvl(p_salary ,' ') || g_delimiter ||
4310 nvl(p_sal_frequency,' ') ||
4311 g_eol;
4312
4313 hr_utility.set_location('Leaving.... :' || l_api_name,100);
4314
4315 return l_buffer;
4316 exception
4317 when others then
4318 hr_utility.set_location('Leaving.... :' || l_api_name,200);
4319 raise;
4320 end a03_md_new_hire_record;
4321 /****************************************************************
4322 * This procedure formats a03 LA new hire audit report header *
4323 * *
4324 * *
4325 ****************************************************************/
4326 function a03_la_new_hire_header
4327 return varchar2
4328 is
4329 l_api_name varchar2(61) := g_package_name || '.a03_la_new_hire_header';
4330 l_buffer varchar2(2000);
4331 begin
4332 --
4333 hr_utility.set_location('Entering...: ' || l_api_name,10);
4334 l_buffer := 'First Name' || g_delimiter ||
4335 'Middle Name' || g_delimiter ||
4336 'Last Name' || g_delimiter ||
4337 'Employee Suffix' || g_delimiter ||
4338 'Social Security Number' || g_delimiter ||
4339 'Employee Address Line 1' || g_delimiter ||
4340 'Employee Address Line 2' || g_delimiter ||
4341 'Employee Address Line 3' || g_delimiter ||
4342 'Employee City' || g_delimiter ||
4343 'Employee State' || g_delimiter ||
4344 'Employee Zip' || g_delimiter ||
4345 'Employee Zip Extension' || g_delimiter ||
4346 'Employee Country Code' || g_delimiter ||
4347 'Employee Country Name' || g_delimiter ||
4348 'Employee Country Zip' || g_delimiter ||
4349 'Employee Marital Status' || g_delimiter ||
4350 'Date of Hire' || g_delimiter ||
4351 'Employer Name' || g_delimiter ||
4352 'Employer Address Line 1' || g_delimiter ||
4353 'Employer Address Line 2' || g_delimiter ||
4354 'Employer Address Line 3' || g_delimiter ||
4355 'Employer City' || g_delimiter ||
4356 'Employer State' || g_delimiter ||
4357 'Employer Zip' || g_delimiter ||
4358 'Employer Zip Extension' || g_delimiter ||
4359 'Employer Country Code' || g_delimiter ||
4360 'Employer Country Name '|| g_delimiter ||
4361 'Employer Country Zip '|| g_delimiter ||
4362 'Federal EIN' || g_delimiter ||
4363 'State EIN' || g_delimiter ||
4364 'Employee Occupation'|| g_delimiter ||
4365 'Employee Salary' || g_delimiter ||
4366 'Employee Salary Frequency'|| g_delimiter ||
4367 'State of Hire' || g_delimiter ||
4368 'Date of Birth' || g_delimiter ||
4369 'Medical Insurance Availed' || g_delimiter ||
4370 'Employer Contact First name' || g_delimiter ||
4371 'Employer Contact Last Name '|| g_delimiter ||
4372 'Employer Phone' ||
4373 g_eol;
4374
4375 hr_utility.set_location('Leaving.... :' || l_api_name,100);
4376
4377 return l_buffer;
4378 exception
4379 when others then
4380 hr_utility.set_location('Leaving.... :' || l_api_name,200);
4381 raise;
4382 end a03_la_new_hire_header;
4383 --
4384 /****************************************************************
4385 * This procedure formats a03 Louisiana new hire audit report *
4386 * *
4387 * *
4388 ****************************************************************/
4389 function a03_la_new_hire_record
4390 (
4391 p_first_name in varchar2
4392 ,p_middle_name in varchar2
4393 ,p_last_name in varchar2
4394 ,p_suffix in varchar2
4395 ,p_national_identifier in varchar2
4396 ,p_emp_address_line1 in varchar2
4397 ,p_emp_address_line2 in varchar2
4398 ,p_emp_address_line3 in varchar2
4399 ,p_emp_city in varchar2
4400 ,p_emp_state in varchar2
4401 ,p_emp_zip in varchar2
4402 ,p_emp_zip_extension in varchar2
4403 ,p_emp_country_code in varchar2
4404 ,p_emp_country_name in varchar2
4405 ,p_emp_country_zip in varchar2
4406 ,p_emp_marital_status in varchar2
4407 ,p_date_of_hire in date
4408 ,p_tax_unit_name in varchar2
4409 ,p_loc_address_line1 in varchar2
4410 ,p_loc_address_line2 in varchar2
4411 ,p_loc_address_line3 in varchar2
4412 ,p_loc_city in varchar2
4413 ,p_loc_state in varchar2
4414 ,p_loc_zip in varchar2
4415 ,p_loc_zip_extension in varchar2
4416 ,p_loc_country_code in varchar2
4417 ,p_loc_country_name in varchar2
4418 ,p_loc_country_zip in varchar2
4419 ,p_federal_id in varchar2
4420 ,p_sit_company_state_id in varchar2
4421 ,p_job in varchar2
4422 ,p_salary in varchar2
4423 ,p_sal_frequency in varchar2
4424 ,p_state_of_hire in varchar2
4425 ,p_date_of_birth in date
4426 ,p_emp_insr_ind in varchar2
4427 ,p_loc_contact_first_name in varchar2
4428 ,p_loc_contact_last_name in varchar2
4429 ,p_loc_phone in varchar2
4430 ) return varchar2
4431 is
4432 l_api_name varchar2(61) := g_package_name || '.a03_la_new_hire_record';
4433 l_buffer varchar2(2000);
4434 v_ssn varchar(9);
4435 l_phone varchar(10);
4436 l_first_name varchar2(3000);
4437 l_middle_name varchar2(3000);
4438 l_last_name varchar2(3000);
4439 l_emp_city varchar2(3000);
4440 l_loc_city varchar2(3000);
4441
4442 begin
4443
4444 hr_utility.set_location('Entering...: ' || l_api_name,10);
4445
4446 hr_utility.trace('p_federal_id= ' || p_federal_id);
4447 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
4448
4449 if substr(p_national_identifier,4,1) = '-' and substr(p_national_identifier,7,1) = '-' then
4450 hr_utility.set_location('National identifier has hyphen' ,12);
4451 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
4452 else
4453 hr_utility.set_location('National identifier has no hyphen' ,14);
4454 v_ssn := p_national_identifier;
4455 end if;
4456
4457 hr_utility.trace('v_ssn= ' || v_ssn);
4458 hr_utility.trace('p_first_name= ' || p_first_name);
4459 hr_utility.trace('p_middle_name= ' || p_middle_name);
4460 hr_utility.trace('p_last_name= ' || p_last_name);
4461 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
4462 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
4463 hr_utility.trace('p_emp_city= ' || p_emp_city);
4464 hr_utility.trace('p_emp_state= ' || p_emp_state);
4465 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
4466 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
4467 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
4468 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
4469
4470 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
4471 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
4472 hr_utility.trace('p_loc_city= ' || p_loc_city);
4473 hr_utility.trace('p_loc_state= ' || p_loc_state);
4474 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
4475 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
4476
4477
4478
4479 hr_utility.set_location(l_api_name,30);
4480 l_phone := replace(nvl(p_loc_phone,' '),'-',NULL);
4481 l_first_name := remove_special_characters(p_first_name);
4482 l_middle_name := remove_special_characters(p_middle_name);
4483 l_last_name := remove_special_characters(p_last_name);
4484 l_emp_city := remove_special_characters(p_emp_city);
4485 l_loc_city := remove_special_characters(p_loc_city);
4486
4487 --
4488 -- format new hire record
4489 --
4490 l_buffer := nvl(l_first_name,'') || g_delimiter ||
4491 nvl(l_middle_name,'') || g_delimiter ||
4492 l_last_name || g_delimiter ||
4493 p_suffix|| g_delimiter ||
4494 v_ssn || g_delimiter ||
4495 nvl(p_emp_address_line1,' ') || g_delimiter ||
4496 nvl(p_emp_address_line2,' ') || g_delimiter ||
4497 nvl(p_emp_address_line3,' ') || g_delimiter ||
4498 nvl(l_emp_city,' ') || g_delimiter ||
4499 nvl(p_emp_state,' ') || g_delimiter ||
4500 nvl(p_emp_zip,' ' ) || g_delimiter ||
4501 nvl(p_emp_zip_extension,'0000') || g_delimiter ||
4502 nvl(p_emp_country_code,' ') || g_delimiter ||
4503 nvl(p_emp_country_name,' ') || g_delimiter ||
4504 nvl(p_emp_country_zip,' ') || g_delimiter ||
4505 nvl(p_emp_marital_status,' ') || g_delimiter ||
4506 nvl(to_char(p_date_of_hire,'CCYYMMDD'),'00000000') || g_delimiter ||
4507 nvl(p_tax_unit_name,' ') || g_delimiter ||
4508 nvl(p_loc_address_line1,' ') || g_delimiter ||
4509 nvl(p_loc_address_line2,' ') || g_delimiter ||
4510 nvl(p_loc_address_line3,' ') || g_delimiter ||
4511 nvl(l_loc_city,' ') || g_delimiter ||
4512 nvl(p_loc_state,' ') || g_delimiter ||
4513 nvl(p_loc_zip,' ') || g_delimiter ||
4514 nvl(p_loc_zip_extension,'0000') || g_delimiter ||
4515 nvl(p_loc_country_code,' ') || g_delimiter ||
4516 nvl(p_loc_country_name,' ') || g_delimiter ||
4517 nvl(p_loc_country_zip,' ') || g_delimiter ||
4518 nvl(p_federal_id,' ') || g_delimiter ||
4519 nvl(p_sit_company_state_id ,' ') || g_delimiter ||
4520 nvl(p_job,' ') || g_delimiter ||
4521 nvl(p_salary ,' ') || g_delimiter ||
4522 nvl(p_sal_frequency,' ') || g_delimiter ||
4523 nvl(p_state_of_hire,' ') || g_delimiter ||
4524 nvl(to_char(p_date_of_birth,'CCYYMMDD'),'00000000') || g_delimiter ||
4525 nvl(p_emp_insr_ind,' ') || g_delimiter ||
4526 nvl(p_loc_contact_first_name,' ') || g_delimiter ||
4527 nvl(p_loc_contact_last_name,' ') || g_delimiter ||
4528 nvl(l_phone,' ') ||
4529 g_eol;
4530
4531 hr_utility.set_location('Leaving.... :' || l_api_name,100);
4532
4533 return l_buffer;
4534 exception
4535 when others then
4536 hr_utility.set_location('Leaving.... :' || l_api_name,200);
4537 raise;
4538 end a03_la_new_hire_record;
4539 --
4540 --#10387522 end
4541 /****************************************************************
4542 * This procedure retrieves location address *
4543 * *
4544 * *
4545 ****************************************************************/
4546 procedure get_location_address
4547 (
4548 p_location_id in number
4549 ,p_address out nocopy varchar2
4550 ,p_city out nocopy varchar2
4551 ,p_state out nocopy varchar2
4552 ,p_zip out nocopy varchar2
4553 ,p_zip_extension out nocopy varchar2
4554 ) IS
4555 --
4556 l_api_name varchar2(61) := g_package_name || '.get_location_address';
4557 f_address varchar2(300) := NULL;
4558 f_city varchar2(48) := NULL;
4559 f_state varchar2(50) := NULL;
4560 f_zip varchar2(20) := NULL;
4561 f_zip_extension varchar2(4) := NULL;
4562 v_index number(5);
4563 --
4564 v_address_line_1 hr_locations.address_line_1%TYPE;
4565 v_address_line_2 hr_locations.address_line_2%TYPE;
4566 v_address_line_3 hr_locations.address_line_3%TYPE;
4567 v_town_or_city hr_locations.town_or_city%TYPE;
4568 v_region_2 hr_locations.region_2%TYPE;
4569 v_region_1 hr_locations.region_1%TYPE;
4570 v_style hr_locations.style%TYPE;
4571 v_postal_code hr_locations.postal_code%TYPE;
4572 --
4573 cursor get_location_record is
4574 select address_line_1, address_line_2, address_line_3,
4575 town_or_city, region_1, region_2, postal_code ,style
4576 from hr_locations
4577 where location_id = p_location_id;
4578 --
4579 begin
4580 --
4581 hr_utility.set_location('Entering :' || l_api_name, 5);
4582 hr_utility.trace('location_id is ' || to_char(p_location_id));
4583 --
4584 open get_location_record;
4585 --
4586 fetch get_location_record into v_address_line_1, v_address_line_2,
4587 v_address_line_3, v_town_or_city, v_region_1
4588 ,v_region_2, v_postal_code ,v_style;
4589 --
4590 hr_utility.set_location(l_api_name, 10);
4591 --
4592 if get_location_record%found
4593 then
4594 --
4595 if v_address_line_1 is not null
4596 then
4597 f_address := v_address_line_1;
4598 end if;
4599 --
4600 hr_utility.set_location(l_api_name, 11);
4601 if v_address_line_2 is not null
4602 then
4603 f_address := f_address || ' ' || v_address_line_2;
4604 end if;
4605 --
4606 hr_utility.set_location(l_api_name, 12);
4607 if v_address_line_3 is not null
4608 then
4609 f_address := f_address || ' ' || v_address_line_3;
4610 end if;
4611 hr_utility.trace('street address is '|| f_address);
4612 --
4613 hr_utility.set_location(l_api_name, 13);
4614 if v_town_or_city is not null
4615 then
4616 f_city:= rpad(v_town_or_city,48,' ');
4617 end if;
4618 hr_utility.trace('city is '|| f_city);
4619 --
4620 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
4621 then
4622 f_state := v_region_2;
4623 elsif v_style = 'CA' and v_region_1 is not null then
4624 f_state := v_region_1;
4625 end if;
4626 hr_utility.trace('state is '|| f_state);
4627 --
4628 if v_postal_code is not null
4629 then
4630 f_zip := v_postal_code;
4631 v_index := instr(v_postal_code,'-') ;
4632 if (v_index <> 0)
4633 then
4634 f_zip := substr(v_postal_code,1,5);
4635 f_zip_extension := substr(v_postal_code,v_index+1,4);
4636 else
4637 f_zip_extension := null;
4638 end if;
4639 end if;
4640 hr_utility.trace('postal code is '|| f_zip);
4641 hr_utility.trace('postal code extension is '|| f_zip_extension);
4642
4643
4644 --
4645 close get_location_record;
4646 --
4647 hr_utility.set_location(l_api_name, 20);
4648
4649 p_address := f_address;
4650 p_city := f_city;
4651 p_state := f_state;
4652 p_zip := f_zip;
4653 p_zip_extension := f_zip_extension;
4654 --
4655 end if;
4656 --
4657 hr_utility.set_location('Leaving ..' || l_api_name, 100);
4658
4659 exception
4660 when others then
4661 hr_utility.trace('Error in ' || l_api_name);
4662 hr_utility.set_location(l_api_name, 30);
4663 --
4664 end get_location_address;
4665 /****************************************************************
4666 * This procedure retrieves location address *
4667 * address_line1,address_line2,address_line3 *
4668 * *
4669 ****************************************************************/
4670 procedure get_location_address_3lines
4671 (
4672 p_location_id in number
4673 ,p_address_line1 out nocopy varchar2
4674 ,p_address_line2 out nocopy varchar2
4675 ,p_address_line3 out nocopy varchar2
4676 ,p_city out nocopy varchar2
4677 ,p_state out nocopy varchar2
4678 ,p_zip out nocopy varchar2
4679 ,p_zip_extension out nocopy varchar2
4680 ,p_country out nocopy varchar2
4681 ) IS
4682 --
4683 l_api_name varchar2(61) := g_package_name || '.get_location_address_3lines';
4684 f_address_line1 varchar2(300) := NULL;
4685 f_address_line2 varchar2(300) := NULL;
4686 f_address_line3 varchar2(300) := NULL;
4687 f_city varchar2(48) := NULL;
4688 f_state varchar2(50) := NULL;
4689 f_zip varchar2(20) := NULL;
4690 f_zip_extension varchar2(4) := NULL;
4691 v_index number(5);
4692 --
4693 v_address_line_1 hr_locations.address_line_1%TYPE;
4694 v_address_line_2 hr_locations.address_line_2%TYPE;
4695 v_address_line_3 hr_locations.address_line_3%TYPE;
4696 v_town_or_city hr_locations.town_or_city%TYPE;
4697 v_region_2 hr_locations.region_2%TYPE;
4698 v_region_1 hr_locations.region_1%TYPE;
4699 v_postal_code hr_locations.postal_code%TYPE;
4700 v_country hr_locations.country%TYPE;
4701 v_style hr_locations.style%TYPE;
4702 --
4703 cursor get_location_record is
4704 select address_line_1, address_line_2, address_line_3,
4705 town_or_city, region_1, region_2, postal_code
4706 ,country,style
4707 from hr_locations
4708 where location_id = p_location_id;
4709 --
4710 begin
4711 --
4712 hr_utility.set_location('Entering :' || l_api_name, 5);
4713 hr_utility.trace('location_id is ' || to_char(p_location_id));
4714 --
4715 open get_location_record;
4716 --
4717 fetch get_location_record into v_address_line_1, v_address_line_2,
4718 v_address_line_3, v_town_or_city, v_region_1,v_region_2
4719 , v_postal_code ,v_country, v_style;
4720 --
4721 hr_utility.set_location(l_api_name, 10);
4722 --
4723 if get_location_record%found
4724 then
4725 --
4726 if v_address_line_1 is not null
4727 then
4728 f_address_line1 := v_address_line_1;
4729 hr_utility.trace('address_line1 is '|| f_address_line1);
4730 end if;
4731 --
4732 hr_utility.set_location(l_api_name, 11);
4733 if v_address_line_2 is not null
4734 then
4735 f_address_line2 := v_address_line_2;
4736 hr_utility.trace('address_line2 is '|| f_address_line2);
4737 end if;
4738 --
4739 hr_utility.set_location(l_api_name, 12);
4740 if v_address_line_3 is not null
4741 then
4742 f_address_line3 := v_address_line_3;
4743 hr_utility.trace('address_line3 is '|| f_address_line3);
4744 end if;
4745 --
4746 hr_utility.set_location(l_api_name, 13);
4747 if v_town_or_city is not null
4748 then
4749 f_city:= rpad(v_town_or_city,48,' ');
4750 end if;
4751 hr_utility.trace('city is '|| f_city);
4752 --
4753 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
4754 then
4755 f_state := v_region_2;
4756 elsif v_style = 'CA' and v_region_1 is not null then
4757 f_state := v_region_1;
4758 end if;
4759 hr_utility.trace('state is '|| f_state);
4760 --
4761 if v_country = 'US'
4762 then
4763 if v_postal_code is not null
4764 then
4765 f_zip := v_postal_code;
4766 v_index := instr(v_postal_code,'-') ;
4767 if (v_index <> 0)
4768 then
4769 f_zip := substr(v_postal_code,1,5);
4770 f_zip_extension := substr(v_postal_code,v_index+1,4);
4771 else
4772 f_zip_extension := null;
4773 end if;
4774 end if;
4775 p_country := ' ';
4776 else
4777 f_zip := v_postal_code;
4778 f_zip_extension := null;
4779 end if;
4780 hr_utility.trace('country code is '|| v_country);
4781 hr_utility.trace('postal code is '|| f_zip);
4782 hr_utility.trace('postal code extension is '|| f_zip_extension);
4783
4784
4785 --
4786 close get_location_record;
4787 --
4788 hr_utility.set_location(l_api_name, 20);
4789
4790 p_address_line1 := f_address_line1;
4791 p_address_line2 := f_address_line2;
4792 p_address_line3 := f_address_line3;
4793 p_city := f_city;
4794 p_state := f_state;
4795 p_zip := f_zip;
4796 p_zip_extension := f_zip_extension;
4797 p_country := v_country;
4798 --
4799 end if;
4800 --
4801 hr_utility.set_location('Leaving ..' || l_api_name, 100);
4802
4803 exception
4804 when others then
4805 hr_utility.trace('Error in ' || l_api_name);
4806 hr_utility.set_location(l_api_name, 30);
4807 --
4808 end get_location_address_3lines;
4809 --
4810 /****************************************************************
4811 * This procedure retrieves employee address *
4812 * *
4813 * *
4814 ****************************************************************/
4815 procedure get_employee_address
4816 (
4817 p_person_id in number
4818 ,p_address out nocopy varchar2
4819 ,p_city out nocopy varchar2
4820 ,p_state out nocopy varchar2
4821 ,p_zip out nocopy varchar2
4822 ,p_zip_extension out nocopy varchar2
4823 ) IS
4824 --
4825 l_api_name varchar2(61) := g_package_name || '.get_employee_address';
4826 f_address varchar2(300) := NULL;
4827 f_city varchar2(48) := NULL;
4828 f_state varchar2(50) := NULL;
4829 f_zip varchar2(20) := NULL;
4830 f_zip_extension varchar2(4) := NULL;
4831 v_index number(5);
4832 --
4833 -- address_record per_addresses%rowtype;
4834 --
4835 v_address_line1 per_addresses.address_line1%TYPE;
4836 v_address_line2 per_addresses.address_line2%TYPE;
4837 v_address_line3 per_addresses.address_line3%TYPE;
4838 v_town_or_city per_addresses.town_or_city%TYPE;
4839 v_region_2 per_addresses.region_2%TYPE;
4840 v_region_1 per_addresses.region_1%TYPE;
4841 v_postal_code per_addresses.postal_code%TYPE;
4842 v_country per_addresses.country%TYPE;
4843 v_style per_addresses.style%TYPE;
4844 --
4845 cursor get_address_record is
4846 select address_line1, address_line2, address_line3,
4847 town_or_city, region_1, region_2, postal_code
4848 ,style
4849 from per_addresses
4850 where person_id = p_person_id
4851 and primary_flag = 'Y'
4852 and nvl(date_to, sysdate) >= sysdate;
4853 --
4854 begin
4855 --
4856 hr_utility.set_location(l_api_name, 0);
4857 --
4858 open get_address_record;
4859 --
4860 fetch get_address_record into v_address_line1, v_address_line2,
4861 v_address_line3, v_town_or_city, v_region_1
4862 ,v_region_2, v_postal_code,v_style;
4863 --
4864 hr_utility.set_location(l_api_name, 5);
4865 --
4866 if get_address_record%found
4867 then
4868 --
4869 if v_address_line1 is not null
4870 then
4871 f_address := v_address_line1;
4872 end if;
4873 --
4874 if v_address_line2 is not null
4875 then
4876 f_address := f_address || ' ' || v_address_line2;
4877 end if;
4878 --
4879 if v_address_line3 is not null
4880 then
4881 f_address := f_address || ' ' || v_address_line3;
4882 end if;
4883 --
4884 hr_utility.trace('Person Address is '|| f_address);
4885
4886 if v_town_or_city is not null
4887 then
4888 f_city:= rpad(v_town_or_city,31,' ');
4889 end if;
4890 hr_utility.trace('city is '|| f_city);
4891 --
4892 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
4893 then
4894 f_state := v_region_2;
4895 elsif v_style = 'CA' and v_region_1 is not null then
4896 f_state := v_region_1;
4897 end if;
4898 hr_utility.trace('state is '|| f_state);
4899 --
4900 if v_postal_code is not null
4901 then
4902 f_zip := v_postal_code;
4903 v_index := instr(v_postal_code,'-') ;
4904 if (v_index <> 0)
4905 then
4906 f_zip := substr(v_postal_code,1,5);
4907 f_zip_extension := substr(v_postal_code,v_index+1,4);
4908 else
4909 f_zip_extension := null;
4910 end if;
4911 end if;
4912 hr_utility.trace('country code is '|| v_country);
4913 hr_utility.trace('postal code is '|| f_zip);
4914 hr_utility.trace('postal extension code is '|| f_zip_extension);
4915
4916 --
4917 hr_utility.set_location(l_api_name, 10);
4918 close get_address_record;
4919 --
4920 --
4921 p_address := f_address;
4922 p_city := f_city;
4923 p_state := f_state;
4924 p_zip := f_zip;
4925 p_zip_extension := f_zip_extension;
4926 --
4927 end if;
4928 --
4929 hr_utility.set_location('Leaving...:' || l_api_name, 50);
4930 --
4931 exception when NO_DATA_FOUND then NULL;
4932 --
4933 end get_employee_address;
4934 --
4935 /****************************************************************
4936 * This procedure retrieves employee address *
4937 * address_line1,address_line2,address_line3 *
4938 * *
4939 ****************************************************************/
4940 procedure get_employee_address_3lines
4941 (
4942 p_person_id in number
4943 ,p_address_line1 out nocopy varchar2
4944 ,p_address_line2 out nocopy varchar2
4945 ,p_address_line3 out nocopy varchar2
4946 ,p_city out nocopy varchar2
4947 ,p_state out nocopy varchar2
4948 ,p_zip out nocopy varchar2
4949 ,p_zip_extension out nocopy varchar2
4950 ,p_country out nocopy varchar2
4951 ) IS
4952 --
4953 l_api_name varchar2(61) := g_package_name || '.get_employee_address_3lines';
4954 f_address_line1 varchar2(300) := NULL;
4955 f_address_line2 varchar2(300) := NULL;
4956 f_address_line3 varchar2(300) := NULL;
4957 f_city varchar2(48) := NULL;
4958 f_state varchar2(50) := NULL;
4959 f_zip varchar2(20) := NULL;
4960 f_zip_extension varchar2(4) := NULL;
4961 v_index number(5);
4962 --
4963 -- address_record per_addresses%rowtype;
4964 --
4965 v_address_line1 per_addresses.address_line1%TYPE;
4966 v_address_line2 per_addresses.address_line2%TYPE;
4967 v_address_line3 per_addresses.address_line3%TYPE;
4968 v_town_or_city per_addresses.town_or_city%TYPE;
4969 v_region_2 per_addresses.region_2%TYPE;
4970 v_region_1 per_addresses.region_1%TYPE;
4971 v_postal_code per_addresses.postal_code%TYPE;
4972 v_country per_addresses.country%TYPE;
4973 v_style per_addresses.style%TYPE;
4974 --
4975 cursor get_address_record is
4976 select address_line1, address_line2, address_line3,
4977 town_or_city, region_1, region_2
4978 , postal_code ,country ,style
4979 from per_addresses
4980 where person_id = p_person_id
4981 and primary_flag = 'Y'
4982 and nvl(date_to, sysdate) >= sysdate;
4983 --
4984 begin
4985 --
4986 hr_utility.set_location(l_api_name, 0);
4987 --
4988 open get_address_record;
4989 --
4990 fetch get_address_record into v_address_line1, v_address_line2,
4991 v_address_line3, v_town_or_city, v_region_1,v_region_2
4992 , v_postal_code ,v_country,v_style;
4993 --
4994 hr_utility.set_location(l_api_name, 5);
4995 --
4996 if get_address_record%found
4997 then
4998 --
4999 if v_address_line1 is not null
5000 then
5001 f_address_line1 := v_address_line1;
5002 hr_utility.trace('address_line1 is '|| f_address_line1);
5003 end if;
5004 --
5005 if v_address_line2 is not null
5006 then
5007 f_address_line2 := v_address_line2;
5008 hr_utility.trace('address_line2 is '|| f_address_line2);
5009 end if;
5010 --
5011 if v_address_line3 is not null
5012 then
5013 f_address_line3 := v_address_line3;
5014 hr_utility.trace('address_line3 is '|| f_address_line3);
5015 end if;
5016 --
5017
5018 if v_town_or_city is not null
5019 then
5020 f_city:= rpad(v_town_or_city,31,' ');
5021 end if;
5022 hr_utility.trace('city is '|| f_city);
5023 --
5024 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
5025 then
5026 f_state := v_region_2;
5027 elsif v_style = 'CA' and v_region_1 is not null then
5028 f_state := v_region_1;
5029 end if;
5030 hr_utility.trace('state is '|| f_state);
5031 --
5032 if v_country = 'US'
5033 then
5034 if v_postal_code is not null
5035 then
5036 f_zip := v_postal_code;
5037 v_index := instr(v_postal_code,'-') ;
5038 if (v_index <> 0)
5039 then
5040 f_zip := substr(v_postal_code,1,5);
5041 f_zip_extension := substr(v_postal_code,v_index+1,4);
5042 else
5043 f_zip_extension := null;
5044 end if;
5045 end if;
5046 p_country := ' ';
5047 else
5048 f_zip := v_postal_code;
5049 f_zip_extension := null;
5050 end if;
5051 hr_utility.trace('country code is '|| v_country);
5052 hr_utility.trace('postal code is '|| f_zip);
5053 hr_utility.trace('postal extension code is '|| f_zip_extension);
5054
5055 --
5056 hr_utility.set_location(l_api_name, 10);
5057 close get_address_record;
5058 --
5059 --
5060 p_address_line1 := f_address_line1;
5061 p_address_line2 := f_address_line2;
5062 p_address_line3 := f_address_line3;
5063 p_city := f_city;
5064 p_state := f_state;
5065 p_zip := f_zip;
5066 p_zip_extension := f_zip_extension;
5067 p_country := v_country;
5068 --
5069 end if;
5070 --
5071 hr_utility.set_location('Leaving...:' || l_api_name, 50);
5072 --
5073 exception when NO_DATA_FOUND then NULL;
5074 --
5075 end get_employee_address_3lines;
5076 --
5077 --
5078 /****************************************************************
5079 * This procedure retrieves employer contact *
5080 * name, title,phone *
5081 * *
5082 ****************************************************************/
5083 procedure get_new_hire_contact( p_person_id in number,
5084 p_business_group_id in number,
5085 p_report_date in date,
5086 p_contact_name out nocopy varchar2,
5087 p_contact_title out nocopy varchar2,
5088 p_contact_phone out nocopy varchar2
5089 ) IS
5090 --
5091 l_api_name varchar2(61) := g_package_name || '.get_new_hire_contact';
5092 v_contact_name varchar2(240); --per_people_f.full_name%TYPE;
5093 v_contact_title varchar2(700); --per_jobs.name%TYPE;
5094 v_contact_phone varchar2(60); -- per_people_f.work_telephone%TYPE;
5095
5096 CURSOR c_new_hire_record IS
5097 Select ppf.first_name || ' ' || ppf.last_name,
5098 job.name,
5099 ppf.work_telephone
5100 From
5101 --per_people_f ppf, --view bug 4912696
5102 per_all_people_f ppf, --table
5103 --per_assignments_f paf, --view bug 4912696
5104 per_all_assignments_f paf, --table
5105 per_jobs job
5106 Where
5107 ppf.person_id = p_person_id
5108 And ppf.business_group_id + 0 = p_business_group_id
5109 And p_report_date between paf.effective_start_date
5110 and paf.effective_end_date
5111 And ppf.person_id = paf.person_id
5112 And paf.assignment_type = 'E'
5113 And paf.primary_flag = 'Y'
5114 And p_report_date between paf.effective_start_date
5115 and paf.effective_end_date
5116 -- BUG2919553
5117 And ppf.effective_start_date =
5118 (select max(ppf1.effective_start_date)
5119 from per_all_people_f ppf1 --table bug 4912696
5120 where ppf1.person_id = ppf.person_id
5121 and ppf1.effective_start_date <= p_report_date
5122 )
5123 -- End of BUG2919553
5124 And paf.job_id = job.job_id(+);
5125
5126 --
5127 begin
5128 --
5129 hr_utility.set_location('Entering....:' || l_api_name,10);
5130 --
5131 OPEN c_new_hire_record;
5132 --LOOP
5133 FETCH c_new_hire_record INTO v_contact_name, v_contact_title, v_contact_phone;
5134 --
5135 p_contact_name := substr(v_contact_name,1,60);
5136 p_contact_title := substr(v_contact_title,1,60);
5137 p_contact_phone := substr(v_contact_phone,1,60);
5138 --
5139 -- EXIT WHEN c_new_hire_record%NOTFOUND;
5140 --END LOOP;
5141
5142 CLOSE c_new_hire_record;
5143 --
5144 hr_utility.trace('Contact name : '||v_contact_name);
5145 hr_utility.trace('Contact title : '||v_contact_title);
5146 hr_utility.set_location('Leaving....:' || l_api_name ,100);
5147 --
5148 exception
5149 when no_data_found then
5150 hr_utility.set_location('Error found in ' || l_api_name,20);
5151 NULL;
5152 when others then
5153 hr_utility.set_location('Error found in ' || l_api_name,30);
5154 --
5155 end get_new_hire_contact;
5156 --
5157 --#10387522 Start
5158 /****************************************************************
5159 * This procedure retrieves employer contact *
5160 * first and last name *
5161 * *
5162 ****************************************************************/
5163 procedure get_new_hire_contact_name( p_person_id in number,
5164 p_business_group_id in number,
5165 p_report_date in date,
5166 p_contact_first_name out nocopy varchar2,
5167 p_contact_last_name out nocopy varchar2
5168 ) IS
5169 --
5170 l_api_name varchar2(61) := g_package_name || '.get_new_hire_contact_name';
5171 lv_contact_first_name per_all_people_f.first_name%TYPE;
5172 lv_contact_last_name per_all_people_f.last_name%TYPE;
5173
5174 CURSOR c_new_hire_record IS
5175 Select ppf.first_name
5176 , ppf.last_name
5177 From per_all_people_f ppf
5178 Where ppf.person_id = p_person_id
5179 And ppf.business_group_id + 0 = p_business_group_id
5180 And ppf.effective_start_date =
5181 (select max(ppf1.effective_start_date)
5182 from per_all_people_f ppf1
5183 where ppf1.person_id = ppf.person_id
5184 and ppf1.effective_start_date <= p_report_date
5185 );
5186 --
5187 begin
5188 --
5189 hr_utility.set_location('Entering....:' || l_api_name,10);
5190 --
5191 OPEN c_new_hire_record;
5192 FETCH c_new_hire_record INTO lv_contact_first_name, lv_contact_last_name;
5193 --
5194 p_contact_first_name := substr(lv_contact_first_name,1,60);
5195 p_contact_last_name := substr(lv_contact_last_name,1,60);
5196 --
5197 CLOSE c_new_hire_record;
5198 --
5199 hr_utility.trace('Contact first name : '||lv_contact_first_name);
5200 hr_utility.trace('Contact last name : '||lv_contact_last_name);
5201 hr_utility.set_location('Leaving....:' || l_api_name ,100);
5202 --
5203 exception
5204 when no_data_found then
5205 hr_utility.set_location('Error found in ' || l_api_name,20);
5206 NULL;
5207 when others then
5208 hr_utility.set_location('Error found in ' || l_api_name,30);
5209 raise;
5210 --
5211 end get_new_hire_contact_name;
5212 --
5213 /****************************************************************
5214 * This function removes special characters from the string *
5215 * excluding the allowed special characters *
5216 * *
5217 ****************************************************************/
5218 function remove_special_characters(p_string IN varchar2,
5219 p_allowed IN varchar2 default NULL)
5220 return varchar2
5221 IS
5222 l_new_string per_all_people_f.FIRST_NAME%TYPE;
5223 l_not_allowed VARCHAR2(100) := '''`;"|\-+=_#$%^&*@!~:<>?/()[]{},.';
5224 BEGIN
5225
5226 if instr(p_allowed,'`') <> 0 then
5227 fnd_file.put_line
5228 (which => fnd_file.log,
5229 buff => 'ERROR: ` is included in the list of allowed characters');
5230 RAISE hr_utility.hr_error;
5231 end if;
5232 if p_allowed IS NOT NULL then
5233 l_not_allowed := replace(translate(l_not_allowed,p_allowed,'`'),'`','');
5234 end if;
5235
5236 l_new_string := replace(translate(p_string,l_not_allowed,'`'),'`','');
5237 return l_new_string;
5238
5239 END;
5240 --
5241 /****************************************************************
5242 * This procedure checks if the length of the string *
5243 * is atleast of the specified length *
5244 * *
5245 ****************************************************************/
5246 procedure check_min_length(p_string IN varchar2,
5247 p_length IN NUMBER,
5248 p_field in varchar2,
5249 p_state in varchar2
5250 )
5251 IS
5252 BEGIN
5253 IF (p_string IS NULL) or ((length(trim(p_string)) < p_length))
5254 THEN
5255 fnd_file.put_line
5256 (which => fnd_file.log,
5257 buff => 'ERROR: '||p_field||' must be a minimum of '||p_length||' characters for '||p_state);
5258 RAISE hr_utility.hr_error;
5259 END IF;
5260 END;
5261
5262 /****************************************************************
5263 * This function returns the salary and frequency of the *
5264 * salary for an employee specific to MD state *
5265 * *
5266 ****************************************************************/
5267
5268 FUNCTION md_get_salary (p_business_group_id IN NUMBER,
5269 p_assignment_id IN NUMBER,
5270 p_report_date IN DATE,
5271 p_frequency OUT nocopy varchar2
5272 ) return NUMBER
5273 --
5274 AS
5275 --
5276 l_effective_start_date date;
5277 l_pay_basis varchar2(60);
5278 l_salary number;
5279 l_normal_hours number;
5280 l_work_schedule varchar2(150);
5281 l_annual_salary number;
5282 l_payroll_id per_all_assignments_f.payroll_id%type;
5283 l_number_per_fiscal_year number;
5284 --
5285 Begin
5286 --
5287 hr_utility.set_location('md_get_salary',5);
5288 hr_utility.trace('p_business_group_id ->'||to_char(p_business_group_id));
5289 hr_utility.trace('p_assignment_id ->'||to_char(p_assignment_id));
5290 hr_utility.trace('p_report_date ->'||p_report_date );
5291 --
5292 Begin
5293 --
5294 Select
5295 peev.effective_start_date,
5296 hl.meaning,
5297 asg.normal_hours,
5298 hscf.segment4,
5299 peev.screen_entry_value,
5300 asg.payroll_id
5301 Into
5302 l_effective_start_date,
5303 l_pay_basis,
5304 l_normal_hours,
5305 l_work_schedule,
5306 l_salary,
5307 l_payroll_id
5308 From
5309 pay_element_entry_values_f peev,
5310 pay_element_entries_f pee,
5311 per_pay_bases ppb,
5312 hr_soft_coding_keyflex hscf,
5313 per_assignments_f asg,
5314 hr_lookups hl
5315 Where
5316 hl.application_id = 800
5317 And hl.lookup_type = 'PAY_BASIS'
5318 And hl.lookup_code = ppb.pay_basis
5319 And peev.element_entry_id = pee.element_entry_id
5320 And peev.effective_start_date = pee.effective_start_date
5321 And peev.input_value_id+0 = ppb.input_value_id
5322 And asg.pay_basis_id = ppb.pay_basis_id
5323 And pee.assignment_id = asg.assignment_id
5324 And hscf.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
5325 And asg.assignment_id = p_assignment_id
5326 And asg.business_group_id = p_business_group_id
5327 And pee.effective_start_date between asg.effective_start_date
5328 and asg.effective_end_date
5329 And p_report_date between pee.effective_start_date
5330 and pee.effective_end_date;
5331 --
5332 hr_utility.trace('l_effective_start_date ->'||l_effective_start_date);
5333 hr_utility.trace('l_pay_basis ->'||l_pay_basis);
5334 hr_utility.trace('l_normal_hours ->'||to_char(l_normal_hours));
5335 hr_utility.trace('l_work_schedule ->'||l_work_schedule);
5336 hr_utility.trace('l_salary ->'||to_char(l_salary));
5337 --
5338 exception
5339 when NO_DATA_FOUND then RETURN NULL;
5340 when others then
5341 hr_utility.set_location('md_get_salary',10);
5342 RETURN NULL;
5343 --
5344 end;
5345 --
5346 l_annual_salary := hr_us_ff_udfs.Convert_Period_Type
5347 (p_business_group_id,null,l_work_schedule,l_normal_hours,l_salary,l_pay_basis,'Year',
5348 null,null,null);
5349
5350 if (l_payroll_id is NULL) or (l_pay_basis = 'MONTHLY') then
5351 select decode(ppb.pay_basis,'MONTHLY','M',
5352 'ANNUAL','Y',
5353 'HOURLY','H',
5354 'PERIOD','P'
5355 )
5356 into p_frequency
5357 from per_pay_bases ppb ,
5358 per_all_assignments_f paf
5359 where paf.assignment_id = p_assignment_id
5360 and ppb.pay_basis_id = paf.pay_basis_id
5361 and ppb.BUSINESS_GROUP_ID=p_business_group_id ;
5362
5363 if l_pay_basis = 'PERIOD' then
5364 p_frequency:='Y';
5365 return l_annual_salary;
5366 end if;
5367
5368 return l_salary ;
5369
5370 else
5371 --
5372 hr_utility.set_location('md_get_salary',15);
5373 hr_utility.trace('l_annual_salary ->'||to_char(l_annual_salary));
5374 --
5375 SELECT ptp.number_per_fiscal_year,
5376 decode(ptp.number_per_fiscal_year
5377 ,26 ,'B'
5378 ,12 ,'M'
5379 ,24 ,'S'
5380 ,52 ,'W'
5381 ,1 ,'Y'
5382 ,'R')
5383 INTO l_number_per_fiscal_year ,
5384 p_frequency
5385 FROM pay_payrolls_f prl,
5386 per_time_period_types ptp,
5387 per_all_assignments_f paf
5388 WHERE ptp.period_type = prl.period_type
5389 and paf.assignment_id = p_assignment_id
5390 and prl.payroll_id = paf.payroll_id
5391 and prl.business_group_id + 0 = p_business_group_id
5392 and p_report_date between paf.effective_start_date
5393 and paf.effective_end_date
5394 and p_report_date between prl.effective_start_date
5395 and prl.effective_end_date;
5396
5397 if p_frequency = 'R' then
5398 p_frequency:='Y';
5399 return l_annual_salary;
5400 end if;
5401
5402 return (round((l_annual_salary/l_number_per_fiscal_year),2));
5403 end if;
5404 --
5405 end md_get_salary;
5406 --#10387522 end
5407 --
5408 end per_new_hire_pkg;