[Home] [Help]
PACKAGE BODY: APPS.HR_APPLICANT_API
Source
1 Package Body hr_applicant_api as
2 /* $Header: peappapi.pkb 120.18.12010000.7 2008/11/24 10:36:50 pchowdav 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 -- Declare cursors and local variables
1249 --
1250 l_proc varchar2(72) := g_package||'create_us_applicant';
1251 l_legislation_code varchar2(30);
1252 --
1253 cursor csr_bg is
1254 select legislation_code
1255 from per_business_groups pbg
1256 where pbg.business_group_id = p_business_group_id;
1257 --
1258 begin
1259 hr_utility.set_location('Entering:'|| l_proc, 5);
1260 --
1261 -- Validation in addition to Table Handlers
1262 --
1263 -- Check that the specified business group is valid.
1264 --
1265 open csr_bg;
1266 fetch csr_bg
1267 into l_legislation_code;
1268 if csr_bg%notfound then
1269 close csr_bg;
1270 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1271 hr_utility.raise_error;
1272 end if;
1273 close csr_bg;
1274 --
1275 -- Check that the legislation of the specified business group is 'US'.
1276 --
1277 if l_legislation_code <> 'US' then
1278 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1279 hr_utility.set_message_token('LEG_CODE','US');
1280 hr_utility.raise_error;
1281 end if;
1282
1283 hr_utility.set_location(l_proc, 6);
1284 --
1285 -- Call the person business process
1286 --
1287 hr_applicant_api.create_applicant
1288 (p_validate => p_validate
1289 ,p_date_received => p_date_received
1290 ,p_business_group_id => p_business_group_id
1291 ,p_last_name => p_last_name
1292 ,p_sex => p_sex
1293 ,p_person_type_id => p_person_type_id
1294 ,p_per_comments => p_comments
1295 ,p_date_employee_data_verified => p_date_employee_data_verified
1296 ,p_date_of_birth => p_date_of_birth
1297 ,p_email_address => p_email_address
1298 ,p_applicant_number => p_applicant_number
1299 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1300 ,p_first_name => p_first_name
1301 ,p_known_as => p_known_as
1302 ,p_marital_status => p_marital_status
1303 ,p_middle_names => p_middle_names
1304 ,p_nationality => p_nationality
1305 ,p_national_identifier => p_ss_number
1306 ,p_previous_last_name => p_previous_last_name
1307 ,p_registered_disabled_flag => p_registered_disabled_flag
1308 ,p_title => p_title
1309 ,p_work_telephone => p_work_telephone
1310 ,p_attribute_category => p_attribute_category
1311 ,p_attribute1 => p_attribute1
1312 ,p_attribute2 => p_attribute2
1313 ,p_attribute3 => p_attribute3
1314 ,p_attribute4 => p_attribute4
1315 ,p_attribute5 => p_attribute5
1316 ,p_attribute6 => p_attribute6
1317 ,p_attribute7 => p_attribute7
1318 ,p_attribute8 => p_attribute8
1319 ,p_attribute9 => p_attribute9
1320 ,p_attribute10 => p_attribute10
1321 ,p_attribute11 => p_attribute11
1322 ,p_attribute12 => p_attribute12
1323 ,p_attribute13 => p_attribute13
1324 ,p_attribute14 => p_attribute14
1325 ,p_attribute15 => p_attribute15
1326 ,p_attribute16 => p_attribute16
1327 ,p_attribute17 => p_attribute17
1328 ,p_attribute18 => p_attribute18
1329 ,p_attribute19 => p_attribute19
1330 ,p_attribute20 => p_attribute20
1331 ,p_attribute21 => p_attribute21
1332 ,p_attribute22 => p_attribute22
1333 ,p_attribute23 => p_attribute23
1334 ,p_attribute24 => p_attribute24
1335 ,p_attribute25 => p_attribute25
1336 ,p_attribute26 => p_attribute26
1337 ,p_attribute27 => p_attribute27
1338 ,p_attribute28 => p_attribute28
1339 ,p_attribute29 => p_attribute29
1340 ,p_attribute30 => p_attribute30
1341 ,p_per_information_category => 'US'
1342 ,p_per_information1 => p_ethnic_origin
1343 ,p_per_information2 => p_i_9
1344 ,p_per_information3 => p_i_9_expiration_date
1345 -- ,p_per_information4 => p_visa_type
1346 ,p_per_information5 => p_veteran_status
1347 ,p_per_information7 => p_new_hire
1348 ,p_per_information8 => p_exception_reason
1349 ,p_per_information9 => p_child_support_obligation
1350 ,p_per_information10 => p_opted_for_medicare_flag
1351 ,p_background_check_status => p_background_check_status
1352 ,p_background_date_check => p_background_date_check
1353 ,p_correspondence_language => p_correspondence_language
1354 ,p_fte_capacity => p_fte_capacity
1355 ,p_hold_applicant_date_until => p_hold_applicant_date_until
1356 ,p_honors => p_honors
1357 ,p_mailstop => p_mailstop
1358 ,p_office_number => p_office_number
1359 ,p_on_military_service => p_on_military_service
1360 ,p_pre_name_adjunct => p_pre_name_adjunct
1361 ,p_projected_start_date => p_projected_start_date
1362 ,p_resume_exists => p_resume_exists
1363 ,p_resume_last_updated => p_resume_last_updated
1364 ,p_student_status => p_student_status
1365 ,p_work_schedule => p_work_schedule
1366 ,p_suffix => p_suffix
1367 ,p_date_of_death => p_date_of_death
1368 ,p_benefit_group_id => p_benefit_group_id
1369 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1370 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1371 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1372 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1373 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1374 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1375 ,p_original_date_of_hire => p_original_date_of_hire
1376 ,p_town_of_birth => p_town_of_birth
1377 ,p_region_of_birth => p_region_of_birth
1378 ,p_country_of_birth => p_country_of_birth
1379 ,p_global_person_id => p_global_person_id
1380 ,p_party_id => p_party_id
1381 --
1382 ,p_person_id => p_person_id
1383 ,p_assignment_id => p_assignment_id
1384 ,p_application_id => p_application_id
1385 ,p_per_object_version_number => p_per_object_version_number
1386 ,p_asg_object_version_number => p_asg_object_version_number
1387 ,p_apl_object_version_number => p_apl_object_version_number
1388 ,p_per_effective_start_date => p_per_effective_start_date
1389 ,p_per_effective_end_date => p_per_effective_end_date
1390 ,p_full_name => p_full_name
1391 ,p_per_comment_id => p_per_comment_id
1392 ,p_assignment_sequence => p_assignment_sequence
1393 ,p_name_combination_warning => p_name_combination_warning
1394 ,p_orig_hire_warning => p_orig_hire_warning
1395 );
1396 --
1397 hr_utility.set_location(' Leaving:'||l_proc, 7);
1398 end create_us_applicant;
1399 --
1400 -- ---------------------------------------------------------------------------
1401 -- |-----------------------------< hire_applicant >---------------------------|
1402 -- ---------------------------------------------------------------------------
1403 -- This is the overloaded version of hire_applicant that matches the
1404 -- base release
1405 procedure hire_applicant
1406 (p_validate in boolean , --default false,
1407 p_hire_date in date,
1408 p_person_id in per_all_people_f.person_id%TYPE,
1409 p_assignment_id in number, --default null,
1410 p_person_type_id in number, --default null,
1411 p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1412 p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1413 p_per_effective_start_date out nocopy date,
1414 p_per_effective_end_date out nocopy date,
1415 p_unaccepted_asg_del_warning out nocopy boolean,
1416 p_assign_payroll_warning out nocopy boolean,
1417 p_original_date_of_hire in date, --default null
1418 p_migrate in boolean default true,
1419 p_source in boolean default false
1420 )
1421 is
1422 --
1423 -- Declare cursors and local variables
1424 --
1425 l_proc varchar2(72) := g_package||'hire__applicant';
1426 --
1427 l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1428 l_employee_number per_all_people_f.employee_number%TYPE;
1429 l_per_effective_start_date date;
1430 l_per_effective_end_date date;
1431 l_unaccepted_asg_del_warning boolean;
1432 l_assign_payroll_warning boolean;
1433 l_oversubscribed_vacancy_id number;
1434 -- l_original_date_of_hire date;
1435 --
1436 begin
1437 --
1438 hr_utility.set_location('Entering:'|| l_proc, 10);
1439 --
1440 l_per_object_version_number:=p_per_object_version_number;
1441 l_employee_number:=p_employee_number;
1442 --
1443 hr_applicant_api.hire_applicant
1444 (p_validate => p_validate
1445 ,p_hire_date => p_hire_date
1446 ,p_person_id => p_person_id
1447 ,p_assignment_id => p_assignment_id
1448 ,p_person_type_id => p_person_type_id
1449 ,p_per_object_version_number => l_per_object_version_number
1450 ,p_employee_number => l_employee_number
1451 ,p_per_effective_start_date => l_per_effective_start_date
1452 ,p_per_effective_end_date => l_per_effective_end_date
1453 ,p_unaccepted_asg_del_warning => l_unaccepted_asg_del_warning
1454 ,p_assign_payroll_warning => l_assign_payroll_warning
1455 ,p_oversubscribed_vacancy_id => l_oversubscribed_vacancy_id
1456 ,p_original_date_of_hire => p_original_date_of_hire
1457 ,p_migrate => p_migrate
1458 ,p_source => p_source
1459 );
1460 --
1461 p_employee_number := l_employee_number;
1462 p_per_object_version_number := l_per_object_version_number;
1463 p_per_effective_start_date := l_per_effective_start_date;
1464 p_per_effective_end_date := l_per_effective_end_date;
1465 p_unaccepted_asg_del_warning := l_unaccepted_asg_del_warning;
1466 p_assign_payroll_warning := l_assign_payroll_warning;
1467 --
1468 hr_utility.set_location('Leaving:'|| l_proc, 20);
1469 --
1470 end hire_applicant;
1471 --
1472 -- ---------------------------------------------------------------------------
1473 -- |-----------------------------< hire_applicant >---------------------------|
1474 -- ---------------------------------------------------------------------------
1475 --
1476 procedure hire_applicant
1477 (p_validate in boolean, --default false,
1478 p_hire_date in date,
1479 p_person_id in per_all_people_f.person_id%TYPE,
1480 p_assignment_id in number, --default null,
1481 p_person_type_id in number, --default null,
1482 p_national_identifier in per_all_people_f.national_identifier%type, --default hr_api.g_varchar2,
1483 p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1484 p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1485 p_per_effective_start_date out nocopy date,
1486 p_per_effective_end_date out nocopy date,
1487 p_unaccepted_asg_del_warning out nocopy boolean,
1488 p_assign_payroll_warning out nocopy boolean,
1489 p_oversubscribed_vacancy_id out nocopy number,
1490 p_original_date_of_hire in date, --default null ,
1491 p_migrate in boolean default true,
1492 p_source in boolean default false
1493 )
1494 is
1495 --
1496 -- Declare cursors and local variables
1497 --
1498 l_proc varchar2(72) := g_package||'hire_applicant';
1499 --
1500 l_exists varchar2(1);
1501 l_count number;
1502 l_multi_flag boolean;
1503 l_chk_system_status per_assignment_status_types.per_system_status%TYPE;
1504 l_chk_person_id per_all_people_f.person_id%TYPE;
1505 --
1506 l_person_type_id number := p_person_type_id;
1507 l_person_type_id1 number;
1508 l_unaccepted_asg_del_warning boolean;
1509 --
1510 l_system_person_type per_person_types.system_person_type%TYPE;
1511 l_business_group_id per_all_people_f.business_group_id%TYPE;
1512 l_legislation_code per_business_groups.legislation_code%TYPE;
1513 l_applicant_number per_all_people_f.applicant_number%TYPE;
1514 l_application_id per_applications.application_id%TYPE;
1515 l_apl_object_version_number per_applications.application_id%TYPE;
1516 --
1517 l_hire_date date;
1518 l_original_date_of_hire date;
1519 --
1520 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1521 l_assignment_id per_assignments_f.assignment_id%TYPE;
1522 l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1523 --
1524 l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525 l_employee_number per_all_people_f.employee_number%TYPE;
1526 l_npw_number per_all_people_f.npw_number%TYPE;
1527 l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528 l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1529 l_comment_id per_assignments_f.comment_id%TYPE;
1530 l_current_applicant_flag varchar2(1);
1531 l_current_emp_or_apl_flag varchar2(1);
1532 l_current_employee_flag varchar2(1);
1533 l_full_name per_all_people_f.full_name%TYPE;
1534 l_name_combination_warning boolean;
1535 l_assign_payroll_warning boolean;
1536 l_orig_hire_warning boolean;
1537 l_hourly_salaried_warning boolean;
1538 --
1539 l_assignment_status_id number;
1540 L_ASG_STATUS_OVN number;
1541 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
1542 l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1543 --
1544 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1545 --
1546 l_primary_flag per_assignments_f.primary_flag%TYPE;
1547 --
1548 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1549 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1550 l_validation_start_date date;
1551 l_validation_end_date date;
1552 l_payroll_id_updated boolean;
1553 l_other_manager_warning boolean;
1554 l_no_managers_warning boolean;
1555 l_org_now_no_manager_warning boolean;
1556 l_oversubscribed_vacancy_id number;
1557 l_vacancy_id per_all_assignments_f.vacancy_id%type;
1558 l_dummy number;
1559 --
1560 --bug no 5105005
1561 l_datetrack_mode varchar2(10):='UPDATE';
1562 cursor csr_pps_ended_ystrdy
1563 is
1564 select final_process_date
1565 from per_periods_of_service pps
1566 where pps.person_id = p_person_id
1567 and pps.actual_termination_date = p_hire_date-1;
1568 --bug no 5105005
1569
1570 lv_per_object_version_number per_all_people_f.object_version_number%TYPE := p_per_object_version_number ;
1571 lv_employee_number per_all_people_f.employee_number%TYPE := p_employee_number ;
1572 --
1573 --
1574 -- Bug# 2273304 Start Here
1575 --
1576 l_date_of_birth date; --2273304
1577 l_age number(3); --2273304
1578 l_minimum_age number(3); --2273304
1579 l_maximum_age number(3); --2273304
1580 --
1581 cursor csr_date_of_birth is
1582 select date_of_birth
1583 from per_all_people_f ppf
1584 where ppf.person_id = p_person_id
1585 and l_business_group_id = ppf.business_group_id
1586 and p_hire_date between effective_start_date
1587 and nvl(effective_end_date,p_hire_date);
1588
1589 cursor csr_bg_age_range is
1590 select hoi1.org_information12, hoi1.org_information13
1591 from hr_organization_information hoi1
1592 where l_business_group_id +0 = hoi1.organization_id
1593 and hoi1.org_information_context = 'Business Group Information';
1594
1595 --
1596 -- Bug# 2273304 End Here
1597 --
1598 cursor csr_future_asg_changes is
1599 select 'x'
1600 from per_assignments_f asg
1601 where asg.person_id = p_person_id
1602 --bug no 5105005
1603 and asg.effective_start_date > p_hire_date;
1604 --bug no 5105005
1605 --
1606 cursor csr_get_devived_details is
1607 select ppt.system_person_type,
1608 per.business_group_id,
1609 bus.legislation_code,
1610 per.applicant_number,
1611 pap.application_id,
1612 pap.object_version_number,
1613 per.npw_number,
1614 per.original_date_of_hire -- #2978566
1615 from per_all_people_f per,
1616 per_business_groups bus,
1617 per_person_types ppt,
1618 per_applications pap
1619 where per.person_type_id = ppt.person_type_id
1620 and per.business_group_id = bus.business_group_id
1621 and per.person_id = pap.person_id
1622 and per.person_id = p_person_id
1623 and l_hire_date between per.effective_start_date
1624 and per.effective_end_date
1625 and l_hire_date between pap.date_received
1626 and nvl(pap.date_end,hr_api.g_eot);
1627 --
1628 cursor csr_chk_asg_status is
1629 select count(asg.assignment_id)
1630 from per_assignments_f asg,
1631 per_assignment_status_types pas
1632 where asg.assignment_status_type_id = pas.assignment_status_type_id
1633 and asg.person_id = p_person_id
1634 and pas.per_system_status = 'ACCEPTED'
1635 and l_hire_date between asg.effective_start_date
1636 and asg.effective_end_date;
1637 --
1638 cursor csr_chk_assignment_id is
1639 select per.person_id,
1640 pas.per_system_status
1641 from per_all_people_f per,
1642 per_assignments_f asg,
1643 per_assignment_status_types pas
1644 where asg.assignment_status_type_id = pas.assignment_status_type_id
1645 and per.person_id = asg.person_id
1646 and l_hire_date between per.effective_start_date
1647 and per.effective_end_date
1648 and asg.assignment_id = p_assignment_id
1649 and l_hire_date between asg.effective_start_date
1650 and asg.effective_end_date;
1651 --
1652 cursor csr_get_un_accepted is
1653 select asg.assignment_id,
1654 asg.object_version_number
1655 from per_assignments_f asg,
1656 per_assignment_status_types pas
1657 where asg.assignment_status_type_id = pas.assignment_status_type_id
1658 and asg.person_id = p_person_id
1659 and l_hire_date between asg.effective_start_date
1660 and asg.effective_end_date
1661 and pas.per_system_status <> 'ACCEPTED'
1662 and asg.assignment_type = 'A' --Fix for bug 2881076
1663 order by asg.assignment_id;
1664 --
1665 cursor csr_get_accepted is
1666 select asg.assignment_id,
1667 asg.object_version_number,
1668 asg.vacancy_id
1669 from per_assignments_f asg,
1670 per_assignment_status_types pas
1671 where asg.assignment_status_type_id = pas.assignment_status_type_id
1672 and asg.person_id = p_person_id
1673 and l_hire_date between asg.effective_start_date
1674 and asg.effective_end_date
1675 and pas.per_system_status = 'ACCEPTED'
1676 and asg.assignment_type = 'A' --changed for bug 6501961
1677 order by asg.assignment_id;
1678 --
1679 -- added for the bug 4681211
1680 cursor csr_get_accepted_pmry is
1681 select asg.assignment_id,
1682 asg.object_version_number,
1683 asg.vacancy_id
1684 from per_assignments_f asg,
1685 per_assignment_status_types pas
1686 where asg.assignment_status_type_id = pas.assignment_status_type_id
1687 and asg.person_id = p_person_id
1688 and l_hire_date between asg.effective_start_date
1689 and asg.effective_end_date
1690 and pas.per_system_status = 'ACCEPTED'
1691 and asg.assignment_id=p_assignment_id;
1692 --
1693 cursor csr_get_accepted_non is
1694 select asg.assignment_id,
1695 asg.object_version_number,
1696 asg.vacancy_id
1697 from per_assignments_f asg,
1698 per_assignment_status_types pas
1699 where asg.assignment_status_type_id = pas.assignment_status_type_id
1700 and asg.person_id = p_person_id
1701 and l_hire_date between asg.effective_start_date
1702 and asg.effective_end_date
1703 and pas.per_system_status = 'ACCEPTED'
1704 and asg.assignment_id <> p_assignment_id
1705 and asg.assignment_type = 'A' --changed for bug 6501961
1706 order by asg.assignment_id;
1707 -- end of bug 4681211
1708 --
1709 cursor csr_vacs(p_vacancy_id number) is
1710 select 1
1711 from per_all_vacancies vac
1712 where vac.vacancy_id=p_vacancy_id
1713 and vac.number_of_openings <
1714 (select count(distinct assignment_id)
1715 from per_all_assignments_f asg
1716 where asg.vacancy_id=p_vacancy_id
1717 and asg.assignment_type='E');
1718 --
1719 --start for i-rec enhancement ww bug # 2675202
1720 -- Cursor,for the applicant who has an address which has a party_id on it,
1721 -- but not a person_id.
1722 --
1723 cursor csr_add(p_party_id number) is
1724 select address_id, object_version_number
1725 from per_addresses
1726 where party_id=p_party_id
1727 and person_id is null;
1728 --
1729 -- Cursor for the applicant who has phone numbers which have a party_id on it,
1730 -- but not a person_id.
1731 --
1732 cursor csr_phn(p_party_id number) is
1733 select phone_id, object_version_number
1734 from per_phones
1735 where party_id=p_party_id
1736 and parent_id is null;
1737 --
1738 -- Cursor for the applicant who has previous employers which have a party_id on it,
1739 -- but not a person_id.
1740 --
1741 cursor csr_pem(p_party_id number) is
1742 select previous_employer_id, object_version_number
1743 from per_previous_employers
1744 where party_id=p_party_id
1745 and person_id is null;
1746 --
1747 -- Cursor for the applicant who has qualifications which have a party_id on it,
1748 -- but not a person_id.
1749 --
1750 cursor csr_qua(p_party_id number) is
1751 select qualification_id, object_version_number
1752 from per_qualifications
1753 where party_id=p_party_id
1754 and person_id is null;
1755 --
1756 -- Cursor for the applicant who has establishment attendances which have a
1757 -- party_id on it, but not a person_id.
1758 --
1759 cursor csr_esa(p_party_id number) is
1760 select attendance_id, object_version_number
1761 from per_establishment_attendances
1762 where party_id=p_party_id
1763 and person_id is null;
1764 --
1765 --
1766
1767 l_add_ovn per_addresses.OBJECT_VERSION_NUMBER%type;
1768 l_phn_ovn per_phones.OBJECT_VERSION_NUMBER%type;
1769 l_parent_table per_phones.PARENT_TABLE%type;
1770 l_pem_ovn per_previous_employers.OBJECT_VERSION_NUMBER%type;
1771 l_qua_ovn per_qualifications.OBJECT_VERSION_NUMBER%type;
1772 l_esa_ovn per_establishment_attendances.OBJECT_VERSION_NUMBER%type;
1773 --
1774 --end for i-rec enhancement ww bug # 2675202
1775 --
1776 --
1777 -- Fix for bug 2881076. Check if there are any old pps with either FPD null
1778 -- or FPD later than new hire date.
1779 cursor csr_pps_not_ended
1780 is
1781 select final_process_date
1782 from per_periods_of_service pps
1783 where pps.person_id = p_person_id
1784 and pps.actual_termination_date is not null
1785 and pps.date_start < p_hire_date
1786 and nvl(pps.final_process_date,p_hire_date) >= p_hire_date;
1787 --
1788 l_pps date;
1789 -- Fix for bug 288076 end.
1790 --
1791
1792 -- start of bug3572499
1793 l_new_hire_code varchar2(30);
1794 --
1795 cursor csr_get_legislation_code is
1796 select legislation_code
1797 from per_business_groups_perf pbg
1798 ,per_all_people_f ppf
1799 where ppf.person_id = p_person_id
1800 and pbg.business_group_id+0 = ppf.business_group_id;
1801
1802 cursor csr_get_new_hire_code is
1803 SELECT per_information7
1804 FROM per_all_people_f
1805 WHERE person_id = p_person_id
1806 AND effective_start_date =
1807 (select max(effective_start_date)
1808 from per_all_people_f
1809 where person_id = p_person_id
1810 );
1811 -- end of bug3572499
1812
1813 -- Fix For Bug 5749371 Starts
1814
1815 cursor csr_existing_SCL (crs_asg_id number) is
1816 select soft_coding_keyflex_id,payroll_id
1817 from per_all_assignments_f asg
1818 where asg.assignment_id = crs_asg_id
1819 -- and asg.primary_flag = 'Y'
1820 and trunc(sysdate) between asg.effective_start_date
1821 and asg.effective_end_date;
1822 --and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
1823
1824 cursor get_scl is
1825 select soft_coding_keyflex_id
1826 from hr_soft_coding_keyflex
1827 where rownum=1;
1828
1829 l_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type;
1830 l_payroll_id per_all_assignments_f.payroll_id%type;
1831 l_dummy_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type;
1832
1833 -- Fix For Bug 5749371 Ends
1834
1835
1836
1837 -- Bug 2833630
1838 PROCEDURE update_salary_proposal(p_assignment_id number
1839 , p_effective_date date) IS
1840
1841 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
1842 l_pyp_object_version_number per_pay_proposals.object_version_number%TYPE;
1843 l_change_date per_pay_proposals.change_date%TYPE;
1844 l_proposed_salary per_pay_proposals.PROPOSED_SALARY_N%TYPE;
1845 l_approved_flag varchar2(1) := 'N'; ---- Changed from Y to N for ER: 6136609
1846 l_inv_next_sal_date_warning boolean;
1847 l_proposed_salary_warning boolean;
1848 l_approved_warning boolean;
1849 l_payroll_warning boolean;
1850
1851 ---- Fix For ER: 6136609 Starts ----
1852
1853 l_autoApprove varchar2(1);
1854
1855 ---- Fix For ER: 6136609 Ends ----
1856
1857 cursor csr_payproposal is
1858 select pay_proposal_id, object_version_number, change_date
1859 ,PROPOSED_SALARY_N
1860 from per_pay_proposals
1861 where assignment_id = p_assignment_id
1862 order by change_date DESC;
1863 BEGIN
1864 open csr_payproposal;
1865 fetch csr_payproposal into l_pay_proposal_id, l_pyp_object_version_number
1866 ,l_change_date, l_proposed_salary;
1867 if csr_payproposal%found and l_change_date < p_effective_date then
1868
1869 ---- Fix For ER: 6136609 Starts ----
1870
1871 l_autoApprove:=fnd_profile.value('HR_AUTO_APPROVE_FIRST_PROPOSAL');
1872 if(l_autoApprove is null or l_autoApprove ='Y') then
1873 hr_utility.set_location(l_proc, 32);
1874 l_approved_flag:='Y';
1875 end if;
1876
1877 ---- Fix For ER: 6136609 Ends ----
1878
1879 hr_maintain_proposal_api.cre_or_upd_salary_proposal
1880 (p_pay_proposal_id => l_pay_proposal_id
1881 ,p_object_version_number => l_pyp_object_version_number
1882 ,p_change_date => p_effective_date
1883 ,p_approved => l_approved_flag
1884 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1885 ,p_proposed_salary_warning => l_proposed_salary_warning
1886 ,p_approved_warning => l_approved_warning
1887 ,p_payroll_warning => l_payroll_warning
1888 );
1889 end if;
1890 close csr_payproposal;
1891 END update_salary_proposal;
1892 -- End bug 2833630
1893 begin
1894 hr_utility.set_location('Entering:'|| l_proc, 10);
1895 --
1896 hr_api.mandatory_arg_error
1897 (p_api_name => l_proc
1898 ,p_argument => 'person_id'
1899 ,p_argument_value => p_person_id
1900 );
1901 --
1902 hr_api.mandatory_arg_error
1903 (p_api_name => l_proc
1904 ,p_argument => 'hire_date'
1905 ,p_argument_value => p_hire_date
1906 );
1907 --
1908 -- Issue a savepoint.
1909 --
1910 --bug no 5105005
1911 open csr_pps_ended_ystrdy;
1912 fetch csr_pps_ended_ystrdy into l_pps;
1913 --
1914 if csr_pps_ended_ystrdy%found then
1915 --
1916 hr_utility.set_location(l_proc,11);
1917 l_datetrack_mode:='CORRECTION';
1918 end if;
1919 close csr_pps_ended_ystrdy;
1920 --bug no 5105005
1921 savepoint hire_applicant;
1922 --
1923 hr_utility.set_location(l_proc, 80);
1924 --
1925 -- Process Logic
1926 --
1927 l_per_object_version_number := p_per_object_version_number;
1928 l_employee_number := p_employee_number;
1929 -- Truncate the time portion from all date parameters
1930 -- which are passed in.
1931 --
1932 l_hire_date := trunc(p_hire_date);
1933
1934 -- #2978566
1935 --
1936 -- Get the derived details for the person DT instance
1937 --
1938 hr_utility.set_location(l_proc,40);
1939
1940 open csr_get_devived_details;
1941 fetch csr_get_devived_details
1942 into l_system_person_type,
1943 l_business_group_id,
1944 l_legislation_code,
1945 l_applicant_number,
1946 l_application_id,
1947 l_apl_object_version_number,
1948 l_npw_number,
1949 l_original_date_of_hire;
1950 if csr_get_devived_details%NOTFOUND
1951 then
1952 --
1953 hr_utility.set_location(l_proc,50);
1954 --
1955 close csr_get_devived_details;
1956 --
1957 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
1958 hr_utility.raise_error;
1959 --
1960 end if;
1961 close csr_get_devived_details;
1962 --
1963 -- #2978566
1964 -- Set the original date of hire
1965 --
1966 if p_original_date_of_hire is not null then
1967 l_original_date_of_hire := trunc(p_original_date_of_hire);
1968 elsif l_original_date_of_hire is null then
1969 l_original_date_of_hire := l_hire_date;
1970 end if;
1971
1972 --
1973 -- Call Before Process User Hook for hire_applicant
1974 --
1975
1976 begin
1977 hr_applicant_bk2.hire_applicant_b
1978 (
1979 p_hire_date => l_hire_date,
1980 p_person_id => p_person_id,
1981 p_assignment_id => p_assignment_id,
1982 p_person_type_id => p_person_type_id,
1983 p_national_identifier => p_national_identifier,
1984 p_per_object_version_number => p_per_object_version_number,
1985 p_employee_number => p_employee_number,
1986 p_original_date_of_hire => l_original_date_of_hire
1987 );
1988 exception
1989 when hr_api.cannot_find_prog_unit then
1990 hr_api.cannot_find_prog_unit_error
1991 (p_module_name => 'HIRE_APPLICANT'
1992 ,p_hook_type => 'BP'
1993 );
1994 --
1995 -- End of the before hook for hire_applicant
1996 --
1997 end;
1998
1999 --
2000 -- Check that there are not any future changes to the assignment
2001 --
2002
2003 open csr_future_asg_changes;
2004 fetch csr_future_asg_changes into l_exists;
2005 --
2006 if csr_future_asg_changes%FOUND then
2007 --
2008 hr_utility.set_location(l_proc,30);
2009 close csr_future_asg_changes;
2010 --
2011 hr_utility.set_message(801,'HR_7975_ASG_INV_FUTURE_ASA');
2012 hr_utility.raise_error;
2013 --
2014 end if;
2015 --
2016 --
2017 hr_utility.set_location(l_proc,270);
2018 --
2019 -- Validation in addition to Row Handlers
2020 --
2021 -- If the specified person type id is not null then check that it
2022 -- corresponds to type 'EMP', is currently active and is in the correct
2023 -- business group, otherwise set person type to the active default for EMP
2024 -- in the current business group.
2025 --
2026 per_per_bus.chk_person_type
2027 (p_person_type_id => l_person_type_id
2028 ,p_business_group_id => l_business_group_id
2029 ,p_expected_sys_type => 'EMP'
2030 );
2031 --
2032 hr_utility.set_location(l_proc,60);
2033 --
2034 -- Check that corresponding person is of 'APL', 'APL_EX_APL' or 'EX_EMP_APL'
2035 -- system person type.
2036 --
2037 if l_system_person_type <> 'APL' and
2038 l_system_person_type <> 'APL_EX_APL' and
2039 l_system_person_type <> 'EX_EMP_APL'
2040 then
2041 --
2042 hr_utility.set_location(l_proc,70);
2043 --
2044 hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
2045 hr_utility.raise_error;
2046 end if;
2047 --
2048 hr_utility.set_location(l_proc,80);
2049 --
2050 -- Bug# 2273304 Start Here
2051 --
2052 open csr_date_of_birth;
2053 fetch csr_date_of_birth into l_date_of_birth;
2054 close csr_date_of_birth;
2055
2056 l_age := trunc(months_between(p_hire_date,l_date_of_birth)/12);
2057
2058 open csr_bg_age_range;
2059 fetch csr_bg_age_range into l_minimum_age, l_maximum_age;
2060 close csr_bg_age_range;
2061
2062 if l_age not between nvl(l_minimum_age,l_age) and
2063 nvl(l_maximum_age,l_age) then
2064 hr_utility.set_message(801, 'HR_7426_EMP_AGE_ILLEGAL');
2065 hr_utility.set_message_token('MIN',to_char(l_minimum_age));
2066 hr_utility.set_message_token('MAX',to_char(l_maximum_age));
2067 hr_utility.raise_error;
2068 end if;
2069 hr_utility.set_location('age:'||l_age||'min:'||l_minimum_age||'max:'||l_maximum_age,91);
2070
2071 --
2072 -- Bug# 2273304 End Here
2073 --
2074 --
2075 -- Check that corresponding person is of 'ACCEPTED' of
2076 -- assignment status type.
2077 --
2078 open csr_chk_asg_status;
2079 fetch csr_chk_asg_status into l_count;
2080 if l_count = 0 then
2081 --
2082 hr_utility.set_location(l_proc,90);
2083 --
2084 close csr_chk_asg_status;
2085 --
2086 hr_utility.set_message(800,'PER_52098_APL_INV_ASG_STATUS');
2087 hr_utility.raise_error;
2088 --
2089 end if;
2090 --
2091 -- If the accepted assignment record is multiple, ASSIGNMENT_ID
2092 -- must be not null.
2093 --
2094 if l_count > 1 then
2095 --
2096 hr_utility.set_location(l_proc,100);
2097 --
2098 --close csr_chk_asg_status;
2099 --
2100 hr_api.mandatory_arg_error
2101 (p_api_name => l_proc
2102 ,p_argument => 'assignment_id'
2103 ,p_argument_value => p_assignment_id
2104 );
2105 --
2106 hr_utility.set_location(l_proc,110);
2107 --
2108 l_multi_flag := TRUE;
2109 --
2110 end if;
2111 --
2112 close csr_chk_asg_status;
2113 --
2114 hr_utility.set_location(l_proc,120);
2115 --
2116 -- Check p_assignment is corresponding data.
2117 -- The assignment record specified by P_ASSIGNMENT_ID on the hire
2118 -- date in the PER_ASSIGNMENTS_F table has assignment status
2119 -- 'ACCEPTED'.
2120 --
2121 if p_assignment_id is not null then
2122 --
2123 hr_utility.set_location(l_proc,130);
2124 --
2125 open csr_chk_assignment_id;
2126 fetch csr_chk_assignment_id
2127 into l_chk_person_id,
2128 l_chk_system_status;
2129 if csr_chk_assignment_id%NOTFOUND then
2130 --
2131 hr_utility.set_location(l_proc,140);
2132 --
2133 close csr_chk_assignment_id;
2134 --
2135 hr_utility.set_message(800,'PER_52099_ASG_INV_ASG_ID');
2136 hr_utility.raise_error;
2137 --
2138 end if;
2139 --
2140 if l_chk_person_id <> p_person_id then
2141 --
2142 hr_utility.set_location(l_proc,150);
2143 --
2144 close csr_chk_assignment_id;
2145 --
2146 hr_utility.set_message(800,'PER_52101_ASG_INV_PER_ID_COMB');
2147 hr_utility.raise_error;
2148 --
2149 end if;
2150 --
2151 if l_chk_system_status <> 'ACCEPTED' then
2152 --
2153 hr_utility.set_location(l_proc,150);
2154 --
2155 close csr_chk_assignment_id;
2156 --
2157 hr_utility.set_message(800,'PER_52100_ASG_INV_PER_TYPE');
2158 hr_utility.raise_error;
2159 --
2160 end if;
2161 --
2162 hr_utility.set_location(l_proc,160);
2163 --
2164 close csr_chk_assignment_id;
2165 --
2166 end if;
2167 --
2168 -- Fix for bug 2881076.
2169 -- check whether the person has periods_of_service record with a value
2170 -- ATD and but no FPD
2171 --
2172 open csr_pps_not_ended;
2173 fetch csr_pps_not_ended into l_pps;
2174 --
2175 if csr_pps_not_ended%found then
2176 --
2177 hr_utility.set_location(l_proc,165);
2178 close csr_pps_not_ended;
2179 --
2180 -- Fix 5196352 - Now we allow rehire before FPD
2181 -- hence do not throw error PER_289308_FUTURE_ENDED_FPD
2182 -- Here we just check FPD should never be null.
2183
2184 if l_pps is null then
2185 hr_utility.set_message('800','HR_449756_FPD_PREV_PDS');
2186 hr_utility.raise_error;
2187 end if;
2188 end if;
2189 --
2190 -- Fix for bug 2881076 end.
2191 hr_utility.set_location(l_proc,170);
2192 --
2193 -- Lock the person record in PER_ALL_PEOPLE_F ready for UPDATE at a later point.
2194 -- (Note: This is necessary because calling the table handlers in locking
2195 -- ladder order invokes an error in per_apl_upd.upd due to the person
2196 -- being modified by the per_per_upd.upd table handler.)
2197 per_per_shd.lck
2198 (p_effective_date => l_hire_date
2199 --bug no 5105005
2200 ,p_datetrack_mode => l_datetrack_mode
2201 --bug no 5105005
2202 ,p_person_id => p_person_id
2203 ,p_object_version_number => p_per_object_version_number
2204 ,p_validation_start_date => l_validation_start_date
2205 ,p_validation_end_date => l_validation_end_date
2206 );
2207 --
2208 hr_utility.set_location(l_proc,180);
2209 --
2210 --start changes for i-rec enhancement ww bug # 2675202
2211 --
2212 if (p_migrate) then
2213 for add_rec in csr_add(per_per_shd.g_old_rec.party_id) loop
2214 l_add_ovn:=add_rec.object_version_number;
2215 per_add_upd.upd(p_address_id => add_rec.address_id
2216 ,p_person_id => per_per_shd.g_old_rec.person_id
2217 ,p_business_group_id => l_business_group_id
2218 ,p_object_version_number => l_add_ovn
2219 ,p_effective_date => l_hire_date);
2220 end loop;
2221 --
2222 hr_utility.set_location(l_proc,181);
2223 --
2224 for phn_rec in csr_phn(per_per_shd.g_old_rec.party_id) loop
2225 l_phn_ovn:=phn_rec.object_version_number;
2226 l_parent_table := 'PER_ALL_PEOPLE_F';
2227 per_phn_upd.upd(p_phone_id => phn_rec.phone_id
2228 ,p_parent_id => per_per_shd.g_old_rec.person_id
2229 ,p_parent_table => l_parent_table
2230 ,p_object_version_number => l_phn_ovn
2231 ,p_effective_date => l_hire_date);
2232 end loop;
2233 --
2234 hr_utility.set_location(l_proc,182);
2235 --
2236 for pem_rec in csr_pem(per_per_shd.g_old_rec.party_id) loop
2237 l_pem_ovn:=pem_rec.object_version_number;
2238 per_pem_upd.upd(p_previous_employer_id => pem_rec.previous_employer_id
2239 ,p_person_id => per_per_shd.g_old_rec.person_id
2240 ,p_business_group_id => l_business_group_id
2241 ,p_object_version_number => l_pem_ovn
2242 ,p_effective_date => l_hire_date);
2243 end loop;
2244 --
2245 hr_utility.set_location(l_proc,183);
2246 --
2247 for qua_rec in csr_qua(per_per_shd.g_old_rec.party_id) loop
2248 l_qua_ovn:=qua_rec.object_version_number;
2249 per_qua_upd.upd(p_qualification_id => qua_rec.qualification_id
2250 ,p_person_id => per_per_shd.g_old_rec.person_id
2251 ,p_business_group_id => l_business_group_id
2252 ,p_object_version_number => l_qua_ovn
2253 ,p_effective_date => l_hire_date);
2254 end loop;
2255 --
2256 hr_utility.set_location(l_proc,184);
2257 --
2258 for esa_rec in csr_esa(per_per_shd.g_old_rec.party_id) loop
2259 l_esa_ovn:=esa_rec.object_version_number;
2260 per_esa_upd.upd(p_attendance_id => esa_rec.attendance_id
2261 ,p_person_id => per_per_shd.g_old_rec.person_id
2262 ,p_business_group_id => l_business_group_id
2263 ,p_object_version_number => l_esa_ovn
2264 ,p_effective_date => l_hire_date);
2265 end loop;
2266 --
2267 hr_utility.set_location(l_proc,185);
2268 --
2269 end if;
2270 --
2271 --End changes for i-rec enhancement ww bug # 2675202
2272 --
2273 -- Update the application details by calling the upd procedure in the
2274 -- application table handler:
2275 -- Date_end is set to l_hire_date - 1;
2276 --
2277 per_apl_upd.upd
2278 (p_application_id => l_application_id
2279 ,p_date_end => l_hire_date - 1
2280 ,p_object_version_number => l_apl_object_version_number
2281 ,p_effective_date => l_hire_date
2282 ,p_validate => false
2283 );
2284 hr_utility.set_location(l_proc,190);
2285 -- PTU : Commented
2286 -- Added to terminate APL record
2287 -- Bug 1253785
2288 -- hr_per_type_usage_internal.maintain_ptu(
2289 -- p_action => 'HIRE_APL',
2290 -- p_person_id => p_person_id,
2291 -- p_actual_termination_date => p_hire_date-1);
2292 --
2293 --
2294 -- Set all unaccepted applicant assignments to have end date = p_hire_date -1
2295 -- by calling the del procedure in the PER_ASSIGNMENTS_F table handler
2296 -- (This is a datetrack DELETE mode operation)
2297 --
2298 open csr_get_un_accepted;
2299 loop
2300 fetch csr_get_un_accepted
2301 into l_assignment_id,
2302 l_asg_object_version_number;
2303 exit when csr_get_un_accepted%NOTFOUND;
2304 --
2305 hr_utility.set_location(l_proc,210);
2306 --
2307 hr_utility.set_location(l_proc,240);
2308 --
2309 per_asg_del.del
2310 (p_assignment_id => l_assignment_id
2311 ,p_effective_start_date => l_effective_start_date
2312 ,p_effective_end_date => l_effective_end_date
2313 ,p_business_group_id => l_business_group_id
2314 ,p_object_version_number => l_asg_object_version_number
2315 ,p_effective_date => l_hire_date -1
2316 ,p_validation_start_date => l_validation_start_date
2317 ,p_validation_end_date => l_validation_end_date
2318 ,p_datetrack_mode => 'DELETE'
2319 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2320 );
2321 --
2322 per_people3_pkg.get_default_person_type
2323 (p_required_type => 'TERM_APL'
2324 ,p_business_group_id => l_business_group_id
2325 ,p_legislation_code => l_legislation_code
2326 ,p_person_type => l_assignment_status_type_id
2327 );
2328
2329 IRC_ASG_STATUS_API.create_irc_asg_status
2330 (p_assignment_id => l_assignment_id
2331 , p_assignment_status_type_id => l_assignment_status_type_id
2332 , p_status_change_date => l_hire_date -- Fix for bug 6036285
2333 , p_assignment_status_id => l_assignment_status_id
2334 , p_object_version_number => l_asg_status_ovn);
2335
2336 hr_utility.set_location(l_proc,250);
2337 --
2338 -- Added the below api call for bug 7540870.
2339 IRC_OFFERS_API.close_offer
2340 ( p_validate => false
2341 ,p_effective_date => l_hire_date-1
2342 ,p_applicant_assignment_id => l_assignment_id
2343 ,p_change_reason => 'MANUAL_CLOSURE'
2344 );
2345
2346 l_unaccepted_asg_del_warning := TRUE;
2347 --
2348 end loop;
2349 --
2350 close csr_get_un_accepted;
2351 --
2352 hr_utility.set_location(l_proc, 260);
2353
2354 -- bug3572499
2355 open csr_get_legislation_code;
2356 fetch csr_get_legislation_code into l_legislation_code;
2357 close csr_get_legislation_code;
2358 -- enf of bug3572499
2359
2360 --
2361 -- Update the person details by calling upd procedure in
2362 -- the per_all_people_f table.
2363 --
2364 l_person_type_id1 :=
2365 hr_person_type_usage_info.get_default_person_type_id
2366 (l_business_group_id
2367 ,'EMP');
2368
2369
2370 if (l_legislation_code <> 'US') then
2371 hr_utility.set_location(l_proc, 270);
2372 per_per_upd.upd(p_person_id => p_person_id
2373 ,p_effective_date => l_hire_date
2374 ,p_applicant_number => l_applicant_number
2375 ,p_person_type_id => l_person_type_id1
2376 ,p_object_version_number => l_per_object_version_number
2377 ,p_national_identifier => p_national_identifier
2378 ,p_employee_number => l_employee_number
2379 ,p_datetrack_mode => 'UPDATE'
2380 ,p_effective_start_date => l_per_effective_start_date
2381 ,p_effective_end_date => l_per_effective_end_date
2382 ,p_comment_id => l_comment_id
2383 ,p_current_applicant_flag => l_current_applicant_flag
2384 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
2385 ,p_current_employee_flag => l_current_employee_flag
2386 ,p_full_name => l_full_name
2387 ,p_name_combination_warning => l_name_combination_warning
2388 ,p_dob_null_warning => p_assign_payroll_warning
2389 ,p_orig_hire_warning => l_orig_hire_warning
2390 ,p_npw_number => l_npw_number
2391 ,p_original_date_of_hire => l_original_date_of_hire
2392 );
2393 else
2394 hr_utility.set_location(l_proc, 272);
2395 -- start of bug3572499
2396 --
2397 -- US legislation specific
2398 --
2399 open csr_get_new_hire_code;
2400 fetch csr_get_new_hire_code into l_new_hire_code;
2401 close csr_get_new_hire_code;
2402 --
2403
2404 --Fix For Bug 5749371 Starts
2405
2406 if p_source=true then
2407 open get_scl;
2408 fetch get_scl into l_dummy_soft_coding_keyflex_id;
2409 close get_scl;
2410 end if;
2411
2412 --Fix For Bug 5749371 Ends
2413
2414 if (l_new_hire_code is NULL) then
2415 hr_utility.set_location(l_proc,274);
2416 l_new_hire_code := 'INCL';
2417 end if;
2418 per_per_upd.upd(p_person_id => p_person_id
2419 ,p_effective_date => l_hire_date
2420 ,p_applicant_number => l_applicant_number
2421 ,p_person_type_id => l_person_type_id1
2422 ,p_object_version_number => l_per_object_version_number
2423 ,p_national_identifier => p_national_identifier
2424 ,p_employee_number => l_employee_number
2425 ,p_datetrack_mode => l_datetrack_mode
2426 ,p_effective_start_date => l_per_effective_start_date
2427 ,p_effective_end_date => l_per_effective_end_date
2428 ,p_comment_id => l_comment_id
2429 ,p_current_applicant_flag => l_current_applicant_flag
2430 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
2431 ,p_current_employee_flag => l_current_employee_flag
2432 ,p_full_name => l_full_name
2433 ,p_name_combination_warning => l_name_combination_warning
2434 ,p_dob_null_warning => p_assign_payroll_warning
2435 ,p_orig_hire_warning => l_orig_hire_warning
2436 ,p_npw_number => l_npw_number
2437 ,p_original_date_of_hire => l_original_date_of_hire
2438 ,p_per_information7 => l_new_hire_code
2439 );
2440 end if;
2441 -- end of bug3572499
2442 --
2443 hr_utility.set_location(l_proc,284);
2444 --
2445 -- PTU : Following Code has been added
2446
2447 hr_per_type_usage_internal.maintain_person_type_usage
2448 (p_effective_date => l_hire_date
2449 ,p_person_id => p_person_id
2450 ,p_person_type_id => l_person_type_id
2451 --bug no 5105005
2452 ,p_datetrack_update_mode=> l_datetrack_mode
2453 --bug no 5105005
2454 );
2455 --
2456 hr_utility.set_location(l_proc,286);
2457 --
2458 l_person_type_id1 :=
2459 hr_person_type_usage_info.get_default_person_type_id
2460 (l_business_group_id
2461 ,'EX_APL');
2462
2463 hr_utility.set_location(l_proc,288);
2464 --
2465 hr_per_type_usage_internal.maintain_person_type_usage
2466 (p_effective_date => l_hire_date
2467 ,p_person_id => p_person_id
2468 ,p_person_type_id => l_person_type_id1
2469 );
2470
2471 -- PTU : End of changes
2472
2473 hr_utility.set_location(l_proc,290);
2474 --
2475 -- Insert the period of service into per_periods_of_service by calling the
2476 -- Periods of Service table handler:
2477 --
2478 per_pds_ins.ins
2479 (p_business_group_id => l_business_group_id
2480 ,p_person_id => p_person_id
2481 ,p_date_start => l_hire_date
2482 ,p_effective_date => l_hire_date
2483 --
2484 ,p_period_of_service_id => l_period_of_service_id
2485 ,p_object_version_number => l_pds_object_version_number
2486 ,p_validate_df_flex => false
2487 );
2488 --
2489 hr_utility.set_location(l_proc,310);
2490 -- -----------------------------------------------------------------------+
2491 -- Processing ACCEPTED APL ASG +
2492 -- -----------------------------------------------------------------------+
2493 --
2494 -- All accepted applicant assignments are changed to employee assignments
2495 -- with default employee assignment.(ACTIVE_ASSIGN)
2496 -- 1) Derive assignment_status_type_id for default 'ACTIVE_ASSIGN'.
2497 -- 2) Update the assignments by calling the upd procedure in the
2498 -- PER_ASSIGNMENTS_F table handler(This is a datetrack UPDATE mode
2499 -- operation)
2500 -- 3) When the accepted assignments are multiple, the primary flag of the
2501 -- record not specified by P_ASSIGNMENT_ID is set to 'N'.
2502 --
2503 -- reset l_assignment_status_type_id for updating the accepted assignments
2504 l_assignment_status_type_id := hr_api.g_number;
2505 --
2506 per_asg_bus1.chk_assignment_status_type
2507 (p_assignment_status_type_id => l_assignment_status_type_id
2508 ,p_business_group_id => l_business_group_id
2509 ,p_legislation_code => l_legislation_code
2510 ,p_expected_system_status => 'ACTIVE_ASSIGN'
2511 );
2512 --
2513 hr_utility.set_location(l_proc,320);
2514 l_oversubscribed_vacancy_id:=null;
2515 --
2516 --
2517 -- start of bug 4681211
2518 -- added an if condition to support the bug 4681211
2519 -- and the ' else ' part of the the api will work as it was previously
2520 if p_assignment_id is not null then
2521 -- first process the primary assignment id
2522 -- so that it can generate the assignment Number correctly.
2523 -- bug 4681211 added
2524 hr_utility.set_location(l_proc,321);
2525 open csr_get_accepted_pmry;
2526 fetch csr_get_accepted_pmry
2527 into l_assignment_id,
2528 l_asg_object_version_number,
2529 l_vacancy_id;
2530 if csr_get_accepted_pmry%FOUND then
2531 --
2532 hr_utility.set_location(l_proc,340);
2533 --
2534 l_primary_flag := 'Y';
2535 --
2536 if l_multi_flag = TRUE then
2537 --
2538 if l_assignment_id <> p_assignment_id then
2539 --
2540 hr_utility.set_location(l_proc,360);
2541 --
2542 l_primary_flag := 'N';
2543 --
2544 end if;
2545 --
2546 hr_utility.set_location(l_proc,370);
2547 --
2548 end if;
2549 --
2550 hr_utility.set_location(l_proc,380);
2551 --
2552
2553 --Fix For Bug 5749371 Starts
2554
2555 open csr_existing_SCL(l_assignment_id);
2556 fetch csr_existing_SCL into l_soft_coding_keyflex_id,l_payroll_id;
2557 close csr_existing_SCL;
2558
2559 if l_soft_coding_keyflex_id is null and l_payroll_id is not null and p_source=true then
2560 l_soft_coding_keyflex_id := l_dummy_soft_coding_keyflex_id;
2561 else
2562 l_soft_coding_keyflex_id := hr_api.g_number;
2563 end if;
2564
2565 --Fix For Bug 5749371 Ends
2566
2567 per_asg_upd.upd
2568 (p_assignment_id => l_assignment_id,
2569 p_object_version_number => l_asg_object_version_number,
2570 p_effective_date => l_hire_date,
2571 p_datetrack_mode => 'UPDATE',
2572 p_assignment_status_type_id => l_assignment_status_type_id,
2573 p_assignment_type => 'E',
2574 p_primary_flag => l_primary_flag,
2575 p_period_of_service_id => l_period_of_service_id,
2576 --
2577 p_effective_start_date => l_effective_start_date,
2578 p_effective_end_date => l_effective_end_date,
2579 p_business_group_id => l_business_group_id,
2580 p_comment_id => l_comment_id,
2581 p_validation_start_date => l_validation_start_date,
2582 p_validation_end_date => l_validation_end_date,
2583 p_payroll_id_updated => l_payroll_id_updated,
2584 p_other_manager_warning => l_other_manager_warning,
2585 p_no_managers_warning => l_no_managers_warning,
2586 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
2587 p_hourly_salaried_warning => l_hourly_salaried_warning,
2588 p_soft_coding_keyflex_id => l_soft_coding_keyflex_id --Fix For Bug 5749371
2589 );
2590 --
2591 --
2592 -- 115.71 (START)
2593 --
2594 hr_utility.set_location(l_proc,385);
2595 --
2596 -- Handle potentially overlapping PDS due to rehire before FPD
2597 --
2598 hr_employee_api.manage_rehire_primary_asgs
2599 (p_person_id => p_person_id
2600 ,p_rehire_date => l_hire_date
2601 ,p_cancel => 'N'
2602 );
2603 --
2604
2605 --
2606 -- 115.71 (END)
2607 --
2608 --
2609 IRC_ASG_STATUS_API.create_irc_asg_status
2610 (p_assignment_id => l_assignment_id
2611 , p_assignment_status_type_id => l_assignment_status_type_id
2612
2613 -- Bug: 2416817 Starts here.
2614 -- Replaced l_effective_end_date with l_effective_start_date to ensure
2615 -- Assignment status change is recorded properly in IRC tables.
2616
2617 , p_status_change_date => l_effective_start_date
2618
2619 -- Bug :2416817 Ends here.
2620
2621 , p_assignment_status_id => l_assignment_status_id
2622 , p_object_version_number => l_asg_status_ovn);
2623
2624 -- Added the below api call for bug 7540870.
2625
2626 IRC_OFFERS_API.close_offer
2627 ( p_validate => false
2628 ,p_effective_date => l_effective_start_date-1
2629 ,p_applicant_assignment_id => l_assignment_id
2630 ,p_change_reason => 'APL_HIRED'
2631 );
2632
2633
2634 open csr_vacs(l_vacancy_id);
2635 fetch csr_vacs into l_dummy;
2636 if csr_vacs%found then
2637 close csr_vacs;
2638 l_oversubscribed_vacancy_id:=l_vacancy_id;
2639 else
2640 close csr_vacs;
2641 end if;
2642 hr_utility.set_location(l_proc,390);
2643 --
2644 -- 2833630: Any salary proposals attached to the APL assignment should have
2645 -- the salary change date updated to be >= the hire date.
2646 --
2647 if p_source = false then
2648 update_salary_proposal(l_assignment_id, l_hire_date);
2649 end if;--fix for bug 5354681
2650 --
2651 hr_utility.set_location(l_proc,395);
2652 --
2653 hr_utility.set_location(l_proc,410);
2654 --
2655 close csr_get_accepted_pmry;
2656 else
2657 close csr_get_accepted_pmry;
2658 end if;
2659
2660 hr_utility.set_location(l_proc,322);
2661 open csr_get_accepted_non;
2662 loop
2663 fetch csr_get_accepted_non
2664 into l_assignment_id,
2665 l_asg_object_version_number,
2666 l_vacancy_id;
2667 exit when csr_get_accepted_non%NOTFOUND;
2668 --
2669 hr_utility.set_location(l_proc,340);
2670 --
2671 l_primary_flag := 'Y';
2672 --
2673 if l_multi_flag = TRUE then
2674 --
2675 if l_assignment_id <> p_assignment_id then
2676 --
2677 hr_utility.set_location(l_proc,360);
2678 --
2679 l_primary_flag := 'N';
2680 --
2681 end if;
2682 --
2683 hr_utility.set_location(l_proc,370);
2684 --
2685 end if;
2686 --
2687 hr_utility.set_location(l_proc,380);
2688 --
2689
2690 --Fix For Bug 5749371 Starts
2691
2692 open csr_existing_SCL(l_assignment_id);
2693 fetch csr_existing_SCL into l_soft_coding_keyflex_id,l_payroll_id;
2694 close csr_existing_SCL;
2695
2696 if l_soft_coding_keyflex_id is null and l_payroll_id is not null and p_source=true then
2697 l_soft_coding_keyflex_id := l_dummy_soft_coding_keyflex_id;
2698 else
2699 l_soft_coding_keyflex_id := hr_api.g_number;
2700 end if;
2701
2702 --Fix For Bug 5749371 Ends
2703
2704 per_asg_upd.upd
2705 (p_assignment_id => l_assignment_id,
2706 p_object_version_number => l_asg_object_version_number,
2707 p_effective_date => l_hire_date,
2708 p_datetrack_mode => 'UPDATE',
2709 p_assignment_status_type_id => l_assignment_status_type_id,
2710 p_assignment_type => 'E',
2711 p_primary_flag => l_primary_flag,
2712 p_period_of_service_id => l_period_of_service_id,
2713 --
2714 p_effective_start_date => l_effective_start_date,
2715 p_effective_end_date => l_effective_end_date,
2716 p_business_group_id => l_business_group_id,
2717 p_comment_id => l_comment_id,
2718 p_validation_start_date => l_validation_start_date,
2719 p_validation_end_date => l_validation_end_date,
2720 p_payroll_id_updated => l_payroll_id_updated,
2721 p_other_manager_warning => l_other_manager_warning,
2722 p_no_managers_warning => l_no_managers_warning,
2723 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
2724 p_hourly_salaried_warning => l_hourly_salaried_warning,
2725 p_soft_coding_keyflex_id => l_soft_coding_keyflex_id --Fix For Bug 5749371
2726 );
2727 --
2728 --
2729 -- 115.71 (START)
2730 --
2731 hr_utility.set_location(l_proc,385);
2732 --
2733 -- Handle potentially overlapping PDS due to rehire before FPD
2734 --
2735 hr_employee_api.manage_rehire_primary_asgs
2736 (p_person_id => p_person_id
2737 ,p_rehire_date => l_hire_date
2738 ,p_cancel => 'N'
2739 );
2740 --
2741
2742 --
2743 -- 115.71 (END)
2744 --
2745 --
2746 IRC_ASG_STATUS_API.create_irc_asg_status
2747 (p_assignment_id => l_assignment_id
2748 , p_assignment_status_type_id => l_assignment_status_type_id
2749
2750 -- Bug: 2416817 Starts here.
2751 -- Replaced l_effective_end_date with l_effective_start_date to ensure
2752 -- Assignment status change is recorded properly in IRC tables.
2753
2754 , p_status_change_date => l_effective_start_date
2755
2756 -- Bug :2416817 Ends here.
2757
2758 , p_assignment_status_id => l_assignment_status_id
2759 , p_object_version_number => l_asg_status_ovn);
2760
2761
2762 -- Added the below api call for bug 7540870.
2763 IRC_OFFERS_API.close_offer
2764 ( p_validate => false
2765 ,p_effective_date => l_effective_start_date-1
2766 ,p_applicant_assignment_id => l_assignment_id
2767 ,p_change_reason => 'APL_HIRED'
2768 );
2769
2770 open csr_vacs(l_vacancy_id);
2771 fetch csr_vacs into l_dummy;
2772 if csr_vacs%found then
2773 close csr_vacs;
2774 l_oversubscribed_vacancy_id:=l_vacancy_id;
2775 else
2776 close csr_vacs;
2777 end if;
2778 hr_utility.set_location(l_proc,390);
2779 --
2780 -- 2833630: Any salary proposals attached to the APL assignment should have
2781 -- the salary change date updated to be >= the hire date.
2782 --
2783 if p_source = false then
2784 update_salary_proposal(l_assignment_id, l_hire_date);
2785 end if;--fix for bug 5354681
2786 --
2787 hr_utility.set_location(l_proc,395);
2788 end loop;
2789 --
2790 hr_utility.set_location(l_proc,410);
2791 --
2792 close csr_get_accepted_non;
2793
2794 else
2795 -- case when p_assignment_id is null
2796 -- this works as how it was previoulsy
2797 hr_utility.set_location(l_proc,323);
2798 hr_utility.set_location('inside the else part',910);
2799 open csr_get_accepted;
2800 loop
2801 fetch csr_get_accepted
2802 into l_assignment_id,
2803 l_asg_object_version_number,
2804 l_vacancy_id;
2805 exit when csr_get_accepted%NOTFOUND;
2806 --
2807 hr_utility.set_location(l_proc,340);
2808 --
2809 l_primary_flag := 'Y';
2810 --
2811 if l_multi_flag = TRUE then
2812 --
2813 if l_assignment_id <> p_assignment_id then
2814 --
2815 hr_utility.set_location(l_proc,360);
2816 --
2817 l_primary_flag := 'N';
2818 --
2819 end if;
2820 --
2821 hr_utility.set_location(l_proc,370);
2822 --
2823 end if;
2824 --
2825 hr_utility.set_location(l_proc,380);
2826 --
2827 per_asg_upd.upd
2828 (p_assignment_id => l_assignment_id,
2829 p_object_version_number => l_asg_object_version_number,
2830 p_effective_date => l_hire_date,
2831 p_datetrack_mode => 'UPDATE',
2832 p_assignment_status_type_id => l_assignment_status_type_id,
2833 p_assignment_type => 'E',
2834 p_primary_flag => l_primary_flag,
2835 p_period_of_service_id => l_period_of_service_id,
2836 --
2837 p_effective_start_date => l_effective_start_date,
2838 p_effective_end_date => l_effective_end_date,
2839 p_business_group_id => l_business_group_id,
2840 p_comment_id => l_comment_id,
2841 p_validation_start_date => l_validation_start_date,
2842 p_validation_end_date => l_validation_end_date,
2843 p_payroll_id_updated => l_payroll_id_updated,
2844 p_other_manager_warning => l_other_manager_warning,
2845 p_no_managers_warning => l_no_managers_warning,
2846 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
2847 p_hourly_salaried_warning => l_hourly_salaried_warning
2848 );
2849 --
2850 --
2851 -- 115.71 (START)
2852 --
2853 hr_utility.set_location(l_proc,385);
2854 --
2855 -- Handle potentially overlapping PDS due to rehire before FPD
2856 --
2857 hr_employee_api.manage_rehire_primary_asgs
2858 (p_person_id => p_person_id
2859 ,p_rehire_date => l_hire_date
2860 ,p_cancel => 'N'
2861 );
2862 --
2863
2864 --
2865 -- 115.71 (END)
2866 --
2867 --
2868 IRC_ASG_STATUS_API.create_irc_asg_status
2869 (p_assignment_id => l_assignment_id
2870 , p_assignment_status_type_id => l_assignment_status_type_id
2871
2872 -- Bug: 2416817 Starts here.
2873 -- Replaced l_effective_end_date with l_effective_start_date to ensure
2874 -- Assignment status change is recorded properly in IRC tables.
2875
2876 , p_status_change_date => l_effective_start_date
2877
2878 -- Bug :2416817 Ends here.
2879
2880 , p_assignment_status_id => l_assignment_status_id
2881 , p_object_version_number => l_asg_status_ovn);
2882 -- Added the below api call for bug 7540870.
2883 IRC_OFFERS_API.close_offer
2884 ( p_validate => false
2885 ,p_effective_date => l_effective_start_date-1
2886 ,p_applicant_assignment_id => l_assignment_id
2887 ,p_change_reason => 'APL_HIRED'
2888 );
2889
2890 hr_utility.set_location(l_proc,386);
2891 open csr_vacs(l_vacancy_id);
2892 fetch csr_vacs into l_dummy;
2893 if csr_vacs%found then
2894 close csr_vacs;
2895 l_oversubscribed_vacancy_id:=l_vacancy_id;
2896 else
2897 close csr_vacs;
2898 end if;
2899 hr_utility.set_location(l_proc,390);
2900 --
2901 -- 2833630: Any salary proposals attached to the APL assignment should have
2902 -- the salary change date updated to be >= the hire date.
2903 --
2904 if p_source = false then
2905 update_salary_proposal(l_assignment_id, l_hire_date);
2906 end if;--fix for bug 5354681
2907 --
2908 hr_utility.set_location(l_proc,395);
2909 end loop;
2910 --
2911 hr_utility.set_location(l_proc,410);
2912 --
2913 close csr_get_accepted;
2914
2915 end if;
2916 -- end of the bug 4681211
2917 --
2918 -- 1766066: added call for contact start date enh.
2919 --
2920 per_people12_pkg.maintain_coverage(p_person_id => p_person_id
2921 ,p_type => 'EMP'
2922 );
2923 -- 1766066 end.
2924
2925 --start changes for bug 6598795
2926 hr_assignment.update_assgn_context_value (l_business_group_id,
2927 p_person_id,
2928 l_assignment_id,
2929 p_hire_date);
2930
2931 SELECT object_version_number
2932 INTO l_asg_object_Version_number
2933 FROM per_all_assignments_f
2934 WHERE business_group_id = l_business_group_id
2935 and person_id = p_person_id
2936 and assignment_id = l_assignment_id
2937 and effective_start_Date = p_hire_date;
2938 --end changes for bug 6598795
2939 --
2940 -- Call After Process User Hook for hire_applicant
2941 --
2942 begin
2943 hr_applicant_bk2.hire_applicant_a
2944 (
2945 p_hire_date => l_hire_date,
2946 p_person_id => p_person_id,
2947 p_assignment_id => p_assignment_id,
2948 p_person_type_id => p_person_type_id,
2949 p_national_identifier => p_national_identifier,
2950 p_per_object_version_number => l_per_object_version_number,
2951 p_employee_number => l_employee_number,
2952 p_per_effective_start_date => l_per_effective_start_date,
2953 p_per_effective_end_date => l_per_effective_end_date,
2954 p_unaccepted_asg_del_warning => l_unaccepted_asg_del_warning,
2955 p_assign_payroll_warning => l_assign_payroll_warning,
2956 p_oversubscribed_vacancy_id => l_oversubscribed_vacancy_id,
2957 p_original_date_of_hire => l_original_date_of_hire
2958 );
2959 exception
2960 when hr_api.cannot_find_prog_unit then
2961 hr_api.cannot_find_prog_unit_error
2962 (p_module_name => 'HIRE_APPLICANT'
2963 ,p_hook_type => 'AP'
2964 );
2965 --
2966 -- End of the after hook for hire_applicant
2967 --
2968 end;
2969 --
2970 -- When in validation only mode raise the Validate_Enabled exception
2971 --
2972 if p_validate then
2973 raise hr_api.validate_enabled;
2974 end if;
2975 --
2976 --
2977 p_employee_number := l_employee_number;
2978 p_per_object_version_number := l_per_object_version_number;
2979 p_per_effective_start_date := l_per_effective_start_date;
2980 p_per_effective_end_date := l_per_effective_end_date;
2981 p_unaccepted_asg_del_warning := l_unaccepted_asg_del_warning;
2982 p_assign_payroll_warning := l_assign_payroll_warning;
2983 p_oversubscribed_vacancy_id := l_oversubscribed_vacancy_id;
2984 --
2985 hr_utility.set_location(' Leaving:'||l_proc, 250);
2986 --
2987 exception
2988 when hr_api.validate_enabled then
2989 --
2990 -- As the Validate_Enabled exception has been raised
2991 -- we must rollback to the savepoint
2992 --
2993 ROLLBACK TO hire_applicant;
2994 --
2995 -- Only set output warning arguments
2996 -- (Any key or derived arguments must be set to null
2997 -- when validation only mode is being used.)
2998 -- Set OUT parameters to null
2999 --
3000 p_employee_number := null;
3001 p_per_object_version_number := null;
3002 p_per_effective_start_date := null;
3003 p_per_effective_end_date := null;
3004 p_unaccepted_asg_del_warning := l_unaccepted_asg_del_warning;
3005 p_assign_payroll_warning := l_assign_payroll_warning;
3006 p_oversubscribed_vacancy_id := l_oversubscribed_vacancy_id;
3007 --
3008 hr_utility.set_location(' Leaving:'||l_proc, 250);
3009 --
3010 when others then
3011 --
3012 -- A validation or unexpected error has occurred
3013 --
3014 -- Added as part of fix to bug 632479
3015 --
3016
3017 p_per_object_version_number := lv_per_object_version_number ;
3018 p_employee_number := lv_employee_number ;
3019 p_per_effective_start_date := null;
3020 p_per_effective_end_date := null;
3021 p_unaccepted_asg_del_warning := null;
3022 p_assign_payroll_warning := null;
3023 p_oversubscribed_vacancy_id := null;
3024
3025 ROLLBACK TO hire_applicant;
3026
3027 --
3028 hr_utility.set_location(' Leaving:'||l_proc, 255);
3029 raise;
3030 -- End of fix.
3031 --
3032 end hire_applicant;
3033 --
3034 -- OLD
3035 -- ----------------------------------------------------------------------------
3036 -- |-------------------------< terminate_applicant >--------------------------|
3037 -- ----------------------------------------------------------------------------
3038 -- OLD
3039 PROCEDURE terminate_applicant
3040 (p_validate IN BOOLEAN --DEFAULT FALSE
3041 ,p_effective_date IN DATE
3042 ,p_person_id IN per_all_people_f.person_id%TYPE
3043 ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3044 ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT hr_api.g_number
3045 ,p_termination_reason IN per_applications.termination_reason%TYPE --DEFAULT NULL
3046 ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3047 ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3048 )
3049 IS
3050 Begin
3051 hr_applicant_api.terminate_applicant
3052 (p_validate => p_validate
3053 ,p_effective_date => p_effective_date
3054 ,p_person_id => p_person_id
3055 ,p_object_version_number => p_object_version_number
3056 ,p_person_type_id => p_person_type_id
3057 ,p_termination_reason => p_termination_reason
3058 ,p_effective_start_date => p_effective_start_date
3059 ,p_effective_end_date => p_effective_end_date
3060 ,p_assignment_status_type_id => Null
3061 );
3062 end terminate_applicant;
3063 --
3064 -- ----------------------------------------------------------------------------
3065 -- |-----------------------< terminate_applicant(New1) >----------------------|
3066 -- ----------------------------------------------------------------------------
3067 --
3068 PROCEDURE terminate_applicant
3069 (p_validate IN BOOLEAN
3070 ,p_effective_date IN DATE
3071 ,p_person_id IN per_all_people_f.person_id%TYPE
3072 ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3073 ,p_person_type_id IN per_person_types.person_type_id%TYPE
3074 ,p_termination_reason IN per_applications.termination_reason%TYPE
3075 ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3076 ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3077 ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3078 )
3079 IS
3080 l_warning boolean;
3081 BEGIN
3082 hr_applicant_api.terminate_applicant
3083 (p_validate => p_validate
3084 ,p_effective_date => p_effective_date
3085 ,p_person_id => p_person_id
3086 ,p_object_version_number => p_object_version_number
3087 ,p_person_type_id => p_person_type_id
3088 ,p_termination_reason => p_termination_reason
3089 ,p_effective_start_date => p_effective_start_date
3090 ,p_effective_end_date => p_effective_end_date
3091 ,p_assignment_status_type_id => p_assignment_status_type_id
3092 ,p_remove_fut_asg_warning => l_warning -- 3652025
3093 );
3094 END terminate_applicant;
3095 --
3096 -- ----------------------------------------------------------------------------
3097 -- |-----------------------< terminate_applicant(New2) >----------------------|
3098 -- ----------------------------------------------------------------------------
3099 --
3100 PROCEDURE terminate_applicant
3101 (p_validate IN BOOLEAN
3102 ,p_effective_date IN DATE
3103 ,p_person_id IN per_all_people_f.person_id%TYPE
3104 ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3105 ,p_person_type_id IN per_person_types.person_type_id%TYPE
3106 ,p_termination_reason IN per_applications.termination_reason%TYPE
3107 ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3108 ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3109 ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3110 ,p_remove_fut_asg_warning OUT NOCOPY BOOLEAN -- 3652025
3111 )
3112 IS
3113 BEGIN
3114 hr_applicant_api.terminate_applicant
3115 (p_validate => p_validate
3116 ,p_effective_date => p_effective_date
3117 ,p_person_id => p_person_id
3118 ,p_object_version_number => p_object_version_number
3119 ,p_person_type_id => p_person_type_id
3120 ,p_termination_reason => p_termination_reason
3121 ,p_change_reason => NULL -- 4066579
3122 ,p_effective_start_date => p_effective_start_date
3123 ,p_effective_end_date => p_effective_end_date
3124 ,p_assignment_status_type_id => p_assignment_status_type_id
3125 ,p_remove_fut_asg_warning => p_remove_fut_asg_warning
3126 );
3127 END terminate_applicant;
3128 --
3129 -- ----------------------------------------------------------------------------
3130 -- |-----------------------< terminate_applicant(New3) >----------------------|
3131 -- ----------------------------------------------------------------------------
3132 --
3133 PROCEDURE terminate_applicant
3134 (p_validate IN BOOLEAN
3135 ,p_effective_date IN DATE
3136 ,p_person_id IN per_all_people_f.person_id%TYPE
3137 ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3138 ,p_person_type_id IN per_person_types.person_type_id%TYPE
3139 ,p_termination_reason IN per_applications.termination_reason%TYPE
3140 ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3141 ,p_change_reason IN per_all_assignments_f.change_reason%TYPE -- 4066579
3142 ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3143 ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3144 ,p_remove_fut_asg_warning OUT NOCOPY BOOLEAN -- 3652025
3145 )
3146 IS
3147 --
3148 -- Local variables
3149 --
3150 l_proc VARCHAR2(72) := g_package||'terminate_applicant';
3151 --
3152 l_legislation_code varchar2(30);
3153 l_asg_status_ovn number;
3154 l_effective_date DATE;
3155 --
3156 l_object_version_number CONSTANT per_all_people_f.object_version_number%TYPE := p_object_version_number;
3157 l_person_type_id per_person_types.person_type_id%TYPE := p_person_type_id;
3158 l_person_type_id1 per_person_types.person_type_id%TYPE;
3159 --
3160 l_assignment_status_id number;
3161 l_assignment_status_type_id number;
3162 l_validation_start_date DATE;
3163 l_validation_end_date DATE;
3164 l_effective_start_date DATE;
3165 l_effective_end_date DATE;
3166 --
3167 l_business_group_id hr_all_organization_units.organization_id%TYPE;
3168 l_org_now_no_manager_warning BOOLEAN;
3169 l_system_person_type per_person_types.system_person_type%TYPE;
3170 l_comment_id hr_comments.comment_id%TYPE;
3171 l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3172 l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3173 l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3174 l_full_name per_all_people_f.full_name%TYPE;
3175 l_name_combination_warning BOOLEAN;
3176 l_dob_null_warning BOOLEAN;
3177 l_orig_hire_warning BOOLEAN;
3178 --
3179 lv_object_version_number per_all_people_f.object_version_number%TYPE := p_object_version_number ;
3180 l_remove_future_asg_warning BOOLEAN;
3181 l_count NUMBER;
3182 --fix for bug 7229710 Starts here.
3183 l_vacancy_id number;
3184
3185
3186
3187 --
3188 -- Local cursors
3189 --
3190 Cursor csr_vacancy_id(l_assg_id number) is
3191 Select vacancy_id
3192 From per_all_assignments_f
3193 Where assignment_id = l_assg_id
3194 And p_effective_date between effective_start_date and effective_end_date;
3195 --fix for bug 7229710 Ends here.
3196
3197 CURSOR csr_applications
3198 (p_effective_date IN DATE
3199 ,p_person_id IN per_all_people_f.person_id%TYPE
3200 )
3201 IS
3202 SELECT apl.application_id
3203 ,apl.object_version_number
3204 ,per.business_group_id
3205 ,per.applicant_number
3206 ,per.employee_number
3207 ,pet.system_person_type
3208 ,per.npw_number
3209 FROM per_applications apl
3210 ,per_person_types pet
3211 ,per_people_f per
3212 WHERE p_effective_date BETWEEN apl.date_received
3213 AND NVL(apl.date_end,p_effective_date)
3214 AND apl.person_id = per.person_id
3215 AND pet.person_type_id = per.person_type_id
3216 AND p_effective_date BETWEEN per.effective_start_date
3217 AND per.effective_end_date
3218 AND per.person_id = p_person_id;
3219 l_application csr_applications%ROWTYPE;
3220 --
3221 CURSOR csr_assignments
3222 (p_effective_date IN DATE
3223 ,p_person_id IN per_all_people_f.person_id%TYPE
3224 )
3225 IS
3226 SELECT asg.assignment_id
3227 ,asg.object_version_number
3228 ,asg.effective_end_date -- 3652025
3229 ,asg.assignment_status_type_id --7229710
3230 FROM per_all_assignments_f asg
3231 WHERE asg.person_id = p_person_id
3232 AND p_effective_date+1 BETWEEN asg.effective_start_date
3233 AND asg.effective_end_date
3234 AND asg.assignment_type = 'A';
3235 --
3236 -- 3652025 >>
3237 CURSOR csr_get_future_apl_asg(cp_person_id number
3238 , cp_effective_date date
3239 , cp_application_id number) IS
3240 SELECT as2.assignment_id, as2.effective_start_date, as2.object_version_number
3241 FROM per_all_assignments_f as2
3242 WHERE as2.person_id = cp_person_id
3243 AND as2.application_id = cp_application_id
3244 AND as2.assignment_type = 'A'
3245 AND as2.effective_start_date > cp_effective_date
3246 AND not exists
3247 (select 'N'
3248 from per_all_assignments_f as1
3249 where as1.assignment_id = as2.assignment_id
3250 and as1.effective_start_date < as2.effective_start_date)
3251 ORDER BY as2.effective_start_date, as2.assignment_id ASC;
3252
3253 CURSOR csr_lock_person(cp_person_id number, cp_termination_date date) IS
3254 SELECT null
3255 FROM per_all_people_f
3256 WHERE person_id = cp_person_id
3257 AND (effective_start_date > cp_termination_date
3258 OR
3259 cp_termination_date between effective_start_date
3260 and effective_end_date)
3261 for update nowait;
3262 --
3263 CURSOR csr_lock_ptu(cp_person_id number, cp_termination_date date) IS
3264 SELECT null
3265 FROM per_person_type_usages_f ptu
3266 ,per_person_types ppt
3267 WHERE person_id = cp_person_id
3268 AND (effective_start_date > cp_termination_date
3269 OR
3270 cp_termination_date between effective_start_date
3271 and effective_end_date)
3272 AND ptu.person_type_id = ppt.person_type_id
3273 AND ppt.system_person_type in ('APL','EX_APL')
3274 -- for update nowait; for bug 6433245
3275 for update of ptu.person_id nowait;
3276 -- <<
3277 --
3278 BEGIN
3279 --
3280 hr_utility.set_location('Entering:'||l_proc,10);
3281 --
3282 -- Ensure mandatory arguments have been passed
3283 --
3284 hr_api.mandatory_arg_error
3285 (p_api_name => l_proc
3286 ,p_argument => 'person_id'
3287 ,p_argument_value => p_person_id
3288 );
3289 --
3290 hr_api.mandatory_arg_error
3291 (p_api_name => l_proc
3292 ,p_argument => 'effective_date'
3293 ,p_argument_value => p_effective_date
3294 );
3295 --
3296 -- Truncate all date parameters passed in
3297 --
3298 l_effective_date := TRUNC(p_effective_date);
3299 --
3300 -- Issue savepoint
3301 --
3302 SAVEPOINT terminate_applicant;
3303 --
3304 hr_utility.set_location(l_proc,20);
3305 --
3306 -- Call Before Process User Hook
3307 --
3308 BEGIN
3309 hr_applicant_bk3.terminate_applicant_b
3310 (p_effective_date => l_effective_date
3311 ,p_person_id => p_person_id
3312 ,p_object_version_number => p_object_version_number
3313 ,p_person_type_id => p_person_type_id
3314 ,p_termination_reason => p_termination_reason
3315 );
3316 EXCEPTION
3317 WHEN hr_api.cannot_find_prog_unit
3318 THEN
3319 hr_api.cannot_find_prog_unit_error
3320 (p_module_name => 'TERMINATE_APPLICANT'
3321 ,p_hook_type => 'B'
3322 );
3323 END;
3324 --
3325 hr_utility.set_location(l_proc,30);
3326 --
3327 -- Derive application details
3328 --
3329 OPEN csr_applications
3330 (p_effective_date => l_effective_date
3331 ,p_person_id => p_person_id
3332 );
3333 FETCH csr_applications INTO l_application;
3334 IF (csr_applications%NOTFOUND)
3335 THEN
3336 CLOSE csr_applications;
3337 hr_utility.set_message(801,'HR_51011_PER_NOT_EXIST_DATE');
3338 hr_utility.raise_error;
3339 END IF;
3340 CLOSE csr_applications;
3341 --
3342 hr_utility.set_location(l_proc,40);
3343 --
3344 -- If person_type_id is not null check it corresponds to the correct type
3345 -- of ex-applicant is currently active and in the correct business group,
3346 -- otherwise set the person type id the active default for 'ex-applicant'
3347 -- in the correct business group.
3348 -- With PTU changes, the person_type_id must be flavour of EX_APL, so modify chk call
3349 -- so that l_person_type_id passes the validated flavour to PTU
3350 --
3351 --
3352 -- Bug Number : 2929504. Added condition for EX_EM_APL
3353 --
3354 -- not needed, these conditions are handled within the
3355 -- update_per_ptu_to_EX_APL procedure
3356 --
3357 -- IF l_application.system_person_type = 'EMP_APL'
3358 -- THEN
3359 -- l_system_person_type := 'EMP';
3360 -- hr_utility.set_location(l_proc,42);
3361 -- ELSIF l_application.system_person_type = 'EX_EMP_APL'
3362 -- THEN
3363 -- l_system_person_type := 'EX_EMP';
3364 -- hr_utility.set_location(l_proc,44);
3365 -- ELSE
3366 -- l_system_person_type := 'EX_APL';
3367 -- hr_utility.set_location(l_proc,46);
3368 -- END IF;
3369 --
3370 -- End of Bug 2929504.
3371 --
3372 per_per_bus.chk_person_type
3373 (p_person_type_id => l_person_type_id
3374 ,p_business_group_id => l_application.business_group_id
3375 ,p_expected_sys_type => 'EX_APL'
3376 );
3377 --
3378 hr_utility.set_location(l_proc,50);
3379 --
3380 --
3381 -- Lock person record
3382 --
3383 open csr_lock_person(p_person_id, l_effective_date);
3384 close csr_lock_person;
3385 --
3386 -- Lock the PTU records
3387 --
3388 open csr_lock_ptu(p_person_id, l_effective_date);
3389 close csr_lock_ptu;
3390 /*
3391 per_per_shd.lck
3392 (p_effective_date => l_effective_date + 1
3393 ,p_datetrack_mode => hr_api.g_update
3394 ,p_person_id => p_person_id
3395 ,p_object_version_number => p_object_version_number
3396 ,p_validation_start_date => l_validation_start_date
3397 ,p_validation_end_date => l_validation_end_date
3398 );
3399 */
3400 --
3401 hr_utility.set_location(l_proc,60);
3402 --
3403 -- Lock application record
3404 --
3405 per_apl_shd.lck
3406 (p_application_id => l_application.application_id
3407 ,p_object_version_number => l_application.object_version_number
3408 );
3409 --
3410 hr_utility.set_location(l_proc,70);
3411 --
3412 -- Terminate all applicant assignments for person
3413 --
3414 -- Remove future-dated assignments
3415 l_count := 0;
3416 FOR l_fut_asg in csr_get_future_apl_asg
3417 (cp_person_id => p_person_id
3418 ,cp_effective_date => l_effective_date
3419 ,cp_application_id => l_application.application_id)
3420 LOOP
3421 per_asg_del.del
3422 (p_assignment_id => l_fut_asg.assignment_id
3423 ,p_object_version_number => l_fut_asg.object_version_number
3424 ,p_effective_date => l_fut_asg.effective_start_date
3425 ,p_datetrack_mode => hr_api.g_zap
3426 ,p_effective_start_date => l_effective_start_date
3427 ,p_effective_end_date => l_effective_end_date
3428 ,p_business_group_id => l_business_group_id
3429 ,p_validation_start_date => l_validation_start_date
3430 ,p_validation_end_date => l_validation_end_date
3431 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3432 );
3433 l_count := l_count + 1;
3434 END LOOP;
3435 --
3436 FOR l_assignment IN csr_assignments
3437 (p_effective_date => l_effective_date
3438 ,p_person_id => p_person_id
3439 )
3440 LOOP
3441 if l_assignment.effective_end_date <> hr_api.g_eot then
3442 -- delete future DT updates
3443 per_asg_del.del
3444 (p_assignment_id => l_assignment.assignment_id
3445 ,p_object_version_number => l_assignment.object_version_number
3446 ,p_effective_date => l_effective_date
3447 ,p_datetrack_mode => hr_api.g_future_change
3448 ,p_effective_start_date => l_effective_start_date
3449 ,p_effective_end_date => l_effective_end_date
3450 ,p_business_group_id => l_business_group_id
3451 ,p_validation_start_date => l_validation_start_date
3452 ,p_validation_end_date => l_validation_end_date
3453 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3454 );
3455
3456 per_asg_del.del
3457 (p_assignment_id => l_assignment.assignment_id
3458 ,p_object_version_number => l_assignment.object_version_number
3459 ,p_effective_date => l_effective_date
3460 ,p_datetrack_mode => hr_api.g_delete
3461 ,p_effective_start_date => l_effective_start_date
3462 ,p_effective_end_date => l_effective_end_date
3463 ,p_business_group_id => l_business_group_id
3464 ,p_validation_start_date => l_validation_start_date
3465 ,p_validation_end_date => l_validation_end_date
3466 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3467 );
3468
3469 l_count := l_count + 1;
3470 else
3471 per_asg_del.del
3472 (p_assignment_id => l_assignment.assignment_id
3473 ,p_object_version_number => l_assignment.object_version_number
3474 ,p_effective_date => l_effective_date
3475 ,p_datetrack_mode => hr_api.g_delete
3476 ,p_effective_start_date => l_effective_start_date
3477 ,p_effective_end_date => l_effective_end_date
3478 ,p_business_group_id => l_business_group_id
3479 ,p_validation_start_date => l_validation_start_date
3480 ,p_validation_end_date => l_validation_end_date
3481 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3482 );
3483 end if;
3484 --
3485 l_legislation_code := per_asg_bus1.return_legislation_code
3486 (l_assignment.assignment_id);
3487 --
3488 if p_assignment_status_type_id is NULL then -- #3371944
3489 per_people3_pkg.get_default_person_type
3490 (p_required_type => 'TERM_APL'
3491 ,p_business_group_id => l_business_group_id
3492 ,p_legislation_code => l_legislation_code
3493 ,p_person_type => l_assignment_status_type_id
3494 );
3495 --
3496 -- #3371944 start
3497 else
3498 l_assignment_status_type_id := p_assignment_status_type_id;
3499 per_asg_bus1.chk_assignment_status_type
3500 (p_assignment_status_type_id => l_assignment_status_type_id
3501 ,p_business_group_id => l_business_group_id
3502 ,p_legislation_code => l_legislation_code
3503 ,p_expected_system_status => 'TERM_APL'
3504 );
3505 end if;
3506 -- #3371944 end
3507 --
3508
3509 --fix for bug 7229710 Starts here.
3510
3511 delete from per_letter_request_lines plrl
3512 where plrl.assignment_id = l_assignment.assignment_id
3513 and plrl.assignment_status_type_id = l_assignment.assignment_status_type_id
3514 and exists
3515 (select null
3516 from per_letter_requests plr
3517 where plr.letter_request_id = plrl.letter_request_id
3518 and plr.request_status = 'PENDING'
3519 and plr.auto_or_manual = 'AUTO');
3520
3521 per_app_asg_pkg.cleanup_letters
3522 (p_assignment_id => l_assignment.assignment_id);
3523 --
3524 -- Check if a letter request is necessary for the assignment.
3525 --
3526 open csr_vacancy_id(l_assignment.assignment_id);
3527 fetch csr_vacancy_id into l_vacancy_id;
3528 if csr_vacancy_id%NOTFOUND then null;
3529 end if;
3530 close csr_vacancy_id;
3531
3532 per_applicant_pkg.check_for_letter_requests
3533 (p_business_group_id => l_business_group_id
3534 ,p_per_system_status => null
3535 ,p_assignment_status_type_id => l_assignment_status_type_id
3536 ,p_person_id => p_person_id
3537 ,p_assignment_id => l_assignment.assignment_id
3538 ,p_effective_start_date => l_effective_start_date
3539 ,p_validation_start_date => l_validation_start_date
3540 ,p_vacancy_id => l_vacancy_id
3541 );
3542
3543
3544 --fix for bug 7229710 Ends here.
3545
3546 IRC_ASG_STATUS_API.create_irc_asg_status
3547 (p_assignment_id => l_assignment.assignment_id
3548 , p_assignment_status_type_id => l_assignment_status_type_id
3549 , p_status_change_date => p_effective_date -- CHANGE FOR THE BUG 5630218 l_effective_start_date --2754362 l_effective_end_date
3550 , p_status_change_reason => p_change_reason -- 4066579
3551 , p_assignment_status_id => l_assignment_status_id
3552 , p_object_version_number => l_asg_status_ovn);
3553 --
3554 -- Close the offers (if any) for this applicant
3555 --
3556 IRC_OFFERS_API.close_offer
3557 ( p_validate => p_validate
3558 ,p_effective_date => p_effective_date
3559 ,p_applicant_assignment_id => l_assignment.assignment_id
3560 ,p_change_reason => 'MANUAL_CLOSURE' --fix for bug 7540870.
3561 );
3562
3563 END LOOP;
3564 --
3565 if l_count > 0 then
3566 l_remove_future_asg_warning := TRUE;
3567 else
3568 l_remove_future_asg_warning := FALSE;
3569 end if;
3570 --
3571 hr_utility.set_location(l_proc,80);
3572 --
3573 -- Update person and ptu records
3574 --
3575 hr_applicant_internal.Update_PER_PTU_To_EX_APL
3576 (p_business_group_id => l_application.business_group_id
3577 ,p_person_id => p_person_id
3578 ,p_effective_date => l_effective_date+1 -- when becomes EX_APL
3579 ,p_person_type_id => l_person_type_id
3580 ,p_per_effective_start_date => l_effective_start_date
3581 ,p_per_effective_end_date => l_effective_end_date
3582 );
3583 --
3584 hr_utility.set_location(l_proc,90);
3585 --
3586 -- End the application
3587 --
3588 UPDATE per_applications
3589 set date_end = l_effective_date
3590 ,termination_reason = p_termination_reason
3591 WHERE application_id = l_application.application_id;
3592 /*
3593 this raises error when calling per_apl_bus.chk_date_end
3594 per_apl_upd.upd
3595 (p_application_id => l_application.application_id
3596 ,p_object_version_number => l_application.object_version_number
3597 ,p_effective_date => l_effective_date
3598 ,p_date_end => l_effective_date
3599 ,p_termination_reason => p_termination_reason
3600 );
3601 */
3602 --
3603 hr_utility.set_location(l_proc,100);
3604
3605 -- PTU : Added
3606 -- l_person_type_id1 :=
3607 -- hr_person_type_usage_info.get_default_person_type_id
3608 -- (l_business_group_id
3609 -- ,l_system_person_type
3610 -- );
3611 -- PTU : End of Changes
3612 -- Update person details
3613 --
3614 --
3615 --per_per_upd.upd
3616 -- (p_person_id => p_person_id
3617 -- ,p_object_version_number => p_object_version_number
3618 -- ,p_effective_date => l_effective_date + 1
3619 -- ,p_applicant_number => l_application.applicant_number
3620 -- ,p_employee_number => l_application.employee_number
3621 -- ,p_datetrack_mode => hr_api.g_update
3622 -- ,p_person_type_id => l_person_type_id1
3623 -- ,p_effective_start_date => l_effective_start_date
3624 -- ,p_effective_end_date => l_effective_end_date
3625 -- ,p_comment_id => l_comment_id
3626 -- ,p_current_applicant_flag => l_current_applicant_flag
3627 -- ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
3628 -- ,p_current_employee_flag => l_current_employee_flag
3629 -- ,p_full_name => l_full_name
3630 -- ,p_name_combination_warning => l_name_combination_warning
3631 -- ,p_dob_null_warning => l_dob_null_warning
3632 -- ,p_orig_hire_warning => l_orig_hire_warning
3633 -- ,p_npw_number => l_application.npw_number
3634 -- );
3635 --
3636 --
3637 -- Maintain person type usage records
3638 --
3639 -- PTU : Commented
3640
3641 -- hr_per_type_usage_internal.maintain_ptu
3642 -- (p_person_id => p_person_id
3643 -- ,p_action => 'TERM_APL'
3644 -- ,p_business_group_id => l_application.business_group_id
3645 -- ,p_actual_termination_date => l_effective_date
3646 -- );
3647 --
3648 -- PTU : Following Code has been added
3649 --
3650 -- hr_utility.set_location(l_proc,100);
3651 --
3652 -- hr_per_type_usage_internal.maintain_person_type_usage
3653 -- (p_effective_date => l_effective_date + 1
3654 -- ,p_person_id => p_person_id
3655 -- ,p_person_type_id => l_person_type_id
3656 -- );
3657
3658 -- PTU : End of changes
3659
3660 -- hr_utility.set_location(l_proc,110);
3661 --
3662 -- Call After Process User Hook
3663 --
3664 BEGIN
3665 hr_applicant_bk3.terminate_applicant_a
3666 (p_effective_date => l_effective_date
3667 ,p_person_id => p_person_id
3668 ,p_object_version_number => p_object_version_number
3669 ,p_person_type_id => p_person_type_id
3670 ,p_termination_reason => p_termination_reason
3671 ,p_effective_start_date => l_effective_start_date
3672 ,p_effective_end_date => l_effective_end_date
3673 ,p_remove_fut_asg_warning => l_remove_future_asg_warning
3674 );
3675 EXCEPTION
3676 WHEN hr_api.cannot_find_prog_unit
3677 THEN
3678 hr_api.cannot_find_prog_unit_error
3679 (p_module_name => 'TERMINATE_APPLICANT'
3680 ,p_hook_type => 'A'
3681 );
3682 END;
3683 --
3684 hr_utility.set_location(l_proc,120);
3685 --
3686 -- When in validation only mode raise validate enabled exception
3687 --
3688 IF p_validate
3689 THEN
3690 RAISE hr_api.validate_enabled;
3691 END IF;
3692 --
3693 -- Set OUT parameters
3694 --
3695 p_effective_start_date := l_effective_start_date;
3696 p_effective_end_date := l_effective_end_date;
3697 p_remove_fut_asg_warning := l_remove_future_asg_warning;
3698 --
3699 hr_utility.set_location(' Leaving:'||l_proc,1000);
3700 --
3701 EXCEPTION
3702 WHEN hr_api.validate_enabled
3703 THEN
3704 --
3705 -- In validation only mode
3706 -- Rollback to savepoint
3707 -- Set relevant output warning arguments
3708 -- Reset any key or derived arguments
3709 --
3710 ROLLBACK TO terminate_applicant;
3711 p_object_version_number := l_object_version_number;
3712 p_effective_start_date := NULL;
3713 p_effective_end_date := NULL;
3714 p_remove_fut_asg_warning := NULL;
3715 --
3716 WHEN OTHERS
3717 THEN
3718 --
3719 -- Validation or unexpected error occured
3720 -- Rollback to savepoint
3721 -- Re-raise exception
3722 --
3723 p_object_version_number := lv_object_version_number;
3724 p_effective_start_date := NULL;
3725 p_effective_end_date := NULL;
3726
3727 ROLLBACK TO terminate_applicant;
3728 RAISE;
3729 --
3730 END terminate_applicant;
3731 --
3732 -- OLD
3733 -- ----------------------------------------------------------------------------
3734 -- |-------------------------< convert_to_applicant >-------------------------|
3735 -- ----------------------------------------------------------------------------
3736 -- OLD
3737 PROCEDURE convert_to_applicant
3738 (p_validate IN BOOLEAN --DEFAULT FALSE
3739 ,p_effective_date IN DATE
3740 ,p_person_id IN per_all_people_f.person_id%TYPE
3741 ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3742 ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3743 ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3744 ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3745 ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3746 ) IS
3747 l_warning boolean;
3748 BEGIN
3749 hr_applicant_api.convert_to_applicant
3750 (p_validate => p_validate
3751 ,p_effective_date => p_effective_date
3752 ,p_person_id => p_person_id
3753 ,p_object_version_number => p_object_version_number
3754 ,p_applicant_number => p_applicant_number
3755 ,p_person_type_id => p_person_type_id
3756 ,p_effective_start_date => p_effective_start_date
3757 ,p_effective_end_date => p_effective_end_date
3758 ,p_appl_override_warning => l_warning
3759 );
3760 END;
3761 -- NEW
3762 -- ----------------------------------------------------------------------------
3763 -- |-------------------------< convert_to_applicant >-------------------------|
3764 -- ----------------------------------------------------------------------------
3765 -- NEW
3766 PROCEDURE convert_to_applicant
3767 (p_validate IN BOOLEAN --DEFAULT FALSE
3768 ,p_effective_date IN DATE
3769 ,p_person_id IN per_all_people_f.person_id%TYPE
3770 ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3771 ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3772 ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3773 ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3774 ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3775 ,p_appl_override_warning OUT NOCOPY boolean -- 3652025
3776 )
3777 IS
3778 --
3779 -- Local variables
3780 --
3781 l_proc VARCHAR2(72) := g_package||'convert_to_applicant';
3782 --
3783 l_effective_date DATE;
3784 --
3785 l_object_version_number CONSTANT per_all_assignments_f.object_version_number%TYPE := p_object_version_number;
3786 l_applicant_number CONSTANT per_all_people_f.applicant_number%TYPE := p_applicant_number;
3787 l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
3788 l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
3789 --
3790 l_person_type_id per_person_types.person_type_id%TYPE;
3791 l_person_type_id1 per_person_types.person_type_id%TYPE;
3792 --
3793 l_future_asgs_count INTEGER;
3794 l_system_person_type per_person_types.system_person_type%TYPE;
3795 l_effective_start_date DATE;
3796 l_effective_end_date DATE;
3797 l_comment_id hr_comments.comment_id%TYPE;
3798 l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3799 l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3800 l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3801 l_full_name per_all_people_f.full_name%TYPE;
3802 l_name_combination_warning BOOLEAN;
3803 l_dob_null_warning BOOLEAN;
3804 l_orig_hire_warning BOOLEAN;
3805 l_application_id per_applications.application_id%TYPE;
3806 l_apl_object_version_number per_applications.object_version_number%TYPE;
3807 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3808 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
3809 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
3810 l_person_type_usage_id per_person_type_usages.person_type_usage_id%TYPE;
3811 l_ptu_object_version_number per_person_type_usages.object_version_number%TYPE;
3812 --
3813 lv_object_version_number per_all_people_f.object_version_number%TYPE := p_object_version_number ;
3814 lv_applicant_number per_all_people_f.applicant_number%TYPE := p_applicant_number ;
3815 l_datetrack_mode varchar2(30); -- Bug 2738584
3816 --
3817 l_per_effective_start_date per_all_people_f.effective_end_date%TYPE;
3818 l_per_effective_end_date per_all_people_f.effective_start_date%TYPE;
3819 l_appl_override_warning boolean;
3820 --
3821 -- Local cursors
3822 --
3823 CURSOR csr_per_details
3824 (p_person_id IN per_all_people_f.person_id%TYPE
3825 ,p_effective_date IN DATE
3826 )
3827 IS
3828 SELECT pet.person_type_id
3829 ,pet.system_person_type
3830 ,per.effective_start_date
3831 ,per.effective_end_date
3832 ,per.applicant_number
3833 ,per.employee_number
3834 ,per.npw_number
3835 ,bus.business_group_id
3836 ,bus.legislation_code
3837 FROM per_people_f per
3838 ,per_business_groups bus
3839 ,per_person_types pet
3840 WHERE per.person_type_id = pet.person_type_id
3841 AND per.business_group_id+0 = bus.business_group_id
3842 AND per.person_id = csr_per_details.p_person_id
3843 AND csr_per_details.p_effective_date BETWEEN per.effective_start_date
3844 AND per.effective_end_date;
3845 l_per_details_rec csr_per_details%ROWTYPE;
3846 --
3847 BEGIN
3848 --
3849 hr_utility.set_location('Entering:'||l_proc,10);
3850 --
3851 -- Ensure mandatory arguments have been passed
3852 --
3853 hr_api.mandatory_arg_error
3854 (p_api_name => l_proc
3855 ,p_argument => 'person_id'
3856 ,p_argument_value => p_person_id
3857 );
3858 --
3859 hr_api.mandatory_arg_error
3860 (p_api_name => l_proc
3861 ,p_argument => 'effective_date'
3862 ,p_argument_value => p_effective_date
3863 );
3864 --
3865 -- Truncate all date parameters passed in
3866 --
3867 l_effective_date := TRUNC(p_effective_date);
3868 --
3869 -- Issue savepoint
3870 --
3871 SAVEPOINT convert_to_applicant;
3872 --
3873 hr_utility.set_location(l_proc,20);
3874 --
3875 -- Get dervied details for person on effective date
3876 --
3877 OPEN csr_per_details
3878 (p_person_id => p_person_id
3879 ,p_effective_date => p_effective_date
3880 );
3881 FETCH csr_per_details INTO l_per_details_rec;
3882 IF csr_per_details%NOTFOUND
3883 THEN
3884 CLOSE csr_per_details;
3885 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
3886 hr_utility.raise_error;
3887 END IF;
3888 CLOSE csr_per_details;
3889 --
3890 hr_utility.set_location(l_proc,30);
3891 --
3892 -- Call Before Process User Hook
3893 --
3894 BEGIN
3895 hr_applicant_bk4.convert_to_applicant_b
3896 (p_effective_date => l_effective_date
3897 ,p_person_id => p_person_id
3898 ,p_business_group_id => l_per_details_rec.business_group_id
3899 ,p_object_version_number => p_object_version_number
3900 ,p_applicant_number => p_applicant_number
3901 ,p_person_type_id => p_person_type_id
3902 );
3903 EXCEPTION
3904 WHEN hr_api.cannot_find_prog_unit
3905 THEN
3906 hr_api.cannot_find_prog_unit_error
3907 (p_module_name => 'APPLY_FOR_VACANCY'
3908 ,p_hook_type => 'BP'
3909 );
3910 END;
3911 --
3912 -- hr_utility.set_location(l_proc,40);
3913 --
3914 -- Check the person is of a correct system person type
3915 --
3916 IF l_per_details_rec.system_person_type NOT IN ('EX_APL','EX_EMP','OTHER')
3917 THEN
3918 hr_utility.set_location(l_proc,50);
3919 hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
3920 hr_utility.raise_error;
3921 END IF;
3922 --
3923 hr_utility.set_location(l_proc,60);
3924 --
3925 -- Ensure the applicant number will not be changed if it exists
3926 --
3927 IF l_per_details_rec.applicant_number IS NOT NULL
3928 AND NVL(p_applicant_number,hr_api.g_number) <> l_per_details_rec.applicant_number
3929 THEN
3930 hr_utility.set_location(l_proc,70);
3931 p_applicant_number := l_per_details_rec.applicant_number;
3932 END IF;
3933 --
3934 hr_utility.set_location(l_proc,80);
3935 --
3936 -- 3652025: Create an applicant, generate the application and
3937 -- the applicant assignment
3938 --
3939 hr_applicant_internal.create_applicant_anytime
3940 (p_effective_date => l_effective_date
3941 ,p_person_id => p_person_id
3942 ,p_applicant_number => p_applicant_number
3943 ,p_per_object_version_number => p_object_version_number
3944 ,p_vacancy_id => NULL
3945 ,p_person_type_id => p_person_type_id
3946 ,p_assignment_status_type_id => NULL
3947 ,p_application_id => l_application_id
3948 ,p_assignment_id => l_assignment_id
3949 ,p_apl_object_version_number => l_apl_object_version_number
3950 ,p_asg_object_version_number => l_asg_object_version_number
3951 ,p_assignment_sequence => l_assignment_sequence
3952 ,p_per_effective_start_date => l_effective_start_date
3953 ,p_per_effective_end_date => l_effective_end_date
3954 ,p_appl_override_warning => l_appl_override_warning);
3955 --
3956 hr_utility.set_location(l_proc,90);
3957 --
3958 -- Update the security lists
3959 --
3960 hr_security_internal.populate_new_person(l_per_details_rec.business_group_id,p_person_id);
3961 --
3962 hr_utility.set_location(l_proc,100);
3963 --
3964 -- Call After Process User Hook
3965 --
3966 BEGIN
3967 hr_applicant_bk4.convert_to_applicant_a
3968 (p_effective_date => l_effective_date
3969 ,p_person_id => p_person_id
3970 ,p_business_group_id => l_per_details_rec.business_group_id
3971 ,p_object_version_number => p_object_version_number
3972 ,p_applicant_number => p_applicant_number
3973 ,p_person_type_id => p_person_type_id
3974 ,p_effective_start_date => l_effective_start_date
3975 ,p_effective_end_date => l_effective_end_date
3976 ,p_appl_override_warning => l_appl_override_warning
3977 );
3978 EXCEPTION
3979 WHEN hr_api.cannot_find_prog_unit
3980 THEN
3981 hr_api.cannot_find_prog_unit_error
3982 (p_module_name => 'APPLY_FOR_VACANCY'
3983 ,p_hook_type => 'AP'
3984 );
3985 END;
3986 --
3987 hr_utility.set_location(l_proc,180);
3988 --
3989 -- When in validation only mode raise validate_enabled exception
3990 --
3991 IF p_validate
3992 THEN
3993 RAISE hr_api.validate_enabled;
3994 END IF;
3995 --
3996 -- Set OUT parameters
3997 --
3998 p_effective_start_date := l_effective_start_date;
3999 p_effective_end_date := l_effective_end_date;
4000 p_appl_override_warning := l_appl_override_warning;
4001 --
4002 hr_utility.set_location(' Leaving:'||l_proc,1000);
4003 --
4004 EXCEPTION
4005 --
4006 WHEN hr_api.validate_enabled
4007 THEN
4008 --
4009 -- In validation only mode
4010 -- Rollback to savepoint
4011 -- Set relevant output warning arguments
4012 -- Reset any key or derived arguments
4013 --
4014 ROLLBACK TO convert_to_applicant;
4015 p_object_version_number := l_object_version_number;
4016 p_applicant_number := l_applicant_number;
4017 p_effective_start_date := NULL;
4018 p_effective_end_date := NULL;
4019 p_appl_override_warning := NULL;
4020 --
4021 WHEN OTHERS
4022 THEN
4023 --
4024 -- Validation or unexpected error occured
4025 -- Ensure opened non-local cursors are closed
4026 -- Rollback to savepoint
4027 -- Re-raise exception
4028 --
4029 p_object_version_number := lv_object_version_number;
4030 p_applicant_number := lv_applicant_number;
4031 p_effective_start_date := NULL;
4032 p_effective_end_date := NULL;
4033 p_appl_override_warning := NULL;
4034
4035 ROLLBACK TO convert_to_applicant;
4036 IF csr_per_details%ISOPEN
4037 THEN
4038 CLOSE csr_per_details;
4039 END IF;
4040 RAISE;
4041 --
4042 END convert_to_applicant;
4043 --
4044 --
4045 FUNCTION override_future_applications
4046 (p_person_id IN NUMBER
4047 ,p_effective_date IN DATE
4048 )
4049 RETURN VARCHAR2 IS
4050
4051 cursor csr_fut_apl is
4052 select application_id
4053 from per_applications
4054 where person_id = p_person_id
4055 and date_received > p_effective_date
4056 order by date_received asc;
4057
4058 cursor csr_current_apl is
4059 select application_id
4060 from per_applications
4061 where person_id = p_person_id
4062 and date_received < p_effective_date
4063 and nvl(date_end,hr_api.g_eot) >= p_effective_date;
4064
4065 cursor csr_apl_yesterday is
4066 select application_id
4067 from per_applications
4068 where person_id = p_person_id
4069 and date_end = p_effective_date-1;
4070
4071 l_future_apl_id per_applications.application_id%type;
4072 l_current_apl_id per_applications.application_id%type;
4073 l_yesterday_apl_id per_applications.application_id%type;
4074 l_raise_warning VARCHAR2(10);
4075
4076 BEGIN
4077 l_raise_warning := 'N';
4078 open csr_fut_apl;
4079 fetch csr_fut_apl into l_future_apl_id;
4080 if csr_fut_apl%found then
4081 open csr_current_apl;
4082 fetch csr_current_apl into l_current_apl_id;
4083 if csr_current_apl%notfound then --yes future, no current
4084 close csr_current_apl;
4085 fetch csr_fut_apl INTO l_future_apl_id;
4086 IF csr_fut_apl%FOUND then
4087 l_raise_warning := 'Y';
4088 end if;
4089 else --yes future, yes current
4090 close csr_current_apl;
4091 l_raise_warning := 'Y';
4092 END IF;
4093 end if;
4094 close csr_fut_apl;
4095
4096 RETURN l_raise_warning;
4097 --
4098 END override_future_applications;
4099 --
4100 -- ----------------------------------------------------------------------------
4101 -- |-----------------< apply_for_job_anytime >-------------------------|
4102 -- ----------------------------------------------------------------------------
4103 procedure apply_for_job_anytime
4104 (p_validate in boolean
4105 ,p_effective_date in date
4106 ,p_person_id in number
4107 ,p_applicant_number in out nocopy varchar2
4108 ,p_per_object_version_number in out nocopy number
4109 ,p_vacancy_id in number
4110 ,p_person_type_id in number
4111 ,p_assignment_status_type_id in number
4112 ,p_application_id out nocopy number
4113 ,p_assignment_id out nocopy number
4114 ,p_apl_object_version_number out nocopy number
4115 ,p_asg_object_version_number out nocopy number
4116 ,p_assignment_sequence out nocopy number
4117 ,p_per_effective_start_date out nocopy date
4118 ,p_per_effective_end_date out nocopy date
4119 ,p_appl_override_warning out nocopy boolean
4120 ) is
4121 --
4122 -- declare local variables
4123 --
4124 l_proc varchar2(72) := g_package||'apply_for_job_anytime';
4125 l_business_group_id per_people_f.business_group_id%type;
4126 l_name_combination_warning boolean;
4127 l_dob_null_warning boolean;
4128 l_orig_hire_warning boolean;
4129 l_organization_id per_business_groups.organization_id%type;
4130 l_legislation_code per_business_groups.legislation_code%type;
4131 l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
4132 l_person_type_id1 per_people_f.person_type_id%type;
4133 l_application_id per_applications.application_id%type;
4134 l_comment_id per_assignments_f.comment_id%type;
4135 l_assignment_sequence per_assignments_f.assignment_sequence%type;
4136 l_assignment_id per_assignments_f.assignment_id%type;
4137 l_object_version_number per_assignments_f.object_version_number%type;
4138 l_current_applicant_flag per_people_f.current_applicant_flag%type;
4139 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
4140 l_current_employee_flag per_people_f.current_employee_flag%type;
4141 l_employee_number per_people_f.employee_number%type;
4142 l_applicant_number per_people_f.applicant_number%TYPE;
4143 l_npw_number per_people_f.npw_number%TYPE;
4144 l_per_object_version_number per_people_f.object_version_number%TYPE;
4145 l_full_name per_people_f.full_name%type;
4146 l_system_person_type per_person_types.system_person_type%type;
4147 l_effective_date date;
4148 l_effective_start_date date;
4149 l_effective_end_date date;
4150 l_default_start_time per_business_groups.default_start_time%type;
4151 l_default_end_time per_business_groups.default_end_time%type;
4152 l_normal_hours number;
4153 l_frequency per_business_groups.frequency%type;
4154 l_recruiter_id per_vacancies.recruiter_id%type;
4155 l_grade_id per_vacancies.grade_id%type;
4156 l_position_id per_vacancies.position_id%type;
4157 l_job_id per_vacancies.job_id%type;
4158 l_location_id per_vacancies.location_id%type;
4159 l_people_group_id per_vacancies.people_group_id%type;
4160 l_vac_organization_id per_vacancies.organization_id%type;
4161 l_vac_business_group_id per_vacancies.business_group_id%type;
4162 l_group_name pay_people_groups.group_name%type;
4163 l_appl_override_warning boolean;
4164 --
4165 -- Local variable added for the before and after business process
4166 -- apply_for_job_anytime
4167 --
4168 l_apl_object_version_number per_applications.object_version_number%TYPE;
4169 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
4170 l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
4171 l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
4172 --
4173 --
4174 -- select and validate the person
4175 --
4176 cursor csr_chk_person_exists is
4177 select ppf.business_group_id
4178 ,ppf.employee_number
4179 ,ppf.npw_number
4180 ,ppt.system_person_type
4181 from per_person_types ppt
4182 ,per_people_f ppf
4183 where ppf.person_id = p_person_id
4184 and ppt.person_type_id = ppf.person_type_id
4185 and ppt.business_group_id + 0 = ppf.business_group_id
4186 and (l_effective_date
4187 between ppf.effective_start_date
4188 and ppf.effective_end_date or ppf.effective_start_date > l_effective_date);
4189 --
4190 --
4191 begin
4192 --
4193 -- Issue a savepoint if operating in validation only mode.
4194 --
4195 if p_validate then
4196 savepoint apply_for_job_anytime;
4197 end if;
4198 --
4199 if g_debug then
4200 hr_utility.set_location('Entering:'|| l_proc, 5);
4201 end if;
4202 --
4203 -- Truncate p_effective_date
4204 --
4205 l_effective_date := trunc(p_effective_date);
4206 -- Initialise local variables
4207 --
4208 l_applicant_number := p_applicant_number;
4209 l_per_object_version_number := p_per_object_version_number;
4210 --
4211 --
4212 -- Validation Logic
4213 --
4214 -- Ensure that the mandatory parameter, p_person_id
4215 -- is not null
4216 --
4217 hr_api.mandatory_arg_error
4218 (p_api_name => l_proc
4219 ,p_argument => 'person id'
4220 ,p_argument_value => p_person_id);
4221 --
4222 if g_debug then
4223 hr_utility.set_location(' Leaving:'||l_proc, 10);
4224 end if;
4225 --
4226 -- Check that this person (p_person_id) exists as of l_effective_date
4227 -- the current person type (per_people_f.person_type_id)
4228 --
4229 open csr_chk_person_exists;
4230 fetch csr_chk_person_exists into
4231 l_business_group_id
4232 ,l_employee_number
4233 ,l_npw_number
4234 ,l_system_person_type;
4235 if csr_chk_person_exists%notfound then
4236 close csr_chk_person_exists;
4237 hr_utility.set_message(800, 'HR_51011_PER_NOT_EXIST_DATE');
4238 hr_utility.raise_error;
4239 end if;
4240 close csr_chk_person_exists;
4241 --
4242 --
4243 if g_debug then
4244 hr_utility.set_location(l_proc, 15);
4245 end if;
4246
4247 --
4248 -- Initialise local variables
4249 --
4250 l_applicant_number := p_applicant_number;
4251 l_per_object_version_number := p_per_object_version_number;
4252 --
4253 begin
4254 --
4255 -- Start of call API User Hook for the before hook of apply_for_job_anytime_b
4256 --
4257 hr_applicant_bk5.apply_for_job_anytime_b
4258 (
4259 p_business_group_id => l_business_group_id
4260 ,p_effective_date => l_effective_date
4261 ,p_person_id => p_person_id
4262 ,p_applicant_number => p_applicant_number
4263 ,p_per_object_version_number => p_per_object_version_number
4264 ,p_vacancy_id => p_vacancy_id
4265 ,p_person_type_id => p_person_type_id
4266 ,p_assignment_status_type_id => p_assignment_status_type_id
4267 );
4268 exception
4269 when hr_api.cannot_find_prog_unit then
4270 hr_api.cannot_find_prog_unit_error
4271 (p_module_name => 'apply_for_job_anytime'
4272 ,p_hook_type => 'BP'
4273 );
4274 --
4275 -- End of API User Hook for the before hook of apply_for_job_anytime
4276 --
4277 end;
4278 -- processing logic
4279 --
4280 if g_debug then
4281 hr_utility.set_location(l_proc, 20);
4282 end if;
4283 --
4284
4285 -- Validate that the person_type_id passed is a flavour of 'APL' or derive the default
4286 --
4287 per_per_bus.chk_person_type
4288 (p_person_type_id => l_person_type_id,
4289 p_business_group_id => l_business_group_id,
4290 p_expected_sys_type => 'APL');
4291 --
4292 if g_debug then
4293 hr_utility.set_location(l_proc, 25);
4294 end if;
4295 --
4296 hr_applicant_internal.create_applicant_anytime
4297 (p_effective_date => l_effective_date
4298 ,p_person_id => p_person_id
4299 ,p_applicant_number => l_applicant_number
4300 ,p_per_object_version_number => l_per_object_version_number
4301 ,p_vacancy_id => p_vacancy_id
4302 ,p_person_type_id => p_person_type_id
4303 ,p_assignment_status_type_id => p_assignment_status_type_id
4304 ,p_application_id => l_application_id
4305 ,p_assignment_id => l_assignment_id
4306 ,p_apl_object_version_number => l_apl_object_version_number
4307 ,p_asg_object_version_number => l_object_version_number
4308 ,p_assignment_sequence => l_assignment_sequence
4309 ,p_per_effective_start_date => l_per_effective_start_date
4310 ,p_per_effective_end_date => l_per_effective_end_date
4311 ,p_appl_override_warning => l_appl_override_warning);
4312 --
4313 hr_security_internal.add_to_person_list(l_effective_date,l_assignment_id);
4314 --
4315 if g_debug then
4316 hr_utility.set_location(l_proc, 30);
4317 end if;
4318 --
4319 --
4320 begin
4321 --
4322 -- Start of call API User Hook for the after hook of apply_for_anytime_a
4323 --
4324 hr_applicant_bk5.apply_for_job_anytime_a
4325 (
4326 p_business_group_id => l_business_group_id
4327 ,p_effective_date => l_effective_date
4328 ,p_person_id => p_person_id
4329 ,p_applicant_number => p_applicant_number
4330 ,p_per_object_version_number => p_per_object_version_number
4331 ,p_vacancy_id => p_vacancy_id
4332 ,p_person_type_id => p_person_type_id
4333 ,p_assignment_status_type_id => p_assignment_status_type_id
4334 ,p_application_id => l_application_id
4335 ,p_assignment_id => l_assignment_id
4336 ,p_apl_object_version_number => l_apl_object_version_number
4337 ,p_asg_object_version_number => l_asg_object_version_number
4338 ,p_assignment_sequence => l_assignment_sequence
4339 ,p_per_effective_start_date => l_per_effective_start_date
4340 ,p_per_effective_end_date => l_per_effective_end_date
4341 ,p_appl_override_warning => l_appl_override_warning
4342 );
4343 --
4344 exception
4345 when hr_api.cannot_find_prog_unit then
4346 hr_api.cannot_find_prog_unit_error
4347 (p_module_name => 'apply_for_job_anytime'
4348 ,p_hook_type => 'AP'
4349 );
4350 --
4351 -- End of API User Hook for the after hook of apply_for_job_anytime_a
4352 --
4353 end;
4354 --
4355 -- Set all output arguments
4356 --
4357 -- fix for bug 7172879 start here.
4358 p_applicant_number := l_applicant_number;
4359 p_per_object_version_number := l_per_object_version_number;
4360 -- fix for bug 7172879 ends here.
4361 p_application_id := l_application_id;
4362 p_assignment_id := l_assignment_id;
4363 p_apl_object_version_number := l_apl_object_version_number;
4364 p_asg_object_version_number := l_object_version_number; -- l_asg_object_version_number ,fix for bug 7172879
4365 p_assignment_sequence := l_assignment_sequence;
4366 p_per_effective_start_date := l_per_effective_start_date;
4367 p_per_effective_end_date := l_per_effective_end_date;
4368 p_appl_override_warning := l_appl_override_warning;
4369 --
4370 -- when in validation only mode raise the Validate_Enabled exception
4371 --
4372
4373 if p_validate then
4374 raise hr_api.validate_enabled;
4375 end if;
4376 --
4377 if g_debug then
4378 hr_utility.set_location(' Leaving:'||l_proc, 50);
4379 end if;
4380 exception
4381 when hr_api.validate_enabled then
4382 --
4383 -- As the Validate_Enabled exception has been raised
4384 -- we must rollback to the savepoint
4385 --
4386 ROLLBACK TO apply_for_job_anytime;
4387 --
4388 -- Only set output warning arguments
4389 -- (Any key or derived arguments must be set to null
4390 -- when validation only mode is being used.)
4391 --
4392
4393 p_per_object_version_number := l_per_object_version_number;
4394 p_applicant_number := l_applicant_number;
4395 p_application_id := null;
4396 p_assignment_id := null;
4397 p_apl_object_version_number := null;
4398 p_asg_object_version_number := null;
4399 p_assignment_sequence := null;
4400 p_per_effective_start_date := null;
4401 p_per_effective_end_date := null;
4402 p_appl_override_warning := null;
4403 --
4404 if g_debug then
4405 hr_utility.set_location(' Leaving:'||l_proc, 55);
4406 end if;
4407 --
4408 end apply_for_job_anytime;
4409 --
4410 --
4411 end hr_applicant_api;