[Home] [Help]
PACKAGE BODY: APPS.PER_HRTCA_MERGE
Source
1 package body per_hrtca_merge as
2 /* $Header: perhrtca.pkb 120.19.12010000.9 2008/11/12 06:36:42 sidsaxen ship $ */
3 --PER_USE_TITLE_IN_FULL_NAME
4 g_package varchar2(30) := 'per_hrtca_merge.';
5 g_count number := 0;
6 --
7 procedure update_person(p_rec in out nocopy per_all_people_f%rowtype) is
8 --
9 l_effective_start_date date;
10 l_effective_end_date date;
11 l_full_name varchar2(240);
12 l_comment_id number;
13 l_name_combination_warning boolean;
14 l_assign_payroll_warning boolean;
15 l_orig_hire_warning boolean;
16 l_proc varchar2(80) := g_package||'update_person';
17 --
18 begin
19 --
20 -- This routine calls the person row handler and sets the party_id
21 -- column to a value that has been passed in.
22 -- This is called during the initial migration of all persons and
23 -- for when a manual party merge takes place.
24 --
25 hr_utility.set_location('Entering '||l_proc,10);
26 --
27 -- Call the hr_person_api.update_person. This causes problems as there are
28 -- rules within the APi that are not enforced properly by the form hence
29 -- many people will experience rows that will not be migrated. We can't
30 -- risk having this as otherwise people will not be able to see their
31 -- qualifications, establishment attendances, etc.
32 --
33 /*
34 hr_person_api.update_person
35 (p_effective_date => p_rec.effective_start_date,
36 p_datetrack_update_mode => 'CORRECTION',
37 p_person_id => p_rec.person_id,
38 p_object_version_number => p_rec.object_version_number,
39 p_employee_number => p_rec.employee_number,
40 p_party_id => p_rec.party_id,
41 p_effective_start_date => l_effective_start_date,
42 p_effective_end_date => l_effective_end_date,
43 p_full_name => l_full_name,
44 p_comment_id => l_comment_id,
45 p_name_combination_warning => l_name_combination_warning,
46 p_assign_payroll_warning => l_assign_payroll_warning,
47 p_orig_hire_warning => l_orig_hire_warning);
48 */
49 update per_all_people_f
50 set party_id = p_rec.party_id
51 where person_id = p_rec.person_id
52 and p_rec.effective_start_date
53 between effective_start_date
54 and effective_end_date;
55 --
56 hr_utility.set_location('Leaving '||l_proc,10);
57 --
58 end update_person;
59 --
60 procedure update_child_tables(p_rec in out nocopy per_all_people_f%rowtype) is
61 --
62 l_proc varchar2(80) := g_package||'update_child_tables';
63 --
64 cursor c_competences is
65 select *
66 from per_competence_elements
67 where person_id = p_rec.person_id;
68 --
69 l_competences c_competences%rowtype;
70 --
71 -- Bug 3648761
72 -- Added Hint to use index for Performance.
73 --
74 cursor c_events is
75 select /*+ INDEX(per_events) */ *
76 from per_events
77 where assignment_id in (select assignment_id
78 from per_all_assignments_f
79 where person_id = p_rec.person_id);
80 --
81 l_events c_events%rowtype;
82 --
83 cursor c_addresses is
84 select *
85 from per_addresses
86 where person_id = p_rec.person_id;
87 --
88 l_addresses c_addresses%rowtype;
89 --
90 cursor c_phones is
91 select *
92 from per_phones
93 where parent_id = p_rec.person_id
94 and parent_table = 'PER_ALL_PEOPLE_F';
95 --
96 l_phones c_phones%rowtype;
97 --
98 cursor c_qualifications is
99 select *
100 from per_qualifications
101 where person_id = p_rec.person_id;
102 --
103 l_qualifications c_qualifications%rowtype;
104 --
105 cursor c_establishment_attendances is
106 select *
107 from per_establishment_attendances
108 where person_id = p_rec.person_id;
109 --
110 cursor c_qualifications_estab is
111 select qua.*
112 from per_qualifications qua,
113 per_establishment_attendances esa
114 where esa.attendance_id = qua.attendance_id
115 and esa.person_id = p_rec.person_id;
116 --
117 l_establishment_attendances c_establishment_attendances%rowtype;
118 --
119 cursor c_prev_employers is
120 select *
121 from per_previous_employers
122 where person_id = p_rec.person_id;
123 --
124 l_prev_employers c_prev_employers%rowtype;
125 --
126 begin
127 --
128 hr_utility.set_location('Entering '||l_proc,10);
129 --
130 -- This package repeatedly calls the child table related API's for a
131 -- particular person and update the party_id column to the party_id
132 -- that has been passed in.
133 --
134 -- 1) Competences
135 -- 2) Events
136 -- 3) Addresses
137 -- 4) Phones
138 -- 5) Qualifications
139 -- 6) Establishment Attendances
140 -- 7) Previous Employers
141 --
142 -- Loop through all of a persons competence records
143 --
144 hr_utility.set_location('FIRST ATTACK : OPEN COMPETENCES',10);
145 --
146 open c_competences;
147 --
148 loop
149 --
150 fetch c_competences into l_competences;
151 exit when c_competences%notfound;
152 --
153 update per_competence_elements
154 set party_id = p_rec.party_id
155 where competence_element_id = l_competences.competence_element_id;
156 --
157 /*
158 hr_competence_element_api.update_competence_element
159 (p_competence_element_id => l_competences.competence_element_id,
160 p_object_version_number => l_competences.object_version_number,
161 p_effective_date => p_rec.effective_start_date,
162 p_party_id => p_rec.party_id);
163 */
164 --
165 end loop;
166 --
167 close c_competences;
168 --
169 hr_utility.set_location('FIRST ATTACK : CLOSE COMPETENCES',10);
170 --
171 -- Loop through all of a persons event records
172 --
173 hr_utility.set_location('FIRST ATTACK : OPEN EVENTS',10);
174 open c_events;
175 --
176 loop
177 --
178 fetch c_events into l_events;
179 exit when c_events%notfound;
180 --
181 update per_events
182 set party_id = p_rec.party_id
183 where event_id = l_events.event_id;
184 /*
185 per_events_api.update_event
186 (p_event_id => l_events.event_id,
187 p_party_id => p_rec.party_id,
188 p_object_version_number => l_events.object_version_number);
189 */
190 --
191 end loop;
192 --
193 close c_events;
194 hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
195 --
196 -- Loop through all of a persons address records
197 --
198 hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
199 open c_addresses;
200 --
201 loop
202 --
203 fetch c_addresses into l_addresses;
204 exit when c_addresses%notfound;
205 --
206 /*
207 hr_person_address_api.update_person_address
208 (p_effective_date => p_rec.effective_start_date,
209 p_address_id => l_addresses.address_id,
210 p_party_id => p_rec.party_id,
211 p_object_version_number => l_addresses.object_version_number);
212 */
213 update per_addresses
214 set party_id = p_rec.party_id
215 where address_id = l_addresses.address_id;
216 --
217 end loop;
218 --
219 close c_addresses;
220 hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
221 --
222 -- Loop through all of a persons phone records
223 --
224 hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
225 open c_phones;
226 --
227 loop
228 --
229 fetch c_phones into l_phones;
230 exit when c_phones%notfound;
231 --
232 /*
233 hr_phone_api.update_phone
234 (p_phone_id => l_phones.phone_id,
235 p_object_version_number => l_phones.object_version_number,
236 p_party_id => p_rec.party_id,
237 p_effective_date => p_rec.effective_start_date);
238 */
239 update per_phones
240 set party_id = p_rec.party_id
241 where phone_id = l_phones.phone_id;
242 --
243 end loop;
244 --
245 close c_phones;
246 hr_utility.set_location('FIRST ATTACK : CLOSE PHONES',10);
247 --
248 -- Loop through all of a persons qualification records
249 --
250 open c_qualifications;
251 --
252 loop
253 --
254 fetch c_qualifications into l_qualifications;
255 exit when c_qualifications%notfound;
256 --
257 -- No API at the moment so use base table.
258 --
259 update per_qualifications
260 set party_id = p_rec.party_id
261 where qualification_id = l_qualifications.qualification_id;
262 --
263 end loop;
264 --
265 close c_qualifications;
266 --
267 open c_qualifications_estab;
268 --
269 loop
270 --
271 fetch c_qualifications_estab into l_qualifications;
272 exit when c_qualifications_estab%notfound;
273 --
274 -- No API at the moment so use base table.
275 --
276 update per_qualifications
277 set party_id = p_rec.party_id
278 where qualification_id = l_qualifications.qualification_id;
279 --
280 end loop;
281 --
282 close c_qualifications_estab;
283 --
284 -- Loop through all of a persons establishment attendance records
285 --
286 open c_establishment_attendances;
287 --
288 loop
289 --
290 fetch c_establishment_attendances into l_establishment_attendances;
291 exit when c_establishment_attendances%notfound;
292 --
293 -- No API at the moment so use base table.
294 --
295 update per_establishment_attendances
296 set party_id = p_rec.party_id
297 where attendance_id = l_establishment_attendances.attendance_id;
298 --
299 end loop;
300 --
301 close c_establishment_attendances;
302 --
303 --
304 -- Loop through all of a persons previous employers records
305 --
306 open c_prev_employers;
307 --
308 loop
309 --
310 fetch c_prev_employers into l_prev_employers;
311 exit when c_prev_employers%notfound; --Bug fix 3618727
312 --
313 --
314 update per_previous_employers
315 set party_id = p_rec.party_id
316 where previous_employer_id = l_prev_employers.previous_employer_id;
317 --
318 end loop;
319 --
320 close c_prev_employers;
321 --
322 hr_utility.set_location('Leaving '||l_proc,10);
323 --
324 end update_child_tables;
325 --
326 function propagate_value
327 (p_old_value in date,
328 p_new_value in date,
329 p_overwrite_data in varchar2) return date is
330 --
331 l_proc varchar2(80) := g_package||'propagate_value';
332 --
333 begin
334 --
335 -- This routine is used as part of the propogation strategy for updates
336 -- to person records. On inserts we do not want to overwrite data with
337 -- null values.
338 --
339 -- If p_overwrite_data = 'Y' then
340 -- return p_new_value
341 -- else
342 -- only overwrite old value if new value is not null
343 -- end if;
344 --
345 hr_utility.set_location('Entering '||l_proc,10);
346 --
347 -- Bug fix 4146782
348 -- If condition added to check whether the value is not
349 -- null. Values are propogated across BG only if it is
350 -- not null
351 --
352 if p_overwrite_data = 'Y' and p_new_value is not null then
353 --
354 return p_new_value;
355 --
356 else
357 --
358 if p_new_value is not null then
359 --
360 return p_new_value;
361 --
362 else
363 --
364 return p_old_value;
365 --
366 end if;
367 --
368 end if;
369 --
370 hr_utility.set_location('Leaving '||l_proc,10);
371 --
372 end propagate_value;
373 --
374 function propagate_value
375 (p_old_value in varchar2,
376 p_new_value in varchar2,
377 p_overwrite_data in varchar2) return varchar2 is
378 --
379 l_proc varchar2(80) := g_package||'propagate_value';
380 --
381 begin
382 --
383 -- This routine is used as part of the propogation strategy for updates
384 -- to person records. On inserts we do not want to overwrite data with
385 -- null values.
386 --
387 -- If p_overwrite_data = 'Y' then
388 -- return p_new_value
389 -- else
390 -- only overwrite old value if new value is not null
391 -- end if;
392 --
393 hr_utility.set_location('Entering '||l_proc,10);
394 --
395 -- Bug fix 4146782
396 -- If condition added to check whether the value is not
397 -- null. Values are propogated across BG only if it is
398 -- not null
399 --
400 if p_overwrite_data = 'Y' and p_new_value is not null then
401 --
402 return p_new_value;
403 --
404 else
405 --
406 if p_new_value is not null then
407 --
408 return p_new_value;
409 --
410 else
411 --
412 return p_old_value;
413 --
414 end if;
415 --
416 end if;
417 --
418 hr_utility.set_location('Leaving '||l_proc,10);
419 --
420 end propagate_value;
421 --
422 function propagate_value
423 (p_old_value in number,
424 p_new_value in number,
425 p_overwrite_data in varchar2) return varchar2 is
426 --
427 l_proc varchar2(80) := g_package||'propagate_value';
428 --
429 begin
430 --
431 -- This routine is used as part of the propogation strategy for updates
432 -- to person records. On inserts we do not want to overwrite data with
433 -- null values.
434 --
435 -- If p_overwrite_data = 'Y' then
436 -- return p_new_value
437 -- else
438 -- only overwrite old value if new value is not null
439 -- end if;
440 --
441 hr_utility.set_location('Entering '||l_proc,10);
442 --
443 -- Bug fix 4146782
444 -- If condition added to check whether the value is not
445 -- null. Values are propogated across BG only if it is
446 -- not null
447 --
448 if p_overwrite_data = 'Y' and p_new_value is not null then
449 --
450 return p_new_value;
451 --
452 else
453 --
454 if p_new_value is not null then
455 --
456 return p_new_value;
457 --
458 else
459 --
460 return p_old_value;
461 --
462 end if;
463 --
464 end if;
465 --
466 hr_utility.set_location('Leaving '||l_proc,10);
467 --
468 end propagate_value;
469 --
470 function get_legislation_code
471 (p_business_group_id in number) return varchar2 is
472 --
473 l_proc varchar2(80) := g_package||'get_legislation_code';
474 l_legislation_code varchar2(80);
475 --
476 begin
477 --
478 -- This procedure returns the legislation code for a particular business
479 -- group.
480 --
481 hr_utility.set_location('Entering '||l_proc,10);
482 --
483 select legislation_code
484 into l_legislation_code
485 from per_business_groups
486 where business_group_id = p_business_group_id;
487 --
488 hr_utility.set_location('Leaving '||l_proc,10);
489 --
490 return l_legislation_code;
491 --
492 end get_legislation_code;
493 --
494 procedure migrate_all_hr_persons(p_number_of_workers in number default 1,
495 p_current_worker in number default 1) is
496 --
497 l_proc varchar2(80) := g_package||'migrate_all_hr_persons';
498 --
499 cursor c_person is
500 select *
501 from per_all_people_f
502 where party_id is null
503 and mod(person_id,p_number_of_workers) = p_current_worker-1
504 and effective_end_date = hr_api.g_eot;
505 --
506 cursor c_old_person(p_person_id number) is
507 select *
508 from per_all_people_f
509 where party_id is null
510 and effective_end_date <> hr_api.g_eot
511 and person_id = p_person_id;
512 --
513 l_person c_person%rowtype;
514 l_old_person c_old_person%rowtype;
515 l_count number := 0;
516 l_data_migrator_mode varchar2(30);
517 --
518 begin
519 --
520 hr_utility.set_location('Entering '||l_proc,10);
521 --
522 -- This routine will create party_records for all person
523 -- records in HRMS. It will then link the created party
524 -- to the child tables of person and these include
525 -- 1) Competences
526 -- 2) Events
527 -- 3) Addresses
528 -- 4) Phones
529 -- 5) Qualifications
530 -- 6) Establishment Attendances
531 --
532 -- Stage 1 - Select person latest records and create TCA
533 -- person records.
534 --
535 l_data_migrator_mode := hr_general.g_data_migrator_mode;
536 hr_general.g_data_migrator_mode := 'Y';
537 --
538 g_count := 100;
539 --
540 open c_person;
541 --
542 loop
543 --
544 fetch c_person into l_person;
545 exit when c_person%notfound;
546 --
547 begin
548 --
549 savepoint last_position;
550 --
551 create_tca_person(p_rec => l_person);
552 --
553 -- Stage 2 - Apply newly created party id to latest
554 -- person record.
555 --
556 update_person(p_rec => l_person);
557 --
558 -- Stage 3 - Take the newly created party_id from the
559 -- person just created and update the old
560 -- person records with that same party_id.
561 --
562 open c_old_person(l_person.person_id);
563 --
564 loop
565 --
566 fetch c_old_person into l_old_person;
567 exit when c_old_person%notfound;
568 --
569 l_old_person.party_id := l_person.party_id;
570 --
571 update_person(p_rec => l_old_person);
572 --
573 end loop;
574 --
575 close c_old_person;
576 --
577 -- Stage 4 - Take the newly created party id from the
578 -- person just created and update the related
579 -- person child information.
580 --
581 update_child_tables(p_rec => l_person);
582 --
583 l_count := l_count + 1;
584 --
585 if mod(l_count,10) = 0 then
586 --
587 -- Commit every ten persons
588 --
589 commit;
590 l_count := 0;
591 --
592 end if;
593 --
594 end;
595 --
596 end loop;
597 --
598 close c_person;
599 --
600 -- Get the last set of records in the chunk.
601 --
602 commit;
603 --
604 g_count := 0;
605 --
606 hr_general.g_data_migrator_mode := l_data_migrator_mode;
607 --
608 hr_utility.set_location('Entering '||l_proc,10);
609 --
610 end;
611 --
612 procedure create_update_contact_point
613 (p_rec in out nocopy per_all_people_f%rowtype) is
614 --
615 l_proc varchar2(80) := g_package||'create_update_contact_point';
616 --
617 -- Cursor to select existing email address from hz_parties
618 CURSOR c1 IS
619 SELECT email_address, last_update_date,contact_point_id,status
620 FROM hz_contact_points hcp
621 WHERE hcp.contact_point_type = 'EMAIL'
622 and hcp.owner_table_name = 'HZ_PARTIES'
623 and hcp.owner_table_id = p_rec.party_id
624 order by last_update_date desc,contact_point_id desc;
625
626
627 --Define local variables
628 -- Modified for bug # 2648797
629 -- The call has been made to TCA v2 file (hz_contact_point_v2pub)
630 -- object version number has been added
631 l_c1 c1%rowtype;
632 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
633 l_email_rec hz_contact_point_v2pub.email_rec_type;
634 l_return_status varchar2(30);
635 l_msg_count number;
636 l_msg_data varchar2(2000);
637 l_contact_point_id number;
638 l_object_version_number number;
639 l_web_rec hz_contact_point_v2pub.web_rec_type;
640 l_edi_rec hz_contact_point_v2pub.edi_rec_type;
641 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
642 l_telex_rec hz_contact_point_v2pub.telex_rec_type;
643 l_init_msg_list varchar2(30);
644 --bug no 5546586 starts here
645 l_email_changed boolean:=false;
646 --bug no 5546586 ends here
647 BEGIN
648 --
649 hr_utility.set_location('Entering '||l_proc,10);
650 --
651 --Added for bug 2648797
652 l_contact_point_rec.created_by_module := 'HR API';
653 --
654 --
655 --
656 --bug no 5546586 starts here
657 if g_old_email_address is null then
658 if p_rec.email_address is not null then
659 l_email_changed:=true;
660 end if;
661 else
662 if p_rec.email_address is null then
663 l_email_changed:=true;
664 elsif g_old_email_address<>p_rec.email_address then
665 l_email_changed:=true;
666 end if;
667 end if;
668 g_old_email_address:=null;
669 --bug no 5546586 ends here
670 OPEN c1;
671 FETCH c1 INTO l_c1;
672 IF c1%found THEN
673 --
674 hr_utility.set_location(l_proc,20);
675 --
676 --Added for bug 2648797
677 --Get the object version number
678 select max(object_version_number)
679 into l_object_version_number
680 from hz_contact_points hcp
681 where hcp.contact_point_id = l_c1.contact_point_id;
682 --
683 --bug no 5546586 starts here
684 if(l_email_changed) then
685 --bug no 5546586 ends here
686 IF (l_c1.email_address is null or l_c1.email_address = 'NULL')
687 and (p_rec.email_address is not null) THEN
688 --
689 hr_utility.set_location('Entering '||l_proc,30);
690 --
691 l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
692 l_email_rec.email_address := p_rec.email_address;
693 --
694 --Modified for Bug 2648797
695 -- api version and p_last_update_date have been commented as TCA V2 does
696 -- not include them
697 --Added for 4697454
698 -- Set Created by Module only when creatinbg
699 -- set to null when updating.
700 l_contact_point_rec.created_by_module := null;
701 hz_contact_point_v2pub.update_contact_point
702 (
703 --p_api_version => 1.0,
704 p_contact_point_rec => l_contact_point_rec,
705 p_email_rec => l_email_rec,
706 -- p_last_update_date => l_c1.last_update_date,
707 x_return_status => l_return_status,
708 x_msg_count => l_msg_count,
709 x_msg_data => l_msg_data,
710 p_web_rec => l_web_rec,
711 p_edi_rec => l_edi_rec,
712 p_phone_rec => l_phone_rec,
713 p_telex_rec => l_telex_rec,
714 p_init_msg_list => l_init_msg_list,
715 p_object_version_number => l_object_version_number
716 );
717 --
718 hr_utility.set_location(l_proc,40);
719 --
720 if l_return_status in ('E','U') then
721 --
722 -- bug 4632157 Starts
723 if l_msg_count > 1 then
724 for i in 1..l_msg_count
725 loop
726 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
727 end loop;
728 end if;
729 -- bug 4632157 ends
730 --
731 hr_utility.set_location(l_msg_data,10);
732 fnd_message.raise_error;
733
734 end if;
735
736
737 ELSIF (l_c1.email_address is not null and p_rec.email_address is not null)
738 and (l_c1.email_address <> p_rec.email_address) THEN
739 --
740 hr_utility.set_location(l_proc,50);
741 --
742 l_contact_point_rec.contact_point_type := 'EMAIL';
743 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
744 l_contact_point_rec.owner_table_id := p_rec.party_id;
745 l_contact_point_rec.status := 'A';
746 l_contact_point_rec.primary_flag := 'Y';
747 l_email_rec.email_address := p_rec.email_address;
748 --
749 --Modified for Bug 2648797
750 -- api version has been commented as TCA V2 does not include it
751 --Added for 4697454
752 -- Set Created by Module only when creatinbg
753 -- set to null when updating.
754 l_contact_point_rec.created_by_module := 'HR API';
755 hz_contact_point_v2pub.create_contact_point
756 (
757 --p_api_version => 1.0,
758 p_contact_point_rec => l_contact_point_rec,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data,
762 p_web_rec => l_web_rec,
763 p_edi_rec => l_edi_rec,
764 p_email_rec => l_email_rec,
765 p_phone_rec => l_phone_rec,
766 p_telex_rec => l_telex_rec,
767 p_init_msg_list => l_init_msg_list,
768 x_contact_point_id => l_contact_point_id
769 );
770 --
771 hr_utility.set_location(l_proc,60);
772 --
773 if l_return_status in ('E','U') then
774 --
775
776 hr_utility.set_location(l_msg_count,10);
777 -- bug 4632157 Starts
778 if l_msg_count > 1 then
779 for i in 1..l_msg_count
780 loop
781 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
782 end loop;
783 end if;
784 -- bug 4632157 ends
785 hr_utility.set_location(l_msg_data,10);
786 fnd_message.raise_error;
787 --
788 end if;
789 --
790
791 hr_utility.set_location(l_proc,70);
792 --
793 l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
794 l_contact_point_rec.status := 'I';
795 l_contact_point_rec.primary_flag := 'N';
796 l_email_rec.email_address := l_c1.email_address;
797 --
798 -- Start of fix for bug 3374509
799 -- Requerying last_update_date from hz_contact_points
800 -- before passing it tto update contact API.
801 -- Due to Validation relaxation by TCA API. All previous records
802 -- are updated by create contact points API.
803 --
804 select last_update_date
805 into l_c1.last_update_date
806 from hz_contact_points
807 where contact_point_id = l_c1.contact_point_id;
808 --Modified for Bug 2648797
809 -- api version and p_last_update_date have been commented as TCA V2 does
810 -- not include them
811 --Added for 4697454
812 -- Set Created by Module only when creatinbg
813 -- set to null when updating.
814 l_contact_point_rec.created_by_module := null;
815 hz_contact_point_v2pub.update_contact_point
816 (
817 --p_api_version => 1.0,
818 p_contact_point_rec => l_contact_point_rec,
819 p_email_rec => l_email_rec,
820 -- p_last_update_date => l_c1.last_update_date,
821 x_return_status => l_return_status,
822 x_msg_count => l_msg_count,
823 x_msg_data => l_msg_data,
824 p_web_rec => l_web_rec,
825 p_edi_rec => l_edi_rec,
826 p_phone_rec => l_phone_rec,
827 p_telex_rec => l_telex_rec,
828 p_init_msg_list => l_init_msg_list,
829 p_object_version_number => l_object_version_number
830 );
831 --
832 hr_utility.set_location(l_proc,80);
833 --
834
835 if l_return_status in ('E','U') then
836 --
837
838 -- bug 4632157 Starts
839 if l_msg_count > 1 then
840 for i in 1..l_msg_count
841 loop
842 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
843 end loop;
844 end if;
845 -- bug 4632157 ends
846 hr_utility.set_location(l_msg_data,10);
847 fnd_message.raise_error;
848 --
849 end if;
850 -- added <> 'NULL' condition below for bug 4694355
851 ELSIF ( l_c1.email_address is not null and l_c1.email_address <> 'NULL' )
852 and (p_rec.email_address is null) then
853 --
854 hr_utility.set_location(l_proc,90);
855 --
856
857 l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
858 l_contact_point_rec.status := 'I';
859 --Modified for Bug 2648797
860 -- api version and p_last_update_date have been commented as TCA V2 does
861 -- not include them
862 --Added for 4697454
863 -- Set Created by Module only when creatinbg
864 -- set to null when updating.
865 l_contact_point_rec.created_by_module := null;
866 hz_contact_point_v2pub.update_contact_point
867 (
868 --p_api_version => 1.0,
869 p_contact_point_rec => l_contact_point_rec,
870 p_email_rec => l_email_rec,
871 -- p_last_update_date => l_c1.last_update_date,
872 x_return_status => l_return_status,
873 x_msg_count => l_msg_count,
874 x_msg_data => l_msg_data,
875 p_web_rec => l_web_rec,
876 p_edi_rec => l_edi_rec,
877 p_phone_rec => l_phone_rec,
878 p_telex_rec => l_telex_rec,
879 p_init_msg_list => l_init_msg_list,
880 p_object_version_number => l_object_version_number
881 );
882 --
883 hr_utility.set_location(l_proc,100);
884 --
885 if l_return_status in ('E','U') then
886 --
887
888 -- bug 4632157 Starts
889 if l_msg_count > 1 then
890 for i in 1..l_msg_count
891 loop
892 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
893 end loop;
894 end if;
895 -- bug 4632157 ends
896 hr_utility.set_location(l_msg_data,10);
897 fnd_message.raise_error;
898 --
899 end if;
900
901 END IF;
902 --bug no 5546586 starts here
903 end if;
904 --bug no 5546586 ends here
905 ELSE
906 --
907 hr_utility.set_location(l_proc,110);
908 --
909 IF (p_rec.email_address is not null) THEN
910 --
911 hr_utility.set_location(l_proc,120);
912 --
913 l_contact_point_rec.contact_point_type := 'EMAIL';
914 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
915 l_contact_point_rec.owner_table_id := p_rec.party_id;
916 l_contact_point_rec.status := 'A';
917 l_contact_point_rec.primary_flag := 'Y';
918 l_email_rec.email_address := p_rec.email_address;
919 --
920 --Modified for Bug 2648797
921 -- api version has been commented as TCA V2 does not include it
922 --Added for 4697454
923 -- Set Created by Module only when creatinbg
924 -- set to null when updating.
925 l_contact_point_rec.created_by_module := 'HR API';
926 --
927 hz_contact_point_v2pub.create_contact_point
928 (
929 --p_api_version => 1.0,
930 p_contact_point_rec => l_contact_point_rec,
931 x_return_status => l_return_status,
932 x_msg_count => l_msg_count,
933 x_msg_data => l_msg_data,
934 p_web_rec => l_web_rec,
935 p_edi_rec => l_edi_rec,
936 p_email_rec => l_email_rec,
937 p_phone_rec => l_phone_rec,
938 p_telex_rec => l_telex_rec,
939 p_init_msg_list => l_init_msg_list,
940 x_contact_point_id => l_contact_point_id
941 );
942 --
943 hr_utility.set_location(l_proc,130);
944 --
945 if l_return_status in ('E','U') then
946 --
947
948 hr_utility.set_location(l_msg_count,10);
949 -- bug 4632157 Starts
950 if l_msg_count > 1 then
951 for i in 1..l_msg_count
952 loop
953 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
954 end loop;
955 end if;
956 -- bug 4632157 ends
957 hr_utility.set_location(l_msg_data,10);
958 fnd_message.raise_error;
959 --
960 end if;
961
962 END IF;
963 END IF;
964
965 --
966 hr_utility.set_location('Leaving '||l_proc,140);
967 --
968
969 end create_update_contact_point;
970 --
971 -- ------------------------------------------------------------------------------
972 -- |--------------------------< get_system_person_type >------------------------|
973 -- ------------------------------------------------------------------------------
974 FUNCTION get_system_person_type
975 (p_effective_date IN DATE
976 ,p_person_id IN NUMBER
977 )
978 RETURN VARCHAR2
979 IS
980 CURSOR csr_person_types
981 (p_effective_date IN DATE
982 ,p_person_id IN NUMBER
983 )
984 IS
985 SELECT typ.system_person_type
986 FROM per_person_types typ
987 ,per_person_type_usages_f ptu
988 WHERE typ.person_type_id = ptu.person_type_id
989 --AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
990 AND p_effective_date BETWEEN ptu.effective_start_date
991 AND ptu.effective_end_date
992 AND ptu.person_id = p_person_id
993 ORDER BY DECODE(typ.system_person_type
994 ,'EMP' ,1
995 ,'CWK' ,2
996 ,'APL' ,3
997 ,'EX_EMP',4
998 ,'EX_CWK',5
999 ,'EX_APL',6
1000 ,7
1001 );
1002 l_system_person_type VARCHAR2(2000);
1003 l_separator varchar2(1) :='_';
1004 l_proc varchar2(80) := g_package||'.get_system_person_type';
1005 BEGIN
1006 --
1007 hr_utility.set_location('Entering '||l_proc,10);
1008 --
1009 FOR l_person_type IN csr_person_types
1010 (p_effective_date => p_effective_date
1011 ,p_person_id => p_person_id
1012 )
1013 LOOP
1014 IF (l_system_person_type IS NULL)
1015 THEN
1016 l_system_person_type := l_person_type.system_person_type;
1017 ELSE
1018 l_system_person_type := l_system_person_type
1019 || l_separator
1020 || l_person_type.system_person_type;
1021 END IF;
1022 END LOOP;
1023 --
1024 hr_utility.set_location('Leaving '||l_proc,10);
1025 --
1026 RETURN l_system_person_type;
1027 --
1028 END get_system_person_type;
1029 --
1030 -- ------------------------------------------------------------------------------
1031 -- |--------------------------< get_tca_merge_actions >-----------------------|
1032 -- ------------------------------------------------------------------------------
1033 --
1034 -- Function returns actions to be performed depending upon the person_id,
1035 -- party_id and the system_person_type, at the end of time.
1036 --
1037 FUNCTION get_tca_merge_actions
1038 (p_person_id in number
1039 ,p_party_id in number
1040 )
1041 RETURN VARCHAR2
1042 is
1043 --
1044 -- Bug fix 3260686. l_sytem_person_type changed to varchar2(2000).
1045 -- l_system_person_type per_person_types.system_person_type%type;
1046 l_system_person_type varchar2(2000);
1047 --
1048 l_effective_date date := hr_api.g_eot;
1049 l_return_value varchar2(30);
1050 l_proc varchar2(80) := g_package||'.get_tca_merge_actions';
1051 --
1052 -- Cursor to check for persons of a valid person_type for given party_id
1053 --
1054 cursor person_type_party_cur
1055 (
1056 p_effective_date in date,
1057 p_party_id in number
1058 )
1059 IS
1060 SELECT typ.system_person_type
1061 FROM per_all_people_f ppf
1062 ,per_person_types typ
1063 ,per_person_type_usages_f ptu
1064 WHERE ppf.party_id = p_party_id
1065 AND ppf.effective_end_date = p_effective_date
1066 AND ppf.person_id = ptu.person_id
1067 AND typ.person_type_id = ptu.person_type_id
1068 --Remove this as we want to view all PTU types.
1069 --AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
1070 AND ptu.effective_end_date = p_effective_date
1071 ORDER BY DECODE(typ.system_person_type
1072 ,'EMP' ,1
1073 ,'CWK' ,2
1074 ,'APL' ,3
1075 ,'EX_EMP',4
1076 ,'EX_CWK',5
1077 ,'EX_APL',6
1078 ,7
1079 );
1080 --
1081 -- Cursor to check for persons of a valid person_type for given person_id
1082 --
1083 cursor person_type_person_cur
1084 (
1085 p_effective_date in date,
1086 p_person_id in number
1087 )
1088 IS
1089 SELECT typ.system_person_type
1090 FROM per_all_people_f ppf
1091 ,per_person_types typ
1092 ,per_person_type_usages_f ptu
1093 WHERE ppf.person_id = p_person_id
1094 AND ppf.effective_end_date = p_effective_date
1095 AND ppf.person_id = ptu.person_id
1096 AND typ.person_type_id = ptu.person_type_id
1097 --Remove this as we want to view all PTU types.
1098 --AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
1099 AND ptu.effective_end_date = p_effective_date
1100 ORDER BY DECODE(typ.system_person_type
1101 ,'EMP' ,1
1102 ,'CWK' ,2
1103 ,'APL' ,3
1104 ,'EX_EMP',4
1105 ,'EX_CWK',5
1106 ,'EX_APL',6
1107 ,7
1108 );
1109
1110 --
1111 Begin
1112 --
1113 hr_utility.set_location('Entering '||l_proc,10);
1114 --
1115 l_system_person_type := per_hrtca_merge.get_system_person_type(
1116 l_effective_date,
1117 p_person_id);
1118
1119 if p_party_id is null then
1120 --
1121 --
1122 -- If the person type is not OTHER and includes CTW then valid for
1123 -- propagation otherwise not a valid case of propagation
1124 --
1125 -- Notes from MB.
1126 -- ==============
1127 -- Remove composite types as they will never occur in the PTU table.
1128 --
1129 -- As this will normally only get called when creating a person or when
1130 -- converting an EX person to a current person we should be able to
1131 -- ignore the EX types.
1132 --
1133 for person_type in person_type_person_cur(l_effective_date,p_person_id)
1134 loop
1135 --
1136 hr_utility.set_location('Person Type '||person_type.system_person_type,20);
1137 --
1138 --
1139 -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
1140 --
1141 if person_type.system_person_type in ('APL','CWK',
1142 'EMP','EX_APL',
1143 'EX_CWK','EX_EMP') then
1144 l_return_value := 'CREATE PARTY';
1145 --
1146 -- since atleast one person has a valid person type,
1147 -- no need to check further
1148 --
1149 exit;
1150 --
1151 -- 4120469 commented this as part of fix of bug retaining party id for
1152 -- Ex-Emp/Ex-Cwk/Ex-Apl and contacts.
1153 -- elsif person_type.system_person_type in ('EX_APL','EX_CWK',
1154 -- 'EX_EMP') then
1155 -- --
1156 -- -- It's valid to have a party but only if not also contact. So
1157 -- -- set return value and contineu through the rest of the records.
1158 -- --
1159 -- l_return_value := 'PARTY VALID';
1160 -- --
1161 else
1162 --
1163 l_return_value := 'AVOID CREATE PARTY';
1164 --
1165 end if;
1166 --
1167 end loop;
1168 --
1169 else
1170 -- We need to check all persons in all business groups to find a valid person
1171 -- with a valid person_type exists.
1172 --
1173 for person_type in person_type_party_cur(l_effective_date,p_party_id) loop
1174 --
1175 hr_utility.set_location('Person Type '||person_type.system_person_type,30);
1176 --
1177 --
1178 -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
1179 --
1180 if person_type.system_person_type in ('APL','CWK',
1181 'EMP','EX_APL',
1182 'EX_CWK','EX_EMP') then
1183 l_return_value := 'PARTY VALID';
1184 --
1185 -- since atleast one person has a valid person type, no need to check further
1186 --
1187 exit;
1188 --
1189 -- 4120469 Commented out the whole for retaining party id for
1190 -- Ex-Emp/Ex-Cwk/Ex-Apl.
1191 -- elsif person_type.system_person_type in ('EX_APL','EX_CWK',
1192 -- 'EX_EMP') then
1193 -- --
1194 -- -- It's valid to have a party but only if not also contact. So
1195 -- -- set return value and contineu through the rest of the records.
1196 -- --
1197 -- l_return_value := 'PARTY VALID';
1198 --
1199 -- --
1200 else
1201 --
1202 l_return_value := 'PARTY INVALID';
1203 --
1204 end if;
1205 --
1206 end loop;
1207 --
1208 end if;
1209 --
1210 -- Start of fix for IRC Bug 3202002
1211 if l_return_value <> 'CREATE PARTY'
1212 and irc_candidate_test.is_person_a_candidate(p_person_id) then
1213 l_return_value := 'CREATE PARTY';
1214 end if;
1215 --
1216 -- End of fix for IRC Bug 3202002
1217 hr_utility.set_location('Leaving '||l_proc,10);
1218 --
1219 return (l_return_value);
1220 --
1221 end get_tca_merge_actions;
1222 --
1223 --
1224 -- ------------------------------------------------------------------------------
1225 -- |--------------------------< clear_party_from_hrms >-----------------------|
1226 -- ------------------------------------------------------------------------------
1227 --
1228 -- Procedure to clear the party_id from all tables in HRMS that reference it.
1229 -- This is performed when a person is no longer eligible for a party,
1230 -- the party may have been purged from TCA so we also want to break the link to
1231 -- the party in HRMS.
1232 --
1233 procedure clear_party_from_hrms
1234 (p_party_id in number) is
1235 --
1236 l_proc varchar2(80) := g_package||'clear_party_from_hrms';
1237
1238 begin
1239
1240 hr_utility.set_location('Entering : '||l_proc,10);
1241 /*
1242 ** Clear party_id information from all records relating to the current
1243 ** party_id across all business groups.
1244 **
1245 ** For performance reasons just use direct SQL and not the APIs
1246 */
1247 begin
1248 update PER_ADDRESSES
1249 set party_id = null
1250 where party_id = p_party_id;
1251 exception
1252 when no_data_found then
1253 null;
1254 when others then
1255 raise;
1256 end;
1257
1258 begin
1259 update PER_COMPETENCE_ELEMENTS
1260 set party_id = null
1261 where party_id = p_party_id;
1262 exception
1263 when no_data_found then
1264 null;
1265 when others then
1266 raise;
1267 end;
1268
1269 begin
1270 update PER_ESTABLISHMENT_ATTENDANCES
1271 set party_id = null
1272 where party_id = p_party_id;
1273 exception
1274 when no_data_found then
1275 null;
1276 when others then
1277 raise;
1278 end;
1279
1280 begin
1281 update PER_EVENTS
1282 set party_id = null
1283 where party_id = p_party_id;
1284 exception
1285 when no_data_found then
1286 null;
1287 when others then
1288 raise;
1289 end;
1290
1291 begin
1292 update PER_PHONES
1293 set party_id = null
1294 where party_id = p_party_id;
1295 exception
1296 when no_data_found then
1297 null;
1298 when others then
1299 raise;
1300 end;
1301
1302 begin
1303 update PER_PREVIOUS_EMPLOYERS
1304 set party_id = null
1305 where party_id = p_party_id;
1306 exception
1307 when no_data_found then
1308 null;
1309 when others then
1310 raise;
1311 end;
1312
1313 begin
1314 update PER_QUALIFICATIONS
1315 set party_id = null
1316 where party_id = p_party_id;
1317 exception
1318 when no_data_found then
1319 null;
1320 when others then
1321 raise;
1322 end;
1323
1324 begin
1325 update PER_ALL_PEOPLE_F
1326 set party_id = null
1327 where party_id = p_party_id;
1328 exception
1329 when no_data_found then
1330 null;
1331 when others then
1332 raise;
1333 end;
1334
1335 hr_utility.set_location('Leaving : '||l_proc, 100);
1336
1337 end clear_party_from_hrms;
1338 --
1339 -- ------------------------------------------------------------------------------
1340 -- |--------------------------< clear_parties_from_hrms >-----------------------|
1341 -- ------------------------------------------------------------------------------
1342 --
1343 procedure clear_parties_from_hrms is
1344 --
1345 l_proc varchar2(80) := g_package||'clear_parties_from_hrms';
1346 l_data_migrator_mode varchar2(30);
1347 --
1348 cursor c_competences is
1349 select pce.party_id
1350 from per_competence_elements pce,
1351 hr_tca_party_unmerge punm
1352 where pce.party_id = punm.party_id
1353 and punm.status = 'PURGE';
1354 --
1355 t_pce_party_id g_party_id_type;
1356 --
1357 cursor c_events is
1358 select eve.party_id
1359 from per_events eve,
1360 hr_tca_party_unmerge punm
1361 where eve.party_id = punm.party_id
1362 and punm.status = 'PURGE';
1363 --
1364 t_eve_party_id g_party_id_type;
1365 --
1366 cursor c_addresses is
1367 select addr.party_id
1368 from per_addresses addr,
1369 hr_tca_party_unmerge punm
1370 where addr.party_id = punm.party_id
1371 and punm.status = 'PURGE';
1372 --
1373 t_add_party_id g_party_id_type;
1374 --
1375 cursor c_phones is
1376 select phn.party_id
1377 from per_phones phn,
1378 hr_tca_party_unmerge punm
1379 where phn.party_id = punm.party_id
1380 and punm.status = 'PURGE';
1381 --
1382 t_phn_party_id g_party_id_type;
1383 --
1384 cursor c_qualifications is
1385 select qua.party_id
1386 from per_qualifications qua,
1387 hr_tca_party_unmerge punm
1388 where qua.party_id = punm.party_id
1389 and punm.status = 'PURGE';
1390 --
1391 t_qua_party_id g_party_id_type;
1392 --
1393 cursor c_establishment_attendances is
1394 select esta.party_id
1395 from per_establishment_attendances esta,
1396 hr_tca_party_unmerge punm
1397 where esta.party_id = punm.party_id
1398 and punm.status = 'PURGE';
1399 --
1400 t_esta_party_id g_party_id_type;
1401 --
1402 cursor c_prev_employers is
1403 select pemp.party_id
1404 from per_previous_employers pemp,
1405 hr_tca_party_unmerge punm
1406 where pemp.party_id = punm.party_id
1407 and punm.status = 'PURGE';
1408 --
1409 t_pemp_party_id g_party_id_type;
1410 --
1411 cursor c_people is
1412 select papf.party_id
1413 from per_all_people_f papf,
1414 hr_tca_party_unmerge punm
1415 where papf.party_id = punm.party_id
1416 and punm.status = 'PURGE';
1417 --
1418 t_papf_party_id g_party_id_type;
1419 --
1420 begin
1421 --
1422 l_data_migrator_mode := hr_general.g_data_migrator_mode;
1423 hr_general.g_data_migrator_mode := 'Y';
1424 --
1425 --
1426 /*
1427 hr_utility.set_location('Entering : '||l_proc,10);
1428 ** Clear party_id information from all records relating to the current
1429 ** party_id across all business groups.
1430 **
1431 ** For performance reasons just use direct SQL and not the APIs
1432 */
1433
1434 hr_utility.set_location('Entering '||l_proc,10);
1435 --
1436 -- This package repeatedly calls the child table related API's for a
1437 -- particular party and updates the party_id column to null for the
1438 -- party_id that has been passed in.
1439 --
1440 -- 1) Competences
1441 -- 2) Events
1442 -- 3) Addresses
1443 -- 4) Phones
1444 -- 5) Qualifications
1445 -- 6) Establishment Attendances
1446 -- 7) Previous Employers
1447 -- 8) Per all people f
1448 --
1449 -- Loop through all of a persons competence records
1450 --
1451 hr_utility.set_location('FIRST ATTACK : OPEN COMPETENCES',10);
1452 --
1453 open c_competences;
1454 --
1455 loop
1456 --
1457 fetch c_competences BULK COLLECT into t_pce_party_id LIMIT 2000;
1458 if t_pce_party_id.count = 0 then
1459 exit;
1460 end if;
1461 --
1462 FORALL i in t_pce_party_id.FIRST..t_pce_party_id.LAST
1463 update per_competence_elements
1464 set party_id = null
1465 where party_id = t_pce_party_id(i);
1466 --
1467 commit;
1468 --fix for 3831453
1469 t_pce_party_id.delete;
1470 --
1471 end loop;
1472 --
1473 close c_competences;
1474 --
1475 -- Loop through all of a persons event records
1476 --
1477 hr_utility.set_location('FIRST ATTACK : OPEN EVENTS',10);
1478 open c_events;
1479 --
1480 loop
1481 --
1482 fetch c_events BULK COLLECT into t_eve_party_id LIMIT 2000;
1483 --
1484 if t_eve_party_id.count = 0 then
1485 exit;
1486 end if;
1487 --
1488 forall i in t_eve_party_id.first..t_eve_party_id.last
1489 update per_events
1490 set party_id = null
1491 where party_id = t_eve_party_id(i);
1492 --
1493 commit;
1494 --fix for 3831453
1495 t_eve_party_id.delete;
1496 --
1497 end loop;
1498 --
1499 close c_events;
1500 --hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
1501 --
1502 -- Loop through all of a persons address records
1503 --
1504 hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
1505 open c_addresses;
1506 --
1507 loop
1508 --
1509 fetch c_addresses BULK COLLECT into t_add_party_id LIMIT 2000;
1510 --
1511 if t_add_party_id.count = 0 then
1512 exit;
1513 end if;
1514 --
1515 FORALL i in t_add_party_id.FIRST..t_add_party_id.LAST
1516 update per_addresses
1517 set party_id = null
1518 where party_id = t_add_party_id(i);
1519 --
1520 commit;
1521 --fix for 3831453
1522 t_add_party_id.delete;
1523 --
1524 end loop;
1525 --
1526 close c_addresses;
1527 --hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
1528 --
1529 -- Loop through all of a persons phone records
1530 --
1531 hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
1532 open c_phones;
1533 --
1534 loop
1535 --
1536 fetch c_phones BULK COLLECT into t_phn_party_id limit 2000;
1537 --
1538 if t_phn_party_id.count = 0 then
1539 exit;
1540 end if;
1541 --
1542 FORALL i in t_phn_party_id.FIRST..t_phn_party_id.LAST
1543 update per_phones
1544 set party_id = null
1545 where party_id = t_phn_party_id(i);
1546 --
1547 commit;
1548 --fix for 3831453
1549 t_phn_party_id.delete;
1550 --
1551 end loop;
1552 --
1553 close c_phones;
1554 --hr_utility.set_location('FIRST ATTACK : CLOSE PHONES',10);
1555 --
1556 -- Loop through all of a persons qualification records
1557 --
1558 open c_qualifications;
1559 --
1560 loop
1561 --
1562 fetch c_qualifications BULK COLLECT into t_qua_party_id limit 2000;
1563 --
1564 if t_qua_party_id.count = 0 then
1565 exit;
1566 end if;
1567 --
1568 forall i in t_qua_party_id.first..t_qua_party_id.last
1569 update per_qualifications
1570 set party_id = null
1571 where party_id = t_qua_party_id(i);
1572 --
1573 commit;
1574 --fix for 3831453
1575 t_qua_party_id.delete;
1576 --
1577 end loop;
1578 --
1579 close c_qualifications;
1580 --
1581 -- Loop through all of a persons establishment attendance records
1582 --
1583 open c_establishment_attendances;
1584 --
1585 loop
1586 --
1587 fetch c_establishment_attendances BULK COLLECT into t_esta_party_id limit 2000;
1588 --
1589 if t_esta_party_id.count = 0 then
1590 exit;
1591 end if;
1592 --
1593 -- No API at the moment so use base table.
1594 --
1595 forall i in t_esta_party_id.first..t_esta_party_id.last
1596 update per_establishment_attendances
1597 set party_id = null
1598 where party_id = t_esta_party_id(i);
1599 --
1600 commit;
1601 --fix for 3831453
1602 t_esta_party_id.delete;
1603 --
1604 end loop;
1605 --
1606 close c_establishment_attendances;
1607 --
1608 --
1609 -- Loop through all of a persons establishment attendance records
1610 --
1611 open c_prev_employers;
1612 --
1613 loop
1614 --
1615 fetch c_prev_employers BULK COLLECT into t_pemp_party_id LIMIT 2000;
1616 --
1617 if t_pemp_party_id.count = 0 then
1618 exit;
1619 end if;
1620 --
1621 -- No API at the moment so use base table.
1622 --
1623 forall i in t_pemp_party_id.first..t_pemp_party_id.last
1624 update per_previous_employers
1625 set party_id = null
1626 where party_id = t_pemp_party_id(i);
1627 --
1628 commit;
1629 --fix for 3831453
1630 t_pemp_party_id.delete;
1631 --
1632 end loop;
1633 --
1634 close c_prev_employers;
1635 --
1636 open c_people;
1637 --
1638 loop
1639 --
1640 fetch c_people BULK COLLECT into t_papf_party_id LIMIT 2000;
1641 --
1642 if t_papf_party_id.count = 0 then
1643 exit;
1644 end if;
1645 --
1646 forall i in t_papf_party_id.first..t_papf_party_id.last
1647 update per_all_people_f
1648 set party_id = null
1649 where party_id = t_papf_party_id(i);
1650 --
1651 commit;
1652 --fix for 3831453
1653 t_papf_party_id.delete;
1654 --
1655 end loop;
1656 --
1657 close c_people;
1658 --
1659 hr_utility.set_location('Leaving '||l_proc,10);
1660 --
1661 --
1662 hr_general.g_data_migrator_mode := l_data_migrator_mode;
1663 --
1664 --
1665 end clear_parties_from_hrms;
1666 --
1667 -- ------------------------------------------------------------------------------
1668 -- |----------------------------< set_party_in_hrms >-------------------------|
1669 -- ------------------------------------------------------------------------------
1670 --
1671 -- Procedure to set the party_id in all tables in HRMS that reference it.
1672 -- This is performed when a person with no party becomes eligible for a
1673 -- party. e.g. a contact becomes an employee.
1674 --
1675 procedure set_party_in_hrms
1676 (p_person_id in number
1677 ,p_party_id in number) is
1678 --
1679 l_proc varchar2(80) := g_package||'set_party_in_hrms';
1680 -- Bug fix 3632535 starts here
1681
1682 type assignmentid is table of per_all_assignments_f.assignment_id%type index by binary_integer;
1683 l_assignment_id assignmentid;
1684
1685 -- Bug fix 3632535 ends here
1686
1687 begin
1688
1689 hr_utility.set_location('Entering : '||l_proc,10);
1690 /*
1691 ** Clear party_id information from all records relating to the current
1692 ** party_id across all business groups.
1693 **
1694 ** For performance reasons just use direct SQL and not the APIs
1695 */
1696 begin
1697 update PER_ADDRESSES
1698 set party_id = p_party_id
1699 where person_id = p_person_id;
1700 exception
1701 when no_data_found then
1702 null;
1703 when others then
1704 raise;
1705 end;
1706
1707 begin
1708 update PER_COMPETENCE_ELEMENTS
1709 set party_id = p_party_id
1710 where person_id = p_person_id;
1711 exception
1712 when no_data_found then
1713 null;
1714 when others then
1715 raise;
1716 end;
1717
1718 begin
1719 update PER_ESTABLISHMENT_ATTENDANCES
1720 set party_id = p_party_id
1721 where person_id = p_person_id;
1722 exception
1723 when no_data_found then
1724 null;
1725 when others then
1726 raise;
1727 end;
1728
1729 begin
1730 -- bug fix 3632535 starts here
1731 -- update statement split into two to
1732 -- improve performance.
1733
1734 select assignment_id
1735 bulk collect into l_assignment_id
1736 from per_all_assignments_f
1737 where person_id = p_person_id;
1738
1739 forall i in 1..l_assignment_id.count
1740 update PER_EVENTS
1741 set party_id = p_party_id
1742 where assignment_id = l_assignment_id(i);
1743
1744 -- bug fix 3632535 ends here.
1745
1746 /*update PER_EVENTS
1747 set party_id = p_party_id
1748 where assignment_id in (select assignment_id
1749 from per_all_assignments_f
1750 where person_id = p_person_id);*/
1751 exception
1752 when no_data_found then
1753 null;
1754 when others then
1755 raise;
1756 end;
1757
1758 begin
1759 update PER_PHONES
1760 set party_id = p_party_id
1761 -- There is no person_id column in per_phones table
1762 -- where person_id = p_person_id;
1763 where parent_id = p_person_id
1764 and parent_table = 'PER_ALL_PEOPLE_F';
1765 exception
1766 when no_data_found then
1767 null;
1768 when others then
1769 raise;
1770 end;
1771
1772 begin
1773 update PER_PREVIOUS_EMPLOYERS
1774 set party_id = p_party_id
1775 where person_id = p_person_id;
1776 exception
1777 when no_data_found then
1778 null;
1779 when others then
1780 raise;
1781 end;
1782
1783 begin
1784 update PER_QUALIFICATIONS
1785 set party_id = p_party_id
1786 where person_id = p_person_id;
1787 exception
1788 when no_data_found then
1789 null;
1790 when others then
1791 raise;
1792 end;
1793
1794 begin
1795 update PER_ALL_PEOPLE_F
1796 set party_id = p_party_id
1797 where person_id = p_person_id;
1798 exception
1799 when no_data_found then
1800 null;
1801 when others then
1802 raise;
1803 end;
1804
1805 hr_utility.set_location('Leaving : '||l_proc, 100);
1806
1807 end set_party_in_hrms;
1808
1809 --
1810 -- ------------------------------------------------------------------------------
1811 -- |------------------------< clear_purge_parties_temp >----------------------|
1812 -- ------------------------------------------------------------------------------
1813 --
1814 -- Procedure to clear the data from the temp table..
1815 --
1816 procedure clear_purge_parties_temp
1817 is
1818 l_proc varchar2(100) := g_package||'.clear_purge_parties_temp';
1819 begin
1820 hr_utility.set_location('Entering :'||l_proc,10);
1821 delete from hr_purge_parties_gt;
1822 hr_utility.set_location('Leaving :'||l_proc,20);
1823 end;
1824
1825 --
1826 -- ------------------------------------------------------------------------------
1827 -- |---------------------------< add_party_for_purge >------------------------|
1828 -- ------------------------------------------------------------------------------
1829 --
1830 -- Procedure to add a party to the list of parties which are candidates
1831 -- for purging. The list of party_id's to be processed is held in a
1832 -- global temporary table.
1833 --
1834 procedure add_party_for_purge(p_party_id number)
1835 is
1836 l_proc varchar2(100) := g_package||'add_party_for_purge';
1837 begin
1838 hr_utility.set_location('Entering : '||l_proc,10);
1839
1840 begin
1841 insert into hr_purge_parties_gt (party_id) values (p_party_id);
1842 exception
1843 when others then
1844 raise;
1845 end;
1846
1847 hr_utility.set_location('Leaving : '||l_proc,20);
1848 end add_party_for_purge;
1849
1850 --
1851 -- ------------------------------------------------------------------------------
1852 -- |------------------------------< purge_parties >---------------------------|
1853 -- ------------------------------------------------------------------------------
1854 --
1855 -- Procedure to interface with TCA party purge process. This sets up a purge
1856 -- batch for the parties contained with the temporary table and then calls
1857 -- the TCA party purge process.
1858 --
1859 -- This routine will operate differently depending on the maintenance pack
1860 -- level. If the databse is at 11.5.6 or later then the full party purge
1861 -- process will be executed. If it is at an earlier level then a cut
1862 -- down purge will be implemented which simply removes the party without any
1863 -- FK validation.
1864 --
1865 procedure purge_parties
1866 is
1867 l_proc varchar2(100) := g_package||'purge_parties';
1868 l_parent_entity_name hz_merge_dictionary.parent_entity_name%type;
1869 l_errbuf varchar2(1000);
1870 l_retcode varchar2(250);
1871 l_batchid number;
1872 l_subset_sql varchar2(1000) := 'party_id in (select party_id
1873 from hr_purge_parties_gt)';
1874
1875 cursor csr_get_purge_parties is
1876 select party_id
1877 from hr_purge_parties_gt;
1878
1879 l_return_status varchar2(100);
1880 l_msg_count number;
1881 l_msg_data varchar2(1000);
1882 begin
1883
1884 hr_utility.set_location('Entering : '||l_proc,10);
1885
1886 /* Insert the details into HZ_PURGE_BATCHES.
1887 */
1888 hr_utility.set_location(l_proc,30);
1889 select hz_purge_batches_s.nextval
1890 into l_batchid
1891 from sys.dual;
1892
1893 hr_utility.set_location(l_proc,40);
1894 insert into hz_purge_batches (batch_id,
1895 batch_name,
1896 subset_sql,
1897 creation_date,
1898 created_by,
1899 last_update_date,
1900 last_updated_by)
1901 values (l_batchid,
1902 'HR Contact Purge - '||l_batchid,
1903 l_subset_sql,
1904 sysdate,
1905 fnd_global.login_id,
1906 sysdate,
1907 fnd_global.login_id);
1908
1909 /* Process the batch and determine the candidate parties for the purge..
1910 */
1911 hr_utility.set_location(l_proc,50);
1912 hz_purge.identify_purge_parties(l_errbuf, l_retcode,
1913 to_char(l_batchid), 'N');
1914
1915 if l_retcode = 2 then
1916 /*
1917 ** The identify got an error, details of which are in l_errbuf.
1918 ** Raise an error and report the details we've got.
1919 */
1920
1921 fnd_message.set_name('PER','PER_289974_TCA_PERSON');
1922 fnd_message.set_token('PROCEDURE','per_hrtca_merge.purge_parties');
1923 fnd_message.set_token('STEP','20');
1924 fnd_message.set_token('ERROR', l_errbuf);
1925 fnd_message.raise_error;
1926 else
1927 /* The identify candidates was successful so now actually perform
1928 ** the purge of those parties.
1929 */
1930 l_errbuf := null;
1931 l_retcode := null;
1932 hr_utility.set_location(l_proc,60);
1933 hz_purge.purge_parties(l_errbuf, l_retcode, to_char(l_batchid), 'N');
1934
1935 if l_retcode = 2 then
1936 /*
1937 ** The purge got an error, details of which are in l_errbuf.
1938 ** Raise an error and report the details we've got.
1939 */
1940 fnd_message.set_name('PER','PER_289974_TCA_PERSON');
1941 fnd_message.set_token('PROCEDURE','per_hrtca_merge.purge_parties');
1942 fnd_message.set_token('STEP','30');
1943 fnd_message.set_token('ERROR', l_errbuf);
1944 fnd_message.raise_error;
1945 end if;
1946 end if;
1947
1948 /* Clear the temp table.
1949 */
1950 clear_purge_parties_temp;
1951
1952 hr_utility.set_location('Leaving : '||l_proc,100);
1953 end purge_parties;
1954 --
1955 --
1956 -- ------------------------------------------------------------------------------
1957 -- |------------------------------< partyCleanup >----------------------------|
1958 -- ------------------------------------------------------------------------------
1959 --
1960 -- procedure to process a range of person_ids and purge party data when
1961 -- required.
1962 --
1963 -- This is a re-written procedure to use bulk collect.
1964
1965 procedure partyCleanup(
1966 p_process_ctrl IN varchar2,
1967 p_start_rowid IN rowid,
1968 p_end_rowid IN rowid,
1969 p_rows_processed OUT nocopy number) is
1970
1971
1972 --TYPE g_party_id_type IS TABLE OF NUMBER(15);
1973 TYPE l_person_id_type IS TABLE OF NUMBER(15) index by binary_integer;
1974 t_party_id g_party_id_type;
1975 t_person_id l_person_id_type;
1976 t_party_id_to_purge g_party_id_type;
1977 t_party_id_notto_purge g_party_id_type;
1978
1979 cursor csr_get_party_ids is
1980 select distinct p.person_id, p.party_id
1981 from per_all_people_f p
1982 where p.rowid
1983 between p_start_rowid and p_end_rowid
1984 and p.party_id is not null
1985 and not exists (select null
1986 from hr_tca_party_unmerge ptyun
1987 where ptyun.party_id = p.party_id);
1988 --
1989 l_effective_date date := hr_api.g_eot;
1990 l_return_value varchar2(30);
1991 --
1992 -- Cursor to check for persons of a valid person_type for given party_id
1993 --
1994 cursor person_type_party_cur
1995 (
1996 p_effective_date in date,
1997 p_party_id in number
1998 )
1999 IS
2000 SELECT typ.system_person_type
2001 FROM per_all_people_f ppf
2002 ,per_person_types typ
2003 ,per_person_type_usages_f ptu
2004 WHERE ppf.party_id = p_party_id
2005 AND ppf.effective_end_date = p_effective_date
2006 AND ppf.person_id = ptu.person_id
2007 AND typ.person_type_id = ptu.person_type_id
2008 --Remove this as we want to view all PTU types.
2009 --AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
2010 AND ptu.effective_end_date = p_effective_date
2011 ORDER BY DECODE(typ.system_person_type
2012 ,'EMP' ,1
2013 ,'CWK' ,2
2014 ,'APL' ,3
2015 ,'EX_EMP',4
2016 ,'EX_CWK',5
2017 ,'EX_APL',6
2018 ,7
2019 );
2020 --
2021 l_have_rows_topurge number :=0;
2022 --
2023 begin
2024
2025 p_rows_processed := 0;
2026
2027 -- bulk collect
2028 open csr_get_party_ids;
2029 LOOP
2030
2031 --
2032 fetch csr_get_party_ids BULK COLLECT INTO t_person_id, t_party_id LIMIT 2000;
2033 --close csr_get_party_ids;
2034
2035 -- if no rows fetched exit out of proc
2036 if t_person_id.COUNT = 0 THEN
2037 EXIT;
2038 end if;
2039
2040 -- loop through each person_id/party
2041 for i in t_person_id.FIRST..t_person_id.LAST loop
2042 --
2043 -- End of code shifted from a procedure
2044 --
2045 -- We need to check all persons in all business groups to find a valid person
2046 -- with a valid person_type exists.
2047 --
2048 for person_type in person_type_party_cur(l_effective_date,t_party_id(i)) loop
2049 --
2050 --
2051 -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
2052 --
2053 if person_type.system_person_type in ('APL','CWK',
2054 'EMP','EX_APL',
2055 'EX_CWK','EX_EMP') then
2056 l_return_value := 'PARTY VALID';
2057 --
2058 -- since atleast one person has a valid person type, no need to check further
2059 --
2060 exit;
2061 --
2062 -- 4120469 Commented out the whole for retaining party id for
2063 -- Ex-Emp/Ex-Cwk/Ex-Apl.
2064 -- elsif person_type.system_person_type in ('EX_APL','EX_CWK',
2065 -- 'EX_EMP') then
2066 -- --
2067 -- -- It's valid to have a party but only if not also contact. So
2068 -- -- set return value and contineu through the rest of the records.
2069 -- --
2070 -- l_return_value := 'PARTY VALID';
2071 -- --
2072 else
2073 --
2074 l_return_value := 'PARTY INVALID';
2075 --
2076 end if;
2077 --
2078 end loop;
2079 --
2080 -- bug fix 4075396
2081 -- Condition added to check whether the person is an irec candidate.
2082
2083 if l_return_value = 'PARTY INVALID' and
2084 NOT irc_candidate_test.is_person_a_candidate(t_person_id(i)) THEN
2085 /*
2086 ** The party is eligible for purging so add
2087 ** to the cache set of parties to be purged.
2088 */
2089 t_party_id_to_purge(t_party_id_to_purge.COUNT + 1) := t_party_id(i);
2090
2091 else
2092 t_party_id_notto_purge(t_party_id_notto_purge.COUNT + 1) := t_party_id(i);
2093 end if;
2094 --
2095 -- End of code shifted from a procedure
2096 --
2097 end loop;
2098 --
2099 -- bulk insert into hr_tca_party_unmerge added new
2100 --
2101 IF t_party_id_notto_purge.COUNT > 0 THEN
2102 FORALL i IN t_party_id_notto_purge.FIRST..t_party_id_notto_purge.LAST
2103 INSERT INTO hr_tca_party_unmerge (party_id,status) VALUES (t_party_id_notto_purge(i),'NOPURGE');
2104 --
2105 t_party_id_notto_purge.delete;
2106 --
2107 END IF;
2108
2109 -- do we have any parties to purge?
2110 IF t_party_id_to_purge.COUNT > 0 THEN
2111 -- bulk insert into hr_tca_party_unmerge
2112 FORALL i IN t_party_id_to_purge.FIRST..t_party_id_to_purge.LAST
2113 INSERT INTO hr_tca_party_unmerge (party_id,status) VALUES (t_party_id_to_purge(i),'PURGE');
2114 --
2115 t_party_id_to_purge.delete;
2116 --
2117 /*
2118 ** This call is being called after the loop, since the parties to be purged
2119 ** are stored in a table, and to avoid no_data_found exception in clear_parties proc.
2120 */
2121 --clear_parties_from_hrms(p_party_id_to_purge => t_party_id_to_purge);
2122 END IF;
2123
2124 p_rows_processed := t_party_id_to_purge.COUNT+t_party_id_notto_purge.COUNT;
2125 l_have_rows_topurge := l_have_rows_topurge+t_party_id_to_purge.COUNT;
2126 --
2127 commit;
2128 -- Bug 3619347
2129 t_person_id.delete;
2130 t_party_id.delete;
2131 EXIT WHEN csr_get_party_ids%NOTFOUND;
2132 --
2133 END LOOP;
2134 --
2135 close csr_get_party_ids;
2136 --
2137 end partyCleanup;
2138 --
2139 --
2140 -- ------------------------------------------------------------------------------
2141 -- |------------------------< partycleanup_full_conc >--------------------------|
2142 -- ------------------------------------------------------------------------------
2143 --
2144 procedure partycleanup_full_conc(errbuf out NOCOPY varchar2,
2145 retcode out NOCOPY varchar2) is
2146
2147 l_have_rows_topurge number := 0;
2148 --
2149 cursor chk_rows_exist is
2150 select count(party_id)
2151 from hr_tca_party_unmerge
2152 where status = 'PURGE';
2153 --
2154 l_start_rowid rowid;
2155 l_end_rowid rowid;
2156 l_rows_processed number;
2157 --
2158 cursor get_rowid_range is
2159 select min(rowid),
2160 max(rowid)
2161 from per_all_people_f;
2162 --
2163 l_release_name fnd_product_groups.release_name%type;
2164 --
2165 cursor csr_get_release_name is
2166 select release_name
2167 from fnd_product_groups;
2168
2169 l_data_migrator_mode varchar2(30);
2170 --
2171 begin
2172 --
2173 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2174 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
2175 --
2176 open csr_get_release_name;
2177 fetch csr_get_release_name into l_release_name;
2178 --
2179 if csr_get_release_name%found and
2180 l_release_name not in ('11.5.1','11.5.2','11.5.3','11.5.4',
2181 '11.5.5') then
2182 -- and (nvl(fnd_profile.value('HR:TCA_UNMERGE_PROCESS_OPTION'),'I') ='D'
2183 -- or nvl(fnd_profile.value('HR:TCA_UNMERGE_PROCESS_OPTION'),'I') ='P') then
2184 --
2185 open get_rowid_range;
2186 fetch get_rowid_range into l_start_rowid,l_end_rowid;
2187 close get_rowid_range;
2188 --
2189 if l_start_rowid is not null then
2190 per_hrtca_merge.partycleanup(
2191 p_process_ctrl => null,
2192 p_start_rowid => l_start_rowid,
2193 p_end_rowid => l_end_rowid,
2194 p_rows_processed => l_rows_processed
2195 );
2196 end if;
2197 --
2198
2199 --
2200 open chk_rows_exist;
2201 fetch chk_rows_exist into l_have_rows_topurge;
2202 close chk_rows_exist;
2203 --
2204 if l_have_rows_topurge > 0 then
2205 --
2206 --
2207 l_data_migrator_mode := hr_general.g_data_migrator_mode;
2208 hr_general.g_data_migrator_mode := 'Y';
2209 --
2210 clear_parties_from_hrms();
2211 --
2212 hr_general.g_data_migrator_mode := l_data_migrator_mode;
2213 --
2214 begin
2215 insert into hr_purge_parties_gt (PARTY_ID)
2216 select distinct party_id
2217 from hr_tca_party_unmerge
2218 where status = 'PURGE';
2219 end;
2220
2221 /*
2222 ** Process the set of parties which require removing from an HRMS
2223 ** perspective. This routine actually calls the HZ purge process.
2224 */
2225 -- Log message
2226 FND_FILE.put_line(fnd_file.log,'Begin TCA process to identify/purge in HR tables');
2227 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Begin HRMS process to identify/purge in HR tables');
2228 --
2229 --
2230 purge_parties();
2231 --
2232 commit;
2233 -- Log message
2234 FND_FILE.put_line(fnd_file.log,'End TCA process to identify/purge in HR tables');
2235 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'End TCA process to identify/purge in HR tables');
2236 --
2237 begin
2238 update hr_tca_party_unmerge
2239 set status = 'PURGE COMPLETED'
2240 where status = 'PURGE';
2241 end;
2242 --
2243 commit;
2244 end if; -- have rows to purge
2245 --
2246 --
2247 end if; --Version Control
2248 close csr_get_release_name;
2249 --
2250 retcode := '0'; -- (successful completion)
2251 errbuf := ' ';
2252 exception
2253 when others then
2254 retcode := '2'; -- (error)
2255 errbuf := sqlerrm;
2256
2257 end partycleanup_full_conc;
2258 --
2259 -- ------------------------------------------------------------------------------
2260 -- |------------------------< partycleanup_tca_conc >--------------------------|
2261 -- ------------------------------------------------------------------------------
2262 --
2263 procedure partycleanup_tca_conc(errbuf out NOCOPY varchar2,
2264 retcode out NOCOPY varchar2) is
2265 -- procedure partycleanup_tca_conc is
2266 l_have_rows_topurge number :=0;
2267 --
2268 cursor chk_rows_exist is
2269 select count(party_id)
2270 from hr_tca_party_unmerge
2271 where status = 'PURGE';
2272 --
2273 l_data_migrator_mode varchar2(30);
2274 --
2275 begin
2276 --
2277 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2278 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
2279 --
2280 open chk_rows_exist;
2281 fetch chk_rows_exist into l_have_rows_topurge;
2282 close chk_rows_exist;
2283 --
2284 if l_have_rows_topurge > 0 then
2285 --
2286 --
2287 l_data_migrator_mode := hr_general.g_data_migrator_mode;
2288 hr_general.g_data_migrator_mode := 'Y';
2289 --
2290 clear_parties_from_hrms();
2291 --
2292 --
2293 hr_general.g_data_migrator_mode := l_data_migrator_mode;
2294 --
2295 begin
2296 insert into hr_purge_parties_gt (PARTY_ID)
2297 select distinct party_id
2298 from hr_tca_party_unmerge
2299 where status = 'PURGE';
2300 end;
2301
2302 /*
2303 ** Process the set of parties which require removing from an HRMS
2304 ** perspective. This routine actually calls the HZ purge process.
2305 */
2306 -- Log message
2307 FND_FILE.put_line(fnd_file.log,'Begin TCA process to identify/purge in HR tables');
2308 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Begin HRMS process to identify/purge in HR tables');
2309 --
2310 --
2311 purge_parties();
2312 --
2313 commit;
2314 -- Log message
2315 FND_FILE.put_line(fnd_file.log,'End TCA process to identify/purge in HR tables');
2316 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'End TCA process to identify/purge in HR tables');
2317 --
2318 begin
2319 update hr_tca_party_unmerge
2320 set status = 'PURGE COMPLETED'
2321 where status = 'PURGE';
2322 end;
2323 --
2324 commit;
2325 end if; -- have rows to purge
2326 --
2327 retcode := '0'; -- (successful completion)
2328 errbuf := ' ';
2329 --
2330 exception
2331 when others then
2332 retcode := '2'; -- (error)
2333 errbuf := sqlerrm;
2334
2335 end partycleanup_tca_conc;
2336 --
2337 --
2338 -- ------------------------------------------------------------------------------
2339 -- |------------------------< CHK_GUPUD_RUN_BOOLEAN >--------------------------|
2340 -- ------------------------------------------------------------------------------
2341 --
2342 -- this returns string 'TRUE' or 'FALSE' for running the TCA unmerge processes
2343 --
2344 procedure CHK_GUPUD_RUN_BOOLEAN(retstring out NOCOPY varchar2) is
2345 l_retstring varchar2(10) := 'TRUE';
2346 begin
2347 -- For now, this procedure always returns TRUE.
2348 -- need to add appropriate code later
2349 --
2350 retstring := l_retstring;
2351 end;
2352 --
2353 -- ------------------------------------------------------------------------------
2354 -- |----------------------------< get_column_length >---------------------------|
2355 -- ------------------------------------------------------------------------------
2356 -- Added to fix 4201545
2357 -- modified for bug 6931585
2358 function get_column_length(tab_name in varchar2
2359 ,col_name in varchar2
2360 ,fndApplicationIdIn in integer default 800) return number is
2361 --
2362 cursor col_len_cur is
2363 select width
2364 from fnd_columns
2365 where column_name = col_name
2366 and application_id = fndApplicationIdIn
2367 and table_id = (select table_id
2368 from fnd_tables
2369 where table_name = tab_name
2370 and application_id = fndApplicationIdIn); --modified for bug 6931585
2371 l_col_length number;
2372 begin
2373 open col_len_cur;
2374 fetch col_len_cur into l_col_length;
2375 if col_len_cur%found then
2376 return l_col_length;
2377 else
2378 return null;
2379 end if;
2380 close col_len_cur;
2381 --
2382 end get_column_length;
2383 --
2384 -- =============================================================================
2385 -- ------------------------------------------------------------------------------
2386 -- |----------------------------< create_tca_person >--------------------------|
2387 -- ------------------------------------------------------------------------------
2388 --
2389 procedure create_tca_person
2390 (p_rec in out nocopy per_all_people_f%rowtype) is
2391 --
2392 l_proc varchar2(80) := g_package||'create_tca_person';
2393 l_return_status varchar2(30);
2394 l_msg_count number;
2395 l_msg_data varchar2(2000);
2396 l_party_id number;
2397 l_party_number varchar2(2000);
2398 l_profile_id number;
2399 l_leg_code varchar2(80);
2400 l_person_rec hz_party_v2pub.person_rec_type; --Modified for Bug #2648797
2401 l_party_rec hz_party_v2pub.party_rec_type; --Modified for Bug #2648797
2402 --
2403 l_place_of_birth hz_person_profiles.place_of_birth%type;
2404 l_place_of_birth_len pls_integer := 0;
2405 --
2406 l_prev_last_name hz_person_profiles.person_previous_last_name%type;
2407 l_prev_last_name_len pls_integer := 0;
2408 --
2409 l_tab_exists varchar2(1);
2410 --
2411 -- where clause of the cursor is changed to add two other conditions
2412 -- view_application_id = 222, is for the product AR. and
2413 -- looking for valid is not sufficient, but rather we need to check
2414 -- the valid dates.
2415 --
2416 cursor c1(p_lookup_type varchar2, p_lookup_code varchar2) is
2417 select null
2418 from fnd_lookup_values
2419 where lookup_type = p_lookup_type
2420 and lookup_code = p_lookup_code
2421 and enabled_flag = 'Y'
2422 and view_application_id = 222
2423 and trunc(sysdate) between nvl(start_date_active,sysdate)
2424 and nvl(end_date_active,sysdate)
2425 and SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID)
2426 and language = userenv('LANG');
2427 --
2428 l_dummy varchar2(1);
2429 l_tca_merge_action varchar2(30);
2430 l_release_name fnd_product_groups.release_name%type;
2431 --
2432 cursor csr_get_release_name is
2433 select release_name
2434 from fnd_product_groups;
2435 --
2436 begin
2437 --
2438 -- This routine will create a person in TCA. It calls the TCA API passing
2439 -- in the correct column values based on the legislation of the HRMS person.
2440 --
2441 hr_utility.set_location('Entering '||l_proc,10);
2442 --
2443 -- Begin New code added for PARTY UNMERGE
2444 --
2445 -- function get_tca_merge_actions returns four possible values
2446 --
2447 -- CREATE PARTY
2448 -- AVOID CREATE PARTY
2449 -- PARTY INVALID
2450 -- PARTY VALID
2451 --
2452 -- if the function returns either 'CREATE PARTY' or 'PARTY VALID' regular
2453 -- create_tca_person needs to be processed.
2454 --
2455 -- if the function returns AVOID CREATE PARTY create_tca_person will
2456 -- be aborted. This person is not eligible to be merged into TCA.
2457 --
2458 -- if the function returns PARTY INVALID
2459 -- CLEAR_PARTY_FROM_HRMS procedure is called to clear the party from HRMS.
2460 -- ADD_PARTY_FOR_PURGE procedure is called to purge the party from TCA.
2461 -- party_id will be forced to null fro the existing record.
2462 --
2463 -- if the function returns PARTY_VALID
2464 -- Regular processing continues normally
2465 --
2466 hr_utility.set_location('Party unmerge '||l_proc,10);
2467 --
2468 --Added for bug 2648797
2469 l_person_rec.created_by_module := 'HR API';
2470 --
2471 l_tca_merge_action := per_hrtca_merge.get_tca_merge_actions
2472 (p_person_id => p_rec.person_id
2473 ,p_party_id => p_rec.party_id
2474 );
2475 --
2476 hr_utility.set_location('ACTION = :'||l_tca_merge_action,11);
2477 --
2478 if l_tca_merge_action = 'AVOID CREATE PARTY' then
2479 --
2480 -- This person is not eligible to be migrated. Do nothing and return
2481 --
2482 return;
2483 --
2484 elsif l_tca_merge_action = 'PARTY INVALID' then
2485
2486 --
2487 -- We don't need this party any longer. Remove the party_id
2488 -- from HRMS tables.
2489 --
2490 per_hrtca_merge.clear_party_from_hrms
2491 (p_party_id => p_rec.party_id);
2492 --
2493 -- Bug fix 4227635 starts here--
2494 -- Updated orig_system_reference to party id when the
2495 -- link is broken between the TCA and HR.
2496
2497 update hz_parties
2498 set orig_system_reference = p_rec.party_id
2499 where party_id = p_rec.party_id
2500 and substr(orig_system_reference,1,4) = 'PER:';
2501
2502 -- bug fix 4227635 ends here --
2503 --
2504 -- Bug fix 4005740 starts here--
2505
2506 -- Call to purge_parties is commented to improve performance.
2507 -- Party id is inserted into table HR_TCA_PARTY_UNMERGE
2508 -- so that party id will be purged when the user run the
2509 -- party unmerge program next time.
2510
2511 INSERT INTO hr_tca_party_unmerge (party_id,status)
2512 VALUES (p_rec.party_id,'PURGE');
2513 /*
2514 --
2515 -- If we are at the appropropriate maintenance pack(11.5.6 or later)
2516 -- then purge the party from TCA.
2517 --
2518 open csr_get_release_name;
2519 fetch csr_get_release_name into l_release_name;
2520
2521 if csr_get_release_name%found and
2522 l_release_name not in ('11.5.1','11.5.2','11.5.3','11.5.4',
2523 '11.5.5') then
2524 hr_utility.set_location(l_proc,12);
2525 --
2526 -- We've found the release details and are at 11.5.6 or above
2527 -- we have all the TCA infrastructure therefore we can continue
2528 -- with the purge.
2529 --
2530 -- Make calls to party purge for this party_id.
2531 --
2532 --
2533 per_hrtca_merge.add_party_for_purge
2534 (p_party_id => p_rec.party_id
2535 );
2536 --
2537 per_hrtca_merge.purge_parties;
2538 --
2539 end if;*/
2540 --
2541 -- bug fix 4005740 ends here --
2542 p_rec.party_id := null;
2543 return;
2544 --
2545 end if;
2546 hr_utility.set_location('END Party unmerge '||l_proc,20);
2547 --
2548 --
2549 -- End New code added for PARTY UNMERGE
2550 --
2551 --
2552 -- Check if party_id is already passed in, in this case we
2553 -- do not create the person but instead update the person.
2554 --
2555 if p_rec.party_id is not null then
2556 --
2557 update_tca_person(p_rec => p_rec,
2558 p_overwrite_data => 'N');
2559 retUrn;
2560 --
2561 end if;
2562 --
2563 -- Get the persons legislation code for the business group
2564 --
2565 l_leg_code :=
2566 get_legislation_code(p_business_group_id => p_rec.business_group_id);
2567 --
2568 -- START WWBUG 2735866
2569 -- Get the length of person_previous_last_name and assign it to
2570 -- per_hrtca_merge.g_prev_last_name, if not assigned already
2571 --
2572 -- Modiifed to fix 4201545 starts here
2573 hr_utility.set_location('Before prev_last_name loop ',99);
2574 per_hrtca_merge.g_prev_last_name_len := get_column_length('HZ_PERSON_PROFILES','PERSON_PREVIOUS_LAST_NAME',222); --for bug 6331673
2575 --
2576 if per_hrtca_merge.g_prev_last_name_len is null then
2577 begin
2578 --
2579 hr_utility.set_location('Before prev_last_name loop ',99);
2580 loop
2581 l_prev_last_name := l_prev_last_name||'x';
2582 l_prev_last_name_len := l_prev_last_name_len + 1;
2583 end loop;
2584 --
2585 exception
2586 --
2587 when others then
2588 -- error caused by overflow
2589 -- clear the l_prev_last_name to save memory
2590 l_prev_last_name := NULL;
2591 -- the l_prev_last_name_len var will contain the length
2592 -- assign this to the pkg.global variable.
2593 per_hrtca_merge.g_prev_last_name_len := l_prev_last_name_len;
2594 --
2595 end;
2596 end if;
2597 -- Modiifed to fix 4201545 ends here
2598 l_prev_last_name_len := per_hrtca_merge.g_prev_last_name_len;
2599 hr_utility.set_location('Before prev_last_name '||l_prev_last_name_len,99);
2600 -- End WWBUG 2735866
2601 --
2602 -- Assign variables to TCA structure based on legislation
2603 --
2604 -- WWBUG 2098068
2605 if l_leg_code = 'JP' then
2606 --
2607 l_person_rec.person_last_name := p_rec.per_information18;
2608 l_person_rec.person_first_name := p_rec.per_information19;
2609 l_person_rec.person_name_phonetic := p_rec.full_name;
2610 l_person_rec.person_first_name_phonetic := p_rec.first_name;
2611 l_person_rec.person_last_name_phonetic := p_rec.last_name;
2612 --
2613 elsif l_leg_code = 'KR' then
2614 --
2615 l_person_rec.person_first_name := p_rec.first_name;
2616 l_person_rec.person_last_name := p_rec.last_name;
2617 l_person_rec.person_last_name_phonetic := p_rec.per_information1;
2618 l_person_rec.person_first_name_phonetic := p_rec.per_information2;
2619 --
2620 else
2621 --
2622 l_person_rec.person_first_name := p_rec.first_name;
2623 l_person_rec.person_last_name := p_rec.last_name;
2624 --
2625 end if;
2626 --
2627 -- First Name is mandatory so pass some asterisks if first name is null
2628 -- HZ comically removed and added this rule between HZ B and F hence we
2629 -- leave this logic in.
2630 -- Commented for Bug #2738916
2631 -- The TCA code can now accept first name as null. Hence the passing of asterisks
2632 -- is not required.
2633
2634 /* if l_person_rec.person_first_name is null then
2635 --
2636 l_person_rec.person_first_name := '***********';
2637 --
2638 end if;*/
2639 --
2640 -- Assign all other variables
2641 --
2642 l_person_rec.person_middle_name := p_rec.middle_names;
2643 l_person_rec.person_name_suffix := p_rec.suffix;
2644 --l_person_rec.previous_last_name := substr(p_rec.previous_last_name,1,40);
2645 -- This column length is changed to 150 in one of the latest HZ FP.
2646 -- to make it work on both old (40) and new (150) we need substr equal
2647 -- to the length of the column in the database, which is stored in
2648 -- l_prev_last_name_len -- WWBUG 2735866
2649 l_person_rec.person_previous_last_name := substr(p_rec.previous_last_name,
2650 1,l_prev_last_name_len);
2651 l_person_rec.known_as := p_rec.known_as;
2652 l_person_rec.person_identifier := p_rec.person_id;
2653 --
2654 -- WWBUG 2689895
2655 -- Mask data if HZ profile set.
2656 --
2657 if nvl(fnd_profile.value('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'Y' then
2658 --
2659 l_person_rec.date_of_birth := p_rec.date_of_birth;
2660 --
2661 if p_rec.sex is null then
2662 l_person_rec.gender := 'UNSPECIFIED';
2663 elsif p_rec.sex = 'F' then
2664 l_person_rec.gender := 'FEMALE';
2665 elsif p_rec.sex = 'M' then
2666 l_person_rec.gender := 'MALE';
2667 end if;
2668 --
2669 -- Modiifed to fix 4201545 starts here
2670 l_place_of_birth_len := get_column_length('HZ_PERSON_PROFILES','PLACE_OF_BIRTH',222); --for bug 6331673
2671 --
2672 if l_place_of_birth_len is null then
2673 begin
2674 --
2675 loop
2676 l_place_of_birth := l_place_of_birth||'x';
2677 l_place_of_birth_len := l_place_of_birth_len + 1;
2678 end loop;
2679 --
2680 exception
2681 --
2682 when others then
2683 -- error caused by overflow
2684 -- clear the l_place_of_birth to save memory
2685 l_place_of_birth := NULL;
2686 -- the l_place_of_birth_len var will contain the length
2687 end;
2688 --
2689 end if;
2690 --
2691 -- Modiifed to fix 4201545 ends here
2692 l_person_rec.place_of_birth := substr(p_rec.town_of_birth,1,l_place_of_birth_len);
2693 --
2694 -- Ensure that all the variables we map to TCA are valid.
2695 --
2696 if p_rec.marital_status is not null then
2697 --
2698 open c1('MARITAL_STATUS',p_rec.marital_status);
2699 --
2700 fetch c1 into l_dummy;
2701 if c1%notfound then
2702 --
2703 l_person_rec.marital_status := null;
2704 --
2705 else
2706 --
2707 l_person_rec.marital_status := p_rec.marital_status;
2708 --
2709 end if;
2710 --
2711 close c1;
2712 --
2713 end if;
2714 --
2715 else
2716 --
2717 l_person_rec.marital_status := null;
2718 l_person_rec.date_of_birth := null;
2719 l_person_rec.place_of_birth := null;
2720 l_person_rec.gender := null;
2721 --
2722 end if;
2723 --
2724 l_person_rec.date_of_death := p_rec.date_of_death;
2725 l_person_rec.party_rec.orig_system_reference := 'PER:'||p_rec.person_id;
2726 --
2727 if p_rec.title is not null then
2728 --
2729 open c1('CONTACT_TITLE',p_rec.title);
2730 --
2731 fetch c1 into l_dummy;
2732 if c1%notfound then
2733 --
2734 l_person_rec.person_pre_name_adjunct := null;
2735 --
2736 else
2737 --
2738 l_person_rec.person_pre_name_adjunct := p_rec.title;
2739 --
2740 end if;
2741 --
2742 close c1;
2743 --
2744 end if;
2745 --
2746 -- Call TCA API and create the person.
2747 --
2748 -- Bug 4149356 Start of Fix
2749 -- Write only the first 80 characters of first_name to the trace file
2750 --
2751 hr_utility.set_location(substr(l_person_rec.person_first_name,1,70),10);
2752 --
2753 -- Bug 4149356 End of Fix
2754 --Modified Created by Module Code to 'HR API'
2755 fnd_profile.put('HZ_CREATED_BY_MODULE','HR API');
2756 --
2757 -- declare added by risgupta for bug 4375792
2758 declare
2759 l_hzprofile_value varchar2(20);
2760 l_hzprofile_changed varchar2(1) := 'N';
2761 begin
2762 --Modified for Bug 2648797
2763 -- api version,p_commit and p_validation_level have been commented as TCA V2 does
2764 -- not include them
2765 -- START risgupta bug 4375792
2766 l_hzprofile_value := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
2767 if nvl(l_hzprofile_value, 'Y') = 'N' then
2768 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
2769 l_hzprofile_changed := 'Y';
2770 end if;
2771 -- END risgupta bug 4375792
2772
2773 hz_party_v2pub.create_person
2774 (
2775 --p_api_version => 1.0,
2776 p_init_msg_list => 'F',
2777 --p_commit => 'F',
2778 p_person_rec => l_person_rec,
2779 x_return_status => l_return_status,
2780 x_msg_count => l_msg_count,
2781 x_msg_data => l_msg_data,
2782 x_party_id => l_party_id,
2783 x_party_number => l_party_number,
2784 x_profile_id => l_profile_id
2785 -- p_validation_level => 100
2786 );
2787 -- START risgupta bug 4375792
2788 if nvl(l_hzprofile_changed,'N') = 'Y' then
2789 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
2790 l_hzprofile_changed := 'N';
2791 end if;
2792 -- END risgupta bug 4375792
2793 --
2794 if l_return_status in ('E','U') then
2795 --
2796 -- bug 4632157 Starts
2797 if l_msg_count > 1 then
2798 for i in 1..l_msg_count
2799 loop
2800 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2801 end loop;
2802 end if;
2803 -- bug 4632157 ends
2804 fnd_message.raise_error;
2805 --
2806 end if;
2807 exception
2808 when others then
2809 -- START risgupta bug 4375792
2810 if nvl(l_hzprofile_changed,'N') = 'Y' then
2811 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
2812 l_hzprofile_changed := 'N';
2813 end if;
2814 -- END risgupta bug 4375792
2815 --
2816 fnd_message.set_name('PER', 'PER_289974_TCA_PERSON');
2817 fnd_message.set_token('PROCEDURE','per_hrtca_merge.create_tca_person');
2818 fnd_message.set_token('STEP','5');
2819 fnd_message.set_token('ERROR', sqlerrm);
2820 fnd_message.raise_error;
2821 --
2822 end;
2823 --
2824 -- Assign party_id back to party_id for per_all_people_f row
2825 --
2826 hr_utility.set_location('Party ID = '||l_party_id,10);
2827 --
2828 p_rec.party_id := l_party_id;
2829 --
2830 -- We've now got a party_id, this may be for a new person but
2831 -- it might be for an existing person. We therefore need to
2832 -- ripple the party_id down to all the tables in HR which hold
2833 -- it for this person.
2834 --
2835 per_hrtca_merge.set_party_in_hrms(
2836 p_party_id => p_rec.party_id,
2837 p_person_id => p_rec.person_id);
2838 --
2839 if p_rec.email_address is not null then
2840 --
2841 create_update_contact_point(p_rec => p_rec);
2842 --
2843 end if;
2844 --
2845 hr_utility.set_location('Leaving '||l_proc,10);
2846 --
2847 end;
2848 --
2849 --
2850 -- ------------------------------------------------------------------------------
2851 -- |------------------------------< upate_tca_person >--------------------------|
2852 -- ------------------------------------------------------------------------------
2853 --
2854 procedure update_tca_person
2855 (p_rec in out nocopy per_all_people_f%rowtype,
2856 p_overwrite_data in varchar2 default 'Y') is
2857 --
2858 l_proc varchar2(80) := g_package||'.update_tca_person';
2859 l_return_status varchar2(30);
2860 l_msg_count number;
2861 l_msg_data varchar2(2000);
2862 l_leg_code varchar2(80);
2863 l_profile_id number;
2864 l_party_object_version_number number;
2865 l_party_last_update_date date;
2866 l_person_rec hz_party_v2pub.person_rec_type; --Modified for bug# 2648797
2867 l_party_rec hz_party_v2pub.party_rec_type; --Modified for bug# 2648797
2868 l_dummy number;
2869 l_tca_merge_action varchar2(30);
2870 --
2871 l_place_of_birth hz_person_profiles.place_of_birth%type;
2872 l_place_of_birth_len pls_integer := 0;
2873 --
2874 l_prev_last_name hz_person_profiles.person_previous_last_name%type;
2875 l_prev_last_name_len pls_integer := 0;
2876 --
2877 l_tab_exists varchar2(1);
2878 --
2879 -- Cursor is modified to reflect the ar_lookups so that the lookup value
2880 -- validation does not fail in TCA.
2881 -- added view_application_id = 222 , and sysddate between active dates
2882 -- and security_group clauses, as the ar_lookups is based on this.
2883 --
2884 cursor c1(p_lookup_type varchar2, p_lookup_code varchar2) is
2885 select 1
2886 from fnd_lookup_values
2887 where lookup_type = p_lookup_type
2888 and lookup_code = p_lookup_code
2889 and enabled_flag = 'Y'
2890 and view_application_id = 222
2891 and trunc(sysdate) between nvl(start_date_active,sysdate)
2892 and nvl(end_date_active,sysdate)
2893 and SECURITY_GROUP_ID =
2894 fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID)
2895 and language = userenv('LANG');
2896 --
2897 begin
2898 --
2899 hr_utility.set_location('Entering '||l_proc,10);
2900 --
2901 --
2902 -- Begin New code added for PARTY UNMERGE
2903 --
2904 -- since party unmerge, it is possible to have null party_id for a person
2905 -- and no point in processing this code if party_id does not exist for
2906 -- this person, as this person will not have been created in tca.
2907 -- stop processing where party_id is null.
2908 --
2909 if p_rec.party_id is null then
2910 --
2911 return;
2912 --
2913 end if;
2914 --
2915
2916 if p_rec.effective_end_date <> hr_api.g_eot then
2917 --
2918 -- Record being updated isn't for the end of time so call replicate
2919 -- routine and then return to the calling procedure.
2920 --
2921 g_count := g_count + 1;
2922 --
2923 if g_count = 1 then
2924 --
2925 replicate_person_across_bg(p_rec => p_rec,
2926 p_overwrite_data => p_overwrite_data);
2927 g_count := 0;
2928 --
2929 end if;
2930 --
2931 return;
2932 --
2933 end if;
2934 --
2935 -- Get the persons legislation code for the business group
2936 --
2937 l_leg_code :=
2938 get_legislation_code(p_business_group_id => p_rec.business_group_id);
2939 --
2940 -- START WWBUG 2735866
2941 -- Get the length of person_previous_last_name and assign it to
2942 -- per_hrtca_merge.g_prev_last_name, if not assigned already
2943 --
2944 -- Modiifed to fix 4201545 starts here
2945 hr_utility.set_location('Before prev_last_name loop ',99);
2946 per_hrtca_merge.g_prev_last_name_len := get_column_length('HZ_PERSON_PROFILES','PERSON_PREVIOUS_LAST_NAME',222); --for bug 6331673
2947 --
2948 if per_hrtca_merge.g_prev_last_name_len is null then
2949 begin
2950 --
2951 hr_utility.set_location('Before prev_last_name loop ',99);
2952 loop
2953 l_prev_last_name := l_prev_last_name||'x';
2954 l_prev_last_name_len := l_prev_last_name_len + 1;
2955 end loop;
2956 --
2957 exception
2958 --
2959 when others then
2960 -- error caused by overflow
2961 -- clear the l_prev_last_name to save memory
2962 l_prev_last_name := NULL;
2963 -- the l_prev_last_name_len var will contain the length
2964 -- assign this to the pkg.global variable.
2965 per_hrtca_merge.g_prev_last_name_len := l_prev_last_name_len;
2966 --
2967 end;
2968 --
2969 end if;
2970 -- Modiifed to fix 4201545 ends here
2971 l_prev_last_name_len := per_hrtca_merge.g_prev_last_name_len;
2972 hr_utility.set_location('Before prev_last_name '||l_prev_last_name_len,99);
2973 -- End WWBUG 2735866
2974 --
2975 -- Assign variables to TCA structure based on legislation
2976 --
2977 -- WWBUG 2096068
2978 if l_leg_code = 'JP' then
2979 --
2980 l_person_rec.person_last_name := p_rec.per_information18;
2981 l_person_rec.person_first_name := p_rec.per_information19;
2982 l_person_rec.person_name_phonetic := p_rec.full_name;
2983 l_person_rec.person_first_name_phonetic := p_rec.first_name;
2984 l_person_rec.person_last_name_phonetic := p_rec.last_name;
2985 --
2986 elsif l_leg_code = 'KR' then
2987 --
2988 l_person_rec.person_first_name := p_rec.first_name;
2989 l_person_rec.person_last_name := p_rec.last_name;
2990 l_person_rec.person_last_name_phonetic := p_rec.per_information1;
2991 l_person_rec.person_first_name_phonetic := p_rec.per_information2;
2992 --
2993 else
2994 --
2995 l_person_rec.person_first_name := p_rec.first_name;
2996 l_person_rec.person_last_name := p_rec.last_name;
2997 --
2998 end if;
2999 --
3000 -- First Name is mandatory so pass some asterisks if first name is null
3001 -- HZ removed and added this rule between HZ B and F.
3002 --
3003 -- Commented for Bug #2738916
3004 -- The TCA code can now accept first name as null. Hence the passing of asterisks
3005 -- is not required.
3006
3007 /* if l_person_rec.person_first_name is null then
3008 --
3009 l_person_rec.person_first_name := '***********';
3010 --
3011 end if; */
3012 --
3013 -- Assign all other variables
3014 --
3015 hr_utility.set_location('UPDATE_TCA_PERSON Before middle Name ###'||p_rec.middle_names||'***',99);
3016 l_person_rec.person_middle_name := nvl(p_rec.middle_names,FND_API.G_MISS_CHAR); -- for bug 6609549.
3017 l_person_rec.person_name_suffix := nvl(p_rec.suffix,FND_API.G_MISS_CHAR);--fix for bug7411512.
3018 --l_person_rec.previous_last_name := substr(p_rec.previous_last_name,1,40);
3019 -- This column length is changed to 150 in one of the latest HZ FP.
3020 -- to make it work on both old (40) and new (150) we need substr equal
3021 -- to the length of the column in the database, which is stored in
3022 -- l_prev_last_name_len -- WWBUG 2735866
3023 l_person_rec.person_previous_last_name := substr(p_rec.previous_last_name,
3024 1,l_prev_last_name_len);
3025 l_person_rec.known_as := p_rec.known_as;
3026 l_person_rec.person_identifier := p_rec.person_id;
3027 --
3028 -- WWBUG 2689895
3029 -- Mask data if HZ profile set.
3030 --
3031 if nvl(fnd_profile.value('HZ_PROTECT_HR_PERSON_INFO'),'-1') <> 'Y' then
3032 --
3033 if p_rec.sex is null then
3034 l_person_rec.gender := 'UNSPECIFIED';
3035 elsif p_rec.sex = 'F' then
3036 l_person_rec.gender := 'FEMALE';
3037 elsif p_rec.sex = 'M' then
3038 l_person_rec.gender := 'MALE';
3039 else
3040 l_person_rec.gender := p_rec.sex;
3041 end if;
3042 --
3043 l_person_rec.date_of_birth := p_rec.date_of_birth;
3044 --
3045 -- Modified to fix 4201545 starts here
3046 l_place_of_birth_len := get_column_length('HZ_PERSON_PROFILES','PLACE_OF_BIRTH',222); --for bug 6331673
3047 --
3048 if l_place_of_birth_len is null then
3049 begin
3050 --
3051 loop
3052 --
3053 l_place_of_birth := l_place_of_birth||'x';
3054 l_place_of_birth_len := l_place_of_birth_len + 1;
3055 --
3056 end loop;
3057 --
3058 exception
3059 --
3060 when others then
3061 -- error caused by overflow
3062 -- clear the l_place_of_birth to save memory
3063 l_place_of_birth := NULL;
3064 -- the l_place_of_birth_len var will contain the length
3065 end;
3066 --
3067 end if;
3068 --
3069 -- Modified to fix 4201545 ends here
3070 l_person_rec.place_of_birth := substr(p_rec.town_of_birth,1,l_place_of_birth_len);
3071 --
3072 -- Ensure that all the variables we map to TCA are valid.
3073 --
3074 if p_rec.marital_status is not null then
3075 --
3076 open c1('MARITAL_STATUS',p_rec.marital_status);
3077 --
3078 fetch c1 into l_dummy;
3079 if c1%notfound then
3080 --
3081 l_person_rec.marital_status := null;
3082 --
3083 else
3084 --
3085 l_person_rec.marital_status := p_rec.marital_status;
3086 --
3087 end if;
3088 --
3089 close c1;
3090 --
3091 end if;
3092 --
3093 else
3094 --
3095 l_person_rec.marital_status := null;
3096 l_person_rec.place_of_birth := null;
3097 l_person_rec.date_of_birth := null;
3098 l_person_rec.gender := null;
3099 --
3100 end if;
3101 --
3102 l_person_rec.date_of_death := p_rec.date_of_death;
3103 --
3104 -- Set party_id of record we are updating
3105 --
3106 l_person_rec.party_rec.party_id := p_rec.party_id;
3107 --
3108 if p_rec.title is not null then
3109 --
3110 open c1('CONTACT_TITLE',p_rec.title);
3111 --
3112 fetch c1 into l_dummy;
3113 if c1%notfound then
3114 --
3115 l_person_rec.person_pre_name_adjunct := null;
3116 --
3117 else
3118 --
3119 l_person_rec.person_pre_name_adjunct := p_rec.title;
3120 --
3121 end if;
3122 --
3123 close c1;
3124 --
3125 end if;
3126 --
3127 -- Get the latest person record
3128 --
3129 select max(last_update_date)
3130 into l_party_last_update_date
3131 from hz_parties
3132 where party_id = p_rec.party_id;
3133 --
3134 --Added for bug 2648797
3135 --Get the object version number
3136
3137 select max(object_version_number)
3138 into l_party_object_version_number
3139 from hz_parties
3140 where party_id = p_rec.party_id;
3141 --
3142 -- Call TCA API and update the person.
3143 --
3144 -- Set HR security profile as HR can only update these records.
3145 --Modified Created by Module Code to 'HR API'
3146 fnd_profile.put('HZ_CREATED_BY_MODULE','HR API');
3147 --
3148 begin
3149 --Modified for Bug 2648797
3150 -- api version,p_commit,p_party_last_update_date and p_validation_level have been commented as TCA V2 does
3151 -- not include them
3152 -- object version number has been added
3153
3154 hz_party_v2pub.update_person
3155 (
3156 --p_api_version => 1.0,
3157 p_init_msg_list => 'F',
3158 --p_commit => 'F',
3159 p_person_rec => l_person_rec,
3160 --p_party_last_update_date => l_party_last_update_date,
3161 p_party_object_version_number => l_party_object_version_number, --Added for bug# 2648797
3162 x_profile_id => l_profile_id,
3163 x_return_status => l_return_status,
3164 x_msg_count => l_msg_count,
3165 x_msg_data => l_msg_data
3166 --p_validation_level => 100
3167 );
3168 --
3169 if l_return_status in ('E','U') then
3170 --
3171 hr_utility.set_location(substr(l_msg_data,1,80),10);
3172 fnd_message.set_token('POO',p_rec.party_id);
3173 fnd_message.raise_error;
3174 --
3175 end if;
3176
3177 exception
3178 when others then
3179 --
3180 fnd_message.set_name('PER','PER_289974_TCA_PERSON');
3181 fnd_message.set_token('PROCEDURE','per_hrtca_merge.update_tca_person');
3182 fnd_message.set_token('STEP','10');
3183 -- fnd_message.set_token('ERROR', sqlerrm); Bug 5408534 should use l_msg_data
3184 fnd_message.set_token('ERROR', l_msg_data);
3185 fnd_message.raise_error;
3186 --
3187 end;
3188 --
3189 -- Update the reference for special case where we have created a party
3190 -- and then assigned that party to an HR person.
3191 -- This will prevent any future updates to the TCA data unless the update
3192 -- comes from HR.
3193 --
3194 update hz_parties
3195 set orig_system_reference = 'PER:'||p_rec.person_id
3196 where party_id = p_rec.party_id
3197 and substr(orig_system_reference,1,4) <> 'PER:';
3198 --
3199 -- Dirty hack to get around 2078156
3200 -- PER_CONTACT_RELATIONSHIPS_PKG checks for SQL%NOTFOUND
3201 -- after call to update_tca_person, and raises NO_DATA_FOUND
3202 -- if true. This update statement is causing PER_CONTACT_RELATIONSHIPS
3203 -- to fail - so, force a dummy query to reset SQL%NOTFOUND here
3204 if (SQL%NOTFOUND) then
3205 select 1 into l_dummy from dual;
3206 end if;
3207 --
3208 --Modified Created by Module Code to 'HR API'
3209 fnd_profile.put('HZ_CREATED_BY_MODULE','HR API');
3210 --
3211 g_count := g_count + 1;
3212 --
3213 if g_count = 1 then
3214 --
3215 replicate_person_across_bg(p_rec => p_rec,
3216 p_overwrite_data => p_overwrite_data);
3217 g_count := 0;
3218 --
3219 end if;
3220 --
3221 -- Dirty hack to get around 2078156
3222 -- PER_CONTACT_RELATIONSHIPS_PKG checks for SQL%NOTFOUND
3223 -- after call to update_tca_person, and raises NO_DATA_FOUND
3224 -- if true. Statements in this routine can result in SQL%NOTFOUND
3225 -- being true, causing PER_CONTACT_RELATIONSHIPS
3226 -- to fail erroneously - so, force a dummy query
3227 -- to reset SQL%NOTFOUND here
3228 if (SQL%NOTFOUND) then
3229 select 1 into l_dummy from dual;
3230 end if;
3231 --
3232 create_update_contact_point(p_rec => p_rec);
3233 --
3234 hr_utility.set_location('Leaving '||l_proc,10);
3235 --
3236 exception
3237 --
3238 when others then
3239 --
3240 fnd_profile.put('HZ_CREATED_BY_MODULE','NON HR');
3241 raise;
3242 --
3243 end;
3244 --
3245 --
3246 -- ------------------------------------------------------------------------------
3247 -- |------------------------< replicate_person_across_bg >----------------------|
3248 -- ------------------------------------------------------------------------------
3249 --
3250 procedure replicate_person_across_bg
3251 (p_rec in out nocopy per_all_people_f%rowtype,
3252 p_overwrite_data in varchar2 default 'Y') is
3253 --
3254 l_proc varchar2(80) := g_package||'.replicate_person_across_bg';
3255 l_host_leg varchar2(30);
3256 l_target_leg varchar2(30);
3257 --
3258 cursor c_person is
3259 select *
3260 from per_all_people_f
3261 where effective_start_date <= p_rec.effective_end_date
3262 and effective_end_date >= p_rec.effective_start_date
3263 and person_id <> p_rec.person_id
3264 and party_id = p_rec.party_id
3265 order by person_id, effective_start_date;
3266 --
3267 type l_person_tab is table of c_person%rowtype index by binary_integer;
3268 l_person l_person_tab;
3269 --
3270 cursor csr_get_person_details(cp_person_id number,
3271 cp_effective_date date) is
3272 select *
3273 from per_all_people_f
3274 where person_id = cp_person_id
3275 and cp_effective_date between effective_start_date and effective_end_date;
3276 l_person_rec csr_get_person_details%rowtype;
3277 --
3278 l_last_bg_id number := -1;
3279 l_correction boolean;
3280 l_update boolean;
3281 l_update_override boolean;
3282 l_update_change_insert boolean;
3283 l_datetrack_mode varchar2(30);
3284 l_effective_start_date date;
3285 l_effective_end_date date;
3286 l_full_name varchar2(240);
3287 l_duplicate_flag varchar2(30);
3288 l_comment_id number;
3289 l_name_combination_warning boolean;
3290 l_assign_payroll_warning boolean;
3291 l_orig_hire_warning boolean;
3292 l_validation_start_date date;
3293 l_validation_end_date date;
3294 l_dummy_lock_id number;
3295 l_copy_rec per_all_people_f%rowtype;
3296 l_ref_person_id number;
3297 l_ref_effective_start_date date;
3298 --
3299 l_local_name per_all_people_f.local_name%TYPE;
3300 l_global_name per_all_people_f.global_name%TYPE;
3301 l_order_name per_all_people_f.order_name%TYPE;
3302 --
3303 begin
3304 --
3305 hr_utility.set_location('Entering '||l_proc,10);
3306 --
3307 -- Bug fix 3598173. NVL added to if condition.
3308
3309 if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
3310 --
3311 return;
3312 --
3313 end if;
3314 --
3315 -- Get all records with the same party id where the effective start date
3316 -- <= host effective end date and effective end date >= effective start
3317 -- date of host record.
3318 --
3319 open c_person;
3320 --
3321 loop
3322 --
3323 fetch c_person into l_person(l_person.count+1);
3324 exit when c_person%notfound;
3325 --
3326 end loop;
3327 --
3328 close c_person;
3329 --
3330 if l_person.count = 0 then
3331 --
3332 return;
3333 --
3334 end if;
3335 --
3336 -- Get the host legislation
3337 --
3338 l_host_leg :=
3339 get_legislation_code(p_business_group_id => p_rec.business_group_id);
3340 --
3341 for l_count in 1..l_person.count loop
3342 --
3343 -- Get the target legislation only if the business group has changed.
3344 --
3345 if l_target_leg is null or
3346 l_person(l_count).business_group_id <> l_last_bg_id then
3347 --
3348 l_target_leg := get_legislation_code
3349 (p_business_group_id => l_person(l_count).business_group_id);
3350 --
3351 end if;
3352 --
3353 -- WWBUG 2560449
3354 --
3355 if l_host_leg <> l_target_leg and
3356 (l_host_leg in ('KR','JP') or l_target_leg in ('KR','JP')) then
3357 --
3358 -- Lets skip this update as the legislations are not compatible.
3359 --
3360 null;
3361 --
3362 else
3363 --
3364 -- We now have four possible scenarios for any update that takes
3365 -- place and they are as follows.
3366 --
3367 -- Effective Start Date of target record and Effective End Date of
3368 -- target record fall between Effective Start Date and Effective End
3369 -- Date of host record. This means a correction.
3370 --
3371 -- Effective Start Date of target record is before Effective Start
3372 -- Date of host record and Effective End Date of target record is
3373 -- before Effective End Date of host record. This means an update
3374 -- change insert.
3375 --
3376 -- Effective Start Date of target record is after the Effective Start
3377 -- Date of host record and Effective End Date of target record is
3378 -- after Effective End Date of host record. This means a
3379 -- correction followed by an update or update_change_insert based
3380 -- on whether future records exist.
3381 --
3382 -- Effective Start Date of target record is before the Effective
3383 -- Start Date of host record and Effective End Date of target record
3384 -- is after the Effective End Date of host record. This means an
3385 -- update_change_insert or update followed by an update_change_insert
3386 -- or update based on whether future rows exist.
3387 --
3388 if l_person(l_count).effective_start_date >= p_rec.effective_start_date and
3389 l_person(l_count).effective_end_date <= p_rec.effective_end_date then
3390 --
3391 l_datetrack_mode := 'CORRECTION';
3392 --
3393 elsif l_person(l_count).effective_start_date >= p_rec.effective_start_date and
3394 l_person(l_count).effective_end_date > p_rec.effective_end_date then
3395 --
3396 l_datetrack_mode := 'CORRECTION';
3397 --
3398 -- Then an update or update_change_insert
3399 --
3400 elsif l_person(l_count).effective_start_date < p_rec.effective_start_date and
3401 l_person(l_count).effective_end_date <= p_rec.effective_end_date then
3402 --
3403 if l_person(l_count).effective_end_date <> hr_api.g_eot then
3404 --
3405 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
3406 --
3407 elsif l_person(l_count).effective_end_date = hr_api.g_eot then
3408 --
3409 l_datetrack_mode := 'UPDATE';
3410 --
3411 end if;
3412 --
3413 elsif l_person(l_count).effective_start_date < p_rec.effective_start_date and
3414 l_person(l_count).effective_end_date > p_rec.effective_end_date then
3415 --
3416 if l_person(l_count).effective_end_date <> hr_api.g_eot then
3417 --
3418 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
3419 --
3420 elsif l_person(l_count).effective_end_date = hr_api.g_eot then
3421 --
3422 l_datetrack_mode := 'UPDATE';
3423 --
3424 end if;
3425 --
3426 -- Then another update_change_insert or update
3427 --
3428 end if;
3429 --
3430 -- Now call the API with the appropriate calling mode.
3431 --
3432 -- To avoid the ora-1002 error that comes from recursive calls
3433 -- we have to make sure that the update routine calls update
3434 -- tca person and then calls replicate just once. Replicate must
3435 -- then handle the update to all other person records and the
3436 -- update to TCA. We can not let the API's handle this as otherwise
3437 -- we get into a recursive nightmare with the following happening :
3438 -- API -> TCA -> REPLICATE -> API -> TCA.....
3439 -- We will make it work like so :
3440 -- API -> TCA -> REPLICATE.
3441 --
3442 -- It works fine through API's but not when called from the forms
3443 -- interface. Correct code will be left to make transistion easier.
3444 --
3445 hr_utility.set_location('Locking Record',10);
3446 hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3447 hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3448 hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3449 hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3450 --
3451 -- We have to lock ourselves as the row handler locks per_people_f
3452 -- which could mean that we can't lock the other records due to
3453 -- security issues. Additionally we have to use a loop to go through
3454 -- the records as otherwise we raise a 1002 error, fetch out of
3455 -- sequence.
3456 --
3457 -- Fix for 9i.
3458 -- No array referencing in SQL seems to be allowed.
3459 --
3460 l_ref_effective_start_date := l_person(l_count).effective_start_date;
3461 l_ref_person_id := l_person(l_count).person_id;
3462 --
3463 select person_id
3464 into l_dummy_lock_id
3465 from per_all_people_f
3466 where l_ref_effective_start_date
3467 between effective_start_date
3468 and effective_end_date
3469 and person_id = l_ref_person_id
3470 for update nowait;
3471 /*
3472 per_per_shd.lck
3473 (p_person_id => l_person(l_count).person_id,
3474 p_datetrack_mode => l_datetrack_mode,
3475 p_object_version_number => l_person(l_count).object_version_number,
3476 p_effective_date => p_rec.effective_start_date,
3477 p_validation_start_date => l_validation_start_date,
3478 p_validation_end_date => l_validation_end_date);
3479 */
3480 --
3481 if l_datetrack_mode = 'CORRECTION' then
3482 --
3483 hr_utility.set_location('Updating Record in Correction mode',10);
3484 --
3485 -- Set the values of the columns.
3486 --
3487 l_copy_rec.date_of_birth :=
3488 propagate_value
3489 (l_person(l_count).date_of_birth,
3490 p_rec. date_of_birth,
3491 p_overwrite_data);
3492 l_copy_rec.first_name :=
3493 propagate_value
3494 (l_person(l_count).first_name,
3495 p_rec.first_name,
3496 p_overwrite_data);
3497 l_copy_rec.known_as :=
3498 propagate_value
3499 (l_person(l_count).known_as,
3500 p_rec.known_as,
3501 p_overwrite_data);
3502 l_copy_rec.marital_status :=
3503 propagate_value
3504 (l_person(l_count).marital_status,
3505 p_rec.marital_status,
3506 p_overwrite_data);
3507 l_copy_rec.middle_names :=
3508 propagate_value
3509 (l_person(l_count).middle_names,
3510 p_rec.middle_names,
3511 p_overwrite_data);
3512 l_copy_rec.nationality :=
3513 propagate_value
3514 (l_person(l_count).nationality,
3515 p_rec.nationality,
3516 p_overwrite_data);
3517 l_copy_rec.sex :=
3518 propagate_value
3519 (l_person(l_count).sex,
3520 p_rec.sex,
3521 p_overwrite_data);
3522 l_copy_rec.title :=
3523 propagate_value
3524 (l_person(l_count).title,
3525 p_rec.title,
3526 p_overwrite_data);
3527 l_copy_rec.blood_type :=
3528 propagate_value
3529 (l_person(l_count).blood_type,
3530 p_rec.blood_type,
3531 p_overwrite_data);
3532 l_copy_rec.correspondence_language :=
3533 propagate_value
3534 (l_person(l_count).correspondence_language,
3535 p_rec.correspondence_language,
3536 p_overwrite_data);
3537 l_copy_rec.honors :=
3538 propagate_value
3539 (l_person(l_count).honors,
3540 p_rec.honors,
3541 p_overwrite_data);
3542 l_copy_rec.pre_name_adjunct :=
3543 propagate_value
3544 (l_person(l_count).pre_name_adjunct,
3545 p_rec.pre_name_adjunct,
3546 p_overwrite_data);
3547 l_copy_rec.rehire_authorizor :=
3548 propagate_value
3549 (l_person(l_count).rehire_authorizor,
3550 p_rec.rehire_authorizor,
3551 p_overwrite_data);
3552 l_copy_rec.rehire_recommendation :=
3553 propagate_value
3554 (l_person(l_count).rehire_recommendation,
3555 p_rec.rehire_recommendation,
3556 p_overwrite_data);
3557 l_copy_rec.resume_exists :=
3558 propagate_value
3559 (l_person(l_count).resume_exists,
3560 p_rec.resume_exists,
3561 p_overwrite_data);
3562 l_copy_rec.resume_last_updated :=
3563 propagate_value
3564 (l_person(l_count).resume_last_updated,
3565 p_rec.resume_last_updated,
3566 p_overwrite_data);
3567 l_copy_rec.second_passport_exists :=
3568 propagate_value
3569 (l_person(l_count).second_passport_exists,
3570 p_rec.second_passport_exists,
3571 p_overwrite_data);
3572 l_copy_rec.student_status :=
3573 propagate_value
3574 (l_person(l_count).student_status,
3575 p_rec.student_status,
3576 p_overwrite_data);
3577 l_copy_rec.suffix :=
3578 propagate_value
3579 (l_person(l_count).suffix,
3580 p_rec.suffix,
3581 p_overwrite_data);
3582 l_copy_rec.date_of_death :=
3583 propagate_value
3584 (l_person(l_count).date_of_death,
3585 p_rec.date_of_death,
3586 p_overwrite_data);
3587 l_copy_rec.uses_tobacco_flag :=
3588 propagate_value
3589 (l_person(l_count).uses_tobacco_flag,
3590 p_rec.uses_tobacco_flag,
3591 p_overwrite_data);
3592 l_copy_rec.town_of_birth :=
3593 propagate_value
3594 (l_person(l_count).town_of_birth,
3595 p_rec.town_of_birth,
3596 p_overwrite_data);
3597 l_copy_rec.region_of_birth :=
3598 propagate_value
3599 (l_person(l_count).region_of_birth,
3600 p_rec.region_of_birth,
3601 p_overwrite_data);
3602 l_copy_rec.country_of_birth :=
3603 propagate_value
3604 (l_person(l_count).country_of_birth,
3605 p_rec.country_of_birth,
3606 p_overwrite_data);
3607 l_copy_rec.fast_path_employee :=
3608 propagate_value
3609 (l_person(l_count).fast_path_employee,
3610 p_rec.fast_path_employee,
3611 p_overwrite_data);
3612 l_copy_rec.email_address := propagate_value
3613 (l_person(l_count).email_address,
3614 p_rec.email_address,
3615 p_overwrite_data);
3616 l_copy_rec.fte_capacity := propagate_value
3617 (l_person(l_count).fte_capacity,
3618 p_rec.fte_capacity,
3619 p_overwrite_data);
3620 -- Bug fix 3598173 starts here
3621 l_copy_rec.previous_last_name :=
3622 propagate_value
3623 (l_person(l_count).previous_last_name,
3624 p_rec.previous_last_name,
3625 p_overwrite_data);
3626 -- Bug fix 3598173 ends here.
3627 --
3628 --hr_person.derive_full_name
3629 -- (p_first_name => l_copy_rec.first_name,
3630 -- p_middle_names => l_copy_rec.middle_names,
3631 -- p_last_name => p_rec.last_name,
3632 -- p_known_as => l_copy_rec.known_as,
3633 -- p_title => l_copy_rec.title,
3634 -- p_suffix => l_copy_rec.suffix,
3635 -- p_pre_name_adjunct => l_copy_rec.pre_name_adjunct,
3636 -- p_date_of_birth => l_copy_rec.date_of_birth,
3637 -- p_person_id => l_person(l_count).person_id,
3638 -- p_business_group_id => l_person(l_count).business_group_id,
3639 -- p_full_name => l_full_name,
3640 -- p_duplicate_flag => l_duplicate_flag);
3641 --
3642 hr_person_name.derive_person_names -- #3889584
3643 (p_format_name => NULL, -- derive all person names
3644 p_business_group_id => l_person(l_count).business_group_id,
3645 p_person_id => l_person(l_count).person_id,
3646 p_first_name => l_copy_rec.first_name,
3647 p_middle_names => l_copy_rec.middle_names,
3648 p_last_name => p_rec.last_name,
3649 p_known_as => l_copy_rec.known_as,
3650 p_title => l_copy_rec.title,
3651 p_suffix => l_copy_rec.suffix,
3652 p_pre_name_adjunct => l_copy_rec.pre_name_adjunct,
3653 p_date_of_birth => l_copy_rec.date_of_birth,
3654 p_previous_last_name => l_copy_rec.previous_last_name ,
3655 p_email_address => l_copy_rec.email_address,
3656 p_employee_number => l_person(l_count).employee_number ,
3657 p_applicant_number => l_person(l_count).applicant_number ,
3658 p_npw_number => l_person(l_count).npw_number,
3659 p_per_information1 => l_person(l_count).per_information1 ,
3660 p_per_information2 => l_person(l_count).per_information2 ,
3661 p_per_information3 => l_person(l_count).per_information3 ,
3662 p_per_information4 => l_person(l_count).per_information4 ,
3663 p_per_information5 => l_person(l_count).per_information5 ,
3664 p_per_information6 => l_person(l_count).per_information6 ,
3665 p_per_information7 => l_person(l_count).per_information7 ,
3666 p_per_information8 => l_person(l_count).per_information8 ,
3667 p_per_information9 => l_person(l_count).per_information9 ,
3668 p_per_information10 => l_person(l_count).per_information10 ,
3669 p_per_information11 => l_person(l_count).per_information11 ,
3670 p_per_information12 => l_person(l_count).per_information12 ,
3671 p_per_information13 => l_person(l_count).per_information13 ,
3672 p_per_information14 => l_person(l_count).per_information14 ,
3673 p_per_information15 => l_person(l_count).per_information15 ,
3674 p_per_information16 => l_person(l_count).per_information16 ,
3675 p_per_information17 => l_person(l_count).per_information17 ,
3676 p_per_information18 => l_person(l_count).per_information18 ,
3677 p_per_information19 => l_person(l_count).per_information19 ,
3678 p_per_information20 => l_person(l_count).per_information20 ,
3679 p_per_information21 => l_person(l_count).per_information21 ,
3680 p_per_information22 => l_person(l_count).per_information22 ,
3681 p_per_information23 => l_person(l_count).per_information23 ,
3682 p_per_information24 => l_person(l_count).per_information24 ,
3683 p_per_information25 => l_person(l_count).per_information25 ,
3684 p_per_information26 => l_person(l_count).per_information26 ,
3685 p_per_information27 => l_person(l_count).per_information27 ,
3686 p_per_information28 => l_person(l_count).per_information28 ,
3687 p_per_information29 => l_person(l_count).per_information29 ,
3688 p_per_information30 => l_person(l_count).per_information30 ,
3689 p_attribute1 => l_person(l_count).attribute1 ,
3690 p_attribute2 => l_person(l_count).attribute2 ,
3691 p_attribute3 => l_person(l_count).attribute3 ,
3692 p_attribute4 => l_person(l_count).attribute4 ,
3693 p_attribute5 => l_person(l_count).attribute5 ,
3694 p_attribute6 => l_person(l_count).attribute6 ,
3695 p_attribute7 => l_person(l_count).attribute7 ,
3696 p_attribute8 => l_person(l_count).attribute8 ,
3697 p_attribute9 => l_person(l_count).attribute9 ,
3698 p_attribute10 => l_person(l_count).attribute10 ,
3699 p_attribute11 => l_person(l_count).attribute11 ,
3700 p_attribute12 => l_person(l_count).attribute12 ,
3701 p_attribute13 => l_person(l_count).attribute13 ,
3702 p_attribute14 => l_person(l_count).attribute14 ,
3703 p_attribute15 => l_person(l_count).attribute15 ,
3704 p_attribute16 => l_person(l_count).attribute16 ,
3705 p_attribute17 => l_person(l_count).attribute17 ,
3706 p_attribute18 => l_person(l_count).attribute18 ,
3707 p_attribute19 => l_person(l_count).attribute19 ,
3708 p_attribute20 => l_person(l_count).attribute20 ,
3709 p_attribute21 => l_person(l_count).attribute21 ,
3710 p_attribute22 => l_person(l_count).attribute22 ,
3711 p_attribute23 => l_person(l_count).attribute23,
3712 p_attribute24 => l_person(l_count).attribute24,
3713 p_attribute25 => l_person(l_count).attribute25,
3714 p_attribute26 => l_person(l_count).attribute26,
3715 p_attribute27 => l_person(l_count).attribute27,
3716 p_attribute28 => l_person(l_count).attribute28,
3717 p_attribute29 => l_person(l_count).attribute29,
3718 p_attribute30 => l_person(l_count).attribute30,
3719 p_full_name => l_full_name,
3720 p_order_name => l_order_name,
3721 p_global_name => l_global_name,
3722 p_local_name => l_local_name,
3723 p_duplicate_flag => l_duplicate_flag);
3724
3725 l_ref_effective_start_date := l_person(l_count).effective_start_date;
3726 l_ref_person_id := l_person(l_count).person_id;
3727 --
3728 update per_all_people_f
3729 set last_name = p_rec.last_name,
3730 full_name = l_full_name,
3731 date_of_birth = l_copy_rec.date_of_birth,
3732 first_name = l_copy_rec.first_name,
3733 known_as = l_copy_rec.known_as,
3734 marital_status = l_copy_rec.marital_status,
3735 middle_names = l_copy_rec.middle_names,
3736 nationality = l_copy_rec.nationality,
3737 sex = l_copy_rec.sex,
3738 title = l_copy_rec.title,
3739 blood_type = l_copy_rec.blood_type,
3740 correspondence_language = l_copy_rec.correspondence_language,
3741 honors = l_copy_rec.honors,
3742 pre_name_adjunct = l_copy_rec.pre_name_adjunct,
3743 rehire_authorizor = l_copy_rec.rehire_authorizor,
3744 rehire_recommendation = l_copy_rec.rehire_recommendation,
3745 resume_exists = l_copy_rec.resume_exists,
3746 resume_last_updated = l_copy_rec.resume_last_updated,
3747 second_passport_exists = l_copy_rec.second_passport_exists,
3748 student_status = l_copy_rec.student_status,
3749 suffix = l_copy_rec.suffix,
3750 date_of_death = l_copy_rec.date_of_death,
3751 uses_tobacco_flag = l_copy_rec.uses_tobacco_flag,
3752 town_of_birth = l_copy_rec.town_of_birth,
3753 region_of_birth = l_copy_rec.region_of_birth,
3754 country_of_birth = l_copy_rec.country_of_birth,
3755 fast_path_employee = l_copy_rec.fast_path_employee,
3756 email_address = l_copy_rec.email_address,
3757 fte_capacity = l_copy_rec.fte_capacity,
3758 previous_last_name = l_copy_rec.previous_last_name, -- bug fix 3598173.
3759 order_name = l_order_name,
3760 global_name = l_global_name,
3761 local_name = l_local_name
3762 where person_id = l_ref_person_id
3763 and l_ref_effective_start_date
3764 between effective_start_date
3765 and effective_end_date;
3766 --
3767 elsif l_datetrack_mode = 'UPDATE' then
3768 --
3769 hr_utility.set_location('Updating Record in update mode',10);
3770 --
3771 l_ref_effective_start_date := l_person(l_count).effective_start_date;
3772 l_ref_person_id := l_person(l_count).person_id;
3773 --
3774 update per_all_people_f
3775 set effective_end_date = p_rec.effective_start_date-1
3776 where person_id = l_ref_person_id
3777 and l_ref_effective_start_date
3778 between effective_start_date
3779 and effective_end_date;
3780 --
3781 hr_utility.set_location('Getting max OVN in update mode',10);
3782 --
3783 l_person(l_count).object_version_number :=
3784 dt_api.get_object_version_number
3785 (p_base_table_name => 'per_all_people_f',
3786 p_base_key_column => 'person_id',
3787 p_base_key_value => l_person(l_count).person_id);
3788 --
3789 hr_utility.set_location('inserting new record in update mode',10);
3790 --
3791 l_copy_rec.date_of_birth :=
3792 propagate_value
3793 (l_person(l_count).date_of_birth,
3794 p_rec. date_of_birth,
3795 p_overwrite_data);
3796 l_copy_rec.first_name :=
3797 propagate_value
3798 (l_person(l_count).first_name,
3799 p_rec.first_name,
3800 p_overwrite_data);
3801 l_copy_rec.known_as :=
3802 propagate_value
3803 (l_person(l_count).known_as,
3804 p_rec.known_as,
3805 p_overwrite_data);
3806 l_copy_rec.marital_status :=
3807 propagate_value
3808 (l_person(l_count).marital_status,
3809 p_rec.marital_status,
3810 p_overwrite_data);
3811 l_copy_rec.middle_names :=
3812 propagate_value
3813 (l_person(l_count).middle_names,
3814 p_rec.middle_names,
3815 p_overwrite_data);
3816 l_copy_rec.nationality :=
3817 propagate_value
3818 (l_person(l_count).nationality,
3819 p_rec.nationality,
3820 p_overwrite_data);
3821 l_copy_rec.sex :=
3822 propagate_value
3823 (l_person(l_count).sex,
3824 p_rec.sex,
3825 p_overwrite_data);
3826 l_copy_rec.title :=
3827 propagate_value
3828 (l_person(l_count).title,
3829 p_rec.title,
3830 p_overwrite_data);
3831 l_copy_rec.blood_type :=
3832 propagate_value
3833 (l_person(l_count).blood_type,
3834 p_rec.blood_type,
3835 p_overwrite_data);
3836 l_copy_rec.correspondence_language :=
3837 propagate_value
3838 (l_person(l_count).correspondence_language,
3839 p_rec.correspondence_language,
3840 p_overwrite_data);
3841 l_copy_rec.honors :=
3842 propagate_value
3843 (l_person(l_count).honors,
3844 p_rec.honors,
3845 p_overwrite_data);
3846 l_copy_rec.pre_name_adjunct :=
3847 propagate_value
3848 (l_person(l_count).pre_name_adjunct,
3849 p_rec.pre_name_adjunct,
3850 p_overwrite_data);
3851 l_copy_rec.rehire_authorizor :=
3852 propagate_value
3853 (l_person(l_count).rehire_authorizor,
3854 p_rec.rehire_authorizor,
3855 p_overwrite_data);
3856 l_copy_rec.rehire_recommendation :=
3857 propagate_value
3858 (l_person(l_count).rehire_recommendation,
3859 p_rec.rehire_recommendation,
3860 p_overwrite_data);
3861 l_copy_rec.resume_exists :=
3862 propagate_value
3863 (l_person(l_count).resume_exists,
3864 p_rec.resume_exists,
3865 p_overwrite_data);
3866 l_copy_rec.resume_last_updated :=
3867 propagate_value
3868 (l_person(l_count).resume_last_updated,
3869 p_rec.resume_last_updated,
3870 p_overwrite_data);
3871 l_copy_rec.second_passport_exists :=
3872 propagate_value
3873 (l_person(l_count).second_passport_exists,
3874 p_rec.second_passport_exists,
3875 p_overwrite_data);
3876 l_copy_rec.student_status :=
3877 propagate_value
3878 (l_person(l_count).student_status,
3879 p_rec.student_status,
3880 p_overwrite_data);
3881 l_copy_rec.suffix :=
3882 propagate_value
3883 (l_person(l_count).suffix,
3884 p_rec.suffix,
3885 p_overwrite_data);
3886 l_copy_rec.date_of_death :=
3887 propagate_value
3888 (l_person(l_count).date_of_death,
3889 p_rec.date_of_death,
3890 p_overwrite_data);
3891 l_copy_rec.uses_tobacco_flag :=
3892 propagate_value
3893 (l_person(l_count).uses_tobacco_flag,
3894 p_rec.uses_tobacco_flag,
3895 p_overwrite_data);
3896 l_copy_rec.town_of_birth :=
3897 propagate_value
3898 (l_person(l_count).town_of_birth,
3899 p_rec.town_of_birth,
3900 p_overwrite_data);
3901 l_copy_rec.region_of_birth :=
3902 propagate_value
3903 (l_person(l_count).region_of_birth,
3904 p_rec.region_of_birth,
3905 p_overwrite_data);
3906 l_copy_rec.country_of_birth :=
3907 propagate_value
3908 (l_person(l_count).country_of_birth,
3909 p_rec.country_of_birth,
3910 p_overwrite_data);
3911 l_copy_rec.fast_path_employee :=
3912 propagate_value
3913 (l_person(l_count).fast_path_employee,
3914 p_rec.fast_path_employee,
3915 p_overwrite_data);
3916 l_copy_rec.email_address := propagate_value
3917 (l_person(l_count).email_address,
3918 p_rec.email_address,
3919 p_overwrite_data);
3920 l_copy_rec.fte_capacity := propagate_value
3921 (l_person(l_count).fte_capacity,
3922 p_rec.fte_capacity,
3923 p_overwrite_data);
3924 --
3925 -- Bug fix 3598173 starts here
3926 l_copy_rec.previous_last_name :=
3927 propagate_value
3928 (l_person(l_count).previous_last_name,
3929 p_rec.previous_last_name,
3930 p_overwrite_data);
3931 -- Bug fix 3598173 ends here.
3932
3933 open csr_get_person_details(l_person(l_count).person_id, p_rec.effective_start_date-1);
3934 fetch csr_get_person_details into l_person_rec;
3935 if csr_get_person_details%FOUND then
3936 close csr_get_person_details;
3937
3938 --hr_person.derive_full_name
3939 --(p_first_name => l_copy_rec.first_name,
3940 -- p_middle_names => l_copy_rec.middle_names,
3941 -- p_last_name => p_rec.last_name,
3942 -- p_known_as => l_copy_rec.known_as,
3943 -- p_title => l_copy_rec.title,
3944 -- p_suffix => l_copy_rec.suffix,
3945 -- p_pre_name_adjunct => l_copy_rec.pre_name_adjunct,
3946 -- p_date_of_birth => l_copy_rec.date_of_birth,
3947 -- p_person_id => l_person(l_count).person_id,
3948 -- p_business_group_id => l_person(l_count).business_group_id,
3949 -- p_full_name => l_full_name,
3950 -- p_duplicate_flag => l_duplicate_flag);
3951 --
3952 hr_person_name.derive_person_names -- #3889584
3953 (p_format_name => NULL, -- derive all person names
3954 p_business_group_id => l_person(l_count).business_group_id,
3955 p_person_id => l_person(l_count).person_id,
3956 p_first_name => l_copy_rec.first_name,
3957 p_middle_names => l_copy_rec.middle_names,
3958 p_last_name => p_rec.last_name,
3959 p_known_as => l_copy_rec.known_as,
3960 p_title => l_copy_rec.title,
3961 p_suffix => l_copy_rec.suffix,
3962 p_pre_name_adjunct => l_copy_rec.pre_name_adjunct,
3963 p_date_of_birth => l_copy_rec.date_of_birth,
3964 p_previous_last_name => l_copy_rec.previous_last_name ,
3965 p_email_address => l_copy_rec.email_address,
3966 p_employee_number => l_person_rec.employee_number ,
3967 p_applicant_number => l_person_rec.applicant_number ,
3968 p_npw_number => l_person_rec.npw_number,
3969 p_per_information1 => l_person_rec.per_information1 ,
3970 p_per_information2 => l_person_rec.per_information2 ,
3971 p_per_information3 => l_person_rec.per_information3 ,
3972 p_per_information4 => l_person_rec.per_information4 ,
3973 p_per_information5 => l_person_rec.per_information5 ,
3974 p_per_information6 => l_person_rec.per_information6 ,
3975 p_per_information7 => l_person_rec.per_information7 ,
3976 p_per_information8 => l_person_rec.per_information8 ,
3977 p_per_information9 => l_person_rec.per_information9 ,
3978 p_per_information10 => l_person_rec.per_information10 ,
3979 p_per_information11 => l_person_rec.per_information11 ,
3980 p_per_information12 => l_person_rec.per_information12 ,
3981 p_per_information13 => l_person_rec.per_information13 ,
3982 p_per_information14 => l_person_rec.per_information14 ,
3983 p_per_information15 => l_person_rec.per_information15 ,
3984 p_per_information16 => l_person_rec.per_information16 ,
3985 p_per_information17 => l_person_rec.per_information17 ,
3986 p_per_information18 => l_person_rec.per_information18 ,
3987 p_per_information19 => l_person_rec.per_information19 ,
3988 p_per_information20 => l_person_rec.per_information20 ,
3989 p_per_information21 => l_person_rec.per_information21 ,
3990 p_per_information22 => l_person_rec.per_information22 ,
3991 p_per_information23 => l_person_rec.per_information23 ,
3992 p_per_information24 => l_person_rec.per_information24 ,
3993 p_per_information25 => l_person_rec.per_information25 ,
3994 p_per_information26 => l_person_rec.per_information26 ,
3995 p_per_information27 => l_person_rec.per_information27 ,
3996 p_per_information28 => l_person_rec.per_information28 ,
3997 p_per_information29 => l_person_rec.per_information29 ,
3998 p_per_information30 => l_person_rec.per_information30 ,
3999 p_attribute1 => l_person_rec.attribute1 ,
4000 p_attribute2 => l_person_rec.attribute2 ,
4001 p_attribute3 => l_person_rec.attribute3 ,
4002 p_attribute4 => l_person_rec.attribute4 ,
4003 p_attribute5 => l_person_rec.attribute5 ,
4004 p_attribute6 => l_person_rec.attribute6 ,
4005 p_attribute7 => l_person_rec.attribute7 ,
4006 p_attribute8 => l_person_rec.attribute8 ,
4007 p_attribute9 => l_person_rec.attribute9 ,
4008 p_attribute10 => l_person_rec.attribute10 ,
4009 p_attribute11 => l_person_rec.attribute11 ,
4010 p_attribute12 => l_person_rec.attribute12 ,
4011 p_attribute13 => l_person_rec.attribute13 ,
4012 p_attribute14 => l_person_rec.attribute14 ,
4013 p_attribute15 => l_person_rec.attribute15 ,
4014 p_attribute16 => l_person_rec.attribute16 ,
4015 p_attribute17 => l_person_rec.attribute17 ,
4016 p_attribute18 => l_person_rec.attribute18 ,
4017 p_attribute19 => l_person_rec.attribute19 ,
4018 p_attribute20 => l_person_rec.attribute20 ,
4019 p_attribute21 => l_person_rec.attribute21 ,
4020 p_attribute22 => l_person_rec.attribute22 ,
4021 p_attribute23 => l_person_rec.attribute23,
4022 p_attribute24 => l_person_rec.attribute24,
4023 p_attribute25 => l_person_rec.attribute25,
4024 p_attribute26 => l_person_rec.attribute26,
4025 p_attribute27 => l_person_rec.attribute27,
4026 p_attribute28 => l_person_rec.attribute28,
4027 p_attribute29 => l_person_rec.attribute29,
4028 p_attribute30 => l_person_rec.attribute30,
4029 p_full_name => l_full_name,
4030 p_order_name => l_order_name,
4031 p_global_name => l_global_name,
4032 p_local_name => l_local_name,
4033 p_duplicate_flag => l_duplicate_flag);
4034
4035 hr_utility.set_location('Before insert for update event',10);
4036 hr_utility.set_location('Person ID '||l_person(l_count).person_id,10);
4037 hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
4038 --
4039 insert into per_all_people_f
4040 (person_id,
4041 effective_start_date,
4042 effective_end_date,
4043 business_group_id,
4044 person_type_id,
4045 last_name,
4046 full_name,
4047 start_date,
4048 applicant_number,
4049 comment_id,
4050 current_applicant_flag,
4051 current_emp_or_apl_flag,
4052 current_employee_flag,
4053 date_employee_data_verified,
4054 date_of_birth,
4055 email_address,
4056 employee_number,
4057 expense_check_send_to_address,
4058 first_name,
4059 known_as,
4060 marital_status,
4061 middle_names,
4062 nationality,
4063 national_identifier,
4064 previous_last_name,
4065 registered_disabled_flag,
4066 sex,
4067 title,
4068 vendor_id,
4069 request_id,
4070 program_application_id,
4071 program_id,
4072 program_update_date,
4073 attribute_category,
4074 attribute1,
4075 attribute2,
4076 attribute3,
4077 attribute4,
4078 attribute5,
4079 attribute6,
4080 attribute7,
4081 attribute8,
4082 attribute9,
4083 attribute10,
4084 attribute11,
4085 attribute12,
4086 attribute13,
4087 attribute14,
4088 attribute15,
4089 attribute16,
4090 attribute17,
4091 attribute18,
4092 attribute19,
4093 attribute20,
4094 attribute21,
4095 attribute22,
4096 attribute23,
4097 attribute24,
4098 attribute25,
4099 attribute26,
4100 attribute27,
4101 attribute28,
4102 attribute29,
4103 attribute30,
4104 per_information_category,
4105 per_information1,
4106 per_information2,
4107 per_information3,
4108 per_information4,
4109 per_information5,
4110 per_information6,
4111 per_information7,
4112 per_information8,
4113 per_information9,
4114 per_information10,
4115 per_information11,
4116 per_information12,
4117 per_information13,
4118 per_information14,
4119 per_information15,
4120 per_information16,
4121 per_information17,
4122 per_information18,
4123 per_information19,
4124 per_information20,
4125 object_version_number,
4126 suffix,
4127 DATE_OF_DEATH,
4128 BACKGROUND_CHECK_STATUS ,
4129 BACKGROUND_DATE_CHECK ,
4130 BLOOD_TYPE ,
4131 CORRESPONDENCE_LANGUAGE ,
4132 FAST_PATH_EMPLOYEE ,
4133 FTE_CAPACITY ,
4134 HOLD_APPLICANT_DATE_UNTIL ,
4135 HONORS ,
4136 INTERNAL_LOCATION ,
4137 LAST_MEDICAL_TEST_BY ,
4138 LAST_MEDICAL_TEST_DATE ,
4139 MAILSTOP ,
4140 OFFICE_NUMBER ,
4141 ON_MILITARY_SERVICE ,
4142 ORDER_NAME ,
4143 PRE_NAME_ADJUNCT ,
4144 PROJECTED_START_DATE ,
4145 REHIRE_AUTHORIZOR ,
4146 REHIRE_RECOMMENDATION ,
4147 RESUME_EXISTS ,
4148 RESUME_LAST_UPDATED ,
4149 SECOND_PASSPORT_EXISTS ,
4150 STUDENT_STATUS ,
4151 WORK_SCHEDULE ,
4152 PER_INFORMATION21 ,
4153 PER_INFORMATION22 ,
4154 PER_INFORMATION23 ,
4155 PER_INFORMATION24 ,
4156 PER_INFORMATION25 ,
4157 PER_INFORMATION26 ,
4158 PER_INFORMATION27 ,
4159 PER_INFORMATION28 ,
4160 PER_INFORMATION29 ,
4161 PER_INFORMATION30 ,
4162 REHIRE_REASON ,
4163 benefit_group_id ,
4164 receipt_of_death_cert_date ,
4165 coord_ben_med_pln_no ,
4166 coord_ben_no_cvg_flag ,
4167 COORD_BEN_MED_EXT_ER,
4168 COORD_BEN_MED_PL_NAME,
4169 COORD_BEN_MED_INSR_CRR_NAME,
4170 COORD_BEN_MED_INSR_CRR_IDENT,
4171 COORD_BEN_MED_CVG_STRT_DT,
4172 COORD_BEN_MED_CVG_END_DT,
4173 uses_tobacco_flag ,
4174 dpdnt_adoption_date ,
4175 dpdnt_vlntry_svce_flag ,
4176 original_date_of_hire ,
4177 town_of_birth ,
4178 region_of_birth ,
4179 country_of_birth ,
4180 global_person_id ,
4181 party_id ,
4182 created_by,
4183 creation_date,
4184 last_update_date,
4185 last_updated_by,
4186 last_update_login,
4187 global_name,
4188 local_name,
4189 npw_number, -- 5123559
4190 current_npw_flag) -- 5123559)
4191 -- ----------------------- +
4192 VALUES
4193 -- ----------------------- +
4194 (l_person(l_count).person_id,
4195 p_rec.effective_start_date,
4196 hr_api.g_eot,
4197 l_person_rec.business_group_id,
4198 l_person_rec.person_type_id,
4199 p_rec.last_name,
4200 l_full_name,
4201 l_person_rec.start_date,
4202 l_person_rec.applicant_number,
4203 l_person_rec.comment_id,
4204 l_person_rec.current_applicant_flag,
4205 l_person_rec.current_emp_or_apl_flag,
4206 l_person_rec.current_employee_flag,
4207 l_person_rec.date_employee_data_verified,
4208 l_copy_rec.date_of_birth,
4209 l_copy_rec.email_address,
4210 l_person_rec.employee_number,
4211 l_person_rec.expense_check_send_to_address,
4212 l_copy_rec.first_name,
4213 l_copy_rec.known_as,
4214 l_copy_rec.marital_status,
4215 l_copy_rec.middle_names,
4216 l_copy_rec.nationality,
4217 l_person_rec.national_identifier,
4218 l_copy_rec.previous_last_name, -- Bug fix 3598173.
4219 l_person_rec.registered_disabled_flag,
4220 l_copy_rec.sex,
4221 l_copy_rec.title,
4222 l_person_rec.vendor_id,
4223 l_person_rec.request_id,
4224 l_person_rec.program_application_id,
4225 l_person_rec.program_id,
4226 l_person_rec.program_update_date,
4227 l_person_rec.attribute_category,
4228 l_person_rec.attribute1,
4229 l_person_rec.attribute2,
4230 l_person_rec.attribute3,
4231 l_person_rec.attribute4,
4232 l_person_rec.attribute5,
4233 l_person_rec.attribute6,
4234 l_person_rec.attribute7,
4235 l_person_rec.attribute8,
4236 l_person_rec.attribute9,
4237 l_person_rec.attribute10,
4238 l_person_rec.attribute11,
4239 l_person_rec.attribute12,
4240 l_person_rec.attribute13,
4241 l_person_rec.attribute14,
4242 l_person_rec.attribute15,
4243 l_person_rec.attribute16,
4244 l_person_rec.attribute17,
4245 l_person_rec.attribute18,
4246 l_person_rec.attribute19,
4247 l_person_rec.attribute20,
4248 l_person_rec.attribute21,
4249 l_person_rec.attribute22,
4250 l_person_rec.attribute23,
4251 l_person_rec.attribute24,
4252 l_person_rec.attribute25,
4253 l_person_rec.attribute26,
4254 l_person_rec.attribute27,
4255 l_person_rec.attribute28,
4256 l_person_rec.attribute29,
4257 l_person_rec.attribute30,
4258 l_person_rec.per_information_category,
4259 l_person_rec.per_information1,
4260 l_person_rec.per_information2,
4261 l_person_rec.per_information3,
4262 l_person_rec.per_information4,
4263 l_person_rec.per_information5,
4264 l_person_rec.per_information6,
4265 l_person_rec.per_information7,
4266 l_person_rec.per_information8,
4267 l_person_rec.per_information9,
4268 l_person_rec.per_information10,
4269 l_person_rec.per_information11,
4270 l_person_rec.per_information12,
4271 l_person_rec.per_information13,
4272 l_person_rec.per_information14,
4273 l_person_rec.per_information15,
4274 l_person_rec.per_information16,
4275 l_person_rec.per_information17,
4276 l_person_rec.per_information18,
4277 l_person_rec.per_information19,
4278 l_person_rec.per_information20,
4279 l_person(l_count).object_version_number,
4280 l_copy_rec.suffix,
4281 l_copy_rec.DATE_OF_DEATH,
4282 l_person_rec.BACKGROUND_CHECK_STATUS ,
4283 l_person_rec.BACKGROUND_DATE_CHECK ,
4284 l_copy_rec.BLOOD_TYPE,
4285 l_copy_rec.CORRESPONDENCE_LANGUAGE,
4286 l_copy_rec.FAST_PATH_EMPLOYEE,
4287 l_copy_rec.FTE_CAPACITY,
4288 l_person_rec.HOLD_APPLICANT_DATE_UNTIL ,
4289 l_copy_rec.HONORS,
4290 l_person_rec.INTERNAL_LOCATION ,
4291 l_person_rec.LAST_MEDICAL_TEST_BY ,
4292 l_person_rec.LAST_MEDICAL_TEST_DATE ,
4293 l_person_rec.MAILSTOP ,
4294 l_person_rec.OFFICE_NUMBER ,
4295 l_person_rec.ON_MILITARY_SERVICE ,
4296 l_ORDER_NAME ,
4297 l_copy_rec.PRE_NAME_ADJUNCT,
4298 l_person_rec.PROJECTED_START_DATE ,
4299 l_copy_rec.REHIRE_AUTHORIZOR,
4300 l_copy_rec.REHIRE_RECOMMENDATION,
4301 l_copy_rec.RESUME_EXISTS,
4302 l_copy_rec.RESUME_LAST_UPDATED,
4303 l_copy_rec.SECOND_PASSPORT_EXISTS,
4304 l_copy_rec.STUDENT_STATUS,
4305 l_person_rec.WORK_SCHEDULE ,
4306 l_person_rec.per_iNFORMATION21 ,
4307 l_person_rec.per_iNFORMATION22 ,
4308 l_person_rec.per_iNFORMATION23 ,
4309 l_person_rec.per_iNFORMATION24 ,
4310 l_person_rec.per_iNFORMATION25 ,
4311 l_person_rec.per_iNFORMATION26 ,
4312 l_person_rec.per_iNFORMATION27 ,
4313 l_person_rec.per_iNFORMATION28 ,
4314 l_person_rec.per_iNFORMATION29 ,
4315 l_person_rec.per_iNFORMATION30 ,
4316 l_person_rec.REHIRE_REASON ,
4317 l_person_rec.BENEFIT_GROUP_ID ,
4318 l_person_rec.RECEIPT_OF_DEATH_CERT_DATE ,
4319 l_person_rec.COORD_BEN_MED_PLN_NO ,
4320 l_person_rec.COORD_BEN_NO_CVG_FLAG ,
4321 l_person_rec.COORD_BEN_MED_EXT_ER,
4322 l_person_rec.COORD_BEN_MED_PL_NAME,
4323 l_person_rec.COORD_BEN_MED_INSR_CRR_NAME,
4324 l_person_rec.COORD_BEN_MED_INSR_CRR_IDENT,
4325 l_person_rec.COORD_BEN_MED_CVG_STRT_DT,
4326 l_person_rec.COORD_BEN_MED_CVG_END_DT ,
4327 l_copy_rec.USES_TOBACCO_FLAG,
4328 l_person_rec.DPDNT_ADOPTION_DATE ,
4329 l_person_rec.DPDNT_VLNTRY_SVCE_FLAG ,
4330 l_person_rec.ORIGINAL_DATE_OF_HIRE ,
4331 l_copy_rec.town_of_birth,
4332 l_copy_rec.region_of_birth,
4333 l_copy_rec.country_of_birth,
4334 l_person_rec.global_person_id ,
4335 l_person_rec.party_id ,
4336 l_person_rec.created_by,
4337 l_person_rec.creation_date,
4338 sysdate,
4339 fnd_global.user_id,
4340 fnd_global.login_id,
4341 l_global_name,
4342 l_local_name,
4343 l_person_rec.npw_number, -- 5123559
4344 l_person_rec.current_npw_flag); -- 5123559
4345 else
4346 close csr_get_person_details;
4347 end if;
4348 --
4349 elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
4350 --
4351 hr_utility.set_location('updating record in update change insert mode',10);
4352 --
4353 update per_all_people_f
4354 set effective_end_date = p_rec.effective_start_date-1
4355 where person_id = l_person(l_count).person_id
4356 and p_rec.effective_start_date
4357 between effective_start_date
4358 and effective_end_date;
4359 --
4360 hr_utility.set_location('getting max ovn in update change insert mode',10);
4361 --
4362 l_person(l_count).object_version_number :=
4363 dt_api.get_object_version_number
4364 (p_base_table_name => 'per_all_people_f',
4365 p_base_key_column => 'person_id',
4366 p_base_key_value => l_person(l_count).person_id);
4367 --
4368 hr_utility.set_location('inserting record in update change insert mode',10);
4369 --
4370 l_copy_rec.date_of_birth :=
4371 propagate_value
4372 (l_person(l_count).date_of_birth,
4373 p_rec. date_of_birth,
4374 p_overwrite_data);
4375 l_copy_rec.first_name :=
4376 propagate_value
4377 (l_person(l_count).first_name,
4378 p_rec.first_name,
4379 p_overwrite_data);
4380 l_copy_rec.known_as :=
4381 propagate_value
4382 (l_person(l_count).known_as,
4383 p_rec.known_as,
4384 p_overwrite_data);
4385 l_copy_rec.marital_status :=
4386 propagate_value
4387 (l_person(l_count).marital_status,
4388 p_rec.marital_status,
4389 p_overwrite_data);
4390 l_copy_rec.middle_names :=
4391 propagate_value
4392 (l_person(l_count).middle_names,
4393 p_rec.middle_names,
4394 p_overwrite_data);
4395 l_copy_rec.nationality :=
4396 propagate_value
4397 (l_person(l_count).nationality,
4398 p_rec.nationality,
4399 p_overwrite_data);
4400 l_copy_rec.sex :=
4401 propagate_value
4402 (l_person(l_count).sex,
4403 p_rec.sex,
4404 p_overwrite_data);
4405 l_copy_rec.title :=
4406 propagate_value
4407 (l_person(l_count).title,
4408 p_rec.title,
4409 p_overwrite_data);
4410 l_copy_rec.blood_type :=
4411 propagate_value
4412 (l_person(l_count).blood_type,
4413 p_rec.blood_type,
4414 p_overwrite_data);
4415 l_copy_rec.correspondence_language :=
4416 propagate_value
4417 (l_person(l_count).correspondence_language,
4418 p_rec.correspondence_language,
4419 p_overwrite_data);
4420 l_copy_rec.honors :=
4421 propagate_value
4422 (l_person(l_count).honors,
4423 p_rec.honors,
4424 p_overwrite_data);
4425 l_copy_rec.pre_name_adjunct :=
4426 propagate_value
4427 (l_person(l_count).pre_name_adjunct,
4428 p_rec.pre_name_adjunct,
4429 p_overwrite_data);
4430 l_copy_rec.rehire_authorizor :=
4431 propagate_value
4432 (l_person(l_count).rehire_authorizor,
4433 p_rec.rehire_authorizor,
4434 p_overwrite_data);
4435 l_copy_rec.rehire_recommendation :=
4436 propagate_value
4437 (l_person(l_count).rehire_recommendation,
4438 p_rec.rehire_recommendation,
4439 p_overwrite_data);
4440 l_copy_rec.resume_exists :=
4441 propagate_value
4442 (l_person(l_count).resume_exists,
4443 p_rec.resume_exists,
4444 p_overwrite_data);
4445 l_copy_rec.resume_last_updated :=
4446 propagate_value
4447 (l_person(l_count).resume_last_updated,
4448 p_rec.resume_last_updated,
4449 p_overwrite_data);
4450 l_copy_rec.second_passport_exists :=
4451 propagate_value
4452 (l_person(l_count).second_passport_exists,
4453 p_rec.second_passport_exists,
4454 p_overwrite_data);
4455 l_copy_rec.student_status :=
4456 propagate_value
4457 (l_person(l_count).student_status,
4458 p_rec.student_status,
4459 p_overwrite_data);
4460 l_copy_rec.suffix :=
4461 propagate_value
4462 (l_person(l_count).suffix,
4463 p_rec.suffix,
4464 p_overwrite_data);
4465 l_copy_rec.date_of_death :=
4466 propagate_value
4467 (l_person(l_count).date_of_death,
4468 p_rec.date_of_death,
4469 p_overwrite_data);
4470 l_copy_rec.uses_tobacco_flag :=
4471 propagate_value
4472 (l_person(l_count).uses_tobacco_flag,
4473 p_rec.uses_tobacco_flag,
4474 p_overwrite_data);
4475 l_copy_rec.town_of_birth :=
4476 propagate_value
4477 (l_person(l_count).town_of_birth,
4478 p_rec.town_of_birth,
4479 p_overwrite_data);
4480 l_copy_rec.region_of_birth :=
4481 propagate_value
4482 (l_person(l_count).region_of_birth,
4483 p_rec.region_of_birth,
4484 p_overwrite_data);
4485 l_copy_rec.country_of_birth :=
4486 propagate_value
4487 (l_person(l_count).country_of_birth,
4488 p_rec.country_of_birth,
4489 p_overwrite_data);
4490 l_copy_rec.fast_path_employee :=
4491 propagate_value
4492 (l_person(l_count).fast_path_employee,
4493 p_rec.fast_path_employee,
4494 p_overwrite_data);
4495 l_copy_rec.email_address := propagate_value
4496 (l_person(l_count).email_address,
4497 p_rec.email_address,
4498 p_overwrite_data);
4499 l_copy_rec.fte_capacity := propagate_value
4500 (l_person(l_count).fte_capacity,
4501 p_rec.fte_capacity,
4502 p_overwrite_data);
4503 -- Bug fix 3598173 starts here
4504 l_copy_rec.previous_last_name :=
4505 propagate_value
4506 (l_person(l_count).previous_last_name,
4507 p_rec.previous_last_name,
4508 p_overwrite_data);
4509 -- Bug fix 3598173 ends here.
4510 --
4511 open csr_get_person_details(l_person(l_count).person_id, p_rec.effective_start_date-1);
4512 fetch csr_get_person_details into l_person_rec;
4513 if csr_get_person_details%FOUND then
4514 close csr_get_person_details;
4515
4516 --hr_person.derive_full_name
4517 --(p_first_name => l_copy_rec.first_name,
4518 -- p_middle_names => l_copy_rec.middle_names,
4519 -- p_last_name => p_rec.last_name,
4520 -- p_known_as => l_copy_rec.known_as,
4521 -- p_title => l_copy_rec.title,
4522 -- p_suffix => l_copy_rec.suffix,
4523 -- p_pre_name_adjunct => l_copy_rec.pre_name_adjunct,
4524 -- p_date_of_birth => l_copy_rec.date_of_birth,
4525 -- p_person_id => l_person(l_count).person_id,
4526 -- p_business_group_id => l_person(l_count).business_group_id,
4527 -- p_full_name => l_full_name,
4528 -- p_duplicate_flag => l_duplicate_flag);
4529 --
4530 hr_person_name.derive_person_names -- #3889584
4531 (p_format_name => NULL, -- derive all person names
4532 p_business_group_id => l_person(l_count).business_group_id,
4533 p_person_id => l_person(l_count).person_id,
4534 p_first_name => l_copy_rec.first_name,
4535 p_middle_names => l_copy_rec.middle_names,
4536 p_last_name => p_rec.last_name,
4537 p_known_as => l_copy_rec.known_as,
4538 p_title => l_copy_rec.title,
4539 p_suffix => l_copy_rec.suffix,
4540 p_pre_name_adjunct => l_copy_rec.pre_name_adjunct,
4541 p_date_of_birth => l_copy_rec.date_of_birth,
4542 p_previous_last_name => l_copy_rec.previous_last_name ,
4543 p_email_address => l_copy_rec.email_address,
4544 p_employee_number => l_person_rec.employee_number ,
4545 p_applicant_number => l_person_rec.applicant_number ,
4546 p_npw_number => l_person_rec.npw_number,
4547 p_per_information1 => l_person_rec.per_information1 ,
4548 p_per_information2 => l_person_rec.per_information2 ,
4549 p_per_information3 => l_person_rec.per_information3 ,
4550 p_per_information4 => l_person_rec.per_information4 ,
4551 p_per_information5 => l_person_rec.per_information5 ,
4552 p_per_information6 => l_person_rec.per_information6 ,
4553 p_per_information7 => l_person_rec.per_information7 ,
4554 p_per_information8 => l_person_rec.per_information8 ,
4555 p_per_information9 => l_person_rec.per_information9 ,
4556 p_per_information10 => l_person_rec.per_information10 ,
4557 p_per_information11 => l_person_rec.per_information11 ,
4558 p_per_information12 => l_person_rec.per_information12 ,
4559 p_per_information13 => l_person_rec.per_information13 ,
4560 p_per_information14 => l_person_rec.per_information14 ,
4561 p_per_information15 => l_person_rec.per_information15 ,
4562 p_per_information16 => l_person_rec.per_information16 ,
4563 p_per_information17 => l_person_rec.per_information17 ,
4564 p_per_information18 => l_person_rec.per_information18 ,
4565 p_per_information19 => l_person_rec.per_information19 ,
4566 p_per_information20 => l_person_rec.per_information20 ,
4567 p_per_information21 => l_person_rec.per_information21 ,
4568 p_per_information22 => l_person_rec.per_information22 ,
4569 p_per_information23 => l_person_rec.per_information23 ,
4570 p_per_information24 => l_person_rec.per_information24 ,
4571 p_per_information25 => l_person_rec.per_information25 ,
4572 p_per_information26 => l_person_rec.per_information26 ,
4573 p_per_information27 => l_person_rec.per_information27 ,
4574 p_per_information28 => l_person_rec.per_information28 ,
4575 p_per_information29 => l_person_rec.per_information29 ,
4576 p_per_information30 => l_person_rec.per_information30 ,
4577 p_attribute1 => l_person_rec.attribute1 ,
4578 p_attribute2 => l_person_rec.attribute2 ,
4579 p_attribute3 => l_person_rec.attribute3 ,
4580 p_attribute4 => l_person_rec.attribute4 ,
4581 p_attribute5 => l_person_rec.attribute5 ,
4582 p_attribute6 => l_person_rec.attribute6 ,
4583 p_attribute7 => l_person_rec.attribute7 ,
4584 p_attribute8 => l_person_rec.attribute8 ,
4585 p_attribute9 => l_person_rec.attribute9 ,
4586 p_attribute10 => l_person_rec.attribute10 ,
4587 p_attribute11 => l_person_rec.attribute11 ,
4588 p_attribute12 => l_person_rec.attribute12 ,
4589 p_attribute13 => l_person_rec.attribute13 ,
4590 p_attribute14 => l_person_rec.attribute14 ,
4591 p_attribute15 => l_person_rec.attribute15 ,
4592 p_attribute16 => l_person_rec.attribute16 ,
4593 p_attribute17 => l_person_rec.attribute17 ,
4594 p_attribute18 => l_person_rec.attribute18 ,
4595 p_attribute19 => l_person_rec.attribute19 ,
4596 p_attribute20 => l_person_rec.attribute20 ,
4597 p_attribute21 => l_person_rec.attribute21 ,
4598 p_attribute22 => l_person_rec.attribute22 ,
4599 p_attribute23 => l_person_rec.attribute23,
4600 p_attribute24 => l_person_rec.attribute24,
4601 p_attribute25 => l_person_rec.attribute25,
4602 p_attribute26 => l_person_rec.attribute26,
4603 p_attribute27 => l_person_rec.attribute27,
4604 p_attribute28 => l_person_rec.attribute28,
4605 p_attribute29 => l_person_rec.attribute29,
4606 p_attribute30 => l_person_rec.attribute30,
4607 p_full_name => l_full_name,
4608 p_order_name => l_order_name,
4609 p_global_name => l_global_name,
4610 p_local_name => l_local_name,
4611 p_duplicate_flag => l_duplicate_flag);
4612
4613
4614 insert into per_all_people_f
4615 (person_id,
4616 effective_start_date,
4617 effective_end_date,
4618 business_group_id,
4619 person_type_id,
4620 last_name,
4621 full_name,
4622 start_date,
4623 applicant_number,
4624 comment_id,
4625 current_applicant_flag,
4626 current_emp_or_apl_flag,
4627 current_employee_flag,
4628 date_employee_data_verified,
4629 date_of_birth,
4630 email_address,
4631 employee_number,
4632 expense_check_send_to_address,
4633 first_name,
4634 known_as,
4635 marital_status,
4636 middle_names,
4637 nationality,
4638 national_identifier,
4639 previous_last_name,
4640 registered_disabled_flag,
4641 sex,
4642 title,
4643 vendor_id,
4644 request_id,
4645 program_application_id,
4646 program_id,
4647 program_update_date,
4648 attribute_category,
4649 attribute1,
4650 attribute2,
4651 attribute3,
4652 attribute4,
4653 attribute5,
4654 attribute6,
4655 attribute7,
4656 attribute8,
4657 attribute9,
4658 attribute10,
4659 attribute11,
4660 attribute12,
4661 attribute13,
4662 attribute14,
4663 attribute15,
4664 attribute16,
4665 attribute17,
4666 attribute18,
4667 attribute19,
4668 attribute20,
4669 attribute21,
4670 attribute22,
4671 attribute23,
4672 attribute24,
4673 attribute25,
4674 attribute26,
4675 attribute27,
4676 attribute28,
4677 attribute29,
4678 attribute30,
4679 per_information_category,
4680 per_information1,
4681 per_information2,
4682 per_information3,
4683 per_information4,
4684 per_information5,
4685 per_information6,
4686 per_information7,
4687 per_information8,
4688 per_information9,
4689 per_information10,
4690 per_information11,
4691 per_information12,
4692 per_information13,
4693 per_information14,
4694 per_information15,
4695 per_information16,
4696 per_information17,
4697 per_information18,
4698 per_information19,
4699 per_information20,
4700 object_version_number,
4701 suffix,
4702 DATE_OF_DEATH,
4703 BACKGROUND_CHECK_STATUS ,
4704 BACKGROUND_DATE_CHECK ,
4705 BLOOD_TYPE ,
4706 CORRESPONDENCE_LANGUAGE ,
4707 FAST_PATH_EMPLOYEE ,
4708 FTE_CAPACITY ,
4709 HOLD_APPLICANT_DATE_UNTIL ,
4710 HONORS ,
4711 INTERNAL_LOCATION ,
4712 LAST_MEDICAL_TEST_BY ,
4713 LAST_MEDICAL_TEST_DATE ,
4714 MAILSTOP ,
4715 OFFICE_NUMBER ,
4716 ON_MILITARY_SERVICE ,
4717 ORDER_NAME ,
4718 PRE_NAME_ADJUNCT ,
4719 PROJECTED_START_DATE ,
4720 REHIRE_AUTHORIZOR ,
4721 REHIRE_RECOMMENDATION ,
4722 RESUME_EXISTS ,
4723 RESUME_LAST_UPDATED ,
4724 SECOND_PASSPORT_EXISTS ,
4725 STUDENT_STATUS ,
4726 WORK_SCHEDULE ,
4727 PER_INFORMATION21 ,
4728 PER_INFORMATION22 ,
4729 PER_INFORMATION23 ,
4730 PER_INFORMATION24 ,
4731 PER_INFORMATION25 ,
4732 PER_INFORMATION26 ,
4733 PER_INFORMATION27 ,
4734 PER_INFORMATION28 ,
4735 PER_INFORMATION29 ,
4736 PER_INFORMATION30 ,
4737 REHIRE_REASON ,
4738 benefit_group_id ,
4739 receipt_of_death_cert_date ,
4740 coord_ben_med_pln_no ,
4741 coord_ben_no_cvg_flag ,
4742 COORD_BEN_MED_EXT_ER,
4743 COORD_BEN_MED_PL_NAME,
4744 COORD_BEN_MED_INSR_CRR_NAME,
4745 COORD_BEN_MED_INSR_CRR_IDENT,
4746 COORD_BEN_MED_CVG_STRT_DT,
4747 COORD_BEN_MED_CVG_END_DT,
4748 uses_tobacco_flag ,
4749 dpdnt_adoption_date ,
4750 dpdnt_vlntry_svce_flag ,
4751 original_date_of_hire ,
4752 town_of_birth ,
4753 region_of_birth ,
4754 country_of_birth ,
4755 global_person_id ,
4756 party_id ,
4757 created_by,
4758 creation_date,
4759 last_update_date,
4760 last_updated_by,
4761 last_update_login,
4762 global_name,
4763 local_name)
4764 -- ------------------------ +
4765 VALUES
4766 -- ------------------------ +
4767 (l_person(l_count).person_id,
4768 p_rec.effective_start_date,
4769 l_person(l_count).effective_end_date,
4770 l_person_rec.business_group_id,
4771 l_person_rec.person_type_id,
4772 p_rec.last_name,
4773 l_full_name,
4774 l_person_rec.start_date,
4775 l_person_rec.applicant_number,
4776 l_person_rec.comment_id,
4777 l_person_rec.current_applicant_flag,
4778 l_person_rec.current_emp_or_apl_flag,
4779 l_person_rec.current_employee_flag,
4780 l_person_rec.date_employee_data_verified,
4781 l_copy_rec.date_of_birth,
4782 l_copy_rec.email_address,
4783 l_person_rec.employee_number,
4784 l_person_rec.expense_check_send_to_address,
4785 l_copy_rec.first_name,
4786 l_copy_rec.known_as,
4787 l_copy_rec.marital_status,
4788 l_copy_rec.middle_names,
4789 l_copy_rec.nationality,
4790 l_person_rec.national_identifier,
4791 l_copy_rec.previous_last_name, -- bug fix 3598173.
4792 l_person_rec.registered_disabled_flag,
4793 l_copy_rec.sex,
4794 l_copy_rec.title,
4795 l_person_rec.vendor_id,
4796 l_person_rec.request_id,
4797 l_person_rec.program_application_id,
4798 l_person_rec.program_id,
4799 l_person_rec.program_update_date,
4800 l_person_rec.attribute_category,
4801 l_person_rec.attribute1,
4802 l_person_rec.attribute2,
4803 l_person_rec.attribute3,
4804 l_person_rec.attribute4,
4805 l_person_rec.attribute5,
4806 l_person_rec.attribute6,
4807 l_person_rec.attribute7,
4808 l_person_rec.attribute8,
4809 l_person_rec.attribute9,
4810 l_person_rec.attribute10,
4811 l_person_rec.attribute11,
4812 l_person_rec.attribute12,
4813 l_person_rec.attribute13,
4814 l_person_rec.attribute14,
4815 l_person_rec.attribute15,
4816 l_person_rec.attribute16,
4817 l_person_rec.attribute17,
4818 l_person_rec.attribute18,
4819 l_person_rec.attribute19,
4820 l_person_rec.attribute20,
4821 l_person_rec.attribute21,
4822 l_person_rec.attribute22,
4823 l_person_rec.attribute23,
4824 l_person_rec.attribute24,
4825 l_person_rec.attribute25,
4826 l_person_rec.attribute26,
4827 l_person_rec.attribute27,
4828 l_person_rec.attribute28,
4829 l_person_rec.attribute29,
4830 l_person_rec.attribute30,
4831 l_person_rec.per_information_category,
4832 l_person_rec.per_information1,
4833 l_person_rec.per_information2,
4834 l_person_rec.per_information3,
4835 l_person_rec.per_information4,
4836 l_person_rec.per_information5,
4837 l_person_rec.per_information6,
4838 l_person_rec.per_information7,
4839 l_person_rec.per_information8,
4840 l_person_rec.per_information9,
4841 l_person_rec.per_information10,
4842 l_person_rec.per_information11,
4843 l_person_rec.per_information12,
4844 l_person_rec.per_information13,
4845 l_person_rec.per_information14,
4846 l_person_rec.per_information15,
4847 l_person_rec.per_information16,
4848 l_person_rec.per_information17,
4849 l_person_rec.per_information18,
4850 l_person_rec.per_information19,
4851 l_person_rec.per_information20,
4852 l_person(l_count).object_version_number,
4853 l_copy_rec.suffix,
4854 l_copy_rec.DATE_OF_DEATH,
4855 l_person_rec.BACKGROUND_CHECK_STATUS ,
4856 l_person_rec.BACKGROUND_DATE_CHECK ,
4857 l_copy_rec.BLOOD_TYPE,
4858 l_copy_rec.CORRESPONDENCE_LANGUAGE,
4859 l_copy_rec.FAST_PATH_EMPLOYEE,
4860 l_copy_rec.FTE_CAPACITY,
4861 l_person_rec.HOLD_APPLICANT_DATE_UNTIL ,
4862 l_copy_rec.HONORS,
4863 l_person_rec.INTERNAL_LOCATION ,
4864 l_person_rec.LAST_MEDICAL_TEST_BY ,
4865 l_person_rec.LAST_MEDICAL_TEST_DATE ,
4866 l_person_rec.MAILSTOP ,
4867 l_person_rec.OFFICE_NUMBER ,
4868 l_person_rec.ON_MILITARY_SERVICE ,
4869 l_ORDER_NAME ,
4870 l_copy_rec.PRE_NAME_ADJUNCT,
4871 l_person_rec.PROJECTED_START_DATE ,
4872 l_copy_rec.REHIRE_AUTHORIZOR,
4873 l_copy_rec.REHIRE_RECOMMENDATION,
4874 l_copy_rec.RESUME_EXISTS,
4875 l_copy_rec.RESUME_LAST_UPDATED,
4876 l_copy_rec.SECOND_PASSPORT_EXISTS,
4877 l_copy_rec.STUDENT_STATUS,
4878 l_person_rec.WORK_SCHEDULE ,
4879 l_person_rec.per_iNFORMATION21 ,
4880 l_person_rec.per_iNFORMATION22 ,
4881 l_person_rec.per_iNFORMATION23 ,
4882 l_person_rec.per_iNFORMATION24 ,
4883 l_person_rec.per_iNFORMATION25 ,
4884 l_person_rec.per_iNFORMATION26 ,
4885 l_person_rec.per_iNFORMATION27 ,
4886 l_person_rec.per_iNFORMATION28 ,
4887 l_person_rec.per_iNFORMATION29 ,
4888 l_person_rec.per_iNFORMATION30 ,
4889 l_person_rec.REHIRE_REASON ,
4890 l_person_rec.BENEFIT_GROUP_ID ,
4891 l_person_rec.RECEIPT_OF_DEATH_CERT_DATE ,
4892 l_person_rec.COORD_BEN_MED_PLN_NO ,
4893 l_person_rec.COORD_BEN_NO_CVG_FLAG ,
4894 l_person_rec.COORD_BEN_MED_EXT_ER,
4895 l_person_rec.COORD_BEN_MED_PL_NAME,
4896 l_person_rec.COORD_BEN_MED_INSR_CRR_NAME,
4897 l_person_rec.COORD_BEN_MED_INSR_CRR_IDENT,
4898 l_person_rec.COORD_BEN_MED_CVG_STRT_DT,
4899 l_person_rec.COORD_BEN_MED_CVG_END_DT ,
4900 l_copy_rec.USES_TOBACCO_FLAG,
4901 l_person_rec.DPDNT_ADOPTION_DATE ,
4902 l_person_rec.DPDNT_VLNTRY_SVCE_FLAG ,
4903 l_person_rec.ORIGINAL_DATE_OF_HIRE ,
4904 l_copy_rec.town_of_birth,
4905 l_copy_rec.region_of_birth,
4906 l_copy_rec.country_of_birth,
4907 l_person_rec.global_person_id ,
4908 l_person_rec.party_id ,
4909 l_person_rec.created_by,
4910 l_person_rec.creation_date,
4911 sysdate,
4912 fnd_global.user_id,
4913 fnd_global.login_id,
4914 l_global_name,
4915 l_local_name);
4916 else
4917 close csr_get_person_details;
4918 end if;
4919 end if;
4920 /*
4921 hr_person_api.update_person
4922 (p_effective_date => p_rec.effective_start_date,
4923 p_datetrack_update_mode => l_datetrack_mode,
4924 p_person_id => l_person(l_count).person_id,
4925 p_object_version_number => l_person(l_count).object_version_number,
4926 p_employee_number => l_person(l_count).employee_number,
4927 p_last_name => p_rec.last_name,
4928 p_date_of_birth => p_rec.date_of_birth,
4929 p_first_name => p_rec.first_name,
4930 p_known_as => p_rec.known_as,
4931 p_marital_status => p_rec.marital_status,
4932 p_middle_names => p_rec.middle_names,
4933 p_nationality => p_rec.nationality,
4934 p_sex => p_rec.sex,
4935 p_title => p_rec.title,
4936 p_blood_type => p_rec.blood_type,
4937 p_correspondence_language => p_rec.correspondence_language,
4938 p_honors => p_rec.honors,
4939 p_pre_name_adjunct => p_rec.pre_name_adjunct,
4940 p_rehire_authorizor => p_rec.rehire_authorizor,
4941 p_rehire_recommendation => p_rec.rehire_recommendation,
4942 p_resume_exists => p_rec.resume_exists,
4943 p_resume_last_updated => p_rec.resume_last_updated,
4944 p_second_passport_exists => p_rec.second_passport_exists,
4945 p_student_status => p_rec.student_status,
4946 p_suffix => p_rec.suffix,
4947 p_date_of_death => p_rec.date_of_death,
4948 p_uses_tobacco_flag => p_rec.uses_tobacco_flag,
4949 p_town_of_birth => p_rec.town_of_birth,
4950 p_region_of_birth => p_rec.region_of_birth,
4951 p_country_of_birth => p_rec.country_of_birth,
4952 p_fast_path_employee => p_rec.fast_path_employee,
4953 p_email_address => p_rec.email_address,
4954 p_fte_capacity => p_rec.fte_capacity,
4955 p_effective_start_date => l_effective_start_date,
4956 p_effective_end_date => l_effective_end_date,
4957 p_full_name => l_full_name,
4958 p_comment_id => l_comment_id,
4959 p_name_combination_warning => l_name_combination_warning,
4960 p_assign_payroll_warning => l_assign_payroll_warning,
4961 p_orig_hire_warning => l_orig_hire_warning);
4962 */
4963 --
4964 if l_person(l_count).effective_start_date >= p_rec.effective_start_date and
4965 l_person(l_count).effective_end_date > p_rec.effective_end_date or
4966 l_person(l_count).effective_start_date < p_rec.effective_start_date and
4967 l_person(l_count).effective_end_date > p_rec.effective_end_date then
4968 --
4969 hr_utility.set_location('finding dt delete modes',10);
4970 --
4971 dt_api.find_dt_upd_modes
4972 (p_effective_date => p_rec.effective_end_date,
4973 p_base_table_name => 'PER_ALL_PEOPLE_F',
4974 p_base_key_column => 'PERSON_ID',
4975 p_base_key_value => l_person(l_count).person_id,
4976 p_correction => l_correction,
4977 p_update => l_update,
4978 p_update_override => l_update_override,
4979 p_update_change_insert => l_update_change_insert);
4980 --
4981 -- Put old values back to what they were.
4982 --
4983 if l_update_change_insert then
4984 --
4985 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
4986 --
4987 elsif l_update then
4988 --
4989 l_datetrack_mode := 'UPDATE';
4990 --
4991 else
4992 --
4993 l_datetrack_mode := 'CORRECTION';
4994 --
4995 end if;
4996 --
4997 -- Now call the API with the appropriate calling mode.
4998 --
4999 if l_datetrack_mode = 'UPDATE' then
5000 --
5001 hr_utility.set_location('updating person in update mode',10);
5002 --
5003 update per_all_people_f
5004 set effective_end_date = p_rec.effective_end_date
5005 where person_id = l_person(l_count).person_id
5006 and p_rec.effective_start_date
5007 between effective_start_date
5008 and effective_end_date;
5009 --
5010 hr_utility.set_location('getting max ovn in update mode',10);
5011 --
5012 l_person(l_count).object_version_number :=
5013 dt_api.get_object_version_number
5014 (p_base_table_name => 'per_all_people_f',
5015 p_base_key_column => 'person_id',
5016 p_base_key_value => l_person(l_count).person_id);
5017 --
5018 hr_utility.set_location('inserting record in update mode',10);
5019 --
5020 -- Now the insert
5021 --
5022 insert into per_all_people_f
5023 (person_id,
5024 effective_start_date,
5025 effective_end_date,
5026 business_group_id,
5027 person_type_id,
5028 last_name,
5029 start_date,
5030 applicant_number,
5031 comment_id,
5032 current_applicant_flag,
5033 current_emp_or_apl_flag,
5034 current_employee_flag,
5035 date_employee_data_verified,
5036 date_of_birth,
5037 email_address,
5038 employee_number,
5039 expense_check_send_to_address,
5040 first_name,
5041 full_name,
5042 known_as,
5043 marital_status,
5044 middle_names,
5045 nationality,
5046 national_identifier,
5047 previous_last_name,
5048 registered_disabled_flag,
5049 sex,
5050 title,
5051 vendor_id,
5052 request_id,
5053 program_application_id,
5054 program_id,
5055 program_update_date,
5056 attribute_category,
5057 attribute1,
5058 attribute2,
5059 attribute3,
5060 attribute4,
5061 attribute5,
5062 attribute6,
5063 attribute7,
5064 attribute8,
5065 attribute9,
5066 attribute10,
5067 attribute11,
5068 attribute12,
5069 attribute13,
5070 attribute14,
5071 attribute15,
5072 attribute16,
5073 attribute17,
5074 attribute18,
5075 attribute19,
5076 attribute20,
5077 attribute21,
5078 attribute22,
5079 attribute23,
5080 attribute24,
5081 attribute25,
5082 attribute26,
5083 attribute27,
5084 attribute28,
5085 attribute29,
5086 attribute30,
5087 per_information_category,
5088 per_information1,
5089 per_information2,
5090 per_information3,
5091 per_information4,
5092 per_information5,
5093 per_information6,
5094 per_information7,
5095 per_information8,
5096 per_information9,
5097 per_information10,
5098 per_information11,
5099 per_information12,
5100 per_information13,
5101 per_information14,
5102 per_information15,
5103 per_information16,
5104 per_information17,
5105 per_information18,
5106 per_information19,
5107 per_information20,
5108 object_version_number,
5109 suffix,
5110 DATE_OF_DEATH,
5111 BACKGROUND_CHECK_STATUS ,
5112 BACKGROUND_DATE_CHECK ,
5113 BLOOD_TYPE ,
5114 CORRESPONDENCE_LANGUAGE ,
5115 FAST_PATH_EMPLOYEE ,
5116 FTE_CAPACITY ,
5117 HOLD_APPLICANT_DATE_UNTIL ,
5118 HONORS ,
5119 INTERNAL_LOCATION ,
5120 LAST_MEDICAL_TEST_BY ,
5121 LAST_MEDICAL_TEST_DATE ,
5122 MAILSTOP ,
5123 OFFICE_NUMBER ,
5124 ON_MILITARY_SERVICE ,
5125 ORDER_NAME ,
5126 PRE_NAME_ADJUNCT ,
5127 PROJECTED_START_DATE ,
5128 REHIRE_AUTHORIZOR ,
5129 REHIRE_RECOMMENDATION ,
5130 RESUME_EXISTS ,
5131 RESUME_LAST_UPDATED ,
5132 SECOND_PASSPORT_EXISTS ,
5133 STUDENT_STATUS ,
5134 WORK_SCHEDULE ,
5135 PER_INFORMATION21 ,
5136 PER_INFORMATION22 ,
5137 PER_INFORMATION23 ,
5138 PER_INFORMATION24 ,
5139 PER_INFORMATION25 ,
5140 PER_INFORMATION26 ,
5141 PER_INFORMATION27 ,
5142 PER_INFORMATION28 ,
5143 PER_INFORMATION29 ,
5144 PER_INFORMATION30 ,
5145 REHIRE_REASON ,
5146 benefit_group_id ,
5147 receipt_of_death_cert_date ,
5148 coord_ben_med_pln_no ,
5149 coord_ben_no_cvg_flag ,
5150 COORD_BEN_MED_EXT_ER,
5151 COORD_BEN_MED_PL_NAME,
5152 COORD_BEN_MED_INSR_CRR_NAME,
5153 COORD_BEN_MED_INSR_CRR_IDENT,
5154 COORD_BEN_MED_CVG_STRT_DT,
5155 COORD_BEN_MED_CVG_END_DT,
5156 uses_tobacco_flag ,
5157 dpdnt_adoption_date ,
5158 dpdnt_vlntry_svce_flag ,
5159 original_date_of_hire ,
5160 town_of_birth ,
5161 region_of_birth ,
5162 country_of_birth ,
5163 global_person_id ,
5164 party_id ,
5165 created_by,
5166 creation_date,
5167 last_update_date,
5168 last_updated_by,
5169 last_update_login,
5170 global_name,
5171 local_name)
5172 select
5173 l_person(l_count).person_id,
5174 p_rec.effective_end_date+1,
5175 hr_api.g_eot,
5176 business_group_id,
5177 person_type_id,
5178 p_rec.last_name,
5179 start_date,
5180 applicant_number,
5181 comment_id,
5182 current_applicant_flag,
5183 current_emp_or_apl_flag,
5184 current_employee_flag,
5185 date_employee_data_verified,
5186 p_rec.date_of_birth,
5187 p_rec.email_address,
5188 employee_number,
5189 expense_check_send_to_address,
5190 p_rec.first_name,
5191 full_name,
5192 p_rec.known_as,
5193 p_rec.marital_status,
5194 p_rec.middle_names,
5195 p_rec.nationality,
5196 national_identifier,
5197 p_rec.previous_last_name, -- Bug fix 3598173.
5198 registered_disabled_flag,
5199 p_rec.sex,
5200 p_rec.title,
5201 vendor_id,
5202 request_id,
5203 program_application_id,
5204 program_id,
5205 program_update_date,
5206 attribute_category,
5207 attribute1,
5208 attribute2,
5209 attribute3,
5210 attribute4,
5211 attribute5,
5212 attribute6,
5213 attribute7,
5214 attribute8,
5215 attribute9,
5216 attribute10,
5217 attribute11,
5218 attribute12,
5219 attribute13,
5220 attribute14,
5221 attribute15,
5222 attribute16,
5223 attribute17,
5224 attribute18,
5225 attribute19,
5226 attribute20,
5227 attribute21,
5228 attribute22,
5229 attribute23,
5230 attribute24,
5231 attribute25,
5232 attribute26,
5233 attribute27,
5234 attribute28,
5235 attribute29,
5236 attribute30,
5237 per_information_category,
5238 per_information1,
5239 per_information2,
5240 per_information3,
5241 per_information4,
5242 per_information5,
5243 per_information6,
5244 per_information7,
5245 per_information8,
5246 per_information9,
5247 per_information10,
5248 per_information11,
5249 per_information12,
5250 per_information13,
5251 per_information14,
5252 per_information15,
5253 per_information16,
5254 per_information17,
5255 per_information18,
5256 per_information19,
5257 per_information20,
5258 l_person(l_count).object_version_number,
5259 p_rec.suffix,
5260 p_rec.DATE_OF_DEATH ,
5261 BACKGROUND_CHECK_STATUS ,
5262 BACKGROUND_DATE_CHECK ,
5263 p_rec.BLOOD_TYPE ,
5264 p_rec.CORRESPONDENCE_LANGUAGE ,
5265 p_rec.FAST_PATH_EMPLOYEE ,
5266 p_rec.FTE_CAPACITY ,
5267 HOLD_APPLICANT_DATE_UNTIL ,
5268 p_rec.HONORS ,
5269 INTERNAL_LOCATION ,
5270 LAST_MEDICAL_TEST_BY ,
5271 LAST_MEDICAL_TEST_DATE ,
5272 MAILSTOP ,
5273 OFFICE_NUMBER ,
5274 ON_MILITARY_SERVICE ,
5275 ORDER_NAME ,
5276 p_rec.PRE_NAME_ADJUNCT ,
5277 PROJECTED_START_DATE ,
5278 p_rec.REHIRE_AUTHORIZOR ,
5279 p_rec.REHIRE_RECOMMENDATION ,
5280 p_rec.RESUME_EXISTS ,
5281 p_rec.RESUME_LAST_UPDATED ,
5282 p_rec.SECOND_PASSPORT_EXISTS ,
5283 p_rec.STUDENT_STATUS ,
5284 WORK_SCHEDULE ,
5285 PER_INFORMATION21 ,
5286 PER_INFORMATION22 ,
5287 PER_INFORMATION23 ,
5288 PER_INFORMATION24 ,
5289 PER_INFORMATION25 ,
5290 PER_INFORMATION26 ,
5291 PER_INFORMATION27 ,
5292 PER_INFORMATION28 ,
5293 PER_INFORMATION29 ,
5294 PER_INFORMATION30 ,
5295 REHIRE_REASON ,
5296 BENEFIT_GROUP_ID ,
5297 RECEIPT_OF_DEATH_CERT_DATE ,
5298 COORD_BEN_MED_PLN_NO ,
5299 COORD_BEN_NO_CVG_FLAG ,
5300 COORD_BEN_MED_EXT_ER,
5301 COORD_BEN_MED_PL_NAME,
5302 COORD_BEN_MED_INSR_CRR_NAME,
5303 COORD_BEN_MED_INSR_CRR_IDENT,
5304 COORD_BEN_MED_CVG_STRT_DT,
5305 COORD_BEN_MED_CVG_END_DT ,
5306 p_rec.USES_TOBACCO_FLAG ,
5307 DPDNT_ADOPTION_DATE ,
5308 DPDNT_VLNTRY_SVCE_FLAG ,
5309 ORIGINAL_DATE_OF_HIRE ,
5310 p_rec.town_of_birth ,
5311 p_rec.region_of_birth ,
5312 p_rec.country_of_birth ,
5313 global_person_id ,
5314 party_id ,
5315 created_by,
5316 creation_date,
5317 sysdate,
5318 fnd_global.user_id,
5319 fnd_global.login_id,
5320 global_name,
5321 local_name
5322 from per_all_people_f
5323 where person_id = l_person(l_count).person_id
5324 and p_rec.effective_start_date-1
5325 between effective_start_date
5326 and effective_end_date;
5327 --
5328 elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
5329 --
5330 hr_utility.set_location('updating record in update change insert mode',10);
5331 --
5332 update per_all_people_f
5333 set effective_end_date = p_rec.effective_end_date
5334 where person_id = l_person(l_count).person_id
5335 and p_rec.effective_start_date
5336 between effective_start_date
5337 and effective_end_date;
5338 --
5339 hr_utility.set_location('getting max ovn in update change insert mode',10);
5340 --
5341 l_person(l_count).object_version_number :=
5342 dt_api.get_object_version_number
5343 (p_base_table_name => 'per_all_people_f',
5344 p_base_key_column => 'person_id',
5345 p_base_key_value => l_person(l_count).person_id);
5346 --
5347 -- Now the insert
5348 --
5349 hr_utility.set_location('inserting record in update change insert mode',10);
5350 --
5351 insert into per_all_people_f
5352 (person_id,
5353 effective_start_date,
5354 effective_end_date,
5355 business_group_id,
5356 person_type_id,
5357 last_name,
5358 start_date,
5359 applicant_number,
5360 comment_id,
5361 current_applicant_flag,
5362 current_emp_or_apl_flag,
5363 current_employee_flag,
5364 date_employee_data_verified,
5365 date_of_birth,
5366 email_address,
5367 employee_number,
5368 expense_check_send_to_address,
5369 first_name,
5370 full_name,
5371 known_as,
5372 marital_status,
5373 middle_names,
5374 nationality,
5375 national_identifier,
5376 previous_last_name,
5377 registered_disabled_flag,
5378 sex,
5379 title,
5380 vendor_id,
5381 request_id,
5382 program_application_id,
5383 program_id,
5384 program_update_date,
5385 attribute_category,
5386 attribute1,
5387 attribute2,
5388 attribute3,
5389 attribute4,
5390 attribute5,
5391 attribute6,
5392 attribute7,
5393 attribute8,
5394 attribute9,
5395 attribute10,
5396 attribute11,
5397 attribute12,
5398 attribute13,
5399 attribute14,
5400 attribute15,
5401 attribute16,
5402 attribute17,
5403 attribute18,
5404 attribute19,
5405 attribute20,
5406 attribute21,
5407 attribute22,
5408 attribute23,
5409 attribute24,
5410 attribute25,
5411 attribute26,
5412 attribute27,
5413 attribute28,
5414 attribute29,
5415 attribute30,
5416 per_information_category,
5417 per_information1,
5418 per_information2,
5419 per_information3,
5420 per_information4,
5421 per_information5,
5422 per_information6,
5423 per_information7,
5424 per_information8,
5425 per_information9,
5426 per_information10,
5427 per_information11,
5428 per_information12,
5429 per_information13,
5430 per_information14,
5431 per_information15,
5432 per_information16,
5433 per_information17,
5434 per_information18,
5435 per_information19,
5436 per_information20,
5437 object_version_number,
5438 suffix,
5439 DATE_OF_DEATH,
5440 BACKGROUND_CHECK_STATUS ,
5441 BACKGROUND_DATE_CHECK ,
5442 BLOOD_TYPE ,
5443 CORRESPONDENCE_LANGUAGE ,
5444 FAST_PATH_EMPLOYEE ,
5445 FTE_CAPACITY ,
5446 HOLD_APPLICANT_DATE_UNTIL ,
5447 HONORS ,
5448 INTERNAL_LOCATION ,
5449 LAST_MEDICAL_TEST_BY ,
5450 LAST_MEDICAL_TEST_DATE ,
5451 MAILSTOP ,
5452 OFFICE_NUMBER ,
5453 ON_MILITARY_SERVICE ,
5454 ORDER_NAME ,
5455 PRE_NAME_ADJUNCT ,
5456 PROJECTED_START_DATE ,
5457 REHIRE_AUTHORIZOR ,
5458 REHIRE_RECOMMENDATION ,
5459 RESUME_EXISTS ,
5460 RESUME_LAST_UPDATED ,
5461 SECOND_PASSPORT_EXISTS ,
5462 STUDENT_STATUS ,
5463 WORK_SCHEDULE ,
5464 PER_INFORMATION21 ,
5465 PER_INFORMATION22 ,
5466 PER_INFORMATION23 ,
5467 PER_INFORMATION24 ,
5468 PER_INFORMATION25 ,
5469 PER_INFORMATION26 ,
5470 PER_INFORMATION27 ,
5471 PER_INFORMATION28 ,
5472 PER_INFORMATION29 ,
5473 PER_INFORMATION30 ,
5474 REHIRE_REASON ,
5475 benefit_group_id ,
5476 receipt_of_death_cert_date ,
5477 coord_ben_med_pln_no ,
5478 coord_ben_no_cvg_flag ,
5479 COORD_BEN_MED_EXT_ER,
5480 COORD_BEN_MED_PL_NAME,
5481 COORD_BEN_MED_INSR_CRR_NAME,
5482 COORD_BEN_MED_INSR_CRR_IDENT,
5483 COORD_BEN_MED_CVG_STRT_DT,
5484 COORD_BEN_MED_CVG_END_DT,
5485 uses_tobacco_flag ,
5486 dpdnt_adoption_date ,
5487 dpdnt_vlntry_svce_flag ,
5488 original_date_of_hire ,
5489 town_of_birth ,
5490 region_of_birth ,
5491 country_of_birth ,
5492 global_person_id ,
5493 party_id ,
5494 created_by,
5495 creation_date,
5496 last_update_date,
5497 last_updated_by,
5498 last_update_login,
5499 global_name,
5500 local_name)
5501 select
5502 l_person(l_count).person_id,
5503 p_rec.effective_end_date+1,
5504 l_person(l_count).effective_end_date,
5505 business_group_id,
5506 person_type_id,
5507 p_rec.last_name,
5508 start_date,
5509 applicant_number,
5510 comment_id,
5511 current_applicant_flag,
5512 current_emp_or_apl_flag,
5513 current_employee_flag,
5514 date_employee_data_verified,
5515 p_rec.date_of_birth,
5516 p_rec.email_address,
5517 employee_number,
5518 expense_check_send_to_address,
5519 p_rec.first_name,
5520 full_name,
5521 p_rec.known_as,
5522 p_rec.marital_status,
5523 p_rec.middle_names,
5524 p_rec.nationality,
5525 national_identifier,
5526 p_rec.previous_last_name, -- bug fix 3598173
5527 registered_disabled_flag,
5528 p_rec.sex,
5529 p_rec.title,
5530 vendor_id,
5531 request_id,
5532 program_application_id,
5533 program_id,
5534 program_update_date,
5535 attribute_category,
5536 attribute1,
5537 attribute2,
5538 attribute3,
5539 attribute4,
5540 attribute5,
5541 attribute6,
5542 attribute7,
5543 attribute8,
5544 attribute9,
5545 attribute10,
5546 attribute11,
5547 attribute12,
5548 attribute13,
5549 attribute14,
5550 attribute15,
5551 attribute16,
5552 attribute17,
5553 attribute18,
5554 attribute19,
5555 attribute20,
5556 attribute21,
5557 attribute22,
5558 attribute23,
5559 attribute24,
5560 attribute25,
5561 attribute26,
5562 attribute27,
5563 attribute28,
5564 attribute29,
5565 attribute30,
5566 per_information_category,
5567 per_information1,
5568 per_information2,
5569 per_information3,
5570 per_information4,
5571 per_information5,
5572 per_information6,
5573 per_information7,
5574 per_information8,
5575 per_information9,
5576 per_information10,
5577 per_information11,
5578 per_information12,
5579 per_information13,
5580 per_information14,
5581 per_information15,
5582 per_information16,
5583 per_information17,
5584 per_information18,
5585 per_information19,
5586 per_information20,
5587 l_person(l_count).object_version_number,
5588 p_rec.suffix,
5589 p_rec.DATE_OF_DEATH ,
5590 BACKGROUND_CHECK_STATUS ,
5591 BACKGROUND_DATE_CHECK ,
5592 p_rec.BLOOD_TYPE ,
5593 p_rec.CORRESPONDENCE_LANGUAGE ,
5594 p_rec.FAST_PATH_EMPLOYEE ,
5595 p_rec.FTE_CAPACITY ,
5596 HOLD_APPLICANT_DATE_UNTIL ,
5597 p_rec.HONORS ,
5598 INTERNAL_LOCATION ,
5599 LAST_MEDICAL_TEST_BY ,
5600 LAST_MEDICAL_TEST_DATE ,
5601 MAILSTOP ,
5602 OFFICE_NUMBER ,
5603 ON_MILITARY_SERVICE ,
5604 ORDER_NAME ,
5605 p_rec.PRE_NAME_ADJUNCT ,
5606 PROJECTED_START_DATE ,
5607 p_rec.REHIRE_AUTHORIZOR ,
5608 p_rec.REHIRE_RECOMMENDATION ,
5609 p_rec.RESUME_EXISTS ,
5610 p_rec.RESUME_LAST_UPDATED ,
5611 p_rec.SECOND_PASSPORT_EXISTS ,
5612 p_rec.STUDENT_STATUS ,
5613 WORK_SCHEDULE ,
5614 PER_INFORMATION21 ,
5615 PER_INFORMATION22 ,
5616 PER_INFORMATION23 ,
5617 PER_INFORMATION24 ,
5618 PER_INFORMATION25 ,
5619 PER_INFORMATION26 ,
5620 PER_INFORMATION27 ,
5621 PER_INFORMATION28 ,
5622 PER_INFORMATION29 ,
5623 PER_INFORMATION30 ,
5624 REHIRE_REASON ,
5625 BENEFIT_GROUP_ID ,
5626 RECEIPT_OF_DEATH_CERT_DATE ,
5627 COORD_BEN_MED_PLN_NO ,
5628 COORD_BEN_NO_CVG_FLAG ,
5629 COORD_BEN_MED_EXT_ER,
5630 COORD_BEN_MED_PL_NAME,
5631 COORD_BEN_MED_INSR_CRR_NAME,
5632 COORD_BEN_MED_INSR_CRR_IDENT,
5633 COORD_BEN_MED_CVG_STRT_DT,
5634 COORD_BEN_MED_CVG_END_DT ,
5635 p_rec.USES_TOBACCO_FLAG ,
5636 DPDNT_ADOPTION_DATE ,
5637 DPDNT_VLNTRY_SVCE_FLAG ,
5638 ORIGINAL_DATE_OF_HIRE ,
5639 p_rec.town_of_birth ,
5640 p_rec.region_of_birth ,
5641 p_rec.country_of_birth ,
5642 global_person_id ,
5643 party_id ,
5644 created_by,
5645 creation_date,
5646 sysdate,
5647 fnd_global.user_id,
5648 fnd_global.login_id,
5649 global_name,
5650 local_name
5651 from per_all_people_f
5652 where person_id = l_person(l_count).person_id
5653 and p_rec.effective_start_date-1
5654 between effective_start_date
5655 and effective_end_date;
5656 --
5657 end if;
5658 /*
5659 hr_person_api.update_person
5660 (p_effective_date => p_rec.effective_end_date,
5661 p_datetrack_update_mode => l_datetrack_mode,
5662 p_person_id => l_person(l_count).person_id,
5663 p_object_version_number => l_person(l_count).object_version_number,
5664 p_employee_number => l_person(l_count).employee_number,
5665 p_last_name => l_person(l_count).last_name,
5666 p_date_of_birth => l_person(l_count).date_of_birth,
5667 p_first_name => l_person(l_count).first_name,
5668 p_known_as => l_person(l_count).known_as,
5669 p_marital_status => l_person(l_count).marital_status,
5670 p_middle_names => l_person(l_count).middle_names,
5671 p_nationality => l_person(l_count).nationality,
5672 p_sex => l_person(l_count).sex,
5673 p_title => l_person(l_count).title,
5674 p_blood_type => l_person(l_count).blood_type,
5675 p_correspondence_language => l_person(l_count).correspondence_language,
5676 p_honors => l_person(l_count).honors,
5677 p_pre_name_adjunct => l_person(l_count).pre_name_adjunct,
5678 p_rehire_authorizor => l_person(l_count).rehire_authorizor,
5679 p_rehire_recommendation => l_person(l_count).rehire_recommendation,
5680 p_resume_exists => l_person(l_count).resume_exists,
5681 p_resume_last_updated => l_person(l_count).resume_last_updated,
5682 p_second_passport_exists => l_person(l_count).second_passport_exists,
5683 p_student_status => l_person(l_count).student_status,
5684 p_suffix => l_person(l_count).suffix,
5685 p_date_of_death => l_person(l_count).date_of_death,
5686 p_uses_tobacco_flag => l_person(l_count).uses_tobacco_flag,
5687 p_town_of_birth => l_person(l_count).town_of_birth,
5688 p_region_of_birth => l_person(l_count).region_of_birth,
5689 p_country_of_birth => l_person(l_count).country_of_birth,
5690 p_fast_path_employee => l_person(l_count).fast_path_employee,
5691 p_email_address => l_person(l_count).email_address,
5692 p_fte_capacity => l_person(l_count).fte_capacity,
5693 p_effective_start_date => l_effective_start_date,
5694 p_effective_end_date => l_effective_end_date,
5695 p_full_name => l_full_name,
5696 p_comment_id => l_comment_id,
5697 p_name_combination_warning => l_name_combination_warning,
5698 p_assign_payroll_warning => l_assign_payroll_warning,
5699 p_orig_hire_warning => l_orig_hire_warning);
5700 */
5701 --
5702 end if;
5703 --
5704 end if;
5705 --
5706 l_last_bg_id := l_person(l_count).business_group_id;
5707 --
5708 end loop;
5709 --
5710 hr_utility.set_location('Leaving '||l_proc,10);
5711 --
5712 end;
5713 --
5714 --
5715 -- ------------------------------------------------------------------------------
5716 -- |------------------------------< per_party_merge >---------------------------|
5717 -- ------------------------------------------------------------------------------
5718 --
5719 procedure per_party_merge
5720 (p_entity_name in varchar2,
5721 p_from_id in number,
5722 p_to_id out nocopy number,
5723 p_from_fk_id in number,
5724 p_to_fk_id in number,
5725 p_parent_entity_name in varchar2,
5726 p_batch_id in number,
5727 p_batch_party_id in number,
5728 p_return_status out nocopy varchar2) is
5729 --
5730 l_proc varchar2(80) := g_package||'.per_party_merge';
5731 --
5732 cursor c_person is
5733 select ppf.*
5734 from per_all_people_f ppf
5735 where ppf.party_id = p_from_fk_id
5736 order by ppf.effective_start_date;
5737 --
5738 l_person c_person%rowtype;
5739 l_effective_start_date date;
5740 l_effective_end_date date;
5741 l_full_name varchar2(255);
5742 l_comment_id number;
5743 l_name_combination_warning boolean;
5744 l_assign_payroll_warning boolean;
5745 l_orig_hire_warning boolean;
5746 --
5747 begin
5748 --
5749 hr_utility.set_location('Entering '||l_proc,10);
5750 --
5751 p_return_status := FND_API.G_RET_STS_SUCCESS;
5752 --
5753 -- This routine must select all the person record information and
5754 -- child information and update the party accordingly.
5755 --
5756 -- The code will first update all the person records for a person
5757 -- and then update all the child tables for that person.
5758 --
5759 g_count := 100;
5760 --
5761 open c_person;
5762 --
5763 loop
5764 --
5765 fetch c_person into l_person;
5766 exit when c_person%notfound;
5767 --
5768 l_person.party_id := p_to_fk_id;
5769 --
5770 -- Update the person record.
5771 --
5772 /*
5773 hr_person_api.update_person
5774 (p_effective_date => l_person.effective_start_date,
5775 p_datetrack_update_mode => 'CORRECTION',
5776 p_person_id => l_person.person_id,
5777 p_object_version_number => l_person.object_version_number,
5778 p_employee_number => l_person.employee_number,
5779 p_party_id => l_person.party_id,
5780 p_effective_start_date => l_effective_start_date,
5781 p_effective_end_date => l_effective_end_date,
5782 p_full_name => l_full_name,
5783 p_comment_id => l_comment_id,
5784 p_name_combination_warning => l_name_combination_warning,
5785 p_assign_payroll_warning => l_assign_payroll_warning,
5786 p_orig_hire_warning => l_orig_hire_warning);
5787 --
5788 -- Update all the child table records.
5789 --
5790 */
5791 update per_all_people_f
5792 set party_id = l_person.party_id
5793 where person_id = l_person.person_id
5794 and effective_start_date = l_person.effective_start_date;
5795 --
5796 if l_person.effective_end_date = hr_api.g_eot then
5797 --
5798 update_child_tables(p_rec => l_person);
5799 --
5800 end if;
5801 --
5802 end loop;
5803 --
5804 close c_person;
5805 --
5806 g_count := 0;
5807 --
5808 hr_utility.set_location('Leaving '||l_proc,10);
5809 --
5810 exception
5811 when others then
5812 p_return_status := 'F';
5813 end;
5814 --
5815 --
5816 -- ----------------------------------------------------------------------------
5817 -- |----------------------------< get_party_details >-------------------------|
5818 -- ----------------------------------------------------------------------------
5819 function get_party_details
5820 (p_party_id in number,
5821 p_effective_date in date) return per_per_shd.g_rec_type is
5822 --
5823 cursor c1 is
5824 select *
5825 from per_all_people_f
5826 where party_id = p_party_id
5827 and p_effective_date
5828 between effective_start_date
5829 and effective_end_date;
5830 --
5831 l_c1 c1%rowtype;
5832 l_rec per_per_shd.g_rec_type;
5833 --
5834 begin
5835 --
5836 if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
5837 --
5838 return l_rec;
5839 --
5840 end if;
5841 --
5842 -- Just get the first record regardless.
5843 --
5844 open c1;
5845 --
5846 fetch c1 into l_c1;
5847 --
5848 -- Assigining the fields to the records type
5849 if c1%found then
5850 l_rec.first_name := l_c1.first_name;
5851 l_rec.sex := l_c1.sex;
5852 l_rec.title := l_c1.title;
5853 l_rec.date_of_birth := l_c1.date_of_birth;
5854 l_rec.date_of_death := l_c1.date_of_death;
5855 l_rec.known_as := l_c1.known_as;
5856 l_rec.marital_status := l_c1.marital_status;
5857 l_rec.middle_names := l_c1.middle_names;
5858 l_rec.nationality := l_c1.nationality;
5859 l_rec.blood_type := l_c1.blood_type;
5860 l_rec.correspondence_language := l_c1.correspondence_language;
5861 l_rec.honors := l_c1.honors;
5862 l_rec.pre_name_adjunct := l_c1.pre_name_adjunct;
5863 l_rec.rehire_authorizor := l_c1.rehire_authorizor;
5864 l_rec.rehire_recommendation := l_c1.rehire_recommendation;
5865 l_rec.resume_exists := l_c1.resume_exists;
5866 l_rec.resume_last_updated := l_c1.resume_last_updated;
5867 l_rec.second_passport_exists := l_c1.second_passport_exists;
5868 l_rec.student_status := l_c1.student_status;
5869 l_rec.suffix := l_c1.suffix;
5870 l_rec.uses_tobacco_flag := l_c1.uses_tobacco_flag;
5871 l_rec.town_of_birth := l_c1.town_of_birth;
5872 l_rec.region_of_birth := l_c1.region_of_birth;
5873 l_rec.country_of_birth := l_c1.country_of_birth;
5874 l_rec.fast_path_employee := l_c1.fast_path_employee;
5875 l_rec.email_address := l_c1.email_address;
5876 l_rec.fte_capacity := l_c1.fte_capacity;
5877 end if;
5878 --
5879 close c1;
5880 --
5881 return l_rec;
5882 --
5883 end get_party_details;
5884 --
5885 --
5886 -- ------------------------------------------------------------------------------
5887 -- |---------------------------< migrate_all_hr_email >-------------------------|
5888 -- ------------------------------------------------------------------------------
5889 --
5890 procedure migrate_all_hr_email(p_number_of_workers in number default 1,
5891 p_current_worker in number default 1) is
5892 --
5893 l_proc varchar2(80) := g_package||'migrate_all_hr_email';
5894 --
5895 cursor c_person is
5896 select *
5897 from per_all_people_f ppf
5898 where ppf.email_address is not null
5899 and mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
5900 and ppf.effective_end_date = hr_api.g_eot
5901 and ppf.party_id is not null
5902 and not exists(select null
5903 from hz_contact_points
5904 where owner_table_name = 'HZ_PARTIES'
5905 and owner_table_id = ppf.party_id
5906 and email_address = nvl(ppf.email_address,'NULL'));
5907 --
5908 l_person c_person%rowtype;
5909 l_count number := 0;
5910 l_data_migrator_mode varchar2(30);
5911 --
5912 begin
5913 --
5914 hr_utility.set_location('Entering '||l_proc,10);
5915 --
5916 -- This routine will create contact point records for all person
5917 -- records in HRMS which have an email address.
5918 --
5919 -- Stage 1 - Select person latest records and create TCA contact point
5920 -- records.
5921 --
5922 open c_person;
5923 --
5924 loop
5925 --
5926 fetch c_person into l_person;
5927 exit when c_person%notfound;
5928 --
5929 l_count := l_count + 1;
5930 --
5931 create_update_contact_point(p_rec => l_person);
5932 --
5933 if mod(l_count,10) = 0 then
5934 --
5935 -- Commit every ten persons
5936 --
5937 commit;
5938 l_count := 0;
5939 --
5940 end if;
5941 --
5942 end loop;
5943 --
5944 close c_person;
5945 --
5946 -- Get the last set of records in the chunk.
5947 --
5948 commit;
5949 --
5950 hr_utility.set_location('Entering '||l_proc,10);
5951 --
5952 end;
5953 --
5954 --
5955 -- ------------------------------------------------------------------------------
5956 -- |---------------------------< migrate_all_hr_gender >------------------------|
5957 -- ------------------------------------------------------------------------------
5958 --
5959 procedure migrate_all_hr_gender(p_number_of_workers in number default 1,
5960 p_current_worker in number default 1) is
5961 --
5962 l_proc varchar2(80) := g_package||'migrate_all_hr_gender';
5963 --
5964 cursor c_person is
5965 select *
5966 from per_all_people_f ppf
5967 where mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
5968 and ppf.effective_end_date = hr_api.g_eot
5969 and ppf.party_id is not null
5970 and exists(select null
5971 from hz_person_profiles
5972 where party_id = ppf.party_id
5973 and nvl(gender,'Z') in ('Z','U','M','F'));
5974 --
5975 l_person c_person%rowtype;
5976 l_count number := 0;
5977 --
5978 begin
5979 --
5980 hr_utility.set_location('Entering '||l_proc,10);
5981 --
5982 open c_person;
5983 --
5984 loop
5985 --
5986 fetch c_person into l_person;
5987 exit when c_person%notfound;
5988 --
5989 l_count := l_count + 1;
5990 --
5991 update hz_person_profiles
5992 set gender = decode(l_person.sex,null,'UNSPECIFIED'
5993 ,'F','FEMALE'
5994 ,'MALE')
5995 where party_id = l_person.party_id;
5996 --
5997 if mod(l_count,10) = 0 then
5998 --
5999 -- Commit every ten persons
6000 --
6001 commit;
6002 l_count := 0;
6003 --
6004 end if;
6005 --
6006 end loop;
6007 --
6008 close c_person;
6009 --
6010 commit;
6011 --
6012 hr_utility.set_location('Leaving '||l_proc,10);
6013 --
6014 end migrate_all_hr_gender;
6015 --
6016 function get_person_details
6017 (p_party_id in number,
6018 p_person_id in number,
6019 p_effective_date in date) return per_per_shd.g_rec_type is
6020
6021 --
6022 l_proc varchar2(80) := g_package||'get_person_details';
6023 --
6024 cursor c1 is
6025 select *
6026 from per_all_people_f
6027 where party_id = p_party_id
6028 and person_id = p_person_id
6029 and p_effective_date
6030 between effective_start_date
6031 and effective_end_date;
6032 --
6033 l_c1 c1%rowtype;
6034 l_rec per_per_shd.g_rec_type;
6035 --
6036 begin
6037 --
6038
6039 hr_utility.set_location('Entering '||l_proc,10);
6040
6041 if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
6042 --
6043 return l_rec;
6044 --
6045 end if;
6046 --
6047 -- Just get the first record regardless.
6048 --
6049 hr_utility.set_location(l_proc,20);
6050 --
6051 open c1;
6052
6053 fetch c1 into l_c1;
6054 --
6055 -- Assigining the fields to the records type
6056 if c1%found then
6057
6058 l_rec.first_name := l_c1.first_name;
6059 l_rec.sex := l_c1.sex;
6060 l_rec.title := l_c1.title;
6061 l_rec.date_of_birth := l_c1.date_of_birth;
6062 l_rec.date_of_death := l_c1.date_of_death;
6063 l_rec.known_as := l_c1.known_as;
6064 l_rec.marital_status := l_c1.marital_status;
6065 l_rec.middle_names := l_c1.middle_names;
6066 l_rec.nationality := l_c1.nationality;
6067 l_rec.blood_type := l_c1.blood_type;
6068 l_rec.correspondence_language := l_c1.correspondence_language;
6069 l_rec.honors := l_c1.honors;
6070 l_rec.pre_name_adjunct := l_c1.pre_name_adjunct;
6071 l_rec.rehire_authorizor := l_c1.rehire_authorizor;
6072 l_rec.rehire_recommendation := l_c1.rehire_recommendation;
6073 l_rec.resume_exists := l_c1.resume_exists;
6074 l_rec.resume_last_updated := l_c1.resume_last_updated;
6075 l_rec.second_passport_exists := l_c1.second_passport_exists;
6076 l_rec.student_status := l_c1.student_status;
6077 l_rec.suffix := l_c1.suffix;
6078 l_rec.uses_tobacco_flag := l_c1.uses_tobacco_flag;
6079 l_rec.town_of_birth := l_c1.town_of_birth;
6080 l_rec.region_of_birth := l_c1.region_of_birth;
6081 l_rec.country_of_birth := l_c1.country_of_birth;
6082 l_rec.fast_path_employee := l_c1.fast_path_employee;
6083 l_rec.email_address := l_c1.email_address;
6084 l_rec.fte_capacity := l_c1.fte_capacity;
6085
6086 end if;
6087 --
6088 close c1;
6089 --
6090 hr_utility.set_location('Leaving '||l_proc,30);
6091 --
6092 return l_rec;
6093 --
6094 end get_person_details;
6095
6096 -- Bug fix 4137950 starts here --
6097 -- Over loaded procedure added --
6098
6099 procedure migrate_all_hr_persons(p_start_rowid in rowid,
6100 p_end_rowid in rowid,
6101 p_rows_processed out NOCOPY number) is
6102
6103 -- Pl/sql table fetch person ids into.
6104 TYPE l_person_id_type IS TABLE OF NUMBER(15) index by binary_integer;
6105 --
6106 t_party_id g_party_id_type;
6107 t_person_id l_person_id_type;
6108 t_elig_person_id l_person_id_type;
6109 --
6110 -- variable to store the count of person record to be updated
6111 -- with party id.
6112 l_elig_person_cnt number;
6113 -- Cursor to fetch the person id in the range.
6114 cursor csr_person is
6115 select person_id
6116 from per_all_people_f
6117 where party_id is null
6118 and rowid between p_start_rowid and p_end_rowid
6119 and effective_end_date = hr_api.g_eot;
6120 --
6121 -- Cursor to fecth the details for a person.
6122 --
6123 cursor csr_per_details(p_person_id number) is
6124 select *
6125 from per_all_people_f
6126 where person_id = p_person_id
6127 and effective_end_date = hr_api.g_eot;
6128 --
6129 l_per_rec csr_per_details%rowtype;
6130 --
6131 begin
6132 -- intialize the rows processed count
6133 -- and the eligible person count
6134 p_rows_processed := 0;
6135 l_elig_person_cnt := 0;
6136 --
6137 open csr_person;
6138 loop
6139 -- fetch the person ids into pl/sql table.
6140 fetch csr_person bulk collect into t_person_id limit 1000;
6141 --
6142 if t_person_id.count = 0 then
6143 exit;
6144 end if;
6145 --
6146 -- Loop to create party records for the person with party id
6147 -- as null.
6148 --
6149 for i in t_person_id.first..t_person_id.last
6150 loop
6151
6152 open csr_per_details(t_person_id(i));
6153 fetch csr_per_details into l_per_rec;
6154 close csr_per_details;
6155
6156 per_hrtca_merge.create_tca_person( p_rec => l_per_rec );
6157
6158 if l_per_rec.party_id is not null then
6159 --
6160 l_elig_person_cnt := l_elig_person_cnt+1;
6161 t_party_id(l_elig_person_cnt) := l_per_rec.party_id;
6162 t_elig_person_id(l_elig_person_cnt) := t_person_id(i);
6163 --
6164 end if;
6165
6166 end loop;
6167 --
6168 -- Update all HR tables having party id column with
6169 -- respective party id stored in pl/sql table.
6170 --
6171 -- Bulk update person records if there are person records
6172 -- to be updated.
6173 --
6174 if t_elig_person_id.count > 0 then
6175 --
6176 forall i in t_elig_person_id.first..t_elig_person_id.last
6177 update per_all_people_f
6178 set party_id = t_party_id(i)
6179 where person_id = t_elig_person_id(i);
6180 --
6181 -- Bulk update competence records
6182 --
6183 forall i in t_elig_person_id.first..t_elig_person_id.last
6184 update per_competence_elements
6185 set party_id = t_party_id(i)
6186 where person_id = t_elig_person_id(i);
6187 --
6188 -- Bulk update events records
6189 --
6190 forall i in t_elig_person_id.first..t_elig_person_id.last
6191 update per_events
6192 set party_id = t_party_id(i)
6193 where assignment_id in
6194 (select assignment_id
6195 from per_all_assignments_f
6196 where person_id = t_elig_person_id(i));
6197 --
6198 -- Bulk update address records
6199 --
6200 forall i in t_elig_person_id.first..t_elig_person_id.last
6201 update per_addresses
6202 set party_id = t_party_id(i)
6203 where person_id = t_elig_person_id(i);
6204 --
6205 -- Bulk update phone records
6206 --
6207 forall i in t_elig_person_id.first..t_elig_person_id.last
6208 update per_phones
6209 set party_id = t_party_id(i)
6210 where parent_id = t_elig_person_id(i)
6211 and parent_table = 'PER_ALL_PEOPLE_F';
6212 --
6213 -- Bulk update qualification records
6214 --
6215 forall i in t_elig_person_id.first..t_elig_person_id.last
6216 update per_qualifications
6217 set party_id = t_party_id(i)
6218 where person_id = t_elig_person_id(i);
6219 --
6220 -- Bulk update etablishment attendances records
6221 --
6222 forall i in t_elig_person_id.first..t_elig_person_id.last
6223 update per_establishment_attendances
6224 set party_id = t_party_id(i)
6225 where person_id = t_elig_person_id(i);
6226 --
6227 -- Bulk update previous employment records
6228 --
6229 forall i in t_elig_person_id.first..t_elig_person_id.last
6230 update per_previous_employers
6231 set party_id = t_party_id(i)
6232 where person_id = t_elig_person_id(i);
6233 --
6234 end if;
6235 --
6236 -- update the rows processed count.
6237 --
6238 p_rows_processed := p_rows_processed + t_person_id.count;
6239 --
6240 -- commit the migrated records.
6241 --
6242 commit;
6243 --
6244 -- Clear the pl/sql tables
6245 t_person_id.delete;
6246 t_party_id.delete;
6247 t_elig_person_id.delete;
6248 --
6249 l_elig_person_cnt := 0;
6250 --
6251 end loop;
6252 --
6253 close csr_person;
6254 --
6255 end migrate_all_hr_persons;
6256 -- Bug fix 4137950 ends here --
6257 --
6258 -- Bug fix 5247146 starts here --
6259 -- Over loaded procedure added --
6260 -- ------------------------------------------------------------------------------
6261 -- |---------------------------< migrate_all_hr_email >-------------------------|
6262 -- ------------------------------------------------------------------------------
6263 --
6264 procedure migrate_all_hr_email(p_start_rowid in rowid,
6265 p_end_rowid in rowid,
6266 p_rows_processed out NOCOPY number) is
6267 --
6268 l_proc varchar2(80) := g_package||'migrate_all_hr_email2';
6269 --
6270 cursor c_person is
6271 select /*+ rowid(ppf) */ *
6272 from per_all_people_f ppf
6273 where ppf.email_address is not null
6274 and ppf.ROWID between p_start_rowid and p_end_rowid
6275 and ppf.effective_end_date = hr_api.g_eot
6276 and ppf.party_id is not null
6277 and not exists(select /*+ no_unnest */ null
6278 from hz_contact_points
6279 where owner_table_name = 'HZ_PARTIES'
6280 and owner_table_id = ppf.party_id
6281 and email_address = nvl(ppf.email_address,'NULL'));
6282
6283 --
6284 l_person c_person%rowtype;
6285 l_count number := 0;
6286 l_data_migrator_mode varchar2(30);
6287 --
6288
6289 begin
6290
6291 hr_utility.set_location('Entering '||l_proc,10);
6292
6293 l_data_migrator_mode := hr_general.g_data_migrator_mode;
6294 hr_general.g_data_migrator_mode := 'Y';
6295
6296 -- intialize the rows processed count
6297 p_rows_processed := 0;
6298
6299 open c_person;
6300 --
6301 loop
6302 --
6303 fetch c_person into l_person;
6304 exit when c_person%notfound;
6305 --
6306 --
6307 -- Issue a savepoint.
6308 --
6309 begin
6310 savepoint last_pos;
6311
6312 l_count := l_count + 1;
6313 --
6314 create_update_contact_point(p_rec => l_person);
6315 --
6316 if mod(l_count,10) = 0 then
6317 --
6318 -- Commit every ten persons
6319 --
6320 commit;
6321 l_count := 0;
6322 --
6323 end if;
6324 --
6325 p_rows_processed :=p_rows_processed+1;
6326 exception
6327 when others then
6328 ROLLBACK TO last_pos;
6329 end;
6330 end loop;
6331 --
6332 close c_person;
6333 --
6334 -- Get the last set of records in the chunk.
6335 --
6336 commit;
6337
6338 --
6339 hr_general.g_data_migrator_mode := l_data_migrator_mode;
6340 --
6341 hr_utility.set_location('Leaving '||l_proc,20);
6342 --
6343 end migrate_all_hr_email;
6344 --
6345 -- Bug fix 5395601 starts here --
6346 -- ------------------------------------------------------------------------------
6347 -- |---------------------------< Purge_person >-------------------------|
6348 -- ------------------------------------------------------------------------------
6349 --
6350 procedure purge_person (p_person_id number,p_party_id number ) is
6351 begin
6352
6353 hr_utility.set_location('purge_person ', 12);
6354 --fix for bug 6620368 starts here.
6355 -- Call to purge_parties is commented to improve performance.
6356 -- Party id is inserted into table HR_TCA_PARTY_UNMERGE
6357 -- so that party id will be purged when the user run the
6358 -- party unmerge program next time.
6359
6360 INSERT INTO hr_tca_party_unmerge (party_id,status)
6361 VALUES (p_party_id,'PURGE');
6362
6363
6364 /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6365 per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6366 hr_utility.set_location('before calling purge_parties ', 13);
6367 per_hrtca_merge.purge_parties;
6368 hr_utility.set_location('After call ', 14);*/
6369
6370 hr_utility.set_location('purge_person ', 13);
6371 --fix for bug 6620368 ends here.
6372 exception
6373 when others then
6374 ROLLBACK TO hr_delete_person;
6375 end purge_person;
6376 --
6377 end per_hrtca_merge;
6378 --