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