[Home] [Help]
PACKAGE BODY: APPS.PER_NEW_HIRE_PKG
Source
1 package body per_new_hire_pkg as
2 /* $Header: pernhire.pkb 120.7.12010000.4 2008/08/06 09:34:49 ubhat 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 ========================================================================
58 REM
59 */
60 --
61 -- Global variables and Constants.
62 --
63 g_character_set varchar2(80);
64 g_package_name constant varchar2(30) := 'per_new_hire_pkg';
65
66 g_e4_code varchar2(2) := 'E4';
67 g_w4_code varchar2(2) := 'W4';
68 g_t4_code varchar2(2) := 'T4';
69 g_eol varchar2(1) := fnd_global.local_chr(10) ;
70 g_delimiter varchar2(1) := ',';
71
72 /****************************************************************
73 * This procedure sets character_set to out put file *
74 * *
75 * *
76 ****************************************************************/
77 procedure char_set_init
78 (
79 p_character_set in varchar2
80 )
81 is
82 l_api_name varchar2(61) := g_package_name || '.char_set_init';
83 l_package_name varchar2(30) := g_package_name;
84 begin
85 --
86 --hr_utility.trace_on(null,'NEWHIRE');
87 hr_utility.set_location('Entering:' ||l_api_name,10);
88 --
89 -- Check mandatory argument.
90 --
91 hr_api.mandatory_arg_error(
92 p_api_name => l_api_name,
93 p_argument => 'p_character_set',
94 p_argument_value => p_character_set);
95 --
96 -- Initialize global variables.
97 --
98 g_character_set := p_character_set;
99 --
100 hr_utility.set_location(l_api_name,20);
101 --
102 hr_utility.set_location('Leaving.... :' || l_api_name,50);
103 end char_set_init;
104
105 /****************************************************************
106 * This procedure formats California E4 record *
107 * *
108 * *
109 ****************************************************************/
110 function ca_e4_record
111 (
112 p_record_identifier in varchar2
113 ,p_federal_id in varchar2
114 ,p_sit_company_state_id in varchar2
115 ,p_branch_code in varchar2
116 ,p_tax_unit_name in varchar2
117 ,p_street_address in varchar2
118 ,p_city in varchar2
119 ,p_state in varchar2
120 ,p_zip in varchar2
121 ,p_zip_extension in varchar2 default null
122 ) return varchar2
123 is
124 l_api_name varchar2(61) := g_package_name || '.ca_e4_record';
125 l_buffer varchar2(2000);
126 v_sit varchar2(40);
127 p_end_of_rec varchar2(2000);
128 begin
129
130 hr_utility.set_location('Entering:' ||l_api_name,10);
131
132 --
133 -- Check mandatory argument.
134 --
135 hr_api.mandatory_arg_error(
136 p_api_name => l_api_name,
137 p_argument => 'p_record_identifier',
138 p_argument_value => p_record_identifier);
139
140 hr_utility.set_location(l_api_name,11);
141 hr_utility.trace('p_federal_id= ' || p_federal_id);
142 hr_utility.trace('p_sit_company_state_id= ' || p_sit_company_state_id);
143
144 v_sit := replace(p_sit_company_state_id,'-',null);
145
146 hr_utility.trace('v_sit_company_state_id= ' || v_sit);
147
148 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
149 hr_utility.trace('p_street_address= ' || p_street_address);
150 hr_utility.trace('p_city= ' || p_city);
151 hr_utility.trace('p_state= ' || p_state);
152 hr_utility.trace('p_zip= ' || p_zip);
153 hr_utility.trace('p_zip_extension= ' || p_zip_extension);
154
155 hr_utility.set_location(l_api_name,30);
156
157 --
158 -- format new hire rocord
159 --
160
161 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
162
163 l_buffer := upper(p_record_identifier ||
164 rpad(p_federal_id,9,' ') ||
165 rpad(nvl(v_sit,'0'),8,'0') ||
166 rpad(nvl(p_branch_code,' '),3,' ') ||
167 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
168 rpad(p_street_address,40,' ') ||
169 rpad(p_city,25,' ') ||
170 rpad(p_state,2,' ') ||
171 rpad(p_zip,5,' ') ||
172 rpad(nvl(p_zip_extension,' '),4,' ') ||
173 lpad(' ',32,' '))||p_end_of_rec ;
174
175
176 hr_utility.set_location('Leaving.... :' || l_api_name,40);
177
178 return convert(l_buffer,g_character_set);
179 exception
180 when others then
181 hr_utility.set_location('Leaving.... :' || l_api_name,70);
182 -- fnd_message.set_name('PER','HR_ERROR');
183 -- fnd_message.set_token('SQL',g_ca_e4_sql);
184 -- fnd_message.raise_error;
185 raise;
186 end ca_e4_record;
187
188 /****************************************************************
189 * This procedure formats California W4 record *
190 * *
191 * *
192 ****************************************************************/
193 function ca_w4_record
194 (
195 p_record_identifier in varchar2
196 ,p_national_identifier in varchar2
197 ,p_first_name in varchar2
198 ,p_middle_name in varchar2
199 ,p_last_name in varchar2
200 ,p_street_address in varchar2
201 ,p_city in varchar2
202 ,p_state in varchar2
203 ,p_zip in varchar2
204 ,p_zip_extension in varchar2
205 ,p_date_of_hire in date
206 ) return varchar2
207 is
208 l_api_name varchar2(61) := g_package_name || '.ca_w4_record';
209 l_buffer varchar2(2000);
210 v_ssn varchar(9);
211 p_end_of_rec varchar2(2000);
212 begin
213 hr_utility.set_location('Entering:' ||l_api_name,10);
214
215 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
216 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
217
218 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
219
220 hr_utility.trace('v_ssn= ' || v_ssn);
221
222 hr_utility.trace('p_first_name= ' || p_first_name);
223
224 hr_utility.trace('p_middle_name= ' || p_middle_name);
225 hr_utility.trace('p_last_name= ' || p_last_name);
226
227 hr_utility.trace('p_street_address= ' || p_street_address);
228 hr_utility.trace('p_city= ' || p_city);
229 hr_utility.trace('p_state= ' || p_state);
230 hr_utility.trace('p_zip= ' || p_zip);
231 hr_utility.trace('p_zip_extension= ' || p_zip_extension);
232 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
233
234 --
235 -- format new hire rocord
236 --
237
238 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
239
240 l_buffer := upper(
241 p_record_identifier ||
242 rpad(nvl(v_ssn,' '),9,' ') || -- BUG4504074
243 rpad(nvl(p_first_name,' '),16,' ') ||
244 rpad(nvl(p_middle_name,' '),1,' ') ||
245 rpad(p_last_name,30,' ') ||
246 rpad(nvl(p_street_address,' '),40,' ') ||
247 rpad(nvl(p_city,' '),25,' ') ||
248 rpad(nvl(p_state,' '),2,' ') ||
249 rpad(nvl(p_zip,' '),5,' ') ||
250 rpad(nvl(p_zip_extension,' '),4,' ') ||
251 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
252 lpad(' ',33,' '))||p_end_of_rec;
253
254 hr_utility.trace('l_buffer = ' || l_buffer);
255
256
257 hr_utility.set_location('Leaving.... :' || l_api_name,60);
258
259 return convert(l_buffer,g_character_set);
260 exception
261 when others then
262 hr_utility.set_location('Leaving.... :' || l_api_name,70);
263 raise;
264 end ca_w4_record;
265 --
266 /****************************************************************
267 * This procedure formats California T4 record *
268 * *
269 * *
270 ****************************************************************/
271 FUNCTION ca_t4_record
272 (
273 p_record_identifier in varchar2
274 ,p_number_of_employee in number
275 ) return varchar2
276 IS
277 l_api_name varchar2(61) := g_package_name || '.ca_t4_record';
278 l_buffer varchar2(2000);
279 p_end_of_rec varchar2(2000);
280 begin
281 --
282 hr_utility.set_location('Entering:' ||l_api_name,10);
283
284 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
285 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
286
287 --
288 -- format new hire rocord
289 --
290
291 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
292
293 l_buffer := upper(
294 p_record_identifier ||
295 lpad(to_char(p_number_of_employee),11,'0') ||
296 lpad(' ',162,' '))||p_end_of_rec;
297
298 hr_utility.set_location(l_api_name,20);
299
300 return convert(l_buffer,g_character_set);
301 exception
302 when others then
303 hr_utility.set_location('Leaving.... :' || l_api_name,30);
304 raise;
305 end ca_t4_record;
306 --
307 /****************************************************************
308 * This procedure formats NEW YORK 1A record for Transmitter *
309 * *
310 * *
311 ****************************************************************/
312 function ny_1a_record
313 (
314 p_record_identifier in varchar2
315 ,p_creation_date in varchar2
316 ,p_federal_id in varchar2
317 ,p_tax_unit_name in varchar2
318 ,p_street_address in varchar2
319 ,p_city in varchar2
320 ,p_state in varchar2
321 ,p_zip in varchar2
322 ) return varchar2
323 is
324 l_api_name varchar2(61) := g_package_name || '.ny_1a_record';
325 l_buffer varchar2(2000);
326 p_end_of_rec varchar2(2000);
327 begin
328
329 hr_utility.set_location('Entering...:' ||l_api_name,10);
330
331 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
332 hr_utility.trace('p_federal_id= ' || p_federal_id);
336 hr_utility.trace('p_state= ' || p_state);
333 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
334 hr_utility.trace('p_street_address= ' || p_street_address);
335 hr_utility.trace('p_city= ' || p_city);
337 hr_utility.trace('p_zip= ' || p_zip);
338
339 hr_utility.set_location(l_api_name,30);
340
341 --
342 -- format new hire rocord
343 --
344
345 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
346
347 l_buffer := upper(p_record_identifier ||
348 p_creation_date ||
349 rpad(nvl(p_federal_id,' '),11,' ') ||
350 rpad(nvl(p_tax_unit_name,' '),40,' ') ||
351 rpad(nvl(p_street_address,' '),30,' ') ||
352 rpad(nvl(p_city,' '),18,' ') ||
353 rpad(nvl(p_state,' '),2,' ') ||
354 rpad(nvl(p_zip,' '),9,' ') ||
355 lpad(' ',10,' '))||p_end_of_rec;
356
357
358 hr_utility.set_location('Leaving.... :' || l_api_name,40);
359
360 return convert(l_buffer,g_character_set);
361 exception
362 when others then
363 hr_utility.set_location('Leaving.... :' || l_api_name,70);
364 raise;
365 end ny_1a_record;
366 --
367 /****************************************************************
368 * This procedure formats NEW YORK 1E record for Employer *
369 * *
370 * *
371 ****************************************************************/
372 function ny_1e_record
373 (
374 p_record_identifier in varchar2
375 ,p_federal_id in varchar2
376 ,p_tax_unit_name in varchar2
377 ,p_street_address in varchar2
378 ,p_city in varchar2
379 ,p_state in varchar2
380 ,p_zip in varchar2
381 ) return varchar2
382 is
383 l_api_name varchar2(61) := g_package_name || '.ny_1e_record';
384 l_buffer varchar2(2000);
385 p_end_of_rec varchar2(2000);
386 begin
387
388 hr_utility.set_location('Entering:' ||l_api_name,10);
389
390 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
391 hr_utility.trace('p_federal_id= ' || p_federal_id);
392 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
393 hr_utility.trace('p_street_address= ' || p_street_address);
394 hr_utility.trace('p_city= ' || p_city);
395 hr_utility.trace('p_state= ' || p_state);
396 hr_utility.trace('p_zip= ' || p_zip);
397
398 hr_utility.set_location(l_api_name,30);
399
400 --
401 -- format new hire rocord
402 --
403
404 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
405
406 l_buffer := upper(p_record_identifier ||
407 lpad(' ',4,' ') ||
408 rpad(nvl(p_federal_id,' '),11,' ') ||
409 ' ' || -- blank 1
410 rpad(nvl(p_tax_unit_name,' '),40,' ') ||
411 ' ' || -- blank 1
412 rpad(nvl(p_street_address,' '),30,' ') ||
413 rpad(nvl(p_city,' '),18,' ') ||
414 rpad(nvl(p_state,' '),2,' ') ||
415 rpad(nvl(p_zip,' '),9,' ') ||
416 lpad(' ',10,' '))||p_end_of_rec;
417
418
419 hr_utility.set_location('Leaving.... :' || l_api_name,40);
420
421 return convert(l_buffer,g_character_set);
422 exception
423 when others then
424 hr_utility.set_location('Leaving.... :' || l_api_name,70);
425 raise;
426 end ny_1e_record;
427 --
428 --
429 /****************************************************************
430 * This procedure formats NEW YORK 1H record for Employee *
431 * *
432 * *
433 ****************************************************************/
434 function ny_1h_record
435 (
436 p_record_identifier in varchar2
437 ,p_national_identifier in varchar2
438 ,p_first_name in varchar2
439 ,p_middle_name in varchar2
440 ,p_last_name in varchar2
441 ,p_street_address in varchar2
442 ,p_city in varchar2
443 ,p_state in varchar2
444 ,p_zip in varchar2
445 ,p_date_of_hire in date
446 ) return varchar2
447 is
448 l_api_name varchar2(61) := g_package_name || '.ny_1h_record';
449 l_buffer varchar2(2000);
450 v_ssn varchar(9);
451 p_end_of_rec varchar2(2000);
452 begin
453 hr_utility.set_location('Entering:' ||l_api_name,10);
454
455 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
456 hr_utility.trace('p_national_identifier= ' || p_national_identifier);
457
458 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
459
460 hr_utility.trace('v_ssn= ' || v_ssn);
461 hr_utility.trace('p_first_name= ' || p_first_name);
462 hr_utility.trace('p_middle_name= ' || p_middle_name);
463 hr_utility.trace('p_last_name= ' || p_last_name);
464
465 hr_utility.trace('p_street_address= ' || p_street_address);
469 hr_utility.trace('p_date_of_hire= ' || to_char(p_date_of_hire));
466 hr_utility.trace('p_city= ' || p_city);
467 hr_utility.trace('p_state= ' || p_state);
468 hr_utility.trace('p_zip= ' || p_zip);
470
471 --
472 -- format new hire rocord
473 --
474
475 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
476
477 l_buffer := upper(
478 p_record_identifier ||
479 rpad(nvl(v_ssn,' '),9,' ') || -- BUG4504074
480 rpad((p_last_name || ',' || nvl(p_first_name,'') || ' ' ||
481 nvl(substr(p_middle_name,1,1),'')),28,' ') ||
482 rpad(nvl(p_street_address,' '),30,' ') ||
483 rpad(nvl(p_city,' '),18,' ') ||
484 rpad(nvl(p_state,' '),2,' ') ||
485 rpad(nvl(p_zip,' '),5,' ') ||
486 ' ' || -- blank 1
487 rpad(nvl(to_char(p_date_of_hire,'MMDDRR'),' '),6,' ') ||
488 lpad(' ',27,' '))||p_end_of_rec;
489
490 hr_utility.trace('l_buffer = ' || l_buffer);
491
492
493 hr_utility.set_location('Leaving.... :' || l_api_name,60);
494
495 return convert(l_buffer,g_character_set);
496 exception
497 when others then
498 hr_utility.set_location('Leaving.... :' || l_api_name,70);
499 raise;
500 end ny_1h_record;
501 --
502 /****************************************************************
503 * This procedure formats NEW YORK 1T record *
504 * *
505 * *
506 ****************************************************************/
507 FUNCTION ny_1t_record
508 (
509 p_record_identifier in varchar2
510 ,p_number_of_employee in number
511 ) return varchar2
512 IS
513 l_api_name varchar2(61) := g_package_name || '.ny_1t_record';
514 l_buffer varchar2(2000);
515 p_end_of_rec varchar2(2000);
516 begin
517 --
518 hr_utility.set_location('Entering:' ||l_api_name,10);
519
520 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
521
522 --
523 -- format new hire rocord
524 --
525
526 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
527
528 l_buffer := upper(
529 p_record_identifier ||
530 lpad(to_char(p_number_of_employee),7,' ') ||
531 lpad(' ',119,' '))||p_end_of_rec;
532
533 hr_utility.set_location(l_api_name,20);
534
535 return convert(l_buffer,g_character_set);
536 exception
537 when others then
538 hr_utility.set_location('Leaving.... :' || l_api_name,30);
539 raise;
540 end ny_1t_record;
541 --
542 /****************************************************************
543 * This procedure formats NEW YORK 1F record *
544 * *
545 * *
546 ****************************************************************/
547 FUNCTION ny_1f_record
548 (
549 p_record_identifier in varchar2
550 ,p_number_of_employer in number
551 ) return varchar2
552 IS
553 l_api_name varchar2(61) := g_package_name || '.ny_1f_record';
554 l_buffer varchar2(2000);
555 p_end_of_rec varchar2(2000);
556 begin
557 --
558 hr_utility.set_location('Entering:' ||l_api_name,10);
559
560 hr_utility.trace('p_number_of_emplyer = ' || p_number_of_employer);
561
562 --
563 -- format new hire rocord
564 --
565
566 p_end_of_rec := fnd_global.local_chr(13)||fnd_global.local_chr(10);
567
568 l_buffer := upper(
569 p_record_identifier ||
570 lpad(to_char(p_number_of_employer),7,' ') ||
571 lpad(' ',119,' '))||p_end_of_rec;
572
573 hr_utility.set_location(l_api_name,20);
574
575 return convert(l_buffer,g_character_set);
576 exception
577 when others then
578 hr_utility.set_location('Leaving.... :' || l_api_name,30);
579 raise;
580 end ny_1f_record;
581 --
582 /****************************************************************
583 * This procedure formats FLORIDA New Hire record *
584 * *
585 * *
586 ****************************************************************/
587 function fl_new_hire_record
588 (
589 p_record_identifier in varchar2
590 ,p_first_name in varchar2
591 ,p_middle_name in varchar2
592 ,p_last_name in varchar2
593 ,p_national_identifier in varchar2
594 ,p_emp_address_line1 in varchar2
595 ,p_emp_address_line2 in varchar2
596 ,p_emp_address_line3 in varchar2
597 ,p_emp_city in varchar2
598 ,p_emp_state in varchar2
599 ,p_emp_zip in varchar2
600 ,p_emp_zip_extension in varchar2
601 ,p_emp_country_code in varchar2
602 ,p_date_of_birth in date
603 ,p_date_of_hire in date
604 ,p_state_of_hire in varchar2
605 ,p_federal_id in varchar2
606 ,p_sit_company_state_id in varchar2
607 ,p_tax_unit_name in varchar2
608 ,p_loc_address_line1 in varchar2
609 ,p_loc_address_line2 in varchar2
610 ,p_loc_address_line3 in varchar2
611 ,p_loc_city in varchar2
612 ,p_loc_state in varchar2
613 ,p_loc_zip in varchar2
614 ,p_loc_zip_extension in varchar2
615 ,p_loc_country_code in varchar2
616 ,p_loc_phone in varchar2
617 ,p_loc_phone_extension in varchar2
618 ,p_loc_contact in varchar2
619 ,p_opt_address_line1 in varchar2
620 ,p_opt_address_line2 in varchar2
621 ,p_opt_address_line3 in varchar2
622 ,p_opt_city in varchar2
623 ,p_opt_state in varchar2
624 ,p_opt_zip in varchar2
625 ,p_opt_zip_extension in varchar2
626 ,p_opt_country_code in varchar2
627 ,p_opt_phone in varchar2
628 ,p_opt_phone_extension in varchar2
629 ,p_opt_contact in varchar2
630 ,p_multi_state in varchar2
631 ) return varchar2
632 is
633 l_api_name varchar2(61) := g_package_name || '.fl_new_hire_record';
634 l_buffer varchar2(2000);
635 v_ssn varchar(9);
636 l_phone varchar(10);
637 l_opt_phone varchar(10);
638 begin
639
640 hr_utility.set_location('Entering...: ' || l_api_name,10);
641
642 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
643
644 hr_utility.trace('p_first_name= ' || p_first_name);
645 hr_utility.trace('p_middle_name= ' || p_middle_name);
646 hr_utility.trace('p_last_name= ' || p_last_name);
647
648 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
649
650 hr_utility.trace('v_ssn= ' || v_ssn);
651 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
652 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
653 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line3);
654 hr_utility.trace('p_emp_city= ' || p_emp_city);
655 hr_utility.trace('p_emp_state= ' || p_emp_state);
656 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
657 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
658 hr_utility.trace('p_emp_country_code= ' || p_emp_country_code);
659 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
660 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
661
662 hr_utility.trace('p_federal_id= ' || p_federal_id);
663 hr_utility.trace('p_sit_company_state_id=' || p_sit_company_state_id);
664 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
665 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
666 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
667 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
668 hr_utility.trace('p_loc_city= ' || p_loc_city);
669 hr_utility.trace('p_loc_state= ' || p_loc_state);
670 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
671 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
672 hr_utility.trace('p_loc_country_code= ' || p_loc_country_code);
673 hr_utility.trace('p_loc_phone= ' || p_loc_phone);
674
675 l_phone := replace(replace(replace(replace(replace(nvl(p_loc_phone,' '),'(',null),'-',null),'.',null),')',null),' ',null);
676
677 hr_utility.trace('l_phone= ' || l_phone);
678 hr_utility.trace('p_loc_contact= ' || p_loc_contact);
679
680 l_opt_phone := replace(replace(replace(replace(replace(nvl(p_opt_phone,' '),'(',null),'-',null),'.',null),')',null),' ',null);
681
682 hr_utility.trace('l_opt_phone= ' || l_opt_phone);
683 hr_utility.trace('p_multi_state= ' || p_multi_state);
684 hr_utility.set_location(l_api_name,30);
685
686 --
687 -- format new hire rocord
688 --
689 l_buffer := p_record_identifier || -- 'FL Newhire Record'
690 '1.00' ||
691 rpad(nvl(p_first_name,' '),16,' ') ||
692 rpad(nvl(p_middle_name,' '),16,' ') ||
693 rpad(nvl(p_last_name,' '),30,' ') ||
694 rpad(nvl(v_ssn,' '),9,' ') ||
695 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
696 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
697 rpad(nvl(p_emp_address_line3,' '),40,' ') ||
698 rpad(nvl(p_emp_city,' '),25,' ') ||
699 rpad(nvl(p_emp_state,' '),2,' ') ||
700 rpad(nvl(p_emp_zip,' '),20,' ') ||
701 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
702 rpad(nvl(p_emp_country_code,' '),2,' ') ||
703 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') ||
704 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') ||
705 ' ' || -- rpad(nvl(p_state_of_hire,' '),2,' ') ||
706 ' ' || -- filler blank 2
707 rpad(nvl(p_federal_id,' '),9,' ') ||
708 rpad(nvl(p_sit_company_state_id,' '),12,' ') ||
709 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
710 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
711 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
712 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
713 rpad(nvl(p_loc_city,' '),25,' ') ||
717 rpad(nvl(p_loc_country_code,' '),2,' ') ||
714 rpad(nvl(p_loc_state,' '),2,' ') ||
715 rpad(nvl(p_loc_zip,' '),20,' ') ||
716 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
718 rpad(nvl(l_phone,' '),10,' ') ||
719 rpad(nvl(p_loc_phone_extension,' '),6,' ') ||
720 rpad(nvl(p_loc_contact,' '),20,' ') ||
721 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
722 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
723 rpad(nvl(p_opt_address_line3,' '),40,' ') ||
724 rpad(nvl(p_opt_city,' '),25,' ') ||
725 rpad(nvl(p_opt_state,' '),2,' ') ||
726 rpad(nvl(p_opt_zip,' '),20,' ') ||
727 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
728 rpad(nvl(p_opt_country_code,' '),2,' ') ||
729 rpad(nvl(l_opt_phone,' '),10,' ') ||
730 rpad(nvl(p_opt_phone_extension,' '),6,' ') ||
731 rpad(nvl(p_opt_contact,' '),20,' ') ||
732 ' ' || -- filler blank 1
733 rpad(nvl(p_multi_state,' '),1,' ') ||
734 rpad(' ',30,' '); -- filler blank 30
735
736
737 hr_utility.set_location('Leaving.... :' || l_api_name,40);
738
739 return convert(l_buffer,g_character_set);
740 exception
741 when others then
742 hr_utility.set_location('Leaving.... with ERROR :' || l_api_name,200);
743 raise;
744 end fl_new_hire_record;
745 --
746 /****************************************************************
747 * This procedure formats ILLINOIS New Hire record *
748 * *
749 * *
750 ****************************************************************/
751 function il_new_hire_record
752 (
753 p_record_identifier in varchar2
754 ,p_national_identifier in varchar2
755 ,p_first_name in varchar2
756 ,p_middle_name in varchar2
757 ,p_last_name in varchar2
758 ,p_emp_address_line1 in varchar2
759 ,p_emp_address_line2 in varchar2
760 ,p_emp_city in varchar2
761 ,p_emp_state in varchar2
762 ,p_emp_zip in varchar2
763 ,p_emp_zip_extension in varchar2
764 ,p_date_of_hire in date
765 ,p_federal_id in varchar2
766 ,p_tax_unit_name in varchar2
767 ,p_loc_address_line1 in varchar2
768 ,p_loc_address_line2 in varchar2
769 ,p_loc_city in varchar2
770 ,p_loc_state in varchar2
771 ,p_loc_zip in varchar2
772 ,p_loc_zip_extension in varchar2
773 ,p_opt_address_line1 in varchar2
774 ,p_opt_address_line2 in varchar2
775 ,p_opt_city in varchar2
776 ,p_opt_state in varchar2
777 ,p_opt_zip in varchar2
778 ,p_opt_zip_extension in varchar2
779 ) return varchar2
780 is
781 l_api_name varchar2(61) := g_package_name || '.il_new_hire_record';
782 l_buffer varchar2(2000);
783 v_ssn varchar(9);
784 begin
785
786 hr_utility.set_location('Entering...: ' ||l_api_name,10);
787
788 hr_utility.trace('p_record_identifier= ' ||p_record_identifier);
789 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
790
791 hr_utility.trace('v_ssn= ' ||v_ssn);
792
793 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
794 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
795 hr_utility.trace('p_emp_city= ' || p_emp_city);
796 hr_utility.trace('p_emp_state= ' || p_emp_state);
797 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
798 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
799 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
800
801 hr_utility.trace('p_federal_id= ' || p_federal_id);
802 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
803 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
804 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
805 hr_utility.trace('p_loc_city= ' || p_loc_city);
806 hr_utility.trace('p_loc_state= ' || p_loc_state);
807 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
808 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
809
810 hr_utility.set_location(l_api_name,30);
811 --
812 -- format new hire rocord
813 --
814 l_buffer := p_record_identifier || -- 'W4'
815 rpad(nvl(v_ssn,' '),9,' ') ||
816 rpad(nvl(p_first_name,' '),16,' ') ||
817 rpad(nvl(p_middle_name,' '),16,' ') ||
818 rpad(nvl(p_last_name,' '),30,' ') ||
819 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
820 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
821 rpad(' ',40,' ') ||
822 rpad(nvl(p_emp_city,' '),25,' ') ||
823 rpad(nvl(p_emp_state,' '),2,' ') ||
824 rpad(nvl(p_emp_zip,' '),5,' ') ||
825 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
826 rpad(' ',42,' ') ||
827 rpad(' ',8,' ') ||
828 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
829 ' ' || -- 2 blank
830 rpad(nvl(p_federal_id,' '),9,' ') ||
831 rpad(' ',12,' ') ||
832 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
833 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
834 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
835 rpad(' ',40,' ') ||
836 rpad(nvl(p_loc_city,' '),25,' ') ||
837 rpad(nvl(p_loc_state,' '),2,' ') ||
838 rpad(nvl(p_loc_zip,' '),5,' ') ||
839 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
840 rpad(' ',42,' ') ||
841 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
842 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
843 rpad(' ',40,' ') ||
844 rpad(nvl(p_opt_city,' '),25,' ') ||
845 rpad(nvl(p_opt_state,' '),2,' ') ||
846 rpad(nvl(p_opt_zip,' '),5,' ') ||
847 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
848 rpad(' ',42,' ') ||
849 rpad(' ',50,' ') ;
850
851
852
853 hr_utility.set_location('Leaving.... :' || l_api_name,100);
854
855 return convert(l_buffer,g_character_set);
856 exception
857 when others then
858 hr_utility.set_location('Leaving.... :' || l_api_name,200);
859 raise;
860 end il_new_hire_record;
861
862 -- sjawid al
863
864 function al_new_hire_record
865 (
866 p_national_identifier in varchar2
867 ,p_dir_acc_number in varchar2
868 ,p_date_of_hire in date
869 ,p_indicator in varchar2
870 ,p_first_name in varchar2
871 ,p_middle_name in varchar2
872 ,p_last_name in varchar2
873 ,p_emp_address_line1 in varchar2
874 ,p_emp_address_line2 in varchar2
875 ,p_emp_address_line3 in varchar2
876 ,p_emp_city in varchar2
877 ,p_emp_state in varchar2
878 ,p_emp_zip in varchar2
879 ,p_emp_zip_extension in varchar2
880 ,p_federal_id in varchar2
881 ,p_tax_unit_name in varchar2
882 ,p_loc_address_line1 in varchar2
883 ,p_loc_address_line2 in varchar2
884 ,p_loc_address_line3 in varchar2
885 ,p_loc_city in varchar2
886 ,p_loc_state in varchar2
887 ,p_loc_zip in varchar2
888 ,p_blanks in varchar2
889 ) return varchar2
890 is
891 l_api_name varchar2(61) := g_package_name || '.al_new_hire_record';
892 l_buffer varchar2(2000);
893 v_ssn varchar(9);
894 function format_text(p_text in varchar2,p_length in number) return varchar2
895 is
896 begin
897 return (rpad(nvl(p_text,' '),p_length,' ') );
898 end format_text;
899 begin
900 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
901
902 l_buffer := format_text(v_ssn,9)|| --Social Security Number
903 -- format_text(p_dir_acc_number,10)||
904 lpad(nvl(p_dir_acc_number,' '),10,'0')|| --Account Number**
905 format_text(to_char(p_date_of_hire,'MMDDYY'),6)|| --Activity Date
906 format_text(p_indicator,'1')|| --Indicator
907 format_text(p_last_name||'/'||p_first_name||'/'||p_middle_name||'/'||substr(p_middle_name,1,1),27)|| --Employee's Name
908 format_text(p_emp_address_line1||' '||p_emp_address_line2||' '||p_emp_address_line3,30)|| --Employee's Street Address
909 format_text(p_emp_city,20)|| --Employee's City Name
910 format_text(p_emp_state,2)|| --Employee's State Name
911 format_text(p_emp_zip||p_emp_zip_extension,9)||--Employee's ZIP + 4 ZIP Code
912 format_text(p_federal_id,9)|| --Employer's FEIN
913 format_text(p_tax_unit_name,20)|| --Employer's name
914 format_text(p_loc_address_line1||' '||p_loc_address_line2||' '||p_loc_address_line3,14)|| --employers address
915 format_text(p_loc_city,11)|| --employers city
916 format_text(p_loc_state,2)|| --employers state
917 format_text(p_loc_zip,5)|| --employers zip
918 format_text(' ',25);
919
920 return l_buffer;
921 end al_new_hire_record;
922 -- sjawid end al
923 --
924 /****************************************************************
925 * This procedure formats Texas T4 record *
926 * *
927 * *
928 ****************************************************************/
929 FUNCTION tx_t4_record
930 (
931 p_record_identifier in varchar2
932 ,p_number_of_employee in number
933 ) return varchar2
934 IS
935 l_api_name varchar2(61) := g_package_name || '.tx_t4_record';
936 l_buffer varchar2(2000);
937 begin
938 --
939 hr_utility.set_location('Entering:' ||l_api_name,10);
940
941 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
942 hr_utility.trace('p_number_of_emplyee = ' || p_number_of_employee);
943
944 --
945 -- format new hire rocord
946 --
947 l_buffer := upper(
948 p_record_identifier ||
949 lpad(to_char(p_number_of_employee),11,'0') ||
950 lpad(' ',788,' '));
951
952 hr_utility.set_location(l_api_name,20);
953
954 return convert(l_buffer,g_character_set);
955 exception
956 when others then
957 hr_utility.set_location('Leaving.... :' || l_api_name,30);
958 raise;
959 end tx_t4_record;
960 --
961 /****************************************************************
962 * This procedure formats TEXAS New Hire record *
963 * *
964 * *
965 ****************************************************************/
966 function tx_new_hire_record
967 (
968 p_record_identifier in varchar2
969 ,p_national_identifier in varchar2
970 ,p_first_name in varchar2
971 ,p_middle_name in varchar2
972 ,p_last_name in varchar2
973 ,p_emp_address_line1 in varchar2
974 ,p_emp_address_line2 in varchar2
975 ,p_emp_city in varchar2
976 ,p_emp_state in varchar2
977 ,p_emp_zip in varchar2
978 ,p_emp_zip_extension in varchar2
979 ,p_emp_country_code in varchar2
980 ,p_emp_country_name in varchar2
981 ,p_emp_country_zip in varchar2
982 ,p_date_of_birth in date
983 ,p_date_of_hire in date
984 ,p_state_of_hire in varchar2
985 ,p_federal_id in varchar2
986 ,p_state_ein in varchar2
987 ,p_tax_unit_name in varchar2
988 ,p_loc_address_line1 in varchar2
989 ,p_loc_address_line2 in varchar2
990 ,p_loc_address_line3 in varchar2
991 ,p_loc_city in varchar2
992 ,p_loc_state in varchar2
993 ,p_loc_zip in varchar2
994 ,p_loc_zip_extension in varchar2
995 ,p_loc_country_code in varchar2
996 ,p_loc_country_name in varchar2
997 ,p_loc_country_zip in varchar2
998 ,p_opt_address_line1 in varchar2
999 ,p_opt_address_line2 in varchar2
1000 ,p_opt_address_line3 in varchar2
1001 ,p_opt_city in varchar2
1002 ,p_opt_state in varchar2
1003 ,p_opt_zip in varchar2
1004 ,p_opt_zip_extension in varchar2
1005 ,p_opt_country_code in varchar2
1006 ,p_opt_country_name in varchar2
1007 ,p_opt_country_zip in varchar2
1008 ,p_salary in varchar2
1009 ,p_frequency in varchar2
1010 ) return varchar2
1011 is
1012 l_api_name varchar2(61) := g_package_name || '.tx_new_hire_record';
1013 l_buffer varchar2(2000);
1014 v_ssn varchar(9);
1015 begin
1016
1017 hr_utility.set_location('Entering...: ' || l_api_name,10);
1018
1019 hr_utility.trace('p_record_identifier= ' || p_record_identifier);
1020 hr_utility.trace('p_federal_id= ' || p_federal_id);
1021 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1022 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1023
1024 hr_utility.trace('v_ssn= ' || v_ssn);
1025 hr_utility.trace('p_first_name = ' || p_first_name);
1026 hr_utility.trace('p_middle_name = ' || p_middle_name);
1027 hr_utility.trace('p_last_name = ' || p_last_name);
1028 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1029 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1030 hr_utility.trace('p_emp_city= ' || p_emp_city);
1031 hr_utility.trace('p_emp_state= ' || p_emp_state);
1032 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1033 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1034 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1035 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1036 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
1037
1038 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1039 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1040 hr_utility.trace('p_loc_address_line3= ' || p_loc_address_line3);
1041 hr_utility.trace('p_loc_city= ' || p_loc_city);
1042 hr_utility.trace('p_loc_state= ' || p_loc_state);
1043 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1044 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1045
1046 hr_utility.trace('p_salary= ' || p_salary);
1047 hr_utility.trace('p_frequency= ' || p_frequency);
1048
1049 hr_utility.set_location(l_api_name,30);
1050
1051 --
1052 -- format new hire rocord
1053 --
1054 l_buffer := upper(
1055 p_record_identifier || -- 'W4'
1056 rpad(nvl(v_ssn,' '),9,' ') || -- BUG4504074
1057 rpad(nvl(p_first_name,' '),16,' ') ||
1058 rpad(nvl(p_middle_name,' '),16,' ') ||
1059 rpad(nvl(p_last_name,' '),30,' ') ||
1060 rpad(nvl(p_emp_address_line1,' '),40,' ') ||
1061 rpad(nvl(p_emp_address_line2,' '),40,' ') ||
1062 rpad(' ',40,' ') ||
1063 rpad(nvl(p_emp_city,' '),25,' ') ||
1064 rpad(nvl(p_emp_state,' '),2,' ') ||
1065 rpad(nvl(p_emp_zip,' '),5,' ') ||
1066 rpad(nvl(p_emp_zip_extension,' '),4,' ') ||
1067 rpad(nvl(p_emp_country_code,' '),2,' ') ||
1068 rpad(nvl(p_emp_country_name,' '),25,' ') ||
1069 rpad(nvl(p_emp_country_zip,' '),15,' ') ||
1070 nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || -- BUG3954955
1071 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || -- BUG3954955
1072 rpad(nvl(p_state_of_hire,' '),2,' ') ||
1073 rpad(nvl(p_federal_id,' '),9,' ') ||
1074 rpad(nvl(p_state_ein,' '),12,' ') ||
1075 rpad(nvl(p_tax_unit_name,' '),45,' ') ||
1076 rpad(nvl(p_loc_address_line1,' '),40,' ') ||
1077 rpad(nvl(p_loc_address_line2,' '),40,' ') ||
1078 rpad(nvl(p_loc_address_line3,' '),40,' ') ||
1079 rpad(nvl(p_loc_city,' '),25,' ') ||
1080 rpad(nvl(p_loc_state,' '),2,' ') ||
1081 rpad(nvl(p_loc_zip,' '),5,' ') ||
1082 rpad(nvl(p_loc_zip_extension,' '),4,' ') ||
1083 rpad(nvl(p_loc_country_code,' '),2,' ') ||
1084 rpad(nvl(p_loc_country_name,' '),25,' ') ||
1085 rpad(nvl(p_loc_country_zip,' '),15,' ') ||
1086 rpad(nvl(p_opt_address_line1,' '),40,' ') ||
1087 rpad(nvl(p_opt_address_line2,' '),40,' ') ||
1088 rpad(nvl(p_opt_address_line3,' '),40,' ') ||
1089 rpad(nvl(p_opt_city,' '),25,' ') ||
1090 rpad(nvl(p_opt_state,' '),2,' ') ||
1091 rpad(nvl(p_opt_zip,' '),5,' ') ||
1092 rpad(nvl(p_opt_zip_extension,' '),4,' ') ||
1093 rpad(nvl(p_opt_country_code,' '),2,' ') ||
1094 rpad(nvl(p_opt_country_name,' '),25,' ') ||
1095 rpad(nvl(p_opt_country_zip,' '),15,' ') ||
1096 rpad(' ',10,' ') ||
1097 rpad(' ',9) || -- rpad(nvl(p_salary,' '),9,'0') ||
1098 ' ' || -- rpad(nvl(p_frequency,' '),1,' ') ||
1099 rpad(' ',30,' ')) ;
1100
1101
1102 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1103
1104
1105 return convert(l_buffer,g_character_set);
1106 exception
1107 when others then
1108 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1109 raise;
1110 end tx_new_hire_record;
1111 --
1112 /****************************************************************
1113 * This procedure formats a03 FL new hire audit report *
1114 * *
1115 * *
1116 ****************************************************************/
1117 function a03_fl_new_hire_header
1118 return varchar2
1119 is
1120 l_api_name varchar2(61) := g_package_name || '.a03_fl_new_hire_header';
1121 l_buffer varchar2(2000);
1122 begin
1123 --
1124 hr_utility.set_location('Entering...: ' || l_api_name,10);
1125 l_buffer := 'First Name' || g_delimiter ||
1126 'Middle Name' || g_delimiter ||
1127 'Last Name' || g_delimiter ||
1128 'Social Security Number' || g_delimiter ||
1129 'Employee Address Line 1' || g_delimiter ||
1130 'Employee Address Line 2' || g_delimiter ||
1131 'Employee Address Line 3' || g_delimiter ||
1132 'Employee City' || g_delimiter ||
1133 'Employee State' || g_delimiter ||
1134 'Employee Zip' || g_delimiter ||
1135 'Employee Zip Extension' || g_delimiter ||
1136 'Employee Country Code' || g_delimiter ||
1137 'Date of Birth' || g_delimiter ||
1138 'Date of Hire' || g_delimiter ||
1139 'State of Hire' || g_delimiter ||
1140 'FEIN' || g_delimiter ||
1141 'State SUI' || g_delimiter ||
1142 'Employer Name' || g_delimiter ||
1143 'Employer Address Line 1' || g_delimiter ||
1144 'Employer Address Line 2' || g_delimiter ||
1145 'Employer Address Line 3' || g_delimiter ||
1146 'Employer City' || g_delimiter ||
1147 'Employer State' || g_delimiter ||
1148 'Employer Zip' || g_delimiter ||
1149 'Employer Zip Extension' || g_delimiter ||
1150 'Employer Country Code' || g_delimiter ||
1151 'Employer Phone' || g_delimiter ||
1152 'Employer Contact' || g_delimiter ||
1153 'Multi-State Indicator' ||
1154 g_eol;
1155
1156 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1157
1158 return l_buffer;
1159 exception
1160 when others then
1161 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1162 raise;
1163 end a03_fl_new_hire_header;
1164 --
1165 /****************************************************************
1166 * This procedure formats a03 Florida new hire audit report *
1167 * *
1168 * *
1169 ****************************************************************/
1170 function a03_fl_new_hire_record
1171 (
1172 p_national_identifier in varchar2
1173 ,p_first_name in varchar2
1174 ,p_middle_name in varchar2
1175 ,p_last_name in varchar2
1176 ,p_emp_address_line1 in varchar2
1177 ,p_emp_address_line2 in varchar2
1178 ,p_emp_address_line3 in varchar2
1179 ,p_emp_city in varchar2
1180 ,p_emp_state in varchar2
1181 ,p_emp_zip in varchar2
1182 ,p_emp_zip_extension in varchar2
1183 ,p_emp_country_code in varchar2
1184 ,p_date_of_birth in date
1185 ,p_date_of_hire in date
1186 ,p_state_of_hire in varchar2
1187 ,p_federal_id in varchar2
1188 ,p_state_ein in varchar2
1189 ,p_tax_unit_name in varchar2
1190 ,p_loc_address_line1 in varchar2
1191 ,p_loc_address_line2 in varchar2
1192 ,p_loc_address_line3 in varchar2
1193 ,p_loc_city in varchar2
1194 ,p_loc_state in varchar2
1195 ,p_loc_zip in varchar2
1196 ,p_loc_zip_extension in varchar2
1197 ,p_loc_country_code in varchar2
1198 ,p_contact_phone in varchar2
1199 ,p_contact_phone_ext in varchar2
1200 ,p_contact_name in varchar2
1201 ,p_multi_state in varchar2
1202 ) return varchar2
1203 is
1204 l_api_name varchar2(61) := g_package_name || '.a03_fl_new_hire_record';
1205 l_buffer varchar2(2000);
1206 v_ssn varchar(9);
1207 begin
1208
1209 hr_utility.set_location('Entering...: ' || l_api_name,10);
1210
1211 hr_utility.trace('p_federal_id= ' || p_federal_id);
1212 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1213 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1214
1215 hr_utility.trace('v_ssn= ' || v_ssn);
1216 hr_utility.trace('p_first_name= ' || p_first_name);
1217 hr_utility.trace('p_middle_name= ' || p_middle_name);
1218 hr_utility.trace('p_last_name= ' || p_last_name);
1219 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1220 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1221 hr_utility.trace('p_emp_city= ' || p_emp_city);
1222 hr_utility.trace('p_emp_state= ' || p_emp_state);
1223 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1224 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1225 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1226 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1227
1228 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1229 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1230 hr_utility.trace('p_loc_city= ' || p_loc_city);
1231 hr_utility.trace('p_loc_state= ' || p_loc_state);
1232 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1233 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1234 hr_utility.trace('p_contact_phone = ' || p_contact_phone);
1235 hr_utility.trace('p_contact_name = ' || p_contact_name);
1236
1237
1238 hr_utility.set_location(l_api_name,30);
1239
1240 --
1241 -- format new hire rocord
1242 --
1243 l_buffer := replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
1244 replace(nvl(p_middle_name,''),',',' ') || g_delimiter ||
1245 replace(p_last_name,',',' ') || g_delimiter ||
1246 v_ssn || g_delimiter ||
1247 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
1248 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
1249 replace(nvl(p_emp_address_line3,' '),',',' ') || g_delimiter ||
1250 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
1251 nvl(p_emp_state,' ') || g_delimiter ||
1252 nvl(p_emp_zip,' ') || g_delimiter ||
1253 nvl(p_emp_zip_extension,' ') || g_delimiter ||
1254 nvl(p_emp_country_code,' ') || g_delimiter ||
1255 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
1256 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
1257 nvl(p_state_of_hire,' ') || g_delimiter ||
1258 nvl(p_federal_id,' ') || g_delimiter ||
1259 nvl(p_state_ein,' ') || g_delimiter ||
1260 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
1261 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
1262 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
1263 replace(nvl(p_loc_address_line3,' '),',',' ') || g_delimiter ||
1264 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
1265 nvl(p_loc_state,' ') || g_delimiter ||
1266 nvl(p_loc_zip,' ') || g_delimiter ||
1267 nvl(p_loc_zip_extension,' ') || g_delimiter ||
1268 nvl(p_loc_country_code,' ') || g_delimiter ||
1269 nvl(p_contact_phone,' ') || g_delimiter ||
1270 replace(nvl(p_contact_name,' '),',',' ') || g_delimiter ||
1274 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1271 nvl(p_multi_state,' ') ||
1272 g_eol;
1273
1275
1276 return l_buffer;
1277 exception
1278 when others then
1279 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1280 raise;
1281 end a03_fl_new_hire_record;
1282 --
1283
1284 -- al audit report
1285
1286 function a03_al_new_hire_header
1287 return varchar2
1288 is
1289 l_api_name varchar2(61) := g_package_name || '.a03_al_new_hire_header';
1290 l_buffer varchar2(2000);
1291 begin
1292 --
1293 hr_utility.set_location('Entering...: ' || l_api_name,10);
1294 l_buffer := 'First Name' || g_delimiter ||
1295 'Middle Name' || g_delimiter ||
1296 'Last Name' || g_delimiter ||
1297 'Social Security Number' || g_delimiter ||
1298 'Employee Address Line 1' || g_delimiter ||
1299 'Employee Address Line 2' || g_delimiter ||
1300 'Employee Address Line 3' || g_delimiter ||
1301 'Employee City' || g_delimiter ||
1302 'Employee State' || g_delimiter ||
1303 'Employee Zip' || g_delimiter ||
1304 'Employee Zip Extension' || g_delimiter ||
1305 'Employee Country Code' || g_delimiter ||
1306 'Date of Birth' || g_delimiter ||
1307 'Date of Hire' || g_delimiter ||
1308 'State of Hire' || g_delimiter ||
1309 'FEIN' || g_delimiter ||
1310 'State SUI' || g_delimiter ||
1311 'Employer Name' || g_delimiter ||
1312 'Employer Address Line 1' || g_delimiter ||
1313 'Employer Address Line 2' || g_delimiter ||
1314 'Employer Address Line 3' || g_delimiter ||
1315 'Employer City' || g_delimiter ||
1316 'Employer State' || g_delimiter ||
1317 'Employer Zip' || g_delimiter ||
1318 'Employer Zip Extension' || g_delimiter ||
1319 'Employer Country Code' || g_delimiter ||
1320 'Employer Phone' || g_delimiter ||
1321 'Employer Contact' || g_delimiter ||
1322 'Multi-State Indicator' ||
1323 g_eol;
1324
1325 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1326
1327 return l_buffer;
1328 exception
1329 when others then
1330 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1331 raise;
1332 end a03_al_new_hire_header;
1333
1334
1335 function a03_al_new_hire_record
1336 (
1337 p_national_identifier in varchar2
1338 ,p_first_name in varchar2
1339 ,p_middle_name in varchar2
1340 ,p_last_name in varchar2
1341 ,p_emp_address_line1 in varchar2
1342 ,p_emp_address_line2 in varchar2
1343 ,p_emp_address_line3 in varchar2
1344 ,p_emp_city in varchar2
1345 ,p_emp_state in varchar2
1346 ,p_emp_zip in varchar2
1347 ,p_emp_zip_extension in varchar2
1348 ,p_emp_country_code in varchar2
1349 ,p_date_of_birth in date
1350 ,p_date_of_hire in date
1351 ,p_state_of_hire in varchar2
1352 ,p_federal_id in varchar2
1353 ,p_state_ein in varchar2
1354 ,p_tax_unit_name in varchar2
1355 ,p_loc_address_line1 in varchar2
1356 ,p_loc_address_line2 in varchar2
1357 ,p_loc_address_line3 in varchar2
1358 ,p_loc_city in varchar2
1359 ,p_loc_state in varchar2
1360 ,p_loc_zip in varchar2
1361 ,p_loc_zip_extension in varchar2
1362 ,p_loc_country_code in varchar2
1363 ,p_contact_phone in varchar2
1364 ,p_contact_phone_ext in varchar2
1365 ,p_contact_name in varchar2
1366 ,p_multi_state in varchar2
1367 ) return varchar2
1368 is
1369 l_api_name varchar2(61) := g_package_name || '.a03_al_new_hire_record';
1370 l_buffer varchar2(2000);
1371 v_ssn varchar(9);
1372 begin
1373
1374 hr_utility.set_location('Entering...: ' || l_api_name,10);
1375
1376 hr_utility.trace('p_federal_id= ' || p_federal_id);
1377 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1378 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1379
1380 hr_utility.trace('v_ssn= ' || v_ssn);
1381 hr_utility.trace('p_first_name= ' || p_first_name);
1382 hr_utility.trace('p_middle_name= ' || p_middle_name);
1383 hr_utility.trace('p_last_name= ' || p_last_name);
1384 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1385 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1386 hr_utility.trace('p_emp_city= ' || p_emp_city);
1387 hr_utility.trace('p_emp_state= ' || p_emp_state);
1388 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1389 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1390 hr_utility.trace('p_date_of_birth= ' || p_date_of_birth);
1391 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1392
1393 hr_utility.trace('p_loc_address_line1= ' || p_loc_address_line1);
1394 hr_utility.trace('p_loc_address_line2= ' || p_loc_address_line2);
1395 hr_utility.trace('p_loc_city= ' || p_loc_city);
1396 hr_utility.trace('p_loc_state= ' || p_loc_state);
1397 hr_utility.trace('p_loc_zip= ' || p_loc_zip);
1398 hr_utility.trace('p_loc_zip_extension= ' || p_loc_zip_extension);
1402
1399 hr_utility.trace('p_contact_phone = ' || p_contact_phone);
1400 hr_utility.trace('p_contact_name = ' || p_contact_name);
1401
1403 hr_utility.set_location(l_api_name,30);
1404
1405 --
1406 -- format new hire rocord
1407 --
1408 l_buffer := replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
1409 replace(nvl(p_middle_name,''),',',' ') || g_delimiter ||
1410 replace(p_last_name,',',' ') || g_delimiter ||
1411 v_ssn || g_delimiter ||
1412 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
1413 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
1414 replace(nvl(p_emp_address_line3,' '),',',' ') || g_delimiter ||
1415 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
1416 nvl(p_emp_state,' ') || g_delimiter ||
1417 nvl(p_emp_zip,' ') || g_delimiter ||
1418 nvl(p_emp_zip_extension,' ') || g_delimiter ||
1419 nvl(p_emp_country_code,' ') || g_delimiter ||
1420 nvl(to_char(p_date_of_birth,'MMDDYYYY'),' ') || g_delimiter ||
1421 nvl(to_char(p_date_of_hire,'MMDDYYYY'),' ') || g_delimiter ||
1422 nvl(p_state_of_hire,' ') || g_delimiter ||
1423 nvl(p_federal_id,' ') || g_delimiter ||
1424 nvl(p_state_ein,' ') || g_delimiter ||
1425 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
1426 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
1427 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
1428 replace(nvl(p_loc_address_line3,' '),',',' ') || g_delimiter ||
1429 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
1430 nvl(p_loc_state,' ') || g_delimiter ||
1431 nvl(p_loc_zip,' ') || g_delimiter ||
1432 nvl(p_loc_zip_extension,' ') || g_delimiter ||
1433 nvl(p_loc_country_code,' ') || g_delimiter ||
1434 nvl(p_contact_phone,' ') || g_delimiter ||
1435 replace(nvl(p_contact_name,' '),',',' ') || g_delimiter ||
1436 nvl(p_multi_state,' ') ||
1437 g_eol;
1438
1439 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1440
1441 return l_buffer;
1442 exception
1443 when others then
1444 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1445 raise;
1446 end a03_al_new_hire_record;
1447 --
1448
1449 /****************************************************************
1450 * This procedure formats a03 IL new hire audit report *
1451 * *
1452 * *
1453 ****************************************************************/
1454 function a03_il_new_hire_header
1455 return varchar2
1456 is
1457 l_api_name varchar2(61) := g_package_name || '.a03_il_new_hire_header';
1458 l_buffer varchar2(2000);
1459 begin
1460 --
1461 hr_utility.set_location('Entering...: ' || l_api_name,10);
1462 l_buffer := 'Social Security Number' || g_delimiter ||
1463 'First Name' || g_delimiter ||
1464 'Middle Name' || g_delimiter ||
1465 'Last Name' || g_delimiter ||
1466 'Employee Address Line 1' || g_delimiter ||
1467 'Employee Address Line 2' || g_delimiter ||
1468 'Employee City' || g_delimiter ||
1469 'Employee State' || g_delimiter ||
1470 'Employee Zip' || g_delimiter ||
1471 'Employee Zip Extension' || g_delimiter ||
1472 'Date of Hire' || g_delimiter ||
1473 'FEIN' || g_delimiter ||
1474 'Employer Name' || g_delimiter ||
1475 'Employer Address Line 1' || g_delimiter ||
1476 'Employer Address Line 2' || g_delimiter ||
1477 'Employer City' || g_delimiter ||
1478 'Employer State' || g_delimiter ||
1479 'Employer Zip' || g_delimiter ||
1480 'Employer Zip Extension' || g_delimiter ||
1481 g_eol;
1482
1483 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1484
1485 return l_buffer;
1486 exception
1487 when others then
1488 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1489 raise;
1490 end a03_il_new_hire_header;
1491 --
1492 /****************************************************************
1493 * This procedure formats a03 IL new hire audit report *
1494 * *
1495 * *
1496 ****************************************************************/
1497 function a03_il_new_hire_record
1498 (
1499 p_national_identifier in varchar2
1500 ,p_first_name in varchar2
1501 ,p_middle_name in varchar2
1502 ,p_last_name in varchar2
1503 ,p_emp_address_line1 in varchar2
1504 ,p_emp_address_line2 in varchar2
1505 ,p_emp_city in varchar2
1506 ,p_emp_state in varchar2
1507 ,p_emp_zip in varchar2
1508 ,p_emp_zip_extension in varchar2
1509 ,p_date_of_hire in date
1510 ,p_federal_id in varchar2
1511 ,p_tax_unit_name in varchar2
1512 ,p_loc_address_line1 in varchar2
1513 ,p_loc_address_line2 in varchar2
1514 ,p_loc_city in varchar2
1515 ,p_loc_state in varchar2
1516 ,p_loc_zip in varchar2
1517 ,p_loc_zip_extension in varchar2
1518 ) return varchar2
1519 is
1520 l_api_name varchar2(61) := g_package_name || '.a03_il_new_hire_record';
1521 l_buffer varchar2(2000);
1522 v_ssn varchar(9);
1526
1523 begin
1524
1525 hr_utility.set_location('Entering...: ' || l_api_name,10);
1527 hr_utility.trace('p_federal_id= ' || p_federal_id);
1528 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1529 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1530
1531 hr_utility.trace('v_ssn= ' || v_ssn);
1532 hr_utility.trace('p_first_name= ' || p_first_name);
1533 hr_utility.trace('p_middle_name= ' || p_middle_name);
1534 hr_utility.trace('p_last_name= ' || p_last_name);
1535 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1536 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1537 hr_utility.trace('p_emp_city= ' || p_emp_city);
1538 hr_utility.trace('p_emp_state= ' || p_emp_state);
1539 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1540 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1541 hr_utility.trace('p_date_of_birth= ' || p_date_of_hire);
1542
1543 hr_utility.set_location(l_api_name,30);
1544
1545 --
1546 -- format new hire rocord
1547 --
1548 l_buffer := v_ssn || g_delimiter ||
1549 replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
1550 replace(nvl(p_middle_name,''),',',' ') || g_delimiter ||
1551 replace(p_last_name,',',' ') || g_delimiter ||
1552 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
1553 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
1554 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
1555 nvl(p_emp_state,' ') || g_delimiter ||
1556 nvl(p_emp_zip,' ') || g_delimiter ||
1557 nvl(p_emp_zip_extension,' ') || g_delimiter ||
1558 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || g_delimiter ||
1559 nvl(p_federal_id,' ') || g_delimiter ||
1560 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
1561 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
1562 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
1563 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
1564 nvl(p_loc_state,' ') || g_delimiter ||
1565 nvl(p_loc_zip,' ') || g_delimiter ||
1566 nvl(p_loc_zip_extension,' ') ||
1567 g_eol;
1568
1569 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1570
1571 return l_buffer;
1572 exception
1573 when others then
1574 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1575 raise;
1576 end a03_il_new_hire_record;
1577 --
1578 --
1579 /****************************************************************
1580 * This procedure formats a03 TX new hire audit report *
1581 * *
1582 * *
1583 ****************************************************************/
1584 function a03_tx_new_hire_header
1585 return varchar2
1586 is
1587 l_api_name varchar2(61) := g_package_name || '.a03_tx_new_hire_header';
1588 l_buffer varchar2(2000);
1589 begin
1590 --
1591 hr_utility.set_location('Entering...: ' || l_api_name,10);
1592 l_buffer := upper(
1593 'Social Security Number' || g_delimiter ||
1594 'First Name' || g_delimiter ||
1595 'Middle Name' || g_delimiter ||
1596 'Last Name' || g_delimiter ||
1597 'Employee Address Line 1' || g_delimiter ||
1598 'Employee Address Line 2' || g_delimiter ||
1599 'Employee Address Line 3' || g_delimiter ||
1600 'Employee City' || g_delimiter ||
1601 'Employee State' || g_delimiter ||
1602 'Employee Zip' || g_delimiter ||
1603 'Employee Zip Extension' || g_delimiter ||
1604 'Employee Foreign Country Code' || g_delimiter ||
1605 -- 'Employee Foreign Country Name' || g_delimiter ||
1606 'Employee Foreign Zip' || g_delimiter ||
1607 'Date of Birth' || g_delimiter ||
1608 'Date of Hire' || g_delimiter ||
1609 'State of Hire' || g_delimiter ||
1610 'FEIN' || g_delimiter ||
1611 'State EIN' || g_delimiter ||
1612 'Employer Name' || g_delimiter ||
1613 'Employer Address Line 1' || g_delimiter ||
1614 'Employer Address Line 2' || g_delimiter ||
1615 'Employer Address Line 3' || g_delimiter ||
1616 'Employer City' || g_delimiter ||
1617 'Employer State' || g_delimiter ||
1618 'Employer Zip' || g_delimiter ||
1619 'Employer Zip Extension' || g_delimiter ||
1620 -- 'Employer Foreign Country Code' || g_delimiter ||
1621 -- 'Employer Foreign Country Name' || g_delimiter ||
1622 -- 'Employer Foreign Country Zip' ||
1623 g_eol);
1624
1625 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1626
1627 return l_buffer;
1628 exception
1629 when others then
1630 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1631 raise;
1632 end a03_tx_new_hire_header;
1633 --
1634 /****************************************************************
1635 * This procedure formats a03 TX new hire audit report *
1636 * *
1637 * *
1638 ****************************************************************/
1639 function a03_tx_new_hire_record
1640 (
1641 p_national_identifier in varchar2
1642 ,p_first_name in varchar2
1643 ,p_middle_name in varchar2
1644 ,p_last_name in varchar2
1645 ,p_emp_address_line1 in varchar2
1649 ,p_emp_state in varchar2
1646 ,p_emp_address_line2 in varchar2
1647 ,p_emp_address_line3 in varchar2
1648 ,p_emp_city in varchar2
1650 ,p_emp_zip in varchar2
1651 ,p_emp_zip_extension in varchar2
1652 ,p_emp_country_code in varchar2
1653 ,p_emp_country_name in varchar2
1654 ,p_emp_country_zip in varchar2
1655 ,p_date_of_birth in date
1656 ,p_date_of_hire in date
1657 ,p_state_of_hire in varchar2
1658 ,p_federal_id in varchar2
1659 ,p_state_ein in varchar2
1660 ,p_tax_unit_name in varchar2
1661 ,p_loc_address_line1 in varchar2
1662 ,p_loc_address_line2 in varchar2
1663 ,p_loc_address_line3 in varchar2
1664 ,p_loc_city in varchar2
1665 ,p_loc_state in varchar2
1666 ,p_loc_zip in varchar2
1667 ,p_loc_zip_extension in varchar2
1668 ,p_loc_country_code in varchar2
1669 ,p_loc_country_name in varchar2
1670 ,p_loc_country_zip in varchar2
1671 ) return varchar2
1672 is
1673 l_api_name varchar2(61) := g_package_name || '.a03_tx_new_hire_record';
1674 l_buffer varchar2(2000);
1675 v_ssn varchar(9);
1676 begin
1677
1678 hr_utility.set_location('Entering...: ' || l_api_name,10);
1679
1680 hr_utility.trace('p_federal_id= ' || p_federal_id);
1681 hr_utility.trace('p_tax_unit_name= ' || p_tax_unit_name);
1682 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1683
1684 hr_utility.trace('v_ssn= ' || v_ssn);
1685 hr_utility.trace('p_first_name= ' || p_first_name);
1686 hr_utility.trace('p_middle_name= ' || p_middle_name);
1687 hr_utility.trace('p_last_name= ' || p_last_name);
1688 hr_utility.trace('p_emp_address_line1= ' || p_emp_address_line1);
1689 hr_utility.trace('p_emp_address_line2= ' || p_emp_address_line2);
1690 hr_utility.trace('p_emp_address_line3= ' || p_emp_address_line2);
1691 hr_utility.trace('p_emp_city= ' || p_emp_city);
1692 hr_utility.trace('p_emp_state= ' || p_emp_state);
1693 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1694 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1695 hr_utility.trace('p_date_of_birth= ' || p_date_of_hire);
1696 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1697 hr_utility.trace('p_state_of_hire= ' || p_state_of_hire);
1698
1699 hr_utility.set_location(l_api_name,30);
1700
1701 --
1702 -- format new hire rocord
1703 --
1704 l_buffer := upper(
1705 v_ssn || g_delimiter ||
1706 replace(nvl(p_first_name,' '),',',' ') || g_delimiter ||
1707 replace(nvl(p_middle_name,' '),',',' ') || g_delimiter ||
1708 replace(p_last_name,',',' ') || g_delimiter ||
1709 replace(nvl(p_emp_address_line1,' '),',',' ') || g_delimiter ||
1710 replace(nvl(p_emp_address_line2,' '),',',' ') || g_delimiter ||
1711 replace(nvl(p_emp_address_line3,' '),',',' ') || g_delimiter ||
1712 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
1713 nvl(p_emp_state,' ') || g_delimiter ||
1714 nvl(p_emp_zip,' ') || g_delimiter ||
1715 nvl(p_emp_zip_extension,' ') || g_delimiter ||
1716 nvl(p_emp_country_code,' ') || g_delimiter ||
1717 -- nvl(p_emp_country_name,' ') || g_delimiter ||
1718 nvl(p_emp_country_zip,' ') || g_delimiter ||
1719 nvl(to_char(p_date_of_birth,'YYYYMMDD'),' ') || g_delimiter || -- BUG3954955
1720 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') || g_delimiter || -- BUG3954955
1721 nvl(p_state_of_hire,' ') || g_delimiter ||
1722 nvl(p_federal_id,' ') || g_delimiter ||
1723 nvl(p_state_ein,' ') || g_delimiter ||
1724 replace(nvl(p_tax_unit_name,' '),',',' ') || g_delimiter ||
1725 replace(nvl(p_loc_address_line1,' '),',',' ') || g_delimiter ||
1726 replace(nvl(p_loc_address_line2,' '),',',' ') || g_delimiter ||
1727 replace(nvl(p_loc_address_line3,' '),',',' ') || g_delimiter ||
1728 replace(nvl(p_loc_city,' '),',',' ') || g_delimiter ||
1729 nvl(p_loc_state,' ') || g_delimiter ||
1730 nvl(p_loc_zip,' ') || g_delimiter ||
1731 nvl(p_loc_zip_extension,' ') || g_delimiter ||
1732 g_eol);
1733
1734 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1735
1736 return l_buffer;
1737 exception
1738 when others then
1739 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1740 raise;
1741 end a03_tx_new_hire_record;
1742
1743
1744 /****************************************************************
1745 * This procedure formats a03 new hire CA audit report *
1746 * *
1747 * *
1748 ****************************************************************/
1749 function a03_ca_new_hire_header
1750 return varchar2
1751 is
1752 l_api_name varchar2(61) := g_package_name || '.a03_ca_new_hire_header';
1753 l_buffer varchar2(2000);
1754 begin
1755 --
1756 hr_utility.set_location('Entering...: ' || l_api_name,10);
1757 l_buffer := 'SOCIAL SECURITY NUMBER' || g_delimiter ||
1758 'FIRST NAME' || g_delimiter ||
1759 'MIDDLE INITIAL' || g_delimiter ||
1760 'LAST NAME' || g_delimiter ||
1764 'EMPLOYEE ZIP' || g_delimiter ||
1761 'EMPLOYEE STREET ADDRESS' || g_delimiter ||
1762 'EMPLOYEE CITY' || g_delimiter ||
1763 'EMPLOYEE STATE' || g_delimiter ||
1765 'EMPLOYEE ZIP EXTENSION' || g_delimiter ||
1766 'DATE OF HIRE' ||
1767 g_eol;
1768
1769 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1770
1771 return l_buffer;
1772 exception
1773 when others then
1774 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1775 raise;
1776 end a03_ca_new_hire_header;
1777 --
1778 /****************************************************************
1779 * This procedure formats a03 CA new hire audit report *
1780 * *
1781 * *
1782 ****************************************************************/
1783 function a03_ca_new_hire_record
1784 (
1785 p_national_identifier in varchar2
1786 ,p_first_name in varchar2
1787 ,p_middle_name in varchar2
1788 ,p_last_name in varchar2
1789 ,p_emp_address_line in varchar2
1790 ,p_emp_city in varchar2
1791 ,p_emp_state in varchar2
1792 ,p_emp_zip in varchar2
1793 ,p_emp_zip_extension in varchar2
1794 ,p_date_of_hire in date
1795 ) return varchar2
1796 is
1797 l_api_name varchar2(61) := g_package_name || '.a03_ca_new_hire_record';
1798 l_buffer varchar2(2000);
1799 v_ssn varchar(9);
1800 begin
1801
1802 hr_utility.set_location('Entering...: ' || l_api_name,10);
1803
1804 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1805
1806 hr_utility.trace('v_ssn= ' || v_ssn);
1807 hr_utility.trace('p_first_name= ' || p_first_name);
1808 hr_utility.trace('p_middle_name= ' || p_middle_name);
1809 hr_utility.trace('p_last_name= ' || p_last_name);
1810 hr_utility.trace('p_emp_address_line = ' || p_emp_address_line);
1811 hr_utility.trace('p_emp_city= ' || p_emp_city);
1812 hr_utility.trace('p_emp_state= ' || p_emp_state);
1813 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1814 hr_utility.trace('p_emp_zip_extension= ' || p_emp_zip_extension);
1815 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1816
1817 hr_utility.set_location(l_api_name,30);
1818
1819 --
1820 -- format new hire rocord
1821 --
1822 l_buffer := upper(
1823 v_ssn || g_delimiter ||
1824 replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
1825 replace(nvl(substr(p_middle_name,1,1),''),',',' ') || g_delimiter ||
1826 replace(p_last_name,',',' ') || g_delimiter ||
1827 replace(nvl(p_emp_address_line,' '),',',' ') || g_delimiter ||
1828 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
1829 nvl(p_emp_state,' ') || g_delimiter ||
1830 nvl(p_emp_zip,' ') || g_delimiter ||
1831 nvl(p_emp_zip_extension,' ') || g_delimiter ||
1832 nvl(to_char(p_date_of_hire,'YYYYMMDD'),' ') ||
1833 g_eol
1834 );
1835
1836 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1837
1838 return l_buffer;
1839 exception
1840 when others then
1841 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1842 raise;
1843 end a03_ca_new_hire_record;
1844 --
1845 /****************************************************************
1846 * This procedure formats a03 NY new hire audit report *
1847 * *
1848 * *
1849 ****************************************************************/
1850 function a03_ny_new_hire_header
1851 return varchar2
1852 is
1853 l_api_name varchar2(61) := g_package_name || '.a03_ny_new_hire_header';
1854 l_buffer varchar2(2000);
1855 begin
1856 --
1857 hr_utility.set_location('Entering...: ' || l_api_name,10);
1858 l_buffer := upper(
1859 'Social Security Number' || g_delimiter ||
1860 'Last Name' || g_delimiter ||
1861 'First Name' || g_delimiter ||
1862 'Middle Initial' || g_delimiter ||
1863 'Employee Street Address' || g_delimiter ||
1864 'Employee City' || g_delimiter ||
1865 'Employee State' || g_delimiter ||
1866 'Employee Zip' || g_delimiter ||
1867 'Hire Date' || g_delimiter ||
1868 g_eol);
1869
1870 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1871
1872 return l_buffer;
1873 exception
1874 when others then
1875 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1876 raise;
1877 end a03_ny_new_hire_header;
1878 --
1879 /****************************************************************
1880 * This procedure formats a03 NY new hire audit report *
1881 * *
1882 * *
1883 ****************************************************************/
1884 function a03_ny_new_hire_record
1885 (
1886 p_national_identifier in varchar2
1887 ,p_first_name in varchar2
1888 ,p_middle_name in varchar2
1889 ,p_last_name in varchar2
1890 ,p_emp_address_line in varchar2
1891 ,p_emp_city in varchar2
1892 ,p_emp_state in varchar2
1893 ,p_emp_zip in varchar2
1894 ,p_date_of_hire in date
1895 ) return varchar2
1896 is
1897 l_api_name varchar2(61) := g_package_name || '.a03_ny_new_hire_record';
1898 l_buffer varchar2(2000);
1899 v_ssn varchar(9);
1900 begin
1901
1902 hr_utility.set_location('Entering...: ' || l_api_name,10);
1903
1904 v_ssn := substr(p_national_identifier,1,3) || substr(p_national_identifier,5,2) || substr(p_national_identifier,8,4);
1905
1906 hr_utility.trace('v_ssn= ' || v_ssn);
1907 hr_utility.trace('p_first_name= ' || p_first_name);
1908 hr_utility.trace('p_middle_name= ' || p_middle_name);
1909 hr_utility.trace('p_last_name= ' || p_last_name);
1910 hr_utility.trace('p_emp_address_line = ' || p_emp_address_line);
1911 hr_utility.trace('p_emp_city= ' || p_emp_city);
1912 hr_utility.trace('p_emp_state= ' || p_emp_state);
1913 hr_utility.trace('p_emp_zip= ' || p_emp_zip);
1914 hr_utility.trace('p_date_of_hire= ' || p_date_of_hire);
1915
1916 hr_utility.set_location(l_api_name,30);
1917
1918 --
1919 -- format new hire rocord
1920 --
1921 l_buffer := upper(
1922 v_ssn || g_delimiter ||
1923 replace(p_last_name,',',' ') || g_delimiter ||
1924 replace(nvl(p_first_name,''),',',' ') || g_delimiter ||
1925 replace(nvl(substr(p_middle_name,1,1),''),',',' ') || g_delimiter ||
1926 replace(nvl(p_emp_address_line,' '),',',' ') || g_delimiter ||
1927 replace(nvl(p_emp_city,' '),',',' ') || g_delimiter ||
1928 nvl(p_emp_state,' ') || g_delimiter ||
1929 nvl(p_emp_zip,' ') || g_delimiter ||
1930 nvl(to_char(p_date_of_hire,'MMDDYY'),' ') ||
1931 g_eol);
1932
1933 hr_utility.set_location('Leaving.... :' || l_api_name,100);
1934
1935 return l_buffer;
1936 exception
1937 when others then
1938 hr_utility.set_location('Leaving.... :' || l_api_name,200);
1939 raise;
1940 end a03_ny_new_hire_record;
1941 --
1942
1943 /****************************************************************
1944 * This procedure retrieves location address *
1945 * *
1946 * *
1947 ****************************************************************/
1948 procedure get_location_address
1949 (
1950 p_location_id in number
1951 ,p_address out nocopy varchar2
1952 ,p_city out nocopy varchar2
1953 ,p_state out nocopy varchar2
1954 ,p_zip out nocopy varchar2
1955 ,p_zip_extension out nocopy varchar2
1956 ) IS
1957 --
1958 l_api_name varchar2(61) := g_package_name || '.get_location_address';
1959 f_address varchar2(300) := NULL;
1960 f_city varchar2(48) := NULL;
1961 f_state varchar2(50) := NULL;
1965 --
1962 f_zip varchar2(20) := NULL;
1963 f_zip_extension varchar2(4) := NULL;
1964 v_index number(5);
1966 v_address_line_1 hr_locations.address_line_1%TYPE;
1967 v_address_line_2 hr_locations.address_line_2%TYPE;
1968 v_address_line_3 hr_locations.address_line_3%TYPE;
1969 v_town_or_city hr_locations.town_or_city%TYPE;
1970 v_region_2 hr_locations.region_2%TYPE;
1971 v_region_1 hr_locations.region_1%TYPE;
1972 v_style hr_locations.style%TYPE;
1973 v_postal_code hr_locations.postal_code%TYPE;
1974 --
1975 cursor get_location_record is
1976 select address_line_1, address_line_2, address_line_3,
1977 town_or_city, region_1, region_2, postal_code ,style
1978 from hr_locations
1979 where location_id = p_location_id;
1980 --
1981 begin
1982 --
1983 hr_utility.set_location('Entering :' || l_api_name, 5);
1984 hr_utility.trace('location_id is ' || to_char(p_location_id));
1985 --
1986 open get_location_record;
1987 --
1988 fetch get_location_record into v_address_line_1, v_address_line_2,
1989 v_address_line_3, v_town_or_city, v_region_1
1990 ,v_region_2, v_postal_code ,v_style;
1991 --
1992 hr_utility.set_location(l_api_name, 10);
1993 --
1994 if get_location_record%found
1995 then
1996 --
1997 if v_address_line_1 is not null
1998 then
1999 f_address := v_address_line_1;
2000 end if;
2001 --
2002 hr_utility.set_location(l_api_name, 11);
2003 if v_address_line_2 is not null
2004 then
2005 f_address := f_address || ' ' || v_address_line_2;
2006 end if;
2007 --
2008 hr_utility.set_location(l_api_name, 12);
2009 if v_address_line_3 is not null
2010 then
2011 f_address := f_address || ' ' || v_address_line_3;
2012 end if;
2013 hr_utility.trace('street address is '|| f_address);
2014 --
2015 hr_utility.set_location(l_api_name, 13);
2016 if v_town_or_city is not null
2017 then
2018 f_city:= rpad(v_town_or_city,48,' ');
2019 end if;
2020 hr_utility.trace('city is '|| f_city);
2021 --
2022 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
2023 then
2024 f_state := v_region_2;
2025 elsif v_style = 'CA' and v_region_1 is not null then
2026 f_state := v_region_1;
2027 end if;
2028 hr_utility.trace('state is '|| f_state);
2029 --
2030 if v_postal_code is not null
2031 then
2032 f_zip := v_postal_code;
2033 v_index := instr(v_postal_code,'-') ;
2034 if (v_index <> 0)
2035 then
2036 f_zip := substr(v_postal_code,1,5);
2037 f_zip_extension := substr(v_postal_code,v_index+1,4);
2038 else
2039 f_zip_extension := null;
2040 end if;
2041 end if;
2042 hr_utility.trace('postal code is '|| f_zip);
2043 hr_utility.trace('postal code extension is '|| f_zip_extension);
2044
2045
2046 --
2047 close get_location_record;
2048 --
2049 hr_utility.set_location(l_api_name, 20);
2050
2051 p_address := f_address;
2052 p_city := f_city;
2053 p_state := f_state;
2054 p_zip := f_zip;
2055 p_zip_extension := f_zip_extension;
2056 --
2057 end if;
2058 --
2059 hr_utility.set_location('Leaving ..' || l_api_name, 100);
2060
2061 exception
2062 when others then
2063 hr_utility.trace('Error in ' || l_api_name);
2064 hr_utility.set_location(l_api_name, 30);
2065 --
2066 end get_location_address;
2067 /****************************************************************
2068 * This procedure retrieves location address *
2069 * address_line1,address_line2,address_line3 *
2070 * *
2071 ****************************************************************/
2072 procedure get_location_address_3lines
2073 (
2074 p_location_id in number
2075 ,p_address_line1 out nocopy varchar2
2076 ,p_address_line2 out nocopy varchar2
2077 ,p_address_line3 out nocopy varchar2
2078 ,p_city out nocopy varchar2
2079 ,p_state out nocopy varchar2
2080 ,p_zip out nocopy varchar2
2081 ,p_zip_extension out nocopy varchar2
2082 ,p_country out nocopy varchar2
2083 ) IS
2084 --
2085 l_api_name varchar2(61) := g_package_name || '.get_location_address_3lines';
2086 f_address_line1 varchar2(300) := NULL;
2087 f_address_line2 varchar2(300) := NULL;
2088 f_address_line3 varchar2(300) := NULL;
2089 f_city varchar2(48) := NULL;
2090 f_state varchar2(50) := NULL;
2091 f_zip varchar2(20) := NULL;
2092 f_zip_extension varchar2(4) := NULL;
2093 v_index number(5);
2094 --
2095 v_address_line_1 hr_locations.address_line_1%TYPE;
2096 v_address_line_2 hr_locations.address_line_2%TYPE;
2097 v_address_line_3 hr_locations.address_line_3%TYPE;
2098 v_town_or_city hr_locations.town_or_city%TYPE;
2099 v_region_2 hr_locations.region_2%TYPE;
2100 v_region_1 hr_locations.region_1%TYPE;
2101 v_postal_code hr_locations.postal_code%TYPE;
2102 v_country hr_locations.country%TYPE;
2106 select address_line_1, address_line_2, address_line_3,
2103 v_style hr_locations.style%TYPE;
2104 --
2105 cursor get_location_record is
2107 town_or_city, region_1, region_2, postal_code
2108 ,country,style
2109 from hr_locations
2110 where location_id = p_location_id;
2111 --
2112 begin
2113 --
2114 hr_utility.set_location('Entering :' || l_api_name, 5);
2115 hr_utility.trace('location_id is ' || to_char(p_location_id));
2116 --
2117 open get_location_record;
2118 --
2119 fetch get_location_record into v_address_line_1, v_address_line_2,
2120 v_address_line_3, v_town_or_city, v_region_1,v_region_2
2121 , v_postal_code ,v_country, v_style;
2122 --
2123 hr_utility.set_location(l_api_name, 10);
2124 --
2125 if get_location_record%found
2126 then
2127 --
2128 if v_address_line_1 is not null
2129 then
2130 f_address_line1 := v_address_line_1;
2131 hr_utility.trace('address_line1 is '|| f_address_line1);
2132 end if;
2133 --
2134 hr_utility.set_location(l_api_name, 11);
2135 if v_address_line_2 is not null
2136 then
2137 f_address_line2 := v_address_line_2;
2138 hr_utility.trace('address_line2 is '|| f_address_line2);
2139 end if;
2140 --
2141 hr_utility.set_location(l_api_name, 12);
2142 if v_address_line_3 is not null
2143 then
2144 f_address_line3 := v_address_line_3;
2145 hr_utility.trace('address_line3 is '|| f_address_line3);
2146 end if;
2147 --
2148 hr_utility.set_location(l_api_name, 13);
2149 if v_town_or_city is not null
2150 then
2151 f_city:= rpad(v_town_or_city,48,' ');
2152 end if;
2153 hr_utility.trace('city is '|| f_city);
2154 --
2155 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
2156 then
2157 f_state := v_region_2;
2158 elsif v_style = 'CA' and v_region_1 is not null then
2159 f_state := v_region_1;
2160 end if;
2161 hr_utility.trace('state is '|| f_state);
2162 --
2163 if v_country = 'US'
2164 then
2165 if v_postal_code is not null
2166 then
2167 f_zip := v_postal_code;
2168 v_index := instr(v_postal_code,'-') ;
2169 if (v_index <> 0)
2170 then
2171 f_zip := substr(v_postal_code,1,5);
2172 f_zip_extension := substr(v_postal_code,v_index+1,4);
2173 else
2174 f_zip_extension := null;
2175 end if;
2176 end if;
2177 p_country := ' ';
2178 else
2179 f_zip := v_postal_code;
2180 f_zip_extension := null;
2181 end if;
2182 hr_utility.trace('country code is '|| v_country);
2183 hr_utility.trace('postal code is '|| f_zip);
2184 hr_utility.trace('postal code extension is '|| f_zip_extension);
2185
2186
2187 --
2188 close get_location_record;
2189 --
2190 hr_utility.set_location(l_api_name, 20);
2191
2192 p_address_line1 := f_address_line1;
2193 p_address_line2 := f_address_line2;
2194 p_address_line3 := f_address_line3;
2195 p_city := f_city;
2196 p_state := f_state;
2197 p_zip := f_zip;
2198 p_zip_extension := f_zip_extension;
2199 p_country := v_country;
2200 --
2201 end if;
2202 --
2203 hr_utility.set_location('Leaving ..' || l_api_name, 100);
2204
2205 exception
2206 when others then
2207 hr_utility.trace('Error in ' || l_api_name);
2208 hr_utility.set_location(l_api_name, 30);
2209 --
2210 end get_location_address_3lines;
2211 --
2212 /****************************************************************
2213 * This procedure retrieves employee address *
2214 * *
2215 * *
2216 ****************************************************************/
2217 procedure get_employee_address
2218 (
2219 p_person_id in number
2220 ,p_address out nocopy varchar2
2221 ,p_city out nocopy varchar2
2222 ,p_state out nocopy varchar2
2223 ,p_zip out nocopy varchar2
2224 ,p_zip_extension out nocopy varchar2
2225 ) IS
2226 --
2227 l_api_name varchar2(61) := g_package_name || '.get_employee_address';
2228 f_address varchar2(300) := NULL;
2229 f_city varchar2(48) := NULL;
2230 f_state varchar2(50) := NULL;
2231 f_zip varchar2(20) := NULL;
2232 f_zip_extension varchar2(4) := NULL;
2233 v_index number(5);
2234 --
2235 -- address_record per_addresses%rowtype;
2236 --
2237 v_address_line1 per_addresses.address_line1%TYPE;
2238 v_address_line2 per_addresses.address_line2%TYPE;
2239 v_address_line3 per_addresses.address_line3%TYPE;
2240 v_town_or_city per_addresses.town_or_city%TYPE;
2241 v_region_2 per_addresses.region_2%TYPE;
2242 v_region_1 per_addresses.region_1%TYPE;
2243 v_postal_code per_addresses.postal_code%TYPE;
2244 v_country per_addresses.country%TYPE;
2245 v_style per_addresses.style%TYPE;
2246 --
2247 cursor get_address_record is
2248 select address_line1, address_line2, address_line3,
2249 town_or_city, region_1, region_2, postal_code
2250 ,style
2251 from per_addresses
2252 where person_id = p_person_id
2253 and primary_flag = 'Y'
2254 and nvl(date_to, sysdate) >= sysdate;
2255 --
2256 begin
2257 --
2258 hr_utility.set_location(l_api_name, 0);
2259 --
2260 open get_address_record;
2261 --
2262 fetch get_address_record into v_address_line1, v_address_line2,
2263 v_address_line3, v_town_or_city, v_region_1
2264 ,v_region_2, v_postal_code,v_style;
2265 --
2266 hr_utility.set_location(l_api_name, 5);
2267 --
2268 if get_address_record%found
2269 then
2270 --
2271 if v_address_line1 is not null
2272 then
2273 f_address := v_address_line1;
2274 end if;
2275 --
2276 if v_address_line2 is not null
2277 then
2278 f_address := f_address || ' ' || v_address_line2;
2279 end if;
2280 --
2281 if v_address_line3 is not null
2282 then
2283 f_address := f_address || ' ' || v_address_line3;
2284 end if;
2285 --
2286 hr_utility.trace('Person Address is '|| f_address);
2287
2288 if v_town_or_city is not null
2289 then
2290 f_city:= rpad(v_town_or_city,31,' ');
2291 end if;
2292 hr_utility.trace('city is '|| f_city);
2293 --
2294 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
2295 then
2296 f_state := v_region_2;
2297 elsif v_style = 'CA' and v_region_1 is not null then
2298 f_state := v_region_1;
2299 end if;
2300 hr_utility.trace('state is '|| f_state);
2301 --
2302 if v_postal_code is not null
2303 then
2304 f_zip := v_postal_code;
2305 v_index := instr(v_postal_code,'-') ;
2306 if (v_index <> 0)
2307 then
2308 f_zip := substr(v_postal_code,1,5);
2309 f_zip_extension := substr(v_postal_code,v_index+1,4);
2310 else
2311 f_zip_extension := null;
2312 end if;
2313 end if;
2314 hr_utility.trace('country code is '|| v_country);
2315 hr_utility.trace('postal code is '|| f_zip);
2316 hr_utility.trace('postal extension code is '|| f_zip_extension);
2317
2318 --
2319 hr_utility.set_location(l_api_name, 10);
2320 close get_address_record;
2321 --
2322 --
2323 p_address := f_address;
2324 p_city := f_city;
2325 p_state := f_state;
2326 p_zip := f_zip;
2327 p_zip_extension := f_zip_extension;
2328 --
2329 end if;
2330 --
2331 hr_utility.set_location('Leaving...:' || l_api_name, 50);
2332 --
2333 exception when NO_DATA_FOUND then NULL;
2334 --
2335 end get_employee_address;
2336 --
2337 /****************************************************************
2338 * This procedure retrieves employee address *
2339 * address_line1,address_line2,address_line3 *
2340 * *
2341 ****************************************************************/
2342 procedure get_employee_address_3lines
2343 (
2344 p_person_id in number
2345 ,p_address_line1 out nocopy varchar2
2346 ,p_address_line2 out nocopy varchar2
2347 ,p_address_line3 out nocopy varchar2
2348 ,p_city out nocopy varchar2
2349 ,p_state out nocopy varchar2
2350 ,p_zip out nocopy varchar2
2351 ,p_zip_extension out nocopy varchar2
2352 ,p_country out nocopy varchar2
2353 ) IS
2354 --
2355 l_api_name varchar2(61) := g_package_name || '.get_employee_address_3lines';
2356 f_address_line1 varchar2(300) := NULL;
2357 f_address_line2 varchar2(300) := NULL;
2358 f_address_line3 varchar2(300) := NULL;
2362 f_zip_extension varchar2(4) := NULL;
2359 f_city varchar2(48) := NULL;
2360 f_state varchar2(50) := NULL;
2361 f_zip varchar2(20) := NULL;
2363 v_index number(5);
2364 --
2365 -- address_record per_addresses%rowtype;
2366 --
2367 v_address_line1 per_addresses.address_line1%TYPE;
2368 v_address_line2 per_addresses.address_line2%TYPE;
2369 v_address_line3 per_addresses.address_line3%TYPE;
2370 v_town_or_city per_addresses.town_or_city%TYPE;
2371 v_region_2 per_addresses.region_2%TYPE;
2372 v_region_1 per_addresses.region_1%TYPE;
2373 v_postal_code per_addresses.postal_code%TYPE;
2374 v_country per_addresses.country%TYPE;
2375 v_style per_addresses.style%TYPE;
2376 --
2377 cursor get_address_record is
2378 select address_line1, address_line2, address_line3,
2379 town_or_city, region_1, region_2
2380 , postal_code ,country ,style
2381 from per_addresses
2382 where person_id = p_person_id
2383 and primary_flag = 'Y'
2384 and nvl(date_to, sysdate) >= sysdate;
2385 --
2386 begin
2387 --
2388 hr_utility.set_location(l_api_name, 0);
2389 --
2390 open get_address_record;
2391 --
2392 fetch get_address_record into v_address_line1, v_address_line2,
2393 v_address_line3, v_town_or_city, v_region_1,v_region_2
2394 , v_postal_code ,v_country,v_style;
2395 --
2396 hr_utility.set_location(l_api_name, 5);
2397 --
2398 if get_address_record%found
2399 then
2400 --
2401 if v_address_line1 is not null
2402 then
2403 f_address_line1 := v_address_line1;
2404 hr_utility.trace('address_line1 is '|| f_address_line1);
2405 end if;
2406 --
2407 if v_address_line2 is not null
2408 then
2409 f_address_line2 := v_address_line2;
2410 hr_utility.trace('address_line2 is '|| f_address_line2);
2411 end if;
2412 --
2413 if v_address_line3 is not null
2414 then
2415 f_address_line3 := v_address_line3;
2416 hr_utility.trace('address_line3 is '|| f_address_line3);
2417 end if;
2418 --
2419
2420 if v_town_or_city is not null
2421 then
2422 f_city:= rpad(v_town_or_city,31,' ');
2423 end if;
2424 hr_utility.trace('city is '|| f_city);
2425 --
2426 if (v_style = 'US' or v_style = 'US_GLB') and v_region_2 is not null
2427 then
2428 f_state := v_region_2;
2429 elsif v_style = 'CA' and v_region_1 is not null then
2430 f_state := v_region_1;
2431 end if;
2432 hr_utility.trace('state is '|| f_state);
2433 --
2434 if v_country = 'US'
2435 then
2436 if v_postal_code is not null
2437 then
2438 f_zip := v_postal_code;
2439 v_index := instr(v_postal_code,'-') ;
2440 if (v_index <> 0)
2441 then
2442 f_zip := substr(v_postal_code,1,5);
2443 f_zip_extension := substr(v_postal_code,v_index+1,4);
2444 else
2445 f_zip_extension := null;
2446 end if;
2447 end if;
2448 p_country := ' ';
2449 else
2450 f_zip := v_postal_code;
2451 f_zip_extension := null;
2452 end if;
2453 hr_utility.trace('country code is '|| v_country);
2454 hr_utility.trace('postal code is '|| f_zip);
2455 hr_utility.trace('postal extension code is '|| f_zip_extension);
2456
2457 --
2458 hr_utility.set_location(l_api_name, 10);
2459 close get_address_record;
2460 --
2461 --
2462 p_address_line1 := f_address_line1;
2463 p_address_line2 := f_address_line2;
2464 p_address_line3 := f_address_line3;
2465 p_city := f_city;
2466 p_state := f_state;
2467 p_zip := f_zip;
2468 p_zip_extension := f_zip_extension;
2469 p_country := v_country;
2470 --
2471 end if;
2472 --
2473 hr_utility.set_location('Leaving...:' || l_api_name, 50);
2474 --
2475 exception when NO_DATA_FOUND then NULL;
2476 --
2477 end get_employee_address_3lines;
2478 --
2479 --
2480 /****************************************************************
2481 * This procedure retrieves employer contact *
2482 * name, title,phone *
2483 * *
2484 ****************************************************************/
2485 procedure get_new_hire_contact( p_person_id in number,
2486 p_business_group_id in number,
2487 p_report_date in date,
2488 p_contact_name out nocopy varchar2,
2489 p_contact_title out nocopy varchar2,
2490 p_contact_phone out nocopy varchar2
2491 ) IS
2492 --
2493 l_api_name varchar2(61) := g_package_name || '.get_new_hire_contact';
2494 v_contact_name varchar2(240); --per_people_f.full_name%TYPE;
2495 v_contact_title varchar2(700); --per_jobs.name%TYPE;
2496 v_contact_phone varchar2(60); -- per_people_f.work_telephone%TYPE;
2497
2498 CURSOR c_new_hire_record IS
2499 Select ppf.first_name || ' ' || ppf.last_name,
2500 job.name,
2501 ppf.work_telephone
2502 From
2503 --per_people_f ppf, --view bug 4912696
2504 per_all_people_f ppf, --table
2505 --per_assignments_f paf, --view bug 4912696
2506 per_all_assignments_f paf, --table
2507 per_jobs job
2508 Where
2509 ppf.person_id = p_person_id
2510 And ppf.business_group_id + 0 = p_business_group_id
2511 And p_report_date between paf.effective_start_date
2512 and paf.effective_end_date
2513 And ppf.person_id = paf.person_id
2514 And paf.assignment_type = 'E'
2515 And paf.primary_flag = 'Y'
2516 And p_report_date between paf.effective_start_date
2517 and paf.effective_end_date
2518 -- BUG2919553
2519 And ppf.effective_start_date =
2520 (select max(ppf1.effective_start_date)
2521 from per_all_people_f ppf1 --table bug 4912696
2522 where ppf1.person_id = ppf.person_id
2523 and ppf1.effective_start_date <= p_report_date
2524 )
2525 -- End of BUG2919553
2526 And paf.job_id = job.job_id(+);
2527
2528 --
2529 begin
2530 --
2531 hr_utility.set_location('Entering....:' || l_api_name,10);
2532 --
2533 OPEN c_new_hire_record;
2534 --LOOP
2535 FETCH c_new_hire_record INTO v_contact_name, v_contact_title, v_contact_phone;
2536 --
2537 p_contact_name := substr(v_contact_name,1,60);
2538 p_contact_title := substr(v_contact_title,1,60);
2539 p_contact_phone := substr(v_contact_phone,1,60);
2540 --
2541 -- EXIT WHEN c_new_hire_record%NOTFOUND;
2542 --END LOOP;
2543
2544 CLOSE c_new_hire_record;
2545 --
2546 hr_utility.trace('Contact name : '||v_contact_name);
2547 hr_utility.trace('Contact title : '||v_contact_title);
2548 hr_utility.set_location('Leaving....:' || l_api_name ,100);
2549 --
2550 exception
2551 when no_data_found then
2552 hr_utility.set_location('Error found in ' || l_api_name,20);
2553 NULL;
2554 when others then
2555 hr_utility.set_location('Error found in ' || l_api_name,30);
2556 --
2557 end get_new_hire_contact;
2558 --
2559 --
2560 end per_new_hire_pkg;