[Home] [Help]
PACKAGE BODY: APPS.HR_CONTINGENT_WORKER_API
Source
1 Package Body hr_contingent_worker_api as
2 /* $Header: pecwkapi.pkb 120.4.12010000.1 2008/07/28 04:28:10 appldev ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_contingent_worker_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------------< create_cwk >------------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_cwk
13 (p_validate in boolean default false
14 ,p_start_date in date
15 ,p_business_group_id in number
16 ,p_last_name in varchar2
17 ,p_person_type_id in number default null
18 ,p_npw_number in out nocopy varchar2
19 ,p_background_check_status in varchar2 default null
20 ,p_background_date_check in date default null
21 ,p_blood_type in varchar2 default null
22 ,p_comments in varchar2 default null
23 ,p_correspondence_language in varchar2 default null
24 ,p_country_of_birth in varchar2 default null
25 ,p_date_of_birth in date default null
26 ,p_date_of_death in date default null
27 ,p_dpdnt_adoption_date in date default null
28 ,p_dpdnt_vlntry_svce_flag in varchar2 default null
29 ,p_email_address in varchar2 default null
30 ,p_first_name in varchar2 default null
31 ,p_fte_capacity in number default null
32 ,p_honors in varchar2 default null
33 ,p_internal_location in varchar2 default null
34 ,p_known_as in varchar2 default null
35 ,p_last_medical_test_by in varchar2 default null
36 ,p_last_medical_test_date in date default null
37 ,p_mailstop in varchar2 default null
38 ,p_marital_status in varchar2 default null
39 ,p_middle_names in varchar2 default null
40 ,p_national_identifier in varchar2 default null
41 ,p_nationality in varchar2 default null
42 ,p_office_number in varchar2 default null
43 ,p_on_military_service in varchar2 default null
44 ,p_party_id in number default null
45 ,p_pre_name_adjunct in varchar2 default null
46 ,p_previous_last_name in varchar2 default null
47 ,p_projected_placement_end in date default null
48 ,p_receipt_of_death_cert_date in date default null
49 ,p_region_of_birth in varchar2 default null
50 ,p_registered_disabled_flag in varchar2 default null
51 ,p_resume_exists in varchar2 default null
52 ,p_resume_last_updated in date default null
53 ,p_second_passport_exists in varchar2 default null
54 ,p_sex in varchar2 default null
55 ,p_student_status in varchar2 default null
56 ,p_suffix in varchar2 default null
57 ,p_title in varchar2 default null
58 ,p_town_of_birth in varchar2 default null
59 ,p_uses_tobacco_flag in varchar2 default null
60 ,p_vendor_id in number default null
61 ,p_work_schedule in varchar2 default null
62 ,p_work_telephone in varchar2 default null
63 ,p_exp_check_send_to_address in varchar2 default null
64 ,p_hold_applicant_date_until in date default null
65 ,p_date_employee_data_verified in date default null
66 ,p_benefit_group_id in number default null
67 ,p_coord_ben_med_pln_no in varchar2 default null
68 ,p_coord_ben_no_cvg_flag in varchar2 default null
69 ,p_original_date_of_hire in date default null
70 ,p_attribute_category in varchar2 default null
71 ,p_attribute1 in varchar2 default null
72 ,p_attribute2 in varchar2 default null
73 ,p_attribute3 in varchar2 default null
74 ,p_attribute4 in varchar2 default null
75 ,p_attribute5 in varchar2 default null
76 ,p_attribute6 in varchar2 default null
77 ,p_attribute7 in varchar2 default null
78 ,p_attribute8 in varchar2 default null
79 ,p_attribute9 in varchar2 default null
80 ,p_attribute10 in varchar2 default null
81 ,p_attribute11 in varchar2 default null
82 ,p_attribute12 in varchar2 default null
83 ,p_attribute13 in varchar2 default null
84 ,p_attribute14 in varchar2 default null
85 ,p_attribute15 in varchar2 default null
86 ,p_attribute16 in varchar2 default null
87 ,p_attribute17 in varchar2 default null
88 ,p_attribute18 in varchar2 default null
89 ,p_attribute19 in varchar2 default null
90 ,p_attribute20 in varchar2 default null
91 ,p_attribute21 in varchar2 default null
92 ,p_attribute22 in varchar2 default null
93 ,p_attribute23 in varchar2 default null
94 ,p_attribute24 in varchar2 default null
95 ,p_attribute25 in varchar2 default null
96 ,p_attribute26 in varchar2 default null
97 ,p_attribute27 in varchar2 default null
98 ,p_attribute28 in varchar2 default null
99 ,p_attribute29 in varchar2 default null
100 ,p_attribute30 in varchar2 default null
101 ,p_per_information_category in varchar2 default null
102 ,p_per_information1 in varchar2 default null
103 ,p_per_information2 in varchar2 default null
104 ,p_per_information3 in varchar2 default null
105 ,p_per_information4 in varchar2 default null
106 ,p_per_information5 in varchar2 default null
107 ,p_per_information6 in varchar2 default null
108 ,p_per_information7 in varchar2 default null
109 ,p_per_information8 in varchar2 default null
110 ,p_per_information9 in varchar2 default null
111 ,p_per_information10 in varchar2 default null
112 ,p_per_information11 in varchar2 default null
113 ,p_per_information12 in varchar2 default null
114 ,p_per_information13 in varchar2 default null
115 ,p_per_information14 in varchar2 default null
116 ,p_per_information15 in varchar2 default null
117 ,p_per_information16 in varchar2 default null
118 ,p_per_information17 in varchar2 default null
119 ,p_per_information18 in varchar2 default null
120 ,p_per_information19 in varchar2 default null
121 ,p_per_information20 in varchar2 default null
122 ,p_per_information21 in varchar2 default null
123 ,p_per_information22 in varchar2 default null
124 ,p_per_information23 in varchar2 default null
125 ,p_per_information24 in varchar2 default null
126 ,p_per_information25 in varchar2 default null
127 ,p_per_information26 in varchar2 default null
128 ,p_per_information27 in varchar2 default null
129 ,p_per_information28 in varchar2 default null
130 ,p_per_information29 in varchar2 default null
131 ,p_per_information30 in varchar2 default null
132 ,p_person_id out nocopy number
133 ,p_per_object_version_number out nocopy number
134 ,p_per_effective_start_date out nocopy date
135 ,p_per_effective_end_date out nocopy date
136 ,p_pdp_object_version_number out nocopy number
137 ,p_full_name out nocopy varchar2
138 ,p_comment_id out nocopy number
139 ,p_assignment_id out nocopy number
140 ,p_asg_object_version_number out nocopy number
141 ,p_assignment_sequence out nocopy number
142 ,p_assignment_number out nocopy varchar2
143 ,p_name_combination_warning out nocopy boolean
144 ) is
145 --
146 -- Declare cursors and local variables
147 --
148
149 l_proc varchar2(72) := g_package||'create_cwk';
150 l_person_id number;
151 l_person_type_id per_person_types.person_type_id%type := p_person_type_id;
152 l_person_type_id1 per_person_types.person_type_id%type;
153 l_start_date date;
154 l_date_of_birth date;
155 l_date_of_death date;
156 l_receipt_of_death_cert_date date;
157 l_dpdnt_adoption_date date;
158 l_per_object_version_number number;
159 l_per_effective_start_date date;
160 l_per_effective_end_date date;
161 l_current_npw_flag per_people_f.current_npw_flag%type;
162 l_current_applicant_flag per_people_f.current_applicant_flag%type;
163 l_current_employee_flag per_people_f.current_employee_flag%type;
164 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
165 l_employee_number per_people_f.employee_number%TYPE;
166 l_applicant_number per_people_f.applicant_number%TYPE;
167 l_npw_number per_people_f.npw_number%TYPE;
168 l_full_name per_people_f.full_name%type;
169 l_comment_id number;
170 l_dob_null_warning boolean;
171 l_orig_hire_warning boolean;
172 l_assignment_id number;
173 l_asg_object_version_number number;
174 l_assignment_sequence per_assignments_f.assignment_sequence%type;
175 l_assignment_number per_assignments_f.assignment_number%type;
176 l_name_combination_warning boolean;
177 l_period_of_placement_id number;
178 l_pdp_object_version_number number;
179 l_phn_object_version_number number;
180 l_phone_id number;
181 --
182 begin
183
184 hr_utility.set_location('Entering:'|| l_proc, 10);
185
186 --
187 -- Issue a savepoint
188 --
189 savepoint create_cwk;
190 --
191 -- Truncate the time portion from all IN date parameters
192 --
193 l_start_date := trunc(p_start_date);
194 l_date_of_birth := trunc(p_date_of_birth);
195 l_date_of_death := trunc(p_date_of_death);
196 l_receipt_of_death_cert_date := trunc(p_receipt_of_death_cert_date);
197 l_dpdnt_adoption_date := trunc(p_dpdnt_adoption_date);
198 --
199 -- Call Before Process User Hook
200 --
201 begin
202 hr_contingent_worker_bk1.create_cwk_b
203 (p_start_date => l_start_date
204 ,p_business_group_id => p_business_group_id
205 ,p_last_name => p_last_name
206 ,p_person_type_id => p_person_type_id
207 ,p_npw_number => p_npw_number
208 ,p_background_check_status => p_background_check_status
209 ,p_background_date_check => p_background_date_check
210 ,p_blood_type => p_blood_type
211 ,p_comments => p_comments
212 ,p_correspondence_language => p_correspondence_language
213 ,p_country_of_birth => p_country_of_birth
214 ,p_date_of_birth => l_date_of_birth
215 ,p_date_of_death => l_date_of_death
216 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
217 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
218 ,p_email_address => p_email_address
219 ,p_first_name => p_first_name
220 ,p_fte_capacity => p_fte_capacity
221 ,p_honors => p_honors
222 ,p_internal_location => p_internal_location
223 ,p_known_as => p_known_as
224 ,p_last_medical_test_by => p_last_medical_test_by
225 ,p_last_medical_test_date => p_last_medical_test_date
226 ,p_mailstop => p_mailstop
227 ,p_marital_status => p_marital_status
228 ,p_middle_names => p_middle_names
229 ,p_national_identifier => p_national_identifier
230 ,p_nationality => p_nationality
231 ,p_office_number => p_office_number
232 ,p_on_military_service => p_on_military_service
233 ,p_party_id => p_party_id
234 ,p_pre_name_adjunct => p_pre_name_adjunct
235 ,p_previous_last_name => p_previous_last_name
236 ,p_projected_placement_end => null
237 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
238 ,p_region_of_birth => p_region_of_birth
239 ,p_registered_disabled_flag => p_registered_disabled_flag
240 ,p_resume_exists => p_resume_exists
241 ,p_resume_last_updated => p_resume_last_updated
242 ,p_second_passport_exists => p_second_passport_exists
243 ,p_sex => p_sex
244 ,p_student_status => p_student_status
245 ,p_suffix => p_suffix
246 ,p_title => p_title
247 ,p_town_of_birth => p_town_of_birth
248 ,p_uses_tobacco_flag => p_uses_tobacco_flag
249 ,p_vendor_id => p_vendor_id
250 ,p_work_schedule => p_work_schedule
251 ,p_work_telephone => p_work_telephone
252 ,p_exp_check_send_to_address => p_exp_check_send_to_address
253 ,p_hold_applicant_date_until => p_hold_applicant_date_until
254 ,p_date_employee_data_verified => p_date_employee_data_verified
255 ,p_benefit_group_id => p_benefit_group_id
256 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
257 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
258 ,p_original_date_of_hire => p_original_date_of_hire
259 ,p_attribute_category => p_attribute_category
260 ,p_attribute1 => p_attribute1
261 ,p_attribute2 => p_attribute2
262 ,p_attribute3 => p_attribute3
263 ,p_attribute4 => p_attribute4
264 ,p_attribute5 => p_attribute5
265 ,p_attribute6 => p_attribute6
266 ,p_attribute7 => p_attribute7
267 ,p_attribute8 => p_attribute8
268 ,p_attribute9 => p_attribute9
269 ,p_attribute10 => p_attribute10
270 ,p_attribute11 => p_attribute11
271 ,p_attribute12 => p_attribute12
272 ,p_attribute13 => p_attribute13
273 ,p_attribute14 => p_attribute14
274 ,p_attribute15 => p_attribute15
275 ,p_attribute16 => p_attribute16
276 ,p_attribute17 => p_attribute17
277 ,p_attribute18 => p_attribute18
278 ,p_attribute19 => p_attribute19
279 ,p_attribute20 => p_attribute20
280 ,p_attribute21 => p_attribute21
281 ,p_attribute22 => p_attribute22
282 ,p_attribute23 => p_attribute23
283 ,p_attribute24 => p_attribute24
284 ,p_attribute25 => p_attribute25
285 ,p_attribute26 => p_attribute26
286 ,p_attribute27 => p_attribute27
287 ,p_attribute28 => p_attribute28
288 ,p_attribute29 => p_attribute29
289 ,p_attribute30 => p_attribute30
290 ,p_per_information_category => p_per_information_category
291 ,p_per_information1 => p_per_information1
292 ,p_per_information2 => p_per_information2
293 ,p_per_information3 => p_per_information3
294 ,p_per_information4 => p_per_information4
295 ,p_per_information5 => p_per_information5
296 ,p_per_information6 => p_per_information6
297 ,p_per_information7 => p_per_information7
298 ,p_per_information8 => p_per_information8
299 ,p_per_information9 => p_per_information9
300 ,p_per_information10 => p_per_information10
301 ,p_per_information11 => p_per_information11
302 ,p_per_information12 => p_per_information12
303 ,p_per_information13 => p_per_information13
304 ,p_per_information14 => p_per_information14
305 ,p_per_information15 => p_per_information15
306 ,p_per_information16 => p_per_information16
307 ,p_per_information17 => p_per_information17
308 ,p_per_information18 => p_per_information18
309 ,p_per_information19 => p_per_information19
310 ,p_per_information20 => p_per_information20
311 ,p_per_information21 => p_per_information21
312 ,p_per_information22 => p_per_information22
313 ,p_per_information23 => p_per_information23
314 ,p_per_information24 => p_per_information24
315 ,p_per_information25 => p_per_information25
316 ,p_per_information26 => p_per_information26
317 ,p_per_information27 => p_per_information27
318 ,p_per_information28 => p_per_information28
319 ,p_per_information29 => p_per_information29
320 ,p_per_information30 => p_per_information30
321 );
322 exception
323 when hr_api.cannot_find_prog_unit then
324 hr_api.cannot_find_prog_unit_error
325 (p_module_name => 'create_cwk'
326 ,p_hook_type => 'BP'
327 );
328 end;
329 --
330 hr_utility.set_location(l_proc, 20);
331 --
332 -- Validation in addition to Row Handlers
333 --
334 -- If the specified person type id is not null then check that it
335 -- corresponds to type 'CWK', is currently active and is in the correct
336 -- business group, otherwise set person type to the active default for CWK
337 -- in the current business group.
338 --
339 per_per_bus.chk_person_type
340 (p_person_type_id => l_person_type_id
341 ,p_business_group_id => p_business_group_id
342 ,p_expected_sys_type => 'CWK'
343 );
344 --
345 hr_utility.set_location(l_proc, 30);
346 --
347 -- Initialise local variables as appropriate
348 --
349 l_applicant_number := null;
350 l_employee_number := null;
351 l_npw_number := p_npw_number;
352 l_current_npw_flag := 'Y';
353 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
354 (p_business_group_id,
355 'OTHER');
356 --
357 -- Process Logic
358 --
359 per_per_ins.ins
360 (p_start_date => l_start_date
361 ,p_effective_date => l_start_date
362 ,p_business_group_id => p_business_group_id
363 ,p_person_type_id => l_person_type_id1
364 ,p_last_name => p_last_name
365 ,p_background_check_status => p_background_check_status
366 ,p_background_date_check => p_background_date_check
367 ,p_blood_type => p_blood_type
368 ,p_comments => p_comments
369 ,p_correspondence_language => p_correspondence_language
370 ,p_country_of_birth => p_country_of_birth
371 ,p_current_npw_flag => l_current_npw_flag
372 ,p_date_of_birth => l_date_of_birth
373 ,p_date_of_death => l_date_of_death
374 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
375 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
376 ,p_email_address => p_email_address
377 ,p_first_name => p_first_name
378 ,p_fte_capacity => p_fte_capacity
379 ,p_honors => p_honors
380 ,p_internal_location => p_internal_location
381 ,p_known_as => p_known_as
382 ,p_last_medical_test_by => p_last_medical_test_by
383 ,p_last_medical_test_date => p_last_medical_test_date
384 ,p_mailstop => p_mailstop
385 ,p_marital_status => p_marital_status
386 ,p_middle_names => p_middle_names
387 ,p_national_identifier => p_national_identifier
388 ,p_nationality => p_nationality
389 ,p_office_number => p_office_number
390 ,p_on_military_service => p_on_military_service
391 ,p_party_id => p_party_id
392 ,p_pre_name_adjunct => p_pre_name_adjunct
393 ,p_previous_last_name => p_previous_last_name
394 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
395 ,p_region_of_birth => p_region_of_birth
396 ,p_registered_disabled_flag => p_registered_disabled_flag
397 ,p_resume_exists => p_resume_exists
398 ,p_resume_last_updated => p_resume_last_updated
399 ,p_second_passport_exists => p_second_passport_exists
400 ,p_sex => p_sex
401 ,p_student_status => p_student_status
402 ,p_suffix => p_suffix
403 ,p_title => p_title
404 ,p_town_of_birth => p_town_of_birth
405 ,p_uses_tobacco_flag => p_uses_tobacco_flag
406 ,p_work_schedule => p_work_schedule
407 ,p_expense_check_send_to_addres => p_exp_check_send_to_address
408 ,p_hold_applicant_date_until => p_hold_applicant_date_until
409 ,p_date_employee_data_verified => p_date_employee_data_verified
410 ,p_benefit_group_id => p_benefit_group_id
411 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
412 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
413 ,p_original_date_of_hire => p_original_date_of_hire
414 ,p_attribute_category => p_attribute_category
415 ,p_attribute1 => p_attribute1
416 ,p_attribute2 => p_attribute2
417 ,p_attribute3 => p_attribute3
418 ,p_attribute4 => p_attribute4
419 ,p_attribute5 => p_attribute5
420 ,p_attribute6 => p_attribute6
421 ,p_attribute7 => p_attribute7
422 ,p_attribute8 => p_attribute8
423 ,p_attribute9 => p_attribute9
424 ,p_attribute10 => p_attribute10
425 ,p_attribute11 => p_attribute11
426 ,p_attribute12 => p_attribute12
427 ,p_attribute13 => p_attribute13
428 ,p_attribute14 => p_attribute14
429 ,p_attribute15 => p_attribute15
430 ,p_attribute16 => p_attribute16
431 ,p_attribute17 => p_attribute17
432 ,p_attribute18 => p_attribute18
433 ,p_attribute19 => p_attribute19
434 ,p_attribute20 => p_attribute20
435 ,p_attribute21 => p_attribute21
436 ,p_attribute22 => p_attribute22
437 ,p_attribute23 => p_attribute23
438 ,p_attribute24 => p_attribute24
439 ,p_attribute25 => p_attribute25
440 ,p_attribute26 => p_attribute26
441 ,p_attribute27 => p_attribute27
442 ,p_attribute28 => p_attribute28
443 ,p_attribute29 => p_attribute29
444 ,p_attribute30 => p_attribute30
445 ,p_per_information_category => p_per_information_category
446 ,p_per_information1 => p_per_information1
447 ,p_per_information2 => p_per_information2
448 ,p_per_information3 => p_per_information3
449 ,p_per_information4 => p_per_information4
450 ,p_per_information5 => p_per_information5
451 ,p_per_information6 => p_per_information6
452 ,p_per_information7 => p_per_information7
453 ,p_per_information8 => p_per_information8
454 ,p_per_information9 => p_per_information9
455 ,p_per_information10 => p_per_information10
456 ,p_per_information11 => p_per_information11
457 ,p_per_information12 => p_per_information12
458 ,p_per_information13 => p_per_information13
459 ,p_per_information14 => p_per_information14
460 ,p_per_information15 => p_per_information15
461 ,p_per_information16 => p_per_information16
462 ,p_per_information17 => p_per_information17
463 ,p_per_information18 => p_per_information18
464 ,p_per_information19 => p_per_information19
465 ,p_per_information20 => p_per_information20
466 ,p_per_information21 => p_per_information21
467 ,p_per_information22 => p_per_information22
468 ,p_per_information23 => p_per_information23
469 ,p_per_information24 => p_per_information24
470 ,p_per_information25 => p_per_information25
471 ,p_per_information26 => p_per_information26
472 ,p_per_information27 => p_per_information27
473 ,p_per_information28 => p_per_information28
474 ,p_per_information29 => p_per_information29
475 ,p_per_information30 => p_per_information30
476 --
477 ,p_applicant_number => l_applicant_number
478 ,p_employee_number => l_employee_number
479 ,p_npw_number => p_npw_number
480 ,p_person_id => l_person_id
481 ,p_object_version_number => l_per_object_version_number
482 ,p_effective_start_date => l_per_effective_start_date
483 ,p_effective_end_date => l_per_effective_end_date
484 ,p_full_name => l_full_name
485 ,p_comment_id => l_comment_id
486 ,p_current_applicant_flag => l_current_applicant_flag
487 ,p_current_employee_flag => l_current_employee_flag
488 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
489 ,p_name_combination_warning => l_name_combination_warning
490 ,p_dob_null_warning => l_dob_null_warning
491 ,p_orig_hire_warning => l_orig_hire_warning
492 );
493 --
494 hr_utility.set_location(l_proc, 40);
495
496 --
497 -- Add this person to the relevant security definitions.
498 --
499 hr_security_internal.populate_new_person
500 (p_person_id => l_person_id
501 ,p_business_group_id => p_business_group_id);
502
503 hr_utility.set_location(l_proc, 45);
504
505 --
506 -- Create the period of placement record
507 --
508 per_pdp_ins.ins
509 (p_date_start => l_start_date
510 ,p_effective_date => l_start_date
511 ,p_business_group_id => p_business_group_id
512 ,p_person_id => l_person_id
513 ,p_projected_termination_date => null
514 ,p_validate_df_flex => false
515 --
516 ,p_object_version_number => l_pdp_object_version_number
517 );
518
519 hr_utility.set_location(l_proc, 60);
520
521 --
522 -- Maintain Person Type Usages
523 --
524 hr_per_type_usage_internal.maintain_person_type_usage
525 (p_effective_date => l_start_date
526 ,p_person_id => l_person_id
527 ,p_person_type_id => l_person_type_id
528 );
529 --
530 --
531 hr_utility.set_location(l_proc, 70);
532 --
533 -- Create the default non-payrolled assignment
534 --
535 hr_assignment_internal.create_default_cwk_asg
536 (p_effective_date => l_start_date
537 ,p_business_group_id => p_business_group_id
538 ,p_person_id => l_person_id
539 ,p_placement_date_start => l_start_date
540 --
541 ,p_assignment_id => l_assignment_id
542 ,p_object_version_number => l_asg_object_version_number
543 ,p_assignment_sequence => l_assignment_sequence
544 ,p_assignment_number => l_assignment_number
545 );
546 --
547 hr_utility.set_location(l_proc, 80);
548 --
549 -- Create a phone row using the newly created person as the parent row.
550 -- This phone row replaces the work_telephone column on the person.
551 --
552 if p_work_telephone is not null then
553 hr_phone_api.create_phone
554 (p_date_from => l_start_date
555 ,p_date_to => null
556 ,p_phone_type => 'W1'
557 ,p_phone_number => p_work_telephone
558 ,p_parent_id => l_person_id
559 ,p_parent_table => 'PER_ALL_PEOPLE_F'
560 ,p_validate => FALSE
561 ,p_effective_date => l_start_date
562 ,p_object_version_number => l_phn_object_version_number --out
563 ,p_phone_id => l_phone_id --out
564 );
565 end if;
566 --
567 -- Call After Process User Hook
568 --
569 begin
570 hr_contingent_worker_bk1.create_cwk_a
571 (p_start_date => l_start_date
572 ,p_business_group_id => p_business_group_id
573 ,p_last_name => p_last_name
574 ,p_person_type_id => p_person_type_id
575 ,p_npw_number => p_npw_number
576 ,p_background_check_status => p_background_check_status
577 ,p_background_date_check => p_background_date_check
578 ,p_blood_type => p_blood_type
579 ,p_comments => p_comments
580 ,p_correspondence_language => p_correspondence_language
581 ,p_country_of_birth => p_country_of_birth
582 ,p_date_of_birth => l_date_of_birth
583 ,p_date_of_death => l_date_of_death
584 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
585 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
586 ,p_email_address => p_email_address
587 ,p_first_name => p_first_name
588 ,p_fte_capacity => p_fte_capacity
589 ,p_honors => p_honors
590 ,p_internal_location => p_internal_location
591 ,p_known_as => p_known_as
592 ,p_last_medical_test_by => p_last_medical_test_by
593 ,p_last_medical_test_date => p_last_medical_test_date
594 ,p_mailstop => p_mailstop
595 ,p_marital_status => p_marital_status
596 ,p_middle_names => p_middle_names
597 ,p_national_identifier => p_national_identifier
598 ,p_nationality => p_nationality
599 ,p_office_number => p_office_number
600 ,p_on_military_service => p_on_military_service
601 ,p_party_id => p_party_id
602 ,p_pre_name_adjunct => p_pre_name_adjunct
603 ,p_previous_last_name => p_previous_last_name
604 ,p_projected_placement_end => null
605 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
606 ,p_region_of_birth => p_region_of_birth
607 ,p_registered_disabled_flag => p_registered_disabled_flag
608 ,p_resume_exists => p_resume_exists
609 ,p_resume_last_updated => p_resume_last_updated
610 ,p_second_passport_exists => p_second_passport_exists
611 ,p_sex => p_sex
612 ,p_student_status => p_student_status
613 ,p_suffix => p_suffix
614 ,p_title => p_title
615 ,p_town_of_birth => p_town_of_birth
616 ,p_uses_tobacco_flag => p_uses_tobacco_flag
617 ,p_vendor_id => p_vendor_id
618 ,p_work_schedule => p_work_schedule
619 ,p_work_telephone => p_work_telephone
620 ,p_exp_check_send_to_address => p_exp_check_send_to_address
621 ,p_hold_applicant_date_until => p_hold_applicant_date_until
622 ,p_date_employee_data_verified => p_date_employee_data_verified
623 ,p_benefit_group_id => p_benefit_group_id
624 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
625 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
626 ,p_original_date_of_hire => p_original_date_of_hire
627 ,p_attribute_category => p_attribute_category
628 ,p_attribute1 => p_attribute1
629 ,p_attribute2 => p_attribute2
630 ,p_attribute3 => p_attribute3
631 ,p_attribute4 => p_attribute4
632 ,p_attribute5 => p_attribute5
633 ,p_attribute6 => p_attribute6
634 ,p_attribute7 => p_attribute7
635 ,p_attribute8 => p_attribute8
636 ,p_attribute9 => p_attribute9
637 ,p_attribute10 => p_attribute10
638 ,p_attribute11 => p_attribute11
639 ,p_attribute12 => p_attribute12
640 ,p_attribute13 => p_attribute13
641 ,p_attribute14 => p_attribute14
642 ,p_attribute15 => p_attribute15
643 ,p_attribute16 => p_attribute16
644 ,p_attribute17 => p_attribute17
645 ,p_attribute18 => p_attribute18
646 ,p_attribute19 => p_attribute19
647 ,p_attribute20 => p_attribute20
648 ,p_attribute21 => p_attribute21
649 ,p_attribute22 => p_attribute22
650 ,p_attribute23 => p_attribute23
651 ,p_attribute24 => p_attribute24
652 ,p_attribute25 => p_attribute25
653 ,p_attribute26 => p_attribute26
654 ,p_attribute27 => p_attribute27
655 ,p_attribute28 => p_attribute28
656 ,p_attribute29 => p_attribute29
657 ,p_attribute30 => p_attribute30
658 ,p_per_information_category => p_per_information_category
659 ,p_per_information1 => p_per_information1
660 ,p_per_information2 => p_per_information2
661 ,p_per_information3 => p_per_information3
662 ,p_per_information4 => p_per_information4
663 ,p_per_information5 => p_per_information5
664 ,p_per_information6 => p_per_information6
665 ,p_per_information7 => p_per_information7
666 ,p_per_information8 => p_per_information8
667 ,p_per_information9 => p_per_information9
668 ,p_per_information10 => p_per_information10
669 ,p_per_information11 => p_per_information11
670 ,p_per_information12 => p_per_information12
671 ,p_per_information13 => p_per_information13
672 ,p_per_information14 => p_per_information14
673 ,p_per_information15 => p_per_information15
674 ,p_per_information16 => p_per_information16
675 ,p_per_information17 => p_per_information17
676 ,p_per_information18 => p_per_information18
677 ,p_per_information19 => p_per_information19
678 ,p_per_information20 => p_per_information20
679 ,p_per_information21 => p_per_information21
680 ,p_per_information22 => p_per_information22
681 ,p_per_information23 => p_per_information23
682 ,p_per_information24 => p_per_information24
683 ,p_per_information25 => p_per_information25
684 ,p_per_information26 => p_per_information26
685 ,p_per_information27 => p_per_information27
686 ,p_per_information28 => p_per_information28
687 ,p_per_information29 => p_per_information29
688 ,p_per_information30 => p_per_information30
689 ,p_person_id => l_person_id
690 ,p_per_object_version_number => l_per_object_version_number
691 ,p_per_effective_start_date => l_per_effective_start_date
692 ,p_per_effective_end_date => l_per_effective_end_date
693 ,p_pdp_object_version_number => l_pdp_object_version_number
694 ,p_full_name => l_full_name
695 ,p_comment_id => l_comment_id
696 ,p_assignment_id => l_assignment_id
697 ,p_asg_object_version_number => l_asg_object_version_number
698 ,p_assignment_sequence => l_assignment_sequence
699 ,p_assignment_number => l_assignment_number
700 ,p_name_combination_warning => l_name_combination_warning
701 );
702 exception
703 when hr_api.cannot_find_prog_unit then
704 hr_api.cannot_find_prog_unit_error
705 (p_module_name => 'create_cwk'
706 ,p_hook_type => 'AP'
707 );
708 end;
709 --
710 -- When in validation only mode raise the Validate_Enabled exception
711 --
712 if p_validate then
713 raise hr_api.validate_enabled;
714 end if;
715 --
716 -- Set all output arguments
717 --
718
719 --
720 -- Start of fix for bug 3684087 by risgupta on 12092005 as done in bug# 3062967
721 --
722 begin
723 SELECT object_version_number
724 INTO p_per_object_Version_number
725 FROM per_all_people_f
726 WHERE person_id = l_person_id
727 And effective_start_Date = l_per_effective_start_date
728 and effective_end_Date = l_per_effective_end_date;
729 exception
730 when no_data_found then
731 p_per_object_Version_number := l_per_object_version_number;
732 end;
733 --
734 -- END of fix for bug 3684087
735 --
736
737 p_person_id := l_person_id;
738 p_assignment_sequence := l_assignment_sequence;
739 p_assignment_number := l_assignment_number;
740 p_assignment_id := l_assignment_id;
741 -- commented for bug# 3684087
742 --p_per_object_version_number := l_per_object_version_number;
743 p_asg_object_version_number := l_asg_object_version_number;
744 p_pdp_object_version_number := l_pdp_object_version_number;
745 p_per_effective_start_date := l_per_effective_start_date;
746 p_per_effective_end_date := l_per_effective_end_date;
747 p_full_name := l_full_name;
748 p_comment_id := l_comment_id;
749 p_name_combination_warning := l_name_combination_warning;
750 --
751 hr_utility.set_location(' Leaving:'||l_proc, 70);
752 exception
753 when hr_api.validate_enabled then
754 --
755 -- As the Validate_Enabled exception has been raised
756 -- we must rollback to the savepoint
757 --
758 rollback to create_cwk;
759 --
760 -- Only set output warning arguments
761 -- (Any key or derived arguments must be set to null
762 -- when validation only mode is being used.)
763 --
764 p_person_id := null;
765 p_npw_number := l_npw_number;
766 p_assignment_sequence := null;
767 p_assignment_number := null;
768 p_assignment_id := null;
769 p_per_object_version_number := null;
770 p_asg_object_version_number := null;
771 p_pdp_object_version_number := null;
772 p_per_effective_start_date := null;
773 p_per_effective_end_date := null;
774 p_full_name := null;
775 p_comment_id := null;
776 p_name_combination_warning := l_name_combination_warning;
777 hr_utility.set_location(' Leaving:'||l_proc, 80);
778 when others then
779 --
780 -- A validation or unexpected error has occured
781 --
782 rollback to create_cwk;
783 --
784 -- set in out parameters and set out parameters
785 --
786 p_person_id := null;
787 p_npw_number := l_npw_number;
788 p_assignment_sequence := null;
789 p_assignment_number := null;
790 p_assignment_id := null;
791 p_per_object_version_number := null;
792 p_asg_object_version_number := null;
793 p_pdp_object_version_number := null;
794 p_per_effective_start_date := null;
795 p_per_effective_end_date := null;
796 p_full_name := null;
797 p_comment_id := null;
798 p_name_combination_warning := l_name_combination_warning;
799 hr_utility.set_location(' Leaving:'||l_proc, 90);
800 raise;
801 end create_cwk;
802 --
803 -- ----------------------------------------------------------------------------
804 -- |------------------------------< convert_to_cwk >--------------------------|
805 -- ----------------------------------------------------------------------------
806 --
807 procedure convert_to_cwk
808 (p_validate in boolean default false
809 ,p_effective_date in date
810 ,p_person_id in number
811 ,p_object_version_number in out nocopy number
812 ,p_npw_number in out nocopy varchar2
813 ,p_projected_placement_end in date default null
814 ,p_person_type_id in number default null
815 ,p_datetrack_update_mode in varchar2
816 ,p_per_effective_start_date out nocopy date
817 ,p_per_effective_end_date out nocopy date
818 ,p_pdp_object_version_number out nocopy number
819 ,p_assignment_id out nocopy number
820 ,p_asg_object_version_number out nocopy number
821 ,p_assignment_sequence out nocopy number
822 ) is
823 --
824 -- Declare cursors and local variables
825 --
826 l_proc varchar2(72) := g_package||'convert_to_cwk';
827 l_object_version_number per_people_f.object_version_number%type;
828 l_npw_number per_people_f.npw_number%type;
829 l_person_type_id number := p_person_type_id;
830 l_person_type_id1 number;
831 l_effective_date date;
832 l_datetrack_update_mode varchar2(30);
833 l_ptu_update_mode varchar2(30);
834 l_per_effective_start_date date;
835 l_per_effective_end_date date;
836 l_comment_id number;
837 l_current_applicant_flag varchar2(1);
838 l_current_emp_or_apl_flag varchar2(1);
839 l_current_employee_flag varchar2(1);
840 l_full_name per_all_people_f.full_name%TYPE;
841 l_name_combination_warning boolean;
842 l_dob_null_warning boolean;
843 l_orig_hire_warning boolean;
844 l_pdp_object_version_number number;
845 l_assignment_id number;
846 l_asg_object_version_number number;
847 l_assignment_sequence number;
848 l_assignment_number per_assignments_f.assignment_number%type;
849 --
850 cursor csr_per_details
851 (p_person_id number
852 ,p_effective_date date
853 )
854 IS
855 SELECT ppf.business_group_id
856 ,ppf.person_type_id
857 ,ppt.system_person_type
858 ,ppf.npw_number
859 ,ppf.applicant_number
860 ,ppf.employee_number
861 FROM per_all_people_f ppf
862 ,per_person_types ppt
863 WHERE ppt.person_type_id = ppf.person_type_id
864 AND ppf.person_id = p_person_id
865 AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
866 --
867 l_per_details_rec csr_per_details%rowtype;
868 --
869 begin
870 hr_utility.set_location('Entering:'|| l_proc, 10);
871
872 --
873 -- Ensure mandatory arguments have been passed
874 --
875 hr_api.mandatory_arg_error
876 (p_api_name => l_proc
877 ,p_argument => 'person_id'
878 ,p_argument_value => p_person_id
879 );
880 --
881 hr_api.mandatory_arg_error
882 (p_api_name => l_proc
883 ,p_argument => 'effective_date'
884 ,p_argument_value => p_effective_date
885 );
886 --
887 -- Issue a savepoint
888 --
889 savepoint convert_to_cwk;
890 --
891 -- Truncate the time portion from all IN date parameters
892 --
893 l_effective_date := trunc(p_effective_date);
894 --
895 l_object_version_number := p_object_version_number;
896 l_npw_number := p_npw_number;
897 --
898 -- Call Before Process User Hook
899 --
900 begin
901 hr_contingent_worker_bk2.convert_to_cwk_b
902 (p_effective_date => l_effective_date
903 ,p_person_id => p_person_id
904 ,p_object_version_number => p_object_version_number
905 ,p_npw_number => p_npw_number
906 ,p_projected_placement_end => null
907 ,p_person_type_id => p_person_type_id
908 ,p_datetrack_update_mode => p_datetrack_update_mode
909 );
910 exception
911 when hr_api.cannot_find_prog_unit then
912 hr_api.cannot_find_prog_unit_error
913 (p_module_name => 'convert_to_cwk'
914 ,p_hook_type => 'BP'
915 );
916 end;
917 hr_utility.set_location(l_proc, 20);
918 --
919 -- Validation in addition to Row Handlers
920 --
921 -- Get derived details for person on effective date
922 --
923 OPEN csr_per_details
924 (p_person_id => p_person_id
925 ,p_effective_date => p_effective_date
926 );
927 FETCH csr_per_details INTO l_per_details_rec;
928 IF csr_per_details%NOTFOUND
929 THEN
930 CLOSE csr_per_details;
931 fnd_message.set_name('PER','PER_289602_CWK_INV_PERSON_ID');
932 fnd_message.raise_error;
933 END IF;
934 CLOSE csr_per_details;
935 --
936 -- If the specified person type id is not null then check that it
937 -- corresponds to type 'CWK', is currently active and is in the correct
938 -- business group, otherwise set person type to the active default for CWK
939 -- in the current business group.
940 --
941 per_per_bus.chk_person_type
942 (p_person_type_id => l_person_type_id
943 ,p_business_group_id => l_per_details_rec.business_group_id
944 ,p_expected_sys_type => 'CWK'
945 );
946 hr_utility.set_location(l_proc, 30);
947 --
948 -- Check for future person_type_changes
949 --
950 if hr_person.chk_future_person_type(l_per_details_rec.system_person_type
951 ,p_person_id
952 ,l_per_details_rec.business_group_id
953 ,p_effective_date) then
954 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
955 fnd_message.raise_error;
956 end if;
957 --
958 -- Check against the existing person type records.
959 --
960 if hr_general2.is_person_type(p_person_id
961 ,'EMP'
962 ,l_effective_date)
963 or hr_general2.is_person_type(p_person_id
964 ,'CWK'
965 ,l_effective_date) then
966 fnd_message.set_name('PER','PER_289603_CWK_INV_PERSON_TYPE');
967 fnd_message.raise_error;
968 elsif l_per_details_rec.system_person_type = 'OTHER' then
969 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
970 (l_per_details_rec.business_group_id,
971 'OTHER');
972 else
973 l_person_type_id1 := l_per_details_rec.person_type_id;
974 hr_utility.set_location(l_proc,50);
975 end if;
976 --
977 -- Ensure the npw_number will not be changed if it exists
978 --
979 IF l_per_details_rec.npw_number IS NOT NULL
980 AND NVL(p_npw_number,hr_api.g_number) <> l_per_details_rec.npw_number
981 THEN
982 hr_utility.set_location(l_proc,60);
983 p_npw_number := l_per_details_rec.npw_number;
984 END IF;
985 --
986 -- Check: is this back-to-back contract?
987 --
988 if hr_general2.is_person_type(p_person_id
989 ,'EX_CWK'
990 ,l_effective_date)
991 and hr_general2.is_person_type(p_person_id
992 ,'CWK'
993 ,l_effective_date-1) then
994 l_datetrack_update_mode := 'CORRECTION';
995 l_ptu_update_mode := 'CORRECTION';
996 hr_utility.set_location(l_proc,70);
997 elsif hr_general2.is_person_type(p_person_id
998 ,'EX_EMP'
999 ,l_effective_date)
1000 and hr_general2.is_person_type(p_person_id
1001 ,'EMP'
1002 ,l_effective_date-1) then
1003 l_datetrack_update_mode := 'CORRECTION';
1004 l_ptu_update_mode := 'UPDATE';
1005 hr_utility.set_location(l_proc,80);
1006 else
1007 --
1008 -- Support the use of CORRECTION for the person table handler routines.
1009 --
1010 if p_datetrack_update_mode = 'CORRECTION' then
1011 l_datetrack_update_mode := p_datetrack_update_mode;
1012 else
1013 l_datetrack_update_mode := 'UPDATE';
1014 end if;
1015 l_ptu_update_mode := 'UPDATE';
1016 end if;
1017 --
1018 -- Process Logic
1019 --
1020 per_per_upd.upd
1021 (p_person_id => p_person_id
1022 ,p_effective_date => l_effective_date
1023 ,p_datetrack_mode => l_datetrack_update_mode
1024 ,p_person_type_id => l_person_type_id1
1025 ,p_applicant_number => l_per_details_rec.applicant_number
1026 ,p_employee_number => l_per_details_rec.employee_number
1027 ,p_npw_number => p_npw_number
1028 ,p_current_npw_flag => 'Y'
1029 ,p_object_version_number => p_object_version_number
1030
1031 ,p_effective_start_date => l_per_effective_start_date
1032 ,p_effective_end_date => l_per_effective_end_date
1033 ,p_comment_id => l_comment_id
1034 ,p_current_applicant_flag => l_current_applicant_flag
1035 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
1036 ,p_current_employee_flag => l_current_employee_flag
1037 ,p_full_name => l_full_name
1038 ,p_name_combination_warning => l_name_combination_warning
1039 ,p_dob_null_warning => l_dob_null_warning
1040 ,p_orig_hire_warning => l_orig_hire_warning
1041 );
1042
1043 hr_utility.set_location(l_proc, 90);
1044
1045 hr_security_internal.populate_new_person
1046 (p_business_group_id => l_per_details_rec.business_group_id
1047 ,p_person_id => p_person_id);
1048
1049 hr_utility.set_location(l_proc, 93);
1050
1051 --
1052 -- Maintain PTU
1053 --
1054 hr_per_type_usage_internal.maintain_person_type_usage
1055 (p_effective_date => l_effective_date
1056 ,p_person_id => p_person_id
1057 ,p_person_type_id => l_person_type_id
1058 ,p_datetrack_update_mode => l_ptu_update_mode
1059 );
1060
1061 hr_utility.set_location(l_proc, 95);
1062
1063 --
1064 -- Create the period of placement record
1065 --
1066 per_pdp_ins.ins
1067 (p_date_start => l_effective_date
1068 ,p_effective_date => l_effective_date
1069 ,p_business_group_id => l_per_details_rec.business_group_id
1070 ,p_person_id => p_person_id
1071 ,p_projected_termination_date => null
1072 ,p_validate_df_flex => false
1073 --
1074 ,p_object_version_number => l_pdp_object_version_number
1075 );
1076 --
1077 hr_utility.set_location(l_proc, 100);
1078 --
1079 -- Create the default non-payrolled assignment
1080 --
1081 hr_assignment_internal.create_default_cwk_asg
1082 (p_effective_date => l_effective_date
1083 ,p_business_group_id => l_per_details_rec.business_group_id
1084 ,p_person_id => p_person_id
1085 ,p_placement_date_start => l_effective_date
1086 --
1087 ,p_assignment_id => l_assignment_id
1088 ,p_object_version_number => l_asg_object_version_number
1089 ,p_assignment_sequence => l_assignment_sequence
1090 ,p_assignment_number => l_assignment_number
1091 );
1092 --
1093 hr_utility.set_location(l_proc, 110);
1094 --
1095 -- Call After Process User Hook
1096 --
1097 begin
1098 hr_contingent_worker_bk2.convert_to_cwk_a
1099 (p_effective_date => l_effective_date
1100 ,p_person_id => p_person_id
1101 ,p_object_version_number => p_object_version_number
1102 ,p_npw_number => p_npw_number
1103 ,p_projected_placement_end => null
1104 ,p_person_type_id => l_person_type_id
1105 ,p_datetrack_update_mode => p_datetrack_update_mode
1106 ,p_per_effective_start_date => l_per_effective_start_date
1107 ,p_per_effective_end_date => l_per_effective_end_date
1108 ,p_pdp_object_version_number => l_pdp_object_version_number
1109 ,p_assignment_id => l_assignment_id
1110 ,p_asg_object_version_number => l_asg_object_version_number
1111 ,p_assignment_sequence => l_assignment_sequence
1112 );
1113 exception
1114 when hr_api.cannot_find_prog_unit then
1115 hr_api.cannot_find_prog_unit_error
1116 (p_module_name => 'convert_to_cwk'
1117 ,p_hook_type => 'AP'
1118 );
1119 end;
1120 --
1121 -- When in validation only mode raise the Validate_Enabled exception
1122 --
1123 if p_validate then
1124 raise hr_api.validate_enabled;
1125 end if;
1126 --
1127 -- Set all output arguments
1128 --
1129 p_per_effective_start_date := l_per_effective_start_date;
1130 p_per_effective_end_date := l_per_effective_end_date;
1131 p_pdp_object_version_number := l_pdp_object_version_number;
1132 p_assignment_id := l_assignment_id;
1133 p_asg_object_version_number := l_asg_object_version_number;
1134 p_assignment_sequence := l_assignment_sequence;
1135 --
1136 hr_utility.set_location(' Leaving:'||l_proc, 170);
1137 exception
1138 when hr_api.validate_enabled then
1139 --
1140 -- As the Validate_Enabled exception has been raised
1141 -- we must rollback to the savepoint
1142 --
1143 rollback to convert_to_cwk;
1144 --
1145 -- Only set output warning arguments
1146 -- (Any key or derived arguments must be set to null
1147 -- when validation only mode is being used.)
1148 --
1149 p_object_version_number := l_object_version_number;
1150 p_npw_number := l_npw_number;
1151 p_pdp_object_version_number := null;
1152 p_per_effective_start_date := null;
1153 p_per_effective_end_date := null;
1154 p_assignment_id := null;
1155 p_asg_object_version_number := null;
1156 p_assignment_sequence := null;
1157 hr_utility.set_location(' Leaving:'||l_proc, 180);
1158 when others then
1159 --
1160 -- A validation or unexpected error has occured
1161 --
1162 rollback to convert_to_cwk;
1163 --
1164 -- set in out parameters and set out parameters
1165 --
1166 p_object_version_number := l_object_version_number;
1167 p_npw_number := l_npw_number;
1168 p_pdp_object_version_number := null;
1169 p_per_effective_start_date := null;
1170 p_per_effective_end_date := null;
1171 p_assignment_id := null;
1172 p_asg_object_version_number := null;
1173 p_assignment_sequence := null;
1174 --
1175 hr_utility.set_location(' Leaving:'||l_proc, 190);
1176 raise;
1177 end convert_to_cwk;
1178 --
1179 -- ----------------------------------------------------------------------------
1180 -- |------------------------------< apply_for_job >---------------------------|
1181 -- ----------------------------------------------------------------------------
1182 --
1183 procedure apply_for_job
1184 (p_validate in boolean default false
1185 ,p_effective_date in date
1186 ,p_person_id in number
1187 ,p_object_version_number in out nocopy number
1188 ,p_applicant_number in out nocopy varchar2
1189 ,p_person_type_id in number default null
1190 ,p_vacancy_id in number default null
1191 ,p_per_effective_start_date out nocopy date
1192 ,p_per_effective_end_date out nocopy date
1193 ,p_application_id out nocopy number
1194 ,p_apl_object_version_number out nocopy number
1195 ,p_assignment_id out nocopy number
1196 ,p_asg_object_version_number out nocopy number
1197 ,p_assignment_sequence out nocopy number
1198 ) is
1199 --
1200 -- Declare cursors and local variables
1201 --
1202 l_proc varchar2(72) := g_package||'apply_for_job';
1203 l_effective_date date;
1204 l_person_type_id number := p_person_type_id;
1205 l_person_type_id1 number;
1206 l_comment_id number;
1207 l_current_applicant_flag varchar2(1);
1208 l_current_emp_or_apl_flag varchar2(1);
1209 l_current_employee_flag varchar2(1);
1210 l_full_name per_all_people_f.full_name%TYPE;
1211 l_name_combination_warning boolean;
1212 l_dob_null_warning boolean;
1213 l_orig_hire_warning boolean;
1214 l_per_effective_start_date date;
1215 l_per_effective_end_date date;
1216 l_application_id number;
1217 l_apl_object_version_number number;
1218 l_assignment_id number;
1219 l_asg_object_version_number number;
1220 l_assignment_sequence number;
1221 l_object_version_number number;
1222 l_applicant_number per_people_f.applicant_number%type;
1223 l_dummy varchar2(1);
1224 --
1225 cursor csr_per_details
1226 (p_person_id number
1227 ,p_effective_date date
1228 )
1229 IS
1230 SELECT ppf.business_group_id
1231 ,ppf.person_type_id
1232 ,ppt.system_person_type
1233 ,ppf.npw_number
1234 ,ppf.applicant_number
1235 ,ppf.employee_number
1236 FROM per_all_people_f ppf
1237 ,per_person_types ppt
1238 WHERE ppt.person_type_id = ppf.person_type_id
1239 AND ppf.person_id = p_person_id
1240 AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
1241 --
1242 l_per_details_rec csr_per_details%rowtype;
1243 --
1244 cursor csr_fut_apl
1245 (p_person_id number
1246 ,p_effective_date date
1247 )
1248 IS
1249 select 'Y'
1250 from dual
1251 where exists (select 'Y'
1252 from per_person_type_usages_f ptu
1253 ,per_person_types ppt
1254 where ptu.person_id = p_person_id
1255 and ppt.person_type_id = ptu.person_type_id
1256 and ptu.effective_start_date >= p_effective_date
1257 and ppt.system_person_type = 'APL');
1258 --
1259 begin
1260 hr_utility.set_location('Entering:'|| l_proc, 10);
1261 --
1262 -- Ensure mandatory arguments have been passed
1263 --
1264 hr_api.mandatory_arg_error
1265 (p_api_name => l_proc
1266 ,p_argument => 'person_id'
1267 ,p_argument_value => p_person_id
1268 );
1269 --
1270 hr_api.mandatory_arg_error
1271 (p_api_name => l_proc
1272 ,p_argument => 'effective_date'
1273 ,p_argument_value => p_effective_date
1274 );
1275 --
1276 -- Issue a savepoint
1277 --
1278 savepoint apply_for_job;
1279 --
1280 -- Truncate the time portion from all IN date parameters
1281 --
1282 l_effective_date := trunc(p_effective_date);
1283 --
1284 l_object_version_number := p_object_version_number;
1285 l_applicant_number := p_applicant_number;
1286 --
1287 -- Call Before Process User Hook
1288 --
1289 begin
1290 hr_contingent_worker_bk3.apply_for_job_b
1291 (p_effective_date => l_effective_date
1292 ,p_person_id => p_person_id
1293 ,p_object_version_number => p_object_version_number
1294 ,p_applicant_number => p_applicant_number
1295 ,p_person_type_id => p_person_type_id
1296 ,p_vacancy_id => p_vacancy_id
1297 );
1298 exception
1299 when hr_api.cannot_find_prog_unit then
1300 hr_api.cannot_find_prog_unit_error
1301 (p_module_name => 'apply_for_job'
1302 ,p_hook_type => 'BP'
1303 );
1304 end;
1305 hr_utility.set_location(l_proc, 20);
1306 --
1307 -- Validation in addition to Row Handlers
1308 --
1309 --
1310 -- Get derived details for person on effective date
1311 --
1312 OPEN csr_per_details
1313 (p_person_id => p_person_id
1314 ,p_effective_date => p_effective_date
1315 );
1316 FETCH csr_per_details INTO l_per_details_rec;
1317 IF csr_per_details%NOTFOUND
1318 THEN
1319 CLOSE csr_per_details;
1320 fnd_message.set_name('PER','PER_289602_CWK_INV_PERSON_ID');
1321 fnd_message.raise_error;
1322 END IF;
1323 CLOSE csr_per_details;
1324 hr_utility.set_location(l_proc, 25);
1325 --
1326 -- Check for future person_type_changes
1327 --
1328 if hr_person.chk_future_person_type(l_per_details_rec.system_person_type
1329 ,p_person_id
1330 ,l_per_details_rec.business_group_id
1331 ,p_effective_date) then
1332 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
1333 fnd_message.raise_error;
1334 end if;
1335
1336 hr_utility.set_location(l_proc, 28);
1337 --
1338 -- Check against the existing person type records.
1339 --
1340 open csr_fut_apl(p_person_id,l_effective_date);
1341 fetch csr_fut_apl into l_dummy;
1342 if csr_fut_apl%found
1343 or not hr_general2.is_person_type(p_person_id
1344 ,'CWK'
1345 ,l_effective_date) then
1346 close csr_fut_apl;
1347 fnd_message.set_name('PER','PER_289603_CWK_INV_PERSON_TYPE');
1348 fnd_message.raise_error;
1349 else
1350 close csr_fut_apl;
1351 end if;
1352 hr_utility.set_location(l_proc, 30);
1353 --
1354 -- If the specified person type id is not null then check that it
1355 -- corresponds to type 'APL', is currently active and is in the correct
1356 -- business group, otherwise set person type to the active default for APL
1357 -- in the current business group.
1358 --
1359 per_per_bus.chk_person_type
1360 (p_person_type_id => l_person_type_id
1361 ,p_business_group_id => l_per_details_rec.business_group_id
1362 ,p_expected_sys_type => 'APL'
1363 );
1364 hr_utility.set_location(l_proc, 40);
1365 --
1366 -- Ensure the applicant number will not be changed if it exists
1367 --
1368 IF l_per_details_rec.applicant_number IS NOT NULL
1369 AND NVL(p_applicant_number,hr_api.g_number) <> l_per_details_rec.applicant_number
1370 THEN
1371 hr_utility.set_location(l_proc,50);
1372 p_applicant_number := l_per_details_rec.applicant_number;
1373 END IF;
1374 --
1375 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1376 (l_per_details_rec.business_group_id,
1377 'APL');
1378 --
1379 -- Process Logic
1380 --
1381 per_per_upd.upd
1382 (p_person_id => p_person_id
1383 ,p_effective_date => l_effective_date
1384 ,p_datetrack_mode => hr_api.g_update
1385 ,p_person_type_id => l_person_type_id1
1386 ,p_applicant_number => p_applicant_number
1387 ,p_employee_number => l_per_details_rec.employee_number
1388 ,p_npw_number => l_per_details_rec.npw_number
1389 ,p_current_npw_flag => 'Y'
1390 ,p_object_version_number => p_object_version_number
1391
1392 ,p_effective_start_date => l_per_effective_start_date
1393 ,p_effective_end_date => l_per_effective_end_date
1394 ,p_comment_id => l_comment_id
1395 ,p_current_applicant_flag => l_current_applicant_flag
1396 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
1397 ,p_current_employee_flag => l_current_employee_flag
1398 ,p_full_name => l_full_name
1399 ,p_name_combination_warning => l_name_combination_warning
1400 ,p_dob_null_warning => l_dob_null_warning
1401 ,p_orig_hire_warning => l_orig_hire_warning
1402 );
1403 hr_utility.set_location(l_proc, 60);
1404 --
1405 hr_security_internal.populate_new_person
1406 (p_business_group_id => l_per_details_rec.business_group_id
1407 ,p_person_id => p_person_id
1408 );
1409 hr_utility.set_location(l_proc, 70);
1410 --
1411 -- Maintain PTU
1412 --
1413 hr_per_type_usage_internal.maintain_person_type_usage
1414 (p_effective_date => l_effective_date
1415 ,p_person_id => p_person_id
1416 ,p_person_type_id => l_person_type_id
1417 );
1418 hr_utility.set_location(l_proc, 80);
1419 --
1420 -- create an application.
1421 --
1422 per_apl_ins.ins
1423 (p_application_id => l_application_id
1424 ,p_business_group_id => l_per_details_rec.business_group_id
1425 ,p_person_id => p_person_id
1426 ,p_date_received => l_effective_date
1427 ,p_object_version_number => l_apl_object_version_number
1428 ,p_effective_date => l_effective_date
1429 );
1430 hr_utility.set_location(l_proc, 90);
1431 --
1432 hr_assignment_internal.create_default_apl_asg
1433 (p_effective_date => l_effective_date
1434 ,p_person_id => p_person_id
1435 ,p_business_group_id => l_per_details_rec.business_group_id
1436 ,p_application_id => l_application_id
1437 ,p_assignment_id => l_assignment_id
1438 ,p_object_version_number => l_asg_object_version_number
1439 ,p_assignment_sequence => l_assignment_sequence
1440 );
1441 hr_utility.set_location(l_proc, 100);
1442 --
1443 -- Call After Process User Hook
1444 --
1445 begin
1446 hr_contingent_worker_bk3.apply_for_job_a
1447 (p_effective_date => l_effective_date
1448 ,p_person_id => p_person_id
1449 ,p_object_version_number => p_object_version_number
1450 ,p_applicant_number => p_applicant_number
1451 ,p_person_type_id => l_person_type_id
1452 ,p_vacancy_id => p_vacancy_id
1453 ,p_per_effective_start_date => l_per_effective_start_date
1454 ,p_per_effective_end_date => l_per_effective_end_date
1455 ,p_application_id => l_application_id
1456 ,p_apl_object_version_number => l_apl_object_version_number
1457 ,p_assignment_id => l_assignment_id
1458 ,p_asg_object_version_number => l_asg_object_version_number
1459 ,p_assignment_sequence => l_assignment_sequence
1460 );
1461 exception
1462 when hr_api.cannot_find_prog_unit then
1463 hr_api.cannot_find_prog_unit_error
1464 (p_module_name => 'apply_for_job'
1465 ,p_hook_type => 'AP'
1466 );
1467 end;
1468 --
1469 -- When in validation only mode raise the Validate_Enabled exception
1470 --
1471 if p_validate then
1472 raise hr_api.validate_enabled;
1473 end if;
1474 --
1475 -- Set all output arguments
1476 --
1477 p_per_effective_start_date := l_per_effective_start_date;
1478 p_per_effective_end_date := l_per_effective_end_date;
1479 p_application_id := l_application_id;
1480 p_apl_object_version_number := l_apl_object_version_number;
1481 p_assignment_id := l_assignment_id;
1482 p_asg_object_version_number := l_asg_object_version_number;
1483 p_assignment_sequence := l_assignment_sequence;
1484 --
1485 hr_utility.set_location(' Leaving:'||l_proc, 170);
1486 exception
1487 when hr_api.validate_enabled then
1488 --
1489 -- As the Validate_Enabled exception has been raised
1490 -- we must rollback to the savepoint
1491 --
1492 rollback to apply_for_job;
1493 --
1494 -- Only set output warning arguments
1495 -- (Any key or derived arguments must be set to null
1496 -- when validation only mode is being used.)
1497 --
1498 p_object_version_number := l_object_version_number;
1499 p_applicant_number := l_applicant_number;
1500 p_per_effective_start_date := null;
1501 p_per_effective_end_date := null;
1502 p_application_id := null;
1503 p_apl_object_version_number := null;
1504 p_assignment_id := null;
1505 p_asg_object_version_number := null;
1506 p_assignment_sequence := null;
1507 hr_utility.set_location(' Leaving:'||l_proc, 180);
1508 when others then
1509 --
1510 -- A validation or unexpected error has occured
1511 --
1512 rollback to apply_for_job;
1513 --
1514 --
1515 -- set in out parameters and set out parameters
1516 --
1517 p_object_version_number := l_object_version_number;
1518 p_applicant_number := l_applicant_number;
1519 p_per_effective_start_date := null;
1520 p_per_effective_end_date := null;
1521 p_application_id := null;
1522 p_apl_object_version_number := null;
1523 p_assignment_id := null;
1524 p_asg_object_version_number := null;
1525 p_assignment_sequence := null;
1526 --
1527 hr_utility.set_location(' Leaving:'||l_proc, 190);
1528 raise;
1529 end apply_for_job;
1530 --
1531 -- ----------------------------------------------------------------------------
1532 -- |---------------------------< pre_term_check >-----------------------------|
1533 -- ----------------------------------------------------------------------------
1534 --
1535 procedure pre_term_check(p_status IN OUT NOCOPY VARCHAR2
1536 ,p_business_group_id IN NUMBER
1537 ,p_person_id IN NUMBER
1538 ,p_session_date IN DATE
1539 )is
1540 --
1541 --
1542 v_dummy VARCHAR2(1);
1543 l_proc varchar2(45) := g_package||'pre_term_check';
1544 --
1545 begin
1546 if p_status = 'SUPERVISOR' then
1547 begin
1548 hr_utility.set_location(l_proc,10);
1549 Select 'X'
1550 into v_dummy
1551 from sys.dual
1552 where exists (select 'Assignments Exist'
1553 from per_all_assignments_f paf
1554 where paf.supervisor_id = p_person_id
1555 and paf.business_group_id = p_business_group_id
1556 and p_session_date between paf.effective_start_date
1557 and paf.effective_end_date);
1558 p_status := 'WARNING';
1559 return;
1560 exception
1561 when no_data_found then
1562 return;
1563 end;
1564 elsif p_status = 'EVENT' then
1565 begin
1566 hr_utility.set_location(l_proc,30);
1567 select 'X'
1568 into v_dummy
1569 from sys.dual
1570 where exists ( select 'Events exist'
1571 from per_events pe
1572 , per_bookings pb
1573 where pe.business_group_id = pb.business_group_id
1574 and (pb.business_group_id = p_business_group_id OR
1575 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1576 and pe.event_id = pb.event_id
1577 and pe.event_or_interview = 'E'
1578 and pb.person_id = p_person_id
1579 and pe.date_start > p_session_date
1580 );
1581 p_status := 'WARNING';
1582 return;
1583 exception
1584 when no_data_found then
1585 return;
1586 end;
1587 end if;
1588 if p_status = 'INTERVIEW' then
1589 begin
1590 hr_utility.set_location(l_proc,40);
1591 select 'X'
1592 into v_dummy
1593 from sys.dual
1594 where exists(select 'Interview rows exist'
1595 from per_events pe
1596 where pe.business_group_id = p_business_group_id
1597 and pe.event_or_interview = 'I'
1598 and pe.internal_contact_person_id = p_person_id
1599 and pe.date_start > p_session_date
1600 )
1601 OR
1602 exists(select 'Interview rows exist'
1603 from per_events pe
1604 ,per_bookings pb
1605 where pe.business_group_id = pb.business_group_id
1606 and (pb.business_group_id = p_business_group_id OR
1607 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1608 and pe.event_id = pb.event_id
1609 and pe.event_or_interview = 'I'
1610 and pb.person_id = p_person_id
1611 and pe.date_start > p_session_date
1612 );
1613 p_status := 'WARNING';
1614 return;
1615 exception
1616 when no_data_found then
1617 return;
1618 end;
1619 end if;
1620 if p_status = 'REVIEW' then
1621 begin
1622 hr_utility.set_location(l_proc,50);
1623 select 'X'
1624 into v_dummy
1625 from sys.dual
1626 where exists ( select 'Perf Review rows exist'
1627 from per_performance_reviews ppr
1628 where ppr.person_id = p_person_id
1629 and review_date > p_session_date
1630 );
1631 p_status := 'WARNING';
1632 return;
1633 exception
1634 when no_data_found then
1635 return;
1636 end;
1637 end if;
1638 if p_status = 'RECRUITER' then
1639 begin
1640 hr_utility.set_location(l_proc,60);
1641 select 'X'
1642 into v_dummy
1643 from sys.dual
1644 where exists (select 'Recruiter for vacancy'
1645 from per_vacancies pv
1646 where
1647 -- Fix for bug 3446782. This condition exists in the view.
1648 /*(pv.business_group_id = p_business_group_id OR
1649 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1650 and */
1651 pv.recruiter_id = p_person_id
1652 and nvl(pv.date_to, p_session_date) >= p_session_date);
1653 p_status := 'WARNING';
1654 return;
1655 exception
1656 when no_data_found then
1657 return;
1658 end;
1659 end if;
1660 end pre_term_check;
1661 --
1662 -- ----------------------------------------------------------------------------
1663 -- |-----------------------< actual_termination_placement >-------------------|
1664 -- ----------------------------------------------------------------------------
1665 --
1666 procedure actual_termination_placement
1667 (p_validate in boolean default false
1668 ,p_effective_date in date
1669 ,p_person_id in number
1670 ,p_date_start in date
1671 ,p_object_version_number in out nocopy number
1672 ,p_actual_termination_date in date
1673 ,p_last_standard_process_date in out nocopy date
1674 ,p_person_type_id in number default hr_api.g_number
1675 ,p_assignment_status_type_id in number default hr_api.g_number
1676 ,p_termination_reason in varchar2 default hr_api.g_varchar2
1677 ,p_supervisor_warning out nocopy boolean
1678 ,p_event_warning out nocopy boolean
1679 ,p_interview_warning out nocopy boolean
1680 ,p_review_warning out nocopy boolean
1681 ,p_recruiter_warning out nocopy boolean
1682 ,p_asg_future_changes_warning out nocopy boolean
1683 ,p_entries_changed_warning out nocopy varchar2
1684 ,p_pay_proposal_warning out nocopy boolean
1685 ,p_dod_warning out nocopy boolean
1686 ) is
1687 --
1688 -- Declare cursors and local variables
1689 --
1690 -- Out variables
1691 --
1692 l_asg_future_changes_warning boolean := FALSE;
1693 l_entries_changed_warning varchar2(1) := 'N';
1694 l_event_warning boolean := FALSE;
1695 l_interview_warning boolean := FALSE;
1696 l_last_standard_process_date date;
1697 l_pdp_object_version_number number;
1698 l_recruiter_warning boolean := FALSE;
1699 l_review_warning boolean := FALSE;
1700 l_supervisor_warning boolean := FALSE;
1701 l_dod_warning boolean := FALSE;
1702 --
1703 l_assignment_status_type_id number;
1704 l_business_group_id number;
1705 l_comment_id number;
1706 l_cr_asg_future_changes_warn boolean := FALSE;
1707 l_cr_entries_changed_warn varchar2(1) := 'N';
1708 l_pay_proposal_warn boolean := FALSE;
1709 l_current_npw_flag varchar2(1);
1710 l_dob_null_warning boolean;
1711 l_effective_date date;
1712 l_effective_end_date date;
1713 l_effective_start_date date;
1714 l_npw_number per_people_f.npw_number%TYPE;
1715 l_exists varchar2(1);
1716 l_full_name per_all_people_f.full_name%TYPE;
1717 l_legislation_code per_business_groups.legislation_code%TYPE;
1718 l_max_tpe_end_date date;
1719 l_name_combination_warning boolean;
1720 l_orig_hire_warning boolean;
1721 l_per_object_version_number number;
1722 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1723 l_person_id number;
1724 l_date_start date;
1725 l_person_type_id number;
1726 l_proc varchar2(72)
1727 := g_package ||
1728 'actual_termination_placement';
1729 l_system_person_type1 per_person_types.system_person_type%TYPE;
1730 l_per_effective_start_date date;
1731 l_datetrack_mode varchar2(30);
1732 l_validation_start_date date;
1733 l_validation_end_date date;
1734 l_actual_termination_date date;
1735 l_status varchar2(11);
1736 l_current_dod date;
1737 l_date_of_death date;
1738 l_ptu_object_version_number number;
1739 l_person_type_usage_id number;
1740 l_applicant_number per_all_people_f.applicant_number%TYPE;
1741 l_employee_number per_all_people_f.employee_number%TYPE := hr_api.g_varchar2;
1742 l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
1743 l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
1744 l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
1745 --
1746
1747 cursor csr_future_per_changes is
1748 select null
1749 from per_all_people_f per
1750 where per.person_id = l_person_id
1751 and per.effective_start_date > l_actual_termination_date;
1752 --
1753 cursor csr_get_asgs_to_terminate is
1754 select asg.assignment_id
1755 , asg.object_version_number
1756 from per_all_assignments_f asg
1757 where asg.person_id = l_person_id
1758 and asg.period_of_placement_date_start = l_date_start
1759 and l_actual_termination_date + 1 between asg.effective_start_date
1760 and asg.effective_end_date
1761 order by asg.primary_flag;
1762 --
1763 cursor csr_get_derived_details is
1764 select bus.business_group_id
1765 , bus.legislation_code
1766 , per.person_id
1767 , per.effective_start_date
1768 , per.object_version_number
1769 , per.npw_number
1770 , per.applicant_number
1771 from per_all_people_f per
1772 , per_business_groups bus
1773 , per_periods_of_placement pdp
1774 where pdp.person_id = p_person_id
1775 and pdp.date_start = p_date_start
1776 and bus.business_group_id = pdp.business_group_id
1777 and per.person_id = pdp.person_id
1778 and l_actual_termination_date between per.effective_start_date
1779 and per.effective_end_date;
1780 --
1781 cursor csr_get_max_tpe_end_date is
1782 select max(tpe.end_date)
1783 from per_time_periods tpe
1784 ,per_all_assignments_f asg
1785 where asg.person_id = l_person_id
1786 and asg.period_of_placement_date_start = l_date_start
1787 and l_actual_termination_date between asg.effective_start_date
1788 and asg.effective_end_date
1789 and asg.payroll_id is not null
1790 and tpe.payroll_id = asg.payroll_id
1791 and l_actual_termination_date between tpe.start_date
1792 and tpe.end_date;
1793 --
1794 cursor csr_date_of_death is
1795 select date_of_death
1796 from per_all_people_f
1797 where person_id = l_person_id;
1798 --
1799 --
1800 begin
1801 hr_utility.set_location('Entering:'|| l_proc, 1);
1802 --
1803 -- Issue a savepoint.
1804 --
1805 savepoint actual_termination_placement;
1806 --
1807 -- Initialise local variables
1808 --
1809 l_person_type_id := p_person_type_id;
1810 l_assignment_status_type_id := p_assignment_status_type_id;
1811 l_last_standard_process_date := trunc(p_last_standard_process_date);
1812 l_pdp_object_version_number := p_object_version_number;
1813 l_actual_termination_date := trunc(p_actual_termination_date);
1814 l_effective_date := trunc(p_effective_date);
1815 l_date_start := trunc(p_date_start);
1816 --
1817 hr_utility.set_location(l_proc, 10);
1818 --
1819 -- Validation in addition to Table Handlers
1820 --
1821 -- Check period of placement and get business group details for validation.
1822 --
1823 hr_api.mandatory_arg_error
1824 (p_api_name => l_proc
1825 ,p_argument => 'person_id'
1826 ,p_argument_value => p_person_id
1827 );
1828 --
1829 hr_api.mandatory_arg_error
1830 (p_api_name => l_proc
1831 ,p_argument => 'date_start'
1832 ,p_argument_value => p_date_start
1833 );
1834 --
1835 hr_api.mandatory_arg_error
1836 (p_api_name => l_proc
1837 ,p_argument => 'actual_termination_date'
1838 ,p_argument_value => l_actual_termination_date
1839 );
1840 --
1841 hr_utility.set_location(l_proc, 20);
1842 --
1843 open csr_get_derived_details;
1844 fetch csr_get_derived_details
1845 into l_business_group_id
1846 , l_legislation_code
1847 , l_person_id
1848 , l_per_effective_start_date
1849 , l_per_object_version_number
1850 , l_npw_number
1851 , l_applicant_number;
1852 --
1853 if csr_get_derived_details%NOTFOUND
1854 then
1855 --
1856 hr_utility.set_location(l_proc, 30);
1857 --
1858 close csr_get_derived_details;
1859 --
1860 fnd_message.set_name('PER','HR_289609_PDP_NOT_EXISTS');
1861 fnd_message.raise_error;
1862 end if;
1863 --
1864 close csr_get_derived_details;
1865
1866 hr_utility.set_location(l_proc, 40);
1867 --
1868 -- Check that the corresponding person has a person type usage of
1869 -- contingent worker at the actual termination date.
1870 --
1871 if not (hr_general2.is_person_type
1872 (p_person_id => p_person_id
1873 ,p_person_type => 'CWK'
1874 ,p_effective_date => l_actual_termination_date))
1875 then
1876
1877 hr_utility.set_location(l_proc, 50);
1878
1879 fnd_message.set_name('PER','HR_289612_PDP_NOT_PTU_CWK');
1880 fnd_message.raise_error;
1881
1882 end if;
1883
1884 hr_utility.set_location(l_proc, 60);
1885
1886 --
1887 -- Check that there are not any future changes to the person.
1888 --
1889 open csr_future_per_changes;
1890 fetch csr_future_per_changes
1891 into l_exists;
1892 --
1893 if csr_future_per_changes%FOUND
1894 then
1895 --
1896 hr_utility.set_location(l_proc, 70);
1897 --
1898 close csr_future_per_changes;
1899 --
1900 fnd_message.set_name('PAY','HR_7957_PDS_INV_ATT_FUTURE');
1901 fnd_message.raise_error;
1902 end if;
1903 --
1904 hr_utility.set_location(l_proc, 80);
1905 --
1906 close csr_future_per_changes;
1907
1908 --
1909 -- if p_person_type_id is entered, is should be of an ex-
1910 -- contingent worker person type. l_person_type_id is
1911 -- passed because it is an IN OUT parameter and is populated
1912 -- with the default person_type_id if it was null.
1913 --
1914 per_per_bus.chk_person_type
1915 (p_person_type_id => l_person_type_id
1916 ,p_business_group_id => l_business_group_id
1917 ,p_expected_sys_type => 'EX_CWK'
1918 );
1919 --
1920 hr_utility.set_location(l_proc, 90);
1921 hr_utility.trace('l_assignment_status_type_id: '||
1922 to_char(l_assignment_status_type_id));
1923 hr_utility.trace('l_business_group_id: '||
1924 to_char(l_business_group_id));
1925 hr_utility.trace('l_legislation_code: '||l_legislation_code);
1926
1927
1928 --
1929 -- If l_assignment_status_type_id is g_number then derive it's
1930 -- default value, otherwise validate it (the parameter is an
1931 -- IN OUT).
1932 --
1933 -- For CWK Phase I we bypass this check because we are not
1934 -- changing the assignment status to 'terminated'.
1935 --
1936 /*
1937 per_asg_bus1.chk_assignment_status_type
1938 (p_assignment_status_type_id => l_assignment_status_type_id
1939 ,p_business_group_id => l_business_group_id
1940 ,p_legislation_code => l_legislation_code
1941 ,p_expected_system_status => 'TERM_CWK_ASG'
1942 );
1943 */
1944 --
1945 hr_utility.set_location(l_proc, 100);
1946 --
1947 -- Validate/derive the last standard process date.
1948 --
1949 --
1950 hr_utility.set_location(l_proc, 130);
1951 --
1952 if l_last_standard_process_date is not null
1953 then
1954 --
1955 hr_utility.set_location(l_proc, 140);
1956 --
1957 -- Check that the last standard process date is on or after the actual
1958 -- termination date.
1959 --
1960 if not l_last_standard_process_date >= l_actual_termination_date
1961 then
1962 --
1963 hr_utility.set_location(l_proc, 150);
1964 --
1965 fnd_message.set_name('PAY','HR_7505_PDS_INV_LSP_ATT_DT');
1966 fnd_message.raise_error;
1967 end if;
1968 else
1969 --
1970 hr_utility.set_location(l_proc, 160);
1971 --
1972 -- Last standard process date is null => derive it.
1973 --
1974 -- Find the max tpe end date of any payrolls that are assigned.
1975 --
1976 open csr_get_max_tpe_end_date;
1977 fetch csr_get_max_tpe_end_date
1978 into l_max_tpe_end_date;
1979 --
1980 if csr_get_max_tpe_end_date%NOTFOUND
1981 then
1982 --
1983 hr_utility.set_location(l_proc, 170);
1984 --
1985 close csr_get_max_tpe_end_date;
1986 --
1987 -- As the cursor should always return at least a null value, this
1988 -- should never happen!
1989 --
1990 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1991 fnd_message.set_token('PROCEDURE', l_proc);
1992 fnd_message.set_token('STEP','175');
1993 fnd_message.raise_error;
1994 end if;
1995 --
1996 close csr_get_max_tpe_end_date;
1997 --
1998 hr_utility.set_location(l_proc, 180);
1999 --
2000 if l_max_tpe_end_date is not null
2001 then
2002 --
2003 hr_utility.set_location(l_proc, 190);
2004 --
2005 -- A time period end date has been found, so set the last standard
2006 -- process date to that.
2007 --
2008 l_last_standard_process_date := l_max_tpe_end_date;
2009 else
2010 --
2011 hr_utility.set_location(l_proc, 200);
2012 --
2013 -- Either there was not an assignment assigned to a payroll, or
2014 -- there was no time period for that payroll as of the actual
2015 -- termination date. It doesn't matter which as we will default
2016 -- the LSPD to the ATD.
2017 --
2018 l_last_standard_process_date := l_actual_termination_date;
2019 end if;
2020 end if;
2021 --
2022 hr_utility.set_location(l_proc, 240);
2023 --
2024 -- Lock the person record in PER_PEOPLE_F ready for UPDATE at a later point.
2025 -- (Note: This is necessary because calling the table handlers in locking
2026 -- ladder order invokes an error in per_pdp_upd.upd due to the person
2027 -- being modified by the per_per_upd.upd table handler.)
2028 --
2029 l_datetrack_mode := 'UPDATE';
2030 --
2031 per_per_shd.lck
2032 (p_effective_date => l_actual_termination_date + 1
2033 ,p_datetrack_mode => l_datetrack_mode
2034 ,p_person_id => l_person_id
2035 ,p_object_version_number => l_per_object_version_number
2036 ,p_validation_start_date => l_validation_start_date
2037 ,p_validation_end_date => l_validation_end_date
2038 );
2039
2040 hr_utility.set_location(l_proc, 245);
2041 --
2042 -- Update actual termination date and last standard process date in
2043 -- periods of placement table.
2044
2045 per_pdp_upd.upd
2046 (p_effective_date => p_actual_termination_date + 1
2047 ,p_object_version_number => l_pdp_object_version_number
2048 ,p_person_id => l_person_id
2049 ,p_date_start => l_date_start
2050 ,p_actual_termination_date => l_actual_termination_date
2051 ,p_last_standard_process_date => l_last_standard_process_date
2052 ,p_termination_reason => p_termination_reason);
2053
2054 --
2055 if p_termination_reason = 'D' then
2056 open csr_date_of_death;
2057 fetch csr_date_of_death into l_current_dod;
2058 if l_current_dod is null then
2059 l_date_of_death := p_actual_termination_date;
2060 l_dod_warning := TRUE;
2061 else
2062 l_date_of_death := l_current_dod;
2063 end if;
2064 close csr_date_of_death;
2065 end if;
2066 --
2067 if l_dod_warning = TRUE then
2068 hr_utility.set_location(l_proc, 998);
2069 else
2070 hr_utility.set_location(l_proc,999);
2071 end if;
2072 --
2073 -- Update person type in person table.
2074 --
2075 hr_utility.set_location(l_proc, 250);
2076 per_per_upd.upd
2077 (p_person_id => l_person_id
2078 ,p_effective_start_date => l_effective_start_date
2079 ,p_effective_end_date => l_effective_end_date
2080 ,p_comment_id => l_comment_id
2081 ,p_current_npw_flag => l_current_npw_flag
2082 ,p_npw_number => l_npw_number
2083 ,p_applicant_number => l_applicant_number
2084 ,p_employee_number => l_employee_number
2085 ,p_current_applicant_flag => l_current_applicant_flag
2086 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
2087 ,p_current_employee_flag => l_current_employee_flag
2088 ,p_full_name => l_full_name
2089 ,p_object_version_number => l_per_object_version_number
2090 ,p_effective_date => l_actual_termination_date + 1
2091 ,p_datetrack_mode => 'UPDATE'
2092 ,p_date_of_death => l_date_of_death
2093 ,p_validate => p_validate
2094 ,p_name_combination_warning => l_name_combination_warning
2095 ,p_dob_null_warning => l_dob_null_warning
2096 ,p_orig_hire_warning => l_orig_hire_warning
2097 );
2098 --
2099 hr_utility.set_location(l_proc, 260);
2100 --
2101 -- Terminate the assignments, ensuring that the non-primaries are
2102 -- processed before the primary (implemented via 'order by primary_flag'
2103 -- clause in cursor declaration).
2104 --
2105 for csr_rec in csr_get_asgs_to_terminate
2106 loop
2107 --
2108 hr_utility.set_location(l_proc, 270);
2109 --
2110 hr_assignment_internal.actual_term_cwk_asg
2111 (p_assignment_id => csr_rec.assignment_id
2112 ,p_object_version_number => csr_rec.object_version_number
2113 ,p_actual_termination_date => l_actual_termination_date
2114 ,p_last_standard_process_date => l_last_standard_process_date
2115 ,p_assignment_status_type_id => l_assignment_status_type_id
2116 ,p_effective_start_date => l_effective_start_date
2117 ,p_effective_end_date => l_effective_end_date
2118 ,p_asg_future_changes_warning => l_cr_asg_future_changes_warn
2119 ,p_entries_changed_warning => l_cr_entries_changed_warn
2120 ,p_pay_proposal_warning => l_pay_proposal_warn
2121 );
2122 --
2123 hr_utility.set_location(l_proc, 280);
2124 --
2125 -- Set entries changed warning using the precedence of 'S', then 'Y', then
2126 -- 'N'.
2127 --
2128 if l_cr_entries_changed_warn = 'S' or
2129 l_entries_changed_warning = 'S' then
2130 --
2131 hr_utility.set_location(l_proc, 290);
2132 --
2133 l_entries_changed_warning := 'S';
2134 --
2135 elsif l_cr_entries_changed_warn = 'Y' or
2136 l_entries_changed_warning = 'Y' then
2137 --
2138 hr_utility.set_location(l_proc, 300);
2139 --
2140 l_entries_changed_warning := 'Y';
2141
2142 else
2143 --
2144 hr_utility.set_location(l_proc, 305);
2145 --
2146 l_entries_changed_warning := 'N';
2147
2148 end if;
2149 --
2150 hr_utility.set_location(l_proc, 310);
2151 --
2152 -- Set future changes warning.
2153 --
2154 if l_cr_asg_future_changes_warn or l_asg_future_changes_warning
2155 then
2156 --
2157 hr_utility.set_location(l_proc, 320);
2158 --
2159 l_asg_future_changes_warning := TRUE;
2160
2161 end if;
2162
2163 end loop;
2164 --
2165 hr_utility.set_location(l_proc, 330);
2166 --
2167 -- Added code to support the following Out warning parameters.
2168 --
2169 l_status := 'SUPERVISOR';
2170 pre_term_check(l_status,
2171 l_business_group_id,
2172 l_person_id,
2173 l_actual_termination_date);
2174 if l_status = 'WARNING' then
2175 p_supervisor_warning := TRUE;
2176 else
2177 p_supervisor_warning := FALSE;
2178 end if;
2179 --
2180 l_status := 'EVENT';
2181 pre_term_check(l_status,
2182 l_business_group_id,
2183 l_person_id,
2184 l_actual_termination_date);
2185 if l_status = 'WARNING' then
2186 p_event_warning := TRUE;
2187 else
2188 p_event_warning := FALSE;
2189 end if;
2190 --
2191 l_status := 'INTERVIEW';
2192 pre_term_check(l_status,
2193 l_business_group_id,
2194 l_person_id,
2195 l_actual_termination_date);
2196 if l_status = 'WARNING' then
2197 p_interview_warning := TRUE;
2198 else
2199 p_interview_warning := FALSE;
2200 end if;
2201 --
2202 l_status := 'REVIEW';
2203 pre_term_check(l_status,
2204 l_business_group_id,
2205 l_person_id,
2206 l_actual_termination_date);
2207 if l_status = 'WARNING' then
2208 p_review_warning := TRUE;
2209 else
2210 p_review_warning := FALSE;
2211 end if;
2212 --
2213 l_status := 'RECRUITER';
2214 pre_term_check(l_status,
2215 l_business_group_id,
2216 l_person_id,
2217 l_actual_termination_date);
2218 if l_status = 'WARNING' then
2219 p_recruiter_warning := TRUE;
2220 else
2221 p_recruiter_warning := FALSE;
2222 end if;
2223 --
2224
2225 --
2226 -- Make the PTU changes for terminating a contingent worker.
2227 -- l_person_type_id holds validated flavour of EX_EMP
2228
2229 hr_per_type_usage_internal.maintain_person_type_usage
2230 (p_effective_date => p_actual_termination_date +1
2231 ,p_person_id => l_person_id
2232 ,p_person_type_id => l_person_type_id
2233 ,p_datetrack_update_mode => 'UPDATE'
2234 );
2235 --
2236 -- When in validation only mode raise the Validate_Enabled exception
2237 --
2238 if p_validate then
2239 raise hr_api.validate_enabled;
2240 end if;
2241 --
2242 -- Set all output arguments
2243 --
2244 p_asg_future_changes_warning := l_asg_future_changes_warning;
2245 p_entries_changed_warning := l_entries_changed_warning;
2246 p_pay_proposal_warning := l_pay_proposal_warn;
2247 p_dod_warning := l_dod_warning;
2248 p_last_standard_process_date := l_last_standard_process_date;
2249 p_object_version_number := l_pdp_object_version_number;
2250 --
2251 --
2252 hr_utility.set_location(' Leaving:'||l_proc, 340);
2253 exception
2254 when hr_api.validate_enabled then
2255 --
2256 -- As the Validate_Enabled exception has been raised
2257 -- we must rollback to the savepoint
2258 --
2259 ROLLBACK TO actual_termination_placement;
2260 --
2261 -- Only set output warning arguments
2262 -- (Any key or derived arguments must be set to null
2263 -- when validation only mode is being used.)
2264 --
2265 p_asg_future_changes_warning := l_asg_future_changes_warning;
2266 p_entries_changed_warning := l_entries_changed_warning;
2267 p_pay_proposal_warning := l_pay_proposal_warn;
2268 p_dod_warning := l_dod_warning;
2269 --
2270 -- p_object_version_number and p_last_standard_process_date
2271 -- should return their IN values, they still hold their IN values
2272 -- so do nothing here.
2273 --
2274 --
2275 when others then
2276 --
2277 -- A validation or unexpected error has occurred
2278 --
2279 ROLLBACK TO actual_termination_placement;
2280 --
2281 -- set in out parameters and set out parameters
2282 --
2283 p_object_version_number := l_pdp_object_version_number;
2284 p_last_standard_process_date := l_last_standard_process_date;
2285 p_supervisor_warning := null;
2286 p_event_warning := null;
2287 p_interview_warning := null;
2288 p_review_warning := null;
2289 p_recruiter_warning := null;
2290 p_asg_future_changes_warning := null;
2291 p_entries_changed_warning := null;
2292 p_pay_proposal_warning := null;
2293 p_dod_warning := null;
2294
2295 raise;
2296 --
2297 -- End of fix.
2298 --
2299 end actual_termination_placement;
2300 --
2301 -- ----------------------------------------------------------------------------
2302 -- |-------------------------< final_process_placement >----------------------|
2303 -- ----------------------------------------------------------------------------
2304 --
2305 procedure final_process_placement
2306 (p_validate in boolean default false
2307 ,p_person_id in number
2308 ,p_date_start in date
2309 ,p_object_version_number in out nocopy number
2310 ,p_final_process_date in out nocopy date
2311 ,p_org_now_no_manager_warning out nocopy boolean
2312 ,p_asg_future_changes_warning out nocopy boolean
2313 ,p_entries_changed_warning out nocopy varchar2
2314 ) is
2315 --
2316 -- Declare cursors and local variables
2317 --
2318 -- Out variables
2319 --
2320 l_asg_future_changes_warning boolean := FALSE;
2321 l_entries_changed_warning varchar2(1) := 'N';
2322 l_final_process_date date;
2323 l_org_now_no_manager_warning boolean := FALSE;
2324 l_pdp_object_version_number number;
2325 --
2326 l_actual_termination_date date;
2327 l_cr_asg_future_changes_warn boolean := FALSE;
2328 l_cr_entries_changed_warn varchar2(1) := 'N';
2329 l_cr_org_now_no_manager_warn boolean := FALSE;
2330 l_effective_end_date date;
2331 l_effective_start_date date;
2332 l_exists varchar2(1);
2333 l_last_standard_process_date date;
2334 l_legislation_code per_business_groups.legislation_code%TYPE;
2335 l_object_version_number number;
2336 l_person_id number;
2337 l_proc varchar2(72) := g_package ||
2338 'final_process_placement';
2339 l_exemppet_eff_date date;
2340 --
2341 cursor csr_get_derived_details is
2342 select bus.legislation_code
2343 , pdp.actual_termination_date
2344 , pdp.last_standard_process_date
2345 , pdp.person_id
2346 , pdp.object_version_number
2347 from per_business_groups bus
2348 , per_periods_of_placement pdp
2349 where pdp.person_id = p_person_id
2350 and pdp.date_start = p_date_start
2351 and bus.business_group_id = pdp.business_group_id;
2352 --
2353 cursor csr_get_asgs_to_final_proc is
2354 select asg.assignment_id
2355 , asg.object_version_number
2356 , asg.primary_flag
2357 from per_all_assignments_f asg
2358 where asg.person_id = p_person_id
2359 and asg.period_of_placement_date_start = p_date_start
2360 and l_final_process_date between asg.effective_start_date
2361 and asg.effective_end_date
2362 order by asg.primary_flag;
2363 --
2364
2365 begin
2366 hr_utility.set_location('Entering:'|| l_proc, 1);
2367 --
2368 l_final_process_date := trunc(p_final_process_date);
2369 --
2370 -- Issue a savepoint.
2371 --
2372 savepoint final_process_placement;
2373
2374 hr_utility.set_location(l_proc, 10);
2375 --
2376 -- Validation in addition to Table Handlers
2377 --
2378 -- Check person id.
2379 --
2380 hr_api.mandatory_arg_error
2381 (p_api_name => l_proc
2382 ,p_argument => 'person_id'
2383 ,p_argument_value => p_person_id
2384 );
2385 --
2386 -- Check date start.
2387 --
2388 hr_api.mandatory_arg_error
2389 (p_api_name => l_proc
2390 ,p_argument => 'date_start'
2391 ,p_argument_value => p_date_start
2392 );
2393 --
2394 -- Check object version number.
2395 --
2396 hr_api.mandatory_arg_error
2397 (p_api_name => l_proc
2398 ,p_argument => 'object_version_number'
2399 ,p_argument_value => p_object_version_number
2400 );
2401 --
2402 hr_utility.set_location(l_proc, 20);
2403 --
2404 open csr_get_derived_details;
2405 fetch csr_get_derived_details
2406 into l_legislation_code
2407 ,l_actual_termination_date
2408 ,l_last_standard_process_date
2409 ,l_person_id
2410 ,l_object_version_number;
2411 --
2412 if csr_get_derived_details%NOTFOUND
2413 then
2414 --
2415 hr_utility.set_location(l_proc, 30);
2416 --
2417 close csr_get_derived_details;
2418 --
2419 fnd_message.set_name('PER','HR_289609_PDP_NOT_EXISTS');
2420 fnd_message.raise_error;
2421 end if;
2422 --
2423 close csr_get_derived_details;
2424 --
2425 -- Validate the derived OVN with passed OVN.
2426
2427 if l_object_version_number <> p_object_version_number
2428 then
2429
2430 fnd_message.set_name('PAY','HR_7155_OBJECT_INVALID');
2431 fnd_message.raise_error;
2432 end if;
2433
2434 hr_utility.set_location(l_proc, 40);
2435 --
2436 -- Check that the actual termination date has already been set.
2437 --
2438 if l_actual_termination_date is null
2439 then
2440 --
2441 hr_utility.set_location(l_proc, 50);
2442 --
2443 fnd_message.set_name('PAY','HR_51007_ASG_INV_NOT_ACT_TERM');
2444 fnd_message.raise_error;
2445 end if;
2446 --
2447 -- Check if the final process date is set
2448 --
2449 if l_legislation_code = 'US'
2450 and p_final_process_date is null
2451 then
2452 --
2453 -- Default the FPD to the LSPD
2454 --
2455 l_final_process_date := l_actual_termination_date;
2456 --
2457 -- Add one day to the last standard process date to get the
2458 -- validation date
2459 --
2460 -- Set the EX CWK effective date to the FPD + 1
2461 --
2462 -- Note: Since the FPD equals the ATD then the cwk
2463 -- has not been Ex CWK for at least one day
2464 --
2465 l_exemppet_eff_date := l_final_process_date+1;
2466 --
2467 elsif p_final_process_date is null
2468 then
2469 --
2470 -- Default the FPD to the LSPD
2471 --
2472 l_final_process_date := l_last_standard_process_date;
2473 --
2474 -- Add one day to the last standard process date to get the
2475 -- validation date
2476 --
2477 -- Set the EX CWK effective date to the FPD + 1
2478 --
2479 -- Note: Since the FPD equals the LSPD then the cwk
2480 -- has not been Ex CWK for at least one day
2481 --
2482 l_exemppet_eff_date := l_final_process_date+1;
2483 --
2484 elsif p_final_process_date = l_actual_termination_date then
2485 --
2486 l_final_process_date := p_final_process_date;
2487 --
2488 -- Set the EX CWK effective date to the FPD + 1
2489 --
2490 -- Note: Since the FPD equals the ATD then the cwk
2491 -- has not been Ex CWK for at least one day
2492 --
2493 l_exemppet_eff_date := p_final_process_date+1;
2494 --
2495 else
2496 --
2497 l_final_process_date := p_final_process_date;
2498 --
2499 l_exemppet_eff_date := p_final_process_date;
2500 --
2501 end if;
2502 --
2503 hr_utility.set_location(l_proc, 90);
2504 --
2505 -- Check that the corresponding person is of EX_CWK system person type.
2506 --
2507 if not (hr_general2.is_person_type
2508 (p_person_id => p_person_id
2509 ,p_person_type => 'EX_CWK'
2510 ,p_effective_date => l_exemppet_eff_date))
2511 then
2512 --
2513 hr_utility.set_location(l_proc, 100);
2514 --
2515 fnd_message.set_name('PER','HR_289613_PDP_NOT_PTU_EX_CWK');
2516 fnd_message.raise_error;
2517 --
2518 end if;
2519 --
2520 hr_utility.set_location(l_proc, 110);
2521 --
2522 --
2523 -- Check that there are no COBRA benefits after the final process date.
2524 --
2525 -- Not implemented yet due to outstanding issues.
2526 --
2527 -- Update final process date in periods of placement table.
2528 --
2529 per_pdp_upd.upd
2530 (p_person_id => p_person_id
2531 ,p_date_start => p_date_start
2532 ,p_final_process_date => l_final_process_date
2533 ,p_object_version_number => l_object_version_number
2534 ,p_effective_date => l_final_process_date
2535 );
2536
2537 --
2538 hr_utility.set_location(l_proc, 120);
2539 --
2540 -- Final process the assignments, ensuring that the non-primaries are
2541 -- processed before the primary (implemented via 'order by primary_flag'
2542 -- clause in cursor declaration).
2543 --
2544 for csr_rec in csr_get_asgs_to_final_proc
2545 loop
2546 --
2547 hr_utility.set_location(l_proc, 130);
2548 --
2549 hr_assignment_internal.final_process_cwk_asg
2550 (p_assignment_id => csr_rec.assignment_id
2551 ,p_object_version_number => csr_rec.object_version_number
2552 ,p_actual_termination_date => l_actual_termination_date
2553 ,p_final_process_date => l_final_process_date
2554 ,p_effective_start_date => l_effective_start_date
2555 ,p_effective_end_date => l_effective_end_date
2556 ,p_org_now_no_manager_warning => l_cr_org_now_no_manager_warn
2557 ,p_asg_future_changes_warning => l_cr_asg_future_changes_warn
2558 ,p_entries_changed_warning => l_cr_entries_changed_warn
2559 );
2560 --
2561 hr_utility.set_location(l_proc, 140);
2562 --
2563 -- Set entries changed warning using the precedence of 'S', then 'Y', then
2564 -- 'N'.
2565 --
2566 if l_cr_entries_changed_warn = 'S'
2567 or l_entries_changed_warning = 'S'
2568 then
2569 --
2570 hr_utility.set_location(l_proc, 150);
2571 --
2572 l_entries_changed_warning := 'S';
2573 --
2574 elsif l_cr_entries_changed_warn = 'Y'
2575 or l_entries_changed_warning = 'Y'
2576 then
2577 --
2578 hr_utility.set_location(l_proc, 160);
2579 --
2580 l_entries_changed_warning := 'Y';
2581 --
2582 else
2583 --
2584 hr_utility.set_location(l_proc, 165);
2585 --
2586 l_entries_changed_warning := 'N';
2587 --
2588 end if;
2589 --
2590 hr_utility.set_location(l_proc, 170);
2591 --
2592 -- Set future changes warning.
2593 --
2594 if l_cr_asg_future_changes_warn
2595 then
2596 --
2597 hr_utility.set_location(l_proc, 180);
2598 --
2599 l_asg_future_changes_warning := TRUE;
2600 end if;
2601 --
2602 -- Set org now no manager warning.
2603 --
2604 if l_cr_org_now_no_manager_warn
2605 then
2606 --
2607 hr_utility.set_location(l_proc, 190);
2608 --
2609 l_org_now_no_manager_warning := TRUE;
2610 end if;
2611 end loop;
2612
2613 hr_utility.set_location(l_proc, 200);
2614
2615 --
2616 -- When in validation only mode raise the Validate_Enabled exception
2617 --
2618 if p_validate then
2619 raise hr_api.validate_enabled;
2620 end if;
2621 --
2622 -- Set all output arguments
2623 --
2624 p_asg_future_changes_warning := l_asg_future_changes_warning;
2625 p_entries_changed_warning := l_entries_changed_warning;
2626 p_final_process_date := l_final_process_date;
2627 p_object_version_number := l_object_version_number;
2628 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2629 --
2630 hr_utility.set_location(' Leaving:'||l_proc, 400);
2631 exception
2632 when hr_api.validate_enabled then
2633 --
2634 -- As the Validate_Enabled exception has been raised
2635 -- we must rollback to the savepoint
2636 --
2637 ROLLBACK TO final_process_placement;
2638 --
2639 -- Only set output warning arguments
2640 -- (Any key or derived arguments must be set to null
2641 -- when validation only mode is being used.)
2642 --
2643 p_asg_future_changes_warning := l_asg_future_changes_warning;
2644 p_entries_changed_warning := l_entries_changed_warning;
2645 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2646 --
2647 when others then
2648 --
2649 -- A validation or unexpected error has occurred
2650 --
2651 ROLLBACK TO final_process_placement;
2652 --
2653 -- set in out parameters and set out parameters
2654 --
2655 p_object_version_number := l_object_version_number;
2656 p_final_process_date := l_final_process_date;
2657 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2658 p_asg_future_changes_warning := l_asg_future_changes_warning;
2659 p_entries_changed_warning := null;
2660 --
2661 raise;
2662 --
2663 end final_process_placement;
2664 --
2665 -- ----------------------------------------------------------------------------
2666 -- |-------------------------< terminate_placement >--------------------------|
2667 -- ----------------------------------------------------------------------------
2668 --
2669 procedure terminate_placement
2670 (p_validate in boolean default false
2671 ,p_effective_date in date
2672 ,p_person_id in number
2673 ,p_date_start in date
2674 ,p_object_version_number in out nocopy number
2675 ,p_person_type_id in number default hr_api.g_number
2676 ,p_assignment_status_type_id in number default hr_api.g_number
2677 ,p_actual_termination_date in date default hr_api.g_date
2678
2679 /*
2680 The following two parameters are available for internal-use only until
2681 payroll support for contingent workers is introduced. Setting them has
2682 no impact.
2683 */
2684 ,p_final_process_date in out nocopy date
2685 ,p_last_standard_process_date in out nocopy date
2686
2687 ,p_termination_reason in varchar2 default hr_api.g_varchar2
2688 ,p_projected_termination_date in date default hr_api.g_date
2689 ,p_attribute_category in varchar2 default hr_api.g_varchar2
2690 ,p_attribute1 in varchar2 default hr_api.g_varchar2
2691 ,p_attribute2 in varchar2 default hr_api.g_varchar2
2692 ,p_attribute3 in varchar2 default hr_api.g_varchar2
2693 ,p_attribute4 in varchar2 default hr_api.g_varchar2
2694 ,p_attribute5 in varchar2 default hr_api.g_varchar2
2695 ,p_attribute6 in varchar2 default hr_api.g_varchar2
2696 ,p_attribute7 in varchar2 default hr_api.g_varchar2
2697 ,p_attribute8 in varchar2 default hr_api.g_varchar2
2698 ,p_attribute9 in varchar2 default hr_api.g_varchar2
2699 ,p_attribute10 in varchar2 default hr_api.g_varchar2
2700 ,p_attribute11 in varchar2 default hr_api.g_varchar2
2701 ,p_attribute12 in varchar2 default hr_api.g_varchar2
2702 ,p_attribute13 in varchar2 default hr_api.g_varchar2
2703 ,p_attribute14 in varchar2 default hr_api.g_varchar2
2704 ,p_attribute15 in varchar2 default hr_api.g_varchar2
2705 ,p_attribute16 in varchar2 default hr_api.g_varchar2
2706 ,p_attribute17 in varchar2 default hr_api.g_varchar2
2707 ,p_attribute18 in varchar2 default hr_api.g_varchar2
2708 ,p_attribute19 in varchar2 default hr_api.g_varchar2
2709 ,p_attribute20 in varchar2 default hr_api.g_varchar2
2710 ,p_attribute21 in varchar2 default hr_api.g_varchar2
2711 ,p_attribute22 in varchar2 default hr_api.g_varchar2
2712 ,p_attribute23 in varchar2 default hr_api.g_varchar2
2713 ,p_attribute24 in varchar2 default hr_api.g_varchar2
2714 ,p_attribute25 in varchar2 default hr_api.g_varchar2
2715 ,p_attribute26 in varchar2 default hr_api.g_varchar2
2716 ,p_attribute27 in varchar2 default hr_api.g_varchar2
2717 ,p_attribute28 in varchar2 default hr_api.g_varchar2
2718 ,p_attribute29 in varchar2 default hr_api.g_varchar2
2719 ,p_attribute30 in varchar2 default hr_api.g_varchar2
2720 ,p_information_category in varchar2 default hr_api.g_varchar2
2721 ,p_information1 in varchar2 default hr_api.g_varchar2
2722 ,p_information2 in varchar2 default hr_api.g_varchar2
2723 ,p_information3 in varchar2 default hr_api.g_varchar2
2724 ,p_information4 in varchar2 default hr_api.g_varchar2
2725 ,p_information5 in varchar2 default hr_api.g_varchar2
2726 ,p_information6 in varchar2 default hr_api.g_varchar2
2727 ,p_information7 in varchar2 default hr_api.g_varchar2
2728 ,p_information8 in varchar2 default hr_api.g_varchar2
2729 ,p_information9 in varchar2 default hr_api.g_varchar2
2730 ,p_information10 in varchar2 default hr_api.g_varchar2
2731 ,p_information11 in varchar2 default hr_api.g_varchar2
2732 ,p_information12 in varchar2 default hr_api.g_varchar2
2733 ,p_information13 in varchar2 default hr_api.g_varchar2
2734 ,p_information14 in varchar2 default hr_api.g_varchar2
2735 ,p_information15 in varchar2 default hr_api.g_varchar2
2736 ,p_information16 in varchar2 default hr_api.g_varchar2
2737 ,p_information17 in varchar2 default hr_api.g_varchar2
2738 ,p_information18 in varchar2 default hr_api.g_varchar2
2739 ,p_information19 in varchar2 default hr_api.g_varchar2
2740 ,p_information20 in varchar2 default hr_api.g_varchar2
2741 ,p_information21 in varchar2 default hr_api.g_varchar2
2742 ,p_information22 in varchar2 default hr_api.g_varchar2
2743 ,p_information23 in varchar2 default hr_api.g_varchar2
2744 ,p_information24 in varchar2 default hr_api.g_varchar2
2745 ,p_information25 in varchar2 default hr_api.g_varchar2
2746 ,p_information26 in varchar2 default hr_api.g_varchar2
2747 ,p_information27 in varchar2 default hr_api.g_varchar2
2748 ,p_information28 in varchar2 default hr_api.g_varchar2
2749 ,p_information29 in varchar2 default hr_api.g_varchar2
2750 ,p_information30 in varchar2 default hr_api.g_varchar2
2751 ,p_supervisor_warning out nocopy boolean
2752 ,p_event_warning out nocopy boolean
2753 ,p_interview_warning out nocopy boolean
2754 ,p_review_warning out nocopy boolean
2755 ,p_recruiter_warning out nocopy boolean
2756 ,p_asg_future_changes_warning out nocopy boolean
2757 ,p_entries_changed_warning out nocopy varchar2
2758 ,p_pay_proposal_warning out nocopy boolean
2759 ,p_dod_warning out nocopy boolean
2760 ,p_org_now_no_manager_warning out nocopy boolean
2761 ,p_addl_rights_warning out nocopy boolean -- Fix 1370960
2762 ) is
2763
2764 l_proc varchar2(80) := g_package||'terminate_placement';
2765 l_curr_actual_termination_date date;
2766 l_curr_final_process_date date;
2767 l_final_process_date date;
2768 dummy number := 0; -- fix 1370960
2769
2770 cursor csr_get_pdp_details is
2771 select actual_termination_date
2772 ,final_process_date
2773 from per_periods_of_placement
2774 where person_id = p_person_id
2775 and date_start = p_date_start;
2776
2777 -- fix 1370960
2778 cursor csr_roles_to_terminate is
2779 select
2780 role_id
2781 , object_version_number
2782 , end_date
2783 from per_roles
2784 where person_id = p_person_id
2785 and p_actual_termination_date
2786 between start_date
2787 and nvl(end_date, hr_api.g_eot);
2788
2789 cursor csr_chk_addl_rights is
2790 select role_id
2791 from per_roles
2792 where person_id = p_person_id
2793 and EMP_RIGHTS_FLAG = 'Y'
2794 and nvl(end_of_rights_date, hr_api.g_eot) > p_actual_termination_date;
2795 -- end fix 1370960
2796
2797 begin
2798
2799 hr_utility.set_location('Entering: '||l_proc, 10);
2800
2801 --
2802 -- Issue a savepoint
2803 --
2804 savepoint terminate_placement;
2805
2806 hr_utility.set_location(l_proc, 15);
2807
2808 /* For CWK Phase I we default the final process
2809 and last standard process date to the actual
2810 termination date so that assignments are immediately
2811 ended.
2812 For CWK Phase II, this defaulting will be removed
2813 so that the behaviour supports CWKs on payrolls. */
2814
2815 -- l_final_process_date := p_final_process_date;
2816 l_final_process_date := p_actual_termination_date;
2817 p_last_standard_process_date := p_actual_termination_date;
2818
2819 --
2820 -- Start of API User Hook for the before hook of terminate_placement
2821 --
2822 begin
2823 hr_contingent_worker_bk4.terminate_placement_b
2824 (p_effective_date => p_effective_date
2825 ,p_person_id => p_person_id
2826 ,p_date_start => p_date_start
2827 ,p_object_version_number => p_object_version_number
2828 ,p_person_type_id => p_person_type_id
2829 ,p_assignment_status_type_id => p_assignment_status_type_id
2830 ,p_actual_termination_date => p_actual_termination_date
2831 ,p_final_process_date => l_final_process_date
2832 ,p_last_standard_process_date => p_last_standard_process_date
2833 ,p_termination_reason => p_termination_reason
2834 ,p_projected_termination_date => null
2835 ,p_attribute_category => p_attribute_category
2836 ,p_attribute1 => p_attribute1
2837 ,p_attribute2 => p_attribute2
2838 ,p_attribute3 => p_attribute3
2839 ,p_attribute4 => p_attribute4
2840 ,p_attribute5 => p_attribute5
2841 ,p_attribute6 => p_attribute6
2842 ,p_attribute7 => p_attribute7
2843 ,p_attribute8 => p_attribute8
2844 ,p_attribute9 => p_attribute9
2845 ,p_attribute10 => p_attribute10
2846 ,p_attribute11 => p_attribute11
2847 ,p_attribute12 => p_attribute12
2848 ,p_attribute13 => p_attribute13
2849 ,p_attribute14 => p_attribute14
2850 ,p_attribute15 => p_attribute15
2851 ,p_attribute16 => p_attribute16
2852 ,p_attribute17 => p_attribute17
2853 ,p_attribute18 => p_attribute18
2854 ,p_attribute19 => p_attribute19
2855 ,p_attribute20 => p_attribute20
2856 ,p_attribute21 => p_attribute21
2857 ,p_attribute22 => p_attribute22
2858 ,p_attribute23 => p_attribute23
2859 ,p_attribute24 => p_attribute24
2860 ,p_attribute25 => p_attribute25
2861 ,p_attribute26 => p_attribute26
2862 ,p_attribute27 => p_attribute27
2863 ,p_attribute28 => p_attribute28
2864 ,p_attribute29 => p_attribute29
2865 ,p_attribute30 => p_attribute30
2866 ,p_information_category => p_information_category
2867 ,p_information1 => p_information1
2868 ,p_information2 => p_information2
2869 ,p_information3 => p_information3
2870 ,p_information4 => p_information4
2871 ,p_information5 => p_information5
2872 ,p_information6 => p_information6
2873 ,p_information7 => p_information7
2874 ,p_information8 => p_information8
2875 ,p_information9 => p_information9
2876 ,p_information10 => p_information10
2877 ,p_information11 => p_information11
2878 ,p_information12 => p_information12
2879 ,p_information13 => p_information13
2880 ,p_information14 => p_information14
2881 ,p_information15 => p_information15
2882 ,p_information16 => p_information16
2883 ,p_information17 => p_information17
2884 ,p_information18 => p_information18
2885 ,p_information19 => p_information19
2886 ,p_information20 => p_information20
2887 ,p_information21 => p_information21
2888 ,p_information22 => p_information22
2889 ,p_information23 => p_information23
2890 ,p_information24 => p_information24
2891 ,p_information25 => p_information25
2892 ,p_information26 => p_information26
2893 ,p_information27 => p_information27
2894 ,p_information28 => p_information28
2895 ,p_information29 => p_information29
2896 ,p_information30 => p_information30
2897 );
2898 exception
2899 when hr_api.cannot_find_prog_unit then
2900 hr_api.cannot_find_prog_unit_error
2901 (p_module_name => 'TERMINATE_PLACEMENT',
2902 p_hook_type => 'BP'
2903 );
2904 end;
2905 --
2906 -- End of API User Hook for the before hook of actual_termination
2907 --
2908 hr_utility.set_location(l_proc, 20);
2909
2910 /*
2911 ** We need to get the details currently on the PDP record so that
2912 ** we know if the person has already been partially or fully terminated.
2913 ** If partial termination (ATD set but FPD not set) then don't call
2914 ** actual_termination_emp API. If full termination (ATD and FPD both set)
2915 ** then don't call either termination API and just update the details.
2916 */
2917 open csr_get_pdp_details;
2918 fetch csr_get_pdp_details
2919 into l_curr_actual_termination_date
2920 ,l_curr_final_process_date;
2921 close csr_get_pdp_details;
2922
2923 hr_utility.set_location(l_proc, 30);
2924
2925 /*
2926 ** Save the non-termination related PDP information....
2927 */
2928 hr_periods_of_placement_api.update_pdp_details
2929 (p_validate => FALSE
2930 ,p_effective_date => p_effective_date
2931 ,p_object_version_number => p_object_version_number
2932 ,p_person_id => p_person_id
2933 ,p_date_start => p_date_start
2934 ,p_termination_reason => p_termination_reason
2935 ,p_projected_termination_date => null
2936 ,p_attribute_category => p_attribute_category
2937 ,p_attribute1 => p_attribute1
2938 ,p_attribute2 => p_attribute2
2939 ,p_attribute3 => p_attribute3
2940 ,p_attribute4 => p_attribute4
2941 ,p_attribute5 => p_attribute5
2942 ,p_attribute6 => p_attribute6
2943 ,p_attribute7 => p_attribute7
2944 ,p_attribute8 => p_attribute8
2945 ,p_attribute9 => p_attribute9
2946 ,p_attribute10 => p_attribute10
2947 ,p_attribute11 => p_attribute11
2948 ,p_attribute12 => p_attribute12
2949 ,p_attribute13 => p_attribute13
2950 ,p_attribute14 => p_attribute14
2951 ,p_attribute15 => p_attribute15
2952 ,p_attribute16 => p_attribute16
2953 ,p_attribute17 => p_attribute17
2954 ,p_attribute18 => p_attribute18
2955 ,p_attribute19 => p_attribute19
2956 ,p_attribute20 => p_attribute20
2957 ,p_attribute21 => p_attribute21
2958 ,p_attribute22 => p_attribute22
2959 ,p_attribute23 => p_attribute23
2960 ,p_attribute24 => p_attribute24
2961 ,p_attribute25 => p_attribute25
2962 ,p_attribute26 => p_attribute26
2963 ,p_attribute27 => p_attribute27
2964 ,p_attribute28 => p_attribute28
2965 ,p_attribute29 => p_attribute29
2966 ,p_attribute30 => p_attribute30
2967 ,p_information_category => p_information_category
2968 ,p_information1 => p_information1
2969 ,p_information2 => p_information2
2970 ,p_information3 => p_information3
2971 ,p_information4 => p_information4
2972 ,p_information5 => p_information5
2973 ,p_information6 => p_information6
2974 ,p_information7 => p_information7
2975 ,p_information8 => p_information8
2976 ,p_information9 => p_information9
2977 ,p_information10 => p_information10
2978 ,p_information11 => p_information11
2979 ,p_information12 => p_information12
2980 ,p_information13 => p_information13
2981 ,p_information14 => p_information14
2982 ,p_information15 => p_information15
2983 ,p_information16 => p_information16
2984 ,p_information17 => p_information17
2985 ,p_information18 => p_information18
2986 ,p_information19 => p_information19
2987 ,p_information20 => p_information20
2988 ,p_information21 => p_information21
2989 ,p_information22 => p_information22
2990 ,p_information23 => p_information23
2991 ,p_information24 => p_information24
2992 ,p_information25 => p_information25
2993 ,p_information26 => p_information26
2994 ,p_information27 => p_information27
2995 ,p_information28 => p_information28
2996 ,p_information29 => p_information29
2997 ,p_information30 => p_information30
2998 );
2999
3000 hr_utility.set_location(l_proc, 40);
3001
3002 /*
3003 ** Process actual termination date if it's set for the first time....
3004 */
3005 if l_curr_actual_termination_date is null
3006 and p_actual_termination_date is not null
3007 then
3008
3009 hr_utility.set_location(l_proc, 50);
3010
3011 hr_contingent_worker_api.actual_termination_placement
3012 (p_validate => FALSE
3013 ,p_effective_date => p_effective_date
3014 ,p_person_id => p_person_id
3015 ,p_date_start => p_date_start
3016 ,p_object_version_number => p_object_version_number
3017 ,p_actual_termination_date => p_actual_termination_date
3018 ,p_last_standard_process_date => p_last_standard_process_date
3019 ,p_person_type_id => p_person_type_id
3020 ,p_assignment_status_type_id => p_assignment_status_type_id
3021 ,p_termination_reason => p_termination_reason
3022 ,p_supervisor_warning => p_supervisor_warning
3023 ,p_event_warning => p_event_warning
3024 ,p_interview_warning => p_interview_warning
3025 ,p_review_warning => p_review_warning
3026 ,p_recruiter_warning => p_recruiter_warning
3027 ,p_asg_future_changes_warning => p_asg_future_changes_warning
3028 ,p_entries_changed_warning => p_entries_changed_warning
3029 ,p_pay_proposal_warning => p_pay_proposal_warning
3030 ,p_dod_warning => p_dod_warning
3031 );
3032
3033 -- fix 1370960
3034 -- Terminate the roles
3035 for roles_rec in csr_roles_to_terminate
3036 loop
3037 per_supplementary_role_api.update_supplementary_role(
3038 p_effective_date => p_effective_date
3039 ,p_role_id => roles_rec.role_id
3040 ,p_object_version_number => roles_rec.object_version_number
3041 ,p_end_date => p_actual_termination_date
3042 ,p_old_end_date => roles_rec.end_date
3043 );
3044 end loop;
3045
3046 -- Raise a warning if extra rights are there for the person
3047 open csr_chk_addl_rights;
3048 fetch csr_chk_addl_rights into dummy;
3049 if csr_chk_addl_rights%found then
3050 p_addl_rights_warning := TRUE;
3051 else
3052 p_addl_rights_warning := FALSE;
3053 end if;
3054 close csr_chk_addl_rights;
3055 -- end fix 1370960
3056
3057 end if;
3058
3059 hr_utility.set_location(l_proc, 60);
3060
3061 /*
3062 ** If it's set process final process date....
3063 */
3064 if l_curr_final_process_date is null
3065 and l_final_process_date is not null
3066 then
3067
3068 hr_utility.set_location(l_proc, 70);
3069
3070 hr_contingent_worker_api.final_process_placement
3071 (p_validate => FALSE
3072 ,p_person_id => p_person_id
3073 ,p_date_start => p_date_start
3074 ,p_object_version_number => p_object_version_number
3075 ,p_final_process_date => l_final_process_date
3076 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
3077 ,p_asg_future_changes_warning => p_asg_future_changes_warning
3078 ,p_entries_changed_warning => p_entries_changed_warning
3079 );
3080 end if;
3081
3082 hr_utility.set_location(l_proc, 80);
3083 --
3084 -- Added HR workflow for the termination process. Bug 3829474
3085 -- Even though the following call is psecific to period of service,
3086 -- used the same forperiod of placement.
3087 --
3088
3089 /* Bug 5504659
3090 PER_HRWF_SYNCH.per_pds_wf(
3091 p_person_id => p_person_id
3092 ,p_date_start => p_actual_termination_date
3093 ,p_date => p_actual_termination_date
3094 ,p_action => 'TERMINATION');
3095 Note : added p_date_start to test, earlier code does not work*/
3096
3097 --
3098 -- Fix for bug 3829474 ends here.
3099 --
3100 -- Start of API User Hook for the after hook of terminate_placement
3101 --
3102 begin
3103 hr_contingent_worker_bk4.terminate_placement_a
3104 (p_effective_date => p_effective_date
3105 ,p_person_id => p_person_id
3106 ,p_date_start => p_date_start
3107 ,p_object_version_number => p_object_version_number
3108 ,p_person_type_id => p_person_type_id
3109 ,p_assignment_status_type_id => p_assignment_status_type_id
3110 ,p_actual_termination_date => p_actual_termination_date
3111 ,p_final_process_date => l_final_process_date
3112 ,p_last_standard_process_date => p_last_standard_process_date
3113 ,p_termination_reason => p_termination_reason
3114 ,p_projected_termination_date => null
3115 ,p_attribute_category => p_attribute_category
3116 ,p_attribute1 => p_attribute1
3117 ,p_attribute2 => p_attribute2
3118 ,p_attribute3 => p_attribute3
3119 ,p_attribute4 => p_attribute4
3120 ,p_attribute5 => p_attribute5
3121 ,p_attribute6 => p_attribute6
3122 ,p_attribute7 => p_attribute7
3123 ,p_attribute8 => p_attribute8
3124 ,p_attribute9 => p_attribute9
3125 ,p_attribute10 => p_attribute10
3126 ,p_attribute11 => p_attribute11
3127 ,p_attribute12 => p_attribute12
3128 ,p_attribute13 => p_attribute13
3129 ,p_attribute14 => p_attribute14
3130 ,p_attribute15 => p_attribute15
3131 ,p_attribute16 => p_attribute16
3132 ,p_attribute17 => p_attribute17
3133 ,p_attribute18 => p_attribute18
3134 ,p_attribute19 => p_attribute19
3135 ,p_attribute20 => p_attribute20
3136 ,p_attribute21 => p_attribute21
3137 ,p_attribute22 => p_attribute22
3138 ,p_attribute23 => p_attribute23
3139 ,p_attribute24 => p_attribute24
3140 ,p_attribute25 => p_attribute25
3141 ,p_attribute26 => p_attribute26
3142 ,p_attribute27 => p_attribute27
3143 ,p_attribute28 => p_attribute28
3144 ,p_attribute29 => p_attribute29
3145 ,p_attribute30 => p_attribute30
3146 ,p_information_category => p_information_category
3147 ,p_information1 => p_information1
3148 ,p_information2 => p_information2
3149 ,p_information3 => p_information3
3150 ,p_information4 => p_information4
3151 ,p_information5 => p_information5
3152 ,p_information6 => p_information6
3153 ,p_information7 => p_information7
3154 ,p_information8 => p_information8
3155 ,p_information9 => p_information9
3156 ,p_information10 => p_information10
3157 ,p_information11 => p_information11
3158 ,p_information12 => p_information12
3159 ,p_information13 => p_information13
3160 ,p_information14 => p_information14
3161 ,p_information15 => p_information15
3162 ,p_information16 => p_information16
3163 ,p_information17 => p_information17
3164 ,p_information18 => p_information18
3165 ,p_information19 => p_information19
3166 ,p_information20 => p_information20
3167 ,p_information21 => p_information21
3168 ,p_information22 => p_information22
3169 ,p_information23 => p_information23
3170 ,p_information24 => p_information24
3171 ,p_information25 => p_information25
3172 ,p_information26 => p_information26
3173 ,p_information27 => p_information27
3174 ,p_information28 => p_information28
3175 ,p_information29 => p_information29
3176 ,p_information30 => p_information30
3177 ,p_supervisor_warning => p_supervisor_warning
3178 ,p_event_warning => p_event_warning
3179 ,p_interview_warning => p_interview_warning
3180 ,p_review_warning => p_review_warning
3181 ,p_recruiter_warning => p_recruiter_warning
3182 ,p_asg_future_changes_warning => p_asg_future_changes_warning
3183 ,p_entries_changed_warning => p_entries_changed_warning
3184 ,p_pay_proposal_warning => p_pay_proposal_warning
3185 ,p_dod_warning => p_dod_warning
3186 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
3187 ,p_addl_rights_warning => p_addl_rights_warning -- Fix 1370960
3188 );
3189 exception
3190 when hr_api.cannot_find_prog_unit then
3191 hr_api.cannot_find_prog_unit_error
3192 (p_module_name => 'TERMINATE_PLACEMENT',
3193 p_hook_type => 'AP'
3194 );
3195 end;
3196
3197 --
3198 -- When in validation only mode raise the Validate_Enabled exception
3199 --
3200 IF p_validate THEN
3201 RAISE hr_api.validate_enabled;
3202 END IF;
3203
3204 hr_utility.set_location('Leaving: '||l_proc, 90);
3205
3206 EXCEPTION
3207 when hr_api.validate_enabled then
3208 --
3209 -- As the Validate_Enabled exception has been raised
3210 -- we must rollback to the savepoint
3211 --
3212 ROLLBACK TO terminate_placement;
3213
3214 when others then
3215 --
3216 -- A validation or unexpected error has occurred
3217 --
3218 ROLLBACK TO terminate_placement;
3219 --
3220 -- set in out parameters and set out parameters
3221 --
3222 --p_object_version_number := l_object_version_number;
3223 --
3224 raise;
3225 --
3226 hr_utility.set_location('Leaving: '||l_proc, 100);
3227
3228 end terminate_placement;
3229 --
3230 -- ----------------------------------------------------------------------------
3231 -- |-------------------------< reverse_terminate_placement >------------------|
3232 -- ----------------------------------------------------------------------------
3233 --
3234 procedure reverse_terminate_placement
3235 (p_validate in boolean default false
3236 ,p_person_id in number
3237 ,p_actual_termination_date in date
3238 ,p_clear_details in varchar2 default 'N'
3239 ,p_fut_actns_exist_warning out nocopy boolean
3240 ) is
3241
3242 l_proc varchar2(80) := g_package||'reverse_terminate_placement';
3243 l_final_process_date DATE;
3244 l_last_standard_process_date DATE;
3245 l_per_system_status VARCHAR2(30);
3246 l_max_end_date DATE;
3247 l_effective_end_date DATE;
3248 l_action_chk VARCHAR2(1) := 'N';
3249 l_action_date DATE;
3250 l_asg_status_type_id NUMBER;
3251 FPD_FLAG BOOLEAN;
3252 b_future_person_type_err BOOLEAN := FALSE;
3253
3254 CURSOR c_assignment IS
3255 SELECT assignment_id
3256 , assignment_status_type_id
3257 , business_group_id
3258 FROM per_all_assignments_f ass
3259 WHERE ass.person_id = p_person_id
3260 AND ass.effective_end_date = p_actual_termination_date
3261 FOR UPDATE;
3262 --
3263 CURSOR future_person_types IS
3264 SELECT pt.system_person_type
3265 FROM per_person_type_usages_f ptu,
3266 per_person_types pt
3267 WHERE ptu.person_id = p_person_id
3268 AND ptu.person_type_id = pt.person_type_id
3269 AND ptu.effective_start_date > p_actual_termination_date;
3270
3271 cursor c1 is
3272 select *
3273 from per_periods_of_placement
3274 where person_id = p_person_id
3275 and actual_termination_date = p_actual_termination_date;
3276 --
3277
3278 -- fix 1370960
3279 cursor csr_roles is
3280 select role_id
3281 ,object_version_number
3282 ,old_end_date from
3283 per_roles
3284 where person_id = p_person_id
3285 and end_date = p_actual_termination_date
3286 for update nowait;
3287 -- fix 1370960 end
3288
3289 l_c1 c1%rowtype;
3290 --
3291 --
3292 -- START WWBUG fix for 1390173
3293 --
3294 l_old ben_pps_ler.g_pps_ler_rec;
3295 l_new ben_pps_ler.g_pps_ler_rec;
3296 --
3297 -- END WWBUG fix for 1390173
3298 --
3299 --
3300 -- Start of Fix for WWBUG 1408379
3301 --
3302 cursor c2(p_assignment_id number) is
3303 select *
3304 from per_assignment_budget_values_f
3305 where assignment_id = p_assignment_id
3306 and effective_end_date = l_final_process_date;
3307 --
3308 l_old_abv ben_abv_ler.g_abv_ler_rec;
3309 l_new_abv ben_abv_ler.g_abv_ler_rec;
3310 l_c2 c2%rowtype;
3311 --
3312 -- End of Fix for WWBUG 1408379
3313 --
3314
3315 begin
3316
3317 hr_utility.set_location('Entering: '||l_proc, 10);
3318
3319 --
3320 -- Issue a savepoint.
3321 --
3322 savepoint reverse_terminate_placement;
3323
3324 hr_api.mandatory_arg_error
3325 (p_api_name => l_proc
3326 ,p_argument => 'person_id'
3327 ,p_argument_value => p_person_id
3328 );
3329 --
3330 hr_api.mandatory_arg_error
3331 (p_api_name => l_proc
3332 ,p_argument => 'actual_termination_date'
3333 ,p_argument_value => p_actual_termination_date
3334 );
3335
3336 --
3337 -- Start of API User Hook for the before hook of reverse_terminate_placement
3338 --
3339 begin
3340 hr_contingent_worker_bk5.reverse_terminate_placement_b
3341 (p_validate => p_validate
3342 ,p_person_id => p_person_id
3343 ,p_actual_termination_date => p_actual_termination_date
3344 ,p_clear_details => p_clear_details
3345 );
3346 exception
3347 when hr_api.cannot_find_prog_unit then
3348 hr_api.cannot_find_prog_unit_error
3349 (p_module_name => 'REVERSE_TERMINATE_PLACEMENT',
3350 p_hook_type => 'BP'
3351 );
3352 end;
3353 --
3354 -- End of API User Hook for the before hook of reverse_terminate_placement
3355 --
3356 hr_utility.set_location(l_proc, 20);
3357
3358 hr_utility.trace('Entered reverse termination for '||p_person_id);
3359 --
3360 hr_utility.set_location(l_proc,25);
3361 begin
3362 SELECT pdp.final_process_date
3363 , pdp.last_standard_process_date
3364 INTO l_final_process_date
3365 , l_last_standard_process_date
3366 FROM per_periods_of_placement pdp
3367 WHERE pdp.person_id = p_person_id
3368 AND pdp.actual_termination_date = p_actual_termination_date;
3369 --
3370 exception when NO_DATA_FOUND then
3371
3372 fnd_message.set_name('PER','HR_289614_PDP_STILL_OPEN');
3373 fnd_message.raise_error;
3374 end;
3375 --
3376 hr_utility.set_location(l_proc,30);
3377
3378 --
3379 -- Check for future person types
3380 --
3381 for fpt_rec in future_person_types loop
3382
3383 hr_utility.set_location(l_proc,35);
3384 hr_utility.trace('System person type: '||fpt_rec.system_person_type);
3385 -- start of bug 4457651
3386 -- commented out the if condition and redifined it
3387 -- if fpt_rec.system_person_type <> 'EX_CWK' then
3388 if fpt_rec.system_person_type in ('EMP','APL','CWK') then
3389 b_future_person_type_err := TRUE;
3390 end if;
3391
3392 end loop;
3393
3394 if b_future_person_type_err = TRUE then
3395 fnd_message.set_name('PAY','HR_7122_EMP_CNCL_TERM_INVLD');
3396 fnd_message.raise_error;
3397 end if;
3398 --
3399 hr_utility.set_location(l_proc,40);
3400
3401 --
3402 -- Check for future completed actions.
3403 --
3404 if l_last_standard_process_date is not null then
3405
3406 if p_actual_termination_date is not null
3407 and l_last_standard_process_date > p_actual_termination_date then
3408 l_action_date := l_last_standard_process_date;
3409 else
3410 l_action_date := null;
3411 end if;
3412
3413 else
3414
3415 l_action_date := p_actual_termination_date;
3416
3417 end if;
3418
3419 BEGIN
3420 SELECT 'Y'
3421 INTO l_action_chk
3422 FROM dual
3423 WHERE exists
3424 (SELECT null
3425 FROM pay_payroll_actions pac,
3426 pay_assignment_actions act,
3427 per_all_assignments_f asg
3428 WHERE asg.person_id = p_person_id
3429 AND act.assignment_id = asg.assignment_id
3430 AND pac.payroll_action_id = act.payroll_action_id
3431 AND pac.action_type not in ('X','BEE') -- Bug 889806,2711532
3432 AND pac.effective_date > l_final_process_date);
3433 exception when NO_DATA_FOUND then null;
3434 END;
3435 --
3436 hr_utility.set_location(l_proc,45);
3437
3438 IF l_action_chk = 'N' THEN
3439 BEGIN
3440 SELECT 'W'
3441 INTO l_action_chk
3442 FROM sys.dual
3443 WHERE exists
3444 (SELECT null
3445 FROM pay_payroll_actions pac,
3446 pay_assignment_actions act,
3447 per_all_assignments_f asg
3448 WHERE asg.person_id = p_person_id
3449 AND act.assignment_id = asg.assignment_id
3450 AND pac.payroll_action_id = act.payroll_action_id
3451 AND pac.action_status = 'C'
3452 AND (pac.effective_date BETWEEN l_action_date AND l_final_process_date));
3453 --
3454 hr_utility.set_location(l_proc,7);
3455 exception when NO_DATA_FOUND then null;
3456 END;
3457 END IF;
3458
3459 --
3460 IF l_action_chk = 'W' THEN
3461
3462 fnd_message.set_name('PER','HR_289615_FUTURE_ACTIONS_EXIST');
3463 fnd_message.raise_error;
3464 END IF;
3465 --
3466 if l_action_chk = 'Y' then
3467 p_fut_actns_exist_warning := TRUE;
3468 end if;
3469 --
3470 hr_utility.set_location(l_proc,50);
3471
3472 FPD_FLAG := (l_final_process_date IS NOT NULL);
3473 --
3474 hr_utility.set_location(l_proc,55);
3475 UPDATE per_people_f pp
3476 SET pp.effective_end_date = hr_api.g_eot
3477 WHERE pp.person_id = p_person_id
3478 AND p_actual_termination_date
3479 BETWEEN pp.effective_start_date
3480 AND pp.effective_end_date;
3481 --
3482 hr_utility.set_location(l_proc,60);
3483 DELETE per_people_f pp
3484 WHERE pp.person_id = p_person_id
3485 AND pp.effective_start_date > p_actual_termination_date;
3486 --
3487 hr_utility.set_location(l_proc,65);
3488 --
3489 -- WWBUG # - CERN want to keep old details
3490 -- was a feature of rel 9
3491 if (p_clear_details = 'N') then
3492 --
3493 --
3494 -- START WWBUG fix for 1390173
3495 --
3496 /* open c1;
3497 fetch c1 into l_c1;
3498 if c1%found then
3499 --
3500 l_old.PERSON_ID := l_c1.person_id;
3501 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
3502 l_old.DATE_START := l_c1.date_start;
3503 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
3504 l_old.TERMINATION_REASON := l_c1.termination_reason;
3505 l_old.ATTRIBUTE1 := l_c1.attribute1;
3506 l_old.ATTRIBUTE2 := l_c1.attribute2;
3507 l_old.ATTRIBUTE3 := l_c1.attribute3;
3508 l_old.ATTRIBUTE4 := l_c1.attribute4;
3509 l_old.ATTRIBUTE5 := l_c1.attribute5;
3510 l_old.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3511 l_new.PERSON_ID := l_c1.person_id;
3512 l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
3513 l_new.DATE_START := l_c1.date_start;
3514 l_new.ACTUAL_TERMINATION_DATE := null;
3515 l_new.TERMINATION_REASON := null;
3516 l_new.ATTRIBUTE1 := l_c1.attribute1;
3517 l_new.ATTRIBUTE2 := l_c1.attribute2;
3518 l_new.ATTRIBUTE3 := l_c1.attribute3;
3519 l_new.ATTRIBUTE4 := l_c1.attribute4;
3520 l_new.ATTRIBUTE5 := l_c1.attribute5;
3521 l_new.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3522 --
3523 ben_pps_ler.ler_chk(p_old => l_old
3524 ,p_new => l_new
3525 ,p_event => 'UPDATING'
3526 ,p_effective_date => l_c1.date_start);
3527 --
3528 end if;
3529 close c1;
3530 --
3531 */
3532 --
3533 -- END WWBUG fix for 1390173
3534 --
3535 UPDATE per_periods_of_placement pdp
3536 SET pdp.actual_termination_date = null
3537 , pdp.last_standard_process_date = null
3538 , pdp.final_process_date = null
3539 , pdp.termination_reason = null
3540 , pdp.projected_termination_date = null
3541 WHERE pdp.person_id = p_person_id
3542 AND pdp.actual_termination_date = p_actual_termination_date;
3543 else
3544 --
3545 --
3546 -- START WWBUG fix for 1390173
3547 --
3548 /*
3549 open c1;
3550 fetch c1 into l_c1;
3551 if c1%found then
3552 --
3553 l_old.PERSON_ID := l_c1.person_id;
3554 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
3555 l_old.DATE_START := l_c1.date_start;
3556 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
3557 l_old.TERMINATION_REASON := l_c1.leaving_reason;
3558 l_old.ATTRIBUTE1 := l_c1.attribute1;
3559 l_old.ATTRIBUTE2 := l_c1.attribute2;
3560 l_old.ATTRIBUTE3 := l_c1.attribute3;
3561 l_old.ATTRIBUTE4 := l_c1.attribute4;
3562 l_old.ATTRIBUTE5 := l_c1.attribute5;
3563 l_old.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3564 l_new.PERSON_ID := l_c1.person_id;
3565 l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
3566 l_new.DATE_START := l_c1.date_start;
3567 l_new.ACTUAL_TERMINATION_DATE := null;
3568 l_new.TERMINATION_REASON := null;
3569 l_new.ATTRIBUTE1 := l_c1.attribute1;
3570 l_new.ATTRIBUTE2 := l_c1.attribute2;
3571 l_new.ATTRIBUTE3 := l_c1.attribute3;
3572 l_new.ATTRIBUTE4 := l_c1.attribute4;
3573 l_new.ATTRIBUTE5 := l_c1.attribute5;
3574 l_new.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3575 --
3576 ben_pps_ler.ler_chk(p_old => l_old
3577 ,p_new => l_new
3578 ,p_event => 'UPDATING'
3579 ,p_effective_date => l_c1.date_start);
3580 --
3581 end if;
3582 close c1;
3583 */
3584 --
3585 --
3586 -- END WWBUG fix for 1390173
3587 --
3588 UPDATE per_periods_of_placement pdp
3589 SET pdp.actual_termination_date = null
3590 , pdp.last_standard_process_date = null
3591 , pdp.final_process_date = null
3592 , pdp.termination_reason = null
3593 , pdp.projected_termination_date = null
3594 WHERE pdp.person_id = p_person_id
3595 AND pdp.actual_termination_date = p_actual_termination_date;
3596 --
3597 end if;
3598
3599 --
3600 -- FIX to WWBUG 1176101
3601 --
3602 /*
3603 ben_dt_trgr_handle.periods_of_service
3604 (p_rowid => null
3605 ,p_person_id => p_person_id
3606 ,p_pds_atd => null
3607 ,p_pds_leaving_reason => null
3608 -- Bug 1854968
3609 ,p_pds_old_atd => l_old.actual_termination_date
3610 ,p_pds_fpd => null);
3611 */
3612 --
3613 --
3614 --
3615 hr_utility.set_location(l_proc,70);
3616 FOR c_asg_rec IN c_assignment LOOP
3617 --
3618 hr_utility.set_location(l_proc,75);
3619 SELECT per_system_status
3620 INTO l_per_system_status
3621 FROM per_assignment_status_types
3622 WHERE assignment_status_type_id = c_asg_rec.assignment_status_type_id;
3623 --
3624 -- Get the assignment_status_id from the record which ended on ATD
3625 -- so that we can set the other records for this assignment which
3626 -- are currently TERM_CWK_ASG back to the appropriate ACTIVE_CWK_ASG status.
3627 --
3628 SELECT assignment_status_type_id
3629 INTO l_asg_status_type_id
3630 FROM per_all_assignments_f
3631 WHERE assignment_id = c_asg_rec.assignment_id
3632 AND effective_end_date = p_actual_termination_date;
3633 --
3634 hr_utility.set_location(l_proc,80);
3635
3636 SELECT max(asg.effective_end_date)
3637 INTO l_max_end_date
3638 FROM per_all_assignments_f asg
3639 WHERE asg.assignment_id = c_asg_rec.assignment_id;
3640
3641 --
3642 if l_per_system_status <> 'TERM_CWK_ASG' then
3643 hr_utility.set_location(l_proc,85);
3644 if FPD_FLAG then
3645 hr_utility.set_location(l_proc,90);
3646 if l_max_end_date <> l_final_process_date then
3647 l_effective_end_date := l_max_end_date;
3648 else
3649 hr_utility.set_location(l_proc,95);
3650 l_effective_end_date := hr_api.g_eot;
3651 end if;
3652 else
3653 hr_utility.set_location(l_proc,100);
3654 l_effective_end_date := l_max_end_date;
3655 end if;
3656 --
3657 hr_utility.set_location(l_proc,105);
3658 --
3659 -- Open out the last dated assignment record to the end of time or
3660 -- max_end_date based on above logic.
3661 --
3662 UPDATE per_all_assignments_f ass
3663 SET ass.effective_end_date = l_effective_end_date
3664 WHERE assignment_id = c_asg_rec.assignment_id
3665 AND effective_end_date = l_max_end_date;
3666 --
3667 -- We want to keep all the assignment records after the ATD so
3668 -- update them all to the same assignment_status as the record
3669 -- which ends on ATD.
3670 --
3671 -- As a result of the changes due to bug 1271513 as a result of
3672 -- terminating and reverse terminating an additional assignment
3673 -- record will exist which runs from ATD+1 to the start date-1
3674 -- of the next date effective record for the person or EOT
3675 -- depending on the data present at time of termination.
3676 --
3677 UPDATE per_all_assignments_f ass
3678 SET ass.assignment_status_type_id = l_asg_status_type_id
3679 WHERE assignment_id = c_asg_rec.assignment_id
3680 AND effective_start_date >= p_actual_termination_date;
3681 --
3682 end if;
3683 --
3684 if FPD_FLAG then
3685 hr_utility.set_location(l_proc,110);
3686 if l_max_end_date <> l_final_process_date then
3687 null;
3688 else
3689 hr_utility.set_location(l_proc,115);
3690 l_effective_end_date := hr_api.g_eot;
3691 --
3692 hr_utility.set_location(l_proc,120);
3693 UPDATE per_secondary_ass_statuses sas
3694 SET sas.end_date = null
3695 WHERE sas.assignment_id = c_asg_rec.assignment_id
3696 AND sas.end_date = l_final_process_date;
3697 --
3698 hr_utility.set_location(l_proc,125);
3699 UPDATE pay_personal_payment_methods_f ppm
3700 SET ppm.effective_end_date = l_effective_end_date
3701 WHERE ppm.assignment_id = c_asg_rec.assignment_id
3702 AND ppm.effective_end_date = l_final_process_date;
3703 --
3704 hr_utility.set_location(l_proc,130);
3705 UPDATE pay_cost_allocations_f pca
3706 SET pca.effective_end_date = l_effective_end_date
3707 WHERE pca.assignment_id = c_asg_rec.assignment_id
3708 AND pca.effective_end_date = l_final_process_date;
3709 --
3710 hr_utility.set_location(l_proc,135);
3711 UPDATE per_spinal_point_placements_f spp
3712 SET spp.effective_end_date = l_effective_end_date
3713 WHERE spp.assignment_id = c_asg_rec.assignment_id
3714 AND spp.effective_end_date = l_final_process_date;
3715 --
3716 UPDATE pay_grade_rules_f pgr
3717 SET pgr.effective_end_date = l_effective_end_date
3718 WHERE pgr.grade_or_spinal_point_Id = c_asg_rec.assignment_id
3719 AND pgr.rate_type = 'A'
3720 AND pgr.effective_end_date = l_final_process_date;
3721 --
3722 --
3723 -- Adding code to update the date tracked tax tables to resolve bug
3724 -- 920233.
3725 -- Adding an extra verification to make sure the tax records are reverse
3726 -- only for US legislation. This extra verification is done because
3727 -- this package peempter.pkb is part of the CORE HR code and UK
3728 -- customer do not use these TAX tables. Only Customers with HR/CERIDIAN
3729 -- use this TAX tables.
3730
3731 if hr_general.chk_geocodes_installed ='Y' then
3732 hr_utility.set_location(l_proc,140);
3733 pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
3734 (c_asg_rec.assignment_id
3735 ,l_final_process_date);
3736
3737 end if; /* verification chk_geocodes_installed */
3738 --
3739 -- SASmith 30-APR-1998
3740 -- Due to date tracking of assignment_budget_values
3741
3742 hr_utility.set_location(l_proc,145);
3743 --
3744 -- Start of Fix for WWBUG 1408379
3745 --
3746 open c2(c_asg_rec.assignment_id);
3747 --
3748 loop
3749 --
3750 fetch c2 into l_c2;
3751 exit when c2%notfound;
3752 --
3753 l_old_abv.assignment_id := l_c2.assignment_id;
3754 l_old_abv.business_group_id := l_c2.business_group_id;
3755 l_old_abv.value := l_c2.value;
3756 l_old_abv.assignment_budget_value_id := l_c2.assignment_budget_value_id;
3757 l_old_abv.effective_start_date := l_c2.effective_start_date;
3758 l_old_abv.effective_end_date := l_c2.effective_end_date;
3759 l_new_abv.assignment_id := l_c2.assignment_id;
3760 l_new_abv.business_group_id := l_c2.business_group_id;
3761 l_new_abv.value := l_c2.value;
3762 l_new_abv.assignment_budget_value_id := l_c2.assignment_budget_value_id;
3763 l_new_abv.effective_start_date := l_c2.effective_start_date;
3764 l_new_abv.effective_end_date := l_effective_end_date;
3765 --
3766 update per_assignment_budget_values_f abv
3767 set abv.effective_end_date = l_effective_end_date
3768 where abv.assignment_id = c_asg_rec.assignment_id
3769 and abv.assignment_budget_value_id = l_c2.assignment_budget_value_id
3770 and abv.effective_end_date = l_final_process_date;
3771 /* --
3772 ben_abv_ler.ler_chk(p_old => l_old_abv,
3773 p_new => l_new_abv,
3774 p_effective_date => l_c2.effective_start_date);
3775 */ --
3776 end loop;
3777 --
3778 close c2;
3779 --
3780 -- End of Fix for WWBUG 1408379
3781 --
3782 end if;
3783 --
3784 end if;
3785 --
3786 -- open up element entries closed down by the termination
3787 --
3788 hr_utility.set_location(l_proc,150);
3789 hrentmnt.maintain_entries_asg(c_asg_rec.assignment_id
3790 ,c_asg_rec.business_group_id
3791 ,'CNCL_TERM'
3792 ,p_actual_termination_date
3793 ,l_last_standard_process_date
3794 ,l_final_process_date
3795 ,'DELETE_NEXT_CHANGE'
3796 ,null
3797 ,null);
3798 --
3799 --
3800 END LOOP;
3801
3802 --
3803 -- Change the person type usage record back to contingent worker
3804 --
3805 hr_per_type_usage_internal.cancel_person_type_usage
3806 (p_effective_date => p_actual_termination_date+1
3807 ,p_person_id => p_person_id
3808 ,p_system_person_type => 'EX_CWK'
3809 );
3810
3811 hr_utility.set_location(l_proc, 155);
3812
3813 --
3814 -- Start of API User Hook for the after hook of reverse_terminate_placement
3815 --
3816 begin
3817 hr_contingent_worker_bk5.reverse_terminate_placement_a
3818 (p_validate => p_validate
3819 ,p_person_id => p_person_id
3820 ,p_actual_termination_date => p_actual_termination_date
3821 ,p_clear_details => p_clear_details
3822 ,p_fut_actns_exist_warning => p_fut_actns_exist_warning
3823 );
3824 exception
3825 when hr_api.cannot_find_prog_unit then
3826 hr_api.cannot_find_prog_unit_error
3827 (p_module_name => 'REVERSE_TERMINATE_PLACEMENT',
3828 p_hook_type => 'AP'
3829 );
3830 end;
3831 --
3832 -- End of API User Hook for the after hook of reverse_terminate_placement
3833 --
3834
3835 -- fix 1370960
3836 for roles_rec in csr_roles
3837 loop
3838 per_supplementary_role_api.update_supplementary_role(
3839 p_effective_date => p_actual_termination_date+1
3840 ,p_role_id => roles_rec.role_id
3841 ,p_object_version_number => roles_rec.object_version_number
3842 ,p_end_date => roles_rec.old_end_date
3843 );
3844 end loop;
3845 -- 1370960 end
3846
3847 hr_utility.set_location('Leaving: '||l_proc, 200);
3848
3849 exception
3850 when hr_api.validate_enabled then
3851 --
3852 -- As the Validate_Enabled exception has been raised
3853 -- we must rollback to the savepoint
3854 --
3855 ROLLBACK TO reverse_terminate_placement;
3856
3857 when others then
3858 --
3859 -- A validation or unexpected error has occurred
3860 --
3861 ROLLBACK TO reverse_terminate_placement;
3862 --
3863 -- set in out parameters and set out parameters
3864 --
3865 --p_object_version_number := l_object_version_number;
3866 --
3867 raise;
3868 --
3869 end reverse_terminate_placement;
3870 --
3871 -- ----------------------------------------------------------------------------
3872 -- |--------------------------< Get_Length_of_Placement >---------------------|
3873 -- ----------------------------------------------------------------------------
3874 --
3875 procedure get_length_of_placement
3876 (p_effective_date in date
3877 ,p_business_group_id in number
3878 ,p_person_id in number
3879 ,p_date_start in date
3880 ,p_total_years out nocopy number
3881 ,p_total_months out nocopy number) is
3882
3883 l_proc varchar2(80) := g_package||'get_length_of_placement';
3884 l_total_years number := 0;
3885 l_total_months number := 0;
3886
3887 cursor c_get_length is
3888 select trunc(sum(months_between
3889 (least
3890 (nvl(ACTUAL_TERMINATION_DATE + 1, p_effective_date + 1),
3891 p_effective_date + 1)
3892 ,DATE_START)) / 12, 0) total_years,
3893 trunc(mod(sum(months_between
3894 (least
3895 (nvl(ACTUAL_TERMINATION_DATE + 1, p_effective_date + 1),
3896 p_effective_date + 1)
3897 ,DATE_START)), 12), 0) total_months
3898 from PER_PERIODS_OF_PLACEMENT
3899 where PERSON_ID = p_person_id
3900 and business_group_id = p_business_group_id
3901 and DATE_START = p_date_start
3902 and DATE_START <= p_effective_date;
3903
3904
3905 begin
3906
3907 hr_utility.set_location('Entering: '||l_proc, 10);
3908 --
3909 -- Get the total length of the placement
3910 --
3911 open c_get_length;
3912 fetch c_get_length into l_total_years,
3913 l_total_months;
3914
3915 if c_get_length%NOTFOUND then
3916
3917 --
3918 -- The person could not be found.
3919 --
3920 hr_utility.set_location(l_proc, 30);
3921
3922 end if;
3923
3924 close c_get_length;
3925
3926 --
3927 -- Set the out parameters
3928 --
3929 p_total_years := l_total_years;
3930 p_total_months := l_total_months;
3931
3932 hr_utility.set_location('Leaving: '||l_proc, 30);
3933
3934 end get_length_of_placement;
3935
3936 end hr_contingent_worker_api;