[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_API
Source
1 Package Body hr_person_api as
2 /* $Header: peperapi.pkb 120.4.12010000.3 2008/09/08 14:54:17 sidsaxen 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 -- Declare cursors and local variables
1566 --
1567 l_proc varchar2(72) := g_package||'update_us_person';
1568 l_effective_date date;
1569 l_legislation_code per_business_groups.legislation_code%type;
1570 l_discard_varchar2 varchar2(30);
1571 --
1572 cursor check_legislation
1573 (c_person_id per_people_f.person_id%TYPE,
1574 c_effective_date date
1575 )
1576 is
1577 select bgp.legislation_code
1578 from per_people_f per,
1579 -- per_business_groups bgp -- 6131445
1580 -- where per.business_group_id = bgp.business_group_id
1581 per_business_groups_perf bgp
1582 where per.business_group_id+0 = bgp.business_group_id
1583 and per.person_id = c_person_id
1584 and c_effective_date
1585 between per.effective_start_date and per.effective_end_date;
1586 --
1587 begin
1588 if g_debug then
1589 hr_utility.set_location('Entering:'|| l_proc, 5);
1590 end if;
1591 --
1592 -- Initialise local variables
1593 --
1594 l_effective_date := trunc(p_effective_date);
1595 --
1596 -- Validation in addition to Row Handlers
1597 --
1598 -- Check that the person exists.
1599 --
1600 open check_legislation(p_person_id, l_effective_date);
1601 fetch check_legislation into l_legislation_code;
1602 if check_legislation%notfound then
1603 close check_legislation;
1604 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1605 hr_utility.raise_error;
1606 end if;
1607 close check_legislation;
1608 if g_debug then
1609 hr_utility.set_location(l_proc, 20);
1610 end if;
1611 --
1612 -- Check that the legislation of the specified business group is 'US'.
1613 --
1614 if l_legislation_code <> 'US' then
1615 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1616 hr_utility.set_message_token('LEG_CODE','US');
1617 hr_utility.raise_error;
1618 end if;
1619 if g_debug then
1620 hr_utility.set_location(l_proc, 30);
1621 end if;
1622 --
1623 -- Update the person record using the update_person BP
1624 --
1625 hr_person_api.update_person
1626 (p_validate => p_validate
1627 ,p_effective_date => l_effective_date
1628 ,p_datetrack_update_mode => p_datetrack_update_mode
1629 ,p_person_id => p_person_id
1630 ,p_object_version_number => p_object_version_number
1631 ,p_person_type_id => p_person_type_id
1632 ,p_last_name => p_last_name
1633 ,p_applicant_number => p_applicant_number
1634 ,p_comments => p_comments
1635 ,p_date_employee_data_verified => p_date_employee_data_verified
1636 ,p_date_of_birth => p_date_of_birth
1637 ,p_email_address => p_email_address
1638 ,p_employee_number => p_employee_number
1639 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
1640 ,p_first_name => p_first_name
1641 ,p_known_as => p_known_as
1642 ,p_marital_status => p_marital_status
1643 ,p_middle_names => p_middle_names
1644 ,p_nationality => p_nationality
1645 ,p_national_identifier => p_ss_number
1646 ,p_previous_last_name => p_previous_last_name
1647 ,p_registered_disabled_flag => p_registered_disabled_flag
1648 ,p_sex => p_sex
1649 ,p_title => p_title
1650 ,p_vendor_id => p_vendor_id
1651 ,p_work_telephone => p_work_telephone
1652 ,p_attribute_category => p_attribute_category
1653 ,p_attribute1 => p_attribute1
1654 ,p_attribute2 => p_attribute2
1655 ,p_attribute3 => p_attribute3
1656 ,p_attribute4 => p_attribute4
1657 ,p_attribute5 => p_attribute5
1658 ,p_attribute6 => p_attribute6
1659 ,p_attribute7 => p_attribute7
1660 ,p_attribute8 => p_attribute8
1661 ,p_attribute9 => p_attribute9
1662 ,p_attribute10 => p_attribute10
1663 ,p_attribute11 => p_attribute11
1664 ,p_attribute12 => p_attribute12
1665 ,p_attribute13 => p_attribute13
1666 ,p_attribute14 => p_attribute14
1667 ,p_attribute15 => p_attribute15
1668 ,p_attribute16 => p_attribute16
1669 ,p_attribute17 => p_attribute17
1670 ,p_attribute18 => p_attribute18
1671 ,p_attribute19 => p_attribute19
1672 ,p_attribute20 => p_attribute20
1673 ,p_attribute21 => p_attribute21
1674 ,p_attribute22 => p_attribute22
1675 ,p_attribute23 => p_attribute23
1676 ,p_attribute24 => p_attribute24
1677 ,p_attribute25 => p_attribute25
1678 ,p_attribute26 => p_attribute26
1679 ,p_attribute27 => p_attribute27
1680 ,p_attribute28 => p_attribute28
1681 ,p_attribute29 => p_attribute29
1682 ,p_attribute30 => p_attribute30
1683 ,p_per_information_category => 'US'
1684 ,p_per_information1 => p_ethnic_origin
1685 ,p_per_information2 => p_I_9
1686 ,p_per_information3 => p_I_9_expiration_date
1687 -- ,p_per_information4 => p_visa_type
1688 ,p_per_information5 => p_veteran_status
1689 ,p_per_information7 => p_new_hire
1690 ,p_per_information8 => p_exception_reason
1691 ,p_per_information9 => p_child_support_obligation
1692 ,p_per_information10 => p_opted_for_medicare_flag
1693 ,p_date_of_death => p_date_of_death
1694 ,p_background_check_status => p_background_check_status
1695 ,p_background_date_check => p_background_date_check
1696 ,p_blood_type => p_blood_type
1697 ,p_correspondence_language => p_correspondence_language
1698 ,p_fast_path_employee => p_fast_path_employee
1699 ,p_fte_capacity => p_fte_capacity
1700 ,p_hold_applicant_date_until => p_hold_applicant_date_until
1701 ,p_honors => p_honors
1702 ,p_internal_location => p_internal_location
1703 ,p_last_medical_test_by => p_last_medical_test_by
1704 ,p_last_medical_test_date => p_last_medical_test_date
1705 ,p_mailstop => p_mailstop
1706 ,p_office_number => p_office_number
1707 ,p_on_military_service => p_on_military_service
1708 ,p_pre_name_adjunct => p_pre_name_adjunct
1709 ,p_projected_start_date => p_projected_start_date
1710 ,p_rehire_authorizor => p_rehire_authorizor
1711 ,p_rehire_recommendation => p_rehire_recommendation
1712 ,p_resume_exists => p_resume_exists
1713 ,p_resume_last_updated => p_resume_last_updated
1714 ,p_second_passport_exists => p_second_passport_exists
1715 ,p_student_status => p_student_status
1716 ,p_work_schedule => p_work_schedule
1717 ,p_rehire_reason => p_rehire_reason
1718 ,p_suffix => p_suffix
1719 ,p_benefit_group_id => p_benefit_group_id
1720 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1721 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1722 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1723 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
1724 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
1725 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
1726 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
1727 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
1728 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
1729 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1730 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
1731 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1732 ,p_original_date_of_hire => p_original_date_of_hire
1733 ,p_adjusted_svc_date => p_adjusted_svc_date
1734 ,p_town_of_birth => p_town_of_birth
1735 ,p_region_of_birth => p_region_of_birth
1736 ,p_country_of_birth => p_country_of_birth
1737 ,p_global_person_id => p_global_person_id
1738 ,p_party_id => p_party_id
1739 ,p_npw_number => p_npw_number
1740 ,p_effective_start_date => p_effective_start_date
1741 ,p_effective_end_date => p_effective_end_date
1742 ,p_full_name => p_full_name
1743 ,p_comment_id => p_comment_id
1744 ,p_name_combination_warning => p_name_combination_warning
1745 ,p_assign_payroll_warning => p_assign_payroll_warning
1746 ,p_orig_hire_warning => p_orig_hire_warning
1747 );
1748 if g_debug then
1749 hr_utility.set_location('Leaving: '||l_proc, 30);
1750 end if;
1751 --
1752 end update_us_person;
1753 --
1754 -- Fix for 3908271 starts here.
1755 --
1756 -- ----------------------------------------------------------------------------
1757 -- |---------------------------< delete_person >------------------------------|
1758 -- ----------------------------------------------------------------------------
1759 --
1760 procedure delete_person
1761 (p_validate in boolean default false
1762 ,p_effective_date in date
1763 ,p_person_id in number
1764 ,p_perform_predel_validation boolean default false
1765 ,p_person_org_manager_warning out nocopy varchar2) is
1766 --
1767 /*## Cursor to fetch the Release Versions
1768 ## For bug 3945358 */
1769 cursor RelVersion is
1770 select RELEASE_NAME from FND_PRODUCT_groups;
1771 --
1772
1773 --for bug 7369431
1774 cursor chk_person_type is
1775 SELECT typ.system_person_type
1776 FROM per_person_types typ
1777 ,per_person_type_usages_f ptu
1778 WHERE typ.person_type_id = ptu.person_type_id
1779 AND p_effective_date BETWEEN ptu.effective_start_date
1780 AND ptu.effective_end_date
1781 AND ptu.person_id = p_person_id;
1782
1783 l_system_person_type VARCHAR2(2000);
1784 --
1785
1786 l_effective_date date;
1787 l_validate boolean;
1788 l_party_id per_all_people_f.party_id%type;
1789 --
1790 l_proc varchar2(72) := g_package||'delete_person';
1791 --
1792 begin
1793
1794 -- Fetch party Id for the TCA bug 3945358
1795 select party_id into l_party_id from per_all_people_f
1796 where person_id = p_person_id and rownum =1;
1797 --
1798
1799 -- added for bug 7369431
1800 Open chk_person_type;
1801 fetch chk_person_type into l_system_person_type;
1802 close chk_person_type;
1803 --
1804
1805 if g_debug then
1806 hr_utility.set_location('Entering:'|| l_proc, 5);
1807 hr_utility.set_location('p_effective_date:'||
1808 to_char(p_effective_date,'DD/MM/YYYY'), 5);
1809 hr_utility.set_location('p_person_id :'|| p_person_id, 5);
1810 end if;
1811 --
1812 -- Initialise local variables before call to hr_person_bk2.delete_person_b
1813 --
1814 l_effective_date := trunc(p_effective_date);
1815 l_validate := p_validate ;
1816 --
1817 -- Issue a savepoint.
1818 --
1819 savepoint hr_delete_person;
1820 --
1821 begin
1822 --
1823 -- Start of API User Hook for the before hook of update_person
1824 --
1825 hr_person_bk2.delete_person_b
1826 (p_effective_date => l_effective_date
1827 ,p_person_id => p_person_id
1828 );
1829 --
1830 exception
1831 when hr_api.cannot_find_prog_unit then
1832 hr_api.cannot_find_prog_unit_error
1833 (p_module_name => 'DELETE_PERSON'
1834 ,p_hook_type => 'BP'
1835 );
1836 --
1837 -- End of API User Hook for the before hook of delete_person
1838 --
1839 end;
1840 --
1841 if g_debug then
1842 hr_utility.set_location(l_proc, 6);
1843 end if;
1844 --
1845 -- Check whether the person is manager for any organization.
1846 -- If so, set the out warning parameter.
1847 --
1848 hr_person_internal.delete_org_manager(p_person_id => p_person_id
1849 ,p_effective_date => p_effective_date
1850 ,p_person_org_manager_warning => p_person_org_manager_warning);
1851 --
1852 -- Perform minimal Core HR pre-delete validations.
1853 --
1854 -- The following procedure need to be modified to remove the
1855 -- other product validations like BEN.
1856 --
1857 HR_PERSON_INTERNAL.weak_predel_validation
1858 (p_person_id => p_person_id
1859 ,p_effective_date => l_effective_date);
1860 --
1861 if g_debug then
1862 hr_utility.set_location(l_proc, 7);
1863 end if;
1864 --
1865 -- Depending on the parameter p_perform_predel_validation,
1866 -- additional Core HR validations (strong validations).
1867 --
1868 IF p_perform_predel_validation THEN
1869 --
1870 -- The following procedure now will contains only
1871 -- Core HR specific validations as other product validations
1872 -- will be commented out and should be done through hooks.
1873 --
1874 HR_PERSON_INTERNAL.strong_predel_validation
1875 (p_person_id => p_person_id
1876 ,p_effective_date => l_effective_date);
1877 --
1878 END IF;
1879 --
1880 -- Now perform the deletion of the person.
1881 --
1882 HR_PERSON_INTERNAL.delete_person
1883 (p_person_id => p_person_id
1884 ,p_effective_date => l_effective_date);
1885 --
1886 if g_debug then
1887 hr_utility.set_location(l_proc, 8);
1888 end if;
1889 --
1890 begin
1891 --
1892 -- Start of API User Hook for the after hook of delete_person
1893 --
1894 hr_person_bk2.delete_person_a
1895 (p_effective_date => l_effective_date
1896 ,p_person_id => p_person_id
1897 ,p_person_org_manager_warning => p_person_org_manager_warning);
1898 exception
1899 when hr_api.cannot_find_prog_unit then
1900 hr_api.cannot_find_prog_unit_error
1901 (p_module_name => 'DELETE_PERSON'
1902 ,p_hook_type => 'AP'
1903 );
1904 --
1905 -- End of API User Hook for the after hook of delete_person
1906 --
1907 end;
1908 --
1909 --
1910 -- When in validation only mode raise the Validate_Enabled exception
1911 --
1912 if p_validate then
1913 raise hr_api.validate_enabled;
1914 end if;
1915 --
1916
1917 /*----- for TCA person Purge Start for the bug 3945358 -*/
1918 hr_utility.set_location('before calling purge_person ', 90);
1919 begin
1920 for I in RelVersion
1921 loop
1922 if I.RELEASE_NAME not in ('11.5.1', '11.5.2','11.5.3','11.5.4','11.5.5') then
1923 begin
1924 -- for bug 7369431
1925 if l_party_id is not null and nvl(l_system_person_type,'OTHER') not in('OTHER') THEN
1926 per_hrtca_merge.purge_person (p_person_id => p_person_id,p_party_id => l_party_id);
1927 end if;
1928 --
1929 exception
1930 when others then
1931 ROLLBACK TO hr_delete_person;
1932 end;
1933 end if;
1934 end loop;
1935 hr_utility.set_location('After calling purge_person ', 91);
1936 end;
1937 /*----- End for TCA person Purge -*/
1938
1939 if g_debug then
1940 hr_utility.set_location(' Leaving:'||l_proc, 100);
1941 end if;
1942 --
1943 exception
1944 when hr_api.validate_enabled then
1945 --
1946 -- As the Validate_Enabled exception has been raised
1947 -- we must rollback to the savepoint
1948 --
1949 p_person_org_manager_warning := null;
1950 --
1951 ROLLBACK TO hr_delete_person;
1952 --
1953 when others then
1954 --
1955 -- A validation or unexpected error has occurred
1956 --
1957 p_person_org_manager_warning := null;
1958 --
1959 ROLLBACK TO hr_delete_person;
1960 raise;
1961 --
1962 end delete_person;
1963
1964 --
1965 -- Fix for 3908271 ends here.
1966 --
1967 end hr_person_api;