[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_API
Source
1 Package Body hr_person_api as
2 /* $Header: peperapi.pkb 120.13 2010/05/20 09:24:48 sudsahu ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_person_api.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |---------------------------< update_person >------------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure update_person
14 (p_validate in boolean default false
15 ,p_effective_date in date
16 ,p_datetrack_update_mode in varchar2
17 ,p_person_id in number
18 ,p_object_version_number in out nocopy number
19 ,p_person_type_id in number default hr_api.g_number
20 ,p_last_name in varchar2 default hr_api.g_varchar2
21 ,p_applicant_number in varchar2 default hr_api.g_varchar2
22 ,p_comments in varchar2 default hr_api.g_varchar2
23 ,p_date_employee_data_verified in date default hr_api.g_date
24 ,p_date_of_birth in date default hr_api.g_date
25 ,p_email_address in varchar2 default hr_api.g_varchar2
26 ,p_employee_number in out nocopy varchar2
27 ,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
28 ,p_first_name in varchar2 default hr_api.g_varchar2
29 ,p_known_as in varchar2 default hr_api.g_varchar2
30 ,p_marital_status in varchar2 default hr_api.g_varchar2
31 ,p_middle_names in varchar2 default hr_api.g_varchar2
32 ,p_nationality in varchar2 default hr_api.g_varchar2
33 ,p_national_identifier in varchar2 default hr_api.g_varchar2
34 ,p_previous_last_name in varchar2 default hr_api.g_varchar2
35 ,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
36 ,p_sex in varchar2 default hr_api.g_varchar2
37 ,p_title in varchar2 default hr_api.g_varchar2
38 ,p_vendor_id in number default hr_api.g_number
39 ,p_work_telephone in varchar2 default hr_api.g_varchar2
40 ,p_attribute_category in varchar2 default hr_api.g_varchar2
41 ,p_attribute1 in varchar2 default hr_api.g_varchar2
42 ,p_attribute2 in varchar2 default hr_api.g_varchar2
43 ,p_attribute3 in varchar2 default hr_api.g_varchar2
44 ,p_attribute4 in varchar2 default hr_api.g_varchar2
45 ,p_attribute5 in varchar2 default hr_api.g_varchar2
46 ,p_attribute6 in varchar2 default hr_api.g_varchar2
47 ,p_attribute7 in varchar2 default hr_api.g_varchar2
48 ,p_attribute8 in varchar2 default hr_api.g_varchar2
49 ,p_attribute9 in varchar2 default hr_api.g_varchar2
50 ,p_attribute10 in varchar2 default hr_api.g_varchar2
51 ,p_attribute11 in varchar2 default hr_api.g_varchar2
52 ,p_attribute12 in varchar2 default hr_api.g_varchar2
53 ,p_attribute13 in varchar2 default hr_api.g_varchar2
54 ,p_attribute14 in varchar2 default hr_api.g_varchar2
55 ,p_attribute15 in varchar2 default hr_api.g_varchar2
56 ,p_attribute16 in varchar2 default hr_api.g_varchar2
57 ,p_attribute17 in varchar2 default hr_api.g_varchar2
58 ,p_attribute18 in varchar2 default hr_api.g_varchar2
59 ,p_attribute19 in varchar2 default hr_api.g_varchar2
60 ,p_attribute20 in varchar2 default hr_api.g_varchar2
61 ,p_attribute21 in varchar2 default hr_api.g_varchar2
62 ,p_attribute22 in varchar2 default hr_api.g_varchar2
63 ,p_attribute23 in varchar2 default hr_api.g_varchar2
64 ,p_attribute24 in varchar2 default hr_api.g_varchar2
65 ,p_attribute25 in varchar2 default hr_api.g_varchar2
66 ,p_attribute26 in varchar2 default hr_api.g_varchar2
67 ,p_attribute27 in varchar2 default hr_api.g_varchar2
68 ,p_attribute28 in varchar2 default hr_api.g_varchar2
69 ,p_attribute29 in varchar2 default hr_api.g_varchar2
70 ,p_attribute30 in varchar2 default hr_api.g_varchar2
71 ,p_per_information_category in varchar2 default hr_api.g_varchar2
72 ,p_per_information1 in varchar2 default hr_api.g_varchar2
73 ,p_per_information2 in varchar2 default hr_api.g_varchar2
74 ,p_per_information3 in varchar2 default hr_api.g_varchar2
75 ,p_per_information4 in varchar2 default hr_api.g_varchar2
76 ,p_per_information5 in varchar2 default hr_api.g_varchar2
77 ,p_per_information6 in varchar2 default hr_api.g_varchar2
78 ,p_per_information7 in varchar2 default hr_api.g_varchar2
79 ,p_per_information8 in varchar2 default hr_api.g_varchar2
80 ,p_per_information9 in varchar2 default hr_api.g_varchar2
81 ,p_per_information10 in varchar2 default hr_api.g_varchar2
82 ,p_per_information11 in varchar2 default hr_api.g_varchar2
83 ,p_per_information12 in varchar2 default hr_api.g_varchar2
84 ,p_per_information13 in varchar2 default hr_api.g_varchar2
85 ,p_per_information14 in varchar2 default hr_api.g_varchar2
86 ,p_per_information15 in varchar2 default hr_api.g_varchar2
87 ,p_per_information16 in varchar2 default hr_api.g_varchar2
88 ,p_per_information17 in varchar2 default hr_api.g_varchar2
89 ,p_per_information18 in varchar2 default hr_api.g_varchar2
90 ,p_per_information19 in varchar2 default hr_api.g_varchar2
91 ,p_per_information20 in varchar2 default hr_api.g_varchar2
92 ,p_per_information21 in varchar2 default hr_api.g_varchar2
93 ,p_per_information22 in varchar2 default hr_api.g_varchar2
94 ,p_per_information23 in varchar2 default hr_api.g_varchar2
95 ,p_per_information24 in varchar2 default hr_api.g_varchar2
96 ,p_per_information25 in varchar2 default hr_api.g_varchar2
97 ,p_per_information26 in varchar2 default hr_api.g_varchar2
98 ,p_per_information27 in varchar2 default hr_api.g_varchar2
99 ,p_per_information28 in varchar2 default hr_api.g_varchar2
100 ,p_per_information29 in varchar2 default hr_api.g_varchar2
101 ,p_per_information30 in varchar2 default hr_api.g_varchar2
102 ,p_date_of_death in date default hr_api.g_date
103 ,p_background_check_status in varchar2 default hr_api.g_varchar2
104 ,p_background_date_check in date default hr_api.g_date
105 ,p_blood_type in varchar2 default hr_api.g_varchar2
106 ,p_correspondence_language in varchar2 default hr_api.g_varchar2
107 ,p_fast_path_employee in varchar2 default hr_api.g_varchar2
108 ,p_fte_capacity in number default hr_api.g_number
109 ,p_hold_applicant_date_until in date default hr_api.g_date
110 ,p_honors in varchar2 default hr_api.g_varchar2
111 ,p_internal_location in varchar2 default hr_api.g_varchar2
112 ,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
113 ,p_last_medical_test_date in date default hr_api.g_date
114 ,p_mailstop in varchar2 default hr_api.g_varchar2
115 ,p_office_number in varchar2 default hr_api.g_varchar2
116 ,p_on_military_service in varchar2 default hr_api.g_varchar2
117 ,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
118 ,p_projected_start_date in date default hr_api.g_date
119 ,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
120 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
121 ,p_resume_exists in varchar2 default hr_api.g_varchar2
122 ,p_resume_last_updated in date default hr_api.g_date
123 ,p_second_passport_exists in varchar2 default hr_api.g_varchar2
124 ,p_student_status in varchar2 default hr_api.g_varchar2
125 ,p_work_schedule in varchar2 default hr_api.g_varchar2
126 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
127 ,p_suffix in varchar2 default hr_api.g_varchar2
128 ,p_benefit_group_id in number default hr_api.g_number
129 ,p_receipt_of_death_cert_date in date default hr_api.g_date
130 ,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
131 ,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
132 ,p_coord_ben_med_ext_er in varchar2 default hr_api.g_varchar2
133 ,p_coord_ben_med_pl_name in varchar2 default hr_api.g_varchar2
134 ,p_coord_ben_med_insr_crr_name in varchar2 default hr_api.g_varchar2
135 ,p_coord_ben_med_insr_crr_ident in varchar2 default hr_api.g_varchar2
136 ,p_coord_ben_med_cvg_strt_dt in date default hr_api.g_date
137 ,p_coord_ben_med_cvg_end_dt in date default hr_api.g_date
138 ,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
139 ,p_dpdnt_adoption_date in date default hr_api.g_date
140 ,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
141 ,p_original_date_of_hire in date default hr_api.g_date
142 ,p_adjusted_svc_date in date default hr_api.g_date
143 ,p_town_of_birth in varchar2 default hr_api.g_varchar2
144 ,p_region_of_birth in varchar2 default hr_api.g_varchar2
145 ,p_country_of_birth in varchar2 default hr_api.g_varchar2
146 ,p_global_person_id in varchar2 default hr_api.g_varchar2
147 ,p_party_id in number default hr_api.g_number
148 ,p_npw_number in varchar2 default hr_api.g_varchar2
149 ,p_effective_start_date out nocopy date
150 ,p_effective_end_date out nocopy date
151 ,p_full_name out nocopy varchar2
152 ,p_comment_id out nocopy number
153 ,p_name_combination_warning out nocopy boolean
154 ,p_assign_payroll_warning out nocopy boolean
155 ,p_orig_hire_warning out nocopy boolean
156 ) is
157 --
158 -- Declare cursors and local variables
159 --
160 l_proc varchar2(72) := g_package||'update_person';
161 l_effective_date date;
162 l_person_type_id per_person_types.person_type_id%type :=
163 p_person_type_id;
164 l_person_type_id1 per_person_types.person_type_id%type :=
165 p_person_type_id;
166 l_business_group_id per_people_f.business_group_id%type;
167 l_old_person_type_id per_all_people_f.person_type_id%type;
168 l_current_system_person_type per_person_types.system_person_type%type;
169 l_validate boolean := false;
170 l_discard_varchar2 varchar2(40);
171 l_applicant_number per_people_f.applicant_number%TYPE;
172 l_object_version_number per_people_f.object_version_number%TYPE;
173 l_employee_number per_people_f.employee_number%TYPE;
174 l_npw_number per_people_f.npw_number%TYPE := p_npw_number;
175 l_date_employee_data_verified per_people_f.date_employee_data_verified%TYPE;
176 l_date_of_birth per_people_f.date_of_birth%TYPE;
177 l_date_of_death date;
178 l_receipt_of_death_cert_date date;
179 l_dpdnt_adoption_date date;
180 l_original_date_of_hire date;
181 --
182 l_effective_start_date date;
183 l_effective_end_date date;
184 l_full_name per_people_f.full_name%type;
185 l_comment_id per_people_f.comment_id%type;
186 l_name_combination_warning boolean;
187 l_assign_payroll_warning boolean;
188 l_orig_hire_warning boolean;
189 --
190 l_old_work_telephone per_phones.phone_number%TYPE;
191 l_phn_object_version_number per_phones.object_version_number%TYPE;
192 l_phone_id per_phones.phone_id%TYPE;
193 l_phn_date_to per_phones.date_to%TYPE; --Line added for bug# 878827
194 --
195 l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
196 l_pds_adjusted_svc_date per_periods_of_service.adjusted_svc_date%type;
197 l_adjusted_svc_date date;
198 l_period_of_service_id per_periods_of_service.period_of_service_id%type;
199 --
200 cursor csr_bg is
201 select per.business_group_id
202 from per_people_f per
203 where per.person_id = p_person_id
204 and l_effective_date between per.effective_start_date
205 and per.effective_end_date;
206 --
207 cursor csr_system_type(p_person_type_id1 number) is
208 select pet.system_person_type
209 from per_person_types pet
210 where pet.person_type_id=p_person_type_id1;
211 --
212 cursor csr_period_of_service is
213 select pds.period_of_service_id, pds.object_version_number, pds.adjusted_svc_date
214 from per_periods_of_service pds
215 where pds.person_id = p_person_id
216 and l_effective_date between
217 nvl(pds.date_start, hr_general.start_of_time)
218 and nvl(pds.actual_termination_date,hr_general.end_of_time);
219 --
220 cursor csr_phones is
221 select phone_number,
222 phone_id,
223 object_version_number,
224 date_to --Line added for bug# 878827
225 from per_phones phn
226 where phn.parent_id = p_person_id
227 and phn.parent_table = 'PER_ALL_PEOPLE_F'
228 and phn.phone_type = 'W1'
229 and p_effective_date between phn.date_from and
230 nvl(phn.date_to,p_effective_date);
231
232 begin
233 if g_debug then
234 hr_utility.set_location('Entering:'|| l_proc, 5);
235 end if;
236 --
237
238 -- Store initial values for IN OUT parameters
239 --
240 l_object_version_number := p_object_version_number;
241 l_employee_number := p_employee_number;
242 --
243 -- Initialise local variables before call to hr_person_bk1.update_person_b
244 --
245 l_effective_date := trunc(p_effective_date);
246 l_applicant_number := p_applicant_number;
247 l_date_employee_data_verified := trunc(p_date_employee_data_verified);
248 l_date_of_birth := trunc(p_date_of_birth);
249 l_date_of_death := trunc(p_date_of_death);
250 l_receipt_of_death_cert_date := trunc(p_receipt_of_death_cert_date);
251 l_dpdnt_adoption_date := trunc(p_dpdnt_adoption_date);
252 l_original_date_of_hire := trunc(p_original_date_of_hire);
253 l_adjusted_svc_date := trunc(p_adjusted_svc_date);
254 l_validate := p_validate ;
255
256 --
257 -- Issue a savepoint.
258 --
259 savepoint hr_update_person; --bug3040309
260 begin
261 --
262 -- Start of API User Hook for the before hook of update_person
263 --
264 hr_person_bk1.update_person_b
265 (p_effective_date => l_effective_date
266 ,p_datetrack_update_mode => p_datetrack_update_mode
267 ,p_person_id => p_person_id
268 ,p_object_version_number => p_object_version_number
269 ,p_person_type_id => p_person_type_id
270 ,p_last_name => p_last_name
271 ,p_applicant_number => p_applicant_number
272 ,p_comments => p_comments
273 ,p_date_employee_data_verified => l_date_employee_data_verified
274 ,p_date_of_birth => l_date_of_birth
275 ,p_email_address => p_email_address
276 ,p_employee_number => p_employee_number
277 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
278 ,p_first_name => p_first_name
279 ,p_known_as => p_known_as
280 ,p_marital_status => p_marital_status
281 ,p_middle_names => p_middle_names
282 ,p_nationality => p_nationality
283 ,p_national_identifier => p_national_identifier
284 ,p_previous_last_name => p_previous_last_name
285 ,p_registered_disabled_flag => p_registered_disabled_flag
286 ,p_sex => p_sex
287 ,p_title => p_title
288 ,p_vendor_id => p_vendor_id
289 ,p_work_telephone => p_work_telephone
290 ,p_attribute_category => p_attribute_category
291 ,p_attribute1 => p_attribute1
292 ,p_attribute2 => p_attribute2
293 ,p_attribute3 => p_attribute3
294 ,p_attribute4 => p_attribute4
295 ,p_attribute5 => p_attribute5
296 ,p_attribute6 => p_attribute6
297 ,p_attribute7 => p_attribute7
298 ,p_attribute8 => p_attribute8
299 ,p_attribute9 => p_attribute9
300 ,p_attribute10 => p_attribute10
301 ,p_attribute11 => p_attribute11
302 ,p_attribute12 => p_attribute12
303 ,p_attribute13 => p_attribute13
304 ,p_attribute14 => p_attribute14
305 ,p_attribute15 => p_attribute15
306 ,p_attribute16 => p_attribute16
307 ,p_attribute17 => p_attribute17
308 ,p_attribute18 => p_attribute18
309 ,p_attribute19 => p_attribute19
310 ,p_attribute20 => p_attribute20
311 ,p_attribute21 => p_attribute21
312 ,p_attribute22 => p_attribute22
313 ,p_attribute23 => p_attribute23
314 ,p_attribute24 => p_attribute24
315 ,p_attribute25 => p_attribute25
316 ,p_attribute26 => p_attribute26
317 ,p_attribute27 => p_attribute27
318 ,p_attribute28 => p_attribute28
319 ,p_attribute29 => p_attribute29
320 ,p_attribute30 => p_attribute30
321 ,p_per_information_category => p_per_information_category
322 ,p_per_information1 => p_per_information1
323 ,p_per_information2 => p_per_information2
324 ,p_per_information3 => p_per_information3
325 ,p_per_information4 => p_per_information4
326 ,p_per_information5 => p_per_information5
327 ,p_per_information6 => p_per_information6
328 ,p_per_information7 => p_per_information7
329 ,p_per_information8 => p_per_information8
330 ,p_per_information9 => p_per_information9
331 ,p_per_information10 => p_per_information10
332 ,p_per_information11 => p_per_information11
333 ,p_per_information12 => p_per_information12
334 ,p_per_information13 => p_per_information13
335 ,p_per_information14 => p_per_information14
336 ,p_per_information15 => p_per_information15
337 ,p_per_information16 => p_per_information16
338 ,p_per_information17 => p_per_information17
339 ,p_per_information18 => p_per_information18
340 ,p_per_information19 => p_per_information19
341 ,p_per_information20 => p_per_information20
342 ,p_per_information21 => p_per_information21
343 ,p_per_information22 => p_per_information22
344 ,p_per_information23 => p_per_information23
345 ,p_per_information24 => p_per_information24
346 ,p_per_information25 => p_per_information25
347 ,p_per_information26 => p_per_information26
348 ,p_per_information27 => p_per_information27
349 ,p_per_information28 => p_per_information28
350 ,p_per_information29 => p_per_information29
351 ,p_per_information30 => p_per_information30
352 ,p_date_of_death => l_date_of_death
353 ,p_background_check_status => p_background_check_status
354 ,p_background_date_check => p_background_date_check
355 ,p_blood_type => p_blood_type
356 ,p_correspondence_language => p_correspondence_language
357 ,p_fast_path_employee => p_fast_path_employee
358 ,p_fte_capacity => p_fte_capacity
359 ,p_hold_applicant_date_until => p_hold_applicant_date_until
360 ,p_honors => p_honors
361 ,p_internal_location => p_internal_location
362 ,p_last_medical_test_by => p_last_medical_test_by
363 ,p_last_medical_test_date => p_last_medical_test_date
364 ,p_mailstop => p_mailstop
365 ,p_office_number => p_office_number
366 ,p_on_military_service => p_on_military_service
367 ,p_pre_name_adjunct => p_pre_name_adjunct
368 ,p_projected_start_date => p_projected_start_date
369 ,p_rehire_authorizor => p_rehire_authorizor
370 ,p_rehire_recommendation => p_rehire_recommendation
371 ,p_resume_exists => p_resume_exists
372 ,p_resume_last_updated => p_resume_last_updated
373 ,p_second_passport_exists => p_second_passport_exists
374 ,p_student_status => p_student_status
375 ,p_work_schedule => p_work_schedule
376 ,p_rehire_reason => p_rehire_reason
377 ,p_suffix => p_suffix
378 ,p_benefit_group_id => p_benefit_group_id
379 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
380 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
381 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
382 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
383 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
384 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
385 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
386 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
387 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
388 ,p_uses_tobacco_flag => p_uses_tobacco_flag
389 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
390 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
391 ,p_original_date_of_hire => l_original_date_of_hire
392 ,p_adjusted_svc_date => l_adjusted_svc_date
393 ,p_town_of_birth => p_town_of_birth
394 ,p_region_of_birth => p_region_of_birth
395 ,p_country_of_birth => p_country_of_birth
396 ,p_global_person_id => p_global_person_id
397 ,p_party_id => p_party_id
398 ,p_npw_number => l_npw_number
399 );
400 exception
401 when hr_api.cannot_find_prog_unit then
402 hr_api.cannot_find_prog_unit_error
403 (p_module_name => 'UPDATE_PERSON'
404 ,p_hook_type => 'BP'
405 );
406 --
407 -- End of API User Hook for the before hook of update_person
408 --
409 end;
410 --
411 if g_debug then
412 hr_utility.set_location(l_proc, 6);
413 end if;
414 --
415 -- Validation in addition to Table Handlers
416 --
417 -- Fetch the person's business group id
418 --
419 open csr_bg;
420 fetch csr_bg
421 into l_business_group_id;
422 --
423 if csr_bg%notfound then
424 close csr_bg;
425 hr_utility.set_message(801, 'HR_7971_PER_PER_IN_PERSON');
426 hr_utility.raise_error;
427 end if;
428 close csr_bg;
429 --
430 if g_debug then
431 hr_utility.set_location(l_proc, 7);
432 end if;
433 --
434 /*
435 This is obsolete. With introduction of Contingent Workers using the PTU model
436 (ie per_all_people_f stores only a person type of OTHER) this
437 check raises erroneous errors. Comment out nocopy also person_type_id in the call to per_per_upd
438 This ensures that PT is unchanged on per_all_people_f
439 Instead added validation before the PTU call below.
440
441 if p_person_type_id <> hr_api.g_number then
442 open csr_current_type;
443 fetch csr_current_type
444 into l_current_system_person_type;
445 if csr_current_type%notfound then
446 close csr_current_type;
447 else
448 close csr_current_type;
449 per_per_bus.chk_person_type
450 (p_person_type_id => l_person_type_id
451 ,p_business_group_id => l_business_group_id
452 ,p_expected_sys_type => l_current_system_person_type);
453 l_person_type_id1 :=
454 hr_person_type_usage_info.get_default_person_type_id(l_person_type_id);
455 end if;
456 end if;
457 */
458 --
459 if g_debug then
460 hr_utility.set_location(l_proc, 8);
461 end if;
462 --
463 -- Update the person record
464 --
465 per_per_upd.upd
466 (p_person_id => p_person_id
467 ,p_effective_start_date => l_effective_start_date
468 ,p_effective_end_date => l_effective_end_date
469 -- ,p_person_type_id => l_person_type_id1
470 ,p_last_name => p_last_name
471 ,p_applicant_number => l_applicant_number
472 ,p_comment_id => l_comment_id
473 ,p_comments => p_comments
474 ,p_current_applicant_flag => l_discard_varchar2
475 ,p_current_emp_or_apl_flag => l_discard_varchar2
476 ,p_current_employee_flag => l_discard_varchar2
477 ,p_date_employee_data_verified => l_date_employee_data_verified
478 ,p_date_of_birth => l_date_of_birth
479 ,p_email_address => p_email_address
480 ,p_employee_number => p_employee_number
481 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
482 ,p_first_name => p_first_name
483 ,p_full_name => l_full_name
484 ,p_known_as => p_known_as
485 ,p_marital_status => p_marital_status
486 ,p_middle_names => p_middle_names
487 ,p_nationality => p_nationality
488 ,p_national_identifier => p_national_identifier
489 ,p_previous_last_name => p_previous_last_name
490 ,p_registered_disabled_flag => p_registered_disabled_flag
491 ,p_sex => p_sex
492 ,p_title => p_title
493 ,p_vendor_id => p_vendor_id
494 -- ,p_work_telephone => p_work_telephone -- Now handled by create_phone
495 ,p_attribute_category => p_attribute_category
496 ,p_attribute1 => p_attribute1
497 ,p_attribute2 => p_attribute2
498 ,p_attribute3 => p_attribute3
499 ,p_attribute4 => p_attribute4
500 ,p_attribute5 => p_attribute5
501 ,p_attribute6 => p_attribute6
502 ,p_attribute7 => p_attribute7
503 ,p_attribute8 => p_attribute8
504 ,p_attribute9 => p_attribute9
505 ,p_attribute10 => p_attribute10
506 ,p_attribute11 => p_attribute11
507 ,p_attribute12 => p_attribute12
508 ,p_attribute13 => p_attribute13
509 ,p_attribute14 => p_attribute14
510 ,p_attribute15 => p_attribute15
511 ,p_attribute16 => p_attribute16
512 ,p_attribute17 => p_attribute17
513 ,p_attribute18 => p_attribute18
514 ,p_attribute19 => p_attribute19
515 ,p_attribute20 => p_attribute20
516 ,p_attribute21 => p_attribute21
517 ,p_attribute22 => p_attribute22
518 ,p_attribute23 => p_attribute23
519 ,p_attribute24 => p_attribute24
520 ,p_attribute25 => p_attribute25
521 ,p_attribute26 => p_attribute26
522 ,p_attribute27 => p_attribute27
523 ,p_attribute28 => p_attribute28
524 ,p_attribute29 => p_attribute29
525 ,p_attribute30 => p_attribute30
526 ,p_per_information_category => p_per_information_category
527 ,p_per_information1 => p_per_information1
528 ,p_per_information2 => p_per_information2
529 ,p_per_information3 => p_per_information3
530 ,p_per_information4 => p_per_information4
531 ,p_per_information5 => p_per_information5
532 ,p_per_information6 => p_per_information6
533 ,p_per_information7 => p_per_information7
534 ,p_per_information8 => p_per_information8
535 ,p_per_information9 => p_per_information9
536 ,p_per_information10 => p_per_information10
537 ,p_per_information11 => p_per_information11
538 ,p_per_information12 => p_per_information12
539 ,p_per_information13 => p_per_information13
540 ,p_per_information14 => p_per_information14
541 ,p_per_information15 => p_per_information15
542 ,p_per_information16 => p_per_information16
543 ,p_per_information17 => p_per_information17
544 ,p_per_information18 => p_per_information18
545 ,p_per_information19 => p_per_information19
546 ,p_per_information20 => p_per_information20
547 ,p_per_information21 => p_per_information21
548 ,p_per_information22 => p_per_information22
549 ,p_per_information23 => p_per_information23
550 ,p_per_information24 => p_per_information24
551 ,p_per_information25 => p_per_information25
552 ,p_per_information26 => p_per_information26
553 ,p_per_information27 => p_per_information27
554 ,p_per_information28 => p_per_information28
555 ,p_per_information29 => p_per_information29
556 ,p_per_information30 => p_per_information30
557 ,p_date_of_death => l_date_of_death
558 ,p_background_check_status => p_background_check_status
559 ,p_background_date_check => p_background_date_check
560 ,p_blood_type => p_blood_type
561 ,p_correspondence_language => p_correspondence_language
562 ,p_fast_path_employee => p_fast_path_employee
563 ,p_fte_capacity => p_fte_capacity
564 ,p_hold_applicant_date_until => p_hold_applicant_date_until
565 ,p_honors => p_honors
566 ,p_internal_location => p_internal_location
567 ,p_last_medical_test_by => p_last_medical_test_by
568 ,p_last_medical_test_date => p_last_medical_test_date
569 ,p_mailstop => p_mailstop
570 ,p_office_number => p_office_number
571 ,p_on_military_service => p_on_military_service
572 ,p_pre_name_adjunct => p_pre_name_adjunct
573 ,p_projected_start_date => p_projected_start_date
574 ,p_rehire_authorizor => p_rehire_authorizor
575 ,p_rehire_recommendation => p_rehire_recommendation
576 ,p_resume_exists => p_resume_exists
577 ,p_resume_last_updated => p_resume_last_updated
578 ,p_second_passport_exists => p_second_passport_exists
579 ,p_student_status => p_student_status
580 ,p_work_schedule => p_work_schedule
581 ,p_rehire_reason => p_rehire_reason
582 ,p_suffix => p_suffix
583 ,p_benefit_group_id => p_benefit_group_id
584 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
585 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
586 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
587 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
588 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
589 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
590 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
591 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
592 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
593 ,p_uses_tobacco_flag => p_uses_tobacco_flag
594 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
595 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
596 ,p_original_date_of_hire => p_original_date_of_hire
597 ,p_town_of_birth => p_town_of_birth
598 ,p_region_of_birth => p_region_of_birth
599 ,p_country_of_birth => p_country_of_birth
600 ,p_global_person_id => p_global_person_id
601 ,p_party_id => p_party_id
602 ,p_object_version_number => p_object_version_number
603 ,p_effective_date => l_effective_date
604 ,p_datetrack_mode => p_datetrack_update_mode
605 ,p_validate => l_validate
606 ,p_name_combination_warning => l_name_combination_warning
607 ,p_dob_null_warning => l_assign_payroll_warning
608 ,p_orig_hire_warning => l_orig_hire_warning
609 ,p_npw_number => l_npw_number
610 );
611 --
612 -- PTU : Following Code has been added since the user PT flavour may have changed
613 -- 07/03/2002: adhunter: changed this check. Now raise error if user is trying to
614 -- update a person_type_id corresponding to a system type that is not on PTU.
615 -- This is required because of a "feature" of PERWSQHM that people can be created
616 -- and PT updated immediately.
617
618 -- Composite person types are not allowed for p_person_type_id, unless it is
619 -- the same person type the person is holding on the p_effective_date.
620 -- Bug# 2777435
621
622 if p_person_type_id <> hr_api.g_number then
623 --
624 -- get the current system person type from per_all_people_f
625 -- Bug 4030116
626 -- modifed the following SQL to check whether the person_type_id
627 -- is already exists for the PTU table instead of per_all_people_f
628 l_old_person_type_id := null;
629 begin
630 select person_type_id into l_old_person_type_id
631 from per_person_type_usages_f ptu
632 where ptu.person_id=p_person_id
633 and ptu.person_type_id = p_person_type_id
634 and p_effective_date between ptu.effective_start_date
635 and ptu.effective_end_date;
636 exception
637 when no_data_found then
638 l_old_person_type_id := null;
639 end;
640 --
641 -- get the new system person type
642
643 open csr_system_type(p_person_type_id);
644 fetch csr_system_type into l_current_system_person_type;
645 close csr_system_type;
646
647 --
648 -- if both matches and it is a composite type then do no more validation
649 --
650 -- Bug 3472119
651 -- Modified if condition.
652 -- If there is no change in the person type id, there is no need to
653 -- update PTU.
654 -- Bug 4030116
655 -- changed condition to is not null
656 --
657 if l_old_person_type_id is not null
658 then null;
659 else
660 --
661 -- if both does not match and new type is a composite then raise error
662 -- Bug 4030116
663 -- removed the match condition as its already checked.
664 --
665 if l_current_system_person_type in ('EMP_APL','EX_EMP_APL','APL_EX_APL')
666 then
667 fnd_message.set_name('PER','PER_289965_COMP_PER_TYPE_INVLD');
668 fnd_message.raise_error;
669 end if;
670 --
671 -- Check if the new person type is valid for current person type
672
673 if not hr_general2.is_person_type
674 (p_person_id,l_current_system_person_type,l_effective_date) then
675 fnd_message.set_name('PER','PER_289603_CWK_INV_PERSON_TYPE');
676 fnd_message.raise_error;
677 end if;
678
679 hr_per_type_usage_internal.maintain_person_type_usage
680 (p_effective_date => l_effective_start_date
681 ,p_person_id => p_person_id
682 ,p_person_type_id => p_person_type_id
683 ,p_datetrack_update_mode => p_datetrack_update_mode
684 );
685 end if;
686 end if;
687 -- PTU : End of changes
688 --
689 -- Update per_periods_of_service
690 --
691 open csr_period_of_service;
692 fetch csr_period_of_service
693 into l_period_of_service_id, l_pds_object_version_number,l_pds_adjusted_svc_date;
694 if csr_period_of_service%FOUND then
695 -- Bug#885806. DBMS_OUTPUT.PUT_LINE calls were replaced with HR_UTILITY.TRACE...
696 hr_utility.trace('doing the PDS update');
697 hr_utility.trace('PDS id '||to_char(l_period_of_service_id));
698 hr_utility.trace('PDS ovn '||to_char(l_pds_object_version_number));
699 -- dbms_output.put_line('doing the PDS update');
700 -- dbms_output.put_line('PDS id '||to_char(l_period_of_service_id));
701 -- dbms_output.put_line('PDS ovn '||to_char(l_pds_object_version_number));
702
703 -- Bug # 2679759 : Added nvl to l_adjusted_svc_date and l_pds_adjusted_svc_date.
704 -- Fix for bug 5941249.
705 if (l_adjusted_svc_date is null or l_adjusted_svc_date <> hr_api.g_date) --fix for 2497699
706 and nvl(l_pds_adjusted_svc_date,hr_api.g_date) <> nvl(l_adjusted_svc_date,hr_api.g_date)
707 then
708 if g_debug then
709 hr_utility.set_location('In '||l_proc||' in the If condition of SVC date ',52);
710 end if;
711 per_pds_upd.upd
712 (p_adjusted_svc_date => l_adjusted_svc_date
713 ,p_effective_date => l_effective_date
714 ,p_period_of_service_id => l_period_of_service_id
715 ,p_object_version_number => l_pds_object_version_number
716 );
717 /*----------------------- start for the bug 4924261 ----------------------------------*/
718 /* elsif nvl(l_adjusted_svc_date,hr_api.g_date) = hr_api.g_date --fix for 4924261
719 and nvl(l_pds_adjusted_svc_date,hr_api.g_date) <> nvl(l_adjusted_svc_date,hr_api.g_date)
720 then
721 if g_debug then
722 hr_utility.set_location('In '||l_proc||' in the If condition of (SVC -null) date ',54);
723 end if;
724 per_pds_upd.upd
725 (p_adjusted_svc_date => l_adjusted_svc_date
726 ,p_effective_date => l_effective_date
727 ,p_period_of_service_id => l_period_of_service_id
728 ,p_object_version_number => l_pds_object_version_number
729 );*/
730 /*----------------------- end for the bug 4924261 -----------------------------------*/
731 end if;
732
733 -- Bug#885806. DBMS_OUTPUT.PUT_LINE calls were replaced with HR_UTILITY.TRACE...
734 -- dbms_output.put_line('PDS id '||to_char(l_period_of_service_id));
735 -- dbms_output.put_line('PDS ovn '||to_char(l_pds_object_version_number));
736 hr_utility.trace('PDS id '||to_char(l_period_of_service_id));
737 hr_utility.trace('PDS ovn '||to_char(l_pds_object_version_number));
738 end if;
739 close csr_period_of_service;
740 --
741 -- Beginning of logic for the update of the phones table
742 --
743 -- Firstly, find the number, ovn and id for the old work_telephone (if it exists)
744 --
745 open csr_phones;
746 fetch csr_phones into l_old_work_telephone,
747 l_phone_id,
748 l_phn_object_version_number,
749 l_phn_date_to; -- Line added for bug# 878827
750 close csr_phones;
751
752
753 if g_debug then
754 hr_utility.set_location('In '||l_proc||' and phone no is: '||p_work_telephone, 63);
755 end if;
756 if g_debug then
757 hr_utility.set_location('In '||l_proc||' and old phone no is: '||l_old_work_telephone, 64);
758 end if;
759 if g_debug then
760 hr_utility.set_location('In '||l_proc||' and ovn is: '||l_phn_object_version_number, 65);
761 end if;
762 if g_debug then
763 hr_utility.set_location('In '||l_proc||' and phone id is: '||l_phone_id, 66);
764 end if;
765 --
766 -- If old entry is null and new entry is not null then just use the create
767 -- phone B.P. This step is the same regardless of dt upd mode.
768 --
769 if (p_work_telephone <> hr_api.g_varchar2) then
770 if (l_old_work_telephone is null and
771 p_work_telephone is not null) then
772 if g_debug then
773 hr_utility.set_location('Creating new phone', 67);
774 end if;
775 hr_phone_api.create_phone
776 (p_date_from => l_effective_date
777 ,p_date_to => null
778 ,p_phone_type => 'W1'
779 ,p_phone_number => p_work_telephone
780 ,p_parent_id => p_person_id
781 ,p_parent_table => 'PER_ALL_PEOPLE_F'
782 ,p_validate => FALSE
783 ,p_effective_date => l_effective_date
784 ,p_object_version_number => l_phn_object_version_number --out
785 ,p_phone_id => l_phone_id --out
786 );
787 --
788 -- The way we deal with the update of the phones table now depends on
789 -- p_datetrack_update_mode, so switch on this parameter.
790 --
791 elsif p_datetrack_update_mode = 'CORRECTION' then
792 --
793 -- If old entry is not null and corrected entry is null then delete the phone.
794 --
795 if l_old_work_telephone is not null and p_work_telephone is null then
796 if g_debug then
797 hr_utility.set_location('Deleting phone', 68);
798 end if;
799 hr_phone_api.delete_phone(FALSE, l_phone_id, l_phn_object_version_number);
800
801 --
802 -- If old and corrected entries are both not null then update the row with
803 -- no changes to start and to dates.
804 --
805 elsif l_old_work_telephone is not null and p_work_telephone is not null then
806 if g_debug then
807 hr_utility.set_location('Updating phone in correction mode', 67);
808 end if;
809 hr_phone_api.update_phone
810 (p_phone_id => l_phone_id,
811 p_phone_number => p_work_telephone,
812 p_object_version_number => l_phn_object_version_number,
813 p_effective_date => l_effective_date);
814 end if;
815 --
816 -- Logic for updating phones table when dt upd mode is an UPDATE one.
817 --
818 elsif p_datetrack_update_mode = 'UPDATE' or
819 p_datetrack_update_mode = 'UPDATE_OVERRIDE' or
820 p_datetrack_update_mode = 'UPDATE_CHANGE_INSERT' then
821 --
822 -- If old entry is not null and updated entry is null then update current
823 -- phone to have an end date of the day before the effective date.
824 --
825 if l_old_work_telephone is not null and p_work_telephone is null then
826 if g_debug then
827 hr_utility.set_location('Updating old phone in update mode', 69);
828 end if;
829 hr_phone_api.update_phone
830 (p_phone_id => l_phone_id,
831 p_date_to => l_effective_date - 1,
832 p_object_version_number => l_phn_object_version_number,
833 p_effective_date => l_effective_date);
834 --
835 -- If old and updated entries are both not null then cap the old row by
836 -- updating the date to with the effective date minus 1 and then create a new
837 -- row with a date from as the effective date.
838 --
839 elsif l_old_work_telephone is not null and p_work_telephone is not null then
840
841 if g_debug then
842 hr_utility.set_location('Capping old phone in update mode', 70);
843 end if;
844 hr_phone_api.update_phone
845 (p_phone_id => l_phone_id,
846 p_date_to => l_effective_date -1,
847 p_object_version_number => l_phn_object_version_number,
848 p_effective_date => l_effective_date);
849
850 if g_debug then
851 hr_utility.set_location('Creating new phone in update mode', 71);
852 end if;
853 hr_phone_api.create_phone
854 (p_date_from => l_effective_date
855 ,p_date_to => l_phn_date_to
856 --replaced null with l_phn_date_to for bug# 878827
857 ,p_phone_type => 'W1'
858 ,p_phone_number => p_work_telephone
859 ,p_parent_id => p_person_id
860 ,p_parent_table => 'PER_ALL_PEOPLE_F'
861 ,p_validate => FALSE
862 ,p_effective_date => l_effective_date
863 ,p_object_version_number => l_phn_object_version_number --out
864 ,p_phone_id => l_phone_id --out
865 );
866 end if;
867 end if;
868 end if;
869 --
870 -- End of logic for the update of the phones table
871 --
872
873 if l_name_combination_warning = FALSE then
874 if g_debug then
875 hr_utility.set_location(l_proc,87);
876 end if;
877 elsif l_name_combination_warning = TRUE then
878 if g_debug then
879 hr_utility.set_location(l_proc,88);
880 end if;
881 else
882 if g_debug then
883 hr_utility.set_location(l_proc,89);
884 end if;
885 end if;
886 if g_debug then
887 hr_utility.set_location(l_proc, 90);
888 end if;
889 begin
890 --
891 -- Start of API User Hook for the after hook of update_person
892 --
893 hr_person_bk1.update_person_a
894 (p_effective_date => l_effective_date
895 ,p_datetrack_update_mode => p_datetrack_update_mode
896 ,p_person_id => p_person_id
897 ,p_object_version_number => p_object_version_number
898 ,p_person_type_id => p_person_type_id
899 ,p_last_name => p_last_name
900 ,p_applicant_number => p_applicant_number
901 ,p_comments => p_comments
902 ,p_date_employee_data_verified => l_date_employee_data_verified
903 ,p_date_of_birth => l_date_of_birth
904 ,p_email_address => p_email_address
905 ,p_employee_number => p_employee_number
906 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
907 ,p_first_name => p_first_name
908 ,p_known_as => p_known_as
909 ,p_marital_status => p_marital_status
910 ,p_middle_names => p_middle_names
911 ,p_nationality => p_nationality
912 ,p_national_identifier => p_national_identifier
913 ,p_previous_last_name => p_previous_last_name
914 ,p_registered_disabled_flag => p_registered_disabled_flag
915 ,p_sex => p_sex
916 ,p_title => p_title
917 ,p_vendor_id => p_vendor_id
918 -- ,p_work_telephone => p_work_telephone
919 ,p_attribute_category => p_attribute_category
920 ,p_attribute1 => p_attribute1
921 ,p_attribute2 => p_attribute2
922 ,p_attribute3 => p_attribute3
923 ,p_attribute4 => p_attribute4
924 ,p_attribute5 => p_attribute5
925 ,p_attribute6 => p_attribute6
926 ,p_attribute7 => p_attribute7
927 ,p_attribute8 => p_attribute8
928 ,p_attribute9 => p_attribute9
929 ,p_attribute10 => p_attribute10
930 ,p_attribute11 => p_attribute11
931 ,p_attribute12 => p_attribute12
932 ,p_attribute13 => p_attribute13
933 ,p_attribute14 => p_attribute14
934 ,p_attribute15 => p_attribute15
935 ,p_attribute16 => p_attribute16
936 ,p_attribute17 => p_attribute17
937 ,p_attribute18 => p_attribute18
938 ,p_attribute19 => p_attribute19
939 ,p_attribute20 => p_attribute20
940 ,p_attribute21 => p_attribute21
941 ,p_attribute22 => p_attribute22
942 ,p_attribute23 => p_attribute23
943 ,p_attribute24 => p_attribute24
944 ,p_attribute25 => p_attribute25
945 ,p_attribute26 => p_attribute26
946 ,p_attribute27 => p_attribute27
947 ,p_attribute28 => p_attribute28
948 ,p_attribute29 => p_attribute29
949 ,p_attribute30 => p_attribute30
950 ,p_per_information_category => p_per_information_category
951 ,p_per_information1 => p_per_information1
952 ,p_per_information2 => p_per_information2
953 ,p_per_information3 => p_per_information3
954 ,p_per_information4 => p_per_information4
955 ,p_per_information5 => p_per_information5
956 ,p_per_information6 => p_per_information6
957 ,p_per_information7 => p_per_information7
958 ,p_per_information8 => p_per_information8
959 ,p_per_information9 => p_per_information9
960 ,p_per_information10 => p_per_information10
961 ,p_per_information11 => p_per_information11
962 ,p_per_information12 => p_per_information12
963 ,p_per_information13 => p_per_information13
964 ,p_per_information14 => p_per_information14
965 ,p_per_information15 => p_per_information15
966 ,p_per_information16 => p_per_information16
967 ,p_per_information17 => p_per_information17
968 ,p_per_information18 => p_per_information18
969 ,p_per_information19 => p_per_information19
970 ,p_per_information20 => p_per_information20
971 ,p_per_information21 => p_per_information21
972 ,p_per_information22 => p_per_information22
973 ,p_per_information23 => p_per_information23
974 ,p_per_information24 => p_per_information24
975 ,p_per_information25 => p_per_information25
976 ,p_per_information26 => p_per_information26
977 ,p_per_information27 => p_per_information27
978 ,p_per_information28 => p_per_information28
979 ,p_per_information29 => p_per_information29
980 ,p_per_information30 => p_per_information30
981 ,p_date_of_death => l_date_of_death
982 ,p_background_check_status => p_background_check_status
983 ,p_background_date_check => p_background_date_check
984 ,p_blood_type => p_blood_type
985 ,p_correspondence_language => p_correspondence_language
986 ,p_fast_path_employee => p_fast_path_employee
987 ,p_fte_capacity => p_fte_capacity
988 ,p_hold_applicant_date_until => p_hold_applicant_date_until
989 ,p_honors => p_honors
990 ,p_internal_location => p_internal_location
991 ,p_last_medical_test_by => p_last_medical_test_by
992 ,p_last_medical_test_date => p_last_medical_test_date
993 ,p_mailstop => p_mailstop
994 ,p_office_number => p_office_number
995 ,p_on_military_service => p_on_military_service
996 ,p_pre_name_adjunct => p_pre_name_adjunct
997 ,p_projected_start_date => p_projected_start_date
998 ,p_rehire_authorizor => p_rehire_authorizor
999 ,p_rehire_recommendation => p_rehire_recommendation
1000 ,p_resume_exists => p_resume_exists
1001 ,p_resume_last_updated => p_resume_last_updated
1002 ,p_second_passport_exists => p_second_passport_exists
1003 ,p_student_status => p_student_status
1004 ,p_work_schedule => p_work_schedule
1005 ,p_rehire_reason => p_rehire_reason
1006 ,p_suffix => p_suffix
1007 ,p_benefit_group_id => p_benefit_group_id
1008 ,p_receipt_of_death_cert_date => l_receipt_of_death_cert_date
1009 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1010 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1011 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1012 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1013 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1014 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1015 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1016 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1017 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1018 ,p_dpdnt_adoption_date => l_dpdnt_adoption_date
1019 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1020 ,p_original_date_of_hire => p_original_date_of_hire
1021 ,p_adjusted_svc_date => l_adjusted_svc_date
1022 ,p_effective_start_date => l_effective_start_date
1023 ,p_effective_end_date => l_effective_end_date
1024 ,p_full_name => l_full_name
1025 ,p_comment_id => l_comment_id
1026 ,p_town_of_birth => p_town_of_birth
1027 ,p_region_of_birth => p_region_of_birth
1028 ,p_country_of_birth => p_country_of_birth
1029 ,p_global_person_id => p_global_person_id
1030 ,p_party_id => p_party_id
1031 ,p_npw_number => l_npw_number
1032 ,p_name_combination_warning => l_name_combination_warning
1033 ,p_assign_payroll_warning => l_assign_payroll_warning
1034 ,p_orig_hire_warning => l_orig_hire_warning
1035 );
1036 exception
1037 when hr_api.cannot_find_prog_unit then
1038 hr_api.cannot_find_prog_unit_error
1039 (p_module_name => 'UPDATE_PERSON'
1040 ,p_hook_type => 'AP'
1041 );
1042 --
1043 -- End of API User Hook for the after hook of update_person
1044 --
1045 end;
1046 --
1047 -- Populating the OUT parameters.
1048 --
1049 p_effective_start_date := l_effective_start_date;
1050 p_effective_end_date := l_effective_end_date;
1051 p_full_name := l_full_name;
1052 p_comment_id := l_comment_id;
1053 p_name_combination_warning := l_name_combination_warning;
1054 p_assign_payroll_warning := l_assign_payroll_warning;
1055 p_orig_hire_warning := l_orig_hire_warning;
1056 --
1057 -- When in validation only mode raise the Validate_Enabled exception
1058 --
1059 if p_validate then
1060 raise hr_api.validate_enabled;
1061 end if;
1062 if g_debug then
1063 hr_utility.set_location(' Leaving:'||l_proc, 10);
1064 end if;
1065 exception
1066 when hr_api.validate_enabled then
1067 --
1068 -- As the Validate_Enabled exception has been raised
1069 -- we must rollback to the savepoint
1070 --
1071 ROLLBACK TO hr_update_person; --bug3040309
1072 --
1073 -- Only set output warning arguments
1074 -- (Any key or derived arguments must be set to null
1075 -- when validation only mode is being used.)
1076 --
1077 p_object_version_number := l_object_version_number;
1078 p_employee_number := l_employee_number;
1079 p_effective_start_date := null;
1080 p_effective_end_date := null;
1081 p_full_name := null;
1082 p_comment_id := null;
1083 --
1084 when others then
1085 --
1086 -- A validation or unexpected error has occurred
1087 --
1088 -- Added as part of fix to bug 632479
1089 --
1090 p_object_version_number := l_object_version_number;
1091 p_employee_number := l_employee_number;
1092 p_effective_start_date := null;
1093 p_effective_end_date := null;
1094 p_full_name := null;
1095 p_comment_id := null;
1096 p_orig_hire_warning := null;
1097 p_name_combination_warning := null;
1098 p_assign_payroll_warning := null;
1099 ROLLBACK TO hr_update_person; --bug3040309
1100 raise;
1101 --
1102 -- End of fix.
1103 --
1104 end update_person;
1105 --
1106 -- ----------------------------------------------------------------------------
1107 -- |--------------------------< update_gb_person >----------------------------|
1108 -- ----------------------------------------------------------------------------
1109 --
1110 procedure update_gb_person
1111 (p_validate in boolean default false
1112 ,p_effective_date in date
1113 ,p_datetrack_update_mode in varchar2
1114 ,p_person_id in number
1115 ,p_object_version_number in out nocopy number
1116 ,p_person_type_id in number default hr_api.g_number
1117 ,p_last_name in varchar2 default hr_api.g_varchar2
1118 ,p_applicant_number in varchar2 default hr_api.g_varchar2
1119 ,p_comments in varchar2 default hr_api.g_varchar2
1120 ,p_date_employee_data_verified in date default hr_api.g_date
1121 ,p_date_of_birth in date default hr_api.g_date
1122 ,p_email_address in varchar2 default hr_api.g_varchar2
1123 ,p_employee_number in out nocopy varchar2
1124 ,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
1125 ,p_first_name in varchar2 default hr_api.g_varchar2
1126 ,p_known_as in varchar2 default hr_api.g_varchar2
1127 ,p_marital_status in varchar2 default hr_api.g_varchar2
1128 ,p_middle_names in varchar2 default hr_api.g_varchar2
1129 ,p_nationality in varchar2 default hr_api.g_varchar2
1130 ,p_ni_number in varchar2 default hr_api.g_varchar2
1131 ,p_previous_last_name in varchar2 default hr_api.g_varchar2
1132 ,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
1133 ,p_sex in varchar2 default hr_api.g_varchar2
1134 ,p_title in varchar2 default hr_api.g_varchar2
1135 ,p_vendor_id in number default hr_api.g_number
1136 ,p_work_telephone in varchar2 default hr_api.g_varchar2
1137 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1138 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1139 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1140 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1141 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1142 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1143 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1144 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1145 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1146 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1147 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1148 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1149 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1150 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1151 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1152 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1153 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1154 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1155 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1156 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1157 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1158 ,p_attribute21 in varchar2 default hr_api.g_varchar2
1159 ,p_attribute22 in varchar2 default hr_api.g_varchar2
1160 ,p_attribute23 in varchar2 default hr_api.g_varchar2
1161 ,p_attribute24 in varchar2 default hr_api.g_varchar2
1162 ,p_attribute25 in varchar2 default hr_api.g_varchar2
1163 ,p_attribute26 in varchar2 default hr_api.g_varchar2
1164 ,p_attribute27 in varchar2 default hr_api.g_varchar2
1165 ,p_attribute28 in varchar2 default hr_api.g_varchar2
1166 ,p_attribute29 in varchar2 default hr_api.g_varchar2
1167 ,p_attribute30 in varchar2 default hr_api.g_varchar2
1168 ,p_ethnic_origin in varchar2 default hr_api.g_varchar2
1169 ,p_director in varchar2 default hr_api.g_varchar2
1170 ,p_pensioner in varchar2 default hr_api.g_varchar2
1171 ,p_work_permit_number in varchar2 default hr_api.g_varchar2
1172 ,p_addl_pension_years in varchar2 default hr_api.g_varchar2
1173 ,p_addl_pension_months in varchar2 default hr_api.g_varchar2
1174 ,p_addl_pension_days in varchar2 default hr_api.g_varchar2
1175 ,p_ni_multiple_asg in varchar2 default hr_api.g_varchar2
1176 ,p_paye_aggregate_assignment in varchar2 default hr_api.g_varchar2
1177 ,p_date_of_death in date default hr_api.g_date
1178 ,p_background_check_status in varchar2 default hr_api.g_varchar2
1179 ,p_background_date_check in date default hr_api.g_date
1180 ,p_blood_type in varchar2 default hr_api.g_varchar2
1181 ,p_correspondence_language in varchar2 default hr_api.g_varchar2
1182 ,p_fast_path_employee in varchar2 default hr_api.g_varchar2
1183 ,p_fte_capacity in number default hr_api.g_number
1184 ,p_hold_applicant_date_until in date default hr_api.g_date
1185 ,p_honors in varchar2 default hr_api.g_varchar2
1186 ,p_internal_location in varchar2 default hr_api.g_varchar2
1187 ,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
1188 ,p_last_medical_test_date in date default hr_api.g_date
1189 ,p_mailstop in varchar2 default hr_api.g_varchar2
1190 ,p_office_number in varchar2 default hr_api.g_varchar2
1191 ,p_on_military_service in varchar2 default hr_api.g_varchar2
1192 ,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
1193 ,p_projected_start_date in date default hr_api.g_date
1194 ,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
1195 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
1196 ,p_resume_exists in varchar2 default hr_api.g_varchar2
1197 ,p_resume_last_updated in date default hr_api.g_date
1198 ,p_second_passport_exists in varchar2 default hr_api.g_varchar2
1199 ,p_student_status in varchar2 default hr_api.g_varchar2
1200 ,p_work_schedule in varchar2 default hr_api.g_varchar2
1201 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
1202 ,p_suffix in varchar2 default hr_api.g_varchar2
1203 ,p_benefit_group_id in number default hr_api.g_number
1204 ,p_receipt_of_death_cert_date in date default hr_api.g_date
1205 ,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
1206 ,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
1207 ,p_coord_ben_med_ext_er in varchar2 default hr_api.g_varchar2
1208 ,p_coord_ben_med_pl_name in varchar2 default hr_api.g_varchar2
1209 ,p_coord_ben_med_insr_crr_name in varchar2 default hr_api.g_varchar2
1210 ,p_coord_ben_med_insr_crr_ident in varchar2 default hr_api.g_varchar2
1211 ,p_coord_ben_med_cvg_strt_dt in date default hr_api.g_date
1212 ,p_coord_ben_med_cvg_end_dt in date default hr_api.g_date
1213 ,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
1214 ,p_dpdnt_adoption_date in date default hr_api.g_date
1215 ,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
1216 ,p_original_date_of_hire in date default hr_api.g_date
1217 ,p_adjusted_svc_date in date default hr_api.g_date
1218 ,p_town_of_birth in varchar2 default hr_api.g_varchar2
1219 ,p_region_of_birth in varchar2 default hr_api.g_varchar2
1220 ,p_country_of_birth in varchar2 default hr_api.g_varchar2
1221 ,p_global_person_id in varchar2 default hr_api.g_varchar2
1222 ,p_party_id in number default hr_api.g_number
1223 ,p_npw_number in varchar2 default hr_api.g_varchar2
1224 ,p_effective_start_date out nocopy date
1225 ,p_effective_end_date out nocopy date
1226 ,p_full_name out nocopy varchar2
1227 ,p_comment_id out nocopy number
1228 ,p_name_combination_warning out nocopy boolean
1229 ,p_assign_payroll_warning out nocopy boolean
1230 ,p_orig_hire_warning out nocopy boolean
1231 ) is
1232 --
1233 -- Declare cursors and local variables
1234 --
1235
1236 l_proc varchar2(72) := g_package||'update_gb_person';
1237 l_effective_date date;
1238 l_legislation_code per_business_groups.legislation_code%type;
1239 l_discard_varchar2 varchar2(30);
1240 --
1241 /*cursor check_legislation
1242 (c_person_id per_people_f.person_id%TYPE,
1243 c_effective_date date
1244 )
1245 is
1246 select bgp.legislation_code
1247 from per_people_f per,
1248 per_business_groups bgp
1249 where per.business_group_id = bgp.business_group_id
1250 and per.person_id = c_person_id
1251 and c_effective_date
1252 between per.effective_start_date and per.effective_end_date;
1253
1254 Modified the cursor for the bug 6131445 and 6064284 */
1255
1256 cursor check_legislation
1257 (c_person_id per_people_f.person_id%TYPE,
1258 c_effective_date date
1259 )
1260 is
1261 select bgp.legislation_code
1262 from per_people_f per,
1263 per_business_groups_perf bgp
1264 where per.business_group_id+0 = bgp.business_group_id
1265 and per.person_id = c_person_id
1266 and c_effective_date
1267 between per.effective_start_date and per.effective_end_date;
1268 --
1269 begin
1270
1271 if g_debug then
1272 hr_utility.set_location('Entering:'|| l_proc, 5);
1273 end if;
1274 --
1275 -- Initialise local variables
1276 --
1277 l_effective_date := trunc(p_effective_date);
1278 --
1279 -- Validation in addition to Row Handlers
1280 --
1281 -- Check that the person exists.
1282 --
1283 open check_legislation(p_person_id, l_effective_date);
1284 fetch check_legislation into l_legislation_code;
1285 if check_legislation%notfound then
1286 close check_legislation;
1287 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1288 hr_utility.raise_error;
1289 end if;
1290 close check_legislation;
1291 if g_debug then
1292 hr_utility.set_location(l_proc, 20);
1293 end if;
1294 --
1295 -- Check that the legislation of the specified business group is 'GB'.
1296 --
1297 if l_legislation_code <> 'GB' then
1298 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1299 hr_utility.set_message_token('LEG_CODE','GB');
1300 hr_utility.raise_error;
1301 end if;
1302 if g_debug then
1303 hr_utility.set_location(l_proc, 30);
1304 end if;
1305 --
1306 -- Update the person record using the update_person BP
1307 --
1308 hr_person_api.update_person
1309 (p_validate => p_validate
1310 ,p_effective_date => l_effective_date
1311 ,p_datetrack_update_mode => p_datetrack_update_mode
1312 ,p_person_id => p_person_id
1313 ,p_object_version_number => p_object_version_number
1314 ,p_person_type_id => p_person_type_id
1315 ,p_last_name => p_last_name
1316 ,p_applicant_number => p_applicant_number
1317 ,p_comments => p_comments
1318 ,p_date_employee_data_verified => p_date_employee_data_verified
1319 ,p_date_of_birth => p_date_of_birth
1320 ,p_email_address => p_email_address
1321 ,p_employee_number => p_employee_number
1322 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1323 ,p_first_name => p_first_name
1324 ,p_known_as => p_known_as
1325 ,p_marital_status => p_marital_status
1326 ,p_middle_names => p_middle_names
1327 ,p_nationality => p_nationality
1328 ,p_national_identifier => p_ni_number
1329 ,p_previous_last_name => p_previous_last_name
1330 ,p_registered_disabled_flag => p_registered_disabled_flag
1331 ,p_sex => p_sex
1332 ,p_title => p_title
1333 ,p_vendor_id => p_vendor_id
1334 ,p_work_telephone => p_work_telephone
1335 ,p_attribute_category => p_attribute_category
1336 ,p_attribute1 => p_attribute1
1337 ,p_attribute2 => p_attribute2
1338 ,p_attribute3 => p_attribute3
1339 ,p_attribute4 => p_attribute4
1340 ,p_attribute5 => p_attribute5
1341 ,p_attribute6 => p_attribute6
1342 ,p_attribute7 => p_attribute7
1343 ,p_attribute8 => p_attribute8
1344 ,p_attribute9 => p_attribute9
1345 ,p_attribute10 => p_attribute10
1346 ,p_attribute11 => p_attribute11
1347 ,p_attribute12 => p_attribute12
1348 ,p_attribute13 => p_attribute13
1349 ,p_attribute14 => p_attribute14
1350 ,p_attribute15 => p_attribute15
1351 ,p_attribute16 => p_attribute16
1352 ,p_attribute17 => p_attribute17
1353 ,p_attribute18 => p_attribute18
1354 ,p_attribute19 => p_attribute19
1355 ,p_attribute20 => p_attribute20
1356 ,p_attribute21 => p_attribute21
1357 ,p_attribute22 => p_attribute22
1358 ,p_attribute23 => p_attribute23
1359 ,p_attribute24 => p_attribute24
1360 ,p_attribute25 => p_attribute25
1361 ,p_attribute26 => p_attribute26
1362 ,p_attribute27 => p_attribute27
1363 ,p_attribute28 => p_attribute28
1364 ,p_attribute29 => p_attribute29
1365 ,p_attribute30 => p_attribute30
1366 ,p_per_information_category => 'GB'
1367 ,p_per_information1 => p_ethnic_origin
1368 ,p_per_information2 => p_director
1369 ,p_per_information4 => p_pensioner
1370 ,p_per_information5 => p_work_permit_number
1371 ,p_per_information6 => p_addl_pension_years
1372 ,p_per_information7 => p_addl_pension_months
1373 ,p_per_information8 => p_addl_pension_days
1374 ,p_per_information9 => p_ni_multiple_asg
1375 ,p_per_information10 => p_paye_aggregate_assignment
1376 ,p_date_of_death => p_date_of_death
1377 ,p_background_check_status => p_background_check_status
1378 ,p_background_date_check => p_background_date_check
1379 ,p_blood_type => p_blood_type
1380 ,p_correspondence_language => p_correspondence_language
1381 ,p_fast_path_employee => p_fast_path_employee
1382 ,p_fte_capacity => p_fte_capacity
1383 ,p_hold_applicant_date_until => p_hold_applicant_date_until
1384 ,p_honors => p_honors
1385 ,p_internal_location => p_internal_location
1386 ,p_last_medical_test_by => p_last_medical_test_by
1387 ,p_last_medical_test_date => p_last_medical_test_date
1388 ,p_mailstop => p_mailstop
1389 ,p_office_number => p_office_number
1390 ,p_on_military_service => p_on_military_service
1391 ,p_pre_name_adjunct => p_pre_name_adjunct
1392 ,p_projected_start_date => p_projected_start_date
1393 ,p_rehire_authorizor => p_rehire_authorizor
1394 ,p_rehire_recommendation => p_rehire_recommendation
1395 ,p_resume_exists => p_resume_exists
1396 ,p_resume_last_updated => p_resume_last_updated
1397 ,p_second_passport_exists => p_second_passport_exists
1398 ,p_student_status => p_student_status
1399 ,p_work_schedule => p_work_schedule
1400 ,p_rehire_reason => p_rehire_reason
1401 ,p_suffix => p_suffix
1402 ,p_benefit_group_id => p_benefit_group_id
1403 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1404 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1405 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1406 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1407 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1408 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1409 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1410 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1411 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1412 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1413 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1414 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1415 ,p_original_date_of_hire => p_original_date_of_hire
1416 ,p_adjusted_svc_date => p_adjusted_svc_date
1417 ,p_town_of_birth => p_town_of_birth
1418 ,p_region_of_birth => p_region_of_birth
1419 ,p_country_of_birth => p_country_of_birth
1420 ,p_global_person_id => p_global_person_id
1421 ,p_party_id => p_party_id
1422 ,p_npw_number => p_npw_number
1423 ,p_effective_start_date => p_effective_start_date
1424 ,p_effective_end_date => p_effective_end_date
1425 ,p_full_name => p_full_name
1426 ,p_comment_id => p_comment_id
1427 ,p_name_combination_warning => p_name_combination_warning
1428 ,p_assign_payroll_warning => p_assign_payroll_warning
1429 ,p_orig_hire_warning => p_orig_hire_warning
1430 );
1431 --
1432 if g_debug then
1433 hr_utility.set_location(' Leaving:'||l_proc, 7);
1434 end if;
1435 --
1436 end update_gb_person;
1437 --
1438 -- ----------------------------------------------------------------------------
1439 -- |--------------------------< update_us_person >----------------------------|
1440 -- ----------------------------------------------------------------------------
1441 --
1442 procedure update_us_person
1443 (p_validate in boolean default false
1444 ,p_effective_date in date
1445 ,p_datetrack_update_mode in varchar2
1446 ,p_person_id in number
1447 ,p_object_version_number in out nocopy number
1448 ,p_person_type_id in number default hr_api.g_number
1449 ,p_last_name in varchar2 default hr_api.g_varchar2
1450 ,p_applicant_number in varchar2 default hr_api.g_varchar2
1451 ,p_comments in varchar2 default hr_api.g_varchar2
1452 ,p_date_employee_data_verified in date default hr_api.g_date
1453 ,p_date_of_birth in date default hr_api.g_date
1454 ,p_email_address in varchar2 default hr_api.g_varchar2
1455 ,p_employee_number in out nocopy varchar2
1456 ,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
1457 ,p_first_name in varchar2 default hr_api.g_varchar2
1458 ,p_known_as in varchar2 default hr_api.g_varchar2
1459 ,p_marital_status in varchar2 default hr_api.g_varchar2
1460 ,p_middle_names in varchar2 default hr_api.g_varchar2
1461 ,p_nationality in varchar2 default hr_api.g_varchar2
1462 ,p_ss_number in varchar2 default hr_api.g_varchar2
1463 ,p_previous_last_name in varchar2 default hr_api.g_varchar2
1464 ,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
1465 ,p_sex in varchar2 default hr_api.g_varchar2
1466 ,p_title in varchar2 default hr_api.g_varchar2
1467 ,p_vendor_id in number default hr_api.g_number
1468 ,p_work_telephone in varchar2 default hr_api.g_varchar2
1469 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1470 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1471 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1472 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1473 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1474 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1475 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1476 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1477 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1478 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1479 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1480 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1481 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1482 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1483 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1484 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1485 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1486 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1487 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1488 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1489 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1490 ,p_attribute21 in varchar2 default hr_api.g_varchar2
1491 ,p_attribute22 in varchar2 default hr_api.g_varchar2
1492 ,p_attribute23 in varchar2 default hr_api.g_varchar2
1493 ,p_attribute24 in varchar2 default hr_api.g_varchar2
1494 ,p_attribute25 in varchar2 default hr_api.g_varchar2
1495 ,p_attribute26 in varchar2 default hr_api.g_varchar2
1496 ,p_attribute27 in varchar2 default hr_api.g_varchar2
1497 ,p_attribute28 in varchar2 default hr_api.g_varchar2
1498 ,p_attribute29 in varchar2 default hr_api.g_varchar2
1499 ,p_attribute30 in varchar2 default hr_api.g_varchar2
1500 ,p_ethnic_origin in varchar2 default hr_api.g_varchar2
1501 ,p_I_9 in varchar2 default hr_api.g_varchar2
1502 ,p_I_9_expiration_date in varchar2 default hr_api.g_varchar2
1503 -- ,p_visa_type in varchar2 default hr_api.g_varchar2
1504 ,p_veteran_status in varchar2 default hr_api.g_varchar2
1505 ,p_new_hire in varchar2 default hr_api.g_varchar2
1506 ,p_exception_reason in varchar2 default hr_api.g_varchar2
1507 ,p_child_support_obligation in varchar2 default hr_api.g_varchar2
1508 ,p_opted_for_medicare_flag in varchar2 default hr_api.g_varchar2
1509 ,p_date_of_death in date default hr_api.g_date
1510 ,p_background_check_status in varchar2 default hr_api.g_varchar2
1511 ,p_background_date_check in date default hr_api.g_date
1512 ,p_blood_type in varchar2 default hr_api.g_varchar2
1513 ,p_correspondence_language in varchar2 default hr_api.g_varchar2
1514 ,p_fast_path_employee in varchar2 default hr_api.g_varchar2
1515 ,p_fte_capacity in number default hr_api.g_number
1516 ,p_hold_applicant_date_until in date default hr_api.g_date
1517 ,p_honors in varchar2 default hr_api.g_varchar2
1518 ,p_internal_location in varchar2 default hr_api.g_varchar2
1519 ,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
1520 ,p_last_medical_test_date in date default hr_api.g_date
1521 ,p_mailstop in varchar2 default hr_api.g_varchar2
1522 ,p_office_number in varchar2 default hr_api.g_varchar2
1523 ,p_on_military_service in varchar2 default hr_api.g_varchar2
1524 ,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
1525 ,p_projected_start_date in date default hr_api.g_date
1526 ,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
1527 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
1528 ,p_resume_exists in varchar2 default hr_api.g_varchar2
1529 ,p_resume_last_updated in date default hr_api.g_date
1530 ,p_second_passport_exists in varchar2 default hr_api.g_varchar2
1531 ,p_student_status in varchar2 default hr_api.g_varchar2
1532 ,p_work_schedule in varchar2 default hr_api.g_varchar2
1533 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
1534 ,p_suffix in varchar2 default hr_api.g_varchar2
1535 ,p_benefit_group_id in number default hr_api.g_number
1536 ,p_receipt_of_death_cert_date in date default hr_api.g_date
1537 ,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
1538 ,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
1539 ,p_coord_ben_med_ext_er in varchar2 default hr_api.g_varchar2
1540 ,p_coord_ben_med_pl_name in varchar2 default hr_api.g_varchar2
1541 ,p_coord_ben_med_insr_crr_name in varchar2 default hr_api.g_varchar2
1542 ,p_coord_ben_med_insr_crr_ident in varchar2 default hr_api.g_varchar2
1543 ,p_coord_ben_med_cvg_strt_dt in date default hr_api.g_date
1544 ,p_coord_ben_med_cvg_end_dt in date default hr_api.g_date
1545 ,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
1546 ,p_dpdnt_adoption_date in date default hr_api.g_date
1547 ,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
1548 ,p_original_date_of_hire in date default hr_api.g_date
1549 ,p_adjusted_svc_date in date default hr_api.g_date
1550 ,p_town_of_birth in varchar2 default hr_api.g_varchar2
1551 ,p_region_of_birth in varchar2 default hr_api.g_varchar2
1552 ,p_country_of_birth in varchar2 default hr_api.g_varchar2
1553 ,p_global_person_id in varchar2 default hr_api.g_varchar2
1554 ,p_party_id in number default hr_api.g_number
1555 ,p_npw_number in varchar2 default hr_api.g_varchar2
1556 ,p_effective_start_date out nocopy date
1557 ,p_effective_end_date out nocopy date
1558 ,p_full_name out nocopy varchar2
1559 ,p_comment_id out nocopy number
1560 ,p_name_combination_warning out nocopy boolean
1561 ,p_assign_payroll_warning out nocopy boolean
1562 ,p_orig_hire_warning out nocopy boolean
1563 ) is
1564
1565 l_vets100A varchar2(100);
1566
1567 --
1568 -- Declare cursors and local variables
1569 --
1570 l_effective_date date;
1571
1572 /*
1573 l_proc varchar2(72) := g_package||'update_us_person';
1574 l_effective_date date;
1575 l_legislation_code per_business_groups.legislation_code%type;
1576 l_discard_varchar2 varchar2(30);
1577 */
1578 --
1579 /*
1580 cursor check_legislation
1581 (c_person_id per_people_f.person_id%TYPE,
1582 c_effective_date date
1583 )
1584 is
1585 select bgp.legislation_code
1586 from per_people_f per,
1587 -- per_business_groups bgp -- 6131445
1588 -- where per.business_group_id = bgp.business_group_id
1589 per_business_groups_perf bgp
1590 where per.business_group_id+0 = bgp.business_group_id
1591 and per.person_id = c_person_id
1592 and c_effective_date
1593 between per.effective_start_date and per.effective_end_date;
1594 */
1595 --
1596 begin
1597 l_effective_date := trunc(p_effective_date);
1598 /*
1599 if g_debug then
1600 hr_utility.set_location('Entering:'|| l_proc, 5);
1601 end if;
1602 --
1603 -- Initialise local variables
1604 --
1605 l_effective_date := trunc(p_effective_date);
1606 --
1607 -- Validation in addition to Row Handlers
1608 --
1609 -- Check that the person exists.
1610 --
1611 open check_legislation(p_person_id, l_effective_date);
1612 fetch check_legislation into l_legislation_code;
1613 if check_legislation%notfound then
1614 close check_legislation;
1615 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1616 hr_utility.raise_error;
1617 end if;
1618 close check_legislation;
1619 if g_debug then
1620 hr_utility.set_location(l_proc, 20);
1621 end if;
1622 --
1623 -- Check that the legislation of the specified business group is 'US'.
1624 --
1625 if l_legislation_code <> 'US' then
1626 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1627 hr_utility.set_message_token('LEG_CODE','US');
1628 hr_utility.raise_error;
1629 end if;
1630 if g_debug then
1631 hr_utility.set_location(l_proc, 30);
1632 end if;
1633 */
1634 --
1635 -- Update the person record using the update_person BP
1636 --
1637 --hr_person_api.update_person
1638
1639 hr_person_api.update_US_person
1640 (p_validate => p_validate
1641 ,p_effective_date => l_effective_date
1642 ,p_datetrack_update_mode => p_datetrack_update_mode
1643 ,p_person_id => p_person_id
1644 ,p_object_version_number => p_object_version_number
1645 ,p_person_type_id => p_person_type_id
1646 ,p_last_name => p_last_name
1647 ,p_applicant_number => p_applicant_number
1648 ,p_comments => p_comments
1649 ,p_date_employee_data_verified => p_date_employee_data_verified
1650 ,p_date_of_birth => p_date_of_birth
1651 ,p_email_address => p_email_address
1652 ,p_employee_number => p_employee_number
1653 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1654 ,p_first_name => p_first_name
1655 ,p_known_as => p_known_as
1656 ,p_marital_status => p_marital_status
1657 ,p_middle_names => p_middle_names
1658 ,p_nationality => p_nationality
1659 ,p_ss_number => p_ss_number
1660 ,p_previous_last_name => p_previous_last_name
1661 ,p_registered_disabled_flag => p_registered_disabled_flag
1662 ,p_sex => p_sex
1663 ,p_title => p_title
1664 ,p_vendor_id => p_vendor_id
1665 ,p_work_telephone => p_work_telephone
1666 ,p_attribute_category => p_attribute_category
1667 ,p_attribute1 => p_attribute1
1668 ,p_attribute2 => p_attribute2
1669 ,p_attribute3 => p_attribute3
1670 ,p_attribute4 => p_attribute4
1671 ,p_attribute5 => p_attribute5
1672 ,p_attribute6 => p_attribute6
1673 ,p_attribute7 => p_attribute7
1674 ,p_attribute8 => p_attribute8
1675 ,p_attribute9 => p_attribute9
1676 ,p_attribute10 => p_attribute10
1677 ,p_attribute11 => p_attribute11
1678 ,p_attribute12 => p_attribute12
1679 ,p_attribute13 => p_attribute13
1680 ,p_attribute14 => p_attribute14
1681 ,p_attribute15 => p_attribute15
1682 ,p_attribute16 => p_attribute16
1683 ,p_attribute17 => p_attribute17
1684 ,p_attribute18 => p_attribute18
1685 ,p_attribute19 => p_attribute19
1686 ,p_attribute20 => p_attribute20
1687 ,p_attribute21 => p_attribute21
1688 ,p_attribute22 => p_attribute22
1689 ,p_attribute23 => p_attribute23
1690 ,p_attribute24 => p_attribute24
1691 ,p_attribute25 => p_attribute25
1692 ,p_attribute26 => p_attribute26
1693 ,p_attribute27 => p_attribute27
1694 ,p_attribute28 => p_attribute28
1695 ,p_attribute29 => p_attribute29
1696 ,p_attribute30 => p_attribute30
1697 -- ,p_per_information_category => 'US'
1698 ,p_ethnic_origin => p_ethnic_origin
1699 ,p_I_9 => p_I_9
1700 ,p_I_9_expiration_date => p_I_9_expiration_date
1701 -- ,p_visa_type => p_visa_type
1702 ,p_veteran_status => p_veteran_status
1703 ,p_vets100A => l_vets100A
1704 ,p_new_hire => p_new_hire
1705 ,p_exception_reason => p_exception_reason
1706 ,p_child_support_obligation => p_child_support_obligation
1707 ,p_opted_for_medicare_flag => p_opted_for_medicare_flag
1708 ,p_date_of_death => p_date_of_death
1709 ,p_background_check_status => p_background_check_status
1710 ,p_background_date_check => p_background_date_check
1711 ,p_blood_type => p_blood_type
1712 ,p_correspondence_language => p_correspondence_language
1713 ,p_fast_path_employee => p_fast_path_employee
1714 ,p_fte_capacity => p_fte_capacity
1715 ,p_hold_applicant_date_until => p_hold_applicant_date_until
1716 ,p_honors => p_honors
1717 ,p_internal_location => p_internal_location
1718 ,p_last_medical_test_by => p_last_medical_test_by
1719 ,p_last_medical_test_date => p_last_medical_test_date
1720 ,p_mailstop => p_mailstop
1721 ,p_office_number => p_office_number
1722 ,p_on_military_service => p_on_military_service
1723 ,p_pre_name_adjunct => p_pre_name_adjunct
1724 ,p_projected_start_date => p_projected_start_date
1725 ,p_rehire_authorizor => p_rehire_authorizor
1726 ,p_rehire_recommendation => p_rehire_recommendation
1727 ,p_resume_exists => p_resume_exists
1728 ,p_resume_last_updated => p_resume_last_updated
1729 ,p_second_passport_exists => p_second_passport_exists
1730 ,p_student_status => p_student_status
1731 ,p_work_schedule => p_work_schedule
1732 ,p_rehire_reason => p_rehire_reason
1733 ,p_suffix => p_suffix
1734 ,p_benefit_group_id => p_benefit_group_id
1735 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1736 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1737 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1738 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1739 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1740 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1741 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1742 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1743 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1744 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1745 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1746 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1747 ,p_original_date_of_hire => p_original_date_of_hire
1748 ,p_adjusted_svc_date => p_adjusted_svc_date
1749 ,p_town_of_birth => p_town_of_birth
1750 ,p_region_of_birth => p_region_of_birth
1751 ,p_country_of_birth => p_country_of_birth
1752 ,p_global_person_id => p_global_person_id
1753 ,p_party_id => p_party_id
1754 ,p_npw_number => p_npw_number
1755 ,p_effective_start_date => p_effective_start_date
1756 ,p_effective_end_date => p_effective_end_date
1757 ,p_full_name => p_full_name
1758 ,p_comment_id => p_comment_id
1759 ,p_name_combination_warning => p_name_combination_warning
1760 ,p_assign_payroll_warning => p_assign_payroll_warning
1761 ,p_orig_hire_warning => p_orig_hire_warning
1762 );
1763 /* if g_debug then
1764 hr_utility.set_location('Leaving: '||l_proc, 30);
1765 end if;
1766 */
1767 --
1768 end update_us_person;
1769 --
1770
1771 -- Overloaded the function Create_US_employee for bug 8277596
1772
1773 procedure update_us_person
1774 (p_validate in boolean default false
1775 ,p_effective_date in date
1776 ,p_datetrack_update_mode in varchar2
1777 ,p_person_id in number
1778 ,p_object_version_number in out nocopy number
1779 ,p_person_type_id in number default hr_api.g_number
1780 ,p_last_name in varchar2 default hr_api.g_varchar2
1781 ,p_applicant_number in varchar2 default hr_api.g_varchar2
1782 ,p_comments in varchar2 default hr_api.g_varchar2
1783 ,p_date_employee_data_verified in date default hr_api.g_date
1784 ,p_date_of_birth in date default hr_api.g_date
1785 ,p_email_address in varchar2 default hr_api.g_varchar2
1786 ,p_employee_number in out nocopy varchar2
1787 ,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
1788 ,p_first_name in varchar2 default hr_api.g_varchar2
1789 ,p_known_as in varchar2 default hr_api.g_varchar2
1790 ,p_marital_status in varchar2 default hr_api.g_varchar2
1791 ,p_middle_names in varchar2 default hr_api.g_varchar2
1792 ,p_nationality in varchar2 default hr_api.g_varchar2
1793 ,p_ss_number in varchar2 default hr_api.g_varchar2
1794 ,p_previous_last_name in varchar2 default hr_api.g_varchar2
1795 ,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
1796 ,p_sex in varchar2 default hr_api.g_varchar2
1797 ,p_title in varchar2 default hr_api.g_varchar2
1798 ,p_vendor_id in number default hr_api.g_number
1799 ,p_work_telephone in varchar2 default hr_api.g_varchar2
1800 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1801 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1802 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1803 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1804 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1805 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1806 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1807 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1808 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1809 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1810 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1811 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1812 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1813 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1814 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1815 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1816 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1817 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1818 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1819 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1820 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1821 ,p_attribute21 in varchar2 default hr_api.g_varchar2
1822 ,p_attribute22 in varchar2 default hr_api.g_varchar2
1823 ,p_attribute23 in varchar2 default hr_api.g_varchar2
1824 ,p_attribute24 in varchar2 default hr_api.g_varchar2
1825 ,p_attribute25 in varchar2 default hr_api.g_varchar2
1826 ,p_attribute26 in varchar2 default hr_api.g_varchar2
1827 ,p_attribute27 in varchar2 default hr_api.g_varchar2
1828 ,p_attribute28 in varchar2 default hr_api.g_varchar2
1829 ,p_attribute29 in varchar2 default hr_api.g_varchar2
1830 ,p_attribute30 in varchar2 default hr_api.g_varchar2
1831 ,p_ethnic_origin in varchar2 default hr_api.g_varchar2
1832 ,p_I_9 in varchar2 default hr_api.g_varchar2
1833 ,p_I_9_expiration_date in varchar2 default hr_api.g_varchar2
1834 -- ,p_visa_type in varchar2 default hr_api.g_varchar2
1835 ,p_veteran_status in varchar2 default hr_api.g_varchar2
1836 ,p_vets100A in varchar2 -- default hr_api.g_varchar2 -- Fix For Bug # 8833244
1837 ,p_new_hire in varchar2 default hr_api.g_varchar2
1838 ,p_exception_reason in varchar2 default hr_api.g_varchar2
1839 ,p_child_support_obligation in varchar2 default hr_api.g_varchar2
1840 ,p_opted_for_medicare_flag in varchar2 default hr_api.g_varchar2
1841 ,p_date_of_death in date default hr_api.g_date
1842 ,p_background_check_status in varchar2 default hr_api.g_varchar2
1843 ,p_background_date_check in date default hr_api.g_date
1844 ,p_blood_type in varchar2 default hr_api.g_varchar2
1845 ,p_correspondence_language in varchar2 default hr_api.g_varchar2
1846 ,p_fast_path_employee in varchar2 default hr_api.g_varchar2
1847 ,p_fte_capacity in number default hr_api.g_number
1848 ,p_hold_applicant_date_until in date default hr_api.g_date
1849 ,p_honors in varchar2 default hr_api.g_varchar2
1850 ,p_internal_location in varchar2 default hr_api.g_varchar2
1851 ,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
1852 ,p_last_medical_test_date in date default hr_api.g_date
1853 ,p_mailstop in varchar2 default hr_api.g_varchar2
1854 ,p_office_number in varchar2 default hr_api.g_varchar2
1855 ,p_on_military_service in varchar2 default hr_api.g_varchar2
1856 ,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
1857 ,p_projected_start_date in date default hr_api.g_date
1858 ,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
1859 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
1860 ,p_resume_exists in varchar2 default hr_api.g_varchar2
1861 ,p_resume_last_updated in date default hr_api.g_date
1862 ,p_second_passport_exists in varchar2 default hr_api.g_varchar2
1863 ,p_student_status in varchar2 default hr_api.g_varchar2
1864 ,p_work_schedule in varchar2 default hr_api.g_varchar2
1865 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
1866 ,p_suffix in varchar2 default hr_api.g_varchar2
1867 ,p_benefit_group_id in number default hr_api.g_number
1868 ,p_receipt_of_death_cert_date in date default hr_api.g_date
1869 ,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
1870 ,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
1871 ,p_coord_ben_med_ext_er in varchar2 default hr_api.g_varchar2
1872 ,p_coord_ben_med_pl_name in varchar2 default hr_api.g_varchar2
1873 ,p_coord_ben_med_insr_crr_name in varchar2 default hr_api.g_varchar2
1874 ,p_coord_ben_med_insr_crr_ident in varchar2 default hr_api.g_varchar2
1875 ,p_coord_ben_med_cvg_strt_dt in date default hr_api.g_date
1876 ,p_coord_ben_med_cvg_end_dt in date default hr_api.g_date
1877 ,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
1878 ,p_dpdnt_adoption_date in date default hr_api.g_date
1879 ,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
1880 ,p_original_date_of_hire in date default hr_api.g_date
1881 ,p_adjusted_svc_date in date default hr_api.g_date
1882 ,p_town_of_birth in varchar2 default hr_api.g_varchar2
1883 ,p_region_of_birth in varchar2 default hr_api.g_varchar2
1884 ,p_country_of_birth in varchar2 default hr_api.g_varchar2
1885 ,p_global_person_id in varchar2 default hr_api.g_varchar2
1886 ,p_party_id in number default hr_api.g_number
1887 ,p_npw_number in varchar2 default hr_api.g_varchar2
1888 ,p_effective_start_date out nocopy date
1889 ,p_effective_end_date out nocopy date
1890 ,p_full_name out nocopy varchar2
1891 ,p_comment_id out nocopy number
1892 ,p_name_combination_warning out nocopy boolean
1893 ,p_assign_payroll_warning out nocopy boolean
1894 ,p_orig_hire_warning out nocopy boolean
1895 ) is
1896 --
1897 -- Declare cursors and local variables
1898 --
1899 l_proc varchar2(72) := g_package||'update_us_person';
1900 l_effective_date date;
1901 l_legislation_code per_business_groups.legislation_code%type;
1902 l_discard_varchar2 varchar2(30);
1903 --
1904 cursor check_legislation
1905 (c_person_id per_people_f.person_id%TYPE,
1906 c_effective_date date
1907 )
1908 is
1909 select bgp.legislation_code
1910 from per_people_f per,
1911 -- per_business_groups bgp -- 6131445
1912 -- where per.business_group_id = bgp.business_group_id
1913 per_business_groups_perf bgp
1914 where per.business_group_id+0 = bgp.business_group_id
1915 and per.person_id = c_person_id
1916 and c_effective_date
1917 between per.effective_start_date and per.effective_end_date;
1918 --
1919 begin
1920 if g_debug then
1921 hr_utility.set_location('Entering:'|| l_proc, 5);
1922 end if;
1923 --
1924 -- Initialise local variables
1925 --
1926 l_effective_date := trunc(p_effective_date);
1927 --
1928 -- Validation in addition to Row Handlers
1929 --
1930 -- Check that the person exists.
1931 --
1932 open check_legislation(p_person_id, l_effective_date);
1933 fetch check_legislation into l_legislation_code;
1934 if check_legislation%notfound then
1935 close check_legislation;
1936 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1937 hr_utility.raise_error;
1938 end if;
1939 close check_legislation;
1940 if g_debug then
1941 hr_utility.set_location(l_proc, 20);
1942 end if;
1943 --
1944 -- Check that the legislation of the specified business group is 'US'.
1945 --
1946 if l_legislation_code <> 'US' then
1947 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1948 hr_utility.set_message_token('LEG_CODE','US');
1949 hr_utility.raise_error;
1950 end if;
1951 if g_debug then
1952 hr_utility.set_location(l_proc, 30);
1953 end if;
1954 --
1955 -- Update the person record using the update_person BP
1956 --
1957 hr_person_api.update_person
1958 (p_validate => p_validate
1959 ,p_effective_date => l_effective_date
1960 ,p_datetrack_update_mode => p_datetrack_update_mode
1961 ,p_person_id => p_person_id
1962 ,p_object_version_number => p_object_version_number
1963 ,p_person_type_id => p_person_type_id
1964 ,p_last_name => p_last_name
1965 ,p_applicant_number => p_applicant_number
1966 ,p_comments => p_comments
1967 ,p_date_employee_data_verified => p_date_employee_data_verified
1968 ,p_date_of_birth => p_date_of_birth
1969 ,p_email_address => p_email_address
1970 ,p_employee_number => p_employee_number
1971 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1972 ,p_first_name => p_first_name
1973 ,p_known_as => p_known_as
1974 ,p_marital_status => p_marital_status
1975 ,p_middle_names => p_middle_names
1976 ,p_nationality => p_nationality
1977 ,p_national_identifier => p_ss_number
1978 ,p_previous_last_name => p_previous_last_name
1979 ,p_registered_disabled_flag => p_registered_disabled_flag
1980 ,p_sex => p_sex
1981 ,p_title => p_title
1982 ,p_vendor_id => p_vendor_id
1983 ,p_work_telephone => p_work_telephone
1984 ,p_attribute_category => p_attribute_category
1985 ,p_attribute1 => p_attribute1
1986 ,p_attribute2 => p_attribute2
1987 ,p_attribute3 => p_attribute3
1988 ,p_attribute4 => p_attribute4
1989 ,p_attribute5 => p_attribute5
1990 ,p_attribute6 => p_attribute6
1991 ,p_attribute7 => p_attribute7
1992 ,p_attribute8 => p_attribute8
1993 ,p_attribute9 => p_attribute9
1994 ,p_attribute10 => p_attribute10
1995 ,p_attribute11 => p_attribute11
1996 ,p_attribute12 => p_attribute12
1997 ,p_attribute13 => p_attribute13
1998 ,p_attribute14 => p_attribute14
1999 ,p_attribute15 => p_attribute15
2000 ,p_attribute16 => p_attribute16
2001 ,p_attribute17 => p_attribute17
2002 ,p_attribute18 => p_attribute18
2003 ,p_attribute19 => p_attribute19
2004 ,p_attribute20 => p_attribute20
2005 ,p_attribute21 => p_attribute21
2006 ,p_attribute22 => p_attribute22
2007 ,p_attribute23 => p_attribute23
2008 ,p_attribute24 => p_attribute24
2009 ,p_attribute25 => p_attribute25
2010 ,p_attribute26 => p_attribute26
2011 ,p_attribute27 => p_attribute27
2012 ,p_attribute28 => p_attribute28
2013 ,p_attribute29 => p_attribute29
2014 ,p_attribute30 => p_attribute30
2015 ,p_per_information_category => 'US'
2016 ,p_per_information1 => p_ethnic_origin
2017 ,p_per_information2 => p_I_9
2018 ,p_per_information3 => p_I_9_expiration_date
2019 -- ,p_per_information4 => p_visa_type
2020 ,p_per_information5 => p_veteran_status
2021 ,p_per_information7 => p_new_hire
2022 ,p_per_information8 => p_exception_reason
2023 ,p_per_information9 => p_child_support_obligation
2024 ,p_per_information10 => p_opted_for_medicare_flag
2025 ,p_per_information25 => p_vets100A
2026 ,p_date_of_death => p_date_of_death
2027 ,p_background_check_status => p_background_check_status
2028 ,p_background_date_check => p_background_date_check
2029 ,p_blood_type => p_blood_type
2030 ,p_correspondence_language => p_correspondence_language
2031 ,p_fast_path_employee => p_fast_path_employee
2032 ,p_fte_capacity => p_fte_capacity
2033 ,p_hold_applicant_date_until => p_hold_applicant_date_until
2034 ,p_honors => p_honors
2035 ,p_internal_location => p_internal_location
2036 ,p_last_medical_test_by => p_last_medical_test_by
2037 ,p_last_medical_test_date => p_last_medical_test_date
2038 ,p_mailstop => p_mailstop
2039 ,p_office_number => p_office_number
2040 ,p_on_military_service => p_on_military_service
2041 ,p_pre_name_adjunct => p_pre_name_adjunct
2042 ,p_projected_start_date => p_projected_start_date
2043 ,p_rehire_authorizor => p_rehire_authorizor
2044 ,p_rehire_recommendation => p_rehire_recommendation
2045 ,p_resume_exists => p_resume_exists
2046 ,p_resume_last_updated => p_resume_last_updated
2047 ,p_second_passport_exists => p_second_passport_exists
2048 ,p_student_status => p_student_status
2049 ,p_work_schedule => p_work_schedule
2050 ,p_rehire_reason => p_rehire_reason
2051 ,p_suffix => p_suffix
2052 ,p_benefit_group_id => p_benefit_group_id
2053 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
2054 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
2055 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
2056 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
2057 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
2058 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
2059 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
2060 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
2061 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
2062 ,p_uses_tobacco_flag => p_uses_tobacco_flag
2063 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
2064 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
2065 ,p_original_date_of_hire => p_original_date_of_hire
2066 ,p_adjusted_svc_date => p_adjusted_svc_date
2067 ,p_town_of_birth => p_town_of_birth
2068 ,p_region_of_birth => p_region_of_birth
2069 ,p_country_of_birth => p_country_of_birth
2070 ,p_global_person_id => p_global_person_id
2071 ,p_party_id => p_party_id
2072 ,p_npw_number => p_npw_number
2073 ,p_effective_start_date => p_effective_start_date
2074 ,p_effective_end_date => p_effective_end_date
2075 ,p_full_name => p_full_name
2076 ,p_comment_id => p_comment_id
2077 ,p_name_combination_warning => p_name_combination_warning
2078 ,p_assign_payroll_warning => p_assign_payroll_warning
2079 ,p_orig_hire_warning => p_orig_hire_warning
2080 );
2081 if g_debug then
2082 hr_utility.set_location('Leaving: '||l_proc, 30);
2083 end if;
2084 --
2085 end update_us_person;
2086
2087 -- Fix for 3908271 starts here.
2088 --
2089 -- ----------------------------------------------------------------------------
2090 -- |---------------------------< delete_person >------------------------------|
2091 -- ----------------------------------------------------------------------------
2092 --
2093 procedure delete_person
2094 (p_validate in boolean default false
2095 ,p_effective_date in date
2096 ,p_person_id in number
2097 ,p_perform_predel_validation boolean default false
2098 ,p_person_org_manager_warning out nocopy varchar2) is
2099 --
2100 /*## Cursor to fetch the Release Versions
2101 ## For bug 3945358 */
2102 cursor RelVersion is
2103 select RELEASE_NAME from FND_PRODUCT_groups;
2104 --
2105
2106 --for bug 7369431
2107 cursor chk_person_type is
2108 SELECT typ.system_person_type
2109 FROM per_person_types typ
2110 ,per_person_type_usages_f ptu
2111 WHERE typ.person_type_id = ptu.person_type_id
2112 AND p_effective_date BETWEEN ptu.effective_start_date
2113 AND ptu.effective_end_date
2114 AND ptu.person_id = p_person_id;
2115
2116 l_system_person_type VARCHAR2(2000);
2117 --
2118
2119 l_effective_date date;
2120 l_validate boolean;
2121 l_party_id per_all_people_f.party_id%type;
2122 --
2123 l_proc varchar2(72) := g_package||'delete_person';
2124 --
2125 begin
2126
2127 -- Fetch party Id for the TCA bug 3945358
2128 select party_id into l_party_id from per_all_people_f
2129 where person_id = p_person_id and rownum =1;
2130 --
2131
2132 -- added for bug 7369431
2133 Open chk_person_type;
2134 fetch chk_person_type into l_system_person_type;
2135 close chk_person_type;
2136 --
2137
2138 if g_debug then
2139 hr_utility.set_location('Entering:'|| l_proc, 5);
2140 hr_utility.set_location('p_effective_date:'||
2141 to_char(p_effective_date,'DD/MM/YYYY'), 5);
2142 hr_utility.set_location('p_person_id :'|| p_person_id, 5);
2143 end if;
2144 --
2145 -- Initialise local variables before call to hr_person_bk2.delete_person_b
2146 --
2147 l_effective_date := trunc(p_effective_date);
2148 l_validate := p_validate ;
2149 --
2150 -- Issue a savepoint.
2151 --
2152 savepoint hr_delete_person;
2153 --
2154 begin
2155 --
2156 -- Start of API User Hook for the before hook of update_person
2157 --
2158 hr_person_bk2.delete_person_b
2159 (p_effective_date => l_effective_date
2160 ,p_person_id => p_person_id
2161 );
2162 --
2163 exception
2164 when hr_api.cannot_find_prog_unit then
2165 hr_api.cannot_find_prog_unit_error
2166 (p_module_name => 'DELETE_PERSON'
2167 ,p_hook_type => 'BP'
2168 );
2169 --
2170 -- End of API User Hook for the before hook of delete_person
2171 --
2172 end;
2173 --
2174 if g_debug then
2175 hr_utility.set_location(l_proc, 6);
2176 end if;
2177 --
2178 -- Check whether the person is manager for any organization.
2179 -- If so, set the out warning parameter.
2180 --
2181 hr_person_internal.delete_org_manager(p_person_id => p_person_id
2182 ,p_effective_date => p_effective_date
2183 ,p_person_org_manager_warning => p_person_org_manager_warning);
2184 --
2185 -- Perform minimal Core HR pre-delete validations.
2186 --
2187 -- The following procedure need to be modified to remove the
2188 -- other product validations like BEN.
2189 --
2190 HR_PERSON_INTERNAL.weak_predel_validation
2191 (p_person_id => p_person_id
2192 ,p_effective_date => l_effective_date);
2193 --
2194 if g_debug then
2195 hr_utility.set_location(l_proc, 7);
2196 end if;
2197 --
2198 -- Depending on the parameter p_perform_predel_validation,
2199 -- additional Core HR validations (strong validations).
2200 --
2201 IF p_perform_predel_validation THEN
2202 --
2203 -- The following procedure now will contains only
2204 -- Core HR specific validations as other product validations
2205 -- will be commented out and should be done through hooks.
2206 --
2207 HR_PERSON_INTERNAL.strong_predel_validation
2208 (p_person_id => p_person_id
2209 ,p_effective_date => l_effective_date);
2210 --
2211 END IF;
2212 --
2213 -- Now perform the deletion of the person.
2214 --
2215 HR_PERSON_INTERNAL.delete_person
2216 (p_person_id => p_person_id
2217 ,p_effective_date => l_effective_date);
2218 --
2219 if g_debug then
2220 hr_utility.set_location(l_proc, 8);
2221 end if;
2222 --
2223 begin
2224 --
2225 -- Start of API User Hook for the after hook of delete_person
2226 --
2227 hr_person_bk2.delete_person_a
2228 (p_effective_date => l_effective_date
2229 ,p_person_id => p_person_id
2230 ,p_person_org_manager_warning => p_person_org_manager_warning);
2231 exception
2232 when hr_api.cannot_find_prog_unit then
2233 hr_api.cannot_find_prog_unit_error
2234 (p_module_name => 'DELETE_PERSON'
2235 ,p_hook_type => 'AP'
2236 );
2237 --
2238 -- End of API User Hook for the after hook of delete_person
2239 --
2240 end;
2241 --
2242 --
2243 -- When in validation only mode raise the Validate_Enabled exception
2244 --
2245 if p_validate then
2246 raise hr_api.validate_enabled;
2247 end if;
2248 --
2249
2250 /*----- for TCA person Purge Start for the bug 3945358 -*/
2251 hr_utility.set_location('before calling purge_person ', 90);
2252 begin
2253 for I in RelVersion
2254 loop
2255 if I.RELEASE_NAME not in ('11.5.1', '11.5.2','11.5.3','11.5.4','11.5.5') then
2256 begin
2257 -- for bug 7369431
2258 if l_party_id is not null and nvl(l_system_person_type,'OTHER') not in('OTHER') THEN
2259 per_hrtca_merge.purge_person (p_person_id => p_person_id,p_party_id => l_party_id);
2260 end if;
2261 --
2262 exception
2263 when others then
2264 ROLLBACK TO hr_delete_person;
2265 end;
2266 end if;
2267 end loop;
2268 hr_utility.set_location('After calling purge_person ', 91);
2269 end;
2270 /*----- End for TCA person Purge -*/
2271
2272 if g_debug then
2273 hr_utility.set_location(' Leaving:'||l_proc, 100);
2274 end if;
2275 --
2276 exception
2277 when hr_api.validate_enabled then
2278 --
2279 -- As the Validate_Enabled exception has been raised
2280 -- we must rollback to the savepoint
2281 --
2282 p_person_org_manager_warning := null;
2283 --
2284 ROLLBACK TO hr_delete_person;
2285 --
2286 when others then
2287 --
2288 -- A validation or unexpected error has occurred
2289 --
2290 p_person_org_manager_warning := null;
2291 --
2292 ROLLBACK TO hr_delete_person;
2293 raise;
2294 --
2295 end delete_person;
2296
2297 --
2298 -- Fix for 3908271 ends here.
2299 --
2300
2301 -- ----------------------------------------------------------------------------
2302 -- |---------------------------< update_assign_records >----------------------|
2303 -- ----------------------------------------------------------------------------
2304 --
2305
2306 PROCEDURE Update_assign_records(s_assignment_id in NUMBER
2307 ,t_assignment_id in number
2308 ,apl_ass_start_date in DATE
2309 ,p_object_version_number in number
2310 ,p_application_id in number default null)
2311 IS
2312 CURSOR get_pgp(p_people_group_id NUMBER) IS
2313 SELECT *
2314 FROM pay_people_groups
2315 WHERE people_group_id = p_people_group_id;
2316 l_pgp_rec pay_people_groups%ROWTYPE := NULL;
2317 CURSOR get_scl(p_soft_coding_keyflex_id NUMBER) IS
2318 SELECT *
2319 FROM hr_soft_coding_keyflex
2320 WHERE soft_coding_keyflex_id = p_soft_coding_keyflex_id;
2321 l_scl_rec hr_soft_coding_keyflex%ROWTYPE := NULL;
2322 CURSOR get_cag(p_cagr_grade_def_id NUMBER) IS
2323 SELECT *
2324 FROM per_cagr_grades_def
2325 WHERE cagr_grade_def_id = p_cagr_grade_def_id;
2326 l_cag_rec per_cagr_grades_def%ROWTYPE := NULL;
2327 CURSOR assignment_record_update IS
2328 SELECT *
2329 FROM per_all_assignments_f
2330 WHERE assignment_id = s_assignment_id
2331 AND apl_ass_start_date <effective_start_date
2332 ORDER BY effective_start_date;
2333
2334
2335 cursor csr_old_asg_status(p_date date) is
2336 select ast.per_system_status,asg.assignment_status_type_id
2337 from per_assignment_status_types ast,
2338 per_all_assignments_f asg
2339 where ast.assignment_status_type_id = asg.assignment_status_type_id
2340 and asg.assignment_id = t_assignment_id
2341 and p_date between asg.effective_start_date and asg.effective_end_date;
2342
2343 cursor csr_new_asg_status(p_assignment_status_type_id number) is
2344 select ast.per_system_status
2345 from per_assignment_status_types ast
2346 where ast.assignment_status_type_id = p_assignment_status_type_id;
2347
2348 l_assignment_id NUMBER;
2349 l_cagr_id_flex_num NUMBER;
2350 l_cagr_grade_def_id NUMBER;
2351 l_people_group_id NUMBER;
2352 l_soft_coding_keyflex_id NUMBER;
2353 l_asg_effective_start_date DATE;
2354 l_asg_effective_end_date DATE;
2355 l_group_name VARCHAR2(1000);
2356 l_concatenated_segments VARCHAR2(1000);
2357 l_comment_id NUMBER;
2358 l_asg_object_version_number NUMBER;
2359 l_cagr_concatenated_segments VARCHAR2(1000);
2360 l_application_id number;
2361 l_datetrack_update_mode VARCHAR2(20);
2362 l_effective_date date;
2363 l_old_asg_status VARCHAR2(50);
2364 l_assignment_status_type_id number;
2365 l_max_eff_end_date date;
2366 l_new_asg_status VARCHAR2(50);
2367 l_old_asg_status_id number;
2368 l_proc varchar2(72) := g_package||'update_assign_records';
2369 BEGIN
2370 FOR ass_rec IN assignment_record_update LOOP
2371 EXIT WHEN assignment_record_update%NOTFOUND;
2372
2373 IF ass_rec.people_group_id IS NOT NULL THEN
2374 OPEN get_pgp(ass_rec.people_group_id);
2375
2376 FETCH get_pgp INTO l_pgp_rec;
2377
2378 CLOSE get_pgp;
2379 END IF;
2380
2381 if g_debug then
2382 hr_utility.set_location(l_proc, 1);
2383 end if;
2384
2385 IF ass_rec.soft_coding_keyflex_id IS NOT NULL THEN
2386 OPEN get_scl(ass_rec.soft_coding_keyflex_id);
2387
2388 FETCH get_scl INTO l_scl_rec;
2389
2390 CLOSE get_scl;
2391 END IF;
2392
2393 IF ass_rec.cagr_grade_def_id IS NOT NULL THEN
2394 OPEN get_cag(ass_rec.cagr_grade_def_id);
2395
2396 FETCH get_cag INTO l_cag_rec;
2397
2398 CLOSE get_cag;
2399 END IF;
2400
2401 l_people_group_id := ass_rec.people_group_id;
2402
2403 l_soft_coding_keyflex_id := ass_rec.soft_coding_keyflex_id;
2404
2405 l_cagr_grade_def_id := ass_rec.cagr_grade_def_id;
2406
2407
2408
2409
2410 l_assignment_id := t_assignment_id;
2411 if assignment_record_update%rowcount = 1 then
2412 l_asg_object_version_number:=p_object_version_number;
2413 end if;
2414 hr_utility.set_location('l_assignment_id ' || t_assignment_id,10);
2415 hr_utility.set_location('l_asg_object_version_number' || l_asg_object_version_number,10);
2416
2417
2418 open csr_old_asg_status(ass_rec.effective_start_date);
2419 fetch csr_old_asg_status into l_old_asg_status,l_old_asg_status_id;
2420 close csr_old_asg_status;
2421
2422 OPEN csr_new_asg_status( ass_rec.assignment_status_type_id );
2423 FETCH csr_new_asg_status INTO l_new_asg_status;
2424 CLOSE csr_new_asg_status;
2425 if l_old_asg_status =l_new_asg_status then
2426
2427 l_assignment_status_type_id :=ass_rec.assignment_status_type_id;
2428
2429 else
2430 l_assignment_status_type_id :=l_old_asg_status_id;
2431 end if;
2432 if g_debug then
2433 hr_utility.set_location(l_proc, 2);
2434 end if;
2435 hr_assignment_api.update_apl_asg
2436 (p_effective_date => ass_rec.effective_start_date --p_effective_date
2437 ,p_datetrack_update_mode => 'UPDATE'
2438 ,p_assignment_id => l_assignment_id
2439 ,p_object_version_number => l_asg_object_version_number
2440 ,p_grade_id => ass_rec.grade_id
2441 ,p_grade_ladder_pgm_id => ass_rec.grade_ladder_pgm_id
2442 ,p_job_id => ass_rec.job_id
2443 ,p_payroll_id => ass_rec.payroll_id
2444 ,p_location_id => ass_rec.location_id
2445 ,p_organization_id => ass_rec.organization_id
2446 ,p_position_id => ass_rec.position_id
2447 ,p_special_ceiling_step_id => ass_rec.special_ceiling_step_id
2448 ,p_recruiter_id => ass_rec.recruiter_id
2449 ,p_recruitment_activity_id => ass_rec.recruitment_activity_id
2450 ,p_vacancy_id => ass_rec.vacancy_id
2451 ,p_pay_basis_id => ass_rec.pay_basis_id
2452 ,p_person_referred_by_id => ass_rec.person_referred_by_id
2453 ,p_supervisor_id => ass_rec.supervisor_id
2454 ,p_supervisor_assignment_id => ass_rec.supervisor_assignment_id
2455 ,p_source_organization_id => ass_rec.source_organization_id
2456 ,p_change_reason => ass_rec.change_reason
2457 ,p_assignment_status_type_id => l_assignment_status_type_id
2458 ,p_internal_address_line => ass_rec.internal_address_line
2459 ,p_default_code_comb_id => ass_rec.default_code_comb_id
2460 ,p_employment_category => ass_rec.employment_category
2461 ,p_frequency => ass_rec.frequency
2462 ,p_manager_flag => ass_rec.manager_flag
2463 ,p_normal_hours => ass_rec.normal_hours
2464 ,p_perf_review_period => ass_rec.perf_review_period
2465 ,p_perf_review_period_frequency => ass_rec.perf_review_period_frequency
2466 ,p_probation_period => ass_rec.probation_period
2467 ,p_probation_unit => ass_rec.probation_unit
2468 ,p_sal_review_period => ass_rec.sal_review_period
2469 ,p_sal_review_period_frequency => ass_rec.sal_review_period_frequency
2470 ,p_set_of_books_id => ass_rec.set_of_books_id
2471 ,p_title => ass_rec.title
2472 ,p_source_type => ass_rec.source_type
2473 ,p_time_normal_finish => ass_rec.time_normal_finish
2474 ,p_time_normal_start => ass_rec.time_normal_start
2475 ,p_bargaining_unit_code => ass_rec.bargaining_unit_code
2476 ,p_date_probation_end => ass_rec.date_probation_end
2477 ,p_ass_attribute_category => ass_rec.ass_attribute_category
2478 ,p_ass_attribute1 => ass_rec.ass_attribute1
2479 ,p_ass_attribute2 => ass_rec.ass_attribute2
2480 ,p_ass_attribute3 => ass_rec.ass_attribute3
2481 ,p_ass_attribute4 => ass_rec.ass_attribute4
2482 ,p_ass_attribute5 => ass_rec.ass_attribute5
2483 ,p_ass_attribute6 => ass_rec.ass_attribute6
2484 ,p_ass_attribute7 => ass_rec.ass_attribute7
2485 ,p_ass_attribute8 => ass_rec.ass_attribute8
2486 ,p_ass_attribute9 => ass_rec.ass_attribute9
2487 ,p_ass_attribute10 => ass_rec.ass_attribute10
2488 ,p_ass_attribute11 => ass_rec.ass_attribute11
2489 ,p_ass_attribute12 => ass_rec.ass_attribute12
2490 ,p_ass_attribute13 => ass_rec.ass_attribute13
2491 ,p_ass_attribute14 => ass_rec.ass_attribute14
2492 ,p_ass_attribute15 => ass_rec.ass_attribute15
2493 ,p_ass_attribute16 => ass_rec.ass_attribute16
2494 ,p_ass_attribute17 => ass_rec.ass_attribute17
2495 ,p_ass_attribute18 => ass_rec.ass_attribute18
2496 ,p_ass_attribute19 => ass_rec.ass_attribute19
2497 ,p_ass_attribute20 => ass_rec.ass_attribute20
2498 ,p_ass_attribute21 => ass_rec.ass_attribute21
2499 ,p_ass_attribute22 => ass_rec.ass_attribute22
2500 ,p_ass_attribute23 => ass_rec.ass_attribute23
2501 ,p_ass_attribute24 => ass_rec.ass_attribute24
2502 ,p_ass_attribute25 => ass_rec.ass_attribute25
2503 ,p_ass_attribute26 => ass_rec.ass_attribute26
2504 ,p_ass_attribute27 => ass_rec.ass_attribute27
2505 ,p_ass_attribute28 => ass_rec.ass_attribute28
2506 ,p_ass_attribute29 => ass_rec.ass_attribute29
2507 ,p_ass_attribute30 => ass_rec.ass_attribute30
2508 ,p_scl_segment1 => l_scl_rec.segment1
2509 ,p_scl_segment2 => l_scl_rec.segment2
2510 ,p_scl_segment3 => l_scl_rec.segment3
2511 ,p_scl_segment4 => l_scl_rec.segment4
2512 ,p_scl_segment5 => l_scl_rec.segment5
2513 ,p_scl_segment6 => l_scl_rec.segment6
2514 ,p_scl_segment7 => l_scl_rec.segment7
2515 ,p_scl_segment8 => l_scl_rec.segment8
2516 ,p_scl_segment9 => l_scl_rec.segment9
2517 ,p_scl_segment10 => l_scl_rec.segment10
2518 ,p_scl_segment11 => l_scl_rec.segment11
2519 ,p_scl_segment12 => l_scl_rec.segment12
2520 ,p_scl_segment13 => l_scl_rec.segment13
2521 ,p_scl_segment14 => l_scl_rec.segment14
2522 ,p_scl_segment15 => l_scl_rec.segment15
2523 ,p_scl_segment16 => l_scl_rec.segment16
2524 ,p_scl_segment17 => l_scl_rec.segment17
2525 ,p_scl_segment18 => l_scl_rec.segment18
2526 ,p_scl_segment19 => l_scl_rec.segment19
2527 ,p_scl_segment20 => l_scl_rec.segment20
2528 ,p_scl_segment21 => l_scl_rec.segment21
2529 ,p_scl_segment22 => l_scl_rec.segment22
2530 ,p_scl_segment23 => l_scl_rec.segment23
2531 ,p_scl_segment24 => l_scl_rec.segment24
2532 ,p_scl_segment25 => l_scl_rec.segment25
2533 ,p_scl_segment26 => l_scl_rec.segment26
2534 ,p_scl_segment27 => l_scl_rec.segment27
2535 ,p_scl_segment28 => l_scl_rec.segment28
2536 ,p_scl_segment29 => l_scl_rec.segment29
2537 ,p_scl_segment30 => l_scl_rec.segment30
2538 ,p_pgp_segment1 => l_pgp_rec.segment1
2539 ,p_pgp_segment2 => l_pgp_rec.segment2
2540 ,p_pgp_segment3 => l_pgp_rec.segment3
2541 ,p_pgp_segment4 => l_pgp_rec.segment4
2542 ,p_pgp_segment5 => l_pgp_rec.segment5
2543 ,p_pgp_segment6 => l_pgp_rec.segment6
2544 ,p_pgp_segment7 => l_pgp_rec.segment7
2545 ,p_pgp_segment8 => l_pgp_rec.segment8
2546 ,p_pgp_segment9 => l_pgp_rec.segment9
2547 ,p_pgp_segment10 => l_pgp_rec.segment10
2548 ,p_pgp_segment11 => l_pgp_rec.segment11
2549 ,p_pgp_segment12 => l_pgp_rec.segment12
2550 ,p_pgp_segment13 => l_pgp_rec.segment13
2551 ,p_pgp_segment14 => l_pgp_rec.segment14
2552 ,p_pgp_segment15 => l_pgp_rec.segment15
2553 ,p_pgp_segment16 => l_pgp_rec.segment16
2554 ,p_pgp_segment17 => l_pgp_rec.segment17
2555 ,p_pgp_segment18 => l_pgp_rec.segment18
2556 ,p_pgp_segment19 => l_pgp_rec.segment19
2557 ,p_pgp_segment20 => l_pgp_rec.segment20
2558 ,p_pgp_segment21 => l_pgp_rec.segment21
2559 ,p_pgp_segment22 => l_pgp_rec.segment22
2560 ,p_pgp_segment23 => l_pgp_rec.segment23
2561 ,p_pgp_segment24 => l_pgp_rec.segment24
2562 ,p_pgp_segment25 => l_pgp_rec.segment25
2563 ,p_pgp_segment26 => l_pgp_rec.segment26
2564 ,p_pgp_segment27 => l_pgp_rec.segment27
2565 ,p_pgp_segment28 => l_pgp_rec.segment28
2566 ,p_pgp_segment29 => l_pgp_rec.segment29
2567 ,p_pgp_segment30 => l_pgp_rec.segment30
2568 ,p_contract_id => ass_rec.contract_id
2569 ,p_establishment_id => ass_rec.establishment_id
2570 ,p_collective_agreement_id => ass_rec.collective_agreement_id
2571 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2572 ,p_work_at_home => ass_rec.work_at_home
2573 ,p_notice_period => ass_rec.notice_period
2574 ,p_notice_period_uom => ass_rec.notice_period_uom
2575 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2576 ,p_group_name => l_group_name
2577 ,p_concatenated_segments => l_concatenated_segments
2578 ,p_comment_id => l_comment_id
2579 ,p_people_group_id => l_people_group_id
2580 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2581 ,p_effective_start_date => l_asg_effective_start_date
2582 ,p_effective_end_date => l_asg_effective_end_date
2583 -- fix for bug 9718515 starts here.
2584 ,p_job_post_source_name => ass_rec.job_post_source_name
2585 ,p_applicant_rank => ass_rec.applicant_rank
2586 ,p_posting_content_id => ass_rec.posting_content_id
2587 ,p_employee_category => ass_rec.employee_category
2588 -- fix for bug 9718515 ends here.
2589 );
2590
2591 if l_old_asg_status <> l_new_asg_status then
2592
2593 If l_new_asg_status = 'ACTIVE_APL' then
2594 hr_assignment_api.activate_apl_asg
2595 (p_effective_date => ass_rec.effective_start_date
2596 ,p_datetrack_update_mode => 'CORRECTION'
2597 ,p_assignment_id => l_assignment_id
2598 ,p_object_version_number => l_asg_object_version_number
2599 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
2600 ,p_change_reason => ass_rec.change_reason
2601 ,p_effective_start_date => l_asg_effective_start_date
2602 ,p_effective_end_date => l_asg_effective_end_date
2603 );
2604
2605 elsif l_new_asg_status = 'OFFER' then
2606 hr_assignment_api.offer_apl_asg
2607 (p_effective_date => ass_rec.effective_start_date
2608 ,p_datetrack_update_mode => 'CORRECTION'
2609 ,p_assignment_id => l_assignment_id
2610 ,p_object_version_number => l_asg_object_version_number
2611 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
2612 ,p_change_reason => ass_rec.change_reason
2613 ,p_effective_start_date => l_asg_effective_start_date
2614 ,p_effective_end_date => l_asg_effective_end_date
2615 );
2616
2617 elsif l_new_asg_status = 'ACCEPTED' then
2618 hr_assignment_api.accept_apl_asg
2619 (p_effective_date => ass_rec.effective_start_date
2620 ,p_datetrack_update_mode => 'CORRECTION'
2621 ,p_assignment_id => l_assignment_id
2622 ,p_object_version_number => l_asg_object_version_number
2623 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
2624 ,p_change_reason => ass_rec.change_reason
2625 ,p_effective_start_date => l_asg_effective_start_date
2626 ,p_effective_end_date => l_asg_effective_end_date
2627 );
2628
2629 elsif l_new_asg_status = 'INTERVIEW1' then
2630 hr_assignment_api.interview1_apl_asg
2631 (p_effective_date => ass_rec.effective_start_date
2632 ,p_datetrack_update_mode => 'CORRECTION'
2633 ,p_assignment_id => l_assignment_id
2634 ,p_object_version_number => l_asg_object_version_number
2635 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
2636 ,p_change_reason => ass_rec.change_reason
2637 ,p_effective_start_date => l_asg_effective_start_date
2638 ,p_effective_end_date => l_asg_effective_end_date
2639 );
2640
2641 elsif l_new_asg_status = 'INTERVIEW2' then
2642 hr_assignment_api.interview2_apl_asg
2643 (p_effective_date => ass_rec.effective_start_date
2644 ,p_datetrack_update_mode => 'CORRECTION'
2645 ,p_assignment_id => l_assignment_id
2646 ,p_object_version_number => l_asg_object_version_number
2647 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
2648 ,p_change_reason => ass_rec.change_reason
2649 ,p_effective_start_date => l_asg_effective_start_date
2650 ,p_effective_end_date => l_asg_effective_end_date
2651 );
2652
2653 end if;
2654
2655 end if;
2656
2657
2658 END LOOP;
2659 if g_debug then
2660 hr_utility.set_location(l_proc, 3);
2661 end if;
2662 select max(effective_end_date) into l_max_eff_end_date
2663 from per_all_assignments_f
2664 where assignment_id=s_assignment_id;
2665 if l_max_eff_end_date <> to_date('31/12/4712','DD/MM/YYYY') then
2666
2667 hr_assignment_api.terminate_apl_asg
2668 (p_effective_date => l_max_eff_end_date
2669 ,p_assignment_id => l_assignment_id
2670 ,p_object_version_number => l_asg_object_version_number
2671 ,p_effective_start_date => l_asg_effective_start_date
2672 ,p_effective_end_date => l_asg_effective_end_date
2673 );
2674
2675 end if;
2676 if g_debug then
2677 hr_utility.set_location(l_proc, 4);
2678 end if;
2679 END update_assign_records;
2680
2681 --
2682 -- ----------------------------------------------------------------------------
2683 -- |---------------------------< merge_person >------------------------------|
2684 -- ----------------------------------------------------------------------------
2685 --
2686 procedure merge_person(p_target_person_id in number
2687 ,p_source_person_id in number
2688 ,p_term_or_purge_s in varchar2 default null
2689 ,p_create_new_application in varchar2 default 'Y')
2690 IS
2691
2692 CURSOR appl_assignments IS
2693 SELECT assignment_id,
2694 Min(effective_start_date) eff_start_date
2695 FROM per_all_assignments_f
2696 WHERE person_id = p_source_person_id
2697 GROUP BY assignment_id;
2698
2699 CURSOR assign_record(c_apl_ass_start_date DATE,
2700 c_assignment_id NUMBER) IS
2701 SELECT *
2702 FROM per_all_assignments_f
2703 WHERE assignment_id = c_assignment_id
2704 AND c_apl_ass_start_date BETWEEN effective_start_date AND effective_end_date;
2705
2706 CURSOR csr_sys_person_type(c_person_id NUMBER) IS
2707 SELECT pet.system_person_type
2708 FROM per_all_people_f per,
2709 per_person_types pet
2710 WHERE per.person_type_id = pet.person_type_id
2711 AND per.person_id = c_person_id
2712 AND trunc(sysdate) BETWEEN per.effective_start_date AND per.effective_end_date;
2713
2714 CURSOR csr_ptu_details(c_person_id NUMBER) IS
2715 SELECT person_type_usage_id
2716 FROM per_person_type_usages_f
2717 WHERE person_id = c_person_id
2718 AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;
2719
2720
2721 CURSOR get_pgp(c_people_group_id NUMBER) IS
2722 SELECT *
2723 FROM pay_people_groups
2724 WHERE people_group_id = c_people_group_id;
2725
2726 CURSOR get_scl(c_soft_coding_keyflex_id NUMBER) IS
2727 SELECT *
2728 FROM hr_soft_coding_keyflex
2729 WHERE soft_coding_keyflex_id = c_soft_coding_keyflex_id;
2730
2731 CURSOR get_cag(c_cagr_grade_def_id NUMBER) IS
2732 SELECT *
2733 FROM per_cagr_grades_def
2734 WHERE cagr_grade_def_id = c_cagr_grade_def_id;
2735
2736 l_t_person_type VARCHAR2(39);
2737 l_s_person_type VARCHAR2(39);
2738 l_ptu_id NUMBER;
2739 l_bg_id NUMBER;
2740 l_t_start_date DATE;
2741 l_apl_ass_start_date DATE;
2742 l_party_id NUMBER;
2743 l_person_org_manager_warning VARCHAR2(1000);
2744 l_per_assign_record assign_record%ROWTYPE;
2745 l_pgp_rec pay_people_groups%ROWTYPE := NULL;
2746 l_primary_pgp_rec pay_people_groups%ROWTYPE;
2747 l_scl_rec hr_soft_coding_keyflex%ROWTYPE := NULL;
2748 l_primary_scl_rec hr_soft_coding_keyflex%ROWTYPE := NULL;
2749 l_cag_rec per_cagr_grades_def%ROWTYPE := NULL;
2750 l_primary_cag_rec per_cagr_grades_def%ROWTYPE;
2751 l_concatenated_segments VARCHAR2(1000);
2752 l_cagr_grade_def_id NUMBER;
2753 l_people_group_id NUMBER;
2754 l_soft_coding_keyflex_id NUMBER;
2755 l_cagr_concatenated_segments VARCHAR2(1000);
2756 l_group_name VARCHAR2(1000);
2757 l_assignment_id NUMBER;
2758 l_comment_id NUMBER;
2759 l_object_version_number NUMBER;
2760 l_effective_start_date DATE;
2761 l_effective_end_date DATE;
2762 l_assignment_sequence NUMBER;
2763 l_appl_override_warning BOOLEAN;
2764 l_scl_concat_segments VARCHAR2(1000);
2765 l_applicant_number NUMBER;
2766 l_per_object_version_number NUMBER;
2767 l_assignment_status_type_id NUMBER;
2768 l_application_id NUMBER;
2769 l_apl_object_version_number NUMBER;
2770 l_asg_object_version_number NUMBER;
2771 l_per_effective_start_date DATE;
2772 l_per_effective_end_date DATE;
2773 l_exists number :=0;
2774 l_can_exists number :=1;
2775 l_vac_exists number :=0;
2776 l_t_party_id number;
2777 l_prev_exists varchar2(1) := 'N';
2778 l_create_new_application varchar2(1):= 'Y';
2779 l_proc varchar2(72) := g_package||'merge_person';
2780 l_new_assgn_status VARCHAR2(50); --fix for bug 9714569
2781
2782 BEGIN
2783 if g_debug then
2784 hr_utility.set_location('Entering:'|| l_proc, 5);
2785 end if;
2786
2787 OPEN csr_sys_person_type(p_source_person_id);
2788 FETCH csr_sys_person_type INTO l_s_person_type;
2789 CLOSE csr_sys_person_type;
2790
2791 SELECT business_group_id
2792 INTO l_bg_id
2793 FROM per_all_people_f
2794 WHERE person_id = p_source_person_id
2795 AND effective_start_date = start_date;
2796
2797 BEGIN
2798 select 1 into l_exists
2799 from per_person_type_usages_f ptuf, per_person_types ppt
2800 where ppt.system_person_type = 'IRC_REG_USER'
2801 and ptuf.person_type_id = ppt.person_type_id
2802 and ppt.business_group_id + 0 = nvl(l_bg_id,ppt.business_group_id)
2803 and not exists (select null from per_all_assignments_f paaf where paaf.person_id = ptuf.person_id)
2804 and not exists (select null from per_contact_relationships pcr where pcr.contact_person_id = ptuf.person_id)
2805 and ptuf.person_id=p_source_person_id;
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 NULL;
2809 END;
2810
2811 OPEN csr_ptu_details(p_source_person_id);
2812 FETCH csr_ptu_details INTO l_ptu_id;
2813 CLOSE csr_ptu_details;
2814
2815 if g_debug then
2816 hr_utility.set_location(l_proc, 6);
2817 end if;
2818
2819
2820 IF (l_exists = 1
2821 AND p_term_or_purge_s = 'PURGE') THEN
2822 hr_person_api.Delete_person(p_validate => false,
2823 p_effective_date => trunc(sysdate),
2824 p_person_id => p_source_person_id,
2825 p_perform_predel_validation => false,
2826 p_person_org_manager_warning => l_person_org_manager_warning);
2827 ELSIF (l_exists = 1
2828 AND p_term_or_purge_s = 'TERM') THEN
2829 -- hr_utility.set_message(800, 'HR_7092_WFLOW_INV_ACTION'); fix for bug 9691817.
2830 -- hr_utility.raise_error;
2831 null;
2832 END IF;
2833
2834 if g_debug then
2835 hr_utility.set_location(l_proc, 7);
2836 end if;
2837
2838
2839 IF (l_s_person_type = 'APL') THEN
2840
2841
2842 IF (hr_person_type_usage_info.Futsyspertypechgexists(l_ptu_id
2843 ,trunc(sysdate)
2844 ,p_source_person_id)
2845 ) THEN
2846 hr_utility.set_message(800, 'HR_7193_PER_FUT_TYPE_EXISTS');
2847 hr_utility.raise_error;
2848 END IF;
2849
2850 if g_debug then
2851 hr_utility.set_location(l_proc, 8);
2852 end if;
2853
2854 --fix for bug 9692642
2855
2856 begin
2857 select 'Y' into l_prev_exists
2858 from sys.dual where exists (
2859 select 'Previous Person type exists'
2860 from per_person_types ppt
2861 , per_person_type_usages_f ptu
2862 where ptu.person_id = p_source_person_id
2863 and ppt.business_group_id +0= l_bg_id
2864 and ptu.person_type_id = ppt.person_type_id
2865 and ppt.system_person_type <>'APL'
2866 AND (ppt.system_person_type='OTHER' and not exists (select 1
2867 from per_person_type_usages_f ptu1,per_person_types ppt1
2868 where ptu1.person_id=p_source_person_id
2869 and ppt1.person_type_id=ptu1.person_type_id
2870 and ppt1.system_person_type='IRC_REG_USER') )
2871 and ppt.system_person_type<>'IRC_REG_USER'
2872 and sysdate between ptu.effective_start_date and ptu.effective_end_date
2873 union
2874 select 'Previous Person type exists'
2875 from per_periods_of_service pps
2876 where pps.person_id =p_source_person_id
2877 and sysdate >= nvl(pps.actual_termination_date,sysdate)
2878 union
2879 select 'Previous Person type exists'
2880 from per_periods_of_placement ppp
2881 where ppp.person_id = p_source_person_id
2882 and sysdate >= nvl(ppp.actual_termination_date,sysdate));
2883
2884 EXCEPTION
2885 WHEN OTHERS THEN
2886 NULL;
2887 if g_debug then
2888 hr_utility.set_location(l_proc, 8.1);
2889 end if;
2890 end;
2891
2892 IF (l_prev_exists='Y')
2893 THEN
2894 hr_utility.set_message(800, 'PER_442266_PERSON_CANNOT_MERGE');
2895 hr_utility.raise_error;
2896 END IF;
2897
2898 if g_debug then
2899 hr_utility.set_location(l_proc, 9);
2900 end if;
2901
2902 SELECT start_date,party_id
2903 INTO l_t_start_date,l_t_party_id
2904 FROM per_all_people_f
2905 WHERE person_id = p_target_person_id
2906 AND effective_start_date = start_date;
2907
2908
2909 FOR app_rec IN appl_assignments LOOP
2910 IF app_rec.eff_start_date < l_t_start_date THEN
2911 l_apl_ass_start_date := trunc(sysdate);
2912 l_create_new_application:=p_create_new_application;
2913 ELSE
2914 l_create_new_application:='Y';
2915 l_apl_ass_start_date := app_rec.eff_start_date;
2916 END IF;
2917
2918 OPEN csr_sys_person_type(p_target_person_id);
2919
2920 FETCH csr_sys_person_type INTO l_t_person_type;
2921
2922 CLOSE csr_sys_person_type;
2923
2924
2925 OPEN assign_record(l_apl_ass_start_date,app_rec.assignment_id);
2926
2927 FETCH assign_record INTO l_per_assign_record;
2928
2929 CLOSE assign_record;
2930
2931
2932 IF l_per_assign_record.people_group_id IS NOT NULL THEN
2933 OPEN get_pgp(l_per_assign_record.people_group_id);
2934
2935 FETCH get_pgp INTO l_pgp_rec;
2936
2937 CLOSE get_pgp;
2938 END IF;
2939
2940 IF l_per_assign_record.soft_coding_keyflex_id IS NOT NULL THEN
2941 OPEN get_scl(l_per_assign_record.soft_coding_keyflex_id);
2942
2943 FETCH get_scl INTO l_scl_rec;
2944
2945 CLOSE get_scl;
2946 END IF;
2947
2948 IF l_per_assign_record.cagr_grade_def_id IS NOT NULL THEN
2949 OPEN get_cag(l_per_assign_record.cagr_grade_def_id);
2950
2951 FETCH get_cag INTO l_cag_rec;
2952
2953 CLOSE get_cag;
2954 END IF;
2955 l_cagr_grade_def_id := l_per_assign_record.cagr_grade_def_id;
2956
2957 l_people_group_id := l_per_assign_record.people_group_id;
2958
2959 l_soft_coding_keyflex_id := l_per_assign_record.soft_coding_keyflex_id;
2960
2961 l_vac_exists:=0;
2962 BEGIN
2963 select 1 into l_vac_exists from dual
2964 where exists(select 1 from per_all_assignments_f
2965 where vacancy_id=l_per_assign_record.vacancy_id
2966 and person_id=p_target_person_id
2967 and assignment_type='A');
2968 EXCEPTION
2969 WHEN OTHERS THEN
2970 NULL;
2971 END;
2972
2973 if g_debug then
2974 hr_utility.set_location(l_proc, 10);
2975 end if;
2976
2977 IF l_t_person_type IN ('APL','EMP_APL','EX_EMP_APL') and l_vac_exists<>1 and l_create_new_application='Y' THEN
2978
2979 if g_debug then
2980 hr_utility.set_location(l_proc, 11);
2981 end if;
2982
2983 BEGIN
2984
2985 hr_assignment_api.create_secondary_apl_asg
2986 (p_validate => false
2987 ,p_effective_date => l_apl_ass_start_date
2988 ,p_person_id => p_target_person_id
2989 ,p_organization_id => l_per_assign_record.organization_id
2990 ,p_recruiter_id => l_per_assign_record.recruiter_id
2991 ,p_grade_id => l_per_assign_record.grade_id
2992 ,p_position_id => l_per_assign_record.position_id
2993 ,p_job_id => l_per_assign_record.job_id
2994 ,p_assignment_status_type_id => l_per_assign_record.assignment_status_type_id
2995 ,p_payroll_id => l_per_assign_record.payroll_id
2996 ,p_location_id => l_per_assign_record.location_id
2997 ,p_person_referred_by_id => l_per_assign_record.person_referred_by_id
2998 ,p_supervisor_id => l_per_assign_record.supervisor_id
2999 ,p_special_ceiling_step_id => l_per_assign_record.special_ceiling_step_id
3000 ,p_recruitment_activity_id => l_per_assign_record.recruitment_activity_id
3001 ,p_source_organization_id => l_per_assign_record.source_organization_id
3002 ,p_vacancy_id => l_per_assign_record.vacancy_id
3003 ,p_pay_basis_id => l_per_assign_record.pay_basis_id
3004 ,p_change_reason => l_per_assign_record.change_reason
3005 ,p_date_probation_end => l_per_assign_record.date_probation_end
3006 ,p_default_code_comb_id => l_per_assign_record.default_code_comb_id
3007 ,p_employment_category => l_per_assign_record.employment_category
3008 ,p_frequency => l_per_assign_record.frequency
3009 ,p_internal_address_line => l_per_assign_record.internal_address_line
3010 ,p_manager_flag => l_per_assign_record.manager_flag
3011 ,p_normal_hours => l_per_assign_record.normal_hours
3012 ,p_perf_review_period => l_per_assign_record.perf_review_period
3013 ,p_perf_review_period_frequency => l_per_assign_record.perf_review_period_frequency
3014 ,p_probation_period => l_per_assign_record.probation_period
3015 ,p_probation_unit => l_per_assign_record.probation_unit
3016 ,p_sal_review_period => l_per_assign_record.sal_review_period
3017 ,p_sal_review_period_frequency => l_per_assign_record.sal_review_period_frequency
3018 ,p_set_of_books_id => l_per_assign_record.set_of_books_id
3019 ,p_source_type => l_per_assign_record.source_type
3020 ,p_time_normal_finish => l_per_assign_record.time_normal_finish
3021 ,p_time_normal_start => l_per_assign_record.time_normal_start
3022 ,p_bargaining_unit_code => l_per_assign_record.bargaining_unit_code
3023 ,p_ass_attribute_category => l_per_assign_record.ass_attribute_category
3024 ,p_ass_attribute1 => l_per_assign_record.ass_attribute1
3025 ,p_ass_attribute2 => l_per_assign_record.ass_attribute2
3026 ,p_ass_attribute3 => l_per_assign_record.ass_attribute3
3027 ,p_ass_attribute4 => l_per_assign_record.ass_attribute4
3028 ,p_ass_attribute5 => l_per_assign_record.ass_attribute5
3029 ,p_ass_attribute6 => l_per_assign_record.ass_attribute6
3030 ,p_ass_attribute7 => l_per_assign_record.ass_attribute7
3031 ,p_ass_attribute8 => l_per_assign_record.ass_attribute8
3032 ,p_ass_attribute9 => l_per_assign_record.ass_attribute9
3033 ,p_ass_attribute10 => l_per_assign_record.ass_attribute10
3034 ,p_ass_attribute11 => l_per_assign_record.ass_attribute11
3035 ,p_ass_attribute12 => l_per_assign_record.ass_attribute12
3036 ,p_ass_attribute13 => l_per_assign_record.ass_attribute13
3037 ,p_ass_attribute14 => l_per_assign_record.ass_attribute14
3038 ,p_ass_attribute15 => l_per_assign_record.ass_attribute15
3039 ,p_ass_attribute16 => l_per_assign_record.ass_attribute16
3040 ,p_ass_attribute17 => l_per_assign_record.ass_attribute17
3041 ,p_ass_attribute18 => l_per_assign_record.ass_attribute18
3042 ,p_ass_attribute19 => l_per_assign_record.ass_attribute19
3043 ,p_ass_attribute20 => l_per_assign_record.ass_attribute20
3044 ,p_ass_attribute21 => l_per_assign_record.ass_attribute21
3045 ,p_ass_attribute22 => l_per_assign_record.ass_attribute22
3046 ,p_ass_attribute23 => l_per_assign_record.ass_attribute23
3047 ,p_ass_attribute24 => l_per_assign_record.ass_attribute24
3048 ,p_ass_attribute25 => l_per_assign_record.ass_attribute25
3049 ,p_ass_attribute26 => l_per_assign_record.ass_attribute26
3050 ,p_ass_attribute27 => l_per_assign_record.ass_attribute27
3051 ,p_ass_attribute28 => l_per_assign_record.ass_attribute28
3052 ,p_ass_attribute29 => l_per_assign_record.ass_attribute29
3053 ,p_ass_attribute30 => l_per_assign_record.ass_attribute30
3054 ,p_title => l_per_assign_record.title
3055 ,p_scl_segment1 => l_scl_rec.segment1
3056 ,p_scl_segment2 => l_scl_rec.segment2
3057 ,p_scl_segment3 => l_scl_rec.segment3
3058 ,p_scl_segment4 => l_scl_rec.segment4
3059 ,p_scl_segment5 => l_scl_rec.segment5
3060 ,p_scl_segment6 => l_scl_rec.segment6
3061 ,p_scl_segment7 => l_scl_rec.segment7
3062 ,p_scl_segment8 => l_scl_rec.segment8
3063 ,p_scl_segment9 => l_scl_rec.segment9
3064 ,p_scl_segment10 => l_scl_rec.segment10
3065 ,p_scl_segment11 => l_scl_rec.segment11
3066 ,p_scl_segment12 => l_scl_rec.segment12
3067 ,p_scl_segment13 => l_scl_rec.segment13
3068 ,p_scl_segment14 => l_scl_rec.segment14
3069 ,p_scl_segment15 => l_scl_rec.segment15
3070 ,p_scl_segment16 => l_scl_rec.segment16
3071 ,p_scl_segment17 => l_scl_rec.segment17
3072 ,p_scl_segment18 => l_scl_rec.segment18
3073 ,p_scl_segment19 => l_scl_rec.segment19
3074 ,p_scl_segment20 => l_scl_rec.segment20
3075 ,p_scl_segment21 => l_scl_rec.segment21
3076 ,p_scl_segment22 => l_scl_rec.segment22
3077 ,p_scl_segment23 => l_scl_rec.segment23
3078 ,p_scl_segment24 => l_scl_rec.segment24
3079 ,p_scl_segment25 => l_scl_rec.segment25
3080 ,p_scl_segment26 => l_scl_rec.segment26
3081 ,p_scl_segment27 => l_scl_rec.segment27
3082 ,p_scl_segment28 => l_scl_rec.segment28
3083 ,p_scl_segment29 => l_scl_rec.segment29
3084 ,p_scl_segment30 => l_scl_rec.segment30
3085 ,p_scl_concat_segments => l_scl_concat_segments
3086 ,p_concatenated_segments => l_concatenated_segments
3087 ,p_pgp_segment1 => l_pgp_rec.segment1
3088 ,p_pgp_segment2 => l_pgp_rec.segment2
3089 ,p_pgp_segment3 => l_pgp_rec.segment3
3090 ,p_pgp_segment4 => l_pgp_rec.segment4
3091 ,p_pgp_segment5 => l_pgp_rec.segment5
3092 ,p_pgp_segment6 => l_pgp_rec.segment6
3093 ,p_pgp_segment7 => l_pgp_rec.segment7
3094 ,p_pgp_segment8 => l_pgp_rec.segment8
3095 ,p_pgp_segment9 => l_pgp_rec.segment9
3096 ,p_pgp_segment10 => l_pgp_rec.segment10
3097 ,p_pgp_segment11 => l_pgp_rec.segment11
3098 ,p_pgp_segment12 => l_pgp_rec.segment12
3099 ,p_pgp_segment13 => l_pgp_rec.segment13
3100 ,p_pgp_segment14 => l_pgp_rec.segment14
3101 ,p_pgp_segment15 => l_pgp_rec.segment15
3102 ,p_pgp_segment16 => l_pgp_rec.segment16
3103 ,p_pgp_segment17 => l_pgp_rec.segment17
3104 ,p_pgp_segment18 => l_pgp_rec.segment18
3105 ,p_pgp_segment19 => l_pgp_rec.segment19
3106 ,p_pgp_segment20 => l_pgp_rec.segment20
3107 ,p_pgp_segment21 => l_pgp_rec.segment21
3108 ,p_pgp_segment22 => l_pgp_rec.segment22
3109 ,p_pgp_segment23 => l_pgp_rec.segment23
3110 ,p_pgp_segment24 => l_pgp_rec.segment24
3111 ,p_pgp_segment25 => l_pgp_rec.segment25
3112 ,p_pgp_segment26 => l_pgp_rec.segment26
3113 ,p_pgp_segment27 => l_pgp_rec.segment27
3114 ,p_pgp_segment28 => l_pgp_rec.segment28
3115 ,p_pgp_segment29 => l_pgp_rec.segment29
3116 ,p_pgp_segment30 => l_pgp_rec.segment30
3117 ,p_contract_id => l_per_assign_record.contract_id
3118 ,p_establishment_id => l_per_assign_record.establishment_id
3119 ,p_collective_agreement_id => l_per_assign_record.collective_agreement_id
3120 ,p_cag_segment1 => l_cag_rec.segment1
3121 ,p_cag_segment2 => l_cag_rec.segment2
3122 ,p_cag_segment3 => l_cag_rec.segment3
3123 ,p_cag_segment4 => l_cag_rec.segment4
3124 ,p_cag_segment5 => l_cag_rec.segment5
3125 ,p_cag_segment6 => l_cag_rec.segment6
3126 ,p_cag_segment7 => l_cag_rec.segment7
3127 ,p_cag_segment8 => l_cag_rec.segment8
3128 ,p_cag_segment9 => l_cag_rec.segment9
3129 ,p_cag_segment10 => l_cag_rec.segment10
3130 ,p_cag_segment11 => l_cag_rec.segment11
3131 ,p_cag_segment12 => l_cag_rec.segment12
3132 ,p_cag_segment13 => l_cag_rec.segment13
3133 ,p_cag_segment14 => l_cag_rec.segment14
3134 ,p_cag_segment15 => l_cag_rec.segment15
3135 ,p_cag_segment16 => l_cag_rec.segment16
3136 ,p_cag_segment17 => l_cag_rec.segment17
3137 ,p_cag_segment18 => l_cag_rec.segment18
3138 ,p_cag_segment19 => l_cag_rec.segment19
3139 ,p_cag_segment20 => l_cag_rec.segment20
3140 ,p_notice_period => l_per_assign_record.notice_period
3141 ,p_notice_period_uom => l_per_assign_record.notice_period_uom
3142 ,p_employee_category => l_per_assign_record.employee_category
3143 ,p_work_at_home => l_per_assign_record.work_at_home
3144 ,p_job_post_source_name => l_per_assign_record.job_post_source_name
3145 ,p_applicant_rank => l_per_assign_record.applicant_rank
3146 ,p_posting_content_id => l_per_assign_record.posting_content_id
3147 ,p_grade_ladder_pgm_id => l_per_assign_record.grade_ladder_pgm_id
3148 ,p_supervisor_assignment_id => l_per_assign_record.supervisor_assignment_id
3149
3150 ,p_cagr_grade_def_id => l_cagr_grade_def_id
3151
3152 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
3153 ,p_group_name => l_group_name
3154 ,p_assignment_id => l_assignment_id
3155 ,p_people_group_id => l_people_group_id
3156 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
3157 ,p_comment_id => l_comment_id
3158 ,p_object_version_number => l_object_version_number
3159 ,p_effective_start_date => l_effective_start_date
3160 ,p_effective_end_date => l_effective_end_date
3161 ,p_assignment_sequence => l_assignment_sequence
3162 ,p_appl_override_warning => l_appl_override_warning
3163 );
3164
3165 if g_debug then
3166 hr_utility.set_location(l_proc, 12);
3167 end if;
3168
3169
3170 Update_assign_records(l_per_assign_record.assignment_id,
3171 l_assignment_id,
3172 l_apl_ass_start_date,
3173 l_object_version_number);
3174 if g_debug then
3175 hr_utility.set_location(l_proc, 13);
3176 end if;
3177 IRC_INTERVIEW_DETAILS_API.copy_interview_details
3178 (p_source_assignment_id => l_per_assign_record.assignment_id
3179 ,p_target_assignment_id => l_assignment_id
3180 ,p_target_party_id => l_t_party_id
3181 );
3182
3183 IRC_COMMUNICATIONS_API.copy_comm_to_apl_asg
3184 (p_target_asg_id => l_assignment_id
3185 ,p_source_asg_id => l_per_assign_record.assignment_id
3186 );
3187
3188 -- Fix for bug 9718515 .
3189
3190 irc_assignment_details_api.copy_assignment_details
3191 (p_source_assignment_id => l_per_assign_record.assignment_id
3192 ,p_target_assignment_id => l_assignment_id
3193 );
3194 --
3195 irc_referral_info_api.copy_referral_details
3196 (p_source_assignment_id => l_per_assign_record.assignment_id
3197 ,p_target_assignment_id => l_assignment_id
3198 );
3199 --
3200 fnd_attached_documents2_pkg.copy_attachments
3201 ( X_from_entity_name =>'PER_ASSIGNMENTS_F'
3202 , X_from_pk1_value => l_per_assign_record.assignment_id
3203 , X_to_entity_name =>'PER_ASSIGNMENTS_F'
3204 , X_to_pk1_value => l_assignment_id
3205 );
3206
3207
3208 END;
3209
3210 BEGIN
3211 select 1 into l_can_exists
3212 from per_person_type_usages_f ptuf, per_person_types ppt
3213 where ppt.system_person_type = 'IRC_REG_USER'
3214 and ptuf.person_type_id = ppt.person_type_id
3215 and ppt.business_group_id + 0 = nvl(l_bg_id,ppt.business_group_id)
3216 and not exists (select null from per_all_assignments_f paaf where paaf.person_id = ptuf.person_id)
3217 and not exists (select null from per_contact_relationships pcr where pcr.contact_person_id = ptuf.person_id)
3218 and ptuf.person_id=p_target_person_id;
3219 EXCEPTION
3220 WHEN OTHERS THEN
3221 NULL;
3222 END;
3223
3224 if g_debug then
3225 hr_utility.set_location(l_proc, 14);
3226 end if;
3227
3228 ELSIF (l_t_person_type IN ('EMP','EX_EMP')
3229 or (l_t_person_type='OTHER' and l_t_party_id is null)
3230 or (l_t_person_type='OTHER' and l_can_exists =1 ) ) -- condition to chk whether person is canditate
3231 AND l_create_new_application='Y' THEN
3232
3233 BEGIN
3234
3235 hr_applicant_api.apply_for_job_anytime
3236 (p_validate => false
3237 ,p_effective_date => l_apl_ass_start_date
3238 ,p_person_id => p_target_person_id
3239 ,p_applicant_number => l_applicant_number
3240 ,p_per_object_version_number => l_per_object_version_number
3241 ,p_assignment_status_type_id => l_assignment_status_type_id
3242 ,p_application_id => l_application_id
3243 ,p_assignment_id => l_assignment_id
3244 ,p_apl_object_version_number => l_apl_object_version_number
3245 ,p_asg_object_version_number => l_asg_object_version_number
3246 ,p_assignment_sequence => l_assignment_sequence
3247 ,p_per_effective_start_date => l_per_effective_start_date
3248 ,p_per_effective_end_date => l_per_effective_end_date
3249 ,p_appl_override_warning => l_appl_override_warning);
3250
3251 if g_debug then
3252 hr_utility.set_location(l_proc, 15);
3253 end if;
3254
3255 hr_assignment_api.update_apl_asg
3256 (p_effective_date => l_apl_ass_start_date --p_effective_date
3257 ,p_datetrack_update_mode => 'CORRECTION'
3258 ,p_assignment_id => l_assignment_id
3259 ,p_object_version_number => l_asg_object_version_number
3260 ,p_grade_id => l_per_assign_record.grade_id
3261 ,p_grade_ladder_pgm_id => l_per_assign_record.grade_ladder_pgm_id
3262 ,p_job_id => l_per_assign_record.job_id
3263 ,p_payroll_id => l_per_assign_record.payroll_id
3264 ,p_location_id => l_per_assign_record.location_id
3265 ,p_organization_id => l_per_assign_record.organization_id
3266 ,p_position_id => l_per_assign_record.position_id
3267 ,p_application_id => l_application_id
3268 ,p_special_ceiling_step_id => l_per_assign_record.special_ceiling_step_id
3269 ,p_recruiter_id => l_per_assign_record.recruiter_id
3270 ,p_recruitment_activity_id => l_per_assign_record.recruitment_activity_id
3271 ,p_vacancy_id => l_per_assign_record.vacancy_id
3272 ,p_pay_basis_id => l_per_assign_record.pay_basis_id
3273 ,p_person_referred_by_id => l_per_assign_record.person_referred_by_id
3274 ,p_supervisor_id => l_per_assign_record.supervisor_id
3275 ,p_supervisor_assignment_id => l_per_assign_record.supervisor_assignment_id
3276 ,p_source_organization_id => l_per_assign_record.source_organization_id
3277 ,p_change_reason => l_per_assign_record.change_reason
3278 -- ,p_assignment_status_type_id => l_per_assign_record.assignment_status_type_id --fix for bug 9714569
3279 ,p_internal_address_line => l_per_assign_record.internal_address_line
3280 ,p_default_code_comb_id => l_per_assign_record.default_code_comb_id
3281 ,p_employment_category => l_per_assign_record.employment_category
3282 ,p_frequency => l_per_assign_record.frequency
3283 ,p_manager_flag => l_per_assign_record.manager_flag
3284 ,p_normal_hours => l_per_assign_record.normal_hours
3285 ,p_perf_review_period => l_per_assign_record.perf_review_period
3286 ,p_perf_review_period_frequency => l_per_assign_record.perf_review_period_frequency
3287 ,p_probation_period => l_per_assign_record.probation_period
3288 ,p_probation_unit => l_per_assign_record.probation_unit
3289 ,p_sal_review_period => l_per_assign_record.sal_review_period
3290 ,p_sal_review_period_frequency => l_per_assign_record.sal_review_period_frequency
3291 ,p_set_of_books_id => l_per_assign_record.set_of_books_id
3292 ,p_title => l_per_assign_record.title
3293 ,p_source_type => l_per_assign_record.source_type
3294 ,p_time_normal_finish => l_per_assign_record.time_normal_finish
3295 ,p_time_normal_start => l_per_assign_record.time_normal_start
3296 ,p_bargaining_unit_code => l_per_assign_record.bargaining_unit_code
3297 ,p_date_probation_end => l_per_assign_record.date_probation_end
3298 ,p_ass_attribute_category => l_per_assign_record.ass_attribute_category
3299 ,p_ass_attribute1 => l_per_assign_record.ass_attribute1
3300 ,p_ass_attribute2 => l_per_assign_record.ass_attribute2
3301 ,p_ass_attribute3 => l_per_assign_record.ass_attribute3
3302 ,p_ass_attribute4 => l_per_assign_record.ass_attribute4
3303 ,p_ass_attribute5 => l_per_assign_record.ass_attribute5
3304 ,p_ass_attribute6 => l_per_assign_record.ass_attribute6
3305 ,p_ass_attribute7 => l_per_assign_record.ass_attribute7
3306 ,p_ass_attribute8 => l_per_assign_record.ass_attribute8
3307 ,p_ass_attribute9 => l_per_assign_record.ass_attribute9
3308 ,p_ass_attribute10 => l_per_assign_record.ass_attribute10
3309 ,p_ass_attribute11 => l_per_assign_record.ass_attribute11
3310 ,p_ass_attribute12 => l_per_assign_record.ass_attribute12
3311 ,p_ass_attribute13 => l_per_assign_record.ass_attribute13
3312 ,p_ass_attribute14 => l_per_assign_record.ass_attribute14
3313 ,p_ass_attribute15 => l_per_assign_record.ass_attribute15
3314 ,p_ass_attribute16 => l_per_assign_record.ass_attribute16
3315 ,p_ass_attribute17 => l_per_assign_record.ass_attribute17
3316 ,p_ass_attribute18 => l_per_assign_record.ass_attribute18
3317 ,p_ass_attribute19 => l_per_assign_record.ass_attribute19
3318 ,p_ass_attribute20 => l_per_assign_record.ass_attribute20
3319 ,p_ass_attribute21 => l_per_assign_record.ass_attribute21
3320 ,p_ass_attribute22 => l_per_assign_record.ass_attribute22
3321 ,p_ass_attribute23 => l_per_assign_record.ass_attribute23
3322 ,p_ass_attribute24 => l_per_assign_record.ass_attribute24
3323 ,p_ass_attribute25 => l_per_assign_record.ass_attribute25
3324 ,p_ass_attribute26 => l_per_assign_record.ass_attribute26
3325 ,p_ass_attribute27 => l_per_assign_record.ass_attribute27
3326 ,p_ass_attribute28 => l_per_assign_record.ass_attribute28
3327 ,p_ass_attribute29 => l_per_assign_record.ass_attribute29
3328 ,p_ass_attribute30 => l_per_assign_record.ass_attribute30
3329 ,p_scl_segment1 => l_scl_rec.segment1
3330 ,p_scl_segment2 => l_scl_rec.segment2
3331 ,p_scl_segment3 => l_scl_rec.segment3
3332 ,p_scl_segment4 => l_scl_rec.segment4
3333 ,p_scl_segment5 => l_scl_rec.segment5
3334 ,p_scl_segment6 => l_scl_rec.segment6
3335 ,p_scl_segment7 => l_scl_rec.segment7
3336 ,p_scl_segment8 => l_scl_rec.segment8
3337 ,p_scl_segment9 => l_scl_rec.segment9
3338 ,p_scl_segment10 => l_scl_rec.segment10
3339 ,p_scl_segment11 => l_scl_rec.segment11
3340 ,p_scl_segment12 => l_scl_rec.segment12
3341 ,p_scl_segment13 => l_scl_rec.segment13
3342 ,p_scl_segment14 => l_scl_rec.segment14
3343 ,p_scl_segment15 => l_scl_rec.segment15
3344 ,p_scl_segment16 => l_scl_rec.segment16
3345 ,p_scl_segment17 => l_scl_rec.segment17
3346 ,p_scl_segment18 => l_scl_rec.segment18
3347 ,p_scl_segment19 => l_scl_rec.segment19
3348 ,p_scl_segment20 => l_scl_rec.segment20
3349 ,p_scl_segment21 => l_scl_rec.segment21
3350 ,p_scl_segment22 => l_scl_rec.segment22
3351 ,p_scl_segment23 => l_scl_rec.segment23
3352 ,p_scl_segment24 => l_scl_rec.segment24
3353 ,p_scl_segment25 => l_scl_rec.segment25
3354 ,p_scl_segment26 => l_scl_rec.segment26
3355 ,p_scl_segment27 => l_scl_rec.segment27
3356 ,p_scl_segment28 => l_scl_rec.segment28
3357 ,p_scl_segment29 => l_scl_rec.segment29
3358 ,p_scl_segment30 => l_scl_rec.segment30
3359 ,p_pgp_segment1 => l_pgp_rec.segment1
3360 ,p_pgp_segment2 => l_pgp_rec.segment2
3361 ,p_pgp_segment3 => l_pgp_rec.segment3
3362 ,p_pgp_segment4 => l_pgp_rec.segment4
3363 ,p_pgp_segment5 => l_pgp_rec.segment5
3364 ,p_pgp_segment6 => l_pgp_rec.segment6
3365 ,p_pgp_segment7 => l_pgp_rec.segment7
3366 ,p_pgp_segment8 => l_pgp_rec.segment8
3367 ,p_pgp_segment9 => l_pgp_rec.segment9
3368 ,p_pgp_segment10 => l_pgp_rec.segment10
3369 ,p_pgp_segment11 => l_pgp_rec.segment11
3370 ,p_pgp_segment12 => l_pgp_rec.segment12
3371 ,p_pgp_segment13 => l_pgp_rec.segment13
3372 ,p_pgp_segment14 => l_pgp_rec.segment14
3373 ,p_pgp_segment15 => l_pgp_rec.segment15
3374 ,p_pgp_segment16 => l_pgp_rec.segment16
3375 ,p_pgp_segment17 => l_pgp_rec.segment17
3376 ,p_pgp_segment18 => l_pgp_rec.segment18
3377 ,p_pgp_segment19 => l_pgp_rec.segment19
3378 ,p_pgp_segment20 => l_pgp_rec.segment20
3379 ,p_pgp_segment21 => l_pgp_rec.segment21
3380 ,p_pgp_segment22 => l_pgp_rec.segment22
3381 ,p_pgp_segment23 => l_pgp_rec.segment23
3382 ,p_pgp_segment24 => l_pgp_rec.segment24
3383 ,p_pgp_segment25 => l_pgp_rec.segment25
3384 ,p_pgp_segment26 => l_pgp_rec.segment26
3385 ,p_pgp_segment27 => l_pgp_rec.segment27
3386 ,p_pgp_segment28 => l_pgp_rec.segment28
3387 ,p_pgp_segment29 => l_pgp_rec.segment29
3388 ,p_pgp_segment30 => l_pgp_rec.segment30
3389 ,p_contract_id => l_per_assign_record.contract_id
3390 ,p_establishment_id => l_per_assign_record.establishment_id
3391 ,p_collective_agreement_id => l_per_assign_record.collective_agreement_id
3392 ,p_cagr_grade_def_id => l_cagr_grade_def_id
3393 ,p_work_at_home => l_per_assign_record.work_at_home
3394 ,p_notice_period => l_per_assign_record.notice_period
3395 ,p_notice_period_uom => l_per_assign_record.notice_period_uom
3396 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
3397 ,p_group_name => l_group_name
3398 ,p_concatenated_segments => l_concatenated_segments
3399 ,p_comment_id => l_comment_id
3400 ,p_people_group_id => l_people_group_id
3401 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
3402 ,p_effective_start_date => l_effective_start_date
3403 ,p_effective_end_date => l_effective_end_date
3404 -- fix for bug 9718515 Starts here.
3405 ,p_job_post_source_name => l_per_assign_record.job_post_source_name
3406 ,p_applicant_rank => l_per_assign_record.applicant_rank
3407 ,p_posting_content_id => l_per_assign_record.posting_content_id
3408 ,p_employee_category => l_per_assign_record.employee_category
3409 -- fix for bug 9718515 Starts here.
3410 );
3411
3412 --fix for bug starts 9714569 here
3413 begin
3414 select ast.per_system_status into l_new_assgn_status
3415 from per_assignment_status_types ast
3416 where ast.assignment_status_type_id = l_per_assign_record.assignment_status_type_id;
3417
3418 exception
3419 when no_data_found then
3420 null;
3421 end;
3422
3423 If l_new_assgn_status = 'OFFER' then
3424 hr_assignment_api.offer_apl_asg
3425 (p_effective_date => l_apl_ass_start_date
3426 ,p_datetrack_update_mode => 'CORRECTION'
3427 ,p_assignment_id => l_assignment_id
3428 ,p_object_version_number => l_asg_object_version_number
3429 ,p_assignment_status_type_id => l_per_assign_record.assignment_status_type_id
3430 ,p_change_reason => l_per_assign_record.change_reason
3431 ,p_effective_start_date => l_effective_start_date
3432 ,p_effective_end_date => l_effective_end_date
3433 );
3434
3435 elsif l_new_assgn_status = 'ACCEPTED' then
3436 hr_assignment_api.accept_apl_asg
3437 (p_effective_date => l_apl_ass_start_date
3438 ,p_datetrack_update_mode => 'CORRECTION'
3439 ,p_assignment_id => l_assignment_id
3440 ,p_object_version_number => l_asg_object_version_number
3441 ,p_assignment_status_type_id => l_per_assign_record.assignment_status_type_id
3442 ,p_change_reason => l_per_assign_record.change_reason
3443 ,p_effective_start_date => l_effective_start_date
3444 ,p_effective_end_date => l_effective_end_date
3445 );
3446
3447 elsif l_new_assgn_status = 'INTERVIEW1' then
3448 hr_assignment_api.interview1_apl_asg
3449 (p_effective_date =>l_apl_ass_start_date
3450 ,p_datetrack_update_mode => 'CORRECTION'
3451 ,p_assignment_id => l_assignment_id
3452 ,p_object_version_number => l_asg_object_version_number
3453 ,p_assignment_status_type_id => l_per_assign_record.assignment_status_type_id
3454 ,p_change_reason => l_per_assign_record.change_reason
3455 ,p_effective_start_date => l_effective_start_date
3456 ,p_effective_end_date => l_effective_end_date
3457 );
3458
3459 elsif l_new_assgn_status = 'INTERVIEW2' then
3460 hr_assignment_api.interview2_apl_asg
3461 (p_effective_date => l_apl_ass_start_date
3462 ,p_datetrack_update_mode => 'CORRECTION'
3463 ,p_assignment_id => l_assignment_id
3464 ,p_object_version_number => l_asg_object_version_number
3465 ,p_assignment_status_type_id => l_per_assign_record.assignment_status_type_id
3466 ,p_change_reason => l_per_assign_record.change_reason
3467 ,p_effective_start_date => l_effective_start_date
3468 ,p_effective_end_date => l_effective_end_date
3469 );
3470
3471 end if;
3472 --fix for bug end 9714569 here
3473
3474
3475 if g_debug then
3476 hr_utility.set_location(l_proc, 16);
3477 end if;
3478
3479 Update_assign_records(l_per_assign_record.assignment_id
3480 ,l_assignment_id
3481 ,l_apl_ass_start_date
3482 ,l_asg_object_version_number
3483 ,l_application_id );
3484
3485 if g_debug then
3486 hr_utility.set_location(l_proc, 17);
3487 end if;
3488
3489 IRC_INTERVIEW_DETAILS_API.copy_interview_details
3490 (p_source_assignment_id => l_per_assign_record.assignment_id
3491 ,p_target_assignment_id => l_assignment_id
3492 ,p_target_party_id => l_t_party_id
3493 );
3494
3495 IRC_COMMUNICATIONS_API.copy_comm_to_apl_asg
3496 (p_target_asg_id => l_assignment_id
3497 ,p_source_asg_id => l_per_assign_record.assignment_id
3498 );
3499
3500 -- Fix for bug 9718515 .
3501
3502 irc_assignment_details_api.copy_assignment_details
3503 (p_source_assignment_id => l_per_assign_record.assignment_id
3504 ,p_target_assignment_id => l_assignment_id
3505 );
3506 --
3507 irc_referral_info_api.copy_referral_details
3508 (p_source_assignment_id => l_per_assign_record.assignment_id
3509 ,p_target_assignment_id => l_assignment_id
3510 );
3511 --
3512 fnd_attached_documents2_pkg.copy_attachments
3513 ( X_from_entity_name =>'PER_ASSIGNMENTS_F'
3514 , X_from_pk1_value => l_per_assign_record.assignment_id
3515 , X_to_entity_name =>'PER_ASSIGNMENTS_F'
3516 , X_to_pk1_value => l_assignment_id
3517 );
3518
3519
3520 END;
3521 END IF;
3522 END LOOP;
3523
3524 if g_debug then
3525 hr_utility.set_location(l_proc, 18);
3526 end if;
3527
3528 DECLARE
3529 l_object_version_number NUMBER;
3530 l_effective_start_date per_all_people_f.effective_start_date%TYPE;
3531 l_effective_end_date per_all_people_f.effective_end_date%TYPE;
3532 l_person_org_manager_warning VARCHAR2(1000);
3533 BEGIN
3534 IF p_term_or_purge_s = 'TERM' THEN
3535 hr_applicant_api.Terminate_applicant(p_validate => false,
3536 p_effective_date => trunc(sysdate) -1,
3537 p_person_id => p_source_person_id,
3538 p_object_version_number => l_object_version_number,
3539 p_effective_start_date => l_effective_start_date,
3540 p_effective_end_date => l_effective_end_date);
3541
3542 if g_debug then
3543 hr_utility.set_location(l_proc, 19);
3544 end if;
3545
3546
3547
3548 ELSIF p_term_or_purge_s = 'PURGE' THEN
3549
3550 hr_person_api.Delete_person(p_validate => false,
3551 p_effective_date => trunc(sysdate),
3552 p_person_id => p_source_person_id,
3553 p_perform_predel_validation => false,
3554 p_person_org_manager_warning => l_person_org_manager_warning);
3555 END IF;
3556 END;
3557
3558 if g_debug then
3559 hr_utility.set_location(l_proc, 20);
3560 end if;
3561
3562
3563 elsif l_exists <> 1 then
3564 hr_utility.set_message(800, 'HR_7092_WFLOW_INV_ACTION');
3565 hr_utility.raise_error;
3566
3567 END IF;
3568 END merge_person;
3569
3570
3571 --
3572 -- ----------------------------------------------------------------------------
3573 -- |---------------------------< merge_party >------------------------------|
3574 -- ----------------------------------------------------------------------------
3575 --
3576 procedure merge_party(p_validate in boolean default false
3577 ,p_target_party_id in number
3578 ,p_source_party_id in number
3579 ,p_term_or_purge_s in varchar2 default null
3580 ,p_create_new_application in varchar2 default 'Y') is
3581
3582 cursor c_get_per_details(c_party_id number) is
3583 select person_id,business_group_id from per_all_people_f
3584 where party_id=c_party_id;
3585
3586 l_t_person_id number;
3587 l_t_bg_id number;
3588 l_proc varchar2(72) := g_package||'merge_party';
3589
3590
3591 begin
3592
3593
3594 if g_debug then
3595 hr_utility.set_location('Entering:'|| l_proc, 5);
3596 hr_utility.set_location('p_target_party_id:'|| p_target_party_id, 5);
3597 hr_utility.set_location('p_source_party_id:'|| p_source_party_id, 5);
3598 end if;
3599
3600 savepoint hr_merge_party;
3601
3602 -- get the person details that belong to source party
3603
3604 for s_per_det in c_get_per_details(p_source_party_id)
3605 loop
3606
3607 l_t_person_id:=null;
3608 l_t_bg_id :=null;
3609
3610 if g_debug then
3611 hr_utility.set_location(l_proc, 6);
3612 end if;
3613
3614 begin
3615 -- check if person exists with target party_id in same bg as of source person.
3616 -- if yes, call merge person procedure to copy applications from source to target person
3617 -- if not, update the party_id of source person with target party_id.
3618
3619 select distinct person_id,business_group_id
3620 into l_t_person_id,l_t_bg_id
3621 from per_all_people_f
3622 where party_id=p_target_party_id
3623 and business_group_id=s_per_det.business_group_id;
3624
3625
3626 merge_person(l_t_person_id,s_per_det.person_id,p_term_or_purge_s,p_create_new_application);
3627
3628
3629 exception
3630 when no_data_found then
3631 null;
3632 end;
3633
3634 end loop;
3635 if g_debug then
3636 hr_utility.set_location(l_proc, 7);
3637 end if;
3638
3639 update per_all_people_f paaf
3640 set party_id=p_target_party_id
3641 where party_id=p_source_party_id
3642 and not exists(select 1 from per_all_people_f
3643 where business_group_id=paaf.business_group_id
3644 and party_id=p_target_party_id);
3645
3646 if p_validate then
3647 raise hr_api.validate_enabled;
3648 end if;
3649
3650 exception
3651 when hr_api.validate_enabled then
3652 --
3653 -- As the Validate_Enabled exception has been raised
3654 -- we must rollback to the savepoint
3655 --
3656 ROLLBACK TO hr_merge_party;
3657 --
3658 when others then
3659 --
3660 -- A validation or unexpected error has occurred
3661 --
3662 --
3663 ROLLBACK TO hr_merge_party;
3664 raise;
3665
3666 END;
3667
3668 end hr_person_api;