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