1 Package Body hr_person_internal as
2 /* $Header: peperbsi.pkb 120.9.12010000.3 2008/08/06 09:25:58 ubhat 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('Leaving:'||l_proc, 100);
742 end if;
743 --
744 END weak_predel_validation;
745 --
746 -- ----------------------------------------------------------------------------
747 -- |-----------------------< strong_predel_validation >-----------------------|
748 -- ----------------------------------------------------------------------------
749 --
750 -- Descrption:
751 --
752 -- It performs many checks to find if additional data has been entered for this
753 -- person. It is more stringent than weak_predel_validation and ensures that
754 -- this person only has the default data set up by entering a person, contact
755 -- or applicant afresh onto the system. If additional data is found then the
756 -- delete of this person from the calling module is invalid as it is beyond
757 -- its scope. The Delete Person form should therefore be used (which only
758 -- performs weak_predel_validation) if a delete really is required.
759 --
760 PROCEDURE strong_predel_validation(p_person_id IN number
761 ,p_effective_date IN date)
762 IS
763 --
764 l_person_types number;
765 l_delete_permitted varchar2(1);
766 --
767 -- Bug 3524713 Starts Here
768 CURSOR ben_ext_chg_log (
769 p_person_id NUMBER
770 ) IS
771 SELECT ext_chg_evt_log_id
772 FROM ben_ext_chg_evt_log
773 WHERE person_id = p_person_id
774 FOR UPDATE OF ext_chg_evt_log_id;
775 --
776 l_id NUMBER;
777 -- Bug 3524713 Ends Here
778 --
779 l_proc varchar2(72) := g_package||'strong_predel_validation';
780 --
781 BEGIN
782 --
783 if g_debug then
784 hr_utility.set_location('Entering:'|| l_proc, 10);
785 end if;
786 --
787 -- check if person type changes exist.
788 --
789 select count(*)
790 into l_person_types
791 from per_people_f ppf,
792 per_person_types ppt
793 where ppf.person_id = P_PERSON_ID
794 and ppf.effective_end_date >= p_effective_date
795 and ppf.person_type_id = ppt.person_type_id
796 and ( exists
797 (select null
798 from per_people_f ppf2,
799 per_person_types ppt2
800 where ppf2.person_id = ppf.person_id
801 and ppf2.effective_end_date >= p_effective_date
802 and ppf2.person_type_id = ppt2.person_type_id
803 and ppt2.system_person_type <> ppt.system_person_type
804 )
805 or exists
806 ( select null
807 from per_periods_of_placement ppp
808 where ppp.person_id=ppf.person_id
809 and actual_termination_date>=p_effective_date
810 and actual_termination_date is not null)); --fix for bug 6730008.
811 --
812 if l_person_types > 0 then
813 --
814 hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
815 hr_utility.raise_error;
816 --
817 end if;
818 --
819 if g_debug then
820 hr_utility.set_location(l_proc, 20);
821 end if;
822 --
823 begin
824 --
825 -- bug fix 3732129.
826 -- Select statement modified to improve performance.
827 --
828 select 'Y'
829 into l_delete_permitted
830 from sys.dual
831 where not exists (
832 select null
833 from per_letter_request_lines r
834 where r.person_id = P_PERSON_ID
835 and r.date_from >= p_effective_date );
836 --
837 select 'Y'
838 into l_delete_permitted
839 from sys.dual
840 where not exists (
841 select null
842 from per_letter_request_lines r
843 where exists (
844 select null
845 from per_assignments_f a
846 where a.person_id = P_PERSON_ID
847 and a.effective_start_date >= p_effective_date
848 and a.assignment_id = r.assignment_id));
849 --
850 exception
851 when NO_DATA_FOUND then
852 hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
853 hr_utility.raise_error;
854 end;
855 --
856 if g_debug then
857 hr_utility.set_location(l_proc, 30);
858 end if;
859 --
860 begin
861 --
862 select 'Y'
863 into l_delete_permitted
864 from sys.dual
865 where not exists (
866 select null
867 from per_contact_relationships r
868 where r.person_id = P_PERSON_ID
869 or r.contact_person_id = P_PERSON_ID);
870 --
871 exception
872 when NO_DATA_FOUND then
873 hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
874 hr_utility.raise_error;
875 end;
876 --
877 if g_debug then
878 hr_utility.set_location(l_proc, 40);
879 end if;
880 --
881 begin
882 --
883 select 'Y'
884 into l_delete_permitted
885 from sys.dual
886 where not exists (
887 select null
888 from per_events e
889 where e.internal_contact_person_id = P_PERSON_ID);
890 --
891 exception
892 when NO_DATA_FOUND then
893 hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
894 hr_utility.raise_error;
895 end;
896 --
897 if g_debug then
898 hr_utility.set_location(l_proc, 50);
899 end if;
900 --
901 begin
902 --
903 select 'Y'
904 into l_delete_permitted
905 from sys.dual
906 where not exists (
907 select null
908 from per_bookings b
909 where b.person_id = P_PERSON_ID);
910 --
911 exception
912 when NO_DATA_FOUND then
913 hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
914 hr_utility.raise_error;
915 end;
916 --
917 if g_debug then
918 hr_utility.set_location(l_proc, 60);
919 end if;
920 --
921 begin
922 --
923 select 'Y'
924 into l_delete_permitted
925 from sys.dual
926 where 1 >= (
927 select count(*)
928 from per_assignments_f a
929 where a.person_id = P_PERSON_ID
930 and a.effective_start_date >= p_effective_date);
931 --
932 exception
933 when NO_DATA_FOUND then
934 hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
935 hr_utility.raise_error;
936 end;
937 --
938 if g_debug then
939 hr_utility.set_location(l_proc, 70);
940 end if;
941 --
942 begin
943 --
944 select 'Y'
945 into l_delete_permitted
946 from sys.dual
947 where not exists (
948 select null
949 from per_assignments_f a
950 where a.recruiter_id = P_PERSON_ID
951 or a.supervisor_id = P_PERSON_ID);
952 --
953 exception
954 when NO_DATA_FOUND then
955 hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
956 hr_utility.raise_error;
957 end;
958 --
959 if g_debug then
960 hr_utility.set_location(l_proc, 80);
961 end if;
962 --
963 begin
964 --
965 select 'Y'
966 into l_delete_permitted
967 from sys.dual
968 where not exists (
969 select null
970 from per_periods_of_service p
971 where p.termination_accepted_person_id = P_PERSON_ID);
972 --
973 exception
974 when NO_DATA_FOUND then
975 hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
976 hr_utility.raise_error;
977 end;
978 --
979 if g_debug then
980 hr_utility.set_location(l_proc, 90);
981 end if;
982 --
983 begin
984 --
985 select 'Y'
986 into l_delete_permitted
987 from sys.dual
988 where not exists (
989 select null
990 from per_person_analyses a
991 where a.person_id = P_PERSON_ID);
992 --
993 exception
994 when NO_DATA_FOUND then
995 hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
996 hr_utility.raise_error;
997 end;
998 --
999 if g_debug then
1000 hr_utility.set_location(l_proc, 100);
1001 end if;
1002 --
1003 begin
1004 --
1005 select 'Y'
1006 into l_delete_permitted
1007 from sys.dual
1008 where not exists (
1009 select null
1010 from per_absence_attendances a
1011 where a.person_id = P_PERSON_ID);
1012 --
1013 exception
1014 when NO_DATA_FOUND then
1015 hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
1016 hr_utility.raise_error;
1017 end;
1018 --
1019 if g_debug then
1020 hr_utility.set_location(l_proc, 110);
1021 end if;
1022 --
1023 begin
1024 --
1025 select 'Y'
1026 into l_delete_permitted
1027 from sys.dual
1028 where not exists (
1029 select null
1030 from per_absence_attendances a
1031 where a.authorising_person_id = P_PERSON_ID
1032 or a.replacement_person_id = P_PERSON_ID);
1033 --
1034 exception
1035 when NO_DATA_FOUND then
1036 hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
1037 hr_utility.raise_error;
1038 end;
1039 --
1040 if g_debug then
1041 hr_utility.set_location(l_proc, 120);
1042 end if;
1043 --
1044 begin
1045 --
1046 select 'Y'
1047 into l_delete_permitted
1048 from sys.dual
1049 where not exists (
1050 select null
1051 from per_recruitment_activities r
1052 where r.authorising_person_id = P_PERSON_ID
1053 or r.internal_contact_person_id = P_PERSON_ID);
1054 --
1055 exception
1056 when NO_DATA_FOUND then
1057 hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
1058 hr_utility.raise_error;
1059 end;
1060 --
1061 if g_debug then
1062 hr_utility.set_location(l_proc, 130);
1063 end if;
1064 --
1065 begin
1066 --
1067 select 'Y'
1068 into l_delete_permitted
1069 from sys.dual
1070 where not exists (
1071 select null
1072 from per_appraisals apr
1073 where apr.appraisee_person_id = P_PERSON_ID
1074 or apr.appraiser_person_id = P_PERSON_ID);
1075 --
1076 exception
1077 when NO_DATA_FOUND then
1078 fnd_message.set_name('PER','PER_52467_APR_PAR_REC_NO_DEL');
1079 fnd_message.raise_error;
1080 end;
1081 --
1082 if g_debug then
1083 hr_utility.set_location(l_proc, 140);
1084 end if;
1085 --
1086 begin
1087 --
1088 select 'Y'
1089 into l_delete_permitted
1090 from sys.dual
1091 where not exists (
1092 select null
1093 from per_participants par
1094 where par.person_id = P_PERSON_ID);
1095 --
1096 exception
1097 when NO_DATA_FOUND then
1098 fnd_message.set_name('PER','PER_52467_APR_PAR_REC_NO_DEL');
1099 fnd_message.raise_error;
1100 end;
1101 --
1102 if g_debug then
1103 hr_utility.set_location(l_proc, 150);
1104 end if;
1105 --
1106 begin
1107 --
1108 select 'Y'
1109 into l_delete_permitted
1110 from sys.dual
1111 where not exists (
1112 select null
1113 from per_requisitions r
1114 where r.person_id = P_PERSON_ID);
1115 --
1116 exception
1117 when NO_DATA_FOUND then
1118 hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
1119 hr_utility.raise_error;
1120 end;
1121 --
1122 if g_debug then
1123 hr_utility.set_location(l_proc, 160);
1124 end if;
1125 --
1126 begin
1127 --
1128 select 'Y'
1129 into l_delete_permitted
1130 from sys.dual
1131 where not exists (
1132 select null
1133 from per_vacancies v
1134 where v.recruiter_id = P_PERSON_ID);
1135 --
1136 exception
1137 when NO_DATA_FOUND then
1138 hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
1139 hr_utility.raise_error;
1140 end;
1141 --
1142 if g_debug then
1143 hr_utility.set_location(l_proc, 170);
1144 end if;
1145 --
1146 -- Any discretionary link element entries?
1147 --
1148 begin
1149 --
1150 select 'Y'
1151 into l_delete_permitted
1152 from sys.dual
1153 where not exists (
1154 select null
1155 from pay_element_entries_f e,
1156 per_assignments_f a,
1157 pay_element_links_f l
1158 where a.person_id = P_PERSON_ID
1159 and a.assignment_id = e.assignment_id
1160 and e.element_link_id = l.element_link_id
1161 and l.standard_link_flag = 'N');
1162 --
1163 exception
1164 when NO_DATA_FOUND then
1165 hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
1166 hr_utility.raise_error;
1167 end;
1168 --
1169 if g_debug then
1170 hr_utility.set_location(l_proc, 180);
1171 end if;
1172 --
1173 -- Any entry adjustments, overrides etc.?
1174 -- (We cannot capture manual enty of standard link entries)
1175 --
1176 begin
1177 --
1178 select 'Y'
1179 into l_delete_permitted
1180 from sys.dual
1181 where not exists (
1182 select null
1183 from pay_element_entries_f e,
1184 per_assignments_f a
1185 where a.person_id = P_PERSON_ID
1186 and a.assignment_id = e.assignment_id
1187 and e.entry_type <> 'E');
1188 --
1189 exception
1190 when NO_DATA_FOUND then
1191 hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1192 hr_utility.raise_error;
1193 end;
1194 --
1195 if g_debug then
1196 hr_utility.set_location(l_proc, 190);
1197 end if;
1198 --
1199 begin
1200 --
1201 select 'Y'
1202 into l_delete_permitted
1203 from sys.dual
1204 where not exists (
1205 select null
1206 from per_assignment_extra_info i
1207 where exists (
1208 select null
1209 from per_assignments_f a
1210 where a.person_id = P_PERSON_ID
1211 and a.assignment_id = i.assignment_id));
1212 --
1213 exception
1214 when NO_DATA_FOUND then
1215 hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1216 hr_utility.raise_error;
1217 end;
1218 --
1219 if g_debug then
1220 hr_utility.set_location(l_proc, 200);
1221 end if;
1222 --
1223 begin
1224 --
1225 select 'Y'
1226 into l_delete_permitted
1227 from sys.dual
1228 where not exists (
1229 select null
1230 from per_secondary_ass_statuses s
1231 where exists (
1232 select null
1233 from per_assignments_f a
1234 where a.person_id = P_PERSON_ID
1235 and a.assignment_id = s.assignment_id));
1236 --
1237 exception
1238 when NO_DATA_FOUND then
1239 hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
1240 hr_utility.raise_error;
1241 end;
1242 --
1243 if g_debug then
1244 hr_utility.set_location(l_proc, 210);
1245 end if;
1246 --
1247 begin
1248 --
1249 select 'Y'
1250 into l_delete_permitted
1251 from sys.dual
1252 where not exists (
1253 select null
1254 from per_events e
1255 where exists (
1256 select null
1257 from per_assignments_f a
1258 where a.person_id = P_PERSON_ID
1259 and a.assignment_id = e.assignment_id));
1260 --
1261 exception
1262 when NO_DATA_FOUND then
1263 hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1264 hr_utility.raise_error;
1265 end;
1266 --
1267 if g_debug then
1268 hr_utility.set_location(l_proc, 220);
1269 end if;
1270 --
1271 begin
1272 --
1273 select 'Y'
1274 into l_delete_permitted
1275 from sys.dual
1276 where not exists (
1277 select null
1278 from per_spinal_point_placements_f p
1279 where exists (
1280 select null
1281 from per_assignments_f a
1282 where a.person_id = P_PERSON_ID
1283 and a.assignment_id = p.assignment_id));
1284 --
1285 exception
1286 when NO_DATA_FOUND then
1287 hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1288 hr_utility.raise_error;
1289 end;
1290 --
1291 if g_debug then
1292 hr_utility.set_location(l_proc, 230);
1293 end if;
1294 --
1295 begin
1296 --
1297 select 'Y'
1298 into l_delete_permitted
1299 from sys.dual
1300 where not exists (
1301 select null
1302 from per_quickpaint_result_text t
1303 where exists (
1304 select null
1305 from per_assignments_f a
1306 where a.person_id = P_PERSON_ID
1307 and a.assignment_id = t.assignment_id));
1308 --
1309 exception
1310 when NO_DATA_FOUND then
1311 hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1312 hr_utility.raise_error;
1313 end;
1314 --
1315 if g_debug then
1316 hr_utility.set_location(l_proc, 240);
1317 end if;
1318 --
1319 begin
1320 --
1321 select 'Y'
1322 into l_delete_permitted
1323 from sys.dual
1324 where not exists (
1325 select null
1326 from per_cobra_cov_enrollments c
1327 where exists (
1328 select null
1329 from per_assignments_f a
1330 where a.person_id = P_PERSON_ID
1331 and a.assignment_id = c.assignment_id));
1332 --
1333 exception
1334 when NO_DATA_FOUND then
1335 hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1336 hr_utility.raise_error;
1337 end;
1338 --
1339 if g_debug then
1340 hr_utility.set_location(l_proc, 250);
1341 end if;
1342 --
1343 OPEN ben_ext_chg_log (p_person_id);
1344 --
1345 LOOP
1346 --
1347 FETCH ben_ext_chg_log INTO l_id;
1348 EXIT WHEN ben_ext_chg_log%NOTFOUND;
1349 --
1350 DELETE FROM ben_ext_chg_evt_log
1351 WHERE CURRENT OF ben_ext_chg_log;
1352 --
1353 END LOOP;
1354 --
1355 CLOSE ben_ext_chg_log;
1356 --
1357 -- Bug 3524713 Ends Here
1358 --
1359 ben_person_delete.perform_ri_check(p_person_id);
1360 --
1361 if g_debug then
1362 hr_utility.set_location(l_proc, 260);
1363 end if;
1364 --
1365 -- Validation for OTA.
1366 --
1367 per_ota_predel_validation.ota_predel_per_validation(P_PERSON_ID);
1368 --
1369 -- validation for PA
1370 --
1371 if g_debug then
1372 hr_utility.set_location(l_proc, 270);
1373 end if;
1374 --
1375 pa_person.pa_predel_validation(P_PERSON_ID);
1376 --
1377 -- validation for WIP
1378 --
1379 if g_debug then
1380 hr_utility.set_location(l_proc, 280);
1381 end if;
1382 --
1383 wip_person.wip_predel_validation(P_PERSON_ID);
1384 --
1385 -- validation for ENG
1386 --
1387 if g_debug then
1388 hr_utility.set_location(l_proc, 290);
1389 end if;
1390 --
1391 eng_person.eng_predel_validation(P_PERSON_ID);
1392 --
1393 -- validation for AP
1394 --
1395 if g_debug then
1396 hr_utility.set_location(l_proc, 300);
1397 end if;
1398 --
1399 ap_person.ap_predel_validation(P_PERSON_ID);
1400 --
1401 -- validation for FA
1402 --
1403 if g_debug then
1404 hr_utility.set_location(l_proc, 310);
1405 end if;
1406 --
1407 fa_person.fa_predel_validation(P_PERSON_ID);
1408 --
1409 -- validation for PO
1410 --
1411 if g_debug then
1412 hr_utility.set_location(l_proc, 320);
1413 end if;
1414 --
1415 po_person.po_predel_validation(P_PERSON_ID);
1416 --
1417 -- validation for RCV
1418 --
1419 if g_debug then
1420 hr_utility.set_location(l_proc, 330);
1421 end if;
1422 --
1423 rcv_person.rcv_predel_validation(P_PERSON_ID);
1424 --
1425 if g_debug then
1426 hr_utility.set_location('Leaving:'||l_proc, 999);
1427 end if;
1428 --
1429 END strong_predel_validation;
1430 --
1431 -- ----------------------------------------------------------------------------
1432 -- |----------------------< people_default_deletes >--------------------------|
1433 -- ----------------------------------------------------------------------------
1434 --
1435 -- Description :
1436 --
1437 -- Delete routine for deleting information set up as default when people
1438 -- are created. Used primarily for delete on PERPEEPI (Enter Person).
1439 -- The strong_predel_validation should first be performed to ensure that
1440 -- no additional info (apart from default) has been entered.
1441 --
1442 -- NOTE
1443 --
1444 -- See delete_person for p_form_call details. Further, p_form_call is
1445 -- set to TRUE when this procedure is called from check_contact as
1446 -- there is no need to check the existance of the contact.
1447 --
1448 PROCEDURE people_default_deletes (p_person_id IN number)
1449 IS
1450 --
1451 l_assignment_id number(15);
1452 l_proc varchar2(72) := g_package||'people_default_deletes';
1453 l_pk1_value1 varchar2(72) := p_person_id;
1454
1455 --
1456 CURSOR lock_person_rows IS
1457 select person_id
1458 from per_people_f
1459 where person_id = P_PERSON_ID
1460 FOR UPDATE;
1461 --
1462 CURSOR attached_docs_cursor1 IS
1463 SELECT attached_document_id
1464 FROM fnd_attached_documents
1465 WHERE pk1_value = l_pk1_value1;
1466 --
1467 cursor delattachments_cursor1 (x_attached_document_id in number) is
1468 select datatype_id
1469 from fnd_attached_docs_form_vl
1470 where attached_document_id = x_attached_document_id;
1471 --
1472 l_datatype_id number;
1473 l_attached_document_id number;
1474 deldatarec1 delattachments_cursor1%ROWTYPE;
1475 --
1476 BEGIN
1477 --
1478 if g_debug then
1479 hr_utility.set_location('Entering:'||l_proc, 10);
1480 end if;
1481 --
1482 hr_person_internal.person_existance_check(P_PERSON_ID);
1483 --
1484 if g_debug then
1485 hr_utility.set_location(l_proc, 20);
1486 end if;
1487 --
1488 open LOCK_PERSON_ROWS;
1489 --
1490 -- Now start cascade.
1491 --
1492 -- Start of Fix for WWBUG 1294400
1493 -- All of benefits is a child of HR and PAY so its safe to delete
1494 -- benefits stuff first.
1495 --
1496 ben_person_delete.delete_ben_rows(p_person_id);
1497 --
1498 -- End of Fix for WWBUG 1294400
1499 --
1500 if g_debug then
1501 hr_utility.set_location(l_proc, 30);
1502 end if;
1503 --
1504 hr_security.delete_per_from_list(P_PERSON_ID);
1505 --
1506 if g_debug then
1507 hr_utility.set_location(l_proc, 40);
1508 end if;
1509 --
1510 -- Lock assignments now, delete at end.
1511 -- Can select into a variable as max one assignment should exist (as
1512 -- strong_predel_validation has already been performed).
1513 -- May not be assignments (for contacts, for eg) so exception.
1514 --
1515 begin
1516 --
1517 select ass.assignment_id
1518 into l_assignment_id
1519 from per_assignments_f ass
1520 where ass.person_id = P_PERSON_ID
1521 FOR UPDATE;
1522 exception
1523 when NO_DATA_FOUND then null;
1524 end;
1525 --
1526 if g_debug then
1527 hr_utility.set_location(l_proc, 50);
1528 end if;
1529 --
1530 begin
1531 --
1532 delete from pay_personal_payment_methods p
1533 where p.assignment_id = l_assignment_id;
1534 exception
1535 when NO_DATA_FOUND then null;
1536 end;
1537 --
1538 if g_debug then
1539 hr_utility.set_location(l_proc, 60);
1540 end if;
1541 --
1542 begin
1543 --
1544 delete from per_assignment_budget_values_f v
1545 where v.assignment_id = l_assignment_id;
1546 exception
1547 when NO_DATA_FOUND then null;
1548 end;
1549 --
1550 if g_debug then
1551 hr_utility.set_location(l_proc, 70);
1552 end if;
1553 --
1554 begin
1555 delete from per_addresses a
1556 where a.person_id = P_PERSON_ID;
1557 exception
1558 when NO_DATA_FOUND then null;
1559 end;
1560 --
1561 if g_debug then
1562 hr_utility.set_location(l_proc, 80);
1563 end if;
1564 --
1565 begin
1566 delete from per_phones a
1567 where a.parent_id = P_PERSON_ID
1568 and a.parent_table = 'PER_ALL_PEOPLE_F';
1569 exception
1570 when NO_DATA_FOUND then null;
1571 end;
1572 --
1573 if g_debug then
1574 hr_utility.set_location(l_proc, 90);
1575 end if;
1576 --
1577 begin
1578 delete from pay_cost_allocations_f a
1579 where a.assignment_id = l_assignment_id;
1580 exception
1581 when NO_DATA_FOUND then null;
1582 end;
1583 --
1584 if g_debug then
1585 hr_utility.set_location(l_proc, 100);
1586 end if;
1587 --
1588 begin
1589 delete from pay_element_entry_values_f v
1590 where v.element_entry_id in
1591 (select e.element_entry_id
1592 from pay_element_entries_f e
1593 where e.assignment_id = l_assignment_id);
1594 exception
1595 when NO_DATA_FOUND then null;
1596 end;
1597 --
1598 if g_debug then
1599 hr_utility.set_location(l_proc, 110);
1600 end if;
1601 --
1602 begin
1603 delete from pay_run_results r
1604 where r.source_type = 'E'
1605 and r.source_id in
1606 (select e.element_entry_id
1607 from pay_element_entries_f e
1608 where e.assignment_id = l_assignment_id);
1609 exception
1610 when NO_DATA_FOUND then null;
1611 end;
1612 --
1613 if g_debug then
1614 hr_utility.set_location(l_proc, 120);
1615 end if;
1616 --
1617 begin
1618 delete from pay_element_entries_f e
1619 where e.assignment_id = l_assignment_id;
1620 exception
1621 when NO_DATA_FOUND then null;
1622 end;
1623 --
1624 if g_debug then
1625 hr_utility.set_location(l_proc, 130);
1626 end if;
1627 --
1628 -- No exception, should succeed.
1629 --
1630 begin
1631 delete from per_assignments_f ass
1632 where ass.assignment_id = l_assignment_id;
1633 end;
1634 --
1635 if g_debug then
1636 hr_utility.set_location(l_proc, 140);
1637 end if;
1638 --
1639 begin
1640 delete from per_periods_of_service p
1641 where p.person_id = P_PERSON_ID;
1642 exception
1643 when NO_DATA_FOUND then null;
1644 end;
1645 --
1646 if g_debug then
1647 hr_utility.set_location(l_proc, 150);
1648 end if;
1649 --
1650 begin
1651 delete from per_applications a
1652 where a.person_id = P_PERSON_ID;
1653 exception
1654 when NO_DATA_FOUND then null;
1655 end;
1656 --
1657 -- Added this delete for quickhire checklists
1658 --
1659 if g_debug then
1660 hr_utility.set_location(l_proc, 160);
1661 end if;
1662 --
1663 begin
1664 delete from per_checklist_items
1665 where person_id = P_PERSON_ID;
1666 exception
1667 when NO_DATA_FOUND then null;
1668 end;
1669 --
1670 -- End addition for quickhire checklists
1671 --
1672 --
1673 if g_debug then
1674 hr_utility.set_location(l_proc, 170);
1675 end if;
1676 --
1677 close LOCK_PERSON_ROWS;
1678 --
1679 if g_debug then
1680 hr_utility.set_location(l_proc, 180);
1681 end if;
1682 --
1683 begin
1684 delete from per_people_f
1685 where person_id = P_PERSON_ID;
1686 exception
1687 when NO_DATA_FOUND then null;
1688 end;
1689 --
1690 if g_debug then
1691 hr_utility.set_location(l_proc, 190);
1692 end if;
1693 --
1694 begin
1695 delete from per_periods_of_placement p
1696 where p.person_id = P_PERSON_ID;
1697 exception
1698 when NO_DATA_FOUND then null;
1699 end;
1700 --
1701 if g_debug then
1702 hr_utility.set_location('Leaving:'||l_proc, 999);
1703 end if;
1704 --
1705 begin
1706 for attached_docs_rec in attached_docs_cursor1
1707 LOOP
1708 if attached_docs_cursor1%NOTFOUND then
1709 return;
1710 end if;
1711 l_attached_document_id := attached_docs_rec.attached_document_id;
1712 open delattachments_cursor1 (l_attached_document_id);
1713 FETCH delattachments_cursor1 into deldatarec1;
1714 if delattachments_cursor1%NOTFOUND then
1715 return;
1716 end if;
1717 l_datatype_id := deldatarec1.datatype_id ;
1718 FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
1719 l_datatype_id,
1720 'Y' );
1721 CLOSE delattachments_cursor1;
1722 END LOOP;
1723 exception
1724 when NO_DATA_FOUND then null;
1725 end;
1726 if g_debug then
1727 hr_utility.set_location('Leaving:'||l_proc, 999);
1728 end if;
1729 --
1730 END people_default_deletes;
1731 --
1732 -- ----------------------------------------------------------------------------
1733 -- |---------------------< applicant_default_deletes >------------------------|
1734 -- ----------------------------------------------------------------------------
1735 --
1736 -- Description :
1737 --
1738 -- Delete routine for deleting information set up as default when
1739 -- applicants are entered. Used primarily for delete on PERREAQE
1740 -- (Applicant Quick Entry). The strong_predel_validation should first be
1741 -- performed to ensure that no additional info (apart from default) has
1742 -- been entered.
1743 --
1744 PROCEDURE applicant_default_deletes(p_person_id IN number)
1745 IS
1746 --
1747 l_assignment_id number(15);
1748 l_proc varchar2(72) := g_package||'applicant_default_deletes';
1749 l_pk1_value2 varchar2(72) := p_person_id;
1750
1751 --
1752 CURSOR lock_person_rows IS
1753 SELECT person_id
1754 FROM per_people_f
1755 WHERE person_id = P_PERSON_ID
1756 FOR UPDATE;
1757 --
1758 CURSOR attached_docs_cursor2 IS
1759 SELECT attached_document_id
1760 FROM fnd_attached_documents
1761 WHERE pk1_value = l_pk1_value2;
1762 --
1763 cursor delattachments_cursor2 (x_attached_document_id in number) is
1764 select datatype_id
1765 from fnd_attached_docs_form_vl
1766 where attached_document_id = x_attached_document_id;
1767 --
1768 l_datatype_id number;
1769 l_attached_document_id number;
1770 deldatarec2 delattachments_cursor2%ROWTYPE;
1771 --
1772 BEGIN
1773 --
1774 if g_debug then
1775 hr_utility.set_location('Entering:'||l_proc, 10);
1776 end if;
1777 --
1778 hr_person_internal.person_existance_check(P_PERSON_ID);
1779 --
1780 if g_debug then
1781 hr_utility.set_location(l_proc, 10);
1782 end if;
1783 --
1784 open LOCK_PERSON_ROWS;
1785 --
1786 -- Now start cascade.
1787 --
1788 if g_debug then
1789 hr_utility.set_location(l_proc, 20);
1790 end if;
1791 --
1792 begin
1793 delete from per_person_list l
1794 where l.person_id = P_PERSON_ID;
1795 exception
1796 when NO_DATA_FOUND then null;
1797 end;
1798 --
1799 if g_debug then
1800 hr_utility.set_location(l_proc, 30);
1801 end if;
1802 --
1803 -- Can select into a variable as only one assignment should exist (as
1804 -- strong_predel_validation has already been performed).
1805 --
1806 begin
1807 select ass.assignment_id
1808 into l_assignment_id
1809 from per_assignments_f ass
1810 where ass.person_id = P_PERSON_ID
1811 FOR UPDATE;
1812 end;
1813 --
1814 if g_debug then
1815 hr_utility.set_location(l_proc, 40);
1816 end if;
1817 --
1818 begin
1819 delete from per_addresses a
1820 where a.person_id = P_PERSON_ID;
1821 exception
1822 when NO_DATA_FOUND then null;
1823 end;
1824 --
1825 if g_debug then
1826 hr_utility.set_location(l_proc, 50);
1827 end if;
1828 --
1829 begin
1830 delete from per_phones a
1831 where a.parent_id = P_PERSON_ID
1832 and a.parent_table = 'PER_ALL_PEOPLE_F';
1833 exception
1834 when NO_DATA_FOUND then null;
1835 end;
1836 --
1837 if g_debug then
1838 hr_utility.set_location(l_proc, 60);
1839 end if;
1840 --
1841 begin
1842 delete from per_assignments_f ass
1843 where ass.assignment_id = l_assignment_id;
1844 end;
1845 --
1846 if g_debug then
1847 hr_utility.set_location(l_proc, 70);
1848 end if;
1849 --
1850 begin
1851 delete from per_applications a
1852 where a.person_id = P_PERSON_ID;
1853 exception
1854 when NO_DATA_FOUND then null;
1855 end;
1856 --
1857 -- Added this delete for quickhire checklists
1858 --
1859 if g_debug then
1860 hr_utility.set_location(l_proc, 80);
1861 end if;
1862 --
1863 begin
1864 delete from per_checklist_items
1865 where person_id = P_PERSON_ID;
1866 exception
1867 when NO_DATA_FOUND then null;
1868 end;
1869 --
1870 -- End addition for quickhire checklists
1871 --
1872 -- Added this delete for PTU
1873 --
1874 if g_debug then
1875 hr_utility.set_location(l_proc, 90);
1876 end if;
1877 --
1878 begin
1879 delete from per_person_type_usages_f
1880 where person_id = P_PERSON_ID;
1881 exception
1882 when NO_DATA_FOUND then null;
1883 end;
1884 --
1885 -- End addition for PTU
1886 --
1887 if g_debug then
1888 hr_utility.set_location(l_proc, 100);
1889 end if;
1890 --
1891 close LOCK_PERSON_ROWS;
1892 --
1893 if g_debug then
1894 hr_utility.set_location(l_proc, 110);
1895 end if;
1896 --
1897 begin
1898 delete from per_people_f
1899 where person_id = P_PERSON_ID;
1900 exception
1901 when NO_DATA_FOUND then null;
1902 end;
1903 --
1904 if g_debug then
1905 hr_utility.set_location('Leaving:'||l_proc, 999);
1906 end if;
1907 --
1908 begin
1909 for attached_docs_rec in attached_docs_cursor2
1910 LOOP
1911 if attached_docs_cursor2%NOTFOUND then
1912 return;
1913 end if;
1914 l_attached_document_id := attached_docs_rec.attached_document_id;
1915 open delattachments_cursor2 (l_attached_document_id);
1916 FETCH delattachments_cursor2 into deldatarec2;
1917 if delattachments_cursor2%NOTFOUND then
1918 return;
1919 end if;
1920 l_datatype_id := deldatarec2.datatype_id ;
1921 FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
1922 l_datatype_id,
1923 'Y' );
1924 CLOSE delattachments_cursor2;
1925 END LOOP;
1926 exception
1927 when NO_DATA_FOUND then null;
1928 end;
1929 if g_debug then
1930 hr_utility.set_location('Leaving:'||l_proc, 999);
1931 end if;
1932 --
1933 END applicant_default_deletes;
1934 --
1935 -- ----------------------------------------------------------------------------
1936 -- |---------------------------< delete_person >------------------------------|
1937 -- ----------------------------------------------------------------------------
1938 --
1939 PROCEDURE delete_person (p_person_id IN number
1940 ,p_effective_date IN date)
1941 IS
1942 --
1943 l_pk1_value3 varchar2(72) := p_person_id;
1944 --
1945 CURSOR csr_this_persons_contacts IS
1946 SELECT contact_person_id,
1947 contact_relationship_id
1948 FROM per_contact_relationships
1949 WHERE person_id = P_PERSON_ID;
1950 --
1951 CURSOR csr_lock_person_rows IS
1952 SELECT person_id
1953 FROM per_people_f
1954 WHERE person_id = P_PERSON_ID
1955 FOR UPDATE;
1956 --
1957 CURSOR csr_lock_assignment_rows IS
1958 SELECT assignment_id
1959 FROM per_assignments_f
1960 WHERE person_id = P_PERSON_ID
1961 FOR UPDATE;
1962 --
1963 CURSOR csr_delete_components IS
1964 SELECT pp.pay_proposal_id
1965 FROM per_pay_proposals pp,
1966 per_assignments_f pa
1967 WHERE pa.person_id = P_PERSON_ID
1968 AND pa.assignment_id = pp.assignment_id
1969 FOR UPDATE;
1970 --
1971 CURSOR csr_medical_assessment_records IS
1972 SELECT medical_assessment_id,
1973 object_version_number
1974 FROM per_medical_Assessments pma
1975 WHERE pma.person_id = p_person_id;
1976 --
1977 CURSOR csr_work_incidents IS
1978 SELECT incident_id, object_version_number
1979 FROM per_work_incidents
1980 WHERE person_id = p_person_id;
1981 --
1982 CURSOR csr_disabilities IS
1983 SELECT disability_id, object_version_number,
1984 effective_start_date, effective_end_date
1985 FROM per_disabilities_f
1986 WHERE person_id = p_person_id;
1987 --
1988 CURSOR csr_roles IS
1989 SELECT role_id, object_version_number
1990 FROM per_roles
1991 WHERE person_id= p_person_id;
1992 --
1993 CURSOR csr_ptu IS
1994 SELECT distinct person_type_usage_id
1995 FROM per_person_type_usages_f ptu
1996 WHERE ptu.person_id = p_person_id
1997 ORDER BY person_type_usage_id;
1998 --
1999 CURSOR csr_asg IS
2000 SELECT distinct assignment_id
2001 FROM per_assignments_f
2002 WHERE person_id = p_person_id;
2003 --
2004 CURSOR attached_docs_cursor IS
2005 SELECT attached_document_id
2006 FROM fnd_attached_documents
2007 WHERE pk1_value = l_pk1_value3;
2008 --
2009 cursor delattachments_cursor (x_attached_document_id in number) is
2010 select datatype_id
2011 from fnd_attached_docs_form_vl
2012 where attached_document_id = x_attached_document_id;
2013 --
2014 --
2015 -- local variables
2016 --
2017 l_dummy number(3) := null; /* Bug 941 591 */
2018 l_proposal_id number;
2019 l_review_cursor number;
2020 l_rows_processed number;
2021 l_incident_id per_work_incidents.person_id%TYPE;
2022 l_disability_id per_disabilities_f.disability_id%TYPE;
2023 l_object_version_no per_disabilities_f.object_version_number%TYPE;
2024 l_ovn_roles per_roles.object_version_number%TYPE;
2025 l_role_id per_roles.role_id%TYPE;
2026 --
2027 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
2028 l_effective_date date;
2029 l_object_version_number number;
2030 l_effective_start_date date;
2031 l_effective_end_date date;
2032 --
2033 -- bug fix 3732129 starts here.
2034 -- to improve performance assignment id fetched into a pl/sql table.
2035 --
2036 type assignmentid is table of per_all_assignments_f.assignment_id%type
2037 index by binary_integer;
2038 --
2039 l_assignment_id assignmentid;
2040 l_proc varchar2(72) := g_package||'delete_person';
2041 --
2042 l_datatype_id number;
2043 l_attached_document_id number;
2044 deldatarec delattachments_cursor%ROWTYPE;
2045 --
2046 -- Fix for 4490489 starts here
2047 l_party_id number(15);
2048 l_count number(15);
2049
2050 -- Fix for 4490489 ends here
2051 BEGIN
2052 --
2053 if g_debug then
2054 hr_utility.set_location('Entering:'||l_proc, 10);
2055 end if;
2056 --
2057 -- Lock person rows, delete at end of procedure.
2058 --
2059 OPEN csr_lock_person_rows;
2060 --
2061 -- Now start cascade.
2062 --
2063 if g_debug then
2064 hr_utility.set_location(l_proc, 20);
2065 end if;
2066 --
2067 -- bug fix 3732129 starts here.
2068 -- fetching the assignment ids into a pl/sql table.
2069 --
2070 OPEN csr_asg;
2071 FETCH csr_asg bulk collect into l_assignment_id;
2072 CLOSE csr_asg;
2073 --
2074 -- bug fix 3732129 ends here.
2075 --
2076 if g_debug then
2077 hr_utility.set_location(l_proc, 30);
2078 end if;
2079 --
2080 begin
2081 --
2082 update per_requisitions r
2083 set r.person_id = null
2084 where r.person_id = P_PERSON_ID;
2085 --
2086 exception
2087 when NO_DATA_FOUND then
2088 if g_debug then
2089 hr_utility.set_location(l_proc, 40);
2090 end if;
2091 end;
2092 --
2093 if g_debug then
2094 hr_utility.set_location(l_proc, 50);
2095 end if;
2096 --
2097 begin
2098 -- bug fix 3732129.
2099 -- Delete statement modified to improve performance.
2100 delete from per_letter_request_lines l
2101 where l.person_id = P_PERSON_ID;
2102 --
2103 forall i in 1..l_assignment_id.count
2104 delete from per_letter_request_lines l
2105 where l.assignment_id = l_assignment_id(i);
2106 --
2107 exception
2108 when NO_DATA_FOUND then
2109 if g_debug then
2110 hr_utility.set_location(l_proc, 60);
2111 end if;
2112 end;
2113 --
2114 -- Leave per_letter_requests for the moment - may not be necessary to
2115 -- delete the parent with no children which requires some work with
2116 -- cursors.
2117 --
2118 if g_debug then
2119 hr_utility.set_location(l_proc, 70);
2120 end if;
2121 --
2122 begin
2123 --
2124 delete from per_absence_attendances a
2125 where a.person_id = P_PERSON_ID;
2126 --
2127 exception
2128 when NO_DATA_FOUND then
2129 if g_debug then
2130 hr_utility.set_location(l_proc, 80);
2131 end if;
2132 end;
2133 --
2134 if g_debug then
2135 hr_utility.set_location(l_proc, 90);
2136 end if;
2137 --
2138 begin
2139 --
2140 update per_absence_attendances a
2141 set a.authorising_person_id = null
2142 where a.authorising_person_id = P_PERSON_ID;
2143 --
2144 exception
2145 when NO_DATA_FOUND then
2146 if g_debug then
2147 hr_utility.set_location(l_proc, 100);
2148 end if;
2149 end;
2150 --
2151 if g_debug then
2152 hr_utility.set_location(l_proc, 110);
2153 end if;
2154 --
2155 begin
2156 --
2157 update per_absence_attendances a
2158 set a.replacement_person_id = null
2159 where a.replacement_person_id = P_PERSON_ID;
2160 --
2161 exception
2162 when NO_DATA_FOUND then
2163 if g_debug then
2164 hr_utility.set_location(l_proc, 120);
2165 end if;
2166 end;
2167 --
2168 if g_debug then
2169 hr_utility.set_location(l_proc, 130);
2170 end if;
2171 --
2172 begin
2173 --
2174 delete from per_person_analyses a
2175 where a.person_id = P_PERSON_ID;
2176 --
2177 exception
2178 when NO_DATA_FOUND then
2179 if g_debug then
2180 hr_utility.set_location(l_proc, 140);
2181 end if;
2182 end;
2183 --
2184 if g_debug then
2185 hr_utility.set_location(l_proc, 150);
2186 end if;
2187 --
2188 -- Delete of per_periods_of_service at end after delete of
2189 -- per_assignments_f.
2190 --
2191 begin
2192 --
2193 update per_periods_of_service p
2194 set p.termination_accepted_person_id = null
2195 where p.termination_accepted_person_id = P_PERSON_ID;
2196 --
2197 exception
2198 when NO_DATA_FOUND then
2199 if g_debug then
2200 hr_utility.set_location(l_proc, 160);
2201 end if;
2202 end;
2203 --
2204 if g_debug then
2205 hr_utility.set_location(l_proc, 170);
2206 end if;
2207 --
2208 begin
2209 --
2210 update per_recruitment_activities r
2211 set r.authorising_person_id = null
2212 where r.authorising_person_id = P_PERSON_ID;
2213 --
2214 exception
2215 when NO_DATA_FOUND then
2216 if g_debug then
2217 hr_utility.set_location(l_proc, 180);
2218 end if;
2219 end;
2220 --
2221 if g_debug then
2222 hr_utility.set_location(l_proc, 190);
2223 end if;
2224 --
2225 begin
2226 --
2227 update per_recruitment_activities r
2228 set r.internal_contact_person_id = null
2229 where r.internal_contact_person_id = P_PERSON_ID;
2230 --
2231 exception
2232 when NO_DATA_FOUND then
2233 if g_debug then
2234 hr_utility.set_location(l_proc, 200);
2235 end if;
2236 end;
2237 --
2238 if g_debug then
2239 hr_utility.set_location(l_proc, 210);
2240 end if;
2241 --
2242 -- Bug 4873360 fix for performance repository sql id 14960331.
2243 -- Rewrote the delete query commented out below (and already once tuned for
2244 -- bug 3619599) to avoid a merge join cartesian and a full table scan on
2245 -- PER_PARTICIPANTS, HR_QUEST_ANSWER_VALUES and PER_APPRAISALS
2246 --
2247 -- Broke query into two peices using conditional logic in a pl/sql block to
2248 -- see if delete needs to be run.
2249 --
2250 begin -- Delete from HR_QUEST_ANSWER_VALUES
2251 begin -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
2252 begin
2253 select 1
2254 into l_dummy
2255 from sys.dual
2256 where exists (
2257 select null
2258 from per_participants par
2259 where par.person_id = P_PERSON_ID);
2260 exception
2261 when NO_DATA_FOUND then
2262 l_dummy := null;
2263 if g_debug then
2264 hr_utility.set_location(l_proc, 211);
2265 end if;
2266 end;
2267 if l_dummy = 1
2268 then
2269 l_dummy := null;
2270 delete from hr_quest_answer_values qsv2
2271 where qsv2.quest_answer_val_id in
2272 (select qsv.quest_answer_val_id
2273 from hr_quest_answer_values qsv
2274 ,hr_quest_answers qsa
2275 ,per_participants par
2276 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2277 and qsa.type_object_id = par.participant_id
2278 and qsa.type = 'PARTICIPANT'
2279 and par.person_id = P_PERSON_ID);
2280 end if;
2281 if g_debug then
2282 hr_utility.set_location(l_proc, 215);
2283 end if;
2284 end; -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
2285 begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
2286 begin
2287 select 2
2288 into l_dummy
2289 from sys.dual
2290 where exists (
2291 select null
2292 from per_appraisals apr
2293 where (apr.appraiser_person_id = P_PERSON_ID
2294 or apr.appraisee_person_id = P_PERSON_ID));
2295 exception
2296 when NO_DATA_FOUND then
2297 l_dummy := null;
2298 if g_debug then
2299 hr_utility.set_location(l_proc, 220);
2300 end if;
2301 end;
2302 if l_dummy = 2
2303 then
2304 l_dummy := null;
2305 delete from hr_quest_answer_values qsv2
2306 where qsv2.quest_answer_val_id in
2307 (select qsv.quest_answer_val_id
2308 from hr_quest_answer_values qsv
2309 ,hr_quest_answers qsa
2310 ,per_appraisals apr
2311 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2312 and qsa.type_object_id = apr.appraisal_id
2313 and qsa.type='APPRAISAL'
2314 and (apr.appraisee_person_id = P_PERSON_ID
2315 or apr.appraiser_person_id = P_PERSON_ID));
2316 if g_debug then
2317 hr_utility.set_location(l_proc, 221);
2318 end if;
2319 end if;
2320 end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
2321 end; -- Delete from HR_QUEST_ANSWER_VALUES
2322 -- original sql.
2323 /* -- Delete from HR_QUEST_ANSWER_VALUES
2324 delete from hr_quest_answer_values qsv2
2325 where qsv2.quest_answer_val_id in
2326 (select qsv.quest_answer_val_id
2327 from hr_quest_answer_values qsv
2328 ,hr_quest_answers qsa
2329 ,per_appraisals apr
2330 ,per_participants par
2331 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2332 and (qsa.type_object_id = apr.appraisal_id
2333 and qsa.type='APPRAISAL'
2334 and (apr.appraisee_person_id = P_PERSON_ID
2335 or apr.appraiser_person_id = P_PERSON_ID))
2336 or (qsa.type_object_id = par.participant_id
2337 and qsa.type='PARTICIPANT'
2338 and par.person_id = P_PERSON_ID)
2339 ); -- Fix 3619599
2340 exception
2341 when NO_DATA_FOUND then
2342 if g_debug then
2343 hr_utility.set_location(l_proc, 220);
2344 end if;
2345 end; */
2346 -- Now delete from HR_QUEST_ANSWERS
2347 begin
2348 --
2349 -- Fix 3619599 and 4894116
2350 delete from hr_quest_answers qsa2
2351 where qsa2.questionnaire_answer_id in
2352 (
2353 select qsa.questionnaire_answer_id
2354 from hr_quest_answers qsa
2355 ,per_appraisals apr
2356 where (qsa.type_object_id = apr.appraisal_id
2357 and qsa.type='APPRAISAL'
2358 and (apr.appraiser_person_id = p_person_id
2359 or apr.appraisee_person_id = p_person_id))
2360 Union All
2361
2362 select qsa.questionnaire_answer_id
2363 from hr_quest_answers qsa
2364 ,per_participants par
2365 where (qsa.type_object_id = par.participant_id
2366 and qsa.type='PARTICIPANT'
2367 and par.person_id = p_person_id )
2368 ) ;
2369
2370 --
2371 exception
2372 when NO_DATA_FOUND then
2373 if g_debug then
2374 hr_utility.set_location(l_proc, 230);
2375 end if;
2376 end;
2377 --
2378 -- Now delete from per_participants
2379 begin
2380
2381 -- Fix 4894116
2382 delete from per_participants par2
2383 where par2.participant_id in
2384 ( select par.participant_id
2385 from per_participants par
2386 where par.person_id = P_PERSON_Id
2387 union all
2388 select par.participant_id
2389 from per_participants par
2390 ,per_appraisals apr
2391 where
2392 (par.participation_in_column = 'APPRAISAL_ID'
2393 and par.participation_in_table = 'PER_APPRAISALS'
2394 and participation_in_id = apr.appraisal_id
2395 and (apr.appraisee_person_id = P_PERSON_ID
2396 or apr.appraiser_person_id = p_person_id)
2397 )
2398 );
2399 --
2400 exception
2401 when NO_DATA_FOUND then
2402 if g_debug then
2403 hr_utility.set_location(l_proc, 240);
2404 end if;
2405 end;
2406 --
2407 -- Now delete from per_appraisals
2408 --
2409 begin
2410 --
2411 delete from per_appraisals apr
2412 where apr.appraiser_person_id = P_PERSON_ID
2413 or apr.appraisee_person_id = P_PERSON_ID;
2414 --
2415 exception
2416 when NO_DATA_FOUND then
2417 if g_debug then
2418 hr_utility.set_location(l_proc, 250);
2419 end if;
2420 end;
2421 --
2422 if g_debug then
2423 hr_utility.set_location(l_proc, 260);
2424 end if;
2425 --
2426 hr_security.delete_per_from_list(P_PERSON_ID);
2427 --
2428 if g_debug then
2429 hr_utility.set_location(l_proc, 270);
2430 end if;
2431 --
2432 begin
2433 --
2434 update per_vacancies v
2435 set v.recruiter_id = null
2436 where v.recruiter_id = P_PERSON_ID;
2437 --
2438 exception
2439 when NO_DATA_FOUND then
2440 if g_debug then
2441 hr_utility.set_location(l_proc, 280);
2442 end if;
2443 end;
2444 --
2445 if g_debug then
2446 hr_utility.set_location(l_proc, 290);
2447 end if;
2448 --
2449 begin
2450 --
2451 update per_assignments_f ass
2452 set ass.person_referred_by_id = null
2453 where ass.person_referred_by_id = P_PERSON_ID;
2454 --
2455 exception
2456 when NO_DATA_FOUND then
2457 if g_debug then
2458 hr_utility.set_location(l_proc, 300);
2459 end if;
2460 end;
2461 --
2462 if g_debug then
2463 hr_utility.set_location(l_proc, 310);
2464 end if;
2465 --
2466 begin
2467 --
2468 update per_assignments_f a
2469 set a.recruiter_id = null
2470 where a.recruiter_id = P_PERSON_ID;
2471 --
2472 exception
2473 when NO_DATA_FOUND then
2474 if g_debug then
2475 hr_utility.set_location(l_proc, 320);
2476 end if;
2477 end;
2478 --
2479 if g_debug then
2480 hr_utility.set_location(l_proc, 330);
2481 end if;
2482 --
2483 begin
2484 --
2485 update per_assignments_f a
2486 set a.supervisor_id = null
2487 where a.supervisor_id = P_PERSON_ID;
2488 --
2489 exception
2490 when NO_DATA_FOUND then
2491 if g_debug then
2492 hr_utility.set_location(l_proc, 340);
2493 end if;
2494 end;
2495 --
2496 if g_debug then
2497 hr_utility.set_location(l_proc, 350);
2498 end if;
2499 --
2500 -- LOCK ASSIGNMENTS NOW: have to use cursor as cannot return >1 row for
2501 -- 'into' part of PL/SQL.
2502 --
2503 OPEN csr_lock_assignment_rows;
2504 --
2505 if g_debug then
2506 hr_utility.set_location(l_proc, 360);
2507 end if;
2508 --
2509 begin
2510 --
2511 -- Bug 349818. Delete from per_pay_proposal_components before
2512 -- deleting from the parent record in per_pay_proposals to
2513 -- maintain referential integrity, using the cursor csr_delete_components
2514 -- and the original per_pay_proposals delete.
2515 --
2516 OPEN csr_delete_components;
2517 LOOP
2518 FETCH csr_delete_components INTO l_proposal_id;
2519 EXIT WHEN csr_delete_components%NOTFOUND;
2520 DELETE FROM per_pay_proposal_components
2521 WHERE pay_proposal_id = l_proposal_id;
2522 END LOOP;
2523 --
2524 CLOSE csr_delete_components;
2525 --
2526 -- Now delete the parent proposal record.
2527 --
2528 delete from per_pay_proposals p
2529 where exists (
2530 select null
2531 from per_assignments_f ass
2532 where ass.assignment_id = p.assignment_id
2533 and ass.person_id = P_PERSON_ID);
2534 --
2535 exception
2536 when NO_DATA_FOUND then
2537 if g_debug then
2538 hr_utility.set_location(l_proc, 370);
2539 end if;
2540 end;
2541 --
2542 if g_debug then
2543 hr_utility.set_location(l_proc, 380);
2544 end if;
2545 --
2546 begin
2547 --
2548 delete from pay_personal_payment_methods_f m
2549 where m.assignment_id in
2550 (select ass.assignment_id
2551 from per_assignments_f ass
2552 where ass.person_id = P_PERSON_ID);
2553 --
2554 exception
2555 when NO_DATA_FOUND then
2556 if g_debug then
2557 hr_utility.set_location(l_proc, 390);
2558 end if;
2559 end;
2560 --
2561 if g_debug then
2562 hr_utility.set_location(l_proc, 400);
2563 end if;
2564 --
2565 begin
2566 --
2567 delete from per_assignment_budget_values_f a
2568 where a.assignment_id in
2569 (select ass.assignment_id
2570 from per_assignments_f ass
2571 where ass.person_id = P_PERSON_ID);
2572 --
2573 exception
2574 when NO_DATA_FOUND then
2575 if g_debug then
2576 hr_utility.set_location(l_proc, 410);
2577 end if;
2578 end;
2579 --
2580 if g_debug then
2581 hr_utility.set_location(l_proc, 420);
2582 end if;
2583 --
2584 begin
2585 --
2586 delete from per_assignment_extra_info a
2587 where a.assignment_id in
2588 (select ass.assignment_id
2589 from per_assignments_f ass
2590 where ass.person_id = P_PERSON_ID);
2591 --
2592 exception
2593 when NO_DATA_FOUND then
2594 if g_debug then
2595 hr_utility.set_location(l_proc, 430);
2596 end if;
2597 end;
2598 --
2599 if g_debug then
2600 hr_utility.set_location(l_proc, 440);
2601 end if;
2602 --
2603 begin
2604 --
2605 delete from per_secondary_ass_statuses a
2606 where a.assignment_id in
2607 (select ass.assignment_id
2608 from per_assignments_f ass
2609 where ass.person_id = P_PERSON_ID);
2610 --
2611 exception
2612 when NO_DATA_FOUND then
2613 if g_debug then
2614 hr_utility.set_location(l_proc, 450);
2615 end if;
2616 end;
2617 --
2618 if g_debug then
2619 hr_utility.set_location(l_proc, 460);
2620 end if;
2621 --
2622 -- Delete COBRA references and then any contact relationships. COBRA
2623 -- must be deleted first as PER_COBRA_COV_ENROLLMENTS has a
2624 -- contact_relationship_id which may be constrained later.
2625 --
2626 begin
2627 --
2628 delete from per_cobra_coverage_benefits c2
2629 where c2.cobra_coverage_enrollment_id in
2630 (select c.cobra_coverage_enrollment_id
2631 from per_cobra_cov_enrollments c
2632 where exists
2633 (select null
2634 from per_assignments_f ass
2635 where ass.assignment_id = c.assignment_id
2636 and ass.person_id = P_PERSON_ID)
2637 );
2638 --
2639 exception
2640 when NO_DATA_FOUND then
2641 if g_debug then
2642 hr_utility.set_location(l_proc, 470);
2643 end if;
2644 end;
2645 --
2646 if g_debug then
2647 hr_utility.set_location(l_proc, 480);
2648 end if;
2649 --
2650 begin
2651 --
2652 delete from per_cobra_coverage_benefits c2
2653 where c2.cobra_coverage_enrollment_id in
2654 (select c.cobra_coverage_enrollment_id
2655 from per_cobra_cov_enrollments c
2656 ,per_contact_relationships r
2657 where r.contact_person_id = P_PERSON_ID
2658 and c.contact_relationship_id = r.contact_relationship_id
2659 and exists
2660 (select null
2661 from per_assignments_f ass
2662 where ass.assignment_id = c.assignment_id
2663 and ass.person_id = r.person_id)
2664 );
2665 --
2666 exception
2667 when NO_DATA_FOUND then
2668 if g_debug then
2669 hr_utility.set_location(l_proc, 490);
2670 end if;
2671 end;
2672 --
2673 if g_debug then
2674 hr_utility.set_location(l_proc, 500);
2675 end if;
2676 --
2677 begin
2678 --
2679 delete from per_cobra_coverage_statuses c2
2680 where c2.cobra_coverage_enrollment_id in
2681 (select c.cobra_coverage_enrollment_id
2682 from per_cobra_cov_enrollments c
2683 where exists
2684 (select null
2685 from per_assignments_f ass
2686 where ass.assignment_id = c.assignment_id
2687 and ass.person_id = P_PERSON_ID)
2688 );
2689 --
2690 exception
2691 when NO_DATA_FOUND then
2692 if g_debug then
2693 hr_utility.set_location(l_proc, 510);
2694 end if;
2695 end;
2696 --
2697 if g_debug then
2698 hr_utility.set_location(l_proc, 520);
2699 end if;
2700 --
2701 begin
2702 --
2703 delete from per_cobra_coverage_statuses c2
2704 where c2.cobra_coverage_enrollment_id in
2705 (select c.cobra_coverage_enrollment_id
2706 from per_cobra_cov_enrollments c
2707 ,per_contact_relationships r
2708 where r.contact_person_id = P_PERSON_ID
2709 and c.contact_relationship_id = r.contact_relationship_id
2710 and exists
2711 (select null
2712 from per_assignments_f ass
2713 where ass.assignment_id = c.assignment_id
2714 and ass.person_id = r.person_id)
2715 );
2716 --
2717 exception
2718 when NO_DATA_FOUND then
2719 if g_debug then
2720 hr_utility.set_location(l_proc, 530);
2721 end if;
2722 end;
2723 --
2724 if g_debug then
2725 hr_utility.set_location(l_proc, 540);
2726 end if;
2727 --
2728 begin
2729 --
2730 delete from per_sched_cobra_payments c2
2731 where c2.cobra_coverage_enrollment_id in
2732 (select c.cobra_coverage_enrollment_id
2733 from per_cobra_cov_enrollments c
2734 where exists
2735 (select null
2736 from per_assignments_f ass
2737 where ass.assignment_id = c.assignment_id
2738 and ass.person_id = P_PERSON_ID)
2739 );
2740 --
2741 exception
2742 when NO_DATA_FOUND then
2743 if g_debug then
2744 hr_utility.set_location(l_proc, 550);
2745 end if;
2746 end;
2747 --
2748 if g_debug then
2749 hr_utility.set_location(l_proc, 560);
2750 end if;
2751 --
2752 begin
2753 --
2754 delete from per_sched_cobra_payments c2
2755 where c2.cobra_coverage_enrollment_id in
2756 (select c.cobra_coverage_enrollment_id
2757 from per_cobra_cov_enrollments c
2758 ,per_contact_relationships r
2759 where r.contact_person_id = P_PERSON_ID
2760 and c.contact_relationship_id = r.contact_relationship_id
2761 and exists
2762 (select null
2763 from per_assignments_f ass
2764 where ass.assignment_id = c.assignment_id
2765 and ass.person_id = r.person_id)
2766 );
2767 --
2768 exception
2769 when NO_DATA_FOUND then
2770 if g_debug then
2771 hr_utility.set_location(l_proc, 570);
2772 end if;
2773 end;
2774 --
2775 if g_debug then
2776 hr_utility.set_location(l_proc, 580);
2777 end if;
2778 --
2779 begin
2780 --
2781 delete from per_cobra_cov_enrollments c
2782 where c.assignment_id in
2783 (select ass.assignment_id
2784 from per_assignments_f ass
2785 where ass.person_id = P_PERSON_ID);
2786 --
2787 exception
2788 when NO_DATA_FOUND then
2789 if g_debug then
2790 hr_utility.set_location(l_proc, 590);
2791 end if;
2792 end;
2793 --
2794 if g_debug then
2795 hr_utility.set_location(l_proc, 600);
2796 end if;
2797 --
2798 begin
2799 --
2800 delete from per_cobra_cov_enrollments c
2801 where exists
2802 (select null
2803 from per_contact_relationships r
2804 where r.contact_person_id = P_PERSON_ID
2805 and c.contact_relationship_id = r.contact_relationship_id
2806 and exists
2807 (select null
2808 from per_assignments_f ass
2809 where ass.assignment_id = c.assignment_id
2810 and ass.person_id = r.person_id)
2811 );
2812 --
2813 exception
2814 when NO_DATA_FOUND then
2815 if g_debug then
2816 hr_utility.set_location(l_proc, 610);
2817 end if;
2818 end;
2819 --
2820 --Bug# 3026024 Start Here
2821 --Description : Delete the entry in the table ben_covered_dependents_f for the
2822 -- contact person whom is getting deleted.
2823 --
2824 --
2825 if g_debug then
2826 hr_utility.set_location(l_proc, 620);
2827 end if;
2828 --
2829 begin
2830 --
2831 delete from ben_covered_dependents_f c
2832 where c.contact_relationship_id in
2833 (select r.contact_relationship_id
2834 from per_contact_relationships r
2835 where r.contact_person_id = p_person_id
2836 );
2837 --
2838 exception
2839 when NO_DATA_FOUND then
2840 if g_debug then
2841 hr_utility.set_location(l_proc, 630);
2842 end if;
2843 end;
2844 --
2845 --Bug# 3026024 End Here
2846 --
2847 --
2848 -- If this person has any contacts then check whether they have had any
2849 -- extra info entered for them. If they have not then delete the
2850 -- contacts as well. If they do have extra info then just delete the
2851 -- relationship.
2852 --
2853 -- NB If b is created as a contact of b then 2 contact relationships are
2854 -- are created: a,b and b,a so that they can be queried in either
2855 -- direction. Hence must delete both here.
2856 --
2857 if g_debug then
2858 hr_utility.set_location(l_proc, 640);
2859 end if;
2860 --
2861 begin
2862 --
2863 select count(*)
2864 into l_dummy
2865 from per_contact_relationships r
2866 where r.person_id = P_PERSON_ID;
2867 --
2868 if l_dummy > 0 then
2869 for EACH_CONTACT in csr_this_persons_contacts loop
2870 --
2871 delete from per_contact_relationships r
2872 where (r.person_id = P_PERSON_ID
2873 and r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
2874 or (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
2875 and r.contact_person_id = P_PERSON_ID);
2876 --
2877 hr_person_internal.check_contact(P_PERSON_ID,
2878 EACH_CONTACT.CONTACT_PERSON_ID,
2879 EACH_CONTACT.CONTACT_RELATIONSHIP_ID,
2880 p_effective_date);
2881 --
2882 end loop;
2883 --
2884 end if;
2885 --
2886 exception
2887 when NO_DATA_FOUND then
2888 if g_debug then
2889 hr_utility.set_location(l_proc, 650);
2890 end if;
2891 end;
2892 --
2893 if g_debug then
2894 hr_utility.set_location(l_proc, 660);
2895 end if;
2896 --
2897 begin
2898 --
2899 delete from per_contact_relationships r
2900 where r.contact_person_id = P_PERSON_ID;
2901 --
2902 exception
2903 when NO_DATA_FOUND then
2904 if g_debug then
2905 hr_utility.set_location(l_proc, 670);
2906 end if;
2907 end;
2908 --
2909 if g_debug then
2910 hr_utility.set_location(l_proc, 680);
2911 end if;
2912 --
2913 begin
2914 --
2915 delete from per_addresses a
2916 where a.person_id = P_PERSON_ID;
2917 --
2918 exception
2919 when NO_DATA_FOUND then
2920 if g_debug then
2921 hr_utility.set_location(l_proc, 690);
2922 end if;
2923 end;
2924 --
2925 if g_debug then
2926 hr_utility.set_location(l_proc, 700);
2927 end if;
2928 --
2929 begin
2930 --
2931 delete from per_phones a
2932 where a.parent_id = P_PERSON_ID
2933 and a.parent_table = 'PER_ALL_PEOPLE_F';
2934 --
2935 exception
2936 when NO_DATA_FOUND then
2937 if g_debug then
2938 hr_utility.set_location(l_proc, 710);
2939 end if;
2940 end;
2941 --
2942 if g_debug then
2943 hr_utility.set_location(l_proc, 720);
2944 end if;
2945 --
2946 -- we must do this delete in dynamic sql because the per_performance_reviews
2947 -- table will not exist if the database has not been upgraded to new salary
2948 -- admin (introduced April 1998). The procedure would not compile if this
2949 -- was not dynamic. if the table is not found then the error (which starts
2950 -- with 'ORA-00942') is ignored.
2951 --
2952 begin
2953 --
2954 l_review_cursor:=dbms_sql.open_cursor;
2955 dbms_sql.parse(l_review_cursor,'DELETE from PER_PERFORMANCE_REVIEWS
2956 where person_id=:x',dbms_sql.v7);
2957 dbms_sql.bind_variable(l_review_cursor, ':x',P_PERSON_ID);
2958 l_rows_processed:=dbms_sql.execute(l_review_cursor);
2959 dbms_sql.close_cursor(l_review_cursor);
2960 --
2961 exception
2962 when NO_DATA_FOUND then dbms_sql.close_cursor(l_review_cursor);
2963 when OTHERS then
2964 dbms_sql.close_cursor(l_review_cursor);
2965 --
2966 if(substr(sqlerrm,0,9)<>'ORA-00942') then
2967 raise;
2968 end if;
2969 end;
2970 --
2971 if g_debug then
2972 hr_utility.set_location(l_proc, 730);
2973 end if;
2974 --
2975 -- About to delete interview events for assignments. However, must
2976 -- first delete bookings (interviewers) for those events.
2977 --
2978 begin
2979 -- bug fix 3732129.
2980 -- Delete statement modified to improve performance.
2981 --
2982 forall i in 1..l_assignment_id.count
2983 delete from per_bookings b
2984 where b.event_id in
2985 (select e.event_id
2986 from per_events e
2987 where e.assignment_id = l_assignment_id(i));
2988
2989 /*delete from per_bookings b
2990 where b.event_id in
2991 (select e.event_id
2992 from per_events e
2993 where exists (
2994 select null
2995 from per_assignments_f ass
2996 where ass.assignment_id = e.assignment_id
2997 and ass.person_id = P_PERSON_ID)
2998 );*/
2999 exception
3000 when NO_DATA_FOUND then
3001 if g_debug then
3002 hr_utility.set_location(l_proc, 740);
3003 end if;
3004 end;
3005 --
3006 if g_debug then
3007 hr_utility.set_location(l_proc, 750);
3008 end if;
3009 --
3010 begin
3011 -- bug fix 3732129.
3012 -- Delete statement modified to improve performance.
3013 --
3014 forall i in 1..l_assignment_id.count
3015 delete from per_events e
3016 where e.assignment_id = l_assignment_id(i);
3017
3018 /* delete from per_events e
3019 where e.assignment_id in (
3020 select ass.assignment_id
3021 from per_assignments_f ass
3022 where ass.person_id = P_PERSON_ID);*/
3023 exception
3024 when NO_DATA_FOUND then
3025 if g_debug then
3026 hr_utility.set_location(l_proc, 760);
3027 end if;
3028 end;
3029 --
3030 if g_debug then
3031 hr_utility.set_location(l_proc, 770);
3032 end if;
3033 --
3034 begin
3035 --
3036 update per_events e
3037 set e.internal_contact_person_id = null
3038 where e.internal_contact_person_id = P_PERSON_ID;
3039 --
3040 exception
3041 when NO_DATA_FOUND then
3042 if g_debug then
3043 hr_utility.set_location(l_proc, 780);
3044 end if;
3045 end;
3046 --
3047 if g_debug then
3048 hr_utility.set_location(l_proc, 790);
3049 end if;
3050 --
3051 begin
3052 --
3053 delete from per_bookings b
3054 where b.person_id = P_PERSON_ID;
3055 --
3056 exception
3057 when NO_DATA_FOUND then
3058 if g_debug then
3059 hr_utility.set_location(l_proc, 800);
3060 end if;
3061 end;
3062 --
3063 if g_debug then
3064 hr_utility.set_location(l_proc, 810);
3065 end if;
3066 --
3067 begin
3068 --
3069 delete from per_quickpaint_result_text q
3070 where q.assignment_id in
3071 (select ass.assignment_id
3072 from per_assignments_f ass
3073 where ass.person_id = P_PERSON_ID);
3074 --
3075 exception
3076 when NO_DATA_FOUND then
3077 if g_debug then
3078 hr_utility.set_location(l_proc, 820);
3079 end if;
3080 end;
3081 --
3082 if g_debug then
3083 hr_utility.set_location(l_proc, 830);
3084 end if;
3085 --
3086 -- Validation has already been performed against
3087 -- hr_assignment_set_amendments in weak_predel_validation.
3088 --
3089 begin
3090 --
3091 delete from hr_assignment_set_amendments h
3092 where h.assignment_id in
3093 (select ass.assignment_id
3094 from per_assignments_f ass
3095 where ass.person_id = P_PERSON_ID);
3096 --
3097 exception
3098 when NO_DATA_FOUND then
3099 if g_debug then
3100 hr_utility.set_location(l_proc, 840);
3101 end if;
3102 end;
3103 --
3104 if g_debug then
3105 hr_utility.set_location(l_proc, 850);
3106 end if;
3107 --
3108 begin
3109 --
3110 delete from pay_cost_allocations_f a
3111 where a.assignment_id in
3112 (select ass.assignment_id
3113 from per_assignments_f ass
3114 where ass.person_id = P_PERSON_ID);
3115 --
3116 exception
3117 when NO_DATA_FOUND then
3118 if g_debug then
3119 hr_utility.set_location(l_proc, 860);
3120 end if;
3121 end;
3122 --
3123 if g_debug then
3124 hr_utility.set_location(l_proc, 870);
3125 end if;
3126 --
3127 begin
3128 --
3129 delete from per_spinal_point_placements_f p
3130 where p.assignment_id in
3131 (select ass.assignment_id
3132 from per_assignments_f ass
3133 where ass.person_id = P_PERSON_ID);
3134 --
3135 exception
3136 when NO_DATA_FOUND then
3137 if g_debug then
3138 hr_utility.set_location(l_proc, 880);
3139 end if;
3140 end;
3141 --
3142 if g_debug then
3143 hr_utility.set_location(l_proc, 890);
3144 end if;
3145 --
3146 -- Validation has already been performed against
3147 -- pay_assignment_actions in weak_predel_validation.
3148 --
3149 begin
3150 --
3151 delete from pay_assignment_actions a
3152 where a.assignment_id in
3153 (select ass.assignment_id
3154 from per_assignments_f ass
3155 where ass.person_id = P_PERSON_ID);
3156 --
3157 exception
3158 when NO_DATA_FOUND then
3159 if g_debug then
3160 hr_utility.set_location(l_proc, 900);
3161 end if;
3162 end;
3163 --
3164 if g_debug then
3165 hr_utility.set_location(l_proc, 910);
3166 end if;
3167 --
3168 begin
3169 --
3170 delete from pay_assignment_latest_balances b
3171 where b.assignment_id in
3172 (select ass.assignment_id
3173 from per_assignments_f ass
3174 where ass.person_id = P_PERSON_ID);
3175 --
3176 exception
3177 when NO_DATA_FOUND then
3178 if g_debug then
3179 hr_utility.set_location(l_proc, 920);
3180 end if;
3181 end;
3182 --
3183 if g_debug then
3184 hr_utility.set_location(l_proc, 930);
3185 end if;
3186 --
3187 begin
3188 -- bug fix 3732129
3189 -- Delete statement modified to improve performance.
3190 --
3191 forall i in 1..l_assignment_id.count
3192 delete from pay_assignment_link_usages_f u
3193 where u.assignment_id = l_assignment_id(i);
3194
3195 /*delete from pay_assignment_link_usages_f u
3196 where
3197 u.assignment_id in (
3198 select ass.assignment_id
3199 from per_assignments_f ass
3200 where ass.person_id = P_PERSON_ID); */
3201 exception
3202 when NO_DATA_FOUND then
3203 if g_debug then
3204 hr_utility.set_location(l_proc, 940);
3205 end if;
3206 end;
3207 --
3208 if g_debug then
3209 hr_utility.set_location(l_proc, 950);
3210 end if;
3211 --
3212 begin
3213 --
3214 delete from pay_element_entry_values_f v
3215 where v.element_entry_id in
3216 (select e.element_entry_id
3217 from pay_element_entries_f e
3218 where exists
3219 (select null
3220 from per_assignments_f ass
3221 where ass.assignment_id = e.assignment_id
3222 and ass.person_id = P_PERSON_ID)
3223 );
3224 --
3225 exception
3226 when NO_DATA_FOUND then
3227 if g_debug then
3228 hr_utility.set_location(l_proc, 960);
3229 end if;
3230 end;
3231 --
3232 if g_debug then
3233 hr_utility.set_location(l_proc, 970);
3234 end if;
3235 --
3236 begin
3237 --
3238 delete from pay_run_results r
3239 where r.source_type = 'E'
3240 and r.source_id in
3241 (select e.element_entry_id
3242 from pay_element_entries_f e
3243 where exists
3244 (select null
3245 from per_assignments_f ass
3246 where ass.assignment_id = e.assignment_id
3247 and ass.person_id = P_PERSON_ID)
3248 );
3249 --
3250 exception
3251 when NO_DATA_FOUND then
3252 if g_debug then
3253 hr_utility.set_location(l_proc, 980);
3254 end if;
3255 end;
3256 --
3257 if g_debug then
3258 hr_utility.set_location(l_proc, 990);
3259 end if;
3260 --
3261 begin
3262 --
3263 delete from pay_element_entries_f e
3264 where e.assignment_id in
3265 (select ass.assignment_id
3266 from per_assignments_f ass
3267 where ass.person_id = P_PERSON_ID);
3268 --
3269 exception
3270 when NO_DATA_FOUND then
3271 if g_debug then
3272 hr_utility.set_location(l_proc, 10);
3273 end if;
3274 end;
3275 --
3276 if g_debug then
3277 hr_utility.set_location(l_proc, 20);
3278 end if;
3279 --
3280 -- Rmonge Bug 1686922 22-FEB-2002
3281 -- Tax records were not being deleted. Therefore, there were orphans rows in
3282 -- the pay_us_fed_tax_rules_f, pay_us_state_tax_rules_f,
3283 -- pay_us_county_tax_rules_f, and pay_us_city_tax_rules_f.
3284 --
3285 begin
3286 --
3287 Delete pay_us_emp_fed_tax_rules_f peft
3288 Where peft.assignment_id in
3289 (select ass.assignment_id
3290 from per_assignments_f ass
3291 where ass.person_id = p_person_id );
3292 --
3293 exception
3294 when no_data_found then
3295 if g_debug then
3296 hr_utility.set_location(l_proc, 30);
3297 end if;
3298 end;
3299 --
3300 begin
3301 --
3302 if g_debug then
3303 hr_utility.set_location(l_proc, 40);
3304 end if;
3305 --
3306 Delete pay_us_emp_state_tax_rules_f pest
3307 Where pest.assignment_id in
3308 (select ass.assignment_id
3309 from per_assignments_f ass
3310 where ass.person_id = p_person_id );
3311 --
3312 exception
3313 when no_data_found then
3314 if g_debug then
3315 hr_utility.set_location(l_proc, 50);
3316 end if;
3317 end;
3318 --
3319 begin
3320 --
3321 if g_debug then
3322 hr_utility.set_location(l_proc, 60);
3323 end if;
3324 --
3325 Delete pay_us_emp_county_tax_rules_f pect
3326 Where pect.assignment_id in
3327 (select ass.assignment_id
3328 from per_assignments_f ass
3329 where ass.person_id = p_person_id );
3330 --
3331 exception
3332 when no_data_found then
3333 if g_debug then
3334 hr_utility.set_location(l_proc, 70);
3335 end if;
3336 end;
3337 --
3338 begin
3339 --
3340 if g_debug then
3341 hr_utility.set_location(l_proc, 80);
3342 end if;
3343 --
3344 Delete pay_us_emp_city_tax_rules_f pecit
3345 Where pecit.assignment_id in
3346 (select ass.assignment_id
3347 from per_assignments_f ass
3348 where ass.person_id = p_person_id );
3349 --
3350 exception
3351 when no_data_found then
3352 if g_debug then
3353 hr_utility.set_location(l_proc, 90);
3354 end if;
3355 end;
3356 -- Finished, now unlock assignments and delete them.
3357 --
3358 close csr_lock_assignment_rows;
3359 --
3360 if g_debug then
3361 hr_utility.set_location(l_proc, 100);
3362 end if;
3363 --
3364 begin
3365 --
3366 delete from per_all_assignments_f a
3367 where a.person_id = P_PERSON_ID;
3368 --
3369 exception
3370 when NO_DATA_FOUND then
3371 if g_debug then
3372 hr_utility.set_location(l_proc, 110);
3373 end if;
3374 end;
3375 --
3376 if g_debug then
3377 hr_utility.set_location(l_proc, 120);
3378 end if;
3379 --
3380 begin
3381 --
3382 delete from per_periods_of_service p
3383 where p.person_id = P_PERSON_ID;
3384 --
3385 exception
3386 when NO_DATA_FOUND then
3387 if g_debug then
3388 hr_utility.set_location(l_proc, 130);
3389 end if;
3390 end;
3391 --
3392 if g_debug then
3393 hr_utility.set_location(l_proc, 140);
3394 end if;
3395 --
3396 begin
3397 --
3398 delete from per_applications a
3399 where a.person_id = P_PERSON_ID;
3400 --
3401 exception
3402 when NO_DATA_FOUND then
3403 if g_debug then
3404 hr_utility.set_location(l_proc, 150);
3405 end if;
3406 end;
3407 --
3408 -- 03/18/98 Bug #642566
3409 -- delete per_people_extra_info records
3410 if g_debug then
3411 hr_utility.set_location(l_proc, 160);
3412 end if;
3413 --
3414 begin
3415 --
3416 delete from per_people_extra_info e
3417 where e.person_id = P_PERSON_ID;
3418 --
3419 exception
3420 when NO_DATA_FOUND then
3421 if g_debug then
3422 hr_utility.set_location(l_proc, 170);
3423 end if;
3424 end;
3425 -- 03/18/98 Change Ends
3426 --
3427 -- 03/18/98 Change Ends
3428 --
3429 -- 28/5/98
3430 -- Add delete from per_person_type_usages_f
3431 if g_debug then
3432 hr_utility.set_location(l_proc, 180);
3433 end if;
3434 --
3435 for ptu_rec in csr_ptu loop
3436 --
3437 select min(ptu1.effective_start_date)
3438 into l_effective_date
3439 from per_person_type_usages_f ptu1
3440 where ptu1.person_type_usage_id = ptu_rec.person_type_usage_id;
3441 --
3442 select ptu2.object_version_number
3443 into l_object_version_number
3444 from per_person_type_usages_f ptu2
3445 where ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
3446 and ptu2.effective_start_date = l_effective_date;
3447 --
3448 if g_debug then
3449 --
3450 hr_utility.set_location('l_person_type_usage_id = '||to_char(ptu_rec.person_type_usage_id),44);
3451 hr_utility.set_location('l_effective_date = '||to_char(l_effective_date,'DD/MM/YYYY'),44);
3452 hr_utility.set_location('l_object_version_number = '||to_char(l_object_version_number),44);
3453 --
3454 end if;
3455 begin
3456 --
3457 -- hr_per_type_usage_internal.maintain_ptu(
3458 -- p_person_id => p_person_id,
3459 -- p_action => 'DELETE',
3460 -- p_period_of_service_id => NULL,
3461 -- p_actual_termination_date => NULL,
3462 -- p_business_group_id => NULL,
3463 -- p_date_start => NULL,
3464 -- p_leaving_reason => NULL,
3465 -- p_old_date_start => NULL,
3466 -- p_old_leaving_reason => NULL);
3467
3468 hr_per_type_usage_internal.delete_person_type_usage
3469 (p_person_type_usage_id => ptu_rec.person_type_usage_id
3470 ,p_effective_date => l_effective_date
3471 ,p_datetrack_mode => 'ZAP'
3472 ,p_object_version_number => l_object_version_number
3473 ,p_effective_start_date => l_effective_start_date
3474 ,p_effective_end_date => l_effective_end_date
3475 );
3476 exception
3477 when NO_DATA_FOUND then null;
3478 end;
3479 --
3480 end loop;
3481 --
3482 -- delete per_person_dlvry_methods
3483 if g_debug then
3484 hr_utility.set_location(l_proc, 190);
3485 end if;
3486 --
3487 begin
3488 --
3489 delete from per_person_dlvry_methods
3490 where person_id = P_PERSON_ID;
3491 exception
3492 when NO_DATA_FOUND then null;
3493 end;
3494 --
3495 -- Added this delete for quickhire checklists
3496 --
3497 if g_debug then
3498 hr_utility.set_location(l_proc, 200);
3499 end if;
3500 begin
3501 --
3502 delete from per_checklist_items
3503 where person_id = P_PERSON_ID;
3504 exception
3505 when NO_DATA_FOUND then null;
3506 end;
3507 --
3508 -- End addition for quickhire checklists
3509 --
3510 -- delete per_qualification and per_subjects_taken records
3511 if g_debug then
3512 hr_utility.set_location(l_proc, 210);
3513 end if;
3514 --
3515 begin
3516 -- Fix for 4490489 starts here
3517 --
3518 select distinct party_id into l_party_id
3519 from per_all_people_f
3520 where person_id = p_person_id;
3521 --
3522 select count(distinct person_id) into l_count
3523 from per_all_people_f
3524 where party_id = l_party_id;
3525 --
3526 if l_count = 1 then
3527 --PMFLETCH Added delete from tl table
3528 --
3529 delete from per_subjects_taken_tl st
3530 where st.subjects_taken_id IN
3531 (select s.subjects_taken_id
3532 from per_subjects_taken s
3533 ,per_qualifications q
3534 where q.party_id = l_party_id
3535 and s.qualification_id = q.qualification_id
3536 );
3537 --
3538 if g_debug then
3539 hr_utility.set_location(l_proc, 220);
3540 end if;
3541 --
3542 delete from per_subjects_taken s
3543 where s.qualification_id in
3544 (select qualification_id
3545 from per_qualifications
3546 where party_id = l_party_id );
3547 --
3548 if g_debug then
3549 hr_utility.set_location(l_proc, 230);
3550 end if;
3551 --PMFLETCH Added delete from tl table
3552 delete from per_qualifications_tl qt
3553 where qt.qualification_id in
3554 (select q.qualification_id
3555 from per_qualifications q
3556 where q.party_id = l_party_id);
3557 --
3558 if g_debug then
3559 hr_utility.set_location(l_proc, 240);
3560 end if;
3561 --
3562 delete from per_qualifications q
3563 where q.party_id = l_party_id;
3564 --
3565 end if;
3566 --
3567 exception
3568 when NO_DATA_FOUND then
3569 if g_debug then
3570 hr_utility.set_location(l_proc, 250);
3571 end if;
3572 end;
3573 --
3574 -- Fix for 4490489 ends here
3575 --
3576 --
3577 --PMFLETCH Added delete from tl table
3578 /* delete from per_subjects_taken_tl st
3579 where st.subjects_taken_id IN
3580 (select s.subjects_taken_id
3581 from per_subjects_taken s
3582 ,per_qualifications q
3583 where q.person_id = P_PERSON_ID
3584 and s.qualification_id = q.qualification_id
3585 );
3586 --
3587 if g_debug then
3588 hr_utility.set_location(l_proc, 220);
3589 end if;
3590 --
3591 delete from per_subjects_taken s
3592 where s.qualification_id in
3593 (select qualification_id
3594 from per_qualifications
3595 where person_id = P_PERSON_ID );
3596 --
3597 if g_debug then
3598 hr_utility.set_location(l_proc, 230);
3599 end if;
3600 --PMFLETCH Added delete from tl table
3601 delete from per_qualifications_tl qt
3602 where qt.qualification_id in
3603 (select q.qualification_id
3604 from per_qualifications q
3605 where q.person_id = P_PERSON_ID);
3606 --
3607 if g_debug then
3608 hr_utility.set_location(l_proc, 240);
3609 end if;
3610 --
3611 delete from per_qualifications q
3612 where q.person_id = P_PERSON_ID;
3613 --
3614 exception
3615 when NO_DATA_FOUND then
3616 if g_debug then
3617 hr_utility.set_location(l_proc, 250);
3618 end if;
3619 end; */
3620 --
3621 close csr_lock_person_rows;
3622 --
3623 if g_debug then
3624 hr_utility.set_location(l_proc, 260);
3625 end if;
3626
3627 --changes for 5166353 starts here
3628 ben_person_delete.delete_ben_rows(P_PERSON_ID);
3629 --changes for 5166353 ends here
3630 --
3631 begin
3632 --
3633 delete from per_all_people_f
3634 where person_id = P_PERSON_ID;
3635 exception
3636 when NO_DATA_FOUND then
3637 if g_debug then
3638 hr_utility.set_location(l_proc, 270);
3639 end if;
3640 end;
3641 --
3642 -- Now remove contracts
3643 --
3644 hr_contract_api.maintain_contracts (
3645 P_PERSON_ID,
3646 NULL,
3647 NULL);
3648 --
3649 if g_debug then
3650 hr_utility.set_location(l_proc, 280);
3651 end if;
3652 --
3653 -- Now remove Medical Assessments
3654 --
3655 FOR mea_rec IN csr_medical_assessment_records LOOP
3656 --
3657 per_medical_assessment_api.delete_medical_assessment
3658 (FALSE
3659 ,mea_rec.medical_assessment_id
3660 ,mea_rec.object_version_number);
3661 --
3662 END LOOP;
3663 --
3664 if g_debug then
3665 hr_utility.set_location(l_proc, 290);
3666 end if;
3667 --
3668 --
3669 -- Now remove disabilities
3670 --
3671 open csr_disabilities;
3672 LOOP
3673 fetch csr_disabilities INTO l_disability_id, l_object_version_no, l_effective_start_date, l_effective_end_date;
3674 EXIT when csr_disabilities%NOTFOUND;
3675 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);
3676 END LOOP;
3677 --
3678 if g_debug then
3679 hr_utility.set_location(l_proc, 300);
3680 end if;
3681 --
3682 --
3683 -- Now remove Work incidences
3684 --
3685 open csr_work_incidents;
3686 LOOP
3687 fetch csr_work_incidents INTO l_incident_id, l_object_version_number;
3688 EXIT when csr_work_incidents%NOTFOUND;
3689 per_work_incident_api.delete_work_incident(false,l_incident_id, l_object_version_number);
3690 END LOOP;
3691 --
3692 if g_debug then
3693 hr_utility.set_location(l_proc, 310);
3694 end if;
3695 --
3696 --
3697 -- Now remove Supplementary Roles
3698 --
3699 OPEN csr_roles;
3700 LOOP
3701 fetch csr_roles into l_role_id, l_ovn_roles;
3702 EXIT when csr_roles%notfound;
3703 per_supplementary_role_api.delete_supplementary_role(false, l_role_id, l_ovn_roles);
3704 END LOOP;
3705 --
3706 if g_debug then
3707 hr_utility.set_location(l_proc, 320);
3708 end if;
3709 --
3710 --
3711 begin
3712 delete from per_periods_of_placement p
3713 where p.person_id = P_PERSON_ID;
3714 exception
3715 when NO_DATA_FOUND then
3716 if g_debug then
3717 hr_utility.set_location(l_proc, 330);
3718 end if;
3719 end;
3720 --
3721 --
3722 -- Now remove Attachments
3723 --
3724 begin
3725 for attached_docs_rec in attached_docs_cursor
3726 LOOP
3727 if attached_docs_cursor%NOTFOUND then
3728 return;
3729 end if;
3730 l_attached_document_id := attached_docs_rec.attached_document_id;
3731 open delattachments_cursor (l_attached_document_id);
3732 FETCH delattachments_cursor into deldatarec;
3733 if delattachments_cursor%NOTFOUND then
3734 return;
3735 end if;
3736 l_datatype_id := deldatarec.datatype_id ;
3737 FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
3738 l_datatype_id,
3739 'Y' );
3740 CLOSE delattachments_cursor;
3741 END LOOP;
3742 exception
3743 when NO_DATA_FOUND then null;
3744 end;
3745 --
3746 if g_debug then
3747 hr_utility.set_location('Leaving:'||l_proc, 999);
3748 end if;
3749 --
3750 END delete_person;
3751 end hr_person_internal;
[Home] [Help]