[Home] [Help]
PACKAGE BODY: APPS.HR_EMPLOYEE_API
Source
1 Package Body hr_employee_api as
2 /* $Header: peempapi.pkb 120.14.12020000.3 2012/07/05 02:28:06 amnaraya ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_employee_api.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- Package cursors
10 --
11 CURSOR csr_future_asgs
12 (p_person_id IN per_all_people_f.person_id%TYPE
13 ,p_effective_date IN DATE
14 )
15 IS
16 SELECT asg.assignment_id
17 ,asg.object_version_number
18 FROM per_assignments_f asg
19 WHERE asg.person_id = csr_future_asgs.p_person_id
20 --
21 -- 115.58 (START)
22 --
23 AND asg.assignment_type <> 'B'
24 --
25 -- 115.58 (START)
26 --
27 AND asg.effective_start_date >= csr_future_asgs.p_effective_date;
28 --
29 -- -----------------------------------------------------------------------------
30 -- |--------------------------< future_asgs_count >----------------------------|
31 -- -----------------------------------------------------------------------------
32 --
33 -- {Start Of Comments}
34 --
35 -- Description:
36 -- Determines the number of assignments for a person which start on or after
37 -- a date.
38 --
39 -- Prerequisites:
40 -- None
41 --
42 -- In Parameters:
43 -- Name Reqd Type Description
44 -- p_person_id No number Person id
45 -- p_effective_date No date Effective date
46 --
47 -- Post Success:
48 -- The number of assignments for the person starting on or after a date is
49 -- returned.
50 --
51 -- Post Failure:
52 -- An error is raised.
53 --
54 -- Access Status:
55 -- Internal Development Use Only
56 --
57 -- {End Of Comments}
58 --
59 FUNCTION future_asgs_count
60 (p_person_id IN per_all_people_f.person_id%TYPE
61 ,p_effective_date IN DATE
62 )
63 RETURN INTEGER
64 IS
65 --
66 -- Local variables
67 --
68 l_proc VARCHAR2(72);
69 --
70 l_future_asgs_count INTEGER := 0;
71 --
72 -- 115.58 (START)
73 --
74 CURSOR csr_back_to_back IS
75 SELECT 'x'
76 FROM per_assignments_f asg1
77 ,per_assignments_f asg2
78 ,per_assignment_status_types pas1
79 ,per_assignment_status_types pas2
80 ,per_periods_of_service pds
81 WHERE pds.person_id = p_person_id
82 AND pds.person_id = asg1.person_id
83 AND pds.person_id = asg2.person_id
84 AND asg1.assignment_status_type_id = pas1.assignment_status_type_id
85 AND asg2.assignment_status_type_id = pas2.assignment_status_type_id
86 AND pds.final_process_date > pds.actual_termination_date
87 AND pds.actual_termination_date+1 = p_effective_date
88 AND asg1.effective_start_date = p_effective_date
89 AND pas1.per_system_status = 'TERM_ASSIGN'
90 AND asg2.effective_end_date+1 = p_effective_date
91 AND pas2.per_system_status = 'ACTIVE_ASSIGN';
92 --
93 l_dummy VARCHAR2(1);
94 --
95 -- 115.58 (END)
96 --
97 --
98 BEGIN
99 --
100 if g_debug then
101 l_proc := g_package||'future_asgs_count';
102 hr_utility.set_location('Entering:'||l_proc,10);
103 end if;
104 --
105 FOR l_future_asgs_rec IN
106 csr_future_asgs
107 (p_person_id => p_person_id
108 ,p_effective_date => p_effective_date
109 )
110 LOOP
111 l_future_asgs_count := l_future_asgs_count + 1;
112 END LOOP;
113 --
114 if g_debug then
115 hr_utility.set_location(' Leaving:'||l_proc,100);
116 end if;
117 --
118 --
119 -- 115.58 (START)
120 --
121 -- Check if this is a back-to-back scenario with FPD > ATD and ED = ATD+1
122 -- and ASG1-ESD = ED and ASG1-TYPE = 'TERM_ASSIGN' and ASG2-EED+1 = ED and
123 -- ASG2-TYPE = 'ACTIVE_ASSIGN' and l_future_asgs_count = 1
124 -- If so, this does not consitute an assignment change.
125 -- Make l_future_asgs_count = 0
126 IF l_future_asgs_count = 1 THEN
127 OPEN csr_back_to_back;
128 FETCH csr_back_to_back INTO l_dummy;
129 IF csr_back_to_back%FOUND THEN
130 l_future_asgs_count := 0;
131 END IF;
132 CLOSE csr_back_to_back;
133 END IF;
134 --
135 -- 115.58 (END)
136 --
137 RETURN(l_future_asgs_count);
138 --
139 EXCEPTION
140 WHEN OTHERS
141 THEN
142 IF csr_future_asgs%ISOPEN
143 THEN
144 CLOSE csr_future_asgs;
145 END IF;
146 RAISE;
147 --
148 END future_asgs_count;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |--------------------------< create_employee >-----------------------------|
152 -- ----------------------------------------------------------------------------
153 --
154 procedure create_employee
155 (p_validate in boolean default false
156 ,p_hire_date in date
157 ,p_business_group_id in number
158 ,p_last_name in varchar2
159 ,p_sex in varchar2
160 ,p_person_type_id in number default null
161 ,p_per_comments in varchar2 default null
162 ,p_date_employee_data_verified in date default null
163 ,p_date_of_birth in date default null
164 ,p_email_address in varchar2 default null
165 ,p_employee_number in out nocopy varchar2
166 ,p_expense_check_send_to_addres in varchar2 default null
167 ,p_first_name in varchar2 default null
168 ,p_known_as in varchar2 default null
169 ,p_marital_status in varchar2 default null
170 ,p_middle_names in varchar2 default null
171 ,p_nationality in varchar2 default null
172 ,p_national_identifier in varchar2 default null
173 ,p_previous_last_name in varchar2 default null
174 ,p_registered_disabled_flag in varchar2 default null
175 ,p_title in varchar2 default null
176 ,p_vendor_id in number default null
177 ,p_work_telephone in varchar2 default null
178 ,p_attribute_category in varchar2 default null
179 ,p_attribute1 in varchar2 default null
180 ,p_attribute2 in varchar2 default null
181 ,p_attribute3 in varchar2 default null
182 ,p_attribute4 in varchar2 default null
183 ,p_attribute5 in varchar2 default null
184 ,p_attribute6 in varchar2 default null
185 ,p_attribute7 in varchar2 default null
186 ,p_attribute8 in varchar2 default null
187 ,p_attribute9 in varchar2 default null
188 ,p_attribute10 in varchar2 default null
189 ,p_attribute11 in varchar2 default null
190 ,p_attribute12 in varchar2 default null
191 ,p_attribute13 in varchar2 default null
192 ,p_attribute14 in varchar2 default null
193 ,p_attribute15 in varchar2 default null
194 ,p_attribute16 in varchar2 default null
195 ,p_attribute17 in varchar2 default null
196 ,p_attribute18 in varchar2 default null
197 ,p_attribute19 in varchar2 default null
198 ,p_attribute20 in varchar2 default null
199 ,p_attribute21 in varchar2 default null
200 ,p_attribute22 in varchar2 default null
201 ,p_attribute23 in varchar2 default null
202 ,p_attribute24 in varchar2 default null
203 ,p_attribute25 in varchar2 default null
204 ,p_attribute26 in varchar2 default null
205 ,p_attribute27 in varchar2 default null
206 ,p_attribute28 in varchar2 default null
207 ,p_attribute29 in varchar2 default null
208 ,p_attribute30 in varchar2 default null
209 ,p_per_information_category in varchar2 default null
210 ,p_per_information1 in varchar2 default null
211 ,p_per_information2 in varchar2 default null
212 ,p_per_information3 in varchar2 default null
213 ,p_per_information4 in varchar2 default null
214 ,p_per_information5 in varchar2 default null
215 ,p_per_information6 in varchar2 default null
216 ,p_per_information7 in varchar2 default null
217 ,p_per_information8 in varchar2 default null
218 ,p_per_information9 in varchar2 default null
219 ,p_per_information10 in varchar2 default null
220 ,p_per_information11 in varchar2 default null
221 ,p_per_information12 in varchar2 default null
222 ,p_per_information13 in varchar2 default null
223 ,p_per_information14 in varchar2 default null
224 ,p_per_information15 in varchar2 default null
225 ,p_per_information16 in varchar2 default null
226 ,p_per_information17 in varchar2 default null
227 ,p_per_information18 in varchar2 default null
228 ,p_per_information19 in varchar2 default null
229 ,p_per_information20 in varchar2 default null
230 ,p_per_information21 in varchar2 default null
231 ,p_per_information22 in varchar2 default null
232 ,p_per_information23 in varchar2 default null
233 ,p_per_information24 in varchar2 default null
234 ,p_per_information25 in varchar2 default null
235 ,p_per_information26 in varchar2 default null
236 ,p_per_information27 in varchar2 default null
237 ,p_per_information28 in varchar2 default null
238 ,p_per_information29 in varchar2 default null
239 ,p_per_information30 in varchar2 default null
240 ,p_date_of_death in date default null
241 ,p_background_check_status in varchar2 default null
242 ,p_background_date_check in date default null
243 ,p_blood_type in varchar2 default null
244 ,p_correspondence_language in varchar2 default null
245 ,p_fast_path_employee in varchar2 default null
246 ,p_fte_capacity in number default null
247 ,p_honors in varchar2 default null
248 ,p_internal_location in varchar2 default null
249 ,p_last_medical_test_by in varchar2 default null
250 ,p_last_medical_test_date in date default null
251 ,p_mailstop in varchar2 default null
252 ,p_office_number in varchar2 default null
253 ,p_on_military_service in varchar2 default null
254 ,p_pre_name_adjunct in varchar2 default null
255 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
256 ,p_projected_start_date in date default null
257 ,p_resume_exists in varchar2 default null
258 ,p_resume_last_updated in date default null
259 ,p_second_passport_exists in varchar2 default null
260 ,p_student_status in varchar2 default null
261 ,p_work_schedule in varchar2 default null
262 ,p_suffix in varchar2 default null
263 ,p_benefit_group_id in number default null
264 ,p_receipt_of_death_cert_date in date default null
265 ,p_coord_ben_med_pln_no in varchar2 default null
266 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
267 ,p_coord_ben_med_ext_er in varchar2 default null
268 ,p_coord_ben_med_pl_name in varchar2 default null
269 ,p_coord_ben_med_insr_crr_name in varchar2 default null
270 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
271 ,p_coord_ben_med_cvg_strt_dt in date default null
272 ,p_coord_ben_med_cvg_end_dt in date default null
273 ,p_uses_tobacco_flag in varchar2 default null
274 ,p_dpdnt_adoption_date in date default null
275 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
276 ,p_original_date_of_hire in date default null
277 ,p_adjusted_svc_date in date default null
278 ,p_town_of_birth in varchar2 default null
279 ,p_region_of_birth in varchar2 default null
280 ,p_country_of_birth in varchar2 default null
281 ,p_global_person_id in varchar2 default null
282 ,p_party_id in number default null
283 ,p_person_id out nocopy number
284 ,p_assignment_id out nocopy number
285 ,p_per_object_version_number out nocopy number
286 ,p_asg_object_version_number out nocopy number
287 ,p_per_effective_start_date out nocopy date
288 ,p_per_effective_end_date out nocopy date
289 ,p_full_name out nocopy varchar2
290 ,p_per_comment_id out nocopy number
291 ,p_assignment_sequence out nocopy number
292 ,p_assignment_number out nocopy varchar2
293 ,p_name_combination_warning out nocopy boolean
294 ,p_assign_payroll_warning out nocopy boolean
295 ,p_orig_hire_warning out nocopy boolean
296 )
297 is
298 --
299 -- Declare cursors and local variables
300 --
301 l_proc varchar2(72);
302 l_person_type_id per_person_types.person_type_id%type := p_person_type_id;
303 l_person_type_id1 per_person_types.person_type_id%type;
304 l_person_id per_people_f.person_id%type;
305 l_period_of_service_id per_periods_of_service.period_of_service_id%type;
306 l_employee_number per_people_f.employee_number%type;
307 l_emp_num per_people_f.employee_number%type := p_employee_number;
308 l_applicant_number per_people_f.applicant_number%TYPE;
309 l_npw_number per_people_f.npw_number%TYPE;
310 l_assignment_sequence per_assignments_f.assignment_sequence%type;
311 l_assignment_number per_assignments_f.assignment_number%type;
312 l_hire_date date;
313 l_discard_number number;
314 l_discard_date date;
315 l_discard_varchar2 varchar2(30);
316 l_assignment_id number;
317 l_per_object_version_number number;
318 l_asg_object_version_number number;
319 l_per_effective_start_date date;
320 l_per_effective_end_date date;
321 l_full_name per_people_f.full_name%type;
322 l_per_comment_id number;
323 l_name_combination_warning boolean;
324 l_assign_payroll_warning boolean;
325 l_orig_hire_warning boolean;
326 l_date_employee_data_verified date;
327 l_date_of_birth date;
328 l_phn_object_version_number per_phones.object_version_number%TYPE;
329 l_phone_id per_phones.phone_id%TYPE;
330 l_date_of_death date;
331 l_receipt_of_death_cert_date date;
332 l_dpdnt_adoption_date date;
333 l_original_date_of_hire date;
334 l_adjusted_svc_date date;
335 --
336 begin
337 if g_debug then
338 l_proc := g_package||'create_employee';
339 hr_utility.set_location('Entering:'|| l_proc, 10);
340 end if;
341 --
342 -- Issue a savepoint.
343 --
344 savepoint create_employee;
345 --
346 --
347 -- Truncate the time portion from all date parameters
348 -- which are passed in.
349 --
350 l_hire_date := trunc(p_hire_date);
351 l_date_employee_data_verified := trunc(p_date_employee_data_verified);
352 l_date_of_birth := trunc(p_date_of_birth);
353 l_date_of_death := trunc(p_date_of_death);
354 l_receipt_of_death_cert_date := trunc(p_receipt_of_death_cert_date);
355 l_dpdnt_adoption_date := trunc(p_dpdnt_adoption_date);
356 l_original_date_of_hire := trunc(p_original_date_of_hire);
357 l_adjusted_svc_date := trunc(p_adjusted_svc_date);
358 --
359 begin
360 --
361 -- Start of API User Hook for the before hook of create_employee
362 --
363 hr_employee_bk1.create_employee_b
364 (p_hire_date => l_hire_date
365 ,p_business_group_id => p_business_group_id
366 ,p_last_name => p_last_name
367 ,p_sex => p_sex
368 ,p_person_type_id => p_person_type_id
369 ,p_per_comments => p_per_comments
370 ,p_date_employee_data_verified => l_date_employee_data_verified
371 ,p_date_of_birth => l_date_of_birth
372 ,p_email_address => p_email_address
373 ,p_employee_number => p_employee_number
374 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
375 ,p_first_name => p_first_name
376 ,p_known_as => p_known_as
377 ,p_marital_status => p_marital_status
378 ,p_middle_names => p_middle_names
379 ,p_nationality => p_nationality
380 ,p_national_identifier => p_national_identifier
381 ,p_previous_last_name => p_previous_last_name
382 ,p_registered_disabled_flag => p_registered_disabled_flag
383 ,p_title => p_title
384 ,p_vendor_id => p_vendor_id
385 ,p_work_telephone => p_work_telephone
386 ,p_attribute_category => p_attribute_category
387 ,p_attribute1 => p_attribute1
388 ,p_attribute2 => p_attribute2
389 ,p_attribute3 => p_attribute3
390 ,p_attribute4 => p_attribute4
391 ,p_attribute5 => p_attribute5
392 ,p_attribute6 => p_attribute6
393 ,p_attribute7 => p_attribute7
394 ,p_attribute8 => p_attribute8
395 ,p_attribute9 => p_attribute9
396 ,p_attribute10 => p_attribute10
397 ,p_attribute11 => p_attribute11
398 ,p_attribute12 => p_attribute12
399 ,p_attribute13 => p_attribute13
400 ,p_attribute14 => p_attribute14
401 ,p_attribute15 => p_attribute15
402 ,p_attribute16 => p_attribute16
403 ,p_attribute17 => p_attribute17
404 ,p_attribute18 => p_attribute18
405 ,p_attribute19 => p_attribute19
406 ,p_attribute20 => p_attribute20
407 ,p_attribute21 => p_attribute21
408 ,p_attribute22 => p_attribute22
409 ,p_attribute23 => p_attribute23
410 ,p_attribute24 => p_attribute24
411 ,p_attribute25 => p_attribute25
412 ,p_attribute26 => p_attribute26
413 ,p_attribute27 => p_attribute27
414 ,p_attribute28 => p_attribute28
415 ,p_attribute29 => p_attribute29
416 ,p_attribute30 => p_attribute30
417 ,p_per_information_category => p_per_information_category
418 ,p_per_information1 => p_per_information1
419 ,p_per_information2 => p_per_information2
420 ,p_per_information3 => p_per_information3
421 ,p_per_information4 => p_per_information4
422 ,p_per_information5 => p_per_information5
423 ,p_per_information6 => p_per_information6
424 ,p_per_information7 => p_per_information7
425 ,p_per_information8 => p_per_information8
426 ,p_per_information9 => p_per_information9
427 ,p_per_information10 => p_per_information10
428 ,p_per_information11 => p_per_information11
429 ,p_per_information12 => p_per_information12
430 ,p_per_information13 => p_per_information13
431 ,p_per_information14 => p_per_information14
432 ,p_per_information15 => p_per_information15
433 ,p_per_information16 => p_per_information16
434 ,p_per_information17 => p_per_information17
435 ,p_per_information18 => p_per_information18
436 ,p_per_information19 => p_per_information19
437 ,p_per_information20 => p_per_information20
438 ,p_per_information21 => p_per_information21
439 ,p_per_information22 => p_per_information22
440 ,p_per_information23 => p_per_information23
441 ,p_per_information24 => p_per_information24
442 ,p_per_information25 => p_per_information25
443 ,p_per_information26 => p_per_information26
444 ,p_per_information27 => p_per_information27
445 ,p_per_information28 => p_per_information28
446 ,p_per_information29 => p_per_information29
447 ,p_per_information30 => p_per_information30
448 ,p_date_of_death => l_date_of_death
449 ,p_background_check_status => p_background_check_status
450 ,p_background_date_check => p_background_date_check
451 ,p_blood_type => p_blood_type
452 ,p_correspondence_language => p_correspondence_language
453 ,p_fast_path_employee => p_fast_path_employee
454 ,p_fte_capacity => p_fte_capacity
455 ,p_honors => p_honors
456 ,p_internal_location => p_internal_location
457 ,p_last_medical_test_by => p_last_medical_test_by
458 ,p_last_medical_test_date => p_last_medical_test_date
459 ,p_mailstop => p_mailstop
460 ,p_office_number => p_office_number
461 ,p_on_military_service => p_on_military_service
462 ,p_pre_name_adjunct => p_pre_name_adjunct
463 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
464 ,p_projected_start_date => p_projected_start_date
465 ,p_resume_exists => p_resume_exists
466 ,p_resume_last_updated => p_resume_last_updated
467 ,p_second_passport_exists => p_second_passport_exists
468 ,p_student_status => p_student_status
469 ,p_work_schedule => p_work_schedule
470 ,p_suffix => p_suffix
471 ,p_benefit_group_id => p_benefit_group_id
472 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
473 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
474 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
475 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
476 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
477 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
478 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
479 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
480 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
481 ,p_uses_tobacco_flag => p_uses_tobacco_flag
482 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
483 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
484 ,p_original_date_of_hire => l_original_date_of_hire
485 ,p_adjusted_svc_date => l_adjusted_svc_date
486 ,p_town_of_birth => p_town_of_birth
487 ,p_region_of_birth => p_region_of_birth
488 ,p_country_of_birth => p_country_of_birth
489 ,p_global_person_id => p_global_person_id
490 ,p_party_id => p_party_id
491 );
492 exception
493 when hr_api.cannot_find_prog_unit then
494 hr_api.cannot_find_prog_unit_error
495 (p_module_name => 'CREATE_EMPLOYEE'
496 ,p_hook_type => 'BP'
497 );
498 --
499 -- End of API User Hook for the before hook of create_employee
500 --
501 end;
502 --
503 if g_debug then
504 hr_utility.set_location(l_proc, 20);
505 end if;
506 --
507 -- Truncate the time portion from all date parameters
508 -- which are passed in.
509 --
510 l_hire_date := trunc(p_hire_date);
511 --
512 -- Set the original hire date to sysdate if not passed in.
513
514 if (l_original_date_of_hire is null) THEN
515 l_original_date_of_hire := l_hire_date;
516 end if;
517
518 --
519 -- Validation in addition to Row Handlers
520 --
521 -- If the specified person type id is not null then check that it
522 -- corresponds to type 'EMP', is currently active and is in the correct
523 -- business group, otherwise set person type to the active default for EMP
524 -- in the current business group.
525 --
526 per_per_bus.chk_person_type
527 (p_person_type_id => l_person_type_id
528 ,p_business_group_id => p_business_group_id
529 ,p_expected_sys_type => 'EMP'
530 );
531 if g_debug then
532 hr_utility.set_location(l_proc, 30);
533 end if;
534 --
535 -- Initialise local variables as appropriate
536 --
537 l_applicant_number := null;
538 l_npw_number := null;
539 l_employee_number := p_employee_number;
540 --
541 -- PTU : Changes
542
543 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
544 (p_business_group_id,
545 'EMP');
546 -- PTU : End of Changes
547
548 -- Create the person details
549 --
550 per_per_ins.ins
551 (p_business_group_id => p_business_group_id
552 ,p_person_type_id => l_person_type_id1
553 ,p_last_name => p_last_name
554 ,p_start_date => l_hire_date
555 ,p_effective_date => l_hire_date
556 --
557 ,p_comments => p_per_comments
558 ,p_date_employee_data_verified => l_date_employee_data_verified
559 ,p_date_of_birth => l_date_of_birth
560 ,p_email_address => p_email_address
561 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
562 ,p_first_name => p_first_name
563 ,p_known_as => p_known_as
564 ,p_marital_status => p_marital_status
565 ,p_middle_names => p_middle_names
566 ,p_nationality => p_nationality
567 ,p_national_identifier => p_national_identifier
568 ,p_previous_last_name => p_previous_last_name
569 ,p_registered_disabled_flag => p_registered_disabled_flag
570 ,p_sex => p_sex
571 ,p_title => p_title
572 ,p_vendor_id => p_vendor_id
573 -- ,p_work_telephone => p_work_telephone -- Now Handled by Create_phone
574 ,p_attribute_category => p_attribute_category
575 ,p_attribute1 => p_attribute1
576 ,p_attribute2 => p_attribute2
577 ,p_attribute3 => p_attribute3
578 ,p_attribute4 => p_attribute4
579 ,p_attribute5 => p_attribute5
580 ,p_attribute6 => p_attribute6
581 ,p_attribute7 => p_attribute7
582 ,p_attribute8 => p_attribute8
583 ,p_attribute9 => p_attribute9
584 ,p_attribute10 => p_attribute10
585 ,p_attribute11 => p_attribute11
586 ,p_attribute12 => p_attribute12
587 ,p_attribute13 => p_attribute13
588 ,p_attribute14 => p_attribute14
589 ,p_attribute15 => p_attribute15
590 ,p_attribute16 => p_attribute16
591 ,p_attribute17 => p_attribute17
592 ,p_attribute18 => p_attribute18
593 ,p_attribute19 => p_attribute19
594 ,p_attribute20 => p_attribute20
595 ,p_attribute21 => p_attribute21
596 ,p_attribute22 => p_attribute22
597 ,p_attribute23 => p_attribute23
598 ,p_attribute24 => p_attribute24
599 ,p_attribute25 => p_attribute25
600 ,p_attribute26 => p_attribute26
601 ,p_attribute27 => p_attribute27
602 ,p_attribute28 => p_attribute28
603 ,p_attribute29 => p_attribute29
604 ,p_attribute30 => p_attribute30
605 ,p_per_information_category => p_per_information_category
606 ,p_per_information1 => p_per_information1
607 ,p_per_information2 => p_per_information2
608 ,p_per_information3 => p_per_information3
609 ,p_per_information4 => p_per_information4
610 ,p_per_information5 => p_per_information5
611 ,p_per_information6 => p_per_information6
612 ,p_per_information7 => p_per_information7
613 ,p_per_information8 => p_per_information8
614 ,p_per_information9 => p_per_information9
615 ,p_per_information10 => p_per_information10
616 ,p_per_information11 => p_per_information11
617 ,p_per_information12 => p_per_information12
618 ,p_per_information13 => p_per_information13
619 ,p_per_information14 => p_per_information14
620 ,p_per_information15 => p_per_information15
621 ,p_per_information16 => p_per_information16
622 ,p_per_information17 => p_per_information17
623 ,p_per_information18 => p_per_information18
624 ,p_per_information19 => p_per_information19
625 ,p_per_information20 => p_per_information20
626 ,p_per_information21 => p_per_information21
627 ,p_per_information22 => p_per_information22
628 ,p_per_information23 => p_per_information23
629 ,p_per_information24 => p_per_information24
630 ,p_per_information25 => p_per_information25
631 ,p_per_information26 => p_per_information26
632 ,p_per_information27 => p_per_information27
633 ,p_per_information28 => p_per_information28
634 ,p_per_information29 => p_per_information29
635 ,p_per_information30 => p_per_information30
636 ,p_date_of_death => l_date_of_death
637 ,p_background_check_status => p_background_check_status
638 ,p_background_date_check => p_background_date_check
639 ,p_blood_type => p_blood_type
640 ,p_correspondence_language => p_correspondence_language
641 ,p_fast_path_employee => p_fast_path_employee
642 ,p_fte_capacity => p_fte_capacity
643 ,p_honors => p_honors
644 ,p_internal_location => p_internal_location
645 ,p_last_medical_test_by => p_last_medical_test_by
646 ,p_last_medical_test_date => p_last_medical_test_date
647 ,p_mailstop => p_mailstop
648 ,p_office_number => p_office_number
649 ,p_on_military_service => p_on_military_service
650 ,p_pre_name_adjunct => p_pre_name_adjunct
651 ,p_projected_start_date => p_projected_start_date
652 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
653 ,p_resume_exists => p_resume_exists
654 ,p_resume_last_updated => p_resume_last_updated
655 ,p_second_passport_exists => p_second_passport_exists
656 ,p_student_status => p_student_status
657 ,p_work_schedule => p_work_schedule
658 ,p_suffix => p_suffix
659 ,p_benefit_group_id => p_benefit_group_id
660 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
661 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
662 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
663 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
664 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
665 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
666 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
667 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
668 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
669 ,p_uses_tobacco_flag => p_uses_tobacco_flag
670 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
671 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
672 ,p_original_date_of_hire => l_original_date_of_hire
673 ,p_town_of_birth => p_town_of_birth
674 ,p_region_of_birth => p_region_of_birth
675 ,p_country_of_birth => p_country_of_birth
676 ,p_global_person_id => p_global_person_id
677 ,p_party_id => p_party_id
678 ,p_validate => false
679 --
680 ,p_applicant_number => l_applicant_number
681 ,p_employee_number => p_employee_number
682
683 ,p_person_id => l_person_id
684 ,p_effective_start_date => l_per_effective_start_date
685 ,p_effective_end_date => l_per_effective_end_date
686 ,p_comment_id => l_per_comment_id
687 ,p_current_applicant_flag => l_discard_varchar2
688 ,p_current_emp_or_apl_flag => l_discard_varchar2
689 ,p_current_employee_flag => l_discard_varchar2
690 ,p_full_name => l_full_name
691 ,p_object_version_number => l_per_object_version_number
692 ,p_name_combination_warning => l_name_combination_warning
693 ,p_dob_null_warning => l_assign_payroll_warning
694 ,p_orig_hire_warning => l_orig_hire_warning
695 ,p_npw_number => l_npw_number
696 );
697 --
698 if g_debug then
699 hr_utility.set_location(l_proc, 40);
700 end if;
701 --
702 -- insert the person in to the security list
703 --
704 hr_security_internal.populate_new_person(p_business_group_id,l_person_id);
705 --
706 -- PTU : Following Code has been added
707
708 hr_per_type_usage_internal.maintain_person_type_usage
709 (p_effective_date => l_hire_date
710 ,p_person_id => l_person_id
711 ,p_person_type_id => l_person_type_id
712 );
713
714 -- PTU : End of changes
715
716 if g_debug then
717 hr_utility.set_location(l_proc, 50);
718 end if;
719 --
720 -- Create the period of service record
721 --
722 per_pds_ins.ins
723 (p_business_group_id => p_business_group_id
724 ,p_person_id => l_person_id
725 ,p_date_start => l_hire_date
726 ,p_effective_date => l_hire_date
727 ,p_adjusted_svc_date => l_adjusted_svc_date
728 --
729 ,p_validate => false
730 ,p_validate_df_flex => false
731 --
732 ,p_period_of_service_id => l_period_of_service_id
733 ,p_object_version_number => l_discard_number
734 );
735 if g_debug then
736 hr_utility.set_location(l_proc, 50);
737 end if;
738 --
739 -- Create the default primary employee assignment
740 --
741 hr_assignment_internal.create_default_emp_asg
742 (p_effective_date => l_hire_date
743 ,p_person_id => l_person_id
744 ,p_business_group_id => p_business_group_id
745 ,p_period_of_service_id => l_period_of_service_id
746 --
747 ,p_assignment_id => l_assignment_id
748 ,p_object_version_number => l_asg_object_version_number
749 ,p_assignment_sequence => l_assignment_sequence
750 ,p_assignment_number => l_assignment_number
751 );
752 if g_debug then
753 hr_utility.set_location(l_proc, 60);
754 end if;
755 --
756 -- Create a phone row using the newly created person as the parent row.
757 -- This phone row replaces the work_telephone column on the person.
758 --
759 if p_work_telephone is not null then
760 hr_phone_api.create_phone
761 (p_date_from => l_hire_date
762 ,p_date_to => null
763 ,p_phone_type => 'W1'
764 ,p_phone_number => p_work_telephone
765 ,p_parent_id => l_person_id
766 ,p_parent_table => 'PER_ALL_PEOPLE_F'
767 ,p_validate => FALSE
768 ,p_effective_date => l_hire_date
769 ,p_object_version_number => l_phn_object_version_number --out
770 ,p_phone_id => l_phone_id --out
771 );
772 end if;
773 --
774 --
775 -- Start of fix for bug 3684087
776 --
777 SELECT object_version_number
778 INTO l_per_object_Version_number
779 FROM per_all_people_f
780 WHERE person_id = l_person_id
781 And effective_start_Date = l_per_effective_start_date
782 and effective_end_Date = l_per_effective_end_date;
783 --
784 -- Start of fix for bug 3684087
785
786 --start changes for bug 6598795
787 hr_assignment.update_assgn_context_value (p_business_group_id,
788 l_person_id,
789 l_assignment_id,
790 l_hire_date);
791
792 SELECT object_version_number
793 INTO l_asg_object_Version_number
794 FROM per_all_assignments_f
795 WHERE business_group_id = p_business_group_id
796 and person_id = l_person_id
797 and assignment_id = l_assignment_id
798 and effective_start_Date = l_hire_date;
799 --end changes for bug 6598795
800 --
801 begin
802 --
803 -- Start of API User Hook for the after hook of create_employee
804 --
805 hr_employee_bk1.create_employee_a
806 (p_hire_date => l_hire_date
807 ,p_business_group_id => p_business_group_id
808 ,p_last_name => p_last_name
809 ,p_sex => p_sex
810 ,p_person_type_id => p_person_type_id
811 ,p_per_comments => p_per_comments
812 ,p_date_employee_data_verified => l_date_employee_data_verified
813 ,p_date_of_birth => l_date_of_birth
814 ,p_email_address => p_email_address
815 ,p_employee_number => p_employee_number
816 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
817 ,p_first_name => p_first_name
818 ,p_known_as => p_known_as
819 ,p_marital_status => p_marital_status
820 ,p_middle_names => p_middle_names
821 ,p_nationality => p_nationality
822 ,p_national_identifier => p_national_identifier
823 ,p_previous_last_name => p_previous_last_name
824 ,p_registered_disabled_flag => p_registered_disabled_flag
825 ,p_title => p_title
826 ,p_vendor_id => p_vendor_id
827 ,p_work_telephone => p_work_telephone
828 ,p_attribute_category => p_attribute_category
829 ,p_attribute1 => p_attribute1
830 ,p_attribute2 => p_attribute2
831 ,p_attribute3 => p_attribute3
832 ,p_attribute4 => p_attribute4
833 ,p_attribute5 => p_attribute5
834 ,p_attribute6 => p_attribute6
835 ,p_attribute7 => p_attribute7
836 ,p_attribute8 => p_attribute8
837 ,p_attribute9 => p_attribute9
838 ,p_attribute10 => p_attribute10
839 ,p_attribute11 => p_attribute11
840 ,p_attribute12 => p_attribute12
841 ,p_attribute13 => p_attribute13
842 ,p_attribute14 => p_attribute14
843 ,p_attribute15 => p_attribute15
844 ,p_attribute16 => p_attribute16
845 ,p_attribute17 => p_attribute17
846 ,p_attribute18 => p_attribute18
847 ,p_attribute19 => p_attribute19
848 ,p_attribute20 => p_attribute20
849 ,p_attribute21 => p_attribute21
850 ,p_attribute22 => p_attribute22
851 ,p_attribute23 => p_attribute23
852 ,p_attribute24 => p_attribute24
853 ,p_attribute25 => p_attribute25
854 ,p_attribute26 => p_attribute26
855 ,p_attribute27 => p_attribute27
856 ,p_attribute28 => p_attribute28
857 ,p_attribute29 => p_attribute29
858 ,p_attribute30 => p_attribute30
859 ,p_per_information_category => p_per_information_category
860 ,p_per_information1 => p_per_information1
861 ,p_per_information2 => p_per_information2
862 ,p_per_information3 => p_per_information3
863 ,p_per_information4 => p_per_information4
864 ,p_per_information5 => p_per_information5
865 ,p_per_information6 => p_per_information6
866 ,p_per_information7 => p_per_information7
867 ,p_per_information8 => p_per_information8
868 ,p_per_information9 => p_per_information9
869 ,p_per_information10 => p_per_information10
870 ,p_per_information11 => p_per_information11
871 ,p_per_information12 => p_per_information12
872 ,p_per_information13 => p_per_information13
873 ,p_per_information14 => p_per_information14
874 ,p_per_information15 => p_per_information15
875 ,p_per_information16 => p_per_information16
876 ,p_per_information17 => p_per_information17
877 ,p_per_information18 => p_per_information18
878 ,p_per_information19 => p_per_information19
879 ,p_per_information20 => p_per_information20
880 ,p_per_information21 => p_per_information21
881 ,p_per_information22 => p_per_information22
882 ,p_per_information23 => p_per_information23
883 ,p_per_information24 => p_per_information24
884 ,p_per_information25 => p_per_information25
885 ,p_per_information26 => p_per_information26
886 ,p_per_information27 => p_per_information27
887 ,p_per_information28 => p_per_information28
888 ,p_per_information29 => p_per_information29
889 ,p_per_information30 => p_per_information30
890 ,p_date_of_death => l_date_of_death
891 ,p_background_check_status => p_background_check_status
892 ,p_background_date_check => p_background_date_check
893 ,p_blood_type => p_blood_type
894 ,p_correspondence_language => p_correspondence_language
895 ,p_fast_path_employee => p_fast_path_employee
896 ,p_fte_capacity => p_fte_capacity
897 ,p_honors => p_honors
898 ,p_internal_location => p_internal_location
899 ,p_last_medical_test_by => p_last_medical_test_by
900 ,p_last_medical_test_date => p_last_medical_test_date
901 ,p_mailstop => p_mailstop
902 ,p_office_number => p_office_number
903 ,p_on_military_service => p_on_military_service
904 ,p_pre_name_adjunct => p_pre_name_adjunct
905 ,p_projected_start_date => p_projected_start_date
906 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
907 ,p_resume_exists => p_resume_exists
908 ,p_resume_last_updated => p_resume_last_updated
909 ,p_second_passport_exists => p_second_passport_exists
910 ,p_student_status => p_student_status
911 ,p_work_schedule => p_work_schedule
912 ,p_suffix => p_suffix
913 ,p_benefit_group_id => p_benefit_group_id
914 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
915 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
916 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
917 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
918 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
919 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
920 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
921 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
922 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
923 ,p_uses_tobacco_flag => p_uses_tobacco_flag
924 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
925 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
926 ,p_original_date_of_hire => l_original_date_of_hire
927 ,p_adjusted_svc_date => l_adjusted_svc_date
928 ,p_person_id => l_person_id
929 ,p_assignment_id => l_assignment_id
930 ,p_per_object_version_number => l_per_object_version_number
931 ,p_asg_object_version_number => l_asg_object_version_number
932 ,p_per_effective_start_date => l_per_effective_start_date
933 ,p_per_effective_end_date => l_per_effective_end_date
934 ,p_full_name => l_full_name
935 ,p_per_comment_id => l_per_comment_id
936 ,p_assignment_sequence => l_assignment_sequence
937 ,p_assignment_number => l_assignment_number
938 ,p_town_of_birth => p_town_of_birth
939 ,p_region_of_birth => p_region_of_birth
940 ,p_country_of_birth => p_country_of_birth
941 ,p_global_person_id => p_global_person_id
942 ,p_party_id => p_party_id
943 ,p_name_combination_warning => l_name_combination_warning
944 ,p_assign_payroll_warning => l_assign_payroll_warning
945 ,p_orig_hire_warning => l_orig_hire_warning
946 );
947 exception
948 when hr_api.cannot_find_prog_unit then
949 hr_api.cannot_find_prog_unit_error
950 (p_module_name => 'CREATE_EMPLOYEE'
951 ,p_hook_type => 'AP'
952 );
953 --
954 -- End of API User Hook for the after hook of create_employee
955 --
956 end;
957 --
958 -- Set all output arguments
959 --
960 p_person_id := l_person_id;
961 p_assignment_sequence := l_assignment_sequence;
962 p_assignment_number := l_assignment_number;
963 p_assignment_id := l_assignment_id;
964 p_per_object_version_number := l_per_object_version_number;
965 p_asg_object_version_number := l_asg_object_version_number;
966 p_per_effective_start_date := l_per_effective_start_date;
967 p_per_effective_end_date := l_per_effective_end_date;
968 p_full_name := l_full_name;
969 p_per_comment_id := l_per_comment_id;
970 p_name_combination_warning := l_name_combination_warning;
971 p_assign_payroll_warning := l_assign_payroll_warning;
972 p_orig_hire_warning := l_orig_hire_warning;
973 --
974 -- When in validation only mode raise the Validate_Enabled exception
975 --
976 if p_validate then
977 raise hr_api.validate_enabled;
978 end if;
979 --
980 if g_debug then
981 hr_utility.set_location(' Leaving:'||l_proc, 70);
982 end if;
983 exception
984 when hr_api.validate_enabled then
985 --
986 -- As the Validate_Enabled exception has been raised
987 -- we must rollback to the savepoint
988 --
989 ROLLBACK TO create_employee;
990 --
991 -- Only set output warning arguments
992 -- (Any key or derived arguments must be set to null
993 -- when validation only mode is being used.)
994 --
995 p_employee_number := l_employee_number;
996 p_person_id := null;
997 p_assignment_id := null;
998 p_per_object_version_number := null;
999 p_asg_object_version_number := null;
1000 p_per_effective_start_date := null;
1001 p_per_effective_end_date := null;
1002 p_full_name := null;
1003 p_per_comment_id := null;
1004 p_assignment_sequence := null;
1005 p_assignment_number := null;
1006 p_name_combination_warning := FALSE;
1007 p_assign_payroll_warning := FALSE;
1008 p_orig_hire_warning := FALSE;
1009 --
1010 when others then
1011 --
1012 -- A validation or unexpected error has occurred
1013 --
1014 -- Added as part of fix to bug 632479
1015 --
1016 ROLLBACK TO create_employee;
1017 --
1018 -- set in out parameters and set out parameters
1019 --
1020 p_employee_number := l_emp_num;
1021 p_person_id := null;
1022 p_assignment_id := null;
1023 p_per_object_version_number := null;
1024 p_asg_object_version_number := null;
1025 p_per_effective_start_date := null;
1026 p_per_effective_end_date := null;
1027 p_full_name := null;
1028 p_per_comment_id := null;
1029 p_assignment_sequence := null;
1030 p_assignment_number := null;
1031 p_name_combination_warning := FALSE;
1032 p_assign_payroll_warning := FALSE;
1033 p_orig_hire_warning := FALSE;
1034 raise;
1035 --
1036 -- End of fix.
1037 --
1038 end create_employee;
1039 -- --------------------------------------------------------------------------
1040 --
1041 -- Begin fix for bug 899720
1042 --
1043 -- overload procedure for create_employee
1044 --
1045 procedure create_employee
1046 (p_validate in boolean default false
1047 ,p_hire_date in date
1048 ,p_business_group_id in number
1049 ,p_last_name in varchar2
1050 ,p_sex in varchar2
1051 ,p_person_type_id in number default null
1052 ,p_per_comments in varchar2 default null
1053 ,p_date_employee_data_verified in date default null
1054 ,p_date_of_birth in date default null
1055 ,p_email_address in varchar2 default null
1056 ,p_employee_number in out nocopy varchar2
1057 ,p_expense_check_send_to_addres in varchar2 default null
1058 ,p_first_name in varchar2 default null
1059 ,p_known_as in varchar2 default null
1060 ,p_marital_status in varchar2 default null
1061 ,p_middle_names in varchar2 default null
1062 ,p_nationality in varchar2 default null
1063 ,p_national_identifier in varchar2 default null
1064 ,p_previous_last_name in varchar2 default null
1065 ,p_registered_disabled_flag in varchar2 default null
1066 ,p_title in varchar2 default null
1067 ,p_vendor_id in number default null
1068 ,p_work_telephone in varchar2 default null
1069 ,p_attribute_category in varchar2 default null
1070 ,p_attribute1 in varchar2 default null
1071 ,p_attribute2 in varchar2 default null
1072 ,p_attribute3 in varchar2 default null
1073 ,p_attribute4 in varchar2 default null
1074 ,p_attribute5 in varchar2 default null
1075 ,p_attribute6 in varchar2 default null
1076 ,p_attribute7 in varchar2 default null
1077 ,p_attribute8 in varchar2 default null
1078 ,p_attribute9 in varchar2 default null
1079 ,p_attribute10 in varchar2 default null
1080 ,p_attribute11 in varchar2 default null
1081 ,p_attribute12 in varchar2 default null
1082 ,p_attribute13 in varchar2 default null
1083 ,p_attribute14 in varchar2 default null
1084 ,p_attribute15 in varchar2 default null
1085 ,p_attribute16 in varchar2 default null
1086 ,p_attribute17 in varchar2 default null
1087 ,p_attribute18 in varchar2 default null
1088 ,p_attribute19 in varchar2 default null
1089 ,p_attribute20 in varchar2 default null
1090 ,p_attribute21 in varchar2 default null
1091 ,p_attribute22 in varchar2 default null
1092 ,p_attribute23 in varchar2 default null
1093 ,p_attribute24 in varchar2 default null
1094 ,p_attribute25 in varchar2 default null
1095 ,p_attribute26 in varchar2 default null
1096 ,p_attribute27 in varchar2 default null
1097 ,p_attribute28 in varchar2 default null
1098 ,p_attribute29 in varchar2 default null
1099 ,p_attribute30 in varchar2 default null
1100 ,p_per_information_category in varchar2 default null
1101 ,p_per_information1 in varchar2 default null
1102 ,p_per_information2 in varchar2 default null
1103 ,p_per_information3 in varchar2 default null
1104 ,p_per_information4 in varchar2 default null
1105 ,p_per_information5 in varchar2 default null
1106 ,p_per_information6 in varchar2 default null
1107 ,p_per_information7 in varchar2 default null
1108 ,p_per_information8 in varchar2 default null
1109 ,p_per_information9 in varchar2 default null
1110 ,p_per_information10 in varchar2 default null
1111 ,p_per_information11 in varchar2 default null
1112 ,p_per_information12 in varchar2 default null
1113 ,p_per_information13 in varchar2 default null
1114 ,p_per_information14 in varchar2 default null
1115 ,p_per_information15 in varchar2 default null
1116 ,p_per_information16 in varchar2 default null
1117 ,p_per_information17 in varchar2 default null
1118 ,p_per_information18 in varchar2 default null
1119 ,p_per_information19 in varchar2 default null
1120 ,p_per_information20 in varchar2 default null
1121 ,p_per_information21 in varchar2 default null
1122 ,p_per_information22 in varchar2 default null
1123 ,p_per_information23 in varchar2 default null
1124 ,p_per_information24 in varchar2 default null
1125 ,p_per_information25 in varchar2 default null
1126 ,p_per_information26 in varchar2 default null
1127 ,p_per_information27 in varchar2 default null
1128 ,p_per_information28 in varchar2 default null
1129 ,p_per_information29 in varchar2 default null
1130 ,p_per_information30 in varchar2 default null
1131 ,p_date_of_death in date default null
1132 ,p_background_check_status in varchar2 default null
1133 ,p_background_date_check in date default null
1134 ,p_blood_type in varchar2 default null
1135 ,p_correspondence_language in varchar2 default null
1136 ,p_fast_path_employee in varchar2 default null
1137 ,p_fte_capacity in number default null
1138 ,p_honors in varchar2 default null
1139 ,p_internal_location in varchar2 default null
1140 ,p_last_medical_test_by in varchar2 default null
1141 ,p_last_medical_test_date in date default null
1142 ,p_mailstop in varchar2 default null
1143 ,p_office_number in varchar2 default null
1144 ,p_on_military_service in varchar2 default null
1145 ,p_pre_name_adjunct in varchar2 default null
1146 ,p_rehire_recommendation in varchar2 default null -- Bug3210500
1147 ,p_projected_start_date in date default null
1148 ,p_resume_exists in varchar2 default null
1149 ,p_resume_last_updated in date default null
1150 ,p_second_passport_exists in varchar2 default null
1151 ,p_student_status in varchar2 default null
1152 ,p_work_schedule in varchar2 default null
1153 ,p_suffix in varchar2 default null
1154 ,p_benefit_group_id in number default null
1155 ,p_receipt_of_death_cert_date in date default null
1156 ,p_coord_ben_med_pln_no in varchar2 default null
1157 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
1158 ,p_coord_ben_med_ext_er in varchar2 default null
1159 ,p_coord_ben_med_pl_name in varchar2 default null
1160 ,p_coord_ben_med_insr_crr_name in varchar2 default null
1161 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
1162 ,p_coord_ben_med_cvg_strt_dt in date default null
1163 ,p_coord_ben_med_cvg_end_dt in date default null
1164 ,p_uses_tobacco_flag in varchar2 default null
1165 ,p_dpdnt_adoption_date in date default null
1166 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
1167 ,p_original_date_of_hire in date default null
1168 ,p_adjusted_svc_date in date default null
1169 ,p_town_of_birth in varchar2 default null
1170 ,p_region_of_birth in varchar2 default null
1171 ,p_country_of_birth in varchar2 default null
1172 ,p_global_person_id in varchar2 default null
1173 ,p_party_id in number default null
1174 ,p_person_id out nocopy number
1175 ,p_assignment_id out nocopy number
1176 ,p_per_object_version_number out nocopy number
1177 ,p_asg_object_version_number out nocopy number
1178 ,p_per_effective_start_date out nocopy date
1179 ,p_per_effective_end_date out nocopy date
1180 ,p_full_name out nocopy varchar2
1181 ,p_per_comment_id out nocopy number
1182 ,p_assignment_sequence out nocopy number
1183 ,p_assignment_number out nocopy varchar2
1184 ,p_name_combination_warning out nocopy boolean
1185 ,p_assign_payroll_warning out nocopy boolean
1186 )
1187 is
1188 --
1189 -- Declare cursors and local variables
1190 --
1191 l_proc varchar2(72);
1192 l_orig_hire_warning boolean := false;
1193 --
1194 begin
1195 --
1196 if g_debug then
1197 l_proc := g_package||'create_employee';
1198 hr_utility.set_location('Entering:'||l_proc,111);
1199 end if;
1200 --
1201 hr_employee_api.create_employee
1202 (p_validate => p_validate
1203 ,p_hire_date => p_hire_date
1204 ,p_business_group_id => p_business_group_id
1205 ,p_last_name => p_last_name
1206 ,p_sex => p_sex
1207 ,p_person_type_id => p_person_type_id
1208 ,p_per_comments => p_per_comments
1209 ,p_date_employee_data_verified => p_date_employee_data_verified
1210 ,p_date_of_birth => p_date_of_birth
1211 ,p_email_address => p_email_address
1212 ,p_employee_number => p_employee_number
1213 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1214 ,p_first_name => p_first_name
1215 ,p_known_as => p_known_as
1216 ,p_marital_status => p_marital_status
1217 ,p_middle_names => p_middle_names
1218 ,p_nationality => p_nationality
1219 ,p_national_identifier => p_national_identifier
1220 ,p_previous_last_name => p_previous_last_name
1221 ,p_registered_disabled_flag => p_registered_disabled_flag
1222 ,p_title => p_title
1223 ,p_vendor_id => p_vendor_id
1224 ,p_work_telephone => p_work_telephone
1225 ,p_attribute_category => p_attribute_category
1226 ,p_attribute1 => p_attribute1
1227 ,p_attribute2 => p_attribute2
1228 ,p_attribute3 => p_attribute3
1229 ,p_attribute4 => p_attribute4
1230 ,p_attribute5 => p_attribute5
1231 ,p_attribute6 => p_attribute6
1232 ,p_attribute7 => p_attribute7
1233 ,p_attribute8 => p_attribute8
1234 ,p_attribute9 => p_attribute9
1235 ,p_attribute10 => p_attribute10
1236 ,p_attribute11 => p_attribute11
1237 ,p_attribute12 => p_attribute12
1238 ,p_attribute13 => p_attribute13
1239 ,p_attribute14 => p_attribute14
1240 ,p_attribute15 => p_attribute15
1241 ,p_attribute16 => p_attribute16
1242 ,p_attribute17 => p_attribute17
1243 ,p_attribute18 => p_attribute18
1244 ,p_attribute19 => p_attribute19
1245 ,p_attribute20 => p_attribute20
1246 ,p_attribute21 => p_attribute21
1247 ,p_attribute22 => p_attribute22
1248 ,p_attribute23 => p_attribute23
1249 ,p_attribute24 => p_attribute24
1250 ,p_attribute25 => p_attribute25
1251 ,p_attribute26 => p_attribute26
1252 ,p_attribute27 => p_attribute27
1253 ,p_attribute28 => p_attribute28
1254 ,p_attribute29 => p_attribute29
1255 ,p_attribute30 => p_attribute30
1256 ,p_per_information_category => p_per_information_category
1257 ,p_per_information1 => p_per_information1
1258 ,p_per_information2 => p_per_information2
1259 ,p_per_information3 => p_per_information3
1260 ,p_per_information4 => p_per_information4
1261 ,p_per_information5 => p_per_information5
1262 ,p_per_information6 => p_per_information6
1263 ,p_per_information7 => p_per_information7
1264 ,p_per_information8 => p_per_information8
1265 ,p_per_information9 => p_per_information9
1266 ,p_per_information10 => p_per_information10
1267 ,p_per_information11 => p_per_information11
1268 ,p_per_information12 => p_per_information12
1269 ,p_per_information13 => p_per_information13
1270 ,p_per_information14 => p_per_information14
1271 ,p_per_information15 => p_per_information15
1272 ,p_per_information16 => p_per_information16
1273 ,p_per_information17 => p_per_information17
1274 ,p_per_information18 => p_per_information18
1275 ,p_per_information19 => p_per_information19
1276 ,p_per_information20 => p_per_information20
1277 ,p_per_information21 => p_per_information21
1278 ,p_per_information22 => p_per_information22
1279 ,p_per_information23 => p_per_information23
1280 ,p_per_information24 => p_per_information24
1281 ,p_per_information25 => p_per_information25
1282 ,p_per_information26 => p_per_information26
1283 ,p_per_information27 => p_per_information27
1284 ,p_per_information28 => p_per_information28
1285 ,p_per_information29 => p_per_information29
1286 ,p_per_information30 => p_per_information30
1287 ,p_date_of_death => p_date_of_death
1288 ,p_background_check_status => p_background_check_status
1289 ,p_background_date_check => p_background_date_check
1290 ,p_blood_type => p_blood_type
1291 ,p_correspondence_language => p_correspondence_language
1292 ,p_fast_path_employee => p_fast_path_employee
1293 ,p_fte_capacity => p_fte_capacity
1294 ,p_honors => p_honors
1295 ,p_internal_location => p_internal_location
1296 ,p_last_medical_test_by => p_last_medical_test_by
1297 ,p_last_medical_test_date => p_last_medical_test_date
1298 ,p_mailstop => p_mailstop
1299 ,p_office_number => p_office_number
1300 ,p_on_military_service => p_on_military_service
1301 ,p_pre_name_adjunct => p_pre_name_adjunct
1302 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
1303 ,p_projected_start_date => p_projected_start_date
1304 ,p_resume_exists => p_resume_exists
1305 ,p_resume_last_updated => p_resume_last_updated
1306 ,p_second_passport_exists => p_second_passport_exists
1307 ,p_student_status => p_student_status
1308 ,p_work_schedule => p_work_schedule
1309 ,p_suffix => p_suffix
1310 ,p_benefit_group_id => p_benefit_group_id
1311 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1312 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1313 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1314 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1315 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1316 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1317 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1318 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1319 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1320 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1321 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1322 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1323 ,p_original_date_of_hire => p_original_date_of_hire
1324 ,p_adjusted_svc_date => p_adjusted_svc_date
1325 ,p_town_of_birth => p_town_of_birth
1326 ,p_region_of_birth => p_region_of_birth
1327 ,p_country_of_birth => p_country_of_birth
1328 ,p_global_person_id => p_global_person_id
1329 ,p_party_id => p_party_id
1330 ,p_person_id => p_person_id
1331 ,p_assignment_id => p_assignment_id
1332 ,p_per_object_version_number => p_per_object_version_number
1333 ,p_asg_object_version_number => p_asg_object_version_number
1334 ,p_per_effective_start_date => p_per_effective_start_date
1335 ,p_per_effective_end_date => p_per_effective_end_date
1336 ,p_full_name => p_full_name
1337 ,p_per_comment_id => p_per_comment_id
1338 ,p_assignment_sequence => p_assignment_sequence
1339 ,p_assignment_number => p_assignment_number
1340 ,p_name_combination_warning => p_name_combination_warning
1341 ,p_assign_payroll_warning => p_assign_payroll_warning
1342 ,p_orig_hire_warning => l_orig_hire_warning
1343 );
1344 --
1345 if g_debug then
1346 hr_utility.set_location('Leaving:'||l_proc,111);
1347 end if;
1348 --
1349 end create_employee;
1350 --
1351 -- End of fix for bug 899720
1352 --
1353 -- ----------------------------------------------------------------------------
1354 -- |-------------------------< create_gb_employee >---------------------------|
1355 -- ----------------------------------------------------------------------------
1356 --
1357 procedure create_gb_employee
1358 (p_validate in boolean default false
1359 ,p_hire_date in date
1360 ,p_business_group_id in number
1361 ,p_last_name in varchar2
1362 ,p_sex in varchar2
1363 ,p_person_type_id in number default null
1364 ,p_comments in varchar2 default null
1365 ,p_date_employee_data_verified in date default null
1366 ,p_date_of_birth in date default null
1367 ,p_email_address in varchar2 default null
1368 ,p_employee_number in out nocopy varchar2
1369 ,p_expense_check_send_to_addres in varchar2 default null
1370 ,p_first_name in varchar2 default null
1371 ,p_known_as in varchar2 default null
1372 ,p_marital_status in varchar2 default null
1373 ,p_middle_names in varchar2 default null
1374 ,p_nationality in varchar2 default null
1375 ,p_ni_number in varchar2 default null
1376 ,p_previous_last_name in varchar2 default null
1377 ,p_registered_disabled_flag in varchar2 default null
1378 ,p_title in varchar2 default null
1379 ,p_vendor_id in number default null
1380 ,p_work_telephone in varchar2 default null
1381 ,p_attribute_category in varchar2 default null
1382 ,p_attribute1 in varchar2 default null
1383 ,p_attribute2 in varchar2 default null
1384 ,p_attribute3 in varchar2 default null
1385 ,p_attribute4 in varchar2 default null
1386 ,p_attribute5 in varchar2 default null
1387 ,p_attribute6 in varchar2 default null
1388 ,p_attribute7 in varchar2 default null
1389 ,p_attribute8 in varchar2 default null
1390 ,p_attribute9 in varchar2 default null
1391 ,p_attribute10 in varchar2 default null
1392 ,p_attribute11 in varchar2 default null
1393 ,p_attribute12 in varchar2 default null
1394 ,p_attribute13 in varchar2 default null
1395 ,p_attribute14 in varchar2 default null
1396 ,p_attribute15 in varchar2 default null
1397 ,p_attribute16 in varchar2 default null
1398 ,p_attribute17 in varchar2 default null
1399 ,p_attribute18 in varchar2 default null
1400 ,p_attribute19 in varchar2 default null
1401 ,p_attribute20 in varchar2 default null
1402 ,p_attribute21 in varchar2 default null
1403 ,p_attribute22 in varchar2 default null
1404 ,p_attribute23 in varchar2 default null
1405 ,p_attribute24 in varchar2 default null
1406 ,p_attribute25 in varchar2 default null
1407 ,p_attribute26 in varchar2 default null
1408 ,p_attribute27 in varchar2 default null
1409 ,p_attribute28 in varchar2 default null
1410 ,p_attribute29 in varchar2 default null
1411 ,p_attribute30 in varchar2 default null
1412 ,p_ethnic_origin in varchar2 default null
1413 ,p_director in varchar2 default 'N'
1414 ,p_pensioner in varchar2 default 'N'
1415 ,p_work_permit_number in varchar2 default null
1416 ,p_addl_pension_years in varchar2 default null
1417 ,p_addl_pension_months in varchar2 default null
1418 ,p_addl_pension_days in varchar2 default null
1419 ,p_ni_multiple_asg in varchar2 default 'N'
1420 ,p_date_of_death in date default null
1421 ,p_background_check_status in varchar2 default null
1422 ,p_background_date_check in date default null
1423 ,p_blood_type in varchar2 default null
1424 ,p_correspondence_language in varchar2 default null
1425 ,p_fast_path_employee in varchar2 default null
1426 ,p_fte_capacity in number default null
1427 ,p_honors in varchar2 default null
1428 ,p_internal_location in varchar2 default null
1429 ,p_last_medical_test_by in varchar2 default null
1430 ,p_last_medical_test_date in date default null
1431 ,p_mailstop in varchar2 default null
1432 ,p_office_number in varchar2 default null
1433 ,p_on_military_service in varchar2 default null
1434 ,p_pre_name_adjunct in varchar2 default null
1435 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
1436 ,p_projected_start_date in date default null
1437 ,p_resume_exists in varchar2 default null
1438 ,p_resume_last_updated in date default null
1439 ,p_second_passport_exists in varchar2 default null
1440 ,p_student_status in varchar2 default null
1441 ,p_work_schedule in varchar2 default null
1442 ,p_suffix in varchar2 default null
1443 ,p_benefit_group_id in number default null
1444 ,p_receipt_of_death_cert_date in date default null
1445 ,p_coord_ben_med_pln_no in varchar2 default null
1446 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
1447 ,p_coord_ben_med_ext_er in varchar2 default null
1448 ,p_coord_ben_med_pl_name in varchar2 default null
1449 ,p_coord_ben_med_insr_crr_name in varchar2 default null
1450 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
1451 ,p_coord_ben_med_cvg_strt_dt in date default null
1452 ,p_coord_ben_med_cvg_end_dt in date default null
1453 ,p_uses_tobacco_flag in varchar2 default null
1454 ,p_dpdnt_adoption_date in date default null
1455 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
1456 ,p_original_date_of_hire in date default null
1457 ,p_adjusted_svc_date in date default null
1458 ,p_town_of_birth in varchar2 default null
1459 ,p_region_of_birth in varchar2 default null
1460 ,p_country_of_birth in varchar2 default null
1461 ,p_global_person_id in varchar2 default null
1462 ,p_party_id in number default null
1463 ,p_person_id out nocopy number
1464 ,p_assignment_id out nocopy number
1465 ,p_per_object_version_number out nocopy number
1466 ,p_asg_object_version_number out nocopy number
1467 ,p_per_effective_start_date out nocopy date
1468 ,p_per_effective_end_date out nocopy date
1469 ,p_full_name out nocopy varchar2
1470 ,p_per_comment_id out nocopy number
1471 ,p_assignment_sequence out nocopy number
1472 ,p_assignment_number out nocopy varchar2
1473 ,p_name_combination_warning out nocopy boolean
1474 ,p_assign_payroll_warning out nocopy boolean
1475 ,p_orig_hire_warning out nocopy boolean
1476 ) is
1477 --
1478 -- Declare cursors and local variables
1479 --
1480 l_proc varchar2(72);
1481 l_legislation_code varchar2(2);
1482 l_hire_date date := trunc(p_hire_date);
1483 l_original_date_of_hire date := trunc(p_original_date_of_hire);
1484
1485 -- Used for GB session id
1486 l_session_c number(1):=2;
1487
1488 cursor csr_bg is
1489 select legislation_code
1490 from per_business_groups pbg
1491 where pbg.business_group_id = p_business_group_id;
1492 --
1493 begin
1494
1495 if g_debug then
1496 l_proc := g_package||'create_gb_employee';
1497 hr_utility.set_location('Entering:'|| l_proc, 5);
1498 end if;
1499
1500 begin
1501 select 1 into l_session_c from fnd_sessions
1502 where session_id=userenv('sessionid');
1503 exception
1504 when no_data_found then
1505 insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),p_hire_date);
1506 l_session_c:=0;
1507 end;
1508
1509 --
1510 -- Validation in addition to Row Handlers
1511 --
1512 -- Check that the specified business group is valid.
1513 --
1514 open csr_bg;
1515 fetch csr_bg
1516 into l_legislation_code;
1517 if csr_bg%notfound then
1518 close csr_bg;
1519 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1520 hr_utility.raise_error;
1521 end if;
1522 close csr_bg;
1523 --
1524 -- Check that the legislation of the specified business group is 'GB'.
1525 --
1526 if l_legislation_code <> 'GB' then
1527 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1528 hr_utility.set_message_token('LEG_CODE','GB');
1529 hr_utility.raise_error;
1530 end if;
1531
1532 if g_debug then
1533 hr_utility.set_location(l_proc, 6);
1534 end if;
1535 --
1536 -- set the original date of hire to hire date if null
1537 --
1538 if p_original_date_of_hire is null then
1539 l_original_date_of_hire := l_hire_date;
1540 end if;
1541 --
1542 -- Call the person business process
1543 --
1544 hr_employee_api.create_employee
1545 (p_validate => p_validate
1546 ,p_hire_date => p_hire_date
1547 ,p_business_group_id => p_business_group_id
1548 ,p_last_name => p_last_name
1549 ,p_sex => p_sex
1550 ,p_person_type_id => p_person_type_id
1551 ,p_per_comments => p_comments
1552 ,p_date_employee_data_verified => p_date_employee_data_verified
1553 ,p_date_of_birth => p_date_of_birth
1554 ,p_email_address => p_email_address
1555 ,p_employee_number => p_employee_number
1556 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1557 ,p_first_name => p_first_name
1558 ,p_known_as => p_known_as
1559 ,p_marital_status => p_marital_status
1560 ,p_middle_names => p_middle_names
1561 ,p_nationality => p_nationality
1562 ,p_national_identifier => p_ni_number
1563 ,p_previous_last_name => p_previous_last_name
1564 ,p_registered_disabled_flag => p_registered_disabled_flag
1565 ,p_title => p_title
1566 ,p_vendor_id => p_vendor_id
1567 ,p_work_telephone => p_work_telephone
1568 ,p_attribute_category => p_attribute_category
1569 ,p_attribute1 => p_attribute1
1570 ,p_attribute2 => p_attribute2
1571 ,p_attribute3 => p_attribute3
1572 ,p_attribute4 => p_attribute4
1573 ,p_attribute5 => p_attribute5
1574 ,p_attribute6 => p_attribute6
1575 ,p_attribute7 => p_attribute7
1576 ,p_attribute8 => p_attribute8
1577 ,p_attribute9 => p_attribute9
1578 ,p_attribute10 => p_attribute10
1579 ,p_attribute11 => p_attribute11
1580 ,p_attribute12 => p_attribute12
1581 ,p_attribute13 => p_attribute13
1582 ,p_attribute14 => p_attribute14
1583 ,p_attribute15 => p_attribute15
1584 ,p_attribute16 => p_attribute16
1585 ,p_attribute17 => p_attribute17
1586 ,p_attribute18 => p_attribute18
1587 ,p_attribute19 => p_attribute19
1588 ,p_attribute20 => p_attribute20
1589 ,p_attribute21 => p_attribute21
1590 ,p_attribute22 => p_attribute22
1591 ,p_attribute23 => p_attribute23
1592 ,p_attribute24 => p_attribute24
1593 ,p_attribute25 => p_attribute25
1594 ,p_attribute26 => p_attribute26
1595 ,p_attribute27 => p_attribute27
1596 ,p_attribute28 => p_attribute28
1597 ,p_attribute29 => p_attribute29
1598 ,p_attribute30 => p_attribute30
1599 ,p_per_information_category => 'GB'
1600 ,p_per_information1 => p_ethnic_origin
1601 ,p_per_information2 => p_director
1602 ,p_per_information4 => p_pensioner
1603 ,p_per_information5 => p_work_permit_number
1604 ,p_per_information6 => p_addl_pension_years
1605 ,p_per_information7 => p_addl_pension_months
1606 ,p_per_information8 => p_addl_pension_days
1607 ,p_per_information9 => p_ni_multiple_asg
1608 ,p_date_of_death => p_date_of_death
1609 ,p_background_check_status => p_background_check_status
1610 ,p_background_date_check => p_background_date_check
1611 ,p_blood_type => p_blood_type
1612 ,p_correspondence_language => p_correspondence_language
1613 ,p_fast_path_employee => p_fast_path_employee
1614 ,p_fte_capacity => p_fte_capacity
1615 ,p_honors => p_honors
1616 ,p_internal_location => p_internal_location
1617 ,p_last_medical_test_by => p_last_medical_test_by
1618 ,p_last_medical_test_date => p_last_medical_test_date
1619 ,p_mailstop => p_mailstop
1620 ,p_office_number => p_office_number
1621 ,p_on_military_service => p_on_military_service
1622 ,p_pre_name_adjunct => p_pre_name_adjunct
1623 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
1624 ,p_projected_start_date => p_projected_start_date
1625 ,p_resume_exists => p_resume_exists
1626 ,p_resume_last_updated => p_resume_last_updated
1627 ,p_second_passport_exists => p_second_passport_exists
1628 ,p_student_status => p_student_status
1629 ,p_work_schedule => p_work_schedule
1630 ,p_suffix => p_suffix
1631 ,p_benefit_group_id => p_benefit_group_id
1632 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1633 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1634 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1635 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1636 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1637 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1638 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1639 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1640 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1641 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1642 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1643 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1644 ,p_original_date_of_hire => l_original_date_of_hire
1645 ,p_adjusted_svc_date => p_adjusted_svc_date
1646 ,p_town_of_birth => p_town_of_birth
1647 ,p_region_of_birth => p_region_of_birth
1648 ,p_country_of_birth => p_country_of_birth
1649 ,p_global_person_id => p_global_person_id
1650 ,p_party_id => p_party_id
1651 --
1652 ,p_person_id => p_person_id
1653 ,p_assignment_id => p_assignment_id
1654 ,p_per_object_version_number => p_per_object_version_number
1655 ,p_asg_object_version_number => p_asg_object_version_number
1656 ,p_per_effective_start_date => p_per_effective_start_date
1657 ,p_per_effective_end_date => p_per_effective_end_date
1658 ,p_full_name => p_full_name
1659 ,p_per_comment_id => p_per_comment_id
1660 ,p_assignment_sequence => p_assignment_sequence
1661 ,p_assignment_number => p_assignment_number
1662 ,p_name_combination_warning => p_name_combination_warning
1663 ,p_assign_payroll_warning => p_assign_payroll_warning
1664 ,p_orig_hire_warning => p_orig_hire_warning
1665 );
1666 --
1667
1668 if(l_session_c = 0) then
1669 delete from fnd_sessions where session_id=userenv('sessionid');
1670 end if;
1671
1672 end create_gb_employee;
1673 -- --------------------------------------------------------------------------
1674 --
1675 -- Begin fix for bug 899720
1676 --
1677 -- overload procedure for create_gb_employee
1678 --
1679 procedure create_gb_employee
1680 (p_validate in boolean default false
1681 ,p_hire_date in date
1682 ,p_business_group_id in number
1683 ,p_last_name in varchar2
1684 ,p_sex in varchar2
1685 ,p_person_type_id in number default null
1686 ,p_comments in varchar2 default null
1687 ,p_date_employee_data_verified in date default null
1688 ,p_date_of_birth in date default null
1689 ,p_email_address in varchar2 default null
1690 ,p_employee_number in out nocopy varchar2
1691 ,p_expense_check_send_to_addres in varchar2 default null
1692 ,p_first_name in varchar2 default null
1693 ,p_known_as in varchar2 default null
1694 ,p_marital_status in varchar2 default null
1695 ,p_middle_names in varchar2 default null
1696 ,p_nationality in varchar2 default null
1697 ,p_ni_number in varchar2 default null
1698 ,p_previous_last_name in varchar2 default null
1699 ,p_registered_disabled_flag in varchar2 default null
1700 ,p_title in varchar2 default null
1701 ,p_vendor_id in number default null
1702 ,p_work_telephone in varchar2 default null
1703 ,p_attribute_category in varchar2 default null
1704 ,p_attribute1 in varchar2 default null
1705 ,p_attribute2 in varchar2 default null
1706 ,p_attribute3 in varchar2 default null
1707 ,p_attribute4 in varchar2 default null
1708 ,p_attribute5 in varchar2 default null
1709 ,p_attribute6 in varchar2 default null
1710 ,p_attribute7 in varchar2 default null
1711 ,p_attribute8 in varchar2 default null
1712 ,p_attribute9 in varchar2 default null
1713 ,p_attribute10 in varchar2 default null
1714 ,p_attribute11 in varchar2 default null
1715 ,p_attribute12 in varchar2 default null
1716 ,p_attribute13 in varchar2 default null
1717 ,p_attribute14 in varchar2 default null
1718 ,p_attribute15 in varchar2 default null
1719 ,p_attribute16 in varchar2 default null
1720 ,p_attribute17 in varchar2 default null
1721 ,p_attribute18 in varchar2 default null
1722 ,p_attribute19 in varchar2 default null
1723 ,p_attribute20 in varchar2 default null
1724 ,p_attribute21 in varchar2 default null
1725 ,p_attribute22 in varchar2 default null
1726 ,p_attribute23 in varchar2 default null
1727 ,p_attribute24 in varchar2 default null
1728 ,p_attribute25 in varchar2 default null
1729 ,p_attribute26 in varchar2 default null
1730 ,p_attribute27 in varchar2 default null
1731 ,p_attribute28 in varchar2 default null
1732 ,p_attribute29 in varchar2 default null
1733 ,p_attribute30 in varchar2 default null
1734 ,p_ethnic_origin in varchar2 default null
1735 ,p_director in varchar2 default 'N'
1736 ,p_pensioner in varchar2 default 'N'
1737 ,p_work_permit_number in varchar2 default null
1738 ,p_addl_pension_years in varchar2 default null
1739 ,p_addl_pension_months in varchar2 default null
1740 ,p_addl_pension_days in varchar2 default null
1741 ,p_ni_multiple_asg in varchar2 default 'N'
1742 ,p_date_of_death in date default null
1743 ,p_background_check_status in varchar2 default null
1744 ,p_background_date_check in date default null
1745 ,p_blood_type in varchar2 default null
1746 ,p_correspondence_language in varchar2 default null
1747 ,p_fast_path_employee in varchar2 default null
1748 ,p_fte_capacity in number default null
1749 ,p_honors in varchar2 default null
1750 ,p_internal_location in varchar2 default null
1751 ,p_last_medical_test_by in varchar2 default null
1752 ,p_last_medical_test_date in date default null
1753 ,p_mailstop in varchar2 default null
1754 ,p_office_number in varchar2 default null
1755 ,p_on_military_service in varchar2 default null
1756 ,p_pre_name_adjunct in varchar2 default null
1757 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
1758 ,p_projected_start_date in date default null
1759 ,p_resume_exists in varchar2 default null
1760 ,p_resume_last_updated in date default null
1761 ,p_second_passport_exists in varchar2 default null
1762 ,p_student_status in varchar2 default null
1763 ,p_work_schedule in varchar2 default null
1764 ,p_suffix in varchar2 default null
1765 ,p_benefit_group_id in number default null
1766 ,p_receipt_of_death_cert_date in date default null
1767 ,p_coord_ben_med_pln_no in varchar2 default null
1768 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
1769 ,p_coord_ben_med_ext_er in varchar2 default null
1770 ,p_coord_ben_med_pl_name in varchar2 default null
1771 ,p_coord_ben_med_insr_crr_name in varchar2 default null
1772 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
1773 ,p_coord_ben_med_cvg_strt_dt in date default null
1774 ,p_coord_ben_med_cvg_end_dt in date default null
1775 ,p_uses_tobacco_flag in varchar2 default null
1776 ,p_dpdnt_adoption_date in date default null
1777 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
1778 ,p_original_date_of_hire in date default null
1779 ,p_adjusted_svc_date in date default null
1780 ,p_town_of_birth in varchar2 default null
1781 ,p_region_of_birth in varchar2 default null
1782 ,p_country_of_birth in varchar2 default null
1783 ,p_global_person_id in varchar2 default null
1784 ,p_party_id in number default null
1785 ,p_person_id out nocopy number
1786 ,p_assignment_id out nocopy number
1787 ,p_per_object_version_number out nocopy number
1788 ,p_asg_object_version_number out nocopy number
1789 ,p_per_effective_start_date out nocopy date
1790 ,p_per_effective_end_date out nocopy date
1791 ,p_full_name out nocopy varchar2
1792 ,p_per_comment_id out nocopy number
1793 ,p_assignment_sequence out nocopy number
1794 ,p_assignment_number out nocopy varchar2
1795 ,p_name_combination_warning out nocopy boolean
1796 ,p_assign_payroll_warning out nocopy boolean
1797 )
1798 is
1799 --
1800 -- Declare cursors and local variables
1801 --
1802 l_proc varchar2(72);
1803 l_orig_hire_warning boolean := false;
1804 --
1805 begin
1806 --
1807 if g_debug then
1808 l_proc := g_package||'create_gb_employee';
1809 hr_utility.set_location('Entering:'||l_proc,222);
1810 end if;
1811 --
1812 hr_employee_api.create_gb_employee
1813 (p_validate => p_validate
1814 ,p_hire_date => p_hire_date
1815 ,p_business_group_id => p_business_group_id
1816 ,p_last_name => p_last_name
1817 ,p_sex => p_sex
1818 ,p_person_type_id => p_person_type_id
1819 ,p_comments => p_comments
1820 ,p_date_employee_data_verified => p_date_employee_data_verified
1821 ,p_date_of_birth => p_date_of_birth
1822 ,p_email_address => p_email_address
1823 ,p_employee_number => p_employee_number
1824 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1825 ,p_first_name => p_first_name
1826 ,p_known_as => p_known_as
1827 ,p_marital_status => p_marital_status
1828 ,p_middle_names => p_middle_names
1829 ,p_nationality => p_nationality
1830 ,p_ni_number => p_ni_number
1831 ,p_previous_last_name => p_previous_last_name
1832 ,p_registered_disabled_flag => p_registered_disabled_flag
1833 ,p_title => p_title
1834 ,p_vendor_id => p_vendor_id
1835 ,p_work_telephone => p_work_telephone
1836 ,p_attribute_category => p_attribute_category
1837 ,p_attribute1 => p_attribute1
1838 ,p_attribute2 => p_attribute2
1839 ,p_attribute3 => p_attribute3
1840 ,p_attribute4 => p_attribute4
1841 ,p_attribute5 => p_attribute5
1842 ,p_attribute6 => p_attribute6
1843 ,p_attribute7 => p_attribute7
1844 ,p_attribute8 => p_attribute8
1845 ,p_attribute9 => p_attribute9
1846 ,p_attribute10 => p_attribute10
1847 ,p_attribute11 => p_attribute11
1848 ,p_attribute12 => p_attribute12
1849 ,p_attribute13 => p_attribute13
1850 ,p_attribute14 => p_attribute14
1851 ,p_attribute15 => p_attribute15
1852 ,p_attribute16 => p_attribute16
1853 ,p_attribute17 => p_attribute17
1854 ,p_attribute18 => p_attribute18
1855 ,p_attribute19 => p_attribute19
1856 ,p_attribute20 => p_attribute20
1857 ,p_attribute21 => p_attribute21
1858 ,p_attribute22 => p_attribute22
1859 ,p_attribute23 => p_attribute23
1860 ,p_attribute24 => p_attribute24
1861 ,p_attribute25 => p_attribute25
1862 ,p_attribute26 => p_attribute26
1863 ,p_attribute27 => p_attribute27
1864 ,p_attribute28 => p_attribute28
1865 ,p_attribute29 => p_attribute29
1866 ,p_attribute30 => p_attribute30
1867 ,p_ethnic_origin => p_ethnic_origin
1868 ,p_director => p_director
1869 ,p_pensioner => p_pensioner
1870 ,p_work_permit_number => p_work_permit_number
1871 ,p_addl_pension_years => p_addl_pension_years
1872 ,p_addl_pension_months => p_addl_pension_months
1873 ,p_addl_pension_days => p_addl_pension_days
1874 ,p_ni_multiple_asg => p_ni_multiple_asg
1875 ,p_date_of_death => p_date_of_death
1876 ,p_background_check_status => p_background_check_status
1877 ,p_background_date_check => p_background_date_check
1878 ,p_blood_type => p_blood_type
1879 ,p_correspondence_language => p_correspondence_language
1880 ,p_fast_path_employee => p_fast_path_employee
1881 ,p_fte_capacity => p_fte_capacity
1882 ,p_honors => p_honors
1883 ,p_internal_location => p_internal_location
1884 ,p_last_medical_test_by => p_last_medical_test_by
1885 ,p_last_medical_test_date => p_last_medical_test_date
1886 ,p_mailstop => p_mailstop
1887 ,p_office_number => p_office_number
1888 ,p_on_military_service => p_on_military_service
1889 ,p_pre_name_adjunct => p_pre_name_adjunct
1890 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
1891 ,p_projected_start_date => p_projected_start_date
1892 ,p_resume_exists => p_resume_exists
1893 ,p_resume_last_updated => p_resume_last_updated
1894 ,p_second_passport_exists => p_second_passport_exists
1895 ,p_student_status => p_student_status
1896 ,p_work_schedule => p_work_schedule
1897 ,p_suffix => p_suffix
1898 ,p_benefit_group_id => p_benefit_group_id
1899 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1900 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1901 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1902 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1903 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1904 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1905 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1906 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1907 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1908 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1909 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1910 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1911 ,p_original_date_of_hire => p_original_date_of_hire
1912 ,p_adjusted_svc_date => p_adjusted_svc_date
1913 ,p_town_of_birth => p_town_of_birth
1914 ,p_region_of_birth => p_region_of_birth
1915 ,p_country_of_birth => p_country_of_birth
1916 ,p_global_person_id => p_global_person_id
1917 ,p_party_id => p_party_id
1918 ,p_person_id => p_person_id
1919 ,p_assignment_id => p_assignment_id
1920 ,p_per_object_version_number => p_per_object_version_number
1921 ,p_asg_object_version_number => p_asg_object_version_number
1922 ,p_per_effective_start_date => p_per_effective_start_date
1923 ,p_per_effective_end_date => p_per_effective_end_date
1924 ,p_full_name => p_full_name
1925 ,p_per_comment_id => p_per_comment_id
1926 ,p_assignment_sequence => p_assignment_sequence
1927 ,p_assignment_number => p_assignment_number
1928 ,p_name_combination_warning => p_name_combination_warning
1929 ,p_assign_payroll_warning => p_assign_payroll_warning
1930 ,p_orig_hire_warning => l_orig_hire_warning
1931 );
1932 --
1933 if g_debug then
1934 hr_utility.set_location('Leaving:'||l_proc,222);
1935 end if;
1936 --
1937 end create_gb_employee;
1938 --
1939 -- End of fix for bug 899720
1940 --
1941 -- ----------------------------------------------------------------------------
1942 -- |-------------------------< create_us_employee >---------------------------|
1943 -- ----------------------------------------------------------------------------
1944 --
1945 --
1946 -- Begin fix for bug 899720
1947 --
1948 -- overload procedure for create_us_employee
1949 --
1950 procedure create_us_employee
1951 (p_validate in boolean default false
1952 ,p_hire_date in date
1953 ,p_business_group_id in number
1954 ,p_last_name in varchar2
1955 ,p_sex in varchar2
1956 ,p_person_type_id in number default null
1957 ,p_comments in varchar2 default null
1958 ,p_date_employee_data_verified in date default null
1959 ,p_date_of_birth in date default null
1960 ,p_email_address in varchar2 default null
1961 ,p_employee_number in out nocopy varchar2
1962 ,p_expense_check_send_to_addres in varchar2 default null
1963 ,p_first_name in varchar2 default null
1964 ,p_known_as in varchar2 default null
1965 ,p_marital_status in varchar2 default null
1966 ,p_middle_names in varchar2 default null
1967 ,p_nationality in varchar2 default null
1968 ,p_ss_number in varchar2 default null
1969 ,p_previous_last_name in varchar2 default null
1970 ,p_registered_disabled_flag in varchar2 default null
1971 ,p_title in varchar2 default null
1972 ,p_vendor_id in number default null
1973 ,p_work_telephone in varchar2 default null
1974 ,p_attribute_category in varchar2 default null
1975 ,p_attribute1 in varchar2 default null
1976 ,p_attribute2 in varchar2 default null
1977 ,p_attribute3 in varchar2 default null
1978 ,p_attribute4 in varchar2 default null
1979 ,p_attribute5 in varchar2 default null
1980 ,p_attribute6 in varchar2 default null
1981 ,p_attribute7 in varchar2 default null
1982 ,p_attribute8 in varchar2 default null
1983 ,p_attribute9 in varchar2 default null
1984 ,p_attribute10 in varchar2 default null
1985 ,p_attribute11 in varchar2 default null
1986 ,p_attribute12 in varchar2 default null
1987 ,p_attribute13 in varchar2 default null
1988 ,p_attribute14 in varchar2 default null
1989 ,p_attribute15 in varchar2 default null
1990 ,p_attribute16 in varchar2 default null
1991 ,p_attribute17 in varchar2 default null
1992 ,p_attribute18 in varchar2 default null
1993 ,p_attribute19 in varchar2 default null
1994 ,p_attribute20 in varchar2 default null
1995 ,p_attribute21 in varchar2 default null
1996 ,p_attribute22 in varchar2 default null
1997 ,p_attribute23 in varchar2 default null
1998 ,p_attribute24 in varchar2 default null
1999 ,p_attribute25 in varchar2 default null
2000 ,p_attribute26 in varchar2 default null
2001 ,p_attribute27 in varchar2 default null
2002 ,p_attribute28 in varchar2 default null
2003 ,p_attribute29 in varchar2 default null
2004 ,p_attribute30 in varchar2 default null
2005 ,p_ethnic_origin in varchar2 default null
2006 ,p_I_9 in varchar2 default 'N'
2007 ,p_I_9_expiration_date in varchar2 default null
2008 -- ,p_visa_type in varchar2 default null
2009 ,p_veteran_status in varchar2 default null
2010 ,p_new_hire in varchar2 default null
2011 ,p_exception_reason in varchar2 default null
2012 ,p_child_support_obligation in varchar2 default 'N'
2013 ,p_opted_for_medicare_flag in varchar2 default 'N'
2014 ,p_date_of_death in date default null
2015 ,p_background_check_status in varchar2 default null
2016 ,p_background_date_check in date default null
2017 ,p_blood_type in varchar2 default null
2018 ,p_correspondence_language in varchar2 default null
2019 ,p_fast_path_employee in varchar2 default null
2020 ,p_fte_capacity in number default null
2021 ,p_honors in varchar2 default null
2022 ,p_internal_location in varchar2 default null
2023 ,p_last_medical_test_by in varchar2 default null
2024 ,p_last_medical_test_date in date default null
2025 ,p_mailstop in varchar2 default null
2026 ,p_office_number in varchar2 default null
2027 ,p_on_military_service in varchar2 default null
2028 ,p_pre_name_adjunct in varchar2 default null
2029 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
2030 ,p_projected_start_date in date default null
2031 ,p_resume_exists in varchar2 default null
2032 ,p_resume_last_updated in date default null
2033 ,p_second_passport_exists in varchar2 default null
2034 ,p_student_status in varchar2 default null
2035 ,p_work_schedule in varchar2 default null
2036 ,p_suffix in varchar2 default null
2037 ,p_benefit_group_id in number default null
2038 ,p_receipt_of_death_cert_date in date default null
2039 ,p_coord_ben_med_pln_no in varchar2 default null
2040 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
2041 ,p_coord_ben_med_ext_er in varchar2 default null
2042 ,p_coord_ben_med_pl_name in varchar2 default null
2043 ,p_coord_ben_med_insr_crr_name in varchar2 default null
2044 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
2045 ,p_coord_ben_med_cvg_strt_dt in date default null
2046 ,p_coord_ben_med_cvg_end_dt in date default null
2047 ,p_uses_tobacco_flag in varchar2 default null
2048 ,p_dpdnt_adoption_date in date default null
2049 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
2050 ,p_original_date_of_hire in date default null
2051 ,p_adjusted_svc_date in date default null
2052 ,p_town_of_birth in varchar2 default null
2053 ,p_region_of_birth in varchar2 default null
2054 ,p_country_of_birth in varchar2 default null
2055 ,p_global_person_id in varchar2 default null
2056 ,p_party_id in number default null
2057 ,p_person_id out nocopy number
2058 ,p_assignment_id out nocopy number
2059 ,p_per_object_version_number out nocopy number
2060 ,p_asg_object_version_number out nocopy number
2061 ,p_per_effective_start_date out nocopy date
2062 ,p_per_effective_end_date out nocopy date
2063 ,p_full_name out nocopy varchar2
2064 ,p_per_comment_id out nocopy number
2065 ,p_assignment_sequence out nocopy number
2066 ,p_assignment_number out nocopy varchar2
2067 ,p_name_combination_warning out nocopy boolean
2068 ,p_assign_payroll_warning out nocopy boolean
2069 )
2070 is
2071 --
2072 -- Declare cursors and local variables
2073 --
2074 l_proc varchar2(72) ;
2075 l_orig_hire_warning boolean := false;
2076 --
2077 begin
2078 --
2079 if g_debug then
2080 l_proc := g_package||'create_us_employee';
2081 hr_utility.set_location('Entering:'||l_proc,333);
2082 end if;
2083 --
2084
2085 hr_employee_api.create_us_employee
2086 (p_validate => p_validate
2087 ,p_hire_date => p_hire_date
2088 ,p_business_group_id => p_business_group_id
2089 ,p_last_name => p_last_name
2090 ,p_sex => p_sex
2091 ,p_person_type_id => p_person_type_id
2092 ,p_comments => p_comments
2093 ,p_date_employee_data_verified => p_date_employee_data_verified
2094 ,p_date_of_birth => p_date_of_birth
2095 ,p_email_address => p_email_address
2096 ,p_employee_number => p_employee_number
2097 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
2098 ,p_first_name => p_first_name
2099 ,p_known_as => p_known_as
2100 ,p_marital_status => p_marital_status
2101 ,p_middle_names => p_middle_names
2102 ,p_nationality => p_nationality
2103 ,p_ss_number => p_ss_number
2104 ,p_previous_last_name => p_previous_last_name
2105 ,p_registered_disabled_flag => p_registered_disabled_flag
2106 ,p_title => p_title
2107 ,p_vendor_id => p_vendor_id
2108 ,p_work_telephone => p_work_telephone
2109 ,p_attribute_category => p_attribute_category
2110 ,p_attribute1 => p_attribute1
2111 ,p_attribute2 => p_attribute2
2112 ,p_attribute3 => p_attribute3
2113 ,p_attribute4 => p_attribute4
2114 ,p_attribute5 => p_attribute5
2115 ,p_attribute6 => p_attribute6
2116 ,p_attribute7 => p_attribute7
2117 ,p_attribute8 => p_attribute8
2118 ,p_attribute9 => p_attribute9
2119 ,p_attribute10 => p_attribute10
2120 ,p_attribute11 => p_attribute11
2121 ,p_attribute12 => p_attribute12
2122 ,p_attribute13 => p_attribute13
2123 ,p_attribute14 => p_attribute14
2124 ,p_attribute15 => p_attribute15
2125 ,p_attribute16 => p_attribute16
2126 ,p_attribute17 => p_attribute17
2127 ,p_attribute18 => p_attribute18
2128 ,p_attribute19 => p_attribute19
2129 ,p_attribute20 => p_attribute20
2130 ,p_attribute21 => p_attribute21
2131 ,p_attribute22 => p_attribute22
2132 ,p_attribute23 => p_attribute23
2133 ,p_attribute24 => p_attribute24
2134 ,p_attribute25 => p_attribute25
2135 ,p_attribute26 => p_attribute26
2136 ,p_attribute27 => p_attribute27
2137 ,p_attribute28 => p_attribute28
2138 ,p_attribute29 => p_attribute29
2139 ,p_attribute30 => p_attribute30
2140 ,p_ethnic_origin => p_ethnic_origin
2141 ,p_I_9 => p_I_9
2142 ,p_I_9_expiration_date => p_I_9_expiration_date
2143 -- ,p_visa_type => p_visa_type
2144 ,p_veteran_status => p_veteran_status
2145 ,p_new_hire => p_new_hire
2146 ,p_exception_reason => p_exception_reason
2147 ,p_child_support_obligation => p_child_support_obligation
2148 ,p_opted_for_medicare_flag => p_opted_for_medicare_flag
2149 ,p_date_of_death => p_date_of_death
2150 ,p_background_check_status => p_background_check_status
2151 ,p_background_date_check => p_background_date_check
2152 ,p_blood_type => p_blood_type
2153 ,p_correspondence_language => p_correspondence_language
2154 ,p_fast_path_employee => p_fast_path_employee
2155 ,p_fte_capacity => p_fte_capacity
2156 ,p_honors => p_honors
2157 ,p_internal_location => p_internal_location
2158 ,p_last_medical_test_by => p_last_medical_test_by
2159 ,p_last_medical_test_date => p_last_medical_test_date
2160 ,p_mailstop => p_mailstop
2161 ,p_office_number => p_office_number
2162 ,p_on_military_service => p_on_military_service
2163 ,p_pre_name_adjunct => p_pre_name_adjunct
2164 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
2165 ,p_projected_start_date => p_projected_start_date
2166 ,p_resume_exists => p_resume_exists
2167 ,p_resume_last_updated => p_resume_last_updated
2168 ,p_second_passport_exists => p_second_passport_exists
2169 ,p_student_status => p_student_status
2170 ,p_work_schedule => p_work_schedule
2171 ,p_suffix => p_suffix
2172 ,p_benefit_group_id => p_benefit_group_id
2173 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
2174 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
2175 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
2176 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
2177 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
2178 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
2179 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
2180 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
2181 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
2182 ,p_uses_tobacco_flag => p_uses_tobacco_flag
2183 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
2184 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
2185 ,p_original_date_of_hire => p_original_date_of_hire
2186 ,p_adjusted_svc_date => p_adjusted_svc_date
2187 ,p_town_of_birth => p_town_of_birth
2188 ,p_region_of_birth => p_region_of_birth
2189 ,p_country_of_birth => p_country_of_birth
2190 ,p_global_person_id => p_global_person_id
2191 ,p_party_id => p_party_id
2192 ,p_person_id => p_person_id
2193 ,p_assignment_id => p_assignment_id
2194 ,p_per_object_version_number => p_per_object_version_number
2195 ,p_asg_object_version_number => p_asg_object_version_number
2196 ,p_per_effective_start_date => p_per_effective_start_date
2197 ,p_per_effective_end_date => p_per_effective_end_date
2198 ,p_full_name => p_full_name
2199 ,p_per_comment_id => p_per_comment_id
2200 ,p_assignment_sequence => p_assignment_sequence
2201 ,p_assignment_number => p_assignment_number
2202 ,p_name_combination_warning => p_name_combination_warning
2203 ,p_assign_payroll_warning => p_assign_payroll_warning
2204 ,p_orig_hire_warning => l_orig_hire_warning
2205 );
2206 --
2207 if g_debug then
2208 hr_utility.set_location('Leaving:'||l_proc,333);
2209 end if;
2210 --
2211 end create_us_employee;
2212 --
2213 -- End of fix for bug 899720
2214 --
2215
2216
2217 procedure create_us_employee
2218 (p_validate in boolean default false
2219 ,p_hire_date in date
2220 ,p_business_group_id in number
2221 ,p_last_name in varchar2
2222 ,p_sex in varchar2
2223 ,p_person_type_id in number default null
2224 ,p_comments in varchar2 default null
2225 ,p_date_employee_data_verified in date default null
2226 ,p_date_of_birth in date default null
2227 ,p_email_address in varchar2 default null
2228 ,p_employee_number in out nocopy varchar2
2229 ,p_expense_check_send_to_addres in varchar2 default null
2230 ,p_first_name in varchar2 default null
2231 ,p_known_as in varchar2 default null
2232 ,p_marital_status in varchar2 default null
2233 ,p_middle_names in varchar2 default null
2234 ,p_nationality in varchar2 default null
2235 ,p_ss_number in varchar2 default null
2236 ,p_previous_last_name in varchar2 default null
2237 ,p_registered_disabled_flag in varchar2 default null
2238 ,p_title in varchar2 default null
2239 ,p_vendor_id in number default null
2240 ,p_work_telephone in varchar2 default null
2241 ,p_attribute_category in varchar2 default null
2242 ,p_attribute1 in varchar2 default null
2243 ,p_attribute2 in varchar2 default null
2244 ,p_attribute3 in varchar2 default null
2245 ,p_attribute4 in varchar2 default null
2246 ,p_attribute5 in varchar2 default null
2247 ,p_attribute6 in varchar2 default null
2248 ,p_attribute7 in varchar2 default null
2249 ,p_attribute8 in varchar2 default null
2250 ,p_attribute9 in varchar2 default null
2251 ,p_attribute10 in varchar2 default null
2252 ,p_attribute11 in varchar2 default null
2253 ,p_attribute12 in varchar2 default null
2254 ,p_attribute13 in varchar2 default null
2255 ,p_attribute14 in varchar2 default null
2256 ,p_attribute15 in varchar2 default null
2257 ,p_attribute16 in varchar2 default null
2258 ,p_attribute17 in varchar2 default null
2259 ,p_attribute18 in varchar2 default null
2260 ,p_attribute19 in varchar2 default null
2261 ,p_attribute20 in varchar2 default null
2262 ,p_attribute21 in varchar2 default null
2263 ,p_attribute22 in varchar2 default null
2264 ,p_attribute23 in varchar2 default null
2265 ,p_attribute24 in varchar2 default null
2266 ,p_attribute25 in varchar2 default null
2267 ,p_attribute26 in varchar2 default null
2268 ,p_attribute27 in varchar2 default null
2269 ,p_attribute28 in varchar2 default null
2270 ,p_attribute29 in varchar2 default null
2271 ,p_attribute30 in varchar2 default null
2272 ,p_ethnic_origin in varchar2 default null
2273 ,p_I_9 in varchar2 default 'N'
2274 ,p_I_9_expiration_date in varchar2 default null
2275 -- ,p_visa_type in varchar2 default null
2276 ,p_veteran_status in varchar2 default null
2277 ,p_new_hire in varchar2 default null
2278 ,p_exception_reason in varchar2 default null
2279 ,p_child_support_obligation in varchar2 default 'N'
2280 ,p_opted_for_medicare_flag in varchar2 default 'N'
2281 ,p_date_of_death in date default null
2282 ,p_background_check_status in varchar2 default null
2283 ,p_background_date_check in date default null
2284 ,p_blood_type in varchar2 default null
2285 ,p_correspondence_language in varchar2 default null
2286 ,p_fast_path_employee in varchar2 default null
2287 ,p_fte_capacity in number default null
2288 ,p_honors in varchar2 default null
2289 ,p_internal_location in varchar2 default null
2290 ,p_last_medical_test_by in varchar2 default null
2291 ,p_last_medical_test_date in date default null
2292 ,p_mailstop in varchar2 default null
2293 ,p_office_number in varchar2 default null
2294 ,p_on_military_service in varchar2 default null
2295 ,p_pre_name_adjunct in varchar2 default null
2296 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
2297 ,p_projected_start_date in date default null
2298 ,p_resume_exists in varchar2 default null
2299 ,p_resume_last_updated in date default null
2300 ,p_second_passport_exists in varchar2 default null
2301 ,p_student_status in varchar2 default null
2302 ,p_work_schedule in varchar2 default null
2303 ,p_suffix in varchar2 default null
2304 ,p_benefit_group_id in number default null
2305 ,p_receipt_of_death_cert_date in date default null
2306 ,p_coord_ben_med_pln_no in varchar2 default null
2307 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
2308 ,p_coord_ben_med_ext_er in varchar2 default null
2309 ,p_coord_ben_med_pl_name in varchar2 default null
2310 ,p_coord_ben_med_insr_crr_name in varchar2 default null
2311 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
2312 ,p_coord_ben_med_cvg_strt_dt in date default null
2313 ,p_coord_ben_med_cvg_end_dt in date default null
2314 ,p_uses_tobacco_flag in varchar2 default null
2315 ,p_dpdnt_adoption_date in date default null
2316 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
2317 ,p_original_date_of_hire in date default null
2318 ,p_adjusted_svc_date in date default null
2319 ,p_town_of_birth in varchar2 default null
2320 ,p_region_of_birth in varchar2 default null
2321 ,p_country_of_birth in varchar2 default null
2322 ,p_global_person_id in varchar2 default null
2323 ,p_party_id in number default null
2324 ,p_person_id out nocopy number
2325 ,p_assignment_id out nocopy number
2326 ,p_per_object_version_number out nocopy number
2327 ,p_asg_object_version_number out nocopy number
2328 ,p_per_effective_start_date out nocopy date
2329 ,p_per_effective_end_date out nocopy date
2330 ,p_full_name out nocopy varchar2
2331 ,p_per_comment_id out nocopy number
2332 ,p_assignment_sequence out nocopy number
2333 ,p_assignment_number out nocopy varchar2
2334 ,p_name_combination_warning out nocopy boolean
2335 ,p_assign_payroll_warning out nocopy boolean
2336 ,p_orig_hire_warning out nocopy boolean
2337 )
2338 is
2339
2340 l_vets100A varchar2(100);
2341 --
2342 -- Declare cursors and local variables
2343 --
2344 /* l_proc varchar2(72) ;
2345 l_legislation_code varchar2(2);
2346 l_asg_object_version_number number(9);
2347 --
2348 cursor csr_bg is
2349 select legislation_code
2350 from per_business_groups pbg
2351 where pbg.business_group_id = p_business_group_id;
2352 */
2353 --
2354 begin
2355 --
2356 -- Validation in addition to Row Handlers
2357 --
2358 -- Check that the specified business group is valid.
2359 --
2360 /* open csr_bg;
2361 fetch csr_bg
2362 into l_legislation_code;
2363 if csr_bg%notfound then
2364 close csr_bg;
2365 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2366 hr_utility.raise_error;
2367 end if;
2368 close csr_bg;
2369 if g_debug then
2370 l_proc := g_package||'create_us_employee';
2371 hr_utility.set_location(l_proc, 20);
2372 end if;
2373 --
2374 -- Check that the legislation of the specified business group is 'US'.
2375 --
2376 if l_legislation_code <> 'US' then
2377 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2378 hr_utility.set_message_token('LEG_CODE','US');
2379 hr_utility.raise_error;
2380 end if;
2381 if g_debug then
2382 hr_utility.set_location(l_proc, 30);
2383 end if;
2384 */
2385 --
2386 -- Call the person business process
2387 --
2388
2389
2390 hr_employee_api.create_us_employee
2391 (p_validate => p_validate
2392 ,p_hire_date => p_hire_date
2393 ,p_business_group_id => p_business_group_id
2394 ,p_last_name => p_last_name
2395 ,p_sex => p_sex
2396 ,p_person_type_id => p_person_type_id
2397 ,p_comments => p_comments
2398 ,p_date_employee_data_verified => p_date_employee_data_verified
2399 ,p_date_of_birth => p_date_of_birth
2400 ,p_email_address => p_email_address
2401 ,p_employee_number => p_employee_number
2402 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
2403 ,p_first_name => p_first_name
2404 ,p_known_as => p_known_as
2405 ,p_marital_status => p_marital_status
2406 ,p_middle_names => p_middle_names
2407 ,p_nationality => p_nationality
2408 ,p_ss_number => p_ss_number
2409 ,p_previous_last_name => p_previous_last_name
2410 ,p_registered_disabled_flag => p_registered_disabled_flag
2411 ,p_title => p_title
2412 ,p_vendor_id => p_vendor_id
2413 ,p_work_telephone => p_work_telephone
2414 ,p_attribute_category => p_attribute_category
2415 ,p_attribute1 => p_attribute1
2416 ,p_attribute2 => p_attribute2
2417 ,p_attribute3 => p_attribute3
2418 ,p_attribute4 => p_attribute4
2419 ,p_attribute5 => p_attribute5
2420 ,p_attribute6 => p_attribute6
2421 ,p_attribute7 => p_attribute7
2422 ,p_attribute8 => p_attribute8
2423 ,p_attribute9 => p_attribute9
2424 ,p_attribute10 => p_attribute10
2425 ,p_attribute11 => p_attribute11
2426 ,p_attribute12 => p_attribute12
2427 ,p_attribute13 => p_attribute13
2428 ,p_attribute14 => p_attribute14
2429 ,p_attribute15 => p_attribute15
2430 ,p_attribute16 => p_attribute16
2431 ,p_attribute17 => p_attribute17
2432 ,p_attribute18 => p_attribute18
2433 ,p_attribute19 => p_attribute19
2434 ,p_attribute20 => p_attribute20
2435 ,p_attribute21 => p_attribute21
2436 ,p_attribute22 => p_attribute22
2437 ,p_attribute23 => p_attribute23
2438 ,p_attribute24 => p_attribute24
2439 ,p_attribute25 => p_attribute25
2440 ,p_attribute26 => p_attribute26
2441 ,p_attribute27 => p_attribute27
2442 ,p_attribute28 => p_attribute28
2443 ,p_attribute29 => p_attribute29
2444 ,p_attribute30 => p_attribute30
2445 -- ,p_per_information_category => 'US'
2446 ,p_ethnic_origin => p_ethnic_origin
2447 ,p_I_9 => p_I_9
2448 ,p_I_9_expiration_date => p_I_9_expiration_date
2449 -- ,p_visa_type => p_visa_type
2450 ,p_veteran_status => p_veteran_status
2451 ,p_vets100A => l_vets100A
2452 ,p_new_hire => p_new_hire
2453 ,p_exception_reason => p_exception_reason
2454 ,p_child_support_obligation => p_child_support_obligation
2455 ,p_opted_for_medicare_flag => p_opted_for_medicare_flag
2456 ,p_date_of_death => p_date_of_death
2457 ,p_background_check_status => p_background_check_status
2458 ,p_background_date_check => p_background_date_check
2459 ,p_blood_type => p_blood_type
2460 ,p_correspondence_language => p_correspondence_language
2461 ,p_fast_path_employee => p_fast_path_employee
2462 ,p_fte_capacity => p_fte_capacity
2463 ,p_honors => p_honors
2464 ,p_internal_location => p_internal_location
2465 ,p_last_medical_test_by => p_last_medical_test_by
2466 ,p_last_medical_test_date => p_last_medical_test_date
2467 ,p_mailstop => p_mailstop
2468 ,p_office_number => p_office_number
2469 ,p_on_military_service => p_on_military_service
2470 ,p_pre_name_adjunct => p_pre_name_adjunct
2471 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
2472 ,p_projected_start_date => p_projected_start_date
2473 ,p_resume_exists => p_resume_exists
2474 ,p_resume_last_updated => p_resume_last_updated
2475 ,p_second_passport_exists => p_second_passport_exists
2476 ,p_student_status => p_student_status
2477 ,p_work_schedule => p_work_schedule
2478 ,p_suffix => p_suffix
2479 ,p_benefit_group_id => p_benefit_group_id
2480 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
2481 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
2482 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
2483 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
2484 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
2485 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
2486 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
2487 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
2488 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
2489 ,p_uses_tobacco_flag => p_uses_tobacco_flag
2490 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
2491 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
2492 ,p_original_date_of_hire => p_original_date_of_hire
2493 ,p_adjusted_svc_date => p_adjusted_svc_date
2494 ,p_town_of_birth => p_town_of_birth
2495 ,p_region_of_birth => p_region_of_birth
2496 ,p_country_of_birth => p_country_of_birth
2497 ,p_global_person_id => p_global_person_id
2498 ,p_party_id => p_party_id
2499 ,p_person_id => p_person_id
2500 ,p_assignment_id => p_assignment_id
2501 ,p_per_object_version_number => p_per_object_version_number
2502 ,p_asg_object_version_number => p_asg_object_version_number
2503 ,p_per_effective_start_date => p_per_effective_start_date
2504 ,p_per_effective_end_date => p_per_effective_end_date
2505 ,p_full_name => p_full_name
2506 ,p_per_comment_id => p_per_comment_id
2507 ,p_assignment_sequence => p_assignment_sequence
2508 ,p_assignment_number => p_assignment_number
2509 ,p_name_combination_warning => p_name_combination_warning
2510 ,p_assign_payroll_warning => p_assign_payroll_warning
2511 ,p_orig_hire_warning => p_orig_hire_warning
2512 );
2513 --
2514 /* if g_debug then
2515 hr_utility.set_location(' Leaving:'||l_proc, 40);
2516 end if;
2517 */
2518 end create_us_employee;
2519
2520 -- Bug 8277596.
2521
2522 procedure create_us_employee
2523 (p_validate in boolean default false
2524 ,p_hire_date in date
2525 ,p_business_group_id in number
2526 ,p_last_name in varchar2
2527 ,p_sex in varchar2
2528 ,p_person_type_id in number default null
2529 ,p_comments in varchar2 default null
2530 ,p_date_employee_data_verified in date default null
2531 ,p_date_of_birth in date default null
2532 ,p_email_address in varchar2 default null
2533 ,p_employee_number in out nocopy varchar2
2534 ,p_expense_check_send_to_addres in varchar2 default null
2535 ,p_first_name in varchar2 default null
2536 ,p_known_as in varchar2 default null
2537 ,p_marital_status in varchar2 default null
2538 ,p_middle_names in varchar2 default null
2539 ,p_nationality in varchar2 default null
2540 ,p_ss_number in varchar2 default null
2541 ,p_previous_last_name in varchar2 default null
2542 ,p_registered_disabled_flag in varchar2 default null
2543 ,p_title in varchar2 default null
2544 ,p_vendor_id in number default null
2545 ,p_work_telephone in varchar2 default null
2546 ,p_attribute_category in varchar2 default null
2547 ,p_attribute1 in varchar2 default null
2548 ,p_attribute2 in varchar2 default null
2549 ,p_attribute3 in varchar2 default null
2550 ,p_attribute4 in varchar2 default null
2551 ,p_attribute5 in varchar2 default null
2552 ,p_attribute6 in varchar2 default null
2553 ,p_attribute7 in varchar2 default null
2554 ,p_attribute8 in varchar2 default null
2555 ,p_attribute9 in varchar2 default null
2556 ,p_attribute10 in varchar2 default null
2557 ,p_attribute11 in varchar2 default null
2558 ,p_attribute12 in varchar2 default null
2559 ,p_attribute13 in varchar2 default null
2560 ,p_attribute14 in varchar2 default null
2561 ,p_attribute15 in varchar2 default null
2562 ,p_attribute16 in varchar2 default null
2563 ,p_attribute17 in varchar2 default null
2564 ,p_attribute18 in varchar2 default null
2565 ,p_attribute19 in varchar2 default null
2566 ,p_attribute20 in varchar2 default null
2567 ,p_attribute21 in varchar2 default null
2568 ,p_attribute22 in varchar2 default null
2569 ,p_attribute23 in varchar2 default null
2570 ,p_attribute24 in varchar2 default null
2571 ,p_attribute25 in varchar2 default null
2572 ,p_attribute26 in varchar2 default null
2573 ,p_attribute27 in varchar2 default null
2574 ,p_attribute28 in varchar2 default null
2575 ,p_attribute29 in varchar2 default null
2576 ,p_attribute30 in varchar2 default null
2577 ,p_ethnic_origin in varchar2 default null
2578 ,p_I_9 in varchar2 default 'N'
2579 ,p_I_9_expiration_date in varchar2 default null
2580 -- ,p_visa_type in varchar2 default null
2581 ,p_veteran_status in varchar2 default null
2582 ,p_vets100A in varchar2
2583 ,p_new_hire in varchar2 default null
2584 ,p_exception_reason in varchar2 default null
2585 ,p_child_support_obligation in varchar2 default 'N'
2586 ,p_opted_for_medicare_flag in varchar2 default 'N'
2587 ,p_date_of_death in date default null
2588 ,p_background_check_status in varchar2 default null
2589 ,p_background_date_check in date default null
2590 ,p_blood_type in varchar2 default null
2591 ,p_correspondence_language in varchar2 default null
2592 ,p_fast_path_employee in varchar2 default null
2593 ,p_fte_capacity in number default null
2594 ,p_honors in varchar2 default null
2595 ,p_internal_location in varchar2 default null
2596 ,p_last_medical_test_by in varchar2 default null
2597 ,p_last_medical_test_date in date default null
2598 ,p_mailstop in varchar2 default null
2599 ,p_office_number in varchar2 default null
2600 ,p_on_military_service in varchar2 default null
2601 ,p_pre_name_adjunct in varchar2 default null
2602 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
2603 ,p_projected_start_date in date default null
2604 ,p_resume_exists in varchar2 default null
2605 ,p_resume_last_updated in date default null
2606 ,p_second_passport_exists in varchar2 default null
2607 ,p_student_status in varchar2 default null
2608 ,p_work_schedule in varchar2 default null
2609 ,p_suffix in varchar2 default null
2610 ,p_benefit_group_id in number default null
2611 ,p_receipt_of_death_cert_date in date default null
2612 ,p_coord_ben_med_pln_no in varchar2 default null
2613 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
2614 ,p_coord_ben_med_ext_er in varchar2 default null
2615 ,p_coord_ben_med_pl_name in varchar2 default null
2616 ,p_coord_ben_med_insr_crr_name in varchar2 default null
2617 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
2618 ,p_coord_ben_med_cvg_strt_dt in date default null
2619 ,p_coord_ben_med_cvg_end_dt in date default null
2620 ,p_uses_tobacco_flag in varchar2 default null
2621 ,p_dpdnt_adoption_date in date default null
2622 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
2623 ,p_original_date_of_hire in date default null
2624 ,p_adjusted_svc_date in date default null
2625 ,p_town_of_birth in varchar2 default null
2626 ,p_region_of_birth in varchar2 default null
2627 ,p_country_of_birth in varchar2 default null
2628 ,p_global_person_id in varchar2 default null
2629 ,p_party_id in number default null
2630 ,p_person_id out nocopy number
2631 ,p_assignment_id out nocopy number
2632 ,p_per_object_version_number out nocopy number
2633 ,p_asg_object_version_number out nocopy number
2634 ,p_per_effective_start_date out nocopy date
2635 ,p_per_effective_end_date out nocopy date
2636 ,p_full_name out nocopy varchar2
2637 ,p_per_comment_id out nocopy number
2638 ,p_assignment_sequence out nocopy number
2639 ,p_assignment_number out nocopy varchar2
2640 ,p_name_combination_warning out nocopy boolean
2641 ,p_assign_payroll_warning out nocopy boolean
2642 ,p_orig_hire_warning out nocopy boolean
2643 )
2644 is
2645 --
2646 -- Declare cursors and local variables
2647 --
2648 l_proc varchar2(72) ;
2649 l_legislation_code varchar2(2);
2650 l_asg_object_version_number number(9);
2651 --
2652 cursor csr_bg is
2653 select legislation_code
2654 from per_business_groups pbg
2655 where pbg.business_group_id = p_business_group_id;
2656 --
2657 begin
2658 --
2659 -- Validation in addition to Row Handlers
2660 --
2661 -- Check that the specified business group is valid.
2662 --
2663 open csr_bg;
2664 fetch csr_bg
2665 into l_legislation_code;
2666 if csr_bg%notfound then
2667 close csr_bg;
2668 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2669 hr_utility.raise_error;
2670 end if;
2671 close csr_bg;
2672 if g_debug then
2673 l_proc := g_package||'create_us_employee';
2674 hr_utility.set_location(l_proc, 20);
2675 end if;
2676 --
2677 -- Check that the legislation of the specified business group is 'US'.
2678 --
2679 if l_legislation_code <> 'US' then
2680 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2681 hr_utility.set_message_token('LEG_CODE','US');
2682 hr_utility.raise_error;
2683 end if;
2684 if g_debug then
2685 hr_utility.set_location(l_proc, 30);
2686 end if;
2687 --
2688 -- Call the person business process
2689 --
2690 hr_employee_api.create_employee
2691 (p_validate => p_validate
2692 ,p_hire_date => p_hire_date
2693 ,p_business_group_id => p_business_group_id
2694 ,p_last_name => p_last_name
2695 ,p_sex => p_sex
2696 ,p_person_type_id => p_person_type_id
2697 ,p_per_comments => p_comments
2698 ,p_date_employee_data_verified => p_date_employee_data_verified
2699 ,p_date_of_birth => p_date_of_birth
2700 ,p_email_address => p_email_address
2701 ,p_employee_number => p_employee_number
2702 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
2703 ,p_first_name => p_first_name
2704 ,p_known_as => p_known_as
2705 ,p_marital_status => p_marital_status
2706 ,p_middle_names => p_middle_names
2707 ,p_nationality => p_nationality
2708 ,p_national_identifier => p_ss_number
2709 ,p_previous_last_name => p_previous_last_name
2710 ,p_registered_disabled_flag => p_registered_disabled_flag
2711 ,p_title => p_title
2712 ,p_vendor_id => p_vendor_id
2713 ,p_work_telephone => p_work_telephone
2714 ,p_attribute_category => p_attribute_category
2715 ,p_attribute1 => p_attribute1
2716 ,p_attribute2 => p_attribute2
2717 ,p_attribute3 => p_attribute3
2718 ,p_attribute4 => p_attribute4
2719 ,p_attribute5 => p_attribute5
2720 ,p_attribute6 => p_attribute6
2721 ,p_attribute7 => p_attribute7
2722 ,p_attribute8 => p_attribute8
2723 ,p_attribute9 => p_attribute9
2724 ,p_attribute10 => p_attribute10
2725 ,p_attribute11 => p_attribute11
2726 ,p_attribute12 => p_attribute12
2727 ,p_attribute13 => p_attribute13
2728 ,p_attribute14 => p_attribute14
2729 ,p_attribute15 => p_attribute15
2730 ,p_attribute16 => p_attribute16
2731 ,p_attribute17 => p_attribute17
2732 ,p_attribute18 => p_attribute18
2733 ,p_attribute19 => p_attribute19
2734 ,p_attribute20 => p_attribute20
2735 ,p_attribute21 => p_attribute21
2736 ,p_attribute22 => p_attribute22
2737 ,p_attribute23 => p_attribute23
2738 ,p_attribute24 => p_attribute24
2739 ,p_attribute25 => p_attribute25
2740 ,p_attribute26 => p_attribute26
2741 ,p_attribute27 => p_attribute27
2742 ,p_attribute28 => p_attribute28
2743 ,p_attribute29 => p_attribute29
2744 ,p_attribute30 => p_attribute30
2745 ,p_per_information_category => 'US'
2746 ,p_per_information1 => p_ethnic_origin
2747 ,p_per_information2 => p_I_9
2748 ,p_per_information3 => p_I_9_expiration_date
2749 -- ,p_per_information4 => p_visa_type
2750 ,p_per_information5 => p_veteran_status
2751 ,p_per_information25 => p_vets100A
2752 ,p_per_information7 => p_new_hire
2753 ,p_per_information8 => p_exception_reason
2754 ,p_per_information9 => p_child_support_obligation
2755 ,p_per_information10 => p_opted_for_medicare_flag
2756 ,p_date_of_death => p_date_of_death
2757 ,p_background_check_status => p_background_check_status
2758 ,p_background_date_check => p_background_date_check
2759 ,p_blood_type => p_blood_type
2760 ,p_correspondence_language => p_correspondence_language
2761 ,p_fast_path_employee => p_fast_path_employee
2762 ,p_fte_capacity => p_fte_capacity
2763 ,p_honors => p_honors
2764 ,p_internal_location => p_internal_location
2765 ,p_last_medical_test_by => p_last_medical_test_by
2766 ,p_last_medical_test_date => p_last_medical_test_date
2767 ,p_mailstop => p_mailstop
2768 ,p_office_number => p_office_number
2769 ,p_on_military_service => p_on_military_service
2770 ,p_pre_name_adjunct => p_pre_name_adjunct
2771 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
2772 ,p_projected_start_date => p_projected_start_date
2773 ,p_resume_exists => p_resume_exists
2774 ,p_resume_last_updated => p_resume_last_updated
2775 ,p_second_passport_exists => p_second_passport_exists
2776 ,p_student_status => p_student_status
2777 ,p_work_schedule => p_work_schedule
2778 ,p_suffix => p_suffix
2779 ,p_benefit_group_id => p_benefit_group_id
2780 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
2781 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
2782 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
2783 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
2784 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
2785 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
2786 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
2787 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
2788 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
2789 ,p_uses_tobacco_flag => p_uses_tobacco_flag
2790 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
2791 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
2792 ,p_original_date_of_hire => p_original_date_of_hire
2793 ,p_adjusted_svc_date => p_adjusted_svc_date
2794 ,p_town_of_birth => p_town_of_birth
2795 ,p_region_of_birth => p_region_of_birth
2796 ,p_country_of_birth => p_country_of_birth
2797 ,p_global_person_id => p_global_person_id
2798 ,p_party_id => p_party_id
2799 --
2800 ,p_person_id => p_person_id
2801 ,p_assignment_id => p_assignment_id
2802 ,p_per_object_version_number => p_per_object_version_number
2803 ,p_asg_object_version_number => p_asg_object_version_number
2804 ,p_per_effective_start_date => p_per_effective_start_date
2805 ,p_per_effective_end_date => p_per_effective_end_date
2806 ,p_full_name => p_full_name
2807 ,p_per_comment_id => p_per_comment_id
2808 ,p_assignment_sequence => p_assignment_sequence
2809 ,p_assignment_number => p_assignment_number
2810 ,p_name_combination_warning => p_name_combination_warning
2811 ,p_assign_payroll_warning => p_assign_payroll_warning
2812 ,p_orig_hire_warning => p_orig_hire_warning
2813 );
2814 --
2815 if g_debug then
2816 hr_utility.set_location(' Leaving:'||l_proc, 40);
2817 end if;
2818 end create_us_employee;
2819 -- --------------------------------------------------------------------------
2820
2821 -- ----------------------------------------------------------------------------
2822 -- |--------------------------< re_hire_ex_employee >-------------------------|
2823 -- ----------------------------------------------------------------------------
2824 procedure re_hire_ex_employee
2825 (p_validate in boolean default false
2826 ,p_hire_date in date
2827 ,p_person_id in number
2828 ,p_per_object_version_number in out nocopy number
2829 ,p_person_type_id in number default hr_api.g_number
2830 ,p_rehire_reason in varchar2
2831 ,p_assignment_id out nocopy number
2832 ,p_asg_object_version_number out nocopy number
2833 ,p_per_effective_start_date out nocopy date
2834 ,p_per_effective_end_date out nocopy date
2835 ,p_assignment_sequence out nocopy number
2836 ,p_assignment_number out nocopy varchar2
2837 ,p_assign_payroll_warning out nocopy boolean
2838 ) is
2839 --
2840 -- declare local variables
2841 --
2842 l_proc varchar2(72) := g_package||'re_hire_ex_employee';
2843 l_business_group_id per_people_f.business_group_id%type;
2844 l_ovn per_people_f.object_version_number%type := p_per_object_version_number;
2845 l_name_combination_warning boolean;
2846 l_orig_hire_warning boolean;
2847 l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848 l_person_type_id1 per_people_f.person_type_id%type;
2849 l_comment_id per_people_f.comment_id%type;
2850 l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852 l_current_employee_flag per_people_f.current_employee_flag%type;
2853 l_employee_number per_people_f.employee_number%type;
2854 l_applicant_number per_people_f.applicant_number%TYPE;
2855 l_npw_number per_people_f.npw_number%type;
2856 l_full_name per_people_f.full_name%type;
2857 l_object_version_number per_people_f.object_version_number%type;
2858 l_period_of_service_id per_periods_of_service.period_of_service_id%type;
2859 l_pds_object_version_number per_periods_of_service.object_version_number%type;
2860 l_datetrack_mode varchar2(12);
2861 l_effective_date date;
2862 l_hire_date date;
2863 l_assign_payroll_warning boolean :=FALSE;
2864 --
2865 -- Added local variables for after hook re_hire_ex_employee_a
2866 --
2867 -- Bug 1828850 starts here.
2868 -- The l_assignment_id is declared as per_assignments_f.assignment_id%type.
2869 --
2870 l_assignment_id per_assignments_f.assignment_id%type;
2871 --
2872 -- Bug 1828850 Ends here.
2873 --
2874 l_asg_object_version_number number(9);
2875 l_per_effective_start_date date;
2876 l_per_effective_end_date date;
2877 l_assignment_sequence number(15);
2878 l_assignment_number varchar2(30);
2879 -- Bug 3611984 starts here
2880 l_ptu_datetrack_mode varchar2(12);
2881 cursor c_ptu_start_date is
2882 select effective_start_date
2883 from per_person_type_usages_f ptu,per_person_types ppt
2884 where ptu.person_type_id = ppt.person_type_id
2885 and ptu.person_id = p_person_id
2886 and p_hire_date between ptu.effective_start_date and ptu.effective_end_date
2887 and ppt.system_person_type='EX_EMP';
2888 l_ptu_effective_start_date date;
2889
2890 --
2891 -- 115.57 (START)
2892 --
2893 l_rule_value pay_legislation_rules.rule_mode%TYPE;
2894 l_rule_found BOOLEAN;
2895 l_legislation_code pay_legislation_rules.legislation_code%TYPE;
2896 --
2897 -- 115.57 (END)
2898 --
2899
2900 -- Bug 3611984 starts here
2901
2902 -- --------------------------------------------------------------------------
2903 -- |-------------------------< get_person_details >-------------------------|
2904 -- --------------------------------------------------------------------------
2905 --
2906 -- Description
2907 -- This procedure is used for 2 purposes; to validate that the person
2908 -- exists as of the specified effective date and to select the
2909 -- business group, effective start date and system person type information
2910 --
2911 -- --------------------------------------------------------------------------
2912 procedure get_person_details
2913 (p_person_id in number,
2914 p_effective_date in date,
2915 p_business_group_id out nocopy number,
2916 p_employee_number out nocopy varchar2,
2917 p_effective_start_date out nocopy date,
2918 p_system_person_type out nocopy varchar2) is
2919 --
2920 l_proc varchar2(72);
2921 --
2922 -- select and validate the person
2923 --
2924 -- Fix for 5045840 . Modified the cursor csr_chk_person_exists to
2925 -- use ptu table.
2926 cursor csr_chk_person_exists is
2927 /* select per.business_group_id,
2928 per.employee_number,
2929 per.effective_start_date,
2930 pet.system_person_type
2931 from per_person_types pet,
2932 per_people_f per
2933 where per.person_id = p_person_id
2934 and pet.person_type_id = per.person_type_id
2935 and pet.business_group_id + 0 = per.business_group_id
2936 and p_effective_date
2937 between per.effective_start_date
2938 and per.effective_end_date;*/
2939 select per.business_group_id,
2940 per.employee_number,
2941 per.effective_start_date,
2942 pet.system_person_type
2943 from per_person_types pet,
2944 per_people_f per,
2945 per_person_type_usages_f ptu
2946 where per.person_id = p_person_id
2947 and pet.person_type_id = ptu.person_type_id
2948 and ptu.person_id = per.person_id
2949 -- added this condition for 5601538
2950 and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
2951 /*and ptu.effective_start_date = per.effective_start_date
2952 and ptu.effective_end_date = per.effective_end_date commented for bug 5601538*/
2953 and pet.business_group_id + 0 = per.business_group_id
2954 and p_effective_date
2955 between per.effective_start_date
2956 and per.effective_end_date;
2957 --
2958 begin
2959 if g_debug then
2960 l_proc := g_package||'get_person_details';
2961 hr_utility.set_location('Entering:'|| l_proc, 5);
2962 end if;
2963 --
2964 open csr_chk_person_exists;
2965 fetch csr_chk_person_exists into
2966 p_business_group_id, p_employee_number, p_effective_start_date,
2967 p_system_person_type;
2968 if csr_chk_person_exists%notfound then
2969 close csr_chk_person_exists;
2970 --
2971 -- the person cannot exist as of the supplied effective_date therefore
2972 -- we must error
2973 --
2974 -- This person either does not exist at all or does not exist as of the
2975 -- date specified.
2976 --
2977 hr_utility.set_message(801, 'HR_51011_PER_NOT_EXIST_DATE');
2978 hr_utility.raise_error;
2979 end if;
2980 --changes for bug 5601538 starts here
2981 Loop
2982 if p_system_person_type ='EX_EMP' then
2983 exit;
2984 end if;
2985 fetch csr_chk_person_exists into
2986 p_business_group_id, p_employee_number, p_effective_start_date,
2987 p_system_person_type;
2988 EXIT when csr_chk_person_exists%notfound ;
2989
2990 End loop;
2991 --changes for bug 5601538 ends here
2992 close csr_chk_person_exists;
2993 if g_debug then
2994 hr_utility.set_location(' Leaving:'||l_proc, 10);
2995 end if;
2996 end get_person_details;
2997 -- --------------------------------------------------------------------------
2998 -- |-------------------------< perform_validation >-------------------------|
2999 -- --------------------------------------------------------------------------
3000 --
3001 -- Description
3002 -- This procedure controls and performs the following business process
3003 -- validation:
3004 -- 1) ensure that the p_person_id and p_hire_date parameters are not null.
3005 -- 2) check that this person (p_person_id) exists as of p_hire_date and
3006 -- the current person type (per_people_f.person_type_id) has a
3007 -- corresponding system person type of EX_EMP.
3008 -- 3) ensure the most recent period of service for this person has been
3009 -- completely terminated.
3010 --
3011 -- --------------------------------------------------------------------------
3012 procedure perform_validation
3013 (p_person_id in number,
3014 p_hire_date in date,
3015 p_effective_date out nocopy date,
3016 p_business_group_id out nocopy number,
3017 p_employee_number out nocopy varchar2) is
3018 --
3019 l_proc varchar2(72) := g_package||'perform_validation';
3020 l_system_person_type per_person_types.system_person_type%type;
3021 l_final_process_date per_periods_of_service.final_process_date%type;
3022 l_effective_start_date per_people_f.effective_start_date%type;
3023 l_dummy_number number;
3024 l_dummy_emp_number varchar2(30);
3025 l_dummy_date date;
3026 --
3027 cursor csr_chk_period_of_service is
3028 --
3029 -- 115.57 (START)
3030 --
3031 --select pos.final_process_date
3032 select pos.actual_termination_date,
3033 pos.last_standard_process_date,
3034 pos.final_process_date
3035 --
3036 -- 115.57 (END)
3037 --
3038 from per_periods_of_service pos
3039 where pos.person_id = p_person_id
3040 order by pos.date_start desc;
3041 --
3042 -- 115.57 (START)
3043 --
3044 l_fpd per_periods_of_service.final_process_date%TYPE;
3045 l_atd per_periods_of_service.actual_termination_date%TYPE;
3046 l_lspd per_periods_of_service.last_standard_process_date%TYPE;
3047 --
3048 -- Cursor to get legislation code
3049 --
3050 CURSOR csr_per_legislation
3051 (p_person_id IN per_all_people_f.person_id%TYPE
3052 ,p_effective_date IN DATE
3053 ) IS
3054 SELECT bus.legislation_code
3055 FROM per_people_f per
3056 ,per_business_groups bus
3057 WHERE per.person_id = csr_per_legislation.p_person_id
3058 AND per.business_group_id+0 = bus.business_group_id
3059 AND csr_per_legislation.p_effective_date BETWEEN per.effective_start_date
3060 AND per.effective_end_date;
3061 --
3062 -- 115.57 (END)
3063 --
3064 --
3065 begin
3066 if g_debug then
3067 hr_utility.set_location('Entering:'|| l_proc, 5);
3068 end if;
3069 --
3070 -- Validation Logic
3071 --
3072 -- 1. ensure that the mandatory parameters p_hire_date and p_person_id
3073 -- are not null
3074 --
3075 hr_api.mandatory_arg_error
3076 (p_api_name => l_proc
3077 ,p_argument => 'person id'
3078 ,p_argument_value => p_person_id);
3079 --
3080 hr_api.mandatory_arg_error
3081 (p_api_name => l_proc
3082 ,p_argument => 'hire date'
3083 ,p_argument_value => p_hire_date);
3084 --
3085 -- 2. check that this person (p_person_id) exists as of p_hire_date and the
3086 -- the current person type (per_people_f.person_type_id) has a
3087 -- corresponding system person type of EX_EMP.
3088 --
3089 get_person_details
3090 (p_person_id => p_person_id,
3091 p_effective_date => p_hire_date,
3092 p_business_group_id => p_business_group_id,
3093 p_employee_number => p_employee_number,
3094 p_effective_start_date => l_effective_start_date,
3095 p_system_person_type => l_system_person_type);
3096 --
3097 -- 115.57 (START)
3098 --
3099 --
3100 -- Get person legislation
3101 --
3102 OPEN csr_per_legislation(p_person_id
3103 ,p_hire_date
3104 );
3105 FETCH csr_per_legislation INTO l_legislation_code;
3106 CLOSE csr_per_legislation;
3107 --
3108 -- Check if rehire before FPD is enabled
3109 --
3110 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
3111 ,l_legislation_code
3112 ,l_rule_value
3113 ,l_rule_found
3114 );
3115 --
3116 --
3117 -- 115.57 (END)
3118 --
3119 --
3120 -- ensure that the system person type is 'EX_EMP'
3121 --
3122 if (l_system_person_type <> 'EX_EMP') then
3123 --
3124 -- the system person type is not 'EX_EMP' therefore error
3125 -- You cannot Re-Hire a person who is not an Ex-Employee.
3126 --
3127 hr_utility.set_message(801, 'HR_51012_REHIRE_NOT_EX_EMP');
3128 hr_utility.raise_error;
3129 end if;
3130 if g_debug then
3131 hr_utility.set_location(l_proc, 10);
3132 end if;
3133 --
3134 -- 3. ensure the most recent period of service for this person has been
3135 -- completely terminated. i.e. check that
3136 -- period_of_service.final_process_date is not null and comes before
3137 -- p_hire_date.
3138 -- we only fetch the 1st row (which is the latest pos).
3139 --
3140 open csr_chk_period_of_service;
3141 --
3142 -- 115.57 (START)
3143 --
3144 --fetch csr_chk_period_of_service into l_final_process_date;
3145 fetch csr_chk_period_of_service into l_atd, l_lspd, l_fpd;
3146 --
3147 -- 115.57 (END)
3148 --
3149 if csr_chk_period_of_service%notfound then
3150 close csr_chk_period_of_service;
3151 --
3152 -- a period of service row does not exist for the person therefore
3153 -- we must error
3154 --
3155 -- This person does not have a previous period of service
3156 --
3157 hr_utility.set_message(801, 'HR_51013_PDS_NOT_EXIST');
3158 hr_utility.raise_error;
3159 end if;
3160 close csr_chk_period_of_service;
3161 --
3162 --
3163 -- 115.57 (START)
3164 --
3165 if (l_fpd is null) then
3166 --
3167 -- the employee cannot be terminated as the final process date has not
3168 -- been set
3169 --
3170 -- You cannot re-hire a person who does not have a final processing date
3171 -- set for their most recent period of service
3172 --
3173 hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
3174 hr_utility.raise_error;
3175 end if;
3176 --
3177 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
3178 --
3179 -- Rehire before FPD allowed (new behaviour)
3180 --
3181 if nvl(l_lspd,l_atd) >= p_hire_date then
3182 --
3183 -- the re hire date is before the current LSPD or ATD
3184 --
3185 -- You cannot re-hire an Ex-Employee before their LSPD.
3186 -- Please specify a Re-Hire date which is after the LSPD.
3187 --
3188 --hr_utility.set_message(801, 'HR_449759_REHIRE_AFTER_LSPD');
3189 hr_utility.set_message(800, 'HR_449759_REHIRE_AFTER_LSPD'); -- product ID corrected for bug fix 8929785
3190 hr_utility.raise_error;
3191 end if;
3192 else
3193 --
3194 -- Rehire before FPD is not allowed (old behaviour)
3195 --
3196 if l_fpd >= p_hire_date then
3197 --
3198 -- the re hire date is before the current final process date
3199 --
3200 -- You cannot re-hire an Ex-Employee before their final processing date.
3201 -- Please specify a Re-Hire date which is after the final processing date.
3202 --
3203 hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
3204 hr_utility.raise_error;
3205 end if;
3206 end if;
3207 --
3208 --if (l_final_process_date is null) then
3209 -- --
3210 -- -- the employee cannot be terminated as the final process date has not
3211 -- -- been set
3212 -- --
3213 -- -- You cannot re-hire a person who does not have a final processing date
3214 -- -- set for their most recent period of service
3215 -- --
3216 -- hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
3217 -- hr_utility.raise_error;
3218 --elsif (l_final_process_date >= p_hire_date) then
3219 -- --
3220 -- -- the re hire date is before the current final process date
3221 -- --
3222 -- -- You cannot re-hire an Ex-Employee before their final processing date.
3223 -- -- Please specify a Re-Hire date which is after the final processing date.
3224 -- --
3225 -- hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
3226 -- hr_utility.raise_error;
3227 --end if;
3228 --
3229 -- 115.57 (START)
3230 --
3231 if g_debug then
3232 hr_utility.set_location(l_proc, 20);
3233 end if;
3234 p_effective_date := l_effective_start_date;
3235 --
3236
3237 if g_debug then
3238 hr_utility.set_location(' Leaving:'||l_proc, 25);
3239 end if;
3240 end perform_validation;
3241
3242 begin
3243 if g_debug then
3244 hr_utility.set_location('Entering:'|| l_proc, 5);
3245 end if;
3246 --
3247 -- Issue a savepoint.
3248 --
3249 savepoint re_hire_ex_employee;
3250 --
3251 -- Initialise local variables
3252 --
3253 l_object_version_number := p_per_object_version_number;
3254 l_applicant_number := hr_api.g_varchar2;
3255 l_npw_number := hr_api.g_varchar2;
3256 l_hire_date := trunc(p_hire_date);
3257 --
3258 -- perform business process validation
3259 --
3260 perform_validation
3261 (p_person_id => p_person_id,
3262 p_hire_date => l_hire_date,
3263 p_effective_date => l_effective_date,
3264 p_business_group_id => l_business_group_id,
3265 p_employee_number => l_employee_number);
3266 --
3267 -- processing logic
3268 --
3269 if g_debug then
3270 hr_utility.set_location(l_proc, 10);
3271 end if;
3272 l_person_type_id := p_person_type_id;
3273 --
3274 begin
3275 --
3276 -- Start of call API User Hook for the before hook of re_hire_ex_employee
3277 --
3278 hr_employee_bk2.re_hire_ex_employee_b
3279 (
3280 p_business_group_id =>l_business_group_id
3281 ,p_hire_date =>l_hire_date
3282 ,p_person_id =>p_person_id
3283 ,p_per_object_version_number =>p_per_object_version_number
3284 ,p_person_type_id =>p_person_type_id
3285 ,p_rehire_reason =>p_rehire_reason
3286 );
3287 exception
3288 when hr_api.cannot_find_prog_unit then
3289 hr_api.cannot_find_prog_unit_error
3290 (p_module_name => 'RE_HIRE_EX_EMPLOYEE'
3291 ,p_hook_type => 'BP'
3292 );
3293 --
3294 -- End of API User Hook for the before hook of re_hire_ex_employee
3295 --
3296 end;
3297 --
3298 -- derive and/or validate the person type
3299 --
3300 per_per_bus.chk_person_type
3301 (p_person_type_id => l_person_type_id,
3302 p_business_group_id => l_business_group_id,
3303 p_expected_sys_type => 'EMP');
3304 --
3305 if g_debug then
3306 hr_utility.set_location(l_proc, 15);
3307 end if;
3308 --
3309 if (l_effective_date = p_hire_date) then
3310 l_datetrack_mode := 'CORRECTION';
3311 else
3312 l_datetrack_mode := 'UPDATE';
3313 end if;
3314
3315 -- PTU : Added
3316
3317 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
3318 (l_business_group_id,
3319 'EMP');
3320 -- PTU : End
3321
3322 -- update the person re-hiring as an employee as of the hire date
3323 --
3324 per_per_upd.upd
3325 (p_person_id => p_person_id,
3326 p_person_type_id => l_person_type_id1,
3327 p_effective_date => l_hire_date,
3328 p_datetrack_mode => l_datetrack_mode,
3329 p_object_version_number => p_per_object_version_number,
3330 p_dob_null_warning => p_assign_payroll_warning,
3331 p_effective_start_date => l_per_effective_start_date,
3332 p_effective_end_date => l_per_effective_end_date,
3333 p_rehire_reason => p_rehire_reason,
3334 p_name_combination_warning => l_name_combination_warning,
3335 p_orig_hire_warning => l_orig_hire_warning,
3336 p_comment_id => l_comment_id,
3337 p_current_applicant_flag => l_current_applicant_flag,
3338 p_current_emp_or_apl_flag => l_current_emp_or_apl_flag,
3339 p_current_employee_flag => l_current_employee_flag,
3340 p_employee_number => l_employee_number,
3341 p_applicant_number => l_applicant_number,
3342 p_full_name => l_full_name,
3343 p_npw_number => l_npw_number);
3344 --
3345 if g_debug then
3346 hr_utility.set_location(l_proc, 20);
3347 end if;
3348 --
3349 -- add to current security list
3350 -- when in validation only mode raise the Validate_Enabled exception
3351 --
3352 hr_security_internal.populate_new_person(l_business_group_id,p_person_id);
3353 -- PTU : Following Code has been added
3354 -- Bug 3611984 starts here
3355 begin
3356 open c_ptu_start_date;
3357 fetch c_ptu_start_date into l_ptu_effective_start_date;
3358 close c_ptu_start_date;
3359 end;
3360
3361 if (l_ptu_effective_start_date = p_hire_date) then
3362 l_ptu_datetrack_mode := 'CORRECTION';
3363 else
3364 l_ptu_datetrack_mode := 'UPDATE';
3365 end if;
3366 -- Bug 3611984 ends here
3367 hr_per_type_usage_internal.maintain_person_type_usage
3368 (p_effective_date => l_hire_date
3369 ,p_person_id => p_person_id
3370 ,p_person_type_id => l_person_type_id
3371 ,p_datetrack_update_mode => l_ptu_datetrack_mode -- #3611984 l_datetrack_mode
3372 );
3373
3374 -- PTU : End of changes
3375 --
3376 if g_debug then
3377 hr_utility.set_location(l_proc, 22);
3378 end if;
3379 --
3380 -- create a new period of service for the re-hired employee
3381 --
3382 per_pds_ins.ins
3383 (p_business_group_id => l_business_group_id,
3384 p_person_id => p_person_id,
3385 p_date_start => l_hire_date,
3386 p_period_of_service_id => l_period_of_service_id,
3387 p_effective_date => p_hire_date,
3388 p_validate_df_flex => false,
3389 p_object_version_number => l_pds_object_version_number);
3390 --
3391 if g_debug then
3392 hr_utility.set_location(l_proc, 25);
3393 end if;
3394 --
3395 -- create a default primary assignment with any corresponding standard
3396 -- element entries for the re-hired employee
3397 --
3398 hr_assignment_internal.create_default_emp_asg
3399 (p_effective_date => l_hire_date,
3400 p_person_id => p_person_id,
3401 p_business_group_id => l_business_group_id,
3402 p_period_of_service_id => l_period_of_service_id,
3403 p_assignment_id => l_assignment_id,
3404 p_object_version_number => l_asg_object_version_number,
3405 p_assignment_sequence => l_assignment_sequence,
3406 p_assignment_number => l_assignment_number);
3407 --
3408 -- 115.57 (START)
3409 --
3410 if g_debug then
3411 hr_utility.set_location(l_proc, 26);
3412 end if;
3413 --
3414 -- If rehire before FPD is allowed, any resulting overlapping PDS
3415 -- will require updating the terminated primary assignment to
3416 -- secondary.
3417 --
3418 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
3419 manage_rehire_primary_asgs(p_person_id => p_person_id
3420 ,p_rehire_date => l_hire_date
3421 ,p_cancel => 'N'
3422 );
3423 end if;
3424 --
3425 if g_debug then
3426 hr_utility.set_location(l_proc, 27);
3427 end if;
3428 --
3429 -- 115.57 (END)
3430 --
3431 --
3432 -- when in validation only mode raise the Validate_Enabled exception
3433 --
3434 -- 1766066: added call for contact start date enh.
3435 --
3436 per_people12_pkg.maintain_coverage(p_person_id => p_person_id
3437 ,p_type => 'EMP'
3438 );
3439 -- 1766066 end.
3440 begin
3441 --
3442 -- Start of call API User Hook for the after hook of re_hire_ex_employee
3443 --
3444 hr_employee_bk2.re_hire_ex_employee_a
3445 (
3446 p_business_group_id =>l_business_group_id
3447 ,p_hire_date =>l_hire_date
3448 ,p_person_id =>p_person_id
3449 ,p_per_object_version_number =>p_per_object_version_number
3450 ,p_person_type_id =>p_person_type_id
3451 ,p_rehire_reason =>p_rehire_reason
3452 ,p_assignment_id =>l_assignment_id
3453 ,p_asg_object_version_number =>l_asg_object_version_number
3454 ,p_per_effective_start_date =>l_per_effective_start_date
3455 ,p_per_effective_end_date =>l_per_effective_end_date
3456 ,p_assignment_sequence =>l_assignment_sequence
3457 ,p_assignment_number =>l_assignment_number
3458 ,p_assign_payroll_warning =>l_assign_payroll_warning
3459 );
3460 exception
3461 when hr_api.cannot_find_prog_unit then
3462 hr_api.cannot_find_prog_unit_error
3463 (p_module_name => 'RE_HIRE_EX_EMPLOYEE'
3464 ,p_hook_type => 'BP'
3465 );
3466 --
3467 -- End of API User Hook for the after hook of re_hire_ex_employee
3468 --
3469 end;
3470 --
3471 if p_validate then
3472 raise hr_api.validate_enabled;
3473 end if;
3474 --
3475 -- Set all output arguments
3476 --
3477 p_assignment_id := l_assignment_id;
3478 p_asg_object_version_number := l_asg_object_version_number;
3479 p_per_effective_start_date := l_per_effective_start_date;
3480 p_per_effective_end_date := l_per_effective_end_date;
3481 p_assignment_sequence := l_assignment_sequence;
3482 p_assignment_number := l_assignment_number;
3483 p_assign_payroll_warning := l_assign_payroll_warning;
3484 --
3485 -- When in validation only mode raise the Validate_Enabled exception
3486 --
3487 if g_debug then
3488 hr_utility.set_location(' Leaving:'||l_proc, 30);
3489 end if;
3490 exception
3491 when hr_api.validate_enabled then
3492 --
3493 -- As the Validate_Enabled exception has been raised
3494 -- we must rollback to the savepoint
3495 --
3496 ROLLBACK TO re_hire_ex_employee;
3497 --
3498 -- Only set output warning arguments
3499 -- (Any key or derived arguments must be set to null
3500 -- when validation only mode is being used.)
3501 --
3502 p_per_object_version_number := l_object_version_number;
3503 p_assignment_id := null;
3504 p_asg_object_version_number := null;
3505 p_per_effective_start_date := null;
3506 p_per_effective_end_date := null;
3507 p_assignment_sequence := null;
3508 p_assignment_number := null;
3509 p_assign_payroll_warning := l_assign_payroll_warning;
3510 --
3511 when others then
3512 --
3513 -- A validation or unexpected error has occurred
3514 --
3515 p_per_object_version_number := l_ovn;
3516 p_assignment_id := null;
3517 p_asg_object_version_number := null;
3518 p_per_effective_start_date := null;
3519 p_per_effective_end_date := null;
3520 p_assignment_sequence := null;
3521 p_assignment_number := null;
3522 p_assign_payroll_warning := false;
3523 -- Added as part of fix to bug 632479
3524 --
3525 ROLLBACK TO re_hire_ex_employee;
3526 --
3527 -- set in out parameters and set out parameters
3528 --
3529 raise;
3530 --
3531 -- End of fix.
3532 --
3533 if g_debug then
3534 hr_utility.set_location(' Leaving:'||l_proc, 35);
3535 end if;
3536 end re_hire_ex_employee;
3537 --
3538 -- OLD
3539 -- ----------------------------------------------------------------------------
3540 -- |-----------------< apply_for_internal_vacancy >-------------------------|
3541 -- ----------------------------------------------------------------------------
3542 -- OLD
3543 procedure apply_for_internal_vacancy
3544 (p_validate in boolean default false
3545 ,p_effective_date in date
3546 ,p_person_id in number
3547 ,p_applicant_number in out nocopy varchar2
3548 ,p_per_object_version_number in out nocopy number
3549 ,p_vacancy_id in number default null
3550 ,p_person_type_id in number default hr_api.g_number
3551 ,p_application_id out nocopy number
3552 ,p_assignment_id out nocopy number
3553 ,p_apl_object_version_number out nocopy number
3554 ,p_asg_object_version_number out nocopy number
3555 ,p_assignment_sequence out nocopy number
3556 ,p_per_effective_start_date out nocopy date
3557 ,p_per_effective_end_date out nocopy date
3558 ) is
3559 --
3560 l_warning boolean;
3561 --
3562 begin
3563 hr_employee_api.apply_for_internal_vacancy
3564 (p_validate => p_validate
3565 ,p_effective_date => p_effective_date
3566 ,p_person_id => p_person_id
3567 ,p_applicant_number => p_applicant_number
3568 ,p_per_object_version_number => p_per_object_version_number
3569 ,p_vacancy_id => p_vacancy_id
3570 ,p_person_type_id => p_person_type_id
3571 ,p_application_id => p_application_id
3572 ,p_assignment_id => p_assignment_id
3573 ,p_apl_object_version_number => p_apl_object_version_number
3574 ,p_asg_object_version_number => p_asg_object_version_number
3575 ,p_assignment_sequence => p_assignment_sequence
3576 ,p_per_effective_start_date => p_per_effective_start_date
3577 ,p_per_effective_end_date => p_per_effective_end_date
3578 ,p_appl_override_warning => l_warning
3579 );
3580 end apply_for_internal_vacancy;
3581 -- NEW
3582 -- ----------------------------------------------------------------------------
3583 -- |-----------------< apply_for_internal_vacancy >-------------------------|
3584 -- ----------------------------------------------------------------------------
3585 -- NEW
3586 procedure apply_for_internal_vacancy
3587 (p_validate in boolean default false
3588 ,p_effective_date in date
3589 ,p_person_id in number
3590 ,p_applicant_number in out nocopy varchar2
3591 ,p_per_object_version_number in out nocopy number
3592 ,p_vacancy_id in number default null
3593 ,p_person_type_id in number default hr_api.g_number
3594 ,p_application_id out nocopy number
3595 ,p_assignment_id out nocopy number
3596 ,p_apl_object_version_number out nocopy number
3597 ,p_asg_object_version_number out nocopy number
3598 ,p_assignment_sequence out nocopy number
3599 ,p_per_effective_start_date out nocopy date
3600 ,p_per_effective_end_date out nocopy date
3601 ,p_appl_override_warning out nocopy boolean -- 3652025
3602 ) is
3603 --
3604 -- declare local variables
3605 --
3606 l_proc varchar2(72) := g_package||'apply_for_internal_vacancy';
3607 l_business_group_id per_people_f.business_group_id%type;
3608 l_name_combination_warning boolean;
3609 l_dob_null_warning boolean;
3610 l_orig_hire_warning boolean;
3611 l_organization_id per_business_groups.organization_id%type;
3612 l_legislation_code per_business_groups.legislation_code%type;
3613 l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
3614 l_person_type_id1 per_people_f.person_type_id%type;
3615 l_application_id per_applications.application_id%type;
3616 l_comment_id per_assignments_f.comment_id%type;
3617 l_assignment_sequence per_assignments_f.assignment_sequence%type;
3618 l_assignment_id per_assignments_f.assignment_id%type;
3619 l_object_version_number per_assignments_f.object_version_number%type;
3620 l_current_applicant_flag per_people_f.current_applicant_flag%type;
3621 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622 l_current_employee_flag per_people_f.current_employee_flag%type;
3623 l_employee_number per_people_f.employee_number%type;
3624 l_applicant_number per_people_f.applicant_number%TYPE;
3625 l_npw_number per_people_f.npw_number%TYPE;
3626 l_per_object_version_number per_people_f.object_version_number%TYPE;
3627 l_full_name per_people_f.full_name%type;
3628 l_system_person_type per_person_types.system_person_type%type;
3629 l_effective_date date;
3630 l_effective_start_date date;
3631 l_effective_end_date date;
3632 l_default_start_time per_business_groups.default_start_time%type;
3633 l_default_end_time per_business_groups.default_end_time%type;
3634 l_normal_hours number;
3635 l_frequency per_business_groups.frequency%type;
3636 l_recruiter_id per_vacancies.recruiter_id%type;
3637 l_grade_id per_vacancies.grade_id%type;
3638 l_position_id per_vacancies.position_id%type;
3639 l_job_id per_vacancies.job_id%type;
3640 l_location_id per_vacancies.location_id%type;
3641 l_people_group_id per_vacancies.people_group_id%type;
3642 l_vac_organization_id per_vacancies.organization_id%type;
3643 l_vac_business_group_id per_vacancies.business_group_id%type;
3644 l_group_name pay_people_groups.group_name%type;
3645 l_appl_override_warning boolean;
3646 --
3647 -- Local variable added for the before and after business process
3648 -- apply_for_internal_vacancy
3649 --
3650 l_apl_object_version_number number; -- THESE NEED TO BE CHANGED
3651 l_asg_object_version_number number; -- THESE NEED TO BE CHANGED
3652 l_per_effective_start_date date;
3653 l_per_effective_end_date date;
3654 --
3655 --
3656 -- select and validate the person
3657 --
3658 -- now returns employee number which is needed by upd.upd - thayden
3659 --
3660 cursor csr_chk_person_exists is
3661 select ppf.business_group_id
3662 ,ppf.employee_number
3663 ,ppf.npw_number
3664 ,ppt.system_person_type
3665 from per_person_types ppt
3666 ,per_people_f ppf
3667 where ppf.person_id = p_person_id
3668 and ppt.person_type_id = ppf.person_type_id
3669 and ppt.business_group_id + 0 = ppf.business_group_id
3670 and l_effective_date
3671 between ppf.effective_start_date
3672 and ppf.effective_end_date;
3673 --
3674 -- Get default person type id for a system person type EMP_APL
3675 --
3676 cursor csr_get_person_type_id is
3677 select person_type_id
3678 from per_person_types
3679 where business_group_id = l_business_group_id
3680 and active_flag = 'Y'
3681 and default_flag = 'Y'
3682 and system_person_type = 'EMP_APL';
3683 --
3684 -- Get organization id for business group.
3685 --
3686 cursor csr_get_organization_id is
3687 select organization_id
3688 ,legislation_code
3689 ,default_start_time
3690 ,default_end_time
3691 ,fnd_number.canonical_to_number(working_hours)
3692 ,frequency
3693 from per_business_groups
3694 where business_group_id = l_business_group_id;
3695 --
3696 -- Get vacancy information.
3697 --
3698 cursor csr_get_vacancy_details is
3699 select recruiter_id
3700 ,grade_id
3701 ,position_id
3702 ,job_id
3703 ,location_id
3704 ,people_group_id
3705 ,organization_id -- added org id to cursor. thayden 7/10.
3706 ,business_group_id -- added business_group_id to cursor lma 7/11
3707 from per_vacancies
3708 where vacancy_id = p_vacancy_id;
3709 --
3710 begin
3711 -- Bug 665566 Savepoint issued before validations start
3712 --
3713 -- Issue a savepoint if operating in validation only mode.
3714 --
3715 if p_validate then
3716 savepoint apply_for_internal_vacancy;
3717 end if;
3718 -- Bug 665566 End
3719
3720 if g_debug then
3721 hr_utility.set_location('Entering:'|| l_proc, 5);
3722 end if;
3723 --
3724 -- Truncate p_effective_date
3725 --
3726 l_effective_date := trunc(p_effective_date);
3727 -- Initialise local variables
3728 --
3729 l_applicant_number := p_applicant_number;
3730 l_per_object_version_number := p_per_object_version_number;
3731 --
3732 --
3733 -- Validation Logic
3734 --
3735 -- Ensure that the mandatory parameter, p_person_id
3736 -- is not null
3737 --
3738 hr_api.mandatory_arg_error
3739 (p_api_name => l_proc
3740 ,p_argument => 'person id'
3741 ,p_argument_value => p_person_id);
3742 --
3743 if g_debug then
3744 hr_utility.set_location(' Leaving:'||l_proc, 10);
3745 end if;
3746 --
3747 -- Check that this person (p_person_id) exists as of l_effective_date
3748 -- the current person type (per_people_f.person_type_id) has a
3749 -- corresponding system person type of EMP.
3750 --
3751 open csr_chk_person_exists;
3752 fetch csr_chk_person_exists into
3753 l_business_group_id
3754 ,l_employee_number
3755 ,l_npw_number
3756 ,l_system_person_type;
3757 if csr_chk_person_exists%notfound then
3758 close csr_chk_person_exists;
3759 hr_utility.set_message(800, 'HR_51011_PER_NOT_EXIST_DATE');
3760 hr_utility.raise_error;
3761 end if;
3762 close csr_chk_person_exists;
3763 --
3764 --
3765 -- ensure that the system person type is 'EMP'
3766 -- added and l_system_person_type <> 'EMP_APL' to if 15-Jul-97 lma
3767 if (l_system_person_type <> 'EMP' and l_system_person_type <> 'EMP_APL') then
3768 --
3769 -- the system person type is not 'EMP'.
3770 --
3771 hr_utility.set_message(800, 'PER_52788_PER_INV_PER_TYPE');
3772 hr_utility.raise_error;
3773 end if;
3774 if g_debug then
3775 hr_utility.set_location(l_proc, 15);
3776 end if;
3777
3778 --
3779 -- Initialise local variables
3780 --
3781 l_applicant_number := p_applicant_number;
3782 l_per_object_version_number := p_per_object_version_number;
3783 --
3784 begin
3785 --
3786 -- Start of call API User Hook for the before hook of apply_for_internal_vacancy_b
3787 --
3788 hr_employee_bk3.apply_for_internal_vacancy_b
3789 (
3790 p_business_group_id => l_business_group_id
3791 ,p_effective_date => l_effective_date
3792 ,p_person_id => p_person_id
3793 ,p_applicant_number => p_applicant_number
3794 ,p_per_object_version_number => p_per_object_version_number
3795 ,p_vacancy_id => p_vacancy_id
3796 ,p_person_type_id => p_person_type_id
3797 );
3798 exception
3799 when hr_api.cannot_find_prog_unit then
3800 hr_api.cannot_find_prog_unit_error
3801 (p_module_name => 'APPLY_FOR_INTERNAL_VACANCY'
3802 ,p_hook_type => 'BP'
3803 );
3804 --
3805 -- End of API User Hook for the before hook of apply_for_internal_vacancy
3806 --
3807 end;
3808 -- processing logic
3809 --
3810 if g_debug then
3811 hr_utility.set_location(l_proc, 20);
3812 end if;
3813 --
3814 -- PTU : Following Code has been added
3815 -- Validate that the person_type_id passed is a flavour of 'APL' or derive the default
3816 --
3817 per_per_bus.chk_person_type
3818 (p_person_type_id => l_person_type_id,
3819 p_business_group_id => l_business_group_id,
3820 p_expected_sys_type => 'APL');
3821 --
3822 -- Get default person type id for EMP_APL.
3823 --
3824 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
3825 (l_business_group_id,
3826 'EMP_APL');
3827 -- PTU end of changes
3828 --
3829 -- open csr_get_person_type_id;
3830 -- fetch csr_get_person_type_id into
3831 -- l_person_type_id;
3832 -- if csr_get_person_type_id%notfound then
3833 -- close csr_get_person_type_id;
3834 -- hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
3835 -- hr_utility.raise_error;
3836 -- end if;
3837 -- close csr_get_person_type_id;
3838 --
3839 if g_debug then
3840 hr_utility.set_location(l_proc, 25);
3841 end if;
3842 --
3843 -- Get organization id
3844 --
3845 open csr_get_organization_id;
3846 fetch csr_get_organization_id into
3847 l_organization_id
3848 ,l_legislation_code
3849 ,l_default_start_time
3850 ,l_default_end_time
3851 ,l_normal_hours
3852 ,l_frequency;
3853 if csr_get_organization_id%notfound then
3854 close csr_get_organization_id;
3855 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
3856 hr_utility.raise_error;
3857 end if;
3858 close csr_get_organization_id;
3859 --
3860 -- Get vacancy details.
3861 --
3862 if p_vacancy_id is not null then
3863 open csr_get_vacancy_details;
3864 fetch csr_get_vacancy_details into
3865 l_recruiter_id
3866 ,l_grade_id
3867 ,l_position_id
3868 ,l_job_id
3869 ,l_location_id
3870 ,l_people_group_id
3871 ,l_vac_organization_id -- added org id. thayden 7/10.
3872 ,l_vac_business_group_id; -- added business_group_id. thayden 7/11.
3873 if csr_get_vacancy_details%notfound then
3874 close csr_get_vacancy_details;
3875 hr_utility.set_message(801, 'HR_51001_THE_VAC_NOT_FOUND');
3876 hr_utility.raise_error;
3877 end if;
3878 close csr_get_vacancy_details;
3879 --added if ... end if (Rod's sugguestion)
3880 if l_vac_organization_id is null then
3881 l_vac_organization_id := l_vac_business_group_id;
3882 end if;
3883 else
3884 l_vac_organization_id := l_business_group_id;
3885 end if;
3886 --
3887 if g_debug then
3888 hr_utility.set_location(l_proc, 30);
3889 end if;
3890 --
3891 -- 3652025: Create an applicant, generate the application and
3892 -- the applicant assignment
3893 --
3894 hr_applicant_internal.create_applicant_anytime
3895 (p_effective_date => l_effective_date
3896 ,p_person_id => p_person_id
3897 ,p_applicant_number => p_applicant_number
3898 ,p_per_object_version_number => p_per_object_version_number
3899 ,p_vacancy_id => p_vacancy_id
3900 ,p_person_type_id => p_person_type_id
3901 ,p_assignment_status_type_id => NULL
3902 ,p_application_id => l_application_id
3903 ,p_assignment_id => l_assignment_id
3904 ,p_apl_object_version_number => l_apl_object_version_number
3905 ,p_asg_object_version_number => l_asg_object_version_number
3906 ,p_assignment_sequence => l_assignment_sequence
3907 ,p_per_effective_start_date => l_effective_start_date
3908 ,p_per_effective_end_date => l_effective_end_date
3909 ,p_appl_override_warning => l_appl_override_warning);
3910 --
3911 if g_debug then
3912 hr_utility.set_location(l_proc, 35);
3913 end if;
3914 --
3915 hr_security_internal.add_to_person_list(l_effective_date,l_assignment_id);
3916 --
3917 if g_debug then
3918 hr_utility.set_location(l_proc, 40);
3919 end if;
3920 --
3921 --
3922 begin
3923 --
3924 -- Start of call API User Hook for the after hook of re_hire_ex_employee
3925 --
3926 hr_employee_bk3.apply_for_internal_vacancy_a
3927 (
3928 p_business_group_id => l_business_group_id
3929 ,p_effective_date => l_effective_date
3930 ,p_person_id => p_person_id
3931 ,p_applicant_number => p_applicant_number
3932 ,p_per_object_version_number => p_per_object_version_number
3933 ,p_vacancy_id => p_vacancy_id
3934 ,p_person_type_id => p_person_type_id
3935 ,p_application_id => l_application_id
3936 ,p_assignment_id => l_assignment_id
3937 ,p_apl_object_version_number => l_apl_object_version_number
3938 ,p_asg_object_version_number => l_asg_object_version_number
3939 ,p_assignment_sequence => l_assignment_sequence
3940 ,p_per_effective_start_date => l_per_effective_start_date
3941 ,p_per_effective_end_date => l_per_effective_end_date
3942 ,p_appl_override_warning => l_appl_override_warning
3943 );
3944 --
3945 exception
3946 when hr_api.cannot_find_prog_unit then
3947 hr_api.cannot_find_prog_unit_error
3948 (p_module_name => 'APPLY_FOR_INTERNAL_VACANCY'
3949 ,p_hook_type => 'BP'
3950 );
3951 --
3952 -- End of API User Hook for the after hook of apply_for_internal_vacancy
3953 --
3954 end;
3955 --
3956 -- Set all output arguments
3957 --
3958 p_application_id := l_application_id;
3959 p_assignment_id := l_assignment_id;
3960 p_apl_object_version_number := l_apl_object_version_number;
3961 p_asg_object_version_number := l_asg_object_version_number;
3962 p_assignment_sequence := l_assignment_sequence;
3963 p_per_effective_start_date := l_per_effective_start_date;
3964 p_per_effective_end_date := l_per_effective_end_date;
3965 p_appl_override_warning := l_appl_override_warning;
3966 --
3967 -- when in validation only mode raise the Validate_Enabled exception
3968 --
3969
3970 if p_validate then
3971 raise hr_api.validate_enabled;
3972 end if;
3973 --
3974 if g_debug then
3975 hr_utility.set_location(' Leaving:'||l_proc, 50);
3976 end if;
3977 exception
3978 when hr_api.validate_enabled then
3979 --
3980 -- As the Validate_Enabled exception has been raised
3981 -- we must rollback to the savepoint
3982 --
3983 ROLLBACK TO apply_for_internal_vacancy;
3984 --
3985 -- Only set output warning arguments
3986 -- (Any key or derived arguments must be set to null
3987 -- when validation only mode is being used.)
3988 --
3989 p_per_object_version_number := l_per_object_version_number;
3990 p_applicant_number := l_applicant_number;
3991 p_application_id := null;
3992 p_assignment_id := null;
3993 p_apl_object_version_number := null;
3994 p_asg_object_version_number := null;
3995 p_assignment_sequence := null;
3996 p_per_effective_start_date := null;
3997 p_per_effective_end_date := null;
3998 p_appl_override_warning := null;
3999
4000 if g_debug then
4001 hr_utility.set_location(' Leaving:'||l_proc, 55);
4002 end if;
4003 end apply_for_internal_vacancy;
4004 --
4005 -- ----------------------------------------------------------------------------
4006 -- |----------------------------< hire_into_job - old >-----------------------|
4007 -- ----------------------------------------------------------------------------
4008 --
4009 PROCEDURE hire_into_job
4010 (p_validate IN BOOLEAN DEFAULT FALSE
4011 ,p_effective_date IN DATE
4012 ,p_person_id IN NUMBER
4013 ,p_object_version_number IN OUT NOCOPY NUMBER
4014 ,p_employee_number IN OUT NOCOPY VARCHAR2
4015 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
4016 ,p_person_type_id IN NUMBER DEFAULT NULL
4017 ,p_national_identifier IN VARCHAR2 DEFAULT NULL
4018 ,p_per_information7 IN VARCHAR2 DEFAULT NULL -- 3414724
4019 ,p_effective_start_date OUT NOCOPY DATE
4020 ,p_effective_end_date OUT NOCOPY DATE
4021 ,p_assign_payroll_warning OUT NOCOPY BOOLEAN
4022 ,p_orig_hire_warning OUT NOCOPY BOOLEAN
4023 )
4024 IS
4025 --
4026 -- Local variables
4027 --
4028 l_proc VARCHAR2(72) := g_package||'hire_into_job';
4029 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
4030 --
4031 BEGIN
4032 --
4033 if g_debug then
4034 hr_utility.set_location('Entering:'||l_proc,10);
4035 end if;
4036 --
4037 hr_employee_api.hire_into_job
4038 (p_validate => p_validate
4039 ,p_effective_date => p_effective_date
4040 ,p_person_id => p_person_id
4041 ,p_object_version_number => p_object_version_number
4042 ,p_employee_number => p_employee_number
4043 ,p_datetrack_update_mode => p_datetrack_update_mode
4044 ,p_person_type_id => p_person_type_id
4045 ,p_national_identifier => p_national_identifier
4046 ,p_per_information7 => p_per_information7
4047 ,p_assignment_id => l_assignment_id
4048 ,p_effective_start_date => p_effective_start_date
4049 ,p_effective_end_date => p_effective_end_date
4050 ,p_assign_payroll_warning => p_assign_payroll_warning
4051 ,p_orig_hire_warning => p_orig_hire_warning
4052 );
4053 --
4054 if g_debug then
4055 hr_utility.set_location('Leaving:'||l_proc,999);
4056 end if;
4057
4058 --
4059 END hire_into_job;
4060 --
4061 -- ----------------------------------------------------------------------------
4062 -- |----------------------------< hire_into_job - new >-----------------------|
4063 -- ----------------------------------------------------------------------------
4064 --
4065 PROCEDURE hire_into_job
4066 (p_validate IN BOOLEAN DEFAULT FALSE
4067 ,p_effective_date IN DATE
4068 ,p_person_id IN NUMBER
4069 ,p_object_version_number IN OUT NOCOPY NUMBER
4070 ,p_employee_number IN OUT NOCOPY VARCHAR2
4071 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
4072 ,p_person_type_id IN NUMBER DEFAULT NULL
4073 ,p_national_identifier IN VARCHAR2 DEFAULT NULL
4074 ,p_per_information7 IN VARCHAR2 DEFAULT NULL -- 3414724
4075 ,p_assignment_id OUT NOCOPY NUMBER --Bug#3919096
4076 ,p_effective_start_date OUT NOCOPY DATE
4077 ,p_effective_end_date OUT NOCOPY DATE
4078 ,p_assign_payroll_warning OUT NOCOPY BOOLEAN
4079 ,p_orig_hire_warning OUT NOCOPY BOOLEAN
4080 )
4081 IS
4082 --
4083 -- Local variables
4084 --
4085 l_proc VARCHAR2(72) := g_package||'hire_into_job';
4086 --
4087 l_effective_date DATE;
4088 --
4089 l_object_version_number CONSTANT per_all_assignments_f.object_version_number%TYPE := p_object_version_number;
4090 l_datetrack_update_mode VARCHAR2(30) := p_datetrack_update_mode;
4091 l_employee_number CONSTANT per_all_people_f.applicant_number%TYPE := p_employee_number;
4092 l_emp_num per_all_people_f.applicant_number%TYPE := p_employee_number;
4093 l_ovn per_all_people_f.object_version_number%TYPE := p_object_version_number;
4094 l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
4095 l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
4096 l_assign_payroll_warning BOOLEAN;
4097 l_orig_hire_warning BOOLEAN;
4098 --
4099 l_person_type_id per_person_types.person_type_id%TYPE := p_person_type_id;
4100 l_person_type_id1 per_person_types.person_type_id%TYPE;
4101 --
4102 l_future_asgs_count INTEGER;
4103 l_system_person_type per_person_types.system_person_type%TYPE;
4104 l_effective_start_date DATE;
4105 l_effective_end_date DATE;
4106 l_comment_id hr_comments.comment_id%TYPE;
4107 l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
4108 l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
4109 l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
4110 l_full_name per_all_people_f.full_name%TYPE;
4111 l_name_combination_warning BOOLEAN;
4112 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
4113 l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
4114 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
4115 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
4116 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
4117 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
4118 l_person_type_usage_id per_person_type_usages.person_type_usage_id%TYPE;
4119 l_ptu_object_version_number per_person_type_usages.object_version_number%TYPE;
4120 --
4121 -- Start of fix for bug 3143299
4122 l_final_process_date per_periods_of_service.final_process_date%type;
4123 --
4124 -- Local cursors
4125 cursor csr_chk_period_of_service is
4126 --
4127 -- 115.57 (START)
4128 --
4129 --select pos.final_process_date
4130 select pos.actual_termination_date,
4131 pos.last_standard_process_date,
4132 pos.final_process_date
4133 --
4134 -- 115.57 (END)
4135 --
4136 from per_periods_of_service pos
4137 where pos.person_id = p_person_id
4138 order by pos.date_start desc;
4139 --
4140 -- 115.57 (START)
4141 --
4142 l_fpd per_periods_of_service.final_process_date%TYPE;
4143 l_atd per_periods_of_service.actual_termination_date%TYPE;
4144 l_lspd per_periods_of_service.last_standard_process_date%TYPE;
4145 l_rule_value pay_legislation_rules.rule_mode%TYPE;
4146 l_rule_found BOOLEAN;
4147 --
4148 -- 115.57 (END)
4149 --
4150 --
4151 -- End of fix for bug 3143299
4152 --
4153 CURSOR csr_per_details
4154 (p_person_id IN per_all_people_f.person_id%TYPE
4155 ,p_effective_date IN DATE
4156 )
4157 IS
4158 SELECT pet.person_type_id
4159 ,pet.system_person_type
4160 ,per.effective_start_date
4161 ,per.effective_end_date
4162 ,per.applicant_number
4163 ,per.employee_number
4164 ,per.npw_number
4165 ,bus.business_group_id
4166 ,bus.legislation_code
4167 FROM per_people_f per
4168 ,per_business_groups bus
4169 ,per_person_types pet
4170 WHERE per.person_type_id = pet.person_type_id
4171 AND per.business_group_id+0 = bus.business_group_id
4172 AND per.person_id = csr_per_details.p_person_id
4173 AND csr_per_details.p_effective_date BETWEEN per.effective_start_date
4174 AND per.effective_end_date;
4175 l_per_details_rec csr_per_details%ROWTYPE;
4176
4177 -- Code changes for Bug#14140116 Starts here --
4178
4179 cursor csr_earliest_date is
4180 select min(date_start)
4181 from per_periods_of_service
4182 where p_person_id = person_id;
4183
4184 l_earliest_date date;
4185
4186 -- Code changes for Bug#14140116 Ends here --
4187
4188 BEGIN
4189 --
4190 if g_debug then
4191 hr_utility.set_location('Entering:'||l_proc,10);
4192 end if;
4193 --
4194 -- Ensure mandatory arguments have been passed
4195 --
4196 hr_api.mandatory_arg_error
4197 (p_api_name => l_proc
4198 ,p_argument => 'person_id'
4199 ,p_argument_value => p_person_id
4200 );
4201 --
4202 hr_api.mandatory_arg_error
4203 (p_api_name => l_proc
4204 ,p_argument => 'effective_date'
4205 ,p_argument_value => p_effective_date
4206 );
4207 --
4208 -- Truncate all date parameters passed in
4209 --
4210 l_effective_date := TRUNC(p_effective_date);
4211 --
4212 -- Issue savepoint
4213 --
4214 SAVEPOINT hire_into_job;
4215 --
4216 if g_debug then
4217 hr_utility.set_location(l_proc,20);
4218 end if;
4219 --
4220 -- Get dervied details for person on effective date
4221 --
4222 OPEN csr_per_details
4223 (p_person_id => p_person_id
4224 ,p_effective_date => p_effective_date
4225 );
4226 FETCH csr_per_details INTO l_per_details_rec;
4227 IF csr_per_details%NOTFOUND
4228 THEN
4229 CLOSE csr_per_details;
4230 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
4231 hr_utility.raise_error;
4232 END IF;
4233 CLOSE csr_per_details;
4234 --
4235 if g_debug then
4236 hr_utility.set_location(l_proc,30);
4237 end if;
4238 --
4239 -- Call Before Process User Hook
4240 --
4241 BEGIN
4242 hr_employee_bk4.hire_into_job_b
4243 (p_effective_date => l_effective_date
4244 ,p_person_id => p_person_id
4245 ,p_business_group_id => l_per_details_rec.business_group_id
4246 ,p_object_version_number => p_object_version_number
4247 ,p_datetrack_update_mode => p_datetrack_update_mode
4248 ,p_employee_number => p_employee_number
4249 ,p_person_type_id => p_person_type_id
4250 ,p_national_identifier => p_national_identifier
4251 ,p_per_information7 => p_per_information7 --3414274
4252 );
4253 EXCEPTION
4254 WHEN hr_api.cannot_find_prog_unit
4255 THEN
4256 hr_api.cannot_find_prog_unit_error
4257 (p_module_name => 'HIRE_INTO_JOB'
4258 ,p_hook_type => 'BP'
4259 );
4260 END;
4261 --
4262 if g_debug then
4263 hr_utility.set_location(l_proc,40);
4264 end if;
4265 --
4266 -- Check the person is of a correct system person type
4267 --
4268 IF l_per_details_rec.system_person_type NOT IN ('EX_APL','EX_EMP','OTHER')
4269 THEN
4270 if g_debug then
4271 hr_utility.set_location(l_proc,50);
4272 end if;
4273 hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
4274 hr_utility.raise_error;
4275 END IF;
4276 --
4277 if g_debug then
4278 hr_utility.set_location(l_proc,60);
4279 end if;
4280 -- Start of fix for bug 3143299.
4281 --
4282 --
4283 -- 115.57 (START)
4284 --
4285 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
4286 ,l_per_details_rec.legislation_code
4287 ,l_rule_value
4288 ,l_rule_found
4289 );
4290 --
4291 if g_debug then
4292 hr_utility.set_location(l_proc,62);
4293 end if;
4294 --
4295 -- 115.57 (END)
4296 --
4297 --
4298 -- if the person is of type Ex-employee then ensure the most recent period
4299 -- of service for this person has been completely terminated. i.e. check
4300 -- that period_of_service.final_process_date is not null and comes before
4301 -- p_hire_date. we only fetch the 1st row (which is the latest pos).
4302 --
4303 if l_per_details_rec.system_person_type = 'EX_EMP' then
4304 --
4305 open csr_chk_period_of_service;
4306 --
4307 -- 115.57 (START)
4308 --
4309 --fetch csr_chk_period_of_service into l_final_process_date;
4310 fetch csr_chk_period_of_service into l_atd, l_lspd, l_fpd;
4311 --
4312 -- 115.57 (END)
4313 --
4314 if csr_chk_period_of_service%notfound then
4315 close csr_chk_period_of_service;
4316 --
4317 -- a period of service row does not exist for the person therefore
4318 -- we must error
4319 --
4320 -- This person does not have a previous period of service
4321 --
4322 hr_utility.set_message(801, 'HR_51013_PDS_NOT_EXIST');
4323 hr_utility.raise_error;
4324 end if;
4325 close csr_chk_period_of_service;
4326 --
4327 --
4328 -- 115.57 (START)
4329 --
4330 if (l_fpd is null) then
4331 --
4332 -- the employee cannot be terminated as the final process date has not
4333 -- been set
4334 --
4335 -- You cannot re-hire a person who does not have a final processing date
4336 -- set for their most recent period of service
4337 --
4338 hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
4339 hr_utility.raise_error;
4340 end if;
4341 --
4342 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
4343 --
4344 -- Rehire before FPD allowed (new behaviour)
4345 --
4346 if nvl(l_lspd,l_atd) >= l_effective_date then
4347 --
4348 -- the re hire date is before the current LSPD or ATD
4349 --
4350 -- You cannot re-hire an Ex-Employee before their LSPD.
4351 -- Please specify a Re-Hire date which is after the LSPD.
4352 --
4353 -- hr_utility.set_message(801, 'HR_449759_REHIRE_AFTER_LSPD');
4354 hr_utility.set_message(800, 'HR_449759_REHIRE_AFTER_LSPD'); -- product ID corrected for bug fix 8929785
4355 hr_utility.raise_error;
4356 end if;
4357 else
4358 --
4359 -- Rehire before FPD is not allowed (old behaviour)
4360 --
4361 if l_fpd >= l_effective_date then
4362 --
4363 -- the re hire date is before the current final process date
4364 --
4365 -- You cannot re-hire an Ex-Employee before their final processing date.
4366 -- Please specify a Re-Hire date which is after the final processing date.
4367 --
4368 hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
4369 hr_utility.raise_error;
4370 end if;
4371 end if;
4372 --
4373 --if (l_final_process_date is null) then
4374 -- --
4375 -- -- the employee cannot be terminated as the final process date has not
4376 -- -- been set
4377 -- --
4378 -- -- You cannot re-hire a person who does not have a final processing date
4379 -- -- set for their most recent period of service
4380 -- --
4381 -- hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
4382 -- hr_utility.raise_error;
4383 --elsif (l_final_process_date >= l_effective_date) then
4384 -- --
4385 -- -- the re hire date is before the current final process date
4386 -- --
4387 -- -- You cannot re-hire an Ex-Employee before their final processing date.
4388 -- -- Please specify a Re-Hire date which is after the final processing date.
4389 -- --
4390 -- hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
4391 -- hr_utility.raise_error;
4392 --end if;
4393 --
4394 -- 115.57 (END)
4395 --
4396 end if;
4397 --
4398 if g_debug then
4399 hr_utility.set_location(l_proc,65);
4400 end if;
4401 -- End of fix for bug 3143299
4402
4403 -- Ensure the employee number will not be changed if it exists
4404 --
4405 IF l_per_details_rec.employee_number IS NOT NULL
4406 AND NVL(p_employee_number,hr_api.g_number) <> l_per_details_rec.employee_number
4407 THEN
4408 if g_debug then
4409 hr_utility.set_location(l_proc,70);
4410 end if;
4411 p_employee_number := l_per_details_rec.employee_number;
4412 END IF;
4413 --
4414 if g_debug then
4415 hr_utility.set_location(l_proc,80);
4416 end if;
4417 --
4418 -- Check the person does not have future assignment changes
4419 --
4420 l_future_asgs_count := future_asgs_count
4421 (p_person_id => p_person_id
4422 ,p_effective_date => l_effective_date
4423 );
4424 IF l_future_asgs_count > 0
4425 THEN
4426 if g_debug then
4427 hr_utility.set_location(l_proc,90);
4428 end if;
4429 hr_utility.set_message(800,'HR_7975_ASG_INV_FUTURE_ASA');
4430 hr_utility.raise_error;
4431 END IF;
4432 --
4433 if g_debug then
4434 hr_utility.set_location(l_proc,100);
4435 end if;
4436 --
4437 -- If person type id is not null check it corresponds to the correct type for
4438 -- the persons current system person type is currently active and in the
4439 -- correct business group, otherwise set person type id to the active default
4440 -- for the correct system person type in the correct business group
4441 --
4442 if g_debug then
4443 hr_utility.set_location(l_proc,110);
4444 end if;
4445 l_system_person_type := 'EMP';
4446 per_per_bus.chk_person_type
4447 (p_person_type_id => l_person_type_id
4448 ,p_business_group_id => l_per_details_rec.business_group_id
4449 ,p_expected_sys_type => l_system_person_type
4450 );
4451 --
4452 if g_debug then
4453 hr_utility.set_location(l_proc,120);
4454 end if;
4455 --
4456 -- Check the datetrack mode
4457 --
4458 IF (l_per_details_rec.system_person_type IN ('OTHER','EX_EMP')) -- Bug 3230389
4459 THEN
4460 IF (l_datetrack_update_mode IS NULL)
4461 THEN
4462 if l_effective_date = l_per_details_rec.effective_start_date then -- 3194314
4463 l_datetrack_update_mode := hr_api.g_correction;
4464 else
4465 l_datetrack_update_mode := hr_api.g_update;
4466 end if;
4467 ELSE
4468 IF (l_datetrack_update_mode NOT IN (hr_api.g_update,hr_api.g_correction))
4469 THEN
4470 hr_utility.set_message(800,'HR_7203_DT_UPD_MODE_INVALID');
4471 hr_utility.raise_error;
4472 END IF;
4473 END IF;
4474 ELSE
4475 l_datetrack_update_mode := hr_api.g_update;
4476 END IF;
4477 --
4478 -- PTU : Added
4479
4480 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
4481 ( l_per_details_rec.business_group_id,
4482 'EMP');
4483 -- PTU : End
4484
4485 -- Code changes for Bug#14140116 Starts here --
4486
4487 open csr_earliest_date;
4488 fetch csr_earliest_date into l_earliest_date;
4489 close csr_earliest_date;
4490
4491 -- Code changes for Bug#14140116 Ends here --
4492
4493
4494 -- Update the person details to the new person type
4495 --
4496 per_per_upd.upd
4497 (p_person_id => p_person_id
4498 ,p_effective_start_date => l_per_effective_start_date
4499 ,p_effective_end_date => l_per_effective_end_date
4500 ,p_person_type_id => l_person_type_id1
4501 ,p_applicant_number => l_per_details_rec.applicant_number
4502 ,p_comment_id => l_comment_id
4503 ,p_current_applicant_flag => l_current_applicant_flag
4504 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
4505 ,p_current_employee_flag => l_current_employee_flag
4506 ,p_employee_number => p_employee_number
4507 ,p_national_identifier => p_national_identifier
4508 ,p_full_name => l_full_name
4509 ,p_object_version_number => p_object_version_number
4510 ,p_effective_date => l_effective_date
4511 ,p_datetrack_mode => l_datetrack_update_mode
4512 ,p_name_combination_warning => l_name_combination_warning
4513 ,p_dob_null_warning => l_assign_payroll_warning
4514 ,p_orig_hire_warning => l_orig_hire_warning
4515 ,p_npw_number => l_per_details_rec.npw_number
4516 ,p_per_information7 => p_per_information7 --3414274
4517 --,p_ORIGINAL_DATE_OF_HIRE => l_effective_date -- 12398266 -- Commented to resolve Bug#14140116
4518 ,p_original_date_of_hire => nvl(l_earliest_date,l_effective_date) -- Bug#14140116
4519 );
4520 --
4521 if g_debug then
4522 hr_utility.set_location(l_proc,130);
4523 end if;
4524 --
4525 -- add to current security list
4526 --
4527 hr_security_internal.populate_new_person(l_per_details_rec.business_group_id,p_person_id);
4528 --
4529 if g_debug then
4530 hr_utility.set_location(l_proc,135);
4531 end if;
4532 --
4533 -- Create an period of service for the person
4534 --
4535 per_pds_ins.ins
4536 (p_effective_date => l_effective_date
4537 ,p_business_group_id => l_per_details_rec.business_group_id
4538 ,p_person_id => p_person_id
4539 ,p_date_start => l_effective_date
4540 ,p_validate_df_flex => false
4541 ,p_period_of_service_id => l_period_of_service_id
4542 ,p_object_version_number => l_pds_object_version_number
4543 );
4544 --
4545 if g_debug then
4546 hr_utility.set_location(l_proc,140);
4547 end if;
4548 --
4549 -- Create a default employee assignment for the person
4550 --
4551 hr_assignment_internal.create_default_emp_asg
4552 (p_effective_date => l_effective_date
4553 ,p_business_group_id => l_per_details_rec.business_group_id
4554 ,p_person_id => p_person_id
4555 ,p_period_of_service_id => l_period_of_service_id
4556 ,p_assignment_id => l_assignment_id
4557 ,p_object_version_number => l_asg_object_version_number
4558 ,p_assignment_sequence => l_assignment_sequence
4559 ,p_assignment_number => l_assignment_number
4560 );
4561 --
4562 if g_debug then
4563 hr_utility.set_location(l_proc,150);
4564 end if;
4565 --
4566 -- 115.57 (START)
4567 --
4568 --
4569 -- If rehire before FPD is allowed, any resulting overlapping PDS
4570 -- will require updating the terminated primary assignment to
4571 -- secondary.
4572 --
4573 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
4574 manage_rehire_primary_asgs(p_person_id => p_person_id
4575 ,p_rehire_date => l_effective_date
4576 ,p_cancel => 'N'
4577 );
4578 end if;
4579 --
4580 if g_debug then
4581 hr_utility.set_location(l_proc,155);
4582 end if;
4583 --
4584 -- 115.57 (END)
4585 --
4586 --
4587 -- Create person type usage record
4588 -- No Longer Required: This is automatically created on insert of
4589 -- a period of service record above.
4590 --
4591 /*
4592 hr_per_type_usage_internal.create_person_type_usage
4593 (p_effective_date => l_effective_date
4594 ,p_person_id => p_person_id
4595 ,p_person_type_id => l_person_type_id
4596 ,p_person_type_usage_id => l_person_type_usage_id
4597 ,p_effective_start_date => l_effective_start_date
4598 ,p_effective_end_date => l_effective_end_date
4599 ,p_object_version_number => l_ptu_object_version_number
4600 );
4601 */
4602
4603 -- PTU : Following Code has been added
4604
4605 --start changes for bug8506648
4606 if per_periods_of_service_pkg_v2.IsBackToBackContract(p_person_id, l_effective_date) then
4607 hr_per_type_usage_internal.maintain_person_type_usage
4608 (p_effective_date => l_effective_date
4609 ,p_person_id => p_person_id
4610 ,p_person_type_id => l_person_type_id
4611 ,p_datetrack_update_mode => l_datetrack_update_mode -- Bug 3230389
4612 );
4613 else
4614 hr_per_type_usage_internal.maintain_person_type_usage
4615 (p_effective_date => l_effective_date
4616 ,p_person_id => p_person_id
4617 ,p_person_type_id => l_person_type_id
4618 ,p_datetrack_update_mode => hr_api.g_update
4619 );
4620 end if;
4621 --end changes for bug8506648
4622
4623 -- PTU : End of changes
4624 -- 1766066: added call for contact start date enh.
4625 --
4626 per_people12_pkg.maintain_coverage(p_person_id => p_person_id
4627 ,p_type => 'EMP'
4628 );
4629 -- 1766066 end.
4630 --
4631 -- Call After Process User Hook
4632 --
4633 BEGIN
4634 hr_employee_bk4.hire_into_job_a
4635 (p_effective_date => l_effective_date
4636 ,p_person_id => p_person_id
4637 ,p_business_group_id => l_per_details_rec.business_group_id
4638 ,p_object_version_number => p_object_version_number
4639 ,p_datetrack_update_mode => p_datetrack_update_mode
4640 ,p_employee_number => p_employee_number
4641 ,p_person_type_id => p_person_type_id
4642 ,p_national_identifier => p_national_identifier
4643 ,p_per_information7 => p_per_information7 -- 3414274
4644 ,p_assignment_id => l_assignment_id --Bug#3919096
4645 ,p_effective_start_date => l_per_effective_start_date
4646 ,p_effective_end_date => l_per_effective_end_date
4647 ,p_assign_payroll_warning => l_assign_payroll_warning
4648 ,p_orig_hire_warning => l_orig_hire_warning
4649 );
4650 EXCEPTION
4651 WHEN hr_api.cannot_find_prog_unit
4652 THEN
4653 hr_api.cannot_find_prog_unit_error
4654 (p_module_name => 'HIRE_INTO_JOB'
4655 ,p_hook_type => 'AP'
4656 );
4657 END;
4658 --
4659 if g_debug then
4660 hr_utility.set_location(l_proc,160);
4661 end if;
4662 --
4663 -- When in validation only mode raise validate_enabled exception
4664 --
4665 IF p_validate
4666 THEN
4667 RAISE hr_api.validate_enabled;
4668 END IF;
4669 --
4670 -- Set OUT parameters
4671 --
4672 p_assignment_id := l_assignment_id; --Bug#3919096
4673 p_effective_start_date := l_per_effective_start_date;
4674 p_effective_end_date := l_per_effective_end_date;
4675 p_assign_payroll_warning := l_assign_payroll_warning;
4676 p_orig_hire_warning := l_orig_hire_warning;
4677 --
4678 if g_debug then
4679 hr_utility.set_location(' Leaving:'||l_proc,1000);
4680 end if;
4681 --
4682 EXCEPTION
4683 --
4684 WHEN hr_api.validate_enabled
4685 THEN
4686 --
4687 -- In validation only mode
4688 -- Rollback to savepoint
4689 -- Set relevant output warning arguments
4690 -- Reset any key or derived arguments
4691 --
4692 ROLLBACK TO hire_into_job;
4693 p_object_version_number := l_object_version_number;
4694 p_employee_number := l_employee_number;
4695 p_assignment_id := NULL; --Bug#3919096
4696 p_effective_start_date := NULL;
4697 p_effective_end_date := NULL;
4698 p_assign_payroll_warning := l_assign_payroll_warning;
4699 p_orig_hire_warning := l_orig_hire_warning;
4700 --
4701 WHEN OTHERS
4702 THEN
4703 --
4704 -- Validation or unexpected error occured
4705 -- Ensure opened non-local cursors are closed
4706 -- Rollback to savepoint
4707 -- Re-raise exception
4708 --
4709 ROLLBACK TO hire_into_job;
4710 --
4711 -- set in out parameters and set out parameters
4712 --
4713 p_object_version_number := l_ovn;
4714 p_employee_number := l_emp_num;
4715 p_assignment_id := NULL; --Bug#3919096
4716 p_effective_start_date := NULL;
4717 p_effective_end_date := NULL;
4718 p_assign_payroll_warning := false;
4719 p_orig_hire_warning := false;
4720 --
4721 IF csr_per_details%ISOPEN
4722 THEN
4723 CLOSE csr_per_details;
4724 END IF;
4725 RAISE;
4726 --
4727 END hire_into_job;
4728 --
4729 -- 115.57 (START)
4730 --
4731 -- ----------------------------------------------------------------------------
4732 -- |---------------------< Update_Rehire_Primary_Asgs >-----------------------|
4733 -- ----------------------------------------------------------------------------
4734 -- {Start Of Comments}
4735 --
4736 -- Description:
4737 -- Handles the update of ASG records for rehire before FPD which leads to
4738 -- overlapping PDS and cancel rehire with overlapping PDS. This is intended
4739 -- to be invoked from MANAGE_REHIRE_PRIMARY_ASGS
4740 --
4741 -- Prerequisites:
4742 -- The person assignments and periods of service must already be present.
4743 --
4744 -- In Parameters:
4745 -- Name Reqd Type Description
4746 -- p_person_id Yes number Identifier for the person
4747 -- p_rehire_date Yes date Re-Hire Date
4748 -- p_cancel Yes varchar2 'Y' indicates cancel rehire
4749 -- 'N' indicates rehire
4750 --
4751 -- Post Success:
4752 -- No error is raised if the new hire date is validSG records are updated
4753 -- with correct primary flag values for rehire and cancel rehire.
4754 --
4755 -- Post Failure:
4756 -- An error is raised and control returned.
4757 --
4758 -- Access Status:
4759 -- For Oracle Internal use only.
4760 --
4761 -- {End Of Comments}
4762 --
4763 PROCEDURE update_rehire_primary_asgs
4764 (p_person_id IN NUMBER
4765 ,p_rehire_date IN DATE
4766 ,p_cancel IN VARCHAR2
4767 ) IS
4768 --
4769 l_proc VARCHAR2(80) := g_package||'update_rehire_primary_asgs';
4770 --
4771 -- Cursor to get primary assignment on specified date
4772 --
4773 CURSOR c_prim_asg
4774 (p_person_id IN per_all_people_f.person_id%TYPE
4775 ,p_effective_date IN DATE
4776 ) IS
4777 SELECT assignment_id
4778 ,effective_start_date
4779 ,effective_end_date
4780 ,business_group_id
4781 ,recruiter_id
4782 ,grade_id
4783 ,position_id
4784 ,job_id
4785 ,assignment_status_type_id
4786 ,payroll_id
4787 ,location_id
4788 ,person_referred_by_id
4789 ,supervisor_id
4790 ,special_ceiling_step_id
4791 ,person_id
4792 ,recruitment_activity_id
4793 ,source_organization_id
4794 ,organization_id
4795 ,people_group_id
4796 ,soft_coding_keyflex_id
4797 ,vacancy_id
4798 ,pay_basis_id
4799 ,assignment_sequence
4800 ,assignment_type
4801 ,primary_flag
4802 ,application_id
4803 ,assignment_number
4804 ,change_reason
4805 ,comment_id
4806 ,date_probation_end
4807 ,default_code_comb_id
4808 ,employment_category
4809 ,frequency
4810 ,internal_address_line
4811 ,manager_flag
4812 ,normal_hours
4813 ,perf_review_period
4814 ,perf_review_period_frequency
4815 ,period_of_service_id
4816 ,probation_period
4817 ,probation_unit
4818 ,sal_review_period
4819 ,sal_review_period_frequency
4820 ,set_of_books_id
4821 ,source_type
4822 ,time_normal_finish
4823 ,time_normal_start
4824 ,bargaining_unit_code
4825 ,labour_union_member_flag
4826 ,hourly_salaried_code
4827 ,request_id
4828 ,program_application_id
4829 ,program_id
4830 ,program_update_date
4831 ,ass_attribute_category
4832 ,ass_attribute1
4833 ,ass_attribute2
4834 ,ass_attribute3
4835 ,ass_attribute4
4836 ,ass_attribute5
4837 ,ass_attribute6
4838 ,ass_attribute7
4839 ,ass_attribute8
4840 ,ass_attribute9
4841 ,ass_attribute10
4842 ,ass_attribute11
4843 ,ass_attribute12
4844 ,ass_attribute13
4845 ,ass_attribute14
4846 ,ass_attribute15
4847 ,ass_attribute16
4848 ,ass_attribute17
4849 ,ass_attribute18
4850 ,ass_attribute19
4851 ,ass_attribute20
4852 ,ass_attribute21
4853 ,ass_attribute22
4854 ,ass_attribute23
4855 ,ass_attribute24
4856 ,ass_attribute25
4857 ,ass_attribute26
4858 ,ass_attribute27
4859 ,ass_attribute28
4860 ,ass_attribute29
4861 ,ass_attribute30
4862 ,last_update_date
4863 ,last_updated_by
4864 ,last_update_login
4865 ,created_by
4866 ,creation_date
4867 ,title
4868 ,object_version_number
4869 ,contract_id
4870 ,cagr_id_flex_num
4871 ,cagr_grade_def_id
4872 ,establishment_id
4873 ,collective_agreement_id
4874 ,notice_period
4875 ,notice_period_uom
4876 ,employee_category
4877 ,work_at_home
4878 ,job_post_source_name
4879 ,period_of_placement_date_start
4880 ,vendor_id
4881 ,vendor_site_id
4882 ,po_header_id
4883 ,po_line_id
4884 ,projected_assignment_end
4885 ,vendor_employee_number
4886 ,vendor_assignment_number
4887 ,assignment_category
4888 ,project_title
4889 ,grade_ladder_pgm_id
4890 ,supervisor_assignment_id
4891 FROM per_assignments_f
4892 WHERE person_id = c_prim_asg.p_person_id
4893 AND primary_flag = 'Y'
4894 AND assignment_type <> 'B' -- 115.59
4895 AND c_prim_asg.p_effective_date BETWEEN effective_start_date
4896 AND effective_end_date;
4897 --
4898 lr_prim_asg c_prim_asg%ROWTYPE;
4899 --
4900 BEGIN
4901 --
4902 hr_utility.set_location('Entering:'|| l_proc, 5);
4903 --
4904 -- Issue a savepoint.
4905 --
4906 SAVEPOINT update_rehire_primary_asgs;
4907 --
4908 IF p_cancel = 'Y' THEN
4909 --
4910 -- Perform Cancel Rehire updates to ASG records
4911 --
4912 hr_utility.set_location(l_proc, 10);
4913 --
4914 -- Get the primary assignment on (rehire date - 1)
4915 --
4916 OPEN c_prim_asg (p_person_id
4917 ,(p_rehire_date-1)
4918 );
4919 FETCH c_prim_asg INTO lr_prim_asg;
4920 IF c_prim_asg%NOTFOUND THEN
4921 hr_utility.set_message(800,'PER_52595_PRIM_ASG_INV');
4922 hr_utility.raise_error;
4923 END IF;
4924 CLOSE c_prim_asg;
4925 --
4926 hr_utility.set_location(l_proc, 20);
4927 --
4928 -- Update all assignments records for this assignment with
4929 -- ESD >= rehire date setting primary flag to 'Y'
4930 --
4931 UPDATE per_assignments_f
4932 SET primary_flag = 'Y'
4933 ,object_version_number = object_version_number + 1
4934 WHERE assignment_id = lr_prim_asg.assignment_id
4935 AND assignment_type <> 'B' -- 115.59
4936 AND effective_start_date >= p_rehire_date;
4937 --
4938 ELSIF p_cancel = 'N' THEN
4939 --
4940 -- Perform Rehire updates to ASG records
4941 --
4942 hr_utility.set_location(l_proc, 30);
4943 --
4944 -- Get the primary assignment on rehire date
4945 --
4946 OPEN c_prim_asg (p_person_id
4947 ,p_rehire_date
4948 );
4949 FETCH c_prim_asg INTO lr_prim_asg;
4950 IF c_prim_asg%NOTFOUND THEN
4951 hr_utility.set_message(800,'PER_52595_PRIM_ASG_INV');
4952 hr_utility.raise_error;
4953 END IF;
4954 CLOSE c_prim_asg;
4955 --
4956 IF p_rehire_date = lr_prim_asg.effective_start_date THEN
4957 --
4958 hr_utility.set_location(l_proc, 40);
4959 --
4960 -- Set the primary assignment flag to 'N' for this record.
4961 --
4962 UPDATE per_assignments_f
4963 SET primary_flag = 'N'
4964 ,object_version_number = object_version_number + 1
4965 WHERE assignment_id = lr_prim_asg.assignment_id
4966 AND assignment_type <> 'B' -- 115.59
4967 AND effective_start_date = p_rehire_date;
4968 --
4969 ELSIF p_rehire_date = lr_prim_asg.effective_end_date THEN
4970 --
4971 hr_utility.set_location(l_proc, 50);
4972 --
4973 -- Update EED to rehire date - 1
4974 --
4975 UPDATE per_assignments_f
4976 SET effective_end_date = (p_rehire_date - 1)
4977 ,object_version_number = object_version_number + 1
4978 WHERE assignment_id = lr_prim_asg.assignment_id
4979 AND assignment_type <> 'B' -- 115.59
4980 AND effective_end_date = p_rehire_date;
4981 --
4982 hr_utility.set_location(l_proc, 60);
4983 --
4984 -- Insert new record with primary flag 'N' and ESD
4985 -- same as EED same as rehire date
4986 --
4987 INSERT INTO per_all_assignments_f
4988 (assignment_id
4989 ,effective_start_date
4990 ,effective_end_date
4991 ,business_group_id
4992 ,recruiter_id
4993 ,grade_id
4994 ,position_id
4995 ,job_id
4996 ,assignment_status_type_id
4997 ,payroll_id
4998 ,location_id
4999 ,person_referred_by_id
5000 ,supervisor_id
5001 ,special_ceiling_step_id
5002 ,person_id
5003 ,recruitment_activity_id
5004 ,source_organization_id
5005 ,organization_id
5006 ,people_group_id
5007 ,soft_coding_keyflex_id
5008 ,vacancy_id
5009 ,pay_basis_id
5010 ,assignment_sequence
5011 ,assignment_type
5012 ,primary_flag
5013 ,application_id
5014 ,assignment_number
5015 ,change_reason
5016 ,comment_id
5017 ,date_probation_end
5018 ,default_code_comb_id
5019 ,employment_category
5020 ,frequency
5021 ,internal_address_line
5022 ,manager_flag
5023 ,normal_hours
5024 ,perf_review_period
5025 ,perf_review_period_frequency
5026 ,period_of_service_id
5027 ,probation_period
5028 ,probation_unit
5029 ,sal_review_period
5030 ,sal_review_period_frequency
5031 ,set_of_books_id
5032 ,source_type
5033 ,time_normal_finish
5034 ,time_normal_start
5035 ,bargaining_unit_code
5036 ,labour_union_member_flag
5037 ,hourly_salaried_code
5038 ,request_id
5039 ,program_application_id
5040 ,program_id
5041 ,program_update_date
5042 ,ass_attribute_category
5043 ,ass_attribute1
5044 ,ass_attribute2
5045 ,ass_attribute3
5046 ,ass_attribute4
5047 ,ass_attribute5
5048 ,ass_attribute6
5049 ,ass_attribute7
5050 ,ass_attribute8
5051 ,ass_attribute9
5052 ,ass_attribute10
5053 ,ass_attribute11
5054 ,ass_attribute12
5055 ,ass_attribute13
5056 ,ass_attribute14
5057 ,ass_attribute15
5058 ,ass_attribute16
5059 ,ass_attribute17
5060 ,ass_attribute18
5061 ,ass_attribute19
5062 ,ass_attribute20
5063 ,ass_attribute21
5064 ,ass_attribute22
5065 ,ass_attribute23
5066 ,ass_attribute24
5067 ,ass_attribute25
5068 ,ass_attribute26
5069 ,ass_attribute27
5070 ,ass_attribute28
5071 ,ass_attribute29
5072 ,ass_attribute30
5073 ,last_update_date
5074 ,last_updated_by
5075 ,last_update_login
5076 ,created_by
5077 ,creation_date
5078 ,title
5079 ,object_version_number
5080 ,contract_id
5081 ,cagr_id_flex_num
5082 ,cagr_grade_def_id
5083 ,establishment_id
5084 ,collective_agreement_id
5085 ,notice_period
5086 ,notice_period_uom
5087 ,employee_category
5088 ,work_at_home
5089 ,job_post_source_name
5090 ,period_of_placement_date_start
5091 ,vendor_id
5092 ,vendor_site_id
5093 ,po_header_id
5094 ,po_line_id
5095 ,projected_assignment_end
5096 ,vendor_employee_number
5097 ,vendor_assignment_number
5098 ,assignment_category
5099 ,project_title
5100 ,grade_ladder_pgm_id
5101 ,supervisor_assignment_id
5102 )
5103 VALUES
5104 (lr_prim_asg.assignment_id
5105 ,p_rehire_date -- New ESD
5106 ,p_rehire_date -- New EED
5107 ,lr_prim_asg.business_group_id
5108 ,lr_prim_asg.recruiter_id
5109 ,lr_prim_asg.grade_id
5110 ,lr_prim_asg.position_id
5111 ,lr_prim_asg.job_id
5112 ,lr_prim_asg.assignment_status_type_id
5113 ,lr_prim_asg.payroll_id
5114 ,lr_prim_asg.location_id
5115 ,lr_prim_asg.person_referred_by_id
5116 ,lr_prim_asg.supervisor_id
5117 ,lr_prim_asg.special_ceiling_step_id
5118 ,lr_prim_asg.person_id
5119 ,lr_prim_asg.recruitment_activity_id
5120 ,lr_prim_asg.source_organization_id
5121 ,lr_prim_asg.organization_id
5122 ,lr_prim_asg.people_group_id
5123 ,lr_prim_asg.soft_coding_keyflex_id
5124 ,lr_prim_asg.vacancy_id
5125 ,lr_prim_asg.pay_basis_id
5126 ,lr_prim_asg.assignment_sequence
5127 ,lr_prim_asg.assignment_type
5128 ,'N' -- New Primary Flag
5129 ,lr_prim_asg.application_id
5130 ,lr_prim_asg.assignment_number
5131 ,lr_prim_asg.change_reason
5132 ,lr_prim_asg.comment_id
5133 ,lr_prim_asg.date_probation_end
5134 ,lr_prim_asg.default_code_comb_id
5135 ,lr_prim_asg.employment_category
5136 ,lr_prim_asg.frequency
5137 ,lr_prim_asg.internal_address_line
5138 ,lr_prim_asg.manager_flag
5139 ,lr_prim_asg.normal_hours
5140 ,lr_prim_asg.perf_review_period
5141 ,lr_prim_asg.perf_review_period_frequency
5142 ,lr_prim_asg.period_of_service_id
5143 ,lr_prim_asg.probation_period
5144 ,lr_prim_asg.probation_unit
5145 ,lr_prim_asg.sal_review_period
5146 ,lr_prim_asg.sal_review_period_frequency
5147 ,lr_prim_asg.set_of_books_id
5148 ,lr_prim_asg.source_type
5149 ,lr_prim_asg.time_normal_finish
5150 ,lr_prim_asg.time_normal_start
5151 ,lr_prim_asg.bargaining_unit_code
5152 ,lr_prim_asg.labour_union_member_flag
5153 ,lr_prim_asg.hourly_salaried_code
5154 ,lr_prim_asg.request_id
5155 ,lr_prim_asg.program_application_id
5156 ,lr_prim_asg.program_id
5157 ,lr_prim_asg.program_update_date
5158 ,lr_prim_asg.ass_attribute_category
5159 ,lr_prim_asg.ass_attribute1
5160 ,lr_prim_asg.ass_attribute2
5161 ,lr_prim_asg.ass_attribute3
5162 ,lr_prim_asg.ass_attribute4
5163 ,lr_prim_asg.ass_attribute5
5164 ,lr_prim_asg.ass_attribute6
5165 ,lr_prim_asg.ass_attribute7
5166 ,lr_prim_asg.ass_attribute8
5167 ,lr_prim_asg.ass_attribute9
5168 ,lr_prim_asg.ass_attribute10
5169 ,lr_prim_asg.ass_attribute11
5170 ,lr_prim_asg.ass_attribute12
5171 ,lr_prim_asg.ass_attribute13
5172 ,lr_prim_asg.ass_attribute14
5173 ,lr_prim_asg.ass_attribute15
5174 ,lr_prim_asg.ass_attribute16
5175 ,lr_prim_asg.ass_attribute17
5176 ,lr_prim_asg.ass_attribute18
5177 ,lr_prim_asg.ass_attribute19
5178 ,lr_prim_asg.ass_attribute20
5179 ,lr_prim_asg.ass_attribute21
5180 ,lr_prim_asg.ass_attribute22
5181 ,lr_prim_asg.ass_attribute23
5182 ,lr_prim_asg.ass_attribute24
5183 ,lr_prim_asg.ass_attribute25
5184 ,lr_prim_asg.ass_attribute26
5185 ,lr_prim_asg.ass_attribute27
5186 ,lr_prim_asg.ass_attribute28
5187 ,lr_prim_asg.ass_attribute29
5188 ,lr_prim_asg.ass_attribute30
5189 ,TRUNC(SYSDATE) -- New Last Update Date
5190 ,-1 -- New Updated By
5191 ,-1 -- New Update Login
5192 ,-1 -- New Created By
5193 ,TRUNC(SYSDATE) -- New Creation Date
5194 ,lr_prim_asg.title
5195 ,1 -- New OVN
5196 ,lr_prim_asg.contract_id
5197 ,lr_prim_asg.cagr_id_flex_num
5198 ,lr_prim_asg.cagr_grade_def_id
5199 ,lr_prim_asg.establishment_id
5200 ,lr_prim_asg.collective_agreement_id
5201 ,lr_prim_asg.notice_period
5202 ,lr_prim_asg.notice_period_uom
5203 ,lr_prim_asg.employee_category
5204 ,lr_prim_asg.work_at_home
5205 ,lr_prim_asg.job_post_source_name
5206 ,lr_prim_asg.period_of_placement_date_start
5207 ,lr_prim_asg.vendor_id
5208 ,lr_prim_asg.vendor_site_id
5209 ,lr_prim_asg.po_header_id
5210 ,lr_prim_asg.po_line_id
5211 ,lr_prim_asg.projected_assignment_end
5212 ,lr_prim_asg.vendor_employee_number
5213 ,lr_prim_asg.vendor_assignment_number
5214 ,lr_prim_asg.assignment_category
5215 ,lr_prim_asg.project_title
5216 ,lr_prim_asg.grade_ladder_pgm_id
5217 ,lr_prim_asg.supervisor_assignment_id
5218 );
5219 --
5220 ELSE -- rehire date between but not inclusive of ESD and EED
5221 --
5222 hr_utility.set_location(l_proc, 70);
5223 --
5224 -- Update EED to rehire date - 1
5225 --
5226 UPDATE per_assignments_f
5227 SET effective_end_date = (p_rehire_date - 1)
5228 ,object_version_number = object_version_number + 1
5229 WHERE assignment_id = lr_prim_asg.assignment_id
5230 AND assignment_type <> 'B' -- 115.59
5231 AND effective_start_date = lr_prim_asg.effective_start_date;
5232 --
5233 hr_utility.set_location(l_proc, 80);
5234 --
5235 -- Insert new record primary flag 'N' and ESD
5236 -- same as rehire date
5237 --
5238 INSERT INTO per_all_assignments_f
5239 (assignment_id
5240 ,effective_start_date
5241 ,effective_end_date
5242 ,business_group_id
5243 ,recruiter_id
5244 ,grade_id
5245 ,position_id
5246 ,job_id
5247 ,assignment_status_type_id
5248 ,payroll_id
5249 ,location_id
5250 ,person_referred_by_id
5251 ,supervisor_id
5252 ,special_ceiling_step_id
5253 ,person_id
5254 ,recruitment_activity_id
5255 ,source_organization_id
5256 ,organization_id
5257 ,people_group_id
5258 ,soft_coding_keyflex_id
5259 ,vacancy_id
5260 ,pay_basis_id
5261 ,assignment_sequence
5262 ,assignment_type
5263 ,primary_flag
5264 ,application_id
5265 ,assignment_number
5266 ,change_reason
5267 ,comment_id
5268 ,date_probation_end
5269 ,default_code_comb_id
5270 ,employment_category
5271 ,frequency
5272 ,internal_address_line
5273 ,manager_flag
5274 ,normal_hours
5275 ,perf_review_period
5276 ,perf_review_period_frequency
5277 ,period_of_service_id
5278 ,probation_period
5279 ,probation_unit
5280 ,sal_review_period
5281 ,sal_review_period_frequency
5282 ,set_of_books_id
5283 ,source_type
5284 ,time_normal_finish
5285 ,time_normal_start
5286 ,bargaining_unit_code
5287 ,labour_union_member_flag
5288 ,hourly_salaried_code
5289 ,request_id
5290 ,program_application_id
5291 ,program_id
5292 ,program_update_date
5293 ,ass_attribute_category
5294 ,ass_attribute1
5295 ,ass_attribute2
5296 ,ass_attribute3
5297 ,ass_attribute4
5298 ,ass_attribute5
5299 ,ass_attribute6
5300 ,ass_attribute7
5301 ,ass_attribute8
5302 ,ass_attribute9
5303 ,ass_attribute10
5304 ,ass_attribute11
5305 ,ass_attribute12
5306 ,ass_attribute13
5307 ,ass_attribute14
5308 ,ass_attribute15
5309 ,ass_attribute16
5310 ,ass_attribute17
5311 ,ass_attribute18
5312 ,ass_attribute19
5313 ,ass_attribute20
5314 ,ass_attribute21
5315 ,ass_attribute22
5316 ,ass_attribute23
5317 ,ass_attribute24
5318 ,ass_attribute25
5319 ,ass_attribute26
5320 ,ass_attribute27
5321 ,ass_attribute28
5322 ,ass_attribute29
5323 ,ass_attribute30
5324 ,last_update_date
5325 ,last_updated_by
5326 ,last_update_login
5327 ,created_by
5328 ,creation_date
5329 ,title
5330 ,object_version_number
5331 ,contract_id
5332 ,cagr_id_flex_num
5333 ,cagr_grade_def_id
5334 ,establishment_id
5335 ,collective_agreement_id
5336 ,notice_period
5337 ,notice_period_uom
5338 ,employee_category
5339 ,work_at_home
5340 ,job_post_source_name
5341 ,period_of_placement_date_start
5342 ,vendor_id
5343 ,vendor_site_id
5344 ,po_header_id
5345 ,po_line_id
5346 ,projected_assignment_end
5347 ,vendor_employee_number
5348 ,vendor_assignment_number
5349 ,assignment_category
5350 ,project_title
5351 ,grade_ladder_pgm_id
5352 ,supervisor_assignment_id
5353 )
5354 VALUES
5355 (lr_prim_asg.assignment_id
5356 ,p_rehire_date -- New ESD
5357 ,lr_prim_asg.effective_end_date
5358 ,lr_prim_asg.business_group_id
5359 ,lr_prim_asg.recruiter_id
5360 ,lr_prim_asg.grade_id
5361 ,lr_prim_asg.position_id
5362 ,lr_prim_asg.job_id
5363 ,lr_prim_asg.assignment_status_type_id
5364 ,lr_prim_asg.payroll_id
5365 ,lr_prim_asg.location_id
5366 ,lr_prim_asg.person_referred_by_id
5367 ,lr_prim_asg.supervisor_id
5368 ,lr_prim_asg.special_ceiling_step_id
5369 ,lr_prim_asg.person_id
5370 ,lr_prim_asg.recruitment_activity_id
5371 ,lr_prim_asg.source_organization_id
5372 ,lr_prim_asg.organization_id
5373 ,lr_prim_asg.people_group_id
5374 ,lr_prim_asg.soft_coding_keyflex_id
5375 ,lr_prim_asg.vacancy_id
5376 ,lr_prim_asg.pay_basis_id
5377 ,lr_prim_asg.assignment_sequence
5378 ,lr_prim_asg.assignment_type
5379 ,'N' -- New Primary Flag
5380 ,lr_prim_asg.application_id
5381 ,lr_prim_asg.assignment_number
5382 ,lr_prim_asg.change_reason
5383 ,lr_prim_asg.comment_id
5384 ,lr_prim_asg.date_probation_end
5385 ,lr_prim_asg.default_code_comb_id
5386 ,lr_prim_asg.employment_category
5387 ,lr_prim_asg.frequency
5388 ,lr_prim_asg.internal_address_line
5389 ,lr_prim_asg.manager_flag
5390 ,lr_prim_asg.normal_hours
5391 ,lr_prim_asg.perf_review_period
5392 ,lr_prim_asg.perf_review_period_frequency
5393 ,lr_prim_asg.period_of_service_id
5394 ,lr_prim_asg.probation_period
5395 ,lr_prim_asg.probation_unit
5396 ,lr_prim_asg.sal_review_period
5397 ,lr_prim_asg.sal_review_period_frequency
5398 ,lr_prim_asg.set_of_books_id
5399 ,lr_prim_asg.source_type
5400 ,lr_prim_asg.time_normal_finish
5401 ,lr_prim_asg.time_normal_start
5402 ,lr_prim_asg.bargaining_unit_code
5403 ,lr_prim_asg.labour_union_member_flag
5404 ,lr_prim_asg.hourly_salaried_code
5405 ,lr_prim_asg.request_id
5406 ,lr_prim_asg.program_application_id
5407 ,lr_prim_asg.program_id
5408 ,lr_prim_asg.program_update_date
5409 ,lr_prim_asg.ass_attribute_category
5410 ,lr_prim_asg.ass_attribute1
5411 ,lr_prim_asg.ass_attribute2
5412 ,lr_prim_asg.ass_attribute3
5413 ,lr_prim_asg.ass_attribute4
5414 ,lr_prim_asg.ass_attribute5
5415 ,lr_prim_asg.ass_attribute6
5416 ,lr_prim_asg.ass_attribute7
5417 ,lr_prim_asg.ass_attribute8
5418 ,lr_prim_asg.ass_attribute9
5419 ,lr_prim_asg.ass_attribute10
5420 ,lr_prim_asg.ass_attribute11
5421 ,lr_prim_asg.ass_attribute12
5422 ,lr_prim_asg.ass_attribute13
5423 ,lr_prim_asg.ass_attribute14
5424 ,lr_prim_asg.ass_attribute15
5425 ,lr_prim_asg.ass_attribute16
5426 ,lr_prim_asg.ass_attribute17
5427 ,lr_prim_asg.ass_attribute18
5428 ,lr_prim_asg.ass_attribute19
5429 ,lr_prim_asg.ass_attribute20
5430 ,lr_prim_asg.ass_attribute21
5431 ,lr_prim_asg.ass_attribute22
5432 ,lr_prim_asg.ass_attribute23
5433 ,lr_prim_asg.ass_attribute24
5434 ,lr_prim_asg.ass_attribute25
5435 ,lr_prim_asg.ass_attribute26
5436 ,lr_prim_asg.ass_attribute27
5437 ,lr_prim_asg.ass_attribute28
5438 ,lr_prim_asg.ass_attribute29
5439 ,lr_prim_asg.ass_attribute30
5440 ,TRUNC(SYSDATE) -- New Last Update Date
5441 ,-1 -- New Updated By
5442 ,-1 -- New Update Login
5443 ,-1 -- New Created By
5444 ,TRUNC(SYSDATE) -- New Creation Date
5445 ,lr_prim_asg.title
5446 ,1 -- New OVN
5447 ,lr_prim_asg.contract_id
5448 ,lr_prim_asg.cagr_id_flex_num
5449 ,lr_prim_asg.cagr_grade_def_id
5450 ,lr_prim_asg.establishment_id
5451 ,lr_prim_asg.collective_agreement_id
5452 ,lr_prim_asg.notice_period
5453 ,lr_prim_asg.notice_period_uom
5454 ,lr_prim_asg.employee_category
5455 ,lr_prim_asg.work_at_home
5456 ,lr_prim_asg.job_post_source_name
5457 ,lr_prim_asg.period_of_placement_date_start
5458 ,lr_prim_asg.vendor_id
5459 ,lr_prim_asg.vendor_site_id
5460 ,lr_prim_asg.po_header_id
5461 ,lr_prim_asg.po_line_id
5462 ,lr_prim_asg.projected_assignment_end
5463 ,lr_prim_asg.vendor_employee_number
5464 ,lr_prim_asg.vendor_assignment_number
5465 ,lr_prim_asg.assignment_category
5466 ,lr_prim_asg.project_title
5467 ,lr_prim_asg.grade_ladder_pgm_id
5468 ,lr_prim_asg.supervisor_assignment_id
5469 );
5470 --
5471 END IF;
5472 --
5473 hr_utility.set_location(l_proc, 90);
5474 --
5475 -- Update assignments where ESD > rehire date setting the
5476 -- primary flag to 'N'
5477 --
5478 UPDATE per_assignments_f
5479 SET primary_flag = 'N'
5480 ,object_version_number = object_version_number + 1
5481 WHERE assignment_id = lr_prim_asg.assignment_id
5482 AND effective_start_date > p_rehire_date;
5483 --
5484 END IF; -- check p_cancel
5485 --
5486 hr_utility.set_location('Leaving:'|| l_proc, 9998);
5487 --
5488 EXCEPTION
5489 --
5490 WHEN OTHERS THEN
5491 --
5492 -- An unexpected error has occurred
5493 -- No OUT parameters need to be set
5494 -- No cursors need to be closed
5495 --
5496 hr_utility.set_location('Leaving:'|| l_proc, 9999);
5497 ROLLBACK TO update_rehire_primary_asgs;
5498 RAISE;
5499 --
5500 END update_rehire_primary_asgs;
5501 --
5502 -- ----------------------------------------------------------------------------
5503 -- |-----------------------< MANAGE_REHIRE_PRIMARY_ASGS >---------------------|
5504 -- ----------------------------------------------------------------------------
5505 -- {Start Of Comments}
5506 --
5507 -- Description: This procedure manages the switching of the primary but
5508 -- terminated terminated assignment of an employee on the old
5509 -- period of service to a secondary assignment during rehire
5510 -- and swithcing it back should the rehire be cancelled.
5511 --
5512 -- Prerequisites: None
5513 --
5514 -- In Parameters:
5515 -- Name Reqd Type Description
5516 -- P_PERSON_ID Yes NUMBER Person Identifier
5517 -- P_REHIRE_DATE Yes DATE Employee Re-hire date
5518 -- P_CANCEL Yes VARCHAR2 Flags whether rehire or cancel
5519 -- rehire invocation.
5520 --
5521 -- Post Success:
5522 -- The TERM_ASSIGN assignment record will be flipped from primary to
5523 -- secondary if rehire before FPD and flipped back to primary if the
5524 -- rehire is being cancelled.
5525 --
5526 -- Name Type Description
5527 -- - - -
5528 -- Post Failure:
5529 -- An exception will be raised depending on the nature of failure.
5530 --
5531 -- Access Status:
5532 -- Internal Development Use Only.
5533 --
5534 -- {End Of Comments}
5535 --
5536 procedure manage_rehire_primary_asgs
5537 (p_person_id in number
5538 ,p_rehire_date in date
5539 ,p_cancel in varchar2
5540 ) IS
5541 --
5542 l_proc VARCHAR2(80) := g_package||'manage_rehire_primary_asgs';
5543 --
5544 -- Cursor to get previous PDS that is the latest PDS before
5545 -- rehire date
5546 --
5547 CURSOR csr_prev_pds IS
5548 SELECT pds.period_of_service_id
5549 ,pds.date_start
5550 ,pds.actual_termination_date
5551 ,pds.last_standard_process_date
5552 ,pds.final_process_date
5553 FROM per_periods_of_service pds
5554 WHERE pds.person_id = p_person_id
5555 AND pds.date_start < p_rehire_date
5556 ORDER BY pds.date_start DESC;
5557 --
5558 lr_prev_pds csr_prev_pds%ROWTYPE;
5559 --
5560 -- Cursor to get primary assignment for given PDS
5561 -- on given date
5562 --
5563 CURSOR csr_prim_asg(p_period_of_service_id IN NUMBER
5564 ,p_person_id IN NUMBER
5565 ,p_effective_date IN DATE
5566 ) IS
5567 SELECT asg.assignment_id
5568 ,asg.effective_start_date
5569 ,asg.effective_end_date
5570 FROM per_assignments_f asg
5571 WHERE asg.period_of_service_id = csr_prim_asg.p_period_of_service_id
5572 AND asg.person_id = csr_prim_asg.p_person_id
5573 AND csr_prim_asg.p_effective_date BETWEEN asg.effective_start_date
5574 AND asg.effective_end_date
5575 AND asg.primary_flag = 'Y';
5576 --
5577 lr_prim_asg csr_prim_asg%ROWTYPE;
5578 --
5579 e_nothing_to_manage EXCEPTION;
5580 --
5581 BEGIN
5582 --
5583 hr_utility.set_location('Entering:'|| l_proc, 5);
5584 --
5585 -- Issue a savepoint.
5586 --
5587 SAVEPOINT manage_rehire_primary_asgs;
5588 --
5589 hr_utility.set_location(l_proc, 10);
5590 --
5591 -- Get the previous PDS that is the latest PDS before
5592 -- rehire date
5593 --
5594 OPEN csr_prev_pds;
5595 FETCH csr_prev_pds INTO lr_prev_pds;
5596 IF csr_prev_pds%NOTFOUND THEN
5597 CLOSE csr_prev_pds;
5598 --
5599 -- This is not a rehire
5600 --
5601 RAISE e_nothing_to_manage;
5602 END IF;
5603 CLOSE csr_prev_pds;
5604 --
5605 hr_utility.set_location(l_proc, 20);
5606 --
5607 -- Check if overlap condition
5608 --
5609 IF lr_prev_pds.final_process_date < p_rehire_date THEN
5610 --
5611 -- This is not an overlap condition
5612 --
5613 RAISE e_nothing_to_manage;
5614 END IF;
5615 --
5616 hr_utility.set_location(l_proc, 30);
5617 --
5618 -- Get the associated primary assignment on rehire date
5619 --
5620 OPEN csr_prim_asg(lr_prev_pds.period_of_service_id
5621 ,p_person_id
5622 ,p_rehire_date
5623 );
5624 FETCH csr_prim_asg INTO lr_prim_asg;
5625 IF csr_prim_asg%NOTFOUND THEN
5626 CLOSE csr_prim_asg;
5627 --
5628 hr_utility.set_location(l_proc, 35);
5629 --
5630 -- Get the associated primary assignment on actual term date
5631 --
5632 OPEN csr_prim_asg(lr_prev_pds.period_of_service_id
5633 ,p_person_id
5634 ,lr_prev_pds.actual_termination_date
5635 );
5636 FETCH csr_prim_asg INTO lr_prim_asg;
5637 IF csr_prim_asg%NOTFOUND THEN
5638 CLOSE csr_prim_asg;
5639 --
5640 RAISE e_nothing_to_manage;
5641 END IF;
5642 END IF;
5643 CLOSE csr_prim_asg;
5644 --
5645 hr_utility.set_location(l_proc, 40);
5646 --
5647 -- if p_cancel = 'Y', this is a cancel rehire scenario
5648 -- find primary assignment on rehire date - 1
5649 -- DT CORRECTION to ASG records with ESD >= rehire date to primary
5650 -- else if p_cancel = 'N', this is a rehire scenario
5651 -- DT UPDATE this ASG to secondary on rehire date
5652 -- DT CORRECTION ASG records with ESD > rehire date to secondary
5653 --
5654 update_rehire_primary_asgs
5655 (p_person_id => p_person_id
5656 ,p_rehire_date => p_rehire_date
5657 ,p_cancel => p_cancel
5658 );
5659 --
5660 hr_utility.set_location('Leaving:'|| l_proc, 50);
5661 --
5662 EXCEPTION
5663 --
5664 WHEN e_nothing_to_manage THEN
5665 --
5666 hr_utility.set_location('Leaving:'|| l_proc, 60);
5667 ROLLBACK TO manage_rehire_primary_asgs;
5668 --
5669 WHEN OTHERS THEN
5670 --
5671 -- An unexpected error has occurred
5672 -- No OUT parameters need to be set
5673 -- No cursors need to be closed
5674 --
5675 hr_utility.set_location('Leaving:'|| l_proc, 70);
5676 ROLLBACK TO manage_rehire_primary_asgs;
5677 RAISE;
5678 --
5679 END manage_rehire_primary_asgs;
5680 --
5681 -- 115.57 (END)
5682 --
5683 --
5684 end hr_employee_api;