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