1 Package Body hr_person_internal as
2 /* $Header: peperbsi.pkb 120.19.12020000.2 2012/07/05 03:21:56 amnaraya ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_person_internal.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< product_installed >---------------------------|
11 -- ----------------------------------------------------------------------------
12 PROCEDURE product_installed(p_application_short_name IN varchar2
13 ,p_status OUT NOCOPY varchar2
14 ,p_yes_no OUT NOCOPY varchar2
15 ,p_oracle_username OUT NOCOPY varchar2)
16 IS
17 --
18 l_proc varchar2(72) := g_package||'product_installed';
19 --
20 BEGIN
21 --
22 if g_debug then
23 hr_utility.set_location('Entering:'|| l_proc, 10);
24 end if;
25 --
26 p_yes_no := 'N';
27 p_oracle_username := 'DUMMY';
28 --
29 begin
30 select 'Y', fpi.status
31 into p_yes_no, p_status
32 from fnd_product_installations fpi
33 where fpi.status = 'I'
34 and fpi.application_id =
35 (select fa.application_id
36 from fnd_application fa
37 where fa.application_short_name = P_APPLICATION_SHORT_NAME
38 );
39
40 exception
41 when NO_DATA_FOUND then null;
42 end;
43 --
44 if g_debug then
45 hr_utility.set_location('Leaving:'|| l_proc, 100);
46 end if;
47 --
48 END product_installed;
49 --
50 -- ----------------------------------------------------------------------------
51 -- |------------------------< person_existance_check >------------------------|
52 -- ----------------------------------------------------------------------------
53 --
54 -- Description :
55 -- Raises error (and hence falls right out of package) if this person does
56 -- not exist.
57 --
58 PROCEDURE person_existance_check (p_person_id number)
59 IS
60 --
61 l_dummy number(15);
62 --
63 BEGIN
64 --
65 select count(*)
66 into l_dummy
67 from per_all_people_f p
68 where p.person_id = P_PERSON_ID;
69 --
70 EXCEPTION
71 when NO_DATA_FOUND then
72 --
73 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
74 hr_utility.set_message_token ('PROCEDURE','PERSON_EXISTANCE_CHECK');
75 hr_utility.set_message_token ('STEP', '1');
76 hr_utility.raise_error;
77 --
78 END person_existance_check;
79 --
80 -- ----------------------------------------------------------------------------
81 -- |------------------------< aol_predel_validation >-------------------------|
82 -- ----------------------------------------------------------------------------
83 --
84 -- Description : Foreign key reference check.
85 --
86 PROCEDURE aol_predel_validation (p_person_id number)
87 IS
88 --
89 l_delete_permitted varchar2(1);
90 --
91 BEGIN
92 --
93 begin
94 --
95 select 'Y'
96 into l_delete_permitted
97 from sys.dual
98 where not exists
99 (select null
100 from fnd_user aol
101 where aol.employee_id = P_PERSON_ID
102 );
103 --
104 exception
105 when NO_DATA_FOUND then
106 hr_utility.set_message (801,'HR_6274_ALL_AOL_PER_NO_DEL');
107 hr_utility.raise_error;
108 end;
109 --
110 END aol_predel_validation;
111 --
112 -- ----------------------------------------------------------------------------
113 -- |-------------------------< assignment_set_check >--------------------------|
114 -- ----------------------------------------------------------------------------
115 --
116 -- Description:
117 --
118 -- Sets error code and status if this person has any assignments which are
119 -- the only ones in an assignment set and where that assginment is included.
120 --
121 PROCEDURE assignment_set_check (p_person_id IN number)
122 IS
123 --
124 l_delete_permitted varchar2(1);
125 --
126 BEGIN
127 --
128 select 'Y'
129 into l_delete_permitted
130 from sys.dual
131 where not exists
132 (select null
133 from per_assignments_f ass,
134 hr_assignment_set_amendments asa
135 where asa.assignment_id = ass.assignment_id
136 and ass.person_id = P_PERSON_ID
137 and asa.include_or_exclude = 'I'
138 and not exists
139 (select null
140 from hr_assignment_set_amendments asa2
141 where asa2.assignment_set_id = asa.assignment_set_id
142 and asa2.assignment_id <> asa.assignment_id)
143 );
144 --
145 EXCEPTION
146 when NO_DATA_FOUND then
147 hr_utility.set_message (801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
148 hr_utility.raise_error;
149 --
150 END assignment_set_check;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------------------------< pay_predel_validation >------------------------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description :
157 --
158 -- Ensures that there are no assignments actions for this person other than
159 -- Purge actions. If there are then raise an error and disallow delete.
160 --
161 PROCEDURE pay_predel_validation (p_person_id number)
162 IS
163 --
164 l_delete_permitted varchar2(1);
165 --
166 BEGIN
167 --
168 begin
169 --
170 select 'Y'
171 into l_delete_permitted
172 from sys.dual
173 where not exists
174 (select null
175 from pay_assignment_actions paa
176 ,per_assignments_f ass
177 ,pay_payroll_actions ppa
178 where paa.assignment_id = ass.assignment_id
179 and ass.person_id = P_PERSON_ID
180 and ppa.payroll_action_id = paa.payroll_action_id
181 and ppa.action_type <> 'Z');
182 --
183 exception
184 when NO_DATA_FOUND then
185 hr_utility.set_message (801,'HR_6237_ALL_ASS_ACTIONS_EXIST');
186 hr_utility.raise_error;
187 end;
188 --
189 END pay_predel_validation;
190 --
191 -- ----------------------------------------------------------------------------
192 -- |------------------------< ben_predel_validation >-------------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 -- Description : Ensures that there are no open life events for a person.
196 --
197 PROCEDURE ben_predel_validation(p_person_id NUMBER
198 ,p_effective_date DATE)
199 IS
200 --
201 --
202 BEGIN
203 --
204 ben_person_delete.check_ben_rows_before_delete(p_person_id
205 ,p_effective_date);
206 --
207 END ben_predel_validation;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |----------------------< closed_element_entry_check >----------------------|
211 -- ----------------------------------------------------------------------------
212 --
213 -- Description :
214 -- Check that for any element entries that are about to be deleted, the
215 -- element type is not closed for the duration of that entry. Also check
216 -- that if the assignment is to a payroll, the payroll period is not closed.
217 -- If any of these 2 checks fail, the delete is disallowed.
218 --
219 PROCEDURE closed_element_entry_check(p_person_id IN number
220 ,p_effective_date IN date)
221 IS
222 --
223 CURSOR csr_this_persons_ee IS
224 SELECT l.element_type_id, e.effective_start_date,
225 e.effective_end_date, a.assignment_id
226 FROM pay_element_entries_f e,
227 per_assignments_f a,
228 pay_element_links_f l
229 WHERE a.person_id = P_PERSON_ID
230 and a.assignment_id = e.assignment_id
231 and e.effective_start_date between
232 a.effective_start_date and a.effective_end_date
233 and e.element_link_id = l.element_link_id
234 and e.effective_start_date between
235 l.effective_start_date and l.effective_end_date;
236 --
237 l_proc varchar2(72) := g_package||'closed_element_entry_check';
238 --
239 BEGIN
240 --
241 if g_debug then
242 hr_utility.set_location('Entering:'|| l_proc, 10);
243 end if;
244 --
245 FOR each_entry in csr_this_persons_ee LOOP
246 --
247 hr_entry.chk_element_entry_open(each_entry.element_type_id,
248 each_entry.effective_start_date,
249 each_entry.effective_start_date,
250 each_entry.effective_end_date,
251 each_entry.assignment_id);
252 --
253 END LOOP;
254 --
255 if g_debug then
256 hr_utility.set_location('Leaving:'|| l_proc, 10);
257 end if;
258 --
259 END closed_element_entry_check;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |-----------------------< contact_cobra_validation >-----------------------|
263 -- ----------------------------------------------------------------------------
264 --
265 -- Description :
266 --
267 -- Searches for any contacts of the person being deleted who have
268 -- COBRA Coverage Enrollments which are as a result of the Persons
269 -- Assignments.
270 --
271 PROCEDURE contact_cobra_validation (p_person_id number)
272 IS
273 --
274 l_delete_permitted varchar2(1);
275 --
276 BEGIN
277 --
278 begin
279 --
280 select 'Y'
281 into l_delete_permitted
282 from sys.dual
283 where not exists
284 (select null
285 from per_assignments_f a
286 ,per_contact_relationships c
287 ,per_cobra_cov_enrollments e
288 where a.person_id = P_PERSON_ID
289 and a.assignment_id = e.assignment_id
290 and c.person_id = P_PERSON_ID
291 and c.contact_relationship_id = e.contact_relationship_id);
292 --
293 exception
294 when NO_DATA_FOUND then
295 hr_utility.set_message (801,'HR_6981_ALL_CONT_COBRA_EXISTS');
296 hr_utility.raise_error;
297 end;
298 --
299 END contact_cobra_validation;
300 --
301 -- ----------------------------------------------------------------------------
302 -- |---------------------------< contracts_check >----------------------------|
303 -- ----------------------------------------------------------------------------
304 --
305 -- Description :
306 --
307 -- Raise an error if related contracts exist for the given person.
308 --
309 PROCEDURE contracts_check (p_person_id number)
310 IS
311 --
312 l_delete_permitted varchar2(1);
313 --
314 l_proc varchar2(72) := g_package||'contracts_check';
315 --
316 begin
317 --
318 if g_debug then
319 hr_utility.set_location('Entering:'|| l_proc, 10);
320 end if;
321 --
322 -- Check that no child records exist for the
323 -- person on per_contracts_f when
324 -- the person is deleted
325 --
326 select null
327 into l_delete_permitted
328 from sys.dual
329 where not exists
330 (select null
331 from per_contracts_f
332 where person_id = p_person_id);
333 --
334 exception
335 when NO_DATA_FOUND then
336 hr_utility.set_message(800,'PER_52851_PER_NO_DEL_CONTRACTS');
337 hr_utility.raise_error;
338 --
339 END contracts_check;
340 --
341 -- ----------------------------------------------------------------------------
342 -- |-----------------------------< check_contact >----------------------------|
343 -- ----------------------------------------------------------------------------
344 --
345 -- Description :
346 --
347 -- Is this contact a contact for anybody else? If so then do nothing.
348 -- If not then check if this person has ever been an employee or
349 -- applicant. If they have not then check whether they have any extra
350 -- info entered for them (other than default info). If they have not
351 -- then delete this contact also. Otherwise do nothing.
352 --
353 -- NOTES
354 -- p_person_id Non-contact in relationship.
355 -- p_contact_person_id Contact in this relationship - the person
356 -- who the check is performed against.
357 -- p_contact_relationship_id Relationship which is currently being
358 -- considered for this contact.
359 --
360 PROCEDURE check_contact(p_person_id IN number
361 ,p_contact_person_id IN number
362 ,p_contact_relationship_id IN number
363 ,p_effective_date IN date)
364 IS
365 --
366 l_contact_elsewhere varchar2(1);
367 l_other_only varchar2(1);
368 l_delete_contact varchar2(1);
369 l_proc varchar2(72) := g_package||'check_contact';
370 --
371 BEGIN
372 --
373 if g_debug then
374 hr_utility.set_location('Entering:'|| l_proc, 10);
375 end if;
376 --
377 hr_person_internal.person_existance_check(P_CONTACT_PERSON_ID);
378 --
379 if g_debug then
380 hr_utility.set_location(l_proc, 20);
381 end if;
382 --
383 begin
384 --
385 select 'Y'
386 into l_contact_elsewhere
387 from sys.dual
388 where exists
389 (select null
390 from per_contact_relationships r
391 where r.contact_relationship_id <> P_CONTACT_RELATIONSHIP_ID
392 and r.contact_person_id = P_CONTACT_PERSON_ID);
393 --
394 exception
395 when NO_DATA_FOUND then null;
396 end;
397 --
398 if SQL%ROWCOUNT > 0 then
399 return;
400 end if;
401 --
402 if g_debug then
403 hr_utility.set_location(l_proc, 30);
404 end if;
405 --
406 begin
407 --
408 select 'Y'
409 into l_other_only
410 from sys.dual
411 where not exists
412 (select null
413 from per_people_f p
414 where p.person_id = P_CONTACT_PERSON_ID
415 and p.current_emp_or_apl_flag = 'Y');
416 --
417 exception
418 when NO_DATA_FOUND then return;
419 end;
420 --
421 begin
422 --
423 -- Can contact be deleted? If strong val errors then just trap
424 -- error as we will continue as usual. If it succeeds then delete
425 -- contact.
426 --
427 begin
428 l_delete_contact := 'Y';
429 hr_person_internal.strong_predel_validation(P_CONTACT_PERSON_ID,
430 p_effective_date);
431 exception
432 when hr_utility.hr_error then
433 l_delete_contact := 'N';
434 end;
435 --
436 if l_delete_contact = 'Y' then
437 hr_person_internal.people_default_deletes(P_CONTACT_PERSON_ID);
438 end if;
439 --
440 end;
441 --
442 if g_debug then
443 hr_utility.set_location('Leaving:'||l_proc, 99);
444 end if;
445 END check_contact;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |--------------------------< delete_org_manager >--------------------------|
449 -- ----------------------------------------------------------------------------
450 --
451 PROCEDURE delete_org_manager(p_person_id in number
452 ,p_effective_date in date
453 ,p_person_org_manager_warning out nocopy varchar2) IS
454 --
455 cursor csr_org_details(p_organization_id number) is
456 select org_info.organization_id,
457 org_info.org_information_id,
458 org_info.org_information_context,
459 org_info.org_information1,
460 org_info.org_information2,
461 org_info.org_information3,
462 org_info.org_information4,
463 org_info.org_information5,
464 org_info.org_information6,
465 org_info.org_information7,
466 org_info.org_information8,
467 org_info.org_information9,
468 org_info.org_information10,
469 org_info.org_information11,
470 org_info.org_information12,
471 org_info.org_information13,
472 org_info.org_information14,
473 org_info.org_information15,
474 org_info.org_information16,
475 org_info.org_information17,
476 org_info.org_information18,
477 org_info.org_information19,
478 org_info.org_information20,
479 org_info.object_version_number,
480 org_info.attribute_category,
481 org_info.attribute1,
482 org_info.attribute2,
483 org_info.attribute3,
484 org_info.attribute4,
485 org_info.attribute5,
486 org_info.attribute6,
487 org_info.attribute7,
488 org_info.attribute8,
489 org_info.attribute9,
490 org_info.attribute10,
491 org_info.attribute11,
492 org_info.attribute12,
493 org_info.attribute13,
494 org_info.attribute14,
495 org_info.attribute15,
496 org_info.attribute16,
497 org_info.attribute17,
498 org_info.attribute18,
499 org_info.attribute19,
500 org_info.attribute20
501 from hr_organization_information org_info
502 where org_info.organization_id = p_organization_id
503 and org_info.org_information_context = 'Organization Name Alias'
504 and org_info.org_information2 = to_char(p_person_id);
505 --
506 l_org_details csr_org_details%rowtype;
507 --
508 cursor csr_per_mgr_orgs is
509 select org.organization_id,
510 org_tl.name
511 from hr_all_organization_units org,
512 hr_all_organization_units_tl org_tl
513 where org.organization_id = org_tl.organization_id
514 and exists (select null
515 from hr_organization_information org_info
516 where org_info.organization_id = org.organization_id
517 and org_info.org_information_context = 'Organization Name Alias'
518 and org_info.org_information2 = to_char(p_person_id));
519 --
520 l_per_mgr_orgs csr_per_mgr_orgs%rowtype;
521 --
522 l_per_is_org_mgr_warning boolean := FALSE;
523 l_warning boolean;
524 --
525 BEGIN
526 --
527 if p_person_id is not null then
528 --
529 -- Check whether this will have any impact on org_managers
530 --
531 open csr_per_mgr_orgs;
532 --
533 loop
534 --
535 fetch csr_per_mgr_orgs into l_per_mgr_orgs;
536 exit when csr_per_mgr_orgs%notfound;
537 --
538 l_per_is_org_mgr_warning := TRUE;
539 --
540 open csr_org_details(l_per_mgr_orgs.organization_id);
541 --
542 loop
543 --
544 fetch csr_org_details into l_org_details;
545 exit when csr_org_details%notfound;
546 --
547 if l_org_details.org_information1 is null then
548 --
549 hr_organization_api.delete_org_manager
550 (p_org_information_id => l_org_details.org_information_id,
551 p_object_version_number => l_org_details.object_version_number);
552 --
553 else
554 --
555 hr_organization_api.update_org_manager
556 (p_effective_date => p_effective_date
557 ,p_organization_id => l_org_details.organization_id
558 ,p_org_information_id => l_org_details.org_information_id
559 ,p_org_info_type_code => l_org_details.org_information_context
560 ,p_org_information1 => l_org_details.org_information1
561 ,p_org_information2 => null
562 ,p_org_information3 => null
563 ,p_org_information4 => null
564 ,p_org_information5 => l_org_details.org_information5
565 ,p_org_information6 => l_org_details.org_information6
566 ,p_org_information7 => l_org_details.org_information7
567 ,p_org_information8 => l_org_details.org_information8
568 ,p_org_information9 => l_org_details.org_information9
569 ,p_org_information10 => l_org_details.org_information10
570 ,p_org_information11 => l_org_details.org_information11
571 ,p_org_information12 => l_org_details.org_information12
572 ,p_org_information13 => l_org_details.org_information13
573 ,p_org_information14 => l_org_details.org_information14
574 ,p_org_information15 => l_org_details.org_information15
575 ,p_org_information16 => l_org_details.org_information16
576 ,p_org_information17 => l_org_details.org_information17
577 ,p_org_information18 => l_org_details.org_information18
578 ,p_org_information19 => l_org_details.org_information19
579 ,p_org_information20 => l_org_details.org_information20
580 ,p_attribute_category => l_org_details.attribute_category
581 ,p_attribute1 => l_org_details.attribute1
582 ,p_attribute2 => l_org_details.attribute2
583 ,p_attribute3 => l_org_details.attribute3
584 ,p_attribute4 => l_org_details.attribute4
585 ,p_attribute5 => l_org_details.attribute5
586 ,p_attribute6 => l_org_details.attribute6
587 ,p_attribute7 => l_org_details.attribute7
588 ,p_attribute8 => l_org_details.attribute8
589 ,p_attribute9 => l_org_details.attribute9
590 ,p_attribute10 => l_org_details.attribute10
591 ,p_attribute11 => l_org_details.attribute11
592 ,p_attribute12 => l_org_details.attribute12
593 ,p_attribute13 => l_org_details.attribute13
594 ,p_attribute14 => l_org_details.attribute14
595 ,p_attribute15 => l_org_details.attribute15
596 ,p_attribute16 => l_org_details.attribute16
597 ,p_attribute17 => l_org_details.attribute17
598 ,p_attribute18 => l_org_details.attribute18
599 ,p_attribute19 => l_org_details.attribute19
600 ,p_attribute20 => l_org_details.attribute20
601 ,p_object_version_number => l_org_details.object_version_number
602 ,p_warning => l_warning);
603 --
604 end if;
605 --
606 end loop;
607 --
608 close csr_org_details;
609 --
610 end loop;
611 --
612 close csr_per_mgr_orgs;
613 --
614 end if;
615 --
616 IF l_per_is_org_mgr_warning THEN
617 --
618 p_person_org_manager_warning :=
619 fnd_message.get_string('PER','HR_449563_PER_DEL_ORG_MGR');
620 --
621 END IF;
622 --
623 END delete_org_manager;
624 --
625 -- ----------------------------------------------------------------------------
626 -- |------------------------< weak_predel_validation >------------------------|
627 -- ----------------------------------------------------------------------------
628 --
629 PROCEDURE weak_predel_validation(p_person_id IN number
630 ,p_effective_date IN date)
631 IS
632 --
633 -- Declare the local variables
634 --
635 l_pay_installed varchar2(1);
636 l_pay_status varchar2(1);
637 l_ben_installed varchar2(1);
638 l_ben_status varchar2(1);
639 l_oracle_id varchar2(30);
640 l_delete_permitted varchar2(1);
641 --
642 l_proc varchar2(72) := g_package||'weak_predel_validation';
643 --
644 -- Bug 4672901 Starts Here
645 CURSOR ben_ext_chg_log (
646 p_person_id NUMBER
647 ) IS
648 SELECT ext_chg_evt_log_id
649 FROM ben_ext_chg_evt_log
650 WHERE person_id = p_person_id
651 FOR UPDATE OF ext_chg_evt_log_id;
652 --
653 l_id NUMBER;
654 -- Bug 4672901 Ends Here
655 --
656 BEGIN
657 --
658 if g_debug then
659 hr_utility.set_location('Entering:'|| l_proc, 10);
660 end if;
661 --
662 hr_person_internal.person_existance_check(P_PERSON_ID);
663 --
664 if g_debug then
665 hr_utility.set_location(l_proc, 20);
666 end if;
667 --
668 hr_person_internal.product_installed('PAY'
669 ,l_pay_status
670 ,l_pay_installed
671 ,l_oracle_id);
672 --
673 if g_debug then
674 hr_utility.set_location(l_proc, 30);
675 end if;
676 --
677 hr_person_internal.aol_predel_validation(P_PERSON_ID);
678 --
679 if g_debug then
680 hr_utility.set_location(l_proc, 40);
681 end if;
682 --
683 hr_person_internal.assignment_set_check(P_PERSON_ID);
684 --
685 if g_debug then
686 hr_utility.set_location(l_proc, 50);
687 end if;
688 --
689 if (l_pay_installed = 'Y') then
690 --
691 if g_debug then
692 hr_utility.set_location(l_proc, 55);
693 end if;
694 --
695 hr_person_internal.pay_predel_validation(P_PERSON_ID);
696 --
697 end if;
698
699 -- Bug 4672901 Starts Here
700
701 OPEN ben_ext_chg_log (p_person_id);
702 --
703 LOOP
704 FETCH ben_ext_chg_log INTO l_id;
705 EXIT WHEN ben_ext_chg_log%NOTFOUND;
706 DELETE FROM ben_ext_chg_evt_log
707 WHERE CURRENT OF ben_ext_chg_log;
708 END LOOP;
709 --
710 CLOSE ben_ext_chg_log;
711
712 -- Bug 4672901 Ends Here
713 --
714
715 --
716 -- Removed check for ben install
717 -- as OSB can now have enrollment results
718 -- and unrestricted Life events in progress
719 --
720 hr_person_internal.ben_predel_validation(P_PERSON_ID,p_effective_date);
721 --
722 if g_debug then
723 hr_utility.set_location(l_proc, 60);
724 end if;
725 --
726 hr_person_internal.closed_element_entry_check(P_PERSON_ID, p_effective_date);
727 --
728 if g_debug then
729 hr_utility.set_location(l_proc, 70);
730 end if;
731 --
732 hr_person_internal.contact_cobra_validation(P_PERSON_ID);
733 --
734 if g_debug then
735 hr_utility.set_location(l_proc, 80);
736 end if;
737 --
738 hr_person_internal.contracts_check(P_PERSON_ID);
739 --
740 if g_debug then
741 hr_utility.set_location(l_proc, 90);
742 end if;
743 -- Bug 14028011
744 per_ota_predel_validation.ota_predel_per_validation(P_PERSON_ID);
745 --
746 if g_debug then
747 hr_utility.set_location('Leaving:'||l_proc, 100);
748 end if;
749 --
750 END weak_predel_validation;
751 --
752 -- ----------------------------------------------------------------------------
753 -- |-----------------------< strong_predel_validation >-----------------------|
754 -- ----------------------------------------------------------------------------
755 --
756 -- Descrption:
757 --
758 -- It performs many checks to find if additional data has been entered for this
759 -- person. It is more stringent than weak_predel_validation and ensures that
760 -- this person only has the default data set up by entering a person, contact
761 -- or applicant afresh onto the system. If additional data is found then the
762 -- delete of this person from the calling module is invalid as it is beyond
763 -- its scope. The Delete Person form should therefore be used (which only
764 -- performs weak_predel_validation) if a delete really is required.
765 --
766 PROCEDURE strong_predel_validation(p_person_id IN number
767 ,p_effective_date IN date)
768 IS
769 --
770 l_person_types number;
771 l_delete_permitted varchar2(1);
772 --
773 -- Bug 3524713 Starts Here
774 CURSOR ben_ext_chg_log (
775 p_person_id NUMBER
776 ) IS
777 SELECT ext_chg_evt_log_id
778 FROM ben_ext_chg_evt_log
779 WHERE person_id = p_person_id
780 FOR UPDATE OF ext_chg_evt_log_id;
781 --
782 l_id NUMBER;
783 -- Bug 3524713 Ends Here
784 --
785 l_proc varchar2(72) := g_package||'strong_predel_validation';
786 --
787 BEGIN
788 --
789 if g_debug then
790 hr_utility.set_location('Entering:'|| l_proc, 10);
791 end if;
792 --
793 -- check if person type changes exist.
794 --
795 -- Fix for bug 7045968 starts here
796 -- Modified the select statement to use the person_type_id from
797 -- per_person_type_usages_f
798 -- rather than per_people_f
799
800 /*select count(*)
801 into l_person_types
802 from per_people_f ppf,
803 per_person_types ppt
804 where ppf.person_id = P_PERSON_ID
805 and ppf.effective_end_date >= p_effective_date
806 and ppf.person_type_id = ppt.person_type_id
807 and ( exists
808 (select null
809 from per_people_f ppf2,
810 per_person_types ppt2
811 where ppf2.person_id = ppf.person_id
812 and ppf2.effective_end_date >= p_effective_date
813 and ppf2.person_type_id = ppt2.person_type_id
814 and ppt2.system_person_type <> ppt.system_person_type
815 )
816 or exists
817 (select null
818 from per_periods_of_placement ppp
819 where ppp.person_id=ppf.person_id
820 and actual_termination_date>=p_effective_date
821 and actual_termination_date is not null)
822 or exists
823 (select null
824 from per_periods_of_placement ppp
825 where ppp.person_id=ppf.person_id
826 and ppp.date_start>p_effective_date
827 )
828 ); */ --fix for bug 6730008.
829
830
831 select count(*)
832 into l_person_types
833 from per_person_type_usages_f ptu
834 where ptu.person_id = P_PERSON_ID
835 and ptu.effective_start_date > p_effective_date;
836
837 -- Fix for bug 7045968 ends here
838
839
840 --
841 if l_person_types > 0 then
842 --
843 hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
844 hr_utility.raise_error;
845 --
846 end if;
847 --
848 if g_debug then
849 hr_utility.set_location(l_proc, 20);
850 end if;
851 --
852 begin
853 --
854 -- bug fix 3732129.
855 -- Select statement modified to improve performance.
856 --
857 select 'Y'
858 into l_delete_permitted
859 from sys.dual
860 where not exists (
861 select null
862 from per_letter_request_lines r
863 where r.person_id = P_PERSON_ID
864 and r.date_from >= p_effective_date );
865 --
866 select 'Y'
867 into l_delete_permitted
868 from sys.dual
869 where not exists (
870 select null
871 from per_letter_request_lines r
872 where exists (
873 select null
874 from per_assignments_f a
875 where a.person_id = P_PERSON_ID
876 and a.effective_start_date >= p_effective_date
877 and a.assignment_id = r.assignment_id));
878 --
879 exception
880 when NO_DATA_FOUND then
881 hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
882 hr_utility.raise_error;
883 end;
884 --
885 if g_debug then
886 hr_utility.set_location(l_proc, 30);
887 end if;
888 --
889 begin
890 --
891 select 'Y'
892 into l_delete_permitted
893 from sys.dual
894 where not exists (
895 select null
896 from per_contact_relationships r
897 where r.person_id = P_PERSON_ID
898 or r.contact_person_id = P_PERSON_ID);
899 --
900 exception
901 when NO_DATA_FOUND then
902 hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
903 hr_utility.raise_error;
904 end;
905 --
906 if g_debug then
907 hr_utility.set_location(l_proc, 40);
908 end if;
909 --
910 begin
911 --
912 select 'Y'
913 into l_delete_permitted
914 from sys.dual
915 where not exists (
916 select null
917 from per_events e
918 where e.internal_contact_person_id = P_PERSON_ID);
919 --
920 exception
921 when NO_DATA_FOUND then
922 hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
923 hr_utility.raise_error;
924 end;
925 --
926 if g_debug then
927 hr_utility.set_location(l_proc, 50);
928 end if;
929 --
930 begin
931 --
932 select 'Y'
933 into l_delete_permitted
934 from sys.dual
935 where not exists (
936 select null
937 from per_bookings b
938 where b.person_id = P_PERSON_ID);
939 --
940 exception
941 when NO_DATA_FOUND then
942 hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
943 hr_utility.raise_error;
944 end;
945 --
946 if g_debug then
947 hr_utility.set_location(l_proc, 60);
948 end if;
949 --
950 begin
951 --
952 select 'Y'
953 into l_delete_permitted
954 from sys.dual
955 where 1 >= (
956 select count(*)
957 from per_assignments_f a
958 where a.person_id = P_PERSON_ID
959 and a.effective_start_date >= p_effective_date);
960 --
961 exception
962 when NO_DATA_FOUND then
963 hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
964 hr_utility.raise_error;
965 end;
966 --
967 if g_debug then
968 hr_utility.set_location(l_proc, 70);
969 end if;
970 --
971 begin
972 --
973 select 'Y'
974 into l_delete_permitted
975 from sys.dual
976 where not exists (
977 select null
978 from per_assignments_f a
979 where a.recruiter_id = P_PERSON_ID
980 or a.supervisor_id = P_PERSON_ID);
981 --
982 exception
983 when NO_DATA_FOUND then
984 hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
985 hr_utility.raise_error;
986 end;
987 --
988 if g_debug then
989 hr_utility.set_location(l_proc, 80);
990 end if;
991 --
992 begin
993 --
994 select 'Y'
995 into l_delete_permitted
996 from sys.dual
997 where not exists (
998 select null
999 from per_periods_of_service p
1000 where p.termination_accepted_person_id = P_PERSON_ID);
1001 --
1002 exception
1003 when NO_DATA_FOUND then
1004 hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
1005 hr_utility.raise_error;
1006 end;
1007 --
1008 if g_debug then
1009 hr_utility.set_location(l_proc, 90);
1010 end if;
1011 --
1012 begin
1013 --
1014 select 'Y'
1015 into l_delete_permitted
1016 from sys.dual
1017 where not exists (
1018 select null
1019 from per_person_analyses a
1020 where a.person_id = P_PERSON_ID);
1021 --
1022 exception
1023 when NO_DATA_FOUND then
1024 hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
1025 hr_utility.raise_error;
1026 end;
1027 --
1028 if g_debug then
1029 hr_utility.set_location(l_proc, 100);
1030 end if;
1031 --
1032 begin
1033 --
1034 select 'Y'
1035 into l_delete_permitted
1036 from sys.dual
1037 where not exists (
1038 select null
1039 from per_absence_attendances a
1040 where a.person_id = P_PERSON_ID);
1041 --
1042 exception
1043 when NO_DATA_FOUND then
1044 hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
1045 hr_utility.raise_error;
1046 end;
1047 --
1048 if g_debug then
1049 hr_utility.set_location(l_proc, 110);
1050 end if;
1051 --
1052 begin
1053 --
1054 select 'Y'
1055 into l_delete_permitted
1056 from sys.dual
1057 where not exists (
1058 select null
1059 from per_absence_attendances a
1060 where a.authorising_person_id = P_PERSON_ID
1061 or a.replacement_person_id = P_PERSON_ID);
1062 --
1063 exception
1064 when NO_DATA_FOUND then
1065 hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
1066 hr_utility.raise_error;
1067 end;
1068 --
1069 if g_debug then
1070 hr_utility.set_location(l_proc, 120);
1071 end if;
1072 --
1073 begin
1074 --
1075 select 'Y'
1076 into l_delete_permitted
1077 from sys.dual
1078 where not exists (
1079 select null
1080 from per_recruitment_activities r
1081 where r.authorising_person_id = P_PERSON_ID
1082 or r.internal_contact_person_id = P_PERSON_ID);
1083 --
1084 exception
1085 when NO_DATA_FOUND then
1086 hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
1087 hr_utility.raise_error;
1088 end;
1089 --
1090 if g_debug then
1091 hr_utility.set_location(l_proc, 130);
1092 end if;
1093 --
1094 begin
1095 --
1096 select 'Y'
1097 into l_delete_permitted
1098 from sys.dual
1099 where not exists (
1100 select null
1101 from per_appraisals apr
1102 where apr.appraisee_person_id = P_PERSON_ID
1103 or apr.appraiser_person_id = P_PERSON_ID);
1104 --
1105 exception
1106 when NO_DATA_FOUND then
1107 fnd_message.set_name('PER','PER_52467_APR_PAR_REC_NO_DEL');
1108 fnd_message.raise_error;
1109 end;
1110 --
1111 if g_debug then
1112 hr_utility.set_location(l_proc, 140);
1113 end if;
1114 --
1115 begin
1116 --
1117 select 'Y'
1118 into l_delete_permitted
1119 from sys.dual
1120 where not exists (
1121 select null
1122 from per_participants par
1123 where par.person_id = P_PERSON_ID);
1124 --
1125 exception
1126 when NO_DATA_FOUND then
1127 fnd_message.set_name('PER','PER_52467_APR_PAR_REC_NO_DEL');
1128 fnd_message.raise_error;
1129 end;
1130 --
1131 if g_debug then
1132 hr_utility.set_location(l_proc, 150);
1133 end if;
1134 --
1135 begin
1136 --
1137 select 'Y'
1138 into l_delete_permitted
1139 from sys.dual
1140 where not exists (
1141 select null
1142 from per_requisitions r
1143 where r.person_id = P_PERSON_ID);
1144 --
1145 exception
1146 when NO_DATA_FOUND then
1147 hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
1148 hr_utility.raise_error;
1149 end;
1150 --
1151 if g_debug then
1152 hr_utility.set_location(l_proc, 160);
1153 end if;
1154 --
1155 begin
1156 --
1157 select 'Y'
1158 into l_delete_permitted
1159 from sys.dual
1160 where not exists (
1161 select null
1162 from per_vacancies v
1163 where v.recruiter_id = P_PERSON_ID);
1164 --
1165 exception
1166 when NO_DATA_FOUND then
1167 hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
1168 hr_utility.raise_error;
1169 end;
1170 --
1171 if g_debug then
1172 hr_utility.set_location(l_proc, 170);
1173 end if;
1174 --
1175 -- Any discretionary link element entries?
1176 --
1177 begin
1178 --
1179 select 'Y'
1180 into l_delete_permitted
1181 from sys.dual
1182 where not exists (
1183 select null
1184 from pay_element_entries_f e,
1185 per_assignments_f a,
1186 pay_element_links_f l
1187 where a.person_id = P_PERSON_ID
1188 and a.assignment_id = e.assignment_id
1189 and e.element_link_id = l.element_link_id
1190 and l.standard_link_flag = 'N');
1191 --
1192 exception
1193 when NO_DATA_FOUND then
1194 hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
1195 hr_utility.raise_error;
1196 end;
1197 --
1198 if g_debug then
1199 hr_utility.set_location(l_proc, 180);
1200 end if;
1201 --
1202 -- Any entry adjustments, overrides etc.?
1203 -- (We cannot capture manual enty of standard link entries)
1204 --
1205 begin
1206 --
1207 select 'Y'
1208 into l_delete_permitted
1209 from sys.dual
1210 where not exists (
1211 select null
1212 from pay_element_entries_f e,
1213 per_assignments_f a
1214 where a.person_id = P_PERSON_ID
1215 and a.assignment_id = e.assignment_id
1216 and e.entry_type <> 'E');
1217 --
1218 exception
1219 when NO_DATA_FOUND then
1220 hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1221 hr_utility.raise_error;
1222 end;
1223 --
1224 if g_debug then
1225 hr_utility.set_location(l_proc, 190);
1226 end if;
1227 --
1228 begin
1229 --
1230 select 'Y'
1231 into l_delete_permitted
1232 from sys.dual
1233 where not exists (
1234 select null
1235 from per_assignment_extra_info i
1236 where exists (
1237 select null
1238 from per_assignments_f a
1239 where a.person_id = P_PERSON_ID
1240 and a.assignment_id = i.assignment_id));
1241 --
1242 exception
1243 when NO_DATA_FOUND then
1244 hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1245 hr_utility.raise_error;
1246 end;
1247 --
1248 if g_debug then
1249 hr_utility.set_location(l_proc, 200);
1250 end if;
1251 --
1252 begin
1253 --
1254 select 'Y'
1255 into l_delete_permitted
1256 from sys.dual
1257 where not exists (
1258 select null
1259 from per_secondary_ass_statuses s
1260 where exists (
1261 select null
1262 from per_assignments_f a
1263 where a.person_id = P_PERSON_ID
1264 and a.assignment_id = s.assignment_id));
1265 --
1266 exception
1267 when NO_DATA_FOUND then
1268 hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
1269 hr_utility.raise_error;
1270 end;
1271 --
1272 if g_debug then
1273 hr_utility.set_location(l_proc, 210);
1274 end if;
1275 --
1276 begin
1277 --
1278 select 'Y'
1279 into l_delete_permitted
1280 from sys.dual
1281 where not exists (
1282 select null
1283 from per_events e
1284 where exists (
1285 select null
1286 from per_assignments_f a
1287 where a.person_id = P_PERSON_ID
1288 and a.assignment_id = e.assignment_id));
1289 --
1290 exception
1291 when NO_DATA_FOUND then
1292 hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1293 hr_utility.raise_error;
1294 end;
1295 --
1296 if g_debug then
1297 hr_utility.set_location(l_proc, 220);
1298 end if;
1299 --
1300 begin
1301 --
1302 select 'Y'
1303 into l_delete_permitted
1304 from sys.dual
1305 where not exists (
1306 select null
1307 from per_spinal_point_placements_f p
1308 where exists (
1309 select null
1310 from per_assignments_f a
1311 where a.person_id = P_PERSON_ID
1312 and a.assignment_id = p.assignment_id));
1313 --
1314 exception
1315 when NO_DATA_FOUND then
1316 hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1317 hr_utility.raise_error;
1318 end;
1319 --
1320 if g_debug then
1321 hr_utility.set_location(l_proc, 230);
1322 end if;
1323 --
1324 begin
1325 --
1326 select 'Y'
1327 into l_delete_permitted
1328 from sys.dual
1329 where not exists (
1330 select null
1331 from per_quickpaint_result_text t
1332 where exists (
1333 select null
1334 from per_assignments_f a
1335 where a.person_id = P_PERSON_ID
1336 and a.assignment_id = t.assignment_id));
1337 --
1338 exception
1339 when NO_DATA_FOUND then
1340 hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1341 hr_utility.raise_error;
1342 end;
1343 --
1344 if g_debug then
1345 hr_utility.set_location(l_proc, 240);
1346 end if;
1347 --
1348 begin
1349 --
1350 select 'Y'
1351 into l_delete_permitted
1352 from sys.dual
1353 where not exists (
1354 select null
1355 from per_cobra_cov_enrollments c
1356 where exists (
1357 select null
1358 from per_assignments_f a
1359 where a.person_id = P_PERSON_ID
1360 and a.assignment_id = c.assignment_id));
1361 --
1362 exception
1363 when NO_DATA_FOUND then
1364 hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1365 hr_utility.raise_error;
1366 end;
1367 --
1368 if g_debug then
1369 hr_utility.set_location(l_proc, 250);
1370 end if;
1371 --
1372 OPEN ben_ext_chg_log (p_person_id);
1373 --
1374 LOOP
1375 --
1376 FETCH ben_ext_chg_log INTO l_id;
1377 EXIT WHEN ben_ext_chg_log%NOTFOUND;
1378 --
1379 DELETE FROM ben_ext_chg_evt_log
1380 WHERE CURRENT OF ben_ext_chg_log;
1381 --
1382 END LOOP;
1383 --
1384 CLOSE ben_ext_chg_log;
1385 --
1386 -- Bug 3524713 Ends Here
1387 --
1388 ben_person_delete.perform_ri_check(p_person_id);
1389 --
1390 if g_debug then
1391 hr_utility.set_location(l_proc, 260);
1392 end if;
1393 --
1394 -- Validation for OTA.
1395 --
1396 per_ota_predel_validation.ota_predel_per_validation(P_PERSON_ID);
1397 --
1398 -- validation for PA
1399 --
1400 if g_debug then
1401 hr_utility.set_location(l_proc, 270);
1402 end if;
1403 --
1404 pa_person.pa_predel_validation(P_PERSON_ID);
1405 --
1406 -- validation for WIP
1407 --
1408 if g_debug then
1409 hr_utility.set_location(l_proc, 280);
1410 end if;
1411 --
1412 wip_person.wip_predel_validation(P_PERSON_ID);
1413 --
1414 -- validation for ENG
1415 --
1416 if g_debug then
1417 hr_utility.set_location(l_proc, 290);
1418 end if;
1419 --
1420 eng_person.eng_predel_validation(P_PERSON_ID);
1421 --
1422 -- validation for AP
1423 --
1424 if g_debug then
1425 hr_utility.set_location(l_proc, 300);
1426 end if;
1427 --
1428 ap_person.ap_predel_validation(P_PERSON_ID);
1429 --
1430 -- validation for FA
1431 --
1432 if g_debug then
1433 hr_utility.set_location(l_proc, 310);
1434 end if;
1435 --
1436 fa_person.fa_predel_validation(P_PERSON_ID);
1437 --
1438 -- validation for PO
1439 --
1440 if g_debug then
1441 hr_utility.set_location(l_proc, 320);
1442 end if;
1443 --
1444 po_person.po_predel_validation(P_PERSON_ID);
1445 --
1446 -- validation for RCV
1447 --
1448 if g_debug then
1449 hr_utility.set_location(l_proc, 330);
1450 end if;
1451 --
1452 rcv_person.rcv_predel_validation(P_PERSON_ID);
1453 --
1454 if g_debug then
1455 hr_utility.set_location('Leaving:'||l_proc, 999);
1456 end if;
1457 --
1458 END strong_predel_validation;
1459 --
1460 -- ----------------------------------------------------------------------------
1461 -- |----------------------< people_default_deletes >--------------------------|
1462 -- ----------------------------------------------------------------------------
1463 --
1464 -- Description :
1465 --
1466 -- Delete routine for deleting information set up as default when people
1467 -- are created. Used primarily for delete on PERPEEPI (Enter Person).
1468 -- The strong_predel_validation should first be performed to ensure that
1469 -- no additional info (apart from default) has been entered.
1470 --
1471 -- NOTE
1472 --
1473 -- See delete_person for p_form_call details. Further, p_form_call is
1474 -- set to TRUE when this procedure is called from check_contact as
1475 -- there is no need to check the existance of the contact.
1476 --
1477 PROCEDURE people_default_deletes (p_person_id IN number)
1478 IS
1479 --
1480 l_assignment_id number(15);
1481 l_proc varchar2(72) := g_package||'people_default_deletes';
1482 l_pk1_value1 varchar2(72) := p_person_id;
1483
1484 --
1485 CURSOR lock_person_rows IS
1486 select person_id
1487 from per_people_f
1488 where person_id = P_PERSON_ID
1489 FOR UPDATE;
1490 --
1491 CURSOR attached_docs_cursor1 IS
1492 SELECT attached_document_id
1493 FROM fnd_attached_documents
1494 WHERE pk1_value = l_pk1_value1
1495 AND entity_name IN('PER_ALL_PEOPLE_F','PER_PEOPLE_F'); -- added for bug 9411745
1496 --
1497 cursor delattachments_cursor1 (x_attached_document_id in number) is
1498 select datatype_id
1499 from fnd_attached_docs_form_vl
1500 where attached_document_id = x_attached_document_id;
1501 --
1502 l_datatype_id number;
1503 l_attached_document_id number;
1504 deldatarec1 delattachments_cursor1%ROWTYPE;
1505 --
1506 BEGIN
1507 --
1508 if g_debug then
1509 hr_utility.set_location('Entering:'||l_proc, 10);
1510 end if;
1511 --
1512 hr_person_internal.person_existance_check(P_PERSON_ID);
1513 --
1514 if g_debug then
1515 hr_utility.set_location(l_proc, 20);
1516 end if;
1517 --
1518 open LOCK_PERSON_ROWS;
1519 --
1520 -- Now start cascade.
1521 --
1522 -- Start of Fix for WWBUG 1294400
1523 -- All of benefits is a child of HR and PAY so its safe to delete
1524 -- benefits stuff first.
1525 --
1526 ben_person_delete.delete_ben_rows(p_person_id);
1527 --
1528 -- End of Fix for WWBUG 1294400
1529 --
1530 if g_debug then
1531 hr_utility.set_location(l_proc, 30);
1532 end if;
1533 --
1534 hr_security.delete_per_from_list(P_PERSON_ID);
1535 --
1536 if g_debug then
1537 hr_utility.set_location(l_proc, 40);
1538 end if;
1539 --
1540 -- Lock assignments now, delete at end.
1541 -- Can select into a variable as max one assignment should exist (as
1542 -- strong_predel_validation has already been performed).
1543 -- May not be assignments (for contacts, for eg) so exception.
1544 --
1545 begin
1546 --
1547 select ass.assignment_id
1548 into l_assignment_id
1549 from per_assignments_f ass
1550 where ass.person_id = P_PERSON_ID
1551 FOR UPDATE;
1552 exception
1553 when NO_DATA_FOUND then null;
1554 end;
1555 --
1556 if g_debug then
1557 hr_utility.set_location(l_proc, 50);
1558 end if;
1559 --
1560 begin
1561 --
1562 delete from pay_personal_payment_methods p
1563 where p.assignment_id = l_assignment_id;
1564 exception
1565 when NO_DATA_FOUND then null;
1566 end;
1567 --
1568 if g_debug then
1569 hr_utility.set_location(l_proc, 60);
1570 end if;
1571 --
1572 begin
1573 --
1574 delete from per_assignment_budget_values_f v
1575 where v.assignment_id = l_assignment_id;
1576 exception
1577 when NO_DATA_FOUND then null;
1578 end;
1579 --
1580 if g_debug then
1581 hr_utility.set_location(l_proc, 70);
1582 end if;
1583 --
1584 begin
1585 delete from per_addresses a
1586 where a.person_id = P_PERSON_ID;
1587 exception
1588 when NO_DATA_FOUND then null;
1589 end;
1590 --
1591 if g_debug then
1592 hr_utility.set_location(l_proc, 80);
1593 end if;
1594 --
1595 begin
1596 delete from per_phones a
1597 where a.parent_id = P_PERSON_ID
1598 and a.parent_table = 'PER_ALL_PEOPLE_F';
1599 exception
1600 when NO_DATA_FOUND then null;
1601 end;
1602 --
1603 if g_debug then
1604 hr_utility.set_location(l_proc, 90);
1605 end if;
1606 --
1607 begin
1608 delete from pay_cost_allocations_f a
1609 where a.assignment_id = l_assignment_id;
1610 exception
1611 when NO_DATA_FOUND then null;
1612 end;
1613 --
1614 if g_debug then
1615 hr_utility.set_location(l_proc, 100);
1616 end if;
1617 --
1618 begin
1619 delete from pay_element_entry_values_f v
1620 where v.element_entry_id in
1621 (select e.element_entry_id
1622 from pay_element_entries_f e
1623 where e.assignment_id = l_assignment_id);
1624 exception
1625 when NO_DATA_FOUND then null;
1626 end;
1627 --
1628 if g_debug then
1629 hr_utility.set_location(l_proc, 110);
1630 end if;
1631 --
1632 begin
1633 delete from pay_run_results r
1634 where r.source_type = 'E'
1635 and r.source_id in
1636 (select e.element_entry_id
1637 from pay_element_entries_f e
1638 where e.assignment_id = l_assignment_id);
1639 exception
1640 when NO_DATA_FOUND then null;
1641 end;
1642 --
1643 if g_debug then
1644 hr_utility.set_location(l_proc, 120);
1645 end if;
1646 --
1647 begin
1648 delete from pay_element_entries_f e
1649 where e.assignment_id = l_assignment_id;
1650 exception
1651 when NO_DATA_FOUND then null;
1652 end;
1653 --
1654 if g_debug then
1655 hr_utility.set_location(l_proc, 130);
1656 end if;
1657 --
1658 -- No exception, should succeed.
1659 --
1660 begin
1661 delete from per_assignments_f ass
1662 where ass.assignment_id = l_assignment_id;
1663 end;
1664 --
1665 if g_debug then
1666 hr_utility.set_location(l_proc, 140);
1667 end if;
1668 --
1669 begin
1670 delete from per_periods_of_service p
1671 where p.person_id = P_PERSON_ID;
1672 exception
1673 when NO_DATA_FOUND then null;
1674 end;
1675 --
1676 if g_debug then
1677 hr_utility.set_location(l_proc, 150);
1678 end if;
1679 --
1680 begin
1681 delete from per_applications a
1682 where a.person_id = P_PERSON_ID;
1683 exception
1684 when NO_DATA_FOUND then null;
1685 end;
1686 --
1687 -- Added this delete for quickhire checklists
1688 --
1689 if g_debug then
1690 hr_utility.set_location(l_proc, 160);
1691 end if;
1692 --
1693 begin
1694 delete from per_checklist_items
1695 where person_id = P_PERSON_ID;
1696 exception
1697 when NO_DATA_FOUND then null;
1698 end;
1699 --
1700 -- End addition for quickhire checklists
1701 --
1702 --
1703 if g_debug then
1704 hr_utility.set_location(l_proc, 170);
1705 end if;
1706 --
1707 close LOCK_PERSON_ROWS;
1708 --
1709 if g_debug then
1710 hr_utility.set_location(l_proc, 180);
1711 end if;
1712 --
1713 begin
1714 delete from per_people_f
1715 where person_id = P_PERSON_ID;
1716 exception
1717 when NO_DATA_FOUND then null;
1718 end;
1719 --
1720 if g_debug then
1721 hr_utility.set_location(l_proc, 190);
1722 end if;
1723 --
1724 begin
1725 delete from per_periods_of_placement p
1726 where p.person_id = P_PERSON_ID;
1727 exception
1728 when NO_DATA_FOUND then null;
1729 end;
1730 --
1731 if g_debug then
1732 hr_utility.set_location('Leaving:'||l_proc, 999);
1733 end if;
1734 --
1735 begin
1736 for attached_docs_rec in attached_docs_cursor1
1737 LOOP
1738 if attached_docs_cursor1%NOTFOUND then
1739 return;
1740 end if;
1741 l_attached_document_id := attached_docs_rec.attached_document_id;
1742 open delattachments_cursor1 (l_attached_document_id);
1743 FETCH delattachments_cursor1 into deldatarec1;
1744 if delattachments_cursor1%NOTFOUND then
1745 return;
1746 end if;
1747 l_datatype_id := deldatarec1.datatype_id ;
1748 FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
1749 l_datatype_id,
1750 'Y' );
1751 CLOSE delattachments_cursor1;
1752 END LOOP;
1753 exception
1754 when NO_DATA_FOUND then null;
1755 end;
1756 if g_debug then
1757 hr_utility.set_location('Leaving:'||l_proc, 999);
1758 end if;
1759 --
1760 END people_default_deletes;
1761 --
1762 -- ----------------------------------------------------------------------------
1763 -- |---------------------< applicant_default_deletes >------------------------|
1764 -- ----------------------------------------------------------------------------
1765 --
1766 -- Description :
1767 --
1768 -- Delete routine for deleting information set up as default when
1769 -- applicants are entered. Used primarily for delete on PERREAQE
1770 -- (Applicant Quick Entry). The strong_predel_validation should first be
1771 -- performed to ensure that no additional info (apart from default) has
1772 -- been entered.
1773 --
1774 PROCEDURE applicant_default_deletes(p_person_id IN number)
1775 IS
1776 --
1777 l_assignment_id number(15);
1778 l_proc varchar2(72) := g_package||'applicant_default_deletes';
1779 l_pk1_value2 varchar2(72) := p_person_id;
1780
1781 --
1782 CURSOR lock_person_rows IS
1783 SELECT person_id
1784 FROM per_people_f
1785 WHERE person_id = P_PERSON_ID
1786 FOR UPDATE;
1787 --
1788 CURSOR attached_docs_cursor2 IS
1789 SELECT attached_document_id
1790 FROM fnd_attached_documents
1791 WHERE pk1_value = l_pk1_value2
1792 AND entity_name IN('PER_ALL_PEOPLE_F','PER_PEOPLE_F'); -- added for bug 9411745
1793 --
1794 cursor delattachments_cursor2 (x_attached_document_id in number) is
1795 select datatype_id
1796 from fnd_attached_docs_form_vl
1797 where attached_document_id = x_attached_document_id;
1798 --
1799 l_datatype_id number;
1800 l_attached_document_id number;
1801 deldatarec2 delattachments_cursor2%ROWTYPE;
1802 --
1803 BEGIN
1804 --
1805 if g_debug then
1806 hr_utility.set_location('Entering:'||l_proc, 10);
1807 end if;
1808 --
1809 hr_person_internal.person_existance_check(P_PERSON_ID);
1810 --
1811 if g_debug then
1812 hr_utility.set_location(l_proc, 10);
1813 end if;
1814 --
1815 open LOCK_PERSON_ROWS;
1816 --
1817 -- Now start cascade.
1818 --
1819 if g_debug then
1820 hr_utility.set_location(l_proc, 20);
1821 end if;
1822 --
1823 begin
1824 delete from per_person_list l
1825 where l.person_id = P_PERSON_ID;
1826 exception
1827 when NO_DATA_FOUND then null;
1828 end;
1829 --
1830 if g_debug then
1831 hr_utility.set_location(l_proc, 30);
1832 end if;
1833 --
1834 -- Can select into a variable as only one assignment should exist (as
1835 -- strong_predel_validation has already been performed).
1836 --
1837 begin
1838 select ass.assignment_id
1839 into l_assignment_id
1840 from per_assignments_f ass
1841 where ass.person_id = P_PERSON_ID
1842 FOR UPDATE;
1843 end;
1844 --
1845 if g_debug then
1846 hr_utility.set_location(l_proc, 40);
1847 end if;
1848 --
1849 begin
1850 delete from per_addresses a
1851 where a.person_id = P_PERSON_ID;
1852 exception
1853 when NO_DATA_FOUND then null;
1854 end;
1855 --
1856 if g_debug then
1857 hr_utility.set_location(l_proc, 50);
1858 end if;
1859 --
1860 begin
1861 delete from per_phones a
1862 where a.parent_id = P_PERSON_ID
1863 and a.parent_table = 'PER_ALL_PEOPLE_F';
1864 exception
1865 when NO_DATA_FOUND then null;
1866 end;
1867 --
1868 if g_debug then
1869 hr_utility.set_location(l_proc, 60);
1870 end if;
1871 --
1872 begin
1873 delete from per_assignments_f ass
1874 where ass.assignment_id = l_assignment_id;
1875 end;
1876 --
1877 if g_debug then
1878 hr_utility.set_location(l_proc, 70);
1879 end if;
1880 --
1881 begin
1882 delete from per_applications a
1883 where a.person_id = P_PERSON_ID;
1884 exception
1885 when NO_DATA_FOUND then null;
1886 end;
1887 --
1888 -- Added this delete for quickhire checklists
1889 --
1890 if g_debug then
1891 hr_utility.set_location(l_proc, 80);
1892 end if;
1893 --
1894 begin
1895 delete from per_checklist_items
1896 where person_id = P_PERSON_ID;
1897 exception
1898 when NO_DATA_FOUND then null;
1899 end;
1900 --
1901 -- End addition for quickhire checklists
1902 --
1903 -- Added this delete for PTU
1904 --
1905 if g_debug then
1906 hr_utility.set_location(l_proc, 90);
1907 end if;
1908 --
1909 begin
1910 delete from per_person_type_usages_f
1911 where person_id = P_PERSON_ID;
1912 exception
1913 when NO_DATA_FOUND then null;
1914 end;
1915 --
1916 -- End addition for PTU
1917 --
1918 if g_debug then
1919 hr_utility.set_location(l_proc, 100);
1920 end if;
1921 --
1922 close LOCK_PERSON_ROWS;
1923 --
1924 if g_debug then
1925 hr_utility.set_location(l_proc, 110);
1926 end if;
1927 --
1928 begin
1929 delete from per_people_f
1930 where person_id = P_PERSON_ID;
1931 exception
1932 when NO_DATA_FOUND then null;
1933 end;
1934 --
1935 if g_debug then
1936 hr_utility.set_location('Leaving:'||l_proc, 999);
1937 end if;
1938 --
1939 begin
1940 for attached_docs_rec in attached_docs_cursor2
1941 LOOP
1942 if attached_docs_cursor2%NOTFOUND then
1943 return;
1944 end if;
1945 l_attached_document_id := attached_docs_rec.attached_document_id;
1946 open delattachments_cursor2 (l_attached_document_id);
1947 FETCH delattachments_cursor2 into deldatarec2;
1948 if delattachments_cursor2%NOTFOUND then
1949 return;
1950 end if;
1951 l_datatype_id := deldatarec2.datatype_id ;
1952 FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
1953 l_datatype_id,
1954 'Y' );
1955 CLOSE delattachments_cursor2;
1956 END LOOP;
1957 exception
1958 when NO_DATA_FOUND then null;
1959 end;
1960 if g_debug then
1961 hr_utility.set_location('Leaving:'||l_proc, 999);
1962 end if;
1963 --
1964 END applicant_default_deletes;
1965 --
1966 -- ----------------------------------------------------------------------------
1967 -- |---------------------------< delete_person >------------------------------|
1968 -- ----------------------------------------------------------------------------
1969 --
1970 PROCEDURE delete_person (p_person_id IN number
1971 ,p_effective_date IN date)
1972 IS
1973 --
1974 l_pk1_value3 varchar2(72) := p_person_id;
1975 --
1976 CURSOR csr_this_persons_contacts IS
1977 SELECT contact_person_id,
1978 contact_relationship_id
1979 FROM per_contact_relationships
1980 WHERE person_id = P_PERSON_ID;
1981 --
1982 CURSOR csr_lock_person_rows IS
1983 SELECT person_id
1984 FROM per_people_f
1985 WHERE person_id = P_PERSON_ID
1986 FOR UPDATE;
1987 --
1988 CURSOR csr_lock_assignment_rows IS
1989 SELECT assignment_id
1990 FROM per_assignments_f
1991 WHERE person_id = P_PERSON_ID
1992 FOR UPDATE;
1993 --
1994 CURSOR csr_delete_components IS
1995 SELECT pp.pay_proposal_id
1996 FROM per_pay_proposals pp,
1997 per_assignments_f pa
1998 WHERE pa.person_id = P_PERSON_ID
1999 AND pa.assignment_id = pp.assignment_id
2000 FOR UPDATE;
2001 --
2002 CURSOR csr_medical_assessment_records IS
2003 SELECT medical_assessment_id,
2004 object_version_number
2005 FROM per_medical_Assessments pma
2006 WHERE pma.person_id = p_person_id;
2007 --
2008 CURSOR csr_work_incidents IS
2009 SELECT incident_id, object_version_number
2010 FROM per_work_incidents
2011 WHERE person_id = p_person_id;
2012 --
2013 CURSOR csr_disabilities IS
2014 SELECT disability_id, object_version_number,
2015 effective_start_date, effective_end_date
2016 FROM per_disabilities_f
2017 WHERE person_id = p_person_id;
2018 --
2019 CURSOR csr_roles IS
2020 SELECT role_id, object_version_number
2021 FROM per_roles
2022 WHERE person_id= p_person_id;
2023 --
2024 CURSOR csr_ptu IS
2025 SELECT distinct person_type_usage_id
2026 FROM per_person_type_usages_f ptu
2027 WHERE ptu.person_id = p_person_id
2028 ORDER BY person_type_usage_id;
2029 --
2030 CURSOR csr_asg IS
2031 SELECT distinct assignment_id
2032 FROM per_assignments_f
2033 WHERE person_id = p_person_id;
2034 --
2035 CURSOR attached_docs_cursor IS
2036 SELECT attached_document_id
2037 FROM fnd_attached_documents
2038 WHERE pk1_value = l_pk1_value3
2039 AND entity_name IN('PER_ALL_PEOPLE_F','PER_PEOPLE_F'); -- added for bug 9411745
2040 --
2041 cursor delattachments_cursor (x_attached_document_id in number) is
2042 select datatype_id
2043 from fnd_attached_docs_form_vl
2044 where attached_document_id = x_attached_document_id;
2045 --
2046 --
2047 -- local variables
2048 --
2049 l_dummy number(3) := null; /* Bug 941 591 */
2050 l_proposal_id number;
2051 l_review_cursor number;
2052 l_rows_processed number;
2053 l_incident_id per_work_incidents.person_id%TYPE;
2054 l_disability_id per_disabilities_f.disability_id%TYPE;
2055 l_object_version_no per_disabilities_f.object_version_number%TYPE;
2056 l_ovn_roles per_roles.object_version_number%TYPE;
2057 l_role_id per_roles.role_id%TYPE;
2058 --
2059 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
2060 l_effective_date date;
2061 l_object_version_number number;
2062 l_effective_start_date date;
2063 l_effective_end_date date;
2064 --
2065 -- bug fix 3732129 starts here.
2066 -- to improve performance assignment id fetched into a pl/sql table.
2067 --
2068 type assignmentid is table of per_all_assignments_f.assignment_id%type
2069 index by binary_integer;
2070 --
2071 l_assignment_id assignmentid;
2072 l_proc varchar2(72) := g_package||'delete_person';
2073 --
2074 l_datatype_id number;
2075 l_attached_document_id number;
2076 deldatarec delattachments_cursor%ROWTYPE;
2077 --
2078 -- Fix for 4490489 starts here
2079 l_party_id number(15);
2080 l_count number(15);
2081
2082 -- Fix for 4490489 ends here
2083 BEGIN
2084 --
2085 if g_debug then
2086 hr_utility.set_location('Entering:'||l_proc, 10);
2087 end if;
2088 --
2089 -- Lock person rows, delete at end of procedure.
2090 --
2091 OPEN csr_lock_person_rows;
2092 --
2093 -- Now start cascade.
2094 --
2095 if g_debug then
2096 hr_utility.set_location(l_proc, 20);
2097 end if;
2098 --
2099 -- bug fix 3732129 starts here.
2100 -- fetching the assignment ids into a pl/sql table.
2101 --
2102 OPEN csr_asg;
2103 FETCH csr_asg bulk collect into l_assignment_id;
2104 CLOSE csr_asg;
2105 --
2106 -- bug fix 3732129 ends here.
2107 --
2108 if g_debug then
2109 hr_utility.set_location(l_proc, 30);
2110 end if;
2111 --
2112 begin
2113 --
2114 update per_requisitions r
2115 set r.person_id = null
2116 where r.person_id = P_PERSON_ID;
2117 --
2118 exception
2119 when NO_DATA_FOUND then
2120 if g_debug then
2121 hr_utility.set_location(l_proc, 40);
2122 end if;
2123 end;
2124 --
2125 if g_debug then
2126 hr_utility.set_location(l_proc, 50);
2127 end if;
2128 --
2129 begin
2130 -- bug fix 3732129.
2131 -- Delete statement modified to improve performance.
2132 delete from per_letter_request_lines l
2133 where l.person_id = P_PERSON_ID;
2134 --
2135 forall i in 1..l_assignment_id.count
2136 delete from per_letter_request_lines l
2137 where l.assignment_id = l_assignment_id(i);
2138 --
2139 exception
2140 when NO_DATA_FOUND then
2141 if g_debug then
2142 hr_utility.set_location(l_proc, 60);
2143 end if;
2144 end;
2145 --
2146 -- Leave per_letter_requests for the moment - may not be necessary to
2147 -- delete the parent with no children which requires some work with
2148 -- cursors.
2149 --
2150 if g_debug then
2151 hr_utility.set_location(l_proc, 70);
2152 end if;
2153 --
2154 begin
2155 --
2156 delete from per_absence_attendances a
2157 where a.person_id = P_PERSON_ID;
2158 --
2159 exception
2160 when NO_DATA_FOUND then
2161 if g_debug then
2162 hr_utility.set_location(l_proc, 80);
2163 end if;
2164 end;
2165 --
2166 if g_debug then
2167 hr_utility.set_location(l_proc, 90);
2168 end if;
2169 --
2170 begin
2171 --
2172 update per_absence_attendances a
2173 set a.authorising_person_id = null
2174 where a.authorising_person_id = P_PERSON_ID;
2175 --
2176 exception
2177 when NO_DATA_FOUND then
2178 if g_debug then
2179 hr_utility.set_location(l_proc, 100);
2180 end if;
2181 end;
2182 --
2183 if g_debug then
2184 hr_utility.set_location(l_proc, 110);
2185 end if;
2186 --
2187 begin
2188 --
2189 update per_absence_attendances a
2190 set a.replacement_person_id = null
2191 where a.replacement_person_id = P_PERSON_ID;
2192 --
2193 exception
2194 when NO_DATA_FOUND then
2195 if g_debug then
2196 hr_utility.set_location(l_proc, 120);
2197 end if;
2198 end;
2199 --
2200 if g_debug then
2201 hr_utility.set_location(l_proc, 130);
2202 end if;
2203 --
2204 begin
2205 --
2206 delete from per_person_analyses a
2207 where a.person_id = P_PERSON_ID;
2208 --
2209 exception
2210 when NO_DATA_FOUND then
2211 if g_debug then
2212 hr_utility.set_location(l_proc, 140);
2213 end if;
2214 end;
2215 --
2216 if g_debug then
2217 hr_utility.set_location(l_proc, 150);
2218 end if;
2219 --
2220 -- Delete of per_periods_of_service at end after delete of
2221 -- per_assignments_f.
2222 --
2223 begin
2224 --
2225 update per_periods_of_service p
2226 set p.termination_accepted_person_id = null
2227 where p.termination_accepted_person_id = P_PERSON_ID;
2228 --
2229 exception
2230 when NO_DATA_FOUND then
2231 if g_debug then
2232 hr_utility.set_location(l_proc, 160);
2233 end if;
2234 end;
2235 --
2236 if g_debug then
2237 hr_utility.set_location(l_proc, 170);
2238 end if;
2239 --
2240 begin
2241 --
2242 update per_recruitment_activities r
2243 set r.authorising_person_id = null
2244 where r.authorising_person_id = P_PERSON_ID;
2245 --
2246 exception
2247 when NO_DATA_FOUND then
2248 if g_debug then
2249 hr_utility.set_location(l_proc, 180);
2250 end if;
2251 end;
2252 --
2253 if g_debug then
2254 hr_utility.set_location(l_proc, 190);
2255 end if;
2256 --
2257 begin
2258 --
2259 update per_recruitment_activities r
2260 set r.internal_contact_person_id = null
2261 where r.internal_contact_person_id = P_PERSON_ID;
2262 --
2263 exception
2264 when NO_DATA_FOUND then
2265 if g_debug then
2266 hr_utility.set_location(l_proc, 200);
2267 end if;
2268 end;
2269 --
2270 if g_debug then
2271 hr_utility.set_location(l_proc, 210);
2272 end if;
2273 --
2274 -- Bug 4873360 fix for performance repository sql id 14960331.
2275 -- Rewrote the delete query commented out below (and already once tuned for
2276 -- bug 3619599) to avoid a merge join cartesian and a full table scan on
2277 -- PER_PARTICIPANTS, HR_QUEST_ANSWER_VALUES and PER_APPRAISALS
2278 --
2279 -- Broke query into two peices using conditional logic in a pl/sql block to
2280 -- see if delete needs to be run.
2281 --
2282 begin -- Delete from HR_QUEST_ANSWER_VALUES
2283 begin -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
2284 begin
2285 select 1
2286 into l_dummy
2287 from sys.dual
2288 where exists (
2289 select null
2290 from per_participants par
2291 where par.person_id = P_PERSON_ID);
2292 exception
2293 when NO_DATA_FOUND then
2294 l_dummy := null;
2295 if g_debug then
2296 hr_utility.set_location(l_proc, 211);
2297 end if;
2298 end;
2299 if l_dummy = 1
2300 then
2301 l_dummy := null;
2302 delete from hr_quest_answer_values qsv2
2303 where qsv2.quest_answer_val_id in
2304 (select qsv.quest_answer_val_id
2305 from hr_quest_answer_values qsv
2306 ,hr_quest_answers qsa
2307 ,per_participants par
2308 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2309 and qsa.type_object_id = par.participant_id
2310 and qsa.type = 'PARTICIPANT'
2311 and par.person_id = P_PERSON_ID);
2312 end if;
2313 if g_debug then
2314 hr_utility.set_location(l_proc, 215);
2315 end if;
2316 end; -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
2317 begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
2318 begin
2319 select 2
2320 into l_dummy
2321 from sys.dual
2322 where exists (
2323 select null
2324 from per_appraisals apr
2325 where (apr.appraiser_person_id = P_PERSON_ID
2326 or apr.appraisee_person_id = P_PERSON_ID));
2327 exception
2328 when NO_DATA_FOUND then
2329 l_dummy := null;
2330 if g_debug then
2331 hr_utility.set_location(l_proc, 220);
2332 end if;
2333 end;
2334 if l_dummy = 2
2335 then
2336 l_dummy := null;
2337 delete from hr_quest_answer_values qsv2
2338 where qsv2.quest_answer_val_id in
2339 (select qsv.quest_answer_val_id
2340 from hr_quest_answer_values qsv
2341 ,hr_quest_answers qsa
2342 ,per_appraisals apr
2343 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2344 and qsa.type_object_id = apr.appraisal_id
2345 and qsa.type='APPRAISAL'
2346 and (apr.appraisee_person_id = P_PERSON_ID
2347 or apr.appraiser_person_id = P_PERSON_ID));
2348 if g_debug then
2349 hr_utility.set_location(l_proc, 221);
2350 end if;
2351 end if;
2352 end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
2353 end; -- Delete from HR_QUEST_ANSWER_VALUES
2354 -- original sql.
2355 /* -- Delete from HR_QUEST_ANSWER_VALUES
2356 delete from hr_quest_answer_values qsv2
2357 where qsv2.quest_answer_val_id in
2358 (select qsv.quest_answer_val_id
2359 from hr_quest_answer_values qsv
2360 ,hr_quest_answers qsa
2361 ,per_appraisals apr
2362 ,per_participants par
2363 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2364 and (qsa.type_object_id = apr.appraisal_id
2365 and qsa.type='APPRAISAL'
2366 and (apr.appraisee_person_id = P_PERSON_ID
2367 or apr.appraiser_person_id = P_PERSON_ID))
2368 or (qsa.type_object_id = par.participant_id
2369 and qsa.type='PARTICIPANT'
2370 and par.person_id = P_PERSON_ID)
2371 ); -- Fix 3619599
2372 exception
2373 when NO_DATA_FOUND then
2374 if g_debug then
2375 hr_utility.set_location(l_proc, 220);
2376 end if;
2377 end; */
2378 -- Now delete from HR_QUEST_ANSWERS
2379 begin
2380 --
2381 -- Fix 3619599 and 4894116
2382 delete from hr_quest_answers qsa2
2383 where qsa2.questionnaire_answer_id in
2384 (
2385 select qsa.questionnaire_answer_id
2386 from hr_quest_answers qsa
2387 ,per_appraisals apr
2388 where (qsa.type_object_id = apr.appraisal_id
2389 and qsa.type='APPRAISAL'
2390 and (apr.appraiser_person_id = p_person_id
2391 or apr.appraisee_person_id = p_person_id))
2392 Union All
2393
2394 select qsa.questionnaire_answer_id
2395 from hr_quest_answers qsa
2396 ,per_participants par
2397 where (qsa.type_object_id = par.participant_id
2398 and qsa.type='PARTICIPANT'
2399 and par.person_id = p_person_id )
2400 ) ;
2401
2402 --
2403 exception
2404 when NO_DATA_FOUND then
2405 if g_debug then
2406 hr_utility.set_location(l_proc, 230);
2407 end if;
2408 end;
2409 --
2410 -- Now delete from per_participants
2411 begin
2412
2413 -- Fix 4894116
2414 delete from per_participants par2
2415 where par2.participant_id in
2416 ( select par.participant_id
2417 from per_participants par
2418 where par.person_id = P_PERSON_Id
2419 union all
2420 select par.participant_id
2421 from per_participants par
2422 ,per_appraisals apr
2423 where
2424 (par.participation_in_column = 'APPRAISAL_ID'
2425 and par.participation_in_table = 'PER_APPRAISALS'
2426 and participation_in_id = apr.appraisal_id
2427 and (apr.appraisee_person_id = P_PERSON_ID
2428 or apr.appraiser_person_id = p_person_id)
2429 )
2430 );
2431 --
2432 exception
2433 when NO_DATA_FOUND then
2434 if g_debug then
2435 hr_utility.set_location(l_proc, 240);
2436 end if;
2437 end;
2438 --
2439 -- Now delete from per_appraisals
2440 /*
2441 --
2442 begin
2443 --
2444 delete from per_appraisals apr
2445 where -- apr.appraiser_person_id = P_PERSON_ID or
2446 -- changed as part of bug#8865114
2447 apr.appraisee_person_id = P_PERSON_ID;
2448 --
2449 exception
2450 when NO_DATA_FOUND then
2451 if g_debug then
2452 hr_utility.set_location(l_proc, 250);
2453 end if;
2454 end;
2455 --
2456 */ -- commented as part of bug fix 9885957 suggested by Talent Mgmt team.
2457
2458 -- Deleting from PER_COMPETENCE_ELEMENTS table Bug#11685107
2459
2460 begin
2461
2462 delete from per_competence_elements
2463 where person_id = p_person_id;
2464
2465 exception
2466
2467 when NO_DATA_FOUND then
2468 if g_debug then
2469 hr_utility.set_location(l_proc, 250);
2470 end if;
2471 end;
2472
2473 -- End of modification as part of Bug#11685107
2474
2475 if g_debug then
2476 hr_utility.set_location(l_proc, 260);
2477 end if;
2478 --
2479 hr_security.delete_per_from_list(P_PERSON_ID);
2480 --
2481 if g_debug then
2482 hr_utility.set_location(l_proc, 270);
2483 end if;
2484 --
2485 begin
2486 --
2487 update per_vacancies v
2488 set v.recruiter_id = null
2489 where v.recruiter_id = P_PERSON_ID;
2490 --
2491 exception
2492 when NO_DATA_FOUND then
2493 if g_debug then
2494 hr_utility.set_location(l_proc, 280);
2495 end if;
2496 end;
2497 --
2498 if g_debug then
2499 hr_utility.set_location(l_proc, 290);
2500 end if;
2501 --
2502 begin
2503 --
2504 update per_assignments_f ass
2505 set ass.person_referred_by_id = null
2506 where ass.person_referred_by_id = P_PERSON_ID;
2507 --
2508 exception
2509 when NO_DATA_FOUND then
2510 if g_debug then
2511 hr_utility.set_location(l_proc, 300);
2512 end if;
2513 end;
2514 --
2515 if g_debug then
2516 hr_utility.set_location(l_proc, 310);
2517 end if;
2518 --
2519 begin
2520 --
2521 update per_assignments_f a
2522 set a.recruiter_id = null
2523 where a.recruiter_id = P_PERSON_ID;
2524 --
2525 exception
2526 when NO_DATA_FOUND then
2527 if g_debug then
2528 hr_utility.set_location(l_proc, 320);
2529 end if;
2530 end;
2531 --
2532 if g_debug then
2533 hr_utility.set_location(l_proc, 330);
2534 end if;
2535 --
2536 begin
2537 --
2538 update per_assignments_f a
2539 set a.supervisor_id = null
2540 where a.supervisor_id = P_PERSON_ID;
2541 --
2542 exception
2543 when NO_DATA_FOUND then
2544 if g_debug then
2545 hr_utility.set_location(l_proc, 340);
2546 end if;
2547 end;
2548 --
2549 if g_debug then
2550 hr_utility.set_location(l_proc, 350);
2551 end if;
2552 --
2553 -- LOCK ASSIGNMENTS NOW: have to use cursor as cannot return >1 row for
2554 -- 'into' part of PL/SQL.
2555 --
2556 OPEN csr_lock_assignment_rows;
2557 --
2558 if g_debug then
2559 hr_utility.set_location(l_proc, 360);
2560 end if;
2561 --
2562 begin
2563 --
2564 -- Bug 349818. Delete from per_pay_proposal_components before
2565 -- deleting from the parent record in per_pay_proposals to
2566 -- maintain referential integrity, using the cursor csr_delete_components
2567 -- and the original per_pay_proposals delete.
2568 --
2569 OPEN csr_delete_components;
2570 LOOP
2571 FETCH csr_delete_components INTO l_proposal_id;
2572 EXIT WHEN csr_delete_components%NOTFOUND;
2573 DELETE FROM per_pay_proposal_components
2574 WHERE pay_proposal_id = l_proposal_id;
2575 END LOOP;
2576 --
2577 CLOSE csr_delete_components;
2578 --
2579 -- Now delete the parent proposal record.
2580 --
2581 delete from per_pay_proposals p
2582 where exists (
2583 select null
2584 from per_assignments_f ass
2585 where ass.assignment_id = p.assignment_id
2586 and ass.person_id = P_PERSON_ID);
2587 --
2588 exception
2589 when NO_DATA_FOUND then
2590 if g_debug then
2591 hr_utility.set_location(l_proc, 370);
2592 end if;
2593 end;
2594 --
2595 if g_debug then
2596 hr_utility.set_location(l_proc, 380);
2597 end if;
2598 --
2599 begin
2600 --
2601 delete from pay_personal_payment_methods_f m
2602 where m.assignment_id in
2603 (select ass.assignment_id
2604 from per_assignments_f ass
2605 where ass.person_id = P_PERSON_ID);
2606 --
2607 exception
2608 when NO_DATA_FOUND then
2609 if g_debug then
2610 hr_utility.set_location(l_proc, 390);
2611 end if;
2612 end;
2613 --
2614 if g_debug then
2615 hr_utility.set_location(l_proc, 400);
2616 end if;
2617 --
2618 begin
2619 --
2620 delete from per_assignment_budget_values_f a
2621 where a.assignment_id in
2622 (select ass.assignment_id
2623 from per_assignments_f ass
2624 where ass.person_id = P_PERSON_ID);
2625 --
2626 exception
2627 when NO_DATA_FOUND then
2628 if g_debug then
2629 hr_utility.set_location(l_proc, 410);
2630 end if;
2631 end;
2632 --
2633 if g_debug then
2634 hr_utility.set_location(l_proc, 420);
2635 end if;
2636 --
2637 begin
2638 --
2639 delete from per_assignment_extra_info a
2640 where a.assignment_id in
2641 (select ass.assignment_id
2642 from per_assignments_f ass
2643 where ass.person_id = P_PERSON_ID);
2644 --
2645 exception
2646 when NO_DATA_FOUND then
2647 if g_debug then
2648 hr_utility.set_location(l_proc, 430);
2649 end if;
2650 end;
2651 --
2652 if g_debug then
2653 hr_utility.set_location(l_proc, 440);
2654 end if;
2655 --
2656 begin
2657 --
2658 delete from per_secondary_ass_statuses a
2659 where a.assignment_id in
2660 (select ass.assignment_id
2661 from per_assignments_f ass
2662 where ass.person_id = P_PERSON_ID);
2663 --
2664 exception
2665 when NO_DATA_FOUND then
2666 if g_debug then
2667 hr_utility.set_location(l_proc, 450);
2668 end if;
2669 end;
2670 --
2671 if g_debug then
2672 hr_utility.set_location(l_proc, 460);
2673 end if;
2674 --
2675 -- Delete COBRA references and then any contact relationships. COBRA
2676 -- must be deleted first as PER_COBRA_COV_ENROLLMENTS has a
2677 -- contact_relationship_id which may be constrained later.
2678 --
2679 begin
2680 --
2681 delete from per_cobra_coverage_benefits c2
2682 where c2.cobra_coverage_enrollment_id in
2683 (select c.cobra_coverage_enrollment_id
2684 from per_cobra_cov_enrollments c
2685 where exists
2686 (select null
2687 from per_assignments_f ass
2688 where ass.assignment_id = c.assignment_id
2689 and ass.person_id = P_PERSON_ID)
2690 );
2691 --
2692 exception
2693 when NO_DATA_FOUND then
2694 if g_debug then
2695 hr_utility.set_location(l_proc, 470);
2696 end if;
2697 end;
2698 --
2699 if g_debug then
2700 hr_utility.set_location(l_proc, 480);
2701 end if;
2702 --
2703 begin
2704 --
2705 delete from per_cobra_coverage_benefits c2
2706 where c2.cobra_coverage_enrollment_id in
2707 (select c.cobra_coverage_enrollment_id
2708 from per_cobra_cov_enrollments c
2709 ,per_contact_relationships r
2710 where r.contact_person_id = P_PERSON_ID
2711 and c.contact_relationship_id = r.contact_relationship_id
2712 and exists
2713 (select null
2714 from per_assignments_f ass
2715 where ass.assignment_id = c.assignment_id
2716 and ass.person_id = r.person_id)
2717 );
2718 --
2719 exception
2720 when NO_DATA_FOUND then
2721 if g_debug then
2722 hr_utility.set_location(l_proc, 490);
2723 end if;
2724 end;
2725 --
2726 if g_debug then
2727 hr_utility.set_location(l_proc, 500);
2728 end if;
2729 --
2730 begin
2731 --
2732 delete from per_cobra_coverage_statuses c2
2733 where c2.cobra_coverage_enrollment_id in
2734 (select c.cobra_coverage_enrollment_id
2735 from per_cobra_cov_enrollments c
2736 where exists
2737 (select null
2738 from per_assignments_f ass
2739 where ass.assignment_id = c.assignment_id
2740 and ass.person_id = P_PERSON_ID)
2741 );
2742 --
2743 exception
2744 when NO_DATA_FOUND then
2745 if g_debug then
2746 hr_utility.set_location(l_proc, 510);
2747 end if;
2748 end;
2749 --
2750 if g_debug then
2751 hr_utility.set_location(l_proc, 520);
2752 end if;
2753 --
2754 begin
2755 --
2756 delete from per_cobra_coverage_statuses c2
2757 where c2.cobra_coverage_enrollment_id in
2758 (select c.cobra_coverage_enrollment_id
2759 from per_cobra_cov_enrollments c
2760 ,per_contact_relationships r
2761 where r.contact_person_id = P_PERSON_ID
2762 and c.contact_relationship_id = r.contact_relationship_id
2763 and exists
2764 (select null
2765 from per_assignments_f ass
2766 where ass.assignment_id = c.assignment_id
2767 and ass.person_id = r.person_id)
2768 );
2769 --
2770 exception
2771 when NO_DATA_FOUND then
2772 if g_debug then
2773 hr_utility.set_location(l_proc, 530);
2774 end if;
2775 end;
2776 --
2777 if g_debug then
2778 hr_utility.set_location(l_proc, 540);
2779 end if;
2780 --
2781 begin
2782 --
2783 delete from per_sched_cobra_payments c2
2784 where c2.cobra_coverage_enrollment_id in
2785 (select c.cobra_coverage_enrollment_id
2786 from per_cobra_cov_enrollments c
2787 where exists
2788 (select null
2789 from per_assignments_f ass
2790 where ass.assignment_id = c.assignment_id
2791 and ass.person_id = P_PERSON_ID)
2792 );
2793 --
2794 exception
2795 when NO_DATA_FOUND then
2796 if g_debug then
2797 hr_utility.set_location(l_proc, 550);
2798 end if;
2799 end;
2800 --
2801 if g_debug then
2802 hr_utility.set_location(l_proc, 560);
2803 end if;
2804 --
2805 begin
2806 --
2807 delete from per_sched_cobra_payments c2
2808 where c2.cobra_coverage_enrollment_id in
2809 (select c.cobra_coverage_enrollment_id
2810 from per_cobra_cov_enrollments c
2811 ,per_contact_relationships r
2812 where r.contact_person_id = P_PERSON_ID
2813 and c.contact_relationship_id = r.contact_relationship_id
2814 and exists
2815 (select null
2816 from per_assignments_f ass
2817 where ass.assignment_id = c.assignment_id
2818 and ass.person_id = r.person_id)
2819 );
2820 --
2821 exception
2822 when NO_DATA_FOUND then
2823 if g_debug then
2824 hr_utility.set_location(l_proc, 570);
2825 end if;
2826 end;
2827 --
2828 if g_debug then
2829 hr_utility.set_location(l_proc, 580);
2830 end if;
2831 --
2832 begin
2833 --
2834 delete from per_cobra_cov_enrollments c
2835 where c.assignment_id in
2836 (select ass.assignment_id
2837 from per_assignments_f ass
2838 where ass.person_id = P_PERSON_ID);
2839 --
2840 exception
2841 when NO_DATA_FOUND then
2842 if g_debug then
2843 hr_utility.set_location(l_proc, 590);
2844 end if;
2845 end;
2846 --
2847 if g_debug then
2848 hr_utility.set_location(l_proc, 600);
2849 end if;
2850 --
2851 begin
2852 --
2853 delete from per_cobra_cov_enrollments c
2854 where exists
2855 (select null
2856 from per_contact_relationships r
2857 where r.contact_person_id = P_PERSON_ID
2858 and c.contact_relationship_id = r.contact_relationship_id
2859 and exists
2860 (select null
2861 from per_assignments_f ass
2862 where ass.assignment_id = c.assignment_id
2863 and ass.person_id = r.person_id)
2864 );
2865 --
2866 exception
2867 when NO_DATA_FOUND then
2868 if g_debug then
2869 hr_utility.set_location(l_proc, 610);
2870 end if;
2871 end;
2872 --
2873 --Bug# 3026024 Start Here
2874 --Description : Delete the entry in the table ben_covered_dependents_f for the
2875 -- contact person whom is getting deleted.
2876 --
2877 --
2878 if g_debug then
2879 hr_utility.set_location(l_proc, 620);
2880 end if;
2881 --
2882 begin
2883 --
2884 delete from ben_covered_dependents_f c
2885 where c.contact_relationship_id in
2886 (select r.contact_relationship_id
2887 from per_contact_relationships r
2888 where r.contact_person_id = p_person_id
2889 );
2890 --
2891 exception
2892 when NO_DATA_FOUND then
2893 if g_debug then
2894 hr_utility.set_location(l_proc, 630);
2895 end if;
2896 end;
2897 --
2898 --Bug# 3026024 End Here
2899 --
2900 --
2901 -- If this person has any contacts then check whether they have had any
2902 -- extra info entered for them. If they have not then delete the
2903 -- contacts as well. If they do have extra info then just delete the
2904 -- relationship.
2905 --
2906 -- NB If b is created as a contact of b then 2 contact relationships are
2907 -- are created: a,b and b,a so that they can be queried in either
2908 -- direction. Hence must delete both here.
2909 --
2910 if g_debug then
2911 hr_utility.set_location(l_proc, 640);
2912 end if;
2913 --
2914 begin
2915 --
2916 select count(*)
2917 into l_dummy
2918 from per_contact_relationships r
2919 where r.person_id = P_PERSON_ID;
2920 --
2921 if l_dummy > 0 then
2922 for EACH_CONTACT in csr_this_persons_contacts loop
2923 --
2924 delete from per_contact_relationships r
2925 where (r.person_id = P_PERSON_ID
2926 and r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
2927 or (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
2928 and r.contact_person_id = P_PERSON_ID);
2929 --
2930 hr_person_internal.check_contact(P_PERSON_ID,
2931 EACH_CONTACT.CONTACT_PERSON_ID,
2932 EACH_CONTACT.CONTACT_RELATIONSHIP_ID,
2933 p_effective_date);
2934 --
2935 end loop;
2936 --
2937 end if;
2938 --
2939 exception
2940 when NO_DATA_FOUND then
2941 if g_debug then
2942 hr_utility.set_location(l_proc, 650);
2943 end if;
2944 end;
2945 --
2946 if g_debug then
2947 hr_utility.set_location(l_proc, 660);
2948 end if;
2949 --
2950 begin
2951 --
2952 delete from per_contact_relationships r
2953 where r.contact_person_id = P_PERSON_ID;
2954 --
2955 exception
2956 when NO_DATA_FOUND then
2957 if g_debug then
2958 hr_utility.set_location(l_proc, 670);
2959 end if;
2960 end;
2961 --
2962 if g_debug then
2963 hr_utility.set_location(l_proc, 680);
2964 end if;
2965 --
2966 begin
2967 --
2968 delete from per_addresses a
2969 where a.person_id = P_PERSON_ID;
2970 --
2971 exception
2972 when NO_DATA_FOUND then
2973 if g_debug then
2974 hr_utility.set_location(l_proc, 690);
2975 end if;
2976 end;
2977 --
2978 if g_debug then
2979 hr_utility.set_location(l_proc, 700);
2980 end if;
2981 --
2982 begin
2983 --
2984 delete from per_phones a
2985 where a.parent_id = P_PERSON_ID
2986 and a.parent_table = 'PER_ALL_PEOPLE_F';
2987 --
2988 exception
2989 when NO_DATA_FOUND then
2990 if g_debug then
2991 hr_utility.set_location(l_proc, 710);
2992 end if;
2993 end;
2994 --
2995 if g_debug then
2996 hr_utility.set_location(l_proc, 720);
2997 end if;
2998 --
2999 -- we must do this delete in dynamic sql because the per_performance_reviews
3000 -- table will not exist if the database has not been upgraded to new salary
3001 -- admin (introduced April 1998). The procedure would not compile if this
3002 -- was not dynamic. if the table is not found then the error (which starts
3003 -- with 'ORA-00942') is ignored.
3004 --
3005 begin
3006 --
3007 l_review_cursor:=dbms_sql.open_cursor;
3008 dbms_sql.parse(l_review_cursor,'DELETE from PER_PERFORMANCE_REVIEWS
3009 where person_id=:x',dbms_sql.v7);
3010 dbms_sql.bind_variable(l_review_cursor, ':x',P_PERSON_ID);
3011 l_rows_processed:=dbms_sql.execute(l_review_cursor);
3012 dbms_sql.close_cursor(l_review_cursor);
3013 --
3014 exception
3015 when NO_DATA_FOUND then dbms_sql.close_cursor(l_review_cursor);
3016 when OTHERS then
3017 dbms_sql.close_cursor(l_review_cursor);
3018 --
3019 if(substr(sqlerrm,0,9)<>'ORA-00942') then
3020 raise;
3021 end if;
3022 end;
3023 --
3024 if g_debug then
3025 hr_utility.set_location(l_proc, 730);
3026 end if;
3027 --
3028 -- About to delete interview events for assignments. However, must
3029 -- first delete bookings (interviewers) for those events.
3030 --
3031 begin
3032 -- bug fix 3732129.
3033 -- Delete statement modified to improve performance.
3034 --
3035 forall i in 1..l_assignment_id.count
3036 delete from per_bookings b
3037 where b.event_id in
3038 (select e.event_id
3039 from per_events e
3040 where e.assignment_id = l_assignment_id(i));
3041
3042 /*delete from per_bookings b
3043 where b.event_id in
3044 (select e.event_id
3045 from per_events e
3046 where exists (
3047 select null
3048 from per_assignments_f ass
3049 where ass.assignment_id = e.assignment_id
3050 and ass.person_id = P_PERSON_ID)
3051 );*/
3052 exception
3053 when NO_DATA_FOUND then
3054 if g_debug then
3055 hr_utility.set_location(l_proc, 740);
3056 end if;
3057 end;
3058 --
3059 if g_debug then
3060 hr_utility.set_location(l_proc, 750);
3061 end if;
3062 --
3063 begin
3064 -- bug fix 3732129.
3065 -- Delete statement modified to improve performance.
3066 --
3067 forall i in 1..l_assignment_id.count
3068 delete from per_events e
3069 where e.assignment_id = l_assignment_id(i);
3070
3071 /* delete from per_events e
3072 where e.assignment_id in (
3073 select ass.assignment_id
3074 from per_assignments_f ass
3075 where ass.person_id = P_PERSON_ID);*/
3076 exception
3077 when NO_DATA_FOUND then
3078 if g_debug then
3079 hr_utility.set_location(l_proc, 760);
3080 end if;
3081 end;
3082 --
3083 if g_debug then
3084 hr_utility.set_location(l_proc, 770);
3085 end if;
3086 --
3087 begin
3088 --
3089 update per_events e
3090 set e.internal_contact_person_id = null
3091 where e.internal_contact_person_id = P_PERSON_ID;
3092 --
3093 exception
3094 when NO_DATA_FOUND then
3095 if g_debug then
3096 hr_utility.set_location(l_proc, 780);
3097 end if;
3098 end;
3099 --
3100 if g_debug then
3101 hr_utility.set_location(l_proc, 790);
3102 end if;
3103 --
3104 begin
3105 --
3106 delete from per_bookings b
3107 where b.person_id = P_PERSON_ID;
3108 --
3109 exception
3110 when NO_DATA_FOUND then
3111 if g_debug then
3112 hr_utility.set_location(l_proc, 800);
3113 end if;
3114 end;
3115 --
3116 if g_debug then
3117 hr_utility.set_location(l_proc, 810);
3118 end if;
3119 --
3120 begin
3121 --
3122 delete from per_quickpaint_result_text q
3123 where q.assignment_id in
3124 (select ass.assignment_id
3125 from per_assignments_f ass
3126 where ass.person_id = P_PERSON_ID);
3127 --
3128 exception
3129 when NO_DATA_FOUND then
3130 if g_debug then
3131 hr_utility.set_location(l_proc, 820);
3132 end if;
3133 end;
3134 --
3135 if g_debug then
3136 hr_utility.set_location(l_proc, 830);
3137 end if;
3138 --
3139 -- Validation has already been performed against
3140 -- hr_assignment_set_amendments in weak_predel_validation.
3141 --
3142 begin
3143 --
3144 delete from hr_assignment_set_amendments h
3145 where h.assignment_id in
3146 (select ass.assignment_id
3147 from per_assignments_f ass
3148 where ass.person_id = P_PERSON_ID);
3149 --
3150 exception
3151 when NO_DATA_FOUND then
3152 if g_debug then
3153 hr_utility.set_location(l_proc, 840);
3154 end if;
3155 end;
3156 --
3157 if g_debug then
3158 hr_utility.set_location(l_proc, 850);
3159 end if;
3160 --
3161 begin
3162 --
3163 delete from pay_cost_allocations_f a
3164 where a.assignment_id in
3165 (select ass.assignment_id
3166 from per_assignments_f ass
3167 where ass.person_id = P_PERSON_ID);
3168 --
3169 exception
3170 when NO_DATA_FOUND then
3171 if g_debug then
3172 hr_utility.set_location(l_proc, 860);
3173 end if;
3174 end;
3175 --
3176 if g_debug then
3177 hr_utility.set_location(l_proc, 870);
3178 end if;
3179 --
3180 begin
3181 --
3182 delete from per_spinal_point_placements_f p
3183 where p.assignment_id in
3184 (select ass.assignment_id
3185 from per_assignments_f ass
3186 where ass.person_id = P_PERSON_ID);
3187 --
3188 exception
3189 when NO_DATA_FOUND then
3190 if g_debug then
3191 hr_utility.set_location(l_proc, 880);
3192 end if;
3193 end;
3194 --
3195 if g_debug then
3196 hr_utility.set_location(l_proc, 890);
3197 end if;
3198 --
3199 -- Validation has already been performed against
3200 -- pay_assignment_actions in weak_predel_validation.
3201 --
3202 begin
3203 --
3204 delete from pay_assignment_actions a
3205 where a.assignment_id in
3206 (select ass.assignment_id
3207 from per_assignments_f ass
3208 where ass.person_id = P_PERSON_ID);
3209 --
3210 exception
3211 when NO_DATA_FOUND then
3212 if g_debug then
3213 hr_utility.set_location(l_proc, 900);
3214 end if;
3215 end;
3216 --
3217 if g_debug then
3218 hr_utility.set_location(l_proc, 910);
3219 end if;
3220 --
3221 begin
3222 --
3223 delete from pay_assignment_latest_balances b
3224 where b.assignment_id in
3225 (select ass.assignment_id
3226 from per_assignments_f ass
3227 where ass.person_id = P_PERSON_ID);
3228 --
3229 exception
3230 when NO_DATA_FOUND then
3231 if g_debug then
3232 hr_utility.set_location(l_proc, 920);
3233 end if;
3234 end;
3235 --
3236 if g_debug then
3237 hr_utility.set_location(l_proc, 930);
3238 end if;
3239 --
3240 begin
3241 -- bug fix 3732129
3242 -- Delete statement modified to improve performance.
3243 --
3244 forall i in 1..l_assignment_id.count
3245 delete from pay_assignment_link_usages_f u
3246 where u.assignment_id = l_assignment_id(i);
3247
3248 /*delete from pay_assignment_link_usages_f u
3249 where
3250 u.assignment_id in (
3251 select ass.assignment_id
3252 from per_assignments_f ass
3253 where ass.person_id = P_PERSON_ID); */
3254 exception
3255 when NO_DATA_FOUND then
3256 if g_debug then
3257 hr_utility.set_location(l_proc, 940);
3258 end if;
3259 end;
3260 --
3261 if g_debug then
3262 hr_utility.set_location(l_proc, 950);
3263 end if;
3264 --
3265 begin
3266 --
3267 delete from pay_element_entry_values_f v
3268 where v.element_entry_id in
3269 (select e.element_entry_id
3270 from pay_element_entries_f e
3271 where exists
3272 (select null
3273 from per_assignments_f ass
3274 where ass.assignment_id = e.assignment_id
3275 and ass.person_id = P_PERSON_ID)
3276 );
3277 --
3278 exception
3279 when NO_DATA_FOUND then
3280 if g_debug then
3281 hr_utility.set_location(l_proc, 960);
3282 end if;
3283 end;
3284 --
3285 if g_debug then
3286 hr_utility.set_location(l_proc, 970);
3287 end if;
3288 --
3289 begin
3290 --
3291 delete from pay_run_results r
3292 where r.source_type = 'E'
3293 and r.source_id in
3294 (select e.element_entry_id
3295 from pay_element_entries_f e
3296 where exists
3297 (select null
3298 from per_assignments_f ass
3299 where ass.assignment_id = e.assignment_id
3300 and ass.person_id = P_PERSON_ID)
3301 );
3302 --
3303 exception
3304 when NO_DATA_FOUND then
3305 if g_debug then
3306 hr_utility.set_location(l_proc, 980);
3307 end if;
3308 end;
3309 --
3310 if g_debug then
3311 hr_utility.set_location(l_proc, 990);
3312 end if;
3313 --
3314 begin
3315 --
3316 delete from pay_element_entries_f e
3317 where e.assignment_id in
3318 (select ass.assignment_id
3319 from per_assignments_f ass
3320 where ass.person_id = P_PERSON_ID);
3321 --
3322 exception
3323 when NO_DATA_FOUND then
3324 if g_debug then
3325 hr_utility.set_location(l_proc, 10);
3326 end if;
3327 end;
3328 --
3329 if g_debug then
3330 hr_utility.set_location(l_proc, 20);
3331 end if;
3332 --
3333 -- Rmonge Bug 1686922 22-FEB-2002
3334 -- Tax records were not being deleted. Therefore, there were orphans rows in
3335 -- the pay_us_fed_tax_rules_f, pay_us_state_tax_rules_f,
3336 -- pay_us_county_tax_rules_f, and pay_us_city_tax_rules_f.
3337 --
3338 begin
3339 --
3340 Delete pay_us_emp_fed_tax_rules_f peft
3341 Where peft.assignment_id in
3342 (select ass.assignment_id
3343 from per_assignments_f ass
3344 where ass.person_id = p_person_id );
3345 --
3346 exception
3347 when no_data_found then
3348 if g_debug then
3349 hr_utility.set_location(l_proc, 30);
3350 end if;
3351 end;
3352 --
3353 begin
3354 --
3355 if g_debug then
3356 hr_utility.set_location(l_proc, 40);
3357 end if;
3358 --
3359 Delete pay_us_emp_state_tax_rules_f pest
3360 Where pest.assignment_id in
3361 (select ass.assignment_id
3362 from per_assignments_f ass
3363 where ass.person_id = p_person_id );
3364 --
3365 exception
3366 when no_data_found then
3367 if g_debug then
3368 hr_utility.set_location(l_proc, 50);
3369 end if;
3370 end;
3371 --
3372 begin
3373 --
3374 if g_debug then
3375 hr_utility.set_location(l_proc, 60);
3376 end if;
3377 --
3378 Delete pay_us_emp_county_tax_rules_f pect
3379 Where pect.assignment_id in
3380 (select ass.assignment_id
3381 from per_assignments_f ass
3382 where ass.person_id = p_person_id );
3383 --
3384 exception
3385 when no_data_found then
3386 if g_debug then
3387 hr_utility.set_location(l_proc, 70);
3388 end if;
3389 end;
3390 --
3391 begin
3392 --
3393 if g_debug then
3394 hr_utility.set_location(l_proc, 80);
3395 end if;
3396 --
3397 Delete pay_us_emp_city_tax_rules_f pecit
3398 Where pecit.assignment_id in
3399 (select ass.assignment_id
3400 from per_assignments_f ass
3401 where ass.person_id = p_person_id );
3402 --
3403 exception
3404 when no_data_found then
3405 if g_debug then
3406 hr_utility.set_location(l_proc, 90);
3407 end if;
3408 end;
3409 -- Finished, now unlock assignments and delete them.
3410 --
3411 close csr_lock_assignment_rows;
3412 --
3413 if g_debug then
3414 hr_utility.set_location(l_proc, 100);
3415 end if;
3416 --
3417 begin
3418 --
3419 delete from per_all_assignments_f a
3420 where a.person_id = P_PERSON_ID;
3421 --
3422 exception
3423 when NO_DATA_FOUND then
3424 if g_debug then
3425 hr_utility.set_location(l_proc, 110);
3426 end if;
3427 end;
3428 --
3429 if g_debug then
3430 hr_utility.set_location(l_proc, 120);
3431 end if;
3432 --
3433 begin
3434 --
3435 delete from per_periods_of_service p
3436 where p.person_id = P_PERSON_ID;
3437 --
3438 exception
3439 when NO_DATA_FOUND then
3440 if g_debug then
3441 hr_utility.set_location(l_proc, 130);
3442 end if;
3443 end;
3444 --
3445 if g_debug then
3446 hr_utility.set_location(l_proc, 140);
3447 end if;
3448 --
3449 begin
3450 --
3451 delete from per_applications a
3452 where a.person_id = P_PERSON_ID;
3453 --
3454 exception
3455 when NO_DATA_FOUND then
3456 if g_debug then
3457 hr_utility.set_location(l_proc, 150);
3458 end if;
3459 end;
3460 --
3461 -- 03/18/98 Bug #642566
3462 -- delete per_people_extra_info records
3463 if g_debug then
3464 hr_utility.set_location(l_proc, 160);
3465 end if;
3466 --
3467 begin
3468 --
3469 delete from per_people_extra_info e
3470 where e.person_id = P_PERSON_ID;
3471 --
3472 exception
3473 when NO_DATA_FOUND then
3474 if g_debug then
3475 hr_utility.set_location(l_proc, 170);
3476 end if;
3477 end;
3478 -- 03/18/98 Change Ends
3479 --
3480 -- 03/18/98 Change Ends
3481 --
3482 -- 28/5/98
3483 -- Add delete from per_person_type_usages_f
3484 if g_debug then
3485 hr_utility.set_location(l_proc, 180);
3486 end if;
3487 --
3488 for ptu_rec in csr_ptu loop
3489 --
3490 select min(ptu1.effective_start_date)
3491 into l_effective_date
3492 from per_person_type_usages_f ptu1
3493 where ptu1.person_type_usage_id = ptu_rec.person_type_usage_id;
3494 --
3495 select ptu2.object_version_number
3496 into l_object_version_number
3497 from per_person_type_usages_f ptu2
3498 where ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
3499 and ptu2.effective_start_date = l_effective_date;
3500 --
3501 if g_debug then
3502 --
3503 hr_utility.set_location('l_person_type_usage_id = '||to_char(ptu_rec.person_type_usage_id),44);
3504 hr_utility.set_location('l_effective_date = '||to_char(l_effective_date,'DD/MM/YYYY'),44);
3505 hr_utility.set_location('l_object_version_number = '||to_char(l_object_version_number),44);
3506 --
3507 end if;
3508 begin
3509 --
3510 -- hr_per_type_usage_internal.maintain_ptu(
3511 -- p_person_id => p_person_id,
3512 -- p_action => 'DELETE',
3513 -- p_period_of_service_id => NULL,
3514 -- p_actual_termination_date => NULL,
3515 -- p_business_group_id => NULL,
3516 -- p_date_start => NULL,
3517 -- p_leaving_reason => NULL,
3518 -- p_old_date_start => NULL,
3519 -- p_old_leaving_reason => NULL);
3520
3521 hr_per_type_usage_internal.delete_person_type_usage
3522 (p_person_type_usage_id => ptu_rec.person_type_usage_id
3523 ,p_effective_date => l_effective_date
3524 ,p_datetrack_mode => 'ZAP'
3525 ,p_object_version_number => l_object_version_number
3526 ,p_effective_start_date => l_effective_start_date
3527 ,p_effective_end_date => l_effective_end_date
3528 );
3529 exception
3530 when NO_DATA_FOUND then null;
3531 end;
3532 --
3533 end loop;
3534 --
3535 -- delete per_person_dlvry_methods
3536 if g_debug then
3537 hr_utility.set_location(l_proc, 190);
3538 end if;
3539 --
3540 begin
3541 --
3542 delete from per_person_dlvry_methods
3543 where person_id = P_PERSON_ID;
3544 exception
3545 when NO_DATA_FOUND then null;
3546 end;
3547 --
3548 -- Added this delete for quickhire checklists
3549 --
3550 if g_debug then
3551 hr_utility.set_location(l_proc, 200);
3552 end if;
3553 begin
3554 --
3555 delete from per_checklist_items
3556 where person_id = P_PERSON_ID;
3557 exception
3558 when NO_DATA_FOUND then null;
3559 end;
3560 --
3561 -- End addition for quickhire checklists
3562 --
3563 -- delete per_qualification and per_subjects_taken records
3564 if g_debug then
3565 hr_utility.set_location(l_proc, 210);
3566 end if;
3567 --
3568 begin
3569 -- Fix for 4490489 starts here
3570 --
3571 select distinct party_id into l_party_id
3572 from per_all_people_f
3573 where person_id = p_person_id;
3574 --
3575 select count(distinct person_id) into l_count
3576 from per_all_people_f
3577 where party_id = l_party_id;
3578 --
3579 if l_count = 1 then
3580 --PMFLETCH Added delete from tl table
3581 --
3582 delete from per_subjects_taken_tl st
3583 where st.subjects_taken_id IN
3584 (select s.subjects_taken_id
3585 from per_subjects_taken s
3586 ,per_qualifications q
3587 where q.party_id = l_party_id
3588 and s.qualification_id = q.qualification_id
3589 );
3590 --
3591 if g_debug then
3592 hr_utility.set_location(l_proc, 220);
3593 end if;
3594 --
3595 delete from per_subjects_taken s
3596 where s.qualification_id in
3597 (select qualification_id
3598 from per_qualifications
3599 where party_id = l_party_id );
3600 --
3601 if g_debug then
3602 hr_utility.set_location(l_proc, 230);
3603 end if;
3604 --PMFLETCH Added delete from tl table
3605 delete from per_qualifications_tl qt
3606 where qt.qualification_id in
3607 (select q.qualification_id
3608 from per_qualifications q
3609 where q.party_id = l_party_id);
3610 --
3611 if g_debug then
3612 hr_utility.set_location(l_proc, 240);
3613 end if;
3614 --
3615 delete from per_qualifications q
3616 where q.party_id = l_party_id;
3617 --
3618 end if;
3619 --
3620 exception
3621 when NO_DATA_FOUND then
3622 if g_debug then
3623 hr_utility.set_location(l_proc, 250);
3624 end if;
3625 end;
3626 --
3627 -- Fix for 4490489 ends here
3628 --
3629 --
3630 --PMFLETCH Added delete from tl table
3631 /* delete from per_subjects_taken_tl st
3632 where st.subjects_taken_id IN
3633 (select s.subjects_taken_id
3634 from per_subjects_taken s
3635 ,per_qualifications q
3636 where q.person_id = P_PERSON_ID
3637 and s.qualification_id = q.qualification_id
3638 );
3639 --
3640 if g_debug then
3641 hr_utility.set_location(l_proc, 220);
3642 end if;
3643 --
3644 delete from per_subjects_taken s
3645 where s.qualification_id in
3646 (select qualification_id
3647 from per_qualifications
3648 where person_id = P_PERSON_ID );
3649 --
3650 if g_debug then
3651 hr_utility.set_location(l_proc, 230);
3652 end if;
3653 --PMFLETCH Added delete from tl table
3654 delete from per_qualifications_tl qt
3655 where qt.qualification_id in
3656 (select q.qualification_id
3657 from per_qualifications q
3658 where q.person_id = P_PERSON_ID);
3659 --
3660 if g_debug then
3661 hr_utility.set_location(l_proc, 240);
3662 end if;
3663 --
3664 delete from per_qualifications q
3665 where q.person_id = P_PERSON_ID;
3666 --
3667 exception
3668 when NO_DATA_FOUND then
3669 if g_debug then
3670 hr_utility.set_location(l_proc, 250);
3671 end if;
3672 end; */
3673 --
3674 close csr_lock_person_rows;
3675 --
3676 if g_debug then
3677 hr_utility.set_location(l_proc, 260);
3678 end if;
3679
3680 --changes for 5166353 starts here
3681 ben_person_delete.delete_ben_rows(P_PERSON_ID);
3682 --changes for 5166353 ends here
3683 --
3684 begin
3685 --
3686 delete from per_all_people_f
3687 where person_id = P_PERSON_ID;
3688 exception
3689 when NO_DATA_FOUND then
3690 if g_debug then
3691 hr_utility.set_location(l_proc, 270);
3692 end if;
3693 end;
3694 --
3695 -- Now remove contracts
3696 --
3697 hr_contract_api.maintain_contracts (
3698 P_PERSON_ID,
3699 NULL,
3700 NULL);
3701 --
3702 if g_debug then
3703 hr_utility.set_location(l_proc, 280);
3704 end if;
3705 --
3706 -- Now remove Medical Assessments
3707 --
3708 FOR mea_rec IN csr_medical_assessment_records LOOP
3709 --
3710 per_medical_assessment_api.delete_medical_assessment
3711 (FALSE
3712 ,mea_rec.medical_assessment_id
3713 ,mea_rec.object_version_number);
3714 --
3715 END LOOP;
3716 --
3717 if g_debug then
3718 hr_utility.set_location(l_proc, 290);
3719 end if;
3720 --
3721 --
3722 -- Now remove disabilities
3723 --
3724 open csr_disabilities;
3725 LOOP
3726 fetch csr_disabilities INTO l_disability_id, l_object_version_no, l_effective_start_date, l_effective_end_date;
3727 EXIT when csr_disabilities%NOTFOUND;
3728 per_disability_api.delete_disability(false,p_effective_date ,'ZAP',l_disability_id, l_object_version_no, l_effective_start_date, l_effective_end_date);
3729 END LOOP;
3730 --
3731 if g_debug then
3732 hr_utility.set_location(l_proc, 300);
3733 end if;
3734 --
3735 --
3736 -- Now remove Work incidences
3737 --
3738 open csr_work_incidents;
3739 LOOP
3740 fetch csr_work_incidents INTO l_incident_id, l_object_version_number;
3741 EXIT when csr_work_incidents%NOTFOUND;
3742 per_work_incident_api.delete_work_incident(false,l_incident_id, l_object_version_number);
3743 END LOOP;
3744 --
3745 if g_debug then
3746 hr_utility.set_location(l_proc, 310);
3747 end if;
3748 --
3749 --
3750 -- Now remove Supplementary Roles
3751 --
3752 OPEN csr_roles;
3753 LOOP
3754 fetch csr_roles into l_role_id, l_ovn_roles;
3755 EXIT when csr_roles%notfound;
3756 per_supplementary_role_api.delete_supplementary_role(false, l_role_id, l_ovn_roles);
3757 END LOOP;
3758 --
3759 if g_debug then
3760 hr_utility.set_location(l_proc, 320);
3761 end if;
3762 --
3763 --
3764 begin
3765 delete from per_periods_of_placement p
3766 where p.person_id = P_PERSON_ID;
3767 exception
3768 when NO_DATA_FOUND then
3769 if g_debug then
3770 hr_utility.set_location(l_proc, 330);
3771 end if;
3772 end;
3773 --
3774 --
3775 -- Now remove Attachments
3776 --
3777 begin
3778 for attached_docs_rec in attached_docs_cursor
3779 LOOP
3780 if attached_docs_cursor%NOTFOUND then
3781 return;
3782 end if;
3783 l_attached_document_id := attached_docs_rec.attached_document_id;
3784 open delattachments_cursor (l_attached_document_id);
3785 FETCH delattachments_cursor into deldatarec;
3786 if delattachments_cursor%NOTFOUND then
3787 return;
3788 end if;
3789 l_datatype_id := deldatarec.datatype_id ;
3790 FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
3791 l_datatype_id,
3792 'Y' );
3793 CLOSE delattachments_cursor;
3794 END LOOP;
3795 exception
3796 when NO_DATA_FOUND then null;
3797 end;
3798 -- fix for the bug 8865114
3799 BEGIN
3800 DELETE
3801 FROM per_objectives
3802 WHERE scorecard_id IN (SELECT scorecard_id
3803 FROM per_personal_scorecards
3804 WHERE person_id = P_PERSON_ID)
3805 OR appraisal_id in (SELECT appraisal_id
3806 FROM per_appraisals
3807 WHERE appraisee_person_id = p_person_id) ;
3808
3809 DELETE
3810 FROM per_scorecard_sharing
3811 WHERE scorecard_id IN (SELECT scorecard_id
3812 FROM per_personal_scorecards
3813 WHERE person_id = P_PERSON_ID);
3814 DELETE
3815 FROM hr_api_transaction_steps
3816 WHERE transaction_id IN (SELECT a.transaction_id
3817 FROM hr_api_transactions a,
3818 per_personal_scorecards sc
3819 WHERE a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
3820 AND a.transaction_ref_id = sc.scorecard_id
3821 AND sc.person_id = P_PERSON_ID);
3822
3823 DELETE
3824 FROM hr_api_transactions
3825 WHERE transaction_id IN (SELECT transaction_id
3826 FROM hr_api_transactions a,
3827 per_personal_scorecards sc
3828 WHERE a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
3829 AND a.transaction_ref_id = sc.scorecard_id
3830 AND sc.person_id = P_PERSON_ID);
3831 DELETE
3832 FROM per_personal_scorecards
3833 WHERE person_id = P_PERSON_ID;
3834
3835 EXCEPTION
3836 WHEN Others Then
3837 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1111);
3838
3839 END;
3840 BEGIN
3841 DELETE
3842 FROM per_competence_elements
3843 WHERE assessment_id IN (SELECT assessment_id
3844 FROM per_assessments
3845 WHERE appraisal_id IN (SELECT appraisal_id
3846 FROM per_appraisals
3847 WHERE appraisee_person_id = P_PERSON_ID));
3848 DELETE
3849 FROM per_performance_ratings
3850 WHERE appraisal_id IN (SELECT appraisal_id
3851 FROM per_appraisals
3852 WHERE appraisee_person_id = P_PERSON_ID);
3853 DELETE
3854 FROM per_assessments
3855 WHERE appraisal_id IN (SELECT appraisal_id
3856 FROM per_appraisals
3857 WHERE appraisee_person_id = P_PERSON_ID);
3858
3859 EXCEPTION
3860 WHEN Others Then
3861 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1112);
3862 END;
3863 -- fix 9885957
3864 begin
3865 --
3866 delete from per_appraisals apr
3867 where -- apr.appraiser_person_id = P_PERSON_ID or
3868 -- changed as part of bug#8865114
3869 apr.appraisee_person_id = P_PERSON_ID;
3870 --
3871 exception
3872 when NO_DATA_FOUND then
3873 if g_debug then
3874 hr_utility.set_location(l_proc, 250);
3875 end if;
3876 end;
3877 -- fix 9885957
3878 -- fix for the bug 8865114
3879 --
3880
3881 if g_debug then
3882 hr_utility.set_location('Leaving:'||l_proc, 999);
3883 end if;
3884 --
3885 END delete_person;
3886 end hr_person_internal;
[Home] [Help]