[Home] [Help]
PACKAGE BODY: APPS.HR_EMPLOYEE_API
Source
1 Package Body hr_employee_api as
2 /* $Header: peempapi.pkb 120.8 2007/11/05 14:52:21 sidsaxen 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 p_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 = p_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 procedure create_us_employee
1946 (p_validate in boolean default false
1947 ,p_hire_date in date
1948 ,p_business_group_id in number
1949 ,p_last_name in varchar2
1950 ,p_sex in varchar2
1951 ,p_person_type_id in number default null
1952 ,p_comments in varchar2 default null
1953 ,p_date_employee_data_verified in date default null
1954 ,p_date_of_birth in date default null
1955 ,p_email_address in varchar2 default null
1956 ,p_employee_number in out nocopy varchar2
1957 ,p_expense_check_send_to_addres in varchar2 default null
1958 ,p_first_name in varchar2 default null
1959 ,p_known_as in varchar2 default null
1960 ,p_marital_status in varchar2 default null
1961 ,p_middle_names in varchar2 default null
1962 ,p_nationality in varchar2 default null
1963 ,p_ss_number in varchar2 default null
1964 ,p_previous_last_name in varchar2 default null
1965 ,p_registered_disabled_flag in varchar2 default null
1966 ,p_title in varchar2 default null
1967 ,p_vendor_id in number default null
1968 ,p_work_telephone in varchar2 default null
1969 ,p_attribute_category in varchar2 default null
1970 ,p_attribute1 in varchar2 default null
1971 ,p_attribute2 in varchar2 default null
1972 ,p_attribute3 in varchar2 default null
1973 ,p_attribute4 in varchar2 default null
1974 ,p_attribute5 in varchar2 default null
1975 ,p_attribute6 in varchar2 default null
1976 ,p_attribute7 in varchar2 default null
1977 ,p_attribute8 in varchar2 default null
1978 ,p_attribute9 in varchar2 default null
1979 ,p_attribute10 in varchar2 default null
1980 ,p_attribute11 in varchar2 default null
1981 ,p_attribute12 in varchar2 default null
1982 ,p_attribute13 in varchar2 default null
1983 ,p_attribute14 in varchar2 default null
1984 ,p_attribute15 in varchar2 default null
1985 ,p_attribute16 in varchar2 default null
1986 ,p_attribute17 in varchar2 default null
1987 ,p_attribute18 in varchar2 default null
1988 ,p_attribute19 in varchar2 default null
1989 ,p_attribute20 in varchar2 default null
1990 ,p_attribute21 in varchar2 default null
1991 ,p_attribute22 in varchar2 default null
1992 ,p_attribute23 in varchar2 default null
1993 ,p_attribute24 in varchar2 default null
1994 ,p_attribute25 in varchar2 default null
1995 ,p_attribute26 in varchar2 default null
1996 ,p_attribute27 in varchar2 default null
1997 ,p_attribute28 in varchar2 default null
1998 ,p_attribute29 in varchar2 default null
1999 ,p_attribute30 in varchar2 default null
2000 ,p_ethnic_origin in varchar2 default null
2001 ,p_I_9 in varchar2 default 'N'
2002 ,p_I_9_expiration_date in varchar2 default null
2003 -- ,p_visa_type in varchar2 default null
2004 ,p_veteran_status in varchar2 default null
2005 ,p_new_hire in varchar2 default null
2006 ,p_exception_reason in varchar2 default null
2007 ,p_child_support_obligation in varchar2 default 'N'
2008 ,p_opted_for_medicare_flag in varchar2 default 'N'
2009 ,p_date_of_death in date default null
2010 ,p_background_check_status in varchar2 default null
2011 ,p_background_date_check in date default null
2012 ,p_blood_type in varchar2 default null
2013 ,p_correspondence_language in varchar2 default null
2014 ,p_fast_path_employee in varchar2 default null
2015 ,p_fte_capacity in number default null
2016 ,p_honors in varchar2 default null
2017 ,p_internal_location in varchar2 default null
2018 ,p_last_medical_test_by in varchar2 default null
2019 ,p_last_medical_test_date in date default null
2020 ,p_mailstop in varchar2 default null
2021 ,p_office_number in varchar2 default null
2022 ,p_on_military_service in varchar2 default null
2023 ,p_pre_name_adjunct in varchar2 default null
2024 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
2025 ,p_projected_start_date in date default null
2026 ,p_resume_exists in varchar2 default null
2027 ,p_resume_last_updated in date default null
2028 ,p_second_passport_exists in varchar2 default null
2029 ,p_student_status in varchar2 default null
2030 ,p_work_schedule in varchar2 default null
2031 ,p_suffix in varchar2 default null
2032 ,p_benefit_group_id in number default null
2033 ,p_receipt_of_death_cert_date in date default null
2034 ,p_coord_ben_med_pln_no in varchar2 default null
2035 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
2036 ,p_coord_ben_med_ext_er in varchar2 default null
2037 ,p_coord_ben_med_pl_name in varchar2 default null
2038 ,p_coord_ben_med_insr_crr_name in varchar2 default null
2039 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
2040 ,p_coord_ben_med_cvg_strt_dt in date default null
2041 ,p_coord_ben_med_cvg_end_dt in date default null
2042 ,p_uses_tobacco_flag in varchar2 default null
2043 ,p_dpdnt_adoption_date in date default null
2044 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
2045 ,p_original_date_of_hire in date default null
2046 ,p_adjusted_svc_date in date default null
2047 ,p_town_of_birth in varchar2 default null
2048 ,p_region_of_birth in varchar2 default null
2049 ,p_country_of_birth in varchar2 default null
2050 ,p_global_person_id in varchar2 default null
2051 ,p_party_id in number default null
2052 ,p_person_id out nocopy number
2053 ,p_assignment_id out nocopy number
2054 ,p_per_object_version_number out nocopy number
2055 ,p_asg_object_version_number out nocopy number
2056 ,p_per_effective_start_date out nocopy date
2057 ,p_per_effective_end_date out nocopy date
2058 ,p_full_name out nocopy varchar2
2059 ,p_per_comment_id out nocopy number
2060 ,p_assignment_sequence out nocopy number
2061 ,p_assignment_number out nocopy varchar2
2062 ,p_name_combination_warning out nocopy boolean
2063 ,p_assign_payroll_warning out nocopy boolean
2064 ,p_orig_hire_warning out nocopy boolean
2065 )
2066 is
2067 --
2068 -- Declare cursors and local variables
2069 --
2070 l_proc varchar2(72) ;
2071 l_legislation_code varchar2(2);
2072 l_asg_object_version_number number(9);
2073 --
2074 cursor csr_bg is
2075 select legislation_code
2076 from per_business_groups pbg
2077 where pbg.business_group_id = p_business_group_id;
2078 --
2079 begin
2080 --
2081 -- Validation in addition to Row Handlers
2082 --
2083 -- Check that the specified business group is valid.
2084 --
2085 open csr_bg;
2086 fetch csr_bg
2087 into l_legislation_code;
2088 if csr_bg%notfound then
2089 close csr_bg;
2090 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2091 hr_utility.raise_error;
2092 end if;
2093 close csr_bg;
2094 if g_debug then
2095 l_proc := g_package||'create_us_employee';
2096 hr_utility.set_location(l_proc, 20);
2097 end if;
2098 --
2099 -- Check that the legislation of the specified business group is 'US'.
2100 --
2101 if l_legislation_code <> 'US' then
2102 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2103 hr_utility.set_message_token('LEG_CODE','US');
2104 hr_utility.raise_error;
2105 end if;
2106 if g_debug then
2107 hr_utility.set_location(l_proc, 30);
2108 end if;
2109 --
2110 -- Call the person business process
2111 --
2112 hr_employee_api.create_employee
2113 (p_validate => p_validate
2114 ,p_hire_date => p_hire_date
2115 ,p_business_group_id => p_business_group_id
2116 ,p_last_name => p_last_name
2117 ,p_sex => p_sex
2118 ,p_person_type_id => p_person_type_id
2119 ,p_per_comments => p_comments
2120 ,p_date_employee_data_verified => p_date_employee_data_verified
2121 ,p_date_of_birth => p_date_of_birth
2122 ,p_email_address => p_email_address
2123 ,p_employee_number => p_employee_number
2124 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
2125 ,p_first_name => p_first_name
2126 ,p_known_as => p_known_as
2127 ,p_marital_status => p_marital_status
2128 ,p_middle_names => p_middle_names
2129 ,p_nationality => p_nationality
2130 ,p_national_identifier => p_ss_number
2131 ,p_previous_last_name => p_previous_last_name
2132 ,p_registered_disabled_flag => p_registered_disabled_flag
2133 ,p_title => p_title
2134 ,p_vendor_id => p_vendor_id
2135 ,p_work_telephone => p_work_telephone
2136 ,p_attribute_category => p_attribute_category
2137 ,p_attribute1 => p_attribute1
2138 ,p_attribute2 => p_attribute2
2139 ,p_attribute3 => p_attribute3
2140 ,p_attribute4 => p_attribute4
2141 ,p_attribute5 => p_attribute5
2142 ,p_attribute6 => p_attribute6
2143 ,p_attribute7 => p_attribute7
2144 ,p_attribute8 => p_attribute8
2145 ,p_attribute9 => p_attribute9
2146 ,p_attribute10 => p_attribute10
2147 ,p_attribute11 => p_attribute11
2148 ,p_attribute12 => p_attribute12
2149 ,p_attribute13 => p_attribute13
2150 ,p_attribute14 => p_attribute14
2151 ,p_attribute15 => p_attribute15
2152 ,p_attribute16 => p_attribute16
2153 ,p_attribute17 => p_attribute17
2154 ,p_attribute18 => p_attribute18
2155 ,p_attribute19 => p_attribute19
2156 ,p_attribute20 => p_attribute20
2157 ,p_attribute21 => p_attribute21
2158 ,p_attribute22 => p_attribute22
2159 ,p_attribute23 => p_attribute23
2160 ,p_attribute24 => p_attribute24
2161 ,p_attribute25 => p_attribute25
2162 ,p_attribute26 => p_attribute26
2163 ,p_attribute27 => p_attribute27
2164 ,p_attribute28 => p_attribute28
2165 ,p_attribute29 => p_attribute29
2166 ,p_attribute30 => p_attribute30
2167 ,p_per_information_category => 'US'
2168 ,p_per_information1 => p_ethnic_origin
2169 ,p_per_information2 => p_I_9
2170 ,p_per_information3 => p_I_9_expiration_date
2171 -- ,p_per_information4 => p_visa_type
2172 ,p_per_information5 => p_veteran_status
2173 ,p_per_information7 => p_new_hire
2174 ,p_per_information8 => p_exception_reason
2175 ,p_per_information9 => p_child_support_obligation
2176 ,p_per_information10 => p_opted_for_medicare_flag
2177 ,p_date_of_death => p_date_of_death
2178 ,p_background_check_status => p_background_check_status
2179 ,p_background_date_check => p_background_date_check
2180 ,p_blood_type => p_blood_type
2181 ,p_correspondence_language => p_correspondence_language
2182 ,p_fast_path_employee => p_fast_path_employee
2183 ,p_fte_capacity => p_fte_capacity
2184 ,p_honors => p_honors
2185 ,p_internal_location => p_internal_location
2186 ,p_last_medical_test_by => p_last_medical_test_by
2187 ,p_last_medical_test_date => p_last_medical_test_date
2188 ,p_mailstop => p_mailstop
2189 ,p_office_number => p_office_number
2190 ,p_on_military_service => p_on_military_service
2191 ,p_pre_name_adjunct => p_pre_name_adjunct
2192 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
2193 ,p_projected_start_date => p_projected_start_date
2194 ,p_resume_exists => p_resume_exists
2195 ,p_resume_last_updated => p_resume_last_updated
2196 ,p_second_passport_exists => p_second_passport_exists
2197 ,p_student_status => p_student_status
2198 ,p_work_schedule => p_work_schedule
2199 ,p_suffix => p_suffix
2200 ,p_benefit_group_id => p_benefit_group_id
2201 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
2202 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
2203 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
2204 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
2205 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
2206 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
2207 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
2208 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
2209 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
2210 ,p_uses_tobacco_flag => p_uses_tobacco_flag
2211 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
2212 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
2213 ,p_original_date_of_hire => p_original_date_of_hire
2214 ,p_adjusted_svc_date => p_adjusted_svc_date
2215 ,p_town_of_birth => p_town_of_birth
2216 ,p_region_of_birth => p_region_of_birth
2217 ,p_country_of_birth => p_country_of_birth
2218 ,p_global_person_id => p_global_person_id
2219 ,p_party_id => p_party_id
2220 --
2221 ,p_person_id => p_person_id
2222 ,p_assignment_id => p_assignment_id
2223 ,p_per_object_version_number => p_per_object_version_number
2224 ,p_asg_object_version_number => p_asg_object_version_number
2225 ,p_per_effective_start_date => p_per_effective_start_date
2226 ,p_per_effective_end_date => p_per_effective_end_date
2227 ,p_full_name => p_full_name
2228 ,p_per_comment_id => p_per_comment_id
2229 ,p_assignment_sequence => p_assignment_sequence
2230 ,p_assignment_number => p_assignment_number
2231 ,p_name_combination_warning => p_name_combination_warning
2232 ,p_assign_payroll_warning => p_assign_payroll_warning
2233 ,p_orig_hire_warning => p_orig_hire_warning
2234 );
2235 --
2236 if g_debug then
2237 hr_utility.set_location(' Leaving:'||l_proc, 40);
2238 end if;
2239 end create_us_employee;
2240 -- --------------------------------------------------------------------------
2241 --
2242 -- Begin fix for bug 899720
2243 --
2244 -- overload procedure for create_us_employee
2245 --
2246 procedure create_us_employee
2247 (p_validate in boolean default false
2248 ,p_hire_date in date
2249 ,p_business_group_id in number
2250 ,p_last_name in varchar2
2251 ,p_sex in varchar2
2252 ,p_person_type_id in number default null
2253 ,p_comments in varchar2 default null
2254 ,p_date_employee_data_verified in date default null
2255 ,p_date_of_birth in date default null
2256 ,p_email_address in varchar2 default null
2257 ,p_employee_number in out nocopy varchar2
2258 ,p_expense_check_send_to_addres in varchar2 default null
2259 ,p_first_name in varchar2 default null
2260 ,p_known_as in varchar2 default null
2261 ,p_marital_status in varchar2 default null
2262 ,p_middle_names in varchar2 default null
2263 ,p_nationality in varchar2 default null
2264 ,p_ss_number in varchar2 default null
2265 ,p_previous_last_name in varchar2 default null
2266 ,p_registered_disabled_flag in varchar2 default null
2267 ,p_title in varchar2 default null
2268 ,p_vendor_id in number default null
2269 ,p_work_telephone in varchar2 default null
2270 ,p_attribute_category in varchar2 default null
2271 ,p_attribute1 in varchar2 default null
2272 ,p_attribute2 in varchar2 default null
2273 ,p_attribute3 in varchar2 default null
2274 ,p_attribute4 in varchar2 default null
2275 ,p_attribute5 in varchar2 default null
2276 ,p_attribute6 in varchar2 default null
2277 ,p_attribute7 in varchar2 default null
2278 ,p_attribute8 in varchar2 default null
2279 ,p_attribute9 in varchar2 default null
2280 ,p_attribute10 in varchar2 default null
2281 ,p_attribute11 in varchar2 default null
2282 ,p_attribute12 in varchar2 default null
2283 ,p_attribute13 in varchar2 default null
2284 ,p_attribute14 in varchar2 default null
2285 ,p_attribute15 in varchar2 default null
2286 ,p_attribute16 in varchar2 default null
2287 ,p_attribute17 in varchar2 default null
2288 ,p_attribute18 in varchar2 default null
2289 ,p_attribute19 in varchar2 default null
2290 ,p_attribute20 in varchar2 default null
2291 ,p_attribute21 in varchar2 default null
2292 ,p_attribute22 in varchar2 default null
2293 ,p_attribute23 in varchar2 default null
2294 ,p_attribute24 in varchar2 default null
2295 ,p_attribute25 in varchar2 default null
2296 ,p_attribute26 in varchar2 default null
2297 ,p_attribute27 in varchar2 default null
2298 ,p_attribute28 in varchar2 default null
2299 ,p_attribute29 in varchar2 default null
2300 ,p_attribute30 in varchar2 default null
2301 ,p_ethnic_origin in varchar2 default null
2302 ,p_I_9 in varchar2 default 'N'
2303 ,p_I_9_expiration_date in varchar2 default null
2304 -- ,p_visa_type in varchar2 default null
2305 ,p_veteran_status in varchar2 default null
2306 ,p_new_hire in varchar2 default null
2307 ,p_exception_reason in varchar2 default null
2308 ,p_child_support_obligation in varchar2 default 'N'
2309 ,p_opted_for_medicare_flag in varchar2 default 'N'
2310 ,p_date_of_death in date default null
2311 ,p_background_check_status in varchar2 default null
2312 ,p_background_date_check in date default null
2313 ,p_blood_type in varchar2 default null
2314 ,p_correspondence_language in varchar2 default null
2315 ,p_fast_path_employee in varchar2 default null
2316 ,p_fte_capacity in number default null
2317 ,p_honors in varchar2 default null
2318 ,p_internal_location in varchar2 default null
2319 ,p_last_medical_test_by in varchar2 default null
2320 ,p_last_medical_test_date in date default null
2321 ,p_mailstop in varchar2 default null
2322 ,p_office_number in varchar2 default null
2323 ,p_on_military_service in varchar2 default null
2324 ,p_pre_name_adjunct in varchar2 default null
2325 ,p_rehire_recommendation in varchar2 default null -- Bug 3210500
2326 ,p_projected_start_date in date default null
2327 ,p_resume_exists in varchar2 default null
2328 ,p_resume_last_updated in date default null
2329 ,p_second_passport_exists in varchar2 default null
2330 ,p_student_status in varchar2 default null
2331 ,p_work_schedule in varchar2 default null
2332 ,p_suffix in varchar2 default null
2333 ,p_benefit_group_id in number default null
2334 ,p_receipt_of_death_cert_date in date default null
2335 ,p_coord_ben_med_pln_no in varchar2 default null
2336 ,p_coord_ben_no_cvg_flag in varchar2 default 'N'
2337 ,p_coord_ben_med_ext_er in varchar2 default null
2338 ,p_coord_ben_med_pl_name in varchar2 default null
2339 ,p_coord_ben_med_insr_crr_name in varchar2 default null
2340 ,p_coord_ben_med_insr_crr_ident in varchar2 default null
2341 ,p_coord_ben_med_cvg_strt_dt in date default null
2342 ,p_coord_ben_med_cvg_end_dt in date default null
2343 ,p_uses_tobacco_flag in varchar2 default null
2344 ,p_dpdnt_adoption_date in date default null
2345 ,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
2346 ,p_original_date_of_hire in date default null
2347 ,p_adjusted_svc_date in date default null
2348 ,p_town_of_birth in varchar2 default null
2349 ,p_region_of_birth in varchar2 default null
2350 ,p_country_of_birth in varchar2 default null
2351 ,p_global_person_id in varchar2 default null
2352 ,p_party_id in number default null
2353 ,p_person_id out nocopy number
2354 ,p_assignment_id out nocopy number
2355 ,p_per_object_version_number out nocopy number
2356 ,p_asg_object_version_number out nocopy number
2357 ,p_per_effective_start_date out nocopy date
2358 ,p_per_effective_end_date out nocopy date
2359 ,p_full_name out nocopy varchar2
2360 ,p_per_comment_id out nocopy number
2361 ,p_assignment_sequence out nocopy number
2362 ,p_assignment_number out nocopy varchar2
2363 ,p_name_combination_warning out nocopy boolean
2364 ,p_assign_payroll_warning out nocopy boolean
2365 )
2366 is
2367 --
2368 -- Declare cursors and local variables
2369 --
2370 l_proc varchar2(72) ;
2371 l_orig_hire_warning boolean := false;
2372 --
2373 begin
2374 --
2375 if g_debug then
2376 l_proc := g_package||'create_us_employee';
2377 hr_utility.set_location('Entering:'||l_proc,333);
2378 end if;
2379 --
2380 hr_employee_api.create_us_employee
2381 (p_validate => p_validate
2382 ,p_hire_date => p_hire_date
2383 ,p_business_group_id => p_business_group_id
2384 ,p_last_name => p_last_name
2385 ,p_sex => p_sex
2386 ,p_person_type_id => p_person_type_id
2387 ,p_comments => p_comments
2388 ,p_date_employee_data_verified => p_date_employee_data_verified
2389 ,p_date_of_birth => p_date_of_birth
2390 ,p_email_address => p_email_address
2391 ,p_employee_number => p_employee_number
2392 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
2393 ,p_first_name => p_first_name
2394 ,p_known_as => p_known_as
2395 ,p_marital_status => p_marital_status
2396 ,p_middle_names => p_middle_names
2397 ,p_nationality => p_nationality
2398 ,p_ss_number => p_ss_number
2399 ,p_previous_last_name => p_previous_last_name
2400 ,p_registered_disabled_flag => p_registered_disabled_flag
2401 ,p_title => p_title
2402 ,p_vendor_id => p_vendor_id
2403 ,p_work_telephone => p_work_telephone
2404 ,p_attribute_category => p_attribute_category
2405 ,p_attribute1 => p_attribute1
2406 ,p_attribute2 => p_attribute2
2407 ,p_attribute3 => p_attribute3
2408 ,p_attribute4 => p_attribute4
2409 ,p_attribute5 => p_attribute5
2410 ,p_attribute6 => p_attribute6
2411 ,p_attribute7 => p_attribute7
2412 ,p_attribute8 => p_attribute8
2413 ,p_attribute9 => p_attribute9
2414 ,p_attribute10 => p_attribute10
2415 ,p_attribute11 => p_attribute11
2416 ,p_attribute12 => p_attribute12
2417 ,p_attribute13 => p_attribute13
2418 ,p_attribute14 => p_attribute14
2419 ,p_attribute15 => p_attribute15
2420 ,p_attribute16 => p_attribute16
2421 ,p_attribute17 => p_attribute17
2422 ,p_attribute18 => p_attribute18
2423 ,p_attribute19 => p_attribute19
2424 ,p_attribute20 => p_attribute20
2425 ,p_attribute21 => p_attribute21
2426 ,p_attribute22 => p_attribute22
2427 ,p_attribute23 => p_attribute23
2428 ,p_attribute24 => p_attribute24
2429 ,p_attribute25 => p_attribute25
2430 ,p_attribute26 => p_attribute26
2431 ,p_attribute27 => p_attribute27
2432 ,p_attribute28 => p_attribute28
2433 ,p_attribute29 => p_attribute29
2434 ,p_attribute30 => p_attribute30
2435 ,p_ethnic_origin => p_ethnic_origin
2436 ,p_I_9 => p_I_9
2437 ,p_I_9_expiration_date => p_I_9_expiration_date
2438 -- ,p_visa_type => p_visa_type
2439 ,p_veteran_status => p_veteran_status
2440 ,p_new_hire => p_new_hire
2441 ,p_exception_reason => p_exception_reason
2442 ,p_child_support_obligation => p_child_support_obligation
2443 ,p_opted_for_medicare_flag => p_opted_for_medicare_flag
2444 ,p_date_of_death => p_date_of_death
2445 ,p_background_check_status => p_background_check_status
2446 ,p_background_date_check => p_background_date_check
2447 ,p_blood_type => p_blood_type
2448 ,p_correspondence_language => p_correspondence_language
2449 ,p_fast_path_employee => p_fast_path_employee
2450 ,p_fte_capacity => p_fte_capacity
2451 ,p_honors => p_honors
2452 ,p_internal_location => p_internal_location
2453 ,p_last_medical_test_by => p_last_medical_test_by
2454 ,p_last_medical_test_date => p_last_medical_test_date
2455 ,p_mailstop => p_mailstop
2456 ,p_office_number => p_office_number
2457 ,p_on_military_service => p_on_military_service
2458 ,p_pre_name_adjunct => p_pre_name_adjunct
2459 ,p_rehire_recommendation => p_rehire_recommendation -- Bug 3210500
2460 ,p_projected_start_date => p_projected_start_date
2461 ,p_resume_exists => p_resume_exists
2462 ,p_resume_last_updated => p_resume_last_updated
2463 ,p_second_passport_exists => p_second_passport_exists
2464 ,p_student_status => p_student_status
2465 ,p_work_schedule => p_work_schedule
2466 ,p_suffix => p_suffix
2467 ,p_benefit_group_id => p_benefit_group_id
2468 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
2469 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
2470 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
2471 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
2472 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
2473 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
2474 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
2475 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
2476 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
2477 ,p_uses_tobacco_flag => p_uses_tobacco_flag
2478 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
2479 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
2480 ,p_original_date_of_hire => p_original_date_of_hire
2481 ,p_adjusted_svc_date => p_adjusted_svc_date
2482 ,p_town_of_birth => p_town_of_birth
2483 ,p_region_of_birth => p_region_of_birth
2484 ,p_country_of_birth => p_country_of_birth
2485 ,p_global_person_id => p_global_person_id
2486 ,p_party_id => p_party_id
2487 ,p_person_id => p_person_id
2488 ,p_assignment_id => p_assignment_id
2489 ,p_per_object_version_number => p_per_object_version_number
2490 ,p_asg_object_version_number => p_asg_object_version_number
2491 ,p_per_effective_start_date => p_per_effective_start_date
2492 ,p_per_effective_end_date => p_per_effective_end_date
2493 ,p_full_name => p_full_name
2494 ,p_per_comment_id => p_per_comment_id
2495 ,p_assignment_sequence => p_assignment_sequence
2496 ,p_assignment_number => p_assignment_number
2497 ,p_name_combination_warning => p_name_combination_warning
2498 ,p_assign_payroll_warning => p_assign_payroll_warning
2499 ,p_orig_hire_warning => l_orig_hire_warning
2500 );
2501 --
2502 if g_debug then
2503 hr_utility.set_location('Leaving:'||l_proc,333);
2504 end if;
2505 --
2506 end create_us_employee;
2507 --
2508 -- End of fix for bug 899720
2509 --
2510 -- ----------------------------------------------------------------------------
2511 -- |--------------------------< re_hire_ex_employee >-------------------------|
2512 -- ----------------------------------------------------------------------------
2513 procedure re_hire_ex_employee
2514 (p_validate in boolean default false
2515 ,p_hire_date in date
2516 ,p_person_id in number
2517 ,p_per_object_version_number in out nocopy number
2518 ,p_person_type_id in number default hr_api.g_number
2519 ,p_rehire_reason in varchar2
2520 ,p_assignment_id out nocopy number
2521 ,p_asg_object_version_number out nocopy number
2522 ,p_per_effective_start_date out nocopy date
2523 ,p_per_effective_end_date out nocopy date
2524 ,p_assignment_sequence out nocopy number
2525 ,p_assignment_number out nocopy varchar2
2526 ,p_assign_payroll_warning out nocopy boolean
2527 ) is
2528 --
2529 -- declare local variables
2530 --
2531 l_proc varchar2(72) := g_package||'re_hire_ex_employee';
2532 l_business_group_id per_people_f.business_group_id%type;
2533 l_ovn per_people_f.object_version_number%type := p_per_object_version_number;
2534 l_name_combination_warning boolean;
2535 l_orig_hire_warning boolean;
2536 l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2537 l_person_type_id1 per_people_f.person_type_id%type;
2538 l_comment_id per_people_f.comment_id%type;
2539 l_current_applicant_flag per_people_f.current_applicant_flag%type;
2540 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2541 l_current_employee_flag per_people_f.current_employee_flag%type;
2542 l_employee_number per_people_f.employee_number%type;
2543 l_applicant_number per_people_f.applicant_number%TYPE;
2544 l_npw_number per_people_f.npw_number%type;
2545 l_full_name per_people_f.full_name%type;
2546 l_object_version_number per_people_f.object_version_number%type;
2547 l_period_of_service_id per_periods_of_service.period_of_service_id%type;
2548 l_pds_object_version_number per_periods_of_service.object_version_number%type;
2549 l_datetrack_mode varchar2(12);
2550 l_effective_date date;
2551 l_hire_date date;
2552 l_assign_payroll_warning boolean :=FALSE;
2553 --
2554 -- Added local variables for after hook re_hire_ex_employee_a
2555 --
2556 -- Bug 1828850 starts here.
2557 -- The l_assignment_id is declared as per_assignments_f.assignment_id%type.
2558 --
2559 l_assignment_id per_assignments_f.assignment_id%type;
2560 --
2561 -- Bug 1828850 Ends here.
2562 --
2563 l_asg_object_version_number number(9);
2564 l_per_effective_start_date date;
2565 l_per_effective_end_date date;
2566 l_assignment_sequence number(15);
2567 l_assignment_number varchar2(30);
2568 -- Bug 3611984 starts here
2569 l_ptu_datetrack_mode varchar2(12);
2570 cursor c_ptu_start_date is
2571 select effective_start_date
2572 from per_person_type_usages_f ptu,per_person_types ppt
2573 where ptu.person_type_id = ppt.person_type_id
2574 and ptu.person_id = p_person_id
2575 and p_hire_date between ptu.effective_start_date and ptu.effective_end_date
2576 and ppt.system_person_type='EX_EMP';
2577 l_ptu_effective_start_date date;
2578
2579 --
2580 -- 115.57 (START)
2581 --
2582 l_rule_value pay_legislation_rules.rule_mode%TYPE;
2583 l_rule_found BOOLEAN;
2584 l_legislation_code pay_legislation_rules.legislation_code%TYPE;
2585 --
2586 -- 115.57 (END)
2587 --
2588
2589 -- Bug 3611984 starts here
2590
2591 -- --------------------------------------------------------------------------
2592 -- |-------------------------< get_person_details >-------------------------|
2593 -- --------------------------------------------------------------------------
2594 --
2595 -- Description
2596 -- This procedure is used for 2 purposes; to validate that the person
2597 -- exists as of the specified effective date and to select the
2598 -- business group, effective start date and system person type information
2599 --
2600 -- --------------------------------------------------------------------------
2601 procedure get_person_details
2602 (p_person_id in number,
2603 p_effective_date in date,
2604 p_business_group_id out nocopy number,
2605 p_employee_number out nocopy varchar2,
2606 p_effective_start_date out nocopy date,
2607 p_system_person_type out nocopy varchar2) is
2608 --
2609 l_proc varchar2(72);
2610 --
2611 -- select and validate the person
2612 --
2613 -- Fix for 5045840 . Modified the cursor csr_chk_person_exists to
2614 -- use ptu table.
2615 cursor csr_chk_person_exists is
2616 /* select per.business_group_id,
2617 per.employee_number,
2618 per.effective_start_date,
2619 pet.system_person_type
2620 from per_person_types pet,
2621 per_people_f per
2622 where per.person_id = p_person_id
2623 and pet.person_type_id = per.person_type_id
2624 and pet.business_group_id + 0 = per.business_group_id
2625 and p_effective_date
2626 between per.effective_start_date
2627 and per.effective_end_date;*/
2628 select per.business_group_id,
2629 per.employee_number,
2630 per.effective_start_date,
2631 pet.system_person_type
2632 from per_person_types pet,
2633 per_people_f per,
2634 per_person_type_usages_f ptu
2635 where per.person_id = p_person_id
2636 and pet.person_type_id = ptu.person_type_id
2637 and ptu.person_id = per.person_id
2638 -- added this condition for 5601538
2639 and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
2640 /*and ptu.effective_start_date = per.effective_start_date
2641 and ptu.effective_end_date = per.effective_end_date commented for bug 5601538*/
2642 and pet.business_group_id + 0 = per.business_group_id
2643 and p_effective_date
2644 between per.effective_start_date
2645 and per.effective_end_date;
2646 --
2647 begin
2648 if g_debug then
2649 l_proc := g_package||'get_person_details';
2650 hr_utility.set_location('Entering:'|| l_proc, 5);
2651 end if;
2652 --
2653 open csr_chk_person_exists;
2654 fetch csr_chk_person_exists into
2655 p_business_group_id, p_employee_number, p_effective_start_date,
2656 p_system_person_type;
2657 if csr_chk_person_exists%notfound then
2658 close csr_chk_person_exists;
2659 --
2660 -- the person cannot exist as of the supplied effective_date therefore
2661 -- we must error
2662 --
2663 -- This person either does not exist at all or does not exist as of the
2664 -- date specified.
2665 --
2666 hr_utility.set_message(801, 'HR_51011_PER_NOT_EXIST_DATE');
2667 hr_utility.raise_error;
2668 end if;
2669 --changes for bug 5601538 starts here
2670 Loop
2671 if p_system_person_type ='EX_EMP' then
2672 exit;
2673 end if;
2674 fetch csr_chk_person_exists into
2675 p_business_group_id, p_employee_number, p_effective_start_date,
2676 p_system_person_type;
2677 EXIT when csr_chk_person_exists%notfound ;
2678
2679 End loop;
2680 --changes for bug 5601538 ends here
2681 close csr_chk_person_exists;
2682 if g_debug then
2683 hr_utility.set_location(' Leaving:'||l_proc, 10);
2684 end if;
2685 end get_person_details;
2686 -- --------------------------------------------------------------------------
2687 -- |-------------------------< perform_validation >-------------------------|
2688 -- --------------------------------------------------------------------------
2689 --
2690 -- Description
2691 -- This procedure controls and performs the following business process
2692 -- validation:
2693 -- 1) ensure that the p_person_id and p_hire_date parameters are not null.
2694 -- 2) check that this person (p_person_id) exists as of p_hire_date and
2695 -- the current person type (per_people_f.person_type_id) has a
2696 -- corresponding system person type of EX_EMP.
2697 -- 3) ensure the most recent period of service for this person has been
2698 -- completely terminated.
2699 --
2700 -- --------------------------------------------------------------------------
2701 procedure perform_validation
2702 (p_person_id in number,
2703 p_hire_date in date,
2704 p_effective_date out nocopy date,
2705 p_business_group_id out nocopy number,
2706 p_employee_number out nocopy varchar2) is
2707 --
2708 l_proc varchar2(72) := g_package||'perform_validation';
2709 l_system_person_type per_person_types.system_person_type%type;
2710 l_final_process_date per_periods_of_service.final_process_date%type;
2711 l_effective_start_date per_people_f.effective_start_date%type;
2712 l_dummy_number number;
2713 l_dummy_emp_number varchar2(30);
2714 l_dummy_date date;
2715 --
2716 cursor csr_chk_period_of_service is
2717 --
2718 -- 115.57 (START)
2719 --
2720 --select pos.final_process_date
2721 select pos.actual_termination_date,
2722 pos.last_standard_process_date,
2723 pos.final_process_date
2724 --
2725 -- 115.57 (END)
2726 --
2727 from per_periods_of_service pos
2728 where pos.person_id = p_person_id
2729 order by pos.date_start desc;
2730 --
2731 -- 115.57 (START)
2732 --
2733 l_fpd per_periods_of_service.final_process_date%TYPE;
2734 l_atd per_periods_of_service.actual_termination_date%TYPE;
2735 l_lspd per_periods_of_service.last_standard_process_date%TYPE;
2736 --
2737 -- Cursor to get legislation code
2738 --
2739 CURSOR csr_per_legislation
2740 (p_person_id IN per_all_people_f.person_id%TYPE
2741 ,p_effective_date IN DATE
2742 ) IS
2743 SELECT bus.legislation_code
2744 FROM per_people_f per
2745 ,per_business_groups bus
2746 WHERE per.person_id = csr_per_legislation.p_person_id
2747 AND per.business_group_id+0 = bus.business_group_id
2748 AND csr_per_legislation.p_effective_date BETWEEN per.effective_start_date
2749 AND per.effective_end_date;
2750 --
2751 -- 115.57 (END)
2752 --
2753 --
2754 begin
2755 if g_debug then
2756 hr_utility.set_location('Entering:'|| l_proc, 5);
2757 end if;
2758 --
2759 -- Validation Logic
2760 --
2761 -- 1. ensure that the mandatory parameters p_hire_date and p_person_id
2762 -- are not null
2763 --
2764 hr_api.mandatory_arg_error
2765 (p_api_name => l_proc
2766 ,p_argument => 'person id'
2767 ,p_argument_value => p_person_id);
2768 --
2769 hr_api.mandatory_arg_error
2770 (p_api_name => l_proc
2771 ,p_argument => 'hire date'
2772 ,p_argument_value => p_hire_date);
2773 --
2774 -- 2. check that this person (p_person_id) exists as of p_hire_date and the
2775 -- the current person type (per_people_f.person_type_id) has a
2776 -- corresponding system person type of EX_EMP.
2777 --
2778 get_person_details
2779 (p_person_id => p_person_id,
2780 p_effective_date => p_hire_date,
2781 p_business_group_id => p_business_group_id,
2782 p_employee_number => p_employee_number,
2783 p_effective_start_date => l_effective_start_date,
2784 p_system_person_type => l_system_person_type);
2785 --
2786 -- 115.57 (START)
2787 --
2788 --
2789 -- Get person legislation
2790 --
2791 OPEN csr_per_legislation(p_person_id
2792 ,p_hire_date
2793 );
2794 FETCH csr_per_legislation INTO l_legislation_code;
2795 CLOSE csr_per_legislation;
2796 --
2797 -- Check if rehire before FPD is enabled
2798 --
2799 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
2800 ,l_legislation_code
2801 ,l_rule_value
2802 ,l_rule_found
2803 );
2804 --
2805 --
2806 -- 115.57 (END)
2807 --
2808 --
2809 -- ensure that the system person type is 'EX_EMP'
2810 --
2811 if (l_system_person_type <> 'EX_EMP') then
2812 --
2813 -- the system person type is not 'EX_EMP' therefore error
2814 -- You cannot Re-Hire a person who is not an Ex-Employee.
2815 --
2816 hr_utility.set_message(801, 'HR_51012_REHIRE_NOT_EX_EMP');
2817 hr_utility.raise_error;
2818 end if;
2819 if g_debug then
2820 hr_utility.set_location(l_proc, 10);
2821 end if;
2822 --
2823 -- 3. ensure the most recent period of service for this person has been
2824 -- completely terminated. i.e. check that
2825 -- period_of_service.final_process_date is not null and comes before
2826 -- p_hire_date.
2827 -- we only fetch the 1st row (which is the latest pos).
2828 --
2829 open csr_chk_period_of_service;
2830 --
2831 -- 115.57 (START)
2832 --
2833 --fetch csr_chk_period_of_service into l_final_process_date;
2834 fetch csr_chk_period_of_service into l_atd, l_lspd, l_fpd;
2835 --
2836 -- 115.57 (END)
2837 --
2838 if csr_chk_period_of_service%notfound then
2839 close csr_chk_period_of_service;
2840 --
2841 -- a period of service row does not exist for the person therefore
2842 -- we must error
2843 --
2844 -- This person does not have a previous period of service
2845 --
2846 hr_utility.set_message(801, 'HR_51013_PDS_NOT_EXIST');
2847 hr_utility.raise_error;
2848 end if;
2849 close csr_chk_period_of_service;
2850 --
2851 --
2852 -- 115.57 (START)
2853 --
2854 if (l_fpd is null) then
2855 --
2856 -- the employee cannot be terminated as the final process date has not
2857 -- been set
2858 --
2859 -- You cannot re-hire a person who does not have a final processing date
2860 -- set for their most recent period of service
2861 --
2862 hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
2863 hr_utility.raise_error;
2864 end if;
2865 --
2866 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
2867 --
2868 -- Rehire before FPD allowed (new behaviour)
2869 --
2870 if nvl(l_lspd,l_atd) >= p_hire_date then
2871 --
2872 -- the re hire date is before the current LSPD or ATD
2873 --
2874 -- You cannot re-hire an Ex-Employee before their LSPD.
2875 -- Please specify a Re-Hire date which is after the LSPD.
2876 --
2877 hr_utility.set_message(801, 'HR_449759_REHIRE_AFTER_LSPD');
2878 hr_utility.raise_error;
2879 end if;
2880 else
2881 --
2882 -- Rehire before FPD is not allowed (old behaviour)
2883 --
2884 if l_fpd >= p_hire_date then
2885 --
2886 -- the re hire date is before the current final process date
2887 --
2888 -- You cannot re-hire an Ex-Employee before their final processing date.
2889 -- Please specify a Re-Hire date which is after the final processing date.
2890 --
2891 hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
2892 hr_utility.raise_error;
2893 end if;
2894 end if;
2895 --
2896 --if (l_final_process_date is null) then
2897 -- --
2898 -- -- the employee cannot be terminated as the final process date has not
2899 -- -- been set
2900 -- --
2901 -- -- You cannot re-hire a person who does not have a final processing date
2902 -- -- set for their most recent period of service
2903 -- --
2904 -- hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
2905 -- hr_utility.raise_error;
2906 --elsif (l_final_process_date >= p_hire_date) then
2907 -- --
2908 -- -- the re hire date is before the current final process date
2909 -- --
2910 -- -- You cannot re-hire an Ex-Employee before their final processing date.
2911 -- -- Please specify a Re-Hire date which is after the final processing date.
2912 -- --
2913 -- hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
2914 -- hr_utility.raise_error;
2915 --end if;
2916 --
2917 -- 115.57 (START)
2918 --
2919 if g_debug then
2920 hr_utility.set_location(l_proc, 20);
2921 end if;
2922 p_effective_date := l_effective_start_date;
2923 --
2924
2925 if g_debug then
2926 hr_utility.set_location(' Leaving:'||l_proc, 25);
2927 end if;
2928 end perform_validation;
2929
2930 begin
2931 if g_debug then
2932 hr_utility.set_location('Entering:'|| l_proc, 5);
2933 end if;
2934 --
2935 -- Issue a savepoint.
2936 --
2937 savepoint re_hire_ex_employee;
2938 --
2939 -- Initialise local variables
2940 --
2941 l_object_version_number := p_per_object_version_number;
2942 l_applicant_number := hr_api.g_varchar2;
2943 l_npw_number := hr_api.g_varchar2;
2944 l_hire_date := trunc(p_hire_date);
2945 --
2946 -- perform business process validation
2947 --
2948 perform_validation
2949 (p_person_id => p_person_id,
2950 p_hire_date => l_hire_date,
2951 p_effective_date => l_effective_date,
2952 p_business_group_id => l_business_group_id,
2953 p_employee_number => l_employee_number);
2954 --
2955 -- processing logic
2956 --
2957 if g_debug then
2958 hr_utility.set_location(l_proc, 10);
2959 end if;
2960 l_person_type_id := p_person_type_id;
2961 --
2962 begin
2963 --
2964 -- Start of call API User Hook for the before hook of re_hire_ex_employee
2965 --
2966 hr_employee_bk2.re_hire_ex_employee_b
2967 (
2968 p_business_group_id =>l_business_group_id
2969 ,p_hire_date =>l_hire_date
2970 ,p_person_id =>p_person_id
2971 ,p_per_object_version_number =>p_per_object_version_number
2972 ,p_person_type_id =>p_person_type_id
2973 ,p_rehire_reason =>p_rehire_reason
2974 );
2975 exception
2976 when hr_api.cannot_find_prog_unit then
2977 hr_api.cannot_find_prog_unit_error
2978 (p_module_name => 'RE_HIRE_EX_EMPLOYEE'
2979 ,p_hook_type => 'BP'
2980 );
2981 --
2982 -- End of API User Hook for the before hook of re_hire_ex_employee
2983 --
2984 end;
2985 --
2986 -- derive and/or validate the person type
2987 --
2988 per_per_bus.chk_person_type
2989 (p_person_type_id => l_person_type_id,
2990 p_business_group_id => l_business_group_id,
2991 p_expected_sys_type => 'EMP');
2992 --
2993 if g_debug then
2994 hr_utility.set_location(l_proc, 15);
2995 end if;
2996 --
2997 if (l_effective_date = p_hire_date) then
2998 l_datetrack_mode := 'CORRECTION';
2999 else
3000 l_datetrack_mode := 'UPDATE';
3001 end if;
3002
3003 -- PTU : Added
3004
3005 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
3006 (l_business_group_id,
3007 'EMP');
3008 -- PTU : End
3009
3010 -- update the person re-hiring as an employee as of the hire date
3011 --
3012 per_per_upd.upd
3013 (p_person_id => p_person_id,
3014 p_person_type_id => l_person_type_id1,
3015 p_effective_date => l_hire_date,
3016 p_datetrack_mode => l_datetrack_mode,
3017 p_object_version_number => p_per_object_version_number,
3018 p_dob_null_warning => p_assign_payroll_warning,
3019 p_effective_start_date => l_per_effective_start_date,
3020 p_effective_end_date => l_per_effective_end_date,
3021 p_rehire_reason => p_rehire_reason,
3022 p_name_combination_warning => l_name_combination_warning,
3023 p_orig_hire_warning => l_orig_hire_warning,
3024 p_comment_id => l_comment_id,
3025 p_current_applicant_flag => l_current_applicant_flag,
3026 p_current_emp_or_apl_flag => l_current_emp_or_apl_flag,
3027 p_current_employee_flag => l_current_employee_flag,
3028 p_employee_number => l_employee_number,
3029 p_applicant_number => l_applicant_number,
3030 p_full_name => l_full_name,
3031 p_npw_number => l_npw_number);
3032 --
3033 if g_debug then
3034 hr_utility.set_location(l_proc, 20);
3035 end if;
3036 --
3037 -- add to current security list
3038 -- when in validation only mode raise the Validate_Enabled exception
3039 --
3040 hr_security_internal.populate_new_person(l_business_group_id,p_person_id);
3041 -- PTU : Following Code has been added
3042 -- Bug 3611984 starts here
3043 begin
3044 open c_ptu_start_date;
3045 fetch c_ptu_start_date into l_ptu_effective_start_date;
3046 close c_ptu_start_date;
3047 end;
3048
3049 if (l_ptu_effective_start_date = p_hire_date) then
3050 l_ptu_datetrack_mode := 'CORRECTION';
3051 else
3052 l_ptu_datetrack_mode := 'UPDATE';
3053 end if;
3054 -- Bug 3611984 ends here
3055 hr_per_type_usage_internal.maintain_person_type_usage
3056 (p_effective_date => l_hire_date
3057 ,p_person_id => p_person_id
3058 ,p_person_type_id => l_person_type_id
3059 ,p_datetrack_update_mode => l_ptu_datetrack_mode -- #3611984 l_datetrack_mode
3060 );
3061
3062 -- PTU : End of changes
3063 --
3064 if g_debug then
3065 hr_utility.set_location(l_proc, 22);
3066 end if;
3067 --
3068 -- create a new period of service for the re-hired employee
3069 --
3070 per_pds_ins.ins
3071 (p_business_group_id => l_business_group_id,
3072 p_person_id => p_person_id,
3073 p_date_start => l_hire_date,
3074 p_period_of_service_id => l_period_of_service_id,
3075 p_effective_date => p_hire_date,
3076 p_validate_df_flex => false,
3077 p_object_version_number => l_pds_object_version_number);
3078 --
3079 if g_debug then
3080 hr_utility.set_location(l_proc, 25);
3081 end if;
3082 --
3083 -- create a default primary assignment with any corresponding standard
3084 -- element entries for the re-hired employee
3085 --
3086 hr_assignment_internal.create_default_emp_asg
3087 (p_effective_date => l_hire_date,
3088 p_person_id => p_person_id,
3089 p_business_group_id => l_business_group_id,
3090 p_period_of_service_id => l_period_of_service_id,
3091 p_assignment_id => l_assignment_id,
3092 p_object_version_number => l_asg_object_version_number,
3093 p_assignment_sequence => l_assignment_sequence,
3094 p_assignment_number => l_assignment_number);
3095 --
3096 -- 115.57 (START)
3097 --
3098 if g_debug then
3099 hr_utility.set_location(l_proc, 26);
3100 end if;
3101 --
3102 -- If rehire before FPD is allowed, any resulting overlapping PDS
3103 -- will require updating the terminated primary assignment to
3104 -- secondary.
3105 --
3106 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
3107 manage_rehire_primary_asgs(p_person_id => p_person_id
3108 ,p_rehire_date => l_hire_date
3109 ,p_cancel => 'N'
3110 );
3111 end if;
3112 --
3113 if g_debug then
3114 hr_utility.set_location(l_proc, 27);
3115 end if;
3116 --
3117 -- 115.57 (END)
3118 --
3119 --
3120 -- when in validation only mode raise the Validate_Enabled exception
3121 --
3122 -- 1766066: added call for contact start date enh.
3123 --
3124 per_people12_pkg.maintain_coverage(p_person_id => p_person_id
3125 ,p_type => 'EMP'
3126 );
3127 -- 1766066 end.
3128 begin
3129 --
3130 -- Start of call API User Hook for the after hook of re_hire_ex_employee
3131 --
3132 hr_employee_bk2.re_hire_ex_employee_a
3133 (
3134 p_business_group_id =>l_business_group_id
3135 ,p_hire_date =>l_hire_date
3136 ,p_person_id =>p_person_id
3137 ,p_per_object_version_number =>p_per_object_version_number
3138 ,p_person_type_id =>p_person_type_id
3139 ,p_rehire_reason =>p_rehire_reason
3140 ,p_assignment_id =>l_assignment_id
3141 ,p_asg_object_version_number =>l_asg_object_version_number
3142 ,p_per_effective_start_date =>l_per_effective_start_date
3143 ,p_per_effective_end_date =>l_per_effective_end_date
3144 ,p_assignment_sequence =>l_assignment_sequence
3145 ,p_assignment_number =>l_assignment_number
3146 ,p_assign_payroll_warning =>l_assign_payroll_warning
3147 );
3148 exception
3149 when hr_api.cannot_find_prog_unit then
3150 hr_api.cannot_find_prog_unit_error
3151 (p_module_name => 'RE_HIRE_EX_EMPLOYEE'
3152 ,p_hook_type => 'BP'
3153 );
3154 --
3155 -- End of API User Hook for the after hook of re_hire_ex_employee
3156 --
3157 end;
3158 --
3159 if p_validate then
3160 raise hr_api.validate_enabled;
3161 end if;
3162 --
3163 -- Set all output arguments
3164 --
3165 p_assignment_id := l_assignment_id;
3166 p_asg_object_version_number := l_asg_object_version_number;
3167 p_per_effective_start_date := l_per_effective_start_date;
3168 p_per_effective_end_date := l_per_effective_end_date;
3169 p_assignment_sequence := l_assignment_sequence;
3170 p_assignment_number := l_assignment_number;
3171 p_assign_payroll_warning := l_assign_payroll_warning;
3172 --
3173 -- When in validation only mode raise the Validate_Enabled exception
3174 --
3175 if g_debug then
3176 hr_utility.set_location(' Leaving:'||l_proc, 30);
3177 end if;
3178 exception
3179 when hr_api.validate_enabled then
3180 --
3181 -- As the Validate_Enabled exception has been raised
3182 -- we must rollback to the savepoint
3183 --
3184 ROLLBACK TO re_hire_ex_employee;
3185 --
3186 -- Only set output warning arguments
3187 -- (Any key or derived arguments must be set to null
3188 -- when validation only mode is being used.)
3189 --
3190 p_per_object_version_number := l_object_version_number;
3191 p_assignment_id := null;
3192 p_asg_object_version_number := null;
3193 p_per_effective_start_date := null;
3194 p_per_effective_end_date := null;
3195 p_assignment_sequence := null;
3196 p_assignment_number := null;
3197 p_assign_payroll_warning := l_assign_payroll_warning;
3198 --
3199 when others then
3200 --
3201 -- A validation or unexpected error has occurred
3202 --
3203 p_per_object_version_number := l_ovn;
3204 p_assignment_id := null;
3205 p_asg_object_version_number := null;
3206 p_per_effective_start_date := null;
3207 p_per_effective_end_date := null;
3208 p_assignment_sequence := null;
3209 p_assignment_number := null;
3210 p_assign_payroll_warning := false;
3211 -- Added as part of fix to bug 632479
3212 --
3213 ROLLBACK TO re_hire_ex_employee;
3214 --
3215 -- set in out parameters and set out parameters
3216 --
3217 raise;
3218 --
3219 -- End of fix.
3220 --
3221 if g_debug then
3222 hr_utility.set_location(' Leaving:'||l_proc, 35);
3223 end if;
3224 end re_hire_ex_employee;
3225 --
3226 -- OLD
3227 -- ----------------------------------------------------------------------------
3228 -- |-----------------< apply_for_internal_vacancy >-------------------------|
3229 -- ----------------------------------------------------------------------------
3230 -- OLD
3231 procedure apply_for_internal_vacancy
3232 (p_validate in boolean default false
3233 ,p_effective_date in date
3234 ,p_person_id in number
3235 ,p_applicant_number in out nocopy varchar2
3236 ,p_per_object_version_number in out nocopy number
3237 ,p_vacancy_id in number default null
3238 ,p_person_type_id in number default hr_api.g_number
3239 ,p_application_id out nocopy number
3240 ,p_assignment_id out nocopy number
3241 ,p_apl_object_version_number out nocopy number
3242 ,p_asg_object_version_number out nocopy number
3243 ,p_assignment_sequence out nocopy number
3244 ,p_per_effective_start_date out nocopy date
3245 ,p_per_effective_end_date out nocopy date
3246 ) is
3247 --
3248 l_warning boolean;
3249 --
3250 begin
3251 hr_employee_api.apply_for_internal_vacancy
3252 (p_validate => p_validate
3253 ,p_effective_date => p_effective_date
3254 ,p_person_id => p_person_id
3255 ,p_applicant_number => p_applicant_number
3256 ,p_per_object_version_number => p_per_object_version_number
3257 ,p_vacancy_id => p_vacancy_id
3258 ,p_person_type_id => p_person_type_id
3259 ,p_application_id => p_application_id
3260 ,p_assignment_id => p_assignment_id
3261 ,p_apl_object_version_number => p_apl_object_version_number
3262 ,p_asg_object_version_number => p_asg_object_version_number
3263 ,p_assignment_sequence => p_assignment_sequence
3264 ,p_per_effective_start_date => p_per_effective_start_date
3265 ,p_per_effective_end_date => p_per_effective_end_date
3266 ,p_appl_override_warning => l_warning
3267 );
3268 end apply_for_internal_vacancy;
3269 -- NEW
3270 -- ----------------------------------------------------------------------------
3271 -- |-----------------< apply_for_internal_vacancy >-------------------------|
3272 -- ----------------------------------------------------------------------------
3273 -- NEW
3274 procedure apply_for_internal_vacancy
3275 (p_validate in boolean default false
3276 ,p_effective_date in date
3277 ,p_person_id in number
3278 ,p_applicant_number in out nocopy varchar2
3279 ,p_per_object_version_number in out nocopy number
3280 ,p_vacancy_id in number default null
3281 ,p_person_type_id in number default hr_api.g_number
3282 ,p_application_id out nocopy number
3283 ,p_assignment_id out nocopy number
3284 ,p_apl_object_version_number out nocopy number
3285 ,p_asg_object_version_number out nocopy number
3286 ,p_assignment_sequence out nocopy number
3287 ,p_per_effective_start_date out nocopy date
3288 ,p_per_effective_end_date out nocopy date
3289 ,p_appl_override_warning out nocopy boolean -- 3652025
3290 ) is
3291 --
3292 -- declare local variables
3293 --
3294 l_proc varchar2(72) := g_package||'apply_for_internal_vacancy';
3295 l_business_group_id per_people_f.business_group_id%type;
3296 l_name_combination_warning boolean;
3297 l_dob_null_warning boolean;
3298 l_orig_hire_warning boolean;
3299 l_organization_id per_business_groups.organization_id%type;
3300 l_legislation_code per_business_groups.legislation_code%type;
3301 l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
3302 l_person_type_id1 per_people_f.person_type_id%type;
3303 l_application_id per_applications.application_id%type;
3304 l_comment_id per_assignments_f.comment_id%type;
3305 l_assignment_sequence per_assignments_f.assignment_sequence%type;
3306 l_assignment_id per_assignments_f.assignment_id%type;
3307 l_object_version_number per_assignments_f.object_version_number%type;
3308 l_current_applicant_flag per_people_f.current_applicant_flag%type;
3309 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3310 l_current_employee_flag per_people_f.current_employee_flag%type;
3311 l_employee_number per_people_f.employee_number%type;
3312 l_applicant_number per_people_f.applicant_number%TYPE;
3313 l_npw_number per_people_f.npw_number%TYPE;
3314 l_per_object_version_number per_people_f.object_version_number%TYPE;
3315 l_full_name per_people_f.full_name%type;
3316 l_system_person_type per_person_types.system_person_type%type;
3317 l_effective_date date;
3318 l_effective_start_date date;
3319 l_effective_end_date date;
3320 l_default_start_time per_business_groups.default_start_time%type;
3321 l_default_end_time per_business_groups.default_end_time%type;
3322 l_normal_hours number;
3323 l_frequency per_business_groups.frequency%type;
3324 l_recruiter_id per_vacancies.recruiter_id%type;
3325 l_grade_id per_vacancies.grade_id%type;
3326 l_position_id per_vacancies.position_id%type;
3327 l_job_id per_vacancies.job_id%type;
3328 l_location_id per_vacancies.location_id%type;
3329 l_people_group_id per_vacancies.people_group_id%type;
3330 l_vac_organization_id per_vacancies.organization_id%type;
3331 l_vac_business_group_id per_vacancies.business_group_id%type;
3332 l_group_name pay_people_groups.group_name%type;
3333 l_appl_override_warning boolean;
3334 --
3335 -- Local variable added for the before and after business process
3336 -- apply_for_internal_vacancy
3337 --
3338 l_apl_object_version_number number; -- THESE NEED TO BE CHANGED
3339 l_asg_object_version_number number; -- THESE NEED TO BE CHANGED
3340 l_per_effective_start_date date;
3341 l_per_effective_end_date date;
3342 --
3343 --
3344 -- select and validate the person
3345 --
3346 -- now returns employee number which is needed by upd.upd - thayden
3347 --
3348 cursor csr_chk_person_exists is
3349 select ppf.business_group_id
3350 ,ppf.employee_number
3351 ,ppf.npw_number
3352 ,ppt.system_person_type
3353 from per_person_types ppt
3354 ,per_people_f ppf
3355 where ppf.person_id = p_person_id
3356 and ppt.person_type_id = ppf.person_type_id
3357 and ppt.business_group_id + 0 = ppf.business_group_id
3358 and l_effective_date
3359 between ppf.effective_start_date
3360 and ppf.effective_end_date;
3361 --
3362 -- Get default person type id for a system person type EMP_APL
3363 --
3364 cursor csr_get_person_type_id is
3365 select person_type_id
3366 from per_person_types
3367 where business_group_id = l_business_group_id
3368 and active_flag = 'Y'
3369 and default_flag = 'Y'
3370 and system_person_type = 'EMP_APL';
3371 --
3372 -- Get organization id for business group.
3373 --
3374 cursor csr_get_organization_id is
3375 select organization_id
3376 ,legislation_code
3377 ,default_start_time
3378 ,default_end_time
3379 ,fnd_number.canonical_to_number(working_hours)
3380 ,frequency
3381 from per_business_groups
3382 where business_group_id = l_business_group_id;
3383 --
3384 -- Get vacancy information.
3385 --
3386 cursor csr_get_vacancy_details is
3387 select recruiter_id
3388 ,grade_id
3389 ,position_id
3390 ,job_id
3391 ,location_id
3392 ,people_group_id
3393 ,organization_id -- added org id to cursor. thayden 7/10.
3394 ,business_group_id -- added business_group_id to cursor lma 7/11
3395 from per_vacancies
3396 where vacancy_id = p_vacancy_id;
3397 --
3398 begin
3399 -- Bug 665566 Savepoint issued before validations start
3400 --
3401 -- Issue a savepoint if operating in validation only mode.
3402 --
3403 if p_validate then
3404 savepoint apply_for_internal_vacancy;
3405 end if;
3406 -- Bug 665566 End
3407
3408 if g_debug then
3409 hr_utility.set_location('Entering:'|| l_proc, 5);
3410 end if;
3411 --
3412 -- Truncate p_effective_date
3413 --
3414 l_effective_date := trunc(p_effective_date);
3415 -- Initialise local variables
3416 --
3417 l_applicant_number := p_applicant_number;
3418 l_per_object_version_number := p_per_object_version_number;
3419 --
3420 --
3421 -- Validation Logic
3422 --
3423 -- Ensure that the mandatory parameter, p_person_id
3424 -- is not null
3425 --
3426 hr_api.mandatory_arg_error
3427 (p_api_name => l_proc
3428 ,p_argument => 'person id'
3429 ,p_argument_value => p_person_id);
3430 --
3431 if g_debug then
3432 hr_utility.set_location(' Leaving:'||l_proc, 10);
3433 end if;
3434 --
3435 -- Check that this person (p_person_id) exists as of l_effective_date
3436 -- the current person type (per_people_f.person_type_id) has a
3437 -- corresponding system person type of EMP.
3438 --
3439 open csr_chk_person_exists;
3440 fetch csr_chk_person_exists into
3441 l_business_group_id
3442 ,l_employee_number
3443 ,l_npw_number
3444 ,l_system_person_type;
3445 if csr_chk_person_exists%notfound then
3446 close csr_chk_person_exists;
3447 hr_utility.set_message(800, 'HR_51011_PER_NOT_EXIST_DATE');
3448 hr_utility.raise_error;
3449 end if;
3450 close csr_chk_person_exists;
3451 --
3452 --
3453 -- ensure that the system person type is 'EMP'
3454 -- added and l_system_person_type <> 'EMP_APL' to if 15-Jul-97 lma
3455 if (l_system_person_type <> 'EMP' and l_system_person_type <> 'EMP_APL') then
3456 --
3457 -- the system person type is not 'EMP'.
3458 --
3459 hr_utility.set_message(800, 'PER_52788_PER_INV_PER_TYPE');
3460 hr_utility.raise_error;
3461 end if;
3462 if g_debug then
3463 hr_utility.set_location(l_proc, 15);
3464 end if;
3465
3466 --
3467 -- Initialise local variables
3468 --
3469 l_applicant_number := p_applicant_number;
3470 l_per_object_version_number := p_per_object_version_number;
3471 --
3472 begin
3473 --
3474 -- Start of call API User Hook for the before hook of apply_for_internal_vacancy_b
3475 --
3476 hr_employee_bk3.apply_for_internal_vacancy_b
3477 (
3478 p_business_group_id => l_business_group_id
3479 ,p_effective_date => l_effective_date
3480 ,p_person_id => p_person_id
3481 ,p_applicant_number => p_applicant_number
3482 ,p_per_object_version_number => p_per_object_version_number
3483 ,p_vacancy_id => p_vacancy_id
3484 ,p_person_type_id => p_person_type_id
3485 );
3486 exception
3487 when hr_api.cannot_find_prog_unit then
3488 hr_api.cannot_find_prog_unit_error
3489 (p_module_name => 'APPLY_FOR_INTERNAL_VACANCY'
3490 ,p_hook_type => 'BP'
3491 );
3492 --
3493 -- End of API User Hook for the before hook of apply_for_internal_vacancy
3494 --
3495 end;
3496 -- processing logic
3497 --
3498 if g_debug then
3499 hr_utility.set_location(l_proc, 20);
3500 end if;
3501 --
3502 -- PTU : Following Code has been added
3503 -- Validate that the person_type_id passed is a flavour of 'APL' or derive the default
3504 --
3505 per_per_bus.chk_person_type
3506 (p_person_type_id => l_person_type_id,
3507 p_business_group_id => l_business_group_id,
3508 p_expected_sys_type => 'APL');
3509 --
3510 -- Get default person type id for EMP_APL.
3511 --
3512 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
3513 (l_business_group_id,
3514 'EMP_APL');
3515 -- PTU end of changes
3516 --
3517 -- open csr_get_person_type_id;
3518 -- fetch csr_get_person_type_id into
3519 -- l_person_type_id;
3520 -- if csr_get_person_type_id%notfound then
3521 -- close csr_get_person_type_id;
3522 -- hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
3523 -- hr_utility.raise_error;
3524 -- end if;
3525 -- close csr_get_person_type_id;
3526 --
3527 if g_debug then
3528 hr_utility.set_location(l_proc, 25);
3529 end if;
3530 --
3531 -- Get organization id
3532 --
3533 open csr_get_organization_id;
3534 fetch csr_get_organization_id into
3535 l_organization_id
3536 ,l_legislation_code
3537 ,l_default_start_time
3538 ,l_default_end_time
3539 ,l_normal_hours
3540 ,l_frequency;
3541 if csr_get_organization_id%notfound then
3542 close csr_get_organization_id;
3543 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
3544 hr_utility.raise_error;
3545 end if;
3546 close csr_get_organization_id;
3547 --
3548 -- Get vacancy details.
3549 --
3550 if p_vacancy_id is not null then
3551 open csr_get_vacancy_details;
3552 fetch csr_get_vacancy_details into
3553 l_recruiter_id
3554 ,l_grade_id
3555 ,l_position_id
3556 ,l_job_id
3557 ,l_location_id
3558 ,l_people_group_id
3559 ,l_vac_organization_id -- added org id. thayden 7/10.
3560 ,l_vac_business_group_id; -- added business_group_id. thayden 7/11.
3561 if csr_get_vacancy_details%notfound then
3562 close csr_get_vacancy_details;
3563 hr_utility.set_message(801, 'HR_51001_THE_VAC_NOT_FOUND');
3564 hr_utility.raise_error;
3565 end if;
3566 close csr_get_vacancy_details;
3567 --added if ... end if (Rod's sugguestion)
3568 if l_vac_organization_id is null then
3569 l_vac_organization_id := l_vac_business_group_id;
3570 end if;
3571 else
3572 l_vac_organization_id := l_business_group_id;
3573 end if;
3574 --
3575 if g_debug then
3576 hr_utility.set_location(l_proc, 30);
3577 end if;
3578 --
3579 -- 3652025: Create an applicant, generate the application and
3580 -- the applicant assignment
3581 --
3582 hr_applicant_internal.create_applicant_anytime
3583 (p_effective_date => l_effective_date
3584 ,p_person_id => p_person_id
3585 ,p_applicant_number => p_applicant_number
3586 ,p_per_object_version_number => p_per_object_version_number
3587 ,p_vacancy_id => p_vacancy_id
3588 ,p_person_type_id => p_person_type_id
3589 ,p_assignment_status_type_id => NULL
3590 ,p_application_id => l_application_id
3591 ,p_assignment_id => l_assignment_id
3592 ,p_apl_object_version_number => l_apl_object_version_number
3593 ,p_asg_object_version_number => l_asg_object_version_number
3594 ,p_assignment_sequence => l_assignment_sequence
3595 ,p_per_effective_start_date => l_effective_start_date
3596 ,p_per_effective_end_date => l_effective_end_date
3597 ,p_appl_override_warning => l_appl_override_warning);
3598 --
3599 if g_debug then
3600 hr_utility.set_location(l_proc, 35);
3601 end if;
3602 --
3603 hr_security_internal.add_to_person_list(l_effective_date,l_assignment_id);
3604 --
3605 if g_debug then
3606 hr_utility.set_location(l_proc, 40);
3607 end if;
3608 --
3609 --
3610 begin
3611 --
3612 -- Start of call API User Hook for the after hook of re_hire_ex_employee
3613 --
3614 hr_employee_bk3.apply_for_internal_vacancy_a
3615 (
3616 p_business_group_id => l_business_group_id
3617 ,p_effective_date => l_effective_date
3618 ,p_person_id => p_person_id
3619 ,p_applicant_number => p_applicant_number
3620 ,p_per_object_version_number => p_per_object_version_number
3621 ,p_vacancy_id => p_vacancy_id
3622 ,p_person_type_id => p_person_type_id
3623 ,p_application_id => l_application_id
3624 ,p_assignment_id => l_assignment_id
3625 ,p_apl_object_version_number => l_apl_object_version_number
3626 ,p_asg_object_version_number => l_asg_object_version_number
3627 ,p_assignment_sequence => l_assignment_sequence
3628 ,p_per_effective_start_date => l_per_effective_start_date
3629 ,p_per_effective_end_date => l_per_effective_end_date
3630 ,p_appl_override_warning => l_appl_override_warning
3631 );
3632 --
3633 exception
3634 when hr_api.cannot_find_prog_unit then
3635 hr_api.cannot_find_prog_unit_error
3636 (p_module_name => 'APPLY_FOR_INTERNAL_VACANCY'
3637 ,p_hook_type => 'BP'
3638 );
3639 --
3640 -- End of API User Hook for the after hook of apply_for_internal_vacancy
3641 --
3642 end;
3643 --
3644 -- Set all output arguments
3645 --
3646 p_application_id := l_application_id;
3647 p_assignment_id := l_assignment_id;
3648 p_apl_object_version_number := l_apl_object_version_number;
3649 p_asg_object_version_number := l_asg_object_version_number;
3650 p_assignment_sequence := l_assignment_sequence;
3651 p_per_effective_start_date := l_per_effective_start_date;
3652 p_per_effective_end_date := l_per_effective_end_date;
3653 p_appl_override_warning := l_appl_override_warning;
3654 --
3655 -- when in validation only mode raise the Validate_Enabled exception
3656 --
3657
3658 if p_validate then
3659 raise hr_api.validate_enabled;
3660 end if;
3661 --
3662 if g_debug then
3663 hr_utility.set_location(' Leaving:'||l_proc, 50);
3664 end if;
3665 exception
3666 when hr_api.validate_enabled then
3667 --
3668 -- As the Validate_Enabled exception has been raised
3669 -- we must rollback to the savepoint
3670 --
3671 ROLLBACK TO apply_for_internal_vacancy;
3672 --
3673 -- Only set output warning arguments
3674 -- (Any key or derived arguments must be set to null
3675 -- when validation only mode is being used.)
3676 --
3677 p_per_object_version_number := l_per_object_version_number;
3678 p_applicant_number := l_applicant_number;
3679 p_application_id := null;
3680 p_assignment_id := null;
3681 p_apl_object_version_number := null;
3682 p_asg_object_version_number := null;
3683 p_assignment_sequence := null;
3684 p_per_effective_start_date := null;
3685 p_per_effective_end_date := null;
3686 p_appl_override_warning := null;
3687
3688 if g_debug then
3689 hr_utility.set_location(' Leaving:'||l_proc, 55);
3690 end if;
3691 end apply_for_internal_vacancy;
3692 --
3693 -- ----------------------------------------------------------------------------
3694 -- |----------------------------< hire_into_job - old >-----------------------|
3695 -- ----------------------------------------------------------------------------
3696 --
3697 PROCEDURE hire_into_job
3698 (p_validate IN BOOLEAN DEFAULT FALSE
3699 ,p_effective_date IN DATE
3700 ,p_person_id IN NUMBER
3701 ,p_object_version_number IN OUT NOCOPY NUMBER
3702 ,p_employee_number IN OUT NOCOPY VARCHAR2
3703 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
3704 ,p_person_type_id IN NUMBER DEFAULT NULL
3705 ,p_national_identifier IN VARCHAR2 DEFAULT NULL
3706 ,p_per_information7 IN VARCHAR2 DEFAULT NULL -- 3414724
3707 ,p_effective_start_date OUT NOCOPY DATE
3708 ,p_effective_end_date OUT NOCOPY DATE
3709 ,p_assign_payroll_warning OUT NOCOPY BOOLEAN
3710 ,p_orig_hire_warning OUT NOCOPY BOOLEAN
3711 )
3712 IS
3713 --
3714 -- Local variables
3715 --
3716 l_proc VARCHAR2(72) := g_package||'hire_into_job';
3717 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3718 --
3719 BEGIN
3720 --
3721 if g_debug then
3722 hr_utility.set_location('Entering:'||l_proc,10);
3723 end if;
3724 --
3725 hr_employee_api.hire_into_job
3726 (p_validate => p_validate
3727 ,p_effective_date => p_effective_date
3728 ,p_person_id => p_person_id
3729 ,p_object_version_number => p_object_version_number
3730 ,p_employee_number => p_employee_number
3731 ,p_datetrack_update_mode => p_datetrack_update_mode
3732 ,p_person_type_id => p_person_type_id
3733 ,p_national_identifier => p_national_identifier
3734 ,p_per_information7 => p_per_information7
3735 ,p_assignment_id => l_assignment_id
3736 ,p_effective_start_date => p_effective_start_date
3737 ,p_effective_end_date => p_effective_end_date
3738 ,p_assign_payroll_warning => p_assign_payroll_warning
3739 ,p_orig_hire_warning => p_orig_hire_warning
3740 );
3741 --
3742 if g_debug then
3743 hr_utility.set_location('Leaving:'||l_proc,999);
3744 end if;
3745
3746 --
3747 END hire_into_job;
3748 --
3749 -- ----------------------------------------------------------------------------
3750 -- |----------------------------< hire_into_job - new >-----------------------|
3751 -- ----------------------------------------------------------------------------
3752 --
3753 PROCEDURE hire_into_job
3754 (p_validate IN BOOLEAN DEFAULT FALSE
3755 ,p_effective_date IN DATE
3756 ,p_person_id IN NUMBER
3757 ,p_object_version_number IN OUT NOCOPY NUMBER
3758 ,p_employee_number IN OUT NOCOPY VARCHAR2
3759 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
3760 ,p_person_type_id IN NUMBER DEFAULT NULL
3761 ,p_national_identifier IN VARCHAR2 DEFAULT NULL
3762 ,p_per_information7 IN VARCHAR2 DEFAULT NULL -- 3414724
3763 ,p_assignment_id OUT NOCOPY NUMBER --Bug#3919096
3764 ,p_effective_start_date OUT NOCOPY DATE
3765 ,p_effective_end_date OUT NOCOPY DATE
3766 ,p_assign_payroll_warning OUT NOCOPY BOOLEAN
3767 ,p_orig_hire_warning OUT NOCOPY BOOLEAN
3768 )
3769 IS
3770 --
3771 -- Local variables
3772 --
3773 l_proc VARCHAR2(72) := g_package||'hire_into_job';
3774 --
3775 l_effective_date DATE;
3776 --
3777 l_object_version_number CONSTANT per_all_assignments_f.object_version_number%TYPE := p_object_version_number;
3778 l_datetrack_update_mode VARCHAR2(30) := p_datetrack_update_mode;
3779 l_employee_number CONSTANT per_all_people_f.applicant_number%TYPE := p_employee_number;
3780 l_emp_num per_all_people_f.applicant_number%TYPE := p_employee_number;
3781 l_ovn per_all_people_f.object_version_number%TYPE := p_object_version_number;
3782 l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
3783 l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
3784 l_assign_payroll_warning BOOLEAN;
3785 l_orig_hire_warning BOOLEAN;
3786 --
3787 l_person_type_id per_person_types.person_type_id%TYPE := p_person_type_id;
3788 l_person_type_id1 per_person_types.person_type_id%TYPE;
3789 --
3790 l_future_asgs_count INTEGER;
3791 l_system_person_type per_person_types.system_person_type%TYPE;
3792 l_effective_start_date DATE;
3793 l_effective_end_date DATE;
3794 l_comment_id hr_comments.comment_id%TYPE;
3795 l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3796 l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3797 l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3798 l_full_name per_all_people_f.full_name%TYPE;
3799 l_name_combination_warning BOOLEAN;
3800 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
3801 l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
3802 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3803 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
3804 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
3805 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3806 l_person_type_usage_id per_person_type_usages.person_type_usage_id%TYPE;
3807 l_ptu_object_version_number per_person_type_usages.object_version_number%TYPE;
3808 --
3809 -- Start of fix for bug 3143299
3810 l_final_process_date per_periods_of_service.final_process_date%type;
3811 --
3812 -- Local cursors
3813 cursor csr_chk_period_of_service is
3814 --
3815 -- 115.57 (START)
3816 --
3817 --select pos.final_process_date
3818 select pos.actual_termination_date,
3819 pos.last_standard_process_date,
3820 pos.final_process_date
3821 --
3822 -- 115.57 (END)
3823 --
3824 from per_periods_of_service pos
3825 where pos.person_id = p_person_id
3826 order by pos.date_start desc;
3827 --
3828 -- 115.57 (START)
3829 --
3830 l_fpd per_periods_of_service.final_process_date%TYPE;
3831 l_atd per_periods_of_service.actual_termination_date%TYPE;
3832 l_lspd per_periods_of_service.last_standard_process_date%TYPE;
3833 l_rule_value pay_legislation_rules.rule_mode%TYPE;
3834 l_rule_found BOOLEAN;
3835 --
3836 -- 115.57 (END)
3837 --
3838 --
3839 -- End of fix for bug 3143299
3840 --
3841 CURSOR csr_per_details
3842 (p_person_id IN per_all_people_f.person_id%TYPE
3843 ,p_effective_date IN DATE
3844 )
3845 IS
3846 SELECT pet.person_type_id
3847 ,pet.system_person_type
3848 ,per.effective_start_date
3849 ,per.effective_end_date
3850 ,per.applicant_number
3851 ,per.employee_number
3852 ,per.npw_number
3853 ,bus.business_group_id
3854 ,bus.legislation_code
3855 FROM per_people_f per
3856 ,per_business_groups bus
3857 ,per_person_types pet
3858 WHERE per.person_type_id = pet.person_type_id
3859 AND per.business_group_id+0 = bus.business_group_id
3860 AND per.person_id = csr_per_details.p_person_id
3861 AND csr_per_details.p_effective_date BETWEEN per.effective_start_date
3862 AND per.effective_end_date;
3863 l_per_details_rec csr_per_details%ROWTYPE;
3864 --
3865 BEGIN
3866 --
3867 if g_debug then
3868 hr_utility.set_location('Entering:'||l_proc,10);
3869 end if;
3870 --
3871 -- Ensure mandatory arguments have been passed
3872 --
3873 hr_api.mandatory_arg_error
3874 (p_api_name => l_proc
3875 ,p_argument => 'person_id'
3876 ,p_argument_value => p_person_id
3877 );
3878 --
3879 hr_api.mandatory_arg_error
3880 (p_api_name => l_proc
3881 ,p_argument => 'effective_date'
3882 ,p_argument_value => p_effective_date
3883 );
3884 --
3885 -- Truncate all date parameters passed in
3886 --
3887 l_effective_date := TRUNC(p_effective_date);
3888 --
3889 -- Issue savepoint
3890 --
3891 SAVEPOINT hire_into_job;
3892 --
3893 if g_debug then
3894 hr_utility.set_location(l_proc,20);
3895 end if;
3896 --
3897 -- Get dervied details for person on effective date
3898 --
3899 OPEN csr_per_details
3900 (p_person_id => p_person_id
3901 ,p_effective_date => p_effective_date
3902 );
3903 FETCH csr_per_details INTO l_per_details_rec;
3904 IF csr_per_details%NOTFOUND
3905 THEN
3906 CLOSE csr_per_details;
3907 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
3908 hr_utility.raise_error;
3909 END IF;
3910 CLOSE csr_per_details;
3911 --
3912 if g_debug then
3913 hr_utility.set_location(l_proc,30);
3914 end if;
3915 --
3916 -- Call Before Process User Hook
3917 --
3918 BEGIN
3919 hr_employee_bk4.hire_into_job_b
3920 (p_effective_date => l_effective_date
3921 ,p_person_id => p_person_id
3922 ,p_business_group_id => l_per_details_rec.business_group_id
3923 ,p_object_version_number => p_object_version_number
3924 ,p_datetrack_update_mode => p_datetrack_update_mode
3925 ,p_employee_number => p_employee_number
3926 ,p_person_type_id => p_person_type_id
3927 ,p_national_identifier => p_national_identifier
3928 ,p_per_information7 => p_per_information7 --3414274
3929 );
3930 EXCEPTION
3931 WHEN hr_api.cannot_find_prog_unit
3932 THEN
3933 hr_api.cannot_find_prog_unit_error
3934 (p_module_name => 'HIRE_INTO_JOB'
3935 ,p_hook_type => 'BP'
3936 );
3937 END;
3938 --
3939 if g_debug then
3940 hr_utility.set_location(l_proc,40);
3941 end if;
3942 --
3943 -- Check the person is of a correct system person type
3944 --
3945 IF l_per_details_rec.system_person_type NOT IN ('EX_APL','EX_EMP','OTHER')
3946 THEN
3947 if g_debug then
3948 hr_utility.set_location(l_proc,50);
3949 end if;
3950 hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
3951 hr_utility.raise_error;
3952 END IF;
3953 --
3954 if g_debug then
3955 hr_utility.set_location(l_proc,60);
3956 end if;
3957 -- Start of fix for bug 3143299.
3958 --
3959 --
3960 -- 115.57 (START)
3961 --
3962 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
3963 ,l_per_details_rec.legislation_code
3964 ,l_rule_value
3965 ,l_rule_found
3966 );
3967 --
3968 if g_debug then
3969 hr_utility.set_location(l_proc,62);
3970 end if;
3971 --
3972 -- 115.57 (END)
3973 --
3974 --
3975 -- if the person is of type Ex-employee then ensure the most recent period
3976 -- of service for this person has been completely terminated. i.e. check
3977 -- that period_of_service.final_process_date is not null and comes before
3978 -- p_hire_date. we only fetch the 1st row (which is the latest pos).
3979 --
3980 if l_per_details_rec.system_person_type = 'EX_EMP' then
3981 --
3982 open csr_chk_period_of_service;
3983 --
3984 -- 115.57 (START)
3985 --
3986 --fetch csr_chk_period_of_service into l_final_process_date;
3987 fetch csr_chk_period_of_service into l_atd, l_lspd, l_fpd;
3988 --
3989 -- 115.57 (END)
3990 --
3991 if csr_chk_period_of_service%notfound then
3992 close csr_chk_period_of_service;
3993 --
3994 -- a period of service row does not exist for the person therefore
3995 -- we must error
3996 --
3997 -- This person does not have a previous period of service
3998 --
3999 hr_utility.set_message(801, 'HR_51013_PDS_NOT_EXIST');
4000 hr_utility.raise_error;
4001 end if;
4002 close csr_chk_period_of_service;
4003 --
4004 --
4005 -- 115.57 (START)
4006 --
4007 if (l_fpd is null) then
4008 --
4009 -- the employee cannot be terminated as the final process date has not
4010 -- been set
4011 --
4012 -- You cannot re-hire a person who does not have a final processing date
4013 -- set for their most recent period of service
4014 --
4015 hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
4016 hr_utility.raise_error;
4017 end if;
4018 --
4019 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
4020 --
4021 -- Rehire before FPD allowed (new behaviour)
4022 --
4023 if nvl(l_lspd,l_atd) >= l_effective_date then
4024 --
4025 -- the re hire date is before the current LSPD or ATD
4026 --
4027 -- You cannot re-hire an Ex-Employee before their LSPD.
4028 -- Please specify a Re-Hire date which is after the LSPD.
4029 --
4030 hr_utility.set_message(801, 'HR_449759_REHIRE_AFTER_LSPD');
4031 hr_utility.raise_error;
4032 end if;
4033 else
4034 --
4035 -- Rehire before FPD is not allowed (old behaviour)
4036 --
4037 if l_fpd >= l_effective_date then
4038 --
4039 -- the re hire date is before the current final process date
4040 --
4041 -- You cannot re-hire an Ex-Employee before their final processing date.
4042 -- Please specify a Re-Hire date which is after the final processing date.
4043 --
4044 hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
4045 hr_utility.raise_error;
4046 end if;
4047 end if;
4048 --
4049 --if (l_final_process_date is null) then
4050 -- --
4051 -- -- the employee cannot be terminated as the final process date has not
4052 -- -- been set
4053 -- --
4054 -- -- You cannot re-hire a person who does not have a final processing date
4055 -- -- set for their most recent period of service
4056 -- --
4057 -- hr_utility.set_message(801, 'HR_51014_REHIRE_FINAL_DATE');
4058 -- hr_utility.raise_error;
4059 --elsif (l_final_process_date >= l_effective_date) then
4060 -- --
4061 -- -- the re hire date is before the current final process date
4062 -- --
4063 -- -- You cannot re-hire an Ex-Employee before their final processing date.
4064 -- -- Please specify a Re-Hire date which is after the final processing date.
4065 -- --
4066 -- hr_utility.set_message(801, 'HR_51015_REHIRE_NEW_DATE');
4067 -- hr_utility.raise_error;
4068 --end if;
4069 --
4070 -- 115.57 (END)
4071 --
4072 end if;
4073 --
4074 if g_debug then
4075 hr_utility.set_location(l_proc,65);
4076 end if;
4077 -- End of fix for bug 3143299
4078
4079 -- Ensure the employee number will not be changed if it exists
4080 --
4081 IF l_per_details_rec.employee_number IS NOT NULL
4082 AND NVL(p_employee_number,hr_api.g_number) <> l_per_details_rec.employee_number
4083 THEN
4084 if g_debug then
4085 hr_utility.set_location(l_proc,70);
4086 end if;
4087 p_employee_number := l_per_details_rec.employee_number;
4088 END IF;
4089 --
4090 if g_debug then
4091 hr_utility.set_location(l_proc,80);
4092 end if;
4093 --
4094 -- Check the person does not have future assignment changes
4095 --
4096 l_future_asgs_count := future_asgs_count
4097 (p_person_id => p_person_id
4098 ,p_effective_date => l_effective_date
4099 );
4100 IF l_future_asgs_count > 0
4101 THEN
4102 if g_debug then
4103 hr_utility.set_location(l_proc,90);
4104 end if;
4105 hr_utility.set_message(800,'HR_7975_ASG_INV_FUTURE_ASA');
4106 hr_utility.raise_error;
4107 END IF;
4108 --
4109 if g_debug then
4110 hr_utility.set_location(l_proc,100);
4111 end if;
4112 --
4113 -- If person type id is not null check it corresponds to the correct type for
4114 -- the persons current system person type is currently active and in the
4115 -- correct business group, otherwise set person type id to the active default
4116 -- for the correct system person type in the correct business group
4117 --
4118 if g_debug then
4119 hr_utility.set_location(l_proc,110);
4120 end if;
4121 l_system_person_type := 'EMP';
4122 per_per_bus.chk_person_type
4123 (p_person_type_id => l_person_type_id
4124 ,p_business_group_id => l_per_details_rec.business_group_id
4125 ,p_expected_sys_type => l_system_person_type
4126 );
4127 --
4128 if g_debug then
4129 hr_utility.set_location(l_proc,120);
4130 end if;
4131 --
4132 -- Check the datetrack mode
4133 --
4134 IF (l_per_details_rec.system_person_type IN ('OTHER','EX_EMP')) -- Bug 3230389
4135 THEN
4136 IF (l_datetrack_update_mode IS NULL)
4137 THEN
4138 if l_effective_date = l_per_details_rec.effective_start_date then -- 3194314
4139 l_datetrack_update_mode := hr_api.g_correction;
4140 else
4141 l_datetrack_update_mode := hr_api.g_update;
4142 end if;
4143 ELSE
4144 IF (l_datetrack_update_mode NOT IN (hr_api.g_update,hr_api.g_correction))
4145 THEN
4146 hr_utility.set_message(800,'HR_7203_DT_UPD_MODE_INVALID');
4147 hr_utility.raise_error;
4148 END IF;
4149 END IF;
4150 ELSE
4151 l_datetrack_update_mode := hr_api.g_update;
4152 END IF;
4153 --
4154 -- PTU : Added
4155
4156 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
4157 ( l_per_details_rec.business_group_id,
4158 'EMP');
4159 -- PTU : End
4160
4161 -- Update the person details to the new person type
4162 --
4163 per_per_upd.upd
4164 (p_person_id => p_person_id
4165 ,p_effective_start_date => l_per_effective_start_date
4166 ,p_effective_end_date => l_per_effective_end_date
4167 ,p_person_type_id => l_person_type_id1
4168 ,p_applicant_number => l_per_details_rec.applicant_number
4169 ,p_comment_id => l_comment_id
4170 ,p_current_applicant_flag => l_current_applicant_flag
4171 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
4172 ,p_current_employee_flag => l_current_employee_flag
4173 ,p_employee_number => p_employee_number
4174 ,p_national_identifier => p_national_identifier
4175 ,p_full_name => l_full_name
4176 ,p_object_version_number => p_object_version_number
4177 ,p_effective_date => l_effective_date
4178 ,p_datetrack_mode => l_datetrack_update_mode
4179 ,p_name_combination_warning => l_name_combination_warning
4180 ,p_dob_null_warning => l_assign_payroll_warning
4181 ,p_orig_hire_warning => l_orig_hire_warning
4182 ,p_npw_number => l_per_details_rec.npw_number
4183 ,p_per_information7 => p_per_information7 --3414274
4184 );
4185 --
4186 if g_debug then
4187 hr_utility.set_location(l_proc,130);
4188 end if;
4189 --
4190 -- add to current security list
4191 --
4192 hr_security_internal.populate_new_person(l_per_details_rec.business_group_id,p_person_id);
4193 --
4194 if g_debug then
4195 hr_utility.set_location(l_proc,135);
4196 end if;
4197 --
4198 -- Create an period of service for the person
4199 --
4200 per_pds_ins.ins
4201 (p_effective_date => l_effective_date
4202 ,p_business_group_id => l_per_details_rec.business_group_id
4203 ,p_person_id => p_person_id
4204 ,p_date_start => l_effective_date
4205 ,p_validate_df_flex => false
4206 ,p_period_of_service_id => l_period_of_service_id
4207 ,p_object_version_number => l_pds_object_version_number
4208 );
4209 --
4210 if g_debug then
4211 hr_utility.set_location(l_proc,140);
4212 end if;
4213 --
4214 -- Create a default employee assignment for the person
4215 --
4216 hr_assignment_internal.create_default_emp_asg
4217 (p_effective_date => l_effective_date
4218 ,p_business_group_id => l_per_details_rec.business_group_id
4219 ,p_person_id => p_person_id
4220 ,p_period_of_service_id => l_period_of_service_id
4221 ,p_assignment_id => l_assignment_id
4222 ,p_object_version_number => l_asg_object_version_number
4223 ,p_assignment_sequence => l_assignment_sequence
4224 ,p_assignment_number => l_assignment_number
4225 );
4226 --
4227 if g_debug then
4228 hr_utility.set_location(l_proc,150);
4229 end if;
4230 --
4231 -- 115.57 (START)
4232 --
4233 --
4234 -- If rehire before FPD is allowed, any resulting overlapping PDS
4235 -- will require updating the terminated primary assignment to
4236 -- secondary.
4237 --
4238 if l_rule_found and nvl(l_rule_value,'N') = 'Y' then
4239 manage_rehire_primary_asgs(p_person_id => p_person_id
4240 ,p_rehire_date => l_effective_date
4241 ,p_cancel => 'N'
4242 );
4243 end if;
4244 --
4245 if g_debug then
4246 hr_utility.set_location(l_proc,155);
4247 end if;
4248 --
4249 -- 115.57 (END)
4250 --
4251 --
4252 -- Create person type usage record
4253 -- No Longer Required: This is automatically created on insert of
4254 -- a period of service record above.
4255 --
4256 /*
4257 hr_per_type_usage_internal.create_person_type_usage
4258 (p_effective_date => l_effective_date
4259 ,p_person_id => p_person_id
4260 ,p_person_type_id => l_person_type_id
4261 ,p_person_type_usage_id => l_person_type_usage_id
4262 ,p_effective_start_date => l_effective_start_date
4263 ,p_effective_end_date => l_effective_end_date
4264 ,p_object_version_number => l_ptu_object_version_number
4265 );
4266 */
4267
4268 -- PTU : Following Code has been added
4269
4270 hr_per_type_usage_internal.maintain_person_type_usage
4271 (p_effective_date => l_effective_date
4272 ,p_person_id => p_person_id
4273 ,p_person_type_id => l_person_type_id
4274 ,p_datetrack_update_mode => l_datetrack_update_mode -- Bug 3230389
4275 );
4276
4277 -- PTU : End of changes
4278 -- 1766066: added call for contact start date enh.
4279 --
4280 per_people12_pkg.maintain_coverage(p_person_id => p_person_id
4281 ,p_type => 'EMP'
4282 );
4283 -- 1766066 end.
4284 --
4285 -- Call After Process User Hook
4286 --
4287 BEGIN
4288 hr_employee_bk4.hire_into_job_a
4289 (p_effective_date => l_effective_date
4290 ,p_person_id => p_person_id
4291 ,p_business_group_id => l_per_details_rec.business_group_id
4292 ,p_object_version_number => p_object_version_number
4293 ,p_datetrack_update_mode => p_datetrack_update_mode
4294 ,p_employee_number => p_employee_number
4295 ,p_person_type_id => p_person_type_id
4296 ,p_national_identifier => p_national_identifier
4297 ,p_per_information7 => p_per_information7 -- 3414274
4298 ,p_assignment_id => l_assignment_id --Bug#3919096
4299 ,p_effective_start_date => l_per_effective_start_date
4300 ,p_effective_end_date => l_per_effective_end_date
4301 ,p_assign_payroll_warning => l_assign_payroll_warning
4302 ,p_orig_hire_warning => l_orig_hire_warning
4303 );
4304 EXCEPTION
4305 WHEN hr_api.cannot_find_prog_unit
4306 THEN
4307 hr_api.cannot_find_prog_unit_error
4308 (p_module_name => 'HIRE_INTO_JOB'
4309 ,p_hook_type => 'AP'
4310 );
4311 END;
4312 --
4313 if g_debug then
4314 hr_utility.set_location(l_proc,160);
4315 end if;
4316 --
4317 -- When in validation only mode raise validate_enabled exception
4318 --
4319 IF p_validate
4320 THEN
4321 RAISE hr_api.validate_enabled;
4322 END IF;
4323 --
4324 -- Set OUT parameters
4325 --
4326 p_assignment_id := l_assignment_id; --Bug#3919096
4327 p_effective_start_date := l_per_effective_start_date;
4328 p_effective_end_date := l_per_effective_end_date;
4329 p_assign_payroll_warning := l_assign_payroll_warning;
4330 p_orig_hire_warning := l_orig_hire_warning;
4331 --
4332 if g_debug then
4333 hr_utility.set_location(' Leaving:'||l_proc,1000);
4334 end if;
4335 --
4336 EXCEPTION
4337 --
4338 WHEN hr_api.validate_enabled
4339 THEN
4340 --
4341 -- In validation only mode
4342 -- Rollback to savepoint
4343 -- Set relevant output warning arguments
4344 -- Reset any key or derived arguments
4345 --
4346 ROLLBACK TO hire_into_job;
4347 p_object_version_number := l_object_version_number;
4348 p_employee_number := l_employee_number;
4349 p_assignment_id := NULL; --Bug#3919096
4350 p_effective_start_date := NULL;
4351 p_effective_end_date := NULL;
4352 p_assign_payroll_warning := l_assign_payroll_warning;
4353 p_orig_hire_warning := l_orig_hire_warning;
4354 --
4355 WHEN OTHERS
4356 THEN
4357 --
4358 -- Validation or unexpected error occured
4359 -- Ensure opened non-local cursors are closed
4360 -- Rollback to savepoint
4361 -- Re-raise exception
4362 --
4363 ROLLBACK TO hire_into_job;
4364 --
4365 -- set in out parameters and set out parameters
4366 --
4367 p_object_version_number := l_ovn;
4368 p_employee_number := l_emp_num;
4369 p_assignment_id := NULL; --Bug#3919096
4370 p_effective_start_date := NULL;
4371 p_effective_end_date := NULL;
4372 p_assign_payroll_warning := false;
4373 p_orig_hire_warning := false;
4374 --
4375 IF csr_per_details%ISOPEN
4376 THEN
4377 CLOSE csr_per_details;
4378 END IF;
4379 RAISE;
4380 --
4381 END hire_into_job;
4382 --
4383 -- 115.57 (START)
4384 --
4385 -- ----------------------------------------------------------------------------
4386 -- |---------------------< Update_Rehire_Primary_Asgs >-----------------------|
4387 -- ----------------------------------------------------------------------------
4388 -- {Start Of Comments}
4389 --
4390 -- Description:
4391 -- Handles the update of ASG records for rehire before FPD which leads to
4392 -- overlapping PDS and cancel rehire with overlapping PDS. This is intended
4393 -- to be invoked from MANAGE_REHIRE_PRIMARY_ASGS
4394 --
4395 -- Prerequisites:
4396 -- The person assignments and periods of service must already be present.
4397 --
4398 -- In Parameters:
4399 -- Name Reqd Type Description
4400 -- p_person_id Yes number Identifier for the person
4401 -- p_rehire_date Yes date Re-Hire Date
4402 -- p_cancel Yes varchar2 'Y' indicates cancel rehire
4403 -- 'N' indicates rehire
4404 --
4405 -- Post Success:
4406 -- No error is raised if the new hire date is validSG records are updated
4407 -- with correct primary flag values for rehire and cancel rehire.
4408 --
4409 -- Post Failure:
4410 -- An error is raised and control returned.
4411 --
4412 -- Access Status:
4413 -- For Oracle Internal use only.
4414 --
4415 -- {End Of Comments}
4416 --
4417 PROCEDURE update_rehire_primary_asgs
4418 (p_person_id IN NUMBER
4419 ,p_rehire_date IN DATE
4420 ,p_cancel IN VARCHAR2
4421 ) IS
4422 --
4423 l_proc VARCHAR2(80) := g_package||'update_rehire_primary_asgs';
4424 --
4425 -- Cursor to get primary assignment on specified date
4426 --
4427 CURSOR c_prim_asg
4428 (p_person_id IN per_all_people_f.person_id%TYPE
4429 ,p_effective_date IN DATE
4430 ) IS
4431 SELECT assignment_id
4432 ,effective_start_date
4433 ,effective_end_date
4434 ,business_group_id
4435 ,recruiter_id
4436 ,grade_id
4437 ,position_id
4438 ,job_id
4439 ,assignment_status_type_id
4440 ,payroll_id
4441 ,location_id
4442 ,person_referred_by_id
4443 ,supervisor_id
4444 ,special_ceiling_step_id
4445 ,person_id
4446 ,recruitment_activity_id
4447 ,source_organization_id
4448 ,organization_id
4449 ,people_group_id
4450 ,soft_coding_keyflex_id
4451 ,vacancy_id
4452 ,pay_basis_id
4453 ,assignment_sequence
4454 ,assignment_type
4455 ,primary_flag
4456 ,application_id
4457 ,assignment_number
4458 ,change_reason
4459 ,comment_id
4460 ,date_probation_end
4461 ,default_code_comb_id
4462 ,employment_category
4463 ,frequency
4464 ,internal_address_line
4465 ,manager_flag
4466 ,normal_hours
4467 ,perf_review_period
4468 ,perf_review_period_frequency
4469 ,period_of_service_id
4470 ,probation_period
4471 ,probation_unit
4472 ,sal_review_period
4473 ,sal_review_period_frequency
4474 ,set_of_books_id
4475 ,source_type
4476 ,time_normal_finish
4477 ,time_normal_start
4478 ,bargaining_unit_code
4479 ,labour_union_member_flag
4480 ,hourly_salaried_code
4481 ,request_id
4482 ,program_application_id
4483 ,program_id
4484 ,program_update_date
4485 ,ass_attribute_category
4486 ,ass_attribute1
4487 ,ass_attribute2
4488 ,ass_attribute3
4489 ,ass_attribute4
4490 ,ass_attribute5
4491 ,ass_attribute6
4492 ,ass_attribute7
4493 ,ass_attribute8
4494 ,ass_attribute9
4495 ,ass_attribute10
4496 ,ass_attribute11
4497 ,ass_attribute12
4498 ,ass_attribute13
4499 ,ass_attribute14
4500 ,ass_attribute15
4501 ,ass_attribute16
4502 ,ass_attribute17
4503 ,ass_attribute18
4504 ,ass_attribute19
4505 ,ass_attribute20
4506 ,ass_attribute21
4507 ,ass_attribute22
4508 ,ass_attribute23
4509 ,ass_attribute24
4510 ,ass_attribute25
4511 ,ass_attribute26
4512 ,ass_attribute27
4513 ,ass_attribute28
4514 ,ass_attribute29
4515 ,ass_attribute30
4516 ,last_update_date
4517 ,last_updated_by
4518 ,last_update_login
4519 ,created_by
4520 ,creation_date
4521 ,title
4522 ,object_version_number
4523 ,contract_id
4524 ,cagr_id_flex_num
4525 ,cagr_grade_def_id
4526 ,establishment_id
4527 ,collective_agreement_id
4528 ,notice_period
4529 ,notice_period_uom
4530 ,employee_category
4531 ,work_at_home
4532 ,job_post_source_name
4533 ,period_of_placement_date_start
4534 ,vendor_id
4535 ,vendor_site_id
4536 ,po_header_id
4537 ,po_line_id
4538 ,projected_assignment_end
4539 ,vendor_employee_number
4540 ,vendor_assignment_number
4541 ,assignment_category
4542 ,project_title
4543 ,grade_ladder_pgm_id
4544 ,supervisor_assignment_id
4545 FROM per_assignments_f
4546 WHERE person_id = c_prim_asg.p_person_id
4547 AND primary_flag = 'Y'
4548 AND assignment_type <> 'B' -- 115.59
4549 AND c_prim_asg.p_effective_date BETWEEN effective_start_date
4550 AND effective_end_date;
4551 --
4552 lr_prim_asg c_prim_asg%ROWTYPE;
4553 --
4554 BEGIN
4555 --
4556 hr_utility.set_location('Entering:'|| l_proc, 5);
4557 --
4558 -- Issue a savepoint.
4559 --
4560 SAVEPOINT update_rehire_primary_asgs;
4561 --
4562 IF p_cancel = 'Y' THEN
4563 --
4564 -- Perform Cancel Rehire updates to ASG records
4565 --
4566 hr_utility.set_location(l_proc, 10);
4567 --
4568 -- Get the primary assignment on (rehire date - 1)
4569 --
4570 OPEN c_prim_asg (p_person_id
4571 ,(p_rehire_date-1)
4572 );
4573 FETCH c_prim_asg INTO lr_prim_asg;
4574 IF c_prim_asg%NOTFOUND THEN
4575 hr_utility.set_message(800,'PER_52595_PRIM_ASG_INV');
4576 hr_utility.raise_error;
4577 END IF;
4578 CLOSE c_prim_asg;
4579 --
4580 hr_utility.set_location(l_proc, 20);
4581 --
4582 -- Update all assignments records for this assignment with
4583 -- ESD >= rehire date setting primary flag to 'Y'
4584 --
4585 UPDATE per_assignments_f
4586 SET primary_flag = 'Y'
4587 ,object_version_number = object_version_number + 1
4588 WHERE assignment_id = lr_prim_asg.assignment_id
4589 AND assignment_type <> 'B' -- 115.59
4590 AND effective_start_date >= p_rehire_date;
4591 --
4592 ELSIF p_cancel = 'N' THEN
4593 --
4594 -- Perform Rehire updates to ASG records
4595 --
4596 hr_utility.set_location(l_proc, 30);
4597 --
4598 -- Get the primary assignment on rehire date
4599 --
4600 OPEN c_prim_asg (p_person_id
4601 ,p_rehire_date
4602 );
4603 FETCH c_prim_asg INTO lr_prim_asg;
4604 IF c_prim_asg%NOTFOUND THEN
4605 hr_utility.set_message(800,'PER_52595_PRIM_ASG_INV');
4606 hr_utility.raise_error;
4607 END IF;
4608 CLOSE c_prim_asg;
4609 --
4610 IF p_rehire_date = lr_prim_asg.effective_start_date THEN
4611 --
4612 hr_utility.set_location(l_proc, 40);
4613 --
4614 -- Set the primary assignment flag to 'N' for this record.
4615 --
4616 UPDATE per_assignments_f
4617 SET primary_flag = 'N'
4618 ,object_version_number = object_version_number + 1
4619 WHERE assignment_id = lr_prim_asg.assignment_id
4620 AND assignment_type <> 'B' -- 115.59
4621 AND effective_start_date = p_rehire_date;
4622 --
4623 ELSIF p_rehire_date = lr_prim_asg.effective_end_date THEN
4624 --
4625 hr_utility.set_location(l_proc, 50);
4626 --
4627 -- Update EED to rehire date - 1
4628 --
4629 UPDATE per_assignments_f
4630 SET effective_end_date = (p_rehire_date - 1)
4631 ,object_version_number = object_version_number + 1
4632 WHERE assignment_id = lr_prim_asg.assignment_id
4633 AND assignment_type <> 'B' -- 115.59
4634 AND effective_end_date = p_rehire_date;
4635 --
4636 hr_utility.set_location(l_proc, 60);
4637 --
4638 -- Insert new record with primary flag 'N' and ESD
4639 -- same as EED same as rehire date
4640 --
4641 INSERT INTO per_all_assignments_f
4642 (assignment_id
4643 ,effective_start_date
4644 ,effective_end_date
4645 ,business_group_id
4646 ,recruiter_id
4647 ,grade_id
4648 ,position_id
4649 ,job_id
4650 ,assignment_status_type_id
4651 ,payroll_id
4652 ,location_id
4653 ,person_referred_by_id
4654 ,supervisor_id
4655 ,special_ceiling_step_id
4656 ,person_id
4657 ,recruitment_activity_id
4658 ,source_organization_id
4659 ,organization_id
4660 ,people_group_id
4661 ,soft_coding_keyflex_id
4662 ,vacancy_id
4663 ,pay_basis_id
4664 ,assignment_sequence
4665 ,assignment_type
4666 ,primary_flag
4667 ,application_id
4668 ,assignment_number
4669 ,change_reason
4670 ,comment_id
4671 ,date_probation_end
4672 ,default_code_comb_id
4673 ,employment_category
4674 ,frequency
4675 ,internal_address_line
4676 ,manager_flag
4677 ,normal_hours
4678 ,perf_review_period
4679 ,perf_review_period_frequency
4680 ,period_of_service_id
4681 ,probation_period
4682 ,probation_unit
4683 ,sal_review_period
4684 ,sal_review_period_frequency
4685 ,set_of_books_id
4686 ,source_type
4687 ,time_normal_finish
4688 ,time_normal_start
4689 ,bargaining_unit_code
4690 ,labour_union_member_flag
4691 ,hourly_salaried_code
4692 ,request_id
4693 ,program_application_id
4694 ,program_id
4695 ,program_update_date
4696 ,ass_attribute_category
4697 ,ass_attribute1
4698 ,ass_attribute2
4699 ,ass_attribute3
4700 ,ass_attribute4
4701 ,ass_attribute5
4702 ,ass_attribute6
4703 ,ass_attribute7
4704 ,ass_attribute8
4705 ,ass_attribute9
4706 ,ass_attribute10
4707 ,ass_attribute11
4708 ,ass_attribute12
4709 ,ass_attribute13
4710 ,ass_attribute14
4711 ,ass_attribute15
4712 ,ass_attribute16
4713 ,ass_attribute17
4714 ,ass_attribute18
4715 ,ass_attribute19
4716 ,ass_attribute20
4717 ,ass_attribute21
4718 ,ass_attribute22
4719 ,ass_attribute23
4720 ,ass_attribute24
4721 ,ass_attribute25
4722 ,ass_attribute26
4723 ,ass_attribute27
4724 ,ass_attribute28
4725 ,ass_attribute29
4726 ,ass_attribute30
4727 ,last_update_date
4728 ,last_updated_by
4729 ,last_update_login
4730 ,created_by
4731 ,creation_date
4732 ,title
4733 ,object_version_number
4734 ,contract_id
4735 ,cagr_id_flex_num
4736 ,cagr_grade_def_id
4737 ,establishment_id
4738 ,collective_agreement_id
4739 ,notice_period
4740 ,notice_period_uom
4741 ,employee_category
4742 ,work_at_home
4743 ,job_post_source_name
4744 ,period_of_placement_date_start
4745 ,vendor_id
4746 ,vendor_site_id
4747 ,po_header_id
4748 ,po_line_id
4749 ,projected_assignment_end
4750 ,vendor_employee_number
4751 ,vendor_assignment_number
4752 ,assignment_category
4753 ,project_title
4754 ,grade_ladder_pgm_id
4755 ,supervisor_assignment_id
4756 )
4757 VALUES
4758 (lr_prim_asg.assignment_id
4759 ,p_rehire_date -- New ESD
4760 ,p_rehire_date -- New EED
4761 ,lr_prim_asg.business_group_id
4762 ,lr_prim_asg.recruiter_id
4763 ,lr_prim_asg.grade_id
4764 ,lr_prim_asg.position_id
4765 ,lr_prim_asg.job_id
4766 ,lr_prim_asg.assignment_status_type_id
4767 ,lr_prim_asg.payroll_id
4768 ,lr_prim_asg.location_id
4769 ,lr_prim_asg.person_referred_by_id
4770 ,lr_prim_asg.supervisor_id
4771 ,lr_prim_asg.special_ceiling_step_id
4772 ,lr_prim_asg.person_id
4773 ,lr_prim_asg.recruitment_activity_id
4774 ,lr_prim_asg.source_organization_id
4775 ,lr_prim_asg.organization_id
4776 ,lr_prim_asg.people_group_id
4777 ,lr_prim_asg.soft_coding_keyflex_id
4778 ,lr_prim_asg.vacancy_id
4779 ,lr_prim_asg.pay_basis_id
4780 ,lr_prim_asg.assignment_sequence
4781 ,lr_prim_asg.assignment_type
4782 ,'N' -- New Primary Flag
4783 ,lr_prim_asg.application_id
4784 ,lr_prim_asg.assignment_number
4785 ,lr_prim_asg.change_reason
4786 ,lr_prim_asg.comment_id
4787 ,lr_prim_asg.date_probation_end
4788 ,lr_prim_asg.default_code_comb_id
4789 ,lr_prim_asg.employment_category
4790 ,lr_prim_asg.frequency
4791 ,lr_prim_asg.internal_address_line
4792 ,lr_prim_asg.manager_flag
4793 ,lr_prim_asg.normal_hours
4794 ,lr_prim_asg.perf_review_period
4795 ,lr_prim_asg.perf_review_period_frequency
4796 ,lr_prim_asg.period_of_service_id
4797 ,lr_prim_asg.probation_period
4798 ,lr_prim_asg.probation_unit
4799 ,lr_prim_asg.sal_review_period
4800 ,lr_prim_asg.sal_review_period_frequency
4801 ,lr_prim_asg.set_of_books_id
4802 ,lr_prim_asg.source_type
4803 ,lr_prim_asg.time_normal_finish
4804 ,lr_prim_asg.time_normal_start
4805 ,lr_prim_asg.bargaining_unit_code
4806 ,lr_prim_asg.labour_union_member_flag
4807 ,lr_prim_asg.hourly_salaried_code
4808 ,lr_prim_asg.request_id
4809 ,lr_prim_asg.program_application_id
4810 ,lr_prim_asg.program_id
4811 ,lr_prim_asg.program_update_date
4812 ,lr_prim_asg.ass_attribute_category
4813 ,lr_prim_asg.ass_attribute1
4814 ,lr_prim_asg.ass_attribute2
4815 ,lr_prim_asg.ass_attribute3
4816 ,lr_prim_asg.ass_attribute4
4817 ,lr_prim_asg.ass_attribute5
4818 ,lr_prim_asg.ass_attribute6
4819 ,lr_prim_asg.ass_attribute7
4820 ,lr_prim_asg.ass_attribute8
4821 ,lr_prim_asg.ass_attribute9
4822 ,lr_prim_asg.ass_attribute10
4823 ,lr_prim_asg.ass_attribute11
4824 ,lr_prim_asg.ass_attribute12
4825 ,lr_prim_asg.ass_attribute13
4826 ,lr_prim_asg.ass_attribute14
4827 ,lr_prim_asg.ass_attribute15
4828 ,lr_prim_asg.ass_attribute16
4829 ,lr_prim_asg.ass_attribute17
4830 ,lr_prim_asg.ass_attribute18
4831 ,lr_prim_asg.ass_attribute19
4832 ,lr_prim_asg.ass_attribute20
4833 ,lr_prim_asg.ass_attribute21
4834 ,lr_prim_asg.ass_attribute22
4835 ,lr_prim_asg.ass_attribute23
4836 ,lr_prim_asg.ass_attribute24
4837 ,lr_prim_asg.ass_attribute25
4838 ,lr_prim_asg.ass_attribute26
4839 ,lr_prim_asg.ass_attribute27
4840 ,lr_prim_asg.ass_attribute28
4841 ,lr_prim_asg.ass_attribute29
4842 ,lr_prim_asg.ass_attribute30
4843 ,TRUNC(SYSDATE) -- New Last Update Date
4844 ,-1 -- New Updated By
4845 ,-1 -- New Update Login
4846 ,-1 -- New Created By
4847 ,TRUNC(SYSDATE) -- New Creation Date
4848 ,lr_prim_asg.title
4849 ,1 -- New OVN
4850 ,lr_prim_asg.contract_id
4851 ,lr_prim_asg.cagr_id_flex_num
4852 ,lr_prim_asg.cagr_grade_def_id
4853 ,lr_prim_asg.establishment_id
4854 ,lr_prim_asg.collective_agreement_id
4855 ,lr_prim_asg.notice_period
4856 ,lr_prim_asg.notice_period_uom
4857 ,lr_prim_asg.employee_category
4858 ,lr_prim_asg.work_at_home
4859 ,lr_prim_asg.job_post_source_name
4860 ,lr_prim_asg.period_of_placement_date_start
4861 ,lr_prim_asg.vendor_id
4862 ,lr_prim_asg.vendor_site_id
4863 ,lr_prim_asg.po_header_id
4864 ,lr_prim_asg.po_line_id
4865 ,lr_prim_asg.projected_assignment_end
4866 ,lr_prim_asg.vendor_employee_number
4867 ,lr_prim_asg.vendor_assignment_number
4868 ,lr_prim_asg.assignment_category
4869 ,lr_prim_asg.project_title
4870 ,lr_prim_asg.grade_ladder_pgm_id
4871 ,lr_prim_asg.supervisor_assignment_id
4872 );
4873 --
4874 ELSE -- rehire date between but not inclusive of ESD and EED
4875 --
4876 hr_utility.set_location(l_proc, 70);
4877 --
4878 -- Update EED to rehire date - 1
4879 --
4880 UPDATE per_assignments_f
4881 SET effective_end_date = (p_rehire_date - 1)
4882 ,object_version_number = object_version_number + 1
4883 WHERE assignment_id = lr_prim_asg.assignment_id
4884 AND assignment_type <> 'B' -- 115.59
4885 AND effective_start_date = lr_prim_asg.effective_start_date;
4886 --
4887 hr_utility.set_location(l_proc, 80);
4888 --
4889 -- Insert new record primary flag 'N' and ESD
4890 -- same as rehire date
4891 --
4892 INSERT INTO per_all_assignments_f
4893 (assignment_id
4894 ,effective_start_date
4895 ,effective_end_date
4896 ,business_group_id
4897 ,recruiter_id
4898 ,grade_id
4899 ,position_id
4900 ,job_id
4901 ,assignment_status_type_id
4902 ,payroll_id
4903 ,location_id
4904 ,person_referred_by_id
4905 ,supervisor_id
4906 ,special_ceiling_step_id
4907 ,person_id
4908 ,recruitment_activity_id
4909 ,source_organization_id
4910 ,organization_id
4911 ,people_group_id
4912 ,soft_coding_keyflex_id
4913 ,vacancy_id
4914 ,pay_basis_id
4915 ,assignment_sequence
4916 ,assignment_type
4917 ,primary_flag
4918 ,application_id
4919 ,assignment_number
4920 ,change_reason
4921 ,comment_id
4922 ,date_probation_end
4923 ,default_code_comb_id
4924 ,employment_category
4925 ,frequency
4926 ,internal_address_line
4927 ,manager_flag
4928 ,normal_hours
4929 ,perf_review_period
4930 ,perf_review_period_frequency
4931 ,period_of_service_id
4932 ,probation_period
4933 ,probation_unit
4934 ,sal_review_period
4935 ,sal_review_period_frequency
4936 ,set_of_books_id
4937 ,source_type
4938 ,time_normal_finish
4939 ,time_normal_start
4940 ,bargaining_unit_code
4941 ,labour_union_member_flag
4942 ,hourly_salaried_code
4943 ,request_id
4944 ,program_application_id
4945 ,program_id
4946 ,program_update_date
4947 ,ass_attribute_category
4948 ,ass_attribute1
4949 ,ass_attribute2
4950 ,ass_attribute3
4951 ,ass_attribute4
4952 ,ass_attribute5
4953 ,ass_attribute6
4954 ,ass_attribute7
4955 ,ass_attribute8
4956 ,ass_attribute9
4957 ,ass_attribute10
4958 ,ass_attribute11
4959 ,ass_attribute12
4960 ,ass_attribute13
4961 ,ass_attribute14
4962 ,ass_attribute15
4963 ,ass_attribute16
4964 ,ass_attribute17
4965 ,ass_attribute18
4966 ,ass_attribute19
4967 ,ass_attribute20
4968 ,ass_attribute21
4969 ,ass_attribute22
4970 ,ass_attribute23
4971 ,ass_attribute24
4972 ,ass_attribute25
4973 ,ass_attribute26
4974 ,ass_attribute27
4975 ,ass_attribute28
4976 ,ass_attribute29
4977 ,ass_attribute30
4978 ,last_update_date
4979 ,last_updated_by
4980 ,last_update_login
4981 ,created_by
4982 ,creation_date
4983 ,title
4984 ,object_version_number
4985 ,contract_id
4986 ,cagr_id_flex_num
4987 ,cagr_grade_def_id
4988 ,establishment_id
4989 ,collective_agreement_id
4990 ,notice_period
4991 ,notice_period_uom
4992 ,employee_category
4993 ,work_at_home
4994 ,job_post_source_name
4995 ,period_of_placement_date_start
4996 ,vendor_id
4997 ,vendor_site_id
4998 ,po_header_id
4999 ,po_line_id
5000 ,projected_assignment_end
5001 ,vendor_employee_number
5002 ,vendor_assignment_number
5003 ,assignment_category
5004 ,project_title
5005 ,grade_ladder_pgm_id
5006 ,supervisor_assignment_id
5007 )
5008 VALUES
5009 (lr_prim_asg.assignment_id
5010 ,p_rehire_date -- New ESD
5011 ,lr_prim_asg.effective_end_date
5012 ,lr_prim_asg.business_group_id
5013 ,lr_prim_asg.recruiter_id
5014 ,lr_prim_asg.grade_id
5015 ,lr_prim_asg.position_id
5016 ,lr_prim_asg.job_id
5017 ,lr_prim_asg.assignment_status_type_id
5018 ,lr_prim_asg.payroll_id
5019 ,lr_prim_asg.location_id
5020 ,lr_prim_asg.person_referred_by_id
5021 ,lr_prim_asg.supervisor_id
5022 ,lr_prim_asg.special_ceiling_step_id
5023 ,lr_prim_asg.person_id
5024 ,lr_prim_asg.recruitment_activity_id
5025 ,lr_prim_asg.source_organization_id
5026 ,lr_prim_asg.organization_id
5027 ,lr_prim_asg.people_group_id
5028 ,lr_prim_asg.soft_coding_keyflex_id
5029 ,lr_prim_asg.vacancy_id
5030 ,lr_prim_asg.pay_basis_id
5031 ,lr_prim_asg.assignment_sequence
5032 ,lr_prim_asg.assignment_type
5033 ,'N' -- New Primary Flag
5034 ,lr_prim_asg.application_id
5035 ,lr_prim_asg.assignment_number
5036 ,lr_prim_asg.change_reason
5037 ,lr_prim_asg.comment_id
5038 ,lr_prim_asg.date_probation_end
5039 ,lr_prim_asg.default_code_comb_id
5040 ,lr_prim_asg.employment_category
5041 ,lr_prim_asg.frequency
5042 ,lr_prim_asg.internal_address_line
5043 ,lr_prim_asg.manager_flag
5044 ,lr_prim_asg.normal_hours
5045 ,lr_prim_asg.perf_review_period
5046 ,lr_prim_asg.perf_review_period_frequency
5047 ,lr_prim_asg.period_of_service_id
5048 ,lr_prim_asg.probation_period
5049 ,lr_prim_asg.probation_unit
5050 ,lr_prim_asg.sal_review_period
5051 ,lr_prim_asg.sal_review_period_frequency
5052 ,lr_prim_asg.set_of_books_id
5053 ,lr_prim_asg.source_type
5054 ,lr_prim_asg.time_normal_finish
5055 ,lr_prim_asg.time_normal_start
5056 ,lr_prim_asg.bargaining_unit_code
5057 ,lr_prim_asg.labour_union_member_flag
5058 ,lr_prim_asg.hourly_salaried_code
5059 ,lr_prim_asg.request_id
5060 ,lr_prim_asg.program_application_id
5061 ,lr_prim_asg.program_id
5062 ,lr_prim_asg.program_update_date
5063 ,lr_prim_asg.ass_attribute_category
5064 ,lr_prim_asg.ass_attribute1
5065 ,lr_prim_asg.ass_attribute2
5066 ,lr_prim_asg.ass_attribute3
5067 ,lr_prim_asg.ass_attribute4
5068 ,lr_prim_asg.ass_attribute5
5069 ,lr_prim_asg.ass_attribute6
5070 ,lr_prim_asg.ass_attribute7
5071 ,lr_prim_asg.ass_attribute8
5072 ,lr_prim_asg.ass_attribute9
5073 ,lr_prim_asg.ass_attribute10
5074 ,lr_prim_asg.ass_attribute11
5075 ,lr_prim_asg.ass_attribute12
5076 ,lr_prim_asg.ass_attribute13
5077 ,lr_prim_asg.ass_attribute14
5078 ,lr_prim_asg.ass_attribute15
5079 ,lr_prim_asg.ass_attribute16
5080 ,lr_prim_asg.ass_attribute17
5081 ,lr_prim_asg.ass_attribute18
5082 ,lr_prim_asg.ass_attribute19
5083 ,lr_prim_asg.ass_attribute20
5084 ,lr_prim_asg.ass_attribute21
5085 ,lr_prim_asg.ass_attribute22
5086 ,lr_prim_asg.ass_attribute23
5087 ,lr_prim_asg.ass_attribute24
5088 ,lr_prim_asg.ass_attribute25
5089 ,lr_prim_asg.ass_attribute26
5090 ,lr_prim_asg.ass_attribute27
5091 ,lr_prim_asg.ass_attribute28
5092 ,lr_prim_asg.ass_attribute29
5093 ,lr_prim_asg.ass_attribute30
5094 ,TRUNC(SYSDATE) -- New Last Update Date
5095 ,-1 -- New Updated By
5096 ,-1 -- New Update Login
5097 ,-1 -- New Created By
5098 ,TRUNC(SYSDATE) -- New Creation Date
5099 ,lr_prim_asg.title
5100 ,1 -- New OVN
5101 ,lr_prim_asg.contract_id
5102 ,lr_prim_asg.cagr_id_flex_num
5103 ,lr_prim_asg.cagr_grade_def_id
5104 ,lr_prim_asg.establishment_id
5105 ,lr_prim_asg.collective_agreement_id
5106 ,lr_prim_asg.notice_period
5107 ,lr_prim_asg.notice_period_uom
5108 ,lr_prim_asg.employee_category
5109 ,lr_prim_asg.work_at_home
5110 ,lr_prim_asg.job_post_source_name
5111 ,lr_prim_asg.period_of_placement_date_start
5112 ,lr_prim_asg.vendor_id
5113 ,lr_prim_asg.vendor_site_id
5114 ,lr_prim_asg.po_header_id
5115 ,lr_prim_asg.po_line_id
5116 ,lr_prim_asg.projected_assignment_end
5117 ,lr_prim_asg.vendor_employee_number
5118 ,lr_prim_asg.vendor_assignment_number
5119 ,lr_prim_asg.assignment_category
5120 ,lr_prim_asg.project_title
5121 ,lr_prim_asg.grade_ladder_pgm_id
5122 ,lr_prim_asg.supervisor_assignment_id
5123 );
5124 --
5125 END IF;
5126 --
5127 hr_utility.set_location(l_proc, 90);
5128 --
5129 -- Update assignments where ESD > rehire date setting the
5130 -- primary flag to 'N'
5131 --
5132 UPDATE per_assignments_f
5133 SET primary_flag = 'N'
5134 ,object_version_number = object_version_number + 1
5135 WHERE assignment_id = lr_prim_asg.assignment_id
5136 AND effective_start_date > p_rehire_date;
5137 --
5138 END IF; -- check p_cancel
5139 --
5140 hr_utility.set_location('Leaving:'|| l_proc, 9998);
5141 --
5142 EXCEPTION
5143 --
5144 WHEN OTHERS THEN
5145 --
5146 -- An unexpected error has occurred
5147 -- No OUT parameters need to be set
5148 -- No cursors need to be closed
5149 --
5150 hr_utility.set_location('Leaving:'|| l_proc, 9999);
5151 ROLLBACK TO update_rehire_primary_asgs;
5152 RAISE;
5153 --
5154 END update_rehire_primary_asgs;
5155 --
5156 -- ----------------------------------------------------------------------------
5157 -- |-----------------------< MANAGE_REHIRE_PRIMARY_ASGS >---------------------|
5158 -- ----------------------------------------------------------------------------
5159 -- {Start Of Comments}
5160 --
5161 -- Description: This procedure manages the switching of the primary but
5162 -- terminated terminated assignment of an employee on the old
5163 -- period of service to a secondary assignment during rehire
5164 -- and swithcing it back should the rehire be cancelled.
5165 --
5166 -- Prerequisites: None
5167 --
5168 -- In Parameters:
5169 -- Name Reqd Type Description
5170 -- P_PERSON_ID Yes NUMBER Person Identifier
5171 -- P_REHIRE_DATE Yes DATE Employee Re-hire date
5172 -- P_CANCEL Yes VARCHAR2 Flags whether rehire or cancel
5173 -- rehire invocation.
5174 --
5175 -- Post Success:
5176 -- The TERM_ASSIGN assignment record will be flipped from primary to
5177 -- secondary if rehire before FPD and flipped back to primary if the
5178 -- rehire is being cancelled.
5179 --
5180 -- Name Type Description
5181 -- - - -
5182 -- Post Failure:
5183 -- An exception will be raised depending on the nature of failure.
5184 --
5185 -- Access Status:
5186 -- Internal Development Use Only.
5187 --
5188 -- {End Of Comments}
5189 --
5190 procedure manage_rehire_primary_asgs
5191 (p_person_id in number
5192 ,p_rehire_date in date
5193 ,p_cancel in varchar2
5194 ) IS
5195 --
5196 l_proc VARCHAR2(80) := g_package||'manage_rehire_primary_asgs';
5197 --
5198 -- Cursor to get previous PDS that is the latest PDS before
5199 -- rehire date
5200 --
5201 CURSOR csr_prev_pds IS
5202 SELECT pds.period_of_service_id
5203 ,pds.date_start
5204 ,pds.actual_termination_date
5205 ,pds.last_standard_process_date
5206 ,pds.final_process_date
5207 FROM per_periods_of_service pds
5208 WHERE pds.person_id = p_person_id
5209 AND pds.date_start < p_rehire_date
5210 ORDER BY pds.date_start DESC;
5211 --
5212 lr_prev_pds csr_prev_pds%ROWTYPE;
5213 --
5214 -- Cursor to get primary assignment for given PDS
5215 -- on given date
5216 --
5217 CURSOR csr_prim_asg(p_period_of_service_id IN NUMBER
5218 ,p_person_id IN NUMBER
5219 ,p_effective_date IN DATE
5220 ) IS
5221 SELECT asg.assignment_id
5222 ,asg.effective_start_date
5223 ,asg.effective_end_date
5224 FROM per_assignments_f asg
5225 WHERE asg.period_of_service_id = csr_prim_asg.p_period_of_service_id
5226 AND asg.person_id = csr_prim_asg.p_person_id
5227 AND csr_prim_asg.p_effective_date BETWEEN asg.effective_start_date
5228 AND asg.effective_end_date
5229 AND asg.primary_flag = 'Y';
5230 --
5231 lr_prim_asg csr_prim_asg%ROWTYPE;
5232 --
5233 e_nothing_to_manage EXCEPTION;
5234 --
5235 BEGIN
5236 --
5237 hr_utility.set_location('Entering:'|| l_proc, 5);
5238 --
5239 -- Issue a savepoint.
5240 --
5241 SAVEPOINT manage_rehire_primary_asgs;
5242 --
5243 hr_utility.set_location(l_proc, 10);
5244 --
5245 -- Get the previous PDS that is the latest PDS before
5246 -- rehire date
5247 --
5248 OPEN csr_prev_pds;
5249 FETCH csr_prev_pds INTO lr_prev_pds;
5250 IF csr_prev_pds%NOTFOUND THEN
5251 CLOSE csr_prev_pds;
5252 --
5253 -- This is not a rehire
5254 --
5255 RAISE e_nothing_to_manage;
5256 END IF;
5257 CLOSE csr_prev_pds;
5258 --
5259 hr_utility.set_location(l_proc, 20);
5260 --
5261 -- Check if overlap condition
5262 --
5263 IF lr_prev_pds.final_process_date < p_rehire_date THEN
5264 --
5265 -- This is not an overlap condition
5266 --
5267 RAISE e_nothing_to_manage;
5268 END IF;
5269 --
5270 hr_utility.set_location(l_proc, 30);
5271 --
5272 -- Get the associated primary assignment on rehire date
5273 --
5274 OPEN csr_prim_asg(lr_prev_pds.period_of_service_id
5275 ,p_person_id
5276 ,p_rehire_date
5277 );
5278 FETCH csr_prim_asg INTO lr_prim_asg;
5279 IF csr_prim_asg%NOTFOUND THEN
5280 CLOSE csr_prim_asg;
5281 --
5282 hr_utility.set_location(l_proc, 35);
5283 --
5284 -- Get the associated primary assignment on actual term date
5285 --
5286 OPEN csr_prim_asg(lr_prev_pds.period_of_service_id
5287 ,p_person_id
5288 ,lr_prev_pds.actual_termination_date
5289 );
5290 FETCH csr_prim_asg INTO lr_prim_asg;
5291 IF csr_prim_asg%NOTFOUND THEN
5292 CLOSE csr_prim_asg;
5293 --
5294 RAISE e_nothing_to_manage;
5295 END IF;
5296 END IF;
5297 CLOSE csr_prim_asg;
5298 --
5299 hr_utility.set_location(l_proc, 40);
5300 --
5301 -- if p_cancel = 'Y', this is a cancel rehire scenario
5302 -- find primary assignment on rehire date - 1
5303 -- DT CORRECTION to ASG records with ESD >= rehire date to primary
5304 -- else if p_cancel = 'N', this is a rehire scenario
5305 -- DT UPDATE this ASG to secondary on rehire date
5306 -- DT CORRECTION ASG records with ESD > rehire date to secondary
5307 --
5308 update_rehire_primary_asgs
5309 (p_person_id => p_person_id
5310 ,p_rehire_date => p_rehire_date
5311 ,p_cancel => p_cancel
5312 );
5313 --
5314 hr_utility.set_location('Leaving:'|| l_proc, 50);
5315 --
5316 EXCEPTION
5317 --
5318 WHEN e_nothing_to_manage THEN
5319 --
5320 hr_utility.set_location('Leaving:'|| l_proc, 60);
5321 ROLLBACK TO manage_rehire_primary_asgs;
5322 --
5323 WHEN OTHERS THEN
5324 --
5325 -- An unexpected error has occurred
5326 -- No OUT parameters need to be set
5327 -- No cursors need to be closed
5328 --
5329 hr_utility.set_location('Leaving:'|| l_proc, 70);
5330 ROLLBACK TO manage_rehire_primary_asgs;
5331 RAISE;
5332 --
5333 END manage_rehire_primary_asgs;
5334 --
5335 -- 115.57 (END)
5336 --
5337 --
5338 end hr_employee_api;